SQL Training

Ben Horne

​

What is SQL?

• Structured Query Language
• It is how you communicate with a database
• Standard language for relational database management systems

SQL Operations

1. Create
2. Query
3. Insert
4. Update
5. Delete

SQL Create Database

• Database must be created before it can be used:

CREATE DATABASE [database];

• Manually, databases can be created using specific character sets, like Retain does:

CREATE DATABASE retain DEFAULT CHARACTER SET ‘utf8’ DEFAULT COLLATE ‘utf8_bin’;

• Databases consists of Tables which store data.

SQL Create Table

• Tables are where data is stored.
• Tables can be integrated or alone.
• Tables that are integrated have foreign keys.
• Foreign Keys are references to an ID from another table.
• How to create a table:
• CREATE TABLE [database].`[table_name]` ([column_name] [column’s datatype]([column length])) ENGINE = InnoDB ROW_FORMAT = DEFAULT;
• EX: CREATE TABLE db1.`table1` (Column1 VARCHAR(255)) ENGINE = InnoDB ROW_FORMAT = DEFAULT;

Each column’s properties needs to be defined, so the query is typically more complex than this example.

SQL Query

• 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];

​

SQL Clause Conditions

• Select statements can have conditional requirements.
• This is used with the WHERE clause.

SELECT * FROM [table1] WHERE [table1].[column1] = [table1].[column2];

• Can query from multiple tables.

SQL Insert

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

SQL Update

• 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.

SQL Delete

• 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.

Other SQL Operations

• Select Top
• Like
• Join
• Order by
• In
• Between
• Select Into
• Keys
• Primary - *All Tables should have one
• Foreign Key - references to id from another table
• Constraints
• Sub Selects

Retain and SQL

• Retain is broken into two “databases”:
• Configuration
• Message
• By default, this is a functional separation, not a logical separation… both co-exist in the same “retain” database.
• These databases can be merged into a single database or kept separate.

​

Retain Configuration Database

• Used for any configuration data
• Configuration Data is any of the following (there’s more, but these are the most common:
• Schedules
• Profiles
• Workers
• Jobs
• Users
• Groups
• Modules

Retain Message Database

• This is where archived data is recorded
• Message data can be any of the following (there are more, but these are some of the most common):
• Messages: folder, subject, dates, indexed, sender
• Attachments: name, document_id, size
• Recipients: link of recipient id
• Message Properties: Link to t_name, t_value id

Retain Message Database

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;

Retain Message Database

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 = [#]);

Retain Message Database

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.

Retain Message Database

• 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.

Conclusion

• Each Database Server has its own syntax
• MySQL, MS SQL, Oracle, Postgres
• There are slight deviations but the same query can usually be run against any database engine.
• Best practices:
• Triple check queries that modify data
• Backups are always recommended before modifying customer data
• http://www.w3schools.com/sql/default.asp
• Skillsoft.