Changeset 148


Ignore:
Timestamp:
Jul 26, 2009, 9:07:32 PM (11 years ago)
Author:
pvkouteren
Message:

Summary: Logging capabilities, better database setup, config file reading

setup_db.py: Always adds 'superuser' ibidas, when setup is done, the config file is adjusted so the database can't be cleared by accident. Script now also checks if database already exists and if so, prompts what to do.
postgres.py: Checks if views need to be used based on the user currently logged in. Does SQL logging if desired.
config.py: added shortcut method 'getValue'
IbidasLogger?.py: Logging (always present) that can be used by the code from now on.

ini files are set to some better defaults to prevent unwanted behaviour and some new settings added.

Files:
4 added
4 edited

Legend:

Unmodified
Added
Removed
  • config.py

    r147 r148  
    3737    cp.write(open(filename, "w"))
    3838
     39'''
     40When more than one value is needed from a config file in a script, please use the loadConfig method.
     41This method is just a shortcut when only one specific value is of interest.
     42'''
     43def getValue(file, config, value):
     44    cfg = loadConfig(file, config)
     45    return cfg[value]
  • container/postgres.py

    r143 r148  
    2121import os
    2222import sys
     23from log.IbidasLogger import IbidasLogger
    2324
    2425class LogCursor(psycopg2.extensions.cursor):
     
    6263     logger = logging.getLogger('ibidas_sql')
    6364     ''' Use os.getcwd() here because sys.path[0] is not guaranteed to be the right path '''
    64      logdir = os.getcwd() + '/logs'
     65     logdir = os.getcwd() + '/log/logs'
    6566     if (not os.path.isdir(logdir)):
    6667         os.mkdir(logdir)
     
    7071     logger.addHandler(handler)
    7172     logger.setLevel(logging.INFO)
    72      self.logger = logger
    73 
    74    #def _set_isolation_level(self, level):
    75    #    psycopg2.extensions.cursor.set_isolation_level(0)
    76        
    77    #def _get_isolation_level(self):
    78    #    return psycopg2.extensions.cursor.get_isolation_level()
     73     self.sqllogger = logger
    7974       
    8075   def execute(self, sql, args=None, dontlog=None):
    81        """Executes the query in sql.
    82 
    83         args: arguments to place in the query
    84         dontlog: exception class which should not be logged
     76       """
     77       Executes the query in sql.
     78       args: arguments to place in the query
     79       dontlog: exception class which should not be logged
    8580       """
    8681       
     
    10095       do logging of the used tables here as well (if requested).
    10196       """
    102        query = self._switch_to_views(sql, dontlog) 
    103          
     97
     98       if (not self._getLoggedInUser() == 'ibidas'):
     99           query = self._switch_to_views(sql, dontlog)
     100           #print "Using views =("
     101       else:
     102           query = sql
     103           #print "Not using views =)"
    104104
    105105       t1 = time.time()
     
    112112       t2 = time.time()
    113113       totalt = t2 - t1
    114        if (dontlog is None):
     114       #if (dontlog is None):
     115       import config
     116       query_logging = config.getValue('ibidas.ini', { "ibidas.query_logging": 'on'}, "ibidas.query_logging")
     117       if query_logging == 'on':
    115118          self._log_sql(query, totalt)
    116      
    117        #print "Query took " + str(totalt) + " sec"
    118119
    119120   def _switch_to_views(self, query, dontlog=None):
     
    126127          tables = self._extract_queried_tables(query)
    127128          if tables:
    128               self.logger.info(str(timetook) + ";" + (';'.join(tables)))
     129              self.sqllogger.info(str(timetook) + ";" + (';'.join(tables)))
    129130         
    130131   def _extract_queried_tables(self, query):
     
    159160                    substrings2.append(sub)
    160161       
    161         #substrings2 is goed
    162        
    163162        substrings3 = []
    164163        substrings4 = []
     
    186185   def _getViews(self):
    187186        if(not hasattr(self,'views')):
    188             #print str(time.time())+": Querying for views"
    189187            self.execute("SELECT viewname \
    190188                             FROM pg_views \
     
    197195                            for til in views_list]
    198196        return self.views
     197   
     198   def _getLoggedInUser(self):
     199       """ Obtain all views so that we can easily replace tables for views """
     200       if (not(hasattr(self, 'me'))):
     201           psycopg2.extensions.cursor.execute(self, "SELECT \"current_user\"() AS \"current_user\"")
     202           results= psycopg2.extensions.cursor.fetchall(self)
     203           self.me = ""
     204           for res in results:
     205               self.me = res[0]
     206       return self.me
    199207
    200208class PGConnection(psycopg2.extensions.connection):
     209   
     210    def __init__(self, args):       
     211       il = IbidasLogger()
     212       setattr(self, 'logger', il.getInstance())
     213       self.logger.info("Ibidas session started")
     214       psycopg2.extensions.connection.__init__(self, args)
     215   
    201216    def cursor(self):
    202217       """Returns a logging database cursor"""
     
    204219                                           cursor_factory=PatrickLogCursor)
    205220 
     221    #def close(self):
     222    #    self.logger.info("Ibidas session ended")
     223    #    psycopg2.extensions.connection.close(self)
    206224 
    207225    def getSingletonCursor(self):
     
    214232
    215233    def getConSet(self,modifiable=False):
     234       # start logger here
     235       #il = IbidasLogger()
     236       #setattr(self, 'logger', il.getInstance())     
     237       
    216238       tables = self._getTables()
    217239       for table in tables:
  • setup.ini

    r147 r148  
    11[setup]
    2 numpy_path=/Library/Python/2.5/site-packages/numpy/numpy/core/include
    3 db_host=localhost
    4 db_port=5432
    5 db_name=ibidas
    6 db_user=postgres
    7 db_user_pw=postgres
    8 authenticated_mode=yes
    9 setup_type=new
     2db_user = postgres
     3setup_type = none
     4db_port = 5432
     5db_name = ibidas
     6db_host = 127.0.0.1
     7db_user_pw = postgres
     8authenticated_mode = yes
     9
  • setup_db.py

    r147 r148  
    1010from container import postgres
    1111from psycopg2 import ProgrammingError, OperationalError
     12import psycopg2
    1213import sys
    1314import time
     
    2122    "setup.db_name":                "ibidas",
    2223    "setup.db_user":                "postgres",
    23     "setup.db_user_pw ":            "Put the postgres password here",
     24    "setup.db_user_pw":             "Put the postgres password here",
    2425    "setup.db_host":                "127.0.0.1",
    2526    "setup.db_port":                "5432",
    26     "setup.authenticated_mode":     "yes"
     27    "setup.authenticated_mode":     "yes",
     28    "setup.setup_type":             "new"
    2729}
    2830
    2931setupconfig = config.loadConfig('setup.ini', _ConfigDefault)
     32
     33''' Extra security: Check if the database exists. If so, prompt if the user's sure to delete it '''
     34
     35try:
     36    conn = psycopg2.connect("dbname='"+setupconfig['setup.db_name']+"' " \
     37                               "user='"+setupconfig['setup.db_user']+"' "\
     38                               "host='"+setupconfig['setup.db_host']+"' "\
     39                               "port='"+setupconfig['setup.db_port']+"' "\
     40                               "password='"+setupconfig['setup.db_user_pw']+"'");
     41    conn.close()
     42    db_already_exists = True
     43except:
     44    db_already_exists = False
     45
     46if (db_already_exists is True):
     47    yn = ""
     48    while(not (yn == "y" or yn == "n")):
     49        yn = raw_input("The database you've specified in setup.ini already exist. By proceeding, you'll "\
     50                       "completely clear and delete this existing database. Are you sure you want to do this? (y/n)\n")
     51    if (yn == "n"):
     52        print "=== Database setup script overview ==="
     53        print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] User aborted script. Database was not overwritten."
     54        print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Please supply your preferred data in setup.ini and run this setup again"
     55        print "=== End of database setup script ==="
     56        sys.exit()
     57
     58       
     59''' If the database already exists, the user has decided to clear it. Let's do some work! '''
    3060
    3161try:
     
    3363                                 setupconfig['setup.db_port'],"template1",\
    3464                                 setupconfig['setup.db_user'],setupconfig['setup.db_user_pw'])
    35 #conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'");
    3665except OperationalError:
    3766    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Could not open database connection." \
     
    6190''' Drop the user ibidas if it exists '''
    6291
    63 if (setupconfig['setup.authenticated_mode'] == "no"):
    64     try:
    65         cursor.execute("DROP USER ibidas;")
    66         print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Dropping user 'ibidas'.."
    67     except ProgrammingError:
    68         ''' The database did not exist '''
    69         pass
    70     #except OperationalError:
    71     #    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: The database is accessed by a user. Please close all connections, including PgAdmin, and run this script again!"
    72     #    sys.exit()
     92try:
     93    cursor.execute("DROP USER ibidas;")
     94    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Dropping user 'ibidas'.."
     95except ProgrammingError:
     96    ''' The database did not exist '''
     97    pass
     98#except OperationalError:
     99#    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: The database is accessed by a user. Please close all connections, including PgAdmin, and run this script again!"
     100#    sys.exit()
    73101
    74102   
     
    113141
    114142'''
    115 If authenticated_mode is off, we need a 'superuser', so let's create it.
    116 '''
    117 
    118 if (setupconfig['setup.authenticated_mode'] == "no"):
    119     # create one 'superuser'
    120     ibidas_user_sql = "CREATE USER ibidas WITH PASSWORD 'ibidas' NOCREATEDB CREATEUSER CREATEROLE;"
    121     try:
    122         cursor.execute(ibidas_user_sql)
    123     except ProgrammingError:
    124         print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Unable to create superuser ibidas. Please run this script again!"
    125         sys.exit()
     143We need a 'superuser', so let's create it.
     144'''
     145
     146#if (setupconfig['setup.authenticated_mode'] == "no"):
     147# create one 'superuser'
     148ibidas_user_sql = "CREATE USER ibidas WITH PASSWORD 'ibidas' NOCREATEDB CREATEUSER CREATEROLE;"
     149try:
     150    cursor.execute(ibidas_user_sql)
     151except ProgrammingError:
     152    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Unable to create superuser ibidas. Please run this script again!"
     153    sys.exit()
    126154
    127155''' Apply user restrictions on the tables '''
     
    164192for viewtable in existing:
    165193    viewtable_view = viewtable + "_view"
    166     view_sql = "CREATE VIEW " + viewtable_view + " AS SELECT * FROM " + viewtable + " WHERE source_id IN (SELECT * FROM mydatasets_view);"
     194    if viewtable == 'set':
     195        view_sql = "CREATE VIEW " + viewtable_view + " AS SELECT * FROM " + viewtable + " WHERE set_id IN (SELECT source_id FROM mydatasets_view);"
     196    else:
     197        view_sql = "CREATE VIEW " + viewtable_view + " AS SELECT * FROM " + viewtable + " WHERE source_id IN (SELECT * FROM mydatasets_view);"
    167198    rights_sql = "GRANT SELECT ON TABLE " + viewtable_view + " TO PUBLIC"
    168199    try:
     
    194225        "database.host":                setupconfig['setup.db_host'],
    195226        "database.port":                setupconfig['setup.db_port'],
    196         "ibidas.authenticated_mode":    setupconfig['setup.authenticated_mode']
     227        "ibidas.authenticated_mode":    setupconfig['setup.authenticated_mode'],
     228        "ibidas.query_logging":         'on',
     229        "ibidas.log_level":             'DEBUG'
    197230    }
    198231    dbconfig = config.loadConfig('ibidas.ini', _dbConfigDefault)
     
    209242        "database.host":                setupconfig['setup.db_host'],
    210243        "database.port":                setupconfig['setup.db_port'],
    211         "ibidas.authenticated_mode":    setupconfig['setup.authenticated_mode']
     244        "ibidas.authenticated_mode":    setupconfig['setup.authenticated_mode'],
     245        "ibidas.query_logging":         'on',
     246        "ibidas.log_level":             'DEBUG'
    212247    }
    213248    dbconfig = config.loadConfig('ibidas.ini', _dbConfigDefault)
     
    223258cd.close()
    224259
     260''' Change the setup.ini setup_type variable '''
     261print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Resetting setup.ini variable 'setup_type' to prevent clearing this database next time."
     262setupconfig['setup.setup_type'] = 'none'
     263config.writeConfig('setup.ini', setupconfig)
     264
     265print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Setup_db script finished."
    225266print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] We hope you'll enjoy using Ibidas. For support, please visit https://wiki.nbic.nl/index.php/Ibidas"
    226267print "=== End of database setup script ==="
Note: See TracChangeset for help on using the changeset viewer.