/[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 422 - (show annotations)
Thu May 8 16:52:53 2008 UTC (12 years, 7 months ago) by gerard.lemson
File MIME type: application/xml
File size: 17515 byte(s)
Started work on properly (?) mapping embedded datatypes.
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="$primaryKeyColumnName"/><xsl:text> bigint 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|reference">
236 <xsl:text> , t.</xsl:text><xsl:apply-templates select="." mode="columnName"/>&cr;
237 </xsl:for-each>
238 <xsl:text> FROM </xsl:text><xsl:value-of select="$tableName"/><xsl:text> t</xsl:text>&cr;
239 <xsl:text> , </xsl:text><xsl:apply-templates select="$base" mode="viewName"/> b
240 <xsl:text> WHERE b.</xsl:text><xsl:value-of select="$primaryKeyColumnName"/><xsl:text> = t.</xsl:text><xsl:value-of select="$primaryKeyColumnName"/>;&cr;&cr;
241 </xsl:when>
242 <xsl:otherwise>
243 <xsl:text> SELECT *</xsl:text>&cr;
244 <xsl:text> FROM </xsl:text><xsl:value-of select="$tableName"/>;&cr;&cr;
245 </xsl:otherwise>
246 </xsl:choose>
247 </xsl:template>
248
249
250
251
252 <xsl:template match="objectType" mode="dropView">
253 <!-- generate a single table for the whole object hierarchy below the matched objectType -->
254 <xsl:variable name="tableName">
255 <xsl:apply-templates select="." mode="tableName"/>
256 </xsl:variable>
257 DROP VIEW v_<xsl:value-of select="$tableName"/>;&cr;&cr;
258 </xsl:template>
259
260
261
262
263
264
265 <xsl:template match="objectType" mode="createFKs">
266 <!-- generate a foreign key for each relation -->
267 <xsl:variable name="tableName">
268 <xsl:apply-templates select="." mode="tableName"/>
269 </xsl:variable>
270 <xsl:if test="container">
271 <xsl:variable name="otherTable">
272 <xsl:apply-templates select="key('element',container/@xmiidref)" mode="tableName"/>
273 </xsl:variable>
274 <xsl:text>alter table </xsl:text><xsl:value-of select="$tableName"/> add constraint fk_<xsl:value-of select="$tableName"/>_container&cr;
275 <xsl:text> foreign key (containerId) references </xsl:text><xsl:value-of select="$otherTable"/>(<xsl:value-of select="$primaryKeyColumnName"/>);&cr;&cr;
276 </xsl:if>
277 <xsl:for-each select="reference[not(subsets)]">
278 <xsl:variable name="otherTable">
279 <xsl:apply-templates select="key('element',datatype/@xmiidref)" mode="tableName"/>
280 </xsl:variable>
281 <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;
282 <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;
283 </xsl:for-each>
284 </xsl:template>
285
286
287
288
289 <xsl:template match="objectType" mode="dropFKs">
290 <!-- generate a foreign key for each relation -->
291 <xsl:variable name="tableName">
292 <xsl:apply-templates select="." mode="tableName"/>
293 </xsl:variable>
294 <xsl:for-each select="reference">
295 DROP FOREIGN KEY fk_<xsl:value-of select="$tableName"/>_<xsl:value-of select="name"/> (<xsl:value-of select="name"/>Id);&cr;&cr;
296 </xsl:for-each>
297 </xsl:template>
298
299
300
301
302
303 <xsl:template match="objectType" mode="createIndexes">
304 <!-- generate a foreign key for each relation -->
305 <xsl:variable name="tableName">
306 <xsl:apply-templates select="." mode="tableName"/>
307 </xsl:variable>
308 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;
309
310 <xsl:for-each select="reference[not(subsets)]">
311 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;
312 </xsl:for-each>
313 </xsl:template>
314
315
316
317
318 <xsl:template match="objectType" mode="dropIndexes">
319 <!-- generate a foreign key for each relation -->
320 <xsl:variable name="tableName">
321 <xsl:apply-templates select="." mode="tableName"/>
322 </xsl:variable>
323 <xsl:for-each select="reference">
324 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;
325 </xsl:for-each>
326 </xsl:template>
327
328
329
330
331 <!-- determine whether there is a class that contains this class.
332 If so generate a containerID column.
333 NOTE we should ensure that there is only 1 -->
334 <xsl:template match="objectType" mode="container">
335 <xsl:variable name="xmiid" select="@xmiid"/>
336 <xsl:if test="container">, containerId bigint not null -- <xsl:value-of select="key('element',container/@xmiidref)/name"/>&cr;
337 </xsl:if>
338 </xsl:template>
339
340
341
342 <xsl:template match="attribute">
343 <xsl:variable name="columns">
344 <xsl:apply-templates select="." mode="columns"/>
345 </xsl:variable>
346 <xsl:for-each select="exsl:node-set($columns)/column">
347 <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;
348 </xsl:for-each>
349 </xsl:template>
350
351
352
353
354 <xsl:template match="attribute" mode="old">
355 <xsl:param name="prefix"/>
356
357 <xsl:variable name="columnname">
358 <xsl:choose>
359 <xsl:when test="$prefix">
360 <xsl:value-of select="concat($prefix,'_',name)"/>
361 </xsl:when>
362 <xsl:otherwise>
363 <xsl:value-of select="name"/>
364 </xsl:otherwise>
365 </xsl:choose>
366 </xsl:variable>
367 <xsl:variable name="type" select="key('element',datatype/@xmiidref)"/>
368 <xsl:choose>
369 <xsl:when test="name($type) != 'primitiveType'">
370 <xsl:apply-templates select="$type" mode="columns"><xsl:with-param name="prefix" select="$columnname"/></xsl:apply-templates>
371 </xsl:when>
372 <xsl:otherwise>
373 <xsl:variable name="sqltype">
374 <xsl:call-template name="sqltype">
375 <xsl:with-param name="type" select="$type"/>
376 <xsl:with-param name="constraints" select="constraints"/>
377 </xsl:call-template>
378 </xsl:variable>, <xsl:value-of select="$columnname"/>&bl;<xsl:value-of select="$sqltype"/><xsl:if test="multiplicity = '1'"> not null</xsl:if>&cr;
379 </xsl:otherwise>
380 </xsl:choose>
381
382 </xsl:template>
383
384
385
386 <!-- DEBUG template -->
387 <xsl:template match="datatype/@xmiidref" mode="classifier">
388 <xsl:variable name="type" select="key('element',.)"/>
389 <xsl:value-of select="$type/name"/>
390 </xsl:template>
391 <!-- END DEBUG -->
392
393
394
395
396
397
398 <!-- We need lengths for (var)char datatypes -->
399 <xsl:template match="dataType" mode="columns">
400 <xsl:param name="prefix"/>
401 <xsl:choose>
402 <xsl:when test="not(attribute)">, <xsl:value-of select="$prefix"/> varchar(256) -- <xsl:value-of select="name"/>&cr;</xsl:when>
403 <xsl:otherwise><xsl:apply-templates select="attribute"><xsl:with-param name="prefix" select="$prefix"/></xsl:apply-templates></xsl:otherwise>
404 </xsl:choose>
405 </xsl:template>
406
407
408
409
410 <!-- We need lengths for (var)char datatypes -->
411 <xsl:template match="enumeration" mode="columns">
412 <xsl:param name="prefix"/>, <xsl:value-of select="$prefix"/> varchar(256) -- <xsl:value-of select="name"/>&cr;
413 </xsl:template>
414
415
416
417
418 <xsl:template match="reference">
419 <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;
420 </xsl:if>
421 </xsl:template>
422
423
424
425
426 <xsl:template name="nullity">
427 <xsl:param name="multiplicity"/>
428 <xsl:choose>
429 <xsl:when test="$multiplicity = '1' or $multiplicity = '1..*'">not null</xsl:when>
430 <xsl:otherwise>null</xsl:otherwise>
431 </xsl:choose>
432 </xsl:template>
433
434
435
436
437
438 <!-- TODO
439 Add templates retrieving for a given objectType the table it is in
440 for a given attribute the column(s) it is in
441 for a given reference the column it is in
442 for a given collection the containerId column it is in.
443 -->
444
445
446
447 <!-- Maybe somewhat too much indirection here? -->
448 <xsl:template match="objectType" mode="PK_COLUMN">
449 <xsl:value-of select="$primaryKeyColumnName"/> bigint not null
450 </xsl:template>
451
452
453
454
455 <!-- Discriminator column templates -->
456 <xsl:template match="objectType" mode="discriminatorColumnDeclaration">, <xsl:value-of select="$discriminatorColumnName"/> <xsl:value-of select="$discriminatorColumnType"/>&cr;
457 </xsl:template>
458
459
460
461
462 </xsl:stylesheet>

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