1/**
2 * Parse SQL CREATE TABLE. Simple initial version for community to improve.
3 */
4Draw.loadPlugin(function(ui) {
5
6    function TableModel() {
7        this.Name = null;
8        this.Properties = []
9    }
10
11    function PropertyModel() {
12        this.Name = null;
13        this.Value = null;
14        this.TableName = null;
15        this.ForeignKey = [];
16        this.IsPrimaryKey = false;
17        this.IsForeignKey = false;
18    }
19
20    function ForeignKeyModel() {
21        this.PrimaryKeyName = null;
22        this.ReferencesPropertyName = null
23
24        this.PrimaryKeyTableName = null;
25        this.ReferencesTableName = null;
26
27        this.IsDestination = false;
28    }
29
30    function PrimaryKeyModel() {
31        this.PrimaryKeyName = null;
32        this.PrimaryKeyTableName = null;
33    }
34
35    //SQL Types
36    var SQLServer = 'sqlserver';
37
38    //SQL Modes
39    var MODE_SQLSERVER = null;
40
41    //Table Info
42    var foreignKeyList = [];
43    var primaryKeyList = [];
44    var tableList = [];
45    var cells = [];
46    var tableCell = null;
47    var rowCell = null;
48    var dx = 0;
49    var exportedTables = 0;
50
51
52    //Create Base div
53    var div = document.createElement('div');
54    div.style.userSelect = 'none';
55    div.style.overflow = 'hidden';
56    div.style.padding = '10px';
57    div.style.height = '100%';
58
59    var graph = ui.editor.graph;
60
61    var sqlInput = document.createElement('textarea');
62    sqlInput.style.height = '200px';
63    sqlInput.style.width = '100%';
64    sqlInput.value = 'CREATE TABLE Persons\n(\nPersonID int,\nLastName varchar(255),\n' +
65        'FirstName varchar(255),\nAddress varchar(255),\nCity varchar(255)\n);';
66    mxUtils.br(div);
67    div.appendChild(sqlInput);
68
69    var graph = ui.editor.graph;
70
71    // Extends Extras menu
72    mxResources.parse('fromSql=From SQL');
73
74    var wnd = new mxWindow(mxResources.get('fromSql'), div, document.body.offsetWidth - 480, 140,
75        320, 300, true, true);
76    wnd.destroyOnClose = false;
77    wnd.setMaximizable(false);
78    wnd.setResizable(false);
79    wnd.setClosable(true);
80
81    function AddRow(propertyModel, tableName) {
82
83        var cellName = propertyModel.Name;
84
85        if (propertyModel.IsForeignKey && propertyModel.ForeignKey !== undefined && propertyModel.ForeignKey !== null) {
86            propertyModel.ForeignKey.forEach(function(foreignKeyModel) {
87
88                //We do not want the foreign key to be duplicated in our table to the same property
89                if (tableName !== foreignKeyModel.PrimaryKeyTableName || (tableName === foreignKeyModel.PrimaryKeyTableName && propertyModel.Name !== foreignKeyModel.PrimaryKeyName)) {
90                    cellName += ' | ' + foreignKeyModel.PrimaryKeyTableName + '(' + foreignKeyModel.PrimaryKeyName + ')';
91                }
92            })
93        }
94
95        rowCell = new mxCell(cellName, new mxGeometry(0, 0, 90, 26),
96            'shape=partialRectangle;top=0;left=0;right=0;bottom=0;align=left;verticalAlign=top;spacingTop=-2;fillColor=none;spacingLeft=64;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;dropTarget=0;');
97        rowCell.vertex = true;
98
99        var columnType = propertyModel.IsPrimaryKey && propertyModel.IsForeignKey ? 'PK | FK' : propertyModel.IsPrimaryKey ? 'PK' : propertyModel.IsForeignKey ? 'FK' : '';
100
101        var left = sb.cloneCell(rowCell, columnType);
102        left.connectable = false;
103        left.style = 'shape=partialRectangle;top=0;left=0;bottom=0;fillColor=none;align=left;verticalAlign=middle;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=180;points=[];portConstraint=eastwest;part=1;'
104        left.geometry.width = 54;
105        left.geometry.height = 26;
106        rowCell.insert(left);
107
108        var size = ui.editor.graph.getPreferredSizeForCell(rowCell);
109
110        if (size !== null && tableCell.geometry.width < size.width + 10) {
111            tableCell.geometry.width = size.width + 10;
112        }
113
114        tableCell.insert(rowCell);
115        tableCell.geometry.height += 26;
116
117        rowCell = rowCell;
118
119    };
120
121    function ParseMySQLForeignKey(name, currentTableModel) {
122        var referencesIndex = name.toLowerCase().indexOf("references");
123        var foreignKeySQL = name.substring(0, referencesIndex);
124        var referencesSQL = name.substring(referencesIndex, name.length);
125
126        //Remove references syntax
127        referencesSQL = referencesSQL.replace("REFERENCES ", '');
128
129        //Get Table and Property Index
130        var referencedTableIndex = referencesSQL.indexOf("(");
131        var referencedPropertyIndex = referencesSQL.indexOf(")");
132
133        //Get Referenced Table
134        var referencedTableName = referencesSQL.substring(0, referencedTableIndex);
135
136        //Get Referenced Key
137        var referencedPropertyName = referencesSQL.substring(referencedTableIndex + 1, referencedPropertyIndex);
138
139        //Get ForeignKey
140        var foreignKey = foreignKeySQL.replace("FOREIGN KEY (", '').replace(")", '').replace(" ", '');
141
142        //Create ForeignKey
143        var foreignKeyOriginModel = CreateForeignKey(foreignKey, currentTableModel.Name, referencedPropertyName, referencedTableName, true);
144
145        //Add ForeignKey Origin
146        foreignKeyList.push(foreignKeyOriginModel);
147
148        //Create ForeignKey
149        var foreignKeyDestinationModel = CreateForeignKey(referencedPropertyName, referencedTableName, foreignKey, currentTableModel.Name, false);
150
151        //Add ForeignKey Destination
152        foreignKeyList.push(foreignKeyDestinationModel);
153    };
154
155    function ParseSQLServerForeignKey(name, currentTableModel) {
156        var referencesIndex = name.toLowerCase().indexOf("references");
157
158        if (name.toLowerCase().indexOf("foreign key(") !== -1) {
159            var foreignKeySQL = name.substring(name.toLowerCase().indexOf("foreign key("), referencesIndex).replace("FOREIGN KEY(", '').replace(')', '');
160        } else {
161            var foreignKeySQL = name.substring(name.toLowerCase().indexOf("foreign key ("), referencesIndex).replace("FOREIGN KEY (", '').replace(')', '');
162        }
163
164        var referencesSQL = name.substring(referencesIndex, name.length);
165        var alterTableName = name.substring(0, name.indexOf("WITH")).replace('ALTER TABLE ', '');
166
167        if (referencesIndex !== -1 && alterTableName !== '' && foreignKeySQL !== '' && referencesSQL !== '') {
168
169            //Remove references syntax
170            referencesSQL = referencesSQL.replace("REFERENCES ", '');
171
172            //Get Table and Property Index
173            var referencedTableIndex = referencesSQL.indexOf("(");
174            var referencedPropertyIndex = referencesSQL.indexOf(")");
175
176            //Get Referenced Table
177            var referencedTableName = referencesSQL.substring(0, referencedTableIndex);
178
179            //Parse Name
180            referencedTableName = ParseSQLServerName(referencedTableName);
181
182            //Get Referenced Key
183            var referencedPropertyName = referencesSQL.substring(referencedTableIndex + 1, referencedPropertyIndex);
184
185            //Parse Name
186            referencedPropertyName = ParseSQLServerName(referencedPropertyName);
187
188            //Get ForeignKey
189            var foreignKey = foreignKeySQL.replace("FOREIGN KEY (", '').replace(")", '');
190
191            //Parse Name
192            foreignKey = ParseSQLServerName(foreignKey);
193
194            //Parse Name
195            alterTableName = ParseSQLServerName(alterTableName);
196
197            //Create ForeignKey
198            var foreignKeyOriginModel = CreateForeignKey(foreignKey, alterTableName, referencedPropertyName, referencedTableName, true);
199
200            //Add ForeignKey Origin
201            foreignKeyList.push(foreignKeyOriginModel);
202
203            //Create ForeignKey
204            var foreignKeyDestinationModel = CreateForeignKey(referencedPropertyName, referencedTableName, foreignKey, alterTableName, false);
205
206            //Add ForeignKey Destination
207            foreignKeyList.push(foreignKeyDestinationModel);
208        }
209    };
210
211    function ProcessPrimaryKey() {
212
213        primaryKeyList.forEach(function(primaryModel) {
214            tableList.forEach(function(tableModel) {
215                if (tableModel.Name === primaryModel.PrimaryKeyTableName) {
216                    tableModel.Properties.forEach(function(propertyModel) {
217                        if (propertyModel.Name === primaryModel.PrimaryKeyName) {
218                            propertyModel.IsPrimaryKey = true;
219                        }
220                    });
221                }
222            });
223        });
224    }
225
226    function AssignForeignKey(foreignKeyModel) {
227        tableList.forEach(function(tableModel) {
228            if (tableModel.Name === foreignKeyModel.ReferencesTableName) {
229                tableModel.Properties.forEach(function(propertyModel) {
230                    if (propertyModel.Name === foreignKeyModel.ReferencesPropertyName) {
231                        propertyModel.IsForeignKey = true;
232                        propertyModel.ForeignKey.push(foreignKeyModel);
233                    }
234                });
235            }
236
237            if (tableModel.Name === foreignKeyModel.PrimaryKeyTableName) {
238                tableModel.Properties.forEach(function(propertyModel) {
239                    if (propertyModel.Name === foreignKeyModel.PrimaryKeyName) {
240                        propertyModel.IsForeignKey = true;
241                        propertyModel.ForeignKey.push(foreignKeyModel);
242                    }
243                });
244            }
245        });
246    }
247
248    function ProcessForeignKey() {
249
250        foreignKeyList.forEach(function(foreignKeyModel) {
251            //Assign ForeignKey
252            AssignForeignKey(foreignKeyModel);
253        });
254    }
255
256    function CreateForeignKey(primaryKeyName, primaryKeyTableName, referencesPropertyName, referencesTableName, isDestination) {
257        var foreignKey = new ForeignKeyModel;
258
259        foreignKey.PrimaryKeyTableName = primaryKeyTableName;
260        foreignKey.PrimaryKeyName = primaryKeyName;
261        foreignKey.ReferencesPropertyName = referencesPropertyName;
262        foreignKey.ReferencesTableName = referencesTableName;
263        foreignKey.IsDestination = (isDestination !== undefined && isDestination !== null) ? isDestination : false;
264
265        return foreignKey;
266    };
267
268    function CreatePrimaryKey(primaryKeyName, primaryKeyTableName) {
269        var primaryKey = new PrimaryKeyModel;
270
271        primaryKey.PrimaryKeyTableName = primaryKeyTableName;
272        primaryKey.PrimaryKeyName = primaryKeyName;
273
274        return primaryKey;
275    };
276
277    function CreateProperty(name, tableName, foreignKey, isPrimaryKey) {
278        var property = new PropertyModel;
279        var isForeignKey = foreignKey !== undefined && foreignKey !== null;
280
281        property.Name = name;
282        property.TableName = tableName;
283        property.ForeignKey = isForeignKey ? foreignKey : [];
284        property.IsForeignKey = isForeignKey;
285        property.IsPrimaryKey = isPrimaryKey;
286
287        return property;
288    };
289
290    function CreateTable(name) {
291        var table = new TableModel;
292
293        table.Name = name;
294
295        //Count exported tables
296        exportedTables++;
297
298        return table;
299    };
300
301    function ParseSQLServerName(name, property) {
302        name = name.replace('[dbo].[', '');
303        name = name.replace('](', '');
304        name = name.replace('].[', '.');
305        name = name.replace('[', '');
306
307        if (property == undefined || property == null) {
308            name = name.replace(' [', '');
309            name = name.replace('] ', '');
310        } else {
311            if (name.indexOf(']') !== -1) {
312                name = name.substring(0, name.indexOf(']'));
313            }
314        }
315
316        if (name.lastIndexOf(']') === (name.length - 1)) {
317            name = name.substring(0, name.length - 1);
318        }
319
320        if (name.lastIndexOf(')') === (name.length - 1)) {
321            name = name.substring(0, name.length - 1);
322        }
323
324        if (name.lastIndexOf('(') === (name.length - 1)) {
325            name = name.substring(0, name.length - 1);
326        }
327
328        name = name.replace(' ', '');
329
330        return name;
331    };
332
333    function ParseTableName(name) {
334        if (name.charAt(name.length - 1) === '(') {
335            if (!MODE_SQLSERVER) {
336                name = name.substring(0, name.lastIndexOf(' '));
337            } else {
338                name = ParseSQLServerName(name);
339            }
340        }
341
342        return name;
343    };
344
345    function parseSql(text, type) {
346        var lines = text.split('\n');
347        dx = 0;
348        MODE_SQLSERVER = type !== undefined && type !== null && type == SQLServer;
349
350        tableCell = null;
351        cells = [];
352        exportedTables = 0;
353        tableList = [];
354        foreignKeyList = [];
355
356        var currentTableModel = null;
357
358        //Parse SQL to objects
359        for (var i = 0; i < lines.length; i++) {
360
361            rowCell = null;
362
363            var tmp = mxUtils.trim(lines[i]);
364
365            var propertyRow = tmp.substring(0, 12).toLowerCase();
366
367            //Parse Table
368            if (propertyRow === 'create table') {
369
370                //Parse row
371                var name = mxUtils.trim(tmp.substring(12));
372
373                //Parse Table Name
374                name = ParseTableName(name);
375
376                if (currentTableModel !== null) {
377                    //Add table to the list
378                    tableList.push(currentTableModel);
379                }
380
381                //Create Table
382                currentTableModel = CreateTable(name);
383            }
384            // Parse Properties
385            else if (tmp !== '(' && currentTableModel != null && propertyRow !== 'alter table ') {
386
387                //Parse the row
388                var name = tmp.substring(0, (tmp.charAt(tmp.length - 1) === ',') ? tmp.length - 1 : tmp.length);
389
390                //Attempt to get the Key Type
391                var propertyType = name.substring(0, 11).toLowerCase();
392
393                //Add special constraints
394                if (MODE_SQLSERVER) {
395                    if (tmp.indexOf("CONSTRAINT") !== -1 && tmp.indexOf("PRIMARY KEY") !== -1) {
396                        propertyType = "constrain primary key";
397                    }
398
399                    if (tmp.indexOf("CONSTRAINT") !== -1 && tmp.indexOf("FOREIGN KEY") !== -1) {
400                        propertyType = "constrain foreign key";
401                    }
402                }
403
404                //Verify if this is a property that doesn't have a relationship (One minute of silence for the property)
405                var normalProperty = propertyType !== 'primary key' && propertyType !== 'foreign key' && propertyType !== 'constrain primary key' && propertyType !== 'constrain foreign key';
406
407                //Parse properties that don't have relationships
408                if (normalProperty) {
409
410                    if (name === '' || name === "" || name === ");") {
411                        continue;
412                    }
413
414                    if (MODE_SQLSERVER) {
415                        if (name.indexOf("ASC") !== -1 ||
416                            name.indexOf("DESC") !== -1 ||
417                            name.indexOf("EXEC") !== -1 ||
418                            name.indexOf("WITH") !== -1 ||
419                            name.indexOf("ON") !== -1 ||
420                            name.indexOf("ALTER") !== -1 ||
421                            name.indexOf("/*") !== -1 ||
422                            name.indexOf("CONSTRAIN") !== -1 ||
423                            name.indexOf("SET") !== -1 ||
424                            name.indexOf("NONCLUSTERED") !== -1 ||
425                            name.indexOf("GO") !== -1 ||
426                            name.indexOf("REFERENCES") !== -1) {
427                            continue;
428                        }
429                        //Get delimiter of column name
430                        var firstSpaceIndex = name.indexOf(' ');
431
432                        //Get full name
433                        name = name.substring(0, firstSpaceIndex);
434
435                        name = ParseSQLServerName(name, true);
436                    } else {
437                        //Get delimiter of column name
438                        var firstSpaceIndex = name.indexOf(' ');
439
440                        //Get full name
441                        name = name.substring(0, firstSpaceIndex);
442                    }
443
444                    //Create Property
445                    var propertyModel = CreateProperty(name, currentTableModel.Name, null, false, false);
446
447                    //Add Property to table
448                    currentTableModel.Properties.push(propertyModel);
449                }
450
451                //Parse Primary Key
452                if (propertyType === 'primary key' || propertyType === 'constrain primary key') {
453                    if (!MODE_SQLSERVER) {
454                        var primaryKey = name.replace('PRIMARY KEY (', '').replace(')', '');
455
456                        //Create Primary Key
457                        var primaryKeyModel = CreatePrimaryKey(primaryKey, currentTableModel.Name);
458
459                        //Add Primary Key to List
460                        primaryKeyList.push(primaryKeyModel);
461
462                    } else {
463                        var start = i + 2;
464                        var end = 0;
465                        if (name.indexOf('PRIMARY KEY') !== -1 && name.indexOf('CLUSTERED') === -1) {
466                            var primaryKey = name.replace('PRIMARY KEY (', '').replace(')', '');
467
468                            //Create Primary Key
469                            var primaryKeyModel = CreatePrimaryKey(primaryKey, currentTableModel.Name);
470
471                            //Add Primary Key to List
472                            primaryKeyList.push(primaryKeyModel);
473
474                        } else {
475                            while (end === 0) {
476                                var primaryKeyRow = mxUtils.trim(lines[start]);
477
478                                if (primaryKeyRow.indexOf(')') !== -1) {
479                                    end = 1;
480                                    break;
481                                }
482
483                                start++;
484
485                                primaryKeyRow = primaryKeyRow.replace("ASC", '');
486
487                                //Parse name
488                                primaryKeyRow = ParseSQLServerName(primaryKeyRow, true);
489
490                                //Create Primary Key
491                                var primaryKeyModel = CreatePrimaryKey(primaryKeyRow, currentTableModel.Name);
492
493                                //Add Primary Key to List
494                                primaryKeyList.push(primaryKeyModel);
495                            }
496                        }
497
498                    }
499                }
500
501                //Parse Foreign Key
502                if (propertyType === 'foreign key' || propertyType === 'constrain foreign key') {
503                    if (!MODE_SQLSERVER) {
504                        ParseMySQLForeignKey(name, currentTableModel);
505                    } else {
506                        var completeRow = name;
507
508                        if (name.indexOf('REFERENCES') === -1) {
509                            var referencesRow = mxUtils.trim(lines[i + 1]);
510                            completeRow = 'ALTER TABLE [dbo].[' + currentTableModel.Name + ']  WITH CHECK ADD' + ' ' + name + ' ' + referencesRow;
511                        }
512
513                        ParseSQLServerForeignKey(completeRow, currentTableModel);
514
515                    }
516                }
517
518            } else if (propertyRow === 'alter table ') {
519
520                if (MODE_SQLSERVER) {
521                    //Parse the row
522                    var alterTableRow = tmp.substring(0, (tmp.charAt(tmp.length - 1) === ',') ? tmp.length - 1 : tmp.length);
523                    var referencesRow = mxUtils.trim(lines[i + 1]);
524                    var completeRow = alterTableRow + ' ' + referencesRow;
525
526                    ParseSQLServerForeignKey(completeRow, currentTableModel);
527                }
528            }
529        }
530
531        //Add last table
532        if (currentTableModel !== null) {
533            //Add table to the list
534            tableList.push(currentTableModel);
535        }
536
537        //Process Primary Keys
538        ProcessPrimaryKey();
539
540        //Process Foreign Keys
541        ProcessForeignKey();
542
543        //Create Table in UI
544        CreateTableUI();
545    };
546
547    function CreateTableUI() {
548
549        tableList.forEach(function(tableModel) {
550            //Define table size width
551            var maxNameLenght = 100 + tableModel.Name.length;
552
553            //Create Table
554            tableCell = new mxCell(tableModel.Name, new mxGeometry(dx, 0, maxNameLenght, 26),
555                'swimlane;fontStyle=0;childLayout=stackLayout;horizontal=1;startSize=26;fillColor=#e0e0e0;horizontalStack=0;resizeParent=1;resizeLast=0;collapsible=1;marginBottom=0;swimlaneFillColor=#ffffff;align=center;');
556            tableCell.vertex = true;
557
558            //Resize row
559            var size = ui.editor.graph.getPreferredSizeForCell(rowCell);
560            if (size !== null) {
561                tableCell.geometry.width = size.width + maxNameLenght;
562            }
563
564            //Add Table to cells
565            cells.push(tableCell);
566
567            //Add properties
568            tableModel.Properties.forEach(function(propertyModel) {
569
570                //Add row
571                AddRow(propertyModel, tableModel.Name);
572            });
573
574            //Close table
575            dx += tableCell.geometry.width + 40;
576            tableCell = null;
577        });
578
579        if (cells.length > 0) {
580            var graph = ui.editor.graph;
581            var view = graph.view;
582            var bds = graph.getGraphBounds();
583
584            // Computes unscaled, untranslated graph bounds
585            var x = Math.ceil(Math.max(0, bds.x / view.scale - view.translate.x) + 4 * graph.gridSize);
586            var y = Math.ceil(Math.max(0, (bds.y + bds.height) / view.scale - view.translate.y) + 4 * graph.gridSize);
587
588            graph.setSelectionCells(graph.importCells(cells, x, y));
589            graph.scrollCellToVisible(graph.getSelectionCell());
590        }
591
592        wnd.setVisible(false);
593    };
594
595    mxUtils.br(div);
596
597    var resetBtn = mxUtils.button(mxResources.get('reset'), function() {
598        sqlInput.value = '';
599    });
600
601    resetBtn.style.marginTop = '8px';
602    resetBtn.style.marginRight = '4px';
603    resetBtn.style.padding = '4px';
604    div.appendChild(resetBtn);
605
606    var btn = mxUtils.button('Insert MySQL', function() {
607        parseSql(sqlInput.value);
608    });
609
610    btn.style.marginTop = '8px';
611    btn.style.padding = '4px';
612    div.appendChild(btn);
613
614    var btn = mxUtils.button('Insert SQL Server', function() {
615        parseSql(sqlInput.value, 'sqlserver');
616    });
617
618    btn.style.marginTop = '8px';
619    btn.style.padding = '4px';
620    div.appendChild(btn);
621
622    // Adds action
623    ui.actions.addAction('fromSql', function() {
624        wnd.setVisible(!wnd.isVisible());
625
626        if (wnd.isVisible()) {
627            sqlInput.focus();
628        }
629    });
630
631    var theMenu = ui.menus.get('insert');
632    var oldMenu = theMenu.funct;
633
634    theMenu.funct = function(menu, parent) {
635        oldMenu.apply(this, arguments);
636
637        ui.menus.addMenuItems(menu, ['fromSql'], parent);
638    };
639});