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 fix = property(lambda self:";".join([_.fix or "" for _ in self]))
00224
00225 def dump(self, dir=None , opt="" ):
00226 if dir and not os.path.exists(dir):
00227 os.mkdir(dir)
00228 for i,c in enumerate(self):
00229 name = "%s.sql" % i
00230 path = dir and os.path.join( dir, name ) or name
00231 c.dump( path , opt )
00232
00233 def Dump(cls, dir=".", opt=" --extended-insert=0 --skip-comments ", cfdir=None, reference=False ):
00234 from DybPython import DBConf
00235 cnf = DBConf.from_env()
00236 cas = DBCas(cnf)
00237 dir = os.path.expandvars(dir)
00238 if not os.path.exists(dir):
00239 os.makedirs(dir)
00240 print "DBCas.Dump into %s " % dir
00241 cas.dump( dir=dir, opt=opt )
00242 if cfdir or reference:
00243 DD.Compare( cas, dir, cfdir, reference )
00244
00245 Dump = classmethod(Dump)
00246
00247 def spawn(self):
00248 """
00249 Spawning a cascade creates the databases in the cascade with prefixed names
00250 and populates them with fixtures
00251 """
00252 cas = DBCas(self.cnf, append=False)
00253 for _ in self:
00254 cas.append(_.spawn(fixpass=self.cnf.fixpass))
00255 return cas
00256
00257
00258
00259 class Prep(dict):
00260 def __call__(self, txt ):
00261 for pt,rp in self.items():
00262 txt = re.sub(pt, rp, txt )
00263 return txt.strip()
00264
00265 class DD(dict):
00266 """
00267 Compares directories contained cascade mysqldumps
00268 after first replacing the times from todays dates
00269 avoiding inevitable validity insert time differences
00270
00271 Successful comparison Requires the *DbiTest* and *DybDbiTest* dumps
00272 to be created on the same UTC day.
00273 """
00274
00275 def Compare( cls, cas , dir , cfdir, reference=False ):
00276 if not cfdir and reference == True:
00277 cfdir = dir
00278 dir = os.path.expandvars(dir)
00279 cfdir = os.path.expandvars(cfdir)
00280 print "DD.Compare comparing %s with %s reference %s " % ( dir, cfdir, reference )
00281 assert os.path.isdir(dir), dir
00282 assert os.path.isdir(cfdir), cfdir
00283 for dbno,con in enumerate(cas):
00284 if not con.ascii:
00285 dd = DD(dbno=dbno, dir=dir , cfdir=cfdir , reference=reference )
00286 print dd.assert_()
00287 Compare = classmethod(Compare)
00288
00289 def get_prep(self):
00290 """
00291 Initially this just obscured the times in UTC todays date
00292 (which appears in the Vld table INSERTDATE column) to
00293 allow comparison between DbiTest and DybDbiTest runs done on the same UTC day
00294
00295 However, now that are extending usage of the MYSQLDUMP reference comparisons
00296 to dumps of DBWriter created DB from different days, need to obscure todays date fully
00297 """
00298 if not hasattr(self,'_prep'):
00299 today = datetime.utcnow().strftime("%Y-%m-%d")
00300 self._prep = Prep({re.compile("%s \d{2}:\d{2}:\d{2}" % today ):"....-..-.. ..:..:.." })
00301 return self._prep
00302 prep = property(get_prep)
00303
00304 def dif_(cls, a , b , prep ):
00305 from difflib import unified_diff
00306 return unified_diff( map(prep,open(a).readlines()), map(prep,open(b).readlines()) , a, b )
00307 dif_ = classmethod( dif_ )
00308
00309 def __call__(self):
00310 ref = ".ref" if self['reference'] else ""
00311 a = os.path.join( self['dir'], "%s.sql" % self['dbno'] )
00312 b = os.path.join( self['cfdir'], "%s.sql%s" % ( self['dbno'] , ref ) )
00313 print "DD a", a
00314 print "DD b", b
00315
00316 assert os.path.exists(a) , a
00317 if self['reference']:
00318 if not os.path.exists(b):
00319 print "b %s does not exist, blessing a %s to reference " % ( b, a )
00320 shutil.copyfile( a, b)
00321 else:
00322 assert os.path.exists(b) , b
00323 p = self.prep
00324
00325 return self.dif_( a , b , p )
00326
00327 def assert_(self):
00328 assert len([l for l in self()]) == 0 , self
00329 return self
00330
00331 def __repr__(self):
00332 return "\n".join(["%s %s" % ( self.__class__.__name__, dict.__repr__(self)) ] + [l for l in self()])
00333
00334
00335
00336
00337
00338
00339
00340
00341 if __name__=='__main__':
00342 pass
00343 os.environ.update(
00344 DBCONF='dybdbitest',
00345 DBCONF_URL='mysql://%(host)s/%(database)s_0;mysql://%(host)s/%(database)s_1;mysql://%(host)s/%(database)s_2#$DBITESTROOT/scripts/DemoASCIICatalogue.db',
00346 DBCONF_USER='%(user)s',
00347 DBCONF_PSWD='%(password)s',
00348 DBCONF_FIX='$DBITESTROOT/scripts/prepare_db_0.sql;$DBITESTROOT/scripts/prepare_db_1.sql',
00349 )
00350
00351 from DybPython import DBConf
00352 cnf = DBConf()
00353 cas = DBCas(cnf)
00354 print cas
00355 tas = cas.spawn()
00356 print tas
00357
00358
00359
