def test_plan_merge(self):
print '\n\n\n Running Merge plan test'
with open('../queries/3.json') as json_data:
query = json.loads(json_data.read())
query_data_obj = query_data.create_query_data(query)
with open('../plans/double_plan_3_merge.json') as json_data:
query_plan = json.loads(json_data.read())
query_executor = QueryExecutor()
query_executor.execute_query(query_data_obj, query_plan)
def test_query_1(self):
with open('../queries/4.json') as json_data:
query = json.loads(json_data.read())
start = timer()
query_data_obj = query_data.create_query_data(query)
query_planner = QueryPlanner()
query_plan = query_planner.eval_query(query_data_obj)
print '\nPlan : ' + str(query_plan)
query_executor = QueryExecutor()
query_executor.execute_query(query_data_obj, query_plan)
end = timer()
print('\n\nQuery run time : ' + str(end - start))
def __init__(self, user, host, port, database, password, ssl, timeout,
application_name):
self._sock = None
self._client_encoding = "utf8"
self.notifications = deque(maxlen=100)
self.notices = deque(maxlen=100)
self.parameter_statuses = deque(maxlen=100)
self.lowest_server_version = self._lowest_server_version()
if user is None:
raise InterfaceError(
"The 'user' connection parameter cannot be None")
if isinstance(user, text_type):
self.user = user.encode('utf8')
else:
self.user = user
if isinstance(password, text_type):
self.password = password.encode('utf8')
else:
self.password = password
self.autocommit = False
self._xid = None
self._caches = defaultdict(lambda: defaultdict(dict))
self.statement_number = 0
try:
if host is not None:
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_NODELAY, 1)
sock.connect((host, port))
self._sock = sock.makefile(mode="rwb")
else:
raise ProgrammingError("host must be provided")
except socket.error as e:
raise InterfaceError("communication error", e)
self._flush = self._sock.flush
self._read = self._sock.read
self._write = self._sock.write
self._backend_key_data = None
self.in_transaction = False
self._executor = QueryExecutor(self)
def run_query(self, query_file_path, indexing, stats, optimization, print_json):
start = timer()
with open(query_file_path) as json_data:
query = json.loads(json_data.read())
query_data = QueryData()
query_data_obj = query_data.create_query_data(query, indexing, stats, optimization)
if print_json:
print "\nQuery : " + str(query_data_obj)
query_planner = QueryPlanner()
query_plan = query_planner.eval_query(query_data_obj)
if print_json:
print "\nPlan : " + str(query_plan)
query_executor = QueryExecutor()
query_result = query_executor.execute_query(query_data_obj, query_plan, indexing)
if print_json:
print "\nResult : \n" + str(query_result)
end = timer()
print("\n\nQuery run time : " + str(end - start))
class Connection(object):
# DBAPI Extension: supply exceptions as attributes on the connection
Warning = property(lambda self: self._getError(Warning))
Error = property(lambda self: self._getError(Error))
InterfaceError = property(lambda self: self._getError(InterfaceError))
DatabaseError = property(lambda self: self._getError(DatabaseError))
OperationalError = property(lambda self: self._getError(OperationalError))
IntegrityError = property(lambda self: self._getError(IntegrityError))
InternalError = property(lambda self: self._getError(InternalError))
ProgrammingError = property(lambda self: self._getError(ProgrammingError))
NotSupportedError = property(
lambda self: self._getError(NotSupportedError))
def _getError(self, error):
warn("DB-API extension connection.%s used" % error.__name__,
stacklevel=3)
return error
def __init__(self, user, host, port, database, password, ssl, timeout,
application_name):
self._sock = None
self._client_encoding = "utf8"
self.notifications = deque(maxlen=100)
self.notices = deque(maxlen=100)
self.parameter_statuses = deque(maxlen=100)
self.lowest_server_version = self._lowest_server_version()
if user is None:
raise InterfaceError(
"The 'user' connection parameter cannot be None")
if isinstance(user, text_type):
self.user = user.encode('utf8')
else:
self.user = user
if isinstance(password, text_type):
self.password = password.encode('utf8')
else:
self.password = password
self.autocommit = False
self._xid = None
self._caches = defaultdict(lambda: defaultdict(dict))
self.statement_number = 0
try:
if host is not None:
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_NODELAY, 1)
sock.connect((host, port))
self._sock = sock.makefile(mode="rwb")
else:
raise ProgrammingError("host must be provided")
except socket.error as e:
raise InterfaceError("communication error", e)
self._flush = self._sock.flush
self._read = self._sock.read
self._write = self._sock.write
self._backend_key_data = None
self.in_transaction = False
self._executor = QueryExecutor(self)
def cursor(self):
return Cursor(self)
def execute(self, cursor, stmt, args, flags=0):
self._executor.execute(cursor, stmt, args, flags)
def executemany(self, cursor, stmt, bulk):
self._executor.executemany(cursor, stmt, bulk)
def _lowest_server_version(self):
return StrictVersion('2.2.0')
def close(self):
if self._sock and not self._sock.closed:
self._sock.close()
def commit(self):
pass
def rollback(self):
pass
def create_db_and_table():
QueryExecutor.create_database(db_obj, "happydata")
QueryExecutor.use_database(db_obj, "happydata")
QueryExecutor.drop_table(db_obj, "HappyMails")
QueryExecutor.create_table(db_obj, "HappyMails")
def main():
service = Util.get_gmail_service()
# Service will be None if exception occurs when getting GMail Service
if not service:
return
# Call the Gmail API for retrieving messages.
# Messages fetched are limited to 100 by Gmail by default
results = service.users().messages().list(userId='me').execute()
messages = results.get('messages')
# B.connect to DB
db_obj.connection()
# create new database and table
create_db_and_table()
# Get complete list of mail info for a single update query
val = Util.get_value_list_from_messages(messages, service)
# Insert the mail content to MySQL DB
QueryExecutor.execute_insert_query(db_obj, val)
# fetch ids based on rules.json
rule_list = Util.get_rule_list_from_json()
if not rule_list:
return
# rule_list will be None if json file parsing fails
# Update DB rule-wise
rule_no = 1
for rule in rule_list:
print("Applying Rule filter: ", rule_no)
rule_no += 1
# Construct select for current rule to fetch IDs
query, value_list, desc = Util.construct_select_query(rule)
if not query or not value_list:
print("Data fetch unsuccessful")
return
# Perform select query and fetch rule based Ids
db_obj.cur.execute(query, value_list)
records = db_obj.cur.fetchall()
# perform action for selected Ids
id_list = Util.get_id_list(records)
query, value_list = Util.construct_update_query(rule, id_list)
if not query:
print("Fetching actions unsuccessfull")
return
if len(id_list) > 0:
value_list.extend(id_list)
db_obj.cur.execute(query, value_list)
db_obj.conn.commit()
print("Name of executed Rule: ", desc)
else:
print("The rule'", desc, "'did not fetch any mails")
# Printing all DB data after all rules have been applied
QueryExecutor.execute_select_all_query(db_obj)