[OPEN-ILS-DEV] ***SPAM*** New schema - munge

[Galen Charlton]

Hi,

I'd like to add a new Postgres schema to the Evergreen database schema, to be called 'munge'.  The remit of munge would be to store:

* functions and procedures for manipulating, extracting, and munging data generically, where "generic" = "could apply to more than one of the other schemas".  An example would be stored procedures that handle MARCXML but that are generic enough that they don't apply *only* to biblio, authority, or serials.  Another example might be moving functions such as naco_normalize from public to munge, although that wouldn't be a priority; the main thing that would get us is making it slightly easier to compare versions of the Evergreen schema by making it possible to exclude the public schema from the comparison.
* tables that store information about big data change operations such as bulk loads.  See footnote [1] for an example of what I mean; the stored procedures in the footnote would be what I intend to seed the new schema with.

Functions in this schema could be invoked from higher up the application stack, although some of them may exist purely for the convenience of people manipulating data using SQL.

What would be excluded from this schema would be:

* anything that implements business logic per se
* anything that is too specific to a particular type of data; for example, let's say I added a wrapper for MARC::Lint.  Since, at least at the moment, MARC::Lint can only be used to MARC bib records, a function called marc_lint_report() would belong only in biblio.
* anything that is too specific to migrating data from a particular source - that's what the various migration toolkits floating around are for
* tables in general, except for cases like db_object_stash [1] where the table would be used to store transitory information about a bulk data change operation that is in process.

If there are any major objections to this idea, please let me know.  Of course, I'd also appreciate positive feedback and ideas on the proper scope of this proposed schema.

Regards,

Galen
--
Galen Charlton
VP, Data Services
Equinox Software, Inc. / Your Library's Guide to Open Source
email:  gmc at esilibrary.com
direct: +1 352-215-7548
skype:  gmcharlt
web:    http://www.esilibrary.com/

[1] Stored procedures to stash and recreate indexes; e.g., for using during a bib load

DROP TABLE IF EXISTS munge.db_object_stash;
CREATE TABLE munge.db_object_stash (
  schema_name NAME,
  table_name  NAME,
  object_name NAME,
  object_type TEXT CHECK (object_type IN ('index', 'trigger', 'rule')),
  restore_command TEXT
);

CREATE OR REPLACE FUNCTION munge.stash_index (_table_schema TEXT, _table_name TEXT, _index_name TEXT) RETURNS VOID AS $$
DECLARE
    restore TEXT;
BEGIN
    SELECT indexdef INTO restore
        FROM pg_indexes
        WHERE schemaname = _table_schema
        AND   tablename = _table_name
        AND   indexname = _index_name;
    IF restore IS NULL
    THEN
        RAISE EXCEPTION 'no restore command for %', _index_name;
    END IF;
    INSERT INTO munge.db_object_stash (schema_name, table_name, object_name, object_type, restore_command)
        VALUES(_table_schema, _table_name, _index_name, 'index', restore);
    EXECUTE 'DROP INDEX ' || _table_schema || '.' || _index_name;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;

CREATE OR REPLACE FUNCTION munge.restore_index (_table_schema TEXT, _table_name TEXT, _index_name TEXT) RETURNS VOID AS $$
DECLARE
    restore TEXT;
BEGIN
    SELECT restore_command INTO restore
        FROM munge.db_object_stash
        WHERE schema_name = _table_schema
        AND   table_name = _table_name
        AND   object_name = _index_name
        AND   object_type = 'index';
    EXECUTE restore;
    DELETE FROM munge.db_object_stash
        WHERE schema_name = _table_schema
        AND   table_name = _table_name
        AND   object_name = _index_name
        AND   object_type = 'index';
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
Please come by and visit the Equinox team and learn more about 
Evergreen, Koha, and open source options
ALA Annual meeting in Washington, DC
June 24-28, 2010
booth # 1303