def ForeignKeys(self,
PKCatalogName,
PKSchemaName,
PKTableName,
FKCatalogName,
FKSchemaName,
FKTableName,
handler,
row,
rh,
canceled=None):
q = CScopeUQueue.Lock().SaveString(PKCatalogName).SaveString(
PKSchemaName).SaveString(PKTableName).SaveString(
FKCatalogName).SaveString(FKSchemaName).SaveString(FKTableName)
cb = CAsyncDBHandler.Pair(COdbc.idSQLForeignKeys, handler)
index = self.GetCallIndex()
q.SaveULong(index)
ok = True
with self._csOneSending:
with self._csDB:
self._mapRowset[index] = CAsyncDBHandler.Pair(rh, row)
self._deqResult.append(cb)
ok = self.SendRequest(COdbc.idSQLForeignKeys, q, None, canceled)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def Open(self,
strConnection,
handler=None,
flags=0,
discarded=None,
se=None):
"""
Open a database connection at server side asynchronously
:param strConnection: a database connection string. The database connection string can be an empty string if its server side supports global database connection string
:param hander: a callback for database connecting result
:param flags: a set of flags transferred to server to indicate how to build database connection at server side. It defaults to zero
:param discarded: a callback for tracking cancel or socket closed event. It defaults to None
:param se a callback for tracking an exception from server
:return: True if communication channel is sendable, and False if communication channel is not sendable
"""
ok = True
s = ''
q = CScopeUQueue.Lock().SaveString(strConnection).SaveUInt(flags)
cb = Pair(DB_CONSTS.idOpen, handler)
with self._csOneSending:
#don't make self._csDB locked across calling SendRequest, which may lead to client dead-lock in case a client asynchronously sends lots of requests without use of client side queue.
with self._csDB:
self._flags = flags
self._deqResult.append(cb)
if strConnection:
s = self._strConnection
self._strConnection = strConnection
ok = self.SendRequest(DB_CONSTS.idOpen, q, None, discarded, se)
if not ok:
with self._csDB:
if strConnection:
self._strConnection = s
self._deqResult.remove(cb)
CScopeUQueue.Unlock(q)
return ok
def Statistics(self,
CatalogName,
SchemaName,
TableName,
unique,
reserved,
handler,
row,
rh,
canceled=None):
q = CScopeUQueue.Lock().SaveString(CatalogName).SaveString(
SchemaName).SaveString(TableName).SaveUShort(unique).SaveUShort(
reserved)
cb = CAsyncDBHandler.Pair(COdbc.idSQLStatistics, handler)
index = self.GetCallIndex()
q.SaveULong(index)
with self._csOneSending:
with self._csDB:
self._mapRowset[index] = CAsyncDBHandler.Pair(rh, row)
self._deqResult.append(cb)
ok = self.SendRequest(COdbc.idSQLStatistics, q, None, canceled)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def ForeignKeys(self,
PKCatalogName,
PKSchemaName,
PKTableName,
FKCatalogName,
FKSchemaName,
FKTableName,
handler,
row,
rh,
canceled=None):
q = CScopeUQueue.Lock().SaveString(PKCatalogName).SaveString(
PKSchemaName).SaveString(PKTableName).SaveString(
FKCatalogName).SaveString(FKSchemaName).SaveString(FKTableName)
ok = True
with self._csOneSending:
index = self.GetCallIndex()
q.SaveULong(index)
with self._csDB:
self._mapRowset[index] = Pair(rh, row)
ok = self.SendRequest(
COdbc.idSQLForeignKeys, q, lambda ar: self._ProcessODBC_(
handler, ar, COdbc.idSQLForeignKeys, index), canceled)
if not ok:
with self._csDB:
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def Statistics(self,
CatalogName,
SchemaName,
TableName,
unique,
reserved,
handler,
row,
rh,
canceled=None):
q = CScopeUQueue.Lock().SaveString(CatalogName).SaveString(
SchemaName).SaveString(TableName).SaveUShort(unique).SaveUShort(
reserved)
ok = True
with self._csOneSending:
index = self.GetCallIndex()
q.SaveULong(index)
with self._csDB:
self._mapRowset[index] = Pair(rh, row)
ok = self.SendRequest(
COdbc.idSQLStatistics, q, lambda ar: self._ProcessODBC_(
handler, ar, COdbc.idSQLStatistics, index), canceled)
if not ok:
with self._csDB:
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def SpecialColumns(self,
identifierType,
CatalogName,
SchemaName,
TableName,
scope,
nullable,
handler,
row,
rh,
canceled=None):
q = CScopeUQueue.Lock().SaveShort(identifierType).SaveString(
CatalogName).SaveString(SchemaName).SaveString(
TableName).SaveShort(scope).SaveShort(nullable)
ok = True
with self._csOneSending:
index = self.GetCallIndex()
q.SaveULong(index)
with self._csDB:
self._mapRowset[index] = Pair(rh, row)
ok = self.SendRequest(
COdbc.idSQLSpecialColumns, q, lambda ar: self._ProcessODBC_(
handler, ar, COdbc.idSQLSpecialColumns, index), canceled)
if not ok:
with self._csDB:
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def Prepare(self,
sql,
handler=None,
lstParameterInfo=[],
discarded=None,
se=None):
"""
Send a parameterized SQL statement for preparing with a given array of parameter informations asynchronously
:param sql: a parameterized SQL statement
:param handler: a callback for SQL preparing result
:param lstParameterInfo: a given array of parameter informations
:param discarded: a callback for tracking cancel or socket closed event. It defaults to None
:param se a callback for tracking an exception from server
:return: True if communication channel is sendable, and False if communication channel is not sendable
"""
ok = True
q = CScopeUQueue.Lock().SaveString(sql)
count = 0
if not lstParameterInfo is None:
count = len(lstParameterInfo)
q.SaveUInt(count)
if count > 0:
for one in lstParameterInfo:
one.SaveTo(q)
cb = Pair(DB_CONSTS.idPrepare, handler)
with self._csOneSending:
with self._csDB:
self._deqResult.append(cb)
ok = self.SendRequest(DB_CONSTS.idPrepare, q, None, discarded, se)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
CScopeUQueue.Unlock(q)
return ok
def EndTrans(self,
plan=tagRollbackPlan.rpDefault,
handler=None,
discarded=None,
se=None):
"""
End a manual transaction with a given rollback plan. Note the transaction will be associated with SocketPro client message queue if available to avoid possible transaction lose
:param plan: a value for computing how included transactions should be rollback at server side. It defaults to tagRollbackPlan.rpDefault
:param handler: a callback for tracking its response result
:param discarded: a callback for tracking cancel or socket closed event. It defaults to None
:param se a callback for tracking an exception from server
:return: True if communication channel is sendable, and False if communication channel is not sendable
"""
ok = True
q = CScopeUQueue.Lock().SaveInt(plan)
cb = Pair(DB_CONSTS.idEndTrans, handler)
"""
make sure EndTrans sending and underlying client persistent message queue as one combination sending
to avoid possible request sending/client message writing overlapping within multiple threading environment
"""
with self._csOneSending:
#don't make self._csDB locked across calling SendRequest, which may lead to client dead-lock in case a client asynchronously sends lots of requests without use of client side queue.
with self._csDB:
self._deqResult.append(cb)
ok = self.SendRequest(DB_CONSTS.idEndTrans, q, None, discarded, se)
if ok:
if self._queueOk:
#associate end transaction with underlying client persistent message queue
self.Socket.ClientQueue.EndJob()
self._queueOk = False
else:
with self._csDB:
self._deqResult.remove(cb)
CScopeUQueue.Unlock(q)
return ok
def SpecialColumns(self,
identifierType,
CatalogName,
SchemaName,
TableName,
scope,
nullable,
handler,
row,
rh,
canceled=None):
q = CScopeUQueue.Lock().SaveShort(identifierType).SaveString(
CatalogName).SaveString(SchemaName).SaveString(
TableName).SaveShort(scope).SaveShort(nullable)
cb = CAsyncDBHandler.Pair(COdbc.idSQLSpecialColumns, handler)
index = self.GetCallIndex()
q.SaveULong(index)
ok = True
with self._csOneSending:
with self._csDB:
self._mapRowset[index] = CAsyncDBHandler.Pair(rh, row)
self._deqResult.append(cb)
ok = self.SendRequest(COdbc.idSQLSpecialColumns, q, None, canceled)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def BeginTrans(self,
isolation=tagTransactionIsolation.tiReadCommited,
handler=None,
discarded=None,
se=None):
"""
Start a manual transaction with a given isolation asynchronously. Note the transaction will be associated with SocketPro client message queue if available to avoid possible transaction lose
:param isolation: a value for transaction isolation. It defaults to tagTransactionIsolation.tiReadCommited
:param handler: a callback for tracking its response result. It defaults to None
:param discarded: a callback for tracking cancel or socket closed event. It defaults to None
:param se a callback for tracking an exception from server
:return: True if communication channel is sendable, and False if communication channel is not sendable
"""
ok = True
cb = Pair(DB_CONSTS.idBeginTrans, handler)
q = CScopeUQueue.Lock()
"""
make sure BeginTrans sending and underlying client persistent message queue as one combination sending
to avoid possible request sending/client message writing overlapping within multiple threading environment
"""
with self._csOneSending:
#don't make self._csDB locked across calling SendRequest, which may lead to client dead-lock in case a client asynchronously sends lots of requests without use of client side queue.
with self._csDB:
q.SaveInt(isolation).SaveString(self._strConnection).SaveUInt(
self._flags)
self._deqResult.append(cb)
#associate begin transaction with underlying client persistent message queue
self._queueOk = self.Socket.ClientQueue.StartJob()
ok = self.SendRequest(DB_CONSTS.idBeginTrans, q, None, discarded,
se)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
CScopeUQueue.Unlock(q)
return ok
def _DoMeta3(self, id, s0, s1, s2, handler, row, rh, canceled):
q = CScopeUQueue.Lock().SaveString(s0).SaveString(s1).SaveString(s2)
ok = True
with self._csOneSending:
index = self.GetCallIndex()
q.SaveULong(index)
with self._csDB:
self._mapRowset[index] = Pair(rh, row)
ok = self.SendRequest(
id, q, lambda ar: self._ProcessODBC_(handler, ar, id, index),
canceled)
if not ok:
with self._csDB:
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def _DoMeta3(self, id, s0, s1, s2, handler, row, rh, canceled):
q = CScopeUQueue.Lock().SaveString(s0).SaveString(s1).SaveString(s2)
cb = CAsyncDBHandler.Pair(id, handler)
index = self.GetCallIndex()
q.SaveULong(index)
ok = True
with self._csOneSending:
with self._csDB:
self._mapRowset[index] = CAsyncDBHandler.Pair(rh, row)
self._deqResult.append(cb)
ok = self.SendRequest(id, q, None, canceled)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def _DoMeta4(self, id, s0, s1, s2, s3, handler, row, rh):
q = CScopeUQueue.Lock().SaveString(s0).SaveString(s1).SaveString(s2).SaveString(s3)
cb = CAsyncDBHandler.Pair(id, handler)
index = 0
ok = True
with self._csDB:
self._nCall += 1
index = self._nCall
self._mapRowset[index] = CAsyncDBHandler.Pair(rh, row)
self._deqResult.append(cb)
q.SaveULong(index)
ok = self.SendRequest(id, q, None)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def ExecuteSql(self,
sql,
handler=None,
row=None,
rh=None,
meta=True,
lastInsertId=True,
discarded=None,
se=None):
"""
Process a complex SQL statement which may be combined with multiple basic SQL statements asynchronously
:param sql: a complex SQL statement which may be combined with multiple basic SQL statements
:param handler: a callback for tracking final result
:param row: a callback for tracking record or output parameter returned data
:param rh: a callback for tracking row set of header column informations. Note that there will be NO row set data or its column informations returned if NO such a callback is set
:param meta: a boolean value for better or more detailed column meta details such as unique, not null, primary key, and so on. It defaults to true
:param lastInsertId: a boolean value for last insert record identification number. It defaults to true
:param discarded: a callback for tracking cancel or socket closed event
:param se a callback for tracking an exception from server
:return: True if communication channel is sendable, and False if communication channel is not sendable
"""
ok = True
rowset = isfunction(row)
meta = meta and isfunction(rh)
q = CScopeUQueue.Lock().SaveString(sql).SaveBool(rowset).SaveBool(
meta).SaveBool(lastInsertId)
with self._csOneSending:
index = self.GetCallIndex()
q.SaveULong(index)
#don't make self._csDB locked across calling SendRequest, which may lead to client dead-lock in case a client asynchronously sends lots of requests without use of client side queue.
with self._csDB:
if rowset or meta:
self._mapRowset[index] = Pair(rh, row)
ok = self.SendRequest(
DB_CONSTS.idExecute, q, lambda ar: self._Process_(
handler, ar, DB_CONSTS.idExecute, index), discarded)
if not ok:
with self._csDB:
if rowset or meta:
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def Close(self, handler=None, discarded=None):
"""
Notify connected remote server to close database connection string asynchronously
:param handler: a callback for closing result, which should be OK always as long as there is network or queue available
:param discarded: a callback for tracking cancel or socket closed event. It defaults to None
:return: true if request is successfully sent or queued; and false if request is NOT successfully sent or queued
"""
ok = True
cb = Pair(DB_CONSTS.idClose, handler)
buffer = CScopeUQueue.Lock()
with self._csOneSending:
#don't make self._csDB locked across calling SendRequest, which may lead to client dead-lock in case a client asynchronously sends lots of requests without use of client side queue.
with self._csDB:
self._deqResult.append(cb)
ok = self.SendRequest(DB_CONSTS.idClose, buffer, None, discarded)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
CScopeUQueue.Unlock(buffer)
return ok
def ExecuteSql(self,
sql,
handler=None,
row=None,
rh=None,
meta=True,
lastInsertId=True,
discarded=None):
"""
Process a complex SQL statement which may be combined with multiple basic SQL statements asynchronously
:param sql: a complex SQL statement which may be combined with multiple basic SQL statements
:param handler: a callback for tracking final result
:param row: a callback for tracking record or output parameter returned data
:param rh: a callback for tracking row set of header column informations. Note that there will be NO row set data or its column informations returned if NO such a callback is set
:param meta: a boolean value for better or more detailed column meta details such as unique, not null, primary key, and so on. It defaults to true
:param lastInsertId: a boolean value for last insert record identification number. It defaults to true
:param discarded: a callback for tracking cancel or socket closed event
:return: true if request is successfully sent or queued; and false if request is NOT successfully sent or queued
"""
ok = True
index = self.GetCallIndex()
rowset = (rh or row)
if not rowset:
meta = False
q = CScopeUQueue.Lock().SaveString(sql).SaveBool(rowset).SaveBool(
meta).SaveBool(lastInsertId).SaveULong(index)
cb = Pair(DB_CONSTS.idExecute, handler)
with self._csOneSending:
#don't make self._csDB locked across calling SendRequest, which may lead to client dead-lock in case a client asynchronously sends lots of requests without use of client side queue.
with self._csDB:
if rowset:
self._mapRowset[index] = Pair(rh, row)
self._deqResult.append(cb)
ok = self.SendRequest(DB_CONSTS.idExecute, q, None, discarded)
if not ok:
with self._csDB:
self._deqResult.remove(cb)
if rowset:
self._mapRowset.pop(index)
CScopeUQueue.Unlock(q)
return ok
def ExecuteBatch(self,
isolation,
sql,
vParam,
handler=None,
row=None,
rh=None,
delimiter=';',
batchHeader=None,
discarded=None,
meta=True,
plan=tagRollbackPlan.rpDefault,
vPInfo=None,
lastInsertId=True,
se=None):
"""
Execute a batch of SQL statements on one single call
:param isolation: a value for manual transaction isolation. Specifically, there is no manual transaction around the batch SQL statements if it is tiUnspecified
:param sql: a SQL statement having a batch of individual SQL statements
:param vParam: an array of parameter data which will be bounded to previously prepared parameters. The array size can be 0 if the given batch SQL statement doesn't having any prepared statement
:param handler: a callback for tracking final result
:param row: a callback for receiving records of data
:param rh: a callback for tracking row set of header column informations
:param delimiter: a delimiter string used for separating the batch SQL statements into individual SQL statements at server side for processing
:param batchHeader: a callback for tracking returning batch start error messages
:param discarded: a callback for tracking socket closed or request canceled event
:param meta: a boolean for better or more detailed column meta details such as unique, not null, primary key, and so on
:param plan: a value for computing how included transactions should be rollback
:param vPInfo: a given array of parameter informations which may be empty to some of database management systems
:param lastInsertId: a boolean for last insert record identification number
:param se a callback for tracking an exception from server
:return: True if communication channel is sendable, and False if communication channel is not sendable
"""
if not delimiter:
delimiter = ';'
ok = True
rowset = isfunction(row)
meta = meta and isfunction(rh)
if not vPInfo:
vPInfo = []
q = CScopeUQueue.Lock().SaveString(sql).SaveString(delimiter).SaveInt(
isolation).SaveInt(plan).SaveBool(rowset).SaveBool(meta).SaveBool(
lastInsertId)
queueOk = False
"""
make sure all parameter data sendings and ExecuteParameters sending as one combination sending
to avoid possible request sending overlapping within multiple threading environment
"""
with self._csOneSending:
if vParam and len(vParam):
queueOk = self.Socket.ClientQueue.StartJob()
if not self._SendParametersData(vParam):
CScopeUQueue.Unlock(q)
self._Clean()
return False
index = self.GetCallIndex()
#don't make self._csDB locked across calling SendRequest, which may lead to client dead-lock in case a client asynchronously sends lots of requests without use of client side queue.
with self._csDB:
self._mapParameterCall[index] = vParam
if rowset or meta:
self._mapRowset[index] = Pair(rh, row)
self._mapHandler[index] = batchHeader
q.SaveString(self._strConnection).SaveUInt(self._flags)
q.SaveULong(index)
q.SaveUInt(len(vPInfo))
for one in vPInfo:
one.SaveTo(q)
ok = self.SendRequest(
DB_CONSTS.idExecuteBatch, q, lambda ar: self._Process_(
handler, ar, DB_CONSTS.idExecuteBatch, index), discarded,
se)
if not ok:
with self._csDB:
if rowset or meta:
self._mapRowset.pop(index)
self._mapParameterCall.pop(index)
self._mapHandler.pop(index)
if queueOk:
self.Socket.ClientQueue.EndJob()
CScopeUQueue.Unlock(q)
return ok
def _SendParametersData(self, vParam):
if vParam is None:
return True
size = len(vParam)
self._firstRow = True
def Send(sb):
if sb.GetSize() > 0:
if self._firstRow:
self._firstRow = False
if not self.SendRequest(DB_CONSTS.idBeginRows, sb, None):
return False
else:
if not self.SendRequest(DB_CONSTS.idTransferring, sb,
None):
return False
sb.SetSize(0)
elif self._firstRow:
self._firstRow = False
return self.SendRequest(DB_CONSTS.idBeginRows, None, None)
return True
def SendBlob(sb):
start = True
while sb.GetSize() > DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE:
bytes = sb.PopBytes(DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE)
if start:
if not self.SendRequest(DB_CONSTS.idStartBLOB,
CUQueue(bytes), None):
return False
start = False
else:
if not self.SendRequest(DB_CONSTS.idChunk, CUQueue(bytes),
None):
return False
if not self.SendRequest(DB_CONSTS.idEndBLOB, sb, None):
return False
sb.SetSize(0)
return True
sb = CScopeUQueue.Lock()
for vt in vParam:
if isinstance(vt, str):
#send string as unicode string
length = len(vt) * 2
if length < 2 * DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE:
sb.SaveObject(vt)
else:
if not Send(sb):
CScopeUQueue.Unlock(sb)
return False
length += 6
sb.SaveUInt(length).SaveObject(vt)
if not SendBlob(sb):
CScopeUQueue.Unlock(sb)
return False
elif isinstance(vt, bytearray):
#send array of bytes
length = len(vt)
if length < 2 * DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE:
sb.SaveObject(vt)
else:
if not Send(sb):
CScopeUQueue.Unlock(sb)
return False
length += 6
sb.SaveUInt(length).SaveObject(vt)
if not SendBlob(sb):
CScopeUQueue.Unlock(sb)
return False
else:
sb.SaveObject(vt)
if sb.GetSize(
) >= DB_CONSTS.DEFAULT_RECORD_BATCH_SIZE and not Send(sb):
CScopeUQueue.Unlock(sb)
return False
ok = Send(sb)
CScopeUQueue.Unlock(sb)
return ok
