// Use FTSTblCr.pkg // Free text search - Create FTS tables
Use FTSData.nui // cFTS_TableAccess class.
Use TblCreate // cTableCreator class
object oFTS_TableDefinitions is a cArray
property integer piArticleIdWidth public 0 // NUMERIC 6.0
property integer piPhraseLength public 0 // Minimum 6, maximum 14
object oTable_word is a cTableCreator
procedure OnDefine
set piMaxRecords to 1000000 // 1 million
send DoAddField "WORD_ID" DF_BCD 6
send DoAddField "WORD" DF_ASCII 30
send DoAddField "FREQUENCY" DF_BCD 8
send DoAddOnlineIndex 1 "word_id"
send DoAddOnlineIndex 2 "WORD" // Uppercase = Ignore case
end_procedure
end_object
object oTable_artwrd is a cTableCreator
procedure OnDefine
set piMaxRecords to 10000000 // 10 million
send DoAddField "ARTICLE_ID" DF_BCD (piArticleIdWidth(self))
send DoAddField "WORD_ID" DF_BCD 6
send DoAddOnlineIndex 1 "article_id word_id"
send DoAddOnlineIndex 2 "word_id article_id"
end_procedure
end_object
object oTable_artphr is a cTableCreator
procedure OnDefine
integer liPhrLen liPhrPos
string lsIndex1 lsIndex2 lsFieldName
set piMaxRecords to 16700000 // 16.7 million
move "article_id" to lsIndex1
move "" to lsIndex2
get piPhraseLength to liPhrLen
send DoAddField "ARTICLE_ID" DF_BCD (piArticleIdWidth(self))
for liPhrPos from 1 to liPhrLen
move "WORD_ID" to lsFieldName
if (liPhrPos<>1) move (lsFieldName+"_S"+string(liPhrPos-1)) to lsFieldName
send DoAddField lsFieldName DF_BCD 6
move (lsIndex1*lsFieldName) to lsIndex1
move (lsIndex2*lsFieldName) to lsIndex2
loop
move (lsIndex1*"recnum") to lsIndex1
move (lsIndex2*"recnum") to lsIndex2
send DoAddOnlineIndex 1 (lowercase(trim(lsIndex1)))
send DoAddOnlineIndex 2 (lowercase(trim(lsIndex2)))
// send DoAddField "WORD_ID" DF_BCD 6
// send DoAddField "WORD_ID_S1" DF_BCD 6
// send DoAddField "WORD_ID_S2" DF_BCD 6
// send DoAddField "WORD_ID_S3" DF_BCD 6
// send DoAddField "WORD_ID_S4" DF_BCD 6
// send DoAddField "WORD_ID_S5" DF_BCD 6
// send DoAddOnlineIndex 1 "article_id word_id word_id_s1 word_id_s2 word_id_s3 word_id_s4 word_id_s5 recnum"
// send DoAddOnlineIndex 2 "word_id word_id_s1 word_id_s2 word_id_s3 word_id_s4 word_id_s5 recnum"
end_procedure
end_object
object oTable_articl is a cTableCreator
procedure OnDefine
set piMaxRecords to 1000000 // 1 million
send DoAddField "ARTICLE_ID" DF_BCD (piArticleIdWidth(self))
send DoAddField "DATE" DF_DATE 0
send DoAddField "TIME" DF_ASCII 8
send DoAddField "HIT_COUNT" DF_BCD 6
send DoAddField "ARTICLE_CLASS" DF_ASCII 20
send DoAddField "INDEXED_DATE" DF_DATE 0
send DoAddField "INDEXED_TIME" DF_ASCII 8
send DoAddOnlineIndex 1 "article_id"
end_procedure
end_object
object oTable_search is a cTableCreator
procedure OnDefine
set piMaxRecords to 1000000 // 1 million
send DoAddField "SEARCH_ID" DF_BCD 8 // 1
send DoAddField "USER_ID" DF_BCD 8 // 2
send DoAddField "SEARCH_STRING" DF_ASCII 80 // 3
send DoAddField "SEARCH_DATE" DF_DATE 0 // 4
send DoAddField "SEARCH_TIME" DF_ASCII 8 // 5
send DoAddField "RESULT_COUNT" DF_BCD 6 // 6
send DoAddField "EXECUTION_TIME" DF_BCD 8 // 7
send DoAddField "LOW_DATE_LIMIT" DF_DATE 0 // 8
send DoAddField "HIGH_DATE_LIMIT" DF_DATE 0 // 9
send DoAddField "ORDER_BY" DF_BCD 2 // 10
send DoAddField "ERROR_CODE" DF_BCD 2 // 11
send DoAddField "AUX_VALUE" DF_ASCII 20 // 12
send DoAddOnlineIndex 1 "search_id"
send DoAddOnlineIndex 2 "user_id search_id"
end_procedure
end_object
procedure DoCreateTables integer lhTableAccess integer liArticleIdWidth integer liPhraseLength
integer liFile lhAppDbTable lbSuccess
string lsRoot lsLogical lsDisplay
set piArticleIdWidth to liArticleIdWidth
set piPhraseLength to liPhraseLength
get piFile.i of lhTableAccess FTSTABLE_WORD to liFile
get psRootname.i of lhTableAccess FTSTABLE_WORD to lsRoot
get psLogicalName.i of lhTableAccess FTSTABLE_WORD to lsLogical
get psUserName.i of lhTableAccess FTSTABLE_WORD to lsDisplay
set FileListValues of oTable_word to liFile lsRoot lsLogical lsDisplay
send DoDefine of oTable_word
send CreateTable of oTable_word
get piFile.i of lhTableAccess FTSTABLE_ARTWRD to liFile
get psRootname.i of lhTableAccess FTSTABLE_ARTWRD to lsRoot
get psLogicalName.i of lhTableAccess FTSTABLE_ARTWRD to lsLogical
get psUserName.i of lhTableAccess FTSTABLE_ARTWRD to lsDisplay
set FileListValues of oTable_artwrd to liFile lsRoot lsLogical lsDisplay
send DoDefine of oTable_artwrd
send CreateTable of oTable_artwrd
get piFile.i of lhTableAccess FTSTABLE_ARTPHR to liFile
get psRootname.i of lhTableAccess FTSTABLE_ARTPHR to lsRoot
get psLogicalName.i of lhTableAccess FTSTABLE_ARTPHR to lsLogical
get psUserName.i of lhTableAccess FTSTABLE_ARTPHR to lsDisplay
set FileListValues of oTable_artphr to liFile lsRoot lsLogical lsDisplay
send DoDefine of oTable_artphr
send CreateTable of oTable_artphr
get piFile.i of lhTableAccess FTSTABLE_ARTICL to liFile
get psRootname.i of lhTableAccess FTSTABLE_ARTICL to lsRoot
get psLogicalName.i of lhTableAccess FTSTABLE_ARTICL to lsLogical
get psUserName.i of lhTableAccess FTSTABLE_ARTICL to lsDisplay
set FileListValues of oTable_articl to liFile lsRoot lsLogical lsDisplay
send DoDefine of oTable_articl
send CreateTable of oTable_articl
get piFile.i of lhTableAccess FTSTABLE_SEARCH to liFile
get psRootname.i of lhTableAccess FTSTABLE_SEARCH to lsRoot
get psLogicalName.i of lhTableAccess FTSTABLE_SEARCH to lsLogical
get psUserName.i of lhTableAccess FTSTABLE_SEARCH to lsDisplay
set FileListValues of oTable_search to liFile lsRoot lsLogical lsDisplay
send DoDefine of oTable_search
send CreateTable of oTable_search
end_procedure
end_object // oFTS_TableDefinitions
procedure FTS_CreateTables global integer lhTableAccess integer liArticleIdWidth integer lhPhraseLength
send DoCreateTables of oFTS_TableDefinitions lhTableAccess liArticleIdWidth lhPhraseLength
end_procedure