author | Denis Laxalde <denis.laxalde@logilab.fr> |
Mon, 19 Jun 2017 18:15:28 +0200 | |
changeset 12188 | fea018b2e056 |
parent 11811 | f09efeead7f9 |
child 12355 | c703dc95c82e |
permissions | -rw-r--r-- |
# copyright 2004-2016 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 <http://www.gnu.org/licenses/>. """write a schema as sql""" from hashlib import md5 from six import string_types, text_type from six.moves import range from yams.constraints import (SizeConstraint, UniqueConstraint, Attribute, NOW, TODAY) from logilab import database from logilab.common.decorators import monkeypatch # default are usually not handled at the sql level. If you want them, set # SET_DEFAULT to True SET_DEFAULT = False # backport fix for lgdb #6662663 @monkeypatch(database._GenericAdvFuncHelper) def sql_create_index(self, table, column, unique=False): idx = self._index_name(table, column, unique) if unique: return 'ALTER TABLE %s ADD CONSTRAINT %s UNIQUE(%s)' % (table, idx, column) else: return 'CREATE INDEX %s ON %s(%s)' % (idx, table, column) @monkeypatch(database._GenericAdvFuncHelper) def _index_name(self, table, column, unique=False): if unique: return build_index_name(table, [column], prefix='key_') else: return build_index_name(table, [column], prefix='idx_') def build_index_name(table, columns, prefix='idx_'): """Return a predictable-but-size-constrained name for an index on `table(*columns)`, using an md5 hash. """ return '%s%s' % (prefix, md5((table + ',' + ','.join(sorted(columns))).encode('ascii')).hexdigest()) 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=''): """Yield SQL statements to create a database schema for the given Yams schema. `prefix` may be a string that will be prepended to all table / column names (usually, 'cw_'). """ for etype in sorted(schema.entities()): eschema = schema.eschema(etype) if eschema.final or eschema.type in skip_entities: continue for sql in eschema2sql(dbhelper, eschema, skip_relations, prefix): yield sql for rtype in sorted(schema.relations()): rschema = schema.rschema(rtype) if rschema_has_table(rschema, skip_relations): for sql in rschema2sql(rschema): yield sql def unique_index_name(eschema, attrs): """Return a predictable-but-size-constrained name for a multi-columns unique index on given attributes of the entity schema (actually, the later may be a schema or a string). """ # keep giving eschema instead of table name for bw compat table = text_type(eschema) # unique_index_name is used as name of CWUniqueConstraint, hence it should be unicode return text_type(build_index_name(table, attrs, 'unique_')) def iter_unique_index_names(eschema): """Yield (attrs, index name) where attrs is a list of entity type's attribute names that should be unique together, and index name the unique index name. """ for attrs in eschema._unique_together or (): yield attrs, unique_index_name(eschema, attrs) def eschema_sql_def(dbhelper, eschema, skip_relations=(), prefix=''): """Return a list of (column names, sql type def) for the given entity schema. No constraint nor index are considered - this function is usually for massive import purpose. """ 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] result = [] for i in range(len(attrs)): rschema, attrschema = attrs[i] if attrschema is not None: # creating = False will avoid NOT NULL / REFERENCES constraints sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema, creating=False) else: # inline relation sqltype = 'integer' result.append(('%s%s' % (prefix, rschema.type), sqltype)) return result def eschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): """Yield SQL statements to initialize database from an entity schema.""" table = prefix + eschema.type output = [] w = output.append w('CREATE TABLE %s(' % (table)) 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] for i in range(len(attrs)): rschema, attrschema = attrs[i] if attrschema is not None: sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema) 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 rdef = rschema.rdef(eschema.type, aschema.type) for constraint in rdef.constraints: cstrname, check = check_constraint(rdef, constraint, dbhelper, prefix=prefix) if cstrname is not None: w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check)) w(')') yield '\n'.join(output) # create indexes for i in range(len(attrs)): rschema, attrschema = attrs[i] if attrschema is None or eschema.rdef(rschema).indexed: yield dbhelper.sql_create_index(table, prefix + rschema.type) if attrschema and any(isinstance(cstr, UniqueConstraint) for cstr in eschema.rdef(rschema).constraints): yield dbhelper.sql_create_index(table, prefix + rschema.type, unique=True) for attrs, index_name in iter_unique_index_names(eschema): columns = ['%s%s' % (prefix, attr) for attr in attrs] sqls = dbhelper.sqls_create_multicol_unique_index(table, columns, index_name) for sql in sqls: yield sql.rstrip(';') # remove trailing ';' for consistency def constraint_value_as_sql(value, dbhelper, prefix): """Return the SQL value from a Yams constraint's value, handling special cases where it's a `Attribute`, `TODAY` or `NOW` instance instead of a literal value. """ 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(rdef, constraint, dbhelper, prefix=''): """Return (constraint name, constraint SQL definition) for the given relation definition's constraint. Maybe (None, None) if the constraint is not handled in the backend. """ attr = rdef.rtype.type cstrname = constraint.name_for(rdef) if constraint.type() == 'BoundaryConstraint': value = constraint_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 = constraint_value_as_sql(constraint.minvalue, dbhelper, prefix) condition.append('%s%s >= %s' % (prefix, attr, value)) if constraint.maxvalue is not None: value = constraint_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): """Return string containing a SQL table's column definition from attribute schema.""" attr = rschema.type rdef = rschema.rdef(eschema.type, aschema.type) sqltype = type_from_rdef(dbhelper, rdef) 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): """Return a string containing SQL type name for the given relation definition.""" constraints = list(rdef.constraints) sqltype = None if rdef.object.type == 'String': for constraint in constraints: if isinstance(constraint, SizeConstraint) and constraint.max is not None: size_constrained_string = dbhelper.TYPE_MAPPING.get( 'SizeConstrainedString', 'varchar(%s)') sqltype = size_constrained_string % constraint.max break if sqltype is None: sqltype = sql_type(dbhelper, rdef) return sqltype def sql_type(dbhelper, rdef): """Return a string containing SQL type to use to store values of the given relation definition. """ 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 %(pkey_idx)s PRIMARY KEY(eid_from, eid_to) ); CREATE INDEX %(from_idx)s ON %(table)s(eid_from); CREATE INDEX %(to_idx)s ON %(table)s(eid_to)""" def rschema2sql(rschema): """Yield SQL statements to create database table and indexes for a Yams relation schema.""" assert not rschema.rule table = '%s_relation' % rschema.type sqls = _SQL_SCHEMA % {'table': table, 'pkey_idx': build_index_name(table, ['eid_from', 'eid_to'], 'key_'), 'from_idx': build_index_name(table, ['eid_from'], 'idx_'), 'to_idx': build_index_name(table, ['eid_to'], 'idx_')} for sql in sqls.split(';'): yield sql.strip() def grant_schema(schema, user, set_owner=True, skip_entities=(), prefix=''): """Yield SQL statements to give all access (and ownership if `set_owner` is True) on the database tables for the given Yams schema to `user`. `prefix` may be a string that will be prepended to all table / column names (usually, 'cw_'). """ for etype in sorted(schema.entities()): eschema = schema.eschema(etype) if eschema.final or etype in skip_entities: continue for sql in grant_eschema(eschema, user, set_owner, prefix=prefix): yield sql for rtype in sorted(schema.relations()): rschema = schema.rschema(rtype) if rschema_has_table(rschema, skip_relations=()): # XXX skip_relations should be specified for sql in grant_rschema(rschema, user, set_owner): yield sql def grant_eschema(eschema, user, set_owner=True, prefix=''): """Yield SQL statements to give all access (and ownership if `set_owner` is True) on the database tables for the given Yams entity schema to `user`. """ etype = eschema.type if set_owner: yield 'ALTER TABLE %s%s OWNER TO %s' % (prefix, etype, user) yield 'GRANT ALL ON %s%s TO %s' % (prefix, etype, user) def grant_rschema(rschema, user, set_owner=True): """Yield SQL statements to give all access (and ownership if `set_owner` is True) on the database tables for the given Yams relation schema to `user`. """ if set_owner: yield 'ALTER TABLE %s_relation OWNER TO %s' % (rschema.type, user) yield 'GRANT ALL ON %s_relation TO %s' % (rschema.type, user)