felixge/node-mysql — pull request #546 by tshemsedinov (2013-07-15 11:40:15+00:00)
Title: Implemented mysql data access utilities and system metadata intraspection

Connection methods: queryRow, queryValue, queryArray, queryKeyValue, count, primary, foreign, fields, databases, tables, tableInfo

See usage examples:

connection.queryRow('SELECT * FROM _Language where LanguageId=?', [3], function(err, row) {
console.dir({queryRow:row});
/* Example:
row: {
LanguageId: 3,
LanguageName: 'Russian',
LanguageSign: 'ru',
LanguageISO: 'ru',
Caption: 'Русский'
}
*/
});

connection.queryValue('SELECT LanguageName FROM _Language where LanguageId=?', [8], function(err, name) {
console.dir({queryValue:name});
/* Example:
name: 'Italiano'
*/
});

connection.queryArray('SELECT LanguageSign,LanguageId,LanguageName,Caption,LanguageISO FROM _Language', [], function(err, arr) {
console.dir({queryArray:arr});
/* Example:
arr: {
en: {
LanguageSign: 'en',
LanguageId: 2,
LanguageName: 'English',
Caption: 'Английский',
LanguageISO: 'en' },
ru: {
LanguageSign: 'ru',
LanguageId: 3,
LanguageName: 'Russian',
Caption: 'Русский',
LanguageISO: 'ru' },
de: {
LanguageSign: 'de',
LanguageId: 7,
LanguageName: 'Deutsch',
Caption: 'Немецкий',
LanguageISO: 'de' },
it: {
LanguageSign: 'it',
LanguageId: 8,
LanguageName: 'Italiano',
Caption: 'Итальянский',
LanguageISO: 'it' }
}
*/
});

connection.queryKeyValue('SELECT LanguageId,LanguageName FROM _Language', [], function(err, keyValue) {
console.dir({queryKeyValue:keyValue});
/* Example:
keyValue: {
'1': 'Unknown',
'2': 'English',
'3': 'Russian',
'4': 'Ukrainian',
'5': 'Espanol',
'6': 'Francais',
'7': 'Deutsch',
'8': 'Italiano',
'9': 'Poliski'
}
*/
});

connection.count('_Language', false, function(err, count) {
console.dir({count:count});
/* Example:
count: 9
*/
});

connection.primary('_Language', function(err, primary) {
console.dir({primary:primary});
/* Example:
primary: {
Table: '_language',
Non_unique: 0,
Key_name: 'PRIMARY',
Seq_in_index: 1,
Column_name: 'LanguageId',
Collation: 'A',
Cardinality: 9,
Sub_part: null,
Packed: null,
Null: '',
Index_type: 'BTREE',
Comment: '',
Index_comment: ''
}
*/
});

connection.foreign('_Language', function(err, foreign) {
console.dir({foreign:foreign});
/* Example:
foreign: []
*/
});

connection.fields('_Language', function(err, fields) {
console.dir({fields:fields});
/* Example:
fields: [
{
Field: 'LanguageId',
Type: 'int(10) unsigned',
Collation: null,
Null: 'NO',
Key: 'PRI',
Default: null,
Extra: 'auto_increment',
Privileges: 'select,insert,update,references',
Comment: 'Id(EN),Код(RU)'
}, {
Field: 'LanguageName',
Type: 'varchar(32)',
Collation: 'utf8_general_ci',
Null: 'NO',
Key: 'UNI',
Default: null,
Extra: '',
Privileges: 'select,insert,update,references',
Comment: 'Name(EN),Имя(RU)'
}, {
Field: 'LanguageSign',
Type: 'char(2)',
Collation: 'utf8_general_ci',
Null: 'NO',
Key: 'UNI',
Default: null,
Extra: '',
Privileges: 'select,insert,update,references',
Comment: 'Sign(EN),Знак(RU)'
}, {
Field: 'LanguageISO',
Type: 'char(2)',
Collation: 'utf8_general_ci',
Null: 'NO',
Key: 'UNI',
Default: null,
Extra: '',
Privileges: 'select,insert,update,references',
Comment: 'ISO(EN),ISO(RU)'
}, {
Field: 'Caption',
Type: 'varchar(32)',
Collation: 'utf8_general_ci',
Null: 'NO',
Key: '',
Default: null,
Extra: '',
Privileges: 'select,insert,update,references',
Comment: 'Caption(EN),Заколовок(RU)'
}
]
*/
});

connection.databases('%', function(err, databases) {
console.dir({databases:databases});
/* Example:
databases: [ 'information_schema', 'mezha', 'mysql', 'performance_schema', 'test' ]
*/
});

connection.tables('%', function(err, tables) {
console.dir({tables:tables});
/* Example:
tables: [ '_language' ]
*/
});

connection.tableInfo('_Language', function(err, info) {
console.dir({tableInfo:info});
/* Example:
tableInfo: [ {
Name: '_language',
Engine: 'InnoDB',
Version: 10,
Row_format: 'Compact',
Rows: 9,
Avg_row_length: 1820,
Data_length: 16384,
Max_data_length: 0,
Index_length: 49152,
Data_free: 9437184,
Auto_increment: 10,
Create_time: Mon Jul 15 2013 03:06:08 GMT+0300 (Финляндия (лето)),
Update_time: null,
Check_time: null,
Collation: 'utf8_general_ci',
Checksum: null,
Create_options: '',
Comment: '_Language:Languages(EN),Языки(RU)'
} ]
*/
});