def main():
    db = Database(mode='PROD', echo=True)
    if not db.has_tables():
        db.make_tables()
    with open('.smurfs.txt') as f:
        for line in f:
            fetch_and_store_summoner(line.strip(), db)

    session = db.SessionMaker()
    print(session.query(GameStats, GameStats.champion).all())

 def getGlobalPersonCharacteristics(self):        
    query = "SELECT id FROM globalpersonalcharacteristics"
    database = Database()
    atabase.open()
    rows = database.execSelectQuery( query )
    database.close()
    chars = []
    
    for row in rows:
        charid = row[0]
        char = GlobalPersonCharacteristic(charid)
        chars.append( char )
        
    return chars

    def public(self, **kwargs):

        self.__assert_argument("callback", kwargs)
        self.__assert_argument("maxCount", kwargs)

        entries = Database.get_messages(kwargs["maxCount"])
        return self.__get_jsonp_response(kwargs["callback"], entries)

 def new_database(self):
     ret = self.mainAppWindow.gamestateController.unsaved_changes()
     if(not ret == QMessageBox.Cancel):
         file_dialog = QFileDialog()
         filename = file_dialog.getSaveFileName(self.mainAppWindow, self.mainAppWindow.trUtf8('Create New PGN'), \
                                                None, 'PGN (*.pgn)', QFileDialog.DontUseNativeDialog)
         if(filename):
             if(not filename.endswith(".pgn")):
                 filename = filename + ".pgn"
             self.model.gamestate.last_save_dir = QFileInfo(filename).dir().absolutePath()
             db = Database(filename)
             db.create_new_pgn()
             self.mainAppWindow.save.setEnabled(False)
             self.model.database = db
             self.model.user_settings.active_database = db.filename
     self.mainAppWindow.moves_edit_view.setFocus()

 def logIhmTransfer(self, pid, pid_access, projectname, startdate, currency ):
     query = '''INSERT INTO transferlog(pid,pid_access,projectname,datecollected,currency) 
                  VALUES(%s,%s,'%s','%s','%s')''' % (pid, pid_access, projectname, startdate, currency)
                      
     database = Database()
     database.open()
     database.execUpdateQuery( query )
     database.close()

   def __init__(self):
       db = Database({
           "type": 'postgresql',
           "host": 'fg-cn-decaf-head1.cs.upb.de',
           "port": '5432',
           "database": 'decaf_storage',
           "user": 'pgdecaf',
           "password": 'pgdecafpw'
       })
       #db.drop_all()
       db.init_db()
       # Configure logging
       log_file = LOGFILE
       logger = logging.getLogger(__name__)
       logger.setLevel(logging.DEBUG)
       fh = logging.FileHandler(log_file)
       logger.addHandler(fh)

       self.storage = Storage(db, logger=logger)

 def exportHouseholds(self, project,  filename):
     database = Database()
     database.open()
     
     query = '''SELECT hhid, householdname, dateofcollection FROM households
                   WHERE pid=%s''' % project.pid
                   
     households = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for household in households:
         householdline = '''INSERT INTO households(pid,hhid,householdname,dateofcollection) 
                 VALUES({pid},%s, '%s', '%s')<endl>\n''' % (household[0], household[1], household[2])
         ihmFile.write(householdline)
     ihmFile.close()
     
     self.exportHouseholdCharacteristics(project, filename)
     self.exportHouseholdAssets(project, filename)
     self.exportHouseholdExpenditure(project, filename)
     self.exportHouseholdCropIncome(project, filename)
     self.exportHouseholdLivestockIncome(project, filename)
     self.exportHouseholdWildfoodsIncome(project, filename)
     self.exportHouseholdEmploymentIncome(project, filename)
     self.exportHouseholdTransfersIncome(project, filename)
     self.exportHouseholdMembers(project, filename)

 def test_execUpdateQuery(self):
     self.helper.setup_clean_db()
     database = Database()
     database.open()
     database.execUpdateQuery("""
         insert into projects
           (projectname, startdate, enddate, description, currency)
         values
           ('test', '2012-06-04', '2013-07-03', 'a simple test', 'GBP')""")
     query = 'select * from projects'
     self.assertEqual([(2, u'test', datetime.date(2012, 6, 4),
                     datetime.date(2013, 7, 3), u'a simple test', u'GBP')],
                     database.execSelectQuery(query))
     database.close()

 def getProjectsMatching(self,  pid="",  ptitle=""):
     SQLcondition 	= ""
     if ( pid != "" ):
         SQLcondition = " WHERE pid=%s" % pid
     
     if ( ptitle != "" ):
         if ( SQLcondition == "" ):
             SQLcondition = " WHERE projectname LIKE '%" + "%s" % ( ptitle ) + "%'" 
         else:
             SQLcondition = SQLcondition + " OR projectname LIKE '%" + "%s" % ( ptitle ) + "%'" 
             
     query = ''' SELECT pid FROM projects%s''' % ( SQLcondition )
     
     database = Database()
     database.open()
     rows = database.execSelectQuery(  query )
     database.close()
     
     projects = []
     for row in rows:
         pid = row[0]
         project = Project(pid)
         projects.append( project )
     
     return projects

class TestTableExistence(unittest.TestCase):

    @classmethod
    def setUpClass(self):
        self.db = Database('TEST')
        self.db.make_tables()

    @classmethod
    def tearDownClass(self):
        self.db.destroy_tables()

    def tearDown(self):
        self.db.truncate_tables()

    def test_when_all_tables_exist(self):
        assert 'game_stats' in self.db.Meta.tables.keys()
        assert 'game' in self.db.Meta.tables.keys()
        assert 'summoner' in self.db.Meta.tables.keys()
        assert 'summoner_name' in self.db.Meta.tables.keys()

    def test_all_tables_have_rigth_columns(self):
        t = TableTester(self.db)
        test = t.has_next_table()
        while test:
            test()
            test = t.has_next_table()

 def open_database(self):
     dialog = QFileDialog()
     if self.model.gamestate.last_open_dir != None:
         dialog.setDirectory(self.model.gamestate.last_open_dir)
     filename = dialog.getOpenFileName(
         self.mainAppWindow.chessboard_view,
         self.mainAppWindow.trUtf8("Open PGN"),
         None,
         "PGN (*.pgn)",
         QFileDialog.DontUseNativeDialog,
     )
     if filename:
         db = Database(filename)
         db.init_from_pgn(self.mainAppWindow, self.mainAppWindow.trUtf8("Scanning PGN File..."))
         self.model.database = db
         self.model.user_settings.active_database = db.filename
         selectedGame = 0
         if db.no_of_games() > 1:
             dlg = DialogBrowsePgn(db)
             if dlg.exec_() == QDialog.Accepted:
                 items = dlg.table.selectedItems()
                 selectedGame = int(items[0].text()) - 1
             else:
                 selectedGame = None
         if not selectedGame == None and db.no_of_games() > 0:
             loaded_game = db.load_game(selectedGame)
             self.model.gamestate.current = loaded_game
             self.mainAppWindow.chessboard_view.update()
             self.mainAppWindow.chessboard_view.emit(SIGNAL("statechanged()"))
             self.mainAppWindow.save.setEnabled(False)
             self.mainAppWindow.setLabels()
             self.mainAppWindow.moves_edit_view.setFocus()
             self.model.gamestate.last_open_dir = QFileInfo(filename).dir().absolutePath()
             self.model.gamestate.init_game_tree(self.mainAppWindow)
     self.mainAppWindow.moves_edit_view.setFocus()

 def delCorrespondingIhmProject(self, projectname, startdate,  currency):
     ''' Delete if the project was transfered before '''
     
     query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid  
                  AND transferlog.projectname='%s' AND transferlog.datecollected='%s' 
                  AND transferlog.currency='%s' ''' % (projectname,  startdate, currency)
                  
     db = Database()
     db.open() 
     records = db.execSelectQuery( query )
     for record in records:
         pid = record[0]
         query = '''DELETE FROM projects WHERE pid=%s''' % pid
         db.execUpdateQuery( query )
    
     db.close()

 def getProjects(self):       
     query = "SELECT pid FROM projects"
     database = Database()
     database.open()
     rows = database.execSelectQuery( query )
     database.close()
     projects = []
     
     for row in rows:
         pid = row[0]
         project = Project(pid)
         projects.append( project )
         
     return projects

 def importIhmProjectData(self, project, filename):
     dbfile = file(filename, 'r')
     contents = dbfile.read()
     dbfile.close()
     
     queries = contents.split('<endl>\n')
     
     database = Database()
     database.open()
     
     for index in range(len(queries)):
         if index != 0:    # ignore the first row containing project (already created above) 
             query = queries[index].strip()
             if ( query.startswith("INSERT") ):
                 query = query.replace("{pid}", str(project.pid) )
                 print str(index) + " " + query
                 database.execUpdateQuery( query )
         
     database.close()

 def exportProjectIncomeSources(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT incomesource, incometype FROM projectincomesources
                   WHERE pid=%s''' % project.pid
                   
     incomes = database.execSelectQuery( query )
     
     database.close()
     ihmFile = open(filename, 'a')
     for inc in incomes:
         incomeline = '''INSERT INTO projectincomesources(pid,incomesource,incometype) 
         VALUES({pid},'%s','%s')<endl>\n''' % (inc[0], inc[1])
         ihmFile.write(incomeline)
     ihmFile.close()

 def exportHouseholdCharacteristics(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT hhid, characteristic, charvalue FROM householdcharacteristics
                   WHERE pid=%s''' % project.pid
                   
     chars = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for char in chars:
         charline = '''INSERT INTO householdcharacteristics (pid,hhid, characteristic, charvalue )
             VALUES({pid},%s,'%s','%s')<endl>\n'''  % (char[0], char[1], char[2])
         ihmFile.write(charline)
     ihmFile.close()

 def exportHouseholdAssets(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits FROM assets
                   WHERE pid=%s''' % project.pid
                   
     assets = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for asset in assets:
         assetline = '''INSERT INTO assets (pid, hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits )
             VALUES({pid},%s,'%s','%s','%s',%s,%s)<endl>\n''' % (asset[0], asset[1], asset[2], asset[3], asset[4], asset[5])
         ihmFile.write(assetline)
     ihmFile.close()

 def exportProjectDiet(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT fooditem, unitofmeasure, percentage, priceperunit FROM diet
                   WHERE pid=%s''' % project.pid
                   
     diets = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for diet in diets:
         dietline = '''INSERT INTO diet (pid, fooditem,unitofmeasure,percentage, priceperunit )
                     VALUES({pid},'%s','%s',%s,%s)<endl>\n''' % (diet[0], diet[1], diet[2], diet[3])
         ihmFile.write(dietline)
     ihmFile.close()

 def exportProjectAssets(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT assetname, assettype FROM projectassets
                   WHERE pid=%s''' % project.pid
                   
     assets = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for asset in assets:
         assetline = '''INSERT INTO projectassets(pid,assetname,assettype) 
         VALUES({pid},'%s','%s')<endl>\n''' % (asset[0], asset[1])
         ihmFile.write(assetline)
     ihmFile.close()

 def exportHouseholdCropIncome(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM cropincome
                   WHERE pid=%s''' % project.pid
                   
     crops = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for crop in crops:
         cropline = '''INSERT INTO cropincome(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed)
             VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (crop[0],  crop[1], crop[2], crop[3], crop[4], crop[5], crop[6], crop[7])
         ihmFile.write(cropline)
     ihmFile.close()