Retain SQL Queries

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.

http://www.w3schools.com/sql/default.asp http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

For more detail with archive times SELECT abook_id, f_email, from_unixtime(ts_cache) AS 'Last Cached From Mail Server', from_unixtime(ts_store) AS 'Most recently Accessed', from_unixtime(ts_item) AS 'Item Store Flag' FROM t_abook GROUP BY abook_id;

that should give you a user list with their last cached from the mail server, Most recently Archived date, and Item store date. Wrapped up in a date rather than the unix timestamp. I can make it unix timestamp for you if you like.

For the amount of disk space a user is using. If you total it all up it will be more than what is in the archive because this is counting each message each user is using. Retain impliments single instance sotrage so duplicates are stored only once. This is a resource intensive query. On my system, I have 108 users and 7.4 million items and it took 8 minutes. But it will tell you everything currently in the system. You are not the first to ask for it but you were the first to notice that the report is not returning what you need. So we are creating an enhancement request for this.

SELECT f_email, COUNT(*),SUM(f_size) AS 'Total Storage Use(in Bytes)' FROM t_message_attachments INNER JOIN t_message ON t_message_attachments.message_id = t_message.message_id INNER JOIN t_uuid_mapping ON t_message.uuid_mapping_id = t_uuid_mapping.uid_mapping_id INNER JOIN t_abook ON t_abook.f_uid = t_uuid_mapping.f_uuid GROUP BY abook_id;

To see a count on the number of messages for each index value in the database, run this query (see "How to Login to Your Retain MySQL Database and Run Queries"):

select f_indexed, count(*) from t_message group by f_indexed;