Kinook Software Forums

Go Back   Kinook Software Forums > Ultra Recall > [UR] Frequently Asked Questions > Advanced

Reply
 
Thread Tools Rating: Thread Rating: 35 votes, 5.00 average. Display Modes
  #1  
Old 07-23-2007, 04:49 PM
kinook kinook is online now
Administrator
 
Join Date: 03-06-2001
Location: Colorado
Posts: 5,655
Accessing .urd files directly via SQLite

Ultra Recall databases are based on SQLite.

Note: Standard SQLite tools can also be used to query or modify .urd files. To do this, change the first 15 bytes of .urd file from 'Ultra Recall DB' to 'SQLite format 3' in a binary file editor.

As of Ultra Recall Pro v5.2.0.9, .urd files with the updated signature can also be opened by Ultra Recall.

A modified version of the SQLite console 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).

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


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
Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



All times are GMT -5. The time now is 02:46 PM.


Copyright 1999-2019 Kinook Software, Inc.