Hello,
Thanks for helping with my primary key problem. I was able to start work
on a routine that generates the keyword table i will use for searching. I
don't see anything wrong with the table itself, but the following routine
eats up memory at an exponential rate. Anybody know of some methods I can
rewrite some of this code to make it so it's not holding on to memory?
--Routine to generate keywords table based on all artists in the catalog.
--David Torre
--10-16-2001
--last update 11-1-2001
DISCONNECT CDSTORE
CONNECT CDSTORE
SET FEEDBACK ON
SET ECHO OFF
SET MESS OFF
SET ERR MESS OFF
WRI 'Now creating artist_keywords
table ' AT 1 1
DROP TABLE artistkeys_ln NOCHECK
DROP TABLE artist_keywords NOCHECK
SET FASTFK ON
CREATE TABLE artist_keywords+
(+
artistkeys_id INTEGER NOT NULL UNIQUE,+
keyword TEXT 50 NOT NULL UNIQUE,+
PRIMARY KEY (artistkeys_id)
)
CREATE TABLE artistkeys_ln+
(+
catalog_id INTEGER,+
artistkeys_id INTEGER,+
FOREIGN INDEX (catalog_id) REFERENCES catalog (catalog_id),+
FOREIGN INDEX (artistkeys_id) REFERENCES artist_keywords (artistkeys_id)+
)
SET V vartistkeys_count INTEGER=1
SET V vartistkeys_id INTEGER=NULL
SET V vkeywordgrab TEXT=NULL
SET V vartist TEXT
SET V vtitle TEXT
SET V vkeywords TEXT
SET V vsingle_keyword TEXT
COMPUTE vcatalog_total AS ROWS FROM catalog
SET V vcatalog_total_txt TEXT=(' '+(CTXT(.vcatalog_total))+' items')
DROP CURSOR c1
DECLARE c1 SCROLL CURSOR FOR SELECT catalog_id,artist,title FROM catalog
OPEN c1
LABEL GetRowsFromCatlg
FETCH c1 INTO vcatalog_id INDICATOR vind1, vartist INDICATOR vind2,+
vtitle INDICATOR vind3
IF SQLCODE=100 THEN --If there's no more rows in the catalog
GOTO CleanUp --Stop Adding Rows to the artist_keywords table
ENDIF
--Feedback
SET V vcatalog_id_txt TEXT=('Status: '+(CTXT(.vcatalog_id))+' of')
WRI .vcatalog_id_txt AT 2 1
WRI .vcatalog_total_txt AT 3 1
--make a string that contains all the keywords for this item
SET V vkeywords TEXT=(ULC(SRPL((.vartist),' ',',',0)))
--Grab each keyword one by one and shove it in the artist_keywords table
LABEL AddKeywordsToTable
IF vkeywords IS NULL THEN
SET V vsingle_keyword=NULL
GOTO GetRowsFromCatlg
ENDIF
--extract a keyword from the keywords string
SET V vsingle_keyword TEXT=(SSUB(.vkeywords,1))
--now remove what was just extracted from the keywords string
SET V vkeywords_len=(SLEN(.vkeywords))
SET V vkeywords=(LJS((SRPL(.vkeywords,(.vsingle_keyword),' ',1)),+
.vkeywords_len))
SET V vfirst_char=(SGET(.vkeywords,1,1))
SET V vkeywords_len=(SLEN(.vkeywords))
SET V vsingle_keywd_len=(SLEN(.vsingle_keyword))
SET V vkeywords_isalpha=(ISALPHA(.vkeywords))
SET V vkeywords_isdigit=(ISDIGIT(.vkeywords))
IF vfirst_char=',' THEN
SET v vkeywords=(SGET(.vkeywords,(SLEN(.vkeywords)),2))
ENDIF
--When the last word is stripped from the string, I'm always left with a
--single character that looks like a space but isn't one. The only way
--I could remove it was to check if the string was one character and the
--character wasn't a letter or number. That is what the following
--statement does.
IF ((vkeywords_len=1) AND (vkeywords_isalpha=0 OR vkeywords_isdigit=0))+
THEN
SET V vkeywords=NULL
ENDIF
--Take care of zero length characters (these end up in artist_keywords table
--when there are duplicate keywords in the artist and/or title)
IF (vsingle_keywd_len=0) THEN
SET V vsingle_keyword=NULL
ENDIF
IF vsingle_keyword IS NOT NULL THEN
SET V vkeywordgrab=keyword from artist_keywords where
keyword=.vsingle_keyword
SET V vartistkeys_id=artistkeys_id from artist_keywords where
keyword=.vsingle_keyword
IF vkeywordgrab IS NULL THEN --keyword doesn't appear elsewhere in the
keywords table
INSERT INTO artist_keywords (artistkeys_id,keyword)+
VALUES (.vartistkeys_count,.vsingle_keyword)
INSERT INTO artistkeys_ln (catalog_id,artistkeys_id)+
VALUES (.vcatalog_id,.vartistkeys_count)
vartistkeys_count=vartistkeys_count+1
ENDIF
IF vkeywordgrab IS NOT NULL THEN --keyword does appear in the keywords
table
INSERT INTO artistkeys_ln (catalog_id,artistkeys_id)+
VALUES (.vcatalog_id,.vartistkeys_id)
ENDIF
SET V vartistkeys_id=NULL
SET V vkeywordgrab=NULL
ENDIF
GOTO AddKeywordsToTable
GOTO GetRowsFromCatlg
LABEL CleanUp
WRI 'Finished creating artist_keywords
table ' AT 1 1
DROP CURSOR c1
CLEAR VARIABLES vartist,vtitle,vkeywords,vsingle_keyword,vcatalog_total,+
vcatalog_total_txt,vcatalog_id,vind1,vind2,vind3,vcatalog_id_txt,+
vkeywords_len,vfirst_char,vsingle_keywd_len,vkeywords_isalpha,+
vkeywords_isdigit,vartistkeys_count,vartistkeys_id,vind4,vind5,+
vkeywordgrab
RETURN
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
