Tabelas e diagramas SchemaSpy
Para saber mais sobre nossa estrutura de banco de dados, com suas tabelas e relacionamentos, visite o SuiteCRM Database Schema, gerado com SchemaSpy.
Princípios gerais e exemplos de consultas SQL
Identificadores únicos
SuiteCRM usa UUIDs para seus identificadores únicos. Eles têm a seguinte aparência: 46c35607-bcad-c7f1-1745-558d6b858b27. Eles podem ser gerados em SQL e inseridos em outras consultas como uma sub-seleção:
UPDATE some_table SET id=(SELECT uuid());
Historicamente, desde os dias do SugarCRM, esses ids seguiram este formato, mas na prática qualquer formato era permitido, numérico, string, etc., desde que fosse único. Isso às vezes facilita muito as importações, permitindo que os esquemas de numeração anteriores sejam mantidos.
Os campos personalizados
Os campos personalizados são salvos em tabelas com o mesmo nome do módulo em que são definidos, mas com um sufixo _cstm anexado. Cada campo personalizado terá o nome que você inseriu no Studio, com um sufixo _c. Aqui está um exemplo de consulta para unir a tabela de um módulo com alguns campos personalizados (neste caso, um campo chamado idade quando criado no Studio):
SELECT first_name, last_name, contacts_cstm.age_c
FROM contacts
LEFT JOIN contacts_cstm
ON contacts.id = contacts_cstm.id_c
Liste registros órfãos de contacts_cstm, onde o registro base não está presente, mas uma linha para ele ainda existe na tabela personalizada:
SELECT * — DELETE ChildTable
FROM contacts_cstm ChildTable
LEFT JOIN contacts ParentTable
ON ChildTable.id_c = ParentTable.id
WHERE ParentTable.id IS NULL
Essa consulta é um SELECT, mas se você alterar a primeira linha para o que está depois do comentário (-), ele excluirá essas linhas. Leia o aviso acima e decida com responsabilidade.
Relacionamentos
O SuiteCRM não depende do mecanismo de banco de dados para impor relacionamentos (restrições de chave estrangeira, etc.). Todas essas coisas são tratadas no nível do aplicativo em nosso próprio código PHP. Em geral, SuiteCRM também não faz nenhuma atualização ou exclusão em cascata, há apenas algumas exceções para alguns casos específicos. Isso significa que você pode precisar de alguma limpeza periódica do banco de dados.
Há um trabalho agendado “Prune banco de dados no primeiro dia do mês” que lida com algumas dessas tarefas, mas você precisa avaliar seu próprio caso para decidir sobre as tarefas de limpeza adequadas. Não existe uma solução “tamanho único” para isso, portanto, cada implementação deve seguir as etapas necessárias para seu próprio caso.
Existe uma tabela chamada relacionamentos que contém informações de metadados recuperadas dos vardefs.
Maneiras típicas de JOIN tabelas
Alguns exemplos de consultas devem ser suficientes para dar uma ideia de como as tabelas SuiteCRM normalmente se referem umas às outras:
1. A consulta para obter dados de um campo personalizado fornecido acima; Atravessar um campo Flex Relate onde vários tipos de registros conectados são permitidos.
2. O campo parent_type contém o nome do módulo relacionado, enquanto o parent_id é uma chave estrangeira para a tabela desse módulo
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
3. Uma consulta para percorrer um relacionamento muitos para muitos usando uma tabela intermediária. Neste exemplo, uma lista de nomes de contas e contatos vinculados:
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
Atravessar o relacionamento email_addresses para obter os endereços de e-mail dos usuários (facilmente adaptável para outros módulos, como Contatos, 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
Limpando relacionamentos com registros excluídos
Quando você apaga um registro de um módulo, em muitos casos o SuiteCRM não apaga todos os registros associados, porque é impossível decidir quais devem ou não ser removidos sem conhecer as especificidades de cada negócio. Não excluir é genericamente a abordagem sensata e conservadora. Mas se você decidir no seu caso que precisa remover algumas sobras de registros existentes anteriormente, o seguinte deve ajudá-lo.
Aqui está um exemplo de consulta para procurar registros órfãos, neste caso entradas de grupos de segurança que se referem a registros ausentes:
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’
Esta consulta pode ser facilmente transformada em um DELETE para remover esses registros.
Você também pode fazer uma exclusão mais simples de linhas onde excluído = ‘1’, onde o próprio relacionamento foi excluído, mesmo se o record_id ainda existir:
SELECT record_id, module, deleted
FROM securitygroups_records
WHERE module=’Contacts’ AND deleted=’1′
