/*
 * Copyright 2008 Sun Microsystems, Inc.  All Rights Reserved.
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
 *
 * This code is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License version 2 only, as
 * published by the Free Software Foundation.  Sun designates this
 * particular file as subject to the "Classpath" exception as provided
 * by Sun in the LICENSE file that accompanied this code.
 *
 * This code is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
 * version 2 for more details (a copy is included in the LICENSE file that
 * accompanied this code).
 *
 * You should have received a copy of the GNU General Public License version
 * 2 along with this work; if not, write to the Free Software Foundation,
 * Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
 *
 * Please contact Sun Microsystems, Inc., 4150 Network Circle, Santa Clara,
 * CA 95054 USA or visit www.sun.com if you need additional information or
 * have any questions.
 */

package javafxdb;

/**
* test harness for the Javafx db framwork,
* based on the <href a=http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html>
* JDBC tutorial</href>
* @author jclarke
*/

import javafx.sql.*;
import java.lang.System;


/****
var db = DB{
    user: 'jclarke'
    dbUrl:  "jdbc:mysql://localhost/fxtest";
};
    *******/

var dataSource = com.mysql.jdbc.jdbc2.optional.MysqlDataSource{};
dataSource.setServerName("localhost");
dataSource.setDatabaseName("fxtest");
dataSource.setUser("jclarke");
var db = DB {
    dataSource: dataSource
}

db.execute("drop table if exists COFFEES");
db.execute("drop table if exists SUPPLIERS");

db.execute("create table SUPPLIERS " +
                "(SUP_ID INTEGER NOT NULL, " +
                "SUP_NAME VARCHAR(40), " +
                "STREET VARCHAR(40), " +
                "CITY VARCHAR(20), " +
                "STATE CHAR(2), " +
                "ZIP CHAR(5), " +
                "primary key(SUP_ID))");
db.execute("create table COFFEES " +
                "(COF_NAME varchar(32) NOT NULL, " +
                "SUP_ID int, " +
                "PRICE float, " +
                "SALES int, " +
                "TOTAL int, " +
                "primary key(COF_NAME), " +
                "foreign key(SUP_ID) references SUPPLIERSPK)");

            
db.executeUpdate("insert into SUPPLIERS " +
    "values(49, 'Superior Coffee', '1 Party Place', " +
         "'Mendocino', 'CA', '95460')");

db.executeUpdate("insert into SUPPLIERS " +
    "values(101, 'Acme, Inc.', '99 Market Street', " +
        "'Groundsville', 'CA', '95199')");

db.executeUpdate("insert into SUPPLIERS " +
    "values(150, 'The High Ground', '100 Coffee Lane', " +
         "'Meadows', 'CA', '93966')");

db.executeUpdate("insert into COFFEES " +
    "values('Colombian', 00101, 7.99, 0, 0)");

db.executeUpdate("insert into COFFEES " +
    "values('French_Roast', 00049, 8.99, 0, 0)");

db.executeUpdate("insert into COFFEES " +
    "values('Espresso', 00150, 9.99, 0, 0)");

db.executeUpdate("insert into COFFEES " +
    "values('Colombian_Decaf', 00101, 8.99, 0, 0)");

db.executeUpdate("insert into COFFEES " +
    "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");

db.query("select SUP_NAME, SUP_ID from SUPPLIERS",
        function(rs:java.sql.ResultSet):Void {
            System.out.println("{rs.getString("SUP_NAME")}   {rs.getInt("SUP_ID")}")
        });
db.query("select COF_NAME, PRICE from COFFEES",
        function(rs:java.sql.ResultSet):Void {
            System.out.println("{rs.getString("COF_NAME")}   {%5.2f rs.getFloat("PRICE")}")
        });

var values = ["Kona", "00049", "15.99", "0", "0"];
db.execute("insert into COFFEES values(?,?,?,?,?)", values);

db.query("select COF_NAME, PRICE from COFFEES where COF_NAME = '{values[0]}'",
        function(rs:java.sql.ResultSet):Void {
            System.out.println("::{rs.getString("COF_NAME")}   {%5.2f rs.getFloat("PRICE")}")
        });

db.executeUpdate("UPDATE COFFEES SET COF_NAME = ? WHERE COF_NAME = ?", ['Kona', "7.99"]);
db.query("select COF_NAME, PRICE from COFFEES where COF_NAME = '{values[0]}'",
        function(rs:java.sql.ResultSet):Void {
            System.out.println("::{rs.getString("COF_NAME")}   {%5.2f rs.getFloat("PRICE")}")
        });


var updateString = "update COFFEES " +
                      "set SALES = ? where COF_NAME like ?";
var updateSales = db.getPreparedStatement(updateString);
var salesForWeek = [175, 150, 60, 155, 90, 100];
var coffees = ["Colombian", "French_Roast", "Espresso",
                     "Colombian_Decaf", "French_Roast_Decaf", "Kona"];
for(c in coffees) {
        updateSales.setInt(1, salesForWeek[indexof c]);
        updateSales.setString(2, c);
        updateSales.executeUpdate();
}

db.query("select COF_NAME, SALES from COFFEES",
        function(rs:java.sql.ResultSet):Void {
            System.out.println("++{rs.getString("COF_NAME")}   {%3d rs.getInt("SALES")}")
        });