source: server/db/data_load.sql@ ef1c1c7

main
Last change on this file since ef1c1c7 was ef1c1c7, checked in by Stefan <trsunovstefan@…>, 6 days ago

Wiki docs, phase 6 and phase 7 added
  • Property mode set to 100644
File size: 9.9 KB
Line 
1-- data_load.sql
2-- EduBerza - sample data
3-- Course: Databases 2025/2026 Winter, FINKI UKIM
4--
5-- Idempotent. Truncates all tables in the `project` schema and reloads
6-- deterministic sample data. Run schema_creation.sql first if tables do
7-- not yet exist.
8--
9-- All sample users have the password: test123
10--
11-- One transaction: the P7 checks in advanced_db.sql compare balances with
12-- the ledger at COMMIT, and the users are inserted with their balances
13-- before the deposit rows that back them. In an auto-commit client
14-- (DBeaver) every statement would otherwise be checked on its own.
15
16BEGIN;
17
18SET search_path TO project, public;
19
20TRUNCATE TABLE
21 project.watchlist_items,
22 project.watchlists,
23 project.market_candles,
24 project.market_trades,
25 project.transactions,
26 project.orders,
27 project.holdings,
28 project.markets,
29 project.crypto,
30 project.users
31RESTART IDENTITY CASCADE;
32
33-- ============================================================================
34-- CRYPTO
35-- ============================================================================
36INSERT INTO project.crypto (id, symbol, name) VALUES
37 ('11111111-1111-1111-1111-111111111111', 'BTC', 'Bitcoin'),
38 ('22222222-2222-2222-2222-222222222222', 'ETH', 'Ethereum'),
39 ('33333333-3333-3333-3333-333333333333', 'ADA', 'Cardano'),
40 ('44444444-4444-4444-4444-444444444444', 'SOL', 'Solana'),
41 ('55555555-5555-5555-5555-555555555555', 'DOGE', 'Dogecoin');
42
43-- ============================================================================
44-- MARKETS (all quoted in USD)
45-- ============================================================================
46INSERT INTO project.markets (id, crypto_id, quote_currency, is_active) VALUES
47 ('a1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', 'USD', true),
48 ('a2222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222', 'USD', true),
49 ('a3333333-3333-3333-3333-333333333333', '33333333-3333-3333-3333-333333333333', 'USD', true),
50 ('a4444444-4444-4444-4444-444444444444', '44444444-4444-4444-4444-444444444444', 'USD', true),
51 ('a5555555-5555-5555-5555-555555555555', '55555555-5555-5555-5555-555555555555', 'USD', true);
52
53-- ============================================================================
54-- USERS
55-- Password for all: test123 (stored as sha256 hex hash)
56-- ============================================================================
57INSERT INTO project.users (id, username, email, full_name, password_hash, available_balance, invested_balance) VALUES
58 ('b1111111-1111-1111-1111-111111111111', 'alice', 'alice@example.com', 'Alice Johnson',
59 encode(digest('test123', 'sha256'), 'hex'), 10000.0000, 0),
60 ('b2222222-2222-2222-2222-222222222222', 'bob', 'bob@example.com', 'Bob Smith',
61 encode(digest('test123', 'sha256'), 'hex'), 5000.0000, 0),
62 ('b3333333-3333-3333-3333-333333333333', 'charlie', 'charlie@example.com', 'Charlie Davis',
63 encode(digest('test123', 'sha256'), 'hex'), 2500.0000, 0);
64
65-- ============================================================================
66-- MARKET TRADES
67-- Recent simulated trades per market, used as price source.
68-- ============================================================================
69INSERT INTO project.market_trades (market_id, executed_at, price, quantity, side, source) VALUES
70 -- BTC/USD around $67,000
71 ('a1111111-1111-1111-1111-111111111111', now() - interval '10 min', 66850.250000, 0.120000, 'buy', 'simulation'),
72 ('a1111111-1111-1111-1111-111111111111', now() - interval '8 min', 66910.500000, 0.075000, 'sell', 'simulation'),
73 ('a1111111-1111-1111-1111-111111111111', now() - interval '5 min', 67020.750000, 0.200000, 'buy', 'simulation'),
74 ('a1111111-1111-1111-1111-111111111111', now() - interval '2 min', 67105.100000, 0.050000, 'buy', 'simulation'),
75 ('a1111111-1111-1111-1111-111111111111', now() - interval '30 second', 67140.000000, 0.030000, 'sell', 'simulation'),
76 -- ETH/USD around $3,500
77 ('a2222222-2222-2222-2222-222222222222', now() - interval '10 min', 3490.500000, 1.500000, 'buy', 'simulation'),
78 ('a2222222-2222-2222-2222-222222222222', now() - interval '6 min', 3502.750000, 0.800000, 'sell', 'simulation'),
79 ('a2222222-2222-2222-2222-222222222222', now() - interval '2 min', 3515.250000, 2.100000, 'buy', 'simulation'),
80 ('a2222222-2222-2222-2222-222222222222', now() - interval '30 second', 3520.000000, 0.650000, 'buy', 'simulation'),
81 -- ADA/USD around $0.45
82 ('a3333333-3333-3333-3333-333333333333', now() - interval '10 min', 0.446500, 500.000000, 'buy', 'simulation'),
83 ('a3333333-3333-3333-3333-333333333333', now() - interval '3 min', 0.452000, 1200.000000, 'buy', 'simulation'),
84 ('a3333333-3333-3333-3333-333333333333', now() - interval '30 second', 0.453750, 800.000000, 'sell', 'simulation'),
85 -- SOL/USD around $165
86 ('a4444444-4444-4444-4444-444444444444', now() - interval '10 min', 164.250000, 10.000000, 'buy', 'simulation'),
87 ('a4444444-4444-4444-4444-444444444444', now() - interval '4 min', 165.500000, 5.500000, 'sell', 'simulation'),
88 ('a4444444-4444-4444-4444-444444444444', now() - interval '30 second', 166.100000, 8.000000, 'buy', 'simulation'),
89 -- DOGE/USD around $0.12
90 ('a5555555-5555-5555-5555-555555555555', now() - interval '10 min', 0.118500, 10000.000000, 'buy', 'simulation'),
91 ('a5555555-5555-5555-5555-555555555555', now() - interval '3 min', 0.121250, 7500.000000, 'sell', 'simulation'),
92 ('a5555555-5555-5555-5555-555555555555', now() - interval '30 second', 0.122000, 12000.000000, 'buy', 'simulation');
93
94-- ============================================================================
95-- MARKET CANDLES (1h aggregates, last 5 hours per market)
96-- ============================================================================
97INSERT INTO project.market_candles (market_id, timeframe, open, high, low, close, volume, candle_time) VALUES
98 ('a1111111-1111-1111-1111-111111111111', '1h', 66200, 66500, 66050, 66400, 12.50, date_trunc('hour', now() - interval '5 hour')),
99 ('a1111111-1111-1111-1111-111111111111', '1h', 66400, 66800, 66380, 66700, 15.30, date_trunc('hour', now() - interval '4 hour')),
100 ('a1111111-1111-1111-1111-111111111111', '1h', 66700, 66950, 66650, 66900, 11.80, date_trunc('hour', now() - interval '3 hour')),
101 ('a1111111-1111-1111-1111-111111111111', '1h', 66900, 67100, 66800, 67050, 14.20, date_trunc('hour', now() - interval '2 hour')),
102 ('a1111111-1111-1111-1111-111111111111', '1h', 67050, 67200, 66900, 67140, 10.75, date_trunc('hour', now() - interval '1 hour')),
103 ('a2222222-2222-2222-2222-222222222222', '1h', 3460, 3490, 3450, 3485, 120.0, date_trunc('hour', now() - interval '5 hour')),
104 ('a2222222-2222-2222-2222-222222222222', '1h', 3485, 3510, 3480, 3500, 135.0, date_trunc('hour', now() - interval '4 hour')),
105 ('a2222222-2222-2222-2222-222222222222', '1h', 3500, 3520, 3495, 3515, 110.0, date_trunc('hour', now() - interval '3 hour')),
106 ('a2222222-2222-2222-2222-222222222222', '1h', 3515, 3525, 3500, 3520, 125.5, date_trunc('hour', now() - interval '2 hour')),
107 ('a2222222-2222-2222-2222-222222222222', '1h', 3520, 3530, 3510, 3520, 140.0, date_trunc('hour', now() - interval '1 hour'));
108
109-- ============================================================================
110-- EXAMPLE ORDERS, HOLDINGS AND TRANSACTIONS for alice
111-- Shows a fully-filled market buy and its resulting holding & ledger entry.
112-- ============================================================================
113-- Imported as already completely filled (filled_quantity = quantity).
114INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, filled_quantity, price, placed_at, executed_at) VALUES
115 ('c1111111-1111-1111-1111-111111111111',
116 'b1111111-1111-1111-1111-111111111111',
117 'a2222222-2222-2222-2222-222222222222',
118 'buy', 'market', 'executed', 0.5000, 0.5000, 3500.000000,
119 now() - interval '1 hour', now() - interval '1 hour');
120
121INSERT INTO project.holdings (user_id, crypto_id, quantity, avg_price, updated_at) VALUES
122 ('b1111111-1111-1111-1111-111111111111',
123 '22222222-2222-2222-2222-222222222222',
124 0.5000, 3500.000000, now() - interval '1 hour');
125
126INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) VALUES
127 ('b1111111-1111-1111-1111-111111111111', 'deposit', 10000.0000, 'USD', NULL,
128 'Initial virtual deposit'),
129 ('b2222222-2222-2222-2222-222222222222', 'deposit', 5000.0000, 'USD', NULL,
130 'Initial virtual deposit'),
131 ('b3333333-3333-3333-3333-333333333333', 'deposit', 2500.0000, 'USD', NULL,
132 'Initial virtual deposit'),
133 ('b1111111-1111-1111-1111-111111111111', 'buy', -1750.0000, 'USD',
134 'c1111111-1111-1111-1111-111111111111',
135 'Market buy 0.5 ETH @ 3500.00');
136
137-- After the buy, alice's invested_balance reflects the used funds.
138UPDATE project.users
139 SET available_balance = 10000.0000 - 1750.0000,
140 invested_balance = 1750.0000,
141 updated_at = now()
142 WHERE id = 'b1111111-1111-1111-1111-111111111111';
143
144-- ============================================================================
145-- WATCHLISTS
146-- ============================================================================
147INSERT INTO project.watchlists (id, user_id, name) VALUES
148 ('d1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111', 'Favorites'),
149 ('d2222222-2222-2222-2222-222222222222', 'b2222222-2222-2222-2222-222222222222', 'Bobs Picks');
150
151INSERT INTO project.watchlist_items (watchlist_id, crypto_id) VALUES
152 ('d1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111'),
153 ('d1111111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222'),
154 ('d1111111-1111-1111-1111-111111111111', '44444444-4444-4444-4444-444444444444'),
155 ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'),
156 ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555');
157
158COMMIT;
Note: See TracBrowser for help on using the repository browser.