- PVSM.RU - https://www.pvsm.ru -

Удобства на улице для MySQL драйвера в Node.js

Удобства на улице для MySQL драйвера в Node.jsКто пишет на Node.js и использует MySQL, тот непременно знает, что наш дорогой товарищ Felix Geisendörfer [1] три года назад совершил героический и самоотверженный подвиг: в одиночку разработал очень качественный драйвер [2] для подключения к MySQL, нативно реализовав двоичный протокол этой СУБД на JavaScript. Потом к проекту подключились другие уважаемые товарищи, была реализована поддержка пулов соединений, кластеров, транзакций, восстановление при временной утере соединения и т.д. Сейчас драйвер [3] является самым развитым, хорошо проработанным и активно поддерживаемым из того, что мы имеем в открытых репозитариях npm и github. Удивительно даже, что при хорошо проработанной низкоуровневой реализации, все удобства, которые предоставляет это драйвер для прикладного разработчика, сводятся к одному методу query. Для меня лично, этого очень мало, ну привык дедушка к удобствам для возврата скалярных значений, строк и столбцов в массивы, интроспекции структур БД. Так что, с удовольствием делюсь этими наработками с вами, мои дорогие читатели, но предупреждаю, что все удобства будут на улице. Есть конечно вариант тесной интеграции с драйвером, но FelixGe желает [4] оставить драйвер исключительно низкоуровневым, поэтому я остановился на варианте внешней библиотеки с добавлением к драйверу через примеси. В виде примесей удобства попали и в платформу Impress [5], а так же, опубликованы как патч к драйверу. О функционале и вариантах использования далее.

Удобства выборки данных

Далее, под словом «возвращает» буду разуметь второй параметр callback, а не результат вызова функции.

Выборка одной записи: connection.queryRow(sql, values, callback) возвращает хеш (ассоциативный массив), в котором имена полей становятся ключами (вместо того, чтобы получать массив в массиве через query).

Пример

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

Выборка скаляра (то есть единичного значения): connection.queryValue(sql, values, callback) возвращает одно значение (вместо того, чтобы получать массив в массиве с одним значением). Удобно при выборке одного пол из одной записи, например Id по имени с указанием LIMIT 1 или функций count(*), max(field) и т.д.

Пример

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

Выборка одной колонки: connection.queryArray(sql, values, callback) возвращает массив, заполненный значениями одного поля для каждой записи, из результата выполнения запроса. То есть, это выборка вертикальной колонки, в отличие от выборки горизонтальной записи queryRow.

Пример

connection.queryArray('SELECT LanguageSign FROM Language', [], function(err, result) {
    console.dir({queryHash:result});
    /* Example:
        queryArray: [ 'de', 'en', 'es', 'fr', 'it', 'pl', 'ru', 'ua' ]
    */
});

Выборка хеша : connection.queryHash(sql, values, callback) возвращает хеш (ассоциативный массив) двухуровневой вложенности, где ключи первого уровня — значения первого поля из результата выполнения запроса, а ключи второго уровня — все поля результата запроса (включая и первое).

Пример

connection.queryHash(
    'SELECT LanguageSign, LanguageId, LanguageName, Caption, LanguageISO FROM Language', [],
function(err, result) {
    console.dir({queryHash:result});
    /* Example:
        queryHash: {
            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(sql, values, callback) возвращает хеш (ассоциативный массив), где ключом будет первое поле из результата запроса запросе.

Пример

connection.queryKeyValue(
    'SELECT LanguageISO, LanguageName FROM Language', [], function(err, keyValue) {
    console.dir({queryKeyValue:keyValue});
    /* Example:
        keyValue: {
            en: 'English',
            ru: 'Russian',
            uk: 'Ukrainian',
            es: 'Espanol',
            fr: 'Francais',
            de: 'Deutsch',
            it: 'Italiano',
            pl: 'Poliski'
        }
    */
});

Удобства интроспекции

То есть, удобства получения метаданных, структур и параметров базы для их анализа и автоматического построения логики или интерфейсов по работы с этой базой.

Получение первичного ключа: connection.primary(table, callback) возвращает хеш (ассоциативный массив), с метаданными о первичном ключе, см. набор метаданных в примере.

Пример

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(table, callback) возвращает хеш (ассоциативный массив), с двойной вложенностью, на первом уровне имена внешних ключей, а на втором — метаданные, описывающие этот ключ. Набор полей см. в примере.

Пример

connection.foreign('TemplateCaption', function(err, foreign) {
    console.dir({foreign:foreign});
    /* Example:
        foreign: {
            fkTemplateCaptionLanguage: {
                CONSTRAINT_NAME: 'fkTemplateCaptionLanguage',
                COLUMN_NAME: 'LanguageId',
                ORDINAL_POSITION: 1,
                POSITION_IN_UNIQUE_CONSTRAINT: 1,
                REFERENCED_TABLE_NAME: 'language',
                REFERENCED_COLUMN_NAME: 'LanguageId' }, 
            fkTemplateCaptionTemplate: {
                CONSTRAINT_NAME: 'fkTemplateCaptionTemplate',
                COLUMN_NAME: 'TemplateId',
                ORDINAL_POSITION: 1,
                POSITION_IN_UNIQUE_CONSTRAINT: 1,
                REFERENCED_TABLE_NAME: 'template',
                REFERENCED_COLUMN_NAME: 'TemplateId'
            }
    */
});

Получение ограничений целостности: connection.constraints(table, callback) возвращает хеш (ассоциативный массив), с двойной вложенностью, на первом уровне имена ограничений целостности, а на втором — метаданные, описывающие каждое ограничение. Набор полей см. в примере.

Пример

connection.constraints('TemplateCaption', function(err, constraints) {
    console.dir({constraints:constraints});
    /* Example:
        constraints: {
            fkTemplateCaptionLanguage: {
                CONSTRAINT_NAME: 'fkTemplateCaptionLanguage',
                UNIQUE_CONSTRAINT_NAME: 'PRIMARY',
                REFERENCED_TABLE_NAME: 'Language',
                MATCH_OPTION: 'NONE',
                UPDATE_RULE: 'RESTRICT',
                DELETE_RULE: 'CASCADE' },
            fkTemplateCaptionTemplate: {
                CONSTRAINT_NAME: 'fkTemplateCaptionTemplate',
                UNIQUE_CONSTRAINT_NAME: 'PRIMARY',
                REFERENCED_TABLE_NAME: 'Template',
                MATCH_OPTION: 'NONE',
                UPDATE_RULE: 'RESTRICT',
                DELETE_RULE: 'CASCADE'
            } 
        }
    */
});

Получение метаданных о полях таблицы: connection.fields(table, callback) возвращает массив с метаданными для каждого поля, в том чисте имя, тип, все модификаторы и флаги, комментарии (подробнее см. пример).

Пример

connection.fields('Language', function(err, fields) {
    console.dir({fields:fields});
    /* Example:
        fields: {
            LanguageId: {
                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)' },
            LanguageName: {
                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)'
            }, ...
        }
    */
});

Получение списка баз данных доступных по данному соединению: connection.databases(callback) возвращает массив имен баз (или «схем», как их иногда называют).

Пример

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

Получение списка таблиц для текущего соединения: connection.tables(callback) возвращает хеш (ассоциативный массив) двойной вложенности, где на первом уровне ключи — имена таблиц, а на втором — метаданные для каждой таблицы.

Пример

connection.tables(function(err, tables) {
    console.dir({tables:tables});
    /* Example:
        tables: {
            Language: {
                TABLE_NAME: 'Language',
                TABLE_TYPE: 'BASE TABLE',
                ENGINE: 'InnoDB',
                VERSION: 10,
                ROW_FORMAT: 'Compact',
                TABLE_ROWS: 9,
                AVG_ROW_LENGTH: 1820,
                DATA_LENGTH: 16384,
                MAX_DATA_LENGTH: 0,
                INDEX_LENGTH: 49152,
                DATA_FREE: 8388608,
                AUTO_INCREMENT: 10,
                CREATE_TIME: Mon Jul 15 2013 03:06:08 GMT+0300 (Финляндия (лето)),
                UPDATE_TIME: null,
                CHECK_TIME: null,
                TABLE_COLLATION: 'utf8_general_ci',
                CHECKSUM: null,
                CREATE_OPTIONS: '',
                TABLE_COMMENT: '_Language:Languages(EN),Языки(RU)'
            }, ...
        }
    */
});

Получение метаданных указанной таблицы: connection.tableInfo(table, callback) возвращает хеш (ассоциативный массив) с метаданными (подробнее см. в примере).

Пример

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: ''
        }
    */
});

Получение метаданных о всех ключах данной таблицы: connection.indexes(table, callback) возвращает хеш (ассоциативный массив)? ключами первого уровня в нем являются имена ключей базы данных (), а ключами второго уровня — метаданные о каждом ключе (см. пример для подробного списка метаданных).

Пример

connection.indexes('Language', function(err, info) {
    console.dir({tableInfo:info});
    /* Example:
        indexes: {
            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: '' },
            akLanguage: {
                Table: 'language',
                Non_unique: 0,
                Key_name: 'akLanguage',
                Seq_in_index: 1,
                Column_name: 'LanguageName',
                Collation: 'A',
                Cardinality: 9,
                Sub_part: null,
                Packed: null,
                Null: '',
                Index_type: 'BTREE',
                Comment: '',
                Index_comment: ''
            }
        }
    */
});

Получение процессов на сервере MySQL: connection.processes(callback) возвращает массив хешей, где для каждого процесса даны его параметры.

Пример

connection.processes(function(err, processes) {
    console.dir({processes:processes});
    /* Example:
        processes: [ {
            ID: 62,
            USER: 'mezha',
            HOST: 'localhost:14188',
            DB: 'mezha',
            COMMAND: 'Query',
            TIME: 0,
            STATE: 'executing',
            INFO: 'SELECT * FROM information_schema.PROCESSLIST'
        }, {
            ID: 33,
            USER: 'root',
            HOST: 'localhost:39589',
            DB: null,
            COMMAND: 'Sleep',
            TIME: 1,
            STATE: '',
            INFO: null
        } ]
    */
});

Получение глобальных переменных MySQL: connection.globalVariables(callback)

Пример

connection.globalVariables(function(err, globalVariables) {
    console.dir({globalVariables:globalVariables});
    /* Example:
        globalVariables: {
            MAX_PREPARED_STMT_COUNT: '16382',
            MAX_JOIN_SIZE: '18446744073709551615',
            HAVE_CRYPT: 'NO',
            PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE: '10000',
            INNODB_VERSION: '5.5.32',
            FLUSH_TIME: '1800',
            MAX_ERROR_COUNT: '64',
            ...
        }
    */
});

Получение глобального статуса MySQL: connection.globalStatus(callback)

Пример

connection.globalStatus(function(err, globalStatus) {
    console.dir({globalStatus:globalStatus});
    /* Example:
        globalStatus: {
            ABORTED_CLIENTS: '54',
            ABORTED_CONNECTS: '2',
            BINLOG_CACHE_DISK_USE: '0',
            BINLOG_CACHE_USE: '0',
            BINLOG_STMT_CACHE_DISK_USE: '0',
            BINLOG_STMT_CACHE_USE: '0',
            BYTES_RECEIVED: '654871',
            BYTES_SENT: '212454927',
            COM_ADMIN_COMMANDS: '594',
            ...
        }
    */
});

Получение списка пользователей MySQL: connection.users(callback)

Пример

connection.users(function(err, users) {
    console.dir({users:users});
    /* Example:
        users: [
            {
                Host: 'localhost',
                User: 'root',
                Password: '*90E462C37378CED12064BB3388827D2BA3A9B689',
                Select_priv: 'Y',
                Insert_priv: 'Y',
                Update_priv: 'Y',
                Delete_priv: 'Y',
                Create_priv: 'Y',
                Drop_priv: 'Y',
                Reload_priv: 'Y',
                Shutdown_priv: 'Y',
                Process_priv: 'Y',
                File_priv: 'Y',
                Grant_priv: 'Y',
                References_priv: 'Y',
                Index_priv: 'Y',
                Alter_priv: 'Y',
                Show_db_priv: 'Y',
                Super_priv: 'Y',
                Create_tmp_table_priv: 'Y',
                Lock_tables_priv: 'Y',
                Execute_priv: 'Y',
                Repl_slave_priv: 'Y',
                Repl_client_priv: 'Y',
                Create_view_priv: 'Y',
                Show_view_priv: 'Y',
                Create_routine_priv: 'Y',
                Alter_routine_priv: 'Y',
                Create_user_priv: 'Y',
                Event_priv: 'Y',
                Trigger_priv: 'Y',
                Create_tablespace_priv: 'Y',
                ssl_type: '',
                ssl_cipher: <Buffer >,
                x509_issuer: <Buffer >,
                x509_subject: <Buffer >,
                max_questions: 0,
                max_updates: 0,
                max_connections: 0,
                max_user_connections: 0,
                plugin: '',
                authentication_string: ''
            }, ...
        ]
    */
});

Удобства генерации запросов

Ну и совсем уже барские удобства, позволяющие генерировать SQL или полностью или отдельные WHERE выражения. Я сам не сторонник такого сахара, но иногда бывает нужно автоматизировать генерацию запросов, которые заранее не известны и я позволяю себе такую роскошь.

Генерация условий: connection.where(conditions) работает синхронно, а не асинхронно, как другие функции, т.е. не использует callback. Возвращает построенное WHERE выражение SQL для conditions, описанных в стиле JSON. Нужно обязательно смотреть пример для понимания:

Пример

var where = connection.where({
    id: 5,
    year: ">2010",
    price: "100..200",
    level: "<=3",
    sn: "*str?",
    label: "str",
    code: "(1,2,4,10,11)"
});
console.dir(where);
// Output: "id = 5 AND year > '2010' AND (price BETWEEN '100' AND '200') AND 
// level <= '3' AND sn LIKE '%str_' AND label = 'str' AND code IN (1,2,4,10,11)"

Выборка с условием: connection.select(table, whereFilter, callback)

Пример

connection.select('Language', '*', { LanguageId: "1..3" }, function(err, results) {
    console.dir({select:results});
});

Вставка записи: connection.insert(table, row, callback)

Пример

connection.insert('Language', {
    LanguageName: 'Tatar',
    LanguageSign:'TT',
    LanguageISO:'TT',
    Caption:'Tatar'
}, function(err, recordId) {
    console.dir({insert:recordId});
});

Изменение записи: connection.update(table, row, callback)

Пример

connection.update('Language', {
    LanguageId: 25,
    LanguageName:'Tatarca',
    LanguageSign:'TT',
    LanguageISO:'TT',
    Caption:'Tatarca'
}, function(err, affectedRows) {
    console.dir({update:affectedRows});
});

Вставка, если нет такой записи или изменение, если она уже есть: connection.upsert(table, row, callback)

Пример

connection.upsert('Language', {
    LanguageId: 25,
    LanguageName:'Tatarca',
    LanguageSign:'TT',
    LanguageISO:'TT',
    Caption:'Tatarca'
}, function(err, affectedRows) {
    console.dir({upsert:affectedRows});
});

Получение количества записей в таблице по заданному фильтру или без фильтра: connection.count(table, whereFilter, callback)

Пример

connection.count('Language', { LanguageId: ">3" }, function(err, count) {
    console.dir({count:count});
    /* Example:
        count: 9
    */
});

Удаление записи или нескольких записей: connection.delete(table, whereFilter, callback)

Пример

connection.delete('Language', { LanguageSign:'TT' }, function(err, affectedRows) {
    console.dir({delete:affectedRows});
});

Варианты использования

1. На Github есть патч к драйверу node-mysql со всеми, приведенными выше функциями: github.com/felixge/node-mysql [2]

2. Но я думаю, что поддерживать его я не стану, и подумываю про то, чтобы сделать отдельную библиотеку с более высоким уровнем абстракции, чем просто драйвер, но не такого высокого уровня как ORM библиотеки. То есть, я не хочу делать из этого громоздкую вещь, а позволить добавлять к соединению через примеси только нужные из трех групп функций. То есть, после создания соединения можно будет выбирать, нужна ли интроспекция, нужны ли простые функции выборки скаляров, массивов и хешей и нужны ли более сложные функции для генерации SQL выражений.

3. И третий вариант использования, это сервер приложений Impress для Node.js, в которые весь этот арсенал встроен. Две группы функций по выборке в Impress есть сразу у всех подключений к MySQL, они примешиваются автоматически, при открытии соединения, и только интроспекцию можно добавить через конфиг где нужно раскомментировать соответствующий плагин, чтобы набор плагинов для MySQL был такой:

	plugins: {
		require: [
			"db",
			"db.mysql",
			"db.mysql.introspection",
			...
		]
	},...

Тогда плагин автоматически примешается к каждому соединению: if (db.mysql.introspection) db.mysql.introspection(connection);
Тут искодники плагинов к Impress [5]:

А по поводу выпуска этих удобств в качестве отдельной библиотеки, хочу посоветоваться в вами:

Автор: MarcusAurelius

Источник [8]


Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/mysql/46444

Ссылки в тексте:

[1] Felix Geisendörfer: https://github.com/felixge

[2] качественный драйвер: https://github.com/felixge/node-mysql

[3] драйвер: https://npmjs.org/package/mysql

[4] FelixGe желает: https://github.com/felixge/node-mysql/pull/546#issuecomment-26407587

[5] Impress: http://habrahabr.ru/post/194250/

[6] https://github.com/tshemsedinov/impress/blob/master/lib/db.mysql.js: https://github.com/tshemsedinov/impress/blob/master/lib/db.mysql.js

[7] https://github.com/tshemsedinov/impress/blob/master/lib/db.mysql.introspection.js: https://github.com/tshemsedinov/impress/blob/master/lib/db.mysql.introspection.js

[8] Источник: http://habrahabr.ru/post/198738/