assets/www/js/catalogue.js

   1 var DB_VER = '0.9.15';
   2
   3 var WL_INITIAL = WL + '/media/api/mobile/initial/initial.db';
   4 var WL_UPDATE = WL + '/api/changes/SINCE.json?book_fields=author,html,parent,parent_number,sort_key,title' +
   5                 '&tag_fields=books,category,name,sort_key' +
   6                 '&tag_categories=author,epoch,genre,kind';
   7
   8
   9
  10 var categories = {'author': 'autor',
  11               'epoch': 'epoka',
  12               'genre': 'gatunek',
  13               'kind': 'rodzaj',
  14               'theme': 'motyw'
  15               }
  16
  17 // FIXME: htmlescape strings!
  18
  19
  20 // for preparing sql statements
  21 // use like:
  22 //   var s = new Sql("INSERT ... '{0}', '{1}' ...";
  23 //   s.prepare("abc", ...)
  24 var Sql = function(scheme) {
  25         var self = this;
  26         self.text = scheme;
  27
  28         self.sql_escape = function(term) {
  29                 return term.toString().replace("'", "''");
  30         };
  31
  32         self.prepare = function() {
  33                 var args = arguments;
  34                 return self.text.replace(/{(\d+)}/g, function(match, number) {
  35                         return self.sql_escape(args[parseInt(number)]);
  36                 });
  37         }
  38 };
  39
  40
  41 var Catalogue = new function() {
  42         /* API for database */
  43
  44         var self = this;
  45         self.db = null;
  46
  47         this.init = function(success, error) {
  48                 console.log('Catalogue.init');
  49
  50                 self.updateDB(function() {
  51                         self.db = window.openDatabase("wolnelektury", "1.0", "WL Catalogue", 1);
  52                         if (self.db) {
  53                                 /*var regexp = {
  54                                                 onFunctionCall: function(val) {
  55                                                         var re = new RegExp(val.getString(0));
  56                                                                 if (val.getString(1).match(re))
  57                                                                         return 1;
  58                                                                 else
  59                                                                         return 0;
  60                                                 }
  61                                         };
  62                                 self.db.createFunction("REGEXP", 2, regexp);*/
  63
  64                                 success && success();
  65                         } else {
  66                                 error && error('Nie mogę otworzyć bazy danych: ' + err);
  67                         }
  68
  69                 }, function(err) {
  70                         error && error('Błąd migracji: ' + err);
  71                 });
  72         };
  73
  74         self.sqlSanitize = function(term) {
  75                 return term.toString().replace("'", "''");
  76         };
  77
  78
  79         /* check if DB needs updating and upload a fresh copy, if so */
  80         this.updateDB = function(success, error) {
  81                 var has_ver = window.localStorage.getItem('db_ver');
  82                 if (has_ver == DB_VER) {
  83                         console.log('db ok, skipping')
  84                         success && success();
  85                         return;
  86                 }
  87
  88                 var done = function() {
  89                         FileRepo.clear();
  90                         window.localStorage.setItem('db_ver', DB_VER);
  91                         console.log('db updated');
  92                         success && success();
  93                 };
  94
  95                 // db initialize
  96                 // this is Android-specific for now
  97                 var android = device.version.split('.')[0];
  98                 if (android > 1) {
  99                         self.upload_db_android(done, error);
 100                 } else {
 101                         error && error("Nieobsługiwana wersja systemu. Wymagany Android>=2.0.");
 102                 };
 103         };
 104
 105
 106         this.upload_db_android = function(success, error) {
 107                 // TODO: this should be downloaded from teh net, not stored in res
 108
 109                 console.log('upload db for Android 2.x+');
 110                 // upload databases description file
 111
 112                 var dbname = "wolnelektury";
 113                 var db = window.openDatabase(dbname, "1.0", "WL Catalogue", 500000);
 114                 if (db) {
 115                         console.log('db created successfully');
 116                         DBPut.fetch(WL_INITIAL, function(data) {
 117                                 console.log('db fetch successful');
 118                                 success && success();
 119                         }, function(data) {
 120                                 error && error('Błąd podczas pobierania bazy danych: ' + data);
 121                         });
 122                 } else {
 123                         error && error('Błąd podczas inicjowania bazy danych: ' + data);
 124                 }
 125         };
 126
 127
 128         this.withState = function(callback) {
 129                 self.db.transaction(function(tx) {
 130                         tx.executeSql("SELECT * FROM state", [],
 131                                 function(tx, results) {
 132                                         if (results.rows.length) {
 133                                                 callback(results.rows.item(0));
 134                                         }
 135                                         else {
 136                                                 callback({last_checked: 0});
 137                                         }
 138                                 });
 139                 });
 140         };
 141
 142
 143         this.withBook = function(id, callback, error) {
 144                 console.log('withBook '+id)
 145                 self.db.transaction(function(tx) {
 146                         tx.executeSql("SELECT * FROM book WHERE id="+id, [],
 147                                 function(tx, results) {
 148                                         if (results.rows.length) {
 149                                                 callback(results.rows.item(0));
 150                                         }
 151                                         else {
 152                                                 error && error();
 153                                         }
 154                                 });
 155                 });
 156         };
 157
 158         this.withBooks = function(ids, callback) {
 159                 console.log('withBooks ' + ids)
 160                 self.db.transaction(function(tx) {
 161                         tx.executeSql("SELECT * FROM book WHERE id IN ("+ids+") ORDER BY sort_key", [],
 162                                 function(tx, results) {
 163                                         var items = [];
 164                                         var count = results.rows.length;
 165                                         for (var i=0; i<count; ++i) {
 166                                                 items.push(results.rows.item(i));
 167                                         }
 168                                         callback(items);
 169                                 });
 170                 });
 171         };
 172
 173
 174         this.withChildren = function(id, callback) {
 175                 console.log('withChildren ' + id)
 176                 self.db.transaction(function(tx) {
 177                         tx.executeSql("SELECT * FROM book WHERE parent="+id+" ORDER BY parent_number, sort_key", [],
 178                                 function(tx, results) {
 179                                         var books = [];
 180                                         var count = results.rows.length;
 181                                         for (var i=0; i<count; ++i) {
 182                                                 books.push(results.rows.item(i));
 183                                         }
 184                                         callback(books);
 185                         });
 186                 });
 187         };
 188
 189         this.withTag = function(id, callback, error) {
 190                 console.log('withTag '+id)
 191                 self.db.transaction(function(tx) {
 192                         tx.executeSql("SELECT * FROM tag WHERE id="+id, [],
 193                                 function(tx, results) {
 194                                         if (results.rows.length) {
 195                                                 callback(results.rows.item(0));
 196                                         }
 197                                         else {
 198                                                 error && error();
 199                                         }
 200                                 });
 201                 });
 202         };
 203
 204         this.withCategory = function(category, callback) {
 205                 console.log('withCategory ' + category)
 206                 self.db.transaction(function(tx) {
 207                         tx.executeSql("SELECT * FROM tag WHERE category='"+category+"' ORDER BY sort_key", [],
 208                                 function(tx, results) {
 209                                         var items = [];
 210                                         var count = results.rows.length;
 211                                         for (var i=0; i<count; ++i)
 212                                                 items.push(results.rows.item(i));
 213                                         callback(items);
 214                                 });
 215                 });
 216         };
 217
 218
 219         /* takes a query, returns a list of {view,id,label} objects to a callback */
 220         this.withSearch = function(term, callback) {
 221                 console.log('searching...');
 222                 term =  term.replace(/^\s+|\s+$/g, '') ;
 223                 var found = [];
 224
 225                 function booksFound(tx, results) {
 226                         var len = results.rows.length;
 227                         console.log('found books: ' + len);
 228                         for (var i=0; i<len; i++) {
 229                                 var item = results.rows.item(i);
 230                                 found.push({
 231                                         view: "Book",
 232                                         item: item
 233                                 });
 234                         }
 235                 };
 236
 237                 function tagsFound(tx, results) {
 238                         var len = results.rows.length;
 239                         console.log('found tags: ' + len);
 240                         for (var i=0; i<len; i++) {
 241                                 var item = results.rows.item(i);
 242                                 found.push({
 243                                         view: "Tag",
 244                                         item: item
 245                                 });
 246                         }
 247                         // TODO error handling
 248                         callback(found);
 249                 };
 250
 251
 252                 // FIXME escaping
 253                 // TODO pliterki, start of the word match
 254                 self.db.transaction(function(tx) {
 255                         sql_term = self.sqlSanitize(term); // this is still insane, % and _
 256                         tx.executeSql("SELECT * FROM book WHERE title LIKE '%"+sql_term+"%' ORDER BY sort_key LIMIT 10", [],
 257                         //tx.executeSql("SELECT * FROM book WHERE title REGEXP '.*"+sql_term+".*' ORDER BY sort_key", [],
 258                                 function(tx, results) {
 259                                         // save the books
 260                                         booksFound(tx, results);
 261                                         // and proceed to tags
 262                                         tx.executeSql("SELECT * FROM tag WHERE name LIKE '%"+sql_term+"%' ORDER BY sort_key LIMIT 10",
 263                                                         [], tagsFound);
 264                                 },
 265                                 function(err) {
 266                                         console.log('ERROR:search: '+err.code);
 267                                         callback([]);
 268                                 });
 269                 });
 270         };
 271
 272         self.chainSqls = function(sqls, success, error) {
 273                 self.db.transaction(function(tx) {
 274                         var do_next = function() {
 275                                 if (sqls.length) {
 276                                         var sql = sqls.shift();
 277                                         console.log(sql);
 278                                         tx.executeSql(sql, [], do_next, error);
 279                                 }
 280                                 else {
 281                                         success && success();
 282                                 }
 283                         }
 284                         do_next();
 285                 });
 286         };
 287
 288
 289         self.update = function(data, success, error) {
 290                 var addBookSql = new Sql("\
 291                         INSERT OR REPLACE INTO book \
 292                                 (id, title, html_file,  html_file_size, parent, parent_number, sort_key, pretty_size, authors) \
 293                         VALUES \
 294                                 ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')");
 295                 var addTagSql = new Sql("INSERT OR REPLACE INTO tag (id, category, name, sort_key, books) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')");
 296
 297                 var sqls = [];
 298
 299                 if (data.deleted) {
 300                         for (i in data.deleted.books) {
 301                                 var book_id = data.deleted.books[i];
 302                                 sqls.push("DELETE FROM book WHERE id=" + book_id);
 303                                 FileRepo.deleteIfExists(book_id);
 304                         }
 305
 306                         for (i in data.deleted.tags) {
 307                                 var tag_id = data.deleted.tags[i];
 308                                 sqls.push("DELETE FROM tag WHERE id=" + tag_id);
 309                         }
 310                 }
 311
 312                 if (data.updated) {
 313                         for (i in data.updated.books) {
 314                                 var book = data.updated.books[i];
 315                                 if (!book.html) book.html = {};
 316                                 if (!book.html.url) book.html.url = '';
 317                                 if (!book.html.size) book.html.size = '';
 318                                 if (!book.parent) book.parent = '';
 319                                 if (!book.parent_number) book.parent_number = '';
 320                                 var pretty_size = prettySize(book.html.size);
 321                                 sqls.push(addBookSql.prepare(
 322                                         book.id, book.title, book.html.url, book.html.size,
 323                                         book.parent, book.parent_number, book.sort_key, pretty_size, book.author
 324                                 ));
 325                                 FileRepo.deleteIfExists(book.id);
 326                         }
 327
 328                         for (i in data.updated.tags) {
 329                                 var tag = data.updated.tags[i];
 330                                 var category = categories[tag.category];
 331                                 var books = tag.books.join(',');
 332                                 sqls.push(addTagSql.prepare(tag.id, category, tag.name, tag.sort_key, books));
 333                         }
 334                 }
 335
 336                 sqls.push("UPDATE state SET last_checked=" + data.time_checked);
 337
 338                 self.chainSqls(sqls, success, error);
 339         };
 340
 341
 342         this.sync = function(success, error) {
 343                 self.withState(function(state) {
 344                         var url = WL_UPDATE.replace("SINCE", state.last_checked);
 345                         console.log('sync: ' + url);
 346                         var xhr = new XMLHttpRequest();
 347                         xhr.open("GET", url);
 348                         xhr.onload = function() {
 349                                 console.log('sync: fetched by ajax: ' + url);
 350                                 self.update(JSON.parse(xhr.responseText), success, error);
 351                         }
 352                         xhr.onerror = function(e) {
 353                                 error && error("Błąd aktualizacji bazy danych." + e);
 354                         }
 355                         xhr.send();
 356                 });
 357         };
 358
 359         this.updateLocal = function() {
 360                 FileRepo.withLocal(function(local) {
 361                         self.db.transaction(function(tx) {
 362                                 tx.executeSql("UPDATE book SET _local=0", [], function(tx, results) {
 363                                         ll = local.length;
 364                                         var ids = [];
 365                                         for (var i = 0; i < ll; i ++) {
 366                                                 ids.push(local[i].name);
 367                                         }
 368                                         ids = ids.join(',');
 369                                         tx.executeSql("UPDATE book SET _local=1 where id in ("+ids+")");
 370                                 });
 371                         });
 372                 }, function() {
 373                         self.db.transaction(function(tx) {
 374                                 tx.executeSql("UPDATE book SET _local=0");
 375                         });
 376                 });
 377         };
 378 }