TCP/IP SQLite 3 Network Client/Server solution

netSqlite 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. Download Public Domain

To keep free software free, please support us by donating. We rely on donations, to continue free software development and maintenance. 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. 1 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. 1 2 3 4 5 6 7 8 9 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. 1 2 3 4 5 6 7 8 9 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. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 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>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

#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 ); }