diff -r 058bb3dc685f -r 0b59724cb3f2 cubicweb/server/schema2sql.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/cubicweb/server/schema2sql.py Sat Jan 16 13:48:51 2016 +0100 @@ -0,0 +1,300 @@ +# copyright 2004-2015 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr +# +# This file is part of cubicweb. +# +# yams is free software: you can redistribute it and/or modify it under the +# terms of the GNU Lesser General Public License as published by the Free +# Software Foundation, either version 2.1 of the License, or (at your option) +# any later version. +# +# yams is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more +# details. +# +# You should have received a copy of the GNU Lesser General Public License along +# with yams. If not, see . +"""write a schema as sql""" + +__docformat__ = "restructuredtext en" + +from hashlib import md5 + +from six import string_types +from six.moves import range + +from yams.constraints import (SizeConstraint, UniqueConstraint, Attribute, + NOW, TODAY) + +# default are usually not handled at the sql level. If you want them, set +# SET_DEFAULT to True +SET_DEFAULT = False + +def rschema_has_table(rschema, skip_relations): + """Return True if the given schema should have a table in the database""" + return not (rschema.final or rschema.inlined or rschema.rule or rschema.type in skip_relations) + + +def schema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''): + """write to the output stream a SQL schema to store the objects + corresponding to the given schema + """ + output = [] + w = output.append + for etype in sorted(schema.entities()): + eschema = schema.eschema(etype) + if eschema.final or eschema.type in skip_entities: + continue + w(eschema2sql(dbhelper, eschema, skip_relations, prefix=prefix)) + for rtype in sorted(schema.relations()): + rschema = schema.rschema(rtype) + if rschema_has_table(rschema, skip_relations): + w(rschema2sql(rschema)) + return '\n'.join(output) + + +def dropschema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''): + """write to the output stream a SQL schema to store the objects + corresponding to the given schema + """ + output = [] + w = output.append + for etype in sorted(schema.entities()): + eschema = schema.eschema(etype) + if eschema.final or eschema.type in skip_entities: + continue + stmts = dropeschema2sql(dbhelper, eschema, skip_relations, prefix=prefix) + for stmt in stmts: + w(stmt) + for rtype in sorted(schema.relations()): + rschema = schema.rschema(rtype) + if rschema_has_table(rschema, skip_relations): + w(droprschema2sql(rschema)) + return '\n'.join(output) + + +def eschema_attrs(eschema, skip_relations): + attrs = [attrdef for attrdef in eschema.attribute_definitions() + if not attrdef[0].type in skip_relations] + attrs += [(rschema, None) + for rschema in eschema.subject_relations() + if not rschema.final and rschema.inlined] + return attrs + +def unique_index_name(eschema, columns): + return u'unique_%s' % md5((eschema.type + + ',' + + ','.join(sorted(columns))).encode('ascii')).hexdigest() + +def iter_unique_index_names(eschema): + for columns in eschema._unique_together or (): + yield columns, unique_index_name(eschema, columns) + +def dropeschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): + """return sql to drop an entity type's table""" + # not necessary to drop indexes, that's implictly done when + # dropping the table, but we need to drop SQLServer views used to + # create multicol unique indices + statements = [] + tablename = prefix + eschema.type + if eschema._unique_together is not None: + for columns, index_name in iter_unique_index_names(eschema): + cols = ['%s%s' % (prefix, col) for col in columns] + sqls = dbhelper.sqls_drop_multicol_unique_index(tablename, cols, index_name) + statements += sqls + statements += ['DROP TABLE %s;' % (tablename)] + return statements + + +def eschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): + """write an entity schema as SQL statements to stdout""" + output = [] + w = output.append + table = prefix + eschema.type + w('CREATE TABLE %s(' % (table)) + attrs = eschema_attrs(eschema, skip_relations) + # XXX handle objectinline physical mode + for i in range(len(attrs)): + rschema, attrschema = attrs[i] + if attrschema is not None: + sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema, + indent=' ') + else: # inline relation + sqltype = 'integer REFERENCES entities (eid)' + if i == len(attrs) - 1: + w(' %s%s %s' % (prefix, rschema.type, sqltype)) + else: + w(' %s%s %s,' % (prefix, rschema.type, sqltype)) + for rschema, aschema in attrs: + if aschema is None: # inline relation + continue + attr = rschema.type + rdef = rschema.rdef(eschema.type, aschema.type) + for constraint in rdef.constraints: + cstrname, check = check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=prefix) + if cstrname is not None: + w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check)) + w(');') + # create indexes + for i in range(len(attrs)): + rschema, attrschema = attrs[i] + if attrschema is None or eschema.rdef(rschema).indexed: + w(dbhelper.sql_create_index(table, prefix + rschema.type)) + for columns, index_name in iter_unique_index_names(eschema): + cols = ['%s%s' % (prefix, col) for col in columns] + sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name) + for sql in sqls: + w(sql) + w('') + return '\n'.join(output) + +def as_sql(value, dbhelper, prefix): + if isinstance(value, Attribute): + return prefix + value.attr + elif isinstance(value, TODAY): + return dbhelper.sql_current_date() + elif isinstance(value, NOW): + return dbhelper.sql_current_timestamp() + else: + # XXX more quoting for literals? + return value + +def check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=''): + # XXX should find a better name + cstrname = 'cstr' + md5((eschema.type + attr + constraint.type() + + (constraint.serialize() or '')).encode('ascii')).hexdigest() + if constraint.type() == 'BoundaryConstraint': + value = as_sql(constraint.boundary, dbhelper, prefix) + return cstrname, '%s%s %s %s' % (prefix, attr, constraint.operator, value) + elif constraint.type() == 'IntervalBoundConstraint': + condition = [] + if constraint.minvalue is not None: + value = as_sql(constraint.minvalue, dbhelper, prefix) + condition.append('%s%s >= %s' % (prefix, attr, value)) + if constraint.maxvalue is not None: + value = as_sql(constraint.maxvalue, dbhelper, prefix) + condition.append('%s%s <= %s' % (prefix, attr, value)) + return cstrname, ' AND '.join(condition) + elif constraint.type() == 'StaticVocabularyConstraint': + sample = next(iter(constraint.vocabulary())) + if not isinstance(sample, string_types): + values = ', '.join(str(word) for word in constraint.vocabulary()) + else: + # XXX better quoting? + values = ', '.join("'%s'" % word.replace("'", "''") for word in constraint.vocabulary()) + return cstrname, '%s%s IN (%s)' % (prefix, attr, values) + return None, None + +def aschema2sql(dbhelper, eschema, rschema, aschema, creating=True, indent=''): + """write an attribute schema as SQL statements to stdout""" + attr = rschema.type + rdef = rschema.rdef(eschema.type, aschema.type) + sqltype = type_from_rdef(dbhelper, rdef, creating) + if SET_DEFAULT: + default = eschema.default(attr) + if default is not None: + if aschema.type == 'Boolean': + sqltype += ' DEFAULT %s' % dbhelper.boolean_value(default) + elif aschema.type == 'String': + sqltype += ' DEFAULT %r' % str(default) + elif aschema.type in ('Int', 'BigInt', 'Float'): + sqltype += ' DEFAULT %s' % default + # XXX ignore default for other type + # this is expected for NOW / TODAY + if creating: + if rdef.uid: + sqltype += ' PRIMARY KEY REFERENCES entities (eid)' + elif rdef.cardinality[0] == '1': + # don't set NOT NULL if backend isn't able to change it later + if dbhelper.alter_column_support: + sqltype += ' NOT NULL' + # else we're getting sql type to alter a column, we don't want key / indexes + # / null modifiers + return sqltype + + +def type_from_rdef(dbhelper, rdef, creating=True): + """return a sql type string corresponding to the relation definition""" + constraints = list(rdef.constraints) + unique, sqltype = False, None + if rdef.object.type == 'String': + for constraint in constraints: + if isinstance(constraint, SizeConstraint): + if constraint.max is not None: + size_constrained_string = dbhelper.TYPE_MAPPING.get( + 'SizeConstrainedString', 'varchar(%s)') + sqltype = size_constrained_string % constraint.max + elif isinstance(constraint, UniqueConstraint): + unique = True + if sqltype is None: + sqltype = sql_type(dbhelper, rdef) + if creating and unique: + sqltype += ' UNIQUE' + return sqltype + + +def sql_type(dbhelper, rdef): + sqltype = dbhelper.TYPE_MAPPING[rdef.object] + if callable(sqltype): + sqltype = sqltype(rdef) + return sqltype + + +_SQL_SCHEMA = """ +CREATE TABLE %(table)s ( + eid_from INTEGER NOT NULL REFERENCES entities (eid), + eid_to INTEGER NOT NULL REFERENCES entities (eid), + CONSTRAINT %(table)s_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX %(table)s_from_idx ON %(table)s(eid_from); +CREATE INDEX %(table)s_to_idx ON %(table)s(eid_to);""" + + +def rschema2sql(rschema): + assert not rschema.rule + return _SQL_SCHEMA % {'table': '%s_relation' % rschema.type} + + +def droprschema2sql(rschema): + """return sql to drop a relation type's table""" + # not necessary to drop indexes, that's implictly done when dropping + # the table + return 'DROP TABLE %s_relation;' % rschema.type + + +def grant_schema(schema, user, set_owner=True, skip_entities=(), prefix=''): + """write to the output stream a SQL schema to store the objects + corresponding to the given schema + """ + output = [] + w = output.append + for etype in sorted(schema.entities()): + eschema = schema.eschema(etype) + if eschema.final or etype in skip_entities: + continue + w(grant_eschema(eschema, user, set_owner, prefix=prefix)) + for rtype in sorted(schema.relations()): + rschema = schema.rschema(rtype) + if rschema_has_table(rschema, skip_relations=()): # XXX skip_relations should be specified + w(grant_rschema(rschema, user, set_owner)) + return '\n'.join(output) + + +def grant_eschema(eschema, user, set_owner=True, prefix=''): + output = [] + w = output.append + etype = eschema.type + if set_owner: + w('ALTER TABLE %s%s OWNER TO %s;' % (prefix, etype, user)) + w('GRANT ALL ON %s%s TO %s;' % (prefix, etype, user)) + return '\n'.join(output) + + +def grant_rschema(rschema, user, set_owner=True): + output = [] + if set_owner: + output.append('ALTER TABLE %s_relation OWNER TO %s;' % (rschema.type, user)) + output.append('GRANT ALL ON %s_relation TO %s;' % (rschema.type, user)) + return '\n'.join(output)