1 # copyright 2004-2015 LOGILAB S.A. (Paris, FRANCE), all rights reserved. |
1 # copyright 2004-2016 LOGILAB S.A. (Paris, FRANCE), all rights reserved. |
2 # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr |
2 # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr |
3 # |
3 # |
4 # This file is part of cubicweb. |
4 # This file is part of cubicweb. |
5 # |
5 # |
6 # yams is free software: you can redistribute it and/or modify it under the |
6 # yams is free software: you can redistribute it and/or modify it under the |
29 |
29 |
30 # default are usually not handled at the sql level. If you want them, set |
30 # default are usually not handled at the sql level. If you want them, set |
31 # SET_DEFAULT to True |
31 # SET_DEFAULT to True |
32 SET_DEFAULT = False |
32 SET_DEFAULT = False |
33 |
33 |
|
34 |
34 def rschema_has_table(rschema, skip_relations): |
35 def rschema_has_table(rschema, skip_relations): |
35 """Return True if the given schema should have a table in the database""" |
36 """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 return not (rschema.final or rschema.inlined or rschema.rule or rschema.type in skip_relations) |
37 |
38 |
38 |
39 |
80 attrs += [(rschema, None) |
81 attrs += [(rschema, None) |
81 for rschema in eschema.subject_relations() |
82 for rschema in eschema.subject_relations() |
82 if not rschema.final and rschema.inlined] |
83 if not rschema.final and rschema.inlined] |
83 return attrs |
84 return attrs |
84 |
85 |
|
86 |
85 def unique_index_name(eschema, columns): |
87 def unique_index_name(eschema, columns): |
86 return u'unique_%s' % md5((eschema.type + |
88 return u'unique_%s' % md5((eschema.type + |
87 ',' + |
89 ',' + |
88 ','.join(sorted(columns))).encode('ascii')).hexdigest() |
90 ','.join(sorted(columns))).encode('ascii')).hexdigest() |
|
91 |
89 |
92 |
90 def iter_unique_index_names(eschema): |
93 def iter_unique_index_names(eschema): |
91 for columns in eschema._unique_together or (): |
94 for columns in eschema._unique_together or (): |
92 yield columns, unique_index_name(eschema, columns) |
95 yield columns, unique_index_name(eschema, columns) |
|
96 |
93 |
97 |
94 def dropeschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): |
98 def dropeschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): |
95 """return sql to drop an entity type's table""" |
99 """return sql to drop an entity type's table""" |
96 # not necessary to drop indexes, that's implictly done when |
100 # not necessary to drop indexes, that's implictly done when |
97 # dropping the table, but we need to drop SQLServer views used to |
101 # dropping the table, but we need to drop SQLServer views used to |
98 # create multicol unique indices |
102 # create multicol unique indices |
99 statements = [] |
103 statements = [] |
100 tablename = prefix + eschema.type |
104 tablename = prefix + eschema.type |
101 if eschema._unique_together is not None: |
105 if eschema._unique_together is not None: |
102 for columns, index_name in iter_unique_index_names(eschema): |
106 for columns, index_name in iter_unique_index_names(eschema): |
103 cols = ['%s%s' % (prefix, col) for col in columns] |
107 cols = ['%s%s' % (prefix, col) for col in columns] |
104 sqls = dbhelper.sqls_drop_multicol_unique_index(tablename, cols, index_name) |
108 sqls = dbhelper.sqls_drop_multicol_unique_index(tablename, cols, index_name) |
105 statements += sqls |
109 statements += sqls |
106 statements += ['DROP TABLE %s;' % (tablename)] |
110 statements += ['DROP TABLE %s;' % (tablename)] |
107 return statements |
111 return statements |
108 |
112 |
118 for i in range(len(attrs)): |
122 for i in range(len(attrs)): |
119 rschema, attrschema = attrs[i] |
123 rschema, attrschema = attrs[i] |
120 if attrschema is not None: |
124 if attrschema is not None: |
121 sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema, |
125 sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema, |
122 indent=' ') |
126 indent=' ') |
123 else: # inline relation |
127 else: # inline relation |
124 sqltype = 'integer REFERENCES entities (eid)' |
128 sqltype = 'integer REFERENCES entities (eid)' |
125 if i == len(attrs) - 1: |
129 if i == len(attrs) - 1: |
126 w(' %s%s %s' % (prefix, rschema.type, sqltype)) |
130 w(' %s%s %s' % (prefix, rschema.type, sqltype)) |
127 else: |
131 else: |
128 w(' %s%s %s,' % (prefix, rschema.type, sqltype)) |
132 w(' %s%s %s,' % (prefix, rschema.type, sqltype)) |
130 if aschema is None: # inline relation |
134 if aschema is None: # inline relation |
131 continue |
135 continue |
132 attr = rschema.type |
136 attr = rschema.type |
133 rdef = rschema.rdef(eschema.type, aschema.type) |
137 rdef = rschema.rdef(eschema.type, aschema.type) |
134 for constraint in rdef.constraints: |
138 for constraint in rdef.constraints: |
135 cstrname, check = check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=prefix) |
139 cstrname, check = check_constraint(eschema, aschema, attr, constraint, dbhelper, |
|
140 prefix=prefix) |
136 if cstrname is not None: |
141 if cstrname is not None: |
137 w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check)) |
142 w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check)) |
138 w(');') |
143 w(');') |
139 # create indexes |
144 # create indexes |
140 for i in range(len(attrs)): |
145 for i in range(len(attrs)): |
141 rschema, attrschema = attrs[i] |
146 rschema, attrschema = attrs[i] |
142 if attrschema is None or eschema.rdef(rschema).indexed: |
147 if attrschema is None or eschema.rdef(rschema).indexed: |
143 w(dbhelper.sql_create_index(table, prefix + rschema.type)) |
148 w(dbhelper.sql_create_index(table, prefix + rschema.type)) |
144 for columns, index_name in iter_unique_index_names(eschema): |
149 for columns, index_name in iter_unique_index_names(eschema): |
145 cols = ['%s%s' % (prefix, col) for col in columns] |
150 cols = ['%s%s' % (prefix, col) for col in columns] |
146 sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name) |
151 sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name) |
147 for sql in sqls: |
152 for sql in sqls: |
148 w(sql) |
153 w(sql) |
149 w('') |
154 w('') |
150 return '\n'.join(output) |
155 return '\n'.join(output) |
|
156 |
151 |
157 |
152 def as_sql(value, dbhelper, prefix): |
158 def as_sql(value, dbhelper, prefix): |
153 if isinstance(value, Attribute): |
159 if isinstance(value, Attribute): |
154 return prefix + value.attr |
160 return prefix + value.attr |
155 elif isinstance(value, TODAY): |
161 elif isinstance(value, TODAY): |
157 elif isinstance(value, NOW): |
163 elif isinstance(value, NOW): |
158 return dbhelper.sql_current_timestamp() |
164 return dbhelper.sql_current_timestamp() |
159 else: |
165 else: |
160 # XXX more quoting for literals? |
166 # XXX more quoting for literals? |
161 return value |
167 return value |
|
168 |
162 |
169 |
163 def check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=''): |
170 def check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=''): |
164 # XXX should find a better name |
171 # XXX should find a better name |
165 cstrname = 'cstr' + md5((eschema.type + attr + constraint.type() + |
172 cstrname = 'cstr' + md5((eschema.type + attr + constraint.type() + |
166 (constraint.serialize() or '')).encode('ascii')).hexdigest() |
173 (constraint.serialize() or '')).encode('ascii')).hexdigest() |
183 else: |
190 else: |
184 # XXX better quoting? |
191 # XXX better quoting? |
185 values = ', '.join("'%s'" % word.replace("'", "''") for word in constraint.vocabulary()) |
192 values = ', '.join("'%s'" % word.replace("'", "''") for word in constraint.vocabulary()) |
186 return cstrname, '%s%s IN (%s)' % (prefix, attr, values) |
193 return cstrname, '%s%s IN (%s)' % (prefix, attr, values) |
187 return None, None |
194 return None, None |
|
195 |
188 |
196 |
189 def aschema2sql(dbhelper, eschema, rschema, aschema, creating=True, indent=''): |
197 def aschema2sql(dbhelper, eschema, rschema, aschema, creating=True, indent=''): |
190 """write an attribute schema as SQL statements to stdout""" |
198 """write an attribute schema as SQL statements to stdout""" |
191 attr = rschema.type |
199 attr = rschema.type |
192 rdef = rschema.rdef(eschema.type, aschema.type) |
200 rdef = rschema.rdef(eschema.type, aschema.type) |
216 |
224 |
217 def type_from_rdef(dbhelper, rdef, creating=True): |
225 def type_from_rdef(dbhelper, rdef, creating=True): |
218 """return a sql type string corresponding to the relation definition""" |
226 """return a sql type string corresponding to the relation definition""" |
219 constraints = list(rdef.constraints) |
227 constraints = list(rdef.constraints) |
220 unique, sqltype = False, None |
228 unique, sqltype = False, None |
221 if rdef.object.type == 'String': |
229 for constraint in constraints: |
222 for constraint in constraints: |
230 if isinstance(constraint, UniqueConstraint): |
223 if isinstance(constraint, SizeConstraint): |
231 unique = True |
224 if constraint.max is not None: |
232 elif (isinstance(constraint, SizeConstraint) |
225 size_constrained_string = dbhelper.TYPE_MAPPING.get( |
233 and rdef.object.type == 'String' |
226 'SizeConstrainedString', 'varchar(%s)') |
234 and constraint.max is not None): |
227 sqltype = size_constrained_string % constraint.max |
235 size_constrained_string = dbhelper.TYPE_MAPPING.get( |
228 elif isinstance(constraint, UniqueConstraint): |
236 'SizeConstrainedString', 'varchar(%s)') |
229 unique = True |
237 sqltype = size_constrained_string % constraint.max |
230 if sqltype is None: |
238 if sqltype is None: |
231 sqltype = sql_type(dbhelper, rdef) |
239 sqltype = sql_type(dbhelper, rdef) |
232 if creating and unique: |
240 if creating and unique: |
233 sqltype += ' UNIQUE' |
241 sqltype += ' UNIQUE' |
234 return sqltype |
242 return sqltype |