def load():
    database_name = 'database.db'
    dump_name = 'dump.sql'
    database_path = path.join('.', database_name)
    dump_path = path.join('.', dump_name)
    if path.isfile(database_path):
        try:
            os.remove(database_path)
        except:
            print traceback.format_exc(sys.exc_info()[2])
            return
    if not path.isfile(dump_path):
        print "Can't load a database from ASCII sql if there is no ASCII sql."
        return

    con = sqlite3.connect(database_path)
    c = con.cursor()
    raw = ''
    with open(dump_path, 'rU') as dumpfile:
        for line in dumpfile:
            raw += line
            if want_to_execute(raw) and sqlite3.complete_statement(raw):
                c.execute(raw)
                raw = ''
    if want_to_execute(raw) and sqlite3.complete_statement(raw):
        c.execute(raw)
    try:
        con.commit()
    except sqlite3.OperationalError, e:
        print 'Received error: %s' % e
        print 'Ignored said error.'

def get_tanks(filename):
    tank = re.compile('src="pics/vehicle/contour/(?P<country>\w*).(?P<tank>[\w-]*).png"')
    con = sqlite3.connect(filename)
    con.isolation_level = None
    cur = con.cursor()
    query = 'select frags from fights'
    sqlite3.complete_statement(query)
    cur.execute(query)

    tanks = {
    'usa': set(),
    'ussr': set(),
    'germany': set(),
    'france': set(),
#    'china': set(),
#    'uk': set()
    }

    for frag in cur.fetchall():
        for country, name in tank.findall(frag[0]):
            if country in tanks: # add only countries with expert
                tanks[country].add(name)
    con.close()

    return tanks

    def process_sql_statement(self, sql_statement, request):

        query_id = self.data_manager.get_query_id()

        query_start = time.time()

        logging.info("HttpReceiver: (%d) SQL received: %s", query_id, sql_statement.rstrip())

        if not sqlite3.complete_statement(sql_statement):

            # Try adding a semicolon at the end.
            sql_statement = sql_statement + ";"

            if not sqlite3.complete_statement(sql_statement):
                
                response_meta = { "response_op" : "error", 
                                  "error_message" : "Incomplete sql statement",
                                  "identity" : util.Identity.get_identity() }
                request.write(json.dumps(response_meta))
                request.finish()
                return

            # else it is now a complete statement

        d = self.data_manager.async_validate_and_route_query(sql_statement, query_id)

        d.addCallback(self.sql_complete_callback, query_id, query_start, request)

def merge_load():
    database_name = 'database.db'
    dump_name = 'dump.sql'
    database_path = path.join('.', database_name)
    dump_path = path.join('.', dump_name)
    if not path.isfile(dump_path):
        print "Can't merge a database from ASCII sql if there is no ASCII sql."
        return

    con = sqlite3.connect(database_path)
    c = con.cursor()
    raw = ''
    with open(dump_path, 'rU') as dumpfile:
        for line in dumpfile:
            split = line.split()
            if line.startswith('INSERT INTO') and split[2].find('scans') >= 0:
                values = line.find('VALUES(') + len('VALUES(')
                comma = line.find(',', values)
                raw += line[:values] + 'NULL' + line[comma:]
            if want_to_execute(raw) and sqlite3.complete_statement(raw):
                c.execute(raw)
                raw = ''
    if want_to_execute(raw) and sqlite3.complete_statement(raw):
        c.execute(raw)
    try:
        con.commit()
    except sqlite3.OperationalError, e:
        print 'Received error: %s' % e
        print 'Ignored said error.'

def connect_database():
	if os.path.exists(databaseFile):
		print "Exists"
		# establish connection with the database and make one if it does not exist
		conn = sqlite3.connect(databaseFile)
		# making a Cursor Object for executing sqlite3 commands
		global_module.db_cursor = conn.cursor()
	else :
		print "Server Databse Created"
		sql = open(sqlFile,'r').read()
		sqlite3.complete_statement(sql);
		conn = sqlite3.connect(databaseFile)
		global_module.db_cursor = conn.cursor();
		global_module.db_cursor.executescript(sql);
	print type(global_module.db_cursor);

def session_create_database():
    # Relative file addresses
    dbdumpfile = "db_dump.sqlite.sql"

    print "entro"

    if os.path.exists(dbdumpfile) == False:
        return

    # Set write permissions on the database file
    os.chmod(db_filename, 0o666)
    # Read the dump file
    in_file = open(dbdumpfile, "r")
    sqldump = in_file.read()
    if len(sqldump) < 1:
        return

    '''
    sqlite3.complete_statement(sql) returns True if the string sql
    contains
    one or more complete SQL statements terminated by semicolons.
    It does not verify that the SQL is syntactically correct, only that
    there are
    no unclosed string literals and the statement is terminated by a
    semicolon.
    This can be used to build a shell for SQLite.
    '''
    if sqlite3.complete_statement(sqldump):
        conn = sqlite3.connect(db_filename)
        cursor = conn.cursor()
        cursor.executescript(sqldump)
        conn.close()

    print "db creato!"
    return

    def perform(self, query, obj=None):
        #Create a factory to return dictionary
        def dict_factory(cursor, row):
            d = {}
            for idx, col in enumerate(cursor.description):
                d[col[0]] = row[idx]
            return d
        conn = self.get_db()
        conn.row_factory = dict_factory
        c = conn.cursor()
        
        try:
            if lite.complete_statement(query):
                if obj:
                    c.execute(query, obj)
                else:
                    c.execute(query)

            result = c.fetchall()
            #possibly condition commit to only insert/update/delete.
            conn.commit()

        except lite.Error, e:
            result = '%s' % e.args[0]
            print query

    def lineReceived(self, line):
        """
        When a line comes in from the client appened it to the working sql statement.
        If its a complete statement (semicolon terminated), execute it.
        """

        # Exit on lines with just ctrl-d and/or ctrl-c

        m = re.search(r"^\s*[" + chr(4) + chr(3) + "]+\s*$", line, flags=re.IGNORECASE)

        if m != None:
            self.sendLine("Bye!")
            return 1

        # Add this line to the multi-line sql statement.

        self.sql_statement += line + "\n"

        # Do we have a complete sql statement?

        if sqlite3.complete_statement(self.sql_statement):
            
            query_id = self.factory.data_manager.get_query_id()

            query_start = time.time()

            logging.info("SqlReceiver: (%d:%d) SQL received: %s", self.connection_id, query_id, self.sql_statement.rstrip())

            
            # Enqueue
            d = self.factory.data_manager.async_validate_and_route_query(self.sql_statement, query_id)

            d.addCallback(self.sql_complete_callback, query_id, query_start)

def sqlite_userInputCommand_during_run(sqlite_db):
    # A minimal SQLite shell for experiments

    import sqlite3

    con = sqlite3.connect(sqlite_db)   #was   ":memory:"
    con.isolation_level = None
    cur = con.cursor()

    buffer = ""

    print "Enter your SQL commands to execute in sqlite3."
    print "Enter a blank line to exit."

    while True:
        line = raw_input()
        if line == "":
            break
        buffer += line
        if sqlite3.complete_statement(buffer):
            try:
                buffer = buffer.strip()
                cur.execute(buffer)

                if buffer.lstrip().upper().startswith("SELECT"):
                    print cur.fetchall()
            except sqlite3.Error as e:
                print "An error occurred:", e.args[0]
            buffer = ""

    con.close()

def sql_execute(Cursor, Statement, List):
	print "Is SQL", sqlite3.complete_statement(Statement), Statement
	try:
		Result = Cursor.execute(Statement, List)
		return [Result]
	except sqlite3.Error as e :
		return [False, e]

def call_sqlite():
	con = sqlite3.connect(":memory:")
	con.isolation_level = None
	cur = con.cursor()

	buffer = ""

	print "Enter your SQL commands to execute in sqlite3."
	print "Enter a blank line to exit."

	while True:
		line = raw_input()
		if line == "":
			break
		buffer += line
		if sqlite3.complete_statement(buffer):
			try:
				buffer = buffer.strip()
				cur.execute(buffer)
	
				if buffer.lstrip().upper().startswith("SELECT"):
					print cur.fetchall()
			except sqlite3.Error as e:
				print "An error occurred:", e.args[0]
				buffer = ""

	con.close()

	def execute(self, this):
		if self.fileopened:
			ibuf = self.mainTree.get_widget('ExecInput').get_buffer()
			inpu = ibuf.get_text(ibuf.get_start_iter(), ibuf.get_end_iter())
			ebuf = self.mainTree.get_widget('ExecError').get_buffer()
			erro = ebuf.get_text(ebuf.get_start_iter(), ebuf.get_end_iter())
			ef = self.mainTree.get_widget('ExecField')
			if not inpu.rstrip().endswith(";"):
				inpu = inpu+';'
				ibuf.insert(ibuf.get_end_iter(), ';')
			if sqlite3.complete_statement(inpu):
				try:
					ef.remove(self.exectv)
					self.exectv = False
				except:
					pass
				try:
					result = self.sql(inpu, None, False)
					if inpu.lstrip().upper().startswith("SELECT"):
						ls = False
						i = 0
						for row in self.cursor:
							l = len(row)
							list = []
							for field in row:
								list.append(str(field))
							if not ls:
								columns = [str] * l
								ls = gtk.ListStore(*columns)
							ls.append(list)
							i += 1
						if i == 0:
							ebuf.set_text(_('Empty result'))
						else:
							cols = [""]*l
							cells = [""]*l
							labels = [""]*l
							j = 0
							for k in self.cursor.description:
								labels[j] = str(k[0])
								j += 1
							self.exectv = gtk.TreeView(ls)
							for i in range(0,l):
								cols[i] = gtk.TreeViewColumn(labels[i])
								cells[i] = gtk.CellRendererText()
								cols[i].pack_start(cells[i])
								cols[i].add_attribute(cells[i], 'text', i)
								cols[i].set_sizing(gtk.TREE_VIEW_COLUMN_FIXED)
								cols[i].set_fixed_width(100)
								cols[i].set_resizable(True)
								self.exectv.append_column(cols[i])
								self.exectv.show()
							ef.add(self.exectv)
					self.reloadstructure()
					self.reloadbrowse()
				except sqlite3.Error, e:
					ebuf.set_text(_('[SQLite Error] ')+e.args[0])
			else:
				ebuf.set_text(_('[geekSQLite Error] Not a complete statement!'))

def ingest_sqlite_dump(cursor, filename):
    sql = ''
    for line in open(filename, 'rt').readlines():
        sql += line
        if sqlite3.complete_statement(sql):
            sql = sql.strip()
            if sql != 'COMMIT;':
                cursor.execute(sql)
            sql = ''

 def prepare_cursor(self, db_name, q, opts):
     '''Returns executed cursor'''
     conn = self.connections[db_name]
     cur = conn.cursor()
     if sqlite3.complete_statement(q):
         q = q.strip()
         cur.execute(q, opts)
     else:
         raise ValueError('""%s"" is not a valid SQL Statement' % q)
     return cur

	def doExecute(self, data):
		self.doUpdate(">>> "+data)
		if sqlite3.complete_statement(data):			 
			try:
				data = data.strip()
				self.cur.execute(data)
				if data.lstrip().upper().startswith("SELECT"):
					self.doUpdate(self.cur.fetchall())
			except sqlite3.Error as e:
				self.doUpdate("An error occurred:"+str(e.args[0]))

def add(filename, author, *trackers):
    justBuildTableIfNonexistence("FILES")
    justBuildTableIfNonexistence("FILES_TRACKERS")

    connection = sqlite3.connect("share.db")
    connection.isolation_level = None
    cursor = connection.cursor()
    hashValue = hashlib.md5()
    hashValue.update(filename)
    hashValue.update(author)
    hash = hashValue.hexdigest()
    sql_statement = "INSERT INTO FILES(filename, hash, author, chunk_info) VALUES('"+filename+"','"+hash+"','"+author+"', '');"
    print "Going to execute()\n\t"+sql_statement
    if sqlite3.complete_statement(sql_statement):
        try:
            sql_statement = sql_statement.strip()
            cursor.execute(sql_statement)
            #Get file_id
            sql_statement = "SELECT f.file_id FROM files f WHERE hash='"+hash+"';"
            print "Going to execute()\n\t"+sql_statement
            cursor.execute(sql_statement)
            file_id = cursor.fetchone()[0]
            print type(file_id)
            for each_tracker in trackers:
                for each in each_tracker:
                    sql_statement = "INSERT INTO FILES_TRACKERS(file_id, tracker) VALUES("+str(file_id)+",'"+each+"');"
                    print "Going to execute()\n\t"+sql_statement
                    if sqlite3.complete_statement(sql_statement):
                        try:
                            sql_statement = sql_statement.strip()
                            cursor.execute(sql_statement)
                        except sqlite3.Error as e:
                            print "An error occurred:", e.args[0]
                            return None
            return file_id
        except sqlite3.Error as e:
             print "An error occurred:", e.args[0]
             connection.close()
             return None
    connection.close()
    return None

    def create_new_database(self):
        import os
        import sqlite3
        from config import db_dir, db_filepath, experiment_spectrums_path, schema

        log = ""
        # Create 'data' directory
        if not os.path.exists(db_dir):
            os.makedirs(db_dir)
            log += "[Created Directory: " + db_dir + "]" + "\n"

        # Create spectrum database file
        conn = sqlite3.connect(db_filepath)
        log += "[Created Database File: " + db_filepath + " ]" + "\n"

        # Create 'experiments' directory
        if not os.path.exists(experiment_spectrums_path):
            os.makedirs(experiment_spectrums_path)
            log += "[Created Directory: " + experiment_spectrums_path + "]" + "\n"

        # Create Cursor
        cursor = conn.cursor()

        # Execute SQL Script 'build_tables'
        script = open(schema, 'r').read()
        sqlite3.complete_statement(script)

        try:
            cursor.executescript(script)
        except Exception as e:
            cursor.close()
            print "Table Already exists!"

        log += "[Built Tables]" + "\n"

        # Close DB connection
        conn.close()
        log += "[INIT COMPLETE]" + "\n"

        return None, None

 def sql_execute(self,query,params):
     if sqlite3.complete_statement(query):
         try:
             curs = self.db_connection.cursor()
             curs.isolation_level = None #for autocommit mode
             with curs:
                 curs.executemany(query,params)
         except (sqlite3.Error,sqlite3.IntegrityError) as e:
             print "I cant accomplish connection to {0} database. Error {1}".format(db_name, e.message)
             #curs.rollback() #This method rolls back any changes to the database since the last call to commit().
         else:
             curs.commite()
             curs.close()

def justBuildTableIfNonexistence(tablename):
    connection = sqlite3.connect("share.db")
    connection.isolation_level = None
    cursor = connection.cursor()
    tablename = tablename.strip().upper()
    sql_statement = "SELECT name FROM sqlite_master WHERE type='table' AND name='"+tablename+"';"
    print "Going to test if the table '"+tablename+"' exists?"
    if sqlite3.complete_statement(sql_statement):
        try:
            sql_statement = sql_statement.strip()
            cursor.execute(sql_statement)
            if sql_statement.lstrip().upper().startswith("SELECT"):
                if len(cursor.fetchall()) == 0:
                    print tablename+" does not exist, so now going to create one."
                    if tablename == "FILES":
                        sql_statement = "CREATE TABLE "+tablename+"( file_id INTEGER PRIMARY KEY, filename TEXT, hash TEXT, author TEXT, chunk_info TEXT);"
                    elif tablename == "FILES_TRACKERS":
                        sql_statement = "CREATE TABLE "+tablename+"( files_trackers_id INTEGER PRIMARY KEY, file_id INTEGER, tracker TEXT, FOREIGN KEY(file_id) REFERENCES files(file_id));"
                    else:
                        sql_statement = "INVALID"
                    if sql_statement != "INVALID":
                        print "Going to execute"
                        print "\t"+sql_statement
                        if sqlite3.complete_statement(sql_statement):
                            try:
                                sql_statement = sql_statement.strip()
                                connection.execute(sql_statement)
                            except sqlite3.Error as e:
                                print "An error occurred:", e.args[0]
                    else:
                        print "Table '"+tablename+"' has not been defined.\n"
                    connection.close()
                    return True
                else:
                    return False
        except sqlite3.Error as e:
             print "An error occurred:", e.args[0]
    connection.close()

    def process_sql_statement(self, sql_statement, messageId, use_write_db=False):

        query_id = self.data_manager.get_query_id()

        query_start = time.time()

        logging.info("ZmqReceiver: (%d) SQL received: %s", query_id, sql_statement.rstrip())

        if not sqlite3.complete_statement(sql_statement):

            # Try adding a semicolon at the end.
            sql_statement = sql_statement + ";"

            if not sqlite3.complete_statement(sql_statement):
                return self.sql_complete_callback( { "retval" : 1, 
                                                     "error_message" : "Incomplete sql statement" },
                                                   query_id, query_start )

            # else continue

        # Enqueue
        d = self.data_manager.async_validate_and_route_query(sql_statement, query_id, use_write_db=use_write_db)

        d.addCallback(self.sql_complete_callback, query_id, query_start, messageId)