diff -r 058bb3dc685f -r 0b59724cb3f2 server/schema2sql.py --- a/server/schema2sql.py Mon Jan 04 18:40:30 2016 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,300 +0,0 @@ -# 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)