author | Julien Cristau <julien.cristau@logilab.fr> |
Mon, 23 Nov 2015 17:58:10 +0100 | |
changeset 10897 | 7c386161ebd6 |
parent 10671 | e08102f45237 |
child 11003 | 53820b08a723 |
permissions | -rw-r--r-- |
10481
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
1 |
# copyright 2004-2015 LOGILAB S.A. (Paris, FRANCE), all rights reserved. |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
2 |
# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
3 |
# |
10481
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
4 |
# This file is part of cubicweb. |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
5 |
# |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
6 |
# yams is free software: you can redistribute it and/or modify it under the |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
7 |
# terms of the GNU Lesser General Public License as published by the Free |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
8 |
# Software Foundation, either version 2.1 of the License, or (at your option) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
9 |
# any later version. |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
10 |
# |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
11 |
# yams is distributed in the hope that it will be useful, but WITHOUT ANY |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
12 |
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
13 |
# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
14 |
# details. |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
15 |
# |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
16 |
# You should have received a copy of the GNU Lesser General Public License along |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
17 |
# with yams. If not, see <http://www.gnu.org/licenses/>. |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
18 |
"""write a schema as sql""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
19 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
20 |
__docformat__ = "restructuredtext en" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
21 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
22 |
from hashlib import md5 |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
23 |
|
10443
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
24 |
from six import string_types |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
25 |
from six.moves import range |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
26 |
|
10444
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
27 |
from yams.constraints import (SizeConstraint, UniqueConstraint, Attribute, |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
28 |
NOW, TODAY) |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
29 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
30 |
# default are usually not handled at the sql level. If you want them, set |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
31 |
# SET_DEFAULT to True |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
32 |
SET_DEFAULT = False |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
33 |
|
10481
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
34 |
def rschema_has_table(rschema, skip_relations): |
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
35 |
"""Return True if the given schema should have a table in the database""" |
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
36 |
return not (rschema.final or rschema.inlined or rschema.rule or rschema.type in skip_relations) |
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
37 |
|
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
38 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
39 |
def schema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
40 |
"""write to the output stream a SQL schema to store the objects |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
41 |
corresponding to the given schema |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
42 |
""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
43 |
output = [] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
44 |
w = output.append |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
45 |
for etype in sorted(schema.entities()): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
46 |
eschema = schema.eschema(etype) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
47 |
if eschema.final or eschema.type in skip_entities: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
48 |
continue |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
49 |
w(eschema2sql(dbhelper, eschema, skip_relations, prefix=prefix)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
50 |
for rtype in sorted(schema.relations()): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
51 |
rschema = schema.rschema(rtype) |
10481
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
52 |
if rschema_has_table(rschema, skip_relations): |
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
53 |
w(rschema2sql(rschema)) |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
54 |
return '\n'.join(output) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
55 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
56 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
57 |
def dropschema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
58 |
"""write to the output stream a SQL schema to store the objects |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
59 |
corresponding to the given schema |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
60 |
""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
61 |
output = [] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
62 |
w = output.append |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
63 |
for etype in sorted(schema.entities()): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
64 |
eschema = schema.eschema(etype) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
65 |
if eschema.final or eschema.type in skip_entities: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
66 |
continue |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
67 |
stmts = dropeschema2sql(dbhelper, eschema, skip_relations, prefix=prefix) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
68 |
for stmt in stmts: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
69 |
w(stmt) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
70 |
for rtype in sorted(schema.relations()): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
71 |
rschema = schema.rschema(rtype) |
10481
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
72 |
if rschema_has_table(rschema, skip_relations): |
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
73 |
w(droprschema2sql(rschema)) |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
74 |
return '\n'.join(output) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
75 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
76 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
77 |
def eschema_attrs(eschema, skip_relations): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
78 |
attrs = [attrdef for attrdef in eschema.attribute_definitions() |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
79 |
if not attrdef[0].type in skip_relations] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
80 |
attrs += [(rschema, None) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
81 |
for rschema in eschema.subject_relations() |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
82 |
if not rschema.final and rschema.inlined] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
83 |
return attrs |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
84 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
85 |
def unique_index_name(eschema, columns): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
86 |
return u'unique_%s' % md5((eschema.type + |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
87 |
',' + |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
88 |
','.join(sorted(columns))).encode('ascii')).hexdigest() |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
89 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
90 |
def iter_unique_index_names(eschema): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
91 |
for columns in eschema._unique_together or (): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
92 |
yield columns, unique_index_name(eschema, columns) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
93 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
94 |
def dropeschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
95 |
"""return sql to drop an entity type's table""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
96 |
# not necessary to drop indexes, that's implictly done when |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
97 |
# dropping the table, but we need to drop SQLServer views used to |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
98 |
# create multicol unique indices |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
99 |
statements = [] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
100 |
tablename = prefix + eschema.type |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
101 |
if eschema._unique_together is not None: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
102 |
for columns, index_name in iter_unique_index_names(eschema): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
103 |
cols = ['%s%s' % (prefix, col) for col in columns] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
104 |
sqls = dbhelper.sqls_drop_multicol_unique_index(tablename, cols, index_name) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
105 |
statements += sqls |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
106 |
statements += ['DROP TABLE %s;' % (tablename)] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
107 |
return statements |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
108 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
109 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
110 |
def eschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
111 |
"""write an entity schema as SQL statements to stdout""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
112 |
output = [] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
113 |
w = output.append |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
114 |
table = prefix + eschema.type |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
115 |
w('CREATE TABLE %s(' % (table)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
116 |
attrs = eschema_attrs(eschema, skip_relations) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
117 |
# XXX handle objectinline physical mode |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
118 |
for i in range(len(attrs)): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
119 |
rschema, attrschema = attrs[i] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
120 |
if attrschema is not None: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
121 |
sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema, |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
122 |
indent=' ') |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
123 |
else: # inline relation |
10201
989bbadbcd8d
Add foreign key for inline relations
Julien Cristau <julien.cristau@logilab.fr>
parents:
10199
diff
changeset
|
124 |
sqltype = 'integer REFERENCES entities (eid)' |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
125 |
if i == len(attrs) - 1: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
126 |
w(' %s%s %s' % (prefix, rschema.type, sqltype)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
127 |
else: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
128 |
w(' %s%s %s,' % (prefix, rschema.type, sqltype)) |
10443
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
129 |
for rschema, aschema in attrs: |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
130 |
if aschema is None: # inline relation |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
131 |
continue |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
132 |
attr = rschema.type |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
133 |
rdef = rschema.rdef(eschema.type, aschema.type) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
134 |
for constraint in rdef.constraints: |
10444
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
135 |
cstrname, check = check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=prefix) |
10443
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
136 |
if cstrname is not None: |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
137 |
w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check)) |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
138 |
w(');') |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
139 |
# create indexes |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
140 |
for i in range(len(attrs)): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
141 |
rschema, attrschema = attrs[i] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
142 |
if attrschema is None or eschema.rdef(rschema).indexed: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
143 |
w(dbhelper.sql_create_index(table, prefix + rschema.type)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
144 |
for columns, index_name in iter_unique_index_names(eschema): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
145 |
cols = ['%s%s' % (prefix, col) for col in columns] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
146 |
sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
147 |
for sql in sqls: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
148 |
w(sql) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
149 |
w('') |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
150 |
return '\n'.join(output) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
151 |
|
10444
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
152 |
def as_sql(value, dbhelper, prefix): |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
153 |
if isinstance(value, Attribute): |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
154 |
return prefix + value.attr |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
155 |
elif isinstance(value, TODAY): |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
156 |
return dbhelper.sql_current_date() |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
157 |
elif isinstance(value, NOW): |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
158 |
return dbhelper.sql_current_timestamp() |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
159 |
else: |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
160 |
# XXX more quoting for literals? |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
161 |
return value |
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
162 |
|
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
163 |
def check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=''): |
10443
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
164 |
# XXX should find a better name |
10671
e08102f45237
[py3k] bytes required for hashlib.{md5,sha1}()
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10481
diff
changeset
|
165 |
cstrname = 'cstr' + md5((eschema.type + attr + constraint.type() + |
e08102f45237
[py3k] bytes required for hashlib.{md5,sha1}()
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10481
diff
changeset
|
166 |
(constraint.serialize() or '')).encode('ascii')).hexdigest() |
10443
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
167 |
if constraint.type() == 'BoundaryConstraint': |
10444
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
168 |
value = as_sql(constraint.boundary, dbhelper, prefix) |
10443
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
169 |
return cstrname, '%s%s %s %s' % (prefix, attr, constraint.operator, value) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
170 |
elif constraint.type() == 'IntervalBoundConstraint': |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
171 |
condition = [] |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
172 |
if constraint.minvalue is not None: |
10444
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
173 |
value = as_sql(constraint.minvalue, dbhelper, prefix) |
10443
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
174 |
condition.append('%s%s >= %s' % (prefix, attr, value)) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
175 |
if constraint.maxvalue is not None: |
10444
fb7c1013189e
[schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents:
10443
diff
changeset
|
176 |
value = as_sql(constraint.maxvalue, dbhelper, prefix) |
10443
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
177 |
condition.append('%s%s <= %s' % (prefix, attr, value)) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
178 |
return cstrname, ' AND '.join(condition) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
179 |
elif constraint.type() == 'StaticVocabularyConstraint': |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
180 |
sample = next(iter(constraint.vocabulary())) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
181 |
if not isinstance(sample, string_types): |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
182 |
values = ', '.join(str(word) for word in constraint.vocabulary()) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
183 |
else: |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
184 |
# XXX better quoting? |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
185 |
values = ', '.join("'%s'" % word.replace("'", "''") for word in constraint.vocabulary()) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
186 |
return cstrname, '%s%s IN (%s)' % (prefix, attr, values) |
2d3834df64ab
[schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents:
10204
diff
changeset
|
187 |
return None, None |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
188 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
189 |
def aschema2sql(dbhelper, eschema, rschema, aschema, creating=True, indent=''): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
190 |
"""write an attribute schema as SQL statements to stdout""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
191 |
attr = rschema.type |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
192 |
rdef = rschema.rdef(eschema.type, aschema.type) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
193 |
sqltype = type_from_constraints(dbhelper, aschema.type, rdef.constraints, |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
194 |
creating) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
195 |
if SET_DEFAULT: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
196 |
default = eschema.default(attr) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
197 |
if default is not None: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
198 |
if aschema.type == 'Boolean': |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
199 |
sqltype += ' DEFAULT %s' % dbhelper.boolean_value(default) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
200 |
elif aschema.type == 'String': |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
201 |
sqltype += ' DEFAULT %r' % str(default) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
202 |
elif aschema.type in ('Int', 'BigInt', 'Float'): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
203 |
sqltype += ' DEFAULT %s' % default |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
204 |
# XXX ignore default for other type |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
205 |
# this is expected for NOW / TODAY |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
206 |
if creating: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
207 |
if rdef.uid: |
10204
f8ccae1e271d
Add reference from etype table's eid column to the entities table
Julien Cristau <julien.cristau@logilab.fr>
parents:
10202
diff
changeset
|
208 |
sqltype += ' PRIMARY KEY REFERENCES entities (eid)' |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
209 |
elif rdef.cardinality[0] == '1': |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
210 |
# don't set NOT NULL if backend isn't able to change it later |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
211 |
if dbhelper.alter_column_support: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
212 |
sqltype += ' NOT NULL' |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
213 |
# else we're getting sql type to alter a column, we don't want key / indexes |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
214 |
# / null modifiers |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
215 |
return sqltype |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
216 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
217 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
218 |
def type_from_constraints(dbhelper, etype, constraints, creating=True): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
219 |
"""return a sql type string corresponding to the constraints""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
220 |
constraints = list(constraints) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
221 |
unique, sqltype = False, None |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
222 |
size_constrained_string = dbhelper.TYPE_MAPPING.get('SizeConstrainedString', 'varchar(%s)') |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
223 |
if etype == 'String': |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
224 |
for constraint in constraints: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
225 |
if isinstance(constraint, SizeConstraint): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
226 |
if constraint.max is not None: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
227 |
sqltype = size_constrained_string % constraint.max |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
228 |
elif isinstance(constraint, UniqueConstraint): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
229 |
unique = True |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
230 |
if sqltype is None: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
231 |
sqltype = dbhelper.TYPE_MAPPING[etype] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
232 |
if creating and unique: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
233 |
sqltype += ' UNIQUE' |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
234 |
return sqltype |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
235 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
236 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
237 |
_SQL_SCHEMA = """ |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
238 |
CREATE TABLE %(table)s ( |
10202
aaabcb64f77f
Use foreign keys for relations tables
Julien Cristau <julien.cristau@logilab.fr>
parents:
10201
diff
changeset
|
239 |
eid_from INTEGER NOT NULL REFERENCES entities (eid), |
aaabcb64f77f
Use foreign keys for relations tables
Julien Cristau <julien.cristau@logilab.fr>
parents:
10201
diff
changeset
|
240 |
eid_to INTEGER NOT NULL REFERENCES entities (eid), |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
241 |
CONSTRAINT %(table)s_p_key PRIMARY KEY(eid_from, eid_to) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
242 |
); |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
243 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
244 |
CREATE INDEX %(table)s_from_idx ON %(table)s(eid_from); |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
245 |
CREATE INDEX %(table)s_to_idx ON %(table)s(eid_to);""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
246 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
247 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
248 |
def rschema2sql(rschema): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
249 |
assert not rschema.rule |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
250 |
return _SQL_SCHEMA % {'table': '%s_relation' % rschema.type} |
10481
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
251 |
|
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
252 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
253 |
def droprschema2sql(rschema): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
254 |
"""return sql to drop a relation type's table""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
255 |
# not necessary to drop indexes, that's implictly done when dropping |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
256 |
# the table |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
257 |
return 'DROP TABLE %s_relation;' % rschema.type |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
258 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
259 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
260 |
def grant_schema(schema, user, set_owner=True, skip_entities=(), prefix=''): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
261 |
"""write to the output stream a SQL schema to store the objects |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
262 |
corresponding to the given schema |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
263 |
""" |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
264 |
output = [] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
265 |
w = output.append |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
266 |
for etype in sorted(schema.entities()): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
267 |
eschema = schema.eschema(etype) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
268 |
if eschema.final or etype in skip_entities: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
269 |
continue |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
270 |
w(grant_eschema(eschema, user, set_owner, prefix=prefix)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
271 |
for rtype in sorted(schema.relations()): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
272 |
rschema = schema.rschema(rtype) |
10481
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
273 |
if rschema_has_table(rschema, skip_relations=()): # XXX skip_relations should be specified |
6ac4b1726e9f
[schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10444
diff
changeset
|
274 |
w(grant_rschema(rschema, user, set_owner)) |
10199
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
275 |
return '\n'.join(output) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
276 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
277 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
278 |
def grant_eschema(eschema, user, set_owner=True, prefix=''): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
279 |
output = [] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
280 |
w = output.append |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
281 |
etype = eschema.type |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
282 |
if set_owner: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
283 |
w('ALTER TABLE %s%s OWNER TO %s;' % (prefix, etype, user)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
284 |
w('GRANT ALL ON %s%s TO %s;' % (prefix, etype, user)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
285 |
return '\n'.join(output) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
286 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
287 |
|
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
288 |
def grant_rschema(rschema, user, set_owner=True): |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
289 |
output = [] |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
290 |
if set_owner: |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
291 |
output.append('ALTER TABLE %s_relation OWNER TO %s;' % (rschema.type, user)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
292 |
output.append('GRANT ALL ON %s_relation TO %s;' % (rschema.type, user)) |
218c28bff695
Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
293 |
return '\n'.join(output) |