Revision as of 22:56, 3 November 2007

Sample Uses

A chain of retail stores wants all the stores to share a common customer database, but not all the stores have high-speed internet connections to the main server. Each gets a local copy of the database and synchronizes nightly with the main server.

*or* At the end of the year a company wants to let the accountants work on year-end adjustments off-site or off-line. The accountants can do audits, make changes, roll-back changes that don't seem right and work with the database without touching the main database. Only when they are finished they merge in their changes.

*or* For faster performance and less strain on the master server employees work on local copies of the database and merge changes nightly.

*or* Even if you don't care about synchronizing or working off-line you still want good history tracking and the ability to roll-back erroneous changes. You could enable full logging in MySQL, but that doesn't let you roll-back one user's transactions on June 10, or remove everything that one user did, or checkout a copy of your database as of May 15 at 11:45.

Why did LinuxMCE develop it?

We built it for our LinuxMCE smart home system because our product is very database dependent. Everything is stored in a database--the documentation, the infrared codes, the programmer's class definitions, even the graphics user interface. We have a fully automated sqlCVS repository that automatically gives every user a working copy of the database and synchronizes shared tables, like infrared codes, with every other user.

How does it work?

The User's Manual has more details, but in a nutshell, you put the 'master' database on a server which only sqlCVS has access to, and run sqlCVS listen on the server which opens a port for incoming connections. Any clients then do a sqlCVS import (like a checkout) to get a local working copy. The clients do a sqlCVS update to merge their changes back into the master database, and pull changes from other users.

If there are conflicts because multiple people checked in records with the same auto-increment primary key, sqlCVS handles re-assigning the new number, and propagates the changes to all the foreign key's that reference it. If you change a row that someone else has changed, you are shown the difference and given the option of using your version or the other person's. sqlCVS keeps a full, permanent history of every modification and features atomic commits, so the database is "versioned" after each checkin. It tracks permissions and table + record level ownership. So, if user A makes changes to a row or table that belongs to user B, when he does a checkin, user A's changes are isolated and he is given a batch number and user B's user ID. User B can review the changes user A made, and then approve the batch, causing those changes to be merged into the master database. sqlCVS also supports forking, and remerging branches. At LinuxMCE we use this when we want to change versions, and to allow different people to work with the 'development', 'testing', and 'release' versions.

What changes do I have to make to my application?

sqlCVS is completely transparent. The software that uses the database does not need to be aware of sqlCVS. The only database consideration is that the field names must follow a logical naming convention so sqlCVS can determine what are foreign keys.

How to set up a sqlCVS Server

Prerequisites

• a Linux server
• a LinuxMCE installation
• a MySQL server version 5

Preparing the database

MySQL must be reachable over the network. It's socket can't be accessed from the chroot. Maybe you want to create a user and grant permission to the "pluto_main" database. Don't forget the schema.

Creating the "MasterUsers" database

Create a database "MasterUsers" and import the table "MasterUsers". It is derived from the regular "pluto_main.Users" table and the php function "add_master_user" in http://svn.plutohome.com/pluto/trunk/web/masterusers-site/masterusers-site/users/add_master_user.php

CREATE TABLE `MasterUsers` (
  `PK_MasterUsers` int(11) NOT NULL auto_increment,
  `PlutoId` varchar(32) NOT NULL,
  `Pin` varchar(32) NOT NULL default '0',
  `FirstAccount` varchar(32) NOT NULL,
  `Username` varchar(25) NOT NULL,
  `Password` varchar(32) NOT NULL,
  `PwdIsTemp` tinyint(1) default NULL,
  `Email` varchar(50) default NULL,
  `SignupDate` varchar(50) default NULL,
  `FK_MasterUsers_Referrer` varchar(50) default NULL,
  `sqlCvsAdmin` tinyint(1) default '0',
  `RecordVersion` int(11) NOT NULL default '1',
  `psc_id` int(11) default NULL,
  `psc_batch` int(11) default NULL,
  `psc_user` int(11) default NULL,
  `psc_frozen` tinyint(1) default '0',
  `psc_mod` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `psc_restrict` int(11) default NULL,
  PRIMARY KEY  (`PK_MasterUsers`),
  UNIQUE KEY `psc_id` (`psc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A sample user could look like this:

INSERT INTO `MasterUsers` VALUES (1,'000001','C06','Users','hari','c13b52531693f788a16fd56c35a47f04',0,'hari@vt100.at',
'20071103193538','',0,1,NULL,NULL,NULL,0,'2007-11-03 18:41:22',NULL);

Setting up a chroot

To run sqlCVS on any distribution without hassles we simply pull all required files from the LinuxMCE installation. The utility "ldd" shows the library dependencies. Simply copy the files to a new directory structure. Don't forget to include the database sqlcvs files if you want to prepare a LinuxMCE sqlCVS repository. For more comfort we also add "bash" and "ls". So we end up with a directory containing the following files (filelist deprecated, missing libnss-*, resolv.con, lookup for "localhost" and "dcerouter" is needed for sqlCVS, dont forget "/etc/hosts"):

server:~/chroot# find
.
./sbin
./sbin/ldconfig
./etc
./etc/ld.so.conf
./etc/ld.so.cache
./etc/pluto.conf
./lib
./lib/libcrypt.so.1
./lib/libacl.so.1
./lib/libmysqlclient_r.so.15
./lib/libsepol.so.1
./lib/libSerializeClass.so
./lib/libDCECommon.so
./lib/libPlutoUtils.so
./lib/libnsl.so.1
./lib/libz.so.1
./lib/libselinux.so.1
./lib/libpthread.so.0
./lib/libncurses.so.5
./lib/libgcc_s.so.1
./lib/libm.so.6
./lib/libattr.so.1
./lib/ld-linux.so.2
./lib/libstdc++.so.6
./lib/libc.so.6
./lib/libRACommon.so
./lib/librt.so.1
./lib/libdl.so.2
./bin
./bin/sqlCVS
./bin/ls
./bin/bash
./database
./database/media.sqlcvs
./database/telecom.sqlcvs
./database/constants.sqlcvs
./database/designer.sqlcvs
./database/security.sqlcvs
./database/city.dump
./database/dce.sqlcvs
./database/document.sqlcvs
./database/website.sqlcvs
./database/ir.sqlcvs
./database/local.sqlcvs

Importing the LinuxMCE Data

The first run is primary for creating the "pluto_main" database. The schema will be violated until "city.dump" is imported. The second run will fix the schema.

chroot chroot/
cd database
for i in constants dce designer document ir local media security telecom website; do /bin/sqlCVS -h 127.0.0.1 -u root -r $i import ; done
exit
mysql pluto_main < chroot/database/city.dump
chroot chroot/
cd database
for i in constants dce designer document ir local media security telecom website; do /bin/sqlCVS -h 127.0.0.1 -u root -r $i import ; done
exit

Running the server

The "listen" option starts the server. On the first run answer "y" to fix the repository:

server:/database# /bin/sqlCVS -h 127.0.0.1 listen
 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:
Found an entry in psc_telecom_tables for the table: Users
But it already exists in the repository: local
I must delete it from this repository's database to continue.
Found an entry in psc_telecom_tables for the table: Users
But it already exists in the repository: local
I must delete it from this repository's database to continue.
Delete the bad data, and cleanup the repository table? [N/y] 
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 'media' schema version from: 41
Updating repository 'security' schema version from: 5
Updating repository 'telecom' schema version from: 3
Updating repository 'website' schema version from: 2
13      11/03/07 15:16:12.980           TCPIP: Listening on 3485. <0xb7982b90>

Managing users

The users are stored in the db "MasterUsers" table "MasterUsers". There is a Web-Interface at http://svn.plutohome.com/pluto/trunk/web/masterusers-site. But it seems to be only used for kinda "http-rpc" and depends on multiple databases (mantis, phpbb, ...). Some tweaks give us a simple "add user" interface:

root@bach:/var/www/pluto/masterusers-site# svn diff -x -u masterusers-site
Index: masterusers-site/users/add_master_user.php
===================================================================
--- masterusers-site/users/add_master_user.php  (Revision 17192)
+++ masterusers-site/users/add_master_user.php  (Arbeitskopie)
@@ -1,5 +1,5 @@
 <?
-function add_master_user($conn,$connPlutoVip,$connPlutoHome,$connphpBB,$connMantis){
+function add_master_user($conn){
        // add to MasterUsers table 
        if(isset($_POST['typeUser'])){
                $typeUser=$_POST['typeUser'];
@@ -31,38 +31,6 @@
                        $plutoId=(calculatePlutoID($PK_MasterUsers));
                        $updateMasterUsers=mysql_query("UPDATE MasterUsers SET PlutoId='$plutoId' WHERE PK_MasterUsers='$PK_MasterUsers'",$conn);
 
-                       if($updateMasterUsers){
-                               // sync with PlutoVip
-                               $insertPlutoVipMasterUsers=mysql_query("INSERT INTO MasterUsers 
-                                                                                                       (PK_MasterUsers,PlutoId,Pin,FirstAccount,Username,Email,SignupDate, FK_MasterUsers_Referrer,RecordVersion) 
-                                                                                       VALUES  ('$PK_MasterUsers','$plutoId','$pin','$typeUser','$username','$email','$signupDate','$referrer',1)",$connPlutoVip);
-                               if($insertPlutoVipMasterUsers)
-                                       $updateMasterUsers=mysql_query("UPDATE MasterUsers SET Sync_PlutoVip='1' WHERE PK_MasterUsers='$PK_MasterUsers'",$conn);
-                               // sync with PlutoHome
-                               $insertPlutoHomeMasterUsers=mysql_query("INSERT INTO MasterUsers 
-                                                                                                       (PK_MasterUsers,PlutoId,Pin,FirstAccount,Username,Email,SignupDate, FK_MasterUsers_Referrer,RecordVersion) 
-                                                                                       VALUES  ('$PK_MasterUsers', '$plutoId','$pin','$typeUser','$username','$email','$signupDate','$referrer',1)",$connPlutoHome);
-                               if($insertPlutoHomeMasterUsers)
-                                       $updateMasterUsers=mysql_query("UPDATE MasterUsers SET Sync_PlutoHome='1' WHERE PK_MasterUsers='$PK_MasterUsers'",$conn);
-
-                               $random_password=generatePassword();
-                               $random_passwordMD5=md5($random_password);
-                               // sync with phpBB
-                               $insertphpBB=mysql_query("INSERT INTO phpbb_users (user_id,username,user_password,user_email,user_lang)
-                                                                                                       VALUES  ('$PK_MasterUsers','$username','$random_passwordMD5','$email','english')",$connphpBB);
-
-                               $insertExtPassword=mysql_query("INSERT INTO Users(FK_MasterUsers,ExtPassword)
-                                                                                                       VALUES  ('$PK_MasterUsers','$random_password')",$connPlutoHome);
-                               if($insertphpBB && $insertExtPassword)
-                                       $updateMasterUsers=mysql_query("UPDATE MasterUsers SET Sync_phpBB='1' WHERE PK_MasterUsers='$PK_MasterUsers'",$conn);
-
-                               // sync with Mantis
-                               $insertMantis=mysql_query("INSERT INTO mantis_user_table (id, username, password, email,date_created,cookie_string)
-                                                                                                       VALUES  ('$PK_MasterUsers','$username','$random_passwordMD5','$email','NOW()','$PK_MasterUsers')",$connMantis);
-                               if($insertMantis && $insertExtPassword)
-                                       $updateMasterUsers=mysql_query("UPDATE MasterUsers SET Sync_Mantis='1' WHERE PK_MasterUsers='$PK_MasterUsers'",$conn);
-
-                       }
                        if($insertMasterUsers){
                                $out="MasterUsersID=$PK_MasterUsers";
                        }
@@ -74,4 +42,4 @@
        }
        echo $out;
  }
-?>
\ Kein Zeilenvorschub am Ende der Datei
+?>
Index: masterusers-site/include/config/database.inc.php
===================================================================
--- masterusers-site/include/config/database.inc.php    (Revision 17192)
+++ masterusers-site/include/config/database.inc.php    (Arbeitskopie)
@@ -4,32 +4,9 @@
   $dbUser = 'root';
   $dbPass = '';
   $dbDatabase = 'MasterUsers';
-  $dbServer = '10.0.0.150';
+  $dbServer = '127.0.0.1';
   $dbType = 'mysql';
 
-  $dbPlutoVipUser = 'root';
-  $dbPlutoVipPass = '';
-  $dbPlutoVipDatabase = 'plutovip';
-  $dbPlutoVipServer = '10.0.0.150';
-  $dbPlutoVipType = 'mysql';
-
-  $dbPlutoHomeUser = 'root';
-  $dbPlutoHomePass = '';
-  $dbPlutoHomeDatabase = 'pluto_website';
-  $dbPlutoHomeServer = '10.0.0.150';
-  $dbPlutoHomeType = 'mysql'; 
-  
-  $dbphpBBUser = 'root';
-  $dbphpBBPass = '';
-  $dbphpBBDatabase = 'phpbb2';
-  $dbphpBBServer = '10.0.0.150';
-  $dbphpBBType = 'mysql';
-  
-  $dbMantisUser = 'root';
-  $dbMantisPass = '';
-  $dbMantisDatabase = 'mantis';
-  $dbMantisServer = '10.0.0.150';
-  $dbMantisType = 'mysql';
 /*
  include('include/adodb/adodb-errorhandler.inc.php');
  require('include/adodb/adodb.inc.php');
@@ -61,16 +38,4 @@
   $conn=mysql_connect($dbServer,$dbUser,$dbPass) or die('could not connect to database');
   $db=mysql_select_db($dbDatabase,$conn) or die("could not select $dbDatabase");
   
-  $connPlutoVip=mysql_connect($dbPlutoVipServer,$dbPlutoVipUser,$dbPlutoVipPass,true) or die('could not connect to database');
-  $dbPlutoVip=mysql_select_db($dbPlutoVipDatabase,$connPlutoVip) or die("could not select $dbPlutoVipDatabase");
-
-  $connPlutoHome=mysql_connect($dbPlutoHomeServer,$dbPlutoHomeUser,$dbPlutoHomePass,true) or die('could not connect to database');
-  $dbPlutoHome=mysql_select_db($dbPlutoHomeDatabase,$connPlutoHome) or die("could not select $dbPlutoHomeDatabase");
-
-  $connphpBB=mysql_connect($dbphpBBServer,$dbphpBBUser,$dbphpBBPass,true) or die('could not connect to database');
-  $dbphpBB=mysql_select_db($dbphpBBDatabase,$connphpBB) or die("could not select $dbphpBBDatabase");
-
-  $connMantis=mysql_connect($dbMantisServer,$dbMantisUser,$dbMantisPass,true) or die('could not connect to database');
-  $dbMantis=mysql_select_db($dbMantisDatabase,$connMantis) or die("could not select $dbMantisDatabase");
-
 ?>
Index: masterusers-site/index.php
===================================================================
--- masterusers-site/index.php  (Revision 17192)
+++ masterusers-site/index.php  (Arbeitskopie)
@@ -1,6 +1,7 @@
 <?php
 session_start();
 
+echo "<h1>LinuxMCE sqlCVS User Management</h1>";
 require('include/config/config.inc.php');
 require('include/utils.inc.php');
 
@@ -36,7 +37,21 @@
        break;
 
        default:
-       echo "";
+       echo "<h2>Add User</h2>";
+               ?>
+               <FORM METHOD="POST" ACTION="index.php">
+               <INPUT TYPE="hidden" NAME="section" VALUE="add_master_user">
+               <INPUT TYPE="hidden" NAME="typeUser" VALUE="Users">
+               Username: <INPUT TYPE="text" NAME="username"><br>
+               Password: <INPUT TYPE="password" NAME="password"><br>
+               Email: <INPUT TYPE="text" NAME="email"><br>
+
+               <INPUT TYPE="submit" NAME="submit" VALUE="Add user">
+               </FORM>
+
+
+
+               <?
        break;
 }
-?>
\ Kein Zeilenvorschub am Ende der Datei
+?>

Performing an update

You can update your LinuxMCE installation from the sqlCVS server. The following example updates the repository "dce":

dcerouter# /usr/pluto/bin/sqlCVS -R <sqlCVS server port> -H <sqlCVS server host> -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 "root" -U "sqlcvsuser~sqlcvspassword" -D pluto_main -e update

Checkin changes

/usr/pluto/bin/sqlCVS -H 192.168.38.254 -R 3485 -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 "root" -U "sqlcvsuser~sqlcvspass" -D pluto_main -e checkin

Approving a patch

We can now apply the commit with "sqlCVS approve" on the sqlCVS server:

bash-3.2# sqlCVS -h "127.0.0.1" -u "root" -p "" -D "pluto_main" -P "3306" -r "dce" -t "" -U "sqlcvsuser~sqlcvspass" approve
 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:hari~wipeout
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 'media' schema version from: 41
Updating repository 'security' schema version from: 5
Updating repository 'telecom' schema version from: 3
Updating repository 'website' schema version from: 2
What is the batch number (enter q to quit)? 1831
05      11/03/07 19:37:08.399           void ClientSocket::Disconnect() on this socket: 0x819d420 (m_Socket: 4) <0xb795f6d0>
13      11/03/07 19:37:08.400           Socket::Close() m_Socket 4 <0xb795f6d0>
13      11/03/07 19:37:08.400           Socket::Close() m_Socket 4 closesocket: 0 <0xb795f6d0>
13      11/03/07 19:37:08.400           Socket::~Socket(): deleting socket @0x819d420 foo (socket id in destructor: m_Socket: -1) <0xb795f6d0>
Approved batch in repository: dce
bash-3.2#