sql-hbase.properties

##############################################################################
# sql.properties for build.xml.
# Copyright 2003 by Jan Kiefer.
#
# This file is distributed "as is". It comes with no warranty and the 
# author takes no responsibility for the consequences of its use.
#
# Usage, distribution and modification is allowed to everyone, as long 
# as reference to the author is given and this license note is included.
##############################################################################


sql.createTableAddress="CREATE TABLE address ( addr_id integer not null PRIMARY KEY, addr_street1 varchar(40), addr_street2 varchar(40), addr_city varchar(30), addr_state varchar(20), addr_zip varchar(10), addr_co_id integer)"

sql.createTableAuthor="CREATE TABLE author ( a_id integer not null PRIMARY KEY, a_fname varchar(20), a_lname varchar(20), a_mname varchar(20), a_dob date, a_bio varchar(500))"

sql.createTableCCXacts="CREATE TABLE cc_xacts ( cx_o_id integer not null PRIMARY KEY, cx_type varchar(10), cx_num integer, cx_name varchar(30), cx_expire date, cx_auth_id char(15), cx_xact_amt double, cx_xact_date timestamp, cx_co_id integer)"

sql.createTableCountry="CREATE TABLE country ( co_id integer not null PRIMARY KEY, co_name varchar(50), co_exchange double, co_currency varchar(18))"

sql.createTableCostumer="CREATE TABLE customer ( c_id integer not null PRIMARY KEY, c_uname varchar(20), c_passwd varchar(20), c_fname varchar(17), c_lname varchar(17), c_addr_id integer, c_phone integer, c_email varchar(50), c_since date, c_last_login date, c_login timestamp, c_expiration timestamp, c_discount double, c_balance double, c_ytd_pmt double, c_birthdate date, c_data varchar(500))"

sql.createTableItem="CREATE TABLE item ( i_id integer not null PRIMARY KEY, i_title varchar(60), i_a_id integer, i_pub_date date, i_publisher varchar(60), i_subject varchar(60), i_desc varchar(500), i_related1 integer, i_related2 integer, i_related3 integer, i_related4 integer, i_related5 integer, i_thumbnail varchar(40), i_image varchar(40), i_srp double, i_cost double, i_avail date, i_stock integer, i_isbn char(13), i_page integer, i_backing varchar(15), i_dimensions varchar(25))"

# phoenix jbdc driver does not allow yet more than 1 column as primary key
sql.createTableOrderLine="CREATE TABLE order_line ( ol_id integer not null PRIMARY KEY, ol_o_id integer not null, ol_i_id integer, ol_qty integer, ol_discount double, ol_comments varchar(110))"

sql.createTableOrders="CREATE TABLE orders ( o_id integer not null PRIMARY KEY, o_c_id integer, o_date timestamp, o_sub_total double, o_tax double, o_total double, o_ship_type varchar(10), o_ship_date timestamp, o_bill_addr_id integer, o_ship_addr_id integer, o_status varchar(15))"

sql.createTableShoppingCart="CREATE TABLE shopping_cart ( sc_id integer not null PRIMARY KEY, sc_time timestamp)"

# second primary key was removed
sql.createTableShoppingCartLine="CREATE TABLE shopping_cart_line ( scl_sc_id integer not null PRIMARY KEY, scl_qty integer, scl_i_id integer not null)"





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.getBook="SELECT i_id, i_title, i_a_id, i_pub_date, i_publisher, i_subject, i_desc, i_related1, i_related2, i_related3, i_related4, i_related5, i_thumbnail, i_image, i_srp, i_cost, i_avail, i_stock, i_isbn, i_page, i_backing, i_dimensions, a_id, a_fname, a_lname, a_mname, a_dob, a_bio FROM item inner join author on item.i_a_id = author.a_id WHERE 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.getCustomer="SELECT 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, addr_id, addr_street1, addr_street2, addr_city, addr_state, addr_zip, addr_co_id, co_id, co_name, co_exchange, co_currency FROM customer inner join address ON customer.c_addr_id = address.addr_id  inner join country ON address.addr_co_id = country.co_id WHERE 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"
sql.doSubjectSearch="SELECT i_id, i_title, i_a_id, i_pub_date, i_publisher, i_subject, i_desc, i_related1, i_related2, i_related3, i_related4, i_related5, i_thumbnail, i_image, i_srp, i_cost, i_avail, i_stock, i_isbn, i_page, i_backing, i_dimensions, a_id, a_fname, a_lname, a_mname, a_dob, a_bio FROM item inner join author ON item.i_a_id = author.a_id WHERE item.i_subject = ? ORDER BY item.i_title"

#sql.doTitleSearch="SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_title LIKE ? ORDER BY item.i_title"
sql.doTitleSearch="SELECT i_id, i_title, i_a_id, i_pub_date, i_publisher, i_subject, i_desc, i_related1, i_related2, i_related3, i_related4, i_related5, i_thumbnail, i_image, i_srp, i_cost, i_avail, i_stock, i_isbn, i_page, i_backing, i_dimensions, a_id, a_fname, a_lname, a_mname, a_dob, a_bio FROM item inner join author ON item.i_a_id = author.a_id WHERE item.i_title LIKE ? ORDER BY item.i_title"


#sql.doAuthorSearch="SELECT * FROM author, item WHERE author.a_lname LIKE ? AND item.i_a_id = author.a_id ORDER BY item.i_title"
sql.doAuthorSearch="SELECT i_id, i_title, i_a_id, i_pub_date, i_publisher, i_subject, i_desc, i_related1, i_related2, i_related3, i_related4, i_related5, i_thumbnail, i_image, i_srp, i_cost, i_avail, i_stock, i_isbn, i_page, i_backing, i_dimensions, a_id, a_fname, a_lname, a_mname, a_dob, a_bio FROM author inner join item ON item.i_a_id = author.a_id WHERE author.a_lname LIKE ? ORDER BY item.i_title"

#sql.getNewProducts="SELECT i_id, i_title, a_fname, a_lname " +\
#		 "FROM item, author " +\
#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 "
sql.getNewProducts="SELECT i_id, i_title, a_fname, a_lname " +\
		 "FROM item inner join author " +\
		 "ON item.i_a_id = author.a_id " +\
		 "WHERE item.i_subject = ? " +\
		 "ORDER BY item.i_pub_date DESC,item.i_title "

#sql.getBestSellers="SELECT i_id, i_title, a_fname, a_lname, SUM(ol_qty) AS orderkey " +\
#		 "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 orderkey DESC "
# SE: nested queries/sub queries/derived tables not yet supported https://github.com/forcedotcom/phoenix/issues/5
# TEMP QUERY:
sql.getBestSellers="SELECT i_id, i_title, a_fname, a_lname, SUM(ol_qty) AS orderkey " +\
		 "FROM item inner join order_line " +\
		 "ON item.i_id = order_line.ol_i_id " +\
		 "INNER JOIN author " +\
                 "ON item.i_a_id = author.a_id " +\
		 "WHERE item.i_subject = ? " +\
		 "GROUP BY i_id, i_title, a_fname, a_lname " +\
		 "ORDER BY orderkey DESC "

#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 = ?"
# SE: OR not support in join condition
#sql.getRelated="SELECT J.i_id,J.i_thumbnail from item I inner join item J on (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) where I.i_id = ?"
# TEMP QUERY:
sql.getRelated="SELECT J.i_id,J.i_thumbnail from item I inner join item J on I.i_related1 = J.i_id where I.i_id = ?"


#sql.adminUpdate="UPDATE item SET i_cost = ?, i_image = ?, i_thumbnail = ?, i_pub_date = DATEOB() WHERE i_id = ?"
sql.adminUpdate="UPSERT INTO item(i_cost,i_image,i_thumbnail,i_pub_date,i_id) VALUES(?,?,?,current_date(),?)"

# SE: nested query not supported
#sql.adminUpdate.related="SELECT ol_i_id, SUM(ol_qty) AS orderkey " +\
#		 "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 orderkey DESC "
# TEMP QUERY:
sql.adminUpdate.related="SELECT ol_i_id, SUM(ol_qty) AS orderkey " +\
		 "FROM orders INNER JOIN order_line " + \
		 "ON orders.o_id = order_line.ol_o_id " +\
		 "WHERE order_line.ol_i_id != ? " +\
		 "GROUP BY ol_i_id " +\
		 "ORDER BY orderkey DESC "


#sql.adminUpdate.related1="UPDATE item SET i_related1 = ?, i_related2 = ?, i_related3 = ?, i_related4 = ?, i_related5 = ? WHERE i_id = ?"
sql.adminUpdate.related1="UPSERT INTO item(i_related1, i_related2, i_related3, i_related4, i_related5, i_id) VALUES(?,?,?,?,?,?)"

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 "
sql.getMostRecentOrder.id="SELECT o_id " +\
		     "FROM customer inner join orders " +\
		     "ON customer.c_id = orders.o_c_id " +\
		     "WHERE c_uname = ? " +\
		     "ORDER BY o_date, orders.o_id DESC "


#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.order="SELECT orders.o_id, orders.o_c_id, orders.o_date, orders.o_sub_total, orders.o_tax," +\
"orders.o_total, orders.o_ship_type, orders.o_ship_date, orders.o_bill_addr_id, orders.o_ship_addr_id, orders.o_status," +\
" customer.c_id, customer.c_uname, customer.c_passwd, customer.c_fname, customer.c_lname, customer.c_addr_id, " +\
"customer.c_phone, customer.c_email, customer.c_since, customer.c_last_login, customer.c_login, customer.c_expiration," +\
"customer.c_discount, customer.c_balance, customer.c_ytd_pmt, customer.c_birthdate, customer.c_data," +\
		     "  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 INNER JOIN orders " +\
                     "ON customer.c_id = orders.o_c_id " +\
                     "INNER JOIN cc_xacts " +\
                     "ON cx_o_id = orders.o_id " +\
                     "INNER JOIN address AS ship " +\
                     "ON orders.o_ship_addr_id = ship.addr_id " +\
                     "INNER JOIN country AS ship_co " +\
                     "ON ship.addr_co_id = ship_co.co_id " +\
                     "INNER JOIN address AS bill " +\
                     "ON orders.o_bill_addr_id = bill.addr_id " +\
		     "INNER JOIN country AS bill_co " +\
                     "ON bill.addr_co_id = bill_co.co_id WHERE orders.o_id = ?"

#sql.getMostRecentOrder.lines="SELECT * " + \
#		     "FROM order_line, item " + \
#		     "WHERE ol_o_id = ? " +\
#		     "AND ol_i_id = i_id"
sql.getMostRecentOrder.lines="SELECT ol_id, ol_o_id, ol_i_id, ol_qty, ol_discount, ol_comments, i_id, i_title, i_a_id, i_pub_date, i_publisher, i_subject, i_desc, i_related1, i_related2, i_related3, i_related4, i_related5, i_thumbnail, i_image, i_srp, i_cost, i_avail, i_stock, i_isbn, i_page, i_backing, i_dimensions " + \
		     "FROM order_line INNER JOIN item " + \
	             "ON ol_i_id = i_id " +\
         	     "WHERE ol_o_id = ?"

sql.createEmptyCart="SELECT COUNT(*) FROM shopping_cart"

### SROE: TO TEST
#sql.createEmptyCart.insert="INSERT into shopping_cart (sc_id, sc_time) " + \
#		     "SELECT COUNT(*), DATEOB() FROM shopping_cart"
sql.createEmptyCart.insert="UPSERT into shopping_cart (sc_id, sc_time) " + \
		     "SELECT COUNT(*), current_date() FROM shopping_cart"

sql.addItem="SELECT scl_qty FROM shopping_cart_line WHERE scl_sc_id = ? AND scl_i_id = ?"
### SROE: TO TEST - 2 primary keys
#sql.addItem.update="UPDATE shopping_cart_line SET scl_qty = ? WHERE scl_sc_id = ? AND scl_i_id = ?"
sql.addItem.update="UPSERT INTO shopping_cart_line(scl_qty, scl_sc_id, scl_i_id) VALUES(?,?,?)"

#sql.addItem.put="INSERT into shopping_cart_line (scl_sc_id, scl_qty, scl_i_id) VALUES (?,?,?)"
sql.addItem.put="UPSERT 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.refreshCart.update="UPSERT INTO shopping_cart_line(scl_qty, scl_sc_id, scl_i_id) VALUES(?,?,?)"

sql.addRandomItemToCartIfNecessary="SELECT COUNT(*) from shopping_cart_line where scl_sc_id = ?"

#sql.resetCartTime="UPDATE shopping_cart SET sc_time = DATEOB() WHERE sc_id = ?"
sql.resetCartTime="UPSERT INTO shopping_cart(sc_time, sc_id) VALUES(current_date(), ?)"

#sql.getCart="SELECT * " + \
#		 "FROM shopping_cart_line, item " + \
#		 "WHERE scl_i_id = item.i_id AND scl_sc_id = ?"
sql.getCart="SELECT scl_sc_id, scl_qty, scl_i_id, i_id, i_title, i_a_id, i_pub_date, i_publisher, i_subject, i_desc, i_related1, i_related2, i_related3, i_related4, i_related5, i_thumbnail, i_image, i_srp, i_cost, i_avail, i_stock, i_isbn, i_page, i_backing, i_dimensions " + \
		 "FROM shopping_cart_line INNER JOIN item " + \
		 "ON scl_i_id = item.i_id WHERE scl_sc_id = ?"

#sql.refreshSession="UPDATE customer SET c_login = DATEOB(), c_expiration = ? WHERE c_id = ?"
sql.refreshSession="UPSERT INTO customer(c_login, c_expiration, c_id) VALUES(current_date(),?,?)"

#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="UPSERT 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.enterCCXactTmp="SELECT co_id FROM address, country WHERE addr_id = ? AND addr_co_id = co_id"
sql.enterCCXactTmp="SELECT co_id FROM address INNER JOIN country ON addr_co_id = co_id WHERE addr_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 (?, ?, ?, ?, ?, ?, DATEOB(), ?)"
sql.enterCCXact="UPSERT into cc_xacts (cx_o_id, cx_type, cx_num, cx_name, cx_expire, cx_xact_amt, cx_xact_date, cx_co_id) " + \
		"VALUES (?, ?, ?, ?, ?, ?, current_date(), ?)"

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.insert="UPSERT 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 (?, ?, DATEOB(), ?, 8.25, ?, ?, ?, ?, ?, 'Pending')"
sql.enterOrder.insert="UPSERT 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 (?, ?, current_date(), ?, 8.25, ?, ?, ?, ?, ?, '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.addOrderLine="UPSERT 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.setStock="UPSERT item(i_stock, i_id) VALUES(?,?)"

sql.verifyDBConsistency.custId="SELECT c_id FROM customer"
sql.verifyDBConsistency.itemId="SELECT i_id FROM item"
sql.verifyDBConsistency.addrId="SELECT addr_id FROM address"