Forum OpenACS Q&A: Contributing: OpenFTS search Bboard with OpenaCS 4.5

Hope this code could be util to someone.

Appears to search correctly.

The key to solve the implementation appears to be the line:
CASE WHEN c.mime_type='text/plain; format=flowed' THEN 'text/plain'

Let us know if the implementation is correct, could be improved or made us any suggestions, please.

--------------
The SQL file:
--------------

--
-- packages/bboard/sql/postgresql/bboard-sc-create.sql
--
-- @author Diego Manilla Suárez <mailto:diegoman@usuarios.retecal.es>
-- @creation-date 2003-01-20

select acs_sc_impl__new(
          'FtsContentProvider',                -- impl_contract_name
          'bboard_message',                    -- impl_name
          'bboard'                            -- impl_owner_name
);

select acs_sc_impl_alias__new(
          'FtsContentProvider',                -- impl_contract_name
          'bboard_message',                    -- impl_name
          'datasource',                        -- impl_operation_name
          'bboard__datasource',                -- impl_alias
          'TCL'                                -- impl_pl
);

select acs_sc_impl_alias__new(
          'FtsContentProvider',                -- impl_contract_name
          'bboard_message',                    -- impl_name
          'url',                              -- impl_operation_name
          'bboard__url',                      -- impl_alias
          'TCL'                                -- impl_pl
);

create function bboard__itrg ()
returns opaque as '
begin
    perform search_observer__enqueue(new.message_id,''INSERT'');
    return new;
end;' language 'plpgsql';

create function bboard__dtrg ()
returns opaque as '
begin
    perform search_observer__enqueue(old.message_id,''DELETE'');
    return old;
end;' language 'plpgsql';

create function bboard__utrg ()
returns opaque as '
begin
    perform search_observer__enqueue(old.message_id,''UPDATE'');
    return old;
end;' language 'plpgsql';

create trigger bboard__itrg after insert on bboard_messages
for each row execute procedure bboard__itrg ();

create trigger bboard__dtrg after delete on bboard_messages
for each row execute procedure bboard__dtrg ();

create trigger bboard__utrg after update on bboard_messages
for each row execute procedure bboard__utrg ();
--------------

And the TCL file:

------------------
bboard-sc-procs.tcl
-------------------

ad_library {

    Search OpenFTS-BBoard
    bboard-sc-procs.tcl
    @creation-date 2003-01-20
    @author Diego Manilla Suárez <mailto:diegoman@usuarios.retecal.es>

}


ad_proc bboard__datasource {
        object_id
} {
        @author Diego Manilla Suárez <mailto:diegoman@usuarios.retecal.es>
        @creation-date 2003-01-20
} {
        db_0or1row bboard_datasource {
        SELECT
                b.message_id as object_id,
                c.title as title,
                c.content as content,
        CASE WHEN c.mime_type='text/plain; format=flowed' THEN 'text/plain'
        ELSE c.mime_type END as mime,
                '' as keywords,
                'text' as storage_type
                FROM
                bboard_forum_message_map b,
                cr_revisions c
                WHERE
                b.message_id = c.item_id
                AND
                item_id = :object_id
                ORDER BY
                b.message_id DESC
                LIMIT ALL
  } -column_array datasource

  return [array get datasource]
}

ad_proc bboard__url {
        object_id
} {
        @author Diego Manilla Suárez <mailto:diegoman@usuarios.retecal.es>
        @creation-date 2003-01-20
} {
    db_1row get_forum_id "
        SELECT
        forum_id
        FROM
        bboard_forum_message_map
        WHERE
        message_id=:object_id
    "

        set package_id [apm_package_id_from_key bboard]

        db_1row get_url_stub "
                SELECT
                site_node__url(node_id) as url_stub
                FROM
                site_nodes
                WHERE
                object_id = :package_id
        "

        set url "${url_stub}message?message_id=$object_id&forum_id=$forum_id"

    return $url
}