Last active
April 13, 2022 11:03
-
-
Save brunomartinspro/0782559fcc3a6022d654b47eca723b40 to your computer and use it in GitHub Desktop.
Draw.io - SQL Plugin with SQL Server and MySQL Support
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Parse SQL CREATE TABLE. Simple initial version for community to improve. | |
*/ | |
Draw.loadPlugin(function(ui) { | |
function TableModel() { | |
this.Name = null; | |
this.Properties = [] | |
} | |
function PropertyModel() { | |
this.Name = null; | |
this.Value = null; | |
this.TableName = null; | |
this.ForeignKey = []; | |
this.IsPrimaryKey = false; | |
this.IsForeignKey = false; | |
} | |
function ForeignKeyModel() { | |
this.PrimaryKeyName = null; | |
this.ReferencesPropertyName = null | |
this.PrimaryKeyTableName = null; | |
this.ReferencesTableName = null; | |
this.IsDestination = false; | |
} | |
function PrimaryKeyModel() { | |
this.PrimaryKeyName = null; | |
this.PrimaryKeyTableName = null; | |
} | |
//SQL Types | |
var SQLServer = 'sqlserver'; | |
//SQL Modes | |
var MODE_SQLSERVER = null; | |
//Table Info | |
var foreignKeyList = []; | |
var primaryKeyList = []; | |
var tableList = []; | |
var cells = []; | |
var tableCell = null; | |
var rowCell = null; | |
var dx = 0; | |
var exportedTables = 0; | |
//Create Base div | |
var div = document.createElement('div'); | |
div.style.userSelect = 'none'; | |
div.style.overflow = 'hidden'; | |
div.style.padding = '10px'; | |
div.style.height = '100%'; | |
var graph = ui.editor.graph; | |
var sqlInput = document.createElement('textarea'); | |
sqlInput.style.height = '200px'; | |
sqlInput.style.width = '100%'; | |
sqlInput.value = 'CREATE TABLE Persons\n(\nPersonID int,\nLastName varchar(255),\n' + | |
'FirstName varchar(255),\nAddress varchar(255),\nCity varchar(255)\n);'; | |
mxUtils.br(div); | |
div.appendChild(sqlInput); | |
var graph = ui.editor.graph; | |
// Extends Extras menu | |
mxResources.parse('SQL...=SQL'); | |
var wnd = new mxWindow(mxResources.get('SQL...'), div, document.body.offsetWidth - 480, 140, | |
320, 300, true, true); | |
wnd.destroyOnClose = false; | |
wnd.setMaximizable(false); | |
wnd.setResizable(false); | |
wnd.setClosable(true); | |
function AddRow(propertyModel, tableName) { | |
var cellName = propertyModel.Name; | |
if (propertyModel.IsForeignKey && propertyModel.ForeignKey !== undefined && propertyModel.ForeignKey !== null) { | |
propertyModel.ForeignKey.forEach(function(foreignKeyModel) { | |
//We do not want the foreign key to be duplicated in our table to the same property | |
if (tableName !== foreignKeyModel.PrimaryKeyTableName || (tableName === foreignKeyModel.PrimaryKeyTableName && propertyModel.Name !== foreignKeyModel.PrimaryKeyName)) { | |
cellName += ' | ' + foreignKeyModel.PrimaryKeyTableName + '(' + foreignKeyModel.PrimaryKeyName + ')'; | |
} | |
}) | |
} | |
rowCell = new mxCell(cellName, new mxGeometry(0, 0, 90, 26), | |
'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;'); | |
rowCell.vertex = true; | |
var columnType = propertyModel.IsPrimaryKey && propertyModel.IsForeignKey ? 'PK | FK' : propertyModel.IsPrimaryKey ? 'PK' : propertyModel.IsForeignKey ? 'FK' : ''; | |
var left = sb.cloneCell(rowCell, columnType); | |
left.connectable = false; | |
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;' | |
left.geometry.width = 54; | |
left.geometry.height = 26; | |
rowCell.insert(left); | |
var size = ui.editor.graph.getPreferredSizeForCell(rowCell); | |
if (size !== null && tableCell.geometry.width < size.width + 10) { | |
tableCell.geometry.width = size.width + 10; | |
} | |
tableCell.insert(rowCell); | |
tableCell.geometry.height += 26; | |
rowCell = rowCell; | |
}; | |
function ParseMySQLForeignKey(name, currentTableModel) { | |
var referencesIndex = name.toLowerCase().indexOf("references"); | |
var foreignKeySQL = name.substring(0, referencesIndex); | |
var referencesSQL = name.substring(referencesIndex, name.length); | |
//Remove references syntax | |
referencesSQL = referencesSQL.replace("REFERENCES ", ''); | |
//Get Table and Property Index | |
var referencedTableIndex = referencesSQL.indexOf("("); | |
var referencedPropertyIndex = referencesSQL.indexOf(")"); | |
//Get Referenced Table | |
var referencedTableName = referencesSQL.substring(0, referencedTableIndex); | |
//Get Referenced Key | |
var referencedPropertyName = referencesSQL.substring(referencedTableIndex + 1, referencedPropertyIndex); | |
//Get ForeignKey | |
var foreignKey = foreignKeySQL.replace("FOREIGN KEY (", '').replace(")", '').replace(" ", ''); | |
//Create ForeignKey | |
var foreignKeyOriginModel = CreateForeignKey(foreignKey, currentTableModel.Name, referencedPropertyName, referencedTableName, true); | |
//Add ForeignKey Origin | |
foreignKeyList.push(foreignKeyOriginModel); | |
//Create ForeignKey | |
var foreignKeyDestinationModel = CreateForeignKey(referencedPropertyName, referencedTableName, foreignKey, currentTableModel.Name, false); | |
//Add ForeignKey Destination | |
foreignKeyList.push(foreignKeyDestinationModel); | |
}; | |
function ParseSQLServerForeignKey(name, currentTableModel) { | |
var referencesIndex = name.toLowerCase().indexOf("references"); | |
if (name.toLowerCase().indexOf("foreign key(") !== -1) { | |
var foreignKeySQL = name.substring(name.toLowerCase().indexOf("foreign key("), referencesIndex).replace("FOREIGN KEY(", '').replace(')', ''); | |
} else { | |
var foreignKeySQL = name.substring(name.toLowerCase().indexOf("foreign key ("), referencesIndex).replace("FOREIGN KEY (", '').replace(')', ''); | |
} | |
var referencesSQL = name.substring(referencesIndex, name.length); | |
var alterTableName = name.substring(0, name.indexOf("WITH")).replace('ALTER TABLE ', ''); | |
if (referencesIndex !== -1 && alterTableName !== '' && foreignKeySQL !== '' && referencesSQL !== '') { | |
//Remove references syntax | |
referencesSQL = referencesSQL.replace("REFERENCES ", ''); | |
//Get Table and Property Index | |
var referencedTableIndex = referencesSQL.indexOf("("); | |
var referencedPropertyIndex = referencesSQL.indexOf(")"); | |
//Get Referenced Table | |
var referencedTableName = referencesSQL.substring(0, referencedTableIndex); | |
//Parse Name | |
referencedTableName = ParseSQLServerName(referencedTableName); | |
//Get Referenced Key | |
var referencedPropertyName = referencesSQL.substring(referencedTableIndex + 1, referencedPropertyIndex); | |
//Parse Name | |
referencedPropertyName = ParseSQLServerName(referencedPropertyName); | |
//Get ForeignKey | |
var foreignKey = foreignKeySQL.replace("FOREIGN KEY (", '').replace(")", ''); | |
//Parse Name | |
foreignKey = ParseSQLServerName(foreignKey); | |
//Parse Name | |
alterTableName = ParseSQLServerName(alterTableName); | |
//Create ForeignKey | |
var foreignKeyOriginModel = CreateForeignKey(foreignKey, alterTableName, referencedPropertyName, referencedTableName, true); | |
//Add ForeignKey Origin | |
foreignKeyList.push(foreignKeyOriginModel); | |
//Create ForeignKey | |
var foreignKeyDestinationModel = CreateForeignKey(referencedPropertyName, referencedTableName, foreignKey, alterTableName, false); | |
//Add ForeignKey Destination | |
foreignKeyList.push(foreignKeyDestinationModel); | |
} | |
}; | |
function ProcessPrimaryKey() { | |
primaryKeyList.forEach(function(primaryModel) { | |
tableList.forEach(function(tableModel) { | |
if (tableModel.Name === primaryModel.PrimaryKeyTableName) { | |
tableModel.Properties.forEach(function(propertyModel) { | |
if (propertyModel.Name === primaryModel.PrimaryKeyName) { | |
propertyModel.IsPrimaryKey = true; | |
} | |
}); | |
} | |
}); | |
}); | |
} | |
function AssignForeignKey(foreignKeyModel) { | |
tableList.forEach(function(tableModel) { | |
if (tableModel.Name === foreignKeyModel.ReferencesTableName) { | |
tableModel.Properties.forEach(function(propertyModel) { | |
if (propertyModel.Name === foreignKeyModel.ReferencesPropertyName) { | |
propertyModel.IsForeignKey = true; | |
propertyModel.ForeignKey.push(foreignKeyModel); | |
} | |
}); | |
} | |
if (tableModel.Name === foreignKeyModel.PrimaryKeyTableName) { | |
tableModel.Properties.forEach(function(propertyModel) { | |
if (propertyModel.Name === foreignKeyModel.PrimaryKeyName) { | |
propertyModel.IsForeignKey = true; | |
propertyModel.ForeignKey.push(foreignKeyModel); | |
} | |
}); | |
} | |
}); | |
} | |
function ProcessForeignKey() { | |
foreignKeyList.forEach(function(foreignKeyModel) { | |
//Assign ForeignKey | |
AssignForeignKey(foreignKeyModel); | |
}); | |
} | |
function CreateForeignKey(primaryKeyName, primaryKeyTableName, referencesPropertyName, referencesTableName, isDestination) { | |
var foreignKey = new ForeignKeyModel; | |
foreignKey.PrimaryKeyTableName = primaryKeyTableName; | |
foreignKey.PrimaryKeyName = primaryKeyName; | |
foreignKey.ReferencesPropertyName = referencesPropertyName; | |
foreignKey.ReferencesTableName = referencesTableName; | |
foreignKey.IsDestination = (isDestination !== undefined && isDestination !== null) ? isDestination : false; | |
return foreignKey; | |
}; | |
function CreatePrimaryKey(primaryKeyName, primaryKeyTableName) { | |
var primaryKey = new PrimaryKeyModel; | |
primaryKey.PrimaryKeyTableName = primaryKeyTableName; | |
primaryKey.PrimaryKeyName = primaryKeyName; | |
return primaryKey; | |
}; | |
function CreateProperty(name, tableName, foreignKey, isPrimaryKey) { | |
var property = new PropertyModel; | |
var isForeignKey = foreignKey !== undefined && foreignKey !== null; | |
property.Name = name; | |
property.TableName = tableName; | |
property.ForeignKey = isForeignKey ? foreignKey : []; | |
property.IsForeignKey = isForeignKey; | |
property.IsPrimaryKey = isPrimaryKey; | |
return property; | |
}; | |
function CreateTable(name) { | |
var table = new TableModel; | |
table.Name = name; | |
//Count exported tables | |
exportedTables++; | |
return table; | |
}; | |
function ParseSQLServerName(name, property) { | |
name = name.replace('[dbo].[', ''); | |
name = name.replace('](', ''); | |
name = name.replace('].[', '.'); | |
name = name.replace('[', ''); | |
if (property == undefined || property == null) { | |
name = name.replace(' [', ''); | |
name = name.replace('] ', ''); | |
} else { | |
if (name.indexOf(']') !== -1) { | |
name = name.substring(0, name.indexOf(']')); | |
} | |
} | |
if (name.lastIndexOf(']') === (name.length - 1)) { | |
name = name.substring(0, name.length - 1); | |
} | |
if (name.lastIndexOf(')') === (name.length - 1)) { | |
name = name.substring(0, name.length - 1); | |
} | |
if (name.lastIndexOf('(') === (name.length - 1)) { | |
name = name.substring(0, name.length - 1); | |
} | |
name = name.replace(' ', ''); | |
return name; | |
}; | |
function ParseTableName(name) { | |
if (name.charAt(name.length - 1) === '(') { | |
if (!MODE_SQLSERVER) { | |
name = name.substring(0, name.lastIndexOf(' ')); | |
} else { | |
name = ParseSQLServerName(name); | |
} | |
} | |
return name; | |
}; | |
function parseSql(text, type) { | |
var lines = text.split('\n'); | |
dx = 0; | |
MODE_SQLSERVER = type !== undefined && type !== null && type == SQLServer; | |
tableCell = null; | |
cells = []; | |
exportedTables = 0; | |
tableList = []; | |
foreignKeyList = []; | |
var currentTableModel = null; | |
//Parse SQL to objects | |
for (var i = 0; i < lines.length; i++) { | |
rowCell = null; | |
var tmp = mxUtils.trim(lines[i]); | |
var propertyRow = tmp.substring(0, 12).toLowerCase(); | |
//Parse Table | |
if (propertyRow === 'create table') { | |
//Parse row | |
var name = mxUtils.trim(tmp.substring(12)); | |
//Parse Table Name | |
name = ParseTableName(name); | |
if (currentTableModel !== null) { | |
//Add table to the list | |
tableList.push(currentTableModel); | |
} | |
//Create Table | |
currentTableModel = CreateTable(name); | |
} | |
// Parse Properties | |
else if (tmp !== '(' && currentTableModel != null && propertyRow !== 'alter table ') { | |
//Parse the row | |
var name = tmp.substring(0, (tmp.charAt(tmp.length - 1) === ',') ? tmp.length - 1 : tmp.length); | |
//Attempt to get the Key Type | |
var propertyType = name.substring(0, 11).toLowerCase(); | |
//Add special constraints | |
if (MODE_SQLSERVER) { | |
if (tmp.indexOf("CONSTRAINT") !== -1 && tmp.indexOf("PRIMARY KEY") !== -1) { | |
propertyType = "constrain primary key"; | |
} | |
if (tmp.indexOf("CONSTRAINT") !== -1 && tmp.indexOf("FOREIGN KEY") !== -1) { | |
propertyType = "constrain foreign key"; | |
} | |
} | |
//Verify if this is a property that doesn't have a relationship (One minute of silence for the property) | |
var normalProperty = propertyType !== 'primary key' && propertyType !== 'foreign key' && propertyType !== 'constrain primary key' && propertyType !== 'constrain foreign key'; | |
//Parse properties that don't have relationships | |
if (normalProperty) { | |
if (name === '' || name === "" || name === ");") { | |
continue; | |
} | |
if (MODE_SQLSERVER) { | |
if (name.indexOf("ASC") !== -1 || | |
name.indexOf("DESC") !== -1 || | |
name.indexOf("EXEC") !== -1 || | |
name.indexOf("WITH") !== -1 || | |
name.indexOf("ON") !== -1 || | |
name.indexOf("ALTER") !== -1 || | |
name.indexOf("/*") !== -1 || | |
name.indexOf("CONSTRAIN") !== -1 || | |
name.indexOf("SET") !== -1 || | |
name.indexOf("NONCLUSTERED") !== -1 || | |
name.indexOf("GO") !== -1 || | |
name.indexOf("REFERENCES") !== -1) { | |
continue; | |
} | |
//Get delimiter of column name | |
var firstSpaceIndex = name.indexOf(' '); | |
//Get full name | |
name = name.substring(0, firstSpaceIndex); | |
name = ParseSQLServerName(name, true); | |
} else { | |
//Get delimiter of column name | |
var firstSpaceIndex = name.indexOf(' '); | |
//Get full name | |
name = name.substring(0, firstSpaceIndex); | |
} | |
//Create Property | |
var propertyModel = CreateProperty(name, currentTableModel.Name, null, false, false); | |
//Add Property to table | |
currentTableModel.Properties.push(propertyModel); | |
} | |
//Parse Primary Key | |
if (propertyType === 'primary key' || propertyType === 'constrain primary key') { | |
if (!MODE_SQLSERVER) { | |
var primaryKey = name.replace('PRIMARY KEY (', '').replace(')', ''); | |
//Create Primary Key | |
var primaryKeyModel = CreatePrimaryKey(primaryKey, currentTableModel.Name); | |
//Add Primary Key to List | |
primaryKeyList.push(primaryKeyModel); | |
} else { | |
var start = i + 2; | |
var end = 0; | |
if (name.indexOf('PRIMARY KEY') !== -1 && name.indexOf('CLUSTERED') === -1) { | |
var primaryKey = name.replace('PRIMARY KEY (', '').replace(')', ''); | |
//Create Primary Key | |
var primaryKeyModel = CreatePrimaryKey(primaryKey, currentTableModel.Name); | |
//Add Primary Key to List | |
primaryKeyList.push(primaryKeyModel); | |
} else { | |
while (end === 0) { | |
var primaryKeyRow = mxUtils.trim(lines[start]); | |
if (primaryKeyRow.indexOf(')') !== -1) { | |
end = 1; | |
break; | |
} | |
start++; | |
primaryKeyRow = primaryKeyRow.replace("ASC", ''); | |
//Parse name | |
primaryKeyRow = ParseSQLServerName(primaryKeyRow, true); | |
//Create Primary Key | |
var primaryKeyModel = CreatePrimaryKey(primaryKeyRow, currentTableModel.Name); | |
//Add Primary Key to List | |
primaryKeyList.push(primaryKeyModel); | |
} | |
} | |
} | |
} | |
//Parse Foreign Key | |
if (propertyType === 'foreign key' || propertyType === 'constrain foreign key') { | |
if (!MODE_SQLSERVER) { | |
ParseMySQLForeignKey(name, currentTableModel); | |
} else { | |
var completeRow = name; | |
if (name.indexOf('REFERENCES') === -1) { | |
var referencesRow = mxUtils.trim(lines[i + 1]); | |
completeRow = 'ALTER TABLE [dbo].[' + currentTableModel.Name + '] WITH CHECK ADD' + ' ' + name + ' ' + referencesRow; | |
} | |
ParseSQLServerForeignKey(completeRow, currentTableModel); | |
} | |
} | |
} else if (propertyRow === 'alter table ') { | |
if (MODE_SQLSERVER) { | |
//Parse the row | |
var alterTableRow = tmp.substring(0, (tmp.charAt(tmp.length - 1) === ',') ? tmp.length - 1 : tmp.length); | |
var referencesRow = mxUtils.trim(lines[i + 1]); | |
var completeRow = alterTableRow + ' ' + referencesRow; | |
ParseSQLServerForeignKey(completeRow, currentTableModel); | |
} | |
} | |
} | |
//Add last table | |
if (currentTableModel !== null) { | |
//Add table to the list | |
tableList.push(currentTableModel); | |
} | |
//Process Primary Keys | |
ProcessPrimaryKey(); | |
//Process Foreign Keys | |
ProcessForeignKey(); | |
//Create Table in UI | |
CreateTableUI(); | |
}; | |
function CreateTableUI() { | |
tableList.forEach(function(tableModel) { | |
//Define table size width | |
var maxNameLenght = 100 + tableModel.Name.length; | |
//Create Table | |
tableCell = new mxCell(tableModel.Name, new mxGeometry(dx, 0, maxNameLenght, 26), | |
'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;'); | |
tableCell.vertex = true; | |
//Resize row | |
var size = ui.editor.graph.getPreferredSizeForCell(rowCell); | |
if (size !== null) { | |
tableCell.geometry.width = size.width + maxNameLenght; | |
} | |
//Add Table to cells | |
cells.push(tableCell); | |
//Add properties | |
tableModel.Properties.forEach(function(propertyModel) { | |
//Add row | |
AddRow(propertyModel, tableModel.Name); | |
}); | |
//Close table | |
dx += tableCell.geometry.width + 40; | |
tableCell = null; | |
}); | |
if (cells.length > 0) { | |
var graph = ui.editor.graph; | |
var view = graph.view; | |
var bds = graph.getGraphBounds(); | |
// Computes unscaled, untranslated graph bounds | |
var x = Math.ceil(Math.max(0, bds.x / view.scale - view.translate.x) + 4 * graph.gridSize); | |
var y = Math.ceil(Math.max(0, (bds.y + bds.height) / view.scale - view.translate.y) + 4 * graph.gridSize); | |
graph.setSelectionCells(graph.importCells(cells, x, y)); | |
graph.scrollCellToVisible(graph.getSelectionCell()); | |
} | |
alert('Processed ' + exportedTables + ' tables.'); | |
wnd.setVisible(false); | |
}; | |
mxUtils.br(div); | |
var resetBtn = mxUtils.button(mxResources.get('reset'), function() { | |
sqlInput.value = ''; | |
}); | |
resetBtn.style.marginTop = '8px'; | |
resetBtn.style.marginRight = '4px'; | |
resetBtn.style.padding = '4px'; | |
div.appendChild(resetBtn); | |
var btn = mxUtils.button('Insert MySQL', function() { | |
parseSql(sqlInput.value); | |
}); | |
btn.style.marginTop = '8px'; | |
btn.style.padding = '4px'; | |
div.appendChild(btn); | |
var btn = mxUtils.button('Insert SQL Server', function() { | |
parseSql(sqlInput.value, 'sqlserver'); | |
}); | |
btn.style.marginTop = '8px'; | |
btn.style.padding = '4px'; | |
div.appendChild(btn); | |
// Adds action | |
ui.actions.addAction('SQL...', function() { | |
wnd.setVisible(!wnd.isVisible()); | |
if (wnd.isVisible()) { | |
sqlInput.focus(); | |
} | |
}); | |
var theMenu = ui.menus.get('insert'); | |
var oldMenu = theMenu.funct; | |
theMenu.funct = function(menu, parent) { | |
oldMenu.apply(this, arguments); | |
ui.menus.addMenuItems(menu, ['SQL...'], parent); | |
}; | |
}); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Parse SQL CREATE TABLE. Simple initial version for community to improve. | |
*/ | |
Draw.loadPlugin(function(ui) { | |
function TableModel() { | |
this.Name = null; | |
this.Properties = [] | |
} | |
function PropertyModel() { | |
this.Name = null; | |
this.Value = null; | |
this.TableName = null; | |
this.ForeignKey = []; | |
this.IsPrimaryKey = false; | |
this.IsForeignKey = false; | |
} | |
function ForeignKeyModel() { | |
this.PrimaryKeyName = null; | |
this.ReferencesPropertyName = null | |
this.PrimaryKeyTableName = null; | |
this.ReferencesTableName = null; | |
this.IsDestination = false; | |
} | |
function PrimaryKeyModel() { | |
this.PrimaryKeyName = null; | |
this.PrimaryKeyTableName = null; | |
} | |
//SQL Types | |
var SQLServer = 'sqlserver'; | |
//SQL Modes | |
var MODE_SQLSERVER = null; | |
//Table Info | |
var foreignKeyList = []; | |
var primaryKeyList = []; | |
var tableList = []; | |
var cells = []; | |
var tableCell = null; | |
var rowCell = null; | |
var dx = 0; | |
var exportedTables = 0; | |
//Create Base div | |
var div = document.createElement('div'); | |
div.style.userSelect = 'none'; | |
div.style.overflow = 'hidden'; | |
div.style.padding = '10px'; | |
div.style.height = '100%'; | |
var graph = ui.editor.graph; | |
var sqlInput = document.createElement('textarea'); | |
sqlInput.style.height = '200px'; | |
sqlInput.style.width = '100%'; | |
sqlInput.value = 'CREATE TABLE Persons\n(\nPersonID int,\nLastName varchar(255),\n' + | |
'FirstName varchar(255),\nAddress varchar(255),\nCity varchar(255)\n);'; | |
mxUtils.br(div); | |
div.appendChild(sqlInput); | |
var graph = ui.editor.graph; | |
// Extends Extras menu | |
mxResources.parse('fromSql=From SQL'); | |
var wnd = new mxWindow(mxResources.get('fromSql'), div, document.body.offsetWidth - 480, 140, | |
320, 300, true, true); | |
wnd.destroyOnClose = false; | |
wnd.setMaximizable(false); | |
wnd.setResizable(false); | |
wnd.setClosable(true); | |
function AddRow(propertyModel, tableName) { | |
var cellName = propertyModel.Name; | |
if (propertyModel.IsForeignKey && propertyModel.ForeignKey !== undefined && propertyModel.ForeignKey !== null) { | |
propertyModel.ForeignKey.forEach(function(foreignKeyModel) { | |
//We do not want the foreign key to be duplicated in our table to the same property | |
if (tableName !== foreignKeyModel.PrimaryKeyTableName || (tableName === foreignKeyModel.PrimaryKeyTableName && propertyModel.Name !== foreignKeyModel.PrimaryKeyName)) { | |
cellName += ' | ' + foreignKeyModel.PrimaryKeyTableName + '(' + foreignKeyModel.PrimaryKeyName + ')'; | |
} | |
}) | |
} | |
rowCell = new mxCell(cellName, new mxGeometry(0, 0, 90, 26), | |
'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;'); | |
rowCell.vertex = true; | |
var columnType = propertyModel.IsPrimaryKey && propertyModel.IsForeignKey ? 'PK | FK' : propertyModel.IsPrimaryKey ? 'PK' : propertyModel.IsForeignKey ? 'FK' : ''; | |
var left = sb.cloneCell(rowCell, columnType); | |
left.connectable = false; | |
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;' | |
left.geometry.width = 54; | |
left.geometry.height = 26; | |
rowCell.insert(left); | |
var size = ui.editor.graph.getPreferredSizeForCell(rowCell); | |
if (size !== null && tableCell.geometry.width < size.width + 10) { | |
tableCell.geometry.width = size.width + 10; | |
} | |
tableCell.insert(rowCell); | |
tableCell.geometry.height += 26; | |
rowCell = rowCell; | |
}; | |
function ParseMySQLForeignKey(name, currentTableModel) { | |
var referencesIndex = name.toLowerCase().indexOf("references"); | |
var foreignKeySQL = name.substring(0, referencesIndex); | |
var referencesSQL = name.substring(referencesIndex, name.length); | |
//Remove references syntax | |
referencesSQL = referencesSQL.replace("REFERENCES ", ''); | |
//Get Table and Property Index | |
var referencedTableIndex = referencesSQL.indexOf("("); | |
var referencedPropertyIndex = referencesSQL.indexOf(")"); | |
//Get Referenced Table | |
var referencedTableName = referencesSQL.substring(0, referencedTableIndex); | |
//Get Referenced Key | |
var referencedPropertyName = referencesSQL.substring(referencedTableIndex + 1, referencedPropertyIndex); | |
//Get ForeignKey | |
var foreignKey = foreignKeySQL.replace("FOREIGN KEY (", '').replace(")", '').replace(" ", ''); | |
//Create ForeignKey | |
var foreignKeyOriginModel = CreateForeignKey(foreignKey, currentTableModel.Name, referencedPropertyName, referencedTableName, true); | |
//Add ForeignKey Origin | |
foreignKeyList.push(foreignKeyOriginModel); | |
//Create ForeignKey | |
var foreignKeyDestinationModel = CreateForeignKey(referencedPropertyName, referencedTableName, foreignKey, currentTableModel.Name, false); | |
//Add ForeignKey Destination | |
foreignKeyList.push(foreignKeyDestinationModel); | |
}; | |
function ParseSQLServerForeignKey(name, currentTableModel) { | |
var referencesIndex = name.toLowerCase().indexOf("references"); | |
if (name.toLowerCase().indexOf("foreign key(") !== -1) { | |
var foreignKeySQL = name.substring(name.toLowerCase().indexOf("foreign key("), referencesIndex).replace("FOREIGN KEY(", '').replace(')', ''); | |
} else { | |
var foreignKeySQL = name.substring(name.toLowerCase().indexOf("foreign key ("), referencesIndex).replace("FOREIGN KEY (", '').replace(')', ''); | |
} | |
var referencesSQL = name.substring(referencesIndex, name.length); | |
var alterTableName = name.substring(0, name.indexOf("WITH")).replace('ALTER TABLE ', ''); | |
if (referencesIndex !== -1 && alterTableName !== '' && foreignKeySQL !== '' && referencesSQL !== '') { | |
//Remove references syntax | |
referencesSQL = referencesSQL.replace("REFERENCES ", ''); | |
//Get Table and Property Index | |
var referencedTableIndex = referencesSQL.indexOf("("); | |
var referencedPropertyIndex = referencesSQL.indexOf(")"); | |
//Get Referenced Table | |
var referencedTableName = referencesSQL.substring(0, referencedTableIndex); | |
//Parse Name | |
referencedTableName = ParseSQLServerName(referencedTableName); | |
//Get Referenced Key | |
var referencedPropertyName = referencesSQL.substring(referencedTableIndex + 1, referencedPropertyIndex); | |
//Parse Name | |
referencedPropertyName = ParseSQLServerName(referencedPropertyName); | |
//Get ForeignKey | |
var foreignKey = foreignKeySQL.replace("FOREIGN KEY (", '').replace(")", ''); | |
//Parse Name | |
foreignKey = ParseSQLServerName(foreignKey); | |
//Parse Name | |
alterTableName = ParseSQLServerName(alterTableName); | |
//Create ForeignKey | |
var foreignKeyOriginModel = CreateForeignKey(foreignKey, alterTableName, referencedPropertyName, referencedTableName, true); | |
//Add ForeignKey Origin | |
foreignKeyList.push(foreignKeyOriginModel); | |
//Create ForeignKey | |
var foreignKeyDestinationModel = CreateForeignKey(referencedPropertyName, referencedTableName, foreignKey, alterTableName, false); | |
//Add ForeignKey Destination | |
foreignKeyList.push(foreignKeyDestinationModel); | |
} | |
}; | |
function ProcessPrimaryKey() { | |
primaryKeyList.forEach(function(primaryModel) { | |
tableList.forEach(function(tableModel) { | |
if (tableModel.Name === primaryModel.PrimaryKeyTableName) { | |
tableModel.Properties.forEach(function(propertyModel) { | |
if (propertyModel.Name === primaryModel.PrimaryKeyName) { | |
propertyModel.IsPrimaryKey = true; | |
} | |
}); | |
} | |
}); | |
}); | |
} | |
function AssignForeignKey(foreignKeyModel) { | |
tableList.forEach(function(tableModel) { | |
if (tableModel.Name === foreignKeyModel.ReferencesTableName) { | |
tableModel.Properties.forEach(function(propertyModel) { | |
if (propertyModel.Name === foreignKeyModel.ReferencesPropertyName) { | |
propertyModel.IsForeignKey = true; | |
propertyModel.ForeignKey.push(foreignKeyModel); | |
} | |
}); | |
} | |
if (tableModel.Name === foreignKeyModel.PrimaryKeyTableName) { | |
tableModel.Properties.forEach(function(propertyModel) { | |
if (propertyModel.Name === foreignKeyModel.PrimaryKeyName) { | |
propertyModel.IsForeignKey = true; | |
propertyModel.ForeignKey.push(foreignKeyModel); | |
} | |
}); | |
} | |
}); | |
} | |
function ProcessForeignKey() { | |
foreignKeyList.forEach(function(foreignKeyModel) { | |
//Assign ForeignKey | |
AssignForeignKey(foreignKeyModel); | |
}); | |
} | |
function CreateForeignKey(primaryKeyName, primaryKeyTableName, referencesPropertyName, referencesTableName, isDestination) { | |
var foreignKey = new ForeignKeyModel; | |
foreignKey.PrimaryKeyTableName = primaryKeyTableName; | |
foreignKey.PrimaryKeyName = primaryKeyName; | |
foreignKey.ReferencesPropertyName = referencesPropertyName; | |
foreignKey.ReferencesTableName = referencesTableName; | |
foreignKey.IsDestination = (isDestination !== undefined && isDestination !== null) ? isDestination : false; | |
return foreignKey; | |
}; | |
function CreatePrimaryKey(primaryKeyName, primaryKeyTableName) { | |
var primaryKey = new PrimaryKeyModel; | |
primaryKey.PrimaryKeyTableName = primaryKeyTableName; | |
primaryKey.PrimaryKeyName = primaryKeyName; | |
return primaryKey; | |
}; | |
function CreateProperty(name, tableName, foreignKey, isPrimaryKey) { | |
var property = new PropertyModel; | |
var isForeignKey = foreignKey !== undefined && foreignKey !== null; | |
property.Name = name; | |
property.TableName = tableName; | |
property.ForeignKey = isForeignKey ? foreignKey : []; | |
property.IsForeignKey = isForeignKey; | |
property.IsPrimaryKey = isPrimaryKey; | |
return property; | |
}; | |
function CreateTable(name) { | |
var table = new TableModel; | |
table.Name = name; | |
//Count exported tables | |
exportedTables++; | |
return table; | |
}; | |
function ParseSQLServerName(name, property) { | |
name = name.replace('[dbo].[', ''); | |
name = name.replace('](', ''); | |
name = name.replace('].[', '.'); | |
name = name.replace('[', ''); | |
if (property == undefined || property == null) { | |
name = name.replace(' [', ''); | |
name = name.replace('] ', ''); | |
} else { | |
if (name.indexOf(']') !== -1) { | |
name = name.substring(0, name.indexOf(']')); | |
} | |
} | |
if (name.lastIndexOf(']') === (name.length - 1)) { | |
name = name.substring(0, name.length - 1); | |
} | |
if (name.lastIndexOf(')') === (name.length - 1)) { | |
name = name.substring(0, name.length - 1); | |
} | |
if (name.lastIndexOf('(') === (name.length - 1)) { | |
name = name.substring(0, name.length - 1); | |
} | |
name = name.replace(' ', ''); | |
return name; | |
}; | |
function ParseTableName(name) { | |
if (name.charAt(name.length - 1) === '(') { | |
if (!MODE_SQLSERVER) { | |
name = name.substring(0, name.lastIndexOf(' ')); | |
} else { | |
name = ParseSQLServerName(name); | |
} | |
} | |
return name; | |
}; | |
function parseSql(text, type) { | |
var lines = text.split('\n'); | |
dx = 0; | |
MODE_SQLSERVER = type !== undefined && type !== null && type == SQLServer; | |
tableCell = null; | |
cells = []; | |
exportedTables = 0; | |
tableList = []; | |
foreignKeyList = []; | |
var currentTableModel = null; | |
//Parse SQL to objects | |
for (var i = 0; i < lines.length; i++) { | |
rowCell = null; | |
var tmp = mxUtils.trim(lines[i]); | |
var propertyRow = tmp.substring(0, 12).toLowerCase(); | |
//Parse Table | |
if (propertyRow === 'create table') { | |
//Parse row | |
var name = mxUtils.trim(tmp.substring(12)); | |
//Parse Table Name | |
name = ParseTableName(name); | |
if (currentTableModel !== null) { | |
//Add table to the list | |
tableList.push(currentTableModel); | |
} | |
//Create Table | |
currentTableModel = CreateTable(name); | |
} | |
// Parse Properties | |
else if (tmp !== '(' && currentTableModel != null && propertyRow !== 'alter table ') { | |
//Parse the row | |
var name = tmp.substring(0, (tmp.charAt(tmp.length - 1) === ',') ? tmp.length - 1 : tmp.length); | |
//Attempt to get the Key Type | |
var propertyType = name.substring(0, 11).toLowerCase(); | |
//Add special constraints | |
if (MODE_SQLSERVER) { | |
if (tmp.indexOf("CONSTRAINT") !== -1 && tmp.indexOf("PRIMARY KEY") !== -1) { | |
propertyType = "constrain primary key"; | |
} | |
if (tmp.indexOf("CONSTRAINT") !== -1 && tmp.indexOf("FOREIGN KEY") !== -1) { | |
propertyType = "constrain foreign key"; | |
} | |
} | |
//Verify if this is a property that doesn't have a relationship (One minute of silence for the property) | |
var normalProperty = propertyType !== 'primary key' && propertyType !== 'foreign key' && propertyType !== 'constrain primary key' && propertyType !== 'constrain foreign key'; | |
//Parse properties that don't have relationships | |
if (normalProperty) { | |
if (name === '' || name === "" || name === ");") { | |
continue; | |
} | |
if (MODE_SQLSERVER) { | |
if (name.indexOf("ASC") !== -1 || | |
name.indexOf("DESC") !== -1 || | |
name.indexOf("EXEC") !== -1 || | |
name.indexOf("WITH") !== -1 || | |
name.indexOf("ON") !== -1 || | |
name.indexOf("ALTER") !== -1 || | |
name.indexOf("/*") !== -1 || | |
name.indexOf("CONSTRAIN") !== -1 || | |
name.indexOf("SET") !== -1 || | |
name.indexOf("NONCLUSTERED") !== -1 || | |
name.indexOf("GO") !== -1 || | |
name.indexOf("REFERENCES") !== -1) { | |
continue; | |
} | |
//Get delimiter of column name | |
var firstSpaceIndex = name.indexOf(' '); | |
//Get full name | |
name = name.substring(0, firstSpaceIndex); | |
name = ParseSQLServerName(name, true); | |
} else { | |
//Get delimiter of column name | |
var firstSpaceIndex = name.indexOf(' '); | |
//Get full name | |
name = name.substring(0, firstSpaceIndex); | |
} | |
//Create Property | |
var propertyModel = CreateProperty(name, currentTableModel.Name, null, false, false); | |
//Add Property to table | |
currentTableModel.Properties.push(propertyModel); | |
} | |
//Parse Primary Key | |
if (propertyType === 'primary key' || propertyType === 'constrain primary key') { | |
if (!MODE_SQLSERVER) { | |
var primaryKey = name.replace('PRIMARY KEY (', '').replace(')', ''); | |
//Create Primary Key | |
var primaryKeyModel = CreatePrimaryKey(primaryKey, currentTableModel.Name); | |
//Add Primary Key to List | |
primaryKeyList.push(primaryKeyModel); | |
} else { | |
var start = i + 2; | |
var end = 0; | |
if (name.indexOf('PRIMARY KEY') !== -1 && name.indexOf('CLUSTERED') === -1) { | |
var primaryKey = name.replace('PRIMARY KEY (', '').replace(')', ''); | |
//Create Primary Key | |
var primaryKeyModel = CreatePrimaryKey(primaryKey, currentTableModel.Name); | |
//Add Primary Key to List | |
primaryKeyList.push(primaryKeyModel); | |
} else { | |
while (end === 0) { | |
var primaryKeyRow = mxUtils.trim(lines[start]); | |
if (primaryKeyRow.indexOf(')') !== -1) { | |
end = 1; | |
break; | |
} | |
start++; | |
primaryKeyRow = primaryKeyRow.replace("ASC", ''); | |
//Parse name | |
primaryKeyRow = ParseSQLServerName(primaryKeyRow, true); | |
//Create Primary Key | |
var primaryKeyModel = CreatePrimaryKey(primaryKeyRow, currentTableModel.Name); | |
//Add Primary Key to List | |
primaryKeyList.push(primaryKeyModel); | |
} | |
} | |
} | |
} | |
//Parse Foreign Key | |
if (propertyType === 'foreign key' || propertyType === 'constrain foreign key') { | |
if (!MODE_SQLSERVER) { | |
ParseMySQLForeignKey(name, currentTableModel); | |
} else { | |
var completeRow = name; | |
if (name.indexOf('REFERENCES') === -1) { | |
var referencesRow = mxUtils.trim(lines[i + 1]); | |
completeRow = 'ALTER TABLE [dbo].[' + currentTableModel.Name + '] WITH CHECK ADD' + ' ' + name + ' ' + referencesRow; | |
} | |
ParseSQLServerForeignKey(completeRow, currentTableModel); | |
} | |
} | |
} else if (propertyRow === 'alter table ') { | |
if (MODE_SQLSERVER) { | |
//Parse the row | |
var alterTableRow = tmp.substring(0, (tmp.charAt(tmp.length - 1) === ',') ? tmp.length - 1 : tmp.length); | |
var referencesRow = mxUtils.trim(lines[i + 1]); | |
var completeRow = alterTableRow + ' ' + referencesRow; | |
ParseSQLServerForeignKey(completeRow, currentTableModel); | |
} | |
} | |
} | |
//Add last table | |
if (currentTableModel !== null) { | |
//Add table to the list | |
tableList.push(currentTableModel); | |
} | |
//Process Primary Keys | |
ProcessPrimaryKey(); | |
//Process Foreign Keys | |
ProcessForeignKey(); | |
//Create Table in UI | |
CreateTableUI(); | |
}; | |
function CreateTableUI() { | |
tableList.forEach(function(tableModel) { | |
//Define table size width | |
var maxNameLenght = 100 + tableModel.Name.length; | |
//Create Table | |
tableCell = new mxCell(tableModel.Name, new mxGeometry(dx, 0, maxNameLenght, 26), | |
'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;'); | |
tableCell.vertex = true; | |
//Resize row | |
var size = ui.editor.graph.getPreferredSizeForCell(rowCell); | |
if (size !== null) { | |
tableCell.geometry.width = size.width + maxNameLenght; | |
} | |
//Add Table to cells | |
cells.push(tableCell); | |
//Add properties | |
tableModel.Properties.forEach(function(propertyModel) { | |
//Add row | |
AddRow(propertyModel, tableModel.Name); | |
}); | |
//Close table | |
dx += tableCell.geometry.width + 40; | |
tableCell = null; | |
}); | |
if (cells.length > 0) { | |
var graph = ui.editor.graph; | |
var view = graph.view; | |
var bds = graph.getGraphBounds(); | |
// Computes unscaled, untranslated graph bounds | |
var x = Math.ceil(Math.max(0, bds.x / view.scale - view.translate.x) + 4 * graph.gridSize); | |
var y = Math.ceil(Math.max(0, (bds.y + bds.height) / view.scale - view.translate.y) + 4 * graph.gridSize); | |
graph.setSelectionCells(graph.importCells(cells, x, y)); | |
graph.scrollCellToVisible(graph.getSelectionCell()); | |
} | |
alert('Processed ' + exportedTables + ' tables.'); | |
wnd.setVisible(false); | |
}; | |
mxUtils.br(div); | |
var resetBtn = mxUtils.button(mxResources.get('reset'), function() { | |
sqlInput.value = ''; | |
}); | |
resetBtn.style.marginTop = '8px'; | |
resetBtn.style.marginRight = '4px'; | |
resetBtn.style.padding = '4px'; | |
div.appendChild(resetBtn); | |
var btn = mxUtils.button('Insert MySQL', function() { | |
parseSql(sqlInput.value); | |
}); | |
btn.style.marginTop = '8px'; | |
btn.style.padding = '4px'; | |
div.appendChild(btn); | |
var btn = mxUtils.button('Insert SQL Server', function() { | |
parseSql(sqlInput.value, 'sqlserver'); | |
}); | |
btn.style.marginTop = '8px'; | |
btn.style.padding = '4px'; | |
div.appendChild(btn); | |
// Adds action | |
ui.actions.addAction('fromSql', function() { | |
wnd.setVisible(!wnd.isVisible()); | |
if (wnd.isVisible()) { | |
sqlInput.focus(); | |
} | |
}); | |
var theMenu = ui.menus.get('insert'); | |
var oldMenu = theMenu.funct; | |
theMenu.funct = function(menu, parent) { | |
oldMenu.apply(this, arguments); | |
ui.menus.addMenuItems(menu, ['fromSql'], parent); | |
}; | |
}); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Merged to Production!