43 lines
1.4 KiB
SQL
43 lines
1.4 KiB
SQL
-- SPDX-FileCopyrightText: 2023 Aravinth Manivannan <realaravinth@batsense.net>
|
|
--
|
|
-- SPDX-License-Identifier: AGPL-3.0-or-later
|
|
|
|
CREATE TABLE IF NOT EXISTS survey_mcaptcha_hostname (
|
|
url VARCHAR(3000) UNIQUE NOT NULL,
|
|
secret VARCHAR(100) UNIQUE NOT NULL,
|
|
ID SERIAL PRIMARY KEY NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS survey_mcaptcha_campaign (
|
|
campaign_id VARCHAR(100) NOT NULL,
|
|
public_id VARCHAR(100) NOT NULL,
|
|
url_id INTEGER NOT NULL references survey_mcaptcha_hostname(ID) ON DELETE CASCADE,
|
|
synced_till INTEGER NOT NULL DEFAULT 0,
|
|
ID SERIAL PRIMARY KEY NOT NULL
|
|
);
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS survey_mcaptcha_analytics (
|
|
campaign_id INTEGER references survey_mcaptcha_campaign(ID) ON DELETE CASCADE,
|
|
time INTEGER NOT NULL,
|
|
difficulty_factor INTEGER NOT NULL,
|
|
worker_type VARCHAR(100) NOT NULL,
|
|
ID SERIAL PRIMARY KEY NOT NULL
|
|
);
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS survey_mcaptcha_upload_job_states (
|
|
name VARCHAR(20) NOT NULL UNIQUE,
|
|
ID SERIAL PRIMARY KEY NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS survey_mcaptcha_upload_jobs (
|
|
campaign_id INTEGER references survey_mcaptcha_campaign(ID) ON DELETE CASCADE,
|
|
public_id varchar(100) NOT NULL UNIQUE,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
scheduled_at timestamptz DEFAULT NULL,
|
|
finished_at timestamptz DEFAULT NULL,
|
|
job_state INTEGER references survey_mcaptcha_upload_job_states(ID) ON DELETE CASCADE,
|
|
ID SERIAL PRIMARY KEY NOT NULL
|
|
);
|