SQLite benchmark program

sqlitemark.d

/*********************************************************************************************************************************************
**			GNU LESSER GENERAL PUBLIC LICENSE
**			   Version 2.1, February 1999
**	
**	Copyright (C) 1991, 1999 Free Software Foundation, Inc.
**	51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
**	Everyone is permitted to copy and distribute verbatim copies
**	of this license document, but changing it is not allowed.
**	
**	This is the first released version of the Lesser GPL.  It also counts
**	as the successor of the GNU Library Public License, version 2, hence
**	the version number 2.1.]
**
**
**
**	SQLite Benchmark v 1.0 by Guillemro Estrada, March 2010
**	
**	Tested with:
**	DMD 1.056 + TANGO 0.99 + SQLITE 3.6.20
**	
**
*********************************************************************************************************************************************/

module sqlitemark;

import tango.io.Stdout;
import tango.io.Console;
import tango.io.FilePath;
import tango.io.device.File;
import tango.stdc.stringz;
import tango.time.StopWatch;
import tango.math.random.Kiss;
import tango.net.http.HttpPost;
import Float = tango.text.convert.Float;
import Integer = tango.text.convert.Integer;
import sqlite3;

char[] VERSION = "1.0";

void main(char[][] args) {
	
	try {
		auto version_check = new HttpPost ("http://sqlitemark.heroku.com/version");
		if( VERSION != cast(char[]) version_check.write ) {
			Stdout("THERE IS A NEW VERSION AVAILABLE AT http://sqlitemark.heroku.com").newline;
			Stdout("PRESS ANY KEY TO CONTINUE...").flush;
			Cin.copyln();
		}
	}
	catch(Exception e) {}
	
	uint MAX_VALUES, MAX_TABLES;
	
	if( args.length == 1 ) {
		MAX_VALUES = 1000000;
		MAX_TABLES = 1000;
	}
	else if( args.length == 3 ) {
		MAX_VALUES = Integer.parse(args[1]);
		MAX_TABLES = Integer.parse(args[2]);
	}
	else {
		Stdout("Usage:").newline;
		Stdout("    > sqlitemark.exe MAX_VALUES MAX_TABLES").newline;
		Stdout("    > sqlitemark.exe (no arguments defaults to 1M and 1K)").newline;
		Stdout("Make sure MAX_VALUES % MAX_TABLES == 0 or you will get an assert exception.").newline;
		return;
	}
	/*********************************************************************************************************************************************/
	// INITIALIZING VARIABLES AND DECLARING GLOBAL DATA
	assert( MAX_VALUES % MAX_TABLES == 0 );
	uint PER_TABLE = MAX_VALUES/MAX_TABLES;
	
	Stdout().newline;
	Stdout("***WELCOME TO SQLITE MARK 1.0***").newline;

	Stdout().newline;
	Stdout("1) Initializing system variables, timers and data...").flush;
	StopWatch timer, general;
	Kiss rand = Kiss();
	general.start();
	sqlite3* db;
	sqlite3_stmt* state;
	
	//float[3][] array, store_single, store_many;

	uint count;
	float[] oax,oay,oaz,ssx,ssy,ssz,smx,smy,smz;
	oax.length = oay.length = oaz.length = MAX_VALUES;
	ssx.length = ssy.length = ssz.length = MAX_VALUES;
	smx.length = smy.length = smz.length = MAX_VALUES;
	
	Stdout("DONE!").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// INITIALIZING VARIABLES AND DECLARING GLOBAL DATA
	
	/*********************************************************************************************************************************************/
	// GENERATING MAX_VALUES RANDOM VERTEX float[3] ARRAY
	Stdout("2) Generating ")(MAX_VALUES)(" random vertex float[3] array... ").flush;
	timer.start();
	
	for( int i=0; i < MAX_VALUES; i++ ) {
		//array ~= [rand.fraction(),rand.fraction(),rand.fraction()];
		oax[i] = rand.fraction();
		oay[i] = rand.fraction();
		oaz[i] = rand.fraction();
	}
	timer.stop();
	Stdout("DONE!").newline;
	float generate_random = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(generate_random)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// GENERATING MAX_VALUES RANDOM VERTEX float[3] ARRAY
	
	/*********************************************************************************************************************************************/
	// CREATING A SINGLE TABLE
	Stdout("3) Creating a SINGLE table!").newline;
	timer.start();
	char[] table = "CREATE TABLE vertex (
	id INTEGER PRIMARY KEY NOT NULL,
	x REAL NOT NULL,
	y REAL NOT NULL,
	z REAL NOT NULL)";
	Stdout("OPEN : ")( sqlite3_open("single.db", &db) ).newline;
	Stdout("PREP : ")( sqlite3_prepare(db, toStringz(table),  -1, &state, null) ).newline;
	Stdout("STEP : ")( sqlite3_step(state) ).newline;
	Stdout("FINAL: ")( sqlite3_finalize(state) ).newline;
	Stdout("CLOSE: ")( sqlite3_close(db) ).newline;
	timer.stop();
	float create_single = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(create_single)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// CREATING A SINGLE TABLE
	
	/*********************************************************************************************************************************************/
	// INSERTING MAX_VALUES VALUES INTO A SINGLE TABLE
	Stdout("4) Inserting ")(MAX_VALUES)(" vertex(3) into a SINGLE table!").newline;
	timer.start();
	Stdout("OPEN : ")( sqlite3_open("single.db", &db) ).newline;
	sqlite3_prepare(db, "BEGIN;",  -1, &state, null);
	Stdout("BEGIN: ")( sqlite3_step(state) ).newline;
	sqlite3_finalize(state);
	sqlite3_prepare(db, "INSERT INTO vertex VALUES(NULL, ?, ?, ?);",  -1, &state, null);
	Stdout("Inserting... ").flush;
	for(int i = 0; i < MAX_VALUES; i++) {
		/*
		sqlite3_bind_double(state, 1, array[i][0]);
		sqlite3_bind_double(state, 2, array[i][1]);
		sqlite3_bind_double(state, 3, array[i][2]);
		*/
		sqlite3_bind_double(state, 1, oax[i]);
		sqlite3_bind_double(state, 2, oay[i]);
		sqlite3_bind_double(state, 3, oaz[i]);
		
		sqlite3_step(state);
		sqlite3_reset(state);
	}
	sqlite3_finalize(state);
	Stdout("DONE!").newline;
	sqlite3_prepare(db, "COMMIT;",  -1, &state, null);
	Stdout("COMMIT: ")( sqlite3_step(state) ).newline;
	sqlite3_finalize(state);
	Stdout("CLOSE: ")( sqlite3_close(db) ).newline;
	timer.stop();
	float insert_single = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(insert_single)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// INSERTING MAX_VALUES VALUES INTO A SINGLE TABLE
	
	/*********************************************************************************************************************************************/
	// SELECTING MAX_VALUES VALUES FROM SINGLE TABLE
	Stdout("5) Selecting ")(MAX_VALUES)(" vertex(3) in a SINGLE table!").newline;
	timer.start();
	Stdout("OPEN : ")( sqlite3_open("single.db", &db) ).newline;
	sqlite3_prepare(db,"SELECT * FROM vertex;",  -1, &state, null);
	Stdout("Selecting... ").flush;
	
	count = 0;
	
	while( sqlite3_step(state) == SQLITE_ROW ) {
		
		ssx[count] = sqlite3_column_double(state, 1);
		ssy[count] = sqlite3_column_double(state, 2);
		ssz[count] = sqlite3_column_double(state, 3);
		count++;
		
		//store_single ~= [sqlite3_column_double(state, 1),sqlite3_column_double(state, 2),sqlite3_column_double(state, 3)];
		
	}
	sqlite3_finalize(state);
	Stdout("DONE!").newline;
	Stdout("CLOSE: ")( sqlite3_close(db) ).newline;
	timer.stop();	
	float select_single = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(select_single)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// SELECTING MAX_VALUES VALUES FROM SINGLE TABLE
	
	/*********************************************************************************************************************************************/
	// ASSERTION TEST
	Stdout("ASSERTION TEST SINGLE").newline;
	Stdout("Comparing length and data... ").flush;
	timer.start();
	bool test_single;
	try {
		//assert( store_single.length == MAX_VALUES );
		//assert( store_single == array );
		for( int i = 0; i < MAX_VALUES; i++ ) {
			
			assert( ssx[i] == oax[i] );
			assert( ssy[i] == oay[i] );
			assert( ssz[i] == oaz[i] );
			
			/*
			assert( store_single[i][0] == array[i][0] );
			assert( store_single[i][1] == array[i][1] );
			assert( store_single[i][2] == array[i][2] );
			*/
		}
		Stdout("PASS!").newline;
		test_single = true;
	}
	catch(Exception e) {
		Stdout("FAIL!").newline;
		test_single = false;
	}
	timer.stop();
	float assert_single = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(assert_single)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// ASSERTION TEST
	
	/*********************************************************************************************************************************************/
	// CREATING MANY (MAX_TABLES) TABLES
	Stdout("6) Creating MANY(")(MAX_TABLES)(") tables!").newline;
	timer.start();
	char[][] tables;
	Stdout("OPEN : ")( sqlite3_open("many.db", &db) ).newline;
	sqlite3_prepare(db, "BEGIN;",  -1, &state, null);
	Stdout("BEGIN: ")( sqlite3_step(state) ).newline;
	sqlite3_finalize(state);
	Stdout("Preparing and executing statements... ").flush;
	for( int i = 0; i < MAX_TABLES; i++ ) {
		tables ~= "CREATE TABLE vertex_" ~ Integer.toString(i) ~ "(
	id INTEGER PRIMARY KEY NOT NULL,
	x REAL NOT NULL,
	y REAL NOT NULL,
	z REAL NOT NULL)\n";
		sqlite3_prepare(db, toStringz(tables[i]),  -1, &state, null);
		sqlite3_step(state);
		sqlite3_finalize(state);
	}
	Stdout("DONE!").newline;
	sqlite3_prepare(db, "COMMIT;",  -1, &state, null);
	Stdout("COMMIT: ")( sqlite3_step(state) ).newline;
	sqlite3_finalize(state);
	Stdout("CLOSE: ")( sqlite3_close(db) ).newline;
	timer.stop();
	float create_many = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(create_many)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// CREATING MANY (MAX_TABLES) TABLES
	
	/*********************************************************************************************************************************************/
	// INSERTING MAX_VALUES VALUES INTO MANY (MAX_TABLES) TABLES
	Stdout("7) Inserting ")(MAX_VALUES)(" vertex(3) into MANY(")(MAX_TABLES)(") tables!").newline;
	timer.start();
	Stdout("OPEN : ")( sqlite3_open("many.db", &db) ).newline;
	sqlite3_prepare(db, "BEGIN;",  -1, &state, null);
	Stdout("BEGIN: ")( sqlite3_step(state) ).newline;
	sqlite3_finalize(state);
	Stdout("Inserting (")(PER_TABLE)(" per table)...").flush;
	for( int i = 0; i < MAX_TABLES; i++ ) {
		int offset = (PER_TABLE)*i;
		sqlite3_prepare(db, toStringz("INSERT INTO vertex_"~Integer.toString(i)~" VALUES(NULL, ?, ?, ?);"),  -1, &state, null);
		for(int j = 0; j < (PER_TABLE); j++) {
			
			sqlite3_bind_double(state, 1, oax[j+offset]);
			sqlite3_bind_double(state, 2, oay[j+offset]);
			sqlite3_bind_double(state, 3, oaz[j+offset]);
			
			/*
			sqlite3_bind_double(state, 1, array[j+offset][0]);
			sqlite3_bind_double(state, 2, array[j+offset][1]);
			sqlite3_bind_double(state, 3, array[j+offset][2]);
			*/
			sqlite3_step(state);
			sqlite3_reset(state);
		}
		sqlite3_finalize(state);
	}
	Stdout("DONE!").newline;
	sqlite3_prepare(db, "COMMIT;",  -1, &state, null);
	Stdout("COMMIT: ")( sqlite3_step(state) ).newline;
	sqlite3_finalize(state);
	Stdout("CLOSE: ")( sqlite3_close(db) ).newline;
	timer.stop();
	float insert_many = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(insert_many)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// INSERTING MAX_VALUES VALUES INTO MANY (MAX_TABLES) TABLES
	
	/*********************************************************************************************************************************************/
	// SELECTING MAX_VALUES VALUES FROM MANY (MAX_TABLES) TABLES
	Stdout("8) Selecting ")(MAX_VALUES)(" vertex(3) from MANY(")(MAX_TABLES)(") tables!").newline;
	timer.start();
	Stdout("OPEN : ")( sqlite3_open("many.db", &db) ).newline;
	Stdout("Selecting (")(PER_TABLE)(" per table)...").flush;
	count = 0;
	for( int i = 0; i < MAX_TABLES; i++ ) {
		sqlite3_prepare(db, toStringz("SELECT * FROM vertex_"~Integer.toString(i)~";"),  -1, &state, null);
		
		while( sqlite3_step(state) == SQLITE_ROW ) {
			
			smx[count] = sqlite3_column_double(state, 1);
			smy[count] = sqlite3_column_double(state, 2);
			smz[count] = sqlite3_column_double(state, 3);
			count++;
			
			//store_many ~= [sqlite3_column_double(state, 1),sqlite3_column_double(state, 2),sqlite3_column_double(state, 3)];
		}
		sqlite3_finalize(state);
	}
	Stdout("DONE!").newline;
	Stdout("CLOSE: ")( sqlite3_close(db) ).newline;
	timer.stop();
	float select_many = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(select_many)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// SELECTING MAX_VALUES VALUES FROM MANY (MAX_TABLES) TABLES
	
	/*********************************************************************************************************************************************/
	// ASSERTION TEST
	Stdout("ASSERTION TEST MANY").newline;
	Stdout("Comparing length and data... ").flush;
	timer.start();
	bool test_many;
	try {
		//assert( store_many.length == MAX_VALUES );
		//assert( store_many == array );
		for( int i = 0; i < MAX_VALUES; i++ ) {
			
			assert( smx[i] == oax[i] );
			assert( smy[i] == oay[i] );
			assert( smz[i] == oaz[i] );
			
			/*
			assert( store_many[i][0] == array[i][0] );
			assert( store_many[i][1] == array[i][1] );
			assert( store_many[i][2] == array[i][2] );
			*/
		}
		Stdout("PASS!").newline;
		test_many = true;
	}
	catch(Exception e) {
		Stdout("FAIL!").newline;
		test_many = false;
	}
	timer.stop();
	float assert_many = timer.microsec()/1000000.0;
	Stdout("                                                                 ")(assert_many)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	// ASSERTION TEST
	
	/*********************************************************************************************************************************************/
	//FULLTIME
	general.stop();
	float full_time = general.microsec()/1000000.0;
	Stdout("FULL BENCHMARK TIME: ")(full_time)(" sec").newline;
	Stdout("-------------------------------------------------------------------------------").newline;
	//FULLTIME
	
	/*********************************************************************************************************************************************/
	// CREATE TIME LOG AND DELETE DATABASE FILES 
	
	FilePath data_single = new FilePath("single.db");
	FilePath data_many = new FilePath("many.db");
	
	Stdout("Saving benchmark data in sqlitemark.txt... ").flush;
	FilePath log = new FilePath("sqlitemark.txt");
	if( log.exists() )
		log.remove();
	log.createFile();
	char[] marklog;
	
	//marklog ~= "SQLITE MARK 1.0  -  http://sqlitemark.heroku.com\n";
	marklog ~= "m_values: " ~ Integer.toString(MAX_VALUES) ~ "\n";
	marklog ~= "m_tables: " ~ Integer.toString(MAX_TABLES) ~ "\n";
	marklog ~= "generate: " ~ Float.toString(generate_random, 3) ~ "\n";
	marklog ~= "dbsize_s: "   ~ Float.toString(data_single.fileSize()/1000000.0) ~ "\n";
	marklog ~= "create_s: " ~ Float.toString(create_single, 3) ~ "\n";
	marklog ~= "insert_s: " ~ Float.toString(insert_single, 3) ~ "\n";
	marklog ~= "select_s: " ~ Float.toString(select_single, 3) ~ "\n";
	marklog ~= "assert_s: " ~ Float.toString(assert_single, 3);
	if( test_single )
		marklog ~= " PASS\n";
	else
		marklog ~= " FAIL\n";
	marklog ~= "dbsize_m: "   ~ Float.toString(data_many.fileSize()/1000000.0) ~ "\n";
	marklog ~= "create_m: " ~ Float.toString(create_many, 3) ~ "\n";
	marklog ~= "insert_m: " ~ Float.toString(insert_many, 3) ~ "\n";
	marklog ~= "select_m: " ~ Float.toString(select_many, 3) ~ "\n";
	marklog ~= "assert_m: " ~ Float.toString(assert_many, 3);
	if( test_many )
		marklog ~= " PASS\n";
	else
		marklog ~= " FAIL\n";
	marklog ~= "fulltest: " ~ Float.toString(full_time, 3);
	
	/*
	marklog ~= "SQLITE MARK 1.0\n\n";
	marklog ~= "GENERATE (" ~ Integer.toString(MAX_VALUES) ~ ")[3]\n";
	marklog ~= "random: " ~ Float.toString(generate_random, 3) ~ " sec\n\n";
	marklog ~= "SINGLE TABLE (1) ";
	marklog ~= Float.toString(data_single.fileSize()/1000000.0) ~ "MB.\n";
	marklog ~= "create: " ~ Float.toString(create_single, 3) ~ " sec.\n";
	marklog ~= "insert: " ~ Float.toString(insert_single, 3) ~ " sec.\n";
	marklog ~= "select: " ~ Float.toString(select_single, 3) ~ " sec.\n";
	marklog ~= "assert: " ~ Float.toString(assert_single, 3) ~ " sec.  ";
	if( test_single )
		marklog ~= "PASS!\n\n";
	else
		marklog ~= "FAIL!\n\n";
	marklog ~= "MANY TABLES (" ~ Integer.toString(MAX_TABLES) ~ ") ";
	marklog ~= Float.toString(data_many.fileSize()/1000000.0) ~ "MB.\n";
	marklog ~= "create: " ~ Float.toString(create_many, 3) ~ " sec.\n";
	marklog ~= "insert: " ~ Float.toString(insert_many, 3) ~ " sec.\n";
	marklog ~= "select: " ~ Float.toString(select_many, 3) ~ " sec.\n";
	marklog ~= "assert: " ~ Float.toString(assert_many, 3) ~ " sec.  ";
	if( test_many )
		marklog ~= "PASS!\n\n";
	else
		marklog ~= "FAIL!\n\n";	
	marklog ~= "FULL TEST: " ~ Float.toString(full_time, 4) ~ " sec.\n";
	*/
	
	File.set (log.file(), marklog);
	Stdout("DONE!").newline;
	
	float size_single = data_single.fileSize()/1000000.0;
	float size_many   = data_many.fileSize()/1000000.0;
	
	Stdout("Deleting the database file SINGLE ").flush;
	Stdout(data_single.fileSize()/1000000.0)("MB... ").flush;
	if( data_single.exists() )
		data_single.remove();
	Stdout("DONE!").newline;
	
	Stdout("Deleting the database file MANY ").flush;
	Stdout(data_many.fileSize()/1000000.0)("MB... ").flush;
	if( data_many.exists() )
		data_many.remove();
	Stdout("DONE!").newline;
	
	Stdout("-------------------------------------------------------------------------------").newline;
	// CREATE TIME LOG AND DELETE DATABASE FILES
	
	/*********************************************************************************************************************************************/
	// POST RESULTS ONLINE
	
	
	Stdout("Do you want to post your results online now? (yes/no) ").flush;
	char[] online;
	online = Cin.copyln();
	
	while( online == "yes" ) {
		
		online = "";
		
		auto post = new HttpPost ("http://sqlitemark.heroku.com/remote");
		auto params = post.getRequestParams();
		char[] cpu, speed, cores, name;
		
		Stdout("*CPU (ie. Intel Core2Quad Q6600): ").flush;
		cpu = Cin.copyln();
		
		Stdout("*SPEED [Ghz] (ie. 1.3 or 2.4): ").flush;
		speed = Cin.copyln();
		
		Stdout("*CORES (ie. 1, 2, 3, or 4): ").flush;
		cores = Cin.copyln();
		
		Stdout("*NAME (optional): ").flush;
		name = Cin.copyln();
		Stdout().newline;
		
		if( name == "" )
			name = "Anonymous";
		
		params.add("cpu", cpu);
		params.add("speed", speed);
		params.add("cores", cores);
		params.add("values",Integer.toString(MAX_VALUES));
		params.add("tables",Integer.toString(MAX_TABLES));
		params.add("generate", Float.toString(generate_random,3));
		params.add("size_single", Float.toString(size_single,2));
		params.add("create_single", Float.toString(create_single,3));
		params.add("insert_single", Float.toString(insert_single,3));
		params.add("select_single", Float.toString(select_single,3));
		params.add("assert_single", Float.toString(assert_single,3));
		if( test_single )
			params.add("single_passed","PASS");
		else
			params.add("single_passed","FAIL");
		params.add("size_many", Float.toString(size_many,2));
		params.add("create_many", Float.toString(create_many,3));
		params.add("insert_many", Float.toString(insert_many,3));
		params.add("select_many", Float.toString(select_many,3));
		params.add("assert_many", Float.toString(assert_many,3));
		if( test_many )
			params.add("many_passed","PASS");
		else
			params.add("many_passed","FAIL");	
			
		params.add("full_test", Float.toString(full_time, 4));
		params.add("name", name);
		
		char[] response = cast(char[]) post.write();
		Stdout(">")(response).newline;
		
		if( response[0..6] == "ERRORS" ) {
			Stdout("Try again? (yes/no) ").flush;
			online = Cin.copyln();
		}
	}
	Stdout("Check/Post your results on http://sqlitemark.heroku.com").newline;
	
	// POST RESULTS ONLINE
	
	/*********************************************************************************************************************************************/
	// EXIT
	Stdout("Press ENTER to exit...").flush;
	Cin.copyln();
	// EXIT
	
	/*********************************************************************************************************************************************/
	
	return;
	
}