114 lines
4.0 KiB
Markdown
114 lines
4.0 KiB
Markdown
# Mailshot DDL Reference
|
|
|
|
Source: server from `credentials/app2.env` (`MYSQL_HOST=app2.lan`, `MYSQL_PORT=3308`)
|
|
Database: `members`
|
|
Discovery method: `SHOW CREATE TABLE` for `members.mailshot*`
|
|
|
|
## members.mailshots
|
|
|
|
```sql
|
|
CREATE TABLE `mailshots` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`Purpose` varchar(100) DEFAULT NULL,
|
|
`DataSource` varchar(100) NOT NULL,
|
|
`CC` varchar(100) DEFAULT NULL,
|
|
`BCC` varchar(100) DEFAULT NULL,
|
|
`Subject` longtext NOT NULL,
|
|
`Message` longtext NOT NULL,
|
|
`PDFAttachment` longtext DEFAULT NULL,
|
|
`PDFFilenameDerivedFrom` varchar(255) DEFAULT NULL,
|
|
`ThemeColourHex` varchar(7) DEFAULT NULL,
|
|
`AttachmentNames` longtext DEFAULT NULL,
|
|
`WantSubjectProcessed` tinyint(1) DEFAULT NULL,
|
|
`WantMessageProcessed` tinyint(1) DEFAULT NULL,
|
|
`ReplyTo` varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
```
|
|
|
|
## members.mailshot_attachments
|
|
|
|
```sql
|
|
CREATE TABLE `mailshot_attachments` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(100) NOT NULL,
|
|
`file_name` varchar(255) NOT NULL,
|
|
`mime_type` varchar(255) NOT NULL,
|
|
`file_bytes` longblob NOT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `mailshot_attachments_name_uk` (`name`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
```
|
|
|
|
## members.mailshot_last_run
|
|
|
|
```sql
|
|
CREATE TABLE `mailshot_last_run` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`mailshot_id` int(11) NOT NULL,
|
|
`data_source` varchar(100) DEFAULT NULL,
|
|
`row_index` int(11) NOT NULL DEFAULT 0,
|
|
`recipient_key` varchar(255) DEFAULT NULL,
|
|
`recipient_key_field` varchar(64) DEFAULT NULL,
|
|
`recipient_email_last` varchar(255) DEFAULT NULL,
|
|
`status` varchar(32) NOT NULL,
|
|
`error_message` longtext DEFAULT NULL,
|
|
`warning_message` longtext DEFAULT NULL,
|
|
`attempt_count` int(11) NOT NULL DEFAULT 1,
|
|
`run_started_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`last_attempt_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
KEY `mailshot_last_run_mailshot_idx` (`mailshot_id`),
|
|
KEY `mailshot_last_run_status_idx` (`status`),
|
|
KEY `mailshot_last_run_recipient_idx` (`recipient_key`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
```
|
|
|
|
## members.mailshot_pdf_assets
|
|
|
|
```sql
|
|
CREATE TABLE `mailshot_pdf_assets` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(100) NOT NULL,
|
|
`file_name` varchar(255) NOT NULL,
|
|
`mime_type` varchar(100) NOT NULL,
|
|
`file_bytes` longblob NOT NULL,
|
|
`width_mm` decimal(8,2) NOT NULL,
|
|
`height_mm` decimal(8,2) NOT NULL,
|
|
`justification` enum('left','right','in-place') NOT NULL DEFAULT 'in-place',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `mailshot_pdf_assets_name_uk` (`name`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
```
|
|
|
|
## members.mailshot_queries
|
|
|
|
```sql
|
|
CREATE TABLE `mailshot_queries` (
|
|
`ID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(100) NOT NULL,
|
|
`dsl_text` longtext DEFAULT NULL,
|
|
`dsl_version` varchar(16) NOT NULL DEFAULT 'v1',
|
|
`sql` longtext NOT NULL,
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`ID`),
|
|
UNIQUE KEY `mailshot_queries_unique` (`name`),
|
|
UNIQUE KEY `mailshot_queries_name_uk` (`name`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
```
|
|
|
|
## members.mailshot_test
|
|
|
|
```sql
|
|
CREATE TABLE `mailshot_test` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`Name` varchar(100) NOT NULL,
|
|
`Email` varchar(100) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
```
|