';
}
// if() {}
// if(typeof res == 'object') {
// s += ''+JSON.stringify(res)+'
';
// } else {
// }
return s;
}
function scrollTo(element, to, duration) {
if(duration <= 0){
return;
}
var difference = to - element.scrollTop;
var perTick = difference / duration * 10;
setTimeout(function() {
if(element.scrollTop===to){
return;
}
element.scrollTop = element.scrollTop + perTick;
scrollTo(element, to, duration - 10);
}, 10);
}
alasql.prompt = function(el, useidel, firstsql) {
if(utils.isNode) {
throw new Error('The prompt not realized for Node.js');
}
var prompti = 0;
if(typeof el === 'string'){
el = document.getElementById(el);
}
if(typeof useidel === 'string'){
useidel = document.getElementById(useidel);
}
useidel.textContent = alasql.useid;
if(firstsql) {
alasql.prompthistory.push(firstsql);
prompti = alasql.prompthistory.length;
try {
var tm = Date.now();
alasql.log(firstsql);
alasql.write(''+(Date.now()-tm)+' ms
');
} catch (err) {
alasql.write(''+olduseid+'> '+sql+'
');
alasql.write(''+err+'
');
}
}
var y = el.getBoundingClientRect().top + document.getElementsByTagName('body')[0].scrollTop;
scrollTo(document.getElementsByTagName('body')[0],y,500);
el.onkeydown = function(event) {
if(event.which === 13) {
var sql = el.value;
var olduseid = alasql.useid;
el.value = '';
alasql.prompthistory.push(sql);
prompti = alasql.prompthistory.length;
try {
var tm = Date.now();
alasql.log(sql);
alasql.write('
'+(Date.now()-tm)+' ms
');
} catch (err) {
alasql.write(''+olduseid+'> '+alasql.pretty(sql, false)+'
');
alasql.write(''+err+'
');
}
el.focus();
useidel.textContent = alasql.useid;
var y = el.getBoundingClientRect().top + document.getElementsByTagName('body')[0].scrollTop;
scrollTo(document.getElementsByTagName('body')[0],y,500);
} else if(event.which === 38) {
prompti--; if(prompti<0){
prompti = 0;
}
if(alasql.prompthistory[prompti]) {
el.value = alasql.prompthistory[prompti];
event.preventDefault();
}
} else if(event.which === 40) {
prompti++;
if(prompti>=alasql.prompthistory.length) {
prompti = alasql.prompthistory.length;
el.value = '';
} else if(alasql.prompthistory[prompti]) {
el.value = alasql.prompthistory[prompti];
event.preventDefault();
}
}
}
}
/*
//
// Commit for Alasql.js
// Date: 01.12.2014
// (c) 2014, Andrey Gershun
//
*/
yy.BeginTransaction = function (params) { return yy.extend(this, params); }
yy.BeginTransaction.prototype.toString = function() {
return 'BEGIN TRANSACTION';
}
yy.BeginTransaction.prototype.execute = function (databaseid,params, cb) {
var res = 1;
if(alasql.databases[databaseid].engineid) {
return alasql.engines[alasql.databases[alasql.useid].engineid].begin(databaseid, cb);
} else {
// alasql commit!!!
}
if(cb) cb(res);
return res;
};
yy.CommitTransaction = function (params) { return yy.extend(this, params); }
yy.CommitTransaction.prototype.toString = function() {
return 'COMMIT TRANSACTION';
}
yy.CommitTransaction.prototype.execute = function (databaseid,params, cb) {
var res = 1;
if(alasql.databases[databaseid].engineid) {
return alasql.engines[alasql.databases[alasql.useid].engineid].commit(databaseid, cb);
} else {
// alasql commit!!!
}
if(cb) cb(res);
return res;
};
yy.RollbackTransaction = function (params) { return yy.extend(this, params); }
yy.RollbackTransaction.prototype.toString = function() {
return 'ROLLBACK TRANSACTION';
}
yy.RollbackTransaction.prototype.execute = function (databaseid,params,cb) {
var res = 1;
if(alasql.databases[databaseid].engineid) {
return alasql.engines[alasql.databases[databaseid].engineid].rollback(databaseid, cb);
} else {
// alasql commit!!!
}
if(cb) cb(res);
return res;
};
if(alasql.options.tsql) {
//
// Check tables and views
// IF OBJECT_ID('dbo.Employees') IS NOT NULL
// IF OBJECT_ID('dbo.VSortedOrders', 'V') IS NOT NULL
alasql.stdfn.OBJECT_ID = function(name,type) {
if(typeof type == 'undefined') type = 'T';
type = type.toUpperCase();
var sname = name.split('.');
var dbid = alasql.useid;
var objname = sname[0];
if(sname.length == 2) {
dbid = sname[0];
objname = sname[1];
}
var tables = alasql.databases[dbid].tables;
dbid = alasql.databases[dbid].databaseid;
for(var tableid in tables) {
if(tableid == objname) {
// TODO: What OBJECT_ID actually returns
if(tables[tableid].view && type == 'V') return dbid+'.'+tableid;
if(!tables[tableid].view && type == 'T') return dbid+'.'+tableid;
return undefined;
}
}
return undefined;
};
}
if(alasql.options.mysql) {
}
if(alasql.options.mysql || alasql.options.sqlite) {
// Pseudo INFORMATION_SCHEMA function
alasql.from.INFORMATION_SCHEMA = function(filename, opts, cb, idx, query) {
if(filename == 'VIEWS' || filename == 'TABLES' ) {
var res = [];
for(var databaseid in alasql.databases) {
var tables = alasql.databases[databaseid].tables;
for(var tableid in tables) {
if((tables[tableid].view && filename == 'VIEWS') ||
(!tables[tableid].view && filename == 'TABLES')) {
res.push({TABLE_CATALOG:databaseid,TABLE_NAME:tableid});
}
}
}
if(cb) res = cb(res, idx, query);
return res;
}
throw new Error('Unknown INFORMATION_SCHEMA table');
}
}
if(alasql.options.postgres) {
}
if(alasql.options.oracle) {
}
if(alasql.options.sqlite) {
}
//
// into functions
//
// (c) 2014 Andrey Gershun
//
alasql.into.SQL = function(filename, opts, data, columns, cb) {
var res;
if(typeof filename == 'object') {
opts = filename;
filename = undefined;
}
var opt = {};
alasql.utils.extend(opt, opts);
if(typeof opt.tableid == 'undefined') {
throw new Error('Table for INSERT TO is not defined.');
};
var s = '';
if(columns.length == 0) {
if(typeof data[0] == "object") {
columns = Object.keys(data[0]).map(function(columnid){return {columnid:columnid}});
} else {
// What should I do?
// columns = [{columnid:"_"}];
}
}
for(var i=0,ilen=data.length;i 0) {
columns = Object.keys(data[0]).map(function(columnid){return {columnid:columnid}});
};
// If one parameter
if(typeof filename == 'object') {
opts = filename;
filename = undefined;
};
var res = data.length;
var s = '';
if(data.length > 0) {
var key = columns[0].columnid;
s += data.map(function(d){
return d[key];
}).join('\n');
}
res = alasql.utils.saveFile(filename,s);
if(cb) res = cb(res);
return res;
};
alasql.into.TAB = alasql.into.TSV = function(filename, opts, data, columns, cb) {
var opt = {};
alasql.utils.extend(opt, opts);
opt.separator = '\t';
return alasql.into.CSV(filename, opt, data, columns, cb);
}
alasql.into.CSV = function(filename, opts, data, columns, cb) {
if(columns.length == 0 && data.length > 0) {
columns = Object.keys(data[0]).map(function(columnid){return {columnid:columnid}});
}
if(typeof filename == 'object') {
opts = filename;
filename = undefined;
}
var opt = {};
//opt.separator = ',';
opt.separator = ';';
opt.quote = '"';
alasql.utils.extend(opt, opts);
var res = data.length;
var s = '';
if(opt.headers) {
s += opt.quote+columns.map(function(col){
return col.columnid.trim();
}).join(opt.quote+opt.separator+opt.quote)+opt.quote+'\r\n';
}
data.forEach(function(d, idx){
s += columns.map(function(col){
var s = d[col.columnid];
s = (s+"").replace(new RegExp('\\'+opt.quote,"g"),'""');
//Excel 2013 needs quotes around strings - thanks for _not_ complying with RFC for CSV
if(+s!=s){ // jshint ignore:line
s = opt.quote + s + opt.quote;
}
return s;
}).join(opt.separator)+'\r\n';
});
res = alasql.utils.saveFile(filename,s);
if(cb) res = cb(res);
return res;
};
//
// 831xl.js - Coloring Excel
// 18.04.2015
// Generate XLS file with colors and styles
// with Excel
alasql.into.XLS = function(filename, opts, data, columns, cb) {
// If filename is not defined then output to the result
if(typeof filename == 'object') {
opts = filename;
filename = undefined;
}
// Set sheets
var sheets = {};
if(opts && opts.sheets) {
sheets = opts.sheets;
};
// Default sheet
var sheet = {};
if(typeof sheets['Sheet1'] != 'undefined') {
sheet = sheets[0];
} else {
if(typeof opts != 'undefined') {
sheet = opts;
}
};
// Set sheet name and default is 'Sheet1'
if(typeof sheet.sheetid == 'undefined') {
sheet.sheetid = 'Sheet1';
};
var s = toHTML();
// File is ready to save
var res = alasql.utils.saveFile(filename,s);
if(cb) res = cb(res);
return res;
function toHTML() {
// Generate prologue
var s = ' \
\
';
// Generate body
s += ' 0) {
if(typeof data[0] == 'object') {
if(data[0] instanceof Array) {
columns = data[0].map(function(d,columnidx){
return {columnid:columnidx};
});
} else {
columns = Object.keys(data[0]).map(function(columnid){
return {columnid:columnid};
});
}
}
}
};
// Prepare columns
columns.forEach(function(column,columnidx){
if(typeof sheet.column != 'undefined') {
extend(column,sheet.column);
}
if(typeof column.width == 'undefined') {
if(sheet.column && sheet.column.width !='undefined') {
column.width = sheet.column.width;
} else {
column.width = "120px";
}
}
if(typeof column.width == 'number') column.width = column.width + "px";
if(typeof column.columnid == 'undefined') column.columnid = columnidx;
if(typeof column.title == 'undefined') column.title = ""+column.columnid.trim();
if(sheet.headers && sheet.headers instanceof Array) column.title = sheet.headers[columnidx];
});
// Set columns widths
s += '';
columns.forEach(function (column) {
s += '';
});
s += '';
// Headers
if(sheet.headers) {
s += '';
s += '';
// TODO: Skip columns to body
// Headers
columns.forEach(function (column,columnidx) {
s += ' 0) {
// TODO: Skip columns to body
// Loop over data rows
data.forEach(function(row,rowidx){
// Limit number of rows on the sheet
if(rowidx>sheet.limit) return;
// Create row
s += '';
s2 += s;
s2 += '';
stylesn++;
}
return 's'+styles[hh].styleid;
}
for (var sheetid in sheets) {
var sheet = sheets[sheetid];
// If columns defined in sheet, then take them
if(typeof sheet.columns != 'undefined') {
columns = sheet.columns;
} else {
// Autogenerate columns if they are passed as parameters
if(columns.length == 0 && data.length > 0) {
if(typeof data[0] == 'object') {
if(data[0] instanceof Array) {
columns = data[0].map(function(d,columnidx){
return {columnid:columnidx};
});
} else {
columns = Object.keys(data[0]).map(function(columnid){
return {columnid:columnid};
});
}
}
}
};
// Prepare columns
columns.forEach(function(column,columnidx){
if(typeof sheet.column != 'undefined') {
extend(column,sheet.column);
}
if(typeof column.width == 'undefined') {
if(sheet.column && (typeof sheet.column.width !='undefined')) {
column.width = sheet.column.width;
} else {
column.width = 120;
}
}
if(typeof column.width == 'number') column.width = column.width;
if(typeof column.columnid == 'undefined') column.columnid = columnidx;
if(typeof column.title == 'undefined') column.title = ""+column.columnid.trim();
if(sheet.headers && sheet.headers instanceof Array) column.title = sheet.headers[idx];
});
// Header
s3 +=' \
';
columns.forEach(function (column,columnidx) {
s3 += ''
});
// Headers
if(sheet.headers) {
s3 += '';
// TODO: Skip columns to body
// Headers
columns.forEach(function (column,columnidx) {
s3 += ' 0) {
// Loop over data rows
data.forEach(function(row,rowidx){
// Limit number of rows on the sheet
if(rowidx>sheet.limit) return;
// Extend row properties
var srow = {};
extend(srow,sheet.row);
if(sheet.rows && sheet.rows[rowidx]) {
extend(srow,sheet.rows[rowidx]);
}
s3 += ''
// Data
columns.forEach(function (column,columnidx) {
// Parameters
var cell = {};
extend(cell,sheet.cell);
extend(cell,srow.cell);
if(typeof sheet.column != 'undefined') {
extend(cell,sheet.column.cell);
}
extend(cell,column.cell);
if(sheet.cells && sheet.cells[rowidx] && sheet.cells[rowidx][columnidx]) {
extend(cell,sheet.cells[rowidx][columnidx]);
};
// Create value
var value = row[column.columnid];
if(typeof cell.value == 'function') {
value = cell.value(value,sheet,row,column,cell,rowidx,columnidx);
}
// Define cell type
var typeid = cell.typeid;
if(typeof typeid == 'function') {
typeid = typeid(value,sheet,row,column,cell,rowidx,columnidx);
}
if(typeof typeid == 'undefined') {
if(typeof value == 'number') typeid = 'number';
else if(typeof value == 'string') typeid = 'string';
else if(typeof value == 'boolean') typeid = 'boolean';
else if(typeof value == 'object') {
if(value instanceof Date) typeid = 'date';
}
};
var Type = 'String';
if(typeid == 'number') Type = 'Number';
else if(typeid == 'date') Type = 'Date';
// TODO: What else?
// Prepare Data types styles
var typestyle = '';
if(typeid == 'money') {
typestyle = 'mso-number-format:\"\\#\\,\\#\\#0\\\\ _р_\\.\";white-space:normal;';
} else if(typeid == 'number') {
typestyle = ' ';
} else if (typeid == 'date') {
typestyle = 'mso-number-format:\"Short Date\";';
} else {
// For other types is saved
if( opts.types && opts.types[typeid] && opts.types[typeid].typestyle) {
typestyle = opts.types[typeid].typestyle;
}
}
// TODO Replace with extend...
typestyle = typestyle || 'mso-number-format:\"\\@\";'; // Default type style
s3 += '| ';
// TODO Replace with extend...
var format = cell.format;
if(typeof value == 'undefined') {
s3 += '';
} else if(typeof format != 'undefined') {
if(typeof format == 'function') {
s3 += format(value);
} else if(typeof format == 'string') {
s3 += value; // TODO - add string format
} else {
throw new Error('Unknown format type. Should be function or string');
}
} else {
if(typeid == 'number' || typeid == 'date') {
s3 += value.toString();
} else if(typeid == 'money') {
s3 += (+value).toFixed(2);
} else {
s3 += value;
}
}
s3 += ' | ';
});
s3 += ' ';
});
}
// Finish
s3 += ' | ';
};
s3 +='';
return s1+s2+s3;
};
};
/**
Export to XLSX function
@function
@param {string|object} filename Filename or options
@param {object|undefined} opts Options or undefined
@param {array} data Data
@param {array} columns Columns
@parab {callback} cb Callback function
@return {number} Number of files processed
*/
alasql.into.XLSX = function(filename, opts, data, columns, cb) {
/** @type {number} result */
var res = 1;
if(deepEqual(columns,[{columnid:'_'}])) {
data = data.map(function(dat){return dat._;});
columns = undefined;
} else {
}
/* If Node.js then require() else in browser take a global */
if(typeof exports == 'object') {
var XLSX = require('xlsx');
} else {
var XLSX = utils.global.XLSX;
};
/* If called without filename, use opts */
if(typeof filename == 'object') {
opts = filename;
filename = undefined;
};
/** @type {object} Workbook */
var wb = {SheetNames:[], Sheets:{}};
// Check overwrite flag
if(opts.sourcefilename) {
alasql.utils.loadBinaryFile(opts.sourcefilename,!!cb,function(data){
wb = XLSX.read(data,{type:'binary'});
doExport();
});
} else {
doExport();
};
/* Return result */
if(cb) res = cb(res);
return res;
/**
Export workbook
@function
*/
function doExport() {
/*
If opts is array of arrays then this is a
multisheet workboook, else it is a singlesheet
*/
if(typeof opts == 'object' && opts instanceof Array) {
if(data && data.length > 0) {
data.forEach(function(dat,idx){
prepareSheet(opts[idx],dat,undefined,idx+1)
});
}
} else {
prepareSheet(opts,data,columns,1);
}
saveWorkbook(cb);
}
/**
Prepare sheet
@params {object} opts
@params {array} data
@params {array} columns Columns
*/
function prepareSheet(opts, data, columns, idx) {
/** Default options for sheet */
var opt = {sheetid:'Sheet '+idx,headers:true};
alasql.utils.extend(opt, opts);
// Generate columns if they are not defined
if((!columns || columns.length == 0) && data.length > 0) {
columns = Object.keys(data[0]).map(function(columnid){return {columnid:columnid}});
}
var cells = {};
if(wb.SheetNames.indexOf(opt.sheetid) > -1) {
cells = wb.Sheets[opt.sheetid];
} else {
wb.SheetNames.push(opt.sheetid);
wb.Sheets[opt.sheetid] = {};
cells = wb.Sheets[opt.sheetid];
}
var range = "A1";
if(opt.range) range = opt.range;
var col0 = alasql.utils.xlscn(range.match(/[A-Z]+/)[0]);
var row0 = +range.match(/[0-9]+/)[0]-1;
if(wb.Sheets[opt.sheetid]['!ref']) {
var rangem = wb.Sheets[opt.sheetid]['!ref'];
var colm = alasql.utils.xlscn(rangem.match(/[A-Z]+/)[0]);
var rowm = +rangem.match(/[0-9]+/)[0]-1;
} else {
var colm = 1, rowm = 1;
}
var colmax = Math.max(col0+columns.length,colm);
var rowmax = Math.max(row0+data.length+2,rowm);
var i = row0+1;
wb.Sheets[opt.sheetid]['!ref'] = 'A1:'+alasql.utils.xlsnc(colmax)+(rowmax);
if(opt.headers) {
columns.forEach(function(col, idx){
cells[alasql.utils.xlsnc(col0+idx)+""+i] = {v:col.columnid.trim()};
});
i++;
}
for(var j=0;j= N){
return EOF;
}
if (eol){
return eol = false, EOL;
}
var j = I;
if (text.charCodeAt(j) === quoteCode) {
var i = j;
while (i++ < N) {
if (text.charCodeAt(i) === quoteCode) {
if (text.charCodeAt(i + 1) !== quoteCode){
break;
}
++i;
}
}
I = i + 2;
var c = text.charCodeAt(i + 1);
if (c === 13) {
eol = true;
if (text.charCodeAt(i + 2) === 10){
++I;
}
} else if (c === 10) {
eol = true;
}
return text.substring(j + 1, i).replace(/""/g, '"');
}
while (I < N) {
var c = text.charCodeAt(I++), k = 1;
if(c === 10){
eol = true;
} else if (c === 13) {
eol = true;
if (text.charCodeAt(I) === 10){
++I;
++k;
}
} else if(c !== delimiterCode){
continue;
}
return text.substring(j, I - k);
}
return text.substring(j);
}
while ((t = token()) !== EOF) {
var a = [];
while (t !== EOL && t !== EOF) {
a.push(t);
t = token();
}
if(opt.headers) {
if(n === 0) {
if(typeof opt.headers === 'boolean') {
hs = a;
} else if(opt.headers instanceof Array) {
hs = opt.headers;
var r = {};
hs.forEach(function(h,idx){
r[h] = a[idx];
// Please avoid === here
if((typeof r[h] !== 'undefined') && r[h].length !== 0 && (r[h]).trim() == +r[h]){ // jshint ignore:line
r[h] = +r[h];
}
});
rows.push(r);
}
} else {
var r = {};
hs.forEach(function(h,idx){
r[h] = a[idx];
if((typeof r[h] !== 'undefined') && r[h].length !== 0 && r[h].trim() == +r[h]){ // jshint ignore:line
r[h] = +r[h];
}
});
rows.push(r);
}
n++;
} else {
rows.push(a);
}
}
res = rows;
if(opt.headers) {
if(query && query.sources && query.sources[idx]) {
var columns = query.sources[idx].columns = [];
hs.forEach(function(h){
columns.push({columnid:h});
});
}
}
if(cb){
res = cb(res, idx, query);
}
});
return res;
};
function XLSXLSX(X,filename, opts, cb, idx, query) {
var opt = {};
opts = opts || {};
alasql.utils.extend(opt, opts);
if(typeof opt.headers == 'undefined') opt.headers = true;
var res;
alasql.utils.loadBinaryFile(filename,!!cb,function(data){
// function processData(data) {
var workbook = X.read(data,{type:'binary'});
var sheetid;
if(typeof opt.sheetid === 'undefined') {
sheetid = workbook.SheetNames[0];
} else {
sheetid = opt.sheetid;
}
var range;
if(typeof opt.range === 'undefined') {
range = workbook.Sheets[sheetid]['!ref'];
} else {
range = opt.range;
if(workbook.Sheets[sheetid][range]){
range = workbook.Sheets[sheetid][range];
}
}
var rg = range.split(':');
var col0 = rg[0].match(/[A-Z]+/)[0];
var row0 = +rg[0].match(/[0-9]+/)[0];
var col1 = rg[1].match(/[A-Z]+/)[0];
var row1 = +rg[1].match(/[0-9]+/)[0];
var hh = {};
for(var j=alasql.utils.xlscn(col0);j<=alasql.utils.xlscn(col1);j++){
var col = alasql.utils.xlsnc(j);
if(opt.headers) {
if(workbook.Sheets[sheetid][col+""+row0]) {
hh[col] = workbook.Sheets[sheetid][col+""+row0].v;
} else {
hh[col] = col;
}
} else {
hh[col] = col;
}
}
var res = [];
if(opt.headers){
row0++;
}
for(var i=row0;i<=row1;i++) {
var row = {};
for(var j=alasql.utils.xlscn(col0);j<=alasql.utils.xlscn(col1);j++){
var col = alasql.utils.xlsnc(j);
if(workbook.Sheets[sheetid][col+""+i]) {
row[hh[col]] = workbook.Sheets[sheetid][col+""+i].v;
}
}
res.push(row);
}
// Remove last empty line (issue #548)
if(res.length > 0 && res[res.length-1] && Object.keys(res[res.length-1]).length == 0) {
res.pop();
}
if(cb){
res = cb(res, idx, query);
}
}, function(err){
throw err;
});
return res;
}
alasql.from.XLS = function(filename, opts, cb, idx, query) {
var X;
if(typeof exports === 'object') {
X = require('xlsjs');
} else {
X = utils.global.XLS;
if(!X) {
throw new Error('XLS library is not attached');
}
}
return XLSXLSX(X,filename, opts, cb, idx, query);
}
alasql.from.XLSX = function(filename, opts, cb, idx, query) {
var X;
if(typeof exports === 'object') {
X = require('xlsx');
} else {
X = utils.global.XLSX;
if(!X) {
throw new Error('XLSX library is not attached');
}
}
return XLSXLSX(X,filename, opts, cb, idx, query);
};
alasql.from.XML = function(filename, opts, cb, idx, query) {
var res;
alasql.utils.loadFile(filename,!!cb,function(data){
res = xmlparse(data).root;
if(cb) res = cb(res, idx, query);
});
return res;
};
/**
* Parse the given string of `xml`.
*
* @param {String} xml
* @return {Object}
* @api public
*/
function xmlparse(xml) {
xml = xml.trim();
// strip comments
xml = xml.replace(//g, '');
return document();
/**
* XML document.
*/
function document() {
return {
declaration: declaration(),
root: tag()
}
}
/**
* Declaration.
*/
function declaration() {
var m = match(/^<\?xml\s*/);
if (!m) return;
// tag
var node = {
attributes: {}
};
// attributes
while (!(eos() || is('?>'))) {
var attr = attribute();
if (!attr) return node;
node.attributes[attr.name] = attr.value;
}
match(/\?>\s*/);
return node;
}
/**
* Tag.
*/
function tag() {
var m = match(/^<([\w-:.]+)\s*/);
if (!m) return;
// name
var node = {
name: m[1],
attributes: {},
children: []
};
// attributes
while (!(eos() || is('>') || is('?>') || is('/>'))) {
var attr = attribute();
if (!attr) return node;
node.attributes[attr.name] = attr.value;
}
// self closing tag
if (match(/^\s*\/>\s*/)) {
return node;
}
match(/\??>\s*/);
// content
node.content = content();
// children
var child;
while (child = tag()) {
node.children.push(child);
}
// closing
match(/^<\/[\w-:.]+>\s*/);
return node;
}
/**
* Text content.
*/
function content() {
var m = match(/^([^<]*)/);
if (m) return m[1];
return '';
}
/**
* Attribute.
*/
function attribute() {
var m = match(/([\w:-]+)\s*=\s*("[^"]*"|'[^']*'|\w+)\s*/);
if (!m) return;
return { name: m[1], value: strip(m[2]) }
}
/**
* Strip quotes from `val`.
*/
function strip(val) {
return val.replace(/^['"]|['"]$/g, '');
}
/**
* Match `re` and advance the string.
*/
function match(re) {
var m = xml.match(re);
if (!m) return;
xml = xml.slice(m[0].length);
return m;
}
/**
* End-of-source.
*/
function eos() {
return 0 == xml.length;
}
/**
* Check for `prefix`.
*/
function is(prefix) {
return 0 == xml.indexOf(prefix);
}
};
alasql.from.GEXF = function(filename, opts, cb, idx, query) {
var res;
alasql('SEARCH FROM XML('+filename+')',[],function(data){
res = data;
console.log(res);
if(cb) res=cb(res);
});
return res;
};
/*
//
// HELP for Alasql.js
// Date: 03.11.2014
// (c) 2014, Andrey Gershun
//
*/
yy.Help = function (params) { return yy.extend(this, params); }
yy.Help.prototype.toString = function() {
var s = 'HELP';
if(this.subject) s += ' '+this.subject;
return s;
}
// Help string
helpdocs = [
{command:'ALTER TABLE table RENAME TO table'},
{command:'ALTER TABLE table ADD COLUMN column coldef'},
{command:'ALTER TABLE table MODIFY COLUMN column coldef'},
{command:'ALTER TABLE table RENAME COLUMN column TO column'},
{command:'ALTER TABLE table DROP column'},
{command:'ATTACH engine DATABASE database'},
{command:'ASSERT value'},
{command:'BEGIN [TRANSACTION]'},
{command:'COMMIT [TRANSACTION]'},
{command:'CREATE [engine] DATABASE [IF NOT EXISTS] database'},
{command:'CREATE TABLE [IF NOT EXISTS] table (column definitions)'},
{command:'DELETE FROM table [WHERE expression]'},
{command:'DETACH DATABASE database'},
{command:'DROP [engine] DATABASE [IF EXISTS] database'},
{command:'DROP TABLE [IF EXISTS] table'},
{command:'INSERT INTO table VALUES value,...'},
{command:'INSERT INTO table DEFAULT VALUES'},
{command:'INSERT INTO table SELECT select'},
{command:'HELP [subject]'},
{command:'ROLLBACK [TRANSACTION]'},
{command:'SELECT [modificator] columns [INTO table] [FROM table,...] [[mode] JOIN [ON] [USING]] [WHERE ] [GROUP BY] [HAVING] [ORDER BY] '},
{command:'SET option value'},
{command:'SHOW [engine] DATABASES'},
{command:'SHOW TABLES'},
{command:'SHOW CREATE TABLE table'},
{command:'UPDATE table SET column1 = expression1, ... [WHERE expression]'},
{command:'USE [DATABASE] database'},
{command:'expression'},
{command:'See also http://github/agershun/alasq for more information'}
];
// execute
yy.Help.prototype.execute = function (databaseid, params, cb) {
var ss = [];
if(!this.subject) {
ss = helpdocs;
} else {
ss.push('See also http://github/agershun/alasq for more information');
}
if(cb) ss = cb(ss);
return ss;
};
/*
//
// HELP for Alasql.js
// Date: 03.11.2014
// (c) 2014, Andrey Gershun
//
*/
/**
Print statement
@class
@param {object} params Initial setup properties
*/
yy.Print = function (params) { return yy.extend(this, params); }
/**
Generate SQL string
@this Print statement object
*/
yy.Print.prototype.toString = function() {
var s = 'PRINT';
if(this.statement) s += ' '+this.statement.toString();
return s;
}
/**
Print result of select statement or expression
@param {string} databaseid Database identificator
@param {object} params Query parameters
@param {statement-callback} cb Callback function
@this Print statement object
*/
yy.Print.prototype.execute = function (databaseid,params,cb) {
var self = this;
var res = 1;
alasql.precompile(this,databaseid,params); /** @todo Change from alasql to this */
if(this.exprs && this.exprs.length >0) {
var rs = this.exprs.map(function(expr){
var exprfn = new Function("params,alasql,p",'var y;return '+expr.toJS('({})','', null)).bind(self);
var r = exprfn(params,alasql);
return JSONtoString(r);
});
console.log.apply(console,rs);
} else if(this.select) {
var r = this.select.execute(databaseid,params);
console.log(JSONtoString(r));
} else {
console.log();
}
if(cb) res = cb(res);
return res;
};
/*
//
// HELP for Alasql.js
// Date: 03.11.2014
// (c) 2014, Andrey Gershun
//
*/
yy.Source = function (params) { return yy.extend(this, params); }
yy.Source.prototype.toString = function() {
var s = 'SOURCE';
if(this.url) s += " '"+this.url+" '";
return s;
}
// SOURCE FILE
yy.Source.prototype.execute = function (databaseid,params,cb) {
var res;
loadFile(this.url, !!cb, function(data){
res = alasql(data);
if(cb) res = cb(res);
return res;
}, function(err){
throw err;
});
return res;
};
/*
//
// HELP for Alasql.js
// Date: 03.11.2014
// (c) 2014, Andrey Gershun
//
*/
yy.Require = function (params) { return yy.extend(this, params); }
yy.Require.prototype.toString = function() {
var s = 'REQUIRE';
if(this.paths && this.paths.length > 0) {
s += this.paths.map(function(path){
return path.toString()
}).join(',');
}
if(this.plugins && this.plugins.length > 0) {
s += this.plugins.map(function(plugin){
return plugin.toUpperCase();
}).join(',');
}
return s;
}
/**
Attach plug-in for Alasql
*/
yy.Require.prototype.execute = function (databaseid,params,cb) {
var self = this;
var res = 0;
var ss = '';
if(this.paths && this.paths.length > 0) {
this.paths.forEach(function(path){
loadFile(path.value, !!cb, function(data){
res++;
ss += data;
if(res 0) {
this.plugins.forEach(function(plugin){
// If plugin is not loaded already
if(!alasql.plugins[plugin]) {
loadFile(alasql.path+'/alasql-'+plugin.toLowerCase()+'.js', !!cb, function(data){
// Execute all plugins at the same time
res++;
ss += data;
if(res 0) {
res = res.filter(function(d){
return d.databaseid.match(relike);
});
}
};
if(cb) cb(res);
return res;
};
/**
Create table in localStorage database
@param databaseid {string} AlaSQL database id
@param tableid {string} Table id
@param ifnotexists {boolean} If not exists flag
@param cb {function} Callback
*/
LS.createTable = function(databaseid, tableid, ifnotexists, cb) {
var res = 1;
var lsdbid = alasql.databases[databaseid].lsdbid;
var tb = LS.get(lsdbid+'.'+tableid);
// Check if such record exists
if(tb && !ifnotexists) {
throw new Error('Table "'+tableid+'" alsready exists in localStorage database "'+lsdbid+'"');
};
var lsdb = LS.get(lsdbid);
var table = alasql.databases[databaseid].tables[tableid];
// TODO: Check if required
lsdb.tables[tableid] = true;
LS.set(lsdbid, lsdb);
LS.storeTable(databaseid,tableid);
if(cb) cb(res);
return res;
}
/**
Create table in localStorage database
@param databaseid {string} AlaSQL database id
@param tableid {string} Table id
@param ifexists {boolean} If exists flag
@param cb {function} Callback
*/
LS.dropTable = function (databaseid, tableid, ifexists, cb) {
var res = 1;
var lsdbid = alasql.databases[databaseid].lsdbid;
if(alasql.options.autocommit) {
var lsdb = LS.get(lsdbid);
} else {
var lsdb = alasql.databases[databaseid];
}
if(!ifexists && !lsdb.tables[tableid]) {
throw new Error('Cannot drop table "'+tableid+'" in localStorage, because it does not exist');
};
delete lsdb.tables[tableid];
LS.set(lsdbid, lsdb);
// localStorage.removeItem(lsdbid+'.'+tableid);
LS.removeTable(databaseid,tableid);
if(cb) cb(res);
return res;
}
/**
Read all data from table
*/
LS.fromTable = function(databaseid, tableid, cb, idx, query) {
var lsdbid = alasql.databases[databaseid].lsdbid;
// var res = LS.get(lsdbid+'.'+tableid);
var res = LS.restoreTable(databaseid,tableid).data;
if(cb) res = cb(res, idx, query);
return res;
};
/**
Insert data into the table
@param databaseid {string} Database id
@param tableid {string} Table id
@param value {array} Array of values
@param columns {array} Columns (not used)
@param cb {function} Callback
*/
LS.intoTable = function(databaseid, tableid, value, columns, cb) {
var lsdbid = alasql.databases[databaseid].lsdbid;
var res = value.length;
// var tb = LS.get(lsdbid+'.'+tableid);
var tb = LS.restoreTable(databaseid,tableid);
if(!tb.data) tb.data = [];
tb.data = tb.data.concat(value);
// LS.set(lsdbid+'.'+tableid, tb);
LS.storeTable(databaseid,tableid);
if(cb) cb(res);
return res;
};
/**
Laad data from table
*/
LS.loadTableData = function(databaseid, tableid){
var db = alasql.databases[databaseid];
var lsdbid = alasql.databases[databaseid].lsdbid;
LS.restoreTable(databaseid,tableid);
// db.tables[tableid].data = LS.get(lsdbid+'.'+tableid);
}
/**
Save data to the table
*/
LS.saveTableData = function(databaseid, tableid){
var db = alasql.databases[databaseid];
var lsdbid = alasql.databases[databaseid].lsdbid;
LS.storeTable(lsdbid,tableid);
// LS.set(lsdbid+'.'+tableid,db.tables[tableid].data);
db.tables[tableid].data = undefined;
}
/**
Commit
*/
LS.commit = function(databaseid, cb) {
var db = alasql.databases[databaseid];
var lsdbid = alasql.databases[databaseid].lsdbid;
var lsdb = {databaseid:lsdbid, tables:{}};
if(db.tables) {
for(var tbid in db.tables) {
// TODO: Question - do we need this line
lsdb.tables[tbid] = true;
LS.storeTable(databaseid,tbid);
};
}
LS.set(lsdbid,lsdb);
return 1;
};
/**
Alias BEGIN = COMMIT
*/
LS.begin = LS.commit;
/**
ROLLBACK
*/
LS.rollback = function(databaseid, cb) {
// This does not work and should be fixed
// Plus test 151 and 231
return;
var db = alasql.databases[databaseid];
db.dbversion++;
var lsdbid = alasql.databases[databaseid].lsdbid;
var lsdb = LS.get(lsdbid);
// if(!alasql.options.autocommit) {
delete alasql.databases[databaseid];
alasql.databases[databaseid] = new alasql.Database(databaseid);
extend(alasql.databases[databaseid], lsdb);
alasql.databases[databaseid].databaseid = databaseid;
alasql.databases[databaseid].engineid = 'LOCALSTORAGE';
if(lsdb.tables){
for(var tbid in lsdb.tables) {
LS.restoreTable(databaseid,tbid);
// index columns
// convert types
}
}
// }
}
//
// 91websql.js
// WebSQL database support
// (c) 2014, Andrey Gershun
//
var SQLITE = alasql.engines.SQLITE = function (){};
SQLITE.createDatabase = function(wdbid, args, ifnotexists, dbid, cb){
throw new Error('Connot create SQLITE database in memory. Attach it.');
};
SQLITE.dropDatabase = function(databaseid){
throw new Error('This is impossible to drop SQLite database. Detach it.');
};
SQLITE.attachDatabase = function(sqldbid, dbid, args, params, cb){
var res = 1;
if(alasql.databases[dbid]) {
throw new Error('Unable to attach database as "'+dbid+'" because it already exists');
};
if(args[0] && (args[0] instanceof yy.StringValue)
|| (args[0] instanceof yy.ParamValue)) {
if(args[0] instanceof yy.StringValue) {
var value = args[0].value;
} else if(args[0] instanceof yy.ParamValue) {
var value = params[args[0].param];
}
alasql.utils.loadBinaryFile(value,true,function(data){
var db = new alasql.Database(dbid || sqldbid);
db.engineid = "SQLITE";
db.sqldbid = sqldbid;
var sqldb = db.sqldb = new SQL.Database(data);
db.tables = [];
var tables = sqldb.exec("SELECT * FROM sqlite_master WHERE type='table'")[0].values;
tables.forEach(function(tbl){
db.tables[tbl[1]] = {};
var columns = db.tables[tbl[1]].columns = [];
var ast = alasql.parse(tbl[4]);
var coldefs = ast.statements[0].columns;
if(coldefs && coldefs.length>0) {
coldefs.forEach(function(cd){
columns.push(cd);
});
}
});
cb(1);
}, function(err){
throw new Error('Cannot open SQLite database file "'+args[0].value+'"');
})
return res;
} else {
throw new Error('Cannot attach SQLite database without a file');
};
return res;
}
SQLITE.fromTable = function(databaseid, tableid, cb, idx, query){
var data = alasql.databases[databaseid].sqldb.exec("SELECT * FROM "+tableid);
var columns = query.sources[idx].columns = [];
if(data[0].columns.length > 0) {
data[0].columns.forEach(function(columnid) {
columns.push({columnid:columnid});
});
};
var res = [];
if(data[0].values.length > 0) {
data[0].values.forEach(function(d){
var r = {};
columns.forEach(function(col,idx){
r[col.columnid] = d[idx];
});
res.push(r);
});
}
if(cb) cb(res, idx, query);
};
SQLITE.intoTable = function(databaseid, tableid, value, columns, cb) {
var sqldb = alasql.databases[databaseid].sqldb;
for(var i=0, ilen = value.length;i 1) {
var sql = 'REQUIRE ' + paths.map(function(p){
return '"'+p+'"';
}).join(",");
alasql(sql,[],cb);
}
} else if(path === false) {
delete alasql.webworker;
return;
}
};
}
|