This will be the dominating bug for SMBTA version 1.2.6. Goal: Reduce the database to one table, and adapt all of smbtatools to work with the new database structure. Enable an upgrade path to this new database structure. Reason: A single table having all VFS functions in one will make writing of new functions for smbtaquery much more trivial.
commit [devel f9de378] Initial untested code has been committed to devel to support a single table for all the data. What should work is the setup function smbtad -T, when SMBTA is deployed for the very first time (NOT to be used on an existing database, but a fresh one), and smbtad itself when it is running with that database. I need to do tests with it. For testing, a secondary database in postgresql could be created where this version of smbtad has a playground. Next will be to write a conversion function to allow updating to the new database format by converting the existing tables into the new single table structure, and allow that with smbtatools -C. After that, all of smbtatools, especially smbtaquery have to be altered to work with the new structure. After this hard step "back", it is my opinion writing new functions and enhancing our statistic features will be much easier, as we don't have to do separate queries over different tables. This bug is blocking too many things so I have to get rid of this one first.
Anyone volunteering in testing initial setup of smtbad from devel would be highly welcome
Hi, I'll give it ago later today. Rob
Thank you Robert, if you get an output with select * from data; things look positive.
Initial testing: Nothing ended up in the database: smbtad2=> select * from data; vfs_id | username | usersid | share | domain | timestamp | string1 | length | result | string2 --------+----------+---------+-------+--------+-----------+---------+--------+--------+--------- (0 rows) Errors from smbtad: Jun 21 13:28:11 [smbtad] smbtad version 1.2.4 start up. Jun 21 13:28:46 [smbtad] DBI ERROR: ERROR: syntax error at or near "/"_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:45.230',/home/dev/..._ ^_ Jun 21 13:28:46 [smbtad] DBI ERROR: ERROR: syntax error at or near "/"_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:45.230',/home/dev/..._ ^_ - Last output repeated 8 times - Jun 21 13:28:46 [smbtad] ERROR: Database handling error! Jun 21 13:28:46 [smbtad] DBI ERROR: ERROR: syntax error at or near "/"_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:45.233',/home/dev/..._ ^_ - Last output repeated 9 times - Jun 21 13:28:46 [smbtad] ERROR: Database handling error! Jun 21 13:28:46 [smbtad] DBI ERROR: ERROR: syntax error at or near "/"_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:45.234',/home/dev/..._ ^_ - Last output repeated 9 times - Jun 21 13:28:46 [smbtad] ERROR: Database handling error! Jun 21 13:28:46 [smbtad] DBI ERROR: ERROR: syntax error at or near "."_LINE 1: ...62882449-1226','HDD','AD','2011-06-21 13:28:45.234',.,0,35);_ ^_ - Last output repeated 9 times - Jun 21 13:28:46 [smbtad] ERROR: Database handling error! Jun 21 13:28:46 [smbtad] DBI ERROR: ERROR: syntax error at or near "/"_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:45.235',/home/dev/..._ ^_ - Last output repeated 9 times - Jun 21 13:28:46 [smbtad] ERROR: Database handling error! Jun 21 13:28:46 [smbtad] DBI ERROR: ERROR: syntax error at or near "/"_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:45.236',/home/dev/..._ ^_ - Last output repeated 9 times - Jun 21 13:28:46 [smbtad] ERROR: Database handling error! Jun 21 13:28:46 [smbtad] DBI ERROR: ERROR: syntax error at or near "/"_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:45.217',/home/dev/..._ ^_ - Last output repeated 9 times - Jun 21 13:28:46 [smbtad] ERROR: Database handling error! Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: column "filename" of relation "data" does not exist_LINE 1: ...s_id, username, usersid, share, domain, timestamp,filename, ..._ ^_ Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: current transaction is aborted, commands ignored until end of transaction block_ - Last output repeated 8 times - Jun 21 13:28:51 [smbtad] ERROR: Database handling error! Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: current transaction is aborted, commands ignored until end of transaction block_ - Last output repeated 9 times - Jun 21 13:28:51 [smbtad] ERROR: Database handling error! Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: syntax error at or near "."_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:49.794',.,16895,42..._ ^_ - Last output repeated 9 times - Jun 21 13:28:51 [smbtad] ERROR: Database handling error! Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: syntax error at or near "of"_LINE 1: ...9-1226','HDD','AD','2011-06-21 13:28:49.797',Copy of ACT_Act..._ ^_ - Last output repeated 9 times - Jun 21 13:28:51 [smbtad] ERROR: Database handling error! Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: syntax error at or near "of"_LINE 1: ...9-1226','HDD','AD','2011-06-21 13:28:49.801',Copy of ACT_Act..._ ^_ - Last output repeated 9 times - Jun 21 13:28:51 [smbtad] ERROR: Database handling error! Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: syntax error at or near "of"_LINE 1: ...9-1226','HDD','AD','2011-06-21 13:28:49.801',Copy of ACT_Act..._ ^_ - Last output repeated 9 times - Jun 21 13:28:51 [smbtad] ERROR: Database handling error! Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: syntax error at or near "of"_LINE 1: ...9-1226','HDD','AD','2011-06-21 13:28:49.819',Copy of Copy of..._ ^_ - Last output repeated 9 times - Jun 21 13:28:51 [smbtad] ERROR: Database handling error! Jun 21 13:28:51 [smbtad] DBI ERROR: ERROR: syntax error at or near "/"_LINE 1: ...882449-1226','HDD','AD','2011-06-21 13:28:49.445',/home/dev/..._ ^_ - Last output repeated 9 times - Jun 21 13:28:51 [smbtad] ERROR: Database handling error! Jun 21 13:28:56 [smbtad] DBI ERROR: ERROR: column "filename" of relation "data" does not exist_LINE 1: ...s_id, username, usersid, share, domain, timestamp,filename, ..._ ^_ Jun 21 13:28:56 [smbtad] DBI ERROR: ERROR: current transaction is aborted, commands ignored until end of transaction block_ - Last output repeated 8 times - Jun 21 13:28:56 [smbtad] ERROR: Database handling error! Rob
Please test again Robert, should work now w/ 7c665e0857d464558ad4a39795ff8dd0d144192c
Furthermore, devel branch of smbtatools now has smbtaquery --convert ( smbtaquery -C ) support for 1.2.6. Robert, this is especially hard for me to test as I currently don't have an "old" database available. Is there a way for you to create a copy of your existing 1.2.5 database, and give the conversion process a try on this copy?
If these two things now work ( initial setup and conversion ), next step is to adapt smbtaquery and the other tools to work with the new db.
Way to test the new conversion code is to: - create a copy of an "old" 1.2.5 based DB - run the conversion process on it, in smbtaquery from devel - check if smbtad from devel works flawless on the converted DB Way to test the initial setup is: - create a new database in postgresql - run smbtad -T from devel - check if smbtad from devel works flawless on the created DB
Hi, Sorry I didn't manage to test it yet (very busy this week). I'll do it tomorrow and get back to you.
Benjamin has completed the conversion code, and we have tested both situations at least once. I would love to see another test on smbtaquery -C. Nevertheless, good news, we can now concentrate on the next bugs, that would be escaping of the strings. Afaik, Benjamin is now working on porting smbtatools to the new database structure.
With current devel branch, everything works perfectly. vfs_id | username | usersid | share | domain | timestamp | string1 | length | result | string2 --------+---------------------+-----------------------------------------------+---------------------+--------+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 6 | eileen.sutherland | S-1-5-21-1566210211-763273816-3362882449-1516 | Commercial | AD | 2011-06-12 18:40:13.782 | Utilities and services/EON/Panels and Communities Project Sept 2009 onwards/Ad hoc projects/73. Retail market review/E.ON#73Retail Market ReviewDebrief.ppt | | 0 | Utilities and services/EON/Panels and Communities Project Sept 2009 onwards/Ad hoc projects/73. Retail market review/6709FFB5.tmp 6 | eileen.sutherland | S-1-5-21-1566210211-763273816-3362882449-1516 | Commercial | AD | 2011-06-12 18:40:13.024 | Utilities and services/EON/Panels and Communities Project Sept 2009 onwards/Ad hoc projects/73. Retail market review/1017A584.tmp | | 0 | Utilities and services/EON/Panels and Communities Project Sept 2009 onwards/Ad hoc projects/73. Retail market review/E.ON#73Retail Market ReviewDebrief.ppt 6 | eileen.sutherland | S-1-5-21-1566210211-763273816-3362882449-1516 | Commercial | AD | 2011-06-12 20:23:01.197 | Utilities and services/EON/Panels and Communities Project Sept 2009 onwards/Ad hoc projects/73. Retail market review/E#73Retail Market ReviewDebrief.ppt | | 0 | Utilities and services/EON/Panels and Communities Project Sept 2009 onwards/Ad hoc projects/73. Retail market review/158AFF8D.tmp 6 | eileen.sutherland | S-1-5-21-1566210211-763273816-3362882449-1516 | Commercial | AD | 2011-06-12 20:23:01.436 | Utilities and services/EON/Panels and Communities Project Sept 2009 onwards/Ad hoc projects/73. Retail market review/DE4EC6BC.tmp | | 0 | Utilities and services/EON/Panels and Communities Project Sept 2009 onwards/Ad hoc projects/73. Retail market review/E#73Retail Market ReviewDebrief.ppt 6 | emma.gillingham | S-1-5-21-1566210211-763273816-3362882449-1527 | Commercial | AD | 2011-06-13 08:24:21.896 | Travel/Expedia/eCOS/(Nov 2009) website survey/Reports/NEW weekly/New folder | | 0 | Travel/Expedia/eCOS/(Nov 2009) website survey/Reports/NEW weekly/13.06.11 6 | emma.gillingham | S-1-5-21-1566210211-763273816-3362882449-1527 | Commercial | AD | 2011-06-13 08:24:39.369 | Travel/Expedia/eCOS/(Nov 2009) website survey/Reports/NEW weekly/13.06.11/New folder | | 0 | Travel/Expedia/eCOS/(Nov 2009) website survey/Reports/NEW weekly/13.06.11/Overall Weekly Trending Report 13.06.11 6 | emma.gillingham | S-1-5-21-1566210211-763273816-3362882449-1527 | Commercial | AD | 2011-06-13 08:25:00.366 | Travel/Expedia/eCOS/(Nov 2009) website survey/Reports/NEW weekly/13.06.11/New folder | | 0 | Travel/Expedia/eCOS/(Nov 2009) website survey/Reports/NEW weekly/13.06.11/POS Weekly (%+RAW) 13.06.11 6 | jenny.jones | S-1-5-21-1566210211-763273816-3362882449-1121 | Media | AD | 2011-06-13 08:32:16.449 | Yell.com/Site Survey/Yell.com site survey collection figures weekly trended.xls | | 0 | Yell.com/Site Survey/2EE9EF24.tmp 6 | jenny.jones | S-1-5-21-1566210211-763273816-3362882449-1121 | Media | AD | 2011-06-13 08:32:16.451 | Yell.com/Site Survey/7CD90DEF.tmp | | 0 | Yell.com/Site Survey/Yell.com site survey collection figures weekly trended.xls 6 | aimi.kirby | S-1-5-21-1566210211-763273816-3362882449-1208 | Sales_and_Marketing | AD | 2011-06-13 08:32:22.382 | Training/Training Materials/Training Help Presentations/Dashboards.ppt | | 0 | Training/Training Materials/Training Help Presentations/FED7CD37.tmp
Not sure if that's intentional, but after converting from 1.2.5 to 1.2.6 rmdir table is still there: smbtad2=> \d List of relations Schema | Name | Type | Owner --------+-------+-------+------------ public | data | table | smbtaduser public | rmdir | table | smbtaduser (2 rows)
I have added the rmdir table to the convert function, too. It should be removed now.
Looks like we can close this guy, thank Benjamin!
oops, I forgot we have to finish smbtatools working with the new database to finish this :)
All query functions converted with commit 1774715196abee141aec57995f8509541403c9cf Author: Benjamin Brunner <bbrunner@suse.de> Date: Tue Jul 5 16:08:02 2011 +0200 Converted search function to 1.2.6
Thank you Benjamin! Although I think we will have minor changes to the format until 1.2.6, I think the work here is done, we can close this.
Because of bug#8206, we'll have another format change in the conversion function as well as on initial setup. commit da40f6baf9d8c2e58848a375d9de5c8a515e75a2 Author: Holger Hetterich <hhetter@novell.com> Date: Mon Jul 18 21:52:54 2011 +0200 Make the ip address a UNIQUE key in the table Please take that into account when testing 1.2.6, a new database has to be setup with smbtad -T