Control Panel E-Mail
TCP/IP SQLite 3 Embedded database solutions made easy.
netSqlite
Wallzilla main program page

This is an embedded SQL tcp/ip client/server solution, based on SQLite 3. We’ve added security privileges and user management rights.


To embed the server and client into your solution, you only need to add one file to your project and load SQLQueryAS.dll to get the interfaces to the objects you need. For samples of use see below.


This DLL conains both the client and the server. There is no need for other files, SQLite 3 has been embedded into the dll.

The DLL exports most of the sqlite3 functions. So, if you need to access database files directly, simply import SQLQueryAS.lib and sqlite3.h and start calling the sqlite3 functions.


Public Domain

System Requierment
Windows 95 Windows 98 Windows Me Windows NT 4.0
Windows 2000 Windows 2003 Windows XP Windows Vista



Futures
SQLite 3 Stability
netSqlite can be viewed as a proxy between the SQLite3 and the client. With some minor changes you basically have the same parameters as SQLite3 functions have.
Cashing
netSQLite has a caching algorithm that is there to reduce network bandwidth use.
Server Cleanup
netSqlite is build to prevent memory leaks, by keeping track of client resources and releasing them if the connection is lost.
One file for everything.
Everything you need to deal with sqlite3 and netsqlite is contained in one file. If you need the sqlite3 functions, the dll exports them. If you need netsqlite, you have the interfaces.


Basics

Build time requirement.

To embed the server or client into your projects you need to link to ‘SQLiteQueryAS.dll’, and get the interfaces to the desired functionality. To help you with that and link to it dynamically you can use <LoadPlugin.h>. The interface declarations are in <ISQLQueryAS.h>.

Run-time requirement.

During the run-time the security database has to exist, If the database is missing the client will not be able to open any databases. See below for the security database details.

Security DB helper interface.

There is a helper interface that helps you deal with the security database. Also included in this page is an example which demonstrates the majority of the functions the interface has. examples



Security Database

The server requires one file to exist in order to control user access rights. That is, it requires the "security database" which you set the path to using the following interface. 

ISQLiteServer::SetSecurityDatabase()

The security database has to contain one table called db_list. This table contains the list of databases which the server can access. If the client requests a database that is not on this list, the client is denied access. 

CREATE TABLE db_list ( 
    id             INTEGER         PRIMARY KEY AUTOINCREMENT
                                   NOT NULL
                                   UNIQUE,
    db_name        TEXT            NOT NULL
                                   UNIQUE,
    db_path        TEXT            NOT NULL,
    db_users_table VARCHAR( 100 ) 
);

The db_name field contains the name of the database. This field has to match to the filename of the database without the .db extension.

The db_path field contains the path to the folder containing the database.

The db_users_table field contains the table name which contains the users which have access to the database.


Example

db_name = 'myDatabase' 
db_path = 'C:\databases\' 
db_users_table = 'tbl_1' 

The above will be translated by the server into:
C:\databases\myDatabase.db
and tbl_1 will be checked for the username, password and access flags.

The users table has the following format. 

CREATE TABLE tbl_1 ( 
    id         INTEGER         PRIMARY KEY
                               NOT NULL
                               UNIQUE,
    username   VARCHAR( 100 )  NOT NULL
                               UNIQUE,
    password   VARCHAR( 100 )  NOT NULL,
    permission INTEGER 
);

It is important to mention that tbl_1 can be any name. But that name has to match db_list.db_users_table field.

The username field contains the username case sensitive.

The password field contains the password case sensitive.

The permission field contains the access rights the user has to the database.


The permission field is or'ed with the below values.

typedef struct _tag_security_permission
{
		static const DWORD GRANT_CREATE_TBALE		= 0x00000001;
		static const DWORD GRANT_CREATE_VIEW		= 0x00000002;
		static const DWORD GRANT_CREATE_TRIGGER		= 0x00000004;
		
        static const DWORD GRANT_CREATE_ALL			= 
        GRANT_CREATE_TBALE|GRANT_CREATE_VIEW|GRANT_CREATE_TRIGGER;

		static const DWORD GRANT_DROP_TABLE			= 0x00000008;
		static const DWORD GRANT_DROP_VIEW			= 0x00000010;
		static const DWORD GRANT_DROP_TRIGGER		= 0x00000020;
		
        static const DWORD GRANT_DROP_ALL			= 
        GRANT_DROP_TABLE|GRANT_DROP_VIEW|GRANT_DROP_TRIGGER;

		static const DWORD GRANT_OP_SELECT			= 0x00000040;
		static const DWORD GRANT_OP_INSERT			= 0x00000080;
		static const DWORD GRANT_OP_UPDATE			= 0x00000100;
		static const DWORD GRANT_OP_DELETE			= 0x00000200;
		static const DWORD GRANT_OP_ALTER			= 0x00000400;
		
        static const DWORD GRANT_OP_ALL				= 
        GRANT_OP_SELECT|GRANT_OP_INSERT|GRANT_OP_UPDATE|GRANT_OP_DELETE|GRANT_OP_ALTER;
		
		static const DWORD GRANT_PRAGMAS			= 0x00000800;

		static const DWORD GRANT_CREATE_DB			= 0x00001000;
		static const DWORD GRANT_DROP_DB			= 0x00002000;


		static const DWORD GRANT_EVERYTHING			= 
        GRANT_CREATE_ALL|GRANT_DROP_ALL|GRANT_OP_ALL|GRANT_PRAGMAS|GRANT_CREATE_DB|GRANT_DROP_DB;
}secur_permission;

GRANT_EVERYTHING = 16383 or 0x00003FFF

 

 
Examples

Server Example 1 This is a simple example showing how to embed the server and start it.

Client Example 1 This example shows: client embedding, connecting, and the general steps needed to connect to the server. Also it shows binding of blobs, strings, integers, floats and zero blobs.

Client Example 2 Example of: client embedding, connecting and querying.

Security Database Example demonstrating the ISQLiteSecurityDB interface. The sample creates a security database, does manipulations to demonstrate some of the interface functionality and then deletes the entries.


Embeding the Server into your project
<ISQLQueryAS.h> <LoadPlugin.h> 

#include<windows.h>
#include<stdio.h>
#include<tchar.h>
#include<iostream>

using namespace std;
#include"LoadPlugin.h"   //implementation to load the interfaces
#include"ISQLQueryAS.h"  //Interfaces to the exported objects

cLoadPlugin g_plugins;	  //this is a wrapper object to dynamically link to the dll


/*
	oConnection: is an implementation of the IConnectionObserver
    
    this is optional it's not needed but shows that you can implement functionality
    based on connections
*/
class oConnection :public IConnectionObserver
{
public:
	virtual BOOL ClientConnected( DWORD dwUniqueID , const char* szIP , int iPort )
	{
		cout<<"Client : "<< szIP << "    port : "<<iPort<<"    status: connected"<<endl;
		return( TRUE );//if false is returned the client will get disconnected/rejected
	}
	virtual void ClientDisconnected( DWORD dwUniqueID , const char* szIP , int iPort )
	{
		cout<<"Client : "<< szIP << "    port : "<<iPort<<"    status: disconnected"<<endl;
	}
};

int _tmain(int argc, _TCHAR* argv[])
{
	
	oConnection notify;
	ISQLiteServer *psql = NULL;
	TCHAR szBasePath[1024];
	_tstring base;
	_tstring sdll;
	size_t found;

	//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    //Get this modules path
    //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
	if( !GetModuleFileName( GetModuleHandle(NULL) , szBasePath , 1024 ) )
		return( 0 );
	base = szBasePath;
	if( (found = base.rfind( TEXT("\\") )) == base.npos )
		return( 0 );
        
	base.erase( base.begin()+found+1 , base.end() );
		
    //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    //Get Interface to IID_ISQLiteServer
    //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    sdll = base;
	sdll+= TEXT("SQLQueryAS.dll");
	if( !g_plugins.LoadPlugin(sdll.c_str() , IID_ISQLiteServer , (void**)&psql ) )
	{
		cout<<"error loading plugin"<<endl;
	}

	if( !psql )
		return( 0 );

	//set path to the security database
	psql->SetSecurityDatabase( "c:\\databases\\security.db" );
    //[optional] adding the above interface to see who connects to the server
	psql->observer_add( (IConnectionObserver*)¬ify );
    
    //start server port 6603 and true to start a new thread
    //if the second parameter is fase the function locks
    //6603 is the default port and true is the default
    psql->StartServer( 6603 , true );
 
	cout<<"Enter q to exit"<<endl;
	while( _gettch() != (int)'q' );
	
	psql->StopServer();
	psql->Release();
	psql = NULL;
 
    return( 0 );
}