-- SQL statements used by this application, other than
-- CREATE TABLE statements.
 
-- If the underlying database and driver support stored procedures, then 
-- calls to *simple* stored procedures can also be placed in this file.
-- As long as the CallableStatement can be treated as a PreparedStatement, 
-- then they may be placed in this file.
-- Thus, no items needing registration as an OUT parameter are allowed. 
-- This restriction disallows EXPLICIT return values, but allows the 
-- IMPLICIT return values available from CallableStatement.executeUpdate()
-- and CallableStatement.executeQuery(). That is, this file can include 
-- stored procedures which implicitly return either a single int or a 
-- single ResultSet. If more flexibility is needed, (which is very often 
-- the case) please use StoredProcedureTemplate.java.

-- If a statement has parameters, then the style used by 
-- PreparedStatement is used, in which question marks acts as 
-- placeholders; this style avoids the use of vendor-specific 
-- quoting styles.
--
-- If a statement cannot use parameters in the usual way, then
-- placeholders of the form {i} are used as an alternative (i=0..9).
-- This case is common for SELECTs with many criteria in the 
-- WHERE clause. The {i} are used as placeholders for simple 
-- textual replacement at runtime. This style requires that 
-- any quotes be stated explicitly. This style is not applicable
-- to stored procedures.

-- The Mimer validator was used to verify compliance:
-- http://developer.mimer.se/validator/parser92/index.tml--parser
-- Keywords which do not comply with the ANSI standard are 
--  BINARY (used to force case-sensitive string comparison

-- For SELECTs which are used by the ReportBuilder class to build 
-- reports, it is highly recommended to use the AS keyword, to protect 
-- the application from changes to column names. This is the only 
-- area in which web4j uses column names. More typically, the 
-- column *index* is significant, not the column name. Note that 
-- Oracle will preserve case when AS is used only if the identifier
-- is quoted : SELECT Descr AS "Description" FROM SOME_TABLE

-- Constants are intended mainly for any "magic numbers" that appear 
-- in your SQL statements (especially in WHERE clauses).
-- They can also be used to define short SQL snippets, if desired.
-- Each constant must appear on one line only.
-- No empty lines are permitted in a constants block.
-- Any number of such blocks can appear in each file.
-- Constants must be defined before they are referenced as '${blah}'
-- substitution variables.
-- Constants are not currently shared between .sql files. (If you 
-- would like such a feature, please send a refreshingly pleasant 
-- note of gentle encouragement to javapractices.com.)
constants {
  num_neurons_for_smart_people = 1000000000
  num_neurons_per_replicant = 5000000000
  -- The following two items define policies for rendering 
  -- boolean values in web pages. (This is actually better implemented 
  -- elsewhere. See web.xml).
  render_false_html = <input type="checkbox" name="blah" value="blah" readonly>
  render_true_html = <input type="checkbox" name="blah" value="blah" checked readonly>
}

-- MyUser  
-- -------------------------
ADD_USER {
INSERT INTO MyUser 
(LoginName, LoginPassword, EmailAddress, StarRating, FavoriteTheory, SendCard, Age, DesiredSalary, BirthDate) 
VALUES (?,?,?,?,?,?,?,?,?)
}

-- In this case, ModelBuilder is used to parse the ResultSet.
-- Since ModelBuilder requires that the order of the columns 
-- of the ResultSet match the order of arguments passed to the 
-- corresponding Model Object (MyUser), it seems prudent to explicitly 
-- specify the column names, instead of using the "SELECT * FROM X" style.
--
-- The LEFT JOIN is used to ensure that even NULL values for 
-- FavoriteTheory are returned.
FETCH_USER {
SELECT 
LoginName, 
LoginPassword, 
EmailAddress, 
StarRating, 
MyQuantumTheory.Text AS FavoriteTheory, 
SendCard, 
Age, 
DesiredSalary, 
BirthDate 
FROM MyUser 
LEFT JOIN MyQuantumTheory ON 
MyUser.FavoriteTheory = MyQuantumTheory.Id 
WHERE LoginName=?
}

CHANGE_USER {
UPDATE MyUser SET 
EmailAddress=?, 
StarRating=?, 
FavoriteTheory=?, 
SendCard=?, 
Age=?, 
DesiredSalary=?, 
BirthDate=? 
WHERE LoginName=?
}

DELETE_USER {
DELETE FROM MyUser WHERE LoginName=?
}

FETCH_USER_THEORIES {
SELECT MyQuantumTheory.Text AS TheoryName
FROM MyQuantumTheory, MyUserTheories 
WHERE MyUserTheories.QuantumTheory=MyQuantumTheory.Id 
AND MyUserTheories.LoginName=?
}

DELETE_USER_THEORIES {
DELETE FROM MyUserTheories WHERE LoginName=?
}

ADD_USER_THEORY {
INSERT INTO MyUserTheories 
(LoginName, QuantumTheory) 
VALUES (?,?)
}


-- Pick Lists
--
-- See MyPickListDAO for more information.
--
-- In this implementation, the structure of the various 
-- underlying tables does not need to be uniform. 
-- However, associated *ResultSets* follow the convention
-- that columns named 'Id' and 'Text' must be present. 
-- As well, "extended" pick lists also use OrderIdx and 
-- Description columns. 
-- If the underlying tables do not use these column names, 
-- then the AS keyword can always be used to create the 
-- necessary alias.
-- -------------------------------------------------------------

-- Pick List : Pizza Toppings
FETCH_PIZZA_TOPPINGS {
SELECT Text, Id 
FROM MyPizzaTopping 
ORDER BY Text
}

ADD_PIZZA_TOPPING {
INSERT INTO MyPizzaTopping (Text) VALUES (?)
}

-- The BINARY cast serves to force a case-sensitive comparision.
CHANGE_PIZZA_TOPPING {
UPDATE MyPizzaTopping SET Text=? WHERE BINARY Text=?
}

-- Pick List : Artists
FETCH_ARTISTS {
SELECT FullName AS Text, Id 
FROM MyArtist 
ORDER BY FullName
}

ADD_ARTIST {
INSERT INTO MyArtist (FullName) VALUES (?)
}

-- The BINARY cast serves to force a case-sensitive comparision.
CHANGE_ARTIST {
UPDATE MyArtist SET FullName=? WHERE BINARY FullName=?
}

-- Pick List : Quantum Theories 
-- This is an "extended" pick list, which includes a Description and 
-- explicit OrderIdx, in addition to basic Text.
FETCH_QUANTUM_THEORIES {
SELECT Text, Description, OrderIdx, Id 
FROM MyQuantumTheory 
ORDER BY OrderIdx
}

ADD_QUANTUM_THEORY {
INSERT INTO MyQuantumTheory (Text, Description, OrderIdx) VALUES (?,?,?)
}

-- Pick Lists which include a column devoted to specifying an 
-- explicit sort order need to allow complete rearrangement  
-- of the OrderIdx 
INCREMENT_QUANTUM_THEORY_ORDER_IDX {
UPDATE MyQuantumTheory SET OrderIdx = OrderIdx + 1 WHERE OrderIdx >= ?
}

DECREMENT_QUANTUM_THEORY_ORDER_IDX {
UPDATE MyQuantumTheory SET OrderIdx = OrderIdx - 1 WHERE OrderIdx > ?
}

CHANGE_QUANTUM_THEORY {
UPDATE MyQuantumTheory SET Text=?, Description=? WHERE BINARY Text=?
}

CHANGE_QUANTUM_THEORY_ORDER_IDX {
UPDATE MyQuantumTheory SET OrderIdx=? WHERE BINARY Text=?
}

-- MyPerson
-- This table is very similar to MyUser. It was 
-- created to allow unfettered access and editing of 
-- simple toy data.
-- -----------------------------------------------------
ADD_PERSON {
INSERT INTO MyPerson 
(Name, StarRating, FavoriteTheory, SendCard, Age, DesiredSalary, BirthDate, BodyTemperature, NumNeurons) 
VALUES (?,?,?,?,?,?,?,?,?)
}

-- In this case, ModelBuilder is used to parse the ResultSet.
-- Since ModelBuilder requires that the order of the columns 
-- of the ResultSet match the order of arguments passed to the 
-- corresponding Model Object (MyUser), it seems prudent to expicitly 
-- specify the column names, instead of using the "SELECT * FROM X" style.
--
-- The LEFT JOIN is used to ensure that even NULL values for 
-- FavoriteTheory are returned.
FETCH_PERSON {
SELECT 
Name, 
StarRating, 
MyQuantumTheory.Text AS FavoriteTheory, 
SendCard, 
Age, 
DesiredSalary, 
BirthDate, 
BodyTemperature, 
NumNeurons 
FROM MyPerson 
LEFT JOIN MyQuantumTheory ON 
MyPerson.FavoriteTheory = MyQuantumTheory.Id 
WHERE Name=?
}

CHANGE_PERSON {
UPDATE MyPerson SET 
StarRating=?, 
FavoriteTheory=?, 
SendCard=?, 
Age=?, 
DesiredSalary=?, 
BirthDate=?, 
BodyTemperature=?, 
NumNeurons=? 
WHERE Name=?
}

DELETE_PERSON {
DELETE FROM MyPerson WHERE Name=?
}

-- Used to exercise ValueBuilder, which is used to extract simple 
-- values (often statistics) from the database.
FETCH_NUM_PERSONS_WITH_SIMILAR_NAME {
SELECT COUNT(*) AS NUM_PERSONS 
FROM MyPerson 
WHERE Name LIKE '{0}%'
}

-- Used to exercise the rather unusual case of performing a 
-- database update without any criteria
UPDATE_NUM_NEURONS {
UPDATE MyPerson SET NumNeurons=${num_neurons_per_replicant}
}

-- The following items are used in a report
-- Note that all columns use the 'AS' keyword. This is important 
-- here since the column names are used directly in the report.
-- In other SELECTSs, it is not column name which is significant, 
-- but column index.
FETCH_NUM_USERS {
SELECT COUNT(*) AS NumUsers FROM MyUser;
}

FETCH_DATE_MOST_RECENT_MESSAGE {
SELECT MAX(CreationDate) AS MostRecentMessage FROM MyMessage
}

FETCH_NUM_MSGS_PER_USER {
SELECT 
MyUser.LoginName AS userName, 
COUNT(MyMessage.Id) AS numMessages 
FROM MyUser, MyMessage 
WHERE MyMessage.LoginName=MyUser.LoginName 
GROUP BY MyUser.LoginName
}

FETCH_SMART_PERSONS {
SELECT 
Name as Name, 
StarRating AS StarRating, 
MyQuantumTheory.Text AS FavoriteTheory, 
SendCard AS SendCard, 
Age AS Age, 
DesiredSalary AS DesiredSalary, 
BirthDate AS BirthDate, 
BodyTemperature AS BodyTemperature, 
NumNeurons AS NumNeurons 
FROM MyPerson 
LEFT JOIN MyQuantumTheory ON 
MyPerson.FavoriteTheory = MyQuantumTheory.Id 
WHERE NumNeurons >= ${num_neurons_for_smart_people}
}

-- Reports often need to state details and corresponding totals.
-- In particular, the "details" query can often
-- be re-used directly as a sub-query in the "totals" query.
-- This is artificial here, since the version of MySQL this is 
-- running against does not support sub-queries, unfortunately. 
-- Please see the Oracle version of this query for a demonstration 
-- of how one query can be used in another *without repeating the query*,
-- by using a substitution variable. 
FETCH_NUM_SMART_PERSONS {
SELECT COUNT(*)
FROM MyPerson 
LEFT JOIN MyQuantumTheory ON 
MyPerson.FavoriteTheory = MyQuantumTheory.Id 
WHERE NumNeurons >= ${num_neurons_for_smart_people}
}

-- A repeat of the above query, but with database formatting 
-- functions applied to the result. Note the rather bizarre CASE
-- statement used for booleans, for generating an HTML checkbox.
-- The CASE could be simplified, if desired, to something simpler, 
-- as in :
-- CASE SendCard WHEN 0 THEN 'false' ELSE 'true' END
FETCH_SMART_PERSONS_ADD_FORMATTING {
SELECT 
Name as Name, 
StarRating AS StarRating, 
MyQuantumTheory.Text AS FavoriteTheory, 
CASE SendCard 
 WHEN 0 THEN '${render_false_html}' 
 ELSE '${render_true_html}' 
END AS SendCard, 
Age AS Age, 
FORMAT(DesiredSalary,2) AS DesiredSalary, 
DATE_FORMAT(BirthDate, '%m/%d/%Y') AS BirthDate, 
FORMAT(BodyTemperature,2) AS BodyTemperature, 
FORMAT(NumNeurons,0) AS NumNeurons 
FROM MyPerson 
LEFT JOIN MyQuantumTheory ON 
MyPerson.FavoriteTheory = MyQuantumTheory.Id 
WHERE NumNeurons >= ${num_neurons_for_smart_people}
}

-- Not actually called for MySql, since MySql does not 
-- implement stored procedures. (The reason it 
-- appears here, in a file intended only for MySql, is 
-- merely to keep the items in .sql files
-- synchronized with the elements of the SqlId enumeration.
-- See the oracle version for a "real" implementation.)
UPDATE_NUM_NEURONS_STORED_PROC {
{call update_num_neurons}
}

