I read in the archives of June/July that some work was being done of making koha work with postgres. What progress has been made? Richard Davis Communications Advisor and Webmaster Presbyterian Church of Aotearoa New Zealand PO Box 9049, Wellington, Aotearoa New Zealand Direct: +64 4 381 8285 Mobile: 027 404 8656 Phone: +64 4 801 6000 Fax: +64 4 801 6001 http://www.presbyterian.org.nz/
Hallo ! I'm not really involved in Koha developing yet, but I downloaded Koha 1.2.2 in August and got it run on postgres to about 90%. I've done this to get familiar with Perl, dbs + www and so on... There are many things to do to get the whole System work on postgres, or better: work Database independent. The basic SQL Database Script contains MySQL specific SQL, but can be easily converted to pgsql by mysql2pgsql. The updatedb and the web scripts are more problematic, since they embed many mysql specific. I went through that script, convert on the fly the sql terms an typed it in pgsql. You could say, I updated the db by hand. The next problems are the 'replace into' statments found in many scripts, they must be changed into if 'select [pkey] from ...' then 'update ...' else 'insert into ...' construction like it is done in some module functions. If I would find the time, I'd like to specify it more detailled, later on. 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]' Then any code can contain 'optimizations' for dbs by check the configuration: if dbs==mysql then 'replace into..' else if 'select ...from...' then 'update ...' else 'insert...' Another possibility would be to move all dbs code in one modul and change this modul by selecting the dbs... ervin Weimar, Germany --On Montag, 7. Oktober 2002 16:04 +1300 Richard Davis <RichardD@pcanz.org.nz> wrote:
I read in the archives of June/July that some work was being done of making koha work with postgres. What progress has been made?
Richard Davis Communications Advisor and Webmaster Presbyterian Church of Aotearoa New Zealand PO Box 9049, Wellington, Aotearoa New Zealand Direct: +64 4 381 8285 Mobile: 027 404 8656 Phone: +64 4 801 6000 Fax: +64 4 801 6001 http://www.presbyterian.org.nz/ _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- Ervin Peters 99423 Weimar
Mandi! Ervin Peters In chel di` si favelave...
I'm not really involved in Koha developing yet, but I downloaded Koha 1.2.2 in August and got it run on postgres to about 90%.
Please, continue working on!!!
Another possibility would be to move all dbs code in one modul and change this modul by selecting the dbs...
We have to undestand if non-standard SQL code si really a performance improving or simply some aid to programming. I doubt that some SQL engine could lead to really performance improving using a nonstandard construct, it will be simply a non serious SQL engine. ;) So your approach (rewrinting the SQL in standard one) i think is the better, the other glue can be achieved with DBI/DBD perl database interfaces. [and i hope will be integrated in future koha release...] -- dott. Marco Gaiarin GNUPG Key ID: 240A3D66 Associazione ``La Nostra Famiglia'' http://www.sv.lnf.it/ Polo FVG - Via della Bontà , 7 - 33078 - San Vito al Tagliamento (PN) gaio(at)sv.lnf.it tel +39-0434-842711 fax +39-0434-842797 NO al super tributo alla SIAE! http://www.edisport.it/edisport/afdigitale/petizione.nsf/main?openframeset
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@ooblick.com views of ooblick.com <PROGRAM> ::= Do What I Want
I think, no, i'm SURE, even if it's a good idea, it's NOT a good idea FOR INSTANCE : we are in the middle of the road to create a koha MARC-compatible. It's a BIG deal, and an IMPORTANT one for USERS. So, such a "developper" question must be delayed. I agree it could be a point to solve for 2.0 branch, which is planned when the 1.4.x becomes stable enough. One more reason : we make a big use of the "replace" SQL command, which is mySQL specific, but very useful... It "updates" or "insert" depending on wether the primary key exists or not. koha will get a chance to be DB independant only when no replace is found in the code... -- Paul
--On Montag, 7. Oktober 2002 08:48 -0400 Andrew Arensburger <arensb+koha-devel@ooblick.com> wrote:
The current version in CVS already allows you to do this: use db_scheme = Pg say "db_scheme = PostgreSQL".)
Just in passing: does anyone have any better suggestions for this configuration option than "db_scheme"? Perhaps "db_driver"? It depends on what this option should affect...
Another possibility would be to move all dbs code in one modul and change this modul by selecting the dbs... Then the above option would discribe Database System and the KohaDataHandlingModule: datahandling = [MySQL|PostgrSQL|...] What sorts of things should go into this low-level module, though? What should the interface look like, to be useful? I thougt extracting the datacollection and dataupdating of the webinterface-scripts and the put it in a seperate Module. 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 yes, it's a bit more generic than I thought of, I first would took the data collection and data manipulation and put in in seperate functions. Then I seperate it to a module, and look if I can generalize it by looking what similar functions are needed by other scripts. That would divide data presentation from datacollection and datamanipulating and allow to 'optimize' Datahandling for every Database System, e.g. also makes it possible to use plain textfiles or ODBC / Access on Windows Systems... It also makes it possible to change the Datastructures, e.g. by create views instead of tables in normalisation...
@branches = KDB->list_fields( "branches" # Table "branchname"); # Field to return The meaning of 'list_fields' goes more: 'Please, would you show me which fields are available at this table?' This would be nice to have generic Tableviews... It should be better:
The question is, what should the KDB API look like? It should contain the data functions of any Script and could contain the datacreation and updating funktions. In more specific form or (not XOR) more generalized form. It should be useful, I think it could be. and it ought to be more convenient than the SQL calls that are currently in the code. no, it wouldn't be more convenient since the database structure still has to be created, and the author still has to create the queryconstructs. The benefit would be that we avoid if (dbs==... elseif ...elseif ... or switch dbs...case codeblocks in every script. 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"; }
@branches = KDB->ShowData( "branches", "branchname", "condition" ); or more specialized: @branches = KDB->ShowBranchNames( "condition" ); this would be fine.
? Should it try to be generic enough that the scripts don't have to assume SQL? yes. The Userinterface(scripts) only need data in one desired form, that consist of perl-datatypes. Should it use AUTOLOAD what does that mean? 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> The name 'find_borrower_by_cardnumber' would implizize that there are borrowers which have an cardnumber. If it is a table 'borrowers' or a view containing data from 'Persons', 'feetsizes' and 'has a card from biblio' should be invisible, it is not necessary to create the webinterfacescripts...
greets ervin
-- Andrew Arensburger This message *does* represent the arensb@ooblick.com views of ooblick.com <PROGRAM> ::= Do What I Want
participants (5)
-
Andrew Arensburger -
Ervin Peters -
Marco Gaiarin -
paul POULAIN -
Richard Davis