sqlCVS

[Zaerc]

Does anybody know how this is supposed to work?

I'd like to check out a new clean database for testing with the components I've managed to build from source, but so far I can't find any good documentation on sqlCVS.

http://freshmeat.net/projects/sqlcvs/

http://plutohome.com/wiki/index.php/Pluto_Home_Software
http://plutohome.com/wiki/index.php/SqlCVS
http://plutohome.com/wiki/index.php/SqlCVS_User%27s_manual
http://plutohome.com/wiki/index.php/Naming_of_Foreign_Keys%2C_Indirect_Keys%2C_External_Keys
http://plutohome.com/wiki/index.php/What_do_the_psc_fields_do%3F
http://plutohome.com/wiki/index.php/What_do_the_psc_tables_do%3F
http://plutohome.com/wiki/index.php/SqlCVS_Programmer%27s_Guide

Code: [Select]

# /usr/pluto/bin/sqlCVS --help
 Copyright (C) 2004 Pluto, Inc., a Florida Corporation
 www.plutohome.com
 Phone: +1 (877) 758-8648
This program is distributed according to the terms
of the Pluto Public License, available at:
 http://plutohome.com/index.php?section=public_license
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. See the Pluto Public License for more details.

-------

Unknown option: --help
sqlCVS, v.<=version=>
Usage: sqlCVS [-h MySql hostname] [-u MySql username]
[-p MySql password] [-D MySql database] [-P mysql port]
[-H sqlCVS hostname] [-R sqlCVS port]
[-r Repository( -ies )] [-v] [-i] [-c comments]
[-t Table( s )] [-U Username[~Password][,...]] [-d username]
[-a] [-e everyone] [-w width]
[-s Skip Verification of fields] [-b batch] [-f] [-n]
-h hostname    -- address or DNS of database host,
                        default is `dcerouter`
-u username    -- username for database connection
-p password    -- password for database connection,
                        default is `` ( empty )
-D database    -- database name. default is pluto_main
-P port            -- port for database connection,
                        default is 3306
-r output path -- Where to put the output files.
                        Default is ../[database name]
-t input path  -- Where to find the template files.
                        Default is . then ../sqlCVS
-b batch       -- the batch number you want to approve
-U user~pass   -- the user(s) who are logged in and will be committing rows
-d username    -- the owner of any unclaimed new records
            Default is the first user checking in records
-v verify      -- Verifies the integrity of the database first (foreign keys)
-s Table:Field -- Comma delimted list of Fields to ignore when doing a verify
-i verify id   -- Verifies that all records have unique psc_id
-a allow       -- Allows checking in a row with a foreign key
            to a modified row in another table that is not
            being checked in
-e everyone    -- Checkin all records from every user
-n no prompts  -- Don't ever prompt, just exit if necessary
-c comments    -- Optional comments to be included with a checkin
-w screen width-- This value will 'stick' because it's stored in the config
-S restrictions-- Include the listed restrictions.
                  If one restriction, other than 0,
                  is specified with the -v option, rather than auto-deleting, it will
                  update orphaned rows to be in the same restriction
-f filename    -- Save the output of this command to a file, usually so
                  another program like a website can show the contents
                  normally used with -n
-O PK -N PK    -- For use with change_key command
-A anonymous   -- Support authorization witn anonymous~anonymous
                  username and password - all records are commited w/o quarantine
-C attempts count  -- Maximal attempts to connect server if it's busy, default is unlimited
-T processor timeout  -- Timeout for server-side processor (seconds, default is 15 min)

And from looking at some of the scripts included in the source tarball I gather there are "commands" like: import, checkin, change_key, update-psc as well, but what they actually do remains a mystery to me.  I'm guessing (hoping) that one of these scripts is probably used post-install to get a fresh copy of the pluto database.

Aparently sqlCVS does not accept another local username then "root" so I have simply removed the root password on the local mysql database, now running without parameters gives me the folowing menu:

Code: [Select]

# /usr/pluto/bin/sqlCVS
 Copyright (C) 2004 Pluto, Inc., a Florida Corporation
 www.plutohome.com
 Phone: +1 (877) 758-8648
This program is distributed according to the terms
of the Pluto Public License, available at:
 http://plutohome.com/index.php?section=public_license
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. See the Pluto Public License for more details.

-------

Database host:localhost user:root pass: name:pluto_main port:3306
Users:
Use sqlCVS -? for command line options.

What would you like to do?
In the future you can also make your selections on the command line using the command shown in parenthesis.
------Server-side functions------
1.      Create a new sqlCVS repository from an existing database ( create )
2.      Edit an existing repository ( edit )
3.      Start listening for incoming connections from clients ( listen )
4.      Permanently roll-back checkins ( rollback )
5.      Create a 'dump' file with the tables in the current repository ( dump )
6.      List all the tables and what repositories they are in ( list-tables )
7.      List all the repositories and what tables they have ( list-repositories )
8.      History on all tables ( history-all )
9.      History on no tables ( history-none )
10.     List unauthorized batches ( batches )
11.     List contents of batch ( batch-contents )
---The following are not normally used---
20.     Reset entire database--sqlCVS clients will be out of sync ( reset-all )
21.     Reset the system tables ( reset-sys )
22.     Update psc_id's ( update-psc )
23.     Update last_psc_batch/id **server only** ( update-last-psc )
------Client-side functions------
A.      Import a 'dump' file from a server and make a local, working copy ( import )
B.      Check-in changes you've made locally ( checkin )
C.      Update my local copy with changes from the server ( update )
D.      **disabled** Synchronize my database with the server. Same as checkin+update ( sync )
E.      View my local changes ( diff )
F.      Approve pending batch ( approve )
G.      Reject pending batch ( reject )
H.      Revert changes from a mask file ( revert )
I.      Changes a primary key and propagates ( change_key )

Z.      Change login or users

Q. Quit

I guess at least that clears the commands up a bit.  But now I guess I'm stuck at some file that can't be read:

Code: [Select]

# /usr/pluto/bin/sqlCVS -D pluto_main -r dce import
 Copyright (C) 2004 Pluto, Inc., a Florida Corporation
 www.plutohome.com
 Phone: +1 (877) 758-8648
This program is distributed according to the terms
of the Pluto Public License, available at:
 http://plutohome.com/index.php?section=public_license
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. See the Pluto Public License for more details.

-------

Database host:localhost user:root pass: name:pluto_main port:3306
Users:
01      10/27/07 17:37:17.876           Connect failed Unknown database 'pluto_main' <0xb7b166b0>
Creating database pluto_main
Caught exception: Cannot read file


Code: [Select]

# /usr/pluto/bin/sqlCVS -D pluto_main -r dce import
 Copyright (C) 2004 Pluto, Inc., a Florida Corporation
 www.plutohome.com
 Phone: +1 (877) 758-8648
This program is distributed according to the terms
of the Pluto Public License, available at:
 http://plutohome.com/index.php?section=public_license
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. See the Pluto Public License for more details.

-------

Database host:localhost user:root pass: name:pluto_main port:3306
Users:
Caught exception: Cannot read file

For all I know this could be a configuration file that sqlCVS depends on, but I feel a bit reluctant to try this on a real lmce installation as I'm obviously pretty clueless to what I'm exactly doing here and it wouldn't surprise me at all if I'm doing things horribly wrong...

[PeteK]

Hmm, that's interesting.  I too am a little hesitant to play around with my pluto_main database as I've got a working version (and obviously no way to capture the changes), and I don't want to screw it up.  Is Paul around.  I'm certain he must have played with this for the port.  If not, i'll try to get some info from the Pluto guys.

I take it you're running it as root.  Right?

[Zaerc]

Yes, I'm doing everything as the root user.  I was also running the sqlCVS commands from inside the /usr/pluto/database directory as a lot of the scripts seem to go there before executiong the sqlCVS commands.

I have briefly looked at what seems to be the sources for an installer as well, but that was a bit overwhelming and I couldn't make heads or tails of it, let alone figure out where the installed DB actually comes from.

Needless to say that any pointers would be greatly appreciated.

[darrenmason]

Maybe contact the Pluto developers for some assistance.

My understanding was that the Master DB was meant to not be an active Pluto_Main database, but just for the purpose of being a server.

The commands are meant to mimic CVS commands I guess.

[Zaerc]

Well I got a little further, I copied /usr/pluto/database/dce.sqlcvs from an existing installation, and this seems to have been the missing file.  I've dropped the database to start fresh again first.

Code: [Select]

root@pms32:/usr/pluto/database# /usr/pluto/bin/sqlCVS -n -D pluto_main -r dce import
 Copyright (C) 2004 Pluto, Inc., a Florida Corporation
 www.plutohome.com
 Phone: +1 (877) 758-8648
This program is distributed according to the terms
of the Pluto Public License, available at:
 http://plutohome.com/index.php?section=public_license
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. See the Pluto Public License for more details.

-------

Database host:localhost user:root pass: name:pluto_main port:3306
Users:
01      10/29/07 20:41:21.450           Connect failed Unknown database 'pluto_main' <0xb7b6d6b0>
Creating database pluto_main
Importing table: psc_dce_repset (dce) 1 rows
Importing table: psc_dce_bathdr (dce) 1757 rows
Completed 500 rows out of 1757
Completed 1000 rows out of 1757
Completed 1500 rows out of 1757
Importing table: psc_dce_batuser (dce) 1806 rows
Completed 500 rows out of 1806
Completed 1000 rows out of 1806
Completed 1500 rows out of 1806
Importing table: psc_dce_batdet (dce) 7603 rows
Completed 500 rows out of 7603
Completed 1000 rows out of 7603
Completed 1500 rows out of 7603
Completed 2000 rows out of 7603
Completed 2500 rows out of 7603
Completed 3000 rows out of 7603
Completed 3500 rows out of 7603
Completed 4000 rows out of 7603
Completed 4500 rows out of 7603
Completed 5000 rows out of 7603
Completed 5500 rows out of 7603
Completed 6000 rows out of 7603
Completed 6500 rows out of 7603
Completed 7000 rows out of 7603
Completed 7500 rows out of 7603
Importing table: psc_dce_tables (dce) 69 rows
Importing table: psc_dce_schema (dce) 43 rows
Importing table: Broadcast (dce) 1 rows
Importing table: CannedEvents (dce) 22 rows
Importing table: CannedEvents_CriteriaParmList (dce) 38 rows
Importing table: Command (dce) 843 rows
Completed 500 rows out of 843
Importing table: CommandCategory (dce) 47 rows
Importing table: CommandParameter (dce) 238 rows
Importing table: Command_CommandParameter (dce) 722 rows
Completed 500 rows out of 722
Importing table: Command_Pipe (dce) 25 rows
Importing table: ConfigType (dce) 0 rows
Importing table: ConfigType_File (dce) 0 rows
Importing table: ConfigType_Setting (dce) 0 rows
Importing table: ConfigType_Token (dce) 0 rows
Importing table: ConnectorType (dce) 10 rows
Importing table: CriteriaList (dce) 2 rows
Importing table: CriteriaList_CriteriaParmList (dce) 18 rows
Importing table: CriteriaParmList (dce) 21 rows
Importing table: DHCPDevice (dce) 62 rows
Importing table: DHCPDevice_DeviceData (dce) 0 rows
Importing table: DeviceCategory (dce) 119 rows
Importing table: DeviceCategory_DeviceData (dce) 55 rows
Importing table: DeviceCategory_Event (dce) 0 rows
Importing table: DeviceCommandGroup (dce) 74 rows
Importing table: DeviceCommandGroup_Command (dce) 485 rows
Importing table: DeviceCommandGroup_DeviceCommandGroup_Parent (dce) 0 rows
Importing table: DeviceData (dce) 210 rows
Importing table: DeviceTemplate (dce) 1854 rows
Completed 500 rows out of 1854
Completed 1000 rows out of 1854
Completed 1500 rows out of 1854
Importing table: DeviceTemplate_AV (dce) 60 rows
Importing table: DeviceTemplate_DSPMode (dce) 74 rows
Importing table: DeviceTemplate_DesignObj (dce) 4 rows
Importing table: DeviceTemplate_DeviceCategory_ControlledVia (dce) 1759 rows
Completed 500 rows out of 1759
Completed 1000 rows out of 1759
Completed 1500 rows out of 1759
Importing table: DeviceTemplate_DeviceCategory_ControlledVia_Pipe (dce) 1 rows
Importing table: DeviceTemplate_DeviceCommandGroup (dce) 210 rows
Importing table: DeviceTemplate_DeviceData (dce) 2244 rows
Completed 500 rows out of 2244
Completed 1000 rows out of 2244
Completed 1500 rows out of 2244
Completed 2000 rows out of 2244
Importing table: DeviceTemplate_DeviceTemplate_ControlledVia (dce) 59 rows
Importing table: DeviceTemplate_DeviceTemplate_ControlledVia_Pipe (dce) 36 rows
Importing table: DeviceTemplate_DeviceTemplate_Related (dce) 63 rows
Importing table: DeviceTemplate_Event (dce) 85 rows
Importing table: DeviceTemplate_InfraredGroup (dce) 9 rows
Importing table: DeviceTemplate_Input (dce) 235 rows
Importing table: DeviceTemplate_MediaType (dce) 59 rows
Importing table: DeviceTemplate_MediaType_DesignObj (dce) 9 rows
Importing table: DeviceTemplate_Output (dce) 0 rows
Importing table: DeviceTemplate_PageSetup (dce) 0 rows
Importing table: Event (dce) 72 rows
Importing table: EventCategory (dce) 9 rows
Importing table: EventParameter (dce) 59 rows
Importing table: Event_EventParameter (dce) 143 rows
Importing table: HouseMode (dce) 6 rows
Importing table: Licensing (dce) 0 rows
Importing table: MediaType (dce) 23 rows
Importing table: MediaType_Broadcast (dce) 0 rows
Importing table: Package (dce) 568 rows
Completed 500 rows out of 568
Importing table: Package_Compat (dce) 654 rows
Completed 500 rows out of 654
Importing table: Package_Directory (dce) 532 rows
Completed 500 rows out of 532
Importing table: Package_Directory_File (dce) 1250 rows
Completed 500 rows out of 1250
Completed 1000 rows out of 1250
Importing table: Package_Package (dce) 859 rows
Completed 500 rows out of 859
Importing table: Package_Source (dce) 1443 rows
Completed 500 rows out of 1443
Completed 1000 rows out of 1443
Importing table: Package_Source_Compat (dce) 1420 rows
Completed 500 rows out of 1420
Completed 1000 rows out of 1420
Importing table: Package_Users (dce) 20 rows
Importing table: Package_Version (dce) 0 rows
Importing table: PhoneLineType (dce) 0 rows
Importing table: QuickStartCategory (dce) 0 rows
Importing table: QuickStartTemplate (dce) 2 rows
Importing table: RepositorySource (dce) 21 rows
Importing table: RepositorySource_URL (dce) 17 rows
Importing table: RoomType (dce) 14 rows
Importing table: StartupScript (dce) 45 rows
Importing table: System (dce) 1 rows
Importing table: Version (dce) 46 rows

Which created the pluto_main database with a whole bunch of tables:

Code: [Select]

# mysql pluto_main
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 127 to server version: 5.0.27-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW TABLES;
+--------------------------------------------------+
| Tables_in_pluto_main                             |
+--------------------------------------------------+
| Broadcast                                        |
| CannedEvents                                     |
| CannedEvents_CriteriaParmList                    |
| Command                                          |
| CommandCategory                                  |
| CommandParameter                                 |
| Command_CommandParameter                         |
| Command_Pipe                                     |
| ConfigType                                       |
| ConfigType_File                                  |
| ConfigType_Setting                               |
| ConfigType_Token                                 |
| ConnectorType                                    |
| CriteriaList                                     |
| CriteriaList_CriteriaParmList                    |
| CriteriaParmList                                 |
| DHCPDevice                                       |
| DHCPDevice_DeviceData                            |
| DeviceCategory                                   |
| DeviceCategory_DeviceData                        |
| DeviceCategory_Event                             |
| DeviceCommandGroup                               |
| DeviceCommandGroup_Command                       |
| DeviceCommandGroup_DeviceCommandGroup_Parent     |
| DeviceData                                       |
| DeviceTemplate                                   |
| DeviceTemplate_AV                                |
| DeviceTemplate_DSPMode                           |
| DeviceTemplate_DesignObj                         |
| DeviceTemplate_DeviceCategory_ControlledVia      |
| DeviceTemplate_DeviceCategory_ControlledVia_Pipe |
| DeviceTemplate_DeviceCommandGroup                |
| DeviceTemplate_DeviceData                        |
| DeviceTemplate_DeviceTemplate_ControlledVia      |
| DeviceTemplate_DeviceTemplate_ControlledVia_Pipe |
| DeviceTemplate_DeviceTemplate_Related            |
| DeviceTemplate_Event                             |
| DeviceTemplate_InfraredGroup                     |
| DeviceTemplate_Input                             |
| DeviceTemplate_MediaType                         |
| DeviceTemplate_MediaType_DesignObj               |
| DeviceTemplate_Output                            |
| DeviceTemplate_PageSetup                         |
| Event                                            |
| EventCategory                                    |
| EventParameter                                   |
| Event_EventParameter                             |
| HouseMode                                        |
| Licensing                                        |
| MediaType                                        |
| MediaType_Broadcast                              |
| Package                                          |
| Package_Compat                                   |
| Package_Directory                                |
| Package_Directory_File                           |
| Package_Package                                  |
| Package_Source                                   |
| Package_Source_Compat                            |
| Package_Users                                    |
| Package_Version                                  |
| PhoneLineType                                    |
| QuickStartCategory                               |
| QuickStartTemplate                               |
| RepositorySource                                 |
| RepositorySource_URL                             |
| RoomType                                         |
| StartupScript                                    |
| System                                           |
| Version                                          |
| psc_dce_batdet                                   |
| psc_dce_bathdr                                   |
| psc_dce_batuser                                  |
| psc_dce_repset                                   |
| psc_dce_schema                                   |
| psc_dce_tables                                   |
+--------------------------------------------------+
75 rows in set (0.00 sec)



[hari]

Well I got a little further, I copied /usr/pluto/database/dce.sqlcvs from an existing installation, and this seems to have been the missing file.  I've dropped the database to start fresh again first.

based on your findings i set up a sqlCVS server. The procedure is documented in the wiki:
http://wiki.linuxmce.org/index.php/SqlCVS#How_to_set_up_a_sqlCVS_Server

regards,
Hari

[hari]

The procedure is documented in the wiki:
http://wiki.linuxmce.org/index.php/SqlCVS#How_to_set_up_a_sqlCVS_Server

The LinuxMCE webinterface for sqlCVS works fine. If there is demand i would host a master database for testing.

regards,
Hari

[tschak909]

I can set up one on my Xen slice as well. 

-Thom

[Zaerc]

Awesome work hari, and thank you very much for expanding the wiki page! 

[hari]

Awesome work hari, and thank you very much for expanding the wiki page! 

that was easy after your preparations. What i don't get is how to list the pending batches on the server. The LinuxMCE Admin interface doesn't print the id after checkin. Maybe it will help to use the "View batches" scripts from the admin page on the server.

We need a work flow for updates:
1.) Contact the maintainer before submission
2.) Checkin changes and report batch id to maintainer (or maintainer gets batch id from repo)
3.) Maintainer approves batch

With a test system and two or three people submitting changes we should figure out proper row ownership for the repository. I wonder if we should assign ownership of new rows to a default user. Maybe i should further investigate the permission model of sqlCVS.

I think we should have a developement and a production repository. We could synchronize them with sqlCVS (or maybe with the sqlCVS diff admin interface

@all: does anybody have information about the sqlCVS workflow @ pluto or contacts to pluto regarding sqlCVS?

@tschak909: do you have the slice ready? My public ip pool is empty, need a few days to free another ip and assign more cores and ram.

best regards,
hari

[tschak909]

yeah, my slice is a blank ubuntu server installation, ready to go.

-Thom

[PeteK]

@all: does anybody have information about the sqlCVS workflow @ pluto or contacts to pluto regarding sqlCVS?

They've worked with Paul to do it in the past, but unfortunately, it seems that Paul has other commitments right now, though he's working with Pluto to release the 0710 version.  Daniel K is picking up Paul's role in working with Pluto.  He would be the best person to answer this question.

[hari]

Hi Guys,

i have a repo for testing online.
I need two or three helpful souls to test the following:

Alice has a fresh database
Bob does a checkin of some stuff (e.g. a dce template) with given credentials
Hari approves the batch
Alice pulls the update and verifies the dce template
Charlie modifies template wand commits with other credentials

anyone?

the lazy guys can do a sqlCVS diff via web interface to see differences between the local database and the initial repository.

sqlCVS://62.99.143.187:3999

regards,
Hari

[Zaerc]

Love to help you test, but I'm going to need at least a few days to catch up first.

EDIT:

Starting from scratch, I first followed: http://wiki.linuxmce.org/index.php/SqlCVS#Importing_the_LinuxMCE_Data to get an initial DB setup.  And then: http://wiki.linuxmce.org/index.php/SqlCVS#Performing_an_update replacing <sqlCVS server port> with 3999 and <sqlCVS server host> with 62.99.143.187.  I answered 'y' to the question "Delete the bad data, and cleanup the repository table? [N/y]".  As far as I can tell there weren't any errors reported, so far so good...

Unfortunately I don't have the web-admin working yet on my experimental setup, so I'll try again later with an old test environment (that suffered from a broken DB) or maybe set up a fresh one just for this.  Then I should be able to add some stuff and check it in.  Would this be a suitable addition to test with: http://wiki.linuxmce.org/index.php/Logitech_QuickCam_Sphere?

[hari]

As far as I can tell there weren't any errors reported, so far so good...

great!

Would this be a suitable addition to test with: http://wiki.linuxmce.org/index.php/Logitech_QuickCam_Sphere?

i don't care for our first tests any device template will be fine.

best regards,
Hari