aboutsummaryrefslogtreecommitdiff
path: root/res/sql
diff options
context:
space:
mode:
Diffstat (limited to 'res/sql')
-rw-r--r--res/sql/data/orcl_bids.sql12
-rw-r--r--res/sql/data/orcl_charges.sql6
-rw-r--r--res/sql/data/orcl_items.sql10
-rw-r--r--res/sql/data/orcl_transactions.sql38
-rw-r--r--res/sql/data/orcl_users.sql13
-rw-r--r--res/sql/data/pg_bids.sql9
-rw-r--r--res/sql/data/pg_charges.sql4
-rw-r--r--res/sql/data/pg_items.sql6
-rw-r--r--res/sql/data/pg_transactions.sql38
-rw-r--r--res/sql/data/pg_users.sql12
-rw-r--r--res/sql/functions/orcl_current_datetime.sql10
-rw-r--r--res/sql/functions/orcl_gen_uuid.sql11
-rw-r--r--res/sql/functions/pg_current_datetime.sql10
-rw-r--r--res/sql/functions/pg_gen_uuid.sql9
-rw-r--r--res/sql/orcl_all.sql301
-rw-r--r--res/sql/pg_all.sql265
-rw-r--r--res/sql/tables/orcl_add_constraints_defaults.sql36
-rw-r--r--res/sql/tables/orcl_ddl.sql113
-rw-r--r--res/sql/tables/pg_add_constraints_defaults.sql26
-rw-r--r--res/sql/tables/pg_ddl.sql113
-rw-r--r--res/sql/views/orcl_accounts.sql31
-rw-r--r--res/sql/views/orcl_sales.sql49
-rw-r--r--res/sql/views/pg_accounts.sql31
-rw-r--r--res/sql/views/pg_sales.sql32
24 files changed, 1185 insertions, 0 deletions
diff --git a/res/sql/data/orcl_bids.sql b/res/sql/data/orcl_bids.sql
new file mode 100644
index 0000000..c46eeef
--- /dev/null
+++ b/res/sql/data/orcl_bids.sql
@@ -0,0 +1,12 @@
1INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
2 ('0CA2E8F1-D1F1-49B7-E050-D7936EBE6A05', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
3 '0C98CCCC-219B-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2013-05-05 23:48:16.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
4 26000.00);
5INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
6 ('0CA2E8F1-D1F2-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
7 '0C98CCCC-219B-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2012-05-03 23:50:05.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
8 25000.00);
9INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
10 ('0CA2E8F1-D1F3-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
11 '0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2012-06-01 23:50:53.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
12 12900.00);
diff --git a/res/sql/data/orcl_charges.sql b/res/sql/data/orcl_charges.sql
new file mode 100644
index 0000000..bb8d99d
--- /dev/null
+++ b/res/sql/data/orcl_charges.sql
@@ -0,0 +1,6 @@
1INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) VALUES
2 ('0CA2E8F1-D1EF-49B7-E050-D7936EBE6A05', TO_TIMESTAMP_TZ('2013-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
3 TO_TIMESTAMP_TZ('2013-12-31 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 4.00, 0.25);
4INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) VALUES
5 ('0CA2E8F1-D1F0-49B7-E050-D7936EBE6A05', TO_TIMESTAMP_TZ('2014-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
6 TO_TIMESTAMP_TZ('2014-12-31 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 4.20, 0.27);
diff --git a/res/sql/data/orcl_items.sql b/res/sql/data/orcl_items.sql
new file mode 100644
index 0000000..ab17157
--- /dev/null
+++ b/res/sql/data/orcl_items.sql
@@ -0,0 +1,10 @@
1INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES
2 ('0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
3 TO_TIMESTAMP_TZ('2013-03-15 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
4 TO_TIMESTAMP_TZ('2013-07-01 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Audi A3', 'Voiture',
5 'Bonne voiture bon état pas cher', 12900.00);
6INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES
7 ('0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '0C99BEF9-3760-3E4A-E050-D7936EBE2782',
8 TO_TIMESTAMP_TZ('2013-04-25 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
9 TO_TIMESTAMP_TZ('2013-05-25 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Harley-Davidson', 'Moto',
10 'Alors une moto ça roule sur deux roues.', 25000.00);
diff --git a/res/sql/data/orcl_transactions.sql b/res/sql/data/orcl_transactions.sql
new file mode 100644
index 0000000..fe52e15
--- /dev/null
+++ b/res/sql/data/orcl_transactions.sql
@@ -0,0 +1,38 @@
1INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
2 ('0C98CCCC-219C-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
3 TO_TIMESTAMP_TZ('2013-01-01 08:39:34.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 18000.00, 'VIR');
4INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
5 ('0C98CCCC-219D-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
6 TO_TIMESTAMP_TZ('2013-05-02 08:47:26.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 9500.00, 'VIR');
7INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
8 ('0C98CCCC-219F-C9B1-E050-D7936EBE02BF', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
9 TO_TIMESTAMP_TZ('2013-03-01 08:50:15.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 30000.00, 'VIR');
10
11
12-- transactions for sales
13
14INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
15 ('0DA506A8-8E2F-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
16 TO_TIMESTAMP_TZ('2013-05-26 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), - 26000.00,
17 'PURCHASE 0C98CCCC-219B-C9B1-E050-D7936EBE02BF');
18INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
19 ('0DA506A8-8E30-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
20 TO_TIMESTAMP_TZ('2013-05-26 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), - 6504.00,
21 'CHARGES 0C98CCCC-219B-C9B1-E050-D7936EBE02BF');
22INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
23 ('0DA506A8-8E31-EF92-E050-D7936EBE4603', '0C99BEF9-3760-3E4A-E050-D7936EBE2782',
24 TO_TIMESTAMP_TZ('2013-05-26 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 26000.00,
25 'SALE 0C98CCCC-219B-C9B1-E050-D7936EBE02BF');
26
27INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
28 ('0DA506A8-8E32-EF92-E050-D7936EBE4603', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
29 TO_TIMESTAMP_TZ('2013-07-02 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), - 12900.00,
30 'PURCHASE 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF');
31INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
32 ('0DA506A8-8E33-EF92-E050-D7936EBE4603', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
33 TO_TIMESTAMP_TZ('2013-07-02 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), - 3229.00,
34 'CHARGES 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF');
35INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
36 ('0DA506A8-8E34-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
37 TO_TIMESTAMP_TZ('2013-07-02 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 12900.00,
38 'SALE 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF');
diff --git a/res/sql/data/orcl_users.sql b/res/sql/data/orcl_users.sql
new file mode 100644
index 0000000..11896db
--- /dev/null
+++ b/res/sql/data/orcl_users.sql
@@ -0,0 +1,13 @@
1INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
2VALUES ('0C99BEF9-375F-3E4A-E050-D7936EBE2782', 'duf', 'duf@gmail.com', '123456',
3 TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Francis', 'Dumas', 'FR', '86544',
4 '123 rue Bidon', '0123456789', TO_TIMESTAMP_TZ('2001-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'));
5INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
6VALUES ('0C99BEF9-3760-3E4A-E050-D7936EBE2782', 'johnny', 'johnny.martin@wanadoo.fr', 'azerty',
7 TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Johnny', 'Martin', 'FR', '75001',
8 '78 Rue du Faubourg Saint-Honoré', '0000000000',
9 TO_TIMESTAMP_TZ('2015-01-06 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'));
10INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
11VALUES ('0C99BEF9-3762-3E4A-E050-D7936EBE2782', 'Lefe', 'lefe@outlook.com', 'mbay',
12 TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Estelle', 'Lefebvre', 'FR', '77600',
13 '82, rue de la Place', '0102030405', TO_TIMESTAMP_TZ('2015-01-16 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'));
diff --git a/res/sql/data/pg_bids.sql b/res/sql/data/pg_bids.sql
new file mode 100644
index 0000000..18d42ea
--- /dev/null
+++ b/res/sql/data/pg_bids.sql
@@ -0,0 +1,9 @@
1INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
2 ('0CA2E8F1-D1F1-49B7-E050-D7936EBE6A05', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
3 '0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '2013-05-05 23:48:16.000000', 26000.00);
4INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
5 ('0CA2E8F1-D1F2-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
6 '0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '2012-05-03 23:50:05.000000', 25000.00);
7INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
8 ('0CA2E8F1-D1F3-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
9 '0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', '2012-06-01 23:50:53.000000', 12900.00);
diff --git a/res/sql/data/pg_charges.sql b/res/sql/data/pg_charges.sql
new file mode 100644
index 0000000..b102ff9
--- /dev/null
+++ b/res/sql/data/pg_charges.sql
@@ -0,0 +1,4 @@
1INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE)
2VALUES ('0CA2E8F1-D1EF-49B7-E050-D7936EBE6A05', '2013-01-01 00:00:00.000000', '2013-12-31 23:59:59.999999', 4.00, 0.25);
3INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE)
4VALUES ('0CA2E8F1-D1F0-49B7-E050-D7936EBE6A05', '2014-01-01 00:00:00.000000', '2014-12-31 23:59:59.999999', 4.20, 0.27);
diff --git a/res/sql/data/pg_items.sql b/res/sql/data/pg_items.sql
new file mode 100644
index 0000000..42413ca
--- /dev/null
+++ b/res/sql/data/pg_items.sql
@@ -0,0 +1,6 @@
1INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES
2 ('0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-03-15 00:00:00.000000',
3 '2013-07-01 23:59:59.999999', 'Audi A3', 'Voiture', 'Bonne voiture bon état pas cher', 12900.00);
4INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES
5 ('0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '0C99BEF9-3760-3E4A-E050-D7936EBE2782', '2013-04-25 00:00:00.000000',
6 '2013-05-25 23:59:59.999999', 'Harley-Davidson', 'Moto', 'Alors une moto ça roule sur deux roues.', 25000.00);
diff --git a/res/sql/data/pg_transactions.sql b/res/sql/data/pg_transactions.sql
new file mode 100644
index 0000000..40178bd
--- /dev/null
+++ b/res/sql/data/pg_transactions.sql
@@ -0,0 +1,38 @@
1INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
2 ('0C98CCCC-219C-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-01-01 08:39:34.000000',
3 18000.00, 'VIR');
4INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
5 ('0C98CCCC-219D-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-05-02 08:47:26.000000',
6 9500.00, 'VIR');
7INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
8 ('0C98CCCC-219F-C9B1-E050-D7936EBE02BF', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', '2013-03-01 08:50:15.000000',
9 30000.00, 'VIR');
10
11
12-- transactions for sales
13
14INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
15 ('0DA506A8-8E2F-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
16 '2013-05-26 00:00:00.000000', - 26000.00,
17 'PURCHASE 0C98CCCC-219B-C9B1-E050-D7936EBE02BF');
18INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
19 ('0DA506A8-8E30-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
20 '2013-05-26 00:00:00.000000', - 6504.00,
21 'CHARGES 0C98CCCC-219B-C9B1-E050-D7936EBE02BF');
22INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
23 ('0DA506A8-8E31-EF92-E050-D7936EBE4603', '0C99BEF9-3760-3E4A-E050-D7936EBE2782',
24 '2013-05-26 00:00:00.000000', 26000.00,
25 'SALE 0C98CCCC-219B-C9B1-E050-D7936EBE02BF');
26
27INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
28 ('0DA506A8-8E32-EF92-E050-D7936EBE4603', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
29 '2013-07-02 00:00:00.000000', - 12900.00,
30 'PURCHASE 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF');
31INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
32 ('0DA506A8-8E33-EF92-E050-D7936EBE4603', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
33 '2013-07-02 00:00:00.000000', - 3229.00,
34 'CHARGES 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF');
35INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
36 ('0DA506A8-8E34-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
37 '2013-07-02 00:00:00.000000', 12900.00,
38 'SALE 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF');
diff --git a/res/sql/data/pg_users.sql b/res/sql/data/pg_users.sql
new file mode 100644
index 0000000..d5bc2f9
--- /dev/null
+++ b/res/sql/data/pg_users.sql
@@ -0,0 +1,12 @@
1INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
2VALUES
3 ('0C99BEF9-375F-3E4A-E050-D7936EBE2782', 'duf', 'duf@gmail.com', '123456', '2000-01-01 00:00:00.000000', 'Francis',
4 'Dumas', 'FR', '86544', '123 rue Bidon', '0123456789', '2001-01-01 00:00:00.000000');
5INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
6VALUES
7 ('0C99BEF9-3760-3E4A-E050-D7936EBE2782', 'johnny', 'johnny.martin@wanadoo.fr', 'azerty', '2000-01-01 00:00:00.000000',
8 'Johnny', 'Martin', 'FR', '75001', '78 Rue du Faubourg Saint-Honoré', '0000000000', '2015-01-06 00:00:00.000000');
9INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
10VALUES
11 ('0C99BEF9-3762-3E4A-E050-D7936EBE2782', 'Lefe', 'lefe@outlook.com', 'mbay', '2000-01-01 00:00:00.000000', 'Estelle',
12 'Lefebvre', 'FR', '77600', '82, rue de la Place', '0102030405', '2015-01-16 00:00:00.000000');
diff --git a/res/sql/functions/orcl_current_datetime.sql b/res/sql/functions/orcl_current_datetime.sql
new file mode 100644
index 0000000..bf454d8
--- /dev/null
+++ b/res/sql/functions/orcl_current_datetime.sql
@@ -0,0 +1,10 @@
1CREATE OR REPLACE FUNCTION current_datetime
2 RETURN TIMESTAMP WITH TIME ZONE
3AS
4 BEGIN