#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

void print_result(sqlite3_stmt *stmt);

/* String function, 'hellow newman' from Allen and Owens book*/
void hello_newman(sqlite3_context* ctx, int nargs, sqlite3_value** values){
	const char *msg;

	/* Generate Newman's reply */
	msg = sqlite3_mprintf("Hello %s", sqlite3_value_text(values[0]));
	/* Set the return value. Have sqlite clean up msg w/ sqlite_free(). */
	sqlite3_result_text(ctx, msg, strlen(msg), sqlite3_free);
}

/* Double function that returns the square of a number */
void my_square_function(sqlite3_context* ctx, int nargs, sqlite3_value** values){
	double x = sqlite3_value_double(values[0]);
	double y = x*x;
	sqlite3_result_double(ctx, y);
}

int main(int argc, char **argv){
	sqlite3 *db; //the database
	sqlite3_stmt *stmt_q; //the SQL statement
	int rc;

	rc = sqlite3_open("mydb.sql", &db);
	if( rc ){
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;
	}

	/* can only create the function after the db connection is established */
	sqlite3_create_function( db, "hello_newman", 1, SQLITE_UTF8, NULL, hello_newman, NULL, NULL);
	sqlite3_create_function( db, "square", 1, SQLITE_UTF8, NULL, my_square_function, NULL, NULL);

	/* the functions can now be used in regular SQL! */
	char *sql_qry = "select hello_newman(name), score, square(score) as s_score " \
			 "from mytable " \
			 "where id < 1003 and s_score > 10;";
	rc = sqlite3_prepare_v2(db, sql_qry, -1, &stmt_q, 0);

	if (rc != SQLITE_OK) {  
		fprintf(stderr, "Failed preparing statement: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;
	}

	print_result(stmt_q);
	sqlite3_finalize(stmt_q); //always finalize a statement

	sqlite3_close(db);
	return 0;
}

void print_result(sqlite3_stmt *stmt){
	int rc;

	while((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
		int col;
		for(col=0; col<sqlite3_column_count(stmt)-1; col++) {
			printf("%s|", sqlite3_column_text(stmt, col));
		}
		printf("%s", sqlite3_column_text(stmt, col));
		printf("\n");
	}
}