kinook
07-23-2007, 03:49 PM
Ultra Recall databases are based on SQLite (http://sqlite.org).
Standard SQLite tools can be used to query or modify .urd files.
Other options include
https://sqlitebrowser.org/
https://sqlitestudio.pl/
Note: To access .urd files from the standard SQLite console or other SQLite tools, edit the .urd file in a hex editor and change the first 15 bytes from:
Ultra Recall DB
to:
SQLite format 3
To use the SQLite console (https://www.sqlite.org/cli.html) to query or update Ultra Recall databases, download it https://www.sqlite.org/download.html (the last link under Precompiled Binaries for Windows), extract the files to the path containing the database, and at a Command Prompt, enter commands like this to open the database:
cd "\path\to\database"
Sqlite3.exe "filename.urd"
If the database is encrypted, it should be decrypted first (Tools | Change Password and set a blank password), or use the custom SQLite executable available at https://kinook.com/Download/Misc/SQLite3.zip:
Sqlite3.exe -key password "filename.urd"
and then enter SQL statements as needed (to run a statement, type it in and press Enter).
To call from a batch file, put the commands in a text file and pipe to standard input (see https://stackoverflow.com/questions/660320/how-to-automate-a-process-with-the-sqlite3-exe-command-line-tool).
Note: If the database is open in UR while making changes, use Tree | Refresh All on the menu (Shift+F5) to refresh with any external changes.
***** IMPORTANT *****
Always backup your database beforehand!!! You can easily damage your database, losing information or making it inaccessible from Ultra Recall.
***** IMPORTANT *****
Additional Details
Use
SELECT * FROM SQLITE_MASTER;
to view the database structure.
SELECT * FROM Item;
to view items in database
SELECT * FROM ItemLink;
to view logical links to items
SELECT * FROM ItemAttribute;
to view attributes for items
SELECT * FROM Attribute;
to view attribute properties (name, etc.)
Common Commands
Replace a common string in all URL item attributes:
UPDATE ItemAttribute SET Value = Replace(Value, 'Folder1/', 'Folder2/') WHERE AttributeID = 996 AND Value LIKE '%Folder1/%';
Change an absolute path in all URL item attributes to a relative path:
UPDATE ItemAttribute SET Value = Replace(Value, 'D:\MyFolder\', '') WHERE AttributeID = 996 AND Value LIKE 'D:\MyFolder\%';
Update the Quick Search item search text:
DELETE FROM FilterRow WHERE ItemId=990;
INSERT INTO FilterRow VALUES ((SELECT MAX(FilterRowID)+1 FROM FilterRow),990,0,12,'search text',0,-1,0);
Change all URLs starting with c:\ to e:\:
UPDATE ItemAttribute SET Value = 'e' || substr(Value, 2, 2000) WHERE AttributeID = 996 AND Value LIKE 'c:\%';
Remove a common title prefix from all matching items (replace ebay.co.uk below with desired prefix):
BEGIN;
UPDATE Item SET ItemTitle = substr(ItemTitle, 11, 1000) WHERE ItemTitle LIKE 'ebay.co.uk%';
UPDATE ItemAttribute SET Value = substr(Value, 11, 1000) WHERE AttributeID = 5 AND Value LIKE 'ebay.co.uk%';
COMMIT;
Replace a common string in the title of all items (replace $$dot$$ with text to match on):
BEGIN;
UPDATE Item SET ItemTitle = Replace(ItemTitle, '$$dot$$', '.') WHERE ItemTitle LIKE '%$$dot$$%';
UPDATE ItemAttribute SET Value = Replace(Value, '$$dot$$', '.') WHERE AttributeID = 5 AND Value LIKE '%$$dot$$%';
COMMIT;
List items with duplicate URLs:
SELECT ItemID, Value FROM ItemAttribute WHERE AttributeID=996 AND UPPER(Value) IN (SELECT UPPER(Value) AS Val FROM ItemAttribute WHERE AttributeID=996 GROUP BY Val HAVING(COUNT(*) > 1)) ORDER BY UPPER(Value);
List items with duplicate titles:
SELECT ItemID, Value FROM ItemAttribute WHERE AttributeID=5 AND UPPER(Value) IN (SELECT UPPER(Value) AS Val FROM ItemAttribute WHERE AttributeID=5 GROUP BY Val HAVING(COUNT(*) > 1)) ORDER BY UPPER(Value);
Generate a complete list of user-defined keywords:
SELECT * FROM UserKeyword ORDER BY 1;
Rename a user-defined keyword:
BEGIN;
UPDATE UserKeyword SET Keyword='newword' WHERE Keyword LIKE 'oldword';
UPDATE ItemKeyword SET Keyword='newword' WHERE Keyword LIKE 'oldword' AND AttributeID=1;
COMMIT;
Delete all auto-generated keywords (if FTS is disabled):
BEGIN;
DELETE FROM ItemKeyword WHERE AttributeID <> 1;
UPDATE Item Set KeywordCount = (SELECT COUNT(*) FROM ItemKeyword IK WHERE IK.ItemID = Item.ItemID);
COMMIT;
Delete all auto-generated keywords (if FTS is enabled):
DELETE FROM ftsItem;
VACUUM;
Delete an item, its attributes, and all logical links from database:
DELETE FROM ItemLink WHERE ItemID = 9999;
Rename an icon:
UPDATE Icon SET FileExtension = ".xyz" WHERE FileExtension = "zxy.ico";
Replace an icon:
First, import an icon file (.ico) that will replace the old icon. Then run a SQL statement like this:
UPDATE Icon SET IconBlob = (SELECT IconBlob FROM Icon WHERE FileExtension = "new_icon.ico"), UncompressedSize = (SELECT UncompressedSize FROM Icon WHERE FileExtension = "new_icon.ico") WHERE FileExtension = "old_icon_name";
Then restart UR. The new icon file item can be deleted afterwards.
Make Message Date (read-only system attribute) user-creatable and editable:
UPDATE Attribute SET AllowUserCreate=1, ReadOnly=0 WHERE AttributeID=994;
Insert an item and create a logical link to the new task item under a given parent item in the tree:
SELECT IconID,FileExtension FROM Icon;
SELECT Max(RowID) FROM Item;
INSERT INTO Item
(ItemID, ItemTitle, TemplateItemID,IconID, System, CreateDate, TitleAttributeID)
VALUES(<maxid above+1>,"Task Title",4,996,0,getDate(),5);
Note: Replace 4 above with the ItemID of the template item (My Data -> Templates) to use and 996 with the IconID of the icon to use.
SELECT Max(RowID) FROM ItemLink;
INSERT INTO ItemLink (ItemLinkID,ItemID,ParentItemID) VALUES (<max link id above+1>,<max item id above+1>,<parent item id>);
Global Settings
Query all global settings:
SELECT GlobalDataID,Value FROM GlobalData;
IDs:
2 = last selected item IDs
3 = last selected search IDs
4 = last active root item
5 = last jump item IDs
6 = last tab info
7 = last hoist info
10 = lock title info
11 = show flagged items
12 = layout info
Query a specific global settings value
SELECT Value FROM GlobalData WHERE GlobalDataID = 6;
Update a specific global settings value
UPDATE GlobalData SET Value = '1003,1000' WHERE GlobalDataID = 6;
Standard SQLite tools can be used to query or modify .urd files.
Other options include
https://sqlitebrowser.org/
https://sqlitestudio.pl/
Note: To access .urd files from the standard SQLite console or other SQLite tools, edit the .urd file in a hex editor and change the first 15 bytes from:
Ultra Recall DB
to:
SQLite format 3
To use the SQLite console (https://www.sqlite.org/cli.html) to query or update Ultra Recall databases, download it https://www.sqlite.org/download.html (the last link under Precompiled Binaries for Windows), extract the files to the path containing the database, and at a Command Prompt, enter commands like this to open the database:
cd "\path\to\database"
Sqlite3.exe "filename.urd"
If the database is encrypted, it should be decrypted first (Tools | Change Password and set a blank password), or use the custom SQLite executable available at https://kinook.com/Download/Misc/SQLite3.zip:
Sqlite3.exe -key password "filename.urd"
and then enter SQL statements as needed (to run a statement, type it in and press Enter).
To call from a batch file, put the commands in a text file and pipe to standard input (see https://stackoverflow.com/questions/660320/how-to-automate-a-process-with-the-sqlite3-exe-command-line-tool).
Note: If the database is open in UR while making changes, use Tree | Refresh All on the menu (Shift+F5) to refresh with any external changes.
***** IMPORTANT *****
Always backup your database beforehand!!! You can easily damage your database, losing information or making it inaccessible from Ultra Recall.
***** IMPORTANT *****
Additional Details
Use
SELECT * FROM SQLITE_MASTER;
to view the database structure.
SELECT * FROM Item;
to view items in database
SELECT * FROM ItemLink;
to view logical links to items
SELECT * FROM ItemAttribute;
to view attributes for items
SELECT * FROM Attribute;
to view attribute properties (name, etc.)
Common Commands
Replace a common string in all URL item attributes:
UPDATE ItemAttribute SET Value = Replace(Value, 'Folder1/', 'Folder2/') WHERE AttributeID = 996 AND Value LIKE '%Folder1/%';
Change an absolute path in all URL item attributes to a relative path:
UPDATE ItemAttribute SET Value = Replace(Value, 'D:\MyFolder\', '') WHERE AttributeID = 996 AND Value LIKE 'D:\MyFolder\%';
Update the Quick Search item search text:
DELETE FROM FilterRow WHERE ItemId=990;
INSERT INTO FilterRow VALUES ((SELECT MAX(FilterRowID)+1 FROM FilterRow),990,0,12,'search text',0,-1,0);
Change all URLs starting with c:\ to e:\:
UPDATE ItemAttribute SET Value = 'e' || substr(Value, 2, 2000) WHERE AttributeID = 996 AND Value LIKE 'c:\%';
Remove a common title prefix from all matching items (replace ebay.co.uk below with desired prefix):
BEGIN;
UPDATE Item SET ItemTitle = substr(ItemTitle, 11, 1000) WHERE ItemTitle LIKE 'ebay.co.uk%';
UPDATE ItemAttribute SET Value = substr(Value, 11, 1000) WHERE AttributeID = 5 AND Value LIKE 'ebay.co.uk%';
COMMIT;
Replace a common string in the title of all items (replace $$dot$$ with text to match on):
BEGIN;
UPDATE Item SET ItemTitle = Replace(ItemTitle, '$$dot$$', '.') WHERE ItemTitle LIKE '%$$dot$$%';
UPDATE ItemAttribute SET Value = Replace(Value, '$$dot$$', '.') WHERE AttributeID = 5 AND Value LIKE '%$$dot$$%';
COMMIT;
List items with duplicate URLs:
SELECT ItemID, Value FROM ItemAttribute WHERE AttributeID=996 AND UPPER(Value) IN (SELECT UPPER(Value) AS Val FROM ItemAttribute WHERE AttributeID=996 GROUP BY Val HAVING(COUNT(*) > 1)) ORDER BY UPPER(Value);
List items with duplicate titles:
SELECT ItemID, Value FROM ItemAttribute WHERE AttributeID=5 AND UPPER(Value) IN (SELECT UPPER(Value) AS Val FROM ItemAttribute WHERE AttributeID=5 GROUP BY Val HAVING(COUNT(*) > 1)) ORDER BY UPPER(Value);
Generate a complete list of user-defined keywords:
SELECT * FROM UserKeyword ORDER BY 1;
Rename a user-defined keyword:
BEGIN;
UPDATE UserKeyword SET Keyword='newword' WHERE Keyword LIKE 'oldword';
UPDATE ItemKeyword SET Keyword='newword' WHERE Keyword LIKE 'oldword' AND AttributeID=1;
COMMIT;
Delete all auto-generated keywords (if FTS is disabled):
BEGIN;
DELETE FROM ItemKeyword WHERE AttributeID <> 1;
UPDATE Item Set KeywordCount = (SELECT COUNT(*) FROM ItemKeyword IK WHERE IK.ItemID = Item.ItemID);
COMMIT;
Delete all auto-generated keywords (if FTS is enabled):
DELETE FROM ftsItem;
VACUUM;
Delete an item, its attributes, and all logical links from database:
DELETE FROM ItemLink WHERE ItemID = 9999;
Rename an icon:
UPDATE Icon SET FileExtension = ".xyz" WHERE FileExtension = "zxy.ico";
Replace an icon:
First, import an icon file (.ico) that will replace the old icon. Then run a SQL statement like this:
UPDATE Icon SET IconBlob = (SELECT IconBlob FROM Icon WHERE FileExtension = "new_icon.ico"), UncompressedSize = (SELECT UncompressedSize FROM Icon WHERE FileExtension = "new_icon.ico") WHERE FileExtension = "old_icon_name";
Then restart UR. The new icon file item can be deleted afterwards.
Make Message Date (read-only system attribute) user-creatable and editable:
UPDATE Attribute SET AllowUserCreate=1, ReadOnly=0 WHERE AttributeID=994;
Insert an item and create a logical link to the new task item under a given parent item in the tree:
SELECT IconID,FileExtension FROM Icon;
SELECT Max(RowID) FROM Item;
INSERT INTO Item
(ItemID, ItemTitle, TemplateItemID,IconID, System, CreateDate, TitleAttributeID)
VALUES(<maxid above+1>,"Task Title",4,996,0,getDate(),5);
Note: Replace 4 above with the ItemID of the template item (My Data -> Templates) to use and 996 with the IconID of the icon to use.
SELECT Max(RowID) FROM ItemLink;
INSERT INTO ItemLink (ItemLinkID,ItemID,ParentItemID) VALUES (<max link id above+1>,<max item id above+1>,<parent item id>);
Global Settings
Query all global settings:
SELECT GlobalDataID,Value FROM GlobalData;
IDs:
2 = last selected item IDs
3 = last selected search IDs
4 = last active root item
5 = last jump item IDs
6 = last tab info
7 = last hoist info
10 = lock title info
11 = show flagged items
12 = layout info
Query a specific global settings value
SELECT Value FROM GlobalData WHERE GlobalDataID = 6;
Update a specific global settings value
UPDATE GlobalData SET Value = '1003,1000' WHERE GlobalDataID = 6;