docs/PostgreSQL-Backend.txt

********************************************************************************
                The PostgreSQL Database backend for "current".
                           Development Version (Beta)
********************************************************************************
This software is distributed under the GPL v2, see file "../LICENSE".
Copyright 2002   Toby D. Reeves   <[email protected]>
********************************************************************************

These instruction are for a stock Redhat 7.2 installation.
I'm using the stock "postgresql-python-7.1.3-2.i386.rpm" which supports
the Python DB-SIG API.

Note that Redhat 7.1, 7.0, and 6.2 all come with some earlier version
of postgresql-python.  These DO NOT implement the DB-SIG API.
You MIGHT should be able to compile and install a newer version yourself.
I have compiled it but not used it.

Look at http://www.druid.net/pygresql/ for the informantion.
Here is the tarbar: ftp://ftp.druid.net/pub/distrib/PyGreSQL-3.2.tgz

To build you own rpm that might work do this:
tar xvfz PyGreSQL-3.2.tgz
cd PyGreSQL

There is a typo in setup.py. Change [version = "3.1"] to [version = "3.2"]
vi setup.py

./setup.py bdist_rpm

The rpm is in "PyGreSQL/dist/PyGreSQL-3.2-1.i386.rpm".  The name is different than
on Redhat 7.2.

Recently, developement of PyGreQSL moved to the PostgreSQL tree.

--------------------------------------------------------------------------------
This is the sequence to take a stock Red Hat 7.2 machine and get it to run.
You may need some slightly different commands if your "special".

If you played with the first tarball version of the hack, you need to clean up:
> su
> su postgres
> dropdb currentdb
> dropuser current

Now, we can begin:

Create the current user in PostgreSQL with these commands:
> su
> su postgres
> createuser -A -d -P current

Drop from postgres to root:
> exit

Initialize postgresql and make it start on boot:
> /sbin/checkconfig --add postgresql
> /etc/init.d/postgresql start

You will need to create the file '/var/lib/pgsql/data/postmaster.opts.default' that contains "-i"
to enable Postgresql to listen to TCP/IP connections. See man page on 'pg_ctl'.
Now restart postgresql.

> /etc/init.d/postgresql restart

Postgresql has many authentication methods. See /var/lib/pgsql/data/pg_hba.conf.
The default setup in Postgresql is VERY liberal for connections from the localhost,
and does not enforce passwords.  Thus, the password you entered is NOT USED if you 
do nothing to the pg_hba.conf file.

For now, CurrentDB.py TRIES to use simple "password" authentication.

Drop from root.
> exit

That should do it for the PostgreSQL server setup.
--------------------------------------------------------------------------------

Edit the provided /etc/current/current.conf for your site.
In particular, notice new options 'cache_dir', 'db_name', 'db_host', 'db_user', 
and 'db_password'. Also note that the SQL backend recurses all rpm directories specified,
looking for files ending with ".rpm".  All other files are ignored.

The current.conf item "src_dirs" is not used by the PostgeSQL backend.
The type of an rpm is identified by the contents of the rpm.

The "db_dir" item is required but not used.

Initial the current database.
> cdbadmin create_database

Initialized and load a channel specified by a "label" entry in your current.conf.
> cdbadmin init_channel redhat-i386-7.2

If it finds an rpm that is not compatible with the canonical arch of the channel,
it is added to the database, but not inserted into the channel.

The database population can be drastically sped up (300% in tests) by dropping some
expensive indexes before bulk database changes.  Afterward, recreate indexes.

> cdbadmin drop_indexes
> cdbadmin init_channel redhat-i386-7.2
> cdbadmin init_channel NEXT_CHANNEL
> cdbadmin init_channel NEXT_CHANNEL
> cdbadmin create_indexes

You can also initialize all channels specified by the 'valid_channels' config entry.
> cdbadmin init_channels

If it detects an error, such as a directory that does not exists, fix the config file
and init_channel again.  You should not have to drop what was alread loaded into the
database.  Unless you loaded data that was incorrect.

See what's in a channel.
> cdbadmin list_channel redhat-i386-7.2

See some interesting statistics on the database.  Some are only interesting to the developer.
> cdbadmin stats

Start your current server as described elsewhere.

You probably can dink with database while the server is running, but that has not been tested
throughly.  It will be possible in a future version.

If you add files/directories to the rpm_dirs for a channel, you can just "init_channel" again
If channel already exists, it ONLY rescans files/directories and then recomputes channel.
It does not (yet) update 'name', 'label', 'arch', 'os_release', 'description', or 'parent_channel'.
> cdbadmin init_channel redhat-i386-7.2

Compute a channel.  This is also done by init_channel, so you really do not need to do this.
> cdbadmin compute_channel redhat-i386-7.2

Drop a channel. This does not remove the rpms from the database.  They might be contained in
some other channel.
> cdbadmin del_channel redhat-i386-7.2


Permanently purge rpms from database that do not appear in any channel.  When we get 'up2date -p'
supported, you might not want to do this.  There will be some "light weight" version that deletes
most but not all information gleemed from the rpms.
> cdbadmin purge_unused_rpms

Decide you don't want to keep current with SQL backend.
> cdbadmin drop_database
> su
> su postgres
> dropuser current


--------------------------------------------------------------------------------
                                    THINGS TO DO
It is also possible to remove rpms from a channel.  I have not yet exposed that
function to the command line.

Somebody may see how to get database lookups to go faster.  I've hashed and indexed
for lookups that are either slow or are very common. That makes 300% speed up 
for a thrash_channel test of Red Hat 7.2 Plus Errata.

Put some sanity checks in as need to verify database to cache and rpm dirs.

Put an option in that disables "file" table use.  Most sites will NEVER need this.
It also happens to be quite expensive in time and resources.

Also, "smart" support for multiple canonical architectures (i386, ia64, alpha, ...)
is not yet done.  Have some more thinking to do there.

Lots of uncommon things are not quite right. Or I don't know whats right out of 
several possible choices.  These are flagged in the code.

Write a SQL Auth module.  That enables lots of other stuff.  Then current will not
have to (and probably should not) run in anonymous mode.  That will allow
up2date clients to send what is installed on the client plus a summary of what
hardware is on the client.  With that infomation, you can generate reports on which
of your workstations have what.  That's what I want.  We have workstations and notebooks
which travel and are off the network a lot.  Our delta rpm's to a stock Red Hat
installation changes quite often. I want to keep up with what client has what
and when did it get it.

Someone could do a web interface similiar to RHN, and all the notifications, ect.
That could be useful if you serve a large campus with several administrators.
But it is not that useful to me.

--------------------------------------------------------------------------------
Misc notes for nobody but me:

How I run server for debugging:
./current -c ./current_sql.conf -l - -n -v -v -v -v

To make a patch:
diff -Np1 current-0.9.4/ current-dev > current-0.9.4-sql.patch

To get postgresql prompt:
psql currentdb current
--------------------------------------------------------------------------------