Difference between revisions of "Retain SQL Queries"
(Created page with "SQL Create Database Manually, databases can be created using specific character sets, like Retain does: CREATE DATABASE retain DEFAULT CHARACTER SET ‘utf8’ DEFAULT COLLAT...") |
Revision as of 16:44, 25 August 2016
SQL Create Database
Manually, databases can be created using specific character sets, like Retain does: CREATE DATABASE retain DEFAULT CHARACTER SET ‘utf8’ DEFAULT COLLATE ‘utf8_bin’;
List the databases:
SHOW DATABASES;
List the tables in the retain database: USE retain; (opens the retain database) SHOW TABLES;
Select statements get information from database: SELECT * FROM [table]; SELECT [column1],[column2] FROM [table];
Select statements can have conditional requirements. This is used with the WHERE clause. SELECT * FROM [table1] WHERE [table1].[column1] = [table1].[column2];
Add data to table:
INSERT INTO [table] VALUES ([data1],[data2],[etc]);
Can insert into specific columns of a database:
INSERT INTO [table] ([column1],[column2],[etc]) VALUES ([data1],[data2],[etc]);
Data needs to be updated: UPDATE [table] SET [column] = [new_data] WHERE [column with primary key] = [value]; For a valuable usage of this in Retain, see KB “How to Rebuild Indexes” Update should never be run without the WHERE clause unless you want to change all data for a given table, not usually recommended. This can be very dangerous if you are not careful. Always triple check what you are going to do. Make a database backup first if possible. Once the data has been changed, there is not an undo.
Data needs to be removed from database or table Entire tables can be deleted DROP TABLE [table]; Rows from tables can be deleted: DELETE FROM [table] WHERE [column with primary key] = [value]; Never run without where clause or all data in table will be deleted. Make a backup of database first if possible before deleting any data.
How to find all information about a message in Retain? Properties tab of a message has a Parent Node. This number is the message ID. To find all the message information about a message, there are multiple queries to run: Message: SELECT * FROM t_message WHERE message_id = [#]; Msg attachments: SELECT * FROM t_message_attachments WHERE message_id = [#]; Msg properties: SELECT * FROM t_message_properties WHERE message_id = [#]; To find a document on disk: (see KB http://support2.gwava.com/kb/?View=entry&EntryID=2420) : SELECT * FROM t_document WHERE document_id IN (SELECT document_id FROM t_message_attachments WHERE message_id = [parent node #]) \G;
To find all the recipients for a message: SELECT * FROM t_recipient WHERE recipient_id IN (SELECT recipient_id FROM t_message_recipients WHERE message_id = [#]);
How to find a messages for a hash on disk? Locate the *.dat file on disk to lookup. Queries to run to determine the message_id: SELECT * FROM t_document WHERE hash LIKE ‘%[hash]%’; omit the “.dat” - this extension is only used on disk and is not stored in the database SELECT * FROM t_message_attachments WHERE document_id = (SELECT document_id FROM t_document WHERE hash LIKE '%[hash]%'); SELECT * FROM t_message WHERE message_id IN (SELECT message_id FROM t_message_attachments WHERE document_id IN (SELECT document_id FROM t_document WHERE hash LIKE '%[hash]%')); That last query may return more results than you want. It returns all messages that point to that attachment.
Once we have a message_id, it is not entirely clear who is the owner of that message. Each message has a column called uuid_mapping_id. That id references the t_uuid_mapping table. This table just has a hash of a uuid Not very useful. It has to be linked to the t_abook table. SELECT * FROM t_abook WHERE f_uid LIKE (SELECT f_uuid FROM t_uuid_mapping WHERE uid_mapping_id = [#]); Now you can determine the owner of this message. Using the f_created value from t_message, you can now browse for this message in Retain, since messages are sorted by creation date.
