UserCatalogs

Last edit

Changed: 3,68c3,7

< The script is the demonstraction of the metadata storage creation for the user <b>user1</b>
< <pre>
< BEGIN WORK;
< CREATE SCHEMA cas_metadata_user1;
< SET search_path TO cas_metadata_user1, cas_metadata, public;
< CREATE TABLE catalog_user_list(
< id integer default nextval('catalog_list_id_seq') PRIMARY KEY,
< name varchar UNIQUE NOT NULL,
< info varchar,
< description varchar
< );
< CREATE TABLE table_user_list(
< id integer default nextval('table_list_id_seq') PRIMARY KEY,
< catalog_id bigint REFERENCES catalog_user_list(id) ON DELETE CASCADE ON UPDATE CASCADE,
< name varchar NOT NULL,
< info varchar,
< description varchar,
< UNIQUE (catalog_id, name)
< );
< CREATE TABLE ucd_user_list (
< id serial PRIMARY KEY,
< name varchar UNIQUE,
< info varchar
< );
< CREATE TABLE attribute_user_list(
< id integer default nextval('attribute_list_id_seq'),
< table_id bigint REFERENCES table_user_list(id) ON UPDATE CASCADE ON DELETE CASCADE,
< name varchar NOT NULL,
< ucd_id bigint REFERENCES ucd_user_list(id),
< unit varchar,
< width smallint,
< precision smallint,
< datatype_id bigint REFERENCES cas_metadata.datatype_list(id) ON UPDATE CASCADE ON DELETE CASCADE,
< info varchar,
< description varchar,
< UNIQUE ( table_id, name)
< );
< CREATE VIEW catalog_list AS SELECT * FROM catalog_user_list UNION ALL
< SELECT * FROM cas_metadata.catalog_list;
< CREATE VIEW table_list AS SELECT * from table_user_list UNION ALL
< SELECT * FROM cas_metadata.table_list;
< CREATE VIEW attribute_list AS SELECT * from attribute_user_list UNION ALL
< SELECT * FROM cas_metadata.attribute_list;
< CREATE VIEW ucd_list AS SELECT * from ucd_user_list UNION ALL
< SELECT * FROM cas_metadata.ucd_list;
< CREATE RULE rule_insert_table AS ON INSERT TO table_list DO INSTEAD
< INSERT INTO table_user_list (catalog_id, name, info, description)
< SELECT NEW.catalog_id, NEW.name, NEW.info, NEW.description;
< CREATE RULE rule_update_table AS ON UPDATE TO table_list DO INSTEAD
< UPDATE table_user_list SET catalog_id = NEW.catalog_id, name= NEW.name,
< info = NEW.info, description = NEW.description;
< CREATE RULE rule_insert_attribute AS ON INSERT TO attribute_list DO INSTEAD
< INSERT INTO attribute_user_list (table_id, name, ucd_id, unit, width, precision, datatype_id, info, description)
< SELECT NEW.table_id, NEW.name, NEW.ucd_id, NEW.unit, NEW.width, NEW.precision,NEW.datatype_id, NEW.info, NEW.description;
< CREATE RULE rule_update_attribute AS ON update TO attribute_list DO INSTEAD
< UPDATE attribute_user_list SET table_id = NEW.table_id, name=NEW.name,
< ucd_id = NEW.ucd_id, unit = NEW.unit, width = NEW.width,
< precision = NEW.precision, info = NEW.info, description = NEW.description;
< CREATE RULE rule_insert_ucd AS ON INSERT TO ucd_list DO INSTEAD
< INSERT INTO ucd_user_list (name, info)
< SELECT NEW.name, NEW.info;
< CREATE RULE rule_update_ucd AS ON UPDATE TO table_list DO INSTEAD
< UPDATE table_user_list SET name= NEW.name, info = NEW.info;
< END WORK;
< </pre>
< So after that script, when if the user will use the search_path=cas_metadata_user1,cas_metadata

to

> The scripts for creating of the user's DB structure are available here
> http://vo.astronet.ru/cgi-bin/viewvc.cgi/sql/trunk/user_tables.sql?root=cas&view=markup
> And
the set of functions:
> http://vo.astronet.ru/cgi-bin/viewvc.cgi/sql/trunk/user_tables_functions.sql?root=cas&view=markup

> So after that setup, if the user "someuser" will use the search_path=cas_metadata_someuser,cas_metadata


Here I propose the SQL script creating the DB structure for the user's catalogues. To properly understand the idea, the reader should understand the overall DB stucture in CAS ( see CASDB ).

So, the main purpose of the structure described below is to preserve the already existing interfaces of retrieving the metadata about the catalogues, and to make the interfaces almost independ on the fact whether the queries are run from the anonymous user (when only CAS main tables are seen) or from some specific user with his own set of tables.

The scripts for creating of the user's DB structure are available here http://vo.astronet.ru/cgi-bin/viewvc.cgi/sql/trunk/user_tables.sql?root=cas&view=markup And the set of functions: http://vo.astronet.ru/cgi-bin/viewvc.cgi/sql/trunk/user_tables_functions.sql?root=cas&view=markup

So after that setup, if the user "someuser" will use the search_path=cas_metadata_someuser,cas_metadata in the tables (catalog_list, table_list, attribute_list) he will see the same his own data and the data from system tables. Also since the catalog_list, table_list and attribute_list are just VIEW's it is easy to include in those VIEWs the tables from the other login for example (if somebody wants to share his tables with another login).

That script seem to leave working all the interface functions from the CASDB

Another important notice, that as the primary key I'm using the SEQUENCEs from the ids of the system tables!!!, so the id numeration in the whole system will be consistent (I need that, since several my interface functions do the id lookup, and I don't want to break them on the VIEW like table_list which is union of the table_user_list and system table_list)