DatabaseCreation: load_seed.sql

File load_seed.sql, 7.5 KB (added by 235013, 11 days ago)
Line 
1\set ON_ERROR_STOP on
2\timing on
3
4-- The CSV directory is passed as a psql variable: psql -v csv_dir=/path/to/seed_data -f load_seed.sql
5-- \copy does not expand variables in its arguments, so psql changes into the directory instead.
6\cd :csv_dir
7
8-- Each BEGIN/COMMIT block commits on its own. After a failure, fix the cause,
9-- resume at the failed block and run every block after it, including the
10-- sequence resets; do not repeat the blocks that already committed.
11
12-- =============================================================================
13-- 1. LOOKUP TABLES
14-- =============================================================================
15
16\echo 'Loading lookup tables...'
17
18BEGIN;
19\copy Role (role_id, role_name, description) FROM 'role.csv' CSV HEADER;
20\copy Permission (permission_id, action_name) FROM 'permission.csv' CSV HEADER;
21\copy Industry (industry_id, industry_name) FROM 'industry.csv' CSV HEADER;
22\copy Project_Status (status_id, status_name) FROM 'project_status.csv' CSV HEADER;
23\copy Technology (technology_id, technology_name) FROM 'technology.csv' CSV HEADER;
24\copy Rating_Dimension (dimension_id, dimension_name, description) FROM 'rating_dimension.csv' CSV HEADER;
25\copy Subscription_Tier (tier_id, tier_name, list_price, allows_custom_pricing) FROM 'subscription_tier.csv' CSV HEADER;
26\copy Role_Permission (role_id, permission_id) FROM 'role_permission.csv' CSV HEADER;
27COMMIT;
28
29-- =============================================================================
30-- 2. CORE ENTITIES
31-- =============================================================================
32
33\echo 'Loading vendors and clients...'
34
35BEGIN;
36\copy Vendor (vendor_id, agency_name, website) FROM 'vendor.csv' CSV HEADER;
37\copy Client (client_id, industry_id, company_name, contact_email) FROM 'client.csv' CSV HEADER;
38COMMIT;
39
40\echo 'Loading users...'
41
42BEGIN;
43\copy "User" (user_id, type, first_name, last_name, email, password_hash, is_active, last_login_at, created_at, updated_at) FROM 'user.csv' CSV HEADER;
44\copy Client_User (user_id, client_id) FROM 'client_user.csv' CSV HEADER;
45\copy Vendor_User (user_id, vendor_id) FROM 'vendor_user.csv' CSV HEADER;
46\copy Management_User (user_id, role_id) FROM 'management_user.csv' CSV HEADER;
47COMMIT;
48
49-- =============================================================================
50-- 3. SUBSCRIPTIONS & CONTRACTS
51-- =============================================================================
52
53\echo 'Loading subscriptions...'
54
55BEGIN;
56\copy Vendor_Subscription (contract_id, vendor_id, tier_id, negotiated_price, start_date, end_date, is_active, created_at, updated_at) FROM 'vendor_subscription.csv' CSV HEADER;
57COMMIT;
58
59\echo 'Loading contracts...'
60
61BEGIN;
62\copy Client_Vendor_Contract (contract_id, client_id, vendor_id, contract_number, contract_title, start_date, end_date, total_value, currency_code, terms_summary, is_active, created_at, updated_at) FROM 'client_vendor_contract.csv' CSV HEADER;
63COMMIT;
64
65-- =============================================================================
66-- 4. PROJECTS
67-- =============================================================================
68
69\echo 'Loading projects...'
70
71BEGIN;
72\copy Project (project_id, contract_id, status_id, project_name, start_date, end_date, budget, created_at, updated_at) FROM 'project.csv' CSV HEADER;
73COMMIT;
74
75-- =============================================================================
76-- 5. PROJECT CHILD TABLES
77-- =============================================================================
78
79\echo 'Loading project_technology...'
80
81BEGIN;
82\copy Project_Technology (project_id, technology_id) FROM 'project_technology.csv' CSV HEADER;
83COMMIT;
84
85\echo 'Loading project_budget_audit...'
86
87BEGIN;
88\copy Project_Budget_Audit (audit_id, project_id, old_budget, new_budget, created_at, updated_at) FROM 'project_budget_audit.csv' CSV HEADER;
89COMMIT;
90
91\echo 'Loading project_status_history...'
92
93BEGIN;
94\copy Project_Status_History (project_status_history_id, project_id, vendor_user_id, management_user_id, status_id, changed_at, comment) FROM 'project_status_history.csv' CSV HEADER;
95COMMIT;
96
97-- =============================================================================
98-- 6. REVIEWS
99-- =============================================================================
100
101\echo 'Loading reviews...'
102
103BEGIN;
104\copy Review (review_id, project_id, client_user_id, review_date, summary_text, is_published, created_at, updated_at) FROM 'review.csv' CSV HEADER;
105COMMIT;
106
107\echo 'Loading review_score...'
108
109BEGIN;
110\copy Review_Score (review_id, dimension_id, score_value) FROM 'review_score.csv' CSV HEADER;
111COMMIT;
112
113\echo 'Loading dispute_ticket...'
114
115BEGIN;
116\copy Dispute_Ticket (ticket_id, assigned_management_user_id, review_id, vendor_user_id, reason, is_resolved, filed_at, resolved_at, resolution_note, created_at, updated_at) FROM 'dispute_ticket.csv' CSV HEADER;
117COMMIT;
118
119-- =============================================================================
120-- SEQUENCE RESETS
121-- =============================================================================
122
123\echo 'Resetting sequences...'
124
125BEGIN;
126SELECT setval(pg_get_serial_sequence('Role', 'role_id'),
127 (SELECT MAX(role_id) FROM Role));
128SELECT setval(pg_get_serial_sequence('Permission', 'permission_id'),
129 (SELECT MAX(permission_id) FROM Permission));
130SELECT setval(pg_get_serial_sequence('Industry', 'industry_id'),
131 (SELECT MAX(industry_id) FROM Industry));
132SELECT setval(pg_get_serial_sequence('Project_Status', 'status_id'),
133 (SELECT MAX(status_id) FROM Project_Status));
134SELECT setval(pg_get_serial_sequence('Technology', 'technology_id'),
135 (SELECT MAX(technology_id) FROM Technology));
136SELECT setval(pg_get_serial_sequence('Rating_Dimension', 'dimension_id'),
137 (SELECT MAX(dimension_id) FROM Rating_Dimension));
138SELECT setval(pg_get_serial_sequence('Subscription_Tier','tier_id'),
139 (SELECT MAX(tier_id) FROM Subscription_Tier));
140SELECT setval(pg_get_serial_sequence('Vendor', 'vendor_id'),
141 (SELECT MAX(vendor_id) FROM Vendor));
142SELECT setval(pg_get_serial_sequence('Client', 'client_id'),
143 (SELECT MAX(client_id) FROM Client));
144SELECT setval(pg_get_serial_sequence('"User"', 'user_id'),
145 (SELECT MAX(user_id) FROM "User"));
146SELECT setval(pg_get_serial_sequence('Vendor_Subscription', 'contract_id'),
147 (SELECT MAX(contract_id) FROM Vendor_Subscription));
148SELECT setval(pg_get_serial_sequence('Client_Vendor_Contract', 'contract_id'),
149 (SELECT MAX(contract_id) FROM Client_Vendor_Contract));
150SELECT setval(pg_get_serial_sequence('Project', 'project_id'),
151 (SELECT MAX(project_id) FROM Project));
152SELECT setval(pg_get_serial_sequence('Project_Budget_Audit', 'audit_id'),
153 (SELECT MAX(audit_id) FROM Project_Budget_Audit));
154SELECT setval(pg_get_serial_sequence('Project_Status_History',
155 'project_status_history_id'),
156 (SELECT MAX(project_status_history_id) FROM Project_Status_History));
157SELECT setval(pg_get_serial_sequence('Review', 'review_id'),
158 (SELECT MAX(review_id) FROM Review));
159SELECT setval(pg_get_serial_sequence('Dispute_Ticket', 'ticket_id'),
160 (SELECT MAX(ticket_id) FROM Dispute_Ticket));
161COMMIT;
162
163-- Refresh planner statistics after the bulk load.
164SET statement_timeout = 0;
165VACUUM (ANALYZE);
166
167\echo 'Done.'