/[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 435 - (show annotations)
Fri May 9 16:17:58 2008 UTC (13 years ago) by gerard.lemson
File MIME type: application/xml
File size: 18438 byte(s)
Views do not have v_ in front of their names, tables have t_ as prefix. The views are the public interface to the world.
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 input objectType-s -->
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 <xsl:template match="@*|node()" mode="dump">
94 <xsl:copy>
95 <xsl:apply-templates select="@*|node()" mode="dump"/>
96 </xsl:copy>
97 </xsl:template>
98
99
100
101
102 <xsl:template match="model">
103 <xsl:message>Model = <xsl:value-of select="name"></xsl:value-of></xsl:message>
104 -- Generating DDLs for model <xsl:value-of select="name"/> and DB vendor <xsl:value-of select="$vendor"/>.
105 <xsl:value-of select="$header"/>
106
107 <!-- create object types sorted according to foreign key graph derived from container, inheritance and reference -->
108 <xsl:variable name="sortedObjectTypes">
109 <!-- add non-object types separately -->
110 <xsl:for-each select="//primitiveType|//dataType|//enumeration">
111 <vt><xsl:copy-of select="."/></vt>
112 </xsl:for-each>
113 <xsl:call-template name="topsort">
114 <!-- need to add the value types as well as the objecttypes, otherwise when in the context -->
115 <xsl:with-param name="nodes" select="//objectType[not(reference|extends|container)]"/>
116 <xsl:with-param name="finished" select="/.."/>
117 </xsl:call-template>
118 </xsl:variable>
119
120
121 <!-- CREATE TABLES -->
122 <xsl:variable name="file" select="concat($vendor,'/',name,'_createTables.sql')"/>
123 <xsl:message >Opening file <xsl:value-of select="$file"/></xsl:message>
124 <xsl:result-document href="{$file}">
125 <xsl:value-of select="$header"/>&cr;&cr;
126
127 <xsl:for-each select="exsl:node-set($sortedObjectTypes)/node/objectType">
128 <xsl:sort select="position()" data-type="number" order="ascending"/>
129
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
222 <xsl:template match="objectType" mode="createView">
223 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
224 <xsl:variable name="tableName">
225 <xsl:apply-templates select="." mode="tableName"/>
226 </xsl:variable>
227 <xsl:variable name="viewName">
228 <xsl:apply-templates select="." mode="viewName"/>
229 </xsl:variable>
230 <xsl:variable name="base" select="key('element',extends/@xmiidref)"/>
231 <xsl:text>CREATE VIEW </xsl:text><xsl:value-of select="$viewName"/><xsl:text> AS</xsl:text>&cr;
232 <xsl:choose>
233 <xsl:when test="extends">
234 <xsl:text> SELECT b.*</xsl:text>&cr;
235 <xsl:for-each select="attribute">
236 <xsl:variable name="columns">
237 <xsl:apply-templates select="." mode="columns"/>
238 </xsl:variable>
239 <xsl:for-each select="exsl:node-set($columns)/column">
240 <xsl:text> , t.</xsl:text><xsl:value-of select="name"/>&cr;
241 </xsl:for-each>
242 </xsl:for-each>
243 <xsl:for-each select="reference[not(subsets)]">
244 <xsl:text> , t.</xsl:text><xsl:apply-templates select="." mode="columnName"/>&cr;
245 </xsl:for-each>
246 <xsl:text> FROM </xsl:text><xsl:value-of select="$tableName"/><xsl:text> t</xsl:text>&cr;
247 <xsl:text> , </xsl:text><xsl:apply-templates select="$base" mode="viewName"/> b
248 <xsl:text> WHERE b.</xsl:text><xsl:value-of select="$primaryKeyColumnName"/><xsl:text> = t.</xsl:text><xsl:value-of select="$primaryKeyColumnName"/>
249 </xsl:when>
250 <xsl:otherwise>
251 <xsl:text> SELECT *</xsl:text>&cr;
252 <xsl:text> FROM </xsl:text><xsl:value-of select="$tableName"/>
253 </xsl:otherwise>
254 </xsl:choose>
255 <xsl:choose>
256 <xsl:when test="$vendor='mssqlserver'">
257 &cr;<xsl:text>GO</xsl:text>
258 </xsl:when>
259 <xsl:otherwise>
260 <xsl:text>;</xsl:text>
261 </xsl:otherwise>
262 </xsl:choose>
263 &cr;&cr;
264 </xsl:template>
265
266
267
268
269 <xsl:template match="objectType" mode="dropView">
270 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
271 <xsl:variable name="viewName">
272 <xsl:apply-templates select="." mode="viewName"/>
273 </xsl:variable>
274 <xsl:text>DROP VIEW </xsl:text><xsl:value-of select="$viewName"/>;&cr;&cr;
275 </xsl:template>
276
277
278
279
280
281
282 <xsl:template match="objectType" mode="createFKs">
283 <!-- generate a foreign key for each relation -->
284 <xsl:variable name="tableName">
285 <xsl:apply-templates select="." mode="tableName"/>
286 </xsl:variable>
287 <xsl:if test="container">
288 <xsl:variable name="otherTable">
289 <xsl:apply-templates select="key('element',container/@xmiidref)" mode="tableName"/>
290 </xsl:variable>
291 <xsl:text>alter table </xsl:text><xsl:value-of select="$tableName"/> add constraint fk_<xsl:value-of select="$tableName"/>_container&cr;
292 <xsl:text> foreign key (containerId) references </xsl:text><xsl:value-of select="$otherTable"/>(<xsl:value-of select="$primaryKeyColumnName"/>);&cr;&cr;
293 </xsl:if>
294 <xsl:for-each select="reference[not(subsets)]">
295 <xsl:variable name="otherTable">
296 <xsl:apply-templates select="key('element',datatype/@xmiidref)" mode="tableName"/>
297 </xsl:variable>
298 <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;
299 <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;
300 </xsl:for-each>
301 </xsl:template>
302
303
304
305
306 <xsl:template match="objectType" mode="dropFKs">
307 <!-- generate a foreign key for each relation -->
308 <xsl:variable name="tableName">
309 <xsl:apply-templates select="." mode="tableName"/>
310 </xsl:variable>
311 <xsl:for-each select="reference">
312 DROP FOREIGN KEY fk_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/> (<xsl:apply-templates select="." mode="columnName"/>);&cr;&cr;
313 </xsl:for-each>
314 </xsl:template>
315
316
317
318
319
320 <xsl:template match="objectType" mode="createIndexes">
321 <!-- generate a foreign key for each relation -->
322 <xsl:variable name="tableName">
323 <xsl:apply-templates select="." mode="tableName"/>
324 </xsl:variable>
325 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;
326
327 <xsl:for-each select="reference[not(subsets)]">
328 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;
329 </xsl:for-each>
330 </xsl:template>
331
332
333
334
335 <xsl:template match="objectType" mode="dropIndexes">
336 <!-- generate a foreign key for each relation -->
337 <xsl:variable name="tableName">
338 <xsl:apply-templates select="." mode="tableName"/>
339 </xsl:variable>
340 <xsl:for-each select="reference">
341 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;
342 </xsl:for-each>
343 </xsl:template>
344
345
346
347
348 <!-- determine whether there is a class that contains this class.
349 If so generate a containerID column.
350 NOTE we should ensure that there is only 1 -->
351 <xsl:template match="objectType" mode="container">
352 <xsl:variable name="xmiid" select="@xmiid"/>
353 <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;
354 </xsl:if>
355 </xsl:template>
356
357
358
359 <xsl:template match="attribute">
360 <xsl:variable name="columns">
361 <xsl:apply-templates select="." mode="columns"/>
362 </xsl:variable>
363 <xsl:for-each select="exsl:node-set($columns)/column">
364 <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;
365 </xsl:for-each>
366 </xsl:template>
367
368
369
370
371 <xsl:template match="attribute" mode="old">
372 <xsl:param name="prefix"/>
373
374 <xsl:variable name="columnname">
375 <xsl:choose>
376 <xsl:when test="$prefix">
377 <xsl:value-of select="concat($prefix,'_',name)"/>
378 </xsl:when>
379 <xsl:otherwise>
380 <xsl:value-of select="name"/>
381 </xsl:otherwise>
382 </xsl:choose>
383 </xsl:variable>
384 <xsl:variable name="type" select="key('element',datatype/@xmiidref)"/>
385 <xsl:choose>
386 <xsl:when test="name($type) != 'primitiveType'">
387 <xsl:apply-templates select="$type" mode="columns"><xsl:with-param name="prefix" select="$columnname"/></xsl:apply-templates>
388 </xsl:when>
389 <xsl:otherwise>
390 <xsl:variable name="sqltype">
391 <xsl:call-template name="sqltype">
392 <xsl:with-param name="type" select="$type"/>
393 <xsl:with-param name="constraints" select="constraints"/>
394 </xsl:call-template>
395 </xsl:variable>, <xsl:value-of select="$columnname"/>&bl;<xsl:value-of select="$sqltype"/><xsl:if test="multiplicity = '1'"> not null</xsl:if>&cr;
396 </xsl:otherwise>
397 </xsl:choose>
398
399 </xsl:template>
400
401
402
403 <!-- DEBUG template -->
404 <xsl:template match="datatype/@xmiidref" mode="classifier">
405 <xsl:variable name="type" select="key('element',.)"/>
406 <xsl:value-of select="$type/name"/>
407 </xsl:template>
408 <!-- END DEBUG -->
409
410
411
412
413
414
415 <!-- We need lengths for (var)char datatypes -->
416 <xsl:template match="dataType" mode="columns">
417 <xsl:param name="prefix"/>
418 <xsl:choose>
419 <xsl:when test="not(attribute)">, <xsl:value-of select="$prefix"/> varchar(256) -- <xsl:value-of select="name"/>&cr;</xsl:when>
420 <xsl:otherwise><xsl:apply-templates select="attribute"><xsl:with-param name="prefix" select="$prefix"/></xsl:apply-templates></xsl:otherwise>
421 </xsl:choose>
422 </xsl:template>
423
424
425
426
427 <!-- We need lengths for (var)char datatypes -->
428 <xsl:template match="enumeration" mode="columns">
429 <xsl:param name="prefix"/>, <xsl:value-of select="$prefix"/> varchar(256) -- <xsl:value-of select="name"/>&cr;
430 </xsl:template>
431
432
433
434
435 <xsl:template match="reference">
436 <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;
437 </xsl:if>
438 </xsl:template>
439
440
441
442
443 <xsl:template name="nullity">
444 <xsl:param name="multiplicity"/>
445 <xsl:choose>
446 <xsl:when test="$multiplicity = '1' or $multiplicity = '1..*'">not null</xsl:when>
447 <xsl:otherwise>null</xsl:otherwise>
448 </xsl:choose>
449 </xsl:template>
450
451
452
453
454
455 <!-- TODO
456 Add templates retrieving for a given objectType the table it is in
457 for a given attribute the column(s) it is in
458 for a given reference the column it is in
459 for a given collection the containerId column it is in.
460 -->
461
462
463
464 <!-- Maybe somewhat too much indirection here? -->
465 <xsl:template match="objectType" mode="PK_COLUMN">
466 <xsl:value-of select="concat($primaryKeyColumnName,' ',$IDDatatype)"/><xsl:text> not null</xsl:text>
467 </xsl:template>
468
469
470
471
472 <!-- Discriminator column templates -->
473 <xsl:template match="objectType" mode="discriminatorColumnDeclaration">, <xsl:value-of select="$discriminatorColumnName"/> <xsl:value-of select="$discriminatorColumnType"/>&cr;
474 </xsl:template>
475
476
477
478
479 </xsl:stylesheet>

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