This is a more general bug, which will track a big issue with SMBTA. Currently, SMBTA is employing sqlite for all it's database actions. Using sqlite has several advantages, compared to "the big ones" such as mysql or postresql: - no need to setup at all from an administrator side - easy API However, I come to the conclusion that sqlite is no more sufficient for SMBTA. - smbtad has to serve as a network gate for the clients, as sqlite is not server based - the database part of the SMBTA components cannot be driven networked. I was looking deeper into the postgresql development documentation and think its the right way to go. Using a server based database, smbtatools can connect completely independent from smbtad and run queries. I would like to use the first comment in this bug for a decision, and then track the development of this (which are larger changes) with this bug. So, the questions are now: 1) develop a "general database layer" so that plugins can be used to serve different databases? 2) just fix SMBTA to use postgresql only, and that's it? 3) if (1) applies, shall we get rid of sqlite completely, as it requires smbtad to run queries for clients ( which adds a lot of complexity to smbtad, that would not be needed if the layer would work on server based databases only) ? I would like to hear you opinion on this.
Hey, Personally I think database abstraction layer is the way to go. While I love postgresql and use it everywhere, some people might prefer mysql. If you move to proper database, supporting sqlite will be really hard. I believe dropping it for db abstraction layer is the best way forward.
I also would say an database abstraction layer is the best way, because of the flexibility. And also removing sqlite3 to avoid the way over smbtad to connect to the database with the tools.
Ok. I created a new branch "devel-dblayer" on github for the smbtad repository in which I'll work on this. We can later merge these changes into devel.
Thinking about employing: http://libdbi.sourceforge.net for database abstraction.
Ok, there is now a fork of smbtad in the devel-dblayer branch that builds finally, and is based in libdbi. It is completely untested. Robert, Benjamin, please if you have the time, test. I've added a cmake test to check for libdbi.
Status update: DBI enabled smbtad builds and works tested on the postgresql driver. Suggested setup workflow w/ DBI enabled smbtad: setup an empty database create a user that has access to the database then, a call to smbtad : ./smbtad -S holger -H localhost -M pgsql -N smbtad -P the_password -T Runs the initial setup (as given with the -T option), which will build up the tables in the required database. -S specifies the user to make the connection to the database with, -H specifies the hostname where the database server is running -M specifies the libDBI driver to be used, in this case postgresql, -N specifies the name of the database to use -P specifies the password of the user -T runs the initial setup and will exit after a successful run. It will not daemonize. After the database is setup, smbta can be started for normal operation: ./smbtad -S holger -H localhost -M pgsql -N smbtad -P the_password -u -q 3491 Would setup smbtad to run with these given parameters and an open port for smbtamonitor on 3491 ------- - currently smbtamonitor will not work with smbtad, it's on my list - some code needs cleanup, since we no longer need to differ for smbtamonitor or smbtaquery as clients many code can be removed. - smbtaquery needs to be adapted to work based on DBI -------- Robert, Benjamin, I'd love to see some testing on this, even if smbtamonitor is currently not running against it, it should fill it's database. I am setting up a designated machine at SUSE labs for long term testing of this version.
Since the libdbi connection works in general, this bug will be blocking the 1.2.4 release tracker.
Hi Holger, I'll be testing this extensively for the next couple of days. I will let you know about any problems I find. Rob
We should get rid of the enhanced monitor functionality to do internal queries on their own, for example the MONITOR_TOTAL function, which was retrieving the total sum of the object first, internal inside of smbtad. I don't want to see smbtad doing anything else than pushing data into the database anymore, and sometimes run the maintenance routine, as well as serving smbtamonitor. This task - doing preparation queries - can be outsourced to the clients too. The smbtamonitor client can simply do this SQL query on it's self via DBI. This will further simplify the smbtad code. So my next step is to make the smbtad code simpler by removing the MONITOR_TOTAL function, and instead rely on MONITOR_READ and MONITOR_WRITE, which simply transfer the count of bytes on VFS functions, then adapt smbtatools to DBI.
removed the MONITOR_TOTAL function, and the full code to do internal database queries. This removes a lot of complexity from the code. 2 files changed, 8 insertions(+), 181 deletions(-)
Since this bug is blocking the tracker bug, I could also merge the code now into devel. But we should wait with that until smbtatools is on DBI too, since Benjamin requires a working set of smbtad/smbtatools for his webgui development.
I've created a devel-dblayer branch in smbtatools and work on getting the tools on libdbi. Currently it doesn't build.
a status update on smbtatools wrapper functions in smbtatools to make it work on DBI are in place. Many of the functions are already working. Since sqlite stores a full timestamp (date+time) in a DATE datatype, and postgresql only stores a date (it cuts the time value), the database format has to be changed to declare a timstamp. (right step, as postgresql follows SQL standard) The wrapper function is only a short term solution, longer term task should be to convert to DBI in the query functions directly and get rid of the old functionality.
The changes on the database table have been done, and smbtaquery should now work completely by using libDBI (celebrate!) :) This requires a new database and a new setup done by smbtad -T. I will now work on smbtamonitor to work with smbtad again.
Benjamin, below are the new arguments that the webui must honor due to run DBI enabled smbtaquery: holger@linux-lm9n:~/Dev/smbtatools/run> ./smbtaquery ERROR: not enough arguments. smbtaquery version 1.2.3-devel (C)opyright 2011 by Benjamin Brunner (C)opyright 2011 by Michael Haefner (C)opyright 2011 by Holger Hetterich License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> -M --dbdriver <str> Set the libDBI database driver to use. -N --dbname <str> Set the name of the database to open. -S --dbuser <str> Set the name of the user for the database. -H --dbhost <str> Set the hostname to connect to. -P --dbpassword <str> Set the password to use. -M is a string argument that can contain "pgsql" "mysql" "sqlite" in a select box.
Benjamin, please commit these changes into devel-dblayer. This way you can work on a sqlite based setup until we finally merge every code to devel. When we merged this to devel, you can run the webui against seizewell, until we have created a Stresstest appliance that is fully postgresql and DBI based.
all of smbtatools devel-dblayer has been merged and pushed into devel, we will continue development here. smbtad to follow a bit later.
(In reply to comment #17) > all of smbtatools devel-dblayer has been merged and pushed into devel, we will > continue development here. > > smbtad to follow a bit later. Yaay that's brilliant news! You're pushing these changes really quickly! Grats
short status update - no work on smbtad has been done today :) - smbtaquery functionality should now completely running via libDBI (we fixed make_human_readable to work via long long to not overflow, fix division by zero in the percentage function, fixed the top function and the usage function to work over libDBI, fixed the list function) Benjamin, please check all function via the webui.
Merged anything from smbtad devel-dblayer branch into the devel branch, since smbtatools is already there and the usage of libDBI has been proven a success, we can continue developing in "mainline devel".
currently, if one does want to configure the main parameters (network/database) for smbtamonitor, rrddriver, and smbtaquery, each of it is using it's own configuration file in $(HOME)/./smbtatools . Since the three tools share a lot of configuration data, I suggest to create a single configuration file, as smbtamonitor and smbtaquery will require the same data to connect to the database. Any thoughts? We can logically differ about the applications thanks to the excellent iniparser library, such as [general], [smbtamonitor], [smbtaquery] as sections.
created bso#8097 for comment#21
I checked all functions in the webui and they seem to be working (except last_activity #8096)
I think we can resolve this bug to fixed. DBI is in place all over, and the last problems related to it have been fixed. What's needed is work on the surroundings of this change, such as a tool to convert existing databases, which I am currently working on. This is tracked in bso#8094.