Erlang : Test module – SQLite based number list

ltest.hrl

%% Author: Sam
%% Created: Dec 14, 2011

-define(SQLITE_DRV_NAME, sqlite3_drv).
-define(SQLITE_DRV_PATH, "/opt/slib").

-define(DB_NAME, tdb).
-define(DB_PATH, "/root/ltest/db/").

-define(TBL_NAME, ttbl).
-define(TBL_STRUCT, [{dst, text}, {msg, text}]).

-define(LOG(Msg), io:format("Log ~p ~p ~n", [time(), Msg])).

-define(CHUNK_SIZE, 50).
-define(START_FROM, 94770000000).

-define(REDIS_HOST, "localhost").
-define(REDIS_PORT, 6379).
-define(RH_KEY, <<"sqliteX">>).

sqlite_db.erl

%% Author: Sam
%% Created: Dec 14, 2011

-module(sqlite_db).
-compile(export_all).

-export([create_db/2, create_table/3]).
-export([close_db/1]).

insert_chunk(DbName, Sql) ->
	sqlite3:sql_exec(DbName, Sql).

get_record_count(DbName, TblName) ->
	Sql = "SELECT COUNT(*) FROM " ++ atom_to_list(TblName),
	sqlite3:sql_exec(DbName, Sql).

fetch_chunk(DbName, TblName, StartFrom, ChunkSize) ->
	Sql = "SELECT * FROM " ++ atom_to_list(TblName)  ++  " LIMIT " ++ integer_to_list(StartFrom) ++ ","  ++ integer_to_list(ChunkSize)  ++ ";",
	[{columns, Columns}, {rows, Rows}] = sqlite3:sql_exec(DbName, Sql),
	Rows.

close_db(Dbname) ->
	case catch sqlite3:close(Dbname) of
		ok ->
			io:format("DB Connection closed~n");
		Else ->
			io:format("Error closing db connection ~p~n", [Else])
	end.

create_db(DbName, DbPath) ->
	case catch sqlite3:open(DbName, [{db, DbPath ++ atom_to_list(DbName) ++ ".db"}]) of
		{ok, Pid} ->
			io:format("Database created ~p~n", [Pid]);
		Else ->
			io:format("Error creating database ~p~n", [Else])
	end.

create_table(DbName, TblName, TblStruct) ->
        case catch sqlite3:create_table(DbName, TblName, TblStruct) of
			{ok, 101} ->
				io:format("Table created~n");
			Else ->
				io:format("Error creating table ~p~n", [Else])
		end.

ltest.erl

%% Author: Sam
%% Created: Dec 14, 2011

-module(ltest).
-include("ltest.hrl").

-export([go/0]).
-export([test_suite_v/3, test_suite/3]).
-export([build_chunk/3]).
-export([unload_sqlite_driver/1]).

test_suite(SSize, SChunk, RChunk) ->
	file:delete(?DB_PATH ++ atom_to_list(?DB_NAME) ++ ".db" ),
	?LOG("test suite started"),
	load_sqlite_driver(?SQLITE_DRV_PATH, ?SQLITE_DRV_NAME),
	sqlite_db:create_db(?DB_NAME, ?DB_PATH),
	sqlite_db:create_table(?DB_NAME, ?TBL_NAME, ?TBL_STRUCT),
	?LOG("SQLite data insertion started"),
	insert_many(SSize, ?DB_NAME, SChunk),
	?LOG("SQLite data insertion finished"),
	connect_redis(),
	?LOG("Loading to Redis started"),
	fetch_and_load(?DB_NAME, ?TBL_NAME, RChunk),
	?LOG("Loading to Redis finished"),
	sqlite_db:close_db(?DB_NAME),
	unload_sqlite_driver(?SQLITE_DRV_NAME),
	?LOG("test suite finished").


test_suite_v(SSize, SChunk, RChunk) ->
	file:delete(?DB_PATH ++ atom_to_list(?DB_NAME) ++ ".db" ),
	?LOG("test suite V(verbose) started"),
	load_sqlite_driver(?SQLITE_DRV_PATH, ?SQLITE_DRV_NAME),
	sqlite_db:create_db(?DB_NAME, ?DB_PATH),
	sqlite_db:create_table(?DB_NAME, ?TBL_NAME, ?TBL_STRUCT),
	?LOG("SQLite data insertion started"),
	insert_many(SSize, ?DB_NAME, SChunk),
	?LOG("SQLite data insertion finished"),
	connect_redis(),
	?LOG("Loading to Redis started"),
	fetch_and_load(?DB_NAME, ?TBL_NAME, RChunk),
	?LOG("Loading to Redis finished"),
	sqlite_db:close_db(?DB_NAME),
	unload_sqlite_driver(?SQLITE_DRV_NAME),
	?LOG("test suite V finished").

go() ->
	load_sqlite_driver(?SQLITE_DRV_PATH, ?SQLITE_DRV_NAME).

connect_redis() ->	
	{ok, RedisDb} = erldis:connect(?REDIS_HOST, ?REDIS_PORT),
	register(redis_db, RedisDb).
	
select_record([]) ->
	done;
	%?LOG("Chunk loaded to Redis");
select_record([H|T]) ->
	load_data(H),
	select_record(T).

load_data({Dst, Msg}) ->
	erldis:hset(redis_db, ?RH_KEY, Dst, Msg).

fetch_and_load(DbName, TblName, ChunkSize) ->
	erldis:del(redis_db, ?RH_KEY),
	[{columns, Columns}, {rows, Result}] = sqlite_db:get_record_count(DbName, TblName),
	[{RowCount}] = Result,
	fetch_and_load(RowCount, ChunkSize, 0, DbName, TblName).
fetch_and_load(0, _, _, _, _) ->
	done;
fetch_and_load(RowCount, ChunkSize, StartFrom, DbName, TblName) ->
	case RowCount >= ChunkSize of
		true ->
			Chunk = sqlite_db:fetch_chunk(DbName, TblName, StartFrom, ChunkSize),
			%?LOG(integer_to_list(ChunkSize) ++ " record(s) fetched"),
			select_record(Chunk),
			fetch_and_load(RowCount-ChunkSize, ChunkSize, StartFrom+ChunkSize, DbName, TblName);
		false ->
			fetch_and_load(RowCount, RowCount, StartFrom, DbName, TblName)
	end.


insert_many(NumRecords, DbName, ChunkSize) ->
	insert_many(NumRecords, ChunkSize, ?START_FROM, DbName).
insert_many(0, ChunkSize, StartFrom, DbName) ->
	done;
insert_many(Size, ChunkSize, StartFrom, DbName) ->
	case Size >= ChunkSize of
		true ->
			{LastNumber, TempSqlString} = build_chunk(ChunkSize, StartFrom),
			SqlString = "insert or ignore into ttbl select '94770000000' as dst, 'test message' as msg" ++ TempSqlString ++ ";",
			%io:fwrite("~p~n", [SqlString]),
			sqlite_db:insert_chunk(DbName, SqlString),
			%?LOG(integer_to_list(ChunkSize) ++ " record(s) added"),
			insert_many(Size-ChunkSize, ChunkSize, LastNumber, DbName);
		false ->
			insert_many(abs(Size-ChunkSize), abs(Size-ChunkSize), StartFrom, DbName)
	end.

build_chunk(Size, Data) ->
	build_chunk(Size, Data, "").
build_chunk(0, Data, SqlString) ->
	%io:fwrite("~p", SqlString),
	{Data, SqlString};
build_chunk(Count, Data, SqlString) ->
	% union select 94773785550, "msg 2"
	Record = " union select '" ++ integer_to_list(Data) ++ "', 'test message'",
	Temp = SqlString ++ Record,
	build_chunk(Count-1, Data+1, Temp).

unload_sqlite_driver(DrvName) ->
	try erl_ddll:unload(DrvName) of
		ok ->
			io:format("sqlite3 driver unloaded~n");
		Else ->
			io:format("sqlite3 Error unloading driver ~p~n", [Else])
	catch
		Type:Error ->
			{Type, Error}
	end.

load_sqlite_driver(DrvPath, DrvName) ->
	try erl_ddll:load(DrvPath, DrvName) of
		ok ->
			io:format("sqlite3 driver loaded~n");
		Else -> 
			io:format("sqlite3 Error loading driver ~p~n", [Else])
	catch
		Type:Error ->
			{Type, Error}
	end.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: