bookmark.db

Página 8 de 8. Precedente  1, 2, 3, 4, 5, 6, 7, 8

Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  vic1972 el Jue Abr 14, 2011 10:43 pm

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
vic1972

Mensajes : 2260
Fecha de inscripción : 09/12/2009
Edad : 47
Localización : Malaga

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  George2005 el Vie Abr 15, 2011 12:12 am

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


George2005

Mensajes : 112
Fecha de inscripción : 26/10/2010
Edad : 44
Localización : Olomouc - Czech Republic

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  vic1972 el Sáb Ago 20, 2011 10:08 am

Hey!!
Hope you are having nice holidays guys Smile
Here in Malaga is quite sunny 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 Wink

Byeee
vic1972
vic1972

Mensajes : 2260
Fecha de inscripción : 09/12/2009
Edad : 47
Localización : Malaga

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  George2005 el Mar Sep 06, 2011 9:03 pm

vic1972 escribió:Hey!!
Hope you are having nice holidays guys Smile
Here in Malaga is quite sunny 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 Wink

Byeee
Hi Victor,
well, holidays are gone Wink
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 Sad


George2005

Mensajes : 112
Fecha de inscripción : 26/10/2010
Edad : 44
Localización : Olomouc - Czech Republic

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  vic1972 el Miér Oct 05, 2011 8:52 pm

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
vic1972

Mensajes : 2260
Fecha de inscripción : 09/12/2009
Edad : 47
Localización : Malaga

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  George2005 el Jue Oct 06, 2011 12:24 am

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 Sad


George2005

Mensajes : 112
Fecha de inscripción : 26/10/2010
Edad : 44
Localización : Olomouc - Czech Republic

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  vic1972 el Jue Oct 06, 2011 12:43 pm

Much clear now, thanks.

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

vic1972
vic1972

Mensajes : 2260
Fecha de inscripción : 09/12/2009
Edad : 47
Localización : Malaga

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  George2005 el Vie Oct 07, 2011 5:25 pm

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 Wink ). 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.


George2005

Mensajes : 112
Fecha de inscripción : 26/10/2010
Edad : 44
Localización : Olomouc - Czech Republic

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  vic1972 el Vie Oct 07, 2011 11:00 pm

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

cheers

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


vic1972
vic1972

Mensajes : 2260
Fecha de inscripción : 09/12/2009
Edad : 47
Localización : Malaga

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  vic1972 el Sáb Nov 12, 2011 8:00 pm

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
vic1972

Mensajes : 2260
Fecha de inscripción : 09/12/2009
Edad : 47
Localización : Malaga

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  George2005 el Sáb Nov 12, 2011 9:21 pm

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 Crying or Very sad
But if you have found a solution that it is really great !!!

George2005

Mensajes : 112
Fecha de inscripción : 26/10/2010
Edad : 44
Localización : Olomouc - Czech Republic

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  vic1972 el Dom Nov 13, 2011 3:26 pm

Hey!!! Wink
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
vic1972

Mensajes : 2260
Fecha de inscripción : 09/12/2009
Edad : 47
Localización : Malaga

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  George2005 el Dom Nov 13, 2011 8:26 pm

vic1972 escribió:Hey!!! Wink
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 ...

George2005

Mensajes : 112
Fecha de inscripción : 26/10/2010
Edad : 44
Localización : Olomouc - Czech Republic

Volver arriba Ir abajo

bookmark.db - Página 8 Empty Re: bookmark.db

Mensaje  Contenido patrocinado


Contenido patrocinado


Volver arriba Ir abajo

Página 8 de 8. Precedente  1, 2, 3, 4, 5, 6, 7, 8

Volver arriba


 
Permisos de este foro:
No puedes responder a temas en este foro.