Repository navigation
Expand file tree
/
Copy pathquick_setup.sql
More file actions
75 lines (66 loc) · 2.51 KB
/
Copy pathquick_setup.sql
File metadata and controls
75 lines (66 loc) · 2.51 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
-- =====================================================
-- QUICK SETUP SCRIPT FOR COURIER TRACKING APPLICATION
-- =====================================================
-- Step 0: Drop existing tables (if exist)
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE PARCEL_STATUS_HISTORY CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE PARCELS CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE CHECKPOINTS CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ROUTES CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE CUSTOMERS CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
-- Step 1: Create tables
CREATE TABLE CUSTOMERS (
CUSTOMER_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
NAME VARCHAR2(200) NOT NULL,
EMAIL VARCHAR2(200) UNIQUE,
PHONE VARCHAR2(50)
);
CREATE TABLE ROUTES (
ROUTE_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
NAME VARCHAR2(200) NOT NULL
);
CREATE TABLE CHECKPOINTS (
CHECKPOINT_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
ROUTE_ID NUMBER(19) NOT NULL,
SEQUENCE_NO NUMBER(10) NOT NULL,
LOCATION_NAME VARCHAR2(200) NOT NULL,
CONSTRAINT FK_CHECKPOINTS_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTES(ROUTE_ID)
);
CREATE TABLE PARCELS (
PARCEL_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
TRACKING_ID VARCHAR2(50) NOT NULL UNIQUE,
CUSTOMER_ID NUMBER(19) NOT NULL,
ORIGIN VARCHAR2(200) NOT NULL,
DESTINATION VARCHAR2(200) NOT NULL,
CREATED_AT TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT FK_PARCELS_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID)
);
CREATE TABLE PARCEL_STATUS_HISTORY (
HISTORY_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
PARCEL_ID NUMBER(19) NOT NULL,
CHECKPOINT_ID NUMBER(19) NOT NULL,
STATUS VARCHAR2(50) NOT NULL,
REMARKS VARCHAR2(400),
SCANNED_AT TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT FK_PSH_PARCEL FOREIGN KEY (PARCEL_ID) REFERENCES PARCELS(PARCEL_ID),
CONSTRAINT FK_PSH_CHECKPOINT FOREIGN KEY (CHECKPOINT_ID) REFERENCES CHECKPOINTS(CHECKPOINT_ID)
);
-- Step 2: Indexes
CREATE INDEX IDX_PARCELS_TRACKING ON PARCELS(TRACKING_ID);
CREATE INDEX IDX_PSH_PARCEL ON PARCEL_STATUS_HISTORY(PARCEL_ID);
COMMIT;
SELECT 'Quick setup completed successfully!' AS STATUS FROM DUAL;