Kinook Software Forum

Go Back   Kinook Software Forum > Ultra Recall > [UR] Frequently Asked Questions > Advanced
FAQ Community Calendar Today's Posts Search

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

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


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 07:58 PM.


Copyright © 1999-2023 Kinook Software, Inc.