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
}