]>
Commit | Line | Data |
---|---|---|
6527f429 DM |
1 | /** |
2 | * SQL proxy lets you store data in a SQL database. | |
3 | * The Sencha Touch SQL proxy outputs model data into an HTML5 | |
4 | * local database using WebSQL. | |
5 | * | |
6 | * You can create a Store for the proxy, for example: | |
7 | * | |
8 | * Ext.require(["Ext.data.proxy.SQL"]); | |
9 | * | |
10 | * Ext.define("User", { | |
11 | * extend: "Ext.data.Model", | |
12 | * config: { | |
13 | * fields: [ "firstName", "lastName" ] | |
14 | * } | |
15 | * }); | |
16 | * | |
17 | * Ext.create("Ext.data.Store", { | |
18 | * model: "User", | |
19 | * storeId: "Users", | |
20 | * proxy: { | |
21 | * type: "sql" | |
22 | * } | |
23 | * }); | |
24 | * | |
25 | * Ext.getStore("Users").add({ | |
26 | * firstName: "Polly", | |
27 | * lastName: "Hedra" | |
28 | * }); | |
29 | * | |
30 | * Ext.getStore("Users").sync(); | |
31 | * | |
32 | * To destroy a table use: | |
33 | * | |
34 | * Ext.getStore("Users").getProxy().dropTable(); | |
35 | * | |
36 | * To recreate a table use: | |
37 | * | |
38 | * Ext.data.Store.sync() or Ext.data.Model.save() | |
39 | */\r | |
40 | Ext.define('Ext.data.proxy.Sql', {\r | |
41 | alias: 'proxy.sql',\r | |
42 | extend: 'Ext.data.proxy.Client',\r | |
43 | alternateClassName: 'Ext.data.proxy.SQL',\r | |
44 | isSQLProxy: true,\r | |
45 | config: {\r | |
46 | /** | |
47 | * @cfg {Object} reader | |
48 | * @hide | |
49 | */\r | |
50 | reader: null,\r | |
51 | /** | |
52 | * @cfg {Object} writer | |
53 | * @hide | |
54 | */\r | |
55 | writer: null,\r | |
56 | /** | |
57 | * @cfg {String} table | |
58 | * Optional Table name to use if not provided ModelName will be used | |
59 | */\r | |
60 | table: null,\r | |
61 | /** | |
62 | * @cfg {String} database | |
63 | * Database name to access tables from | |
64 | */\r | |
65 | database: 'Sencha'\r | |
66 | },\r | |
67 | _createOptions: {\r | |
68 | silent: true,\r | |
69 | dirty: false\r | |
70 | },\r | |
71 | updateModel: function(model) {\r | |
72 | var me = this,\r | |
73 | modelName, len, i, columns, quoted;\r | |
74 | if (model) {\r | |
75 | me.uniqueIdStrategy = model.identifier.isUnique;\r | |
76 | if (!me.getTable()) {\r | |
77 | modelName = model.entityName;\r | |
78 | me.setTable(modelName.slice(modelName.lastIndexOf('.') + 1));\r | |
79 | }\r | |
80 | me.columns = columns = me.getPersistedModelColumns(model);\r | |
81 | me.quotedColumns = quoted = [];\r | |
82 | for (i = 0 , len = columns.length; i < len; ++i) {\r | |
83 | quoted.push('"' + columns[i] + '"');\r | |
84 | }\r | |
85 | }\r | |
86 | me.callParent([\r | |
87 | model\r | |
88 | ]);\r | |
89 | },\r | |
90 | setException: function(operation, error) {\r | |
91 | operation.setException(error);\r | |
92 | },\r | |
93 | create: function(operation) {\r | |
94 | var me = this,\r | |
95 | records = operation.getRecords(),\r | |
96 | result, error;\r | |
97 | operation.setStarted();\r | |
98 | me.executeTransaction(function(transaction) {\r | |
99 | me.insertRecords(records, transaction, function(resultSet, statementError) {\r | |
100 | result = resultSet;\r | |
101 | error = statementError;\r | |
102 | });\r | |
103 | }, function(transactionError) {\r | |
104 | operation.setException(transactionError);\r | |
105 | }, function() {\r | |
106 | if (error) {\r | |
107 | operation.setException(statementError);\r | |
108 | } else {\r | |
109 | operation.process(result);\r | |
110 | }\r | |
111 | });\r | |
112 | },\r | |
113 | read: function(operation) {\r | |
114 | var me = this,\r | |
115 | model = me.getModel(),\r | |
116 | records = operation.getRecords(),\r | |
117 | record = records ? records[0] : null,\r | |
118 | result, error, id, params;\r | |
119 | if (record && !record.phantom) {\r | |
120 | id = record.getId();\r | |
121 | } else {\r | |
122 | id = operation.getId();\r | |
123 | }\r | |
124 | if (id !== undefined) {\r | |
125 | params = {\r | |
126 | idOnly: true,\r | |
127 | id: id\r | |
128 | };\r | |
129 | } else {\r | |
130 | params = {\r | |
131 | page: operation.getPage(),\r | |
132 | start: operation.getStart(),\r | |
133 | limit: operation.getLimit(),\r | |
134 | sorters: operation.getSorters(),\r | |
135 | filters: operation.getFilters()\r | |
136 | };\r | |
137 | }\r | |
138 | operation.setStarted();\r | |
139 | me.executeTransaction(function(transaction) {\r | |
140 | me.selectRecords(transaction, params, function(resultSet, statementError) {\r | |
141 | result = resultSet;\r | |
142 | error = statementError;\r | |
143 | });\r | |
144 | }, function(transactionError) {\r | |
145 | operation.setException(transactionError);\r | |
146 | }, function() {\r | |
147 | if (error) {\r | |
148 | operation.setException(statementError);\r | |
149 | } else {\r | |
150 | operation.process(result);\r | |
151 | }\r | |
152 | });\r | |
153 | },\r | |
154 | update: function(operation) {\r | |
155 | var me = this,\r | |
156 | records = operation.getRecords(),\r | |
157 | result, error;\r | |
158 | operation.setStarted();\r | |
159 | me.executeTransaction(function(transaction) {\r | |
160 | me.updateRecords(transaction, records, function(resultSet, statementError) {\r | |
161 | result = resultSet;\r | |
162 | error = statementError;\r | |
163 | });\r | |
164 | }, function(transactionError) {\r | |
165 | operation.setException(transactionError);\r | |
166 | }, function() {\r | |
167 | if (error) {\r | |
168 | operation.setException(statementError);\r | |
169 | } else {\r | |
170 | operation.process(result);\r | |
171 | }\r | |
172 | });\r | |
173 | },\r | |
174 | erase: function(operation) {\r | |
175 | var me = this,\r | |
176 | records = operation.getRecords(),\r | |
177 | result, error;\r | |
178 | operation.setStarted();\r | |
179 | me.executeTransaction(function(transaction) {\r | |
180 | me.destroyRecords(transaction, records, function(resultSet, statementError) {\r | |
181 | result = resultSet;\r | |
182 | error = statementError;\r | |
183 | });\r | |
184 | }, function(transactionError) {\r | |
185 | operation.setException(transactionError);\r | |
186 | }, function() {\r | |
187 | if (error) {\r | |
188 | operation.setException(error);\r | |
189 | } else {\r | |
190 | operation.process(result);\r | |
191 | }\r | |
192 | });\r | |
193 | },\r | |
194 | createTable: function(transaction) {\r | |
195 | var me = this;\r | |
196 | if (!transaction) {\r | |
197 | me.executeTransaction(function(transaction) {\r | |
198 | me.createTable(transaction);\r | |
199 | });\r | |
200 | return;\r | |
201 | }\r | |
202 | me.executeStatement(transaction, 'CREATE TABLE IF NOT EXISTS "' + me.getTable() + '" (' + me.getSchemaString() + ')', function() {\r | |
203 | me.tableExists = true;\r | |
204 | });\r | |
205 | },\r | |
206 | insertRecords: function(records, transaction, callback) {\r | |
207 | var me = this,\r | |
208 | columns = me.columns,\r | |
209 | totalRecords = records.length,\r | |
210 | executed = 0,\r | |
211 | uniqueIdStrategy = me.uniqueIdStrategy,\r | |
212 | setOptions = me._createOptions,\r | |
213 | len = records.length,\r | |
214 | i, record, placeholders, sql, data, values, errors, completeIf;\r | |
215 | completeIf = function(transaction) {\r | |
216 | ++executed;\r | |
217 | if (executed === totalRecords) {\r | |
218 | callback.call(me, new Ext.data.ResultSet({\r | |
219 | success: !errors\r | |
220 | }), errors);\r | |
221 | }\r | |
222 | };\r | |
223 | placeholders = Ext.String.repeat('?', columns.length, ',');\r | |
224 | sql = 'INSERT INTO "' + me.getTable() + '" (' + me.quotedColumns.join(',') + ') VALUES (' + placeholders + ')';\r | |
225 | for (i = 0; i < len; ++i) {\r | |
226 | record = records[i];\r | |
227 | data = me.getRecordData(record);\r | |
228 | values = me.getColumnValues(columns, data);\r | |
229 | // Capture the record in closure scope so we can access it later\r | |
230 | (function(record) {\r | |
231 | me.executeStatement(transaction, sql, values, function(transaction, resultSet) {\r | |
232 | if (!uniqueIdStrategy) {\r | |
233 | record.setId(resultSet.insertId, setOptions);\r | |
234 | }\r | |
235 | completeIf();\r | |
236 | }, function(transaction, error) {\r | |
237 | if (!errors) {\r | |
238 | errors = [];\r | |
239 | }\r | |
240 | errors.push(error);\r | |
241 | completeIf();\r | |
242 | });\r | |
243 | })(record);\r | |
244 | }\r | |
245 | },\r | |
246 | selectRecords: function(transaction, params, callback, scope) {\r | |
247 | var me = this,\r | |
248 | Model = me.getModel(),\r | |
249 | idProperty = Model.idProperty,\r | |
250 | sql = 'SELECT * FROM "' + me.getTable() + '"',\r | |
251 | filterStatement = ' WHERE ',\r | |
252 | sortStatement = ' ORDER BY ',\r | |
253 | values = [],\r | |
254 | sorters, filters, placeholder, i, len, result, filter, sorter, property, operator, value;\r | |
255 | if (params.idOnly) {\r | |
256 | sql += filterStatement + '"' + idProperty + '" = ?';\r | |
257 | values.push(params);\r | |
258 | } else {\r | |
259 | filters = params.filters;\r | |
260 | len = filters && filters.length;\r | |
261 | if (len) {\r | |
262 | for (i = 0; i < len; i++) {\r | |
263 | filter = filters[i];\r | |
264 | property = filter.getProperty();\r | |
265 | value = me.toSqlValue(filter.getValue(), Model.getField(property));\r | |
266 | operator = filter.getOperator();\r | |
267 | if (property !== null) {\r | |
268 | operator = operator || '=';\r | |
269 | placeholder = '?';\r | |
270 | if (operator === 'like' || (operator === '=' && filter.getAnyMatch())) {\r | |
271 | operator = 'LIKE';\r | |
272 | value = '%' + value + '%';\r | |
273 | }\r | |
274 | if (operator === 'in' || operator === 'notin') {\r | |
275 | if (operator === 'notin') {\r | |
276 | operator = 'not in';\r | |
277 | }\r | |
278 | placeholder = '(' + Ext.String.repeat('?', value.length, ',') + ')';\r | |
279 | values = values.concat(value);\r | |
280 | } else {\r | |
281 | values.push(value);\r | |
282 | }\r | |
283 | sql += filterStatement + '"' + property + '" ' + operator + ' ' + placeholder;\r | |
284 | filterStatement = ' AND ';\r | |
285 | }\r | |
286 | }\r | |
287 | }\r | |
288 | sorters = params.sorters;\r | |
289 | len = sorters && sorters.length;\r | |
290 | if (len) {\r | |
291 | for (i = 0; i < len; i++) {\r | |
292 | sorter = sorters[i];\r | |
293 | property = sorter.getProperty();\r | |
294 | if (property !== null) {\r | |
295 | sql += sortStatement + '"' + property + '" ' + sorter.getDirection();\r | |
296 | sortStatement = ', ';\r | |
297 | }\r | |
298 | }\r | |
299 | }\r | |
300 | // handle start, limit, sort, filter and group params\r | |
301 | if (params.page !== undefined) {\r | |
302 | sql += ' LIMIT ' + parseInt(params.start, 10) + ', ' + parseInt(params.limit, 10);\r | |
303 | }\r | |
304 | }\r | |
305 | me.executeStatement(transaction, sql, values, function(transaction, resultSet) {\r | |
306 | var rows = resultSet.rows,\r | |
307 | count = rows.length,\r | |
308 | records = [],\r | |
309 | fields = Model.fields,\r | |
310 | fieldsLen = fields.length,\r | |
311 | raw, data, i, len, j, field, name;\r | |
312 | for (i = 0 , len = count; i < len; ++i) {\r | |
313 | raw = rows.item(i);\r | |
314 | data = {};\r | |
315 | for (j = 0; j < fieldsLen; ++j) {\r | |
316 | field = fields[j];\r | |
317 | name = field.name;\r | |
318 | data[name] = me.fromSqlValue(raw[name], field);\r | |
319 | }\r | |
320 | records.push(new Model(data));\r | |
321 | }\r | |
322 | callback.call(me, new Ext.data.ResultSet({\r | |
323 | records: records,\r | |
324 | success: true,\r | |
325 | total: count,\r | |
326 | count: count\r | |
327 | }));\r | |
328 | }, function(transaction, error) {\r | |
329 | callback.call(me, new Ext.data.ResultSet({\r | |
330 | success: false,\r | |
331 | total: 0,\r | |
332 | count: 0\r | |
333 | }), error);\r | |
334 | });\r | |
335 | },\r | |
336 | updateRecords: function(transaction, records, callback) {\r | |
337 | var me = this,\r | |
338 | columns = me.columns,\r | |
339 | quotedColumns = me.quotedColumns,\r | |
340 | totalRecords = records.length,\r | |
341 | executed = 0,\r | |
342 | updates = [],\r | |
343 | setOptions = me._createOptions,\r | |
344 | len, i, record, placeholders, sql, data, values, errors, completeIf;\r | |
345 | completeIf = function(transaction) {\r | |
346 | ++executed;\r | |
347 | if (executed === totalRecords) {\r | |
348 | callback.call(me, new Ext.data.ResultSet({\r | |
349 | success: !errors\r | |
350 | }), errors);\r | |
351 | }\r | |
352 | };\r | |
353 | for (i = 0 , len = quotedColumns.length; i < len; i++) {\r | |
354 | updates.push(quotedColumns[i] + ' = ?');\r | |
355 | }\r | |
356 | sql = 'UPDATE "' + me.getTable() + '" SET ' + updates.join(', ') + ' WHERE "' + me.getModel().idProperty + '" = ?';\r | |
357 | for (i = 0 , len = records.length; i < len; ++i) {\r | |
358 | record = records[i];\r | |
359 | data = me.getRecordData(record);\r | |
360 | values = me.getColumnValues(columns, data);\r | |
361 | values.push(record.getId());\r | |
362 | // Capture the record in closure scope so we can access it later\r | |
363 | (function(record) {\r | |
364 | me.executeStatement(transaction, sql, values, function(transaction, resultSet) {\r | |
365 | completeIf();\r | |
366 | }, function(transaction, error) {\r | |
367 | if (!errors) {\r | |
368 | errors = [];\r | |
369 | }\r | |
370 | errors.push(error);\r | |
371 | completeIf();\r | |
372 | });\r | |
373 | })(record);\r | |
374 | }\r | |
375 | },\r | |
376 | destroyRecords: function(transaction, records, callback) {\r | |
377 | var me = this,\r | |
378 | table = me.getTable(),\r | |
379 | idProperty = me.getModel().idProperty,\r | |
380 | ids = [],\r | |
381 | values = [],\r | |
382 | destroyedRecords = [],\r | |
383 | len = records.length,\r | |
384 | idStr = '"' + idProperty + '" = ?',\r | |
385 | i, result, record, sql;\r | |
386 | for (i = 0; i < len; i++) {\r | |
387 | ids.push(idStr);\r | |
388 | values.push(records[i].getId());\r | |
389 | }\r | |
390 | sql = 'DELETE FROM "' + me.getTable() + '" WHERE ' + ids.join(' OR ');\r | |
391 | me.executeStatement(transaction, sql, values, function(transaction, resultSet) {\r | |
392 | callback.call(me, new Ext.data.ResultSet({\r | |
393 | success: true\r | |
394 | }));\r | |
395 | }, function(transaction, error) {\r | |
396 | callback.call(me, new Ext.data.ResultSet({\r | |
397 | success: false\r | |
398 | }), error);\r | |
399 | });\r | |
400 | },\r | |
401 | /** | |
402 | * Formats the data for each record before sending it to the server. This | |
403 | * method should be overridden to format the data in a way that differs from the default. | |
404 | * @param {Object} record The record that we are writing to the server. | |
405 | * @return {Object} An object literal of name/value keys to be written to the server. | |
406 | * By default this method returns the data property on the record. | |
407 | */\r | |
408 | getRecordData: function(record) {\r | |
409 | var me = this,\r | |
410 | fields = record.fields,\r | |
411 | idProperty = record.idProperty,\r | |
412 | uniqueIdStrategy = me.uniqueIdStrategy,\r | |
413 | data = {},\r | |
414 | len = fields.length,\r | |
415 | recordData = record.data,\r | |
416 | i, name, value, field;\r | |
417 | for (i = 0; i < len; ++i) {\r | |
418 | field = fields[i];\r | |
419 | if (field.persist !== false) {\r | |
420 | name = field.name;\r | |
421 | if (name === idProperty && !uniqueIdStrategy) {\r | |
422 | \r | |
423 | continue;\r | |
424 | }\r | |
425 | data[name] = me.toSqlValue(recordData[name], field);\r | |
426 | }\r | |
427 | }\r | |
428 | return data;\r | |
429 | },\r | |
430 | getColumnValues: function(columns, data) {\r | |
431 | var len = columns.length,\r | |
432 | values = [],\r | |
433 | i, column, value;\r | |
434 | for (i = 0; i < len; i++) {\r | |
435 | column = columns[i];\r | |
436 | value = data[column];\r | |
437 | if (value !== undefined) {\r | |
438 | values.push(value);\r | |
439 | }\r | |
440 | }\r | |
441 | return values;\r | |
442 | },\r | |
443 | getSchemaString: function() {\r | |
444 | var me = this,\r | |
445 | schema = [],\r | |
446 | model = me.getModel(),\r | |
447 | idProperty = model.idProperty,\r | |
448 | fields = model.fields,\r | |
449 | uniqueIdStrategy = me.uniqueIdStrategy,\r | |
450 | len = fields.length,\r | |
451 | i, field, type, name;\r | |
452 | for (i = 0; i < len; i++) {\r | |
453 | field = fields[i];\r | |
454 | type = field.getType();\r | |
455 | name = field.name;\r | |
456 | if (name === idProperty) {\r | |
457 | if (uniqueIdStrategy) {\r | |
458 | type = me.convertToSqlType(type);\r | |
459 | schema.unshift('"' + idProperty + '" ' + type);\r | |
460 | } else {\r | |
461 | schema.unshift('"' + idProperty + '" INTEGER PRIMARY KEY AUTOINCREMENT');\r | |
462 | }\r | |
463 | } else {\r | |
464 | type = me.convertToSqlType(type);\r | |
465 | schema.push('"' + name + '" ' + type);\r | |
466 | }\r | |
467 | }\r | |
468 | return schema.join(', ');\r | |
469 | },\r | |
470 | convertToSqlType: function(type) {\r | |
471 | switch (type.toLowerCase()) {\r | |
472 | case 'string':\r | |
473 | case 'auto':\r | |
474 | return 'TEXT';\r | |
475 | case 'int':\r | |
476 | case 'date':\r | |
477 | return 'INTEGER';\r | |
478 | case 'float':\r | |
479 | return 'REAL';\r | |
480 | case 'bool':\r | |
481 | return 'NUMERIC';\r | |
482 | }\r | |
483 | },\r | |
484 | dropTable: function() {\r | |
485 | var me = this;\r | |
486 | me.executeTransaction(function(transaction) {\r | |
487 | me.executeStatement(transaction, 'DROP TABLE "' + me.getTable() + '"', function() {\r | |
488 | me.tableExists = false;\r | |
489 | });\r | |
490 | }, null, null, false);\r | |
491 | },\r | |
492 | getDatabaseObject: function() {\r | |
493 | return window.openDatabase(this.getDatabase(), '1.0', 'Sencha Database', 5 * 1024 * 1024);\r | |
494 | },\r | |
495 | privates: {\r | |
496 | executeStatement: function(transaction, sql, values, success, failure) {\r | |
497 | var me = this;\r | |
498 | transaction.executeSql(sql, values, success ? function() {\r | |
499 | success.apply(me, arguments);\r | |
500 | } : null, failure ? function() {\r | |
501 | failure.apply(me, arguments);\r | |
502 | } : null);\r | |
503 | },\r | |
504 | executeTransaction: function(runner, failure, success, autoCreateTable) {\r | |
505 | var me = this;\r | |
506 | autoCreateTable = autoCreateTable !== false;\r | |
507 | me.getDatabaseObject().transaction(runner ? function(transaction) {\r | |
508 | if (autoCreateTable && !me.tableExists) {\r | |
509 | me.createTable(transaction);\r | |
510 | }\r | |
511 | runner.apply(me, arguments);\r | |
512 | } : null, failure ? function() {\r | |
513 | failure.apply(me, arguments);\r | |
514 | } : null, success ? function() {\r | |
515 | success.apply(me, arguments);\r | |
516 | } : null);\r | |
517 | },\r | |
518 | fromSqlValue: function(value, field) {\r | |
519 | if (field.isDateField) {\r | |
520 | value = value ? new Date(value) : null;\r | |
521 | } else if (field.isBooleanField) {\r | |
522 | value = value === 1;\r | |
523 | }\r | |
524 | return value;\r | |
525 | },\r | |
526 | getPersistedModelColumns: function(model) {\r | |
527 | var fields = model.fields,\r | |
528 | uniqueIdStrategy = this.uniqueIdStrategy,\r | |
529 | idProperty = model.idProperty,\r | |
530 | columns = [],\r | |
531 | len = fields.length,\r | |
532 | i, field, name;\r | |
533 | for (i = 0; i < len; ++i) {\r | |
534 | field = fields[i];\r | |
535 | name = field.name;\r | |
536 | if (name === idProperty && !uniqueIdStrategy) {\r | |
537 | \r | |
538 | continue;\r | |
539 | }\r | |
540 | if (field.persist !== false) {\r | |
541 | columns.push(field.name);\r | |
542 | }\r | |
543 | }\r | |
544 | return columns;\r | |
545 | },\r | |
546 | toSqlValue: function(value, field) {\r | |
547 | if (field.isDateField) {\r | |
548 | value = value ? value.getTime() : null;\r | |
549 | } else if (field.isBooleanField) {\r | |
550 | value = value ? 1 : 0;\r | |
551 | }\r | |
552 | return value;\r | |
553 | }\r | |
554 | }\r | |
555 | });\r | |
556 | \r |