SQLize | PHPize | SQLtest

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE SCHEMA IF NOT EXISTS waybills; CREATE TYPE waybills.checkpoint_vehicle_type AS ENUM ('passenger', 'cargo', 'bus'); CREATE TABLE IF NOT EXISTS waybills.checkpoints ( id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, provider TEXT NOT NULL, external_id TEXT NOT NULL, external_name TEXT, lat DOUBLE PRECISION NOT NULL, lon DOUBlE PRECISION NOT NULL, vehicle_types waybills.checkpoint_vehicle_type[] NOT NULL, details JSONB NOT NULL, -- address, schedule, directions, etc is_deleted BOOLEAN NOT NULL DEFAULT FALSE, updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), increment BIGSERIAL NOT NULL ); -- indices CREATE UNIQUE INDEX IF NOT EXISTS idx__checkpoints_provider_external_id ON waybills.checkpoints (provider, external_id); CREATE INDEX IF NOT EXISTS idx__checkpoints_provider ON waybills.checkpoints (provider); CREATE INDEX IF NOT EXISTS idx__checkpoints_updated_at ON waybills.checkpoints (updated_at); CREATE INDEX IF NOT EXISTS idx__checkpoints_increment ON waybills.checkpoints (increment); -- triggers CREATE OR REPLACE FUNCTION set_update() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); NEW.increment = NEXTVAL(TG_ARGV[0]); RETURN NEW; END; $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS trigger_update ON waybills.checkpoints; CREATE TRIGGER trigger_update BEFORE UPDATE ON waybills.checkpoints FOR EACH ROW EXECUTE PROCEDURE set_update('waybills.checkpoints_increment_seq'); -- views DROP TYPE IF EXISTS waybills.provider_checkpoint_v1; CREATE TYPE waybills.provider_checkpoint_v1 AS ( external_id TEXT, external_name TEXT, lat DOUBLE PRECISION, lon DOUBLE PRECISION, vehicle_types waybills.CHECKPOINT_VEHICLE_TYPE[], details JSONB -- address, schedule, directions, etc ); DROP TYPE IF EXISTS waybills.checkpoint_v1; CREATE TYPE waybills.checkpoint_v1 AS ( id UUID, provider TEXT, external_id TEXT, external_name TEXT, lat DOUBLE PRECISION, lon DOUBLE PRECISION, vehicle_types waybills.CHECKPOINT_VEHICLE_TYPE[], details JSONB, -- address, schedule, directions, etc increment BIGINT, updated_at TIMESTAMPTZ, is_deleted BOOLEAN );