[SoC Proposal] Full Text Search support in PostgreSQL GUI Tools

Summary

There are many users (especially novice) asking about better and easier configuration of Full Text Search in PostgreSQL (O)RDBMS [1], which is quite complicated currently. Even new FTS features (e.g. much clearer configuration with SQL syntax) in forthcoming PostgreSQL 8.3 release won't help much for users that want simple "several clicks" setup of a small system with full text indexing. Adding support of nice user-friendly FTS configuration in popular PostgreSQL GUI administration tools [2,3] would help a lot to spread PostgreSQL among wide group of potential users. Also certain GUI features can be helpful even for experienced users, simplifying debug and configuration of amazingly flexible and powerful Full Text Search engine in PostgreSQL.

Deliverables

Implement following in phpPgAdmin and then in pgAdmin III (optional):

1. Interfaces to add/modify FTS configurations, dictionaries and tokens with their processing rules (with drop down menus to exclude typos and other useful features)
2. User-frienly visualisation of ts_debug() to simplify understanding of what processing happened with the input text and swtiches of configuration against which we tested the input
3. FTS operators in usual phpPgAdmin table select interface
4. Online query rewriting (special table to store rewrite rules and test if given rule works as expected); this is especially helpful because does not require reindexing
5. Interface to index statistics and heuristics to understand if its healthy [OPTIONAL]
6. User-friendly index creation (tsvector column + FTS index + trigger on parent text field)
7. Ability to check if FTS configured properly. This includes:
   • some knowledge contained in the interface to perform tests
   • several FTS core features like check if dictionary files exist and are accessible [OPTIONAL]
   • ability to reset configuration and re-parse all dictionary files
8. User actions generate on demand valid SQL suitable for copy-paste into other clients
9. Simple PHP interface for text searches and results visualisation with themes support to simplify building of FTS-enabled web-interfaces ("just copypaste this PHP code and one line of HTML form code to your site and switch on FTS on given table in phpPgAdmin"), possibly as standalone pgFoundry project [OPTIONAL]

Project Details

Basic idea of the work is to code all interfaces and debug ideas firstly in phpPgAdmin and then re-implement ready solutions in pgAdmin III. Having some PHP + PostgreSQL development background, I consider FTS support in phpPgAdmin more simple to start with. Moreover pgAdmin III has much steeper learning curve so it would be reasonable to test all interface prototypes before touching wxWidgets library (which is the base of pgAdmin III). One may argue that even interfaces of desktop application pgAdmin III differ a lot from web application phpPgAdmin. This is true, but is not important in many cases. After first stage of the work, there will be bunch of synchronous forms that will look exactly the same in desktop application. Then, there will be SQL (with FTS configuration changes) generator logic implemented which is quite abstract from application and interfaces. So in my opinion joining such a different GUI tools in one project can be reasonable. Such a sequential approach can save many time.

There will be no backward support of old (PostgreSQL 8.2 and older) tsearch2 configuration, since it almost doubles amount of the work to be done with only few benefits (was discussed in the maillists).

One of the advantage of this work would be independance from PostgreSQL 8.3 release that does not fit into Google SoC 2007 schedule. It means that one does not need to wait till next PostgreSQL release since SoC results can be released just after it finishes with nearest phpPgAdmin release.

Project Schedule

• End of March: propose FTS core features for dictionary files check (this does not belong to Google SoC but is helpful anyway and should be declared before 8.3 feature freeze)
• Beginning of June: study of FTS configuration in commercial databases
• End of June: alpha-version of phpPgAdmin functionality
• Middle of July: simple PHP interface for full text searches
• End of July: release phpPgAdmin patches in production quality, ready for release, with documentation
• End of August: release remaining pgAdmin III functionality I managed to develop

Benefits to PostgreSQL community

1. Complete FTS configuration support in phpPgAdmin
2. Initial FTS configuration support in pgAdmin III
3. Simple PHP interface for full text searches, indexing and results visualisation
4. FTS improvements suggestions

Future work

Finish FTS configuration support in pgAdmin III, help to other developers with implementations of FTS support in other PostgreSQL GUI tools.

About me

Name: Ivan Zolotukhin

Contact Information: iz remove this sai msu ru; ICQ: 287872951; Jabber: ivan.zolotukhin remove this gmail com; Skype: iz-sai

Occupation: PhD student at Moscow State University (MSU, http://www.msu.ru/en/), Moscow, Russia.

Scientific Mentor: Prof. Nikolai Shakura, Sternberg Astronomical Institute, Moscow, Russia (http://www.sai.msu.su/).

Interests: Astrophysics (low mass x-ray binaries) and Virtual Observatory (huge datasets and catalogs, web and database development for astronomical needs).

References

[1] FTS in PostgreSQL 8.3+ Documentation: http://mira.sai.msu.su/~megera/pgsql/ftsdoc/

[2] phpPgAdmin official page: http://phppgadmin.sourceforge.net/

[3] pgAdmin III official page: http://pgadmin.org/

This page was viewed 4018 times