1/* Part of SWI-Prolog 2 3 Author: Mike Elston 4 Matt Lilley 5 E-mail: matt.s.lilley@gmail.com 6 WWW: http://www.swi-prolog.org 7 Copyright (c) 2014, Mike Elston, Matt Lilley 8 All rights reserved. 9 10 Redistribution and use in source and binary forms, with or without 11 modification, are permitted provided that the following conditions 12 are met: 13 14 1. Redistributions of source code must retain the above copyright 15 notice, this list of conditions and the following disclaimer. 16 17 2. Redistributions in binary form must reproduce the above copyright 18 notice, this list of conditions and the following disclaimer in 19 the documentation and/or other materials provided with the 20 distribution. 21 22 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 23 "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 24 LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 25 FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE 26 COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 27 INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, 28 BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 29 LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER 30 CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 31 LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN 32 ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 33 POSSIBILITY OF SUCH DAMAGE. 34*/ 35 36/* PostgreSQL is a trademark of the PostgreSQL Global Development Group. 37 Microsoft, SQL Server, and Windows are either registered trademarks or 38 trademarks of Microsoft Corporation in the United States and/or other 39 countries. SQLite is a registered trademark of Hipp, Wyrick & Company, 40 Inc in the United States. All other trademarks or registered trademarks 41 are the property of their respective owners. 42*/ 43 44:-module(cql_autoschema, 45 []). 46 47% Database metadata API --------------------------------------------------------------------------------------------------------------- 48/* 49%% dbms(+Schema, -DBMSName). 50% Determine the DBMS for a given Schema. 51% Can be autoconfigured. 52:-discontiguous(dbms/2). 53 54%% odbc_data_type(+Schema, +TableSpec, +ColumnName, ?OdbcDataType) 55% OdbcDataType must be a native SQL datatype, such as varchar(30) or decimal(10, 5) 56% Can be autoconfigured. 57:-discontiguous(odbc_data_type/4). 58 59%% primary_key_column_name(+Schema, +TableName, -PrimaryKeyAttributeName). 60% Can be autoconfigured. 61:-discontiguous(primary_key_column_name/3). 62 63%% database_attribute(?EntityType:table/view, ?Schema:atom, ?EntityName:atom, ?ColumnName:atom, ?DomainOrNativeType:atom, ?AllowsNulls:allows_nulls(true/false), ?IsIdentity:is_identity(true/false), ?ColumnDefault) is nondet. 64% Can be autoconfigured. 65:-discontiguous(database_attribute/8). 66 67%% database_attribute(?DomainName:atom, ?OdbcDataType) is nondet. 68% Can be autoconfigured. 69:-discontiguous(database_domain/2). 70 71%% routine_return_type(?Schema:atom, ?EntityName:atom, ?OdbcType). 72% Can be autoconfigured 73:-discontiguous(routine_return_type/3). 74 75%% database_constraint(?Schema:atom, ?EntityName:atom, ?ConstraintName:atom, ?Constraint) is nondet. 76% Constraint is one of: 77% * primary_key(ColumnNames:list) 78% * foreign_key(ForeignTableName:atom, ForeignColumnNames:list, ColumnNames:list) 79% * unique(ColumnNames:list) 80% * check(CheckClause) 81% In theory this can be autoconfigured too, but I have not written the code for it yet 82:-discontiguous(database_constraint/4). 83*/ 84 85:-use_module(library(cql/cql_database)). 86:-use_module(library(dcg/basics)). 87 88% Automatic schema generation --------------------------------------------------------------------------------------------------------------- 89% Works with PostgreSQL and SQLite. Could be extended to work with another DBMS easily enough 90 91schema_fact(Schema, DBMS, dbms(Schema, DBMS)). 92 93schema_fact(Schema, 'PostgreSQL', Fact):- 94 odbc_connection_call(Schema, Connection, odbc_query(Connection, 'SELECT table_name, column_name, data_type, character_maximum_length, numeric_precision, numeric_scale, domain_name, is_nullable, column_default FROM INFORMATION_SCHEMA.columns WHERE table_schema = \'public\'', row(EntityName, ColumnName, NativeType, CharacterMaximumLength, NumericPrecision, NumericScale, DomainType, IsNullable, ColumnDefault))), 95 ( IsNullable == '1' -> AllowsNulls = allows_nulls(true) ; AllowsNulls = allows_nulls(false) ), 96 map_native_type(NativeType, CharacterMaximumLength, NumericPrecision, NumericScale, Mapped), 97 ( DomainType \== {null} -> DomainOrNativeType = domain(DomainType) ; DomainOrNativeType = native_type(Mapped) ), 98 IsIdentity = is_identity(false), 99 ( Fact = database_attribute(table, Schema, EntityName, ColumnName, DomainOrNativeType, AllowsNulls, IsIdentity, ColumnDefault) 100 ; Fact = odbc_data_type(Schema, EntityName, ColumnName, Mapped) 101 ). 102 103schema_fact(Schema, 'PostgreSQL', primary_key_column_name(Schema, EntityName, ColumnName)):- 104 odbc_connection_call(Schema, Connection, odbc_query(Connection, 'SELECT kcu.table_name, column_name FROM INFORMATION_SCHEMA.key_column_usage KCU INNER JOIN INFORMATION_SCHEMA.table_constraints tc ON(tc.table_name = kcu.table_name AND tc.constraint_type = \'PRIMARY KEY\') WHERE kcu.table_schema = \'public\'', row(EntityName, ColumnName))). 105 106schema_fact(Schema, 'PostgreSQL', database_domain(DomainName, Mapped)):- 107 odbc_connection_call(Schema, Connection, odbc_query(Connection, 'SELECT domain_name, data_type, character_maximum_length, numeric_precision, numeric_scale FROM INFORMATION_SCHEMA.domains WHERE domain_schema = \'public\'', row(DomainName, NativeType, CharacterMaximumLength, NumericPrecision, NumericScale))), 108 map_native_type(NativeType, CharacterMaximumLength, NumericPrecision, NumericScale, Mapped). 109 110schema_fact(Schema, 'PostgreSQL', routine_return_type(Schema, RoutineName, Mapped)):- 111 odbc_connection_call(Schema, Connection, odbc_query(Connection, 'SELECT data_type, character_maximum_length, numeric_precision, numeric_scale FROM INFORMATION_SCHEMA.routines WHERE routine_schema = \'public\'', row(RoutineName, NativeType, CharacterMaximumLength, NumericPrecision, NumericScale))), 112 map_native_type(NativeType, CharacterMaximumLength, NumericPrecision, NumericScale, Mapped). 113 114schema_fact(Schema, 'SQLite', Fact):- 115 findall(EntityName, 116 odbc_connection_call(Schema, Connection, odbc_query(Connection, 'SELECT name FROM sqlite_master WHERE type = \'table\'', row(EntityName))), 117 EntityNames), 118 member(EntityName, EntityNames), 119 format(atom(Pragma), 'PRAGMA table_info(~w)', [EntityName]), 120 odbc_connection_call(Schema, Connection, odbc_query(Connection, Pragma, Row)), 121 Row = row(_CID, ColumnName, ColumnTypeAtom, NotNullable, ColumnDefault, IsPkMember), 122 ( NotNullable == 0 -> 123 AllowsNulls = allows_nulls(true) 124 ; otherwise-> 125 AllowsNulls = allows_nulls(false) 126 ), 127 ColumnTypeAtom \== '', % sqlite_master contains sequence tables too, sadly. This excludes them 128 ( sqlite_type(ColumnTypeAtom, ColumnType)-> 129 true 130 ; otherwise-> 131 writeln(bad_type(EntityName, ColumnName, ColumnTypeAtom)) 132 ), 133 IsIdentity = is_identity(false), % FIXME 134 % SQLite does not support domains. Everything is a native_type 135 ( ColumnType == datetime -> 136 % FIXME: What is going on here? 137 OdbcDataType = timestamp 138 ; otherwise-> 139 OdbcDataType = ColumnType 140 ), 141 ( Fact = database_attribute(table, Schema, EntityName, ColumnName, native_type(ColumnType), AllowsNulls, IsIdentity, ColumnDefault) 142 ; Fact = odbc_data_type(Schema, EntityName, ColumnName, OdbcDataType) 143 ; IsPkMember == 1, Fact = primary_key_column_name(Schema, EntityName, ColumnName) 144 ). 145 146 147map_native_type('character varying', N, _, _, varchar(N)):- !. 148map_native_type('decimal', _, P, S, decimal(P, S)):- !. 149map_native_type(X, _, _, _, X). 150 151sqlite_type(Atom, Type):- 152 atom_codes(Atom, Codes), 153 sqlite_type_1(Type, Codes, []). 154sqlite_type_1(decimal(P, S))--> 155 ( "DECIMAL" ; "decimal" ; "Decimal"), 156 !, 157 whitespace, "(", whitespace, number(P), whitespace, ",", whitespace, number(S), whitespace, ")". 158 159sqlite_type_1(varchar(N))--> 160 ( "VARCHAR" ; "varchar" ; "Varchar"), 161 !, 162 whitespace, "(", whitespace, number(N), whitespace, ")". 163 164sqlite_type_1(varbinary(max))--> 165 ( "VARBINARY" ; "varbinary" ; "Varbinary"), !. 166 167sqlite_type_1(longvarchar)--> 168 ( "LONGVARCHAR" ; "longvarchar" ; "Longvarchar"), !. 169 170sqlite_type_1(integer)--> 171 ( "INTEGER" ; "integer" ; "Integer"), !. 172 173sqlite_type_1(datetime)--> 174 ( "DATETIME" ; "datetime" ; "Datetime"), !. 175 176sqlite_type_1(bigint)--> 177 ( "BIGINT" ; "bigint" ; "Bigint"), !. 178 179sqlite_type_1(smallint)--> 180 ( "SMALLINT" ; "smallint" ; "Smallint"), !. 181 182 183whitespace--> [C], {code_type(C, white)}, !, whitespace. 184whitespace--> []. 185 186 187user:term_expansion(:-build_schema(Schema), Facts):- 188 odbc_connection_call(Schema, Connection, odbc_get_connection(Connection, dbms_name(DBMS))), 189 setof(cql:Fact, 190 schema_fact(Schema, DBMS, Fact), 191 Facts). 192