1 # copyright 2004-2013 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 yams.

     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.moves import range

    25 

    26 from yams.constraints import SizeConstraint, UniqueConstraint

    27 

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

    29 # SET_DEFAULT to True

    30 SET_DEFAULT = False

    31 

    32 

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

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

    35     corresponding to the given schema

    36     """

    37     output = []

    38     w = output.append

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

    40         eschema = schema.eschema(etype)

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

    42             continue

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

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

    45         rschema = schema.rschema(rtype)

    46         if rschema.final or rschema.inlined or rschema.rule:

    47             continue

    48         w(rschema2sql(rschema))

    49     return '\n'.join(output)

    50 

    51 

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

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

    54     corresponding to the given schema

    55     """

    56     output = []

    57     w = output.append

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

    59         eschema = schema.eschema(etype)

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

    61             continue

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

    63         for stmt in stmts:

    64             w(stmt)

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

    66         rschema = schema.rschema(rtype)

    67         if rschema.final or rschema.inlined:

    68             continue

    69         w(droprschema2sql(rschema))

    70     return '\n'.join(output)

    71 

    72 

    73 def eschema_attrs(eschema, skip_relations):

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

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

    76     attrs += [(rschema, None)

    77               for rschema in eschema.subject_relations()

    78               if not rschema.final and rschema.inlined]

    79     return attrs

    80 

    81 def unique_index_name(eschema, columns):

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

    83                               ',' +

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

    85 

    86 def iter_unique_index_names(eschema):

    87     for columns in eschema._unique_together or ():

    88         yield columns, unique_index_name(eschema, columns)

    89 

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

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

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

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

    94     # create multicol unique indices

    95     statements = []

    96     tablename = prefix + eschema.type

    97     if eschema._unique_together is not None:

    98         for columns, index_name in iter_unique_index_names(eschema):

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

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

   101             statements += sqls

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

   103     return statements

   104 

   105 

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

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

   108     output = []

   109     w = output.append

   110     table = prefix + eschema.type

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

   112     attrs = eschema_attrs(eschema, skip_relations)

   113     # XXX handle objectinline physical mode

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

   115         rschema, attrschema = attrs[i]

   116         if attrschema is not None:

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

   118                                   indent=' ')

   119         else: # inline relation

   120             # XXX integer is ginco specific

   121             sqltype = 'integer'

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

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

   124         else:

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

   126     w(');')

   127     # create indexes

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

   129         rschema, attrschema = attrs[i]

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

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

   132     for columns, index_name in iter_unique_index_names(eschema):

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

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

   135         for sql in sqls:

   136             w(sql)

   137     w('')

   138     return '\n'.join(output)

   139 

   140 

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

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

   143     attr = rschema.type

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

   145     sqltype = type_from_constraints(dbhelper, aschema.type, rdef.constraints,

   146                                     creating)

   147     if SET_DEFAULT:

   148         default = eschema.default(attr)

   149         if default is not None:

   150             if aschema.type == 'Boolean':

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

   152             elif aschema.type == 'String':

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

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

   155                 sqltype += ' DEFAULT %s' % default

   156             # XXX ignore default for other type

   157             # this is expected for NOW / TODAY

   158     if creating:

   159         if rdef.uid:

   160             sqltype += ' PRIMARY KEY'

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

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

   163             if dbhelper.alter_column_support:

   164                 sqltype += ' NOT NULL'

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

   166     # / null modifiers

   167     return sqltype

   168 

   169 

   170 def type_from_constraints(dbhelper, etype, constraints, creating=True):

   171     """return a sql type string corresponding to the constraints"""

   172     constraints = list(constraints)

   173     unique, sqltype = False, None

   174     size_constrained_string = dbhelper.TYPE_MAPPING.get('SizeConstrainedString', 'varchar(%s)')

   175     if etype == 'String':

   176         for constraint in constraints:

   177             if isinstance(constraint, SizeConstraint):

   178                 if constraint.max is not None:

   179                     sqltype = size_constrained_string % constraint.max

   180             elif isinstance(constraint, UniqueConstraint):

   181                 unique = True

   182     if sqltype is None:

   183         sqltype = dbhelper.TYPE_MAPPING[etype]

   184     if creating and unique:

   185         sqltype += ' UNIQUE'

   186     return sqltype

   187 

   188 

   189 _SQL_SCHEMA = """

   190 CREATE TABLE %(table)s (

   191   eid_from INTEGER NOT NULL,

   192   eid_to INTEGER NOT NULL,

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

   194 );

   195 

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

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

   198 

   199 

   200 def rschema2sql(rschema):

   201     assert not rschema.rule

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

   203     

   204 

   205 def droprschema2sql(rschema):

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

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

   208     # the table

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

   210 

   211 

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

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

   214     corresponding to the given schema

   215     """

   216     output = []

   217     w = output.append

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

   219         eschema = schema.eschema(etype)

   220         if eschema.final or etype in skip_entities:

   221             continue

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

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

   224         rschema = schema.rschema(rtype)

   225         if rschema.final or rschema.inlined:

   226             continue

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

   228     return '\n'.join(output)

   229 

   230 

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

   232     output = []

   233     w = output.append

   234     etype = eschema.type

   235     if set_owner:

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

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

   238     return '\n'.join(output)

   239 

   240 

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

   242     output = []

   243     if set_owner:

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

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

   246     return '\n'.join(output)