//基於json資料格式實現的簡單資料庫——jsonDB
//https://www.cnblogs.com/cynchanpin/p/7068151.html
//已在github上建立專案：https://github.com/ThinkerCodeChina/jsonDB
/**
+-----------------------------------------
* jsonDB 基於json資料格式構建的資料模型
+-----------------------------------------
* @description 對json資料檢索，刪除，查詢和更新
* @author 戚銀(web程式設計師) thinkercode@sina.com
* @date 2014年6月28日
* @version 0.1
* @blog http://blog.csdn.net/thinkercode/
+-----------------------------------------
* 簡單介紹：
* jsonDB是js的一個類庫，是基於json資料格式構建的資料
* 模型實現對json資料增刪改查。
jsonDB的構建源自於HTML5
* 本地儲存的一個應用需求，能夠透過sql對json資料進行增
* 刪改查，同一時候該類庫提供強大的where檢索條件，資料排序，
* limit查詢條件限制等資料庫基本功能。
透過jsonDB能夠輕
* 松維護一個庫/表或多個庫/表。而無需額外實現json的資料
* 的維護等，在該類庫完好以後為簡化sql操作，基於jsonDB核
* 心模組擴充套件了連貫操作模型，簡化對jsonDB的操作以及sql語
* 句出錯的機率。
+-----------------------------------------
* 當前版本號的不足：
* 1.無法支援查詢欄位運算
* 2.不支援對沒有選取的查詢欄位排序
* 3.僅僅支援單個欄位排序，無法進行組合排序操作
* 4.update、delete語句不支援order by子句。導致limit子句功能弱化
* 5.編寫where條件是必須使用()包括where條件
* 6.無法選取深層次的欄位作為返回欄位
* 7.沒有錯誤或異常解決方式
* 8.不支援外部擴充套件
+-----------------------------------------
* jsonDB的使用：
* jsonDB會提供一個具體的使用手冊，當中包括大量演示樣例
* jsonDB還有非常多能夠使用的技巧並沒有一一包括在手冊中
* 期望強大的網友一起挖掘jsonDB的潛在使用技巧。並分享給大家
* 假設使用中發現問題能夠隨時傳送郵件，或者透過部落格留言等方式一起探討
* 同一時候鑑於自己實力有限，期望發動網友一起擴充套件jsonDB。
* 能在未來前端應用中奉獻一份力量
+-----------------------------------------
*/
(function(window,undefined){
_jsonDB = window.jsonDB,
_history = null,
DBCore = {},
Database = {},
DBExpand = {};
/**
* [jsonDB 初始化模型。支援定義本地資料庫和資料表]
* @param mixed data 資料
* @param string dbName 資料庫名字
* @return jsonDB
*/
var jsonDB = function(data,dbName){
//建立資料庫或者資料表
if(data){
dbName = dbName || 'json_db';
eval('Database.'+ dbName +'= data');
}
return jsonDB.fn.init();
}
jsonDB.fn = jsonDB.prototype = {
//初始化外掛
init : function (alias){
if(alias){
_history = window[alias];
window[alias] = jsonDB();
}
return this;
},
query : function(sql){
var type = sql.match(/^(\w+)/);
switch(type[0]){
case 'select' :
case 'delete' :
case 'update' :
return eval('DBCore.fn.'+ type[0] +'(sql+" ")');
break;
default :
return false;
break;
}
},
insert : function(data,dbName){
if(data){
dbName = dbName || 'json_db';
eval('Database.'+ dbName +'.push(data)');
}
return this;
},
findAll : function(dbName){
if(dbName){
return eval('Database.'+ dbName);
}
return Database;
}
};
/**
* [DBExpand 資料庫核心功能擴充套件]
*/
DBExpand = DBExpand.prototype = {
sqlParam : {
fields : '*',
table : 'json_db',
where : null,
order : null,
limit : null,
},
add : function(data){
return this.insert(data,this.sqlParam.table);
},
select : function(){
var sql = 'select '+this.sqlParam.fields+' from '+this.sqlParam.table;
if(this.sqlParam.where){
sql += ' where '+this.sqlParam.where;
}
if(this.sqlParam.order){
sql += ' order by '+this.sqlParam.order;
}
if(this.sqlParam.limit){
sql += ' limit '+this.sqlParam.limit;
}
this.clear();
return this.query(sql);
},
update : function(data){
if(data.length<1){
return false;
}
var sql = 'update '+this.sqlParam.table+' set '+data;
if(this.sqlParam.where){
sql += ' where '+this.sqlParam.where;
}
if(this.sqlParam.limit){
sql += ' limit '+this.sqlParam.limit;
}
this.clear();
return this.query(sql);
},
delete : function(){
if(this.sqlParam.where.length<1){
return false;
}
var sql = 'delete from '+this.sqlParam.table;
if(this.sqlParam.where){
sql += ' where '+this.sqlParam.where;
}
if(this.sqlParam.limit){
sql += ' limit '+this.sqlParam.limit;
}
this.clear();
return this.query(sql);
},
drop : function(dbName){
//建立資料庫或者資料表
if(data){
dbName = dbName || 'json_db';
eval('Database.'+ dbName +'= null');
}
return this;
},
field : function(fields){
if(typeof fields == 'object'){
this.sqlParam.fields = fields.join(',');
}else{
this.sqlParam.fields = fields;
}
return this;
},
table : function(table){
this.sqlParam.table = table;
return this;
},
where : function(where){
this.sqlParam.where = '('+where+')';
return this;
},
order : function(order){
this.sqlParam.order = order;
return this;
},
limit : function(limit){
this.sqlParam.limit = limit;
return this;
},
clear : function(){
this.sqlParam.fields = '*';
this.sqlParam.where = null;
this.sqlParam.order = null;
this.sqlParam.limit = null;
}
}
/**
* [DBCore 資料庫核心]
*/
DBCore.fn = DBCore.prototype = {
SqlRegExp : {
fields: '([a-z0-9_\\,\\.\\s\\*]*?\\s+)',
from : '(from\\s+([a-z0-9_\\.]+)\\s*)?',
where : '(?:where\\s+(\\(.*\\))\\s*)?
',
order : '(?:order\\s+by\\s+([a-z0-9_\\,\\.]+))?
\\s+(asc|desc|ascnum|descnum)?\\s*',
limit : '(?:limit\\s+([0-9\\,]+))?',
set : '(set\\s+(.*?)\\s+)',
table : '(([a-z0-9_\\.]*?)\\s*)?',
},
select : function(sql){
var params = {fields:["*"], from:"json_db", where:"", orderby:[], order: "asc", limit:[]},
SqlRegExp = this.SqlRegExp,
reg = '^(select)\\s+'+SqlRegExp.fields + SqlRegExp.from + SqlRegExp.where + SqlRegExp.order + SqlRegExp.limit,
sqlReg = new RegExp(reg,'i'),
sqlFields = sql.match(sqlReg),
options = {
fields: sqlFields[2].replace(' ','').split(','),
from: (sqlFields[4] == undefined) ? 'json_db' : sqlFields[4],
where: (sqlFields[5] == undefined) ? "true" : sqlFields[5].replace(/([^\>\<\!\=])=([^\>\<\!\=])/gi,'$1 == $2').replace(/\sand\s/gi,' && ').replace(/\sor\s/gi,' || ').replace(/`/gi,''),
orderby: (sqlFields[6] == undefined) ?
[]:sqlFields[6].replace(' ','').split(','),
order: (sqlFields[7] == undefined) ? "asc" : sqlFields[7],
limit: (sqlFields[8] == undefined) ?
[] : sqlFields[8].replace(' ','').split(',')
};
for(i in options){
params[i] = options[i];
}
var result = [];
result = this.filter(params,function(data){
if(options.fields.length == 0 || options.fields[0] == "*"){
return data;
}
var result = {};
for(var i in options.fields){
result[options.fields[i]] = data[options.fields[i]];
}
return result;
});
result = this.orderBy(result,options);
result = this.limit(result,options);
return result;
},
update : function(sql){
var params = {from:"json_db", where:"", limit:[], set:[]},
SqlRegExp = this.SqlRegExp,
reg = '^(update)\\s+'+ SqlRegExp.table + SqlRegExp.set + SqlRegExp.where + SqlRegExp.limit,
sqlReg = new RegExp(reg,'i'),
sqlFields = sql.match(sqlReg),
options = {
from: (sqlFields[3] == undefined) ? 'json_db' : sqlFields[3],
set: (sqlFields[5] == undefined) ? [] : sqlFields[5].replace(' ','').split(','),
where: (sqlFields[6] == undefined) ? "true" : sqlFields[6].replace(/([^\>\<\!\=])=([^\>\<\!\=])/gi,'$1 == $2').replace(/\sand\s/gi,' && ').replace(/\sor\s/gi,' || ').replace(/`/gi,''),
limit: (sqlFields[7] == undefined) ?
[] : sqlFields[7].replace(' ','').split(',')
};
for(i in options){
params[i] = options[i];
}
var jsonData = eval('Database.' + options.from),
tally = 0,
extent = this.extent(params),
setLen = options.set.length,
affected_rows = 0;
if(setLen<1){
return false;
}
options.where = options.where || "true";
for(var i in jsonData){
with(jsonData[i]){
if(eval(options.where)){
if(affected_rows >= extent.start && tally < extent.stop){
for(var j=0;j<setLen;++j){
eval(options.set[j]);
}
++tally;
}else if(tally == extent.stop){
return tally;
}
++affected_rows;
}
}
}
return tally;
},
delete : function(sql){
var params = {from:"json_db", where:"", limit:[]},
SqlRegExp = this.SqlRegExp,
reg = '^(delete)\\s+'+ SqlRegExp.from + SqlRegExp.where + SqlRegExp.limit,
sqlReg = new RegExp(reg,'i'),
sqlFields = sql.match(sqlReg),
options = {
from: (sqlFields[3] == undefined) ? 'json_db' : sqlFields[3],
where: (sqlFields[4] == undefined) ? "true" : sqlFields[4].replace(/([^\>\<\!\=])=([^\>\<\!\=])/gi,'$1 == $2').replace(/\sand\s/gi,' && ').replace(/\sor\s/gi,' || ').replace(/`/gi,''),
limit: (sqlFields[5] == undefined) ?
[] : sqlFields[5].replace(' ','').split(',')
};
for(i in options){
params[i] = options[i];
}
var jsonData = eval('Database.' + options.from + '.concat()'),
tally = 0,
extent = this.extent(params),
affected_rows = 0;
options.where = options.where || "true";
for(var i in jsonData){
with(jsonData[i]){
if(eval(options.where)){
if(affected_rows >= extent.start && tally < extent.stop){
eval('Database.'+options.from+'.splice(i-tally,1)');
++tally;
}else if(tally == extent.stop){
return tally;
}
++affected_rows;
}
}
}
return tally;
},
filter : function(options, callback){
var jsonData = eval('Database.' + options.from),
result = [],
index = 0;
options.where = options.where || "true";
for(var i in jsonData){
with(jsonData[i]){
if(eval(options.where)){
if(callback){
result[index++] = callback(jsonData[i]);
}else{
result[index++] = jsonData[i];
}
}
}
}
return result;
},
orderBy : function(result,options){
if(options.orderby.length == 0){
return result;
}
result.sort(function(a,b){
switch(options.order.toLowerCase()){
case "desc": return (eval('a.'+ options.orderby[0] +' < b.'+ options.orderby[0]))?
1:-1;
case "asc": return (eval('a.'+ options.orderby[0] +' > b.'+ options.orderby[0]))?
1:-1;
case "descnum": return (eval('a.'+ options.orderby[0] +' - b.'+ options.orderby[0]));
case "ascnum": return (eval('b.'+ options.orderby[0] +' - a.'+ options.orderby[0]));
}
});
return result;
},
limit : function(result,options){
switch(options.limit.length){
case 0:
return result;
case 1:
return result.splice(0,options.limit[0]);
case 2:
return result.splice(options.limit[0],options.limit[1]);
}
},
extent : function(options){
switch(options.limit.length){
case 0:
return {start:0, stop:9e+99};
case 1:
return {start:0, stop:options.limit[0]};
case 2:
return {start:options.limit[0], stop:options.limit[1]};
}
}
}
window.jsonDB = jsonDB;
//追加擴充套件功能
jsonDB.fn = jsonDB.prototype =extend(jsonDB.fn,DBExpand);
//合併物件方法
function extend(){
var paramsLen = arguments.length;
if(paramsLen<1){
return false;
}
var target = arguments[0];
for(var i=1;i<paramsLen;++i){
for(var j in arguments[i]){
target[j] = arguments[i][j];
}
}
return target;
}
})(window);