~marcinello/ubuntu-rssreader-app/fix-1297463

.pragma library // I hope this will prevent the waste of memory.

.import QtQuick.LocalStorage 2.0 as SQL

/* For internal usage in module.

 */

var gDbCache = undefined

function openStdDataBase() {

    if (gDbCache === undefined)

    {

        gDbCache = SQL.LocalStorage.openDatabaseSync("RSS Reader", "1.0", "App main DB", 1000000)

    }

    // check table exist after open the database

    gDbCache.transaction(function(tx){

        checkTableExists(tx/*, opts*/);

    }

    )

    return gDbCache

}

// Lol, guys, look here: http://www.sqlite.org/datatype3.html

// All VARCHAR(x) converted to TEXT :)

// I am currently working with 190 symbols length strings, all is ok :)

function checkTableExists(transaction /* and additional string keys */) {

    transaction.executeSql('PRAGMA foreign_keys = ON;')   // enable foreign key support

    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);")

    transaction.executeSql("CREATE TABLE IF NOT EXISTS tag  (id  INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,name  TEXT  NOT NULL UNIQUE );")

    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);")

    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);")

    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');")

    var rs = transaction.executeSql("select * from settings")

    if (rs.rows.length == 0)

    {

        transaction.executeSql('INSERT INTO settings VALUES(?, ?, ?, ?, ?, ?)',

                               [1 , 1.0, "000000", '0', 0, '0'])

    }

}

/* feed operations

 * include select, insert, update and delete operations

 */

// select

function loadFeeds()

{

    var db = openStdDataBase()

    var dbResult

//    var feeds

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT * FROM feed")

        console.log("feed SELECTED: ", dbResult.rows.length)

    }

    )

    return dbResult;  // I suggest that return the whole result in order to know if error occurs

}

// insert

function addFeed(title, source)  // from user input

{

    var dbResult

    var db = openStdDataBase()

    db.transaction(function (tx) {

        /* Check uniqueness.

         */

        dbResult = tx.executeSql("SELECT * FROM feed WHERE source=?", [source])

        if (dbResult.rows.length > 0) {

            console.log("Database, addFeed: already exist feed with source: ", source, "ID", dbResult.rows.item(0).id)

            dbResult = {"error": true, "exist": true}

            return

        }

        dbResult = tx.executeSql('INSERT INTO feed (title, source) VALUES(?, ?)',

                                 [title , source])

        console.log("feed INSERT ID: ", dbResult.insertId)

        dbResult.feedId = tx.executeSql("SELECT * FROM feed WHERE source=?", [source]).rows.item(0).id

        console.log("dbResult.feedId", dbResult.feedId)

    }

    )

    return dbResult;

}

// change confirmed

/* Update feed status.

 * 0 - default, 1 - good, 2 - bad url.

 */

function setFeedStatus(id, status)  // from user input

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

        dbResult = tx.executeSql('UPDATE feed SET status=? WHERE id=?',

                                 [status, id])

        console.log("feed setFeedStatus, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

// update

function updateFeedByUser(id, title, source)  // from user input

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('UPDATE feed SET title=?, source=? WHERE id=?',

                                 [title, source, id])

        console.log("feed updateFeedByUser, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

function updateFeedByXml(id, link, description, title)   // from xml file

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

        dbResult = tx.executeSql('UPDATE feed SET link=?, description=?, title=? WHERE id=?',

                                 [link, description, title, id])

        console.log("feed updateFeedByXml, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

function updateFeedImage(id, image)  //  offline image path

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('UPDATE feed SET image=? WHERE id=?',

                                 [image, id])

        console.log("feed UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

function updateFeedCount(id, count)  //

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('UPDATE feed SET count=? WHERE id=?',

                                 [count, id])

        console.log("feed UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

// delete

function deleteFeed(id)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        tx.executeSql('PRAGMA foreign_keys = ON;')   // enable foreign key support

        dbResult = tx.executeSql('delete from feed WHERE id=?',

                                 [id])

        console.log("feed delete, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

function deleteFeedByTagId(tagId)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        tx.executeSql('PRAGMA foreign_keys = ON;')   // enable foreign key support

        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 = ?)',

                                 [tagId])

        console.log("feed delete by tag id, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

// select feeds without of topic (tag).

function loadFeedsWithoutTopic()

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT * FROM feed WHERE id NOT IN (SELECT feed_id FROM feed_tag)")

        console.log("loadFeedsWithoutTopic SELECTED: ", dbResult.rows.length)

    }

    )

    return dbResult;  // I suggest that return the whole result in order to know if error occurs

}

function feedsCount() {

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT Count(*) FROM feed_tag")

        var obj = dbResult.rows.item(0)

        dbResult.count = obj["Count(*)"]

    }

    )

    return dbResult

}

/* article operations

 * include select, insert, update and delete operations

 *

 *

 */

// select

function loadArticles(params)   // params = {"isAll": true/false, "feedId": id | "tagId" : id}

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        if (params == undefined || params.isAll) // miss params

            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')

        else if (params.feedId)

            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])

        else if (params.tagId)

            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])

    }

    )

    return dbResult;

}

// UNUSED

function loadArticle(articleId)   // params = {"isAll": true/false, "feedId": id | "tagId" : id}

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        dbResult = tx.executeSql('SELECT article.*, feed.title as feed_name FROM article inner join feed on article.feed_id = feed.id WHERE article.id = ?', [articleId])

        console.assert(dbResult.rows.length !== 0, "ERROR loadArticle, " + articleId)

    }

    )

    return dbResult;

}

// load needed properties of every article, like status, favourite, etc..

function preloadArticlesProperties(feedId)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        if (feedId == undefined) // miss feedId

            dbResult = tx.executeSql('SELECT guid, status FROM article WHERE status = "1" ') // guid, status

        else

            dbResult = tx.executeSql('SELECT guid, status FROM article WHERE feed_id = ? AND status = "1" ', [feedId])

    }

    )

    console.log("preloadArticlesProperties: ", dbResult.rows.length)

    return dbResult;

}

// load all favourite articles

function loadFavouriteArticles()

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        dbResult = tx.executeSql('SELECT article.*, feed.title as feed_name FROM article inner join feed on article.feed_id = feed.id WHERE article.favourite = "1" ORDER BY article.pubdate DESC' )

    }

    )

    //console.log("loadFavouriteArticles", dbResult.rows.length)

    //console.assert(dbResult.rows.length !== 0,  "ERROR: There are no saved articles")

    return dbResult;

}

// insert

function addArticle(title, content, link, description, pubdate, guid, feed_id)

{

    var dbResult

    var db = openStdDataBase()

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        /* Check uniqueness.

         */

        dbResult = tx.executeSql("SELECT * FROM article WHERE guid=?", [guid])

        if (dbResult.rows.length > 0) {

            console.log("Database, add article: already exist article with guid: ", guid)

            dbResult = {"error": true, "exist": true}

            return

        }

        dbResult = tx.executeSql('INSERT INTO article (title, content, link, description, pubdate, guid, feed_id) VALUES(?, ?, ?, ?, ?, ?, ?)',

                                 [title, content, link, description, pubdate, guid, feed_id])

        dbResult.articleId = tx.executeSql("SELECT * FROM article WHERE guid=?", [guid]).rows.item(0).id

    }

    )

    return dbResult;

}

/*

  this function is for avoiding hard drive performance issue,

  pass model and feed id as parameters to this function, it will automaticly insert all the articles into database

  add third pamams which is for restoring articles' properties

 */

function addArticles(model, feed_id, restoreArray)

{

    var dbResult

    var db = openStdDataBase()

    db.transaction(function (tx) {

        var article;

        for (var i = 0; i < model.count; i++) {

            article = model.get(i)

            var title =  article.title == undefined ? "" : article.title

            var guid =  article.guid == undefined ? Qt.md5(title) : article.guid

            var link =  article.link == undefined ? "" : article.link

            var pubDate =  article.pubDate == undefined ? "" : article.pubDate

            var description =  article.description == undefined ? "" : article.description

            var content =  article.content == undefined ? "" : article.content

            var image =  article.image == undefined ? "" : article.image

            var media_groups = article.media_groups == undefined ? "" : JSON.stringify(article.media_groups)

            /* Check uniqueness.

             */

            dbResult = tx.executeSql("SELECT * FROM article WHERE guid=? AND feed_id=?", [guid, feed_id])

            if (dbResult.rows.length > 0) {

                console.log("Database, add article: already exist article with source: ", guid)

                continue;

            }

            dbResult = tx.executeSql('INSERT INTO article (title, content, link, description, pubdate, guid, feed_id, image, media_groups) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)',

                                     [title, content, link, description, pubDate, guid, feed_id, image, media_groups])

            // console.log("article INSERT ID: ", JSON.stringify(dbResult) )

        }

        if (restoreArray != undefined) {

            for (var j = 0; j < restoreArray.rows.length; j++) {

                dbResult = tx.executeSql('UPDATE article SET status=? WHERE guid=? AND status="0"',

                                         [restoreArray.rows[j].status, restoreArray.rows[j].guid])

            }

        }

    }

    )

    return dbResult;

}

// update

function updateArticleStatus(id, status)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('UPDATE article SET status=? WHERE id=?',

                                 [status, id])

        console.log("article status UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

function updateArticleFavourite(id, favourite)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('UPDATE article SET favourite=? WHERE id=?',

                                 [favourite, id])

        console.log("article favourite UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

function updateArticleImage(id, image)  //  offline image path

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('UPDATE article SET image=? WHERE id=?',

                                 [image, id])

        //console.log("article UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

// delete

function deleteArticle(id)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('delete from article WHERE id=?',

                                 [id])

        console.log("article delete, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

// clear article table, only status='2' and favourite='1' remain

function clearArticles(feed_id)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql("delete from article WHERE (status='0' OR status='1') AND favourite='0' AND feed_id=?", [feed_id])

        console.log("article delete, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

/* tag operations

 * include select, insert, update and delete operations

 *

 *

 */

// select

function loadTags()

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT * FROM tag")

        console.assert(dbResult.rows.length !== 0, "ERROR: NO TAGS DATABASE")

    }

    )

    return dbResult;

}

// insert

function addTag(name)

{

    var dbResult

    var db = openStdDataBase()

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        /* Check uniqueness.

         */

        dbResult = tx.executeSql("SELECT * FROM tag WHERE name=?", [name])

        if (dbResult.rows.length > 0) {

            console.log("Database, add tag: already exist tag with source: ", name)

            dbResult = {"error": true, "exist": true}

            return

        }

        dbResult = tx.executeSql('INSERT INTO tag (name) VALUES(?)',

                                 [name])

        console.log("tag INSERT ID: ", dbResult.insertId)

        dbResult.tagId = tx.executeSql("SELECT * FROM tag WHERE name=?", [name]).rows.item(0).id

        console.log("dbResult.tagId", dbResult.tagId)

    }

    )

    return dbResult;

}

// update

function updateTag(id, name)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('UPDATE tag SET name=? WHERE id=?',

                                 [name, id])

        console.log("tag UPDATE, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

// delete

function deleteTag(id)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('delete from tag WHERE id=?',

                                 [id])

        console.log("tag delete, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

/* feed_tag operations

 * include select, insert and delete operations

 *

 *

 */

// select

function loadFeedTags()

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT * FROM feed_tag")

    }

    )

    return dbResult;

}

function loadFeedsFromTag(tag_id)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT t1.* FROM feed t1 INNER JOIN feed_tag t2 ON t1.id = t2.feed_id WHERE tag_id =?", [tag_id])

        // console.log("loadFeedsFromTag:", tag_id, "SELECTED: ", dbResult.rows.length)

    }

    )

    return dbResult;

}

// insert

function addFeedTag(feed_id, tag_id)

{

    var dbResult

    var db = openStdDataBase()

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        /* Check uniqueness.

         */

        dbResult = tx.executeSql("SELECT * FROM feed_tag WHERE feed_id=? AND tag_id=? ", [feed_id, tag_id])

        if (dbResult.rows.length > 0) {

            console.log("Database, add feed_tag: already exist feed_tag with source: ", feed_id, tag_id)

            return {"error": true, "exist": true};

        }

        dbResult = tx.executeSql('INSERT INTO feed_tag (feed_id, tag_id) VALUES(?, ?)',

                                 [feed_id, tag_id])

        console.log("feed_tag INSERT ID: ", dbResult.insertId)

    }

    )

    return dbResult;

}

// delete

function deleteFeedTag(id)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('delete from feed_tag WHERE id=?',

                                 [id])

        console.log("feed_tag delete, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

// delete

function deleteFeedTagsByTagId(tagId)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function (tx) {

//        ensureFeedTableExists(tx)

        dbResult = tx.executeSql('delete from feed_tag WHERE tag_id=?',

                                 [tagId])

        console.log("feed_tag delete, AFFECTED ROWS: ", dbResult.rowsAffected)

    }

    )

    return dbResult

}

function deleteFeedTag(feedId, tagId)

{

    var db = openStdDataBase()

    var dbResult

    db.transaction(function(tx) {

        dbResult = tx.executeSql("DELETE FROM feed_tag WHERE feed_id = ? AND tag_id = ?", [feedId, tagId])

        console.log("feed_tag delete by feedId and tagId: ", dbResult.rowsAffected)

    }

    )

    return dbResult;

}

/* settings operations

 * include select and update operations

 *

 *

 */

// select  //for each setting

function getDBVersion()

{

    var db = openStdDataBase()

    var dbResult

    var dbVersion = ""

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT current_database_version FROM settings where id=1")

        if (dbResult.rows.length > 0)

        {

            dbVersion = dbResult.rows.item(0).current_database_version ;

        }

    }

    )

    return dbVersion;

}

function getDBLastUpdate()

{

    var db = openStdDataBase()

    var dbResult

    var dbLastUpdate = ""

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT database_last_updated FROM settings where id=1")

        if (dbResult.rows.length > 0)

        {

            dbLastUpdate = dbResult.rows.item(0).database_last_updated ;

        }

    }

    )

    return dbLastUpdate;

}

/* I want to use update_interval field for storing

 * font and light/dark theme.

 * INTEGER BYTES: 00 00 <useDark 1 | 0> <fontSize value>

 */

function getFontSize()

{

    var db = openStdDataBase()

    var dbResult

    var dbValue = 0

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT update_interval FROM settings WHERE id=1")

        if (dbResult.rows.length > 0)

        {

            dbValue = dbResult.rows.item(0).update_interval

            dbValue = 0xFF & dbValue

        }

    }

    )

    return dbValue;

}

function getUseDarkTheme()

{

    var db = openStdDataBase()

    var dbResult

    var result

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT update_interval FROM settings WHERE id=1")

        if (dbResult.rows.length > 0)

        {

            var dbValue = dbResult.rows.item(0).update_interval

            dbValue = 0xFF00 & dbValue

            result = (dbValue !== 0)

        }

    }

    )

    return result;

}

function getViewMode()

{

    var db = openStdDataBase()

    var dbResult

    var dbViewMode = ""

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT view_mode FROM settings where id=1")

        if (dbResult.rows.length > 0)

        {

            dbViewMode = dbResult.rows.item(0).view_mode ;

        }

    }

    )

    return dbViewMode;

}

function getUpdateInterval()

{

    var db = openStdDataBase()

    var dbResult

    var dbUpdateInterval = 0

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT update_interval FROM settings where id=1")

        if (dbResult.rows.length > 0)

        {

            dbUpdateInterval = dbResult.rows.item(0).update_interval ;

        }

    }

    )

    return dbUpdateInterval;

}

function getNetworkMode()

{

    var db = openStdDataBase()

    var dbResult

    var dbNetworkMode = ""

    db.transaction(function(tx) {

        dbResult = tx.executeSql("SELECT network_mode FROM settings where id=1")

        if (dbResult.rows.length > 0)

        {

            dbNetworkMode = dbResult.rows.item(0).network_mode ;

        }

    }

    )

    return dbNetworkMode;

}

// update  //for each setting

function setDBVersion(current_database_version)

{

    var db = openStdDataBase()