Accessing .urd files directly via SQLite

[kinook]

Ultra Recall databases are based on SQLite.

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 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/...mand-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;