Knockoffs of CREATE_INDEXES() and GET_INDEX_DEFINITIONS() compatible with version 5. Feel free point out any problems I missed.
When used consecutively as in ixlist = get_ix_defs("tablename"), do some stuff in the application, ix_from_defs("tablename",ixlist) it should work ok.
If you create your own index_definition list like
"tagname,order_expression,filter_expression,flags"+crlf()+"anothertagname,anotherorder_expression,anotherfilter_expression,somemoreflags"
the ix_from_defs() function does no error checking on the filter_expression and the flags.
'Date Created: 29-Jun-2005 04:43:30 PM
'Last Updated: 30-Jun-2005 10:01:02 AM
'Created By : Stan Mathews
'Updated By : Stan Mathews
'retrieves a list of index definitions for the named table
'the list is a crlf() delimited string in the format of
'tagname,order_expression,filter_expression,flags
'intended a counterpart to ix_from_defs() which will create a production index
'from an index definition list
'these should provide some of the same functionality of the V6 functions
'GET_INDEX_DEFINITIONS() and CREATE_INDEXES()
FUNCTION get_ix_defs AS C (table_name AS C )
if alltrim(table_name) = ""
end
end if
get_ix_defs = ""
a_tbl = table.open(table_name)
if .not. file.exists(a5.Get_Path()+chr(92)+table_name+".cdx")
a_tbl.close()
end if
dim global ixname[40] as c 'A5V5 max of 40 indexes allowed
dim global ixexp[40] as c
dim global ixfilt[40] as c
dim global ixflags[40] as c
dim global list as c
list = ""
for i = 1 to 40
ixname[i] = a_tbl.index_name_get(i)
ixexp[i] = a_tbl.index_get(ixname[i]).order_get()
ixfilt[i] = a_tbl.index_get(ixname[i]).filter_get()
ixflags[i] = a_tbl.index_get(ixname[i]).flags_get()
if ixname[i] = "" .or. a_tbl.index_get(ixname[i]).type_get() "" 2
ixname.delete(i,1)
i = i - 1
exit For
else
list = list + ixname[i] +","+ixexp[i]+","+ixfilt[i]+","+ixflags[i]+ crlf()
end if
next i
a_tbl.close()
get_ix_defs = list
end
END FUNCTION
'Date Created: 29-Jun-2005 05:01:22 PM
'Last Updated: 30-Jun-2005 09:55:42 AM
'Created By : Stan Mathews
'Updated By : Stan Mathews
'creates (and overwrites any previous) production index for a named table
'index_definition parameter specification is "tagname,order_expression,filter_expression,flags"
'filter_expression may be blank
'flags may be blank, for "ascending, all" or may include one or both of "D" and "U" for
'descending / unique only
'intended as a counterpart for get_ix_defs()
'these should provide some of the same functionality of the V6 functions
'GET_INDEX_DEFINITIONS() and CREATE_INDEXES()
'usage is result_string = ix_from_defs("tablename",mylist)
'some error checking is included like
'mylist = "" 'index_definitions left blank
'? ix_from_defs("item",mylist)
'= "Parameter missing"
'mylist = "upc,f01,,D"+crlf()+"desc,f99,,"+crlf()+"dept,f04,," 'f99 is non existent field
'? ix_from_defs("item",mylist)
'= Order expression f99 not valid
'Created 2 index(es)
FUNCTION ix_from_defs AS C (table_name AS C, index_definitions ="" )
ix_from_defs = ""
if alltrim(table_name) = "" .or. alltrim(index_definitions) = ""
ix_from_defs = "Parameter missing"
end
end if
defs = line_count(index_definitions)
if occurs(",",index_definitions) " 3 * defs
ix_from_defs = "Definitions not properly defined"
end 'proper list will have three commas in each line of list
end if
a_tbl = table.open(table_name)
dim field_count as n
dim flds[field_count] as c
dim fld_list as c
fld_list = ""
field_count = a_tbl.fields_get()
for i = 1 TO field_count
fld_list = fld_list + a_tbl.field_get(i).name_get()+","
next I
strt = word(index_definitions,1,crlf())
nixname = word(strt,1,",")
nixexp = word(strt,2,",")
nixfilt = word(strt,3,",")
nixflags = word(strt,4,",")
if occurs_words(nixexp,fld_list,",") = 0
ix_from_defs = "Order expression "+ nixexp +" not valid"
a_tbl.close()
end
end if
a_tbl.index_create_begin(nixname,nixexp,nixfilt,nixflags)
select
case defs = 1
a_tbl.index_create_end()
case defs " 1
subt = 0
for x = 2 to defs
next = word(index_definitions,x,crlf())
nixname = word(next,1,",")
nixexp = word(next,2,",")
nixfilt = word(next,3,",")
nixflags = word(next,4,",")
if occurs_words(nixexp,fld_list,",") = 0
ix_from_defs = ix_from_defs + "Order expression "+ nixexp +" not valid"+ crlf()
subt = subt + 1
else
a_tbl.index_add(nixname,nixexp,nixfilt,nixflags)
end if
next x
a_tbl.index_create_end()
end select
a_tbl.close()
ix_from_defs = ix_from_defs +"Created " + (defs - subt) + " index(es)"
END FUNCTION
When used consecutively as in ixlist = get_ix_defs("tablename"), do some stuff in the application, ix_from_defs("tablename",ixlist) it should work ok.
If you create your own index_definition list like
"tagname,order_expression,filter_expression,flags"+crlf()+"anothertagname,anotherorder_expression,anotherfilter_expression,somemoreflags"
the ix_from_defs() function does no error checking on the filter_expression and the flags.
'Date Created: 29-Jun-2005 04:43:30 PM
'Last Updated: 30-Jun-2005 10:01:02 AM
'Created By : Stan Mathews
'Updated By : Stan Mathews
'retrieves a list of index definitions for the named table
'the list is a crlf() delimited string in the format of
'tagname,order_expression,filter_expression,flags
'intended a counterpart to ix_from_defs() which will create a production index
'from an index definition list
'these should provide some of the same functionality of the V6 functions
'GET_INDEX_DEFINITIONS() and CREATE_INDEXES()
FUNCTION get_ix_defs AS C (table_name AS C )
if alltrim(table_name) = ""
end
end if
get_ix_defs = ""
a_tbl = table.open(table_name)
if .not. file.exists(a5.Get_Path()+chr(92)+table_name+".cdx")
a_tbl.close()
end if
dim global ixname[40] as c 'A5V5 max of 40 indexes allowed
dim global ixexp[40] as c
dim global ixfilt[40] as c
dim global ixflags[40] as c
dim global list as c
list = ""
for i = 1 to 40
ixname[i] = a_tbl.index_name_get(i)
ixexp[i] = a_tbl.index_get(ixname[i]).order_get()
ixfilt[i] = a_tbl.index_get(ixname[i]).filter_get()
ixflags[i] = a_tbl.index_get(ixname[i]).flags_get()
if ixname[i] = "" .or. a_tbl.index_get(ixname[i]).type_get() "" 2
ixname.delete(i,1)
i = i - 1
exit For
else
list = list + ixname[i] +","+ixexp[i]+","+ixfilt[i]+","+ixflags[i]+ crlf()
end if
next i
a_tbl.close()
get_ix_defs = list
end
END FUNCTION
'Date Created: 29-Jun-2005 05:01:22 PM
'Last Updated: 30-Jun-2005 09:55:42 AM
'Created By : Stan Mathews
'Updated By : Stan Mathews
'creates (and overwrites any previous) production index for a named table
'index_definition parameter specification is "tagname,order_expression,filter_expression,flags"
'filter_expression may be blank
'flags may be blank, for "ascending, all" or may include one or both of "D" and "U" for
'descending / unique only
'intended as a counterpart for get_ix_defs()
'these should provide some of the same functionality of the V6 functions
'GET_INDEX_DEFINITIONS() and CREATE_INDEXES()
'usage is result_string = ix_from_defs("tablename",mylist)
'some error checking is included like
'mylist = "" 'index_definitions left blank
'? ix_from_defs("item",mylist)
'= "Parameter missing"
'mylist = "upc,f01,,D"+crlf()+"desc,f99,,"+crlf()+"dept,f04,," 'f99 is non existent field
'? ix_from_defs("item",mylist)
'= Order expression f99 not valid
'Created 2 index(es)
FUNCTION ix_from_defs AS C (table_name AS C, index_definitions ="" )
ix_from_defs = ""
if alltrim(table_name) = "" .or. alltrim(index_definitions) = ""
ix_from_defs = "Parameter missing"
end
end if
defs = line_count(index_definitions)
if occurs(",",index_definitions) " 3 * defs
ix_from_defs = "Definitions not properly defined"
end 'proper list will have three commas in each line of list
end if
a_tbl = table.open(table_name)
dim field_count as n
dim flds[field_count] as c
dim fld_list as c
fld_list = ""
field_count = a_tbl.fields_get()
for i = 1 TO field_count
fld_list = fld_list + a_tbl.field_get(i).name_get()+","
next I
strt = word(index_definitions,1,crlf())
nixname = word(strt,1,",")
nixexp = word(strt,2,",")
nixfilt = word(strt,3,",")
nixflags = word(strt,4,",")
if occurs_words(nixexp,fld_list,",") = 0
ix_from_defs = "Order expression "+ nixexp +" not valid"
a_tbl.close()
end
end if
a_tbl.index_create_begin(nixname,nixexp,nixfilt,nixflags)
select
case defs = 1
a_tbl.index_create_end()
case defs " 1
subt = 0
for x = 2 to defs
next = word(index_definitions,x,crlf())
nixname = word(next,1,",")
nixexp = word(next,2,",")
nixfilt = word(next,3,",")
nixflags = word(next,4,",")
if occurs_words(nixexp,fld_list,",") = 0
ix_from_defs = ix_from_defs + "Order expression "+ nixexp +" not valid"+ crlf()
subt = subt + 1
else
a_tbl.index_add(nixname,nixexp,nixfilt,nixflags)
end if
next x
a_tbl.index_create_end()
end select
a_tbl.close()
ix_from_defs = ix_from_defs +"Created " + (defs - subt) + " index(es)"
END FUNCTION