Bug 8205 - Simplify the database in SMBTA
Simplify the database in SMBTA
Status: RESOLVED FIXED
Product: smbta
Classification: Unclassified
Component: smbtad
unspecified
All All
: P5 normal
: ---
Assigned To: Benjamin Brunner
:
Depends on:
Blocks: 8204 8232
  Show dependency treegraph
 
Reported: 2011-06-04 21:20 UTC by Holger Hetterich
Modified: 2011-07-18 19:58 UTC (History)
3 users (show)

See Also:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Holger Hetterich 2011-06-04 21:20:49 UTC
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.
Comment 1 Holger Hetterich 2011-06-19 16:56:44 UTC
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.
Comment 2 Holger Hetterich 2011-06-19 17:01:37 UTC
Anyone volunteering in testing initial setup of smtbad from devel would be highly welcome
Comment 3 Robert Piasek 2011-06-20 10:45:31 UTC
Hi,

I'll give it ago later today.

Rob
Comment 4 Holger Hetterich 2011-06-20 17:18:45 UTC
Thank you Robert, if you get an output with
select * from data;     things look positive.
Comment 5 Robert Piasek 2011-06-21 12:30:06 UTC
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
Comment 6 Holger Hetterich 2011-06-27 18:46:14 UTC
Please test again Robert, should work now w/ 7c665e0857d464558ad4a39795ff8dd0d144192c
Comment 7 Holger Hetterich 2011-06-27 21:11:56 UTC
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?
Comment 8 Holger Hetterich 2011-06-28 06:15:02 UTC
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.
Comment 9 Holger Hetterich 2011-06-28 06:18:01 UTC
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
Comment 10 Robert Piasek 2011-06-28 20:55:44 UTC
Hi,

Sorry I didn't manage to test it yet (very busy this week). I'll do it tomorrow and get back to you.
Comment 11 Holger Hetterich 2011-06-30 12:16:49 UTC
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.
Comment 12 Robert Piasek 2011-06-30 13:22:12 UTC
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
Comment 13 Robert Piasek 2011-06-30 13:23:55 UTC
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)
Comment 14 Benjamin Brunner 2011-06-30 15:55:50 UTC
I have added the rmdir table to the convert function, too. It should be removed now.
Comment 15 Holger Hetterich 2011-06-30 16:37:55 UTC
Looks like we can close this guy, thank Benjamin!
Comment 16 Holger Hetterich 2011-06-30 17:45:30 UTC
oops, I forgot we have to finish smbtatools working with the new database to finish this :)
Comment 17 Benjamin Brunner 2011-07-05 14:43:35 UTC
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
Comment 18 Holger Hetterich 2011-07-05 18:16:42 UTC
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.
Comment 19 Holger Hetterich 2011-07-18 19:58:43 UTC
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