1 # copyright 2004-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved. |
|
2 # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr |
|
3 # |
|
4 # This file is part of CubicWeb. |
|
5 # |
|
6 # CubicWeb is free software: you can redistribute it and/or modify it under the |
|
7 # terms of the GNU Lesser General Public License as published by the Free |
|
8 # Software Foundation, either version 2.1 of the License, or (at your option) |
|
9 # any later version. |
|
10 # |
|
11 # CubicWeb is distributed in the hope that it will be useful, but WITHOUT ANY |
|
12 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR |
|
13 # A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more |
|
14 # details. |
|
15 # |
|
16 # You should have received a copy of the GNU Lesser General Public License along |
|
17 # with CubicWeb. If not, see <http://www.gnu.org/licenses/>. |
|
18 """unit tests for module cubicweb.server.schema2sql |
|
19 """ |
|
20 |
|
21 import os.path as osp |
|
22 |
|
23 from logilab.common.testlib import TestCase, unittest_main |
|
24 from logilab.database import get_db_helper |
|
25 |
|
26 from yams.reader import SchemaLoader |
|
27 from cubicweb.server import schema2sql |
|
28 |
|
29 schema2sql.SET_DEFAULT = True |
|
30 |
|
31 DATADIR = osp.abspath(osp.join(osp.dirname(__file__), 'data-schema2sql')) |
|
32 |
|
33 schema = SchemaLoader().load([DATADIR]) |
|
34 |
|
35 |
|
36 EXPECTED_DATA_NO_DROP = """ |
|
37 CREATE TABLE Affaire( |
|
38 sujet varchar(128), |
|
39 ref varchar(12), |
|
40 inline_rel integer REFERENCES entities (eid) |
|
41 ); |
|
42 CREATE INDEX affaire_inline_rel_idx ON Affaire(inline_rel); |
|
43 |
|
44 CREATE TABLE Company( |
|
45 name text |
|
46 ); |
|
47 |
|
48 CREATE TABLE Datetest( |
|
49 dt1 timestamp, |
|
50 dt2 timestamp, |
|
51 d1 date, |
|
52 d2 date, |
|
53 t1 time, |
|
54 t2 time |
|
55 , CONSTRAINT cstredd407706bdfbd2285714dd689e8fcc0 CHECK(d1 <= CAST(clock_timestamp() AS DATE)) |
|
56 ); |
|
57 |
|
58 CREATE TABLE Division( |
|
59 name text |
|
60 ); |
|
61 |
|
62 CREATE TABLE EPermission( |
|
63 name varchar(100) NOT NULL |
|
64 ); |
|
65 CREATE INDEX epermission_name_idx ON EPermission(name); |
|
66 |
|
67 CREATE TABLE Eetype( |
|
68 name varchar(64) UNIQUE NOT NULL, |
|
69 description text, |
|
70 meta boolean, |
|
71 final boolean, |
|
72 initial_state integer REFERENCES entities (eid) |
|
73 ); |
|
74 CREATE INDEX eetype_name_idx ON Eetype(name); |
|
75 CREATE INDEX eetype_initial_state_idx ON Eetype(initial_state); |
|
76 |
|
77 CREATE TABLE Employee( |
|
78 ); |
|
79 |
|
80 CREATE TABLE Note( |
|
81 date varchar(10), |
|
82 type varchar(1), |
|
83 para varchar(512) |
|
84 ); |
|
85 |
|
86 CREATE TABLE Person( |
|
87 nom varchar(64) NOT NULL, |
|
88 prenom varchar(64), |
|
89 sexe varchar(1) DEFAULT 'M', |
|
90 promo varchar(6), |
|
91 titre varchar(128), |
|
92 adel varchar(128), |
|
93 ass varchar(128), |
|
94 web varchar(128), |
|
95 tel integer, |
|
96 fax integer, |
|
97 datenaiss date, |
|
98 test boolean, |
|
99 salary float |
|
100 , CONSTRAINT cstr41fe7db9ce1d5be95de2477e26590386 CHECK(promo IN ('bon', 'pasbon')) |
|
101 ); |
|
102 CREATE UNIQUE INDEX unique_e6c2d219772dbf1715597f7d9a6b3892 ON Person(nom,prenom); |
|
103 |
|
104 CREATE TABLE Salaried( |
|
105 nom varchar(64) NOT NULL, |
|
106 prenom varchar(64), |
|
107 sexe varchar(1) DEFAULT 'M', |
|
108 promo varchar(6), |
|
109 titre varchar(128), |
|
110 adel varchar(128), |
|
111 ass varchar(128), |
|
112 web varchar(128), |
|
113 tel integer, |
|
114 fax integer, |
|
115 datenaiss date, |
|
116 test boolean, |
|
117 salary float |
|
118 , CONSTRAINT cstrc8556fcc665865217761cdbcd220cae0 CHECK(promo IN ('bon', 'pasbon')) |
|
119 ); |
|
120 CREATE UNIQUE INDEX unique_98da0f9de8588baa8966f0b1a6f850a3 ON Salaried(nom,prenom); |
|
121 |
|
122 CREATE TABLE Societe( |
|
123 nom varchar(64), |
|
124 web varchar(128), |
|
125 tel integer, |
|
126 fax integer, |
|
127 rncs varchar(32), |
|
128 ad1 varchar(128), |
|
129 ad2 varchar(128), |
|
130 ad3 varchar(128), |
|
131 cp varchar(12), |
|
132 ville varchar(32) |
|
133 , CONSTRAINT cstrc51dd462e9f6115506a0fe468d4c8114 CHECK(fax <= tel) |
|
134 ); |
|
135 |
|
136 CREATE TABLE State( |
|
137 eid integer PRIMARY KEY REFERENCES entities (eid), |
|
138 name varchar(256) NOT NULL, |
|
139 description text |
|
140 ); |
|
141 CREATE INDEX state_name_idx ON State(name); |
|
142 |
|
143 CREATE TABLE Subcompany( |
|
144 name text |
|
145 ); |
|
146 |
|
147 CREATE TABLE Subdivision( |
|
148 name text |
|
149 ); |
|
150 |
|
151 CREATE TABLE pkginfo( |
|
152 modname varchar(30) NOT NULL, |
|
153 version varchar(10) DEFAULT '0.1' NOT NULL, |
|
154 copyright text NOT NULL, |
|
155 license varchar(3), |
|
156 short_desc varchar(80) NOT NULL, |
|
157 long_desc text NOT NULL, |
|
158 author varchar(100) NOT NULL, |
|
159 author_email varchar(100) NOT NULL, |
|
160 mailinglist varchar(100), |
|
161 debian_handler varchar(6) |
|
162 , CONSTRAINT cstr70f766f834557c715815d76f0a0db956 CHECK(license IN ('GPL', 'ZPL')) |
|
163 , CONSTRAINT cstr831a117424d0007ae0278cc15f344f5e CHECK(debian_handler IN ('machin', 'bidule')) |
|
164 ); |
|
165 |
|
166 |
|
167 CREATE TABLE concerne_relation ( |
|
168 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
169 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
170 CONSTRAINT concerne_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
171 ); |
|
172 |
|
173 CREATE INDEX concerne_relation_from_idx ON concerne_relation(eid_from); |
|
174 CREATE INDEX concerne_relation_to_idx ON concerne_relation(eid_to); |
|
175 |
|
176 CREATE TABLE division_of_relation ( |
|
177 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
178 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
179 CONSTRAINT division_of_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
180 ); |
|
181 |
|
182 CREATE INDEX division_of_relation_from_idx ON division_of_relation(eid_from); |
|
183 CREATE INDEX division_of_relation_to_idx ON division_of_relation(eid_to); |
|
184 |
|
185 CREATE TABLE evaluee_relation ( |
|
186 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
187 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
188 CONSTRAINT evaluee_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
189 ); |
|
190 |
|
191 CREATE INDEX evaluee_relation_from_idx ON evaluee_relation(eid_from); |
|
192 CREATE INDEX evaluee_relation_to_idx ON evaluee_relation(eid_to); |
|
193 |
|
194 CREATE TABLE next_state_relation ( |
|
195 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
196 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
197 CONSTRAINT next_state_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
198 ); |
|
199 |
|
200 CREATE INDEX next_state_relation_from_idx ON next_state_relation(eid_from); |
|
201 CREATE INDEX next_state_relation_to_idx ON next_state_relation(eid_to); |
|
202 |
|
203 CREATE TABLE obj_wildcard_relation ( |
|
204 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
205 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
206 CONSTRAINT obj_wildcard_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
207 ); |
|
208 |
|
209 CREATE INDEX obj_wildcard_relation_from_idx ON obj_wildcard_relation(eid_from); |
|
210 CREATE INDEX obj_wildcard_relation_to_idx ON obj_wildcard_relation(eid_to); |
|
211 |
|
212 CREATE TABLE require_permission_relation ( |
|
213 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
214 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
215 CONSTRAINT require_permission_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
216 ); |
|
217 |
|
218 CREATE INDEX require_permission_relation_from_idx ON require_permission_relation(eid_from); |
|
219 CREATE INDEX require_permission_relation_to_idx ON require_permission_relation(eid_to); |
|
220 |
|
221 CREATE TABLE state_of_relation ( |
|
222 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
223 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
224 CONSTRAINT state_of_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
225 ); |
|
226 |
|
227 CREATE INDEX state_of_relation_from_idx ON state_of_relation(eid_from); |
|
228 CREATE INDEX state_of_relation_to_idx ON state_of_relation(eid_to); |
|
229 |
|
230 CREATE TABLE subcompany_of_relation ( |
|
231 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
232 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
233 CONSTRAINT subcompany_of_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
234 ); |
|
235 |
|
236 CREATE INDEX subcompany_of_relation_from_idx ON subcompany_of_relation(eid_from); |
|
237 CREATE INDEX subcompany_of_relation_to_idx ON subcompany_of_relation(eid_to); |
|
238 |
|
239 CREATE TABLE subdivision_of_relation ( |
|
240 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
241 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
242 CONSTRAINT subdivision_of_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
243 ); |
|
244 |
|
245 CREATE INDEX subdivision_of_relation_from_idx ON subdivision_of_relation(eid_from); |
|
246 CREATE INDEX subdivision_of_relation_to_idx ON subdivision_of_relation(eid_to); |
|
247 |
|
248 CREATE TABLE subj_wildcard_relation ( |
|
249 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
250 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
251 CONSTRAINT subj_wildcard_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
252 ); |
|
253 |
|
254 CREATE INDEX subj_wildcard_relation_from_idx ON subj_wildcard_relation(eid_from); |
|
255 CREATE INDEX subj_wildcard_relation_to_idx ON subj_wildcard_relation(eid_to); |
|
256 |
|
257 CREATE TABLE sym_rel_relation ( |
|
258 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
259 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
260 CONSTRAINT sym_rel_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
261 ); |
|
262 |
|
263 CREATE INDEX sym_rel_relation_from_idx ON sym_rel_relation(eid_from); |
|
264 CREATE INDEX sym_rel_relation_to_idx ON sym_rel_relation(eid_to); |
|
265 |
|
266 CREATE TABLE travaille_relation ( |
|
267 eid_from INTEGER NOT NULL REFERENCES entities (eid), |
|
268 eid_to INTEGER NOT NULL REFERENCES entities (eid), |
|
269 CONSTRAINT travaille_relation_p_key PRIMARY KEY(eid_from, eid_to) |
|
270 ); |
|
271 |
|
272 CREATE INDEX travaille_relation_from_idx ON travaille_relation(eid_from); |
|
273 CREATE INDEX travaille_relation_to_idx ON travaille_relation(eid_to); |
|
274 """ |
|
275 |
|
276 class SQLSchemaTC(TestCase): |
|
277 |
|
278 def test_known_values(self): |
|
279 dbhelper = get_db_helper('postgres') |
|
280 output = schema2sql.schema2sql(dbhelper, schema, skip_relations=('works_for',)) |
|
281 self.assertMultiLineEqual(EXPECTED_DATA_NO_DROP.strip(), output.strip()) |
|
282 |
|
283 |
|
284 if __name__ == '__main__': |
|
285 unittest_main() |
|