RE: [ oracle texte] migration oracle vers postgresql

Volia je que ca donne comme migration coté oracle et cote postgres voir ci dessous

cote oracle
set serveroutput on size 1000000
set verify on
set echo on;
set feedback on;
set heading on;
drop table base_table;
drop table another_table1;
drop table another_table2;
drop index CRUMETEST.YOUR_INDEX;
exec ctx_ddl.drop_preference('single_ds');
create table base_table (id number, column1 varchar2 (20), column2 varchar2 (20), column3_id varchar2 (20), alltext varchar2 ( 1));
create table another_table1 (id number, column1 varchar2 (20));
create table another_table2 (id number, column1 varchar2 (20), column2 varchar2 (20));

insert into base_table values (1, 'btcol1 row1 TEST1', 'btcol2 row1', 'btcol3 row1', null);
insert into base_table values (2, 'btcol1 row2', 'btcol2 row2', 'btcol3 row2', null);
insert into base_table values (3, 'btcol1 row3', 'btcol2 row3', 'btcol3 row3', null);
insert into base_table values (4, 'btcol1 row4', 'btcol2 row4', 'btcol3 row4', null);

insert into another_table1 values (1, 'at1col1 row1');
insert into another_table1 values (2, 'at1col1 row2 TEST2');
insert into another_table1 values (3, 'at1col1 row3');
insert into another_table1 values (4, 'at1col1 row4');

insert into another_table2 values (1, 'at2col1 row1', 'at2col2 row1');
insert into another_table2 values (2, 'at2col1 row2', 'at2col2 row2');
insert into another_table2 values (3, 'at2col1 row3', 'at2col2 row3 TEST3');
insert into another_table2 values (4, 'at2col1 row4', 'at2col2 row4');

create or replace procedure base_table_datastore
(rid in rowid,
alltext in out nocopy clob)
is
begin
for c1 in
(select * from base_table where rowid = rid)
loop
dbms_lob.writeappend (alltext, length (c1.column1), c1.column1);
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c1.column2), c1.column2);
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c1.column3_ID), c1.column3_ID);
for c2 in
(select * from another_table1 where id = c1.id)
loop
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c2.column1), c2.column1);
end loop;
for c3 in
(select * from another_table2 where id = c1.id)
loop
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c3.column1), c3.column1);
dbms_lob.writeappend (alltext, 1, ' ');
dbms_lob.writeappend (alltext, length (c3.column2), c3.column2);
end loop;
end loop;
end base_table_datastore;
/
show errors;

set serveroutput on
declare
v_clob clob;
begin
dbms_output.put_line ('------------------------------------------');
for r in
(select rowid from base_table)
loop
dbms_lob.createtemporary (v_clob, true);
base_table_datastore (r.rowid, v_clob);
dbms_output.put_line (v_clob);
dbms_output.put_line ('------------------------------------------');
dbms_lob.freetemporary (v_clob);
end loop;
end;
/

begin
ctx_ddl.create_preference ('single_ds', 'user_datastore');
ctx_ddl.set_attribute ('single_ds', 'procedure', 'base_table_datastore');
ctx_ddl.set_attribute ('single_ds', 'output_type', 'clob');
end;
/

create index your_index on base_table (alltext) indextype is ctxsys.context parameters ('datastore single_ds sync (on commit)');

select token_text from dr$your_index$i;
select id from base_table where contains (alltext, 'test1') > 0;
select id from base_table where contains (alltext, 'test2') > 0;
select id from base_table where contains (alltext, 'test3') > 0;

coté postgresql

drop index idx_base_table_column1_full_text;
drop index idx_another_table1_column1_full_text;
drop index idx_another_table2_column2_full_text;
drop view search_items;
drop table base_table;
drop table another_table1;
drop table another_table2;

create table base_table (id integer , column1 varchar(20), column2 varchar(20), column3_id varchar(20), alltext varchar( 1));
create table another_table1 (id integer, column1 varchar(20));
create table another_table2 (id integer, column1 varchar(20), column2 varchar(20));

insert into base_table values (1, 'btcol1 row1 TEST1', 'btcol2 row1', 'btcol3 row1', null);
insert into base_table values (2, 'btcol1 row2', 'btcol2 row2', 'btcol3 row2', null);
insert into base_table values (3, 'btcol1 row3', 'btcol2 row3', 'btcol3 row3', null);
insert into base_table values (4, 'btcol1 row4', 'btcol2 row4', 'btcol3 row4', null);

insert into another_table1 values (1, 'at1col1 row1');
insert into another_table1 values (2, 'at1col1 row2 TEST2');
insert into another_table1 values (3, 'at1col1 row3');
insert into another_table1 values (4, 'at1col1 row4');

insert into another_table2 values (1, 'at2col1 row1', 'at2col2 row1');
insert into another_table2 values (2, 'at2col1 row2', 'at2col2 row2');
insert into another_table2 values (3, 'at2col1 row3', 'at2col2 row3 TEST3');
insert into another_table2 values (4, 'at2col1 row4', 'at2col2 row4');

CREATE INDEX idx_base_table_column1_full_text
ON base_table
USING GIN (to_tsvector('english', column1)) ;

CREATE INDEX idx_another_table1_column1_full_text
ON another_table1
USING GIN (to_tsvector('english', column1)) ;

CREATE INDEX idx_another_table2_column2_full_text
ON another_table2
USING GIN (to_tsvector('english', column2)) ;

CREATE VIEW search_items AS
SELECT id, text 'base_table' AS origin_table, column1 AS column1, to_tsvector('english', column1) AS searchable_element FROM base_table
UNION ALL
SELECT id, text 'another_table1' AS origin_table, column1 AS column1, to_tsvector('english', column1) AS searchable_element1 FROM another_table1
UNION ALL
SELECT id, text 'another_table2' AS origin_table, column2 AS column2, to_tsvector('english', column2) AS searchable_element2 FROM another_table2 ;

SELECT id FROM search_items WHERE plainto_tsquery('english', 'test2') @@ searchable_element;
SELECT id FROM search_items WHERE plainto_tsquery('english', 'test1') @@ searchable_element;
SELECT id FROM search_items WHERE plainto_tsquery('english', 'test3') @@ searchable_element;

________________________________
De : CRUMEYROLLE Pierre
Envoyé : mercredi 26 janvier 2022 11:25
À : pgsql-fr-generale(at)postgresql(dot)org <pgsql-fr-generale(at)postgresql(dot)org>
Objet : [ oracle texte] migration oracle vers postgresql

Bonjour
Quelqu'un a-t-il des pistes pour migrer de l'oracle text vers PostgreSQL ? C'est faisable ?

Ci-dessous un aperçu de l'existant oracle (un peu usine à gaz)

Cordialement

create or replace procedure PR_ITEM_FEEDER
(r in rowid, c in out nocopy clob)
as
begin
for x in (select D.CONCLUSION, S.DESC_REPH, S.BEST_PRAC, S.ASSO_DOC,
S.MIT_ACT, S.ROOT_CAUSE, S.FINAL_SOL, S.SC_DEC, S.MAJ_STAT,
I.DESCRIPTION, I.TITLE, I.REFERENCE
from ITEM I
left join DETAIL D on D.ID = I.DETAIL_FK
left join SUMMARY S on S.ID = I.PUBLISHED_SUMMARY_FK
where I.ROWID = r)
loop
if x.CONCLUSION is not null then
dbms_lob.writeappend(c, length(x.CONCLUSION), x.CONCLUSION);
dbms_lob.writeappend(c, 1, ' ');
end if;
if x.DESC_REPH is not null then
dbms_lob.writeappend(c, length(x.DESC_REPH), x.DESC_REPH);
dbms_lob.writeappend(c, 1, ' ');
end if;
if x.BEST_PRAC is not null then
dbms_lob.writeappend(c, length(x.BEST_PRAC), x.BEST_PRAC);
dbms_lob.writeappend(c, 1, ' ');
end if;
if x.ASSO_DOC is not null then
dbms_lob.writeappend(c, length(x.ASSO_DOC), x.ASSO_DOC);
dbms_lob.writeappend(c, 1, ' ');
end if;
if x.MIT_ACT is not null then
dbms_lob.writeappend(c, length(x.MIT_ACT), x.MIT_ACT);
dbms_lob.writeappend(c, 1, ' ');
end if;
if x.ROOT_CAUSE is not null then
dbms_lob.writeappend(c, length(x.ROOT_CAUSE), x.ROOT_CAUSE);
dbms_lob.writeappend(c, 1, ' ');
end if;
if x.FINAL_SOL is not null then
dbms_lob.writeappend(c, length(x.FINAL_SOL), x.FINAL_SOL);
dbms_lob.writeappend(c, 1, ' ');
end if;
if x.SC_DEC is not null then
dbms_lob.writeappend(c, length(x.SC_DEC), x.SC_DEC);
dbms_lob.writeappend(c, 1, ' ');
end if;
if x.MAJ_STAT is not null then
dbms_lob.writeappend(c, length(x.MAJ_STAT), x.MAJ_STAT);
dbms_lob.writeappend(c, 1, ' ');
end if;
dbms_lob.writeappend(c, length(x.TITLE), x.TITLE);
dbms_lob.writeappend(c, 1, ' ');
dbms_lob.writeappend(c, length(x.DESCRIPTION), x.DESCRIPTION);
dbms_lob.writeappend(c, 1, ' ');
dbms_lob.writeappend(c, length(x.REFERENCE), x.REFERENCE);
dbms_lob.writeappend(c, 1, ' ');
end loop;
end;
/
GRANT ALL ON PR_ITEM_FEEDER TO D220_FAIR_SERVICE;
/

create or replace trigger TR_UPDATE_TEXT_ITEM
before update of DESCRIPTION, REFERENCE, TITLE on ITEM
for each row
begin
:new.TEXT_INDEX := :old.TEXT_INDEX;
end;
/
show errors trigger TR_UPDATE_TEXT_ITEM;
/

create or replace trigger TR_UPDATE_TEXT_DETAIL
before update of CONCLUSION on DETAIL
for each row
begin
update ITEM set TEXT_INDEX=NULL where DETAIL_FK=:old.ID;
end;
/
show errors trigger TR_UPDATE_TEXT_DETAIL;
/

create or replace trigger TR_UPDATE_TEXT_SUMMARY
before update of DESC_REPH, BEST_PRAC, ASSO_DOC,
MIT_ACT, ROOT_CAUSE, FINAL_SOL, SC_DEC, MAJ_STAT on SUMMARY
for each row
begin
update ITEM set TEXT_INDEX=NULL where PUBLISHED_SUMMARY_FK=:old.ID;
end;
/
show errors trigger TR_UPDATE_TEXT_SUMMARY;
/

begin
ctx_ddl.create_preference('itemdatastore', 'user_datastore');
ctx_ddl.set_attribute('itemdatastore', 'procedure', 'PR_ITEM_FEEDER');
ctx_ddl.set_attribute('itemdatastore', 'output_type', 'CLOB');
end;
/

begin
ctx_ddl.create_preference('fairstorage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('fairstorage', 'I_TABLE_CLAUSE',
'tablespace D220_ISP_TSDATA_CTX lob (token_info) store as (tablespace D220_ISP_TSLOB_CTX)');
ctx_ddl.set_attribute('fairstorage', 'K_TABLE_CLAUSE',
'tablespace D220_ISP_TSDATA_CTX');
ctx_ddl.set_attribute('fairstorage', 'R_TABLE_CLAUSE',
'tablespace D220_ISP_TSDATA_CTX lob (data) store as (tablespace D220_ISP_TSLOB_CTX disable storage in row cache)');
ctx_ddl.set_attribute('fairstorage', 'N_TABLE_CLAUSE',
'tablespace D220_ISP_TSDATA_CTX');
ctx_ddl.set_attribute('fairstorage', 'I_INDEX_CLAUSE',
'tablespace D220_ISP_TSINDEX_CTX compress 2');
ctx_ddl.set_attribute('fairstorage', 'P_TABLE_CLAUSE',
'tablespace D220_ISP_TSDATA_CTX');
end;
/

begin
ctx_ddl.create_preference('fairwordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('fairwordlist', 'substring_index', 'TRUE');
end;
/

create index IX_ITEM on ITEM(TEXT_INDEX)
indextype is ctxsys.context
parameters ('datastore itemdatastore
storage fairstorage
wordlist fairwordlist
sync (on commit)');
/

create index IX_COMMENTS on COMMENTS(CONTENT)
indextype is ctxsys.context
parameters ('storage fairstorage
wordlist fairwordlist
sync (on commit)');
/

create index IX_ASSESSMENT on ASSESSMENT(CONTENT)
indextype is ctxsys.context
parameters ('storage fairstorage
wordlist fairwordlist
sync (on commit)');
/

create index IX_VOTE on VOTE(CONTENT)
indextype is ctxsys.context
parameters ('storage fairstorage
wordlist fairwordlist
sync (on commit)');
/

create index IX_FEEDBACK on FEEDBACK(CONTENT)
indextype is ctxsys.context
parameters ('storage fairstorage
wordlist fairwordlist
sync (on commit)');
/