45 lines
1.6 KiB
SQL
45 lines
1.6 KiB
SQL
DROP TABLE IF EXISTS subscription;
|
|
|
|
DROP SEQUENCE IF EXISTS subscription_seq;
|
|
CREATE SEQUENCE subscription_seq;
|
|
-- SELECT setval('subscription_seq', 0, true); -- last inserted id by sample data
|
|
|
|
CREATE TABLE "subscription" (
|
|
"subscription_id" int check ("subscription_status_id" > 0) NOT NULL DEFAULT NEXTVAL ('subscription_seq'),
|
|
"order_id" INT NOT NULL,
|
|
"order_product_id" INT NOT NULL,
|
|
"site_id" INT NOT NULL,
|
|
"user_id" INT NOT NULL,
|
|
"payment_address_id" INT NOT NULL,
|
|
"payment_method" text NOT NULL,
|
|
"shipping_address_id" INT NOT NULL,
|
|
"shipping_method" text NOT NULL,
|
|
"product_id" INT NOT NULL,
|
|
"quantity" INT NOT NULL,
|
|
"subscription_plan_id" INT NOT NULL,
|
|
"price" decimal(10,4) NOT NULL,
|
|
"period" TEXT CHECK( period IN ('day','week','month','year') ) NOT NULL DEFAULT 'month',
|
|
"cycle" smallint NOT NULL,
|
|
"length" smallint NOT NULL,
|
|
"left" smallint NOT NULL,
|
|
"trial_price" decimal(10,4) NOT NULL,
|
|
"trial_period" TEXT CHECK( trial_period IN ('day','week','month','year') ) NOT NULL DEFAULT 'month',
|
|
"trial_cycle" smallint NOT NULL,
|
|
"trial_length" smallint NOT NULL,
|
|
"trial_left" smallint NOT NULL,
|
|
"trial_status" smallint NOT NULL,
|
|
"date_next" timestamp(0) NOT NULL DEFAULT now(),
|
|
"note" text NOT NULL,
|
|
"subscription_status_id" INT NOT NULL,
|
|
"language_id" INT NOT NULL,
|
|
"currency_id" INT NOT NULL,
|
|
"ip" TEXT NOT NULL,
|
|
"forwarded_ip" TEXT NOT NULL,
|
|
"user_agent" TEXT NOT NULL,
|
|
"created_at" timestamp(0) NOT NULL DEFAULT now(),
|
|
"updated_at" timestamp(0) NOT NULL DEFAULT now(),
|
|
PRIMARY KEY ("subscription_id")
|
|
);
|
|
|
|
CREATE INDEX "subscription_order_id" ON "subscription" ("order_id");
|