[Koha-devel] Re: [Koha] latest on PostgreSQL

[Andrew Arensburger]

On Mon, Oct 07, 2002 at 10:48:39AM +0200, Ervin Peters wrote:
> But i think we should better discuss either to get it db- independent by 
> using only Ansi SQL or to specify the dbs in koha.conf, like 'dbs = 
> [mysql|pgsql|oracle|sap|ansiSQL]'

	The current version in CVS already allows you to do this: use
the (undocumented) "db_scheme" configuration option in /etc/koha.conf:

	db_scheme = Pg

The value on the right is (currently) passed directly to DBI->connect,
so make sure it's the proper value according to the DBD::Pg manual.
(Now you understand why this is undocumented: it should be possible to
say "db_scheme = PostgreSQL".)

	Just in passing: does anyone have any better suggestions for
this configuration option than "db_scheme"? Perhaps "db_driver"?

> Another possibility would be to move all dbs code in one modul and change 
> this modul by selecting the dbs...

	IMHO this would be a good idea. I don't think it'll ever be
possible to move all SQL statements into a single low-level module,
but it could still help.
	What sorts of things should go into this low-level module,
though? What should the interface look like, to be useful? I can
imagine the following:

	use KDB;	# Koha database interface module.

	($author, $title) = KDB->get_fields_by_key(
				"biblio",	# Table
				"biblionumber",	# Field to search
				12345,		# Value to search for
				"author", "title"); # Fields to return

	@branches = KDB->list_fields(
				"branches"	# Table
				"branchname");	# Field to return

	KDB->replace(
		"biblio",		# Table to update
		"biblionumber" => 12345,# Which record to update
		# Fields to update
		author => "Twain, Mark",
		seriestitle => undef);

		# KDB->replace might simply be portable, and use
		#	select ? update : insert
		# or it can be optimized for MySQL, but this can be
		# done transparently, without affecting the rest of
		# the code.

The question is, what should the KDB API look like? It should be
useful, and it ought to be more convenient than the SQL calls that are
currently in the code. Should there be STL-like iterators, so that you
can write
	$iterator = KDB->list_rows("biblio");
	while (%record = KDB->next($iterator))
	{
		print "$record{title} by $record{author}\n";
	}
? Should it try to be generic enough that the scripts don't have to
assume SQL? Should it use AUTOLOAD so that it can figure out what to
do from the function name, so that you can write
	%borrower = KDB->find_borrower_by_cardnumber("V10000008");
			# find_<table>_by_<field>
?

-- 
Andrew Arensburger                      This message *does* represent the
arensb at ooblick.com                      views of ooblick.com
		     <PROGRAM> ::= Do What I Want