Generating a Web diagram using SQL statements

Simply learn the next Nodejs Web Development Framework Express, as well as some related technologies ...

Part of the main reference for Express: the node. JS Development Framework express4.x, node. js + Express Build Site Simple Example

It is important to note that the Express 4.x builder is independent of a module, and if you want to use the Express command in the system, you must install a separate generator:

NPM Install-g Express Express-generator Bower Supervisorexpress-e bower-express

Supervisor is a good thing, code changes can be hot loaded, automatic restart:Supervisor App.js

(a). First look at the main document, App.js:

var express = require (' Express '), Ejs = require (' Ejs '), routes = require ('./routes/index '), users = require ('./routes/user S '), session = require (' express-session '), Rdbstore = require (' express-mysql-session '); var app = Express ();// Define the view path and modify the ejs template suffix name app.set (' views ', ' views '); App.engine ('. html ', ejs.__express); App.set (' View engine ', ' html '); App.use (Require (' Body-parser '). urlencoded ({extended:false}));//define Static file path App.use (express.static (' public '));  App.use (express.static (' bower_components '));//Session Save Const OPTIONS = {host: ' 1.1.1.1 ', User: ' Test ', Port:     5029, Password: ' Test ', Database: ' Test '};app.use ({secret: ' Test ', store:new rdbstore (options), Cookies: {maxage:10000}, Resave:true, saveuninitialized:true});//URL Route app.get ('/', Routes.index). Post ('    /', routes.index);//display 404 error and Output app.use (function (req, res, next) {var err = new error (' Not Found ');    Err.status = 404; Next (err);}); /production Environment Abnormal print if (app.get (' env ') = = = ' DevelopmeNT ') App.use (function (err, req, res, next) {Res.status (Err.status | | 500);    Res.render (' error ', {message:err.message, error:err}); });//Start and Port//require (' http '). Createserver (APP). Listen (+); App.listen (the function () {Console.log (' Express Server listening on 3000 ');});

Since the No-sql database is not installed, MySQL is used to cache the session,

Install with NPM after entering Express-generated projects:NPM installed express-mysql-session

The usage is simple, when you start a project, a new InnoDB table is automatically created in the target library and can be changed to a memory engine:

ALTER TABLE Sessions MODIFY data VARCHAR, engine=memory;

Of course, the use of MONGOs or RETHINKDB will be more convenient, the problem is that the node module is too many a little confused do not know which good (bug) ... Such as:

npm Install express-session-rethinkdb connect-rethinkdb session-rethinkdb

(b). Ok, next look at "action", Routes/index.js:

var router = require (' Express '). Router (), MySQL = require (' mysql '); const OPTIONS = [{host: ' 1.1.1.1 ', User: ' Test ', port:5029, Password: ' Test ', dat Abase: ' Test ', datestrings:true}, {{host: ' 1.1.1.2 ', User: ' Test ', port:3306, Password: ' Test ', Database: ' Test ', datestrings:true}, {{host: ' 1.1.1.3 ', User: ' Test ', port:3307, Password: ' Test ', Database: ' Test ', datestring s:true//, Multiplestatements:true}];module.exports.index = function (req, res) {    var connection, SQL;     if (Req.method = = ' POST ' && req.body && req.body.host &&/^[1-3]$/.test ( Req.body.host) && req.body.title && req.body.sql) {       //var Pool = Mysql.createpool (options[2]);        connection = Mysql.createconnection ( Options[req.body.host]);        sql = req.body.sql;   } else {         res.render (' index ', {            ' Params ': {title: ', sql: ', Host: '}, ' x ': ' [] ', ' y ': ' [] '        };  &n bsp;      return;   }    connection.query (SQL, function ( Err, rows, fields) {        if (err) throw err;         var x = [], y = [];        for (var i in rows) {    &N bsp;      //if (rows[i].x instanceof Date) x[i] = require (' moment ') (rows[i].x). Format ( ' Yyyy-mm-dd ');            x[i] = rows[i].x;             Y[i] = rows[i].y;        }        Res.json ({' X ': x, ' y ': y});    });    connection.end ();}; 

The code is simple, the GET request returns directly to the page (and of course querystring), and the POST request returns the JSON data.

At first do not know Node-mysql has a datestrings parameters, so use Moment.js to convert the date format, is also very convenient.

Use Bower to download the necessary jquery, Bootstrap, ACE, Echarts, and other libraries before you develop the page:

Bower Install Ace Echarts bootstrap jquery

(iii). Finally, on the display page, views/index.html:

<! DOCTYPE html> 
 The above JS code is as follows: 
 
 
$ (' select '). val (<%-params.host%>); var editor = Ace.edit (' editor '), mychart, options = {tooltip: {show:true}, Legend: {data: [' <%-params.title%> ']}, Xaxis: [{type: ' Category ', Data: <%-x%>}], YAxis: [{ Type: ' value '}], series: [{' Name ': ' <%-params.title%> ', ' type ': ' Bar ', ' data ': <%-y%>}]};editor.set Theme (' Ace/theme/sql '); Editor.getsession (). Setusewrapmode (True); Editor.getsession (). SetMode (' ace/mode/sql ');// Path configuration require.config ({paths: {echarts: ' Echarts/build/dist '}});//use require ([' echarts ', ' Echarts/chart/bar '], F        Unction (EC) {MyChart = Ec.init ($ (' #main '). Get (0));    Mychart.setoption (options); });    function query () {mychart.showloading ();        $.ajax ({url:window.location.pathname, cache:false, type: ' POST ', DataType: ' JSON ', Data: $ (' form '). Serialize () + ' &sql= ' + editor.getvalue (), success:function (obj) {Options.leg End.datA[0] = $ (': Text '). Val ();            Options.xaxis[0].data = obj.x;            Options.series[0].data = OBJ.Y;            Mychart.clear ();            Mychart.hideloading ();        Mychart.setoption (options); }    });}
 
Attention:
 
 
 

  
  
 
   
The setOption method of Echarts is to update the operation, so we need to clear the chart and then re-draw;
 
  
 
 
 
 
 

  
  
 
   
ACE is a source code editor, similar to the famous Codemirror, API is the same;
 
  
 
 
 
 
 

  
  
 
   
If the Bower downloaded ACE is not compiled, go to the Ace-builds page to download.
 
  
 
 
 

 
At this point, a simple Web application was born, as Chartsql did, to improve the Ajax exception handling, Node-mysql connection pool and so on.
 
Haven't touched the front-end related technology for a long time, write up a variety of unfamiliar, code is poor, welcome advice. Finally, here's a picture:
 
Generating a Web diagram using SQL statements