2 * This file is part of WolneLektury-Mobile, licensed under GNU Affero GPLv3 or later.
3 * Copyright © Fundacja Nowoczesna Polska. See NOTICE for more information.
8 var WL_INITIAL = WL + '/media/api/mobile/initial/initial.db';
9 var WL_UPDATE = WL + '/api/changes/SINCE.json?book_fields=author,html,parent,parent_number,sort_key,title' +
10 '&tag_fields=books,category,name,sort_key' +
11 '&tag_categories=author,epoch,genre,kind';
15 var categories = {'author': 'autor',
22 // FIXME: htmlescape strings!
25 // for preparing sql statements
27 // var s = new Sql("INSERT ... '{0}', '{1}' ...";
28 // s.prepare("abc", ...)
29 var Sql = function(scheme) {
33 self.sql_escape = function(term) {
34 return term.toString().replace("'", "''");
37 self.prepare = function() {
39 return self.text.replace(/{(\d+)}/g, function(match, number) {
40 return self.sql_escape(args[parseInt(number)]);
46 var Catalogue = new function() {
47 /* API for database */
52 this.init = function(success, error) {
53 console.log('Catalogue.init');
55 self.updateDB(function() {
56 self.db = window.openDatabase("wolnelektury", "1.0", "WL Catalogue", 1);
59 onFunctionCall: function(val) {
60 var re = new RegExp(val.getString(0));
61 if (val.getString(1).match(re))
67 self.db.createFunction("REGEXP", 2, regexp);*/
71 error && error('Nie mogę otworzyć bazy danych: ' + err);
75 error && error('Błąd migracji: ' + err);
79 self.sqlSanitize = function(term) {
80 return term.toString().replace("'", "''");
84 /* check if DB needs updating and upload a fresh copy, if so */
85 this.updateDB = function(success, error) {
86 var has_ver = window.localStorage.getItem('db_ver');
87 if (has_ver == DB_VER) {
88 console.log('db ok, skipping')
93 var done = function() {
95 window.localStorage.setItem('db_ver', DB_VER);
96 console.log('db updated');
101 // this is Android-specific for now
102 self.upload_db_android(done, error);
106 this.upload_db_android = function(success, error) {
107 console.log('upload db for Android 2.x+');
109 var dbname = "wolnelektury";
110 var db = window.openDatabase(dbname, "1.0", "WL Catalogue", 500000);
112 console.log('db created successfully');
113 DBPut.fetch(WL_INITIAL, function(data) {
114 console.log('db fetch successful');
115 success && success();
117 error && error('Błąd podczas pobierania bazy danych: ' + data);
120 error && error('Błąd podczas inicjowania bazy danych: ' + data);
125 this.withState = function(callback) {
126 self.db.transaction(function(tx) {
127 tx.executeSql("SELECT * FROM state", [],
128 function(tx, results) {
129 if (results.rows.length) {
130 callback(results.rows.item(0));
133 callback({last_checked: 0});
140 this.withBook = function(id, callback, error) {
141 console.log('withBook '+id)
142 self.db.transaction(function(tx) {
143 tx.executeSql("SELECT * FROM book WHERE id="+id, [],
144 function(tx, results) {
145 if (results.rows.length) {
146 callback(results.rows.item(0));
155 this.withBooks = function(ids, callback) {
156 console.log('withBooks ' + ids)
157 self.db.transaction(function(tx) {
158 tx.executeSql("SELECT * FROM book WHERE id IN ("+ids+") ORDER BY sort_key", [],
159 function(tx, results) {
161 var count = results.rows.length;
162 for (var i=0; i<count; ++i) {
163 items.push(results.rows.item(i));
171 this.withChildren = function(id, callback) {
172 console.log('withChildren ' + id)
173 self.db.transaction(function(tx) {
174 tx.executeSql("SELECT * FROM book WHERE parent="+id+" ORDER BY parent_number, sort_key", [],
175 function(tx, results) {
177 var count = results.rows.length;
178 for (var i=0; i<count; ++i) {
179 books.push(results.rows.item(i));
186 this.withTag = function(id, callback, error) {
187 console.log('withTag '+id)
188 self.db.transaction(function(tx) {
189 tx.executeSql("SELECT * FROM tag WHERE id="+id, [],
190 function(tx, results) {
191 if (results.rows.length) {
192 callback(results.rows.item(0));
201 this.withCategory = function(category, callback) {
202 console.log('withCategory ' + category)
203 self.db.transaction(function(tx) {
204 tx.executeSql("SELECT * FROM tag WHERE category='"+category+"' ORDER BY sort_key", [],
205 function(tx, results) {
207 var count = results.rows.length;
208 for (var i=0; i<count; ++i)
209 items.push(results.rows.item(i));
216 /* takes a query, returns a list of {view,id,label} objects to a callback */
217 this.withSearch = function(term, callback) {
218 console.log('searching...');
219 term = term.replace(/^\s+|\s+$/g, '') ;
222 function booksFound(tx, results) {
223 var len = results.rows.length;
224 console.log('found books: ' + len);
225 for (var i=0; i<len; i++) {
226 var item = results.rows.item(i);
234 function tagsFound(tx, results) {
235 var len = results.rows.length;
236 console.log('found tags: ' + len);
237 for (var i=0; i<len; i++) {
238 var item = results.rows.item(i);
244 // TODO error handling
250 // TODO pliterki, start of the word match
251 self.db.transaction(function(tx) {
252 sql_term = self.sqlSanitize(term); // this is still insane, % and _
253 tx.executeSql("SELECT * FROM book WHERE title LIKE '%"+sql_term+"%' ORDER BY sort_key LIMIT 10", [],
254 //tx.executeSql("SELECT * FROM book WHERE title REGEXP '.*"+sql_term+".*' ORDER BY sort_key", [],
255 function(tx, results) {
257 booksFound(tx, results);
258 // and proceed to tags
259 tx.executeSql("SELECT * FROM tag WHERE name LIKE '%"+sql_term+"%' ORDER BY sort_key LIMIT 10",
263 console.log('ERROR:search: '+err.code);
269 self.chainSqls = function(sqls, success, error) {
270 self.db.transaction(function(tx) {
271 var do_next = function() {
273 var sql = sqls.shift();
275 tx.executeSql(sql, [], do_next, error);
278 success && success();
286 self.update = function(data, success, error) {
287 var addBookSql = new Sql("\
288 INSERT OR REPLACE INTO book \
289 (id, title, html_file, html_file_size, parent, parent_number, sort_key, pretty_size, authors) \
291 ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')");
292 var addTagSql = new Sql("INSERT OR REPLACE INTO tag (id, category, name, sort_key, books) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')");
297 for (i in data.deleted.books) {
298 var book_id = data.deleted.books[i];
299 sqls.push("DELETE FROM book WHERE id=" + book_id);
300 FileRepo.deleteIfExists(book_id);
303 for (i in data.deleted.tags) {
304 var tag_id = data.deleted.tags[i];
305 sqls.push("DELETE FROM tag WHERE id=" + tag_id);
310 for (i in data.updated.books) {
311 var book = data.updated.books[i];
312 if (!book.html) book.html = {};
313 if (!book.html.url) book.html.url = '';
314 if (!book.html.size) book.html.size = '';
315 if (!book.parent) book.parent = '';
316 if (!book.parent_number) book.parent_number = '';
317 var pretty_size = prettySize(book.html.size);
318 sqls.push(addBookSql.prepare(
319 book.id, book.title, book.html.url, book.html.size,
320 book.parent, book.parent_number, book.sort_key, pretty_size, book.author
322 FileRepo.deleteIfExists(book.id);
325 for (i in data.updated.tags) {
326 var tag = data.updated.tags[i];
327 var category = categories[tag.category];
328 var books = tag.books.join(',');
329 sqls.push(addTagSql.prepare(tag.id, category, tag.name, tag.sort_key, books));
333 sqls.push("UPDATE state SET last_checked=" + data.time_checked);
335 self.chainSqls(sqls, success, error);
339 this.sync = function(success, error) {
340 self.withState(function(state) {
341 var url = WL_UPDATE.replace("SINCE", state.last_checked);
342 console.log('sync: ' + url);
343 var xhr = new XMLHttpRequest();
344 xhr.open("GET", url);
345 xhr.onload = function() {
346 console.log('sync: fetched by ajax: ' + url);
347 self.update(JSON.parse(xhr.responseText), success, error);
349 xhr.onerror = function(e) {
350 error && error("Błąd aktualizacji bazy danych." + e);
356 this.updateLocal = function() {
357 FileRepo.withLocal(function(local) {
358 self.db.transaction(function(tx) {
359 tx.executeSql("UPDATE book SET _local=0", [], function(tx, results) {
362 for (var i = 0; i < ll; i ++) {
363 ids.push(local[i].name);
366 tx.executeSql("UPDATE book SET _local=1 where id in ("+ids+")");
370 self.db.transaction(function(tx) {
371 tx.executeSql("UPDATE book SET _local=0");