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