sed-like attempt from database

Question

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": "*"
 }
}

• Ismael Miguel: Why do you use ascii? Why not latin1 or utf8?
• James Powell: 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.
• Ismael Miguel: 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/…

Answer 1

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;

• James Powell: 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.
• SirPython: @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).
• James Powell: 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.