def test_delete(self):
self.mustExecOk(
"insert into student (id, name, age) values (-3, 'n3', 10);")
self.mustExecOk(
"insert into student (id, name, age) values (-1, 'n1', 10);")
self.mustExecOk(
"insert into student (id, name, age) values (0, 'n0', 5);")
self.mustExecOk(
"insert into student (id, name, age) values (1, 'n1', 5);")
self.mustExecOk(
"insert into student (id, name, age) values (3, 'n3', 10);")
sql = '''DELETE FROM student \
WHERE id > -2 \
AND id < 2 \
OR name = 'n3' \
AND age in (5, 8)
'''
# row 3, 4 will be deleted
ctx = self.mustExecOk(sql)
self.assertEqual(ctx.status.affectedRows, 2)
sql = '''SELECT id, name, age From student where id > -100'''
ctx = self.mustExecOk(sql)
logger.debug(ctx.rows)
self.assertEqual(ctx.fields, ['id', 'name', 'age'])
self.assertEqual(ctx.rows, [
[-3, 'n3', 10],
[-1, 'n1', 10],
[3, 'n3', 10],
])
def test_CommitRetryable(self):
col1 = 'student.id.data'
col2 = 'student.name.data'
col3 = 'student.age.data'
txn1, txn2, txn3 = self.store.Begin(), self.store.Begin(), self.store.Begin()
# txn1 locks "b"
err = txn1.Set("b", "b1", col2)
self.assertEqual(err, None)
txn1.us.WalkBuffer()
err = txn1.prewrite()
self.assertEqual(err, None)
# txn3 writes "c"
err = txn3.Set("c", "c3", col3)
self.assertEqual(err, None)
err = txn3.Commit()
self.assertEqual(err, None)
# txn2 writes "a"(PK), "b", "c" on different regions.
# "c" will return a retryable error.
# "b" will get a Locked error first
err = txn2.Set("a", "a2", col1)
self.assertEqual(err, None)
err = txn2.Set("b", "b2", col2)
self.assertEqual(err, None)
err = txn2.Set("c", "c2", col3)
self.assertEqual(err, None)
err = txn2.Commit()
self.assertNotEqual(err, None)
self.assertEqual(err, ErrRetry)
logger.debug(err)
def test_select(self):
self.mustExecOk(
"insert into student (id, name, age) values (-3, 'n3', 10);")
self.mustExecOk(
"insert into student (id, name, age) values (-1, 'n1', 10);")
self.mustExecOk(
"insert into student (id, name, age) values (0, 'n0', 5);")
self.mustExecOk(
"insert into student (id, name, age) values (1, 'n1', 5);")
self.mustExecOk(
"insert into student (id, name, age) values (3, 'n3', 10);")
sql = '''SELECT id, name, age From student \
WHERE id > -2 \
AND id < 2 \
OR name = 'n3' \
AND age in (5, 8)
'''
ctx = self.mustExecOk(sql)
logger.debug(ctx.rows)
self.assertEqual(ctx.fields, ['id', 'name', 'age'])
self.assertEqual(ctx.rows, [
[0, 'n0', 5],
[1, 'n1', 5],
])
def Begin(self): # (Transaction, error)
'''
@return: Transaction
'''
txn = Transaction(self)
logger.debug('new txn, startTS=%d', txn.startTS)
return txn
def isInQueuedTasks(self, task, queuedTasks):
for temp in queuedTasks:
logger.debug("queuedTask: %s, task: %s " %
(temp.get('task_id'), task.get('task_id')))
if temp.get('task_id') == task.get('task_id'):
return True
return False
def test_update(self):
self.mustExecOk(
"insert into student (id, name, age) values (-3, 'n3', 10);")
self.mustExecOk(
"insert into student (id, name, age) values (-1, 'n1', 10);")
self.mustExecOk(
"insert into student (id, name, age) values (0, 'n0', 5);")
self.mustExecOk(
"insert into student (id, name, age) values (1, 'n1', 5);")
self.mustExecOk(
"insert into student (id, name, age) values (3, 'n3', 10);")
sql = '''UPDATE student \
SET age = 6 \
WHERE id > -2 \
AND id < 2 \
OR name = 'n3' \
AND age in (5, 8)
'''
# row 3, 4 will be updated
ctx = self.mustExecOk(sql)
self.assertEqual(ctx.status.affectedRows, 2)
sql = '''SELECT id, name, age From student where id in (0, 1)'''
ctx = self.mustExecOk(sql)
logger.debug(ctx.rows)
self.assertEqual(ctx.fields, ['id', 'name', 'age'])
self.assertEqual(ctx.rows, [
[0, 'n0', 6],
[1, 'n1', 6],
])
def Execute(self, ctx):
'''
@param ctx: Context
@return: err in parser or executor
'''
t0 = time.time()
# 1. sql string to statement
err = self.parser.Parser(ctx)
t1 = time.time()
# 2. statement to executor
self.planner.BuildExecutor(ctx)
t2 = time.time()
# 3. executor to txn
if self.autocommit and self.IsDML(ctx):
self.BeginTxn(ctx)
err = ctx.executor.Execute()
if err:
self.RollbackTxn(ctx)
else:
err = self.CommitTxn(ctx)
else:
err = ctx.executor.Execute()
t3 = time.time()
ctx.SetErr(err)
logger.debug("all=%f,parser=%f,plan=%f,exec=%f", t3 - t0, t1 - t0,
t2 - t1, t3 - t2)
return err
def Set(self, session):
'''
@param session: Session
@return: ErrSessionMaxSize
'''
self.lock.Lock()
if session.session_id in self.sessions:
del self.sessions[session.session_id]
elif SessionMaxSize <= len(self.sessions):
# assert not empty
assert self.sessions
# get first element if max size
for sid, s in self.sessions.iteritems():
if s.IsExpired():
logger.debug("pop expired session, id=%d", sid)
self.sessions.pop(sid)
break
else:
logger.debug("max session num")
self.lock.UnLock()
return ErrSessionMaxSize
# update the session to the last of the sessions
self.sessions[session.session_id] = session
self.lock.UnLock()
return None
def upsert(self, position_info: dict):
if tools.check_db_position_count():
return 1
if tools.check_db_position_count(position_info['city']):
return 2
# 利用upsert去重
res = self.post.update_one(
filter={
'position_name': position_info['position_name'],
'city': position_info['city'],
'salary': position_info['salary'],
# 'company_id': position_info['company_id'],
},
update={
'$set': {
'position_name': position_info['position_name'],
'city': position_info['city'],
'salary': position_info['salary'],
},
},
upsert=True)
if not res.matched_count == 1:
logger.debug(f'<{self.name}>: \n- 插入记录{position_info}到数据库中')
tools.db_position_count.update([position_info['city']])
logger.debug(f'<{self.name}>:\n- '
f'当前累计数量：{tools.db_position_count}')
return 0
def rule_check(src, tar, rule="louhao"):
src = utils.clr(src)
tar = utils.clr(tar)
logger.debug("%s %s\n" % (src, tar))
if rule == "louhao":
#reg0 = re.compile("([一二三四五六七八九零]+?[号杠])(?:.*?)?([一二三四五六七八九零]+?[号杠$])")
reg0 = myconfig.CHECK_RULE_LOUHAO
src0 = re.findall(reg0, src)
tar0 = re.findall(reg0, tar)
if len(src0) > 0:
src0 = "".join(src0[0])
else:
src0 = ""
if len(tar0) > 0:
tar0 = "".join(tar0[0])
else:
tar0 = ""
print(src0, tar0, src, tar)
if src0 == tar0 and not src0 == "":
return True
else:
return False
elif rule == "jieluxiang":
#======
#reg0 = re.compile("\D\D\D[街道路巷]")
reg0 = myconfig.CHECK_RULE_JIEDAO
src0 = re.findall(reg0, src)
tar0 = re.findall(reg0, tar)
if len(src0) > 0 and len(tar0) > 0:
if src0[-1] == tar0[-1]:
return True
return False
else:
print(rule)
def Get(self, key, col):
'''Get value by key from local membuffers or mvccdb.
@param key: key to get.
@param col: column name used to locate a mvccdb
@return: value, err
@rtype: str, ErrTxnTimeOut
'''
# 1. find local store first
value = self.us.Get(key, col)
if value:
logger.debug('k=%s,v=%s,col=%s,err=%d', key, value, col, 0)
return value, None
# 2. if key not found in local store, then try to find it from remote mvcc store
db = self.store.GetMvccDB(col)
value, err = db.Get(key, self.startTS, self.isoLevel)
locked = False
cleanup = False
# backoffAndMaybeCleanupLock
if err:
locked = True
pairs, err = self.backoffAndMaybeCleanupLock([err], col)
if not err:
value = pairs[0][1]
cleanup = True
logger.debug('k=%s,v=%s,col=%s,err=%s,locked=%d,cleanup=%d', key,
value, col, err, locked, cleanup)
return value, err
def mainTask(self):
cpt = cpTime.calcProhibitedTime()
executor = ThreadPoolExecutor()
isFirst = True
queuedTasks = []
threadedTasks = []
count = 0
tasksInDb = self.dbConn.selectDialTask()
while (True):
if not isFirst:
tasksInDb = self.dbConn.selectDialTask()
else:
isFirst = False
logger.info("tasksInDb: %s", tasksInDb)
logger.info("queuedTasks: %s", queuedTasks)
logger.info("threadedTasks: %s", threadedTasks)
for task in tasksInDb:
if not self.isInQueuedTasks(task, queuedTasks):
queuedTasks.append(task)
if not self.isTaskTheaded(task, threadedTasks):
taskRec = self.dbConn.selectDialTaskById(
task.get('task_id'))
logger.debug("taskRec to thread: %s" % (taskRec))
if len(taskRec) == 0:
continue
prohibitSlot = taskRec.get('task_timegroup')
if cpt.isTimeSlotProhibit(prohibitSlot):
logger.info("Currently it's prohibited to dial for task %s, don't init dialThread."%\
(task.get('task_id')))
logger.info("Ths task's prohibitSlot is %s" %
(prohibitSlot))
continue
else:
logger.debug("to submit thread: %s" %
(task.get('task_id')))
executor.submit(self.handleTask, taskRec, prohibitSlot)
threadedTasks.append(task)
count = count + 1
if count == 30:
count = 0
records = self.dbConn.selectLatestTime()
for record in records:
taskid = record.get("task_id")
try:
logger.info("任务:%s,已经20分钟没有呼出成功了,先暂停了" % (taskid))
self.hc.sendMsgTimeOut(taskid)
self.dbConn.updateTbTask(taskid, 2)
except Exception as e:
logger.error("Error: sendmsg error: %s." % (e))
logger.info("wait %s seconds to take new task. count:%d\n",
self.dialInterval, count)
self.removeFinishedTasks(threadedTasks, queuedTasks)
time.sleep(self.dialInterval)
def mustWalkBufferOK(self, expect):
self.cs.WalkBuffer()
mutations = self.cs.Mutations()
logger.debug(mutations)
logger.debug(expect)
self.assertEqual(len(mutations), len(expect))
for i in range(len(mutations)):
self.assertEqual(mutations[i], expect[i])
def mustScanOK(self, start, end, expect):
pairs = self.cs.Scan(start, end)
logger.debug(pairs)
logger.debug(expect)
self.assertEqual(len(pairs), len(expect))
for i in range(len(pairs)):
self.assertEqual(pairs[i][0], expect[i][0])
self.assertEqual(pairs[i][1], expect[i][1])
def isTaskTheaded(self, task, threadedTasks):
ret = False
for temp in threadedTasks:
logger.debug("threadedTask: %s, task: %s " %
(temp, task.get('task_id')))
if temp.get('task_id') == task.get('task_id'):
ret = True
return ret
def Delete(self, key, col):
'''Delete set value=None into local memory buffer
@param key: str
@param col: column name used to locate a kv-server
'''
self.dirty = True
self.us.Delete(key, col)
logger.debug('k=%s,col=%s', key, col)
def login(self):
'''
@rtype: int, ErrSessionMaxSize
'''
session_id = SessionCounter.Incr()
session = Session(session_id, self.store)
err = self.session_pool.Set(session)
logger.debug("sid=%d, err=%s", session_id, err)
return session_id, err
def ResolveLocks(self, locks, col):
'''
@param locks: list(kvrpcpb.LockInfo)
'''
if self.isAllExpired(locks):
logger.debug('all locks expired, cleanup start...')
return self.cleanUp(locks, col)
else:
return False
def test_GetRowID_profile():
import time
dbinfo = DBInfo()
for i in range(5):
st = time.time()
for _ in range(1000):
dbinfo.GetRowID(1)
logger.debug("use_time=%d", time.time() - st)
dbinfo.Close()
def isTimeSlotProhibit(self, timeGroup):
slots = timeGroup.split(';')
logger.debug("Configured prohibit time slots: %s", slots)
now = self.getCurrTime()
if self.isInSlots(slots, now):
return True
else:
return False
def Set(self, key, value, col):
'''Set key/value into local memory buffer
@param key: str
@param value: str
@param col: column name used to locate a kv-server
'''
self.dirty = True
self.us.Set(key, value, col)
logger.debug('k=%s,v=%s,col=%s', key, value, col)
def BatchGet(self, keys, col):
'''gets multi keys' value by keys
@param keys: list(str) , keys to get.
@param col: str, column name used to locate a mvccdb
@rtype: dict[str, str], ErrTxnTimeOut
@return: pairs, err.
pairs: is a map contains key/value pairs,
the map will not contain nonexistent keys.
err:
- None: if lock errs is expired and cleanup success.
- ErrTxnTimeOut: if cleanup Timeout
'''
# 1. find local store first
ret_dict = self.us.BatchGet(keys, col)
# logger.debug('keys=%s,col=%s,ret=%s', keys, col, ret_dict)
ks = list()
# for k, v in ret_dict.iteritems():
# if v is None:
# ks.append(k)
for k in keys:
if k not in ret_dict:
ks.append(k)
if len(ks) == 0:
logger.debug('keys=%s,col=%s,ret=%s', keys, col, ret_dict)
return ret_dict, None
# 2. if key not found in local store, then try to find it from remote mvcc store
db = self.store.GetMvccDB(col)
ks.sort()
pairs = db.BatchGet(ks, self.startTS, self.isoLevel)
locked = False
cleanup = False
# backoffAndMaybeCleanupLock
errs = list()
for p in pairs:
if p.Err:
errs.append(p.Err)
else:
ret_dict[p.Key] = p.Value
err = None
if len(errs):
locked = True
pairs, err = self.backoffAndMaybeCleanupLock(errs, col)
if not err:
cleanup = True
for k, v in pairs:
ret_dict[k] = v
else:
ret_dict = None
logger.debug('keys=%s,col=%s,ret=%s,err=%s,locked=%d,cleanup=%d', keys,
col, ret_dict, err, locked, cleanup)
return ret_dict, err
def logout():
# remove the session_id from the session if it's there
session_id = 0
if 'session_id' in session:
session_id = session.get('session_id')
session.pop('session_id')
server.logout(session_id)
ret = 'Logged out session_id=%d\n' % session_id
logger.debug(ret)
return ret
def Insert(self, key, value, col):
'''Insert key/value into local memory buffer
@param key: str
@param value: str
@param col: column name used to locate a kv-server
@rtype: ErrKeyExists
'''
self.dirty = True
err = self.us.Insert(key, value, col)
logger.debug('k=%s,v=%s,col=%s,err=%s', key, value, col, err)
return err
def selectGatewayConfig(self, taskid):
sql = "select DISTINCT task_gateway from tb_task WHERE task_id='%s'" % (
taskid)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch gateway data.")
logger.debug("selectGatewayConfig by taskid results: %s", records)
return records
def updateDialTime(self, rec):
state = rec.get('cr_status')
uuid = rec.get('cr_uuid')
sql = "UPDATE tb_callrecord SET cr_calltime=NOW(3), cr_status=%d WHERE cr_uuid=%d" % (
state, uuid)
logger.debug("updateDialTime sql: %s", sql)
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
except:
logger.error("Error: unable to update dial time.")
return
def LockKeys(self, keys, col): # error
'''lock kv-server's keys, locks will only exists on prewrite phase. and commit will cleanup
the locks.
if lock a same key again in one txn, it will success, but only the first is effective.
if lock a same key again in another txn, it will be a lock confict err from kv-server
and only the first lock is effective.
@param keys: list(str)
@param col: str, column name used to locate a mvccdb
'''
self.dirty = True
self.us.LockKeys(keys, col)
logger.debug('keys=%s,col=%s', keys, col)
def Scan(self, startKey, endKey, limit=None, col=None):
'''scan values by a key range [startKey, endKey)
@param startKey: str , start key of the range
@param endKey: str, end key of the range.
@attention: a range is Left open right closed interval, such as: [startKey, endKey)
@param limit: limit number of results
@param col: str, column name used to locate a mvccdb
@rtype: dict[str, str], ErrTxnTimeOut
@return: pairs, err.
pairs: is a map contains key/value pairs,
he map will not contain nonexistent keys.
err:
- None: if lock errs is expired and cleanup success.
- ErrTxnTimeOut: if cleanup Timeout
'''
if limit is None:
limit = 0xFFFFFFFFFFFFFFFF # MaxUint64
# 1. find local store first
ret_list = self.us.Scan(startKey, endKey, col)
ret_dict = dict(ret_list)
# 2. try to find it from remote mvcc store
db = self.store.GetMvccDB(col)
pairs = db.Scan(startKey, endKey, limit, self.startTS, self.isoLevel)
locked = False
cleanup = False
# backoffAndMaybeCleanupLock
errs = list()
for p in pairs:
# local value first, only update when key nonexistent.
if p.Key not in ret_dict:
if p.Err:
errs.append(p.Err)
else:
ret_dict[p.Key] = p.Value
err = None
if len(errs):
locked = True
pairs, err = self.backoffAndMaybeCleanupLock(errs, col)
if not err:
cleanup = True
for k, v in pairs:
ret_dict[k] = v
else:
ret_dict = None
logger.debug(
'start=%s,end=%s,col=%s,ret=%s,err=%s,locked=%d,cleanup=%d',
startKey, endKey, col, ret_dict, err, locked, cleanup)
return ret_dict, err
def selectGw(self, uuid):
sql = "select cr_uuid, cr_taskid, cr_mobile, cr_status, cr_calltime from \
tb_callrecord WHERE cr_uuid = %d" % (uuid)
record = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
record = cursor.fetchall()
logger.debug(record)
except:
logger.error("Error: unable to fetch gw record data.")
return record
def test_PrewriteRollback(self):
logger.debug('**********txn start***********')
col = 'student.name.data'
self.mustCommit({
col: {
'a': 'a0',
'b': 'b0',
},
}
)
logger.debug('**********txn1 start***********')
txn1 = self.store.Begin()
err = txn1.Set("a", "a1", col)
self.assertEqual(err, None)
err = txn1.Set("b", "b1", col)
self.assertEqual(err, None)
txn1.us.WalkBuffer()
err = txn1.prewrite()
self.assertEqual(err, None)
logger.debug('**********txn2 start***********')
txn2 = self.store.Begin()
txn2.isoLevel = kvrpcpb.RC
v, err = txn2.Get("a", col)
self.assertEqual(err, None)
self.assertEqual(v, 'a0')
logger.debug('**********txn1 retry***********')
err = txn1.prewrite()
if err != nil:
# Retry.
txn1 = self.store.Begin()
err = txn1.Set("a", "a1", col)
self.assertEqual(err, None)
err = txn1.Set("b", "b1", col)
self.assertEqual(err, None)
txn1.us.WalkBuffer()
err = txn1.prewrite()
self.assertEqual(err, None)
commitTS = txn1.store.GetTimestamp()
txn1.commitTS = commitTS
txn1.commit()
self.assertEqual(err, None)
logger.debug('**********txn3 start***********')
txn3 = self.store.Begin()
v, err = txn3.Get("b", col)
self.assertEqual(err, None)
self.assertEqual(v, 'b1')
