bookmark.db

humm yes, I see
Do you think it will be enought to restore the 3rd record for our special set 9999 RepeatFile.ts ..
or it would be necessary to fix all the missing 3rd records...?

I am planning to do a different approach for the auto-fix.

vic1972 escribió:humm yes, I see
Do you think it will be enought to restore the 3rd record for our special set 9999 RepeatFile.ts ..
or it would be necessary to fix all the missing 3rd records...?

I am planning to do a different approach for the auto-fix.

Well, actually it's enough to restore the 3rd record for 9999 set only

Hey!!
Hope you are having nice holidays guys
Here in Malaga is quite sunny

ok, it happened again to me, i am loosing the restart execution in the point where it stopped,
yes, a known issue.
Lets see if for next month i have some time to have again a look at this, and we might start thinking in a new release
of Harmony for this autumn

Byeee

vic1972 escribió:Hey!!
Hope you are having nice holidays guys
Here in Malaga is quite sunny

ok, it happened again to me, i am loosing the restart execution in the point where it stopped,
yes, a known issue.
Lets see if for next month i have some time to have again a look at this, and we might start thinking in a new release
of Harmony for this autumn

Byeee

Hi Victor,
well, holidays are gone
Of course, I am aware of that issue but as I noticed before, the SQlite client which is embedded in DVDPlayer is not quite reliable (eg it sometimes corrupts its own db records). So I will try to thinking about different approach but I must say that I have not got any ideas now

Hello George,
I am having a look into the triggers implementation,
and there is one part where I quite do not understand,

is the when clause of the triggers:AutoRecoveryHandle

Código:

WHEN (CASE WHEN (((SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm') + 1 = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k')) AND ((SELECT fValue FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k') = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB')) AND ((SELECT fKey FROM tParameter WHERE id = (SELECT id FROM tParameter WHERE fKey = ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k')) + 1) = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'))) THEN 0 ELSE (SELECT fValue FROM tHarmony WHERE fKey = 'AutoRecoveryEnable') END)

I have simplified into, in a text editor, just for my understanding:

Código:


WHEN
    (CASE
          WHEN
          (((SELECT id FROM tParameter WHERE fKey = 9999m ) + 1 = (SELECT id FROM tParameter WHERE fKey = 9999k))
                                                            AND 
          ((SELECT fValue FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k') = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB'))
                                                            AND 
          ((SELECT fKey FROM tParameter WHERE id = (SELECT id FROM tParameter WHERE fKey = (9999k) + 1) = "fileset:///tmp/ramfs/volumes/HDD ( Internal )/REC/ Repeat.ts" ))
THEN 0
ELSE
(1)

END)


Could you add some comments, or explain in your own words.
thanks so much.
regards

vic1972 escribió:Hello George,
I am having a look into the triggers implementation,
and there is one part where I quite do not understand,

is the when clause of the triggers:AutoRecoveryHandle

Código:

WHEN (CASE WHEN (((SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm') + 1 = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k')) AND ((SELECT fValue FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k') = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB')) AND ((SELECT fKey FROM tParameter WHERE id = (SELECT id FROM tParameter WHERE fKey = ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k')) + 1) = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'))) THEN 0 ELSE (SELECT fValue FROM tHarmony WHERE fKey = 'AutoRecoveryEnable') END)

I have simplified into, in a text editor, just for my understanding:

Código:


WHEN
    (CASE
          WHEN
          (((SELECT id FROM tParameter WHERE fKey = 9999m ) + 1 = (SELECT id FROM tParameter WHERE fKey = 9999k))
                                                            AND 
          ((SELECT fValue FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k') = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB'))
                                                            AND 
          ((SELECT fKey FROM tParameter WHERE id = (SELECT id FROM tParameter WHERE fKey = (9999k) + 1) = "fileset:///tmp/ramfs/volumes/HDD ( Internal )/REC/ Repeat.ts" ))
THEN 0
ELSE
(1)

END)


Could you add some comments, or explain in your own words.
thanks so much.
regards

Hello Victor,

well, the code you mentioned is the "conditional" part of "AutoRecoveryHandle" trigger, in simply words it does following:

if all three records of " Repeat.ts" in tParameter table are correct then it returns 0, if not, it returns the fvalue of 'AutoRecoveryEnable' fkey (ie normally 1). This returned value is an "boolean" argument of WHEN clause in trigger - if is "true" then the recovery process of " Repeat.ts" db records begins:

AFTER UPDATE ON tHarmony WHEN {value returned by inner code} BEGIN
/* delete any previous db records regarding the Recent File &
create new db records for Recent File &
abandon all subsequent "AFTER UPDATE" triggers
*/
END

AutoRecoveryHandle trigger is actually raised by any changing of tParameter table: in fact, it is in cascade on AutoRecoveryByINSERT & AutoRecoveryByUPDATE & AutoRecoveryByDELETE triggers: these three triggers are raised by INSERT or UPDATE or DELETE of any records in modify tParameter table, so they modify tHarmony table by updating 'AutoRecoveryRaiseTrigger' with random value - this is such moment when AutoRecoveryHandle trigger is raised actually.

Well, it is quite complicated, but I haven't found any simpler solution. It works perfectly in SQlite environment on PC, unfortunately it is not quite reliable in SQlite environment embedded in DVDPlayer

Much clear now, thanks.

Would it be feasible and interesting to have the AutoRecoveryHandle executed every time?
even if it is not needed....

vic1972 escribió:Much clear now, thanks.

Would it be feasible and interesting to have the AutoRecoveryHandle executed every time?
even if it is not needed....

That's great idea!!! So we could try to modify "UPDATE" triggers by using REPLACE statement (alias for "INSERT OR REPLACE") to completely "recover" appropriate DB records each time so the AutoRecoveryHandle wouldn't be needed anymore (I hope ). I am very sorry, unfortunately I am ill now, so I am not just able to work on it but when I get better I will try to modify it in that way.

G E T . W E L L . S O O N !!!!!!!!!!!!!!!!!!



ANother approach, i have thought,
is to have only the update triggers of 1st and 3rd record, eliminate the autorecover trriger.

And then when the lg boots, send an appropiate execute .sql file to the database
(like we do when we insert the triggers the first time)

something like

Código:


DELETE FROM tParameter WHERE fKey = '9999m'
                                          OR
                            fKey = '9999k'
                                          OR
                            fKey = 'fileset:///tmp/ramfs/volumes/HDD ( Internal )/REC/ Repeat.ts'
                                          OR
                            fValue = X'66696C657365743A2F2F2F746D702F72616D66732F766F6C756D65732F484444202820496E7465726E616C20292F5245432F205265706561742E7473';

INSERT INTO tParameter (fKey, fValue) VALUES ('9999m',zeroblob(4) );
INSERT INTO tParameter (fKey, fValue) VALUES ('9999k',X'66696C657365743A2F2F2F746D702F72616D66732F766F6C756D65732F484444202820496E7465726E616C20292F5245432F205265706561742E7473');
INSERT INTO tParameter (fKey, fValue) VALUES ('fileset:///tmp/ramfs/volumes/HDD ( Internal )/REC/ Repeat.ts',zeroblob(16));


By the way, it is curious that sqlite 3 does not support multiple insert, at least,
i have not been able to execute:

INSERT INTO tParameter (fKey,fValue) VALUES ('a','b') , ('c','d');

Hello, been playing around with different versions of the triggers, and have found that eliminating the cascade triggers and the autofix feature, it seems that it is lasting fine for a lot of days;
in fact this have been working fine for at least one month.

The edit trrigers is very basic:

Código:



/* create supplemental (tHarmony) table */
CREATE TABLE IF NOT EXISTS "tHarmony" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL UNIQUE , "fKey" TEXT, "fValue" BLOB); /* table structure */

INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (1,'GUID','DC1E300C-E7F1-4EF9-B5BF-40EDB6DC56C6'); /* unique Id in TEXT (STRING) form for identification the modified db file */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (2,'RecentFilenameText','fileset:///tmp/ramfs/volumes/HDD ( Internal )/REC/ Repeat.ts'); /* full name of Recent File in TEXT form */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (3,'RecentFilenameBLOB',X'66696C657365743A2F2F2F746D702F72616D66732F766F6C756D65732F484444202820496E7465726E616C20292F5245432F205265706561742E7473'); /* full name od Recent File in BLOB form */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (4,'RecentFilenameNumber','9999'); /* the number of Recent File in db records */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (5,'AutoRecoveryEnable',1); /* 1 - enable Auto Recovery of Recent File db records */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (6,'AutoRecoveryRaiseTrigger',zeroblob(16)); /* used for raise the recovery trigger by changing a value to a new value */




/* create new db records for Recent File */
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm',zeroblob(4));
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k',(SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB'));
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'),zeroblob(16));



/* tParameter table "AFTER UPDATE" triggers */
CREATE TRIGGER IF NOT EXISTS "main"."Upd3rdRowByUPDATE" AFTER UPDATE ON tParameter WHEN old.fKey LIKE 'fileset%' BEGIN UPDATE tParameter SET fValue = new.fValue  WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'); END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd3rdRowByINSERT" AFTER INSERT ON tParameter WHEN new.fKey LIKE 'fileset%' BEGIN UPDATE tParameter SET fValue = new.fValue  WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'); END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd1stRowByUPDATE" AFTER UPDATE ON tParameter WHEN old.fKey LIKE '_m' OR old.fKey LIKE '__m' OR old.fKey LIKE '___m'  BEGIN UPDATE tParameter SET fValue = new.fValue WHERE id = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm'); END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd1stRowByINSERT" AFTER INSERT ON tParameter WHEN new.fKey LIKE '_m' OR new.fKey LIKE '__m' OR new.fKey LIKE '___m' BEGIN UPDATE tParameter SET fValue = new.fValue WHERE id = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm'); END;




vic1972 escribió:Hello, been playing around with different versions of the triggers, and have found that eliminating the cascade triggers and the autofix feature, it seems that it is lasting fine for a lot of days;
in fact this have been working fine for at least one month.

The edit trrigers is very basic:

Código:



/* create supplemental (tHarmony) table */
CREATE TABLE IF NOT EXISTS "tHarmony" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL UNIQUE , "fKey" TEXT, "fValue" BLOB); /* table structure */

INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (1,'GUID','DC1E300C-E7F1-4EF9-B5BF-40EDB6DC56C6'); /* unique Id in TEXT (STRING) form for identification the modified db file */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (2,'RecentFilenameText','fileset:///tmp/ramfs/volumes/HDD ( Internal )/REC/ Repeat.ts'); /* full name of Recent File in TEXT form */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (3,'RecentFilenameBLOB',X'66696C657365743A2F2F2F746D702F72616D66732F766F6C756D65732F484444202820496E7465726E616C20292F5245432F205265706561742E7473'); /* full name od Recent File in BLOB form */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (4,'RecentFilenameNumber','9999'); /* the number of Recent File in db records */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (5,'AutoRecoveryEnable',1); /* 1 - enable Auto Recovery of Recent File db records */
INSERT OR IGNORE INTO tHarmony (id, fKey, fValue) VALUES (6,'AutoRecoveryRaiseTrigger',zeroblob(16)); /* used for raise the recovery trigger by changing a value to a new value */




/* create new db records for Recent File */
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm',zeroblob(4));
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'k',(SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameBLOB'));
INSERT INTO tParameter (fKey, fValue) VALUES ((SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'),zeroblob(16));



/* tParameter table "AFTER UPDATE" triggers */
CREATE TRIGGER IF NOT EXISTS "main"."Upd3rdRowByUPDATE" AFTER UPDATE ON tParameter WHEN old.fKey LIKE 'fileset%' BEGIN UPDATE tParameter SET fValue = new.fValue  WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'); END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd3rdRowByINSERT" AFTER INSERT ON tParameter WHEN new.fKey LIKE 'fileset%' BEGIN UPDATE tParameter SET fValue = new.fValue  WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameText'); END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd1stRowByUPDATE" AFTER UPDATE ON tParameter WHEN old.fKey LIKE '_m' OR old.fKey LIKE '__m' OR old.fKey LIKE '___m'  BEGIN UPDATE tParameter SET fValue = new.fValue WHERE id = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm'); END;
CREATE TRIGGER IF NOT EXISTS "main"."Upd1stRowByINSERT" AFTER INSERT ON tParameter WHEN new.fKey LIKE '_m' OR new.fKey LIKE '__m' OR new.fKey LIKE '___m' BEGIN UPDATE tParameter SET fValue = new.fValue WHERE id = (SELECT id FROM tParameter WHERE fKey = (SELECT fValue FROM tHarmony WHERE fKey = 'RecentFilenameNumber') || 'm'); END;



Hello Victor,

I am very very very very very very very sorry, that I have not started to "play" with triggers etc. yet, but I must say that I have not enough time, actually
But if you have found a solution that it is really great !!!

Hey!!!
Not to be sorry , in fact I guess the issue would appear sooner or later.
I just removed all the autorecovery part, which was causing a overhead with the different cascade triggers.
You still have all of the credits of the great work.
I think the line of investigation for future, would be a kind of .sql with different sql instructions that analize and fic the bookmark.db
this can be executed by PM for example each time LG boots up.

Anyway, lets go with this actual solution in this release, and we will see the reactions of people.
Bye for the moment.

vic1972 escribió:Hey!!!
Not to be sorry , in fact I guess the issue would appear sooner or later.
I just removed all the autorecovery part, which was causing a overhead with the different cascade triggers.
You still have all of the credits of the great work.
I think the line of investigation for future, would be a kind of .sql with different sql instructions that analize and fic the bookmark.db
this can be executed by PM for example each time LG boots up.

Anyway, lets go with this actual solution in this release, and we will see the reactions of people.
Bye for the moment.

OK, thanks for your appreciation! It's a great idea to check the state of bookmark.db at the time of booting by independent sql code! I hope that I will have some time in future (or maybe you or anyone else) to try this approach ...