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)