The following python code can be used to change the table structure into btree. Should you wish to change your tables to hash or isam then change the SQL in the second to last line:
print "modify %s to btree unique on %s with unique_scope=statement;\p\g" %(row[0], k.group(1))
Read on for the full code snippet..
import ingresdbi
import re
import getopt
import sys
#
def dbprint( str ):
print "%s;\n\\p\\g" %(str)
return
#
def usage():
print "-v<vnode> -d<database>"
print "-v, --vnode Vnode name"
print "-d, --database Database name"
print "-h, --help Print this message"
#
try:
opts, args = getopt.getopt(sys.argv[1:], "v:d:h", ["vnode=", "database=", "help"])
except getopt.GetoptError:
# print help information and exit:
usage()
sys.exit(2)
#
vnode = "(local)"
db = None
#
for o, a in opts:
if o in ("-v", "--vnode"):
vnode = a
if o in ("-d", "--database"):
db = a
if o in ("-h", "--help"):
usage()
sys.exit(1)
#
if db != None:
ingdb = ingresdbi.connect(vnode=vnode, database=db)
cur = ingdb.cursor();
cur.execute("select trim(table_name),trim(constraint_name), text_segment from iiconstraints where constraint_type = 'P' and (table_name not like 'ii%') and constraint_name like '$%'")
res = cur.fetchall()
for row in res:
rkey = re.compile(r' PRIMARY KEY\(([^)]*)\)')
k = rkey.match(row[2])
print "alter table %s drop constraint \"%s\" cascade;\p\g" %( row[0], row[1] )
print "modify %s to btree unique on %s with unique_scope=statement;\p\g" %(row[0], k.group(1))
print "alter table %s add primary key(%s) with index=base table structure;\p\g" %(row[0], k.group(1))
Thanks to Ray for the the code
