Skip to content

Instantly share code, notes, and snippets.

@termi
Last active August 29, 2015 14:00
Show Gist options
  • Save termi/11061822 to your computer and use it in GitHub Desktop.
Save termi/11061822 to your computer and use it in GitHub Desktop.
String templates is not so good, as it can be :( . string_template_idea.js looks much better
const mysql = require('mysql');
const crypto = require('crypto');
const color = require('ansi-color').set;
const stringUtils = require('./string.es6');
const DATABASE_NAME = 'test123'
, MESSAGES_TABLE_NAME = 'msgid'
, PLURALS_TABLE_NAME = 'plural'
;
function create_sql(quasis, ...expressions) {
let res = "";
for ( let quasi of quasis ) {
let expression;
if ( expression = expressions.shift() ) {
expression = mysql.escapeId(expression);
}
res += (quasi + (expression == null ? "" : expression));
}
return res;
}
function sqlValues(quasis, ...expressions) {
let res = "";
for ( let quasi of quasis ) {
let expression;
if ( expressions.length ) {
if ( typeof (expression = expressions.shift()) !== 'number' ) {
expression = expression ? ("'" + stringUtils.escape(expression) + "'") : "''";
}
}
res += (quasi + (expression == null ? "" : expression));
}
return res;
}
const sql_createDatabase = create_sql`
CREATE DATABASE IF NOT EXISTS ${DATABASE_NAME}`;
const sql_dropTables = create_sql`
DROP TABLE IF EXISTS ${DATABASE_NAME}.${PLURALS_TABLE_NAME};
DROP TABLE IF EXISTS ${DATABASE_NAME}.${MESSAGES_TABLE_NAME}
`;
const sql_createTables = create_sql`
CREATE TABLE IF NOT EXISTS ${DATABASE_NAME}.${MESSAGES_TABLE_NAME} (
${"id_hash"} char(72) NOT NULL DEFAULT '-',
${"msgid"} text NOT NULL,
${"references"} text NOT NULL DEFAULT '',
${"context"} varchar(64) NOT NULL DEFAULT '',
${"task"} varchar(15) NOT NULL,
${"comment"} varchar(1024),
${"is_plural"} TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (${"id_hash"}),
UNIQUE KEY ${"id_UNIQUE"} (${"id_hash"}),
KEY TASK (${"id_hash"})
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS ${DATABASE_NAME}.${PLURALS_TABLE_NAME} (
${"id_hash"} char(72) NOT NULL DEFAULT '-',
${"msgid_id_hash"} char(72) NOT NULL,
${"rule"} varchar(2) NOT NULL,
${"msgid"} text NOT NULL,
${"comment"} varchar(1024),
PRIMARY KEY (${"id_hash"}),
UNIQUE KEY ${"id_UNIQUE"} (${"id_hash"}),
KEY TASK (${"id_hash"})
) ENGINE=InnoDB DEFAULT CHARSET=utf8`;
let connection, openQuerys = 0;
function openConnection() {
if ( !connection ) {
connection = mysql.createConnection({
host: 'db.dev.example.com',
user: 'test123',
password : 'password123',
multipleStatements: true,
charset: 'UTF8',
debug: true
});
connection.connect();
}
openQuerys++;
return connection;
}
function closeConnection() {
openQuerys--;
if ( openQuerys < 1 ) {
connection.destroy();
connection = void 0;
openQuerys = 0;
}
}
function handleAsyncError(error, callback, ...args) {
if ( error ) {
if ( !(error instanceof Error || ({}).toString.call(error) === '[object Error]') ) {
error = new Error((error || "") + "");
}
console.error(error.stack);
callback(error, ...args);
return true;
}
return false;
}
function query(text, callback) {
let connection = openConnection();
return connection.query(text, function(err, rows) {
if ( handleAsyncError(err, callback, rows) ) {
return false;
}
callback(err, rows);
closeConnection();
});
}
function selectMessages(callback) {
let sqlQuery = create_sql`
SELECT ${"id_hash"}, ${"msgid"}, ${"references"}, ${"context"}, ${"is_plural"}
FROM ${DATABASE_NAME}.${MESSAGES_TABLE_NAME}
ORDER BY ${"msgid"};
SELECT ${"msgid_id_hash"}, ${"rule"}, ${"msgid"}
FROM ${DATABASE_NAME}.${PLURALS_TABLE_NAME}
ORDER BY ${"msgid"};
`;
query(sqlQuery, function(err, results) {
if ( err ) {
console.log(color(' \u{2612} selectMessages', 'red'));
return false;
}
console.log(color(' \u{2611} selectMessages', 'green'));
let po_dictionary = {}, temporaryHashesMap = {};
/*
[ { id_hash: 'b48b0089c20a71d3df7b1888f23206f7de2cf067f451c7132709eaf126105e8aaa59d5f1',
msgid: 'Мобильная версия',
references: 'fest/page/layout/../../promo/big/index.xml:155<,>fest/page/layout/../../promo/1tb/index.xml:39',
context: '',
is_plural: 0 }, ...]
*/
let messageRows = results[0];
for( let msg of messageRows ) {
let {msgid, context, is_plural, id_hash, references} = msg;
let key = msgid + context;
delete msg.id_hash;
delete msg.context;
delete msg["is_plural"];
msg.msgctxt = context;
msg.references = references.split("<,>");
temporaryHashesMap[id_hash] = po_dictionary[key] = msg;
if ( is_plural ) {
msg['msgstr'] = {};
}
}
/*
[ { msgid_id_hash: '1fc65ecd0cca43580e9985fc9703d4c5f1ff97f62ec6afa4535a89e0f464c7a741d69ced',
rule: '2',
msgid: 'Всего %d объектов' }, ...]
*/
let pluralRows = results[1];
for ( let pluralMsg of pluralRows ) {
let {msgid, rule, msgid_id_hash} = pluralMsg;
let msg = temporaryHashesMap[msgid_id_hash];
if ( typeof msg !== 'object' || !msg ) {
throw new Error("Can't find message for plural forms");
}
msg['msgstr'][rule] = msgid;
}
temporaryHashesMap = void 0;
callback(err, po_dictionary);
});
}
function insertMessages(po_dictionary, callback) {
let sqlQuery = create_sql`
INSERT INTO ${DATABASE_NAME}.${MESSAGES_TABLE_NAME} (${"id_hash"}, ${"msgid"}, ${"references"}, ${"context"}, ${"task"}, ${"is_plural"})
VALUES `;
let queryParts = [];
let pluralQueryParts = [];
for ( let key of Object.keys(po_dictionary) ) {
let msg = po_dictionary[key];
let {msgid, msgctxt: context, msgstr, references} = msg;
let id_hash = crypto.createHash('sha1').update(key).digest('hex') + crypto.createHash('md5').update(key).digest('hex');
let is_plural = msgstr && typeof msgstr === 'object'
, task = ''
;
msg.id_hash = id_hash;
if ( is_plural ) {
let msgid_id_hash = id_hash;
for ( let rule of Object.keys(msgstr) ) {
let pluralMsg = msgstr[rule];
let key = rule + pluralMsg;
let id_hash = crypto.createHash('sha1').update(key).digest('hex') + crypto.createHash('md5').update(key).digest('hex');
pluralQueryParts.push(sqlValues`(${id_hash}, ${msgid_id_hash}, ${rule}, ${pluralMsg})`);
}
}
queryParts.push(sqlValues`(${id_hash}, ${msgid}, ${references.join("<,>")}, ${context}, ${task}, ${is_plural ? 1 : 0})`);
}
sqlQuery += queryParts.join(",");
sqlQuery += create_sql`\nON DUPLICATE KEY UPDATE ${"msgid"}=VALUES(${"msgid"}),${"context"}=VALUES(${"context"});`;
if ( pluralQueryParts.length ) {
sqlQuery += create_sql`
INSERT INTO ${DATABASE_NAME}.${PLURALS_TABLE_NAME} (${"id_hash"}, ${"msgid_id_hash"}, ${"rule"}, ${"msgid"})
VALUES `;
sqlQuery += pluralQueryParts.join(",");
sqlQuery += create_sql`\nON DUPLICATE KEY UPDATE ${"msgid"}=VALUES(${"msgid"}),${"rule"}=VALUES(${"rule"});`;
}
query(sqlQuery, function(err, results) {
if ( err ) {
console.log(color(' \u2612 insertMessages', 'red'));
}
else {
console.log(color(' \u2611 insertMessages', 'green'));
}
callback(err, results);
});
}
module.exports = {
createDB(callback = function(err) {if( err )throw err;}) {
query(`
${sql_createDatabase};
${sql_createTables};
`, callback);
}
, dropDB(callback = function(err) {if( err )throw err;}) {
query(`
${sql_dropTables}
`, callback);
}
, openConnection
, closeConnection
, selectMessages
, query
, insertMessages
, messages(po_dictionary, callback) {
insertMessages(po_dictionary, (err) => {
if ( !err ) {
selectMessages(callback);
}
else {
callback(err);
}
});
}
, show() {
console.log(`
CREATE:
${sql_createDatabase};
${sql_createTables};
DROP:
${sql_dropTables}
`);
}
, describe(callback = function(err) {if( err )throw err;}) {
query(`
DESCRIBE ${DATABASE_NAME}.${MESSAGES_TABLE_NAME};
DESCRIBE ${DATABASE_NAME}.${PLURALS_TABLE_NAME}
`, callback);
}
};
const mysql = require('mysql');
const crypto = require('crypto');
const color = require('ansi-color').set;
const stringUtils = require('./string.es6');
const DATABASE_NAME = 'test123'
, MESSAGES_TABLE_NAME = 'msgid'
, PLURALS_TABLE_NAME = 'plural'
;
function create_sql(quasis, ...expressions) {
let res = "";
for ( let quasi of quasis ) {
let expression;
if ( expression = expressions.shift() ) {
expression = mysql.escapeId(expression);
}
res += (quasi + (expression == null ? "" : expression));
}
return res;
}
function sqlValues(quasis, ...expressions) {
let res = "";
for ( let quasi of quasis ) {
let expression;
if ( expressions.length ) {
if ( typeof (expression = expressions.shift()) !== 'number' ) {
expression = expression ? ("'" + stringUtils.escape(expression) + "'") : "''";
}
}
res += (quasi + (expression == null ? "" : expression));
}
return res;
}
const sql_createDatabase = create_sql#\t`
CREATE DATABASE IF NOT EXISTS ${DATABASE_NAME}`;
const sql_dropTables = create_sql#\t`
DROP TABLE IF EXISTS ${DATABASE_NAME}.${PLURALS_TABLE_NAME};
DROP TABLE IF EXISTS ${DATABASE_NAME}.${MESSAGES_TABLE_NAME}
`;
const sql_createTables = create_sql#\t`
CREATE TABLE IF NOT EXISTS ${DATABASE_NAME}.${MESSAGES_TABLE_NAME} (
#{id_hash} char(72) NOT NULL DEFAULT '-',
#{msgid} text NOT NULL,
#{references} text NOT NULL DEFAULT '',
#{context} varchar(64) NOT NULL DEFAULT '',
#{task} varchar(15) NOT NULL,
#{comment} varchar(1024),
#{is_plural} TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (#{id_hash}),
UNIQUE KEY #{id_UNIQUE} (#{id_hash}),
KEY TASK (#{id_hash})
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS ${DATABASE_NAME}.${PLURALS_TABLE_NAME} (
#{id_hash} char(72) NOT NULL DEFAULT '-',
#{msgid_id_hash} char(72) NOT NULL,
#{rule} varchar(2) NOT NULL,
#{msgid} text NOT NULL,
#{comment} varchar(1024),
PRIMARY KEY (#{id_hash}),
UNIQUE KEY #{id_UNIQUE} (#{id_hash}),
KEY TASK (#{id_hash})
) ENGINE=InnoDB DEFAULT CHARSET=utf8`
;
let connection, openQuerys = 0;
function openConnection() {
if ( !connection ) {
connection = mysql.createConnection({
host: 'db.dev.example.com',
user: 'test123',
password : 'password123',
multipleStatements: true,
charset: 'UTF8',
debug: true
});
connection.connect();
}
openQuerys++;
return connection;
}
function closeConnection() {
openQuerys--;
if ( openQuerys < 1 ) {
connection.destroy();
connection = void 0;
openQuerys = 0;
}
}
function handleAsyncError(error, callback, ...args) {
if ( error ) {
if ( !(error instanceof Error || ({}).toString.call(error) === '[object Error]') ) {
error = new Error((error || "") + "");
}
console.error(error.stack);
callback(error, ...args);
return true;
}
return false;
}
function query(text, callback) {
let connection = openConnection();
return connection.query(text, function(err, rows) {
if ( handleAsyncError(err, callback, rows) ) {
return false;
}
callback(err, rows);
closeConnection();
});
}
function selectMessages(callback) {
let sqlQuery = create_sql#\t`
SELECT #{id_hash}, #{msgid}, #{references}, #{context}, #{is_plural}
FROM ${DATABASE_NAME}.${MESSAGES_TABLE_NAME}
ORDER BY #{msgid};
SELECT #{msgid_id_hash}, #{rule}, #{msgid}
FROM ${DATABASE_NAME}.${PLURALS_TABLE_NAME}
ORDER BY #{msgid};
`;
query(sqlQuery, function(err, results) {
if ( err ) {
console.log(color(' \u{2612} selectMessages', 'red'));
return false;
}
console.log(color(' \u{2611} selectMessages', 'green'));
let po_dictionary = {}, temporaryHashesMap = {};
/*
[ { id_hash: 'b48b0089c20a71d3df7b1888f23206f7de2cf067f451c7132709eaf126105e8aaa59d5f1',
msgid: 'Мобильная версия',
references: 'fest/page/layout/../../promo/big/index.xml:155<,>fest/page/layout/../../promo/1tb/index.xml:39',
context: '',
is_plural: 0 }, ...]
*/
let messageRows = results[0];
for( let msg of messageRows ) {
let {msgid, context, is_plural, id_hash, references} = msg;
let key = msgid + context;
delete msg.id_hash;
delete msg.context;
delete msg["is_plural"];
msg.msgctxt = context;
msg.references = references.split("<,>");
temporaryHashesMap[id_hash] = po_dictionary[key] = msg;
if ( is_plural ) {
msg['msgstr'] = {};
}
}
/*
[ { msgid_id_hash: '1fc65ecd0cca43580e9985fc9703d4c5f1ff97f62ec6afa4535a89e0f464c7a741d69ced',
rule: '2',
msgid: 'Всего %d объектов' }, ...]
*/
let pluralRows = results[1];
for ( let pluralMsg of pluralRows ) {
let {msgid, rule, msgid_id_hash} = pluralMsg;
let msg = temporaryHashesMap[msgid_id_hash];
if ( typeof msg !== 'object' || !msg ) {
throw new Error("Can't find message for plural forms");
}
msg['msgstr'][rule] = msgid;
}
temporaryHashesMap = void 0;
callback(err, po_dictionary);
});
}
function insertMessages(po_dictionary, callback) {
let sqlQuery = create_sql#\t`
INSERT INTO ${DATABASE_NAME}.${MESSAGES_TABLE_NAME} (#{id_hash}, #{msgid}, #{references}, #{context}, #{task}, #{is_plural})
VALUES `;
let queryParts = [];
let pluralQueryParts = [];
for ( let key of Object.keys(po_dictionary) ) {
let msg = po_dictionary[key];
let {msgid, msgctxt: context, msgstr, references} = msg;
let id_hash = crypto.createHash('sha1').update(key).digest('hex') + crypto.createHash('md5').update(key).digest('hex');
let is_plural = msgstr && typeof msgstr === 'object'
, task = ''
;
msg.id_hash = id_hash;
if ( is_plural ) {
let msgid_id_hash = id_hash;
for ( let rule of Object.keys(msgstr) ) {
let pluralMsg = msgstr[rule];
let key = rule + pluralMsg;
let id_hash = crypto.createHash('sha1').update(key).digest('hex') + crypto.createHash('md5').update(key).digest('hex');
pluralQueryParts.push(sqlValues`(${id_hash}, ${msgid_id_hash}, ${rule}, ${pluralMsg})`);
}
}
queryParts.push(sqlValues`(${id_hash}, ${msgid}, ${references.join("<,>")}, ${context}, ${task}, ${is_plural ? 1 : 0})`);
}
sqlQuery += queryParts.join(",");
sqlQuery += create_sql`\nON DUPLICATE KEY UPDATE #{msgid}=VALUES(#{msgid}),#{context}=VALUES(#{context});`;
if ( pluralQueryParts.length ) {
sqlQuery += create_sql#\t`
INSERT INTO ${DATABASE_NAME}.${PLURALS_TABLE_NAME} (#{id_hash}, #{msgid_id_hash}, #{rule}, #{msgid})
VALUES `;
sqlQuery += pluralQueryParts.join(",");
sqlQuery += create_sql`\nON DUPLICATE KEY UPDATE #{msgid}=VALUES(#{msgid}),#{rule}=VALUES(#{rule});`;
}
query(sqlQuery, function(err, results) {
if ( err ) {
console.log(color(' \u2612 insertMessages', 'red'));
}
else {
console.log(color(' \u2611 insertMessages', 'green'));
}
callback(err, results);
});
}
module.exports = {
createDB(callback = function(err) {if( err )throw err;}) {
query(#\t`
${sql_createDatabase};
${sql_createTables};
`, callback);
}
, dropDB(callback = function(err) {if( err )throw err;}) {
query(#\t`
${sql_dropTables}
`, callback);
}
, openConnection
, closeConnection
, selectMessages
, query
, insertMessages
, messages(po_dictionary, callback) {
insertMessages(po_dictionary, (err) => {
if ( !err ) {
selectMessages(callback);
}
else {
callback(err);
}
});
}
, show() {
console.log(#\t`
CREATE:
${sql_createDatabase};
${sql_createTables};
DROP:
${sql_dropTables}
`);
}
, describe(callback = function(err) {if( err )throw err;}) {
query(#\t`
DESCRIBE ${DATABASE_NAME}.${MESSAGES_TABLE_NAME};
DESCRIBE ${DATABASE_NAME}.${PLURALS_TABLE_NAME}
`, callback);
}
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment