Subject Re: Google-like scoring in databases
Author Roman Rokytskyy
> Let me try some candidate answers.
> First, yes, you definitely need a free text search capability in
> Firebird. Don't know how you got this far without it.

What about geo-spatial capabilities (at least I need them more than
free text search)? Should we put text, spatial, audio and video
fetures into the server?

> Second, can be be a plug-in, or should it be an integral part of the
> engine? Part of the engine for the following reasons:
> 1. Somebody needs to keep track of which fields are searchable,
> i.e. word-indexed.

- plugin introduces new RDB$SEARCHABLE_FIELDS;
- plugin extends SQL with some new keywords;

> 2. Somebody needs to maintain the word index for all insert,
> updates, and deletes of records that contain searchable fields.

- auto-generated trigger
- plugin extends SQL processing

> 3. Somebody needs to map between table and fields names on
> numeric ids (or pay a ghastly penalty for failure to do so).

Search engine takes care of it, table name and field name is input for
it, what it does with it internally, is its own problem.

> 4. Somebody needs to be able to fetch records quickly by table
> id and record number.

Why cannot we use rdb$db_key? Within one table it seems to be unique.

> 5. The DML needs an extension to search explicit table fields
> has part of the boolean, which requires integration with the
> optimizer

Make optimizer plugin-enabled.

> 6. The native APIs and associated plumbing needs to be extended
> to support search semantics.

Why cannot we use SQL here? i know that you do not accept this idea,
and Netfrastructure uses JDBC extension instead. But I think, the only
thing that has to be transferred to the client in addition to the data
in the query is score, but why cannot we introduce CURRENT_SCORE

Best regards,
Roman Rokytskyy