-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsql-postgres.properties
162 lines (129 loc) · 7.83 KB
/
sql-postgres.properties
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
##############################################################################
# sql.properties for build.xml.
# 2003 by Jan Kiefer, extracted from TPCW_Database.java.
#
# This file is distributed "as is". It comes with no warranty and the
# author takes no responsibility for the consequences of its use.
#
# For Copyright license see tpcw/servlets/TPCW_Database.java.
##############################################################################
sql.getName="SELECT c_fname,c_lname FROM customer WHERE c_id = ?"
sql.getBook="SELECT * FROM item,author WHERE item.i_a_id = author.a_id AND i_id = ?"
sql.getCustomer="SELECT * FROM customer, address, country WHERE customer.c_addr_id = address.addr_id AND address.addr_co_id = country.co_id AND customer.c_uname = ?"
sql.doSubjectSearch="SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_subject = ? ORDER BY item.i_title LIMIT 50"
sql.doTitleSearch="SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_title LIKE ? ORDER BY item.i_title LIMIT 50"
sql.doAuthorSearch="SELECT * FROM author, item WHERE author.a_lname LIKE ? AND item.i_a_id = author.a_id ORDER BY item.i_title LIMIT 50"
sql.getNewProducts="SELECT i_id, i_title, a_fname, a_lname " +\
"FROM item, author " +\
"WHERE item.i_a_id = author.a_id " +\
"AND item.i_subject = ? " +\
"ORDER BY item.i_pub_date DESC,item.i_title " +\
"LIMIT 50"
sql.getBestSellers="SELECT i_id, i_title, a_fname, a_lname " +\
"FROM item, author, order_line " +\
"WHERE item.i_id = order_line.ol_i_id " +\
"AND item.i_a_id = author.a_id " +\
"AND order_line.ol_o_id > (SELECT MAX(o_id)-3333 FROM orders)" +\
"AND item.i_subject = ? " +\
"GROUP BY i_id, i_title, a_fname, a_lname " +\
"ORDER BY SUM(ol_qty) DESC " +\
"LIMIT 50"
sql.getRelated="SELECT J.i_id,J.i_thumbnail from item I, item J where (I.i_related1 = J.i_id or I.i_related2 = J.i_id or I.i_related3 = J.i_id or I.i_related4 = J.i_id or I.i_related5 = J.i_id) and I.i_id = ?"
sql.adminUpdate="UPDATE item SET i_cost = ?, i_image = ?, i_thumbnail = ?, i_pub_date = CURRENT_DATE WHERE i_id = ?"
sql.adminUpdate.related="SELECT ol_i_id " +\
"FROM orders, order_line " + \
"WHERE orders.o_id = order_line.ol_o_id " +\
"AND NOT (order_line.ol_i_id = ?) " +\
"AND orders.o_c_id IN (SELECT o_c_id " +\
" FROM orders, order_line " +\
" WHERE orders.o_id = order_line.ol_o_id " +\
" AND orders.o_id > (SELECT MAX(o_id)-10000 FROM orders)" + \
" AND order_line.ol_i_id = ?) " +\
"GROUP BY ol_i_id " +\
"ORDER BY SUM(ol_qty) DESC " +\
"LIMIT 5"
sql.adminUpdate.related1="UPDATE item SET i_related1 = ?, i_related2 = ?, i_related3 = ?, i_related4 = ?, i_related5 = ? WHERE i_id = ?"
sql.getUserName="SELECT c_uname FROM customer WHERE c_id = ?"
sql.getPassword="SELECT c_passwd FROM customer WHERE c_uname = ?"
sql.getRelated1="SELECT i_related1 FROM item where i_id = ?"
sql.getMostRecentOrder.id="SELECT o_id " +\
"FROM customer, orders " +\
"WHERE customer.c_id = orders.o_c_id " +\
"AND c_uname = ? " +\
"ORDER BY o_date, orders.o_id DESC " +\
"LIMIT 1"
sql.getMostRecentOrder.order="SELECT orders.*, customer.*, " +\
" cc_xacts.cx_type, " +\
" ship.addr_street1 AS ship_addr_street1, " +\
" ship.addr_street2 AS ship_addr_street2, " +\
" ship.addr_state AS ship_addr_state, " +\
" ship.addr_zip AS ship_addr_zip, " +\
" ship_co.co_name AS ship_co_name, " +\
" bill.addr_street1 AS bill_addr_street1, " +\
" bill.addr_street2 AS bill_addr_street2, " +\
" bill.addr_state AS bill_addr_state, " +\
" bill.addr_zip AS bill_addr_zip, " +\
" bill_co.co_name AS bill_co_name " +\
"FROM customer, orders, cc_xacts," +\
" address AS ship, " +\
" country AS ship_co, " +\
" address AS bill, " + \
" country AS bill_co " +\
"WHERE orders.o_id = ? " +\
" AND cx_o_id = orders.o_id " +\
" AND customer.c_id = orders.o_c_id " +\
" AND orders.o_bill_addr_id = bill.addr_id " +\
" AND bill.addr_co_id = bill_co.co_id " +\
" AND orders.o_ship_addr_id = ship.addr_id " +\
" AND ship.addr_co_id = ship_co.co_id " +\
" AND orders.o_c_id = customer.c_id"
sql.getMostRecentOrder.lines="SELECT * " + \
"FROM order_line, item " + \
"WHERE ol_o_id = ? " +\
"AND ol_i_id = i_id"
sql.createEmptyCart="SELECT COUNT(*) FROM shopping_cart"
sql.createEmptyCart.insert="INSERT into shopping_cart (sc_id, sc_time) " + \
"VALUES ((SELECT COUNT(*) FROM shopping_cart)," + \
"timestamp 'now')"
sql.addItem="SELECT scl_qty FROM shopping_cart_line WHERE scl_sc_id = ? AND scl_i_id = ?"
sql.addItem.update="UPDATE shopping_cart_line SET scl_qty = ? WHERE scl_sc_id = ? AND scl_i_id = ?"
sql.addItem.put="INSERT into shopping_cart_line (scl_sc_id, scl_qty, scl_i_id) VALUES (?,?,?)"
sql.refreshCart.remove="DELETE FROM shopping_cart_line WHERE scl_sc_id = ? AND scl_i_id = ?"
sql.refreshCart.update="UPDATE shopping_cart_line SET scl_qty = ? WHERE scl_sc_id = ? AND scl_i_id = ?"
sql.addRandomItemToCartIfNecessary="SELECT COUNT(*) from shopping_cart_line where scl_sc_id = ?"
sql.resetCartTime="UPDATE shopping_cart SET sc_time = timestamp 'now' WHERE sc_id = ?"
sql.getCart="SELECT * " + \
"FROM shopping_cart_line, item " + \
"WHERE scl_i_id = item.i_id AND scl_sc_id = ?"
sql.refreshSession="UPDATE customer SET c_login = timestamp 'now', c_expiration = timestamp 'now' + interval '2 hours' WHERE c_id = ?"
sql.createNewCustomer="INSERT into customer (c_id, c_uname, c_passwd, c_fname, c_lname, c_addr_id, c_phone, c_email, c_since, c_last_login, c_login, c_expiration, c_discount, c_balance, c_ytd_pmt, c_birthdate, c_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
sql.createNewCustomer.maxId="SELECT max(c_id) FROM customer"
sql.getCDiscount="SELECT c_discount FROM customer WHERE customer.c_id = ?"
sql.getCAddrId="SELECT c_addr_id FROM customer WHERE customer.c_id = ?"
sql.getCAddr="SELECT c_addr_id FROM customer WHERE customer.c_id = ?"
sql.enterCCXact="INSERT into cc_xacts (cx_o_id, cx_type, cx_num, cx_name, cx_expire, cx_xact_amt, cx_xact_date, cx_co_id) " + \
"VALUES (?, ?, ?, ?, ?, ?, date('now'), (SELECT co_id FROM address, country WHERE addr_id = ? AND addr_co_id = co_id))"
sql.clearCart="DELETE FROM shopping_cart_line WHERE scl_sc_id = ?"
sql.enterAddress.id="SELECT co_id FROM country WHERE co_name = ?"
sql.enterAddress.match="SELECT addr_id FROM address " + \
"WHERE addr_street1 = ? " +\
"AND addr_street2 = ? " + \
"AND addr_city = ? " + \
"AND addr_state = ? " + \
"AND addr_zip = ? " + \
"AND addr_co_id = ?"
sql.enterAddress.insert="INSERT into address (addr_id, addr_street1, addr_street2, addr_city, addr_state, addr_zip, addr_co_id) " + \
"VALUES (?, ?, ?, ?, ?, ?, ?)"
sql.enterAddress.maxId="SELECT max(addr_id) FROM address"
sql.enterOrder.insert="INSERT into orders (o_id, o_c_id, o_date, o_sub_total, " + \
"o_tax, o_total, o_ship_type, o_ship_date, " + \
"o_bill_addr_id, o_ship_addr_id, o_status) " + \
"VALUES (?, ?,date('now') , ?, 8.25, ?, ?, date('now') + ?, ?, ?, 'Pending')"
sql.enterOrder.maxId="SELECT count(o_id) FROM orders"
sql.addOrderLine="INSERT into order_line (ol_id, ol_o_id, ol_i_id, ol_qty, ol_discount, ol_comments) " + \
"VALUES (?, ?, ?, ?, ?, ?)"
sql.getStock="SELECT i_stock FROM item WHERE i_id = ?"
sql.setStock="UPDATE item SET i_stock = ? WHERE i_id = ?"
sql.verifyDBConsistency.custId="SELECT c_id FROM customer"
sql.verifyDBConsistency.itemId="SELECT i_id FROM item"
sql.verifyDBConsistency.addrId="SELECT addr_id FROM address"