1 # copyright 2004-2015 LOGILAB S.A. (Paris, FRANCE), all rights reserved.

     2 # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr

     3 #

     4 # This file is part of cubicweb.

     5 #

     6 # yams is free software: you can redistribute it and/or modify it under the

     7 # terms of the GNU Lesser General Public License as published by the Free

     8 # Software Foundation, either version 2.1 of the License, or (at your option)

     9 # any later version.

    10 #

    11 # yams is distributed in the hope that it will be useful, but WITHOUT ANY

    12 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR

    13 # A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more

    14 # details.

    15 #

    16 # You should have received a copy of the GNU Lesser General Public License along

    17 # with yams. If not, see <http://www.gnu.org/licenses/>.

    18 """write a schema as sql"""

    19 

    20 __docformat__ = "restructuredtext en"

    21 

    22 from hashlib import md5

    23 

    24 from six import string_types

    25 from six.moves import range

    26 

    27 from yams.constraints import (SizeConstraint, UniqueConstraint, Attribute,

    28                               NOW, TODAY)

    29 

    30 # default are usually not handled at the sql level. If you want them, set

    31 # SET_DEFAULT to True

    32 SET_DEFAULT = False

    33 

    34 def rschema_has_table(rschema, skip_relations):

    35     """Return True if the given schema should have a table in the database"""

    36     return not (rschema.final or rschema.inlined or rschema.rule or rschema.type in skip_relations)

    37 

    38 

    39 def schema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''):

    40     """write to the output stream a SQL schema to store the objects

    41     corresponding to the given schema

    42     """

    43     output = []

    44     w = output.append

    45     for etype in sorted(schema.entities()):

    46         eschema = schema.eschema(etype)

    47         if eschema.final or eschema.type in skip_entities:

    48             continue

    49         w(eschema2sql(dbhelper, eschema, skip_relations, prefix=prefix))

    50     for rtype in sorted(schema.relations()):

    51         rschema = schema.rschema(rtype)

    52         if rschema_has_table(rschema, skip_relations):

    53             w(rschema2sql(rschema))

    54     return '\n'.join(output)

    55 

    56 

    57 def dropschema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''):

    58     """write to the output stream a SQL schema to store the objects

    59     corresponding to the given schema

    60     """

    61     output = []

    62     w = output.append

    63     for etype in sorted(schema.entities()):

    64         eschema = schema.eschema(etype)

    65         if eschema.final or eschema.type in skip_entities:

    66             continue

    67         stmts = dropeschema2sql(dbhelper, eschema, skip_relations, prefix=prefix)

    68         for stmt in stmts:

    69             w(stmt)

    70     for rtype in sorted(schema.relations()):

    71         rschema = schema.rschema(rtype)

    72         if rschema_has_table(rschema, skip_relations):

    73             w(droprschema2sql(rschema))

    74     return '\n'.join(output)

    75 

    76 

    77 def eschema_attrs(eschema, skip_relations):

    78     attrs = [attrdef for attrdef in eschema.attribute_definitions()

    79              if not attrdef[0].type in skip_relations]

    80     attrs += [(rschema, None)

    81               for rschema in eschema.subject_relations()

    82               if not rschema.final and rschema.inlined]

    83     return attrs

    84 

    85 def unique_index_name(eschema, columns):

    86     return u'unique_%s' % md5((eschema.type +

    87                               ',' +

    88                               ','.join(sorted(columns))).encode('ascii')).hexdigest()

    89 

    90 def iter_unique_index_names(eschema):

    91     for columns in eschema._unique_together or ():

    92         yield columns, unique_index_name(eschema, columns)

    93 

    94 def dropeschema2sql(dbhelper, eschema, skip_relations=(), prefix=''):

    95     """return sql to drop an entity type's table"""

    96     # not necessary to drop indexes, that's implictly done when

    97     # dropping the table, but we need to drop SQLServer views used to

    98     # create multicol unique indices

    99     statements = []

   100     tablename = prefix + eschema.type

   101     if eschema._unique_together is not None:

   102         for columns, index_name in iter_unique_index_names(eschema):

   103             cols  = ['%s%s' % (prefix, col) for col in columns]

   104             sqls = dbhelper.sqls_drop_multicol_unique_index(tablename, cols, index_name)

   105             statements += sqls

   106     statements += ['DROP TABLE %s;' % (tablename)]

   107     return statements

   108 

   109 

   110 def eschema2sql(dbhelper, eschema, skip_relations=(), prefix=''):

   111     """write an entity schema as SQL statements to stdout"""

   112     output = []

   113     w = output.append

   114     table = prefix + eschema.type

   115     w('CREATE TABLE %s(' % (table))

   116     attrs = eschema_attrs(eschema, skip_relations)

   117     # XXX handle objectinline physical mode

   118     for i in range(len(attrs)):

   119         rschema, attrschema = attrs[i]

   120         if attrschema is not None:

   121             sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema,

   122                                   indent=' ')

   123         else: # inline relation

   124             sqltype = 'integer REFERENCES entities (eid)'

   125         if i == len(attrs) - 1:

   126             w(' %s%s %s' % (prefix, rschema.type, sqltype))

   127         else:

   128             w(' %s%s %s,' % (prefix, rschema.type, sqltype))

   129     for rschema, aschema in attrs:

   130         if aschema is None:  # inline relation

   131             continue

   132         attr = rschema.type

   133         rdef = rschema.rdef(eschema.type, aschema.type)

   134         for constraint in rdef.constraints:

   135             cstrname, check = check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=prefix)

   136             if cstrname is not None:

   137                 w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check))

   138     w(');')

   139     # create indexes

   140     for i in range(len(attrs)):

   141         rschema, attrschema = attrs[i]

   142         if attrschema is None or eschema.rdef(rschema).indexed:

   143             w(dbhelper.sql_create_index(table, prefix + rschema.type))

   144     for columns, index_name in iter_unique_index_names(eschema):

   145         cols  = ['%s%s' % (prefix, col) for col in columns]

   146         sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name)

   147         for sql in sqls:

   148             w(sql)

   149     w('')

   150     return '\n'.join(output)

   151 

   152 def as_sql(value, dbhelper, prefix):

   153     if isinstance(value, Attribute):

   154         return prefix + value.attr

   155     elif isinstance(value, TODAY):

   156         return dbhelper.sql_current_date()

   157     elif isinstance(value, NOW):

   158         return dbhelper.sql_current_timestamp()

   159     else:

   160         # XXX more quoting for literals?

   161         return value

   162 

   163 def check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=''):

   164     # XXX should find a better name

   165     cstrname = 'cstr' + md5((eschema.type + attr + constraint.type() +

   166                              (constraint.serialize() or '')).encode('ascii')).hexdigest()

   167     if constraint.type() == 'BoundaryConstraint':

   168         value = as_sql(constraint.boundary, dbhelper, prefix)

   169         return cstrname, '%s%s %s %s' % (prefix, attr, constraint.operator, value)

   170     elif constraint.type() == 'IntervalBoundConstraint':

   171         condition = []

   172         if constraint.minvalue is not None:

   173             value = as_sql(constraint.minvalue, dbhelper, prefix)

   174             condition.append('%s%s >= %s' % (prefix, attr, value))

   175         if constraint.maxvalue is not None:

   176             value = as_sql(constraint.maxvalue, dbhelper, prefix)

   177             condition.append('%s%s <= %s' % (prefix, attr, value))

   178         return cstrname, ' AND '.join(condition)

   179     elif constraint.type() == 'StaticVocabularyConstraint':

   180         sample = next(iter(constraint.vocabulary()))

   181         if not isinstance(sample, string_types):

   182             values = ', '.join(str(word) for word in constraint.vocabulary())

   183         else:

   184             # XXX better quoting?

   185             values = ', '.join("'%s'" % word.replace("'", "''") for word in constraint.vocabulary())

   186         return cstrname, '%s%s IN (%s)' % (prefix, attr, values)

   187     return None, None

   188 

   189 def aschema2sql(dbhelper, eschema, rschema, aschema, creating=True, indent=''):

   190     """write an attribute schema as SQL statements to stdout"""

   191     attr = rschema.type

   192     rdef = rschema.rdef(eschema.type, aschema.type)

   193     sqltype = type_from_rdef(dbhelper, rdef, creating)

   194     if SET_DEFAULT:

   195         default = eschema.default(attr)

   196         if default is not None:

   197             if aschema.type == 'Boolean':

   198                 sqltype += ' DEFAULT %s' % dbhelper.boolean_value(default)

   199             elif aschema.type == 'String':

   200                 sqltype += ' DEFAULT %r' % str(default)

   201             elif aschema.type in ('Int', 'BigInt', 'Float'):

   202                 sqltype += ' DEFAULT %s' % default

   203             # XXX ignore default for other type

   204             # this is expected for NOW / TODAY

   205     if creating:

   206         if rdef.uid:

   207             sqltype += ' PRIMARY KEY REFERENCES entities (eid)'

   208         elif rdef.cardinality[0] == '1':

   209             # don't set NOT NULL if backend isn't able to change it later

   210             if dbhelper.alter_column_support:

   211                 sqltype += ' NOT NULL'

   212     # else we're getting sql type to alter a column, we don't want key / indexes

   213     # / null modifiers

   214     return sqltype

   215 

   216 

   217 def type_from_rdef(dbhelper, rdef, creating=True):

   218     """return a sql type string corresponding to the relation definition"""

   219     constraints = list(rdef.constraints)

   220     unique, sqltype = False, None

   221     if rdef.object.type == 'String':

   222         for constraint in constraints:

   223             if isinstance(constraint, SizeConstraint):

   224                 if constraint.max is not None:

   225                     size_constrained_string = dbhelper.TYPE_MAPPING.get(

   226                         'SizeConstrainedString', 'varchar(%s)')

   227                     sqltype = size_constrained_string % constraint.max

   228             elif isinstance(constraint, UniqueConstraint):

   229                 unique = True

   230     if sqltype is None:

   231         sqltype = sql_type(dbhelper, rdef)

   232     if creating and unique:

   233         sqltype += ' UNIQUE'

   234     return sqltype

   235 

   236 

   237 def sql_type(dbhelper, rdef):

   238     sqltype = dbhelper.TYPE_MAPPING[rdef.object]

   239     if callable(sqltype):

   240         sqltype = sqltype(rdef)

   241     return sqltype

   242 

   243 

   244 _SQL_SCHEMA = """

   245 CREATE TABLE %(table)s (

   246   eid_from INTEGER NOT NULL REFERENCES entities (eid),

   247   eid_to INTEGER NOT NULL REFERENCES entities (eid),

   248   CONSTRAINT %(table)s_p_key PRIMARY KEY(eid_from, eid_to)

   249 );

   250 

   251 CREATE INDEX %(table)s_from_idx ON %(table)s(eid_from);

   252 CREATE INDEX %(table)s_to_idx ON %(table)s(eid_to);"""

   253 

   254 

   255 def rschema2sql(rschema):

   256     assert not rschema.rule

   257     return _SQL_SCHEMA % {'table': '%s_relation' % rschema.type}

   258 

   259 

   260 def droprschema2sql(rschema):

   261     """return sql to drop a relation type's table"""

   262     # not necessary to drop indexes, that's implictly done when dropping

   263     # the table

   264     return 'DROP TABLE %s_relation;' % rschema.type

   265 

   266 

   267 def grant_schema(schema, user, set_owner=True, skip_entities=(), prefix=''):

   268     """write to the output stream a SQL schema to store the objects

   269     corresponding to the given schema

   270     """

   271     output = []

   272     w = output.append

   273     for etype in sorted(schema.entities()):

   274         eschema = schema.eschema(etype)

   275         if eschema.final or etype in skip_entities:

   276             continue

   277         w(grant_eschema(eschema, user, set_owner, prefix=prefix))

   278     for rtype in sorted(schema.relations()):

   279         rschema = schema.rschema(rtype)

   280         if rschema_has_table(rschema, skip_relations=()):  # XXX skip_relations should be specified

   281             w(grant_rschema(rschema, user, set_owner))

   282     return '\n'.join(output)

   283 

   284 

   285 def grant_eschema(eschema, user, set_owner=True, prefix=''):

   286     output = []

   287     w = output.append

   288     etype = eschema.type

   289     if set_owner:

   290         w('ALTER TABLE %s%s OWNER TO %s;' % (prefix, etype, user))

   291     w('GRANT ALL ON %s%s TO %s;' % (prefix, etype, user))

   292     return '\n'.join(output)

   293 

   294 

   295 def grant_rschema(rschema, user, set_owner=True):

   296     output = []

   297     if set_owner:

   298         output.append('ALTER TABLE %s_relation OWNER TO %s;' % (rschema.type, user))

   299     output.append('GRANT ALL ON %s_relation TO %s;' % (rschema.type, user))

   300     return '\n'.join(output)