def createDatabase(self, statement):
# expect 'create database database_name"
dindx = statement.find("database")
if dindx == -1:
raise Exception, 'Error - illegal statement'
dindx += len('database')
database_name = statement[dindx:].strip()
# first we check to see if we already have a database with the same name
solr_str = "fl=id&q=database_name:" + database_name
database_record = solr_request("localhost:8983", solr_str)
if len(database_record) > 0:
raise Exception, 'Error database already exists!'
solr_str = "<add><doc><field name='id'>%s</field><field name='database_name'>%s</field></doc></add>" % (self.getNextId(),database_name)
res = solr_add("localhost:8983", solr_str)
res = solr_commit("localhost:8983")
return res
def createTable(self, database_name, statement):
# supported format ...
# CREATE TABLE test_table (id int, name VARCHAR(32), ssn VARCHAR(32));
# note we have an issue to deal with here. if our table name is of the
# form x.table_name then we need to use the database name (x) from the
# create statement otherwise we fall back to our self.database_name
# field and if both are None then we must throw an exception
# first, extract table name
tindx = statement.lower().find('table')
if tindx == -1:
raise Exception, 'Error ill formed statement'
tindx += len('table')
pindx = statement.find("(")
if pindx == -1:
raise Exception, 'Error ill formed statement'
table_name = statement[tindx:pindx].strip()
# next determine database name
if table_name.find(".") > -1:
# table name includes the database name
database_name, table_name = table_name.split(".")
else:
# else must have a default db name from a previous use or connect
if self.database_name is None:
raise Exception, 'Error - no database specified'
database_name = self.database_name
# and for our purposes table name is databasename skid tablename
table_name = database_name + "_" + table_name
# NOTE - we have a serious issue with ids !!!! until then we will commit after every update
solr_transaction = "<add><doc><field name='id'>%s</field><field name='table_name'>%s</field></doc></add>" % (self.getNextId(), table_name)
res = solr_add("localhost:8983", solr_transaction)
res = solr_commit("localhost:8983")
table_name += "_"
columns = statement[pindx+1:]
columns = columns[:-1]
column_array = columns.split(",")
for column in column_array:
# TODO: support default values !!! and other attributes (like enums!)
solr_str = ""
ca = column.split()
column_name = ca[0].strip()
column_type = ca[1].strip().lower()
if column_type[:len('int')] == 'int':
column_type = 'int'
elif column_type[:len('varchar')] == 'varchar':
column_type = 'string'
elif column_type[:len('float')] == 'float':
column_type = 'float'
elif column_type[:len('timestamp')] == 'timestamp':
column_type = 'date'
else:
raise Exception, "Error invalid column type"
column_attribute1 = ""
column_default_val = 0
if len(ca) > 2:
column_attribute1 = ca[2].strip()
solr_str = "<field name='id'>%s</field><field name='meta_column_name'>%s</field><field name='meta_column_type'>%s</field>" % (self.getNextId(), table_name + column_name, column_type)
if column_type == 'int':
solr_str += "<field name='meta_column_val_int'>%s</field>" % (column_default_val,)
elif column_type == 'string':
solr_str += "<field name='meta_column_val_string'>%s</field>" % (column_default_val,)
elif column_type == 'float':
solr_str += "<field name='meta_column_val_float'>%s</field>" % (column_default__val,)
elif column_type == 'date':
solr_str += "<field name='meta_column_val_date'>%s</field>" % (column_default_val,)
else:
raise Exception, "Error invalid column type"
solr_str = "<add><doc>" + solr_str + "</doc></add>"
res = solr_add("localhost:8983", solr_str)
res = solr_commit("localhost:8983")
return res
def processUpdate(self, statement):
# currently only the following formats are supported ...
# UPDATE table_name SET age='22' WHERE some condition
# or
# UPDATE table_name SET age='22', year=17, bla='bla' WHERE some condition
# we always require a table name which is followed by 'set'
tindx = statement.lower().find('set')
if tindx == -1:
raise Exception, 'MySolrDb.InvalidStatement - 001'
table_name = statement[len('update'):tindx].strip()
# next we assemble our list of field_name/value pairs
field_portion = ""
# which requires us to know where the where clause starts
# so we might as well deal with that here as well
windx = statement.lower().find('where')
if windx == -1:
field_portion = statement[tindx+len('set')+1:].strip()
else:
field_portion = statement[tindx+len('set')+1:windx].strip()
fields_array = field_portion.split(",")
update_fields = {}
for field in fields_array:
name_val_array = field.split('=')
name = name_val_array[0].strip()
val = name_val_array[1].strip()
update_fields[name] = val
# at this point, update_fields is a dict of fields/values which need to
# be updated for each doc which meets the where_clause critera
#################
## FIX THIS!!! ##
#################
table_index_field = 'id' # BUG - this magic needs to be addressed ASAP!
#####################
## END FIX THIS!!! ##
#####################
where_clause = ""
if windx == -1:
# if no where clause we're updating all of em
where_result = table_index_field + ":[0 TO *]"
else:
where_portion = statement[windx:]
where_clause = where_portion.strip()[len('where'):].strip()
# else we use our standard where clause parser
where_result = self.handleWhereClause(where_clause)
# we produce a solr statement to get affected docs
solr_statement = "fl=*" + "&q=" + where_result
# we query the solr index and stick the result in temporary_result_set
solr_host = self.ip_address + ":" + str(self.port)
solr_statement = "start=0&rows=9999&" + solr_statement
temporary_result_set = solr_request(solr_host, solr_statement)
# next we apply the user changes to this set of documents
# and produce a solr style xml doc which represents a transaction
update_transaction = "<add>"
for result in temporary_result_set:
for field in update_fields:
result[field] = update_fields[field]
# and we also must update the solr index ...
# note - for transactional integrity we really should'nt
# update individually but rather we create a
# master update which we finish with a commit below
#ret_code = solr_add(solr_host, result)
update_transaction += self.convertDictToDoc(result)
update_transaction += "</add><commit/>"
# finally we update our solr index
res = solr_add(self.ip_address+":"+str(self.port), update_transaction)
# and return the update status from the solr response
return 0
def processInsert(self, statement):
# currently only the following formats are supported ...
# INSERT INTO tablename (field1, field2) VALUES (field1_val, field2_val)
# insert has the additional problem of having to support auto-increment fields
iindx = statement.lower().find('into')
if iindx == -1:
raise Exception, 'MySolrDb.InvalidStatement - 001'
# we always require a table name which is followed by '('
tindx = statement.lower().find('(')
if tindx == -1:
raise Exception, 'MySolrDb.InvalidStatement - 001'
table_name = statement[len('insert into'):tindx].strip()
# next determine database name
if table_name.find(".") > -1:
# table name includes the database name
database_name, table_name = table_name.split(".")
else:
# else must have a default db name from a previous use or connect
if self.database_name is None:
raise Exception, 'Error - no database specified'
database_name = self.database_name
# next we extract the field names TODO: validate them
fn_start = tindx + 1
fn_end = statement.find(")")
if fn_end == -1:
raise Exception, 'MySolrDb.InvalidStatement - 001'
fields = statement[fn_start:fn_end]
# we next want our values
vals_start = statement.find("(", fn_end)
if vals_start == -1:
raise Exception, 'MySolrDb.InvalidStatement - 001'
vals_end = statement.find(")", vals_start)
if vals_end == -1:
raise Exception, 'MySolrDb.InvalidStatement - 001'
vals = statement[vals_start+1:vals_end]
fields_array = fields.split(",")
vals_array = vals.split(",")
parent_id = self.getNextId()
# this is gonna get ugly in a hurry :-(
row_anchor = "<add><doc><field name='id'>%s</field><field name='row_anchor'>%s</field></doc></add>" % (parent_id,parent_id)
# this makes next id a reality but it is a bug ridden approach
res = solr_add(self.ip_address+":"+str(self.port), row_anchor)
res = solr_commit("localhost:8983")
indx = 0
# TODO: handle array fields
while indx < len(fields_array):
column_name = fields_array[indx].strip()
column_val = vals_array[indx].strip()
# BUG for now i stript any single quotes but this needs to be handled better soon!
column_val = column_val.replace("'","")
column_xml = "<add><doc><field name='id'>%s</field><field name='parent_id'>%s</field>" % (self.getNextId(),parent_id)
column_xml += "<field name='column_name'>%s</field>" % (database_name + "_" + table_name + "_" + column_name,)
column_xml += "<field name='column_type'>%s</field>" % ('string',) # BUG !!
column_xml += "<field name='column_val_string'>%s</field>" % (column_val,)
column_xml += "</doc></add>"
indx += 1
# finally we send this to solr
res = solr_add(self.ip_address+":"+str(self.port), column_xml)
res = solr_commit("localhost:8983")
return res
