SQL

CREATE TABLE "contact" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT NOT NULL,
  "address" TEXT DEFAULT NULL,
  "created" INTEGER DEFAULT CURRENT_TIMESTAMP,
  "modified" INTEGER DEFAULT NULL
);
INSERT INTO "contact" (id,name,address,created,modified) VALUES ('1','Jon Doe','12 High Street,
12AB3 X21 City,
UK','1556105827','1556105956'), ('2','Markus Miller','70 Wide Square,
99AA1 ABC, City,
UK','1556105896','1556105961'), ('3','Peter Piper','1 Light Ave,
45EU2 2EA, City,
UK','1556105943','1556105966'), ('4','Max Mustermann','Musterstraße 1,
12345 Musterstadt,
Germany','1556106007',''), ('5','Louis Sousa','Av. da República 45,
1050-053 Lisbon,
Portugal','1556106231','1556107593'), ('6','Björn Lindblom','Fjällgatan 34,
413 17 Göteborg,
Sweden','1556106375',''), ('7','Santa Claus','North Pole','1556106400','');
CREATE TABLE "contact_field" (
  "id" INTEGER PRIMARY KEY,
  "contact_id" INTEGER NOT NULL REFERENCES "contact"("id") ON DELETE CASCADE,
  "field_id" INTEGER NOT NULL REFERENCES "field"("id") ON DELETE CASCADE,
  "value" TEXT DEFAULT '',
  UNIQUE ("contact_id", "field_id")
);
INSERT INTO "contact_field" (id,contact_id,field_id,VALUE) VALUES;
CREATE TABLE "contact_list" (
  "id" INTEGER PRIMARY KEY,
  "contact_id" INTEGER NOT NULL REFERENCES "contact"("id") ON DELETE CASCADE,
  "list_id" INTGER NOT NULL REFERENCES "list"("id") ON DELETE CASCADE,
  UNIQUE ("contact_id", "list_id")
);
INSERT INTO "contact_list" (id,contact_id,list_id) VALUES ('2','1','1'), ('1','2','1'), ('3','3','1'), ('4','4','1'), ('5','4','2'), ('7','5','2'), ('8','6','1'), ('9','6','2');
CREATE TABLE "field" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT NOT NULL UNIQUE,
  "type" TEXT DEFAULT 'text',
  "required" INTEGER DEFAULT 0,
  "rows" INTEGER DEFAULT 3,
  "order" INTEGER DEFAULT NULL
);
INSERT INTO "field" (id,name,TYPE,required,ROWS,ORDER) VALUES;
CREATE TABLE "job" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT NOT NULL,
  "orientation" TEXT DEFAULT 'h',
  "width" INTEGER NOT NULL,
  "height" INTEGER NOT NULL,
  "last_use" INTEGER DEFAULT NULL,
  "created" INTEGER DEFAULT CURRENT_TIMESTAMP,
  "modified" INTEGER DEFAULT NULL
);
INSERT INTO "job" (id,name,orientation,width,height,last_use,created,modified) VALUES ('1','Local Newsletter','L','229','114','1556106484','1556106432',''), ('2','Overseas Newsletter','L','229','114','1556106484','1556106444',''), ('3','Global Newsletter','L','229','114','1556106484','1556106453','');
CREATE TABLE "job_list" (
  "id" INTEGER PRIMARY KEY,
  "job_id" INTEGER NOT NULL REFERENCES "job"("id") ON DELETE CASCADE,
  "list_id" INTEGER NOT NULL REFERENCES "list"("id") ON DELETE CASCADE,
  UNIQUE ("job_id", "list_id")
);
INSERT INTO "job_list" (id,job_id,list_id) VALUES ('1','1','1'), ('2','2','2'), ('3','3','1'), ('4','3','2');
CREATE TABLE "list" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT NOT NULL UNIQUE,
  "order" INTEGER DEFAULT NULL
);
INSERT INTO "list" (id,name,ORDER) VALUES ('1','Newsletter','1'), ('2','Overseas','2');