Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100755 153 lines (136 sloc) 5.199 kb
dfd22ee @kchiogio Added function to read commands from file
kchiogio authored
1 #!/usr/bin/python
2
1885a43 @kchiogio added author comment
kchiogio authored
3 #Remy Baumgarten
4 #Kevin Chiogioji
5
a490264 @remyb broke down program into assignments and uncoupled functions
authored
6 import ibm_db
04e5312 @remyb Grabbing connections from dtables that match a given table named parsed ...
authored
7 import antlr3
8 import antlr3.tree
9 from sqlLexer import sqlLexer
10 from sqlParser import sqlParser
11acbeb @remyb part one bug fixes and convert update
authored
11 import ConfigParser
ef32a2b @remyb I added some sample functions to interact with the DB2 instance
authored
12
13 #show stats on database
35b1188 @remyb added multiple instances variables to connect to, SAMPLE database can be...
authored
14 def client_print(db):
15 client = ibm_db.client_info(db)
ef32a2b @remyb I added some sample functions to interact with the DB2 instance
authored
16 print "DRIVER_NAME: string(%d) \"%s\"" % (len(client.DRIVER_NAME), client.DRIVER_NAME)
17 print "DRIVER_VER: string(%d) \"%s\"" % (len(client.DRIVER_VER), client.DRIVER_VER)
18 print "DATA_SOURCE_NAME: string(%d) \"%s\"" % (len(client.DATA_SOURCE_NAME), client.DATA_SOURCE_NAME)
19 print "DRIVER_ODBC_VER: string(%d) \"%s\"" % (len(client.DRIVER_ODBC_VER), client.DRIVER_ODBC_VER)
20 print "ODBC_VER: string(%d) \"%s\"" % (len(client.ODBC_VER), client.ODBC_VER)
7dc30ba @remyb added some sample config reading functions
authored
21 print "ODBC_SQL_CONFORMANCE: string(%d) \"%s\"" % (len(client.ODBC_SQL_CONFORMANCE), client.ODBC_SQL_CONFORMANCE)
ef32a2b @remyb I added some sample functions to interact with the DB2 instance
authored
22 print "APPL_CODEPAGE: int(%s)" % client.APPL_CODEPAGE
23 print "CONN_CODEPAGE: int(%s)" % client.CONN_CODEPAGE
24
11acbeb @remyb part one bug fixes and convert update
authored
25 def config_extract(settings,section):
26 config = ConfigParser.RawConfigParser()
27 config.read(settings)
28 try:
29 options = config.options(section)
30 except:
31 print "[*] The file is not in the correct format, please consult readme"
32 sys.exit()
33 items= {}
34 for option in options:
35 try:
36 items[option] = config.get(section, option)
37 if items[option] == -1:
38 DebugPrint("skip: %s" % option)
39 except:
40 print("exception on %s!" % option)
41 items[option] = None
42 return items
43
eee166a @remyb added Threading for remote connections and real configuration parsing an...
authored
44 # execute query
45 def exec_query(db, query):
46 try:
47 ibm_db.exec_immediate(db,query)
48 except:
11acbeb @remyb part one bug fixes and convert update
authored
49 print "[*] The transaction could not be completed:", query# ,ibm_db.stmt_errormsg()
eee166a @remyb added Threading for remote connections and real configuration parsing an...
authored
50 else:
2f663b8 @remyb Catalog creation and section added to config
authored
51 print "[*] Transaction complete: ",query
11acbeb @remyb part one bug fixes and convert update
authored
52
53
ef32a2b @remyb I added some sample functions to interact with the DB2 instance
authored
54
2f663b8 @remyb Catalog creation and section added to config
authored
55 # check to see if dtables in CATALOG exists, if not, create it
56 def create_catalog(cat, catalog):
57 try:
11acbeb @remyb part one bug fixes and convert update
authored
58 ibm_db.exec_immediate(cat,"create table dtables(tname char(32), nodedriver char(64), " \
59 "nodeurl char(128), nodeuser char(16), nodepasswd char(16), " \
60 "partmtd int, nodeid int, partcol char(32), partparam1 char(32)," \
61 " partparam2 char(32))")
2f663b8 @remyb Catalog creation and section added to config
authored
62 except:
b465195 @remyb Added cataloging, it was missing, fixed issue with catalog insertion not...
authored
63 print "[*] NOTICE catalog table exists, continuing..."
2f663b8 @remyb Catalog creation and section added to config
authored
64
65 # insert metadata
11acbeb @remyb part one bug fixes and convert update
authored
66 def insert_catalog_row(query, conn, node_conf, nodeid):
765f7c4 @kchiogio Edited ddl.txt file
kchiogio authored
67 index = query.split()
68 if index[0].upper() == "CREATE" or index[0].upper() == "DROP":
69 tableName = index[2]
70 elif index[0].upper() == "SELECT":
71 tableName = index[3]
fc3e0cd @kchiogio Fixed table name reading bug
kchiogio authored
72
765f7c4 @kchiogio Edited ddl.txt file
kchiogio authored
73 if tableName.find("(") != -1:
74 tableName = tableName[0:tableName.find("(")]
fc3e0cd @kchiogio Fixed table name reading bug
kchiogio authored
75
765f7c4 @kchiogio Edited ddl.txt file
kchiogio authored
76 cat_row = "INSERT INTO dtables (tname, nodedriver, nodeurl, nodeuser," \
11acbeb @remyb part one bug fixes and convert update
authored
77 " nodepasswd, partmtd, nodeid, partcol, partparam1, partparam2) VALUES" \
78 " ('%s', '%s', '%s', '%s', '%s', %s, %s, '%s', '%s', '%s');" % (tableName.rstrip(";"), node_conf["driver"], node_conf ["hostname"], node_conf["username"], node_conf["passwd"], "NULL", nodeid, "NULL", "NULL", "NULL")
a138164 @remyb removed extra functions and cleaned up debug
authored
79 #print cat_row
b465195 @remyb Added cataloging, it was missing, fixed issue with catalog insertion not...
authored
80 stmt = ibm_db.exec_immediate(conn,cat_row)
81 print "[*] Cataloging transaction...done"
eee166a @remyb added Threading for remote connections and real configuration parsing an...
authored
82
dfd22ee @kchiogio Added function to read commands from file
kchiogio authored
83 # read DDLs from file
9168ef5 @kchiogio Added function to read DDLs
kchiogio authored
84 def readDDL(fileName):
dfd22ee @kchiogio Added function to read commands from file
kchiogio authored
85 try:
9168ef5 @kchiogio Added function to read DDLs
kchiogio authored
86 f = open(fileName,'r')
dfd22ee @kchiogio Added function to read commands from file
kchiogio authored
87 commands = f.readlines()
88 for command in commands:
89 command = command.strip()
90 return commands
91 except:
92 print "the file could not be read\n"
93 else:
94 f.close()
eee166a @remyb added Threading for remote connections and real configuration parsing an...
authored
95
04e5312 @remyb Grabbing connections from dtables that match a given table named parsed ...
authored
96 # Prints the contents of the Query
b52381e @remyb finished assignment part 3
authored
97 def print_results(conn,sql):
98 #sql = "SELECT " + column + " FROM " + table + ";"
99 #print sql
8a7028d checking in part3 processor and addition of query printer for main funct...
Remy authored
100 stmt = ibm_db.exec_immediate(conn,sql)
101 dictionary = ibm_db.fetch_assoc(stmt)
102 while dictionary != False:
103 print '==================='
36fc430 @remyb changed table contents for row interator called via part3 grammer query
authored
104 print "ISBN: ", dictionary["ISBN"]
105 print "Title: ",dictionary["TITLE"]
106 print "Author: ",dictionary["AUTHOR"]
8a7028d checking in part3 processor and addition of query printer for main funct...
Remy authored
107 dictionary = ibm_db.fetch_assoc(stmt)
108
04e5312 @remyb Grabbing connections from dtables that match a given table named parsed ...
authored
109 # Get nodes from catalog for a specific table
110 # and return it as a list (url, user,passwd)
111 def get_nodes(conn,tablename):
112 sql = "SELECT DISTINCT * FROM DTABLES WHERE TNAME = '" + tablename + "';"
113 print "\t",sql
114 stmt = ibm_db.exec_immediate(conn,sql)
115 dictionary = ibm_db.fetch_assoc(stmt)
116 nodes = []
117 while dictionary != False:
118 url = dictionary["NODEURL"].rstrip()
119 user = dictionary["NODEUSER"]
120 passwd = dictionary["NODEPASSWD"]
18ac59c @remyb first part of part4 complete
authored
121 driver = dictionary["NODEDRIVER"]
122 node = (url,user,passwd,driver)
04e5312 @remyb Grabbing connections from dtables that match a given table named parsed ...
authored
123 nodes.append(node)
124 dictionary = ibm_db.fetch_assoc(stmt)
125 return nodes
126
127 # returns a list of tuples for each line in the csv file
128 def loadCSV(filename):
129 contents = []
130 for line in open(filename):
131 fields = line.split(',');
132 number = fields[0]
133 title = fields[1]
134 author = fields[2]
135 book = (number,title,author)
136 contents.append(book)
137 return contents
e9407f5 changed main.py to be loaded as module
Remy authored
138
04e5312 @remyb Grabbing connections from dtables that match a given table named parsed ...
authored
139 # parse to obtain tablename
140 def get_table(sql):
141 stream = antlr3.ANTLRStringStream(sql)
142 lexer = sqlLexer(stream)
143 tokens = antlr3.CommonTokenStream(lexer)
144 parser = sqlParser(tokens)
145 r = parser.sqlstmt()
146 tree = r.tree.toStringTree()
147 if( r.tree.children[0].toString().lower() == "create"):
148 return r.tree.children[2].toString()
149 elif( r.tree.children[0].toString().lower() == "select"):
150 return r.tree.children[3].toString()
151 elif( r.tree.children[0].toString().lower() == "drop"):
152 return r.tree.children[1].toString()
Something went wrong with that request. Please try again.