Difference between revisions of "SqlCVS"

From LinuxMCE
Jump to: navigation, search
(→‎History Table "<Tablename>_pschist")
(→‎Creating the "MasterUsers" database)
 
(17 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
{| align="right"
+
{| align="right"
 
   | __TOC__
 
   | __TOC__
 
   |}
 
   |}
 
[[Category: Programmer's Guide]]
 
[[Category: Programmer's Guide]]
  
−
=== Sample Uses ===
+
= Introduction =
 +
 
 +
This article describes how to use sqlCVS in general. There is a separate article on committing SQL into the linuxmce sqlCVS repository, please see the [[LinuxMCEsqlCVScommitProcedure|LinuxMCE sqlCVS commit process]] for information on how to share a new IR code or other improvement with the community.
 +
 
 +
== 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.
 
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.
Line 24: Line 28:
 
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.
 
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 ==
+
= Users Manual =
 +
== Overview ==
 +
=== Master Database ===
 +
<p>Here is a general overview of how to use sqlCVS, designed to explain quickly what you can do with it.  This assumes you have a master database, we will call 'masterdb' somewhere.</p>
 +
<p>First, put the masterdb on a server somewhere where you will run sqlCVS.  Once you do, you should not use the database directly again.  If you have a web server, for example, that uses the database, it will now run against a separate copy of the database--not the masterdb that sqlCVS will be using.  The masterdb that sqlCVS uses should only be used by sqlCVS, and everyone else will use local copies they checkout from sqlCVS.</p>
 +
=== Repository creation ===
 +
<p>Next, on the server, run sqlCVS passing it the login information for the masterdb.  You will now use the <i>create</i> command to create repository(ies) within the masterdb.  A repository is really just a collection of tables that you will want to logically group together.  For example, you could create an 'Accounts Payable' repository which contained all the tables related to A/P.  When local users want to do a check-out, they will check-out a whole repository.  You can create just 1 repository and put all the tables in it, in which case all users will check-out and check-in the whole database at once.  There may be some tables that you do not put in any repository, but then nobody will be able to access them using sqlCVS.</p>
 +
=== History tracking ===
 +
<p>When creating the repository, we recommend you turn on 'history tracking'.  This causes sqlCVS to create 2 other copies of every table, called [table]_pschist and [table]_pscmask.  These copies has the same structure as the original table, but with all the constraints removed.  Every time someone changes a row, the full contents of the row will be saved into the pschist table, and a record with bit flags added to _pscmask indicating which fields were modified.  This is how sqlCVS can go back in time and undo changes, or restore the database to a prior state.  Your original table will still have only the current values, just like it always did.</p>
 +
=== Creating dump files ===
 +
<p>Next you run sqlCVS with the <i>dump</i> command.  This creates a [repository].sqlcvs file that contains a working copy of the repository (or database) for you to give to the clients.  The clients use the same sqlCVS program (binary) as the server--the whole program is in 1 file shared by both server and client.  The client uses the <i>import</i> command to import the .sqlcvs into their local MySQL database.  This becomes their working copy, and has the same structure as the original database you started with.  It doesn't matter if the .sqlcvs file they import is very old.  It's only a starting point--as soon as they do a sqlCVS <i>update</i> or <i>sync</i> the local copy will be brought current.</p>
 +
=== sqlCVS server mode ("listen") ===
 +
<p>On the server you run sqlCVS with the <i>listen</i> command.  That causes sqlCVS to open a tcp/ip port for an incoming connection from a client, and allows clients to connect to do checkins and updates.</p>
 +
=== Client notes ===
 +
<p>On the client the user will continue to use all the same software without changing anything--he just points the database connection to his local copy rather than the server's.  Your web site, your accounting program, etc. all will use the working copies and will no longer connect directly to the masterdb.  Since the working copy is essentially identical to the original masterdb all the software that uses the database should be unaffected and should work fine with this client copy.  The only difference in the database is the addition of some special psc_ fields at the end of each table.  However, the fields all have default values and you do not ever need to touch them.  The only time this may cause a problem for your software is if it does an INSERT statement without specifying the field names, assuming a certain number of fields.  In that case, the insert statement may fail because the table now contains more fields.  The other potential problem is if your table has a 'timestamp' field.  One of the special psc_ fields is a 'timestamp' so sqlCVS can keep track of what records were modified, but MySQL does not allow a table to have 2 timestamps and still work as expected.</p>
 +
=== Important notes ===
 +
<p><b>***IMPORTANT NOTE***</b>  Some editors, like SQLyog, do not know how to properly handle timestamp fields.  They reset the timestamp field every time the row changes.  This is actually a bug in those editors since it defeats the whole purpose of a timestamp.  PhpMyAdmin does not have this problem, and your software will not either since your software will be unaware of the timestamp field.</p>
 +
 
 +
<p>sqlCVS also added a psc_id field to each table.  This is sqlCVS's internal ID for that row.  It is permanent, and will never change.  This is how sqlCVS is able to accurately know what row was modified.  You can change all your fields, including the primary key, and sqlCVS will still update the correct row in the master database.  This field defaults to NULL and is assigned a value only when you check-in the new row.  That is how sqlCVS knows whether a row is new.</p>
 +
 
 +
<p>There is also a psc_user field.  This contains the user id of the person who owns the record.  The security settings can be changed, but by default only the owner of a record or an administrator can go back and change that record.  The field defaults to NULL, and when the new rows are checked in, sqlCVS will set them to be the user id of whoever is doing the checkin--he becomes the "owner" of those records.  This means that if you have 30 people using the database and adding records, only the person who does the checkin will later be the 'owner' of those rows.  You may modify your application to set the psc_user manually, and sqlCVS will respect your values.  However, once the row has been checked in, the sqlCVS on the server will not allow any local users to change this value.  To change the owner of a record after checkin, you will need to update the record in the sqlCVS master database.  Or you can turn off the security, allowing anyone to modify any records, or have only table-level security, and then it does not matter anymore.  If you do keep the default row-level security and a user who is not an administrator modifies a row that does not belong to him or tries to modify a row or table that is marked as "frozen" or he does not have permission to modify, then when he does a sqlCVS checkin, the server will keep his modifications in a special place, and give him a batch #, together with the name of the user who owns those records.  Then that user, or any administrator, can run sqlCVS 'approve batch' to have those changes put back into the master database.</p>
 +
 
 +
<p>If you have history tracking enabled in the sqlCVS master databsae, you can also checkout local copies of the database as of a given date, or with some modifications ignored.  For example, you can see what the database looked like as of last month, or get a copy that omits all the modifications made by a certain user.</p>
 +
 
 +
<p>When you do a check-in, all changes, including new, deleted and modified rows, are committed in a "batch", and given a batch id.  If the database engine you are using supports transactions (commits and rollbacks), then your checkins will be atomic.  In other words, if you modified 100 rows, when you do a check-in, all 100 rows will be sent to the server in a batch.  If there is a failure--maybe you entered some data in your local copy that violates a constraint--then none of the rows will be checked in.  It's all or nothing.  If the database engine does not support transactions, it's possible to get some rows checked-in, but not all.  This will not break anything, and sqlCVS will checkin the rest of the changes in another batch, but usually atomic commits are preferred.</p>
 +
 
 +
<p>You can also see what modifications where made to the database by date, user, or batch.  If you're an administrator, you can roll back the master database to it's state at a given time, or selectively remove some batches after the fact.  There's also a command-line switch that forces strict verification of database integrity and will not allow rows that reference foreign keys that do not exist.</p>
 +
 
 +
== How to set up a LMCE sqlCVS Server ==
  
 
This howto describes the setup of a sqlCVS server hosting a LinuxMCE database. Your mileage may vary for other purposes.
 
This howto describes the setup of a sqlCVS server hosting a LinuxMCE database. Your mileage may vary for other purposes.
Line 32: Line 64:
 
* 1 Linux server
 
* 1 Linux server
 
* 1 LinuxMCE installation (or other means of getting a running sqlCVS binary)
 
* 1 LinuxMCE installation (or other means of getting a running sqlCVS binary)
−
* .sqlcvs database files (creating a new repository is not yet investigated)
 
−
* MySQL server version 5
 
 
* sqlCVS binary from SVN (includes some fixes for MySQL 5)
 
* sqlCVS binary from SVN (includes some fixes for MySQL 5)
 +
* .sqlcvs database files or a mysqldump for the pluto_main database
 +
* MySQL server version 5
  
 
=== Preparing the database ===
 
=== Preparing the database ===
Line 70: Line 102:
 
A sample user could look like this:
 
A sample user could look like this:
 
<pre>
 
<pre>
−
INSERT INTO `MasterUsers` VALUES (1,'000001','C06','Users','hari','c13b52531693f788a16fd56c35a47f04',0,'hari@vt100.at',
+
INSERT INTO `MasterUsers` VALUES (1,'000001','C06','Users','hari',md5('sUperSecreTpassword'),0,'hari@vt100.at',
 
'20071103193538','',0,1,NULL,NULL,NULL,0,'2007-11-03 18:41:22',NULL);
 
'20071103193538','',0,1,NULL,NULL,NULL,0,'2007-11-03 18:41:22',NULL);
 
</pre>
 
</pre>
 +
 +
Make sure to set sqlCvsAdmin to 1 for people who need to commit stuff to schema.
  
 
=== Setting up a chroot ===
 
=== Setting up a chroot ===
Line 188: Line 222:
 
=== Managing users ===
 
=== 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. The password has to be specified as md5 hash.
+
Currently users are managed manually in the MasterUsers table.
  
 
NOTE: there is a new web interface at http://svn.charonmedia.org/svn/trunk/web/sqlcvsweb. It includes add user functionality.
 
NOTE: there is a new web interface at http://svn.charonmedia.org/svn/trunk/web/sqlcvsweb. It includes add user functionality.
Line 438: Line 472:
  
 
= sqlCVS data modell =
 
= sqlCVS data modell =
 +
== Detailed psc table description ==
 +
[[What_do_the_psc_tables_do%3F]]
 
== Batches ==
 
== Batches ==
 
=== Header Table "psc_<repo>_bathdr" ===
 
=== Header Table "psc_<repo>_bathdr" ===
Line 470: Line 506:
 
== History ==
 
== History ==
 
=== History Table "<Tablename>_pschist" ===
 
=== History Table "<Tablename>_pschist" ===
−
sqlCVS maintains history tables. The schema is taken from the original table, but there is one modification: the primary key in the history table is the combination of the batch id (psc_batch) and the psc row id (psc_id). So sqlCVS can track modifications of the PK_<Table>, too.
+
sqlCVS maintains history tables. The schema is taken from the original table, but there is one modification: the primary key in the history table is the combination of the batch id (psc_batch) and the psc row id (psc_id). So sqlCVS can track modifications of the PK_<Table>, too. There is one special thing for history tables: if a batch is not approved (e.g. checked in with the password "nopass") its written to the history table and referenced by a negative batch id. After approval it gets moved to the main table.
 
<pre>
 
<pre>
 
+--------------+-------------+------+-----+-------------------+-------+
 
+--------------+-------------+------+-----+-------------------+-------+
Line 527: Line 563:
 
| psc_frozen  | tinyint(1)  | YES  |    | 0                |                | # frozen rows cant be changed
 
| psc_frozen  | tinyint(1)  | YES  |    | 0                |                | # frozen rows cant be changed
 
| psc_mod      | timestamp  | YES  |    | CURRENT_TIMESTAMP |                | # last modification of the row
 
| psc_mod      | timestamp  | YES  |    | CURRENT_TIMESTAMP |                | # last modification of the row
−
| psc_restrict | int(11)    | YES  |    | NULL              |                |
+
| psc_restrict | int(11)    | YES  |    | NULL              |                | # if != NULL do not update clients
 
</pre>
 
</pre>
 +
 
== Repository ==
 
== Repository ==
−
=== Repository Settings Table "psc_<repo>_repset ===
+
=== Repository Settings Table "psc_<repo>_repset" ===
 
<pre>
 
<pre>
 
+-------------------+-------------+------+-----+---------+----------------+
 
+-------------------+-------------+------+-----+---------+----------------+
Line 550: Line 587:
 
1 row in set (0.02 sec)
 
1 row in set (0.02 sec)
 
</pre>
 
</pre>
 +
=== Repository Tables Table "psc_<repo>_tables" ===
 +
the "psc_<repo>_tables" references all tables in a repository:
 +
<pre>
 +
mysql> describe psc_dce_tables;
 +
+-------------------+--------------+------+-----+---------+----------------+
 +
| Field            | Type        | Null | Key | Default | Extra          |
 +
+-------------------+--------------+------+-----+---------+----------------+
 +
| PK_psc_dce_tables | int(11)      | NO  | PRI | NULL    | auto_increment |
 +
| Tablename        | varchar(60)  | NO  |    |        |                |
 +
| filter            | varchar(255) | YES  |    | NULL    |                | ~~~ This seems to be the filter for what gets exported, i.e. where 0 means, no records are exported.
 +
| frozen            | tinyint(1)  | NO  |    | 0      |                | if true no changes to that table are accepted
 +
| anonymous        | tinyint(1)  | NO  |    | 0      |                | if true anonymous~anonymous can check in to this table
 +
| last_psc_id      | int(11)      | NO  |    | 0      |                | highest psc_id value in that table
 +
| last_psc_batch    | int(11)      | NO  |    | 0      |                | highest batch_id affecting that table
 +
+-------------------+--------------+------+-----+---------+----------------+
 +
</pre>
 +
 +
=== Schema Table "psc_<repo>_schema" ===
 +
This table holds the schema changes for tables in a repository. Example:
 +
<pre>
 +
mysql> describe psc_dce_schema;
 +
+-------------------+---------+------+-----+---------+----------------+
 +
| Field            | Type    | Null | Key | Default | Extra          |
 +
+-------------------+---------+------+-----+---------+----------------+
 +
| PK_psc_dce_schema | int(11) | NO  | PRI | NULL    | auto_increment |
 +
| Value            | text    | NO  |    |        |                |
 +
+-------------------+---------+------+-----+---------+----------------+
 +
</pre>
 +
Inside there are "ALTER TABLE" statements and so on to modify the schema:
 +
<pre>
 +
mysql> select * from psc_constants_schema;
 +
+-------------------------+-------------------------------------------------------------------------------------------------------------------+
 +
| PK_psc_constants_schema | Value                                                                                                            |
 +
+-------------------------+-------------------------------------------------------------------------------------------------------------------+
 +
|                      2 | alter table `Distro` ,change `InstallerURL` `InstallerURL` varchar (100)  NULL                                  |
 +
|                      3 | alter table `Country` ,add column `UsePostalCode` tinyint (1)  DEFAULT '0' NOT NULL  after `Define`              |
 +
|                      4 | alter table `Pipe` ,drop column `FK_Pipe_UsePath` ,add column `Define` varchar (10)  NULL  after `Description`  |
 +
+-------------------------+-------------------------------------------------------------------------------------------------------------------+
 +
</pre>
 +
 +
= Tutorial for new repositories =

Latest revision as of 19:59, 6 October 2011

Introduction

This article describes how to use sqlCVS in general. There is a separate article on committing SQL into the linuxmce sqlCVS repository, please see the LinuxMCE sqlCVS commit process for information on how to share a new IR code or other improvement with the community.

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.

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.

Users Manual

Overview

Master Database

Here is a general overview of how to use sqlCVS, designed to explain quickly what you can do with it. This assumes you have a master database, we will call 'masterdb' somewhere.

First, put the masterdb on a server somewhere where you will run sqlCVS. Once you do, you should not use the database directly again. If you have a web server, for example, that uses the database, it will now run against a separate copy of the database--not the masterdb that sqlCVS will be using. The masterdb that sqlCVS uses should only be used by sqlCVS, and everyone else will use local copies they checkout from sqlCVS.

Repository creation

Next, on the server, run sqlCVS passing it the login information for the masterdb. You will now use the create command to create repository(ies) within the masterdb. A repository is really just a collection of tables that you will want to logically group together. For example, you could create an 'Accounts Payable' repository which contained all the tables related to A/P. When local users want to do a check-out, they will check-out a whole repository. You can create just 1 repository and put all the tables in it, in which case all users will check-out and check-in the whole database at once. There may be some tables that you do not put in any repository, but then nobody will be able to access them using sqlCVS.

History tracking

When creating the repository, we recommend you turn on 'history tracking'. This causes sqlCVS to create 2 other copies of every table, called [table]_pschist and [table]_pscmask. These copies has the same structure as the original table, but with all the constraints removed. Every time someone changes a row, the full contents of the row will be saved into the pschist table, and a record with bit flags added to _pscmask indicating which fields were modified. This is how sqlCVS can go back in time and undo changes, or restore the database to a prior state. Your original table will still have only the current values, just like it always did.

Creating dump files

Next you run sqlCVS with the dump command. This creates a [repository].sqlcvs file that contains a working copy of the repository (or database) for you to give to the clients. The clients use the same sqlCVS program (binary) as the server--the whole program is in 1 file shared by both server and client. The client uses the import command to import the .sqlcvs into their local MySQL database. This becomes their working copy, and has the same structure as the original database you started with. It doesn't matter if the .sqlcvs file they import is very old. It's only a starting point--as soon as they do a sqlCVS update or sync the local copy will be brought current.

sqlCVS server mode ("listen")

On the server you run sqlCVS with the listen command. That causes sqlCVS to open a tcp/ip port for an incoming connection from a client, and allows clients to connect to do checkins and updates.

Client notes

On the client the user will continue to use all the same software without changing anything--he just points the database connection to his local copy rather than the server's. Your web site, your accounting program, etc. all will use the working copies and will no longer connect directly to the masterdb. Since the working copy is essentially identical to the original masterdb all the software that uses the database should be unaffected and should work fine with this client copy. The only difference in the database is the addition of some special psc_ fields at the end of each table. However, the fields all have default values and you do not ever need to touch them. The only time this may cause a problem for your software is if it does an INSERT statement without specifying the field names, assuming a certain number of fields. In that case, the insert statement may fail because the table now contains more fields. The other potential problem is if your table has a 'timestamp' field. One of the special psc_ fields is a 'timestamp' so sqlCVS can keep track of what records were modified, but MySQL does not allow a table to have 2 timestamps and still work as expected.

Important notes

***IMPORTANT NOTE*** Some editors, like SQLyog, do not know how to properly handle timestamp fields. They reset the timestamp field every time the row changes. This is actually a bug in those editors since it defeats the whole purpose of a timestamp. PhpMyAdmin does not have this problem, and your software will not either since your software will be unaware of the timestamp field.

sqlCVS also added a psc_id field to each table. This is sqlCVS's internal ID for that row. It is permanent, and will never change. This is how sqlCVS is able to accurately know what row was modified. You can change all your fields, including the primary key, and sqlCVS will still update the correct row in the master database. This field defaults to NULL and is assigned a value only when you check-in the new row. That is how sqlCVS knows whether a row is new.

There is also a psc_user field. This contains the user id of the person who owns the record. The security settings can be changed, but by default only the owner of a record or an administrator can go back and change that record. The field defaults to NULL, and when the new rows are checked in, sqlCVS will set them to be the user id of whoever is doing the checkin--he becomes the "owner" of those records. This means that if you have 30 people using the database and adding records, only the person who does the checkin will later be the 'owner' of those rows. You may modify your application to set the psc_user manually, and sqlCVS will respect your values. However, once the row has been checked in, the sqlCVS on the server will not allow any local users to change this value. To change the owner of a record after checkin, you will need to update the record in the sqlCVS master database. Or you can turn off the security, allowing anyone to modify any records, or have only table-level security, and then it does not matter anymore. If you do keep the default row-level security and a user who is not an administrator modifies a row that does not belong to him or tries to modify a row or table that is marked as "frozen" or he does not have permission to modify, then when he does a sqlCVS checkin, the server will keep his modifications in a special place, and give him a batch #, together with the name of the user who owns those records. Then that user, or any administrator, can run sqlCVS 'approve batch' to have those changes put back into the master database.

If you have history tracking enabled in the sqlCVS master databsae, you can also checkout local copies of the database as of a given date, or with some modifications ignored. For example, you can see what the database looked like as of last month, or get a copy that omits all the modifications made by a certain user.

When you do a check-in, all changes, including new, deleted and modified rows, are committed in a "batch", and given a batch id. If the database engine you are using supports transactions (commits and rollbacks), then your checkins will be atomic. In other words, if you modified 100 rows, when you do a check-in, all 100 rows will be sent to the server in a batch. If there is a failure--maybe you entered some data in your local copy that violates a constraint--then none of the rows will be checked in. It's all or nothing. If the database engine does not support transactions, it's possible to get some rows checked-in, but not all. This will not break anything, and sqlCVS will checkin the rest of the changes in another batch, but usually atomic commits are preferred.

You can also see what modifications where made to the database by date, user, or batch. If you're an administrator, you can roll back the master database to it's state at a given time, or selectively remove some batches after the fact. There's also a command-line switch that forces strict verification of database integrity and will not allow rows that reference foreign keys that do not exist.

How to set up a LMCE sqlCVS Server

This howto describes the setup of a sqlCVS server hosting a LinuxMCE database. Your mileage may vary for other purposes.

Prerequisites

  • 1 Linux server
  • 1 LinuxMCE installation (or other means of getting a running sqlCVS binary)
  • sqlCVS binary from SVN (includes some fixes for MySQL 5)
  • .sqlcvs database files or a mysqldump for the pluto_main database
  • 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/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',md5('sUperSecreTpassword'),0,'hari@vt100.at',
'20071103193538','',0,1,NULL,NULL,NULL,0,'2007-11-03 18:41:22',NULL);

Make sure to set sqlCvsAdmin to 1 for people who need to commit stuff to schema.

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

Creating the history tables

To have a full featured repository with approvals and history we need the history tables. They are called like the table with the suffices "_pschist" and "_pschmask". We can create the Tables with sqlCVS. Select history-all from the server menu or use the commandline: 

sqlCVS history-all

Be patients, this will take some time.

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

Currently users are managed manually in the MasterUsers table.

NOTE: there is a new web interface at http://svn.charonmedia.org/svn/trunk/web/sqlcvsweb. It includes add user functionality. 

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 "== LinuxMCE sqlCVS User Management ==";
 require('include/config/config.inc.php');
 require('include/utils.inc.php');
 
@@ -36,7 +37,21 @@
        break;
 
        default:
-       echo "";
+       echo "=== Add User ===";
+               ?>
+               <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
+?>

Usage examples

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 <sqlCVS server host> -R <sqlCVS server port> -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 approve the checkin with "sqlCVS approve": 

bash-3.2# sqlCVS -h "127.0.0.1" -u "root" -p "" -D "pluto_main" -P "3306" -r "dce" -t "" -U "sqlcvsuser~sqlcvspass" approve
[...]
Database host:127.0.0.1 user:root pass: name:pluto_main port:3306
Users:sqlcvsuser~sqlcvspass
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#

Exporting sqlcvs files

sqlCVS -h "127.0.0.1" -u "root" -p "" -D "pluto_main" -P "3306" \
-r "constants,dce,designer,document,ir,local,media,security,telecom,website" -t "" -U "sqlcvsuser~sqlcvspass" dump

Using LinuxMCE as sqlCVS frontend

Submitting a new batch

Adding changes to the database

First we need a new database entry. In this example we siply add a dce device template (Advanced->Configuration->Device Templates, select "Add device template").

Fill in some stuff and save.

Viewing the sqlCVS diff

To show the differences between the repository and the local database we can use the "diff" feature of LinuxMCE (Advanced->sqlCVS->Diff). Fill in the sqlCVS hostname and your credentials. In the repository select "Check all" in the "dce" section. After submitting you can see your changes. Select the device template for checkin and commit.

View batches

Go to "Advanced->sqlCVS->View Batch" and select a repository.

Update the local database

Go to "Advanced->sqlCVS->Update and check the repositories you want to update. The "Next" button will start the update.

sqlCVS data modell

Detailed psc table description

What_do_the_psc_tables_do?

Batches

Header Table "psc_<repo>_bathdr"

As the suffix "_bathdr" suggests this table holds the headers for batches. This example is from the repository dce in the pluto_main database: 

+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| PK_psc_dce_bathdr | int(11)     | NO   | PRI | NULL    | auto_increment | 
| IPAddress         | varchar(16) | YES  |     | NULL    |                | 
| date              | datetime    | YES  |     | NULL    |                | 
| comments          | text        | YES  |     | NULL    |                | 
+-------------------+-------------+------+-----+---------+----------------+

Details Table "psc_<repo>_batdet"

This table holds more details for a batch: 

+--------------------------+-------------+------+-----+---------+----------------+
| Field                    | Type        | Null | Key | Default | Extra          |
+--------------------------+-------------+------+-----+---------+----------------+
| PK_psc_dce_batdet        | int(11)     | NO   | PRI | NULL    | auto_increment | 
| FK_psc_dce_bathdr        | int(11)     | NO   |     | 0       |                | # points to header
| Tablename                | varchar(60) | NO   |     |         |                | 
| New                      | int(11)     | NO   |     | 0       |                | 
| Deleted                  | int(11)     | NO   |     | 0       |                | 
| Modified                 | int(11)     | NO   |     | 0       |                | 
| FK_psc_dce_bathdr_orig   | int(11)     | NO   |     | 0       |                | 
| FK_psc_dce_bathdr_auth   | int(11)     | NO   |     | 0       |                | 
| FK_psc_dce_bathdr_unauth | int(11)     | NO   |     | 0       |                | 
+--------------------------+-------------+------+-----+---------+----------------+

History

History Table "<Tablename>_pschist"

sqlCVS maintains history tables. The schema is taken from the original table, but there is one modification: the primary key in the history table is the combination of the batch id (psc_batch) and the psc row id (psc_id). So sqlCVS can track modifications of the PK_, too. There is one special thing for history tables: if a batch is not approved (e.g. checked in with the password "nopass") its written to the history table and referenced by a negative batch id. After approval it gets moved to the main table. 
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| PK_Version   | int(11)     | YES  |     | NULL              |       | # Original PK
| VersionName  | varchar(22) | YES  |     | NULL              |       |
| BuildName    | varchar(13) | YES  |     | NULL              |       |
| Date         | date        | YES  |     | NULL              |       |
| Description  | varchar(30) | YES  |     | NULL              |       |
| Repository   | tinyint(3)  | YES  |     | NULL              |       |
| Comments     | longtext    | YES  |     | NULL              |       |
| NextSteps    | longtext    | YES  |     | NULL              |       |
| SvnRevision  | int(11)     | YES  |     | NULL              |       |
| SvnBranch    | varchar(25) | YES  |     | NULL              |       |
| psc_id       | int(11)     | NO   | PRI | 0                 |       | # NEW PRIMARY KEY
| psc_batch    | int(11)     | NO   | PRI | 0                 |       | # NEW PRIMARY KEY, points to batch header
| psc_user     | int(11)     | YES  |     | NULL              |       | # user 
| psc_frozen   | tinyint(1)  | YES  |     | NULL              |       |
| psc_mod      | timestamp   | YES  |     | CURRENT_TIMESTAMP |       | # last modification
| psc_restrict | int(11)     | YES  |     | NULL              |       |
| psc_toc      | tinyint(1)  | YES  |     | 0                 |       | # type of change
+--------------+-------------+------+-----+-------------------+-------+

History Mask Table "<Tablename>_pschmask"

This is a bit mask with a TRUE value for every field changed in the corresponding history table: 

+--------------+------------+------+-----+-------------------+-------+
| Field        | Type       | Null | Key | Default           | Extra |
+--------------+------------+------+-----+-------------------+-------+
| PK_Version   | tinyint(1) | YES  |     | NULL              |       |
| VersionName  | tinyint(1) | YES  |     | NULL              |       |
| BuildName    | tinyint(1) | YES  |     | NULL              |       |
| Date         | tinyint(1) | YES  |     | NULL              |       |
| Description  | tinyint(1) | YES  |     | NULL              |       |
| Repository   | tinyint(1) | YES  |     | NULL              |       |
| Comments     | tinyint(1) | YES  |     | NULL              |       |
| NextSteps    | tinyint(1) | YES  |     | NULL              |       |
| SvnRevision  | tinyint(1) | YES  |     | NULL              |       |
| SvnBranch    | tinyint(1) | YES  |     | NULL              |       |
| psc_id       | int(11)    | NO   | PRI | 0                 |       | # NEW PRIMARY KEY
| psc_batch    | int(11)    | NO   | PRI | 0                 |       | # NEW PRIMARY KEY
| psc_user     | tinyint(1) | YES  |     | NULL              |       |
| psc_frozen   | tinyint(1) | YES  |     | NULL              |       |
| psc_mod      | timestamp  | YES  |     | CURRENT_TIMESTAMP |       |
| psc_restrict | tinyint(1) | YES  |     | NULL              |       |
+--------------+------------+------+-----+-------------------+-------+

Special fields

There are some special fields added to every table in a repository: 

| psc_id       | int(11)     | YES  | UNI | NULL              |                | # unique sqlCVS ID for this Table
| psc_batch    | int(11)     | YES  |     | NULL              |                | # the last batch touching this row
| psc_user     | int(11)     | YES  |     | NULL              |                | # the user that commited the change
| psc_frozen   | tinyint(1)  | YES  |     | 0                 |                | # frozen rows cant be changed
| psc_mod      | timestamp   | YES  |     | CURRENT_TIMESTAMP |                | # last modification of the row
| psc_restrict | int(11)     | YES  |     | NULL              |                | # if != NULL do not update clients

Repository

Repository Settings Table "psc_<repo>_repset"

+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| PK_psc_dce_repset | int(11)     | NO   | PRI | NULL    | auto_increment | 
| Setting           | varchar(30) | NO   |     |         |                | 
| Value             | text        | YES  |     | NULL    |                | 
+-------------------+-------------+------+-----+---------+----------------+

For now it stores only the schema version: 

mysql> select * from psc_dce_repset;
+-------------------+---------+-------+
| PK_psc_dce_repset | Setting | Value |
+-------------------+---------+-------+
|                 1 | schema  | 44    | 
+-------------------+---------+-------+
1 row in set (0.02 sec)

Repository Tables Table "psc_<repo>_tables"

the "psc_<repo>_tables" references all tables in a repository: 

mysql> describe psc_dce_tables;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| PK_psc_dce_tables | int(11)      | NO   | PRI | NULL    | auto_increment |
| Tablename         | varchar(60)  | NO   |     |         |                |
| filter            | varchar(255) | YES  |     | NULL    |                | ~~~ This seems to be the filter for what gets exported, i.e. where 0 means, no records are exported.
| frozen            | tinyint(1)   | NO   |     | 0       |                | if true no changes to that table are accepted
| anonymous         | tinyint(1)   | NO   |     | 0       |                | if true anonymous~anonymous can check in to this table
| last_psc_id       | int(11)      | NO   |     | 0       |                | highest psc_id value in that table
| last_psc_batch    | int(11)      | NO   |     | 0       |                | highest batch_id affecting that table
+-------------------+--------------+------+-----+---------+----------------+

Schema Table "psc_<repo>_schema"

This table holds the schema changes for tables in a repository. Example: 

mysql> describe psc_dce_schema;
+-------------------+---------+------+-----+---------+----------------+
| Field             | Type    | Null | Key | Default | Extra          |
+-------------------+---------+------+-----+---------+----------------+
| PK_psc_dce_schema | int(11) | NO   | PRI | NULL    | auto_increment |
| Value             | text    | NO   |     |         |                |
+-------------------+---------+------+-----+---------+----------------+

Inside there are "ALTER TABLE" statements and so on to modify the schema: 

mysql> select * from psc_constants_schema;
+-------------------------+-------------------------------------------------------------------------------------------------------------------+
| PK_psc_constants_schema | Value                                                                                                             |
+-------------------------+-------------------------------------------------------------------------------------------------------------------+
|                       2 | alter table `Distro` ,change `InstallerURL` `InstallerURL` varchar (100)   NULL                                   |
|                       3 | alter table `Country` ,add column `UsePostalCode` tinyint (1)  DEFAULT '0' NOT NULL  after `Define`               |
|                       4 | alter table `Pipe` ,drop column `FK_Pipe_UsePath` ,add column `Define` varchar (10)   NULL  after `Description`   |
+-------------------------+-------------------------------------------------------------------------------------------------------------------+
= Tutorial for new repositories =
Retrieved from "http://wiki.linuxmce.org/index.php?title=SqlCVS&oldid=28778"