Bug 8288 - SMBTA shall support sqlite by means of libDBI
Summary: SMBTA shall support sqlite by means of libDBI
Alias: None
Product: smbta
Classification: Unclassified
Component: general (show other bugs)
Version: unspecified
Hardware: All All
: P5 normal
Target Milestone: ---
Assignee: Holger Hetterich
QA Contact:
Depends on:
Blocks: 8204
  Show dependency treegraph
Reported: 2011-07-05 18:06 UTC by Holger Hetterich
Modified: 2011-09-09 10:58 UTC (History)
4 users (show)

See Also:


Note You need to log in before you can comment on or make changes to this bug.
Description Holger Hetterich 2011-07-05 18:06:47 UTC
We are missing the dbdir parameter to support sqlite via libDBI. If sqlite is able to handle the SMBTA load and traffic is a different question, especially when it comes to concurrent acceses by smbtatools and smbtad. This needs to be tested.
Comment 1 Holger Hetterich 2011-07-05 18:52:57 UTC
I make this blocking the 1.2.6 release bug.
Comment 2 Holger Hetterich 2011-07-05 19:12:58 UTC

we are missing the dbdir parameter...
Comment 3 Giuseppe 2011-07-06 07:10:32 UTC
For little server sqlite can be a good alternative. Looking forward to help you bye!
Comment 4 Holger Hetterich 2011-07-07 18:56:16 UTC
"SQLite3 is a redesign of SQLite which is incompatible with the older 2.x versions. As the API functions and the library itself were renamed, SQLite3 also requires a slightly modified libdbi driver. You can have both the sqlite and the sqlite3 driver installed on your system, but you have to make sure that you access your databases with the driver that matches the database version. The easiest way to accomplish this is to use different database directories for each version."

So i am implementing the support for two of the modules (sqlite and sqlite3). Giuseppe, I highly recommend you to use sqlite3.
Comment 5 Holger Hetterich 2011-07-07 19:15:26 UTC
sqlite3 support WAN mode, which we can enable to support one writer and several readers in parallel which will help to cope with the SMBTA traffic.
Comment 6 Holger Hetterich 2011-07-07 19:46:29 UTC
This is commit 0a35cea6512aedc1c57ad722b8b1228767cbce1b and I haven't tested it at all. With this patch smbtad supports two new parameters in configuration:


The parameters are the same, regardless if the sqlite3 or the sqlite driver is used. The options are only configureable by a configuration file, there is no command line option support for this.

So, Giuseppe, adding these lines to your configuration file _in the database section_ should be worth a try:

  sqlite_dbdir = /PATH/TO/YOUR/DATABASE

sqlite_timeout is set by default to 10000 and we might tune it if we have the base sqlite support working.

Giuseppe, you have a few options to obtain a development version of smbtad with these patch. Probably the easiest is to point your web browser to https://github.com/hhetter/smbtad , make sure you select the "devel" branch, and download a tarball of this version.

You can also install git, and do:
git clone git://github.com/hhetter/smbtad.git

it will then download the source repository and then switch to the devel branch:
git checkout devel
and build from there.

If you have any questions, please ask :)
Comment 7 Holger Hetterich 2011-07-07 19:47:45 UTC
and, the "dbname" parameter shall contain the _filename_ of the database.
Comment 8 Giuseppe 2011-07-11 08:06:30 UTC
Sorry, this week-end I was busy, today I'll try this version. Thank you
Comment 9 Giuseppe 2011-07-12 10:17:24 UTC
I've tried this development version but it won't work.

I have this configuration (/etc/samba/smbtad.conf):

        debug_level = 10
        use_db = 1
        query_port = 3491
        unix_domain_socket = no
        unix_domain_socket_clients = no
        driver = sqlite3 # I've used also 'sqlite'
        dbname = smbdb.sqlite
        sqlite_dbdir = /var/smbtadb
        interval = 01:00:00
        config = 01,00:00:00

when I launch the smbta deamon I receive this error

smbtad -c smbtad.conf 
DBI: could not connect, please check options.
DBI: libdbi could not establish a connection
Error connecting to the database.
please check syslog.

In syslog the only row I have is:

Jul 12 03:04:21 edebian smbtad: smbtad version 1.2.5 start up.

No error after. Maybe I made some error?
Comment 10 Holger Hetterich 2011-07-12 12:50:44 UTC
Giuseppe, sorry that was my fault the parameter is not "dbname" but "name". Try "name" and it should work.
Comment 11 Holger Hetterich 2011-07-12 12:56:10 UTC
Remember to run smbtad -T initially to create a database and the table. That's what I tested and it seems it worked fine. Now I am interested what happens if you give some traffic on the net. If you check the database manually and run select * from data; and get filled tables, it seems to work.
Comment 12 Holger Hetterich 2011-07-12 13:34:06 UTC
make sure the sqlite_dbdir does exist
Comment 13 Giuseppe 2011-07-12 13:37:37 UTC
I'm really sory but it doesn't work.

I've tried with name instead of dbname,  dbname e name togheter, the directory exist, no error message was displayed in syslog/message I don't know where I can found the issue
Comment 14 Holger Hetterich 2011-07-12 15:18:03 UTC
Example smbtad.conf that worked for me:
        debug_level = 10
        use_db = 1
        query_port = 3491
        unix_domain_socket = no
        unix_domain_socket_clients = no
        driver = sqlite3
        name = smbdb.sqlite
        sqlite_dbdir = /home/holger/
        interval = 01:00:00
        config = 01,00:00:00

holger@linux-lm9n:~/Dev/smbtad/run> ./smbtad -T -c ./smbtad.conf

Succesfully created database tables.
Comment 15 Benjamin Brunner 2011-07-12 15:19:35 UTC
Did you run "smbtad -T -c /etc/samba/smbtad.conf" to set up your database, before starting smbtad?
Are the correct drivers for libdbi installed?
The package for SUSE is named "libdbi-drivers-dbd-sqlite3".
Comment 16 Holger Hetterich 2011-07-12 15:21:40 UTC
Giuseppe I do believe dbdir must end in a slash, such as:
 sqlite_dbdir = /var/smbtadb/

The sqlite driver is creating the database full filename by congatenating dbdir and name.
Comment 17 Giuseppe 2011-07-12 15:43:50 UTC
I'm using Debian, the libdbi are installed and the driver for sqlite3 is installed (in debian is called libdbd-sqlite3)

I've inserted the slash in the sqlite_dbdir,
I've tried to re-compile the source and the result is 

root@edebian:~# git clone git://github.com/hhetter/smbtad.git
Cloning into smbtad...
remote: Counting objects: 2120, done.
remote: Compressing objects: 100% (654/654), done.
remote: Total 2120 (delta 1609), reused 1970 (delta 1459)
Receiving objects: 100% (2120/2120), 1.51 MiB | 842 KiB/s, done.
Resolving deltas: 100% (1609/1609), done.
root@edebian:~# mkdir buildsm
root@edebian:~# cd buildsm/
root@edebian:~/buildsm# cmake ../smbtad
-- The C compiler identification is GNU
-- Check for working C compiler: /usr/bin/gcc
-- Check for working C compiler: /usr/bin/gcc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Found LibDbi: /usr/lib/libdbi.so
-- Found Talloc: /usr/lib/libtalloc.so
-- Found Talloc: /usr/lib/libtalloc.so;/usr/lib/libtalloc.so
-- Looking for include files CMAKE_HAVE_PTHREAD_H
-- Looking for include files CMAKE_HAVE_PTHREAD_H - found
-- Looking for pthread_create in pthreads
-- Looking for pthread_create in pthreads - not found
-- Looking for pthread_create in pthread
-- Looking for pthread_create in pthread - found
-- Found Threads: TRUE
-- ----> FYI: Unable to find iniparser libraries on your system
-- ---->      compiling my own version and link it statically.
-- Configuring done
-- Generating done
-- Build files have been written to: /root/buildsm
root@edebian:~/buildsm# make
Scanning dependencies of target buildiniparser
[  0%] Building iniparser.
compiling src/iniparser.c ...
compiling src/dictionary.c ...
a - src/iniparser.o
a - src/dictionary.o
[  7%] Built target buildiniparser
Scanning dependencies of target smbtad
[ 14%] Building C object CMakeFiles/smbtad.dir/src/main.c.o
[ 21%] Building C object CMakeFiles/smbtad.dir/src/configuration.c.o
[ 28%] Building C object CMakeFiles/smbtad.dir/src/help.c.o
[ 35%] Building C object CMakeFiles/smbtad.dir/src/daemon.c.o
[ 42%] Building C object CMakeFiles/smbtad.dir/src/connection_list.c.o
[ 50%] Building C object CMakeFiles/smbtad.dir/src/network.c.o
[ 57%] Building C object CMakeFiles/smbtad.dir/src/protocol.c.o
[ 64%] Building C object CMakeFiles/smbtad.dir/src/cache.c.o
[ 71%] Building C object CMakeFiles/smbtad.dir/src/query_list.c.o
[ 78%] Building C object CMakeFiles/smbtad.dir/src/database.c.o
[ 85%] Building C object CMakeFiles/smbtad.dir/src/crypto/aes.c.o
[ 92%] Building C object CMakeFiles/smbtad.dir/src/crypto/rijndael-alg-fst.c.o
[100%] Building C object CMakeFiles/smbtad.dir/src/monitor-list.c.o
Linking C executable smbtad
[100%] Built target smbtad
root@edebian:~/buildsm# ./smbtad -T -c /etc/samba/smbtad.conf 
DBI: could not connect, please check options.
DBI: libdbi could not establish a connection
Error connecting to the database.
please check syslog.

The cmake found all mandatory libs.
it will be some my fault, I will looking for it. Thank you for your support, you're very kind to me
Comment 18 Benjamin Brunner 2011-07-12 15:49:01 UTC
Please try "git checkout devel" and build it again.
I don't think that the changes are already in master.
Comment 19 Robert Piasek (dead mail address) 2011-07-12 15:49:18 UTC
Hi Giuseppe,

I don't think you're using devel branch and I'm not sure if Holger merged everything back to master yet.

Can you try:

git clone git://github.com/hhetter/smbtad.git
git branch test --track origin/devel
git checkout devel
mkdir build
cd build
cmake ../
make install
Comment 20 Robert Piasek (dead mail address) 2011-07-12 15:50:21 UTC
(In reply to comment #18)
> Please try "git checkout devel" and build it again.
> I don't think that the changes are already in master.

You beat me to it.

Also in my example it should be
git checkout test
of course
Comment 21 Giuseppe 2011-07-12 16:04:44 UTC
I'm a real stupid! It works! Sorry man but I were distracted (I haven't read
the development branch part...).

Tank you very very much for the support and sorry again.

Let me know if there is something I can do for you.

Comment 22 Benjamin Brunner 2011-07-12 16:05:55 UTC
You're welcome :)
And I think we are happy if you test it and tell us your results ;)
Comment 23 Holger Hetterich 2011-07-12 16:07:59 UTC
Next step is we need to know if smbtad is actually able to fill the database with content. If that works, we add the same functionality to smbtatools, and add some error checking (for the configuration) to both. Thank you Giuseppe!
Comment 24 Holger Hetterich 2011-07-14 15:56:23 UTC
Giuseppe, did you manage to get smbtad to fill it's database when the sqlite driver is used?

> select * from data;

shall return input when you create traffic and have VFS modules pointed to smbtad in smb.conf
Comment 25 Robert Piasek (dead mail address) 2011-07-19 13:19:59 UTC

I've done some basic test on smbtad with sqlite3. While it's not as quick as with other SQL databases it works fine.

sqlite3 smbdb.sqlite 
SQLite version 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from data;
8|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:45:29.467|.||35|0
7|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:45:29.455|/home/dev/robert.piasek||0|
9|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:27.347|Tom's leaving do||0|
8|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:27.347|Tom's leaving do||36|0
9|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:27.442|Tom's leaving do||0|
8|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:27.654|Tom's leaving do||36|502
9|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:27.655|Tom's leaving do||0|
8|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:27.655|Tom's leaving do||36|0
9|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:27.727|Tom's leaving do||0|
8|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:27.346|Tom's leaving do||36|502
8|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:45.031|1GB||36|502
9|robert.piasek|S-1-5-21-1566210211-763273816-3362882449-1226|HDD|AD|2011-07-19 13:46:55.239|1GB||0|
Comment 26 Holger Hetterich 2011-07-19 21:33:09 UTC
Thank you Robert for your tests, smbtad seems to work with Sqlite3. Although I would like to speak of it like: "Has been tested to work, but not officially supported by the SMBTA team". ( <- note for documentation )

I'll continue here with the changes needed to be done for smbtatools to accept a sqlite database.

Sqlite is not server based, and as such smbtatools can only be run from the local machine against the database, opening the file on it's own, while smbtad is messing with it.

I predict problems with that.

But since smbtatools is only read-only on the database, there might chances it works.
Comment 27 Holger Hetterich 2011-07-20 21:34:34 UTC
commit a7395b858df4c1899d19cce8246eab23969d2513 

    Add an option "dbpath" to the configuration file reader, to allow
    usage with sqlite. If sqlite is used, we set the dbname parameter
    to the filename of the database and the dbpath parameter to the dbdir
    parameter of sqlite driver. This should enable smbtaquery to run with
    sqlite. (Untested)
Comment 28 Holger Hetterich 2011-07-20 21:43:44 UTC
This needs to be changed to use the same parameters as smbtad does... 
instead of

will do that as soon as time permits
Comment 29 Holger Hetterich 2011-07-20 23:19:24 UTC
To make all of that work with websmbta, we need to enable command line arguments for sqlite_dbdir with smbtaquery.
Comment 30 Holger Hetterich 2011-09-06 18:19:51 UTC
Guiseppe, if you want to try, smbtaquery has also been enhanced to be run on sqlite3. Please ping back. If not, I'll close this bug as fixed, as we have provided the necessary fixes to support this. We can have another bug reopened for this issue if it gets current again. I will leave this bug open for another 3 days, then closing this.
Comment 31 Giuseppe 2011-09-08 09:16:50 UTC
It works you can close the ticket!

Sorry if I haven't reply but I had some troubes. Thank you all for your support and for your patience. 

Giuseppe Guarino.
Comment 32 Holger Hetterich 2011-09-09 10:58:46 UTC