Direct Database Access
- Table of Contents
- Mailing Lists
We do not support writing to the database because it is possible to get the data into an invalid state by using raw CREATE/INSERT/UPDATE commands against this database. Instead, use the API. When using the API, validation of the data is enforced and sometimes additional actions (such as updating dependent records) are performed. Modifying the data or schema in the database through direct access is an unauthorized modification of the software and may invalidate your support.
We do, however, realize that some on-premises customers are going to want to perform read-only access on the database. Contact GreenArrow technical support to have a read-only account set up for you.
The database behind GreenArrow Studio is complex and we do not provide full documentation of the schema. However, we do provide some limited and partial documentation to point those who are going to access the database directly in the right direction.
The database schema itself and the presence of documentation on the schema are subject to change without notice.
Mailing lists are stored in the
To list all non-deleted mailing lists, run this query:
SELECT id, name FROM s_mailing_lists WHERE active;
Larger mailing lists will have their own subscriber tables. This speeds database access when a scan of all subscribers in a mailing list is required.
To find the table containing the subscribers for a mailing list, look at the
- If this is null, then the subscribers are in
- If this is not null, then the subscribers are in the table named by the contents of that column. This table will have have the same schema as
As the number of subscribers in a mailing list grows, the subscribers may be moved to their own table.
Here are two example queries that search for a subscriber by email address in a particular mailing list:
SELECT id, email FROM s_subscribers_99999 WHERE mailing_list_id = 999999 AND LOWER(email) = LOWER('[email protected]')
SELECT id, email FROM s_subscribers WHERE mailing_list_id = 999999 AND LOWER(email) = LOWER('[email protected]')
To search for a subscriber in any mailing list, query the
s_subscriber_statuses table as follows:
SELECT * FROM s_subscriber_statuses WHERE LOWER(email) = LOWER('[email protected]')
Each subscriber has an entry in its mailing list’s table and in the
s_subscriber_statuses table. This allows for quick searching of a subscriber by email address without knowing what mailing list or table it is in.
s_pending_subscribers table contains subscription requests pending confirmation. This is used as part of a confirmed-opt-in process.