Repository navigation
Expand file tree
/
Copy pathpostgres.sql
More file actions
228 lines (200 loc) · 7.62 KB
/
Copy pathpostgres.sql
File metadata and controls
228 lines (200 loc) · 7.62 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
-- Example PostgreSQL database schema with common features
CREATE DATABASE example_db;
\c example_db;
-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "hstore";
-- Custom types and domains
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
CREATE TYPE product_status AS ENUM ('draft', 'published', 'archived');
CREATE DOMAIN email_address AS VARCHAR(255)
CHECK (VALUE ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$');
-- User management tables
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email email_address NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
date_of_birth DATE,
status user_status DEFAULT 'active',
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP WITH TIME ZONE,
is_admin BOOLEAN DEFAULT FALSE,
metadata JSONB
);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_email ON users(email);
-- Product catalog
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
parent_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
level INTEGER GENERATED ALWAYS AS (
CASE
WHEN parent_id IS NULL THEN 0
ELSE 1 + (SELECT level FROM categories p WHERE p.id = categories.parent_id)
END
) STORED,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
category_id INTEGER NOT NULL REFERENCES categories(id),
name VARCHAR(200) NOT NULL,
description TEXT,
price NUMERIC(10,2) NOT NULL,
stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
status product_status DEFAULT 'draft',
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
metadata JSONB,
search_vector tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
setweight(to_tsvector('english', coalesce(description, '')), 'B')
) STORED
);
CREATE INDEX idx_products_search ON products USING GIN(search_vector);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_status ON products(status);
-- Order management
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
order_number VARCHAR(50) NOT NULL UNIQUE,
status order_status DEFAULT 'pending',
total_amount NUMERIC(12,2) NOT NULL,
shipping_address TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES orders(id),
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL,
total_price NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
-- Reviews and ratings
CREATE TABLE reviews (
id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(id),
user_id INTEGER NOT NULL REFERENCES users(id),
rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
title VARCHAR(200),
content TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
is_verified BOOLEAN DEFAULT FALSE,
UNIQUE (product_id, user_id)
);
CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_reviews_user ON reviews(user_id);
-- Functions
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE OR REPLACE FUNCTION calculate_product_rating(product_id_param INTEGER)
RETURNS NUMERIC AS $$
BEGIN
RETURN (
SELECT AVG(rating)::NUMERIC(3,2)
FROM reviews
WHERE product_id = product_id_param
);
END;
$$ LANGUAGE plpgsql;
-- Triggers
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
CREATE OR REPLACE FUNCTION generate_order_number()
RETURNS TRIGGER AS $$
BEGIN
NEW.order_number := 'ORD' || to_char(CURRENT_TIMESTAMP, 'YYYYMMDD') ||
lpad(nextval('orders_id_seq')::text, 4, '0');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION generate_order_number();
CREATE OR REPLACE FUNCTION update_product_stock()
RETURNS TRIGGER AS $$
BEGIN
UPDATE products
SET stock_quantity = stock_quantity - NEW.quantity
WHERE id = NEW.product_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER after_order_item_insert
AFTER INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_product_stock();
-- Sample data
INSERT INTO categories (name, description, parent_id) VALUES
('Electronics', 'Electronic devices and accessories', NULL),
('Computers', 'Desktop and laptop computers', 1),
('Smartphones', 'Mobile phones and accessories', 1),
('Books', 'Physical and digital books', NULL),
('Programming', 'Programming and technical books', 4);
INSERT INTO users (username, email, password_hash, first_name, last_name, status) VALUES
('john_doe', 'john@example.com', crypt('password123', gen_salt('bf')), 'John', 'Doe', 'active'),
('jane_smith', 'jane@example.com', crypt('password456', gen_salt('bf')), 'Jane', 'Smith', 'active');
INSERT INTO products (category_id, name, description, price, stock_quantity, status) VALUES
(2, 'Gaming Laptop', '15" Gaming Laptop with RTX 3080', 1499.99, 10, 'published'),
(3, 'Smartphone X', 'Latest smartphone with 5G support', 999.99, 20, 'published'),
(5, 'Python Programming', 'Complete guide to Python programming', 49.99, 100, 'published');
-- Views
CREATE OR REPLACE VIEW product_summary AS
SELECT
p.id,
p.name,
c.name AS category,
p.price,
p.stock_quantity,
COUNT(r.id) AS review_count,
COALESCE(AVG(r.rating)::NUMERIC(3,2), 0.00) AS avg_rating
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name, c.name, p.price, p.stock_quantity;
-- Materialized view for product statistics
CREATE MATERIALIZED VIEW product_stats AS
SELECT
p.category_id,
c.name AS category_name,
COUNT(p.id) AS product_count,
AVG(p.price)::NUMERIC(10,2) AS avg_price,
SUM(p.stock_quantity) AS total_stock,
COUNT(r.id) AS total_reviews,
COALESCE(AVG(r.rating)::NUMERIC(3,2), 0.00) AS avg_category_rating
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.category_id, c.name
WITH DATA;
CREATE UNIQUE INDEX idx_product_stats_category ON product_stats(category_id);