UPDATE some_table SET id=(SELECT uuid());
To learn about our database structure, with its tables and relationships, please visit the SuiteCRM Database Schema, generated with SchemaSpy.
Some of the queries provided below delete data from your database. Use at your own risk, and be sure to create full backups before trying them. A single mistaken SQL query can produce irreparable damage to your database.
SuiteCRM uses UUID’s for its unique identifiers. They look like this:
They can be generated in SQL and inserted into other queries as a sub-select:
UPDATE some_table SET id=(SELECT uuid());
Historically, since the SugarCRM days, these id’s have followed this format, but in practice any format was allowed, numeric, string, etc., as long as it was unique. This sometimes greatly facilitates imports by allowing previous numbering schemes to be maintained.
Custom fields get saved in tables with the same name as the module they are defined in, but with an
Each custom field will have the name you entered for it in Studio, with a suffix
Here is a sample query to join a module’s table with some custom fields (in this case, a field called age when created in Studio):
SELECT first_name, last_name, contacts_cstm.age_c FROM `contacts` LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c
List orphaned records from
contacts_cstm, where the base record is not present, but a row for it still
exists in the custom table:
SELECT * -- DELETE ChildTable FROM contacts_cstm ChildTable LEFT JOIN contacts ParentTable ON ChildTable.id_c = ParentTable.id WHERE ParentTable.id IS NULL
That query is a SELECT, but if you change the first line to what is after the comment (
it will delete those rows. Please read the warning above and decide responsibly.
SuiteCRM does not rely on the database engine to enforce relationships (foreign key constraints, etc). All these things are handled at application level in our own PHP code.
In general, SuiteCRM also does not do any cascaded updates or cascaded deletes, there are only a few exceptions for some particular cases. This means that you might need some periodic database clean-up. There is a "Prune database on the 1st of month" scheduled job that handles some of these tasks, but you need to evaluate your own case in order to decide on the appropriate house-cleaning tasks. There is no "one-size-fits-all" solution for this, so each implementation should take the necessary steps for its own case.
There is a table called
relationships which contains metadata information retrieved from the vardefs.
A few sample queries should suffice to give you an idea of how SuiteCRM tables typically reference each other:
The query to get data from a custom field that was given above;
Traversing a Flex Relate field where several connected record types are allowed. The
field contains the name of the related module, while the
parent_id is a foreign key into that module’s table:
SELECT accounts.name, calls.name, calls.status FROM accounts INNER JOIN calls ON calls.parent_type = 'Accounts' AND calls.parent_id = accounts.id AND calls.deleted = 0 WHERE accounts.deleted = 0
A query to traverse a many-to-many relationship using a middle table. In this example, a list of account names and linked contacts:
SELECT accounts.name, contacts.first_name, contacts.last_name FROM accounts INNER JOIN accounts_contacts ON (accounts.id = accounts_contacts.account_id AND accounts_contacts.deleted = 0) INNER JOIN contacts ON (contacts.id = accounts_contacts.contact_id AND contacts.deleted = 0) WHERE accounts.deleted = 0 ORDER BY accounts.name
email_addresses relationship to get the email addresses of Users (easily adaptable for
other modules, like Contacts, Leads, etc).
SELECT users.user_name, email_address FROM users LEFT JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id=users.id AND email_addr_bean_rel.bean_module = 'Users' AND email_addr_bean_rel.primary_address = 1 AND email_addr_bean_rel.deleted = 0 LEFT JOIN email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id AND email_addresses.deleted = 0
When you delete a record from a module, in many cases SuiteCRM does not delete all the associated records, because it is impossible to decide which should or should not be removed without knowing the specifics of each business. Not deleting is generically the sensible, conservative approach. But if you decide in your case you need to remove some left-overs from previously existing records, then the following should help you.
Here is a sample query to look for orphaned records, in this case security groups entries referring to missing records:
SELECT record_id, module, s.deleted, c.last_name, c.deleted FROM securitygroups_records s LEFT JOIN contacts c ON s.record_id = c.id WHERE c.id IS NULL AND s.module='Contacts'
This query can easily be turned into a DELETE in order to remove these records.
You might also make a simpler delete of rows where
deleted='1', where the relationship itself was deleted,
even if the
record_id still exists:
SELECT record_id, module, deleted FROM securitygroups_records WHERE module='Contacts' AND deleted='1'
Content is available under GNU Free Documentation License 1.3 or later unless otherwise noted.