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

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