3
\$\begingroup\$

The following code and test data works, but I have had to add delays between writes to MySQL otherwise it acts funny (especially on the target data set which is over 700 rows). The table is simply a number and value (the number is another table's 'foreign key(?)'.

I am looking for suggestions on how I might reorganize this for better behavior. Perhaps a Generator? It's written in JavaScript, and uses node.js 0.10, because I believe I need to become much better in JavaScript. The real goal is to be able to change the last two funcs out to whatever data base hack I need to preform today. This code preforms a Name hack, but in the future I plan on different code that would do what ever data field hacking I need. The mental model is sed for MySQL.

I ended up stripping almost all comments out as they were formatting funny.

JavaScript for node.js 0.10

#!/usr/bin/env node
var path = require('path');
var size = require('window-size');
var mysql = require('mysql');


/***************************************************************************************************
* Yargs setup array.  This beast describes the arguments we take, help, and defaults.              *
* See https://github.com/bcoe/yargs for detailed docs                                              *
*                                                                                                  *
***************************************************************************************************/
var argv = require('yargs')
    .usage('Usage: $0 --host=192.168.5.100 --user=dude --pass=SurfsUp! -D LexPaper ')
    .example('$0 --host=192.168.5.100 --user=jep --pass=rats --db=LexPaper')
    .demand(['host', 'user', 'pass', 'db'])
    .describe('host', 'name/IP address of computer with MySQL running')
    .describe('user', 'User name to login to MySQL with')
    .describe('pass', 'Password to login to MySQL with')
    .describe('db', 'MySQL Database that we will be using')
//    .describe('', '')
    .help('h')
    .alias('H', 'host')
    .alias('U', 'user')
    .alias('P', 'pass')
    .alias('D', 'db')
    .alias('h', 'help')
    .alias('h', '?')
    .wrap(size.width - 1)
    .version(function() { return require ('./package.json').version; })
    .argv;


var queryComplete = 0;

console.log ("------------------------------------------------------------- ");
console.log (path.basename(process.argv[1]), " running, args" + " host=" + argv.host + " user='" + argv.user + "' db='" + argv.db + "'");


var connectParms = {
   user     : argv.user,
   password : argv.pass,
   database : argv.db,
   host     : argv.host,
   port     : 3306
 };

// if host name given is the local host, add the socket pipe.
// This is only necessary if my.cnf entry 'bind-address' is not '0.0.0.0'
// This will only work as written on Linux.
/*
if (argv.host == 'localhost' || argv.host == '127.0.0.1') {
    connectParms.socketPath = '/var/run/mysqld/mysqld.sock';
}
*/

var connection = mysql.createConnection(connectParms);

connection.connect(function(err){
    if(!err) {
        console.log("Database is connected ... \n\n");
    } else {
        console.log("Error connecting database ... " + err + "\n\n");
    }
});

console.log ("Submitting query: " + getQueryStr());
connection.query(getQueryStr(), function(err, rows, fields) {
    if (!err) {
        // console.log('Result: ', rows);
        console.log ("Number of rows in result: " + rows.length);

        for (var i = 0; i < rows.length; i++) {
            processRow (i, rows[i]);
        }

        queryComplete = 1;

    } else {
        console.log('Error while performing Query.');
        queryComplete = 1;
    }
});


/***************************************************************************************************
* Take strings in mixed case/all upper case and make them into 'proper case' Examples:             *
* INPUT                    Output                                                                  *
* ACE CHEMICALS            Ace Chemicals                                                           *
* SIONIS STEEL MILL        Sionis Steel Mill                                                       *
*                                                                                                  *
* Code by http://stackoverflow.com/users/360053/tuan                                               *
* Found on StackOverflow:                                                                          *
* http://stackoverflow.com/questions/196972/convert-string-to-title-case-with-javascript/196991#196991
*                                                                                                  *
***************************************************************************************************/
String.prototype.toProperCase = function () {
    return this.replace(/\w\S*/g, function(txt){return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();});
};

/***************************************************************************************************
* Left pad string with pad character.  Examples:                                                   *
* xx="4";    xx.lpad("0", 5) = '00004'                                                             *
* xx="55";   xx.lpad("0", 3) = '055'                                                               *
* xx="155";  xx.lpad("0", 3) = '155'                                                               *
* xx="0";  xx.lpad("0", 3) = '000'                                                                 *
* xx="8";  xx.lpad(" ", 3) = '  8'                                                                 *
*                                                                                                  *
* Code by: http://stackoverflow.com/users/155861/diecho                                            *
* Found on StackOverflow:                                                                          *
* http://stackoverflow.com/questions/10073699/pad-a-number-with-leading-zeros-in-javascript        *
*                                                                                                  *
***************************************************************************************************/
String.prototype.lpad = function(padString, length) {
    var str = this;
    while (str.length < length)
        str = padString + str;
    return str;
};

/***************************************************************************************************
* Replace one or more characters in a string.                                                      *
* We need this because JavaScript strings are immutable, thus what we want, to change a single     *
* character in a string is 'impossible'.  This returns a new string with the changes the caller    *
* wants, allowing them the illusion of a mutable string.                                           *
*                                                                                                  *
* Code by: http://stackoverflow.com/users/173347/cem-kalyoncu                                      *
* Found on StackOverflow:                                                                          *
* http://stackoverflow.com/questions/1431094/how-do-i-replace-a-character-at-a-particular-index-in-javascript
*                                                                                                  *
***************************************************************************************************/
String.prototype.replaceAt = function(index, character) {
    return this.substr(0, index) + character + this.substr(index + character.length);
};


/***************************************************************************************************
* Exit pause.  Since we are just a command line program, we may exit too quickly for MySQL to      *
* reply before we terminate.  This func will either clean up after N half second pauses, or sooner *
* if the global query complete flag has been set.                                                  *
*                                                                                                  *
*                                                                                                  *
***************************************************************************************************/
(function wait () {
    if (typeof wait.counter == 'undefined' ) {
        // No such variable in the wait prototype, invent it
        wait.counter = 0;
    }

    wait.counter += 1;
    console.log ("count= " + wait.counter + "  queryComplete= " + queryComplete);

    if ((wait.counter < 5) && (queryComplete < 2)) {
        if (queryComplete > 0)
            queryComplete += 1;

        setTimeout(wait, 500);

    } else {
        connection.end();
    }
})();


function getQueryStr () {
    var qry = '';
    qry = 'SELECT * from Customers';
    return qry;
}

var convertTable = [
    { search: "Lex Corp.", replacment: "Lex Corp.", searchEnd: false, fixCase: false },

    { search: "COMP.", replacment: "Company",       searchEnd: true, fixCase: true },
    { search: "FACT.", replacment: "Factory",       searchEnd: true, fixCase: true },
    { search: "CORP.", replacment: "Corporation",   searchEnd: true, fixCase: true },
    { search: "INDUS.", replacment: "industries",   searchEnd: true, fixCase: true },
    { search: "PROD.", replacment: "products",      searchEnd: true, fixCase: true },
    { search: "MFG.", replacment: "Manufacturing",  searchEnd: true, fixCase: true },
    { search: "DIST.", replacment: "Distribution",  searchEnd: true, fixCase: true },
    { search: " INC", replacment: " Inc.",         searchEnd: true, fixCase: true }
];

function processRow (row, dat) {
    var oldStr = dat.CustomerName;
    var len = oldStr.length;
    var newStr = "";
    var fixed = false;
    var padStr = "                                                                                                               ";
    var tmpStr = "";
    var orgin = 0;

    //  tmpStr = row.toString().lpad("0", 3);
    //  console.log('ROW' + tmpStr, dat);

    for (var i = 0; i < convertTable.length; i++) {
        if (convertTable[i].searchEnd)
            orgin = oldStr.length - convertTable[i].search.length;
        else
            orgin = 0;

        if (oldStr.indexOf (convertTable[i].search, orgin) != -1) {
            newStr = oldStr.replace (convertTable[i].search, convertTable[i].replacment);

            // console.log ("YYY Search=[" + convertTable[i].search + "] fixCase=" + convertTable[i].fixCase);
            if (convertTable[i].fixCase) {
                newStr = newStr.toProperCase();

                if ((newStr.charAt(1) == "&") || (newStr.charAt(1) == "-") || (newStr.charAt(1) == ".")) {
                    //console.log ("ZZZ");
                    newStr = newStr.replaceAt (2, newStr.charAt(2).toUpperCase());
                }
            }

            fixed = true;
            break;
        }
    }

    if (!fixed) {
        newStr = oldStr.toProperCase();

        if ((newStr.charAt(1) == "&") || (newStr.charAt(1) == "-") || (newStr.charAt(1) == ".")) {
            newStr = newStr.replaceAt (2, newStr.charAt(2).toUpperCase());
        }

        // Strip trailing period
        // newStr = oldStr.slice(0, - 1);
    }

    //  tmpStr = row.toString().lpad("0", 3);
    //  console.log('ROW' + tmpStr + ':  original=[' + oldStr + "]" + padStr.slice(0, 30 - len) + (fixed ? "CUST" : " std") + "=[" + newStr + "]");


    // ---------------------------- All rules applied.  Now put back into data base
    dat.CustomerName = newStr;
    dat.JetDB_CustomerName = oldStr;


    var sql = "UPDATE Customers SET CustomerName=?,JetDB_CustomerName=? WHERE CustomerSequence=?";
    var inserts = [dat.CustomerName,dat.JetDB_CustomerName,dat.CustomerSequence];
    sql = mysql.format(sql, inserts);

    tmpStr = row.toString().lpad('0', 3);
    console.log ('ROW' + tmpStr + ': SQL=', sql);

    connection.query (sql, function(err, result) {
        if (err)
            console.log ("Row# " + row + " ERROR: ", err);
        else
            console.log ("Row# " + row + " successfully changed");
    });

    return;
}

Test environment (not for review)

SQL file that will create a test database:

--
-- Company fix test data set
--

CREATE DATABASE IF NOT EXISTS `LexPaper` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs;
USE LexPaper;

-- DELETE from Customers where CustomerSequence like '%';

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers` (
    `CustomerSequence` int(5) unsigned NOT NULL AUTO_INCREMENT,
    `CustomerName` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL DEFAULT 'nobody',
    `JetDB_CustomerName` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_cs NULL DEFAULT NULL,
    PRIMARY KEY (`CustomerSequence`)
) ENGINE=MyISAM AUTO_INCREMENT=690 DEFAULT CHARSET=latin1;


INSERT into Customers (CustomerName) VALUES ('ACE CHEMICALS'),('ARKHAM PROD.'),('J&P INDU.'),('BLACKGATE TOOL&DIE'),('SIONIS STEEL MILL'),('TWO FACE DIST.');
INSERT into Customers (CustomerName) VALUES ('PENGUINN PROD.'),('PLANT FACT.'),('MILLER MUSIC DIST.'),('SPRANG MFG.'),('ROBINSON PIZZA DIST.'),('R.H. KANE MFG.');
INSERT into Customers (CustomerName) VALUES ('DIXON DOCK DIST.'),('GRANT PARK INDUS.'),('WAYNE FACT.'),('VICTOR FRIES CORP.'),('JOKER COMP.'),('TWO FACE SLOT MACHINE MFG.');
INSERT into Customers (CustomerName) VALUES ('DOCKSIDE PAPER PROD.'),('BASIL KARLO GRAVEL DIST.'),('Lex Corp.'),('Bane Hammers'),('Pennyworth PROD.'),('ZATANNA MAGIC DIST.');
INSERT into Customers (CustomerName) VALUES ('lucius fox mfg.'),('grason climbing prod.'),('NAPIER&COBBLEPOT TOYS'),('Ra\'s al Ghul Knives'),('Daggett Tool&Die'),('FUGATE TIMEPIECES');
INSERT into Customers (CustomerName) VALUES ('Talia al Ghul Publicity'),('BUZZ BRONSKI MFG.'),('HARLEEN QUINZELL COMP.'),('EDWARD NYGMA GAMES');
INSERT into Customers (CustomerName) VALUES ('D&T LINGERIE'),('D&T JETS'),('D&T WHIPS'),('D&T Chains'),('Diana CHIPS'),('Selina Kyle');

-- Getting tired of inventing company names
INSERT into Customers (CustomerName) VALUES ('frank boles'),('SONDRA FULLER'),('NORA FRIES'),('EVA GREEN'),('GREY LORA'),('SOLOMON GRUNDY'),('JILL HAMPTON'),('CHARLOTTE RIVERS');
INSERT into Customers (CustomerName) VALUES ('karin grace'),('HARLEY QUINN'),('IBN HASSAN'),('HOLLY WOOD'),('DOCTOR AESOP'),('DEACON BLACKFIRE'),('DOCTOR PHOSPHORUS'),('BLACK ALICE');
INSERT into Customers (CustomerName) VALUES ('JOE CHILL'),('KILLER CROC'),('KING KRAKEN'),('CARL KRUGER'),('MAGGIE KYLE'),('LADY SHIVA'),('ARNOLD FLASS'),('TERRENCE CLARK');
INSERT into Customers (CustomerName) VALUES ('DAVID CAIN'),('FERRIS BOYLE'),('ROSE WILSON'),('ROXY ROCKET'),('AMANDA WALLER'),('XAVIER SIMON'),('SIMON STAGG'),('VICTOR ZSASZ');
-- INSERT into Customers (CustomerName) VALUES (''),(''),(''),(''),(''),(''),(''),('');


-- Remove BINARY for case insensitive sorting
SELECT * from Customers ORDER by BINARY CustomerName DESC limit 20;

-- DELETE from Customers where CustomerSequence > 689;

Sample command lines showing use:

# Create data base and populate it.  No my user name is not 'dude'.
mysql -h 127.0.0.1 -t -udude -pCowaBunga  <dataSet.sql

# This will re-build the data base.  Not sure if it will work very first time
mysql -h 127.0.0.1 -t -udude -pCowaBunga -D LexPaper <dataSet.sql

# Node.js program.  Magic sequence at start of file allows direct run from command line
./nameFix.js --host=127.0.0.1 --user=dude --pass=CowaBunga --db=LexPaper

# View the table
mysql -h 127.0.0.1 -t -udude -pCowaBunga -D LexPaper -e "SELECT * from Customers;"

package.json

{
  "name": "DB-Hack",
  "description": "Open the MySQL database and strip the trailing periods from all customer names",
  "version": "0.0.1",
  "private": true,
  "dependencies": {
    "mysql": "*",
    "window-size": "^0.1.1",
    "yargs": "*"
  }
}
\$\endgroup\$
3
  • \$\begingroup\$ Why do you use ascii? Why not latin1 or utf8? \$\endgroup\$ Jul 5 '15 at 19:11
  • \$\begingroup\$ The original data base this was based off of used ASCII, by habit I kept it the same. To make this more useful to others in the future I am going to change the encoding to latin1. Other layers required case case insensitive sorting, and at best as I can tell MySQL 5.5 does not support UTF-8 case insensitive behavior. \$\endgroup\$ Jul 8 '15 at 2:13
  • \$\begingroup\$ Actually, MySQL does support it. You have utf8_unicode_ci and utf8_general_ci. I actually recommend using utf8 in this case, since it can, in some ways, be seen as a multi-byte superset of ascii (every valid ascii character, without counting the control characters, is a valid utf8 character.). You can read about the differences on stackoverflow.com/questions/766809/… \$\endgroup\$ Jul 8 '15 at 8:12
4
\$\begingroup\$
if (typeof wait.counter == 'undefined' ) {

Checking if something is undefined by checking it's type is unnecessary. Since undefined is a falsey value, simply putting the thing you'd like to check in a conditional will do the trick:

if( !wait.counter ) {

console.log ("count= " + ++(wait.counter) + "  queryComplete= " + queryComplete);

All those + signs between "count= " and " queryComplete= " are a little confusing. It seems like you tried to make it more readable by putting ()s around wait.counter, but it would be more readable if you just moved ++wait.counter to a line of it's own.

++wait.counter;
console.log ("count= " + wait.counter + "  queryComplete= " + queryComplete);

Thanks to IsmaelMiguel for recommending to move the ++wait.counter out of console.log all together.


Add some documentation to your functions to describe what they are doing, what they need, why they need it, and what it is returning.


This is all I can review, as I am not familiar with SQL.


P.S.

fixed = true;
break;
\$\endgroup\$
3
  • \$\begingroup\$ I don't check here every day, but I wrote if (typeof wait.counter == 'undefined') very consciously. The first time wait() runs there is no such element in the function's object. So I 'invent' the counter. All subsequent loops increment the now present counter. I also remember that in JavaScript a value of 0 is falsey, I need to know the difference between zero and non-existent. \$\endgroup\$ Jul 8 '15 at 2:32
  • \$\begingroup\$ @JamesPowell I am sorry, I don't quite understand what this message means. Is it a question? If you'd like, you could join me in chat and type @SirPython to get my attention, where you could then ask your question(s). \$\endgroup\$
    – SirPython
    Jul 8 '15 at 14:05
  • \$\begingroup\$ Hi, thanks for paying attention. No, my words were not a question. Somewhere above you had indicated that my use of == 'undefined' was un-necesasary, and I was defending my syntax choice. \$\endgroup\$ Jul 9 '15 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.