Db client
From Yate Documentation
Setup
javascript.conf
[general] scripts_dir=/your/dir/where/js/files/ .... [scripts] init=init.js
If you use MySql edit mysqldb.conf
[general] priority=100 [yate_my] host=localhost port=3306 database=database_name user=database_user password=yourpass123
If you use PostgreSql edit pgsqldb.conf
[general] priority=100 [yate_pg] host=localhost port=5432 database=database_name user=database_user password=yourpass123
init.js
#require "db_client.class.js" // db_client('use account name', 'database module, default pgsql'); var db = new db_client('yate_my', 'mysql'); // or var db = new db_client('yate_pg', 'pgsql'); db.query('INSERT INTO extension (extension,password) VALUES ($,$)', ['100','100']);
Methods
- query - return false or resource(true)
- query_insert - return false or last inser id
- get_affected_rows - return false or affected rows
- get_value_query - return false or first value
- get_array_list - return false or array
- getKeyValArray - return false or array
- get_one_line_assoc - return false or array
- get_assoc_column - return false or array
- get_assoc_column1 - return false or array
Examples
Example table users:
id | fname | lname | group | login | pass |
1 | John | Smit | 0 | test1 | qwe |
2 | Tom | Bink | 1 | test2 | ytr |
3 | Mike | Depp | 1 | test3 | odi |
query:
var q = db.query('UPDATE users SET pass=$ WHERE id=$', [pass, id]); q == false or true;
query_insert:
var q = db.query_insert('INSERT INTO users (fname,lname,group,pass) VALUES($,$,$,$)', ['Grag','Sher','1','123']); q == false or 4;
get_affected_rows:
var q = db.get_affected_rows('UPDATE users SET group=$ WHERE group=$', ['1','0']); q == false or 2;
get_value_query:
var q = db.get_value_query('SELECT pass FROM users WHERE id=$', '1'); q == false or qwe;
get_array_list:
var q = db.get_array_list('SELECT fname, lname, group FROM users'); q == false or [ {'fname'=>'John', 'lname'=>'Smit', 'group'=>'0'}, {'fname'=>'Tom', 'lname'=>'Bink', 'group'=>'1'}, {'fname'=>'Mike', 'lname'=>'Depp', 'group'=>'1'} ];
getKeyValArray:
var q = db.getKeyValArray('SELECT id, login FROM users'); q == false or { '1' : 'test1', '2' : 'test2', '3' : 'test3' };
get_one_line_assoc:
var q = db.get_one_line_assoc('SELECT * FROM users WHERE id=$', '2'); q == false or { 'id' : '2', 'fname' : 'Tom', 'lname' : 'Bink', 'group' : '1', 'login' : 'test2', 'pass' : 'ytr' };
get_assoc_column:
var q = db.get_assoc_column('SELECT login FROM users'); q == false or [ 'test1', 'test2', 'test3' ];
get_assoc_column1:
key object first value
var q = db.get_assoc_column1('SELECT * FROM users'); q == false or { '1' : {'fname':'John', 'lname':'Smit', 'group':'0', 'login':'test1', 'pass':'qwe'}, '2' : {'fname':'Tom', 'lname':'Bink', 'group':'1', 'login':'test2', 'pass':'ytr'}, '3' : {'fname':'Mike', 'lname':'Depp', 'group':'1', 'login':'test3', 'pass':'odi'} };
var q = db.get_assoc_column1('SELECT login, fname, lname FROM users'); q == false or { 'test1' : {'fname':'John', 'lname':'Smit'}, 'test2' : {'fname':'Tom', 'lname':'Bink'}, 'test3' : {'fname':'Mike', 'lname':'Depp'} };
==Code: Db client.class.js
function db_client(db_account, db_type){ if(!db_account) return false; if(!db_type) db_type = 'pgsql'; this.connect = null; this.account = db_account; this.db_types = {'pgsql':0, 'mysql':1}; this.db_type = this.db_types[db_type]; this.last_insert_id = ['SELECT LASTVAL()','LAST_INSERT_ID()']; return this; } db_client.prototype = new Object; db_client.prototype.query = function(q, a) { this.connect = null; this.connect = new Message("database"); this.connect.account = this.account; this.connect.query = this.query_replase(q, a); if (!this.connect.dispatch()) return false; if (this.connect.error) { Engine.debug('SQL ' + this.connect.error); return false; } return true; }; db_client.prototype.query_replase = function(q, a) { if (a) { if (typeof(a) !== 'object') return this.jtreplace(q, a); for (var i = 0; i < a.length; i++) { q = this.jtreplace(q, a[i]); } } return q; }; db_client.prototype.jtreplace = function(q, a) { var list = []; list.push(q.substr(0, q.indexOf('$')), "'" + a + "'", q.substr(q.indexOf('$') + 1, q.length)); return list.join(); }; db_client.prototype.get_array_list = function(q, a) { if (!this.query(q, a)) return false; var rows = []; for (var r = 0; r < this.connect.rows; r++) { rows.push(this.connect.getRow(r)); } return rows; }; db_client.prototype.get_one_line_assoc = function(q, a) { if (!this.query(q, a)) return false; return this.connect.getRow(0); }; db_client.prototype.get_value_query = function(q, a) { if (!this.query(q, a)) return false; if (this.connect.rows == 1 && this.connect.columns == 1) return this.connect.getResult(0, 0); return false; }; db_client.prototype.getKeyValArray = function(q, a) { if (!this.query(q, a)) return false; var rows = {}; for (var r = 0; r < this.connect.rows; r++) { rows[this.connect.getResult(r, 0)] = this.connect.getResult(r, 1); } return rows; }; db_client.prototype.get_assoc_column = function(q, a) { if (!this.query(q, a)) return false; var rows = []; for (var r = 0; r < this.connect.rows; r++) { rows.push(this.connect.getResult(r, 0)); } return rows; }; db_client.prototype.query_insert = function(q, a) { q = q + ';'+ this.last_insert_id[this.db_type]; if (!this.query(q, a)) return false; if (this.connect.rows == 1 && this.connect.columns == 1) return this.connect.getResult(0, 0); return true; };