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.17';
   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                 debug('Catalogue.init');
  54
  55                 self.updateDB(function() {
  56                         if (!self.db)
  57                                 self.db = window.openDatabase("wolnelektury", "1.0", "WL Catalogue", 1000000);
  58                         if (self.db) {
  59                                 /*var regexp = {
  60                                                 onFunctionCall: function(val) {
  61                                                         var re = new RegExp(val.getString(0));
  62                                                                 if (val.getString(1).match(re))
  63                                                                         return 1;
  64                                                                 else
  65                                                                         return 0;
  66                                                 }
  67                                         };
  68                                 self.db.createFunction("REGEXP", 2, regexp);*/
  69
  70                                 success && success();
  71                         } else {
  72                                 error && error('Nie mogę otworzyć bazy danych: ' + err);
  73                         }
  74
  75                 }, function(err) {
  76                         error && error('Błąd migracji: ' + err);
  77                 });
  78         };
  79
  80         self.sqlSanitize = function(term) {
  81                 return term.toString().replace("'", "''");
  82         };
  83
  84
  85         /* check if DB needs updating and upload a fresh copy, if so */
  86         this.updateDB = function(success, error) {
  87                 var has_ver = window.localStorage.getItem('db_ver');
  88                 if (has_ver == DB_VER) {
  89                         debug('db ok, skipping')
  90                         success && success();
  91                         return;
  92                 }
  93
  94                 var done = function() {
  95                         FileRepo.clear();
  96                         window.localStorage.setItem('db_ver', DB_VER);
  97                         debug('db updated');
  98                         success && success();
  99                 };
 100
 101                 // db initialize
 102                 // this is Android-specific for now
 103                 self.createdb(done, error);
 104         };
 105
 106
 107         this.createdb = function(success, error) {
 108                 debug('create db');
 109
 110                 var dbname = "wolnelektury";
 111                 var db = window.openDatabase(dbname, "1.0", "WL Catalogue", 1000000);
 112                 if (db) {
 113                         debug('db created successfully');
 114                         self.db = db;
 115                         var sqls = [];
 116                         sqls.push('CREATE TABLE IF NOT EXISTS book (\
 117                                           id INTEGER PRIMARY KEY,\
 118                                           title VARCHAR,\
 119                                           html_file VARCHAR,\
 120                                           html_file_size INTEGER,\
 121                                           parent INTEGER,\
 122                                           parent_number INTEGER,\
 123                                           sort_key VARCHAR,\
 124                                           pretty_size VARCHAR,\
 125                                           authors VARCHAR,\
 126                                           _local BOOLEAN\
 127                                           );');
 128                         sqls.push('CREATE INDEX IF NOT EXISTS book_title_index ON book (title);');
 129                         sqls.push('CREATE INDEX IF NOT EXISTS book_sort_key_index ON book (sort_key);');
 130                         sqls.push('CREATE INDEX IF NOT EXISTS book_parent_index ON book (parent);');
 131                         sqls.push('CREATE TABLE IF NOT EXISTS tag (\
 132                                           id INTEGER PRIMARY KEY,\
 133                                           name VARCHAR,\
 134                                           category VARCHAR,\
 135                                           sort_key VARCHAR,\
 136                                           books VARCHAR\
 137                                           );');
 138                         sqls.push('CREATE INDEX IF NOT EXISTS tag_name_index ON tag (name);');
 139                         sqls.push('CREATE INDEX IF NOT EXISTS tag_category_index ON tag (category);');
 140                         sqls.push('CREATE INDEX IF NOT EXISTS tag_sort_key_index ON tag (name);');
 141                         sqls.push('CREATE TABLE IF NOT EXISTS state (last_checked INTEGER);');
 142                         sqls.push('DELETE FROM state;');
 143                         sqls.push('INSERT INTO state (last_checked) VALUES(0);');
 144                         self.chainSqls(sqls, success, error);
 145                         /*DBPut.fetch(WL_INITIAL, function(data) {
 146                                 debug('db fetch successful');
 147                                 success && success();
 148                         }, function(data) {
 149                                 error && error('Błąd podczas pobierania bazy danych: ' + data);
 150                         });*/
 151                 } else {
 152                         error && error('Błąd podczas inicjowania bazy danych: ' + data);
 153                 }
 154         };
 155
 156
 157         this.withState = function(callback) {
 158                 self.db.transaction(function(tx) {
 159                         tx.executeSql("SELECT * FROM state", [],
 160                                 function(tx, results) {
 161                                         if (results.rows.length) {
 162                                                 callback(results.rows.item(0));
 163                                         }
 164                                         else {
 165                                                 callback({last_checked: 0});
 166                                         }
 167                                 });
 168                 });
 169         };
 170
 171
 172         this.withBook = function(id, callback, error) {
 173                 debug('withBook '+id)
 174                 self.db.transaction(function(tx) {
 175                         tx.executeSql("SELECT * FROM book WHERE id="+id, [],
 176                                 function(tx, results) {
 177                                         if (results.rows.length) {
 178                                                 callback(results.rows.item(0));
 179                                         }
 180                                         else {
 181                                                 error && error();
 182                                         }
 183                                 });
 184                 });
 185         };
 186
 187         this.withBooks = function(ids, callback) {
 188                 debug('withBooks ' + ids)
 189                 self.db.transaction(function(tx) {
 190                         tx.executeSql("SELECT * FROM book WHERE id IN ("+ids+") ORDER BY sort_key", [],
 191                                 function(tx, results) {
 192                                         var items = [];
 193                                         var count = results.rows.length;
 194                                         for (var i=0; i<count; ++i) {
 195                                                 items.push(results.rows.item(i));
 196                                         }
 197                                         callback(items);
 198                                 });
 199                 });
 200         };
 201
 202
 203         this.withChildren = function(id, callback) {
 204                 debug('withChildren ' + id)
 205                 self.db.transaction(function(tx) {
 206                         tx.executeSql("SELECT * FROM book WHERE parent="+id+" ORDER BY parent_number, sort_key", [],
 207                                 function(tx, results) {
 208                                         var books = [];
 209                                         var count = results.rows.length;
 210                                         for (var i=0; i<count; ++i) {
 211                                                 books.push(results.rows.item(i));
 212                                         }
 213                                         callback(books);
 214                         });
 215                 });
 216         };
 217
 218         this.withTag = function(id, callback, error) {
 219                 debug('withTag '+id)
 220                 self.db.transaction(function(tx) {
 221                         tx.executeSql("SELECT * FROM tag WHERE id="+id, [],
 222                                 function(tx, results) {
 223                                         if (results.rows.length) {
 224                                                 callback(results.rows.item(0));
 225                                         }
 226                                         else {
 227                                                 error && error();
 228                                         }
 229                                 });
 230                 });
 231         };
 232
 233         this.withCategory = function(category, callback) {
 234                 debug('withCategory ' + category)
 235                 self.db.transaction(function(tx) {
 236                         tx.executeSql("SELECT * FROM tag WHERE category='"+category+"' ORDER BY sort_key", [],
 237                                 function(tx, results) {
 238                                         var items = [];
 239                                         var count = results.rows.length;
 240                                         for (var i=0; i<count; ++i)
 241                                                 items.push(results.rows.item(i));
 242                                         callback(items);
 243                                 });
 244                 });
 245         };
 246
 247
 248         /* takes a query, returns a list of {view,id,label} objects to a callback */
 249         this.withSearch = function(term, callback) {
 250                 debug('searching...');
 251                 term =  term.replace(/^\s+|\s+$/g, '') ;
 252                 var found = [];
 253
 254                 function booksFound(tx, results) {
 255                         var len = results.rows.length;
 256                         debug('found books: ' + len);
 257                         for (var i=0; i<len; i++) {
 258                                 var item = results.rows.item(i);
 259                                 found.push({
 260                                         view: "Book",
 261                                         item: item
 262                                 });
 263                         }
 264                 };
 265
 266                 function tagsFound(tx, results) {
 267                         var len = results.rows.length;
 268                         debug('found tags: ' + len);
 269                         for (var i=0; i<len; i++) {
 270                                 var item = results.rows.item(i);
 271                                 found.push({
 272                                         view: "Tag",
 273                                         item: item
 274                                 });
 275                         }
 276                         // TODO error handling
 277                         callback(found);
 278                 };
 279
 280
 281                 // FIXME escaping
 282                 // TODO pliterki, start of the word match
 283                 self.db.transaction(function(tx) {
 284                         sql_term = self.sqlSanitize(term); // this is still insane, % and _
 285                         tx.executeSql("SELECT * FROM book WHERE title LIKE '%"+sql_term+"%' ORDER BY sort_key LIMIT 10", [],
 286                         //tx.executeSql("SELECT * FROM book WHERE title REGEXP '.*"+sql_term+".*' ORDER BY sort_key", [],
 287                                 function(tx, results) {
 288                                         // save the books
 289                                         booksFound(tx, results);
 290                                         // and proceed to tags
 291                                         tx.executeSql("SELECT * FROM tag WHERE name LIKE '%"+sql_term+"%' ORDER BY sort_key LIMIT 10",
 292                                                         [], tagsFound);
 293                                 },
 294                                 function(err) {
 295                                         debug('ERROR:search: '+err.code);
 296                                         callback([]);
 297                                 });
 298                 });
 299         };
 300
 301         self.chainSqls = function(sqls, success, error) {
 302                 self.db.transaction(function(tx) {
 303                         var do_next = function() {
 304                                 if (sqls.length) {
 305                                         var sql = sqls.shift();
 306                                         debug(sql);
 307                                         tx.executeSql(sql, [], do_next, error);
 308                                 }
 309                                 else {
 310                                         success && success();
 311                                 }
 312                         }
 313                         do_next();
 314                 });
 315         };
 316
 317
 318         self.update = function(data, success, error) {
 319                 var addBookSql = new Sql("\
 320                         INSERT OR REPLACE INTO book \
 321                                 (id, title, html_file,  html_file_size, parent, parent_number, sort_key, pretty_size, authors) \
 322                         VALUES \
 323                                 ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')");
 324                 var addTagSql = new Sql("INSERT OR REPLACE INTO tag (id, category, name, sort_key, books) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')");
 325
 326                 var sqls = [];
 327
 328                 if (data.deleted) {
 329                         for (i in data.deleted.books) {
 330                                 var book_id = data.deleted.books[i];
 331                                 sqls.push("DELETE FROM book WHERE id=" + book_id);
 332                                 FileRepo.deleteIfExists(book_id);
 333                         }
 334
 335                         for (i in data.deleted.tags) {
 336                                 var tag_id = data.deleted.tags[i];
 337                                 sqls.push("DELETE FROM tag WHERE id=" + tag_id);
 338                         }
 339                 }
 340
 341                 if (data.updated) {
 342                         for (i in data.updated.books) {
 343                                 var book = data.updated.books[i];
 344                                 if (!book.html) book.html = {};
 345                                 if (!book.html.url) book.html.url = '';
 346                                 if (!book.html.size) book.html.size = '';
 347                                 if (!book.parent) book.parent = '';
 348                                 if (!book.parent_number) book.parent_number = '';
 349                                 var pretty_size = prettySize(book.html.size);
 350                                 sqls.push(addBookSql.prepare(
 351                                         book.id, book.title, book.html.url, book.html.size,
 352                                         book.parent, book.parent_number, book.sort_key, pretty_size, book.author
 353                                 ));
 354                                 FileRepo.deleteIfExists(book.id);
 355                         }
 356
 357                         for (i in data.updated.tags) {
 358                                 var tag = data.updated.tags[i];
 359                                 var category = categories[tag.category];
 360                                 var books = tag.books.join(',');
 361                                 sqls.push(addTagSql.prepare(tag.id, category, tag.name, tag.sort_key, books));
 362                         }
 363                 }
 364
 365                 sqls.push("UPDATE state SET last_checked=" + data.time_checked);
 366
 367                 self.chainSqls(sqls, success, error);
 368         };
 369
 370
 371         this.sync = function(success, error) {
 372                 self.withState(function(state) {
 373                         var url = WL_UPDATE.replace("SINCE", state.last_checked);
 374                         debug('sync: ' + url);
 375                         var xhr = new XMLHttpRequest();
 376                         xhr.open("GET", url);
 377                         xhr.onload = function() {
 378                                 debug('sync: fetched by ajax: ' + url);
 379                                 self.update(JSON.parse(xhr.responseText), success, error);
 380                         }
 381                         xhr.onerror = function(e) {
 382                                 error && error("Błąd aktualizacji bazy danych." + e);
 383                         }
 384                         xhr.send();
 385                 });
 386                 success && success();
 387         };
 388
 389         this.updateLocal = function() {
 390                 FileRepo.withLocal(function(local) {
 391                         self.db.transaction(function(tx) {
 392                                 tx.executeSql("UPDATE book SET _local=0", [], function(tx, results) {
 393                                         ll = local.length;
 394                                         var ids = [];
 395                                         for (var i = 0; i < ll; i ++) {
 396                                                 ids.push(local[i].name);
 397                                         }
 398                                         ids = ids.join(',');
 399                                         tx.executeSql("UPDATE book SET _local=1 where id in ("+ids+")");
 400                                 });
 401                         });
 402                 }, function() {
 403                         self.db.transaction(function(tx) {
 404                                 tx.executeSql("UPDATE book SET _local=0");
 405                         });
 406                 });
 407         };
 408 }