1
.pragma library // I hope this will prevent the waste of memory.
2
.import QtQuick.LocalStorage 2.0 as SQL
4
/* For internal usage in module.
6
var gDbCache = undefined
7
function openStdDataBase() {
8
if (gDbCache === undefined) {
9
gDbCache = SQL.LocalStorage.openDatabaseSync("Shorts", "1.1", "Shorts DB", 1000000)
11
// We can check table existance only once.
12
gDbCache.transaction(function(tx) {
13
checkTableExists(tx/*, opts*/);
20
function checkTableExists(transaction /* and additional string keys */) {
21
transaction.executeSql('PRAGMA foreign_keys = ON;') // enable foreign key support
22
transaction.executeSql("CREATE TABLE IF NOT EXISTS feed (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,source TEXT NULL,title TEXT NULL,link TEXT NULL, description TEXT NULL, status char(1) NULL DEFAULT '0', pubdate INTEGER NULL,image TEXT NULL, count INTEGER NULL DEFAULT 0);")
23
transaction.executeSql("CREATE TABLE IF NOT EXISTS tag (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL UNIQUE );")
24
transaction.executeSql("CREATE TABLE IF NOT EXISTS feed_tag (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,feed_id INTEGER NULL,tag_id INTEGER NULL,FOREIGN KEY(feed_id) REFERENCES feed(id) on delete cascade);")
25
transaction.executeSql("CREATE TABLE IF NOT EXISTS article ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, title TEXT NULL, content TEXT NULL, link TEXT NULL, description TEXT NULL, pubdate INTEGER NULL, status char(1) NULL DEFAULT '0', favourite char(1) NULL DEFAULT '0', image TEXT NULL, guid TEXT NULL, feed_id INTEGER NULL,count INTEGER NULL DEFAULT 0, media_groups TEXT NULL, author TEXT NULL);")
26
transaction.executeSql("CREATE TABLE IF NOT EXISTS settings ( id INTEGER, current_database_version TEXT NULL, database_last_updated TEXT NULL, view_mode char(1) NULL DEFAULT '0', update_interval INTEGER NULL DEFAULT 0, network_mode char(1) NULL DEFAULT '0');")
29
function adjustDb(dbParams) {
31
var db = openStdDataBase()
34
// Update scheme mechanism - step by step updating DB to the latest version.
35
// WARNING: Don't add 'break' statement.
36
switch (dbParams.oldDbVersion) {
38
// Add new column - author.
39
db.transaction(function(tx) {
40
dbResult = tx.executeSql("alter table article add author text")
41
console.log("Database updated: ", JSON.stringify(dbResult))
43
dbParams.newDbVersion = 1.1
45
dbParams.newDbVersion = 1.2
49
db.transaction(function(tx) {
50
dbResult = tx.executeSql("SELECT count(*) AS tagCount FROM feed_tag")
51
tagCount = dbResult.rows.item(0).tagCount
56
addFeed("Developer" , "http://developer.ubuntu.com/feed/")
57
addFeed("Design" , "http://design.canonical.com/feed/")
61
addFeed("Voices" , "http://voices.canonical.com/feed/atom/")
62
addFeed("Insights" , "http://insights.ubuntu.com/feed/")
63
addFeed("Blog" , "http://blog.canonical.com/feed/")
68
// MainView must refresh articles.
69
dbParams.isRefreshRequired = true
74
* include select, insert, update and delete operations
79
var db = openStdDataBase()
82
db.transaction(function(tx) {
83
dbResult = tx.executeSql("SELECT * FROM feed")
84
console.log("feed SELECTED: ", dbResult.rows.length)
86
return dbResult; // I suggest that return the whole result in order to know if error occurs
90
function addFeed(title, source) // from user input
93
var db = openStdDataBase()
94
db.transaction(function (tx) {
97
dbResult = tx.executeSql("SELECT 1 FROM feed WHERE source=?", [source])
98
if (dbResult.rows.length > 0) {
99
console.log("Database, addFeed: already exist feed with source: ", source, "ID", dbResult.rows.item(0).id)
100
dbResult = {"error": true, "exist": true}
104
dbResult = tx.executeSql('INSERT INTO feed (title, source) VALUES(?, ?)',
106
console.log("feed INSERT ID: ", dbResult.insertId)
108
dbResult.feedId = tx.executeSql("SELECT * FROM feed WHERE source=?", [source]).rows.item(0).id
109
console.log("dbResult.feedId", dbResult.feedId)
115
/* Update feed status.
116
* 0 - default, 1 - good, 2 - bad url.
118
function setFeedStatus(id, status) // from user input
120
var db = openStdDataBase()
122
db.transaction(function (tx) {
123
dbResult = tx.executeSql('UPDATE feed SET status=? WHERE id=?',
125
console.log("feed setFeedStatus, AFFECTED ROWS: ", dbResult.rowsAffected)
131
function updateFeedByUser(id, title, source) // from user input
133
var db = openStdDataBase()
135
db.transaction(function (tx) {
136
dbResult = tx.executeSql('UPDATE feed SET title=?, source=? WHERE id=?',
138
console.log("feed updateFeedByUser, AFFECTED ROWS: ", dbResult.rowsAffected)
143
function updateFeedByXml(id, link, description, title) // from xml file
145
var db = openStdDataBase()
147
db.transaction(function (tx) {
148
dbResult = tx.executeSql('UPDATE feed SET link=?, description=?, title=? WHERE id=?',
149
[link, description, title, id])
150
//console.log("feed updateFeedByXml, AFFECTED ROWS: ", dbResult.rowsAffected)
156
function updateFeedImage(id, image) // offline image path
158
var db = openStdDataBase()
160
db.transaction(function (tx) {
161
dbResult = tx.executeSql('UPDATE feed SET image=? WHERE id=?',
163
console.log("feed UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)
168
function updateFeedCount(id, count) //
170
var db = openStdDataBase()
172
db.transaction(function (tx) {
173
dbResult = tx.executeSql('UPDATE feed SET count=? WHERE id=?',
175
console.log("feed UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)
181
function deleteFeed(id)
183
var db = openStdDataBase()
185
db.transaction(function (tx) {
186
dbResult = tx.executeSql('delete from feed where id=?', [id])
187
console.log("feed delete, AFFECTED ROWS: ", dbResult.rowsAffected)
192
function deleteFeedByTagId(tagId)
194
var db = openStdDataBase()
196
db.transaction(function (tx) {
197
dbResult = tx.executeSql('delete from feed where exists (select 1 from feed_tag where feed_tag.feed_id = feed.id and feed_tag.tag_id = ?)',
199
console.log("feed delete by tag id, AFFECTED ROWS: ", dbResult.rowsAffected)
204
// select feeds without of topic (tag).
205
function loadFeedsWithoutTopic()
207
var db = openStdDataBase()
210
db.transaction(function(tx) {
211
dbResult = tx.executeSql("SELECT * FROM feed WHERE id NOT IN (SELECT feed_id FROM feed_tag)")
212
console.log("loadFeedsWithoutTopic SELECTED: ", dbResult.rows.length)
214
return dbResult; // I suggest that return the whole result in order to know if error occurs
217
/* article operations
218
* include select, insert, update and delete operations
223
function loadArticles(params) // params = {"isAll": true/false, "feedId": id | "tagId" : id}
225
var db = openStdDataBase()
228
console.log("loadArticles", JSON.stringify(params))
230
db.transaction(function(tx) {
231
if (params == undefined || params.isAll) // miss params
232
dbResult = tx.executeSql('SELECT article.*, feed.title as feed_name FROM article inner join feed on article.feed_id = feed.id ORDER BY article.pubdate DESC')
233
else if (params.feedId)
234
dbResult = tx.executeSql('SELECT article.*, feed.title as feed_name FROM article inner join feed on article.feed_id = feed.id WHERE article.feed_id = ? ORDER BY article.pubdate DESC', [params.feedId])
235
else if (params.tagId)
236
dbResult = tx.executeSql('SELECT article.*, feed.title as feed_name FROM article INNER JOIN feed on article.feed_id = feed.id INNER JOIN feed_tag on feed_tag.feed_id = feed.id WHERE tag_id = ? ORDER BY article.pubdate DESC', [params.tagId])
237
//dbResult = tx.executeSql('SELECT article.*, feed.title as feed_name FROM article inner join feed on article.feed_id = feed.id WHERE article.feed_id IN (SELECT feed_id FROM feed_tag WHERE tag_id = ?) ORDER BY article.pubdate DESC', [params.tagId])
242
// load all favourite articles
243
function loadFavouriteArticles()
245
var db = openStdDataBase()
248
db.transaction(function(tx) {
249
dbResult = tx.executeSql(' select article.*, \
250
feed.title as feed_name, \
252
from article inner join feed on article.feed_id = feed.id \
253
join feed_tag on feed_tag.feed_id = feed.id \
254
where article.favourite = "1" order by article.pubdate desc')
256
//console.log("loadFavouriteArticles", dbResult.rows.length)
257
//console.assert(dbResult.rows.length !== 0, "ERROR: There are no saved articles")
262
function loadTagHighlights(size) {
263
var db = openStdDataBase()
266
db.transaction(function(tx) {
267
dbResult = tx.executeSql( 'select a.id, f.id as feed_id, ft.tag_id \
269
inner join feed f on f.id = a.feed_id \
270
inner join feed_tag ft on ft.feed_id = f.id \
271
order by ft.tag_id, a.pubdate desc' )
277
for (var i = 0; i < dbResult.rows.length; i++) {
278
var c = dbResult.rows.item(i).tag_id
286
idArray.push(dbResult.rows.item(i).id)
291
var param = idArray.length ? idArray.join() : "-1" // Empty array guard.
292
dbResult = tx.executeSql('select a.*, f.id as feed_id, f.title as feed_name, t.id as tag_id, t.name as tag_name \
294
inner join feed f on a.feed_id = f.id \
295
inner join feed_tag ft on ft.feed_id = f.id \
296
inner join tag t on t.id = ft.tag_id
298
order by t.id, a.pubdate desc'.arg(param)) // Don't know why, but it doesn't work with commas in param
300
//.console.log(idArray.length, idArray)
302
console.log("loadTagHighlights", dbResult.rows.length)
307
this function is for avoiding hard drive performance issue,
308
pass model (plain JS array) and feed id as parameters to this function,
309
it will automaticly insert all the articles into database
310
add third pamams which is for restoring articles' properties
312
function addArticles(model, feed_id, restoreArray)
316
var db = openStdDataBase()
317
db.transaction(function (tx) {
320
for (var i = 0; i < model.length; i++) {
323
var title = article.title ? article.title : ""
324
var guid = article.guid ? article.guid : Qt.md5(title)
325
var link = article.link ? article.link : ""
326
var pubDate = article.pubDate ? article.pubDate : ""
327
var description = article.description ? article.description : ""
328
var content = article.content ? article.content : ""
329
var image = article.image ? article.image : ""
330
var media_groups = article.media_groups ? JSON.stringify(article.media_groups) : ""
331
var author = article.author ? JSON.stringify(article.author) : ""
335
dbResult = tx.executeSql("SELECT 1 FROM article WHERE guid=? AND feed_id=?", [guid, feed_id])
336
if (dbResult.rows.length > 0) {
337
// console.log("Database, add article: already exist article with guid: ", guid)
340
dbResult = tx.executeSql('INSERT INTO article (title, content, link, description, pubdate, guid, feed_id, image, media_groups, author) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
341
[title, content, link, description, pubDate, guid, feed_id, image, media_groups, author])
344
//console.time("restoreArrayCycle")
346
var BASE_HEURISTIC_CHUNK = 512
347
var arrayLength = restoreArray.length
349
var queryGetFunction = function(paramSize) {
350
var baseQuery = 'UPDATE article SET status="1" WHERE guid IN ('
351
for (var i = 0; i < paramSize; i++)
353
baseQuery = baseQuery.substring(0, baseQuery.length - 1) + ') AND status="0"'
357
var chunkOfData = restoreArray.length > BASE_HEURISTIC_CHUNK ? BASE_HEURISTIC_CHUNK : restoreArray.length
358
var fullQuery = queryGetFunction(chunkOfData)
360
for (var j = 0; j < restoreArray.length; j += chunkOfData) {
362
var limit = Math.min(chunkOfData, restoreArray.length - j)
363
var queryToUse = limit == chunkOfData ? fullQuery : queryGetFunction(limit)
366
for (var k = 0; k < limit; k++)
367
queryData.push(restoreArray[j + k].guid)
369
dbResult = tx.executeSql(queryToUse, queryData)
370
// console.log("CONSUMED", limit, "Rows affected:", dbResult.rowsAffected)
372
} // if restore array
373
//console.timeEnd("restoreArrayCycle")
378
function addArticlesEx(entries, feedId)
382
var db = openStdDataBase()
383
db.transaction(function (tx) {
385
// 1. Preload details.
386
//.console.time("Preload")
387
var articlePropertiesDb = dbResult = tx.executeSql("select guid, status \
389
where feed_id = ?", [feedId]) // and status = '1'", [feedId])
390
var feedArticles = []
391
for (var j = 0; j < articlePropertiesDb.rows.length; j++) {
392
var itm = articlePropertiesDb.rows.item(j)
393
feedArticles.push({ "guid" : itm.guid, "status" : itm.status })
395
console.log("feedArticles.length", feedArticles.length)
396
//.console.timeEnd("Preload")
399
//.console.time("DeleteOld")
400
tx.executeSql("delete from article where feed_id = ? and favourite = '0'", [feedId])
401
//.console.timeEnd("DeleteOld")
403
// 3. Insert new objects.
404
//.console.time("InsertArticle")
406
for (var i = 0; i < entries.length; i++) {
409
var title = e.title ? e.title : emptyStr
410
var content = e.content ? e.content : emptyStr
411
var link = e.link ? e.link : emptyStr
412
var author = e.author ? JSON.stringify(e.author) : emptyStr
413
var description = e.description ? e.description : emptyStr
414
var pubDate = e.pubDate ? e.pubDate : 0
415
var guid = e.guid ? e.guid : Qt.md5(title)
416
var image = e.image ? e.image : emptyStr
417
var media_groups = e.media_groups ? JSON.stringify(e.media_groups) : emptyStr
420
//if (feedArticles.some(function(v) { return v.guid == guid } ))
423
dbResult = tx.executeSql('insert into article (title, content, link, description, pubdate, guid, feed_id, image, media_groups, author) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
424
[title, content, link, description, pubDate, guid, feedId, image, media_groups, author])
426
//.console.timeEnd("InsertArticle")
428
// 4. Update statuses.
429
//.console.time("UpdateStatuses")
430
var readArticles = feedArticles.filter(function(e) { return e.status == "1" } )
431
if (readArticles.length) {
432
var BASE_HEURISTIC_CHUNK = 512
433
var arrayLength = readArticles.length
435
var queryGetFunction = function(paramSize) {
436
var baseQuery = 'update article set status="1" where guid in ('
437
for (var i = 0; i < paramSize; i++)
439
baseQuery = baseQuery.substring(0, baseQuery.length - 1) + ') AND status="0"'
443
var chunkOfData = readArticles.length > BASE_HEURISTIC_CHUNK ? BASE_HEURISTIC_CHUNK : readArticles.length
444
var fullQuery = queryGetFunction(chunkOfData)
446
for (var j = 0; j < readArticles.length; j += chunkOfData) {
448
var limit = Math.min(chunkOfData, readArticles.length - j)
449
var queryToUse = limit == chunkOfData ? fullQuery : queryGetFunction(limit)
452
for (var k = 0; k < limit; k++)
453
queryData.push(readArticles[j + k].guid)
455
dbResult = tx.executeSql(queryToUse, queryData)
456
// console.log("CONSUMED", limit, "Rows affected:", dbResult.rowsAffected)
458
} // if restore array
459
//.console.timeEnd("UpdateStatuses")
465
function updateArticleStatus(id, status)
467
var db = openStdDataBase()
469
db.transaction(function (tx) {
470
dbResult = tx.executeSql('update article set status=? WHERE id=?',
472
console.log("article status UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)
477
function updateArticleFavourite(id, favourite) {
478
var db = openStdDataBase()
480
db.transaction(function (tx) {
481
// ensureFeedTableExists(tx)
482
dbResult = tx.executeSql('UPDATE article SET favourite=? WHERE id=?',
484
console.log("article favourite UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)
491
function deleteArticle(id)
493
var db = openStdDataBase()
495
db.transaction(function (tx) {
496
// ensureFeedTableExists(tx)
497
dbResult = tx.executeSql('delete from article WHERE id=?',
499
console.log("article delete, AFFECTED ROWS: ", dbResult.rowsAffected)
504
// clear article table, only status='2' and favourite='1' remain
505
function clearArticles(feed_id)
507
var db = openStdDataBase()
509
db.transaction(function (tx) {
510
// ensureFeedTableExists(tx)
511
dbResult = tx.executeSql("delete from article WHERE (status='0' OR status='1') AND favourite='0' AND feed_id=?", [feed_id])
512
console.log("article delete, AFFECTED ROWS: ", dbResult.rowsAffected)
519
* include select, insert, update and delete operations
526
var db = openStdDataBase()
529
db.transaction(function(tx) {
530
dbResult = tx.executeSql("SELECT * FROM tag")
531
console.assert(dbResult.rows.length !== 0, "ERROR: NO TAGS DATABASE")
537
function addTag(name)
540
var db = openStdDataBase()
541
db.transaction(function (tx) {
542
// ensureFeedTableExists(tx)
546
dbResult = tx.executeSql("SELECT 1 FROM tag WHERE name=?", [name])
547
if (dbResult.rows.length > 0) {
548
console.log("Database, add tag: already exist tag with source: ", name)
549
dbResult = {"error": true, "exist": true}
553
dbResult = tx.executeSql('INSERT INTO tag (name) VALUES(?)',
555
console.log("tag INSERT ID: ", dbResult.insertId)
557
dbResult.tagId = tx.executeSql("SELECT * FROM tag WHERE name=?", [name]).rows.item(0).id
558
console.log("dbResult.tagId", dbResult.tagId)
564
function updateTag(id, name) {
565
var db = openStdDataBase()
567
db.transaction(function (tx) {
568
dbResult = tx.executeSql('UPDATE tag SET name=? WHERE id=?',
570
console.log("tag UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)
576
function deleteTag(id)
578
var db = openStdDataBase()
580
db.transaction(function (tx) {
581
dbResult = tx.executeSql('delete from tag WHERE id=?',
583
console.log("tag delete, AFFECTED ROWS: ", dbResult.rowsAffected)
589
/* feed_tag operations
590
* include select, insert and delete operations
595
function loadFeedTags()
597
var db = openStdDataBase()
600
db.transaction(function(tx) {
601
dbResult = tx.executeSql("SELECT * FROM feed_tag")
606
function loadFeedsFromTag(tag_id)
608
var db = openStdDataBase()
611
db.transaction(function(tx) {
612
dbResult = tx.executeSql("SELECT t1.* FROM feed t1 INNER JOIN feed_tag t2 ON t1.id = t2.feed_id WHERE tag_id =?", [tag_id])
613
// console.log("loadFeedsFromTag:", tag_id, "SELECTED: ", dbResult.rows.length)
619
function addFeedTag(feed_id, tag_id)
622
var db = openStdDataBase()
623
db.transaction(function (tx) {
627
dbResult = tx.executeSql("SELECT 1 FROM feed_tag WHERE feed_id=? AND tag_id=? ", [feed_id, tag_id])
628
if (dbResult.rows.length > 0) {
629
console.log("Database, add feed_tag: already exist feed_tag with source: ", feed_id, tag_id)
630
return {"error": true, "exist": true};
633
dbResult = tx.executeSql('INSERT INTO feed_tag (feed_id, tag_id) VALUES(?, ?)',
635
console.log("feed_tag INSERT ID: ", dbResult.insertId)
641
function deleteFeedTag(id) {
642
var db = openStdDataBase()
644
db.transaction(function (tx) {
645
dbResult = tx.executeSql('delete from feed_tag WHERE id=?',
647
console.log("feed_tag delete, AFFECTED ROWS: ", dbResult.rowsAffected)
653
function deleteFeedTagsByTagId(tagId) {
654
var db = openStdDataBase()
656
db.transaction(function (tx) {
657
dbResult = tx.executeSql('delete from feed_tag WHERE tag_id=?',
659
console.log("feed_tag delete, AFFECTED ROWS: ", dbResult.rowsAffected)
664
function deleteFeedTag(feedId, tagId)
666
var db = openStdDataBase()
669
db.transaction(function(tx) {
670
dbResult = tx.executeSql("DELETE FROM feed_tag WHERE feed_id = ? AND tag_id = ?", [feedId, tagId])
671
console.log("feed_tag delete by feedId and tagId: ", dbResult.rowsAffected)
676
/* operations for testing
677
* include clear and drop operations
682
function clearData(table)
684
var db = openStdDataBase()
689
db.transaction(function(tx) {
690
tx.executeSql("delete from feed")
691
console.log("feed clear")
695
db.transaction(function(tx) {
696
tx.executeSql("delete from article")
697
console.log("article clear")
701
db.transaction(function(tx) {
702
tx.executeSql("delete from tag")
703
console.log("tag clear")
707
db.transaction(function(tx) {
708
tx.executeSql("delete from feed_tag")
709
console.log("feed_tag clear")
713
db.transaction(function(tx) {
714
tx.executeSql("delete from settings")
715
console.log("settings clear")
719
db.transaction(function(tx) {
720
tx.executeSql("delete from feed_tag")
721
tx.executeSql("delete from feed")
722
tx.executeSql("delete from tag")
723
tx.executeSql("delete from article")
724
tx.executeSql("delete from settings")
725
console.log("DATABASE clear")
731
function dropTable(table)
733
var db = openStdDataBase()
738
db.transaction(function(tx) {
739
tx.executeSql("DROP TABLE IF EXISTS feed")
740
console.log("feed deleted")
744
db.transaction(function(tx) {
745
tx.executeSql("DROP TABLE IF EXISTS article")
746
console.log("article deleted")
750
db.transaction(function(tx) {
751
tx.executeSql("DROP TABLE IF EXISTS tag")
752
console.log("tag deleted")
756
db.transaction(function(tx) {
757
tx.executeSql("DROP TABLE IF EXISTS feed_tag")
758
console.log("feed_tag deleted")
762
db.transaction(function(tx) {
763
tx.executeSql("DROP TABLE IF EXISTS settings")
764
console.log("settings deleted")
768
db.transaction(function(tx) {
769
tx.executeSql("DROP TABLE IF EXISTS feed")
770
tx.executeSql("DROP TABLE IF EXISTS article")
771
tx.executeSql("DROP TABLE IF EXISTS tag")
772
tx.executeSql("DROP TABLE IF EXISTS feed_tag")
773
tx.executeSql("DROP TABLE IF EXISTS settings")
774
console.log("DATABASE deleted")