/[volute]/trunk/projects/theory/snapdm/res/intermediate2ddl.xsl
ViewVC logotype

Contents of /trunk/projects/theory/snapdm/res/intermediate2ddl.xsl

Parent Directory Parent Directory | Revision Log Revision Log


Revision 690 - (show annotations)
Thu Jul 10 15:27:40 2008 UTC (12 years, 4 months ago) by bourges.laurent
File MIME type: application/xml
File size: 20953 byte(s)
added rank support for collections
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!--
3 This XSLT script transforms a data model from our
4 intermediate representation to a relational database
5 Data Definition Language script.
6
7 By default we use the "joined" object-relational mapping strategy.
8 That is, each objectType has a table of its own, in which only those features defined on
9 the objectType are mapped to columns, the inherited are mapped by base classes.
10
11 We also generate view definitions representing each objectType.
12
13 We assume that all tables are in a single schema.
14 For now we assume that objectType's names are unique over the complete model.
15 TODO We need to check this explicitly and modify the generation if not.
16
17
18 LAURENT : TODO : add foreign key on PK for inheritance :
19
20 CREATE TABLE TargetObjectType (
21 id integer NOT NULL, // inheritance
22 ...
23 CONSTRAINT TargetObjectType_pk PRIMARY KEY (id),
24 CONSTRAINT TargetObjectType_ObjectType_fk FOREIGN KEY (id) REFERENCES ObjectType (id), // inheritance
25 ...
26 )
27
28 -->
29
30 <!DOCTYPE stylesheet [
31 <!ENTITY cr "<xsl:text>
32 </xsl:text>">
33 <!ENTITY bl "<xsl:text> </xsl:text>">
34 <!ENTITY rem "<xsl:text>-- </xsl:text>">
35 ]>
36
37 <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
38 xmlns:exsl="http://exslt.org/common"
39 extension-element-prefixes="exsl"
40 xmlns:xsd="http://www.w3.org/2001/XMLSchema">
41
42
43 <xsl:import href="common-ddl.xsl"/>
44 <xsl:import href="utype.xsl"/>
45
46 <!-- possible values: postgres, mssqlserver -->
47 <xsl:param name="vendor"/>
48
49
50 <xsl:output method="text" encoding="UTF-8" indent="no" />
51
52 <xsl:strip-space elements="*" />
53
54 <xsl:key name="element" match="*" use="@xmiid"/>
55 <xsl:key name="ptype" match="*//primitiveType" use="@xmiid"/>
56 <xsl:key name="dtype" match="*//dataType" use="@xmiid"/>
57 <xsl:key name="enum" match="*//enumeration" use="@xmiid"/>
58
59
60 <xsl:param name="lastModifiedText"/>
61
62 <xsl:variable name="header">&rem;last modification date of the UML model <xsl:value-of select="$lastModifiedText"/>&cr;</xsl:variable>
63
64
65
66
67 <!-- start -->
68 <xsl:template match="/">
69 <xsl:apply-templates select="model"/>
70 </xsl:template>
71
72
73
74 <!-- Topological sort/depth first ordering of input objectType-s -->
75 <!-- From http://www.biglist.com/lists/xsl-list/archives/200101/msg00161.html -->
76 <xsl:template name="topsort">
77 <xsl:param name="nodes"/>
78 <xsl:param name="finished"/>
79 <xsl:variable name="processed" select="$nodes|$finished"/>
80
81 <xsl:for-each select="$nodes">
82 <node><xsl:copy-of select="."/><utype><xsl:apply-templates select="." mode="utype"/></utype></node>
83 </xsl:for-each>
84
85 <xsl:if test="count(//objectType)>count($processed)">
86 <xsl:variable name="nextnodes"
87 select="//objectType[not($processed/@xmiid=@xmiid)
88 and count(reference)=count(reference[datatype/@xmiidref = $processed/@xmiid])
89 and count(container)=count(container[@xmiidref=$processed/@xmiid])
90 and count(extends)=count(extends[@xmiidref=$processed/@xmiid])]"/>
91
92 <xsl:if test="$nextnodes">
93 <xsl:call-template name="topsort">
94 <xsl:with-param name="nodes" select="$nextnodes"/>
95 <xsl:with-param name="finished" select="$processed"/>
96 </xsl:call-template>
97 </xsl:if>
98 </xsl:if>
99 </xsl:template>
100
101
102
103
104 <!-- template dump : debugging purposes only -->
105 <xsl:template match="@*|node()" mode="dump">
106 <xsl:copy>
107 <xsl:apply-templates select="@*|node()" mode="dump"/>
108 </xsl:copy>
109 </xsl:template>
110
111
112
113
114 <xsl:template match="model">
115 <xsl:message>Model = <xsl:value-of select="name"></xsl:value-of></xsl:message>
116 &rem;Generating DDLs for model <xsl:value-of select="name"/> and DB vendor <xsl:value-of select="$vendor"/>.
117 <xsl:value-of select="$header"/>
118
119 <!-- create object types sorted according to foreign key graph derived from container, inheritance and reference -->
120 <xsl:variable name="sortedObjectTypes">
121 <!-- add non-object types separately -->
122 <xsl:for-each select="//primitiveType|//dataType|//enumeration">
123 <vt><xsl:copy-of select="."/></vt>
124 </xsl:for-each>
125 <xsl:call-template name="topsort">
126 <!-- need to add the value types as well as the objecttypes, otherwise when in the context -->
127 <xsl:with-param name="nodes" select="//objectType[not(reference|extends|container)]"/>
128 <xsl:with-param name="finished" select="/.."/>
129 </xsl:call-template>
130 </xsl:variable>
131
132
133 <!-- CREATE TABLES -->
134 <xsl:variable name="file" select="concat($vendor,'/',name,'_createTables.sql')"/>
135 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
136 <xsl:result-document href="{$file}">
137 <xsl:value-of select="$header"/>&cr;&cr;
138
139 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
140 <xsl:sort select="position()" data-type="number" order="ascending"/>
141
142 &rem;<xsl:text>------------------------------------------------------------------------------</xsl:text>&cr;
143 &rem;<xsl:text> Table representation of the objectType </xsl:text><xsl:value-of select="name"/>&cr;
144 &rem;<xsl:text> XMI-ID = </xsl:text><xsl:value-of select="@xmiid"/>&cr;
145 &rem;<xsl:text>------------------------------------------------------------------------------</xsl:text>&cr;
146 &rem;<xsl:value-of select="replace(description,'\n',' ')"/>&cr;
147 &rem;<xsl:text>------------------------------------------------------------------------------</xsl:text>&cr;
148
149 <xsl:apply-templates select="." mode="createTable"/>
150
151 <xsl:apply-templates select="." mode="createIndexes"/>
152 <!-- because of topologically sorted nodes, can define the foreign keys directly after the table definition -->
153 <xsl:apply-templates select="." mode="createFKs"/>
154
155 &cr;&cr;&cr;
156 </xsl:for-each>
157 </xsl:result-document>
158
159
160 <!-- CREATE VIEWS -->
161 <xsl:variable name="file" select="concat($vendor,'/',name,'_createViews.sql')"/>
162 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
163 <xsl:result-document href="{$file}">
164 <xsl:value-of select="$header"/>
165
166 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
167 <xsl:sort select="position()" data-type="number" order="ascending"/>
168 <xsl:apply-templates select="." mode="createView"/>
169
170 </xsl:for-each>
171 </xsl:result-document>
172
173
174 <!-- DROP VIEWS -->
175 <xsl:variable name="file" select="concat($vendor,'/',name,'_dropViews.sql')"/>
176 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
177 <xsl:result-document href="{$file}">
178 <xsl:value-of select="$header"/>
179
180 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
181 <xsl:sort select="position()" data-type="number" order="descending"/>
182 <xsl:apply-templates select="." mode="dropView"/>
183
184 </xsl:for-each>
185 </xsl:result-document>
186
187
188 <!-- DROP TABLES -->
189 <xsl:variable name="file" select="concat($vendor,'/',name,'_dropTables.sql')"/>
190 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
191 <xsl:result-document href="{$file}">
192 <xsl:value-of select="$header"/>
193
194 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
195 <xsl:sort select="position()" data-type="number" order="descending"/>
196 <xsl:apply-templates select="." mode="dropTable"/>
197
198 </xsl:for-each>
199 </xsl:result-document>
200
201 </xsl:template>
202
203
204
205
206 <xsl:template match="objectType" mode="createTable">
207 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
208 <xsl:variable name="tableName">
209 <xsl:apply-templates select="." mode="tableName"/>
210 </xsl:variable>
211 <xsl:text>CREATE TABLE </xsl:text><xsl:value-of select="$tableName"/><xsl:text> (</xsl:text>&cr;
212 &bl;&bl;<xsl:value-of select="$primaryKeyColumnName"/>&bl;
213 <xsl:choose>
214 <xsl:when test="not(extends)">
215 <xsl:value-of select="$IDGentype"/>
216 </xsl:when>
217 <xsl:otherwise>
218 <xsl:value-of select="$IDDatatype"/>&bl;<xsl:text>NOT NULL</xsl:text>
219 </xsl:otherwise>
220 </xsl:choose>
221 &cr;
222 <xsl:if test="not(extends)">
223 <xsl:apply-templates select="." mode="discriminatorColumnDeclaration"/>
224 <xsl:text>, OPTLOCK INTEGER</xsl:text>&cr;
225 <xsl:text>, xmlId VARCHAR(32)</xsl:text>&cr;
226 <xsl:text>, ivoId VARCHAR(255)</xsl:text>&cr;
227 </xsl:if>
228 <xsl:apply-templates select="." mode="container"/>
229 <xsl:apply-templates select="attribute" />
230 <xsl:apply-templates select="reference" />
231 <xsl:text>);</xsl:text>&cr;
232 </xsl:template>
233
234
235
236
237 <xsl:template match="objectType" mode="dropTable">
238 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
239 <xsl:variable name="tableName">
240 <xsl:apply-templates select="." mode="tableName"/>
241 </xsl:variable>
242 <xsl:text>DROP TABLE </xsl:text><xsl:value-of select="$tableName"/>;&cr;&cr;
243 </xsl:template>
244
245
246
247
248 <!-- View definition for a given objectType.
249 Returns all columns for the given type, including inherited ones.
250 Uses view for base class, if available. -->
251 <xsl:template match="objectType" mode="createView">
252 <xsl:variable name="tableName">
253 <xsl:apply-templates select="." mode="tableName"/>
254 </xsl:variable>
255 <xsl:variable name="viewName">
256 <xsl:apply-templates select="." mode="viewName"/>
257 </xsl:variable>
258 <xsl:variable name="base" select="key('element',extends/@xmiidref)"/>
259 <xsl:text>CREATE VIEW </xsl:text><xsl:value-of select="$viewName"/><xsl:text> AS</xsl:text>&cr;
260 <xsl:choose>
261 <xsl:when test="extends">
262 <xsl:text> SELECT b.*</xsl:text>&cr;
263 <xsl:if test="container">
264 <xsl:text> , t.containerId</xsl:text>&cr;
265 </xsl:if>
266 <xsl:for-each select="attribute">
267 <xsl:variable name="columns">
268 <xsl:apply-templates select="." mode="columns"/>
269 </xsl:variable>
270 <xsl:for-each select="exsl:node-set($columns)/column">
271 <xsl:text> , t.</xsl:text><xsl:value-of select="name"/>&cr;
272 </xsl:for-each>
273 </xsl:for-each>
274 <xsl:for-each select="reference[not(subsets)]">
275 <xsl:text> , t.</xsl:text><xsl:apply-templates select="." mode="columnName"/>&cr;
276 </xsl:for-each>
277 <xsl:text> FROM </xsl:text><xsl:value-of select="$tableName"/><xsl:text> t</xsl:text>&cr;
278 <xsl:text> , </xsl:text><xsl:apply-templates select="$base" mode="viewName"/> b
279 <xsl:text> WHERE b.</xsl:text><xsl:value-of select="$primaryKeyColumnName"/><xsl:text> = t.</xsl:text><xsl:value-of select="$primaryKeyColumnName"/>
280 </xsl:when>
281 <xsl:otherwise>
282 <xsl:text> SELECT </xsl:text><xsl:value-of select="$primaryKeyColumnName"/>&cr;
283 <xsl:text> , </xsl:text><xsl:value-of select="$discriminatorColumnName"/>&cr;
284 <xsl:if test="container">
285 <xsl:text> , containerId</xsl:text>&cr;
286 </xsl:if>
287 <xsl:for-each select="attribute">
288 <xsl:variable name="columns">
289 <xsl:apply-templates select="." mode="columns"/>
290 </xsl:variable>
291 <xsl:for-each select="exsl:node-set($columns)/column">
292 <xsl:text> , </xsl:text><xsl:value-of select="name"/>&cr;
293 </xsl:for-each>
294 </xsl:for-each>
295 <xsl:for-each select="reference[not(subsets)]">
296 <xsl:text> , </xsl:text><xsl:apply-templates select="." mode="columnName"/>&cr;
297 </xsl:for-each>
298 <xsl:text> FROM </xsl:text><xsl:value-of select="$tableName"/>
299 </xsl:otherwise>
300 </xsl:choose>
301 <xsl:choose>
302 <xsl:when test="$vendor='mssqlserver'">
303 &cr;<xsl:text>GO</xsl:text>
304 </xsl:when>
305 <xsl:otherwise>
306 <xsl:text>;</xsl:text>
307 </xsl:otherwise>
308 </xsl:choose>
309 &cr;&cr;
310 </xsl:template>
311
312
313
314
315 <xsl:template match="objectType" mode="dropView">
316 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
317 <xsl:variable name="viewName">
318 <xsl:apply-templates select="." mode="viewName"/>
319 </xsl:variable>
320 <xsl:text>DROP VIEW </xsl:text><xsl:value-of select="$viewName"/>;&cr;&cr;
321 </xsl:template>
322
323
324
325
326 <xsl:template match="objectType" mode="createFKs">
327 <!-- generate a foreign key for each relation -->
328 <xsl:variable name="tableName">
329 <xsl:apply-templates select="." mode="tableName"/>
330 </xsl:variable>
331 <xsl:if test="container">
332 <xsl:variable name="otherTable">
333 <xsl:apply-templates select="key('element',container/@xmiidref)" mode="tableName"/>
334 </xsl:variable>
335 <xsl:text>ALTER TABLE </xsl:text><xsl:value-of select="$tableName"/> ADD CONSTRAINT fk_<xsl:value-of select="$tableName"/>_container&cr;
336 <xsl:text> FOREIGN KEY (containerId) REFERENCES </xsl:text><xsl:value-of select="$otherTable"/>(<xsl:value-of select="$primaryKeyColumnName"/>);&cr;&cr;
337 </xsl:if>
338 <xsl:if test="extends">
339 <xsl:variable name="otherTable">
340 <xsl:apply-templates select="key('element',extends/@xmiidref)" mode="tableName"/>
341 </xsl:variable>
342 <xsl:text>ALTER TABLE </xsl:text><xsl:value-of select="$tableName"/> ADD CONSTRAINT fk_<xsl:value-of select="$tableName"/>_extends&cr;
343 <xsl:text> FOREIGN KEY (</xsl:text><xsl:value-of select="$primaryKeyColumnName"/><xsl:text>) REFERENCES </xsl:text><xsl:value-of select="$otherTable"/>(<xsl:value-of select="$primaryKeyColumnName"/>);&cr;&cr;
344 </xsl:if>
345 <xsl:for-each select="reference[not(subsets)]">
346 <xsl:variable name="otherTable">
347 <xsl:apply-templates select="key('element',datatype/@xmiidref)" mode="tableName"/>
348 </xsl:variable>
349 <xsl:text>ALTER TABLE </xsl:text><xsl:value-of select="$tableName"/> ADD CONSTRAINT fk_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/>&cr;
350 <xsl:text> FOREIGN KEY (</xsl:text><xsl:apply-templates select="." mode="columnName"/>) REFERENCES <xsl:value-of select="$otherTable"/>(<xsl:value-of select="$primaryKeyColumnName"/>);&cr;&cr;
351 </xsl:for-each>
352 </xsl:template>
353
354
355
356
357 <xsl:template match="objectType" mode="dropFKs">
358 <!-- generate a foreign key for each relation -->
359 <xsl:variable name="tableName">
360 <xsl:apply-templates select="." mode="tableName"/>
361 </xsl:variable>
362 <xsl:for-each select="reference">
363 DROP FOREIGN KEY fk_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/> (<xsl:apply-templates select="." mode="columnName"/>);&cr;&cr;
364 </xsl:for-each>
365 </xsl:template>
366
367
368
369
370 <xsl:template match="objectType" mode="createIndexes">
371 <!-- generate a foreign key for each relation -->
372 <xsl:variable name="tableName">
373 <xsl:apply-templates select="." mode="tableName"/>
374 </xsl:variable>
375 ALTER TABLE <xsl:value-of select="$tableName"/> ADD CONSTRAINT pk_<xsl:value-of select="$tableName"/>_<xsl:value-of select="$primaryKeyColumnName"/> PRIMARY KEY(<xsl:value-of select="$primaryKeyColumnName"/>);&cr;&cr;
376
377 <xsl:for-each select="reference[not(subsets)]">
378 CREATE INDEX ix_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/> ON <xsl:value-of select="$tableName"/>(<xsl:apply-templates select="." mode="columnName"/>);&cr;&cr;
379 </xsl:for-each>
380 </xsl:template>
381
382
383
384
385 <xsl:template match="objectType" mode="dropIndexes">
386 <!-- generate a foreign key for each relation -->
387 <xsl:variable name="tableName">
388 <xsl:apply-templates select="." mode="tableName"/>
389 </xsl:variable>
390 <xsl:for-each select="reference">
391 DROP INDEX <xsl:value-of select="$tableName"/>.ix_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/> (<xsl:apply-templates select="." mode="columnName"/>);&cr;&cr;
392 </xsl:for-each>
393 </xsl:template>
394
395
396
397
398 <!-- determine whether there is a class that contains this class.
399 If so generate a containerID column.
400 NOTE we should ensure that there is only 1 -->
401 <xsl:template match="objectType" mode="container">
402 <xsl:variable name="xmiid" select="@xmiid"/>
403 <xsl:if test="container">
404 <xsl:text>, containerId </xsl:text><xsl:value-of select="$IDDatatype"/><xsl:text> NOT NULL </xsl:text>&rem;<xsl:value-of select="key('element',container/@xmiidref)/name"/>&cr;
405 <xsl:text>, rank INTEGER NOT NULL </xsl:text>&cr;
406 </xsl:if>
407 </xsl:template>
408
409
410
411
412 <xsl:template match="attribute">
413 <xsl:variable name="columns">
414 <xsl:apply-templates select="." mode="columns"/>
415 </xsl:variable>
416 <xsl:for-each select="exsl:node-set($columns)/column">
417 <xsl:text>, </xsl:text><xsl:value-of select="name"/>&bl;<xsl:value-of select="sqltype"/><xsl:if test="multiplicity = '1'"> NOT NULL</xsl:if>&cr;
418 </xsl:for-each>
419 </xsl:template>
420
421
422
423
424 <xsl:template match="attribute" mode="old">
425 <xsl:param name="prefix"/>
426
427 <xsl:variable name="columnname">
428 <xsl:choose>
429 <xsl:when test="$prefix">
430 <xsl:value-of select="concat($prefix,'_',name)"/>
431 </xsl:when>
432 <xsl:otherwise>
433 <xsl:value-of select="name"/>
434 </xsl:otherwise>
435 </xsl:choose>
436 </xsl:variable>
437 <xsl:variable name="type" select="key('element',datatype/@xmiidref)"/>
438 <xsl:choose>
439 <xsl:when test="name($type) != 'primitiveType'">
440 <xsl:apply-templates select="$type" mode="columns"><xsl:with-param name="prefix" select="$columnname"/></xsl:apply-templates>
441 </xsl:when>
442 <xsl:otherwise>
443 <xsl:variable name="sqltype">
444 <xsl:call-template name="sqltype">
445 <xsl:with-param name="type" select="$type"/>
446 <xsl:with-param name="constraints" select="constraints"/>
447 </xsl:call-template>
448 </xsl:variable>, <xsl:value-of select="$columnname"/>&bl;<xsl:value-of select="$sqltype"/><xsl:if test="multiplicity = '1'"> NOT NULL</xsl:if>&cr;
449 </xsl:otherwise>
450 </xsl:choose>
451
452 </xsl:template>
453
454
455
456
457 <!-- DEBUG template -->
458 <xsl:template match="datatype/@xmiidref" mode="classifier">
459 <xsl:variable name="type" select="key('element',.)"/>
460 <xsl:value-of select="$type/name"/>
461 </xsl:template>
462 <!-- END DEBUG -->
463
464
465
466
467 <!-- We need lengths for (var)char datatypes -->
468 <xsl:template match="dataType" mode="columns">
469 <xsl:param name="prefix"/>
470 <xsl:choose>
471 <xsl:when test="not(attribute)">, <xsl:value-of select="$prefix"/> VARCHAR(256) &rem;<xsl:value-of select="name"/>&cr;</xsl:when>
472 <xsl:otherwise><xsl:apply-templates select="attribute"><xsl:with-param name="prefix" select="$prefix"/></xsl:apply-templates></xsl:otherwise>
473 </xsl:choose>
474 </xsl:template>
475
476
477
478
479 <!-- We need lengths for (var)char datatypes -->
480 <xsl:template match="enumeration" mode="columns">
481 <xsl:param name="prefix"/>, <xsl:value-of select="$prefix"/> VARCHAR(256) &rem;<xsl:value-of select="name"/>&cr;
482 </xsl:template>
483
484
485
486
487 <xsl:template match="reference">
488 <xsl:if test="not(subsets)">, <xsl:apply-templates select="." mode="columnName"/>&bl;<xsl:value-of select="$IDDatatype"/>&bl;<xsl:call-template name="nullity"><xsl:with-param name="multiplicity" select="multiplicity"/></xsl:call-template> &rem;<xsl:value-of select="key('element',datatype/@xmiidref)/name"/>&cr;
489 </xsl:if>
490 </xsl:template>
491
492
493
494
495 <xsl:template name="nullity">
496 <xsl:param name="multiplicity"/>
497 <xsl:choose>
498 <xsl:when test="$multiplicity = '1' or $multiplicity = '1..*'">NOT NULL</xsl:when>
499 <xsl:otherwise>null</xsl:otherwise>
500 </xsl:choose>
501 </xsl:template>
502
503
504
505
506 <!-- TODO
507 Add templates retrieving for a given objectType the table it is in
508 for a given attribute the column(s) it is in
509 for a given reference the column it is in
510 for a given collection the containerId column it is in.
511 -->
512
513
514
515 <!-- Maybe somewhat too much indirection here? -->
516 <xsl:template match="objectType" mode="PK_COLUMN">
517 <xsl:value-of select="concat($primaryKeyColumnName,' ',$IDDatatype)"/><xsl:text> NOT NULL</xsl:text>
518 </xsl:template>
519
520
521
522
523 <!-- Discriminator column templates -->
524 <xsl:template match="objectType" mode="discriminatorColumnDeclaration">, <xsl:value-of select="$discriminatorColumnName"/> <xsl:value-of select="$discriminatorColumnType"/>&cr;
525 </xsl:template>
526
527
528 </xsl:stylesheet>

msdemlei@ari.uni-heidelberg.de
ViewVC Help
Powered by ViewVC 1.1.26