assets/www/js/catalogue.js

   1 /*
   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.
   4  */
   5
   6 var DB_VER = '0.9.15';
   7
   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';
  12
  13
  14
  15 var categories = {'author': 'autor',
  16               'epoch': 'epoka',
  17               'genre': 'gatunek',
  18               'kind': 'rodzaj',
  19               'theme': 'motyw'
  20               }
  21
  22 // FIXME: htmlescape strings!
  23
  24
  25 // for preparing sql statements
  26 // use like:
  27 //   var s = new Sql("INSERT ... '{0}', '{1}' ...";
  28 //   s.prepare("abc", ...)
  29 var Sql = function(scheme) {
  30         var self = this;
  31         self.text = scheme;
  32
  33         self.sql_escape = function(term) {
  34                 return term.toString().replace("'", "''");
  35         };
  36
  37         self.prepare = function() {
  38                 var args = arguments;
  39                 return self.text.replace(/{(\d+)}/g, function(match, number) {
  40                         return self.sql_escape(args[parseInt(number)]);
  41                 });
  42         }
  43 };
  44
  45
  46 var Catalogue = new function() {
  47         /* API for database */
  48
  49         var self = this;
  50         self.db = null;
  51
  52         this.init = function(success, error) {
  53                 console.log('Catalogue.init');
  54
  55                 self.updateDB(function() {
  56                         self.db = window.openDatabase("wolnelektury", "1.0", "WL Catalogue", 1);
  57                         if (self.db) {
  58                                 /*var regexp = {
  59                                                 onFunctionCall: function(val) {
  60                                                         var re = new RegExp(val.getString(0));
  61                                                                 if (val.getString(1).match(re))
  62                                                                         return 1;
  63                                                                 else
  64                                                                         return 0;
  65                                                 }
  66                                         };
  67                                 self.db.createFunction("REGEXP", 2, regexp);*/
  68
  69                                 success && success();
  70                         } else {
  71                                 error && error('Nie mogę otworzyć bazy danych: ' + err);
  72                         }
  73
  74                 }, function(err) {
  75                         error && error('Błąd migracji: ' + err);
  76                 });
  77         };
  78
  79         self.sqlSanitize = function(term) {
  80                 return term.toString().replace("'", "''");
  81         };
  82
  83
  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')
  89                         success && success();
  90                         return;
  91                 }
  92
  93                 var done = function() {
  94                         FileRepo.clear();
  95                         window.localStorage.setItem('db_ver', DB_VER);
  96                         console.log('db updated');
  97                         success && success();
  98                 };
  99
 100                 // db initialize
 101                 // this is Android-specific for now
 102                 self.upload_db_android(done, error);
 103         };
 104
 105
 106         this.upload_db_android = function(success, error) {
 107                 console.log('upload db for Android 2.x+');
 108
 109                 var dbname = "wolnelektury";
 110                 var db = window.openDatabase(dbname, "1.0", "WL Catalogue", 500000);
 111                 if (db) {
 112                         console.log('db created successfully');
 113                         DBPut.fetch(WL_INITIAL, function(data) {
 114                                 console.log('db fetch successful');
 115                                 success && success();
 116                         }, function(data) {
 117                                 error && error('Błąd podczas pobierania bazy danych: ' + data);
 118                         });
 119                 } else {
 120                         error && error('Błąd podczas inicjowania bazy danych: ' + data);
 121                 }
 122         };
 123
 124
 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));
 131                                         }
 132                                         else {
 133                                                 callback({last_checked: 0});
 134                                         }
 135                                 });
 136                 });
 137         };
 138
 139
 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));
 147                                         }
 148                                         else {
 149                                                 error && error();
 150                                         }
 151                                 });
 152                 });
 153         };
 154
 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) {
 160                                         var items = [];
 161                                         var count = results.rows.length;
 162                                         for (var i=0; i<count; ++i) {
 163                                                 items.push(results.rows.item(i));
 164                                         }
 165                                         callback(items);
 166                                 });
 167                 });
 168         };
 169
 170
 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) {
 176                                         var books = [];
 177                                         var count = results.rows.length;
 178                                         for (var i=0; i<count; ++i) {
 179                                                 books.push(results.rows.item(i));
 180                                         }
 181                                         callback(books);
 182                         });
 183                 });
 184         };
 185
 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));
 193                                         }
 194                                         else {
 195                                                 error && error();
 196                                         }
 197                                 });
 198                 });
 199         };
 200
 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) {
 206                                         var items = [];
 207                                         var count = results.rows.length;
 208                                         for (var i=0; i<count; ++i)
 209                                                 items.push(results.rows.item(i));
 210                                         callback(items);
 211                                 });
 212                 });
 213         };
 214
 215
 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, '') ;
 220                 var found = [];
 221
 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);
 227                                 found.push({
 228                                         view: "Book",
 229                                         item: item
 230                                 });
 231                         }
 232                 };
 233
 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);
 239                                 found.push({
 240                                         view: "Tag",
 241                                         item: item
 242                                 });
 243                         }
 244                         // TODO error handling
 245                         callback(found);
 246                 };
 247
 248
 249                 // FIXME escaping
 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) {
 256                                         // save the books
 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",
 260                                                         [], tagsFound);
 261                                 },
 262                                 function(err) {
 263                                         console.log('ERROR:search: '+err.code);
 264                                         callback([]);
 265                                 });
 266                 });
 267         };
 268
 269         self.chainSqls = function(sqls, success, error) {
 270                 self.db.transaction(function(tx) {
 271                         var do_next = function() {
 272                                 if (sqls.length) {
 273                                         var sql = sqls.shift();
 274                                         console.log(sql);
 275                                         tx.executeSql(sql, [], do_next, error);
 276                                 }
 277                                 else {
 278                                         success && success();
 279                                 }
 280                         }
 281                         do_next();
 282                 });
 283         };
 284
 285
 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) \
 290                         VALUES \
 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}')");
 293
 294                 var sqls = [];
 295
 296                 if (data.deleted) {
 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);
 301                         }
 302
 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);
 306                         }
 307                 }
 308
 309                 if (data.updated) {
 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
 321                                 ));
 322                                 FileRepo.deleteIfExists(book.id);
 323                         }
 324
 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));
 330                         }
 331                 }
 332
 333                 sqls.push("UPDATE state SET last_checked=" + data.time_checked);
 334
 335                 self.chainSqls(sqls, success, error);
 336         };
 337
 338
 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);
 348                         }
 349                         xhr.onerror = function(e) {
 350                                 error && error("Błąd aktualizacji bazy danych." + e);
 351                         }
 352                         xhr.send();
 353                 });
 354         };
 355
 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) {
 360                                         ll = local.length;
 361                                         var ids = [];
 362                                         for (var i = 0; i < ll; i ++) {
 363                                                 ids.push(local[i].name);
 364                                         }
 365                                         ids = ids.join(',');
 366                                         tx.executeSql("UPDATE book SET _local=1 where id in ("+ids+")");
 367                                 });
 368                         });
 369                 }, function() {
 370                         self.db.transaction(function(tx) {
 371                                 tx.executeSql("UPDATE book SET _local=0");
 372                         });
 373                 });
 374         };
 375 }