Db client

Latest revision as of 15:44, 26 February 2014

The Javascript db_client class eases work with a database. Thanks Ximik777 :)

[edit] Usage

[edit] Use db_client from script

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']);

[edit] Yate 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

[edit] Methods

1. query - return false or resource(true)
2. query_insert - return false or last inser id
3. get_affected_rows - return false or affected rows
4. get_value_query - return false or first value
5. get_array_list - return false or array
6. getKeyValArray - return false or array
7. get_one_line_assoc - return false or array
8. get_assoc_column - return false or array
9. get_assoc_column1 - return false or array

[edit] Examples

Example table users:

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'}
};

[edit] 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;
   };

See also:

• Javascript