Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> BroadVision 4.0 - How I use .src file to specify indexes?

BroadVision 4.0 - How I use .src file to specify indexes?

From: Dan Bikle <dbikle_at_rahul.net>
Date: 10 Jun 1999 20:56:05 GMT
Message-ID: <7jp8p5$6cd$1@samba.rahul.net> 






Dear comp.infosystems.www.servers.unix:




I just got done with an experiment to determine how I may use
sch_gen and .src files to specify CREATE INDEX commands in
resulting .sql scripts.



Summary:



I found that I could force a single index to get created on 
a single column.



I found that I could not add another single index to another
single column.



I found that sch_gen is quite content to create a unique
concatenated index on multiple columns.



Also I found that sch_gen cannot specify a proper CREATE INDEX
command; it always places a comma after the last column name
in the CREATE INDEX command; this generates an error when 
sent to the sql interpreter.




Details:





Here is a simple .src file:


oooooooooo oooooooooo oooooooooo oooooooooo 
REPLACEMENT:    BV_MM_USER_RESPONSE_REPL


TABLE:  BV_MM_USER_RESPONSE


ATTRIBUTE: QUESTION_ID



TYPE: STRING



COLUMN: number


FRIENDLY_NAME: Question ID


SEMANTICS: Question ID


oooooooooo oooooooooo oooooooooo oooooooooo 




Here is the shell command to make the .sql script:


natalino-tcsh-92% make -f schema.mk mm_user_response_db_replace.sql
/bin/touch mm_user_response_spec_replace.tmp mm_user_response_spec_replace.sql
/bin/rm -f mm_user_response_spec_replace.tmp mm_user_response_spec_replace.sql


/usr/ccs/lib/cpp -B mm_user_response_spec_replace.src mm_user_response_spec_replace.tmp
/opt/bv1to1/bin/sch_gen  -schema mm_user_response_spec_replace.tmp -script mm_user_response_db_replace.sql -cms -class_only
BV_MM_USER_RESPONSE to be replaced. id 13001
natalino-tcsh-93% 



Here is the .sql script (notice no index created on QUESTION_ID):




oooooooooo oooooooooo oooooooooo oooooooooo oooooooooo
natalino-tcsh-93% cat mm_user_response_db_replace.sql
drop table OLD_BV_MM_USER_RESPONSE


;




RENAME BV_MM_USER_RESPONSE TO OLD_BV_MM_USER_RESPONSE



;




delete from BV_ATTRIBUTES where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE' and SYSTEM_ID = 'PM'


;




delete from BV_ATTRIBUTES_EXT where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE' and SYSTEM_ID = 'PM'


;




delete from BV_ATTR_GROUP where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE'


;




delete from BV_ATTR_GROUP_INFO where SCHEMA_NAME = 'BV_UPROF' and GROUP_NAME = 'BV_MM_USER_RESPONSE'


;




delete from BV_EXT_MAP where SCHEMA_NAME = 'BV_UPROF' and EXT_TABLE = 'BV_MM_USER_RESPONSE'


;




insert into BV_ATTRIBUTES values('BV_UPROF', 13001, 'BV_MM_USER_RESPONSE', 'QUESTION_ID', 'STRING', 'UP', 'Question ID', 'Question ID', 0, -1, 0, 0, 0, 0)


;




drop table BV_MM_USER_RESPONSE


;




create table BV_MM_USER_RESPONSE (

        USER_ID int not null,
        QUESTION_ID number null




)


;




create unique index BV_MM_USER_RESPONSE_ID_IDX on BV_MM_USER_RESPONSE(USER_ID)


;




insert into BV_MM_USER_RESPONSE(USER_ID) select USER_ID from BV_USER_PROFILE


;




natalino-tcsh-94% 


oooooooooo oooooooooo oooooooooo oooooooooo oooooooooo





Now I run an experiment with the ATTR_KIND key word
to see if I may get an index placed on QUESTION_ID:




oooooooooo oooooooooo oooooooooo oooooooooo oooooooooo
REPLACEMENT:    BV_MM_USER_RESPONSE_REPL


TABLE:  BV_MM_USER_RESPONSE


ATTRIBUTE: QUESTION_ID



TYPE: STRING



COLUMN: number


FRIENDLY_NAME: Question ID


SEMANTICS: Question ID


ATTR_KIND: KEY



oooooooooo oooooooooo oooooooooo oooooooooo oooooooooo



Again I make the .sql script:



natalino-tcsh-97% make -f schema.mk mm_user_response_db_replace.sql
/bin/touch mm_user_response_spec_replace.tmp mm_user_response_spec_replace.sql
/bin/rm -f mm_user_response_spec_replace.tmp mm_user_response_spec_replace.sql


/usr/ccs/lib/cpp -B mm_user_response_spec_replace.src mm_user_response_spec_replace.tmp
/opt/bv1to1/bin/sch_gen  -schema mm_user_response_spec_replace.tmp -script mm_user_response_db_replace.sql -cms -class_only
BV_MM_USER_RESPONSE to be replaced. id 13001





Now I inspect the .sql script (notice the index created on QUESTION_ID):



natalino-tcsh-98% cat mm_user_response_db_replace.sql
drop table OLD_BV_MM_USER_RESPONSE


;




RENAME BV_MM_USER_RESPONSE TO OLD_BV_MM_USER_RESPONSE



;




delete from BV_ATTRIBUTES where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE' and SYSTEM_ID = 'PM'


;




delete from BV_ATTRIBUTES_EXT where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE' and SYSTEM_ID = 'PM'


;




delete from BV_ATTR_GROUP where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE'


;




delete from BV_ATTR_GROUP_INFO where SCHEMA_NAME = 'BV_UPROF' and GROUP_NAME = 'BV_MM_USER_RESPONSE'


;




delete from BV_EXT_MAP where SCHEMA_NAME = 'BV_UPROF' and EXT_TABLE = 'BV_MM_USER_RESPONSE'


;




insert into BV_ATTRIBUTES values('BV_UPROF', 13001, 'BV_MM_USER_RESPONSE', 'QUESTION_ID', 'STRING', 'UP', 'Question ID', 'Question ID', 128, -1, 0, 0, 0, 0)


;




drop table BV_MM_USER_RESPONSE


;




create table BV_MM_USER_RESPONSE (

        USER_ID int not null,
        QUESTION_ID number not null




)


;




create unique index BV_MM_USER_RESPONSE_ID_IDX on BV_MM_USER_RESPONSE(USER_ID)


;




create unique index BV_MM_USER_RESPONSE_KEY_IDX on BV_MM_USER_RESPONSE(QUESTION_ID,)


;




insert into BV_MM_USER_RESPONSE(USER_ID) select USER_ID from BV_USER_PROFILE


;




natalino-tcsh-99% 




Notice in the script above the 2nd 'create unique index' command is faulty
(at least it is easy for the DBA to fix via vi).




Next, I add another attribute and attempt to place a unique index on it





REPLACEMENT:    BV_MM_USER_RESPONSE_REPL


TABLE:  BV_MM_USER_RESPONSE


ATTRIBUTE: QUESTION_ID



TYPE: STRING



COLUMN: number


FRIENDLY_NAME: Question ID


SEMANTICS: Question ID


ATTR_KIND: KEY


ATTRIBUTE: ANSWER_ID



TYPE: STRING



COLUMN: number


FRIENDLY_NAME: Answer ID


SEMANTICS: Answer ID


ATTR_KIND: KEY


natalino-tcsh-116% !mak


make -f schema.mk mm_user_response_db_replace.sql

/bin/touch mm_user_response_spec_replace.tmp mm_user_response_spec_replace.sql
/bin/rm -f mm_user_response_spec_replace.tmp mm_user_response_spec_replace.sql
/usr/ccs/lib/cpp -B mm_user_response_spec_replace.src mm_user_response_spec_replace.tmp
/opt/bv1to1/bin/sch_gen  -schema mm_user_response_spec_replace.tmp -script mm_user_response_db_replace.sql -cms -class_only


BV_MM_USER_RESPONSE to be replaced. id 13001

natalino-tcsh-117% 
natalino-tcsh-117% 
natalino-tcsh-117% 






natalino-tcsh-117% 


natalino-tcsh-117% cat mm_user_response_db_replace.sql
drop table OLD_BV_MM_USER_RESPONSE


;




RENAME BV_MM_USER_RESPONSE TO OLD_BV_MM_USER_RESPONSE



;




delete from BV_ATTRIBUTES where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE' and SYSTEM_ID = 'PM'


;




delete from BV_ATTRIBUTES_EXT where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE' and SYSTEM_ID = 'PM'


;




delete from BV_ATTR_GROUP where SCHEMA_NAME = 'BV_UPROF' and TABLE_NAME = 'BV_MM_USER_RESPONSE'


;




delete from BV_ATTR_GROUP_INFO where SCHEMA_NAME = 'BV_UPROF' and GROUP_NAME = 'BV_MM_USER_RESPONSE'


;




delete from BV_EXT_MAP where SCHEMA_NAME = 'BV_UPROF' and EXT_TABLE = 'BV_MM_USER_RESPONSE'


;




insert into BV_ATTRIBUTES values('BV_UPROF', 13001, 'BV_MM_USER_RESPONSE', 'QUESTION_ID', 'STRING', 'UP', 'Question ID', 'Question ID', 128, -1, 0, 0, 0, 0)


;




insert into BV_ATTRIBUTES values('BV_UPROF', 13002, 'BV_MM_USER_RESPONSE', 'ANSWER_ID', 'STRING', 'UP', 'Answer ID', 'Answer ID', 128, -1, 0, 0, 0, 0)


;




drop table BV_MM_USER_RESPONSE


;




create table BV_MM_USER_RESPONSE (

        USER_ID int not null,
        QUESTION_ID number not null,
        ANSWER_ID number not null




)


;




create unique index BV_MM_USER_RESPONSE_ID_IDX on BV_MM_USER_RESPONSE(USER_ID)


;




create unique index BV_MM_USER_RESPONSE_KEY_IDX on BV_MM_USER_RESPONSE(QUESTION_ID,ANSWER_ID,)


;




insert into BV_MM_USER_RESPONSE(USER_ID) select USER_ID from BV_USER_PROFILE


;




natalino-tcsh-118% 


natalino-tcsh-118% 




Notice that sch_gen wants to create a concatenated index rather than
two individual indexes.



Question 1: Can I put something in the .src file to force sch_gen to
generate two individual 'CREATE UNIQUE INDEX' commands?



Question 2: Can I put something in the .src file to force sch_gen to
remove the comma after the last column name in the 'CREATE UNIQUE
INDEX' command?



-Dan




Daniel B. Bikle/Independent Oracle Consultant
bikle_at_bikle.com | 650/941-6276 | P.O. BOX AG LOS ALTOS CA 94023
http://www.bikle.com




Received on Thu Jun 10 1999 - 15:56:05 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US