sqlCVS

[Zaerc]

Ok, so I did it the same on an old (and DB-broken) installation. Booted in recovery mode (started mysql by hand), dropped pluto_main, installed it again from the .sqlcvs files,  updated it against the "62.99.143.187" sqlCVS server and rebooted again.

After opening the admin website I had to create a new user as there weren't any (surprise surprise ).  But when I tried logging in I ran into the following error:

Code: [Select]

Fatal error: mysql error: [1064: You have an error in your SQL syntax; check the
 manual that corresponds to your MySQL server version for the right syntax to us
e near '?)) ORDER BY OrderNum ASC' at line 4] in EXECUTE(" SELECT DISTINCT PageS
etup.* FROM PageSetup LEFT JOIN DeviceTemplate ON PageSetup.FK_Package=DeviceTem
plate.FK_Package LEFT JOIN Device ON Device.FK_DeviceTemplate=PK_DeviceTemplate
WHERE showInTopMenu = 1 AND Website='1' AND (PageSetup.FK_Package IS NULL OR (PK
_Device IS NOT NULL AND FK_Installation=?)) ORDER BY OrderNum ASC") in /var/www/
pluto-admin/include/adodb/adodb-errorhandler.inc.php on line 77

I suspect that this is mainly because a few addidtional setup steps are needed (setting an installation number for instance).  I'll do some digging for those and if I can't figure that out then I'll just do a clean install and "update" that to get things going. 

In the mean time if anyone knows more about the post-install setup steps then I'd love to hear about it.

[Zaerc]

Well I finally did a fresh install on my test machine again, and tried a few sqlCVS diffs for starters.

Against the default pluto sqlCVS: Against the "hari" test sqlCVS:

It would be nice if someone could explain what all this means, but I won't be holding my breath.  I'm guessing "Revert" will sync my local DB with the master, and "CheckIn" will upload the "changes" I have (none yet) made locally to the masted DB.  Might not be a bad idea since this is a fresh installation.

I think I'll try "CheckIn" first, hoping that will give you already a batch to look at hari.  And it might not be a bad idea to have the master synced with a fresh install as a starting point. 

[hari]

It would be nice if someone could explain what all this means, but I won't be holding my breath.  I'm guessing "Revert" will sync my local DB with the master, and "CheckIn" will upload the "changes" I have (none yet) made locally to the masted DB.  Might not be a bad idea since this is a fresh installation.

go ahead.. as far as i get it this are only some old entries from the package system to be deleted by your batch.

I think I'll try "CheckIn" first, hoping that will give you already a batch to look at hari.  And it might not be a bad idea to have the master synced with a fresh install as a starting point. 

I think we should further investigate the import process. Maybe we are missing to setup additional tables for the master repository (like for history tracking and stuff). I'm still stuck with some other things. I don't see any way to show the batches with the "sqlCVS" utility alone.. at the moment i'm looking through the php scripts in pluto-admin/operations/sqlCVS. There is a batch history table for each repository. It's named "psc_<repository>_bathdr". To show the batch id's from changes in the dce repo i use "select * from psc_dce_bathdr order by date limit 20". I'll try to figure out how to get the exact sql statements from a batch.
When i get this right i'll update the wiki page.

happy hacking,
Hari

[Zaerc]

I just did a checkin before right you posted:

Code: [Select]

sqlCVS command

sudo -u root /usr/pluto/bin/sqlCVS -R 3999 -H 62.99.143.187 -h localhost -a -n -r dce -t Broadcast,CannedEvents,CannedEvents_CriteriaParmList,Command,CommandCategory,CommandParameter,Command_CommandParameter,Command_Pipe,ConfigType,ConfigType_File,ConfigType_Setting,ConfigType_Token,ConnectorType,CriteriaList,CriteriaList_CriteriaParmList,CriteriaParmList,DeviceCategory,DeviceCategory_DeviceData,DeviceCategory_Event,DeviceCommandGroup,DeviceCommandGroup_Command,DeviceCommandGroup_DeviceCommandGroup_Parent,DeviceData,DeviceTemplate,DeviceTemplate_AV,DeviceTemplate_DesignObj,DeviceTemplate_DeviceCategory_ControlledVia,DeviceTemplate_DeviceCategory_ControlledVia_Pipe,DeviceTemplate_DeviceCommandGroup,DeviceTemplate_DeviceData,DeviceTemplate_DeviceTemplate_ControlledVia,DeviceTemplate_DeviceTemplate_ControlledVia_Pipe,DeviceTemplate_DeviceTemplate_Related,DeviceTemplate_DSPMode,DeviceTemplate_Event,DeviceTemplate_InfraredGroup,DeviceTemplate_Input,DeviceTemplate_MediaType,DeviceTemplate_MediaType_DesignObj,DeviceTemplate_Output,DeviceTemplate_PageSetup,DHCPDevice,DHCPDevice_DeviceData,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 -d "pluto" -U "pluto~pluto" -D pluto_main -e -m /tmp/tmp_sqlcvs_file_6940 checkin 2>/tmp/sqlCVS-errors-1195065994.log | grep "Requesting update table" | cut -f4 -d' '

Status: success

I think I'll try "Revert" next, if only just to see what happens.  I have backed up the entire database before I started so I should be able to put that back just in case. 

EDIT:

The "Revert" went smooth as well:

Code: [Select]

sqlCVS command

sudo -u root /usr/pluto/bin/sqlCVS -R 3999 -H 62.99.143.187 -h localhost -a -n -r dce -t Broadcast,CannedEvents,CannedEvents_CriteriaParmList,Command,CommandCategory,CommandParameter,Command_CommandParameter,Command_Pipe,ConfigType,ConfigType_File,ConfigType_Setting,ConfigType_Token,ConnectorType,CriteriaList,CriteriaList_CriteriaParmList,CriteriaParmList,DeviceCategory,DeviceCategory_DeviceData,DeviceCategory_Event,DeviceCommandGroup,DeviceCommandGroup_Command,DeviceCommandGroup_DeviceCommandGroup_Parent,DeviceData,DeviceTemplate,DeviceTemplate_AV,DeviceTemplate_DesignObj,DeviceTemplate_DeviceCategory_ControlledVia,DeviceTemplate_DeviceCategory_ControlledVia_Pipe,DeviceTemplate_DeviceCommandGroup,DeviceTemplate_DeviceData,DeviceTemplate_DeviceTemplate_ControlledVia,DeviceTemplate_DeviceTemplate_ControlledVia_Pipe,DeviceTemplate_DeviceTemplate_Related,DeviceTemplate_DSPMode,DeviceTemplate_Event,DeviceTemplate_InfraredGroup,DeviceTemplate_Input,DeviceTemplate_MediaType,DeviceTemplate_MediaType_DesignObj,DeviceTemplate_Output,DeviceTemplate_PageSetup,DHCPDevice,DHCPDevice_DeviceData,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 -d "pluto" -U "pluto~pluto" -D pluto_main -e -m /tmp/tmp_sqlcvs_file_2190 revert 2>/tmp/sqlCVS-errors-1195067128.log | grep "Requesting update table" | cut -f4 -d' '

Status: success

Resulting in the following diff: So now I'm synced with your master.  I'm curious what happens when I start the core and orbiter now, probably won't make much difference, but there's only one way to find out...

EDIT:

As anticipated, the system still seems to run fine, I'll have a stab at adding a device template and maybe try to check that in as well, if that's ok with you.

EDIT:

I've added just the logitech quickcam template: so I'm all set to check a real template in whenever you give me the go ahead.

Any luck with the batches yet?  If you can approve this one I could restore the database backup I made earlier and update it from the master.  Eventhough you obviously have a way better understanding of what we're doing here, is there anything I can do to assist you with figuring out how these batches work?  Meanwhile I'll have a look over the sqlCVS documentation again as well.

I'm thinking if we just keep shaking the tree something useful will probably fall out sooner or later.  

[hari]

Hi Zaerc,

Any luck with the batches yet?  If you can approve this one I could restore the database backup I made earlier and update it from the master.  Eventhough you obviously have a way better understanding of what we're doing here, is there anything I can do to assist you with figuring out how these batches work?  Meanwhile I'll have a look over the sqlCVS documentation again as well.

I'm thinking if we just keep shaking the tree something useful will probably fall out sooner or later.  

*plonk*

there fell something out:
http://62.99.143.187:4000/sqlcvs/?site=batch/view

with the batch id one can show the database changes:
sqlcvs:/# sqlCVS -h "127.0.0.1" -u "root" -p "" -D "pluto_main" -P "3306" -r "dce" -t "" -U "hari~XXXXXX" batch-contents
[...]
Batch: 1828 Repository: dce IP: 192.168.117.3 Date: 2007-11-07 06:41:04

Table: Command
=========================
NEW- PK_Command:920 Description:Add Insteon Device to Group Define:(NULL) FK_CommandCategory:26 AVCommand:0 Comments:(NULL) Log:1 psc_id:901 psc_user:2 psc_frozen:0 psc_restrict:(NULL)
NEW- PK_Command:921 Description:TurnOnSprinklerValve Define:(NULL) FK_CommandCategory:36 AVCommand:0 Comments:(NULL) Log:1 psc_id:902 psc_user:2 psc_frozen:0 psc_restrict:(NULL)
NEW- PK_Command:922 Description:TurnOffSprinklerValve Define:(NULL) FK_CommandCategory:36 AVCommand:0 Comments:(NULL) Log:1 psc_id:903 psc_user:2 psc_frozen:0 psc_restrict:(NULL)

so i think we are far enough to be able to run kinda developement repository. We can export sqlcvs files anytime to keep svn branches in sync. Feel free to add devices. Send me a md4 pw via mail or pm, i'll create a user zaerc with admin perms.

best regards,
Hari

[Zaerc]

Awesome, great work!   Looks like you've made another huge leap forward.

I've just checked in that webcam sphere thingy:

Code: [Select]

sudo -u root /usr/pluto/bin/sqlCVS -R 3999 -H 62.99.143.187 -h localhost -a -n -r dce -t Broadcast,CannedEvents,CannedEvents_CriteriaParmList,Command,CommandCategory,CommandParameter,Command_CommandParameter,Command_Pipe,ConfigType,ConfigType_File,ConfigType_Setting,ConfigType_Token,ConnectorType,CriteriaList,CriteriaList_CriteriaParmList,CriteriaParmList,DeviceCategory,DeviceCategory_DeviceData,DeviceCategory_Event,DeviceCommandGroup,DeviceCommandGroup_Command,DeviceCommandGroup_DeviceCommandGroup_Parent,DeviceData,DeviceTemplate,DeviceTemplate_AV,DeviceTemplate_DesignObj,DeviceTemplate_DeviceCategory_ControlledVia,DeviceTemplate_DeviceCategory_ControlledVia_Pipe,DeviceTemplate_DeviceCommandGroup,DeviceTemplate_DeviceData,DeviceTemplate_DeviceTemplate_ControlledVia,DeviceTemplate_DeviceTemplate_ControlledVia_Pipe,DeviceTemplate_DeviceTemplate_Related,DeviceTemplate_DSPMode,DeviceTemplate_Event,DeviceTemplate_InfraredGroup,DeviceTemplate_Input,DeviceTemplate_MediaType,DeviceTemplate_MediaType_DesignObj,DeviceTemplate_Output,DeviceTemplate_PageSetup,DHCPDevice,DHCPDevice_DeviceData,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 -d "pluto" -U "pluto~pluto" -D pluto_main -e -m /tmp/tmp_sqlcvs_file_4410 checkin 2>/tmp/sqlCVS-errors-1195530916.log | grep "Requesting update table" | cut -f4 -d' '

Status: success

And I'll send you an MD5 hash in PM next. 

When this batch is approved I can restore my database from a backup to what it was before I changed it, and try to check the changes out again. 

[hari]

I've just checked in that webcam sphere thingy:

don't see the batch id. can you try again without the grep and cut:

Code: [Select]

sudo -u root /usr/pluto/bin/sqlCVS -R 3999 -H 62.99.143.187 -h localhost -a -n -r dce -t Broadcast,CannedEvents,CannedEvents_CriteriaParmList,Command,CommandCategory,CommandParameter,Command_CommandParameter,Command_Pipe,ConfigType,ConfigType_File,ConfigType_Setting,ConfigType_Token,ConnectorType,CriteriaList,CriteriaList_CriteriaParmList,CriteriaParmList,DeviceCategory,DeviceCategory_DeviceData,DeviceCategory_Event,DeviceCommandGroup,DeviceCommandGroup_Command,DeviceCommandGroup_DeviceCommandGroup_Parent,DeviceData,DeviceTemplate,DeviceTemplate_AV,DeviceTemplate_DesignObj,DeviceTemplate_DeviceCategory_ControlledVia,DeviceTemplate_DeviceCategory_ControlledVia_Pipe,DeviceTemplate_DeviceCommandGroup,DeviceTemplate_DeviceData,DeviceTemplate_DeviceTemplate_ControlledVia,DeviceTemplate_DeviceTemplate_ControlledVia_Pipe,DeviceTemplate_DeviceTemplate_Related,DeviceTemplate_DSPMode,DeviceTemplate_Event,DeviceTemplate_InfraredGroup,DeviceTemplate_Input,DeviceTemplate_MediaType,DeviceTemplate_MediaType_DesignObj,DeviceTemplate_Output,DeviceTemplate_PageSetup,DHCPDevice,DHCPDevice_DeviceData,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 -d "pluto" -U "pluto~pluto" -D pluto_main -e -m /tmp/tmp_sqlcvs_file_4410 checkin
this should spit out the batch id for "submission".

And I'll send you an MD5 hash in PM next. 

great.

When this batch is approved I can restore my database from a backup to what it was before I changed it, and try to check the changes out again. 

i'll approve as soon as i figure out the id

best regards,
hari

[Zaerc]

I guess I shouldn't have been so lazy to use the web-admin, but I figured it knows better what it's doing then I am... I've removed the "-m" option as well since it's not in the "usage" and I have no idea what it actually does, I suspect it sends some of the output to a logfile or something.  I had to paste all the output into a file and attach it since it was to big to put inside the message...

Looks like it did't go through this time.   Seeing those duplicate key errors, could it be in conflict with my earlier checkin attempt?

[hari]

I guess I shouldn't have been so lazy to use the web-admin, but I figured it knows better what it's doing then I am... I've removed the "-m" option as well since it's not in the "usage" and I have no idea what it actually does, I suspect it sends some of the output to a logfile or something.  I had to paste all the output into a file and attach it since it was to big to put inside the message...

Looks like it did't go through this time.   Seeing those duplicate key errors, could it be in conflict with my earlier checkin attempt?

yep. im just going through all psc_dce_* tables to trace the batch ids in sqlcvs... please be patient for an hour or two.. i'll approve your first commit (when i find it *g* )

best regards,
Hari

[Zaerc]

Thanks, I'm so glad at least one of us knows what he's doing.   And I'm not in any rush, so you can just take your time.

[hari]

Thanks, I'm so glad at least one of us knows what he's doing.   And I'm not in any rush, so you can just take your time.

i wanted to progress faster but without dsl (and my 3g provider is slooooooow) the last five weeks were really painful..
don't want to let you wait, i need helpful souls for testing

best regards,
Hari

[hari]

Thanks, I'm so glad at least one of us knows what he's doing.

i hope this is you 

The web gui got some improvements, maybe it helps to get some things sorted for you.. (also look at the wiki page, i did some updates)

best regards,
Hari

[Zaerc]

Thanks, I'm so glad at least one of us knows what he's doing.

i hope this is you 
...

If that were true we wouldn't even have gotten this far.

Anyway, I've been looking around a bit, and I have no clue what batch(es) to approve.  And when I look at: http://62.99.143.187:4000/sqlcvs/?site=batch/view I only see 2 recent IPs, localhost and an internal one.  So I have no idea which one to approve either. 

How about we approve all the recently added batches?  And then see if anything falls out of the tree again.

Oh BTW, logging in on your sqlcvs web interface seems to work (at least that's what it told me).

Edit:

Oh and I almost forgot, when I try to view a batch, I get a bunch of errors:

Code: [Select]

dcerouter_44670:~# /usr/pluto/bin/sqlCVS -h "127.0.0.1" -u "root" -p "" -D "pluto_main" -P "3306" -r "dce" -t "" -H 62.99.143.187 -U "zaerc~XXX" batch-contents
 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:127.0.0.1 user:root pass: name:pluto_main port:3306
Users:zaerc~XXX
Updating repository 'constants' schema version from: 4
Updating repository 'dce' schema version from: 44
Updating repository 'designer' schema version from: 39
Updating repository 'document' schema version from: 1
Updating repository 'ir' schema version from: 4
Updating repository 'local' schema version from: 50
Updating repository 'website' schema version from: 2
What batch number? (0 to quit) 1839
Batch: 1839 Repository: dce IP: 82.77.255.203 Date: 2007-08-08 21:56:30

Table: Version
=========================
01      11/21/07 21:50:07.016           Query failed (Table 'pluto_main.Version_pschist' doesn't exist): SELECT psc_id,psc_toc,`Version_pschist`.* FROM `Version_pschist` WHERE psc_batch=1839 (1) <0xb78d5a00>
05      11/21/07 21:50:07.016           Resetting mysql connection <0xb78d5a00>
01      11/21/07 21:50:07.017           Query failed (Table 'pluto_main.Version_pschist' doesn't exist): SELECT psc_id,psc_toc,`Version_pschist`.* FROM `Version_pschist` WHERE psc_batch=1839 (1) <0xb78d5a00>
05      11/21/07 21:50:07.018           Resetting mysql connection <0xb78d5a00>
01      11/21/07 21:50:07.018           Query failed (): SELECT psc_id,psc_toc,`Version_pschist`.* FROM `Version_pschist` WHERE psc_batch=1839 retry: (nil) <0xb78d5a00>


I figured I'd add the "-H 62.99.143.187" or I might be poking at pluto's sqlCVS (I get the same result without it though).

[hari]

Anyway, I've been looking around a bit, and I have no clue what batch(es) to approve.  And when I look at: http://62.99.143.187:4000/sqlcvs/?site=batch/view I only see 2 recent IPs, localhost and an internal one.  So I have no idea which one to approve either. 

i did some tests today and managed to checkin changes with a regular user (pluto~pluto). They show up with a negative batch id (not yet approved, sqlCVS calls this "quarantine"). After approving the batch id (works from the gui) with an admin user there is another batch generated (i call this "approval batch" for the moment) and the changes fall out of the tree I commited two dce devices to the database (a dhcp stub "Printserver" and an unfinished "Marantz SR7001 RS232").

How about we approve all the recently added batches?  And then see if anything falls out of the tree again.

Did you commit with your own user? Please try to checkin again with pluto~pluto (regular user) and approve with zaerc~XXXXX (sqlcvs admin).

Oh BTW, logging in on your sqlcvs web interface seems to work (at least that's what it told me).

the session handling is not finished yet. I also have to do some security fixes and implement permissions in the web interface. It's really quick and dirty hack to help me understand sqlCVS better It's in the trunk now.

At the moment i try to implement row history for modifications and deletions and understand how row ownership is assumed to work..
Can you point me to the installer script for the database? I wonder if we have to do other things beside importing with sqlCVS to setup a repository. I saw some mysql syntax errors in the sqlCVS log.. maybe i use the wrong mysql version..

best regards,
Hari

[hari]

Oh and I almost forgot, when I try to view a batch, I get a bunch of errors:

Code: [Select]

01      11/21/07 21:50:07.016           Query failed (Table 'pluto_main.Version_pschist' doesn't exist): SELECT psc_id,psc_toc,`Version_pschist`.* FROM `Version_pschist` WHERE psc_batch=1839 (1) <0xb78d5a00>
05      11/21/07 21:50:07.016           Resetting mysql connection <0xb78d5a00>
01      11/21/07 21:50:07.017           Query failed (Table 'pluto_main.Version_pschist' doesn't exist): SELECT psc_id,psc_toc,`Version_pschist`.* FROM `Version_pschist` WHERE psc_batch=1839 (1) <0xb78d5a00>
05      11/21/07 21:50:07.018           Resetting mysql connection <0xb78d5a00>
01      11/21/07 21:50:07.018           Query failed (): SELECT psc_id,psc_toc,`Version_pschist`.* FROM `Version_pschist` WHERE psc_batch=1839 retry: (nil) <0xb78d5a00>


it seems that you are missing the table "Version" from the dce repository. Maybe your initial import went wrong?

best regards,
Hari