[Pagekit-devel] contribute: pkit_setup_db.pl, a more generic pkit_setup_sqlite_dbfile.pl

[<pdu...@so...>]

Hi,

I renamed and modified Boris Zentner's pkit_setup_sqlite_dbfile.pl script,
in order to be db driver independent. For more details, please
read the contained pod-part of the script below. Tests were successful,
using the SQLite and Pg DBD drivers. No other drivers were tested,
but there should be no need to test others ;-).

pkit_setup_db.pl is a contribution and may be added to the project
(and changed), if it is useful.

=== Start of pkit_setup_db.pl ===
#!/usr/bin/perl -w
# $Id: $

use strict;
use warnings FATAL => 'all';
use DBI;


my @driver_names = DBI->available_drivers();
$#driver_names > 0 ||	die "You have NO drivers available!\n" .
	"Please install at least DBD::SQLite or others from CPAN.\n";

if ($#ARGV != 3) {
	my $usage = "Usage: $0 driver dbName userName auth\n" .
		"For the example site, use the following:\n" .
		"$0 SQLite dbfile \"\" \"\"\n";
	if ($#driver_names >= 0) {
		$usage .= "You have the following installed drivers available:\n";
		foreach my $driver (@driver_names) {
			$usage .= "$driver ";
		}
		$usage .= "\n";
	}
	else {
		$usage .= "You have NO drivers available!\n" .
			"Please install at least DBD::SQLite or others from CPAN.\n";
	}
	die $usage;
}

my $driver = $ARGV[0];
my $data_source = $ARGV[1];
my $username = $ARGV[2];
my $auth = $ARGV[3];

if (!grep(/^$driver$/, @driver_names)) {
	my $msg .= "Driver '$driver' is not in the " .
		"list of installed drivers!\n" .
		"You have the following installed drivers available:\n";
	foreach my $driver (@driver_names) {
		$msg .= "$driver ";
	}
	$msg .= "\n";
	die $msg;
}

# The following has a flaw. See bugs, below.
-e $data_source and die <<"EXISTS_ALREADY";
File, '$data_source', exists already.
For security reason we do not overwrite it.
EXISTS_ALREADY

my $dbh = DBI->connect( "dbi:$driver:dbname=$data_source", $username, $auth, { AutoCommit => 1, PrintError => 1, RaiseError => 0 } )
  or die $DBI::errstr;

$dbh->do(
  q{
    CREATE TABLE pkit_user (
      user_id CHAR(8), login CHAR(255), email CHAR(255), passwd CHAR(255)
    )}
) or die $DBI::errstr;

$dbh->do(
  q{ 
    CREATE TABLE sessions (
      id char(32) not null primary key,
      a_session text
    )}
) or die $DBI::errstr;
$dbh->disconnect;

=pod

=head1 NAME

pkit_setup_db.pl

=head1 SYNOPSIS

This script creates the tables, pkit_user and sessions in the specified
database.

=head1 REQUIREMENTS

A DBD driver that matches the specified database. In the case of the
Apache::PageKit example, DBD::SQLite is required.

=head1 USAGE

pkit_setup_db.pl driver dbName userName auth

=over

=item -

C<driver> is the name of the DBD driver that matches the database
being used.

=item -

C<dbName> is the name of the database. When using file-based
databases, as with for instance SQLite, it will be the file name.

=item -

C<userName> is the user name that will be used to login and create
the tables in the database.

=item -

C<auth> is the authentication string needed to log into the database.

=back

=head1 DESCRIPTION

The script logs into the specified database using the specified
user name and authentication string. It then creates the tables
pkit_user and sessions.


CREATE TABLE pkit_user (
	user_id CHAR(8), login CHAR(255), email CHAR(255), passwd CHAR(255)
)

CREATE TABLE sessions (
      id char(32) not null primary key,
      a_session text
)


This may be useful for starting an application, using Apache::PageKit.

=head1 EXAMPLES

  pkit_setup_db.pl dbfile "" ""
  pkit_setup_db.pl pagekit "" ""
  pkit_setup_db.pl pagekit pageusr 'Q#Se$Re;w'

=head1 BUGS

The script has at least one flaw, in that if a dbName is the name of
a non-file-based database (like Postgres) _and_ a file of that name
exists, the script stops. The problem is that I don't know how to check
it, and I don't want to overwrite the file. The work-around will be to
cd to somewhere, where no such a file exists, or mv the file that
has the same name as dbName.

=head1 NOTES

For the Apache::PageKit demo, the SQLite driver must be used.
The script will produce the database file in the cwd.

=head1 CREDITS

  Boris Zentner <bz...@2b...>, for the initial script,
  pkit_setup_sqlite_dbfile.pl, where this script was copied
  and modified from.

=head1 AUTHOR

  Pieter du Preez <pdu...@so...>

=== End of pkit_setup_db.pl ===

cu
--
Pieter