Learning MySQL / PostgreSQL and other stuff

Solaris 11 Package Updates

2011-12-06T08:59:00.001-08:00

Using a Solaris 11 Repository
Solaris 11 has moved to a package management system that is similar to yum, in that is pulls from a network and manages dependencies.
The command used is pkg - enter pkg --help for a full list of options.

root@solaris:~# pkg publisher
PUBLISHER                             TYPE     STATUS   URI
solaris                               origin   online   http://pkg.oracle.com/solaris/release/
root@solaris:~# pkgrepo info -s http://pkg.oracle.com/solaris/release/
PUBLISHER PACKAGES STATUS           UPDATED
solaris   4292     online           2011-11-09T15:42:00.183118Z
root@solaris:~#
root@solaris:~# pkg update
No updates available for this image.
root@solaris:~#

Before you install a package you can dry-run it:

root@solaris:~# pkg install -nv gcc-3
           Packages to install:         2
     Estimated space available:   9.67 GB
Estimated space to be consumed: 219.43 MB
       Create boot environment:        No
Create backup boot environment:        No
            Services to change:         1
          Rebuild boot archive:        No

Changed packages:
solaris
developer/gcc-3
    None -> 3.4.3,5.11-0.175.0.0.0.2.537:20111019T093736Z
developer/gnu-binutils
    None -> 2.19,5.11-0.175.0.0.0.2.537:20111019T095434Z
Services:
restart_fmri:
    svc:/application/texinfo-update:default
root@solaris:~#

The you go ahead and install the package:

root@solaris:~# pkg install -nv gcc-3
           Packages to install:         2
     Estimated space available:   9.67 GB
Estimated space to be consumed: 219.43 MB
       Create boot environment:        No
Create backup boot environment:        No
            Services to change:         1
          Rebuild boot archive:        No

Changed packages:
solaris
developer/gcc-3
    None -> 3.4.3,5.11-0.175.0.0.0.2.537:20111019T093736Z
developer/gnu-binutils
    None -> 2.19,5.11-0.175.0.0.0.2.537:20111019T095434Z
Services:
restart_fmri:
    svc:/application/texinfo-update:default
root@solaris:~#

To list the packages that you have installed:

timj@solaris11vm2:~$ pkg list | head
NAME (PUBLISHER)                                  VERSION                    IFO
SUNWcs                                            0.5.11-0.170               i-r
archiver/gnu-tar                                  1.26-0.175.0.0.0.2.537     i--
audio/audio-utilities                             0.5.11-0.175.0.0.0.2.1     i--
codec/flac                                        1.2.1-0.175.0.0.0.0.0      i--
codec/libtheora                                   1.1.1-0.175.0.0.0.0.0      i--
codec/ogg-vorbis                                  2.30.0-0.175.0.0.0.0.0     i--
codec/speex                                       1.2-0.175.0.0.0.0.0        i--
communication/im/pidgin                           2.10.0-0.175.0.0.0.0.0     i--
compress/bzip2                                    1.0.6-0.175.0.0.0.2.537    i--
timj@solaris11vm2:~$

To list the details of a package:

timj@solaris11vm2:~$ pkg info xcalc
          Name: x11/xcalc
       Summary: xcalc - scientific calculator for X
   Description: xcalc is a scientific calculator desktop accessory that can
                emulate a TI-30 or an HP-10C.
      Category: Applications/Accessories
         State: Installed
     Publisher: solaris
       Version: 1.0.4.1
Build Release: 5.11
        Branch: 0.175.0.0.0.0.1215
Packaging Date: September 27, 2011 12:58:35 PM
          Size: 104.32 kB
          FMRI: pkg://solaris/x11/xcalc@1.0.4.1,5.11-0.175.0.0.0.0.1215:20110927T125835Z
timj@solaris11vm2:~$

Want to know where a file comes from?:

timj@solaris11vm1:~$ pkg search libresolv.so
INDEX      ACTION VALUE                        PACKAGE
basename   link   lib/sparcv9/libresolv.so     pkg:/system/library@0.5.11-0.175.0.0.0.2.1
basename   link   usr/lib/sparcv9/libresolv.so pkg:/system/library@0.5.11-0.175.0.0.0.2.1
basename   link   lib/libresolv.so             pkg:/system/library@0.5.11-0.175.0.0.0.2.1
basename   link   usr/lib/amd64/libresolv.so   pkg:/system/library@0.5.11-0.175.0.0.0.2.1
basename   link   lib/amd64/libresolv.so       pkg:/system/library@0.5.11-0.175.0.0.0.2.1
basename   link   usr/lib/libresolv.so         pkg:/system/library@0.5.11-0.175.0.0.0.2.1
timj@solaris11vm1:~$

To update all packages:

root@solaris11vm1:~# pkg update
No updates available for this image.
root@solaris11vm1:~#

What to do when things go wrong:

root@solaris11vm1:~# pkg verify -v   gcc-3
PACKAGE                                                                 STATUS
pkg://solaris/developer/gcc-3                                               OK
root@solaris11vm1:~# mkfile 1024 /usr/sfw/bin/gcc
root@solaris11vm1:~# pkg verify -v   gcc-3
PACKAGE                                                                 STATUS
pkg://solaris/developer/gcc-3                                            ERROR
        file: usr/sfw/bin/i386-pc-solaris2.11-gcc-3.4.3
                Mode: 01600 should be 0555
                Unexpected Exception: Request error: class file/memory mismatch
root@solaris11vm1:~# pkg fix   gcc-3
Verifying: pkg://solaris/developer/gcc-3                        ERROR
        file: usr/sfw/bin/i386-pc-solaris2.11-gcc-3.4.3
                Mode: 01600 should be 0555
                Unexpected Exception: Request error: class file/memory mismatch
Created ZFS snapshot: 2011-12-07-15:27:53
Repairing: pkg://solaris/developer/gcc-3


DOWNLOAD                                  PKGS       FILES    XFER (MB)
Completed                                  1/1         1/1      0.1/0.1

PHASE                                        ACTIONS
Update Phase                                     3/3

PHASE                                          ITEMS
Image State Update Phase                         2/2
root@solaris11vm1:~# pkg verify -v   gcc-3
PACKAGE                                                                 STATUS
pkg://solaris/developer/gcc-3                                               OK
root@solaris11vm1:~#

Mail merge in PHP

2010-12-16T14:04:00.000-08:00

Just for a laugh

The question was, how do you do a mail merge in PHP. OK, it was a question that no-one was asking, but I never let the deter me before.

Input Data
$ cat inputdata
Tim,Jinkerson,
Ben,De Mora,
Jes,Ferrier,

tjinkers@TJINKERS-GB ~/merge
$ cat template
FAO: .f1 .f2
Dear .f1
Now is the winter of our discontent
made glorious summer by this sun of York
Many thanks
Tim

tjinkers@TJINKERS-GB ~/merge
$

The Program

$ cat mymerge.php
$filename = "template";
$mergef = "inputdata";
$fdm = fopen($mergef,"r");
$fnum=1;
$mdata = fgets($fdm, 512);
while (!feof($fdm))
{
list($f1, $f2, $f3, $f4, $f5) = explode(",", $mdata);
$fd = fopen($filename,"r");
$contents = fread ($fd, filesize($filename));
fclose ($fd);
$ofilename = "file.".$f1.".".$f2.".".$fnum;
$contents=str_replace(".f1", $f1, $contents);
$contents=str_replace(".f2", $f2, $contents);
$contents=str_replace(".f3", $f3, $contents);
$contents=str_replace(".f4", $f4, $contents);
$contents=str_replace(".f5", $f5, $contents);
$fdo = fopen($ofilename,"w");
fwrite ($fdo,$contents);
fclose ($fdo);
$fnum++;
$mdata = fgets($fdm, 512);
}
fclose ($mergef);
?>

tjinkers@TJINKERS-GB ~/merge
$

For each line of the input data, the program reads the template file as a single string, and then does a replace on the markers for the input date. It writes the results out to a file who's name is based on the input data.

$ php mymerge.php

$ ls
file.Ben.De Mora.2 file.Jes.Ferrier.3 file.Tim.Jinkerson.1 inputdata mymerge.php template

tjinkers@TJINKERS-GB ~/merge
$ cat file.Tim.Jinkerson.1
FAO: Tim Jinkerson
Dear Tim
Now is the winter of our discontent
made glorious summer by this sun of York
Many thanks
Tim

tjinkers@TJINKERS-GB ~/merge
$

Creating a Solaris 10 Sparse Zone

2010-10-25T08:04:00.000-07:00

First, create a directory for your zone to be built in.

mkdir -p /zones/myzone

Create a zone config file.

create -b
set zonepath=/zones/myzone
set autoboot=true
add inherit-pkg-dir
set dir=/lib
end
add inherit-pkg-dir
set dir=/platform
end
add inherit-pkg-dir
set dir=/sbin
end
add inherit-pkg-dir
set dir=/usr
end
add net
set address=10.0.2.17/24
set physical=hme0
end

zonecfg -z myzone -f sparsezone.cfg
zoneadm -z myzone install

zoneadm list -v

zlogin -C myzone
zlogin myzone shutdown -y -i0 -g0

Creating a second database from the linux command line

2010-10-21T04:39:00.000-07:00

The objective here was to make a copy of an existing database and bring it up under a new instance. I could in theory have used a different version of the database but at the time I only had one available to me.

Create a new database:

su - root
mkdir /var/lib/postgresql/testinstance
chown postgres:postgres /var/lib/postgresql/testinstance
su - postgres
initdb -D /var/lib/postgresql/testinstance

Starting the Instance

pg_ctl start -D /var/lib/postgresql/testinstance -l /var/tmp/testinstance.log -o "-p 5434"

Check the status:

pg_ctl status -D /var/lib/postgresql/testinstance

Stopping the Instance

pg_ctl stop -D /var/lib/postgresql/testinstance

Create user on new database:

create role timj login password 'xxxxxx';
create database cddb;
alter role timj superuser;
alter role timj nosuperuser;
grant all on database cddb to timj;

Connecting to the database:

psql -d postgres -h localhost -p 5434 -U postgres

Dump the old database:

/usr/bin/pg_dump -h localhost -p 5432 -U timj -F p -v -f "/tmp/mybackup.pgsql" cddb

Restore to new database:

psql -h localhost -p 5434 -U timj -d cddb < /tmp/mybackup.pgsql

Dumping database in custom mode:

/usr/bin/pg_dump -h localhost -p 5432 -U timj -F c -v -f "/tmp/mybackup.pgsqlc" cddb

Use pg_restore if you have a non-ascii backup:

create role timj login password 'xxxxxx';
create database cddb;
grant all on database cddb to timj;
pg_restore -h localhost -p 5434 -U timj -d cddb /tmp/mybackup.pgsqlc

Point-In-Time Recover in Postgresql

2009-12-22T08:54:00.000-08:00

The way you do point in time recovery in Postgresql is to restore a known backup, and then reapply WAL logs until you reach the time you wish to stop at. The database must be in Archive Log Mode (just like Oracle), and you must have all the logs to complete the recovery.

Putting the database in Archive Log Mode

Step 1:
Back up your database
pg_dump -h localhost -p 5432 -U timj -F c -v -f "D:\pgbackup\cddb_21Dec2009_2.backup" cddb

Now let's put the database in archive log mode:

Edit postgresql.conf:

fsync = on
synchronous_commit = on
wal_sync_method = fsync
full_page_writes = on
archive_mode = on
archive_command = 'copy "%p" "D:\\pglogs\\%f"'
archive_timeout = 3600 # switch logs every hour

And remember to bounce the database.

Taking a base backup

I now have to take a base backup of my system.
To do this I logged in as postgres and issued the following:
SELECT pg_start_backup('label2');

I backed the database up using a normal file system backup with the datbase up, and then issued:
SELECT pg_stop_backup();

Next - put in a transaction that we will want recovering:

insert into artist (name) values ('Vicky Beeching');

As this is my first time doing this, I forced a log switch to help with the recovery!

SELECT pg_switch_xlog();

The Disaster!

After my backup, I created a record in the artist table that should be there post recovery. The table has a time trigger on it, so I knew exactky the time I wanted to recover to. I then deleted Nick Drake's entry from the artist table - could there be a bigger mistake than that!

The Recovery

Step 1: Take the database down. This may seem like the obvious first step, but there is a gotchya. The default checkpoint time-out in Postgresql is 5 minutes. This means that if you are relying on recoving your database up to the last good transaction, and if that transaction was less than 5 minutes before hand, it may not have been written to a log file in the pg_xlog directory. Therefore, it may be worth while logging on as an admin user and issuing the command checkpoint; before you shut down the instance.

Step 2: Take a backup!
With the database down, copy off the data directory and any other tablespaces you have in use.

Step 3: Delete your database!
We are going to rebuild the database using the good copy we created in our initial backup, the archived WAL logs and the WAL logs from pg_xlog, so we need to get rid of everything in the existing database.

Step 4: Copy across your backed up database
Copy your good backup into the live data directory.

Step 5: Delete everything from pg_xlog
The files in the restored pg_xlog directory will be from before the backup, and so of no use to you.

Step 6: Copy across the content of your pg_xlog directory
From the backup you took after you discovered you needed to recover, copy the files to your live pg_xlog directory.

Step 7: Create a recovery.conf file
Something like this:

restore_command = 'copy "D:\\pglogs\\%f" "%p"' # Windows
recovery_target_time ('2009-12-22 15:34:56')

The copy command should be in the syntax of whatever OS you are using, very much like the entry in postgresql.conf

Step 8: Restart the database
You might want to restrict who can access while you are checking the recovery.
At the end of the recovery recovery.conf is renamed recovery.done

Step 9: Take a fresh baseline baclup!
It is really good practice to do this before your users start posting transactions, if at all possible.

Solaris 10 CPU Usage

2009-11-19T04:22:00.001-08:00

psrinfo shows how many CPUs you have:

bash-3.00# psrinfo
0       on-line   since 08/24/2009 13:40:13
1       on-line   since 08/24/2009 13:40:18
2       on-line   since 08/24/2009 13:40:18
3       on-line   since 08/24/2009 13:40:18
4       on-line   since 08/24/2009 13:40:18
5       on-line   since 08/24/2009 13:40:18
6       on-line   since 08/24/2009 13:40:18
7       on-line   since 08/24/2009 13:40:18
8       on-line   since 08/24/2009 13:40:18

adding the –v option gives you details about those CPUs

bash-3.00# psrinfo -v | head
Status of virtual processor 0 as of: 11/19/2009 03:50:06
on-line since 08/24/2009 13:40:13.
The sparcv9 processor operates at 1165 MHz,
and has a sparcv9 floating point processor.
Status of virtual processor 1 as of: 11/19/2009 03:50:06
on-line since 08/24/2009 13:40:18.
The sparcv9 processor operates at 1165 MHz,
and has a sparcv9 floating point processor.
Status of virtual processor 2 as of: 11/19/2009 03:50:06
on-line since 08/24/2009 13:40:18.
bash-3.00#

“prstat -s cpu -n 10” is a bit like top, but it tells you what cpu you are running against

bash-3.00# prstat -s cpu -n 10
   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
  3138 900233    895M  588M cpu16    1    0 141:24:49 1.7% mysqld/26
  1005 root       19M   18M sleep   59    0  38:34:21 0.0% esd/1
   944 daemon   3192K 2208K sleep   29    0   0:00:00 0.0% statd/1
   542 root     1768K  256K sleep   29    0   0:00:00 0.0% efdaemon/1
  8633 root     1712K 1168K sleep    1    0   0:00:16 0.0% utmpd/1
   165 root     7568K 4920K sleep   29    0   0:00:00 0.0% devfsadm/8
   940 root     2992K 2136K sleep   29    0   0:00:00 0.0% keyserv/3
   147 root     7464K 4472K sleep   29    0   0:00:00 0.0% syseventd/15
     9 root       11M   11M sleep   29    0   1:00:07 0.0% svc.configd/12
     7 root       32M   31M sleep   29    0   0:04:45 0.0% svc.startd/13
Total: 129 processes, 541 lwps, load averages: 1.12, 1.14, 1.18
bash-3.00#

You can the use the mpstat command to see how each processor is doing:

bash-3.00# mpstat          
CPU minf mjf xcal  intr ithr  csw icsw migr smtx  srw syscl  usr sys  wt idl
  0   80   0  566   264   53   50    0    2    9    0   203    2   2   0  96
  1    1   0  193    15    2   25    0    0    1    0    57    0   0   0  99
  2    0   0   48     8    0   15    0    0    0    0    12    0   0   0 100
  3    0   0    8     3    0    5    0    0    0    0     4    0   0   0 100
  4   15   0   72     8    0   15    0    0    2    0    33    0   0   0  99
  5    0   0    4     3    0    4    0    0    0    0     1    0   0   0 100
  6    0   0    5     7    1   10    0    0    0    0     0    0   0   0 100
  7    0   0    5     6    0    9    0    0    0    0     0    0   0   0 100
  8   64   0  369    22    0   47    0    2    7    0   185    3   1   0  96
  9    3   0   73     3    0    6    0    0    1    0    22    0   0   0 100

“mpstat –p 10 10” will refresh the screen every 10 seconds

Perl: Chose your Database Vendor on the command line!

2009-10-16T07:37:00.000-07:00

I have created two identical databases. Well, when I say 'identical', one is MySQL and the other is PostgreSQL, but other than that they are pretty similar! They have the same schema and pretty much the same data. It occurred to me that the Perl DBI package hides the database details away from you so successfully that it would be possible to write a program that allowed you to specify the type of database you are accessing on the command line!

Firstly, my new version of the command line routine:

sub dbinfo() {
        use Term::ReadKey;
        my $helpinstructions = "Valid parameters are\n-h {host}\n" .
                               "-d {database}\n" . 
                               "-u {user}\n" .
                               "-p [password]\n" .
                               "-dms [mysql/pgsql]\n";
        my %mydbinfo = (        db => "",
                                host => "",
                                user => "",
                                passwd => "",
                                dms => ""
        );


        die $helpinstructions unless ($#ARGV > 5);

        for (my $i=0; $i <= $#ARGV; $i++) {
                $ARGV[$i]= "-?" if ($ARGV[$i] ne '-h' and
                                    $ARGV[$i] ne '-d' and
                                    $ARGV[$i] ne '-u' and
                                    $ARGV[$i] ne '-dms' and
                                    $ARGV[$i] ne '-p' );
                die $helpinstructions if ( $ARGV[$i] eq "-?" ) ;
                $mydbinfo{db} = $ARGV[++$i] if ( $ARGV[$i] eq "-d" ) ;
                $mydbinfo{host} = $ARGV[++$i] if ( $ARGV[$i] eq "-h" ) ;
                $mydbinfo{user} = $ARGV[++$i] if ( $ARGV[$i] eq "-u" ) ;
                $mydbinfo{dms} = $ARGV[++$i] if ( $ARGV[$i] eq "-dms" ) ;
                if ( $ARGV[$i] eq "-p" ) {
                        if ( $i ne $#ARGV) {
                                $mydbinfo{passwd} = $ARGV[++$i];
                        }
                        else {
                                print "\nPlease Enter passwd: ";
                                ReadMode 'noecho';
                                chomp($mydbinfo{passwd} = ReadLine);
                                ReadMode 'normal';
                                print "\n\n";
                        }
                }
        
        }
        die $helpinstructions unless ( $mydbinfo{dms} eq 'mysql' or
                                       $mydbinfo{dms} eq 'pgsql');
        $mydbinfo{dms} = 'PgPP' if ($mydbinfo{dms} eq 'pgsql');
        return %mydbinfo;
}
return true;

And then the program that calls it:

#!/usr/bin/perl
use strict;
use DBI();

require "D:\\Documents and Settings\\timj\\perl\\choosedbcommanddbsettings.pl" or
die "Can't Open DB Settings File";


my %mydbinfo = dbinfo();
my $dbconnect = "DBI:" . $mydbinfo{dms} . ":database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1);

and here's the output:

D:\Documents and Settings\timj\perl>choosedbreport.pl -dms mysql -h localhost -u timj -d cddb -p

Please Enter passwd:

Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>choosedbreport.pl -dms pgsql -h localhost -u timj -d cddb -p

Please Enter passwd:

Name            Title
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Phatfish        Guaranteed
Phatfish        15
Portland        These Broken Hands

D:\Documents and Settings\timj\perl>

Maybe not very useful, but it's a good demonstration of the power of DBI();

Perl: DB Connection details on the command line: Part 2

2009-10-14T10:06:00.000-07:00

The previous post showed a simple routine for extracting out the database connection variables from the command line. It worked, but it had two flaws:

It doesn't validate your parameters
It requires you to put your password on the command line, visible to a PS command in Unix

The code below seeks to address both these issues. If you enter a paramter that I don't understand, it kicks you out, and it you end your input with a -p, it accepts the password without echoing it back to the screen, just like you hoped it would!

sub dbinfo() {
 use Term::ReadKey;
 my $helpinstructions = "Valid parameters are\n-h {host}\n" .
                        "-d {database}\n" . 
          "-u {user}\n" .
          "-p [password]\n";
 my %mydbinfo = ( db => "",
    host => "",
    user => "",
    passwd => ""
 );


 die $helpinstructions unless ($#ARGV > 5);

 for (my $i=0; $i <= $#ARGV; $i++) {
  $ARGV[$i]= "-?" if  ($ARGV[$i] ne '-h' and
     $ARGV[$i] ne '-d' and
     $ARGV[$i] ne '-u' and
     $ARGV[$i] ne '-p' );
  die $helpinstructions if ( $ARGV[$i] eq "-?" ) ;
  $mydbinfo{db} = $ARGV[++$i] if ( $ARGV[$i] eq "-d" ) ;
  $mydbinfo{host} = $ARGV[++$i] if ( $ARGV[$i] eq "-h" ) ;
  $mydbinfo{user} = $ARGV[++$i] if ( $ARGV[$i] eq "-u" ) ;
  if ( $ARGV[$i] eq "-p" ) {
   if ( $i ne $#ARGV) {
    $mydbinfo{passwd} = $ARGV[++$i];
   }
   else {
    print "\nPlease Enter passwd: ";
    ReadMode 'noecho';
    chomp($mydbinfo{passwd} = ReadLine);
    ReadMode 'normal';
    print "\n\n";
   }
  }
 
 }
 return %mydbinfo;
}
return true;

And the output:

D:\Documents and Settings\timj\perl>testcommandline.pl -h localhost -d cddb -u timj -p

Please Enter passwd:

Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>

The great thing is that you can directly replace your previous version of the dbsettings routine with this one, and there is no need to alter your existing scripts!

Perl: DB Connection details on the command line

2009-10-12T09:23:00.000-07:00

What if you want to connect to a database, passing your parameters in on a command line? Here's a handy routine to do that:

First the included code

sub dbinfo() {
 my %mydbinfo = ( db => "",
    host => "",
    user => "",
    passwd => ""
 );


 die "Not Enough Parameters" unless ($#ARGV > 5);

 for (my $i=0; $i <= $#ARGV; $i++) {
  $mydbinfo{db} = $ARGV[++$i] if ( $ARGV[$i] eq "-d" ) ;
  $mydbinfo{host} = $ARGV[++$i] if ( $ARGV[$i] eq "-h" ) ;
  $mydbinfo{user} = $ARGV[++$i] if ( $ARGV[$i] eq "-u" ) ;
  $mydbinfo{passwd} = $ARGV[++$i] if ( $ARGV[$i] eq "-p" ) ;
 
 }
 return %mydbinfo;
}
return true;

Next the code that calls this routine

#!/usr/bin/perl
use strict;
use DBI();

require "D:\\Documents and Settings\\timj\\perl\\commanddbsettings.pl" or
die "Can't Open DB Settings File";


my %mydbinfo = dbinfo();
my $dbconnect = "DBI:mysql:database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1)

and here it is in action

D:\Documents and Settings\timj\perl>testcommandline.pl -d cddb -h localhost -u myuser -p xxx
Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>

It's not perfect, it doesn't do a lot of validation, and it doesn't allow you to enter the password with "echo off", but it's a good starting point.

MySQL: Some Weirdness Going On

2009-10-10T01:22:00.000-07:00

When I created the cdtable to show off Constraints, I had a little trouble. What everything I read in the manuals told me about defining them didn't work. The issue centred around the fields for which you were defining a foreign key constraint. The book told me to define a KEY for each field, but I found I had to define an INDEX.

Compare:

CREATE TABLE cddb.cdtable (
id INT NOT NULL AUTO_INCREMENT,
pubid INT NOT NULL,
artid INT NOT NULL,
title varchar(50) NOT NULL,
yearpublished INT,
PRIMARY KEY (`id`),
KEY `artid` (`artid`),
KEY `pubid` (`pubid`),
CONSTRAINT `cdtable_ibfk_1` FOREIGN KEY (`artid`) REFERENCES `artist` (`id`) ON DELETE CASCADE,
CONSTRAINT `cdtable_ibfk_2` FOREIGN KEY (`pubid`) REFERENCES `publisher` (`id`) ON DELETE CASCADE
) ENGINE=INNODB;

With

CREATE TABLE cddb.cdtable (
id INT NOT NULL AUTO_INCREMENT,
pubid INT NOT NULL,
artid INT NOT NULL,
title varchar(50) NOT NULL,
yearpublished INT,
PRIMARY KEY (id),
INDEX (artid),
FOREIGN KEY (artid)
REFERENCES artist(id) ON DELETE CASCADE,
INDEX (pubid),
FOREIGN KEY (pubid)
REFERENCES publisher(id) ON DELETE CASCADE
) ENGINE=INNODB;

The first is by the book, the second actually work!

Well, this morning I decided to back my database up and transfer it over to my study PC. I used the standard GUI backup tool, and 'lo and behold', the backup script it generated had the same issue:

--
-- Definition of table `cdtable`
--

DROP TABLE IF EXISTS `cdtable`;
CREATE TABLE `cdtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pubid` int(11) NOT NULL,
  `artid` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `yearpublished` int(11) DEFAULT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `artid` (`artid`),
  KEY `pubid` (`pubid`),
  CONSTRAINT `cdtable_ibfk_1` FOREIGN KEY (`artid`) REFERENCES `artist` (`id`) ON DELETE CASCADE,
  CONSTRAINT `cdtable_ibfk_2` FOREIGN KEY (`pubid`) REFERENCES `publisher` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

To allow the backup to restore I had to modify the code to reflect what I had originally written! Good job I'd blogged the code!

Perl: DB Connection details in a seperate file

2009-10-09T05:09:00.000-07:00

And finally, moving the connection details into a separate file that you can call from any program.
dbsettings.pl

sub dbinfo() {
 my %mydbinfo = ( db => "cddb",
    host => "localhost",
    user => "myuser",
    passwd => "xxx"
 );
 return %mydbinfo;
}
return true;

testrequire.pl

#!/usr/bin/perl
use strict;
use DBI();

require "D:\\Documents and Settings\\timj\\perl\\dbsettings.pl" or
die "Can't Open DB Settings File";


my %mydbinfo = dbinfo();
my $dbconnect = "DBI:mysql:database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1);

Perl: Connection Details in a Subroutine

2009-10-08T01:06:00.000-07:00

This example moves the connection information out of the main flow of the program and in to a subroutine. Notice that I have declared the hash %mydbinfo twice. This is because in both cases they are declared as lexically scoped (using 'my'). This means that the %mydbinfo that we see in the subroutine is a totally sepoerate variable from the %mydbinfo in the main body of the code. If like me you earned your living writing COBOL in the 80's this may seem strange - why would you want to do this. The answer is taht it makes it very clear what the scope of the variable is and who can see it. You can define global variables, but it's considered good practice to limit the scope of variables as tightly as possible whilst still achieving your goals.

#!/usr/bin/perl
use strict;
use DBI();

sub dbinfo() {
 my %mydbinfo = ( db => "cddb",
    host => "localhost",
    user => "myuser",
    passwd => "xxx"
 );
 return %mydbinfo;
}
  
my %mydbinfo = dbinfo();
my $dbconnect = "DBI:mysql:database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1)

Perl: Storing your DB Connect in a hash

2009-10-07T22:44:00.000-07:00

This is a half-way house script, that uses a hash to store the database information for the connect.


#!/usr/bin/perl
use strict;
use DBI();

my %mydbinfo = ( db => "cddb",
   host => "localhost",
   user => "myuser",
   passwd => "xxx"
 );
  
my $dbconnect = "DBI:mysql:database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1);

This has the advantage of putting all the details neatly at the start of the code, but still has the full DB connect details hard coded, which is obviously something to avoid in most circumstances.

Reading from a file

2009-07-25T01:46:00.000-07:00

I have started playing with a new database, that will allow the costing of simple recipes. Here's the schema (so far):



DROP TABLE IF EXISTS recipe.ingredient;
DROP TABLE IF EXISTS recipe.item;
DROP TABLE IF EXISTS recipe.unit;

CREATE TABLE recipe.unit (
 id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 PRIMARY KEY (id)
) TYPE = INNODB;

CREATE TABLE recipe.item (
 code VARCHAR(10) NOT NULL,
 name VARCHAR(50) NOT NULL,
 unitid INT NOT NULL,
 cost DECIMAL(10,2) NOT NULL,
 PRIMARY KEY (code),
 FOREIGN KEY (unitid) REFERENCES unit(id)
) TYPE = INNODB;

CREATE TABLE recipe.ingredient (
 recipe_code VARCHAR(10) NOT NULL,
 ingred_code VARCHAR(10) NOT NULL,
 quantity DECIMAL(10,2) NOT NULL,
 FOREIGN KEY (recipe_code) REFERENCES item (code),
 FOREIGN KEY (ingred_code) REFERENCES item (code)
) TYPE = INNODB;

First thing to do is to populate the unit table.



C:\Documents and Settings\Tim\My Documents\DB\Recipe\sql>more unit.txt
1,litre
2,each
3,gram

C:\Documents and Settings\Tim\My Documents\DB\Recipe\sql>

mysql> LOAD DATA LOCAL INFILE 'unit.txt' REPLACE INTO TABLE recipe.unit FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
    -> ;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from unit;
+----+-------+
| id | name  |
+----+-------+
|  1 | litre |
|  2 | each  |
|  3 | gram  |
+----+-------+
3 rows in set (0.00 sec)

mysql>

Note: the stupid 'LINES TERMINATED' option used because I'm running on a stupid OS that uses Character Return Line Feeds for it's line termination.

Then the PHP script to load some raw materials:



C:\Documents and Settings\Tim\My Documents\DB\Recipe\php>more rawingred.txt
EGG,Egg,2,15
FLOUR,Flour,3,0.01
MILK,Milk,1,75

C:\Documents and Settings\Tim\My Documents\DB\Recipe\php>more rawingredload.php
<?php
function openMyDB($dbname)
{
include("DBInfo.inc");
$con = mysql_connect($mydbhost,$mydbuser,$mydbpassword);
if (!$con) {
        die('Could not connect: ' . mysql_error());
}
mysql_select_db($dbname, $con);
return $con;
}

$con = openMyDB("recipe");

$file=fopen("rawingred.txt","r") or exit("Unable to open file!");
while(!feof($file)) {
  $ingred = fgets($file);
  list($code, $name, $unitid, $cost) = split(',', $ingred);
  echo "$code\t$name\t$unitid\t$cost\n";
  $sql="INSERT INTO item (code, name, unitid, cost)
          VALUES ('$code','$name','$unitid','$cost')";
  if (!mysql_query($sql,$con)) {
     die('Error: ' . mysql_error());
  }
  echo "1 record added\n";
  }
fclose($file);

?>
C:\Documents and Settings\Tim\My Documents\DB\Recipe\php>php rawingredload.php
EGG     Egg     2       15

1 record added
FLOUR   Flour   3       0.01

1 record added
MILK    Milk    1       75
1 record added


C:\Documents and Settings\Tim\My Documents\DB\Recipe\php>

mysql> select * from item;
+-------+-------+--------+-------+
| code  | name  | unitid | cost  |
+-------+-------+--------+-------+
| EGG   | Egg   |      2 | 15.00 |
| FLOUR | Flour |      3 |  0.01 |
| MILK  | Milk  |      1 | 75.00 |
+-------+-------+--------+-------+
3 rows in set (0.00 sec)

mysql>

Compare the Perl with PHP

2009-07-22T08:26:00.000-07:00

I had this idea that I should write exactly the same code in PHP that I had just written in Perl, just to compare.


<?php
function openMyDB()
{
include("DBInfo.inc");
$con = mysql_connect($mydbhost,$mydbuser,$mydbpassword);
if (!$con) {
        die('Could not connect: ' . mysql_error());
}
mysql_select_db("cddb", $con);
return $con;
}

$con = openMyDB();

mysql_select_db("cddb", $con);

print "Name\t\tTitle\n";
$result = mysql_query("SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid");
while ($row = mysql_fetch_array($result)) {
        echo $row['name'] . "\t" . $row['title'] . "\n" ;
}
mysql_free_result($result);
mysql_close($con);
?>

D:\Database\CDDB\Reports>php CDRep.php
Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Database\CDDB\Reports>

So, which do you prefer?

Please note that:


include("DBInfo.inc");

is my own code (for what it's worth) that we've seen in previous examples.


D:\Database\CDDB\Reports>more  DBInfo.inc
<?php
$mydbhost="localhost";
$mydbuser="myuser";
$mydbpassword="mypassword";
?>

D:\Database\CDDB\Reports>

A Perl Script that accesses MySQL

2009-07-22T01:49:00.000-07:00

Last year I posted a simple Perl script that accesses a PostgreSQL database using the DBI connector. I decided that I should do the same for MySQL. I'm using Active State Perl on Windows XP. Amusingly, this version does not include the MySQL connectors as default, but it's easy enough to add using perl ppm (Perl's Perl Package Manager).

I want a simple script that lists out artists and their CDs in my database.


#!/usr/bin/perl
use strict;
use DBI();
my $dbh1 = DBI->connect("DBI:mysql:database=cddb;host=localhost","uuuu", "xxxxxx", { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
 print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1)

D:\Documents and Settings\timj\perl>c:\Perl\bin\perl cd.pl
Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>

That's about as simple as it get, I'm sure that there is far more to it than that, but this will do for now!
Tim

Creating a View

2009-07-20T03:06:00.000-07:00

Let's pretend that we regularly want to be able to select all our publishers with the CDs we have on file for them. We saw in the last post that the SQL is:


SELECT c.title, p.name FROM cdtable c RIGHT OUTER JOIN publisher p ON c.pubid = p.id;

We can create a view to do this:


mysql> CREATE VIEW V_Publisher as
    -> SELECT c.title, p.name FROM cdtable c RIGHT OUTER JOIN publisher p ON c.pubid = p.id;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> describe V_Publisher;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| title | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

We can the simply select from this view:


mysql> select * from V_Publisher;
+--------------------+----------------+
| title              | name           |
+--------------------+----------------+
| Waking Up          | EMI            |
| Bethany Dillon     | EMI            |
| Imagination        | EMI            |
| Guaranteed         | SONY           |
| 15                 | SONY           |
| Made To Love Magic | Island Records |
| Five Leaves Left   | Island Records |
| Bryter Layter      | Island Records |
| NULL               | Apple Records  |
+--------------------+----------------+
9 rows in set (0.00 sec)

mysql>

Inner and Outer Joins

2009-07-20T01:54:00.000-07:00

To Demonstrate inner and outer joins, the first thing I need to do is to add some data in to the database that isn't referenced by another table. So here I'm adding a publisher that I have no CDs for. Alas, no Beatles in my collection!


mysql> insert into publisher (name) values ('Apple Records');
Query OK, 1 row affected (0.00 sec)

A normal join would not tell us about Apple Records, as there is nothing to report.


mysql> SELECT c.title, p.name FROM cdtable c, publisher p
    -> where c.pubid = p.id;
+--------------------+----------------+
| title              | name           |
+--------------------+----------------+
| Waking Up          | EMI            |
| Bethany Dillon     | EMI            |
| Imagination        | EMI            |
| Guaranteed         | SONY           |
| 15                 | SONY           |
| Made To Love Magic | Island Records |
| Five Leaves Left   | Island Records |
| Bryter Layter      | Island Records |
+--------------------+----------------+
8 rows in set (0.00 sec)

mysql>

We get the same results when we code an Inner Join,


mysql> SELECT c.title, p.name FROM cdtable c INNER JOIN publisher p ON c.pubid = p.id;
+--------------------+----------------+
| title              | name           |
+--------------------+----------------+
| Waking Up          | EMI            |
| Bethany Dillon     | EMI            |
| Imagination        | EMI            |
| Guaranteed         | SONY           |
| 15                 | SONY           |
| Made To Love Magic | Island Records |
| Five Leaves Left   | Island Records |
| Bryter Layter      | Island Records |
+--------------------+----------------+
8 rows in set (0.00 sec)

mysql>

When we code a righthand outer join on Publisher, we see Apple, but with no CDs of course.


mysql> SELECT c.title, p.name FROM cdtable c RIGHT OUTER JOIN publisher p ON c.pubid = p.id;
+--------------------+----------------+
| title              | name           |
+--------------------+----------------+
| Waking Up          | EMI            |
| Bethany Dillon     | EMI            |
| Imagination        | EMI            |
| Guaranteed         | SONY           |
| 15                 | SONY           |
| Made To Love Magic | Island Records |
| Five Leaves Left   | Island Records |
| Bryter Layter      | Island Records |
| NULL               | Apple Records  |
+--------------------+----------------+
9 rows in set (0.00 sec)

mysql>

A Simple Correlated Subquery Example

2009-07-13T02:25:00.000-07:00

A correlated subquery is a subquery that needs to refer to the data returned in the main query. They can seem pretty scary at first, but they're fairly simply once you get the hang of it.

In this example, I want to list all my artists, along with the most recent CD they have released. To do this, I have to select all the artists and their CDs, and then use a sub query to select the latested (MAX) date published for each artist.

With a corrolated subquery you can (usually) run the query without the subquery to see all the results, like this:


mysql> SELECT artist.name, cd.title, cd.yearpublished
    -> FROM artist, cdtable AS cd
    -> WHERE artist.id = cd.artid
    -> ;
+----------------+--------------------+---------------+
| name           | title              | yearpublished |
+----------------+--------------------+---------------+
| Phatfish       | Guaranteed         |          2007 |
| Phatfish       | 15                 |          2008 |
| Bethany Dillon | Waking Up          |          2007 |
| Bethany Dillon | Bethany Dillon     |          2004 |
| Bethany Dillon | Imagination        |          2005 |
| Nick Drake     | Made To Love Magic |          2004 |
| Nick Drake     | Five Leaves Left   |          2004 |
| Nick Drake     | Bryter Layter      |          2000 |
+----------------+--------------------+---------------+
8 rows in set (0.00 sec)

This lists out all the CDs for each artist, so we need to tell the query to only list the most recent:


mysql> SELECT artist.name, cd.title, cd.yearpublished
    -> FROM artist, cdtable AS cd
    -> WHERE artist.id = cd.artid
    -> AND cd.yearpublished =
    ->     (SELECT MAX(yearpublished)
    ->      FROM cdtable
    ->      WHERE cdtable.artid = cd.artid)
    -> ;
+----------------+--------------------+---------------+
| name           | title              | yearpublished |
+----------------+--------------------+---------------+
| Phatfish       | 15                 |          2008 |
| Bethany Dillon | Waking Up          |          2007 |
| Nick Drake     | Made To Love Magic |          2004 |
| Nick Drake     | Five Leaves Left   |          2004 |
+----------------+--------------------+---------------+
4 rows in set (0.00 sec)

mysql>

Notice that we have two rows returned for Nick Drake. This is because two CDs match the condition. Of course, Nick Drake wasn't publishing anything in 2004, unfortunately. Had I used the original vinyl publication date, it would have been different.

Improving the menus

2009-06-16T05:34:00.000-07:00

It would be great if one single program were to be flexible enough to handle all the menus. After all, they are in a single database table.

The idea is to pass the program a parameter telling it which menu to show. It will test, and if no menu has been requested, it will show the main menu.

I'll also move tings around to put the database open and closing in to functions, and the head and tail printing as well.

One thing to bear in mind is that the menu header is currently hard code, and this will need to be pulled from the database.

So, create a table to store the menu details!

We'll need a primary index on the menus table:


CREATE TABLE menus (
   id           INT NOT NULL AUTO_INCREMENT,
   name         VARCHAR(20),
   title        VARCHAR(40),
   description  VARCHAR(80),
   last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP (),
   PRIMARY KEY (id)
)  ENGINE=INNODB;

CREATE TABLE cddb.menuitems (
   menuid    INT NOT NULL,
   itemno    INT NOT NULL,
   itemtext  VARCHAR(50) NOT NULL,
   itemlink  VARCHAR(150) NOT NULL,
   last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP (),
   FOREIGN KEY (menuid) REFERENCES menus(id) ON DELETE CASCADE
)  ENGINE=INNODB;

And repopulate:


INSERT INTO cddb.menus (name, title, description) values ('mainmenu','Main Menu', 'Welcome to my CD Collection');
INSERT INTO cddb.menus (name, title, description) values ('listmenu','List Menu', 'List Details of my CD Collection');

INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (1,1,'Data Entry','mainmenu.php?menuname=dataentrymenu');

INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (1,2,'Data Lists','mainmenu.php?menuname=listmenu');

INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (1,3,'Reports','mainmenu.php?menuname=reportsmenu');

INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (2,1,'List Publisher','publishers.php');
INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (2,2,'List Artists','artists.php');
INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (2,3,'CDs by Artists','lcdsbyartist.php');
INSERT INTO cddb.menuitems (menuid, itemno, itemtext, itemlink)
  values (2,4,'Tracks by CD','track.php');

Finally - rewrite the menu PHP


<?php
function openMyDB()
{
include("DBInfo.inc");
$con = mysql_connect($mydbhost,$mydbuser,$mydbpassword);
if (!$con) {
 die('Could not connect: ' . mysql_error());
}
mysql_select_db("cddb", $con);
return $con;
}

function closeMyDB($con)
{
mysql_close($con);
}

function writeHead($MenuName)
{
$MyQuery =            "SELECT title, description FROM menus m ";
$MyQuery = $MyQuery . "WHERE m.name = '" . $MenuName . "'";

$result = mysql_query($MyQuery);
$row = mysql_fetch_array($result);

echo "<html>\n<head>\n<title>" . $row['title'] . "</title>\n</head>\n";
echo "<body>\n";
echo "<h1>" . $row['description'] . "</h1>\n";
mysql_free_result($result);
}

function writeTail()
{
echo "</body>\n</html>";
}

// Main Program starts here

$mycon=openMyDB();

if ( empty($_GET['menuname'])) 
 $MyMenuName='mainmenu';
else
 $MyMenuName=$_GET['menuname'];


writehead($MyMenuName);
echo "<ol>\n";

$MyQuery =            "SELECT itemtext, itemlink, itemno FROM menus m, menuitems mi ";
$MyQuery = $MyQuery . "WHERE m.name = '" . $MyMenuName . "' ";
$MyQuery = $MyQuery . "and m.id=mi.menuid order by itemno";

$result = mysql_query($MyQuery);
while ($row = mysql_fetch_array($result)) {
 echo "<li>\n";
 echo "<a href=\"" . $row['itemlink'] . "\">";
 echo $row['itemtext'] . "</a>";
 echo "\n</li>\n";
}
mysql_free_result($result);
echo "</ol>";
writetail();
closeMyDB($mycon);

?>

Tidying up the code

2009-06-09T08:23:00.000-07:00

I have decided to tidy up the code a little. I have moved the database details in to a file that gets included, and moved the database opening and closing into functions. This bit was (slightly) interesting (in fact almost too interesting to be included in this otherwise boring blog). The database open function has to return a value that points to the open database, and then we have to pass that value to the database close function. There - that was exciting wasn't it!

<?php

function openMyDB()
{
include("DBInfo.inc");
$con = mysql_connect($mydbhost,$mydbuser,$mydbpassword);
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("cddb", $con);
return $con;
}

function closeMyDB($con)
{
mysql_close($con);
}

function writeHead()
{
echo "<html>\n<head>\n<title>CDs By Artists</title>\n</head>\n";
echo "<body>\n";
echo "<h1>CDs By Artists</h1>\n";
}

function writeTail()
{
echo "</body>\n</html>";
}

function writeMenu()
{
echo "<form action=\"" . $_SERVER['PHP_SELF'] . "\" method=\"post\">\n";
echo "<select name=\"artid\">\n";

$result = mysql_query("SELECT id, name FROM artist order by name");
while ($row = mysql_fetch_array($result)) {
echo "<option value=\"";
echo $row['id'] . "\">".$row['name'];
echo "</option>\n";
}
mysql_free_result($result);
echo "</select>\n";
echo "<input type=\"submit\" value=\"Submit\">";
echo "</form>\n";
}

function writeDetails($artid)
{
$result = mysql_query("SELECT name FROM artist where id = " . $artid . ";");
$row = mysql_fetch_array($result);
echo "<h3>" . $row['name'] . "</h3>\n";
mysql_free_result($result);
$result = mysql_query("SELECT title, yearpublished from cdtable where artid = " . $artid . ";");
echo "<table>\n<tr><th>Year Published</th><th>Title</th></tr>\n";
while ($row = mysql_fetch_array($result)) {
echo "<tr><td>" . $row['yearpublished'] . "</td>";
echo "<td>" . $row[title] . "</td></tr>\n";
}
mysql_free_result($result);
echo "</table>\n";
}

#
## Main Body
#

$mycon=openMyDB();
writehead();
if ( empty($_POST['artid']) )
{
writeMenu();
}
else
{
writeDetails($_POST['artid']);
}

writetail();
closeMyDB($mycon);
?>

And the incude file looks like this:

<?php
$mydbhost="localhost";
$mydbuser="timj";
$mydbpassword="xxxxxx";
?>

That's enough excitement for one day!

My first form in PHP

2009-06-05T07:01:00.001-07:00

This form checks to see if it has been past a parameter. If not it displays a pull-down list of the artists on file. If it has been passed a value, it uses that value to pull out the CDs on file for an artist. That way the same piece of code is used twice.

I've tried to put things in functions to make it a little more tidy.

<?php
function writeHead()
{
echo "<html>\n<head>\n<title>CDs By Artists</title>\n</head>\n";
echo "<body>\n";
echo "<h1>CDs By Artists</h1>\n";
}

function writeTail()
{
echo "</body>\n</html>";
}

function writeMenu()
{
echo "<form action=\"" . $_SERVER['PHP_SELF'] . "\" method=\"post\">\n";
echo "<select name=\"artid\">\n";

$result = mysql_query("SELECT id, name FROM artist order by name");
while ($row = mysql_fetch_array($result)) {
echo "<option value=\"";
echo $row['id'] . "\">".$row['name'];
echo "</option>\n";
}
mysql_free_result($result);
echo "</select>\n";
echo "<input type=\"submit\" value=\"Submit\">";
echo "</form>\n";
}

function writeDetails($artid)
{
$result = mysql_query("SELECT name FROM artist where id = " . $artid . ";");
$row = mysql_fetch_array($result);
echo "<h3>" . $row['name'] . "</h3>\n";
mysql_free_result($result);
$result = mysql_query("SELECT title, yearpublished from cdtable where artid = " . $artid . ";");
echo "<table>\n<tr><th>Year Published</th><th>Title</th></tr>\n";
while ($row = mysql_fetch_array($result)) {
echo "<tr><td>" . $row['yearpublished'] . "</td>";
echo "<td>" . $row[title] . "</td></tr>\n";
}
mysql_free_result($result);
echo "</table>\n";
}

#
## Main Body
#
$con = mysql_connect("localhost","timj","xxxxxx");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("cddb", $con);

writehead();
if ($_POST['artid']=="") writeMenu();
else writeDetails($_POST['artid']);

writetail();

mysql_close($con);
?>

And here is the Artists table:

2009-06-05T04:26:00.000-07:00

<?php
echo "<html><head><title>Artists</title></head>";
echo "<body>";
echo "<h1>Artists in my CD Collection</h1>";
echo "<ol>\n";
$con = mysql_connect("localhost","timj","xxxxx");
if (!$con) {
die('Could not connect: ' . mysql_error());
}

mysql_select_db("cddb", $con);

$result = mysql_query("SELECT name FROM artist order by name");
while ($row = mysql_fetch_array($result)) {
echo "<li>\n";
echo $row['name'] ;
echo "</li>\n";
}
mysql_free_result($result);
mysql_close($con);
echo "</ol>";
echo "</body>";
echo "</html>";
?>

Pretty much the same as the Publishers screen.

Listing Data

2009-06-04T09:51:00.000-07:00

Here is the code for my Publishers list:

<?php
echo "<html><head><title>List Menu</title></head>";
echo "<body>";
echo "<h1>Explore My CD Collection</h1>";
echo "<ol>\n";
$con = mysql_connect("localhost","timj","xxxxxxx");
if (!$con) {
die('Could not connect: ' . mysql_error());
}

mysql_select_db("cddb", $con);

$result = mysql_query("SELECT itemtext, itemlink, itemno FROM menus WHERE name = 'listmenu' order by itemno");
while ($row = mysql_fetch_array($result)) {
echo "<li>\n";
echo "<a href=\"" . $row['itemlink'] . "\">";
echo $row['itemtext'] . "</a>";
echo "\n</li>\n";
}
mysql_free_result($result);
mysql_close($con);
echo "</ol>";
echo "</body>";
echo "</html>";
?>

The Output html looks like this:

D:\Database\CDDB\DocRoot>php publishers.php
<html><head><title>Publsihers</title></head><body><h1>Publishers in my CD Collection</h1><ol>
<li>
EMI</li>
<li>
SONY</li>
</ol></body></html>
D:\Database\CDDB\DocRoot>

A Trigger to show when a row was last updated

2009-05-11T05:36:00.000-07:00

It is a good idea to be able to tell when a row has been updated on your table.
Here is a simple way of doing that. It's not perfect auditing, for that you would need to write a trigger to store the before and after values in a table that the normal user could not write to, but it's a start.

Add a column to our table to hold the date stamp:


CREATE TABLE cddb.menus (
   name      VARCHAR(20) NOT NULL,
   itemno    INT NOT NULL,
   itemtext  VARCHAR(50) NOT NULL,
   itemlink  VARCHAR(150) NOT NULL,
   last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

and write a simple trigger to fire off when the table is updated:


CREATE TRIGGER cddb.t_menus_1
BEFORE UPDATE
ON cddb.menus
FOR EACH ROW SET
new.last_updated:=CURRENT_TIMESTAMP();

The results we get are:


mysql> select * from menus;
+----------+--------+-----------------+------------------+---------------------+
| name     | itemno | itemtext        | itemlink         | last_updated        |
+----------+--------+-----------------+------------------+---------------------+
| mainmenu |      1 | Data Entre      | dataentry.php    | 2009-05-11 13:57:13 |
| mainmenu |      2 | Data Lists      | datalists.php    | 2009-05-11 13:57:13 |
| mainmenu |      3 | Reports         | reports.php      | 2009-05-11 13:57:13 |
| listmenu |      1 | List Publishers | publishers.php   | 2009-05-11 13:57:13 |
| listmenu |      2 | List Artists    | artists.php      | 2009-05-11 13:57:13 |
| listmenu |      3 | CDs by Artist   | lcdsbyartist.php | 2009-05-11 13:57:13 |
| listmenu |      4 | Tracks by CD    | track.php        | 2009-05-11 13:57:13 |
+----------+--------+-----------------+------------------+---------------------+
7 rows in set (0.02 sec)

mysql> update menus set itemtext = 'New item' where name = 'mainmenu' and itemno=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from menus;
+----------+--------+-----------------+------------------+---------------------+
| name     | itemno | itemtext        | itemlink         | last_updated        |
+----------+--------+-----------------+------------------+---------------------+
| mainmenu |      1 | New item        | dataentry.php    | 2009-05-11 13:59:38 |
| mainmenu |      2 | Data Lists      | datalists.php    | 2009-05-11 13:57:13 |
| mainmenu |      3 | Reports         | reports.php      | 2009-05-11 13:57:13 |
| listmenu |      1 | List Publishers | publishers.php   | 2009-05-11 13:57:13 |
| listmenu |      2 | List Artists    | artists.php      | 2009-05-11 13:57:13 |
| listmenu |      3 | CDs by Artist   | lcdsbyartist.php | 2009-05-11 13:57:13 |
| listmenu |      4 | Tracks by CD    | track.php        | 2009-05-11 13:57:13 |
+----------+--------+-----------------+------------------+---------------------+
7 rows in set (0.00 sec)

mysql>

PHP - My website's menus

2009-05-08T23:27:00.000-07:00

I have decided that I want all the menus on my web site to be dynamic, driven from the database rather than hard-coded in HTML. This has the advantage that it you want to add a menu item, you just add it to the database, you don't need to recode your menus. Of course it requires a little more work up front, but that's what we are here for!

Step 1:
Create a database table to hold your menus:


CREATE TABLE cddb.menus (
  name      VARCHAR(20) NOT NULL,
  itemno    INT NOT NULL,
  itemtext  VARCHAR(50) NOT NULL,
  itemlink  VARCHAR(150) NOT NULL
);

Step 2:
Create a text file with your menus defined in it:


mainmenu,1,Data Centre,dataentry.php
mainmenu,2,Data Lists,datalists.php
mainmenu,3,Reports,reports.php
listmenu,1,List Publishers,publishers.php
listmenu,2,List Artists,artists.php
listmenu,3,CDs by Artist,lcdsbyartist.php
listmenu,4,Tracks by CD,track.php

I am working on a Windows laptop, and I found that the load command didn't handle the windows EOL characters well, it corrupts the final field. I'm sure that there is a way around this, but I ended up using a dos2unix command in cygwin.

Step 3:
Import your menu data into your database:


mysql> LOAD DATA LOCAL INFILE 'menus.txt' INTO TABLE cddb.menus FIELDS TERMINATED BY ',';
Query OK, 7 rows affected, 7 warnings (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 7

mysql> select * from menus;
+----------+--------+-----------------+------------------+
| name     | itemno | itemtext        | itemlink         |
+----------+--------+-----------------+------------------+
| mainmenu |      1 | Data Centre     | dataentry.php    |
| mainmenu |      2 | Data Lists      | datalists.php    |
| mainmenu |      3 | Reports         | reports.php      |
| listmenu |      1 | List Publishers | publishers.php   |
| listmenu |      2 | List Artists    | artists.php      |
| listmenu |      3 | CDs by Artist   | lcdsbyartist.php |
| listmenu |      4 | Tracks by CD    | track.php        |
+----------+--------+-----------------+------------------+
7 rows in set (0.00 sec)

mysql>

Step 4:

Final Step, to get write the PHP to create the index page.


<?php
echo "<html><head><title>Main Menu</title></head>";
echo "<body>";
echo "<h1>Welcome to my CD Collection</h1>";
echo "<ol>\n";
$con = mysql_connect("localhost","timj","xxx");
if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("cddb", $con);

$result = mysql_query("SELECT itemtext, itemlink, itemno FROM menus WHERE name = 'mainmenu' order by itemno");
while ($row = mysql_fetch_array($result)) {
    echo "<li>\n";
    echo "<a href=\"" . $row['itemlink'] . "\">";
    echo $row['itemtext'] . "</a>";
    echo "\n</li>\n";
}
mysql_free_result($result);
mysql_close($con);
echo "</ol>";
echo "</body>";
echo "</html>";
?>

And that's all there is to it!

Foreign Key Contraints in MySQL

2009-05-08T22:25:00.000-07:00

Schema

OK, these are a few tables to show off foreign key constraints:


CREATE TABLE cddb.publisher (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE cddb.artist (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE cddb.cdtable (
id INT NOT NULL AUTO_INCREMENT,
pubid INT NOT NULL,
artid INT NOT NULL,
title varchar(50) NOT NULL,
yearpublished INT,
PRIMARY KEY (id),
INDEX (artid),
FOREIGN KEY (artid)
REFERENCES artist(id) ON DELETE CASCADE,
INDEX (pubid),
FOREIGN KEY (pubid)
REFERENCES publisher(id) ON DELETE CASCADE
) ENGINE=INNODB;

mysql> insert into cdtable (pubid, artid, title, yearpublished)
-> values
-> (1,2,'Waking Up',2007);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> insert into cdtable (pubid, artid, title, yearpublished)
-> values
-> (1,3,'Yesterday, today & forever',2006);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cddb`.`cdtable`, CONSTRAINT `cdta
ble_ibfk_1` FOREIGN KEY (`artid`) REFERENCES `artist` (`id`) ON DELETE CASCADE)
mysql>

As you can see, I could not add 'Yesterday, Today and Forever' as Vicky Beeching has not yet been added to the artists table.

Likewise, if I delete Bethany Dillon as an artist on the system, it will delete all her CDs as well.


mysql> select * from cdtable;
+----+-------+-------+-----------+---------------+
| id | pubid | artid | title     | yearpublished |
+----+-------+-------+-----------+---------------+
|  1 |     1 |     2 | Waking Up |          2007 |
+----+-------+-------+-----------+---------------+
1 row in set (0.00 sec)


mysql> delete from artist where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from cdtable;
Empty set (0.00 sec)

mysql>

A new direction - PHP and MySQL

2009-05-05T14:26:00.000-07:00

I have decided to focus on the LAMP stack, primarily PHP rather than Perl at the moment, and MySQL rather than Postgresql.

create a tablespace
create a database
create a user to own the database
create a role for the user to be in
create a table for publisher
create a table for artist
create a table for CDs, referential integrity against artist and publisher
create a table for tracks, referential integrity against CD
create a read-only user
write backup scripts for the database
ensure that deleting a CD deletes all tracks
ensure that deleting an artist deletes all CDs and tracks
write a report to report in number of CDs owned by artist
create data load scripts for publisher, artist and CDs
write forms to allow entry of publishers, artists, CD's and tracks
write a form to list the tracks of a selected CD
write a report to list all the CD's owned by an owner

My First Trigger!

2008-10-28T10:36:00.001-07:00

The task - to create a table, and set a trigger to mark a row as updated any time an insert or update is run against it.

CREATE TABLE employee
(
surname character varying(20) NOT NULL,
firstname character(20) NOT NULL,
dob date NOT NULL,
badgeid serial NOT NULL,
last_updated date NOT NULL,
CONSTRAINT u_badgeid UNIQUE (badgeid)
)

CREATE FUNCTION set_lastchg() RETURNS opaque AS
'BEGIN
NEW.last_updated = now();
RETURN NEW;
END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER t_employee_1
BEFORE INSERT OR UPDATE
ON employee
FOR EACH ROW
EXECUTE PROCEDURE set_lastchg();

insert into employee (surname, firstname, dob ) values
('Jinkerson','Tim', date '1914-12-25')

All looks good!

Archiving and the Write Ahead Log

2008-10-26T00:32:00.000-07:00

The next lesson is on setting up your database so that we can recover without data loss!

Turn on in postgresql.conf
On Windows XP this can be found at
C:\Program Files\PostgreSQL\8.3\data


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#fsync = on    # turns forced synchronization on or off
#synchronous_commit = on  # immediate fsync at commit
#wal_sync_method = fsync  # the default is the first option
     # supported by the operating system:
     #   open_datasync
     #   fdatasync
     #   fsync
     #   fsync_writethrough
     #   open_sync
#full_page_writes = on   # recover from partial page writes
#wal_buffers = 64kB   # min 32kB
     # (change requires restart)
#wal_writer_delay = 200ms  # 1-10000 milliseconds

#commit_delay = 0   # range 0-100000, in microseconds
#commit_siblings = 5   # range 1-1000

# - Checkpoints -

#checkpoint_segments = 3  # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min  # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s  # 0 is off

# - Archiving -

#archive_mode = off  # allows archiving to be done
    # (change requires restart)
#archive_command = ''  # command to use to archive a logfile segment
#archive_timeout = 0  # force a logfile segment switch after this
    # time; 0 is off

Need to configure the Archive copy command

Making the following changes:

fsync = on
synchronous_commit = on
wal_sync_method = fsync
full_page_writes = on
archive_mode = on
archive_command = 'copy "%p" "J:\\server\\archivedir\\%f"'
archive_timeout = 3600 # switch logs every hour

Loads of information can be found here
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm

Testing it!

--

Backup and Recovery Part 2

2008-10-24T09:10:00.000-07:00

To Perform a simple recovery of a single database table from your previous backup.

Perform the backup!
timj@dell-desktop:~$ pg_dump -i -h localhost -U timj -F c -b -v -f /var/tmp/mybackup testdb
pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined operator families
pg_dump: reading user-defined conversions
pg_dump: reading user-defined tables
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: reading type casts
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "numbers"
pg_dump: finding the columns and types of table "myuser"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
pg_dump: dumping contents of table myuser
pg_dump: dumping contents of table numbers
timj@dell-desktop:~$ ls -trl /var/tmp/mybackup
-rw-r--r-- 1 timj timj 2261 2008-10-24 17:11 /var/tmp/mybackup
timj@dell-desktop:~$

At this stage we lost the 'numbers' table.
drop table number;

So we need to recover it

timj@dell-desktop:~$ pg_restore -c -h localhost -U timj -v /var/tmp/mybackup -d testdb -t numbers
pg_restore: connecting to database for restore
pg_restore: dropping TABLE numbers
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1466; 1259 24589 TABLE numbers timj
pg_restore: [archiver (db)] could not execute query: ERROR: table "numbers" does not exist
Command was:
DROP TABLE public.numbers;
pg_restore: creating TABLE numbers
pg_restore: restoring data for table "numbers"
pg_restore: setting owner and privileges for TABLE numbers
WARNING: errors ignored on restore: 1
timj@dell-desktop:~$

Oracle Idle Timeout

2008-07-16T03:04:00.000-07:00

You can find what your Oracle Idle Timeout is set to by:


SQL> select * from dba_profiles
 2  where RESOURCE_NAME = 'IDLE_TIME';
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        IDLE_TIME                        KERNEL   4320
SQL>

For details of profiles, see: here

Accessing a database via Perl

2008-07-10T13:05:00.000-07:00

Here's a small program I wrote to access a Postgresql database.

#!/usr/bin/perl
use DBI;
$dbh1 = DBI->connect( "dbi:Pg:dbname=testdb@localhost","", "", { RaiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";

my $sql = "select vendor, country, freephone, payphone from numbers ";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $vendor, $country, $freephone, $payphone );
$sth->bind_columns( \$vendor, \$country, \$freephone, \$payphone );

print "Vendor\tCountry\tFreephone\tPayphone\n";
while( $sth->fetch() ) {
print "$vendor\t$country\t$freephone\t$payphone\n";
}

my $sql = "select name, vendor, leader, participant from myuser ";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $vendor, $leader, $participant );
$sth->bind_columns( \$name, \$vendor, \$leader, \$participant );

print "\nName\tVendor\tLeader\tParticipant\n";
while( $sth->fetch() ) {
print "$name\t$vendor\t$leader\t$participant\n";
}

$dbh1->disconnect() if($dbh1);

The output looks like this:

timj@dell-desktop:~/MySync/db$ ./plsql_phones1.pl
Vendor Country Freephone Payphone
Verizon UK 44-20-7075-3246 0808-238-6025
Verizon France 33-1-70-70-74-20 080-563-9647
Verizon USA 1-203-418-3122 866-692-3163
Verizon India 000-800-852-1266

Name Vendor Leader Participant
Tim Verizon 1234567 1234567
timj@dell-desktop:~/MySync/db$

Connecting via OpenOffice

2008-06-08T00:24:00.000-07:00

There's a great link here.
http://www.postgresql.org/docs/7.3/static/libpq-connect.html
I actually managed to connect just by saying

dbname=cddb host=localhost

Installing JDK 1.5 on Solaris

2008-05-16T04:48:00.000-07:00

mkdir jdk-1_5_0_15-solaris-sparcv9-32
mkdir jdk-1_5_0_15-solaris-sparcv9-64
cd jdk-1_5_0_15-solaris-sparcv9-32 uncompress < ../jdk-1_5_0_15-solaris-sparc.tar.Z | tar xf -
cd ../jdk-1_5_0_15-solaris-sparcv9-64
uncompress < ../jdk-1_5_0_15-solaris-sparcv9.tar.Z | tar xf -
pkgrm SUNWj5cfg SUNWj5dmx SUNWj5jmp SUNWj5man pkgrm SUNWj5rt SUNWj5rtx
pkgadd -d . SUNWj5rt
pkgadd -d . SUNWj5cfg SUNWj5dev SUNWj5dmo SUNWj5jmp SUNWj5man
pkgadd -d . SUNWj5rtx SUNWj5dvx SUNWj5dmx
pkginfo -l SUNWj5rt SUNWj5cfg SUNWj5dev SUNWj5dmo SUNWj5jmp SUNWj5man SUNWj5rtx SUNWj5dvx SUNWj5dmx
If you're on a server with Sparse Zones, you need to do this in the Global zone and then test on each sparse zone.
Testing
-bash-3.00$ Cat HelloWorld.java
-bash: Cat: command not found
-bash-3.00$ cat HelloWorld.java
public class HelloWorld { public static void main(String args[])
{ System.out.println("Hello World"); int i; for (i=0; i< args.length; i++)
System.out.println(args[i]); } }
-bash-3.00$
-bash-3.00$ javac HelloWorld.java
-bash-3.00$ java HelloWorld
Hello World
-bash-3.00$

Backing up the database

2008-05-08T08:18:00.000-07:00

/usr/bin/pg_dump -h localhost -p 5432 -U timj -F c -v -f "/home/timj/db/backup_08_05_2008.backup" cddb

-h = host
-p = port
-U = database user
-F c = Format suitable for pg_restore
-v = verbose
-f = file to back up to

Could just say

pg_dump -U timj -F c -f "/home/timj/db/backup_08_05_2008.backup" cddb

Reworking the CD table

2008-05-08T05:39:00.000-07:00

The CD table works, but needs a unique key to link to the tracks on the CD.

CREATE TABLE cdtable
(
cdnum serial NOT NULL,
pubnum int4 NOT NULL,
artnum int4 NOT NULL,
cdtitle varchar(50) NOT NULL,
datepublished date,
CONSTRAINT artnum FOREIGN KEY (artnum) REFERENCES artist (artnum) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT pubnum FOREIGN KEY (pubnum) REFERENCES publisher (pubnum) ON UPDATE NO ACTION ON DELETE NO ACTION
) WITHOUT OIDS
TABLESPACE ts_cddb_1;

insert into cdtable (artnum, pubnum, cdtitle, datepublished)
values (1,3,'Heavenbound','02apr2002');

My First Join In Postgresql

2008-05-08T05:36:00.001-07:00

cddb=> select a.artname, p.pubname, c.cdtitle
cddb-> from artist a, publisher p, cdtable c
cddb-> where a.artnum = c.artnum
cddb-> and p.pubnum = c.pubnum;
artname | pubname | cdtitle
----------+-----------------+-------------
Phatfish | Authentic Media | Heavenbound
(1 row)

cddb=>

Exactly the same as Oracle really!

CD Table with foreign key contraints!

2008-05-08T03:59:00.000-07:00

CREATE TABLE cdtable
(
pubnum int4 NOT NULL,
artnum int4 NOT NULL,
cdtitle varchar(50) NOT NULL,
datepublished date,
CONSTRAINT artnum FOREIGN KEY (artnum) REFERENCES artist (artnum) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT pubnum FOREIGN KEY (pubnum) REFERENCES publisher (pubnum) ON UPDATE NO ACTION ON DELETE NO ACTION
) WITHOUT OIDS
TABLESPACE ts_cddb_1;

The Artist Table

2008-05-06T10:25:00.000-07:00

Creating the Artist Table

CREATE TABLE artist (
artnum SERIAL,
artname VARCHAR(50),
CONSTRAINT uc_artist UNIQUE (artnum)
);

cddb=> insert into artist (artname) values ('Phatfish');
INSERT 0 1
cddb=> insert into artist (artname) values ('Bethany Dillon');
INSERT 0 1
cddb=> select * from artist;
artnum | artname
--------+----------------
1 | Phatfish
2 | Bethany Dillon
(2 rows)

cddb=>

The Publisher Table

2008-05-06T09:10:00.000-07:00

Create a table with a serial key.

CREATE TABLE publisher (
pubnum SERIAL,
pubname VARCHAR(20));

ALTER TABLE publisher ADD CONSTRAINT uc_pub UNIQUE(pubnum);

cddb=> insert into publisher (pubname) values ('EMI');
INSERT 0 1
cddb=> insert into publisher (pubname) values ('SONY');
INSERT 0 1
cddb=> select * from publisher;
pubnum | pubname
--------+---------
1 | EMI
2 | SONY
(2 rows)

cddb=>

pgloader

2008-05-06T08:01:00.000-07:00

Decided I'd play around with pgloader, but couldn't find it in my distro's package manager. I found it in the web, and found that they do not produce an rpm - only a deb! I use Ubuntu on my desktop, but PCLinuxOS on my laptop. So...

First installed Alien through the package manager.

wget http://pgfoundry.org/frs/download.php/1680/pgloader_2.3.0-1_all.deb
(as root)
alien -r --scripts pgloader_2.3.0-1_all.deb
rpm -i pgloader-2.3.0-2.noarch.rpm

First Steps

2008-05-06T05:24:00.000-07:00

I have set a unix password for postgres, and started /etc/init.d/postgresql

su - postgres
made a directory called: '/var/lib/pgsql/cddb_data' owned by postgres

run psql

CREATE USER timj WITH PASSWORD 'xxxxxxxx' ;
CREATE TABLESPACE ts_cddb_1 OWNER timj LOCATION '/var/lib/pgsql/cddb_data';
CREATE DATABASE cddb OWNER timj TABLESPACE ts_cddb_1;

This gives me all I need to pgAdminIII, and I have completed objectives 1, 2 and 3!

Things to achieve

2008-05-06T03:23:00.000-07:00

Here is the first cut of the things I want to achieve. The database will store CD details. I want to:

create a tablespace
create a database
create a user to own the database
create a role for the user to be in
create a table for publisher
create a table for artist
create a table for CDs, referential integrity against artist and publisher
create a table for tracks, referential integrity against CD
create a read-only user
write backup scripts for the database
ensure that deleting a CD deletes all tracks
ensure that deleting an artist deletes all CDs and tracks
write a report to report in number of CDs owned by artist
create data load scripts for publisher, artist and CDs

Learning Postgresql

2008-05-06T02:12:00.000-07:00

Having previously worked as an Oracle developer and an Oracle DBA, but fallen away from the hand's on technical stuff, I have decided to teach myself Postgresql. I have decided that the best way to do this is to set up a series of tacks to achieve, and to blog my attempts to achieve them. I appreciate that this may not be of wide interest to anyone else, but it will at least keep the details in one place.

I expect that the targets will change as I find out more, so I expect to have to restate the targets as I go along. Hopefully I will be able to spend 30 minutes a day on this.

Tim