// TestApp.cpp
// Test application for learning SQLite
// Created 1/14/08 by Kyle Oliver
// Last revised 2/18/08

#include <iostream>
#include <string>
#include <map>
#include "sqlite3.h"
// (Your linker also needs to be able to access the SQLite library.)

using namespace std;

/**
 * A callback function used for querying the database. This function is provided
 * in the SQLite documentation.
 * 
 * @param NotUsed arbitrary pointer that gets passed from sqlite3_exec()
 * @param argc 
 * @param argv 
 * @param azColName 
 */
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
	int i;
	for(i=0; i<argc; i++){
		printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
	}
	printf("\n");
	return 0;
}

/**
 * Runs the testing application.
 */
int main() {
	
	// Get and print the version number for this SQLite release:
	int versNum = sqlite3_libversion_number();
	cout << endl << "Current SQLite version number: " << versNum << endl;

//////////////////////////  Create a new database  /////////////////////////////


	sqlite3* testDB; // DB handle
	int retInt; // for storing the return status of sqlite3 function calls
	char *zErrMsg = 0; // for storing error messages from sqlite3 function calls
	const char* testFile = "TestDB.db"; // duh
	
	// If this file has already been created, delete it.
	remove(testFile);
	
	// Try to open it.
	retInt = sqlite3_open(testFile, &testDB);
	
	// If it won't open for some reason, close it and exit.
	if (SQLITE_OK != retInt) {
		cout << "Error opening new database" << endl;
		sqlite3_close(testDB); 
		exit(1);
	}

	
////////////////////////  Create a new table in the DB  ////////////////////////


	// Prepare the SQL statement.
	const char* createArg = 
		"CREATE TABLE Tunes (Artist TEXT, Song TEXT, Album TEXT, Genre TEXT)";
		
	// Call sqlite3_exec() to pass the SQL statment in.
	retInt = sqlite3_exec(testDB, createArg, callback, 0, &zErrMsg);
	
	// If it doesn't work, report the error.
	if (SQLITE_OK != retInt) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}


/////////////////////////  Add to the new table  ///////////////////////////////


	// Let's add a bunch of songs. First we need to write some SQL to do it.
	
	// Concatenate a bunch of commands for adding single songs.
	string s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, sTot;
	s1 = "INSERT INTO Tunes VALUES ('Beck', 'Where Its At', 'Odelay', 'Alternative');";
	s2 = "INSERT INTO Tunes VALUES ('Beck', 'Novacane', 'Odelay', 'Alternative');";
	s3 = "INSERT INTO Tunes VALUES ('Michael Jackson', 'Thriller', 'Thriller', 'R&B');";
	s4 = "INSERT INTO Tunes VALUES ('Michael Jackson', 'Billie Jean', 'Thriller', 'R&B');";
	s5 = "INSERT INTO Tunes VALUES ('Allison Krauss', 'Baby, Now That Ive Found You', 'Now That Ive Found You', 'Bluegrass');";
	s6 = "INSERT INTO Tunes VALUES ('Curtis Mayfield', 'Move On Up', 'Curtis', 'Soul');";
	s7 = "INSERT INTO Tunes VALUES ('Miles Davis', 'Airegin', 'Cookin With The Miles Davis Quintet', 'Jazz');";
	s8 = "INSERT INTO Tunes VALUES ('Miles Davis', 'If I Were A Bell', 'Relaxin With The Miles Davis Quintet', 'Jazz');";
	s9 = "INSERT INTO Tunes VALUES ('Jeff Beck', 'All Shook Up', 'Beck-Ola', 'Blues Rock');";
	s10 = "INSERT INTO Tunes VALUES ('Elvis Presley', 'All Shook Up', 'All Shook Up', 'Rock n Roll');";
		
	// Concatenate into one SQL block:
	sTot = s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10;
	const char* addArg = sTot.c_str();
	
	// Pass it in.
	retInt = sqlite3_exec(testDB, addArg, callback, 0, &zErrMsg);
	
	// If it doesn't work, report the error.
	if (SQLITE_OK != retInt) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	
	
////////////////////////////  Query the table  /////////////////////////////////


	// Get the names of all the songs in the database from Thriller.
	const char* odeQue =
		"SELECT Song FROM Tunes WHERE Album = 'Odelay'";
	
	cout << endl << "Songs from Thriller in the database:" << endl << endl;
	retInt = sqlite3_exec(testDB, odeQue, callback, 0, &zErrMsg);
	
	// If it doesn't work, report the error.
	if (SQLITE_OK != retInt) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	
	
	// Get the artist names and albums for all soul and R&B tunes. List 
	// distinct values only once.
	const char* soulQue = 
		"SELECT DISTINCT Artist, Album FROM Tunes WHERE Genre = 'Soul' or Genre = 'R&B'";
	
	cout << endl << "Soul and R&B artists and albums in the database:" << endl
			<< endl;
	retInt = sqlite3_exec(testDB, soulQue, callback, 0, &zErrMsg);
	
	// If it doesn't work, report the error.
	if (SQLITE_OK != retInt) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	
	
	// Get the names of all artists named Beck.
	const char* beckQue = 
		"SELECT DISTINCT Artist FROM Tunes WHERE Artist like '%Beck%'";
	
	cout << endl << "Artists named Beck in the database:" << endl
			<< endl;
	retInt = sqlite3_exec(testDB, beckQue, callback, 0, &zErrMsg);
	
	// If it doesn't work, report the error.
	if (SQLITE_OK != retInt) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}

///////////////////////////  Create a table with blobs ////////////////////////

// Blobs are just blocks of data stored exactly as in memory. Here, we'll 
// create a table of phone numbers for various businesses. Since one typical 
// application of blobs might be storing arrays of integers, we'll store each
// phone number as an array of ints. To preserve generality, we'll let the 
// first entry of each array be the number of entries in the remainder of 
// the array.
	
	createArg = "CREATE TABLE ClubNums (bus TEXT, num BLOB)";

	// Call sqlite3_exec() to pass the SQL statment in.
	retInt = sqlite3_exec(testDB, createArg, callback, 0, &zErrMsg);
	
	// If it doesn't work, report the error.
	if (SQLITE_OK != retInt) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}

	// Let's create some business names and phone numbers. 
	const char* closedClub = "King Club";
	const char* newerClub = "High Noon Saloon";
	const char* badClub = "The Annex";
	const char* farClub = "Shank Hall"; 
	int ccNum [8] = {7, 2, 5, 1, 5, 4, 6, 4};
	int ncNum [8] = {7, 2, 6, 8, 1, 1, 2, 2};
	int bcNum [8] = {7, 2, 5, 6, 7, 7, 5, 0};
	int fcNum [11] = {10, 4, 1, 4, 2, 7, 6, 7, 2, 8, 8};

	// Put them in a map we can read from.
	map<const char*, int*> pairs;
	pairs[closedClub] = ccNum;
	pairs[newerClub] = ncNum;
	pairs[badClub] = bcNum;
	pairs[farClub] = fcNum;

	// Because you can't just put a blob into a text statement to pass into the 
	// database, you need to prepare a compiled INSERT statement. Let's do this 
	// for each item in the map.

	const char* currClub;
	int* currNum;
	sqlite3_stmt* compdStmt;
	int rc;

	while (!pairs.empty()) {

		currClub = (*pairs.begin()).first;
		currNum = (*pairs.begin()).second;

		// The first step is to write an SQL statement with wildcards.
		const char* stmtTxt = "INSERT INTO ClubNums(bus, num) VALUES(?,?)";

		// Then you compile the SQL command into an sqlite3 statment.
		rc = sqlite3_prepare(testDB, stmtTxt, -1, &compdStmt, 0);
		if(rc != SQLITE_OK) {
			cout << sqlite3_errmsg(testDB) << endl;
			exit(1);
		}

		// Now bind text and a blob, repectively, to the wildcards in the compiled
		// statement.
		sqlite3_bind_text(compdStmt, 1, currClub, -1, SQLITE_STATIC);
		sqlite3_bind_blob(compdStmt, 2, currNum, // Fixed bug caught by Rob Kennedy
											(currNum[0] + 1) * sizeof(int), SQLITE_STATIC);

		// Now run the virtual machine on this statement by calling sqlite3_step().
		sqlite3_step(compdStmt);

		// Remove the pair and reset the virtual machine.
		pairs.erase(pairs.begin());
		sqlite3_reset(compdStmt);

	}

	// Finalize the virtual machine to release its memory.
	sqlite3_finalize(compdStmt);

///////////////////////////  Query the table with blobs ///////////////////////

	// Create an SQL statment for a query on the database.
	const char* searchArg = "SELECT num FROM ClubNums WHERE bus = ?";
	
	// Compile the SQL command into an sqlite3 statment.
	rc = sqlite3_prepare(testDB, searchArg, -1, &compdStmt, 0);
	if (rc != SQLITE_OK) {
		cout << sqlite3_errmsg(testDB) << endl;
		exit(1);
	}

	// Decide which number we want to search for and bind it to the SQL variable.
	const char* getThisNum = "Shank Hall";
	sqlite3_bind_text(compdStmt, 1, getThisNum, -1, SQLITE_STATIC);

	// Execute the query.
	int* numArr;
	rc = sqlite3_step(compdStmt);
	if (SQLITE_ROW == rc) {

		// Get the size of the blob.
		int bytes = sqlite3_column_bytes(compdStmt, 0);
		
		// Allocate some memory and copy the blob.
		numArr = (int*) malloc(bytes);
		memcpy(numArr, sqlite3_column_blob(compdStmt, 0), bytes);

		// Release the resources from the compiled statement.
		sqlite3_finalize(compdStmt);
	}

	// Get the number of digits in the number.
	int digs = numArr[0];

	// Print 'em.
	cout << "Phone number for " << getThisNum << ": ";
	for (int i = 1; i <= digs; i++) {
		cout << numArr[i];
	}
	cout << endl;

////////////////////////////  Close the database  /////////////////////////////


	// Close the database.
	sqlite3_close(testDB);

}