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