Define Onoff = Off
rem +-----------------------------------------------------------------+
rem | Script ID: doi.sql
rem |
rem | Purpose: Oracle Text Search on words from the Declaration
rem | of Independence. Added some dog words to test
rem | 'About'. No luck with 'About' or 'stem'.
rem |
rem | Developer: Lynn Tobias
rem | Script Date: 9/2/2008
rem | Oracle Ver: 10g
rem |
rem | Table(s) Used: DOI_Context and DOI_CTXCAT created in this query.
rem |
rem | Revisions: Dvl Date Ver Comment/Change
rem | --- -------- --- --------------------------------+
rem |
rem +-----------------------------------------------------------------+
Clear Screen
Set Echo &Onoff Feedback &Onoff Linesize 100 Term Off
Column Words Format A64 Word_Wrapped
Column Pid Format 99 Heading PId
--+--------------------------------------------------------------------------+
--| Drop the table before recreating it and inserting a few recs
--+--------------------------------------------------------------------------+
Drop Table Doi_Context Cascade Constraints Purge;
Drop Table Doi_Ctxcat Cascade Constraints Purge;
Create Table Doi_Context
( Pid Number(2)
, Words Varchar2(4000)
, Constraint Doi_Pk Primary Key (Pid)
);
Insert Into DOI_Context Values(1,'Governments Governed people LAWS abolishing GOVERNORS poodle');
Insert Into DOI_Context Values(2,'representation labrador justice people basset hound ');
Insert Into DOI_Context Values(3,'laws representative peace corgi representatives people');
Insert Into DOI_Context Values(4,'laws abolishing government german shepherd justice peace');
Commit;
--+--------------------------------------------------------------------------+
--| Create a 2nd similar table to test ctxcat index and Catsearch.
--| "You must create a primary key for table."
--+--------------------------------------------------------------------------+
Create Table Doi_Ctxcat
As Select *
From Doi_Context;
Alter Table Doi_Ctxcat Add Primary Key (Pid);
--+--------------------------------------------------------------------------+
--| This is my attempt to get the stem and 'about' to work. No luck yet.
--| The lexer parameter is copied from the internet.
--+--------------------------------------------------------------------------+
Begin
Ctx_Ddl.Create_Preference( 'MyLex', 'Basic_Lexer' );
Ctx_Ddl.Set_Attribute ( 'MyLex', 'Printjoins' , '_-' );
Ctx_Ddl.Set_Attribute ( 'MyLex', 'Index_Themes', 'yes');
Ctx_Ddl.Set_Attribute ( 'MyLex', 'Index_Text' , 'yes');
End;
/
Create Index Doi_Context_Index
On Doi_Context(Words)
Indextype Is Ctxsys.Context
Parameters ( 'Lexer MyLex' );
Create Index Doi_Ctxcat_Index
On Doi_Ctxcat(Words)
Indextype Is Ctxsys.Ctxcat;
--+--------------------------------------------------------------------------+
--| When the text is changed, the index is out of sync unless you execute
--| the sync_index statement -- or you include the sync parameter in the
--| create index.
--+--------------------------------------------------------------------------+
--Parameters('sync (On Commit)');
--execute CTX_DDL.Sync_index('doi_context_index');
--+--------------------------------------------------------------------------+
--| Show the indexes created
--+--------------------------------------------------------------------------+
Column Owner Format A6
Column Constraint_Name Format A17
Column Deferrable Format A10
Column Generated Format A9
Column Index_Name Format A17
Select *
From User_Constraints
Where Constraint_Name Like '%DOI%'
Or Table_Name Like '%DOI%';
--+--------------------------------------------------------------------------+
--| These gave identical results. Display all lines with 'people'
--+--------------------------------------------------------------------------+
Set Term On
Column Words Heading 'Contains(Words, people)'
Select *
From Doi_Context
Where Contains(Words, 'people') >0;
--Select * From Doi_Ctxcat Where Catsearch(Words, 'people', NULL) >0;
--+--------------------------------------------------------------------------+
--| Print any lines with both 'laws' and 'people' in it.
--+--------------------------------------------------------------------------+
Column Words Heading 'Contains(Words, laws AND people)'
Select Pid, Words
From Doi_Context
Where Contains(Words,'laws AND people')>0;
--Select Pid, Words From Doi_Ctxcat Where Catsearch(Words,'laws people', NULL)>0;
--+--------------------------------------------------------------------------+
--| Any line with one word or the other
--+--------------------------------------------------------------------------+
Column Words Heading 'Contains(Words,laws Or People)'
Select Pid, Words
From Doi_Context
Where Contains(Words,'laws Or People')>0;
--select Pid, words From DOI_Ctxcat where catsearch(words,'laws |people', NULL)>0;
--+--------------------------------------------------------------------------+
--| Any line with one word near the other
--+--------------------------------------------------------------------------+
Column Words Heading 'Contains(Words,laws Near People)'
Select Pid, Words, Score(10)
From Doi_Context
Where Contains(Words,'laws Near People',10)>0;
--+--------------------------------------------------------------------------+
--| Any line with first word but not second
--+--------------------------------------------------------------------------+
Column Words Heading 'Contains(Words,laws - People) Laws but not people'
Select Pid, Words
From Doi_Context
Where Contains(Words,'laws - People')>0;
--Select Pid, Words From Doi_Ctxcat Where Catsearch(Words,'laws - People', Null)>0;
--+--------------------------------------------------------------------------+
--| One of three words
--+--------------------------------------------------------------------------+
Column Words Heading 'Contains(Words,Government Or People Or Abolishing'
Select Pid, Words, Score(10)
From Doi_Context
Where Contains(Words,'Government Or People Or Abolishing',10)>0;
Column Words Heading 'Words Weighted: government*3 Or People*1 Or Abolishing*2'
Select Pid, Words,Score(10)
From Doi_Context
Where Contains(Words,'government*3 Or People*1 Or Abolishing*2',10)>3;
--+--------------------------------------------------------------------------+
--| % is wildcard. $ is stem search (not working)
--+--------------------------------------------------------------------------+
Column Words Heading 'Wildcards: (Words,represent%'
Select Pid, Words, Score(10)
From Doi_Context
Where Contains(Words,'represent%',10)>0;
--Select Pid, Words, Score(10)
--From Doi_Context
--Where Contains(Words,'$Represent',10)>0;
--+--------------------------------------------------------------------------+
--| Fuzzy Match: Justis -> Justice
--+--------------------------------------------------------------------------+
Column Words Heading 'Fuzzy Match: (Words,?Justis'
Select Pid, Words, Score(10)
From Doi_Context
Where Contains(Words,'?Justis',10)>0;
--+--------------------------------------------------------------------------+
--| Perform a Soundex Search
--+--------------------------------------------------------------------------+
Column Words Heading 'Soundex: !Piece'
Select Pid,Words, Score(10)
From Doi_Context
Where Contains(Words,'!Piece',10)>0;
--+--------------------------------------------------------------------------+
--| Topic Search - not working yet
--+--------------------------------------------------------------------------+
--Column Words Heading 'Topic Seach: About(Dog)
--Select Pid, Words, Score(10)
-- From Doi_Context
-- Where Contains(Words,'about(Dog)',10)>0;
How to get URL of commentator
-
We sometimes get questions about how to get the web address/URL of a
commentator in our blog. Blogger.com has not provided any way to do that.
However, the...
7 years ago
No comments:
Post a Comment