00001 """
00002 Pythonic representation of a DBI cascade, see :ref:`dbi:cascade` , than
00003 implements spawning of the cascade. Creating a pristine
00004 cascade that can be populated via fixtures.
00005
00006 Advantages :
00007 * allows testing to be perfomed in fully controlled/repeatable DB cascade
00008 * prevents littering production DB with testing detritus
00009
00010 Note such manipulations are not possible with the C++ *DbiCascader* *DbiConnection*
00011 as these fail to be instanciated if the DB does not exist.
00012
00013 """
00014 import os, re, shutil
00015 from datetime import datetime
00016 from GaudiPython import gbl
00017 TUrl = gbl.TUrl
00018 TSQLServer = gbl.TSQLServer
00019
00020 class DBExc(Exception):
00021 pass
00022
00023 class DBCon(dict):
00024 """
00025 Dictionary holding parameters to connect to a DB and
00026 provides functionality to drop/create databases
00027 and run updates/queries against them.
00028 """
00029 prefix = 'tmp_'
00030 _mysqldump = "mysqldump --no-defaults --host=%(host)s --user=%(user)s --password=%(pswd)s %(opt)s %(name)s "
00031
00032
00033 def __init__( self, url, user, pswd , **kwa ):
00034 self.url_ = TUrl(url)
00035 assert self.url_.IsValid()
00036 self.origname = self.name
00037 self.user = user
00038 self.pswd = pswd
00039 self.fix = kwa.get('fix', None)
00040 self.verbosity = kwa.get('verbosity', 0)
00041 name = kwa.get('name', None)
00042 if name:
00043 self.name = name
00044 self._server = None
00045 self._attempt = 0
00046
00047 url = property( lambda self:os.path.expanduser(os.path.expandvars(self.url_.GetUrl())), lambda self,n:self.url_.SetUrl(n) )
00048 name = property( lambda self:self.url_.GetFile(), lambda self,n:self.url_.SetFile(n) )
00049 host = property( lambda self:self.url_.GetHost(), lambda self,n:self.url_.SetHost(n) )
00050 ascii = property( lambda self:self.url_.GetAnchor(), lambda self,n:self.url_.SetAnchor(n) )
00051 opts = property( lambda self:self.url_.GetOptions(), lambda self,n:self.url_.SetOptions(n) )
00052 creds = property( lambda self:dict( name=self.name, host=self.host, user=self.user, pswd=self.pswd ))
00053
00054 def __repr__(self):
00055 return "DBCon(\"%s\",\"%s\",\"%s\", fix=\"%s\" )" % ( self.url , self.user, "***" , self.fix )
00056
00057 def log(self):
00058 return "\n".join( [ dict.__repr__(self) ] )
00059
00060 def errlog(self, dbn ):
00061 if self.server.GetErrorCode() == 0:return
00062 if self.get(dbn,None) == None:
00063 self[dbn] = []
00064 self[dbn].append( self.server.GetErrorMsg() )
00065
00066 def _get_server(self):
00067 """
00068 If the connection attempt fails, try again without specifying the DB name, see :root:`TMySQLServer`
00069
00070 .. todo::
00071
00072 Find way to avoid/capture the error after failure to connect
00073
00074 """
00075 if not(self._server):
00076 self._server = TSQLServer.Connect( self.url , self.user , self.pswd )
00077 self._attempt += 1
00078 if self._server == None:
00079 if self.name != "" and self._attempt < 3:
00080 print "attempt %s failed to connect to %s, try again without specifying the DB " % ( self._attempt, self.url )
00081 self.name = ""
00082 self._get_server()
00083 else:
00084 print "attempt %s failed to connect to %s " % ( self._attempt , self.url )
00085 else:
00086 print "attempt %s succeeded to connect to %s " % ( self._attempt, self.url )
00087 return self._server
00088 server = property( _get_server, doc=_get_server.__doc__ )
00089
00090 def drop(self, dbn ):
00091 if dbn == self.origname:
00092 raise DBExc("cannot drop DB of the originating name \"%s\" for safety" % self.origname )
00093 if self.server.DropDataBase( dbn ) == 0:
00094 if self.verbosity>1:
00095 print "succeeded to drop db \"%s\" " % dbn
00096 else:
00097 print "failed to drop db \"%s\" " % dbn
00098 self.errlog(dbn)
00099
00100 def create(self, dbn , autoclobber=True ):
00101 if dbn == self.origname:
00102 raise DBExc("cannot create DB of the originating name \"%s\" for safety" % self.origname )
00103 self.server.EnableErrorOutput(False)
00104 if self.server.CreateDataBase( dbn ) == 0:
00105 if self.verbosity>1:
00106 print "succeeded to create db \"%s\" " % dbn
00107 else:
00108 err, msg = self.server.GetErrorCode(), self.server.GetErrorMsg()
00109 if err == 1007 and autoclobber == True and len(self.get(dbn,())) < 5:
00110 if self.verbosity>2:
00111 print "failed to create db \"%s\" as it exists already ... " % dbn
00112 self.errlog(dbn)
00113 self.drop(dbn)
00114 self.create(dbn)
00115 else:
00116 print "failed to create db \"%s\" due to %s \"%s\" " % (dbn, err, msg )
00117
00118 self.server.EnableErrorOutput(True)
00119
00120 def _spawn(self, **kwa ):
00121 """
00122 Spawn a DBCon instance ... applying fixture if defined
00123 """
00124 con = DBCon( self.url , self.user, self.pswd , **kwa )
00125 con.fixture()
00126 return con
00127
00128 def spawn( self , fixpass=False ):
00129 """
00130 Create new DB with prefixed name and spawn a DBCon to talk to it with
00131
00132 When *fixpass* is True the DB is neither created or dropped, but it is assumed
00133 to exist. This is used when doing DBI double dipping, used for example in
00134 :dybgaudi:`Database/DBWriter/tests`
00135
00136 """
00137 name = "%s%s" % (DBCon.prefix, self.origname)
00138 if not fixpass:
00139 self.create( name )
00140 return self._spawn( name=name, fix=self.fix )
00141
00142 def tables(self):
00143 return self.server.GetTablesList()
00144
00145 isconnected = property(lambda self:self.server.IsConnected())
00146
00147 def process(self, sql):
00148 """
00149 Attempts to create prepared statement from sql then processes it
00150 """
00151 if sql.strip() == "":
00152 if self.verbosity>2:
00153 print "skipping blank line"
00154 return True
00155
00156 if sql.endswith("insert into DbiDemoData3 values( data supplied by DbiDemodata3 - see DbiValidate::PrepareDatabases() )"):
00157 if self.verbosity>0:
00158 print "skipping DbiValidate atrocity "
00159 return True
00160
00161
00162 if not(self.isconnected):
00163 print "not connected, cannot process \"%s\" " % sql
00164 return
00165 st = self.server.Statement( sql )
00166 if not(st):
00167 print "failed to create statement ... probably an error in the sql "
00168 return
00169 ok = st.Process()
00170 if False == ok:
00171 print "error during processing of statement for sql \"%s\" " % sql
00172 return ok
00173
00174 def populate(self):
00175 self.process( "create table dummy ( val int )")
00176
00177 def fixture(self):
00178 if self.fix == None:
00179 return
00180 self.fixture_( self.fix )
00181
00182 def mysqldump(self, opt ):
00183 return self._mysqldump % dict( self.creds, opt=opt )
00184
00185 def dump(self, path=None, opt="" ):
00186 if not self.ascii:
00187 cmd = path and "%s > %s " % ( self.mysqldump(opt) , path ) or self.mysqldump(opt)
00188
00189 return os.popen( cmd )
00190
00191 def fixture_(self, path_ ):
00192 if not(self.name.startswith(DBCon.prefix)):
00193 raise DBExc("as safety measure cannot apply fixture to DB %s, name must start with prefix \"%s\" " % ( self.name, DBCon.prefix) )
00194 path = os.path.expandvars(os.path.expanduser(path_))
00195 print "%s fixture reading path %s " % (self, path_ )
00196 for sql in open(path).read().split(";"):
00197 if not(self.process(sql)):
00198 print "error during processing of \"%s\" " % sql
00199
00200 class DBCas(list):
00201 """
00202 Represents a cascade of databases (a list of :class:`DBCon` instances)
00203 created from a :class:`DybPython.dbconf.DBConf` instance
00204 """
00205 def __init__( self, cnf , append=True ):
00206 self.cnf = cnf
00207 if not(append):return
00208 urls = cnf.urls
00209 users = cnf.users
00210 pswds = cnf.pswds
00211 fixs = cnf.fixs
00212 for i,url in enumerate(urls):
00213 user = i >= len(users) and users[0] or users[i]
00214 pswd = i >= len(pswds) and pswds[0] or pswds[i]
00215 fix = i < len(fixs) and fixs[i] or None
00216 self.append( DBCon( url, user, pswd, fix=fix ) )
00217
00218 url = property(lambda self:";".join([_.url for _ in self]))
00219 user = property(lambda self:";".join([_.user for _ in self]))
00220 pswd = property(lambda self:";".join([_.pswd for _ in self]))
00221 host = property(lambda self:";".join([_.host for _ in self]))
00222 db = property(lambda self:";".join([_.name for _ in self]))
00223
00224 def dump(self, dir=None , opt="" ):
00225 if dir and not os.path.exists(dir):
00226 os.mkdir(dir)
00227 for i,c in enumerate(self):
00228 name = "%s.sql" % i
00229 path = dir and os.path.join( dir, name ) or name
00230 c.dump( path , opt )
00231
00232 def Dump(cls, dir=".", opt=" --extended-insert=0 --skip-comments ", cfdir=None, reference=False ):
00233 from DybPython import DBConf
00234 cnf = DBConf.from_env()
00235 cas = DBCas(cnf)
00236 dir = os.path.expandvars(dir)
00237 if not os.path.exists(dir):
00238 os.makedirs(dir)
00239 print "DBCas.Dump into %s " % dir
00240 cas.dump( dir=dir, opt=opt )
00241 if cfdir or reference:
00242 DD.Compare( cas, dir, cfdir, reference )
00243
00244 Dump = classmethod(Dump)
00245
00246 def spawn(self):
00247 """
00248 Spawning a cascade creates the databases in the cascade with prefixed names
00249 and populates them with fixtures
00250 """
00251 cas = DBCas(self.cnf, append=False)
00252 for _ in self:
00253 cas.append(_.spawn(fixpass=self.cnf.fixpass))
00254 return cas
00255
00256
00257
00258 class Prep(dict):
00259 def __call__(self, txt ):
00260 for pt,rp in self.items():
00261 txt = re.sub(pt, rp, txt )
00262 return txt.strip()
00263
00264 class DD(dict):
00265 """
00266 Compares directories contained cascade mysqldumps
00267 after first replacing the times from todays dates
00268 avoiding inevitable validity insert time differences
00269
00270 Successful comparison Requires the *DbiTest* and *DybDbiTest* dumps
00271 to be created on the same UTC day.
00272 """
00273
00274 def Compare( cls, cas , dir , cfdir, reference=False ):
00275 if not cfdir and reference == True:
00276 cfdir = dir
00277 dir = os.path.expandvars(dir)
00278 cfdir = os.path.expandvars(cfdir)
00279 print "DD.Compare comparing %s with %s reference %s " % ( dir, cfdir, reference )
00280 assert os.path.isdir(dir), dir
00281 assert os.path.isdir(cfdir), cfdir
00282 for dbno,con in enumerate(cas):
00283 if not con.ascii:
00284 dd = DD(dbno=dbno, dir=dir , cfdir=cfdir , reference=reference )
00285 print dd.assert_()
00286 Compare = classmethod(Compare)
00287
00288 def get_prep(self):
00289 """
00290 Initially this just obscured the times in UTC todays date
00291 (which appears in the Vld table INSERTDATE column) to
00292 allow comparison between DbiTest and DybDbiTest runs done on the same UTC day
00293
00294 However, now that are extending usage of the MYSQLDUMP reference comparisons
00295 to dumps of DBWriter created DB from different days, need to obscure todays date fully
00296 """
00297 if not hasattr(self,'_prep'):
00298 today = datetime.utcnow().strftime("%Y-%m-%d")
00299 self._prep = Prep({re.compile("%s \d{2}:\d{2}:\d{2}" % today ):"....-..-.. ..:..:.." })
00300 return self._prep
00301 prep = property(get_prep)
00302
00303 def dif_(cls, a , b , prep ):
00304 from difflib import unified_diff
00305 return unified_diff( map(prep,open(a).readlines()), map(prep,open(b).readlines()) , a, b )
00306 dif_ = classmethod( dif_ )
00307
00308 def __call__(self):
00309 ref = ".ref" if self['reference'] else ""
00310 a = os.path.join( self['dir'], "%s.sql" % self['dbno'] )
00311 b = os.path.join( self['cfdir'], "%s.sql%s" % ( self['dbno'] , ref ) )
00312 print "DD a", a
00313 print "DD b", b
00314
00315 assert os.path.exists(a) , a
00316 if self['reference']:
00317 if not os.path.exists(b):
00318 print "b %s does not exist, blessing a %s to reference " % ( b, a )
00319 shutil.copyfile( a, b)
00320 else:
00321 assert os.path.exists(b) , b
00322 p = self.prep
00323
00324 return self.dif_( a , b , p )
00325
00326 def assert_(self):
00327 assert len([l for l in self()]) == 0 , self
00328 return self
00329
00330 def __repr__(self):
00331 return "\n".join(["%s %s" % ( self.__class__.__name__, dict.__repr__(self)) ] + [l for l in self()])
00332
00333
00334
00335
00336
00337
00338
00339
00340 if __name__=='__main__':
00341 pass
00342 os.environ.update(
00343 DBCONF='dybdbitest',
00344 DBCONF_URL='mysql://%(host)s/%(database)s_0;mysql://%(host)s/%(database)s_1;mysql://%(host)s/%(database)s_2#$DBITESTROOT/scripts/DemoASCIICatalogue.db',
00345 DBCONF_USER='%(user)s',
00346 DBCONF_PSWD='%(password)s',
00347 DBCONF_FIX='$DBITESTROOT/scripts/prepare_db_0.sql;$DBITESTROOT/scripts/prepare_db_1.sql',
00348 )
00349
00350 from DybPython import DBConf
00351 cnf = DBConf()
00352 cas = DBCas(cnf)
00353 print cas
00354 tas = cas.spawn()
00355 print tas
00356
00357
00358
