Suche
Suche Menü

MySQL Documentation with node.js

MySQL Documentation with node.js

For a customer i´ve been developing a complexe mysql database with more than 130 functions and stored procedures. Thinking of documentation, i started at the beginning with a classical word document (because the project seems to be pretty simple at the beginning). After growing complex i tried to find a solution for automatic documentation of mysql databases. Unfortunately, i found nearly nothing, the only program i´v found was very expensive and rather complex.

So i decided to write a documentation tool on my own.

My requirements where the following:

  • Automatical generation of the documentation out of the source code
  • a basic structure and navigation
  • Optional documentation of relationships with Graphviz (inspired by doxygen, as the whole project was)
  • Effort should not be too high
  • Based on open source

Because i´ve read a lot of really useful turorials on several websites i would like to give something back to the community and hope this one will help someone else. As soon as i have a first version of this tool finished i will upload it to github so that everyone can use this tool.

Update: Now the initial version can be d0wnloaded at github:

The tutorial is structured into the following chapters:

 


Preparation and information collection

First of all i started with playing arround with the information about tables, colums, functions, stored procedures, views etc within mysql.

For stored Procedures and Functions, i found the following query useful:

To avoid display of BLOB (Binary large Objects) it is recommended to convert to utf8:

Calling for a test Function testFunc1 would return the following result:

 

If only information about a specific procedure or function is needed, simply query for the name of the routine:

For Tables the query is quite similar:

And for more Details on each table:

Which brings for our test table tblTest1 the following result:

Of Course you can also get the CREAT TABLE syntax with:

Which brings for our test table the following result:

Now all information can be collected which is needed for a basic documentation.

Important for a good documentation is to comment all tables, columns, procedures etc. within the code. We will read them out and display them for better overview.


Node.js Installation and create Project with WebStorm

After playing aroung with C based code generation, i´ve found it way to much efforts and decided to switch over to node.js.

The main advantages of node.js:

  • Javascript, easier to write and maintain, use of object orientation (sort of, i know)
  • Can also work with the file system
  • MySQL module for convenient access to the database
  • easy to set up web server
  • with Express routing for a REST-Access will be a no brainer
  • With Jade, a HTML Template tool, it would be very easy to set up different sites for information representation

Personally, i use webstorm for working with node.js, a javascript IDE which bases on JetBrains, but every Text- and CodeEditor will work also.

First, we start with a new Project in Webstorm. For convenience, we allow WebStorm to create a node.js Express App, which creates all the necessary files and includes automatically:

Webstorm_newProject

The next window, we simply accept without any changes.

Webstorm_newProject2After a few seconds and some magic, WebStorm will ask you for the nodejs installation. If you want to be clean, simply accept the proposal to load a fresh installation into your project:

Webstorm_newProject3

Personally i like to idea of working with clean installations but you can of course also use your global installation of node.js by changing to Directory.

Now WebStorm download node.js (which might take a while, get a fresh coffee meanwhile ;))


Project Structure

Finally, the installation has been finished and we now can take a first look into the project´s structure:

Webstorm_project1At external Libraries, the node.js core modules and extended modules are shown. For more information about node.js and the modules, please have a look at the project side of node.js.

For our project, the following perspective in the structure is more convenient and focused:

Webstorm_project2As you can see, a bunch of commands are already saved into the several files. For this little tutorial, a complete introduction into node.js would be way to much so we will now concentrate on the most important parts.


Setting up the Project (with WebStorm IDE)

We have a look intro the main file, called app.js.

It loads a couple of modules and set some basic configuration, like the view engine (jade) or the static path for the public files. This means, that all below the folder „public“ will be available from root dir at a webcall. So there all our client side code will be saved.

The other folders are for the server side of our program.

Views contain all or view files, written in jade.

routes contains all our routes which means we will have a complete RESTful path routing within our application.

For central configuration information, we add a config.js file at the root folder of our project with the following content:

and of course we load the configuration at the app.js:

The package.json defines the general installation information for the project, and – most important – which modules should be installed: Because we need the module node-mysql we need to add this at the end of the json file:

(The star means, that always the newest version will be loaded).

And we need to load the mysql-module at the app.js:


Defining the Routes

Now we are going to plan our routes. The main route for the root dir has already been defined by WebStorm:

routes has been loaded earlier within the file:

Which means, routes is pointing to the file index.js at the subfolder routes.

This small example shows the basic principle of hosting websites with node.jos, express and jade. Express receive a request over the included http server. If this requests points to the root directory of the hosted website (‚/‘) it passed the request to ther file routes/index.js where the path of the request will be analyzed again.

For our example, the root call (‚localhost:port) will be catched and then a response will be created with the result of the render call with jade:

This line calls jade with the jade file index.jade and passes a JSON object with one member, the title. Of course it is possible to pass much larger and more objects as we will se later.

The jade template engine makes it much easier to write html structures. And, it is possible to use some javascript constructs, like if–else clauses, for each etc. For reuse of code it is also possible to include jade files into other jade files. More information about jade can be found at the project page.

By default a layout.jade file provides a template for generic header information etc.

This template will now be included into the jade file for our index site:

These few lines of codes generates a a complete html strucure where the JSON Object information will be injected. The included css and jquery files are hosted at the public folder.

And the result looks like this:

main_site

Now we plan our other routes. For convenience, there will be three parts of routes:

http://localhost/menu (shows a menu where tables, procedures etc, can be selected etc.)

http://localhost/list (shows a complete list of talbes, procedures etc.)

http://localhost/print (shows a printable version of the complete lists

We could also use the same principle as before with separate files for all of these three route categories. But we need some database action and want to reuse as much code as possible so i decided to write a class which has different methods for the routes. These class methods will than be called instead of files as you can see below:


The Wrapper Class dBConn

The class dBConn encapsulates all database queries and generates JSON Objects which will be passed to jade templates to generate the html output.

The basic structure looks like this:

To make the code as reusable as possible, two query function will be implemented: One for Stored Procedures and Functions, the other for Tables, Column etc.

These two functions will be called from the different methods of the class which will than pass the results to jade templates.

So the UML class diagram looks like this:

dBConn-UML

The variable that is neccessary because the normal this causes sometimes problems with nested functions (according to some websites).

config is the configuration setting, loaded from the config.js file. It is neccessary to address the right database as well as fill in the Title, author etc. for the printable versions.

pool is a connection pool for mysql, from which the procedures get a connection, use it and release it after finished query. Because of the asynchronous nature of node.js and javascript, it is recommended to use a pool (where connections can be limited etc) to get better control over the mysql connections.

The procedures from procedures(req,res) on are calling internally either querySP or queryTable to geht the data sets from the database.

For a list of all procedures, the method will look like this:

querySP passes a callback function which work with the results. In this case, an error object and a rows object will be returned. The error object can be used to signal if something went wrong and give detailed information (error codes etc.). Rows is the resultset from the database, which now will be passed to the jade template allong with a title.

At the jade template we now can fill in for each row of the resultset the information and css will give us a more convenient design:

Of course we can deliver besides the css file the jQuery framework and a js file to perform some interactive action.

For instant, to give the CREATE PROCEDURE source and the comment entry a more readable look, i wrote a short js function which substitutes \r\n chars with <br \>:


Results

And now let us see what we get from our tool so far:

A call of http://localhost/list/procedures would give us the following result for our test database:

list-procedures

 

The printable version, called with http://localhost/print/procedures, would give us the following result for our test database:

List of Stored Procedures

List of Stored Procedures2

 

The same results can be achieved for the tables by calling:

http://localhost/list/tables:

list-tables

 

And http://localhost/print/tables:

List of all Tables

List of all Tables2

 

For selecting only one stored procedure or table within the list option, simply call the REST APP like this:

http://localhost/procedure/testProc1

or

http://localhost/procedure/tblTest1

Our routes will forward this to the dedicated method of our wrapper class, which grabs the name of the function out of the parameter list and calls the query function with the optional name of the parameter function. This changes the query with additional seletion of the procedure or table.

Next step in my plan is to create graphical relations pictures between the stored procedures and functions like you maybe know from doxygen and add them to the lists.

I hope this tutorial was helpful and i appreciate any comment, question, hint etc. at the comments to this page.

Schreibe einen Kommentar