I wrote a LibreOffice / OpenOffice extension that allows access to these Google Drive files.
To do so, I use a Hsqldb 2.4 database, script file is accessible at: https://github.com/prrvchr/gDriveOOo/blob/master/gDriveOOo/hsqldb/vnd.google-apps.script
The LibreOffice / OpenOffice UNO API does not handle duplicate file names (such as under a file system), while Google Drive offers this possibility.
To work around this problem, I created four View ('ChildUri', 'IdentifierUri', 'ItemUri' and 'Uri') allowing me to build a new name of the form: CONCAT (name, ~, position) for the second and following doublon, position being their position in the GROUP BY clause...
CREATE VIEW PUBLIC."ChildUri" ("Id","Name","Parent") AS SELECT "I"."Id","I"."Name","C"."ItemId" FROM PUBLIC."Items" AS "I" JOIN PUBLIC."Children" AS "C" ON "I"."Id"="C"."ChildId" WHERE "I"."Trashed"=FALSE
CREATE VIEW PUBLIC."IdentifierUri" ("Idx","Name","Parent") AS SELECT ARRAY_AGG("I"."Id" ORDER BY "I"."DateCreated","I"."Id"),"I"."Name","C"."Parent" FROM PUBLIC."Items" AS "I" JOIN PUBLIC."ChildUri" AS "C" ON "I"."Id"="C"."Id" GROUP BY "I"."Name","C"."Parent"
CREATE VIEW PUBLIC."ItemUri" ("Id","Name","Length","Position","Parent") AS SELECT "C"."Id","I"."Name",CARDINALITY("I"."Idx"),POSITION_ARRAY("C"."Id" IN "I"."Idx"),"I"."Parent" FROM PUBLIC."ChildUri" AS "C" JOIN PUBLIC."IdentifierUri" AS "I" ON "C"."Name"="I"."Name" AND "C"."Parent"="I"."Parent"
CREATE VIEW PUBLIC."Uri" ("Id","Name","Uri","Parent") AS SELECT "I"."Id","I"."Name",CASEWHEN("I"."Position"=1,"I"."Name",INSERT("I"."Name", LENGTH("I"."Name") - POSITION('.' IN REVERSE("I"."Name")) + 1,0,CONCAT('~',"I"."Position"))),"I"."Parent" FROM PUBLIC."ItemUri" AS "I"
It works well, but drastically lacks speed when calling the 'selectChild' procedure, it takes 10 seconds to execute, whereas before only some second.
CREATE PROCEDURE PUBLIC."selectChild"(IN USERID VARCHAR(100),IN ITEMID VARCHAR(100),IN URL VARCHAR(250),IN MODE SMALLINT,OUT ROWCOUNT SMALLINT) SPECIFIC "selectChild_1" LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA NEW SAVEPOINT LEVEL DYNAMIC RESULT SETS 1 BEGIN ATOMIC DECLARE TMPCOUNT SMALLINT DEFAULT 0;DECLARE RESULT CURSOR WITH RETURN FOR SELECT "Title","Size","DateModified","DateCreated","IsFolder",CASEWHEN("IsFolder",CONCAT(URL,'/',"Id"),CONCAT(URL,'/',"Uri"))"TargetURL",FALSE "IsHidden",FALSE "IsVolume",FALSE "IsRemote",FALSE "IsRemoveable",FALSE "IsFloppy",FALSE "IsCompactDisc" FROM PUBLIC."Child" WHERE "UserId"=USERID AND "Parent"=ITEMID AND("IsFolder" OR "Loaded">=MODE)FOR READ ONLY;CALL "countChild"(USERID,ITEMID,MODE,TMPCOUNT);SET ROWCOUNT=TMPCOUNT;OPEN RESULT;END
I admit that it exceeds my skills, and turns to you for help.
Thanks in advance.
You need an index to improve SELECT query speed on a table. The SELECT in the procedure needs an index such as the following if it is not already a FOREIGN KEY
CREATE INDEX "ChildIndex" ON "Child"("Parent", "UserId")