1122 lines
25 KiB
PL/PgSQL
1122 lines
25 KiB
PL/PgSQL
--
|
||
-- PostgreSQL database cluster dump
|
||
--
|
||
|
||
\restrict TxL4kAbPon226DZYUHp47qFqdmHdahSXuQMSjIRM4KgpXPztB7SU5ZbX1Ho2uaN
|
||
|
||
SET default_transaction_read_only = off;
|
||
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
|
||
--
|
||
-- Roles
|
||
--
|
||
|
||
CREATE ROLE myuser;
|
||
ALTER ROLE myuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:lcasyFLTxsODZ2pTQzSfaQ==$Q9F2EsOXVyUo6hdnr4vPuErS4wNLYQ4aiszK/l4uar0=:ZOUai23wJ0E2uPl9ZVldTF4Kf6IduAMK+smPEs8LcjE=';
|
||
|
||
--
|
||
-- User Configurations
|
||
--
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
\unrestrict TxL4kAbPon226DZYUHp47qFqdmHdahSXuQMSjIRM4KgpXPztB7SU5ZbX1Ho2uaN
|
||
|
||
--
|
||
-- Databases
|
||
--
|
||
|
||
--
|
||
-- Database "template1" dump
|
||
--
|
||
|
||
\connect template1
|
||
|
||
--
|
||
-- PostgreSQL database dump
|
||
--
|
||
|
||
\restrict EVqvAcoaKMwbiBprCl9lPK9CA0vqbUHYB8u1EeX81QmawOQHBzt74khoAQIAcXl
|
||
|
||
-- Dumped from database version 16.11 (Debian 16.11-1.pgdg13+1)
|
||
-- Dumped by pg_dump version 16.11 (Debian 16.11-1.pgdg13+1)
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
--
|
||
-- PostgreSQL database dump complete
|
||
--
|
||
|
||
\unrestrict EVqvAcoaKMwbiBprCl9lPK9CA0vqbUHYB8u1EeX81QmawOQHBzt74khoAQIAcXl
|
||
|
||
--
|
||
-- Database "mydb" dump
|
||
--
|
||
|
||
--
|
||
-- PostgreSQL database dump
|
||
--
|
||
|
||
\restrict PWAkRltnIYXWYaZ8HdFgY7RYpugTOWwfVPdlgVRbvSNZ1QApQ7QMeVZLLtpz0QK
|
||
|
||
-- Dumped from database version 16.11 (Debian 16.11-1.pgdg13+1)
|
||
-- Dumped by pg_dump version 16.11 (Debian 16.11-1.pgdg13+1)
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
--
|
||
-- Name: mydb; Type: DATABASE; Schema: -; Owner: myuser
|
||
--
|
||
|
||
CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.utf8';
|
||
|
||
|
||
ALTER DATABASE mydb OWNER TO myuser;
|
||
|
||
\unrestrict PWAkRltnIYXWYaZ8HdFgY7RYpugTOWwfVPdlgVRbvSNZ1QApQ7QMeVZLLtpz0QK
|
||
\connect mydb
|
||
\restrict PWAkRltnIYXWYaZ8HdFgY7RYpugTOWwfVPdlgVRbvSNZ1QApQ7QMeVZLLtpz0QK
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
--
|
||
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
|
||
--
|
||
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
|
||
|
||
|
||
--
|
||
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner:
|
||
--
|
||
|
||
COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
|
||
|
||
|
||
--
|
||
-- Name: address; Type: TYPE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TYPE public.address AS (
|
||
street text,
|
||
city text,
|
||
zip text
|
||
);
|
||
|
||
|
||
ALTER TYPE public.address OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: trg_orders_after_delete_restore_stock(); Type: FUNCTION; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE FUNCTION public.trg_orders_after_delete_restore_stock() RETURNS trigger
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
UPDATE public.products
|
||
SET stock = stock + OLD.quantity,
|
||
updated_at = NOW()
|
||
WHERE id = OLD.product_id;
|
||
|
||
RETURN NULL;
|
||
END;
|
||
$$;
|
||
|
||
|
||
ALTER FUNCTION public.trg_orders_after_delete_restore_stock() OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: trg_orders_before_insert_decrement_stock(); Type: FUNCTION; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE FUNCTION public.trg_orders_before_insert_decrement_stock() RETURNS trigger
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
current_stock int;
|
||
BEGIN
|
||
SELECT stock INTO current_stock
|
||
FROM public.products
|
||
WHERE id = NEW.product_id
|
||
FOR UPDATE;
|
||
|
||
IF NOT FOUND THEN
|
||
RAISE EXCEPTION 'product % not found', NEW.product_id;
|
||
END IF;
|
||
|
||
IF current_stock < NEW.quantity THEN
|
||
RAISE EXCEPTION 'insufficient stock: have %, need %',
|
||
current_stock, NEW.quantity;
|
||
END IF;
|
||
|
||
UPDATE public.products
|
||
SET stock = stock - NEW.quantity,
|
||
updated_at = NOW()
|
||
WHERE id = NEW.product_id;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
|
||
ALTER FUNCTION public.trg_orders_before_insert_decrement_stock() OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: trg_orders_before_update_adjust_stock(); Type: FUNCTION; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE FUNCTION public.trg_orders_before_update_adjust_stock() RETURNS trigger
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
old_prod_stock int;
|
||
new_prod_stock int;
|
||
BEGIN
|
||
-- 沒變更商品也沒變更數量就略過
|
||
IF NEW.product_id = OLD.product_id AND NEW.quantity = OLD.quantity THEN
|
||
RETURN NEW;
|
||
END IF;
|
||
|
||
-- 先把舊商品庫存加回去
|
||
IF OLD.product_id IS NOT NULL THEN
|
||
SELECT stock INTO old_prod_stock
|
||
FROM public.products
|
||
WHERE id = OLD.product_id
|
||
FOR UPDATE;
|
||
|
||
IF NOT FOUND THEN
|
||
RAISE EXCEPTION 'old product % not found', OLD.product_id;
|
||
END IF;
|
||
|
||
UPDATE public.products
|
||
SET stock = stock + OLD.quantity,
|
||
updated_at = NOW()
|
||
WHERE id = OLD.product_id;
|
||
END IF;
|
||
|
||
-- 再扣新商品庫存
|
||
SELECT stock INTO new_prod_stock
|
||
FROM public.products
|
||
WHERE id = NEW.product_id
|
||
FOR UPDATE;
|
||
|
||
IF NOT FOUND THEN
|
||
RAISE EXCEPTION 'new product % not found', NEW.product_id;
|
||
END IF;
|
||
|
||
IF new_prod_stock < NEW.quantity THEN
|
||
RAISE EXCEPTION 'insufficient stock after update: have %, need %',
|
||
new_prod_stock, NEW.quantity;
|
||
END IF;
|
||
|
||
UPDATE public.products
|
||
SET stock = stock - NEW.quantity,
|
||
updated_at = NOW()
|
||
WHERE id = NEW.product_id;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
|
||
ALTER FUNCTION public.trg_orders_before_update_adjust_stock() OWNER TO myuser;
|
||
|
||
SET default_tablespace = '';
|
||
|
||
SET default_table_access_method = heap;
|
||
|
||
--
|
||
-- Name: company; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.company (
|
||
id integer NOT NULL,
|
||
name text NOT NULL,
|
||
location public.address
|
||
);
|
||
|
||
|
||
ALTER TABLE public.company OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: company_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.company_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.company_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: company_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.company_id_seq OWNED BY public.company.id;
|
||
|
||
|
||
--
|
||
-- Name: devices; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.devices (
|
||
id integer NOT NULL,
|
||
ip_address inet,
|
||
mac_address macaddr
|
||
);
|
||
|
||
|
||
ALTER TABLE public.devices OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: devices_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.devices_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.devices_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: devices_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.devices_id_seq OWNED BY public.devices.id;
|
||
|
||
|
||
--
|
||
-- Name: person; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.person (
|
||
id integer NOT NULL,
|
||
name text NOT NULL,
|
||
birthdate date
|
||
);
|
||
|
||
|
||
ALTER TABLE public.person OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: employee; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.employee (
|
||
employee_id integer NOT NULL,
|
||
department text
|
||
)
|
||
INHERITS (public.person);
|
||
|
||
|
||
ALTER TABLE public.employee OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: employee_employee_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.employee_employee_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.employee_employee_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: employee_employee_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.employee_employee_id_seq OWNED BY public.employee.employee_id;
|
||
|
||
|
||
--
|
||
-- Name: events; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.events (
|
||
id integer NOT NULL,
|
||
name text,
|
||
start_time timestamp without time zone,
|
||
start_time_tz timestamp with time zone
|
||
);
|
||
|
||
|
||
ALTER TABLE public.events OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: events_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.events_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.events_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: events_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.events_id_seq OWNED BY public.events.id;
|
||
|
||
|
||
--
|
||
-- Name: members; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.members (
|
||
id integer NOT NULL,
|
||
username character varying(50) NOT NULL,
|
||
email text,
|
||
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT members_email_check CHECK ((email ~~ '%@%'::text))
|
||
);
|
||
|
||
|
||
ALTER TABLE public.members OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: members_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.members_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.members_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: members_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.members_id_seq OWNED BY public.members.id;
|
||
|
||
|
||
--
|
||
-- Name: orders; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.orders (
|
||
id integer NOT NULL,
|
||
user_id integer NOT NULL,
|
||
product_id integer NOT NULL,
|
||
quantity integer DEFAULT 1 NOT NULL,
|
||
created_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
|
||
ALTER TABLE public.orders OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: TABLE orders; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON TABLE public.orders IS '訂單資料表，紀錄使用者購買的商品';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN orders.id; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.orders.id IS '訂單編號 (自動遞增)';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN orders.user_id; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.orders.user_id IS '下單使用者，對應 users.id';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN orders.product_id; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.orders.product_id IS '購買的商品，對應 products.id';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN orders.quantity; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.orders.quantity IS '購買數量';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN orders.created_at; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.orders.created_at IS '下單時間';
|
||
|
||
|
||
--
|
||
-- Name: orders_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.orders_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.orders_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: orders_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.orders_id_seq OWNED BY public.orders.id;
|
||
|
||
|
||
--
|
||
-- Name: person_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.person_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.person_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: person_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.person_id_seq OWNED BY public.person.id;
|
||
|
||
|
||
--
|
||
-- Name: products; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.products (
|
||
id integer NOT NULL,
|
||
name character varying(100) NOT NULL,
|
||
description text,
|
||
price numeric(10,2) NOT NULL,
|
||
stock integer DEFAULT 0,
|
||
created_at timestamp without time zone DEFAULT now(),
|
||
updated_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
|
||
ALTER TABLE public.products OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: TABLE products; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON TABLE public.products IS '商品資料表，存放產品基本資訊';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN products.id; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.products.id IS '商品編號 (自動遞增)';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN products.name; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.products.name IS '商品名稱';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN products.description; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.products.description IS '商品描述';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN products.price; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.products.price IS '商品價格，最多兩位小數';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN products.stock; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.products.stock IS '庫存數量';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN products.created_at; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.products.created_at IS '建立時間';
|
||
|
||
|
||
--
|
||
-- Name: products_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.products_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.products_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: products_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.products_id_seq OWNED BY public.products.id;
|
||
|
||
|
||
--
|
||
-- Name: users; Type: TABLE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TABLE public.users (
|
||
id integer NOT NULL,
|
||
username character varying(50) NOT NULL,
|
||
email character varying(100) NOT NULL,
|
||
password character varying(200) NOT NULL,
|
||
created_at timestamp without time zone DEFAULT now(),
|
||
updated_at timestamp without time zone DEFAULT now()
|
||
);
|
||
|
||
|
||
ALTER TABLE public.users OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: TABLE users; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON TABLE public.users IS '使用者資料表，儲存帳號與登入資訊';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN users.id; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.users.id IS '使用者編號 (自動遞增)';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN users.username; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.users.username IS '帳號名稱';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN users.email; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.users.email IS '使用者 Email，必須唯一';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN users.password; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.users.password IS '密碼 (通常存雜湊值)';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN users.created_at; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.users.created_at IS '建立時間';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN users.updated_at; Type: COMMENT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COMMENT ON COLUMN public.users.updated_at IS '更新時間';
|
||
|
||
|
||
--
|
||
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE SEQUENCE public.users_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER SEQUENCE public.users_id_seq OWNER TO myuser;
|
||
|
||
--
|
||
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;
|
||
|
||
|
||
--
|
||
-- Name: company id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.company ALTER COLUMN id SET DEFAULT nextval('public.company_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: devices id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.devices ALTER COLUMN id SET DEFAULT nextval('public.devices_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: employee id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.employee ALTER COLUMN id SET DEFAULT nextval('public.person_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: employee employee_id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.employee ALTER COLUMN employee_id SET DEFAULT nextval('public.employee_employee_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: events id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.events ALTER COLUMN id SET DEFAULT nextval('public.events_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: members id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.members ALTER COLUMN id SET DEFAULT nextval('public.members_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: orders id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.orders ALTER COLUMN id SET DEFAULT nextval('public.orders_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: person id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.person ALTER COLUMN id SET DEFAULT nextval('public.person_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: products id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.products ALTER COLUMN id SET DEFAULT nextval('public.products_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: users id; Type: DEFAULT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Data for Name: company; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.company (id, name, location) FROM stdin;
|
||
1 Masterwork Tech (中山路,台北市,100)
|
||
\.
|
||
|
||
|
||
--
|
||
-- Data for Name: devices; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.devices (id, ip_address, mac_address) FROM stdin;
|
||
1 192.168.1.100 08:00:2b:01:02:03
|
||
\.
|
||
|
||
|
||
--
|
||
-- Data for Name: employee; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.employee (id, name, birthdate, employee_id, department) FROM stdin;
|
||
\.
|
||
|
||
|
||
--
|
||
-- Data for Name: events; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.events (id, name, start_time, start_time_tz) FROM stdin;
|
||
1 Meeting 2025-08-22 14:00:00 2025-08-22 06:00:00+00
|
||
2 Conference 2025-08-22 09:00:00 2025-08-22 09:00:00+00
|
||
\.
|
||
|
||
|
||
--
|
||
-- Data for Name: members; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.members (id, username, email, created_at) FROM stdin;
|
||
1 timmy timmy@example.com 2025-08-22 15:05:39.776007
|
||
2 alice alice@newmail.com 2025-08-22 15:05:39.776007
|
||
7 charlie charlie@example.com 2025-08-22 15:08:36.916081
|
||
\.
|
||
|
||
|
||
--
|
||
-- Data for Name: orders; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.orders (id, user_id, product_id, quantity, created_at) FROM stdin;
|
||
2 1 4 2 2025-08-22 05:40:34.59583+00
|
||
3 2 2 1 2025-08-22 05:40:34.59583+00
|
||
4 3 3 1 2025-08-22 05:40:34.59583+00
|
||
5 3 5 1 2025-08-22 05:40:34.59583+00
|
||
7 1 4 2 2025-08-22 06:00:14.71732+00
|
||
8 1 4 2 2025-08-22 06:19:30.955247+00
|
||
\.
|
||
|
||
|
||
--
|
||
-- Data for Name: person; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.person (id, name, birthdate) FROM stdin;
|
||
\.
|
||
|
||
|
||
--
|
||
-- Data for Name: products; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.products (id, name, description, price, stock, created_at, updated_at) FROM stdin;
|
||
2 Galaxy S24 Samsung 旗艦手機 35900.00 30 2025-08-22 13:32:06.952067 2025-08-22 06:00:03.411145+00
|
||
3 MacBook Pro 14" 蘋果筆電 M3 晶片 69900.00 20 2025-08-22 13:32:06.952067 2025-08-22 06:00:03.411145+00
|
||
1 iPhone 15 Apple 最新款智慧型手機 38900.00 51 2025-08-22 13:32:06.952067 2025-08-22 06:09:18.833917+00
|
||
4 AirPods Pro 2 降噪藍牙耳機 7990.00 96 2025-08-22 13:32:06.952067 2025-08-22 06:20:25.194067+00
|
||
5 Nintendo Switch 2 新一代任天堂遊戲機 12990.00 40 2025-08-22 13:32:06.952067 2025-08-22 06:20:37.793073+00
|
||
\.
|
||
|
||
|
||
--
|
||
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: myuser
|
||
--
|
||
|
||
COPY public.users (id, username, email, password, created_at, updated_at) FROM stdin;
|
||
1 alice alice@example.com hashed_pwd_123 2025-08-22 13:31:53.543448 2025-08-22 13:31:53.543448
|
||
2 bob bob@example.com hashed_pwd_456 2025-08-22 13:31:53.543448 2025-08-22 13:31:53.543448
|
||
3 charlie charlie@example.com hashed_pwd_789 2025-08-22 13:31:53.543448 2025-08-22 13:31:53.543448
|
||
\.
|
||
|
||
|
||
--
|
||
-- Name: company_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.company_id_seq', 1, true);
|
||
|
||
|
||
--
|
||
-- Name: devices_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.devices_id_seq', 1, true);
|
||
|
||
|
||
--
|
||
-- Name: employee_employee_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.employee_employee_id_seq', 1, false);
|
||
|
||
|
||
--
|
||
-- Name: events_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.events_id_seq', 2, true);
|
||
|
||
|
||
--
|
||
-- Name: members_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.members_id_seq', 9, true);
|
||
|
||
|
||
--
|
||
-- Name: orders_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.orders_id_seq', 8, true);
|
||
|
||
|
||
--
|
||
-- Name: person_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.person_id_seq', 1, false);
|
||
|
||
|
||
--
|
||
-- Name: products_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.products_id_seq', 5, true);
|
||
|
||
|
||
--
|
||
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: myuser
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.users_id_seq', 3, true);
|
||
|
||
|
||
--
|
||
-- Name: company company_pkey; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.company
|
||
ADD CONSTRAINT company_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: devices devices_pkey; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.devices
|
||
ADD CONSTRAINT devices_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: events events_pkey; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.events
|
||
ADD CONSTRAINT events_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: members members_pkey; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.members
|
||
ADD CONSTRAINT members_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: members members_username_key; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.members
|
||
ADD CONSTRAINT members_username_key UNIQUE (username);
|
||
|
||
|
||
--
|
||
-- Name: orders orders_pkey; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.orders
|
||
ADD CONSTRAINT orders_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: person person_pkey; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.person
|
||
ADD CONSTRAINT person_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: products products_pkey; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.products
|
||
ADD CONSTRAINT products_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.users
|
||
ADD CONSTRAINT users_email_key UNIQUE (email);
|
||
|
||
|
||
--
|
||
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.users
|
||
ADD CONSTRAINT users_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: idx_members_username; Type: INDEX; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE INDEX idx_members_username ON public.members USING btree (username);
|
||
|
||
|
||
--
|
||
-- Name: orders orders_after_delete_restore_stock; Type: TRIGGER; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TRIGGER orders_after_delete_restore_stock AFTER DELETE ON public.orders FOR EACH ROW EXECUTE FUNCTION public.trg_orders_after_delete_restore_stock();
|
||
|
||
|
||
--
|
||
-- Name: orders orders_before_insert_decrement_stock; Type: TRIGGER; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TRIGGER orders_before_insert_decrement_stock BEFORE INSERT ON public.orders FOR EACH ROW EXECUTE FUNCTION public.trg_orders_before_insert_decrement_stock();
|
||
|
||
|
||
--
|
||
-- Name: orders orders_before_update_adjust_stock; Type: TRIGGER; Schema: public; Owner: myuser
|
||
--
|
||
|
||
CREATE TRIGGER orders_before_update_adjust_stock BEFORE UPDATE ON public.orders FOR EACH ROW EXECUTE FUNCTION public.trg_orders_before_update_adjust_stock();
|
||
|
||
|
||
--
|
||
-- Name: orders orders_product_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.orders
|
||
ADD CONSTRAINT orders_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id);
|
||
|
||
|
||
--
|
||
-- Name: orders orders_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: myuser
|
||
--
|
||
|
||
ALTER TABLE ONLY public.orders
|
||
ADD CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
|
||
|
||
|
||
--
|
||
-- PostgreSQL database dump complete
|
||
--
|
||
|
||
\unrestrict PWAkRltnIYXWYaZ8HdFgY7RYpugTOWwfVPdlgVRbvSNZ1QApQ7QMeVZLLtpz0QK
|
||
|
||
--
|
||
-- Database "postgres" dump
|
||
--
|
||
|
||
\connect postgres
|
||
|
||
--
|
||
-- PostgreSQL database dump
|
||
--
|
||
|
||
\restrict WfbybiJFGsdG2zcyldILapMIAlJzgVhbHJPgqdfWAHerWnwgRKsWY5Xy7dQwQ7X
|
||
|
||
-- Dumped from database version 16.11 (Debian 16.11-1.pgdg13+1)
|
||
-- Dumped by pg_dump version 16.11 (Debian 16.11-1.pgdg13+1)
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
--
|
||
-- PostgreSQL database dump complete
|
||
--
|
||
|
||
\unrestrict WfbybiJFGsdG2zcyldILapMIAlJzgVhbHJPgqdfWAHerWnwgRKsWY5Xy7dQwQ7X
|
||
|
||
--
|
||
-- PostgreSQL database cluster dump complete
|
||
--
|
||
|