Build a workflow with MySQL _ MySQL

To learn how to use MySQL in Node, let's look at a program that requires RDBMS. Suppose you want to create a Web program to record how you spent working days. This requires recording of the date of work, the time spent on work, and descriptions of work completion. To learn how to use MySQL in Node, let's look at a program that requires RDBMS.
Suppose you want to create a Web program to record how you spent working days. This requires recording of the date of work, the time spent on work, and descriptions of work completion.

1. System Analysis 1.1 system flow this program will have a form for entering detailed information about the work,

? After the work information is entered, it can be archived or deleted so that it is no longer displayed above the input field used to enter more work ,. Click the "Archived Work" link to display all the previously Archived Work items.

? 1.2 System task creation program logic creation auxiliary function compilation required for program work allows you to use MySQL to add, delete, update and obtain data functions to compile rendering HTML records and form code 1.3 use Module

This program will use the http Module built in Node to implement the Web server function, and use a third-party module to interact with the MySQL server. A custom module named timetrack is a function specific to a program used to store, modify, and obtain data in MySQL. -4 is the overview of this program.
Run the following command to install this popular MySQL Node module:

npm install mysql

?

1.4 Final results

As shown in the final result 5-5, a simple Web program can be used to record the work and review, archive, and delete work records.

?

2. logic for creating a program

Next, we need to create two file storage program logics. The two files are: timetrack_server.js, which is used to start the program; timetrack. js, which contains modules related to program functions.
Create timetrack_server.js first, and put the code in listing 5-7. This code contains Node's HTTPAPI, program-specific logic, and MySQL API. Enter the host, user, and password settings based on your MySQL configuration.

var http = require( 'http' ) ;var work = require( './lib/timetrack' ) ;var mysql = require( 'mysql' ) ;var db = mysql.createConnection( {    host: '127.0.0.1',    user: 'root',    password: 'root',    database: 'timetrack'} ) ;

Next, add the logic in code listing 5-8 to define the Web program behavior. You can use this program to browse, add, and delete work execution records. You can also archive work records. Archived work records are no longer displayed on the home page, but can be viewed on a separate web page.

var server = http.createServer( function (req, res) {    switch ( req.method ) {        case 'POST': {            switch ( req.url ) {                case '/': {                    work.add( db, req, res ) ;                    break ;                }                case '/archive': {                    work.archive( db, req, res ) ;                    break ;                }                case '/delete': {                    work.delete(db, req, res) ;                    break ;                }            }            break ;        }        case 'GET': {            switch ( req.url ) {                case '/': {                    work.show( db, res ) ;                    break ;                }                case 'http://blog.csdn.net/archived': {                    work.showArchived( db, res ) ;                    break ;                }            }            break ;        }    }} ) ;

Code Listing 5-9 is the last piece of code in timetrack_server.js. This code creates a database table (if it does not exist). start the HTTP server and listen to port 3000 of the local machine. All node-mysql queries are executed using the query function.

db.query(    'create table if not exists work ( ' +    'id int(10) not null auto_increment, ' +    'hours decimal(5, 2) default 0, ' +    'date date, ' +    'archived int(1) default 0, ' +    'description longtext, ' +    'primary key(id) )',    function (err) {        if (err) throw err ;        console.log( 'Server started...' ) ;        server.listen( 3000, '127.0.0.1' ) ;    }) ;

3. create helper functions to send HTML, create forms, and receive form data

The file for starting the program has been completed, and the file for defining other functions of the program has been created. Create a directory named lib, and then create the file timetrack. js under this directory. Put the code in the code list 5-10 into this file, which contains the Node querystring API and defines auxiliary functions to send Web page HTML and receive data submitted through the form.

npm install querystring

var qs = require( 'querystring' ) ;exports.sendHtml = function (res, html) {    res.setHeader( 'Content-Type', 'text/html' ) ;    res.setHeader( 'Content-Length', Buffer.byteLength( html ) ) ;    res.end( html ) ;} ;exports.parseReceivedData = function (req, cb) {    var body = '' ;    req.setEncoding( 'utf8' ) ;    req.on( 'data', function (chunk) {        body = chunk ;    } ) ;    req.on( 'end', function () {        var data = qs.parse( body ) ;        cb( data ) ;    } ) ;} ;exports.actionForm = function (id, path, label) {    var html = '

' ; return html ; } ;4. use MySQL to add data

The auxiliary functions are in place. you need to write the code for adding work records to the MySQL database. Add the code in the following code list to timetrack. js.

exports.add = function (db, req, res) {    exports.parseReceivedData( req, function (work) {        db.query(            'insert into work( hours, date, description ) ' +            'values ( ?, ?, ? )',            [ work.hours, work.date, work.description ],            function (err) {                if (err) throw err ;                exports.show( db, res ) ;            }        ) ;    } ) ;} ;

Pay attention to the question mark (?) in the code above (?), This is a placeholder that specifies where the parameter should be placed. Before being added to a query statement, the query method automatically escapes parameters to prevent SQL injection attacks. In addition, pay attention to the second parameter of the query method, which is a string used to replace the placeholder value.

5. delete MySQL data

exports.delete = function (db, req, res) {    exports.parseReceivedData(req, function (work) {        db.query(            'delete from work where id = ?',            [work.id],            function (err) {                if (err) throw err ;                exports.show( db, res ) ;            }        ) ;    } ) ;} ;

6. update MySQL data

To implement the update workflow logic, mark it as archived and add the following code to timetrack. js.

exports.archive = function (db, req, res) {    exports.parseReceivedData( req, function (work) {        db.query(            'update work set archived = 1 where id = ?',            [work.id],            function (err) {                if (err) throw err ;                exports.show( db, res ) ;            }        ) ;    } ) ;} ;

7. obtain MySQL data

The logic for adding, deleting, and updating work records has been defined. now you can add the logic in code listing 5-14 to timetrack, it is used to obtain work record data (archived or unarchived) and render it as HTML. A callback function is input when a query is initiated. its parameter rows is used to save the returned query results.

exports.show = function (db, res, showArchived) {    console.log( 'in show function' ) ;    var query = 'select * from work ' +       'where archived = ? ' +       'order by date desc ' ;    var archiveValue = (showArchived) ? 1 : 0 ;    console.log( 'archiveValue:' + archiveValue ) ;    db.query(        query,        [archiveValue],        function (err, rows) {            console.log( rows ) ;            if (err) throw err ;            html = (showArchived)                ? ''                : 'Archived Work' ;            html += exports.workHitlistHtml( rows ) ;            html += exports.workFormHtml() ;            exports.sendHtml(res, html) ;        }    ) ;} ;exports.showArchived = function (db, res) {    exports.show(db, res, true) ;}

8. rendering MySQL records

Add the code in the following code list to timetrack. js. It renders the work records as HTML.

exports.workHitlistHtml = function (rows) {    var html = '' ;    for( var i in rows ) {        html += '' ;        html += ''        html += ''        html += ''        if ( !rows[i].archived ) {            html += ''        }        html += '' ;    }    html += '

' + rows[i].date + '

' + rows[i].hours + '

' + rows[i].description + '

' + exports.workArchiveForm( rows[ i ].id ) + '

' +exports.workDeleteForm( rows[i].id )+ '

' ; return html ; } ;9. rendering HTML forms

exports.workFormHtml = function () {   var html = '

' ; return html ; } ; exports.workArchiveForm = function (id) { return exports.actionForm(id, '/archive', 'Archive') ; } ; exports.workDeleteForm = function (id) { return exports.actionForm( id, '/delete', 'Delete' ) ; } ;10. try it.

The program has been completed and can be run now. Remember to use the MySQL management tool to create a database named timetrack. Run the following command in the command line to start the program:

node timetrack_server.js

Finally, access http: // 127.0.0.1: 3000 in the browser.