/[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 407 - (show annotations)
Thu May 8 09:22:17 2008 UTC (12 years, 7 months ago) by gerard.lemson
File MIME type: application/xml
File size: 19258 byte(s)
buggy check in to havelaurent look at problem (xmiidref-s do not find corresponding types for attributes

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
19 <!DOCTYPE stylesheet [
20 <!ENTITY cr "<xsl:text>
21 </xsl:text>">
22 <!ENTITY bl "<xsl:text> </xsl:text>">
23 ]>
24
25 <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
26 xmlns:exsl="http://exslt.org/common"
27 extension-element-prefixes="exsl"
28 xmlns:xsd="http://www.w3.org/2001/XMLSchema">
29
30
31 <xsl:import href="common-ddl.xsl"/>
32
33 <!-- possible values: postgres, mssqlserver -->
34 <xsl:param name="vendor" select="'mssqlserver'"/>
35
36
37 <xsl:output method="text" encoding="UTF-8" indent="no" />
38
39 <xsl:strip-space elements="*" />
40
41 <xsl:key name="element" match="*//*" use="@xmiid"/>
42 <xsl:key name="ptype" match="*//primitiveType" use="@xmiid"/>
43 <xsl:key name="dtype" match="*//dataType" use="@xmiid"/>
44 <xsl:key name="enum" match="*//enumeration" use="@xmiid"/>
45
46
47 <xsl:param name="lastModified"/>
48 <xsl:variable name="header">-- last modification date of the UML model <xsl:value-of select="$lastModified"/>&cr;</xsl:variable>
49
50
51 <xsl:param name="schema"/> <!-- select="'SNAP.'" -->
52
53
54
55
56 <!-- start -->
57 <xsl:template match="/">
58 <xsl:apply-templates select="model"/>
59 </xsl:template>
60
61
62
63 <!-- Topological sort/depth first ordering of doing something -->
64 <!-- From http://www.biglist.com/lists/xsl-list/archives/200101/msg00161.html -->
65 <xsl:template name="topsort">
66 <xsl:param name="nodes"/>
67 <xsl:param name="finished"/>
68 <xsl:variable name="processed" select="$nodes|$finished"/>
69
70 <xsl:for-each select="$nodes">
71 <node><xsl:copy-of select="."/></node>
72 </xsl:for-each>
73
74 <xsl:if test="count(//objectType)>count($processed)">
75 <xsl:variable name="nextnodes"
76 select="//objectType[not($processed/@xmiid=@xmiid)
77 and count(reference)=count(reference[datatype/@xmiidref = $processed/@xmiid])
78 and count(container)=count(container[@xmiidref=$processed/@xmiid])
79 and count(extends)=count(extends[@xmiidref=$processed/@xmiid])]"/>
80
81 <xsl:if test="$nextnodes">
82 <xsl:call-template name="topsort">
83 <xsl:with-param name="nodes" select="$nextnodes"/>
84 <xsl:with-param name="finished" select="$processed"/>
85 </xsl:call-template>
86 </xsl:if>
87 </xsl:if>
88 </xsl:template>
89
90
91
92
93
94 <xsl:template match="model">
95 <xsl:message>Model = <xsl:value-of select="name"></xsl:value-of></xsl:message>
96 -- Generating DDLs for model <xsl:value-of select="name"/> and DB vendor <xsl:value-of select="$vendor"/>.
97 <xsl:value-of select="$header"/>
98
99 <!-- create object types sorted according to foreign key graph derived from container, inheritance and reference -->
100 <xsl:variable name="sortedObjectTypes">
101 <xsl:call-template name="topsort">
102 <xsl:with-param name="nodes" select="//objectType[not(reference|extends|container)]"/>
103 <xsl:with-param name="finished" select="/.."/>
104 </xsl:call-template>
105 </xsl:variable>
106
107
108
109 <!-- CREATE TABLES -->
110 <xsl:variable name="file" select="concat($vendor,'/',name,'_createTables.sql')"/>
111 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
112 <xsl:result-document href="{$file}">
113 <xsl:value-of select="$header"/>&cr;&cr;
114
115 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
116 <xsl:sort select="position()" data-type="number" order="ascending"/>
117
118 <xsl:variable name="type" select="key('element',@xmiid)"/>
119 <xsl:message>Object Type: <xsl:value-of select="$type/name"/></xsl:message>
120 <xsl:for-each select="attribute">
121 <xsl:variable name="xmiidref" select="datatype/@xmiidref"/>
122 <xsl:variable name="attrtype" select="key('element',$xmiidref)/name"/>
123 <xsl:message> Attribute : <xsl:value-of select="concat(name,':',datatype/@xmiidref,':',$attrtype)"/></xsl:message>
124 </xsl:for-each>
125 <xsl:text>------------------------------------------------------</xsl:text>&cr;
126 <xsl:text>-- Table representation of the objectType </xsl:text><xsl:value-of select="name"/>&cr;
127 <xsl:text>-- XMI-ID = </xsl:text><xsl:value-of select="@xmiid"/>&cr;
128 <xsl:text>/*-----------------------</xsl:text>&cr;
129 <xsl:value-of select="description"/>&cr;
130 <xsl:text> ------------------------*/</xsl:text>&cr;
131
132 <xsl:apply-templates select="$type" mode="createTable"/>
133
134 <xsl:apply-templates select="." mode="createIndexes"/>
135 <!-- because of topologically sorted nodes, can define the foreign keys directly after the table definition -->
136 <xsl:apply-templates select="." mode="createFKs"/>
137 </xsl:for-each>
138 </xsl:result-document>
139
140 <!-- CREATE VIEWS -->
141 <xsl:variable name="file" select="concat($vendor,'/',name,'_createViews.sql')"/>
142 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
143 <xsl:result-document href="{$file}">
144 <xsl:value-of select="$header"/>
145
146 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
147 <xsl:sort select="position()" data-type="number" order="ascending"/>
148 <xsl:apply-templates select="." mode="createView"/>
149
150 </xsl:for-each>
151 </xsl:result-document>
152
153 <!-- DROP VIEWS -->
154 <xsl:variable name="file" select="concat($vendor,'/',name,'_dropViews.sql')"/>
155 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
156 <xsl:result-document href="{$file}">
157 <xsl:value-of select="$header"/>
158
159 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
160 <xsl:sort select="position()" data-type="number" order="descending"/>
161 <xsl:apply-templates select="." mode="dropView"/>
162
163 </xsl:for-each>
164 </xsl:result-document>
165
166
167 <!-- DROP TABLES -->
168 <xsl:variable name="file" select="concat($vendor,'/',name,'_dropTables.sql')"/>
169 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
170 <xsl:result-document href="{$file}">
171 <xsl:value-of select="$header"/>
172
173 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
174 <xsl:sort select="position()" data-type="number" order="descending"/>
175 <xsl:apply-templates select="." mode="dropTable"/>
176
177 </xsl:for-each>
178 </xsl:result-document>
179
180 </xsl:template>
181
182
183
184
185
186 <xsl:template match="objectType" mode="createTable">
187 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
188 <xsl:variable name="tableName">
189 <xsl:apply-templates select="." mode="tableName"/>
190 </xsl:variable>
191 <xsl:text>CREATE TABLE </xsl:text><xsl:value-of select="$tableName"/> <xsl:text>(</xsl:text>&cr;
192 &bl;&bl;<xsl:value-of select="$primaryKeyColumnName"/><xsl:text> bigint not null</xsl:text>&cr;
193 <xsl:if test="not(extends)"><xsl:apply-templates select="." mode="discriminatorColumnDeclaration"/></xsl:if>
194 <xsl:apply-templates select="." mode="container"/>
195 <xsl:apply-templates select="attribute" />
196 <xsl:apply-templates select="reference" />);&cr;&cr;
197 </xsl:template>
198
199
200
201
202 <xsl:template match="objectType" mode="dropTable">
203 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
204 <xsl:variable name="tableName">
205 <xsl:apply-templates select="." mode="tableName"/>
206 </xsl:variable>
207 <xsl:text>DROP TABLE </xsl:text><xsl:value-of select="$tableName"/>;&cr;&cr;
208 </xsl:template>
209
210
211
212
213
214 <xsl:template match="objectType" mode="createView">
215 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
216 <xsl:variable name="tableName">
217 <xsl:apply-templates select="." mode="tableName"/>
218 </xsl:variable>
219 <xsl:variable name="viewName">
220 <xsl:apply-templates select="." mode="viewName"/>
221 </xsl:variable>
222 <xsl:variable name="base" select="key('element',extends/@xmiidref)"/>
223 <xsl:text>CREATE VIEW </xsl:text><xsl:value-of select="$viewName"/><xsl:text> AS</xsl:text>&cr;
224 <xsl:choose>
225 <xsl:when test="extends">
226 <xsl:text> SELECT b.*</xsl:text>&cr;
227 <xsl:for-each select="attribute|reference">
228 <xsl:text> , t.</xsl:text><xsl:apply-templates select="." mode="columnName"/>&cr;
229 </xsl:for-each>
230 <xsl:text> FROM </xsl:text><xsl:value-of select="$tableName"/><xsl:text> t</xsl:text>&cr;
231 <xsl:text> , </xsl:text><xsl:apply-templates select="$base" mode="viewName"/> b
232 <xsl:text> WHERE b.</xsl:text><xsl:value-of select="$primaryKeyColumnName"/><xsl:text> = t.</xsl:text><xsl:value-of select="$primaryKeyColumnName"/>;&cr;&cr;
233 </xsl:when>
234 <xsl:otherwise>
235 <xsl:text> SELECT *</xsl:text>&cr;
236 <xsl:text> FROM </xsl:text><xsl:value-of select="$tableName"/>;&cr;&cr;
237 </xsl:otherwise>
238 </xsl:choose>
239 </xsl:template>
240
241
242
243
244 <xsl:template match="objectType" mode="dropView">
245 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
246 <xsl:variable name="tableName">
247 <xsl:apply-templates select="." mode="tableName"/>
248 </xsl:variable>
249 DROP VIEW v_<xsl:value-of select="$tableName"/>;&cr;&cr;
250 </xsl:template>
251
252
253
254
255
256
257 <xsl:template match="objectType" mode="createFKs">
258 <!-- generate a foreign key for each relation -->
259 <xsl:variable name="tableName">
260 <xsl:apply-templates select="." mode="tableName"/>
261 </xsl:variable>
262 <xsl:if test="container">
263 <xsl:variable name="otherTable">
264 <xsl:apply-templates select="key('element',container/@xmiidref)" mode="tableName"/>
265 </xsl:variable>
266 <xsl:text>alter table </xsl:text><xsl:value-of select="$tableName"/> add constraint fk_<xsl:value-of select="$tableName"/>_container&cr;
267 <xsl:text> foreign key (containerId) references </xsl:text><xsl:value-of select="$otherTable"/>(<xsl:value-of select="$primaryKeyColumnName"/>);&cr;&cr;
268 </xsl:if>
269 <xsl:for-each select="reference[not(subsets)]">
270 <xsl:variable name="otherTable">
271 <xsl:apply-templates select="key('element',datatype/@xmiidref)" mode="tableName"/>
272 </xsl:variable>
273 <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;
274 <xsl:text> foreign key (</xsl:text><xsl:value-of select="name"/>Id) references <xsl:value-of select="$otherTable"/>(<xsl:value-of select="$primaryKeyColumnName"/>);&cr;&cr;
275 </xsl:for-each>
276 </xsl:template>
277
278
279
280
281 <xsl:template match="objectType" mode="dropFKs">
282 <!-- generate a foreign key for each relation -->
283 <xsl:variable name="tableName">
284 <xsl:apply-templates select="." mode="tableName"/>
285 </xsl:variable>
286 <xsl:for-each select="reference">
287 DROP FOREIGN KEY fk_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/> (<xsl:value-of select="name"/>Id);&cr;&cr;
288 </xsl:for-each>
289 </xsl:template>
290
291
292
293
294
295 <xsl:template match="objectType" mode="createIndexes">
296 <!-- generate a foreign key for each relation -->
297 <xsl:variable name="tableName">
298 <xsl:apply-templates select="." mode="tableName"/>
299 </xsl:variable>
300 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;
301
302 <xsl:for-each select="reference">
303 CREATE INDEX ix_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/> on <xsl:value-of select="$tableName"/>(<xsl:value-of select="name"/>Id);&cr;&cr;
304 </xsl:for-each>
305 </xsl:template>
306
307
308
309
310 <xsl:template match="objectType" mode="dropIndexes">
311 <!-- generate a foreign key for each relation -->
312 <xsl:variable name="tableName">
313 <xsl:apply-templates select="." mode="tableName"/>
314 </xsl:variable>
315 <xsl:for-each select="reference">
316 DROP INDEX <xsl:value-of select="$tableName"/>.ix_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/> (<xsl:value-of select="name"/>Id);&cr;&cr;
317 </xsl:for-each>
318 </xsl:template>
319
320
321
322
323 <!-- determine whether there is a class that contains this class.
324 If so generate a containerID column.
325 NOTE we should ensure that there is only 1 -->
326 <xsl:template match="objectType" mode="container">
327 <xsl:variable name="xmiid" select="@xmiid"/>
328 <xsl:if test="container">, containerId bigint not null -- <xsl:value-of select="key('element',container/@xmiidref)/name"/>&cr;
329 </xsl:if>
330 </xsl:template>
331
332
333
334
335 <xsl:template match="attribute" >
336 <xsl:param name="prefix"/>
337 <xsl:message ><xsl:value-of select="concat(name,':',datatype/@name)"/>
338 : <xsl:apply-templates select="datatype/@xmiidref" mode="classifier"/></xsl:message>
339
340 <xsl:variable name="columnname">
341 <xsl:choose>
342 <xsl:when test="$prefix">
343 <xsl:value-of select="concat($prefix,'_',name)"/>
344 </xsl:when>
345 <xsl:otherwise>
346 <xsl:value-of select="name"/>
347 </xsl:otherwise>
348 </xsl:choose>
349 </xsl:variable>
350 <xsl:variable name="type" select="key('element',datatype/@xmiidref)"/>
351 <xsl:choose>
352 <xsl:when test="name($type) != 'primitiveType'">
353 <xsl:apply-templates select="$type" mode="columns"><xsl:with-param name="prefix" select="$columnname"/></xsl:apply-templates>
354 </xsl:when>
355 <xsl:otherwise>
356 <xsl:variable name="sqltype">
357 <xsl:call-template name="sqltype">
358 <xsl:with-param name="type" select="$type"/>
359 <xsl:with-param name="constraints" select="constraints"/>
360 </xsl:call-template>
361 </xsl:variable>, <xsl:value-of select="$columnname"/>&bl;<xsl:value-of select="$sqltype"/><xsl:if test="multiplicity = '1'"> not null</xsl:if>&cr;
362 </xsl:otherwise>
363 </xsl:choose>
364
365 </xsl:template>
366
367
368
369 <!-- DEBUG template -->
370 <xsl:template match="datatype/@xmiidref" mode="classifier">
371 <xsl:variable name="type" select="key('element',.)"/>
372 <xsl:value-of select="$type/name"/>
373 </xsl:template>
374 <!-- END DEBUG -->
375
376
377
378
379 <!-- We need lengths for (var)char datatypes -->
380 <xsl:template name="sqltype">
381 <xsl:param name="type"/>
382 <xsl:param name="constraints"/>
383
384 <!--
385 Primitive types :
386 boolean
387 short
388 int
389 long
390 float
391 double
392
393 Date type :
394 datetime
395
396 Characters type :
397 string
398
399 Unsupported type (later) => string :
400 complex
401 rational
402 -->
403 <xsl:choose>
404 <xsl:when test="$type/name = 'boolean'">bit</xsl:when>
405 <xsl:when test="$type/name = 'short'">integer</xsl:when>
406 <xsl:when test="$type/name = 'integer'">integer</xsl:when>
407 <xsl:when test="$type/name = 'long'">bigint</xsl:when>
408 <xsl:when test="$type/name = 'float'">float</xsl:when>
409 <xsl:when test="$type/name = 'double'">real</xsl:when>
410 <xsl:when test="$type/name = 'datetime'">
411 <xsl:choose>
412 <xsl:when test="$vendor = 'mssqlserver'">datetime</xsl:when>
413 <xsl:otherwise>timestamp</xsl:otherwise>
414 </xsl:choose>
415 </xsl:when>
416 <xsl:otherwise>
417 <xsl:variable name="length">
418 <xsl:choose>
419 <xsl:when test="$constraints/length">
420 <xsl:value-of select="$constraints/length"/>
421 </xsl:when>
422 <xsl:when test="$constraints/maxLength">
423 <xsl:value-of select="$constraints/maxLength"/>
424 </xsl:when>
425 <xsl:otherwise>
426 <xsl:value-of select="$defaultVarcharLength"/>
427 </xsl:otherwise>
428 </xsl:choose>
429 </xsl:variable>
430 <xsl:choose>
431 <xsl:when test="$length &lt;= 0"><xsl:value-of select="$unboundedstringtype"/></xsl:when>
432 <xsl:otherwise>varchar(<xsl:value-of select="$length"/>)</xsl:otherwise>
433 </xsl:choose></xsl:otherwise>
434 </xsl:choose>
435 </xsl:template>
436
437
438
439
440 <!-- We need lengths for (var)char datatypes -->
441 <xsl:template match="dataType" mode="columns">
442 <xsl:param name="prefix"/>
443 <xsl:message><xsl:value-of select="$prefix"/>_<xsl:value-of select="name"/></xsl:message>
444 <xsl:choose>
445 <xsl:when test="not(attribute)">, <xsl:value-of select="$prefix"/> varchar(256) -- <xsl:value-of select="name"/>&cr;</xsl:when>
446 <xsl:otherwise><xsl:apply-templates select="attribute"><xsl:with-param name="prefix" select="$prefix"/></xsl:apply-templates></xsl:otherwise>
447 </xsl:choose>
448 </xsl:template>
449
450
451
452
453 <!-- We need lengths for (var)char datatypes -->
454 <xsl:template match="enumeration" mode="columns">
455 <xsl:param name="prefix"/>, <xsl:value-of select="$prefix"/> varchar(256) -- <xsl:value-of select="name"/>&cr;
456 </xsl:template>
457
458
459
460
461 <xsl:template match="reference">
462 <xsl:if test="not(subsets)">, <xsl:value-of select="name"/>Id&bl;bigint <xsl:call-template name="nullity"><xsl:with-param name="multiplicity" select="multiplicity"/></xsl:call-template> -- <xsl:value-of select="key('element',datatype/@xmiidref)/name"/>&cr;
463 </xsl:if>
464 </xsl:template>
465
466
467
468
469 <xsl:template name="nullity">
470 <xsl:param name="multiplicity"/>
471 <xsl:choose>
472 <xsl:when test="$multiplicity = '1' or $multiplicity = '1..*'">not null</xsl:when>
473 <xsl:otherwise>null</xsl:otherwise>
474 </xsl:choose>
475 </xsl:template>
476
477
478
479
480
481 <!-- TODO
482 Add templates retrieving for a given objectType the table it is in
483 for a given attribute the column(s) it is in
484 for a given reference the column it is in
485 for a given collection the containerId column it is in.
486 -->
487
488
489
490 <!-- Maybe somewhat too much indirection here? -->
491 <xsl:template match="objectType" mode="PK_COLUMN">
492 <xsl:value-of select="$primaryKeyColumnName"/> bigint not null
493 </xsl:template>
494
495
496
497
498 <!-- Discriminator column templates -->
499 <xsl:template match="objectType" mode="discriminatorColumnDeclaration">, <xsl:value-of select="$discriminatorColumnName"/> <xsl:value-of select="$discriminatorColumnType"/>&cr;
500 </xsl:template>
501
502
503
504
505 </xsl:stylesheet>

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