Accessing .urd files directly via SQLite

[kinook]

Ultra Recall databases are based on SQLite (http://sqlite.org).

A modified version of the SQLite console (http://www.sqlite.org/sqlite.html) is available at http://www.kinook.com/Download/Misc/SQLite3.zip, which can be used to access or update a UR database directly via SQLite. 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"

or if the database is encrypted:

Sqlite3.exe -key password "filename.urd"

and then enter SQL statements as needed (to run a statement, type it in and press Enter).

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\%';


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


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


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

(change the signature back afterwards to access the database from Ultra Recall again).