1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
| function SQLite(db) {
//simple call should return object, not just run function
if (!(this instanceof SQLite))
return new SQLite(db);
//Create file. AChrom is install directory for XUL app
var
file = Components.classes["@mozilla.org/file/directory_service;1"]
.getService(Components.interfaces.nsIProperties)
.get("AChrom", Components.interfaces.nsIFile);
//.get("ProfD", Components.interfaces.nsIFile);
//Create storage service instance
var
storageService = Components.classes["@mozilla.org/storage/service;1"]
.getService(Components.interfaces.mozIStorageService);
//expand the file to the requested database
file.append(db);
//open the database connection
this.conn = storageService.openDatabase(file);
//Return SQLite object
return this;
}
//Cache for SQLite queries
SQLite.prototype.queries={};
//Create new Query function(s)
SQLite.prototype.queryFunction=function (name,query) {
//If name is an object
alert("ok")
if (typeof name == 'object') {
//initialize our return value
var queryGroup={};
//for each function NVP
for (var i in name)
//create a matching queryFunction
queryGroup[i]=arguments.callee.apply(this,[i,name[i]]);
//return the query namespace
return queryGroup;
}
var
//allow the internal function to access this SQLite instance
_this=this,
//use _conn as a shortcut if necessary
_conn=this.conn,
//Match all % parameters (for type matching)
params=query.matchAll(/%(n|s|f)(\d+)/i),
//And replace with their real counterparts
realQuery=query.replace(/%(n|s|f)(\d+)/ig,'?$2'),
//create the statement
statement=_conn.createStatement(realQuery),
//Create the query function, loaded with the variables available above.
exec = function () {
var i,idx;
//walk through the parameters
for (i=0; i<params.length; i++) {
//get the param index for the parameter
idx=statement.getParameterIndex('?'+params[i][2]);
switch (params[i][1].toLowerCase()) {
case 'n':
//it's a number. Use int64's
statement.bindInt64Parameter(idx,arguments[idx]);
break;
case 'f':
//it's a float. Use doubles.
statement.bindDoubleParameter(idx,arguments[idx]);
break;
default:
//Assume it's a UTF8 string; ASCII and blobs will handle this fine on the input side
statement.bindUTF8StringParameter(idx,arguments[idx]);
}
}
//Some variables for the return
var
cols = statement.columnCount,
col = 0,
colTypes=[],
colNames=[],
ret=[],
name,
rowData;
//If we've requested columns
if (cols>0) {
while (statement.executeStep()) {
//execute like we're expecting a return value.
rowData={};
for (col=0; col<cols; col++) {
if (colNames[col]==undefined) {
//populate the colNames and colTypes arrays, as needed
colNames[col]=statement.getColumnName(col);
colTypes[col]=statement.getTypeOfIndex(col);
}
name=colNames[col];
switch (colTypes[col]) {
//Depending on the column type (this really matters on output)
//fill the rowData object
case 0:
rowData[name]=null; break;
case 1:
rowData[name]=statement.getInt64(col); break;
case 2:
rowData[name]=statement.getDouble(col); break;
case 3:
rowData[name]=statement.getUTF8String(col); break;
case 4:
rowData[name]=statement.getBlob(col); break;
}
}
//And, once this row is done, append it to the array.
ret.push(rowData);
}
} else {
//If there's no columns to grab, just run the query
statement.execute();
}
//Either way, reset to stave off hobgoblins
statement.reset();
if (ret.length>0) //We've got return values? Good! Send 'em up.
return ret;
//We don't? Well, an insert will return the last insert ID
//and a no-value select will return 0.
return _conn.lastInsertRowID;
};
//Add exec to our set of precached queries, and return it.
return this.queries[name]=exec;
};
String.prototype.matchAll = function (re,set) {
//Required for SQLite parameter matching
if (set==undefined) set=RE_MATCH_ORDER;
//copy source string, initialize output array
var ret=[],ts = this;
while (re.test(ts)) {
//match
ret.push(ts.match(re));
//remove match
ts=ts.replace(re,'');
}
//If we have to swap orders, do so.
if (set==RE_SET_ORDER) {
var i,j,nRet = [];
for (i=0; i<ret.length; i++) {
for (j=0; j<ret[i].length; j++) {
if (i==0) nRet[j]=[];
nRet[j].push(ret[i][j]);
}
}
ret=nRet;
}
return ret;
} |
