firethorn

view firethorn-webapp/src/main/webapp/WEB-INF/data/pgsql_tap_schema.sql @ 4293:a0341ca72077

Undo changes to pgsql_tap_schema.sql
author Stelios <stv@roe.ac.uk>
date Fri Mar 06 00:51:33 2020 +0200 (22 months ago)
parents 822a5b9c622b
children
line source
1 CREATE TABLE "TAP_SCHEMA"."schemas" ("ts_schema_id" SERIAL PRIMARY KEY, "schema_name" VARCHAR, "description" VARCHAR, "utype" VARCHAR, "ft_schema_id" INTEGER);
2 CREATE TABLE "TAP_SCHEMA"."tables" ("ts_table_id" SERIAL PRIMARY KEY, "schema_name" VARCHAR, "table_name" VARCHAR, "table_type" VARCHAR, "description" VARCHAR, "utype" VARCHAR, "ft_table_id" INTEGER);
3 CREATE TABLE "TAP_SCHEMA"."columns" ("ts_column_id" SERIAL PRIMARY KEY, "table_name" VARCHAR, "column_name" VARCHAR, "description" VARCHAR, "unit" VARCHAR, "ucd" VARCHAR, "utype" VARCHAR, "datatype" VARCHAR, "size" INTEGER, "arraysize" INTEGER, "principal" INTEGER, "indexed" INTEGER, "std" INTEGER, "ft_column_id" INTEGER);
4 CREATE TABLE "TAP_SCHEMA"."keys" ("ts_key_id" SERIAL PRIMARY KEY, "key_id" VARCHAR, "from_table" VARCHAR, "target_table" VARCHAR, "description" VARCHAR, "utype" VARCHAR);
5 CREATE TABLE "TAP_SCHEMA"."key_columns" ("ts_key_column_id" SERIAL PRIMARY KEY, "key_id" VARCHAR, "from_column" VARCHAR, "target_column" VARCHAR);
7 INSERT INTO "TAP_SCHEMA"."schemas"("schema_name", "description", "utype", "ft_schema_id") VALUES ('TAP_SCHEMA', 'TAP_SCHEMA', NULL, 0);
8 INSERT INTO "TAP_SCHEMA"."tables"("schema_name", "table_name", "table_type", "description", "utype", "ft_table_id") VALUES ('TAP_SCHEMA', 'TAP_SCHEMA.schemas', 'table', 'List of schemas published in this TAP service.', NULL,0);
9 INSERT INTO "TAP_SCHEMA"."tables" ("schema_name", "table_name", "table_type", "description", "utype", "ft_table_id") VALUES ('TAP_SCHEMA', 'TAP_SCHEMA.tables', 'table', 'List of tables published in this TAP service.', NULL,0);
10 INSERT INTO "TAP_SCHEMA"."tables" ("schema_name", "table_name", "table_type", "description", "utype", "ft_table_id") VALUES ('TAP_SCHEMA', 'TAP_SCHEMA.columns', 'table', 'List of columns of all tables listed in TAP_SCHEMA.TABLES and published in this TAP service.', NULL,0);
11 INSERT INTO "TAP_SCHEMA"."tables" ("schema_name", "table_name", "table_type", "description", "utype", "ft_table_id") VALUES ('TAP_SCHEMA', 'TAP_SCHEMA.keys', 'table', 'List all foreign keys but provides just the tables linked by the foreign key. To know which columns of these tables are linked, see in TAP_SCHEMA.key_columns using the key_id.', NULL,0);
12 INSERT INTO "TAP_SCHEMA"."tables" ("schema_name", "table_name", "table_type", "description", "utype", "ft_table_id") VALUES ('TAP_SCHEMA', 'TAP_SCHEMA.key_columns', 'table', 'List all foreign keys but provides just the columns linked by the foreign key. To know the table of these columns, see in TAP_SCHEMA.keys using the key_id.', NULL,0);
13 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.schemas', 'schema_name', 'schema name, possibly qualified', '', '', '', 'char', -1, -1, 1, 0, 1, 0);
14 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.schemas', 'description', 'brief description of schema', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
15 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.schemas', 'utype', 'UTYPE if schema corresponds to a data model', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
16 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.tables', 'schema_name', 'the schema name from TAP_SCHEMA.schemas', '', '', '', 'char', -1, -1, 1, 0, 1, 0);
17 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.tables', 'table_name', 'table name as it should be used in queries', '', '', '', 'char', -1, -1, 1, 0, 1, 0);
18 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.tables', 'table_type', 'one of: table, view', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
19 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.tables', 'description', 'brief description of table', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
20 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.tables', 'utype', 'UTYPE if table corresponds to a data model', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
21 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'table_name', 'table name from TAP_SCHEMA.tables', '', '', '', 'char', -1, -1, 1, 0, 1, 0);
22 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'column_name', 'column name', '', '', '', 'char', -1, -1, 1, 0, 1, 0);
23 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'description', 'brief description of column', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
24 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'unit', 'unit in VO standard format', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
25 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'ucd', 'UCD of column if any', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
26 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'utype', 'UTYPE of column if any', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
27 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'datatype', 'ADQL datatype as in section 2.5', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
28 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', '"size"', 'length of variable length datatypes', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
29 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'arraysize', 'length of variable length datatypes', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
30 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'principal', 'a principal column; 1 means true, 0 means false', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
31 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'indexed', 'an indexed column; 1 means true, 0 means false', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
32 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'std', 'a standard column; 1 means true, 0 means false', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
33 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.keys', 'key_id', 'unique key identifier', '', '', '', 'char', -1, -1, 1, 0, 1, 0);
34 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.keys', 'from_table', 'fully qualified table name', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
35 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.keys', 'target_table', 'fully qualified table name', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
36 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.keys', 'description', 'description of this key', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
37 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.keys', 'utype', 'utype of this key', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
38 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.key_columns', 'key_id', 'unique key identifier', '', '', '', 'char', -1, -1, 1, 0, 1, 0);
39 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.key_columns', 'from_column', 'key column name in the from_table', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
40 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.key_columns', 'target_column', 'key column name in the target_table', '', '', '', 'char', -1, -1, 0, 0, 1, 0);
41 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.schemas', 'ts_schema_id', 'ts_schema_id', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
42 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'ts_column_id', 'ts_column_id', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
43 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.tables', 'ts_table_id', 'ts_table_id', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
44 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.keys', 'ts_key_id', 'ts_key_id', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
45 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.key_columns', 'ts_key_column_id', 'ts_key_column_id', '', '', '', 'int', -1, -1, 0, 0, 1, 0);
46 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.schemas', 'ft_schema_id', 'ft_schema_id', '', '', '', 'int', -1, -1, 0, 0, 0, 0);
47 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.tables', 'ft_table_id', 'ft_table_id', '', '', '', 'int', -1, -1, 0, 0, 0, 0);
48 INSERT INTO "TAP_SCHEMA"."columns" ("table_name", "column_name", "description", "unit", "ucd", "utype", "datatype", "size", "arraysize", "principal", "indexed", "std", "ft_column_id") VALUES ('TAP_SCHEMA.columns', 'ft_column_id', 'ft_column_id', '', '', '', 'int', -1, -1, 0, 0, 0, 0);