Category Archives: sqlite

Erlang : SQLite related stuff

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% SQLite related functions
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

is_sqlite_enabled() ->
	try application:get_env(team, sqlite_enabled) of
		% sqlite enabled
		{ok, true}->
			true;
		{ok, false}->
		% sqlite disabled
			false;
		_ ->
			?INFO("ERROR - Check sqlite_enabled field in team sys.config", [])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.

get_drv_path() ->
	try application:get_env(team, sqlite_drv_path) of
		{ok, DrvPath}->
			%?DEBUG("driver path : ~p", [DrvPath]),
			DrvPath;		
		_ ->
			?INFO("ERROR - Check sqlite_drv_path field in team sys.config", [])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.

get_drv_name() ->
	try application:get_env(team, sqlite_drv_name) of
		{ok, DrvName}->
			%?DEBUG("driver name : ~p", [DrvName]),
			DrvName;		
		_ ->
			?INFO("ERROR - Check sqlite_drv_name field in team sys.config", [])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.

% team_db:load_sqlite_driver().
load_sqlite_driver() ->
	try erl_ddll:load(get_drv_path(), get_drv_name()) of
		ok ->
			?DEBUG("sqlite3 driver loaded", []);
		Else -> 
			?DEBUG("sqlite3 error loading driver ~p", [Else])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.

% team_db:unload_sqlite_driver().
unload_sqlite_driver() ->
	try erl_ddll:unload(get_drv_name()) of
		ok ->
			?DEBUG("sqlite3 driver unloaded", []);
		Else ->
			?DEBUG("sqlite3 error unloading driver ~p", [Else])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.
	
create_db_conn(DbName, DbPath) ->
	?DEBUG("~p",[{create_db_conn, [DbName, DbPath]}]),
	case catch sqlite3:open(list_to_atom(DbName), [{db, DbPath ++ DbName ++ ".db"}]) of
		{ok, Pid} ->
			?DEBUG("db connection created ~p", [Pid]);
		{error, {already_started, Pid_}} ->
			?DEBUG("already started ~p", [Pid_]),
			close_db_conn(DbName, DbPath),
			create_db_conn(DbName, DbPath);
		Else ->
			?DEBUG("error creating db conn ~p", [Else])
	end.

close_db_conn(DbName, DbPath) ->
	?DEBUG("~p",[{close_db_conn, [DbName]}]),
	case catch sqlite3:close(list_to_atom(DbName)) of
		ok ->
			?DEBUG("db connection closed", []);
		%{'EXIT',{noproc, X_ }} ->			
		Else ->
			?DEBUG("error closing db conn ~p", [Else])
	end.

get_db_path(EA) ->
	try application:get_env(team, sqlite_db_path) of
		{ok, Path}->
			Path ++ "/" ++ EA ++ "/";		
		_ ->
			?INFO("ERROR - Check sqlite_db_path field in team sys.config", [])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.

get_numlist_tblname() ->
	try application:get_env(team, sqlite_numlist_tblname) of
		{ok, TblName}->
			TblName;		
		_ ->
			?INFO("ERROR - Check sqlite_numlist_tblname field in team sys.config", [])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.

get_numfield_name() ->
	try application:get_env(team, sqlite_numfield_name) of
		{ok, FieldName}->
			FieldName;		
		_ ->
			?INFO("ERROR - Check sqlite_numfield_name field in team sys.config", [])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.

get_msgfield_name() ->
	try application:get_env(team, sqlite_msgfield_name) of
		{ok, FieldName}->
			FieldName;		
		_ ->
			?INFO("ERROR - Check sqlite_msgfield_name field in team sys.config", [])
	catch
		Type:Error ->
			?ERROR("~p", [{Type, Error}])
	end.

get_scalar_result(Camp, Sql) ->
	try sqlite3:sql_exec(list_to_atom(Camp), Sql) of
		[{columns, _}, {rows,[{Result}]}] ->
			Result;
		Else -> 
			?DEBUG("scalar result : ~p", [Else])
	catch
		Type:Error ->
			?DEBUG("sql exec exception ~p", [{Type, Error}])
	end.


% team_db:get_row_count("camp:system:x_num_only").
get_row_count(Key) ->
	[ _, EA, Camp | _ ] = string:tokens(Key, ":"),
	?DEBUG("~p",[{get_row_count, [EA, Camp]}]),
	Sql = "SELECT COUNT(*) FROM " ++ atom_to_list(get_numlist_tblname()) ++ ";",
	RowCount = get_scalar_result(Camp, Sql),
	RowCount.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

% team_db:create_db_conn("camp:system:x_num_only").

create_db_conn(Key) ->
	[ _, EA, Camp | _ ] = string:tokens(Key, ":"),
	create_db_conn(Camp, get_db_path(EA)).	

% team_db:close_db_conn("camp:system:x_num_only").
close_db_conn(Key) ->
	[ _, _, Camp | _ ] = string:tokens(Key, ":"),
	close_db_conn(Camp, null).


fetch_chunk(number_only, DbName, StartFrom, ChunkSize) ->
	Sql = "SELECT " ++ atom_to_list(get_numfield_name()) ++ " FROM " ++ atom_to_list(get_numlist_tblname())  ++  " LIMIT " ++ integer_to_list(StartFrom) ++ ","  ++ integer_to_list(ChunkSize) ++ ";",
	[{columns, _}, {rows, Rows}] = sqlite3:sql_exec(list_to_atom(DbName), Sql),	
	Rows.

fetch_chunk(DbName, StartFrom, ChunkSize) ->
	Sql = "SELECT " ++ atom_to_list(get_numfield_name()) ++ " , " ++ atom_to_list(get_msgfield_name()) ++ " FROM " ++ atom_to_list(get_numlist_tblname())  ++  " LIMIT " ++ integer_to_list(StartFrom) ++ ","  ++ integer_to_list(ChunkSize) ++ ";",
	[{columns, _}, {rows, Rows}] = sqlite3:sql_exec(list_to_atom(DbName), Sql),	
	Rows.


sq_create_db_conn(Key) ->
	case is_sqlite_enabled() of
	  true ->
		 create_db_conn(Key)
  end. 

sq_close_db_conn(Key) ->
	case is_sqlite_enabled() of
	  true ->
		  close_db_conn(Key)
  end. 

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%% Default Fucntions - modified
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

%% scard is modified to deal with sqlite
%% original fucntion team_db:scard
sq_scard(Key) -> 
	case is_sqlite_enabled() of
	  true ->
		 create_db_conn(Key),
		 RowCount = get_row_count(Key),
		 close_db_conn(Key),
		 RowCount;
	  false ->
		  scard(?DB0, Key)
  end. 

%% sunionstore is modified to deal with sqlite
%% original function team_db:sunionstore/3
sq_sunionstore(DstKey, Keys) ->  
	case is_sqlite_enabled() of
	  true ->
		 ?DEBUG("~p", [{sunionstore}]); 
	  false ->
		  sunionstore(?DB0, DstKey, Keys)
	end.

%% spop is modified to deal with sqlite
%% original function team_db:spop/2
sq_spop(Key, Chunk) ->	
	%?DEBUG("~p", [{Key, Chunk}]),
	case is_sqlite_enabled() of
	  true ->	
		 %?DEBUG("~p", [{sq_spop, true}]),
		  case Chunk of
			  [ {Dst} | Rest ] ->
				  ?DEBUG("~p", [binary_to_list(Dst)]),
				  {binary_to_list(Dst), Rest};
			  [ {Dst, Msg} | Rest ] ->
				  ?DEBUG("~p", [{binary_to_list(Dst), binary_to_list(Msg)}]),
				  {binary_to_list(Dst), binary_to_list(Msg), Rest}
		  end;
	  false ->
  		{spop(?DB0, Key), Chunk}
	end.

%% hget is modified to deal with sqlite
%% original function team_db:hget/2
sq_hget(Key, Field, Msg) ->
	case is_sqlite_enabled() of
	  true ->
		  ?DEBUG("~p", [Msg]),
		  [Msg];
		 %%["{\"<num>\":\"x\",\"<ppc>\":\"xyz\"}"];
	  false ->
		   hget(?DB0, Key, Field)
	end.

Advertisements

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.

Erlang : SQLite data record manipulation

-module(sqlite_3).
-export([test/0]).

rec([]) ->
        empty;
rec([H|T]) ->
        {Dst, Msg} = H,
        io:fwrite("DST : ~p~n", [binary_to_list(Dst)]),
        io:fwrite("MSG : ~p~n~n", [binary_to_list(Msg)]),
        rec(T).

test() ->
        % substitute for SQLite data record
        Record = [
                {columns,[dst,msg]},
                {rows,
                        [{<<"94770000000">>,<<"msg 1">>},
                        {<<"94770000001">>,<<"msg 2">>}]
                }
                ],      
        [{columns, Columns} , {rows, Rows}]  = Record,
        rec(Rows).

Sample output

[root@sam test]# erl
Erlang R14B (erts-5.8.1)  [smp:2:2] [rq:2] [async-threads:0] [hipe] [kernel-poll:false]

Eshell V5.8.1  (abort with ^G)
1> c(sqlite_3).
./t.erl:21: Warning: variable 'Columns' is unused
{ok,sqlite_3}
2> sqlite_3:test().
DST : "94770000000"
MSG : "msg 1"

DST : "94770000001"
MSG : "msg 2"

empty
3> 

Erlang : How to load sqlite3_drv.so

Create sqlite_1.erl

vim sqlite_1.erl

i have created it inĀ /usr/local/myErl/

File content

-module(sqlite_1).
-export([load_driver/0]).
-define(DRV_PATH, "/opt/drivers/").
-define(DRV_NAME, "sqlite3_drv").

%% loads the sqlite_drv.so
load_driver() ->
  case erl_ddll:load(?DRV_PATH, ?DRV_NAME) of
    ok ->
      ok;
  Else ->
    io:fwrite("Unable to load sqlite3_drv~p~n", [{Else}])
  end.

How to run

[root@sam myErl]# erl
<pre>Erlang R14B (erts-5.8.1)

[smp:2:2] [rq:2] [async-threads:0] [hipe] [kernel-poll:false]
Eshell V5.8.1 (abort with ^G)
1> c(sqlite_1).
{ok,sqlite_1}
2> m(sqlite_1).
Module sqlite_1 compiled: Date: December 11 2011, Time: 08.16
Compiler options: []
Object file: /usr/local/myErl/sqlite_1.beam
Exports:
 load_driver/0
 module_info/0
 module_info/1
ok
3> sqlite_1:load_driver().
ok
4>