JavaScript Database Designer with SQL Generator

We are going to use the JS flowchart library as a database design tool. We will create DB tables, add rows, connect the tables and generate SQL statements that would create the tables.

Here is a screenshot of the application:

Database Designer Application with SQL Generator

Database Designer Application with SQL Generator

I. Project Setup

We need two JavaScript libraries for the flowchart:

  • MindFusion.Common.js
  • MindFusion.Diagramming.js

We copy them in the work folder of the project, where we will put the HTML and the JavaScript code behind. Then we create an HTML file and name it DBDesign.html. There we will reference the two JavaScript libraries:

<a href="http://MindFusion.Common.js">http://MindFusion.Common.js</a>
<a href="http://MindFusion.Diagramming.js">http://MindFusion.Diagramming.js</a>

We reference those two libraries at the end of the HTML file, just before the closing tag. This way we are sure that the majority of the browsers will load the scripts correct.

We need an HTML 5 Canvas element for the diagram to draw itself onto and we create one inside a <div> tag:

<div style="width: 100%;height: 100%;overflow: auto">
	This page requires a browser that supports HTML 5 Canvas element.			

It’s important to set and id for the Canvas element, that’s how we will get it in the JavaScript code behind file.

We create the JS file to be used by this project as DBDesign.js and we place it in the same directory as the two other JS files. We add a reference to it in the HTML page:

<a href="http://DBDesign.js">http://DBDesign.js</a>

II. UI Controls

The DBDesigner has a line of controls at the bottoms that provide menus – add/edit/delete row, create/delete/rename table and a button for connection info. We create them as buttons:

<div id="controls" style="height: 150px" left: 0; right: 401px;">
   <input type="button" id="btnAddRow" value="Add row" style="margin-left: 5px; margin-bottom: 2px;" />
   <input type="button" id="btnEditRow" value="Edit row" style="margin-left: 5px; margin-bottom: 2px;" />
   <input type="button" id="btnDeleteRow" value="Delete row" style="margin-left: 5px; margin-bottom: 2px;" />
…..

We add a textarea for the generated SQL and we close the div:

 <textarea id="generatedSql" style="height: 120px;width: 100%"></textarea>
</div>

When the user presses one of those buttons we show a dialog. The dialogs are forms. Here is the form that renames a table:

<div id="renameTable-dialog" title="Rename Table">
  <form>
	<fieldset>
  	 <label for="renameTableCaption">Table name</label>
	</fieldset>
	</form>
	</div>

III. General Diagram Settings

Let’s start coding the JavaScript methods for the DBDesign application. We use the document.ready method to initialize the Diagram:

var Diagram = MindFusion.Diagramming.Diagram;

var diagram;

$(document).ready(function () {
   // create a Diagram component that wraps the "diagram" canvas
   diagram = MindFusion.AbstractionLayer.createControl(Diagram, null, null, null, $("#diagram")[0]);
……….

});

We use the id of the diagram Canvas that we set in the web page and now create the diagram control. Once we have it we set some properties to it:

// set some Diagram properties.
diagram.setBehavior(Behavior.LinkTables);
diagram.setAllowSelfLoops(false);
diagram.setBackBrush('#F0F0F0');
diagram.setLinkHeadShape('Triangle');
diagram.setLinkHeadShapeSize(4);
diagram.getSelection().allowMultipleSelection = false;

We change the default Behavior of the diagram control to “LinkTables”, which means users would be able to connect table rows. We stop users from creating self loops on tables and add some styling: the back brush is set to light gray, the head shape of links is ‘Triangle’ and we forbid the users to select multiple objects.

The styling of the diagram is done through themes. We create a theme and add to it a style for the table nodes:

// set the Diagram style.
var theme = new Theme();

var tableNodeStyle = new Style();
tableNodeStyle.setBrush({ type: 'LinearGradientBrush', color1: 'rgb(224, 233, 233)', color2: 'rgb(102, 154, 204)', angle: 30 });
tableNodeStyle.setTextColor({ type: 'SolidBrush', color: 'rgb(45, 57, 86)' });
tableNodeStyle.setStroke('rgb(192, 192, 192)');

The tableNodeStyle sets the brush, text color and stroke for the tables. Let’s tell the theme object that this is the style for table nodes:

theme.styles['std:TableNode'] = tableNodeStyle;

And let’s tell the diagram control that it has a theme:

diagram.setTheme(theme);

Link styling is done in the same way and you can find the code in the *.zip file that is available for download.

IV. Events

Handling events is the most important part of this application. We have events raised by the diagram elements and we have events that are raised by the JavaScript buttons. Let’s start with the js buttons. When the web page is loaded there is a single button active from the row of buttons available at the bottom of the page – “Create table”. In the document.ready() method we wire the button with an event:

$('#btnCreateTable').button().click(function (event) { createTable(); });

This event calls the createTable method that generates a TableNode instance:

function createTable() {
	// create a new table with the specified extent
	var table = diagram.getFactory().createTableNode(
				15 + tableCount * 3, 15 + tableCount * 4, 50, 60);
	table.setText("Table" + tableCount++);
	table.redimTable(2, 0);
	table.setScrollable(true);
	table.setConnectionStyle(ConnectionStyle.Rows);

	// set the first column to resize with the table
	table.getColumn(0).columnStyle = ColumnStyle.AutoWidth;

	generateSQL();
}

The createTableNode method accepts as arguments the x and y coordinates of the new TableNode and its width and height. We create initially the table with two columns and no rows. By default the tables can be scrolled and the links connect table rows.

The generateSQL method is a simple one – it just creates an SQL table. You can expand the sample with more complicated SQL statements but in our case we just create a table with the columns that were set to the TableNode:

function generateSQL() {
   var text = '';

   // enumerate all tables in the current diagram
   ArrayList.forEach(diagram.nodes, function (table) {
   text += "CREATE TABLE " + table.getText() + "\r\n(";

   // enumerate all rows of a table
   for (var r = 0; r &lt; table.cells.rows; ++r) {
   // get text of cells in current row
   text += &quot;\t&quot; + table.getCell(0, r).getText() + &quot; &quot; + table.getCell(1, r).getText();
   if (r &lt; table.cells.rows - 1)
	 text += &quot;,\r\n&quot;;
   }
	text += &quot;\r\n);\r\n\r\n&quot;;
   });

  $('#generatedSql')[0].innerHTML = text;
}

When the SQL text is generated we assign it to the textarea instance that we created.

V. Diagram Events

Here we will talk about the events fired by the diagram control. Once a table is created the users can double click on it to create new rows, edit or delete existing rows. This happens when we handle the nodeDoubleClicked event:

diagram.addEventListener(Events.nodeDoubleClicked, function (sender, args) {
	if (tblClicked != args.getNode()) {
		tblClicked = args.getNode();
	}
….

});

Here we identify the table that is clicked and then we have to decide which dialogue to show:

if (tblClicked) {
		var cellClicked = tblClicked.cellFromPoint(args.getMousePosition());
		if (cellClicked) {
			rowClicked = cellClicked.row;
			editRowOpen();
		}
		else if (tblClicked.hitTestManipulators(args.getMousePosition()) == null) {
		if (args.getMousePosition().y <= tblClicked.getBounds().y + tblClicked.getCaptionHeight())
			renameTableOpen();
			else
			addRowOpen();
		    }
		}

If an existing cell is clicked we open the editRow form. If the caption of the table was clicked we open the form for rename of a table. If none of those, we open the form that adds a new row.

Let’s look how the addRow dialogue opens:

function addRowOpen() {
  var table = tblClicked || diagram.getActiveItem();

  if (!table || !AbstractionLayer.isInstanceOfType(TableNode, table))
	return;

   addRowDialog.dialog("open");
}

the method calls the dialog method of addRowDialog. At the beginning of the js file we have declared a variable:

var addRowDialog = null

Then we create the addRowDialog object:

addRowDialog = $("#addRow-dialog").dialog({
		autoOpen: false,
		resizable: false,
		height: 'auto',
		width: 250,
		modal: false,
		buttons: {
			"OK": addRow,
			Cancel: function () {
				addRowDialog.dialog("close");
			}
		},
		close: function () {
			addRowType.val("NUMBER");
			addRowType.selectmenu("refresh");
			addRowForm[0].reset();
		}
	});
	addRowForm = addRowDialog.find("form").on("submit", function (event) {
		event.preventDefault();
		addRow();
	});

Here we create the dialog that has auto height, width of 250 and two buttons: OK and Cancel. The Cancel button closes the dialog. When the user has pressed OK the form is submitted and the addRow method is called.

The form that shows is defined in the HTML page and looks like that:

<div id="addRow-dialog" title="New Field">
		<form>
		<fieldset>
			<label for="addRow-fieldName">
				Field name</label>
			
			<label for="addRow-fieldType">
				Field type</label>
			
				NUMBER
				CHAR(32)
				DATE
				VARCHAR
				BLOB
			
		</fieldset>
		</form>
	</div> 

The addRow method gets the clicked table and gets the two cells at the last row. It gets the text that was chosen in the dialog and assigns it to the cells. Then the dialog is closed and the SQL is generated once again.

function addRow() {
	var table = tblClicked || diagram.getActiveItem();

	if (!table || !AbstractionLayer.isInstanceOfType(TableNode, table))
		return;

	table.addRow();

	var lastRow = table.cells.rows - 1;

	// use the cell indexer to access cells by their column and row
	table.getCell(0, lastRow).setText(addRowName[0].value);
	table.getCell(1, lastRow).setText(addRowType[0].value);

	// close the dialog
	addRowDialog.dialog("close");

	// refresh SQL definition
	generateSQL();
}

And that’s the end for this tutorial. You can download the sample together with the necessary JavaScript libraries from this link:

Download the JavaScript Database Designer Application

Find out more about MindFusion JavaScript Diagram Library at https://mindfusion.eu/javascript-diagram.html

A JavaScript Application for Server Load Monitoring

In two blog posts we will look at the main steps to create a sample server load web application. We will use the chart, gauge and diagram libraries. The data is simulated with random numbers.

Server Load Application in JavaScript

Server Load Application in JavaScript

Run The Application

The chart shows number of users on a given connection between two stations in the network at each moment. The graphic includes data for the last 30 seconds. The diagram shows the servers and clients that build the network. By default the charts shows data only for the two most important connections, out of total 10. Users can select different connections and view their graphics. The gauge control provides data for the average count of users at any given moment.

I. Project Setup

We create an empty website and add a Scripts folder with the necessary files:

MindFusion.Gauges.js
MindFusion.Diagramming.js
MindFusion.Common.js
MindFusion.Charting.js
require.js

and a reference to jQuery or the jQuery library itself:

jquery.js

We create a blank HTML file and we create three HTML Canvas elements – one for each of the controls: chart, diagram, gauge.

We use the Flexbox layout and we create a CSS file referenced by the index.html file where we write the CSS settings for the layout:

<link href="common/style.css" rel="stylesheet" />

We initialize a section region that would have a Flexbox layout:

section {
   display: flex;
   max-width: 700px;
}


The CSS class used for <div> elements inside <section>:

.column {
  margin: 10px 10px 0px 0px;
  flex: 1 1 0;
  border: 1px solid #cecece;
}

section:first-of-type .column:first-of-type {
  flex: 1 1 auto;
}

We specify that the first column on the second row would be twice wider than the other column. This is the diagram, and the other column is occupied by the gauge.

section:nth-of-type(2) .column:first-of-type {
  flex: 2 2 22;
}

That’s how the HTML uses the CSS attributes:

<section>
    <div class="column">
      <canvas id="lineChart"></canvas>
    </div>
</section>

Note the id=”lineChart” attribute – we will use the id to initialize the LineChart object in the *.js file. The diagram and gauge Canvas instances also have id-s.

At the end of the index.html we include a reference to the require.js file to load the chart and gauge libraries this way:

<script src="Scripts/diagram.js" type="text/javascript"></script>
<script data-main="charts" src="Scripts/require.js"></script>
</body>

Note: Internet Explorer might not load properly the JavaScript libraries if they are declared at the beginning of the file, (in the head section) before the initialization of the Canvas-es. Therefore it is best to put the script references at the bottom, right before the closing </body> tag.

II. The Gauge

The code for the OvalGauge and the LineChart is in a single method:

var lineChart = null;

define(["require", "exports", 'MindFusion.Common', 'Scripts/MindFusion.Charting', 'MindFusion.Gauges'], function (require, exports, MindFusion_Common_1, m, g) {
    "use strict";
.........
.........
} 

Before the method we declare a global variable for the LineChart. We need to access it in the diagram file so it must have a global visibility.

The OvalGauge control is created using the id of the HTML Canvas:

var userCounter = g.OvalGauge.create($('#userCounter')[0], false);

We will use two Events – raised before the background was painted and before the pointer was painted – to customize how the gauge background and pointer look.

//use custom painting of the background and of the pointer
userCounter.addEventListener(g.Events.prepaintBackground, onGaugerepaintBackground.bind(this));
userCounter.addEventListener(g.Events.prepaintPointer, onPrepaintPointer.bind(this));

The gauge needs a scale – we create an OvalScale and set its min and max value:

var scale = new g.OvalScale(userCounter);
scale.setMinValue(0);
scale.setMaxValue(40);
................

We will also set all three types of settings on the scale – MajorTickSettings, MiddleTickSettings and MinorTickSettings

//initialize the major settings
var majorSettings = scale.majorTickSettings;
majorSettings.setTickShape(TickShape.Ellipse);
..........................

A CustomInterval at the MajorTickSettings indicates a special range. We will use one to paint in red the portion on the scale that corresponds to the high amount of users:

 var interval = new g.CustomInterval();
    interval.setMinValue(35);
    interval.setFill('Red');
    majorSettings.addCustomInterval(interval);

Then we customize the MajorTickSettings, the MiddleTickSettings and MinorTickSettings:

//initialize the middle settings
var middleSettings = scale.middleTickSettings;
middleSettings.setShowLabels(false);
...............

//initalize the minor settings
var minorSettings = scale.minorTickSettings;
minorSettings.setShowLabels(false);
minorSettings.setShowTicks(false);

The scale shows a Range: that is a visual indication of regions on the gauge. It is determined by its setMinValue and setMaxValue values and in our case we will show it on the whole gauge:

//add a range in gradient colors
var range = new g.Range();
range.setMinValue(0);
range.setMaxValue(40);
range.setFill(g.Utils.createLinearGradient(320, [1, '#ce0000', 0.8, '#ce0000', 0.7, '#FFA500', 0.6, '#FFD700', 0.5, '#008000', 0, '#008000']));
...............
scale.addRange(range);

That’s how we handle the PrepaintBackground event to draw custom background for the gauge:

//paint the background in gradient
function onGaugerepaintBackground(sender, args) {
   args.setCancelDefaultPainting(true);
   var context = args.getContext();
   var element = args.getElement();
   var size = sender.getSize();
   var ellipse = new g.Ellipse();
   ellipse.setFill('gray');
   ellipse.setStroke('transparent');
   args.paintVisualElement(ellipse, size);
   var ellipse = new g.Ellipse();
   ellipse.setFill(g.Utils.createLinearGradient(300, [0, '#808080', 0.2, '#808080', 0.8, '#909090', 1, '#909090']));
   ellipse.setStroke('transparent');
   ellipse.setMargin(new g.Thickness(0.015));
   args.paintVisualElement(ellipse, size);
}

The code that handles the prepaint pointer event is similar. The value of the Pointer is set this way:

//add some initial value
pointer.setValue(26);

When the last chart values changes – each second – we update the pointer value:

var pointer = userCounter.scales[0].pointers[0];
pointer.setValue(sum/10);

III. The Chart

First we create the LineChart object from the Canvas that we have initialized in the HTML. We take the width and height of the control from its parent container:

var lineChartEl = document.getElementById('lineChart');
lineChartEl.width = lineChartEl.offsetParent.clientWidth;
lineChartEl.height = lineChartEl.offsetParent.clientHeight;
lineChart = new Controls.LineChart(lineChartEl);

The data for the line series is stored in 10 Series2D instances. For each of them we need a list with the X and Y values. The X-values are the same for all series, the Y-values are randomly generated numbers. All of them are instances of the List class:

var values1 = new Collections.List();
var values2 = new Collections.List();
......................................
var xValues = new Collections.List();
var xLabels = new Collections.List();

Here we fill the xValues list with numbers:

//initialize x-values and labels, generate sample data for the series
for (var i = 0; i &lt; 30; i++) {
  xValues.add(i);
  setXLabels(false);
  generateData();
}

The setXLabels method takes care of the custom labels at the X-axis. At each 3rd call, it removes the first three values and adds three new ones: one with the current time stamp and two more as empty strings.

if (d.getSeconds() % 3 == 0)
   {
     //clear the first three values
     //if the count of the labels is more than 30
     if (removeFirst) {
         xLabels.removeAt(0);
         xLabels.removeAt(0);
         xLabels.removeAt(0);
       }

       //add a label and two empty strings
       xLabels.add(d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds());
       xLabels.add("");
       xLabels.add("");                
    }   

We create the Series2D instances and add them to the Series property of the line chart:

//the series for the chart
var series = new Collections.ObservableCollection(new Array(
    new Charting.Series2D(xValues, values1, xLabels),
    new Charting.Series2D(xValues, values2, null),
    ...........
    new Charting.Series2D(xValues, values10, null)));

The xLables are assigned just to the first series, they will serve as labels source for the X-axis.To show them, we must first hide the coordinates and assign them to the xAxis:

lineChart.xAxis.labels = xLabels;
lineChart.showXCoordinates = false;

then we must “tell” the first series that its labels are used for the XAxis:

//tell the series that the labels are for the X-axis.
series.item(0).supportedLabels = m.MindFusion.Charting.LabelKinds.XAxisLabel;

We use the title property of a Series object to identify the series. That’s why we assign to them unique labels:

//series titles are important - we identify the series with them
for (var i = 0; i < 4; i++)
    series.item(i).title = "Client" + i;

for (var i = 0; i < 3; i++)
    series.item(i + 4).title ="Network" + i;

for (var i = 0; i < 3; i++)
    series.item(i + 7).title ="Data" + i;

Since it is going to be a long chart, we want a second Y-axis to appear to the right. That can be done by adding another YAxisRenderer with the same yAxis to the components rendered by default by the LineChart control. We add the new YAxisRenderer to a vertical StackPanel:

 var y2Stack = new m.MindFusion.Charting.Components.StackPanel();
 y2Stack.orientation = m.MindFusion.Charting.Components.Orientation.Vertical;
 y2Stack.gridRow = 0;
 //add the stack panel to the last grid column
 y2Stack.gridColumn = lineChart.chartPanel.columns.count() - 1;

 lineChart.chartPanel.children.add(y2Stack);

The layout manager for a LineChart is a Grid panel. We add a new column to it, where the second Y-axis will be rendered. Then we add the StackPanel with the YAxisRenderer to this column. Next we add the yAxis and we specify that the plot is not to the left side of it:

//create the second Y-axis
var secondYAxis = new Charting.YAxisRenderer(lineChart.yAxis);
secondYAxis.plotLeftSide = false;
lineChart.yAxisRenderers.add(secondYAxis);
y2Stack.children.add(secondYAxis); 

Then we customize the grid and trigger the timer that will update the data values at each second:

lineChart.gridType = Charting.GridType.Crossed;
lineChart.backColor = new Drawing.Color.fromArgb(230, 230, 230);
lineChart.theme.gridColor1 = Drawing.Color.fromArgb(1, 255, 255, 255);
lineChart.theme.gridColor2 = Drawing.Color.fromArgb(1, 255, 255, 255);
lineChart.theme.gridLineColor = Drawing.Color.fromArgb(0.5, 240, 240, 240);
//start the timer
setInterval(setTime, 1000);

Finally, let’s look at the styling of the series. We keep the brushes in a list. The colors for those brushes are stored in a list with lists – each one with three elements for the red, green and blue values of the color.

//the colors for the brushes
var brushes = new Collections.List();

var rgbColors = new Collections.List();
rgbColors.add(new Array(102, 154, 204));
..............

What we actually do to show the graphics of the connections that are selected in the diagram is thicken the strokes for those line graphics and set the thickness to the rest to 0.15 to make them barely visible.

We do that by using the thicknesses property of the PerSeriesStyle class that we use for styling the chart.

lineChart.plot.seriesStyle = new Charting.PerSeriesStyle(brushes, brushes, thicknesses);

And here is how we create the thicknesses and the brushes:

//create brushes for the chart
var thicknesses = new Collections.List();
  for (var i = 0; i < 10; i++)
   {        
      var a = rgbColors.item(i);
      brushes.add(new Drawing.Brush(new Drawing.Color.fromArgb(a[0], a[1], a[2])));
      if (i == 5 || i == 8)
          thicknesses.add(3.0);
      else
         thicknesses.add(0.15);
      
  }



Only the 5th and 8th thickness are set to 3, the others are almost zero – enough to draw the silhouettes of the graphics.

And that’s all for this part I of the tutorial on how to build the client side of a sample server load monitor application in JavaScript. In part II we will look at the diagram control. You can run the sample from here:

Run the online server load monitor application

Here is the link to download the full source code for the application:

Download Source Code

You can also fork it from GitHub.

Find out more about the chart, gauge and diagram JavaScript libraries from their official pages on the MindFusion website.