On Thu, Jan 16, 2003 at 10:40:39AM +0100, paul POULAIN said:

Marco Gaiarin a ?crit:

...

I hope that koha will be, sooner or later, a full db-indipendent project, so i can use my favourite DBMS, Postgres, but at the same time, and with more strenght ;), the DB schema have to be rewritten to be fully normalized and relational. [could just be this, i've simple not seen FOREIGN KEY statement in .sql file, i don't want to flame ;) ]

i've bad news for you Marco...

we have decided recently to go ... to a deepest mySQL dependency. mySQL has a feature that doesn't exist in ANSI-SQL92 : full-text indexing. It's a VERY usefull feature to seach into a biblio : you search "sun moon" and the db auto-search for title that could correspond. It's a VERY complicated thing to do manually *and* with indexed search. (see : http://www.mysql.com/doc/en/Fulltext_Search.html)

Hi There Just to throw in my 2 cents worth, i dont see using mysql fulltext searching as ruling out db independence. It just forces us to code more carefully, perhaps setting a value in /etc/koha.conf usefulltext=yes or the like. Which we can then use to decide whether to make a standard like search, or to use a fulltext index. I think database independence is a laudible aim, but id hate to see it rule out potential optimisations also. There is a always a tradeoff and its a matter of balance. Chris -- Chris Cormack Programmer 025 500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz

On Thu, Jan 16, 2003 at 11:02:43AM +0100, paul POULAIN said:

Chris Cormack a ?crit:

...

Hi There

Just to throw in my 2 cents worth, i dont see using mysql fulltext searching as ruling out db independence. It just forces us to code more carefully, perhaps setting a value in /etc/koha.conf usefulltext=yes or the like. Which we can then use to decide whether to make a standard like search, or to use a fulltext index.

BUT : the standard like search is UNUSEABLE on a 50 000+ biblio DB, as every search means a full parsing of the table. If you add the same thing for the thesaurus table, which is widely used in marc cataloguing, and some other place where full text indexing will be usefull...

Hi Again paul :) Well maybe not 50,000 .. but 500,000+ ... its working ok for HLT's 78709 biblios. But I take your point, and agree wholeheartedly the current search code badly needs a rewrite and lots of optimisation. And im sure HLT wouldnt say no to any speed increase :-) I think what i was trying to say (bear in mind its getting late here, and my mind is probably muddled :-)) was that we should be able to provide the option for other people to use a database of their choice. Without restricting those who want to from using mysql fulltext indexing. On the topic of fulltext searching we had a discussion about it on irc today. I cant currently get to the irc logs but when i can, ill mark the discussion and post the url to the devel list. Chris -- Chris Cormack Programmer 025 500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz

Marco Gaiarin a écrit:

...

we have decided recently to go ... to a deepest mySQL dependency. mySQL has a feature that doesn't exist in ANSI-SQL92 : full-text indexing.

http://www.postgresql.org/idocs/index.php?functions-matching.html#FUNCTIONS-...

No, but pg, as often in GNU programs, have regexp. ;)

OK, but can pg use indexes on select with regexp ? Because the problem is not on "can it be done", but "can it be done FAST", to avoid a 5-10 seconds waiting time on each biblio select !

I think it is not the case to fight against one or other DBMS, simply wrote down a correct relational database schema in normal form, and put inside as standard as possible SQL instructions, for the rest using specialized instructions case()ing on the DBMS type.

I agree with you for the schema in a normal form. Let's release a stable 2.0 and improve this aspect (with many others...) -- Paul POULAIN Consultant indépendant en logiciels libres responsable francophone de koha (SIGB libre http://www.koha-fr.org)