def _unlock_many():
tickets = []
with self.db_engine.begin() as conn:
cur = conn.execute(_sql('select * from slc_rad_online where nas_addr = :nas_addr'),nas_addr=nas_addr)
for online in cur:
conn.execute(bsql,**_ticket(online))
conn.execute(_sql('delete from slc_rad_online where nas_addr = :nas_addr'),nas_addr=nas_addr)
def _unlock_many():
tickets = []
with self.db_engine.begin() as conn:
cur = conn.execute(_sql('select * from slc_rad_online where nas_addr = :nas_addr'),nas_addr=nas_addr)
for online in cur:
conn.execute(bsql,**_ticket(online))
conn.execute(_sql('delete from slc_rad_online where nas_addr = :nas_addr'),nas_addr=nas_addr)
def _raw_set(self, key, value, timeout, **kwargs):
with self.dbengine.begin() as conn:
_time = int(time.time()) + int(timeout)
try:
conn.execute(_sql('insert into system_session values (:key, :value, :time) '), key=key, value=value, time=_time)
except:
conn.execute(_sql('update system_session \n set _value=:value, _time=:time\n where _key=:key'), key=key, value=value, time=_time)
def set(self, key, value, expire = 0):
self.set_total += 1
raw_data = self.encode_data(value)
with self.dbengine.begin() as conn:
_time = expire > 0 and int(time.time()) + int(expire) or 0
try:
conn.execute(_sql('insert into %s values (:key, :value, :time) ' % self.cache_table), key=key, value=raw_data, time=_time)
except:
conn.execute(_sql('delete from %s where _key = :key ' % self.cache_table), key=key)
conn.execute(_sql('insert into %s values (:key, :value, :time) ' % self.cache_table), key=key, value=raw_data, time=_time)
def _raw_set(self, key, value, timeout,**kwargs):
with self.dbengine.begin() as conn:
_time = int(time.time()) + int(timeout)
try:
conn.execute(_sql("insert into system_session values (:key, :value, :time) "),
key=key,value=value,time=_time)
except:
conn.execute(_sql("""update system_session
set _value=:value, _time=:time
where _key=:key"""),
key=key,value=value,time=_time)
def set(self, key, value, expire=0):
raw_data = self.encode_data(value)
with self.dbengine.begin() as conn:
_time = expire>0 and (int(time.time()) + int(expire)) or 0
try:
conn.execute(_sql("insert into %s values (:key, :value, :time) " % self.cache_table),
key=key,value=raw_data,time=_time)
except:
conn.execute(_sql("delete from %s where _key = :key " % self.cache_table),key=key)
conn.execute(_sql("insert into %s values (:key, :value, :time) " % self.cache_table),
key=key,value=raw_data,time=_time)
def _unlock_one():
ticket = None
with self.db_engine.begin() as conn:
sql = _sql('select * from slc_rad_online where nas_addr = :nas_addr and acct_session_id = :session_id')
cur = conn.execute(sql,nas_addr=nas_addr,session_id=acct_session_id)
online = cur.fetchone()
if online:
ticket = _ticket(online)
dsql = _sql('delete from slc_rad_online where nas_addr = :nas_addr and acct_session_id = :session_id')
conn.execute(dsql,nas_addr=nas_addr,session_id=acct_session_id)
conn.execute(bsql,**ticket)
def _unlock_one():
ticket = None
with self.db_engine.begin() as conn:
sql = _sql('select * from slc_rad_online where nas_addr = :nas_addr and acct_session_id = :session_id')
cur = conn.execute(sql,nas_addr=nas_addr,session_id=acct_session_id)
online = cur.fetchone()
if online:
ticket = _ticket(online)
dsql = _sql('delete from slc_rad_online where nas_addr = :nas_addr and acct_session_id = :session_id')
conn.execute(dsql,nas_addr=nas_addr,session_id=acct_session_id)
conn.execute(bsql,**ticket)
def get(self, key):
self.get_total += 1
raw_data = None
_del_func = self.delete
with self.dbengine.begin() as conn:
try:
cur = conn.execute(_sql("select _value, _time from %s where _key = :key " % self.cache_table), key=key)
_cache = cur.fetchone()
if _cache:
self.hit_total += 1
_time = int(_cache["_time"])
if _time > 0 and time.time() > _time:
reactor.callLater(0.01, _del_func, key)
else:
raw_data = _cache["_value"]
except:
import traceback
traceback.print_exc()
try:
if raw_data:
return self.decode_data(raw_data)
except:
self.delete(key)
return None
def _delete(self, key):
with self.dbengine.begin() as conn:
try:
conn.execute(_sql("delete from system_session where _key = :key "),key=key)
except:
import traceback
traceback.print_exc()
def create_logtable(db_engine):
create_sql_tpl = """
CREATE TABLE {0} (
id INTEGER NOT NULL PRIMARY KEY autoincrement,
host VARCHAR(32) NOT NULL,
time VARCHAR(19) NOT NULL,
facility VARCHAR(16) NOT NULL,
priority VARCHAR(16) NOT NULL,
username VARCHAR(16) NULL,
message VARCHAR(1024) NOT NULL
);
"""
mysql_create_sql_tpl = """
CREATE TABLE {0} (
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
host VARCHAR(32) NOT NULL,
time VARCHAR(19) NOT NULL,
facility VARCHAR(16) NOT NULL,
priority VARCHAR(16) NOT NULL,
username VARCHAR(16) NULL,
message VARCHAR(1024) NOT NULL
)
COMMENT='syslog table'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
"""
sql_tpl = 'mysql' in db_engine.driver and mysql_create_sql_tpl or create_sql_tpl
table_name = "log_{0}".format(datetime.datetime.now().strftime("%Y%m%d%H"))
sqlstr = sql_tpl.format(table_name)
with db_engine.begin() as conn:
try:
conn.execute(_sql(sqlstr))
except:
logging.exception("create logtable error")
def delete(self,key):
with self.dbengine.begin() as conn:
try:
conn.execute(_sql("delete from %s where _key = :key " % self.cache_table),key=key)
except:
import traceback
traceback.print_exc()
def update_user_flow_length(self, username, flow_length):
with self.db_engine.begin() as conn:
sql = _sql(
"update slc_rad_account set flow_length = flow_length where account_number = :account"
)
conn.execute(sql, flow_length=flow_length, account=username)
self.update_user_cache(username)
def update_user_vlan_id2(self, username, vlan_id2):
with self.db_engine.begin() as conn:
sql = _sql(
"update slc_rad_account set vlan_id2 = :vlan_id2 where account_number = :account"
)
conn.execute(sql, vlan_id2=vlan_id2, account=username)
self.update_user_cache(username)
def get_user(self, username):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql(
"select a.*,p.product_policy from slc_rad_account a,slc_rad_product p \
where a.product_id = p.id and a.account_number = :account "),
account=username)
return cur.fetchone()
def add_online(self, online):
with self.db_engine.begin() as conn:
keys = ','.join(online.keys())
vals = ",".join(["'%s'" % c for c in online.values()])
sql = _sql('insert into slc_rad_online (%s) values(%s)' %
(keys, vals))
conn.execute(sql)
def count_online(self, account_number):
with self.db_engine.begin() as conn:
sql = _sql(
'select count(id) as online from slc_rad_online where account_number = :account'
)
cur = conn.execute(sql, account=account_number)
return cur.fetchone()['online']
def get_bas(self, ipaddr):
with self.db_engine.begin() as conn:
cur = conn.execute(
_sql("select * from slc_rad_bas where ip_addr = :ip_addr"),
ip_addr=ipaddr)
bas = cur.fetchone()
return bas
def get_product_attrs(self, product_id):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql(
"select * from slc_rad_product_attr where product_id = :product_id "
),
product_id=product_id)
return cur.fetchall()
def update_user_mac(self, username, mac_addr):
with self.db_engine.begin() as conn:
sql = _sql(
"update slc_rad_account set mac_addr = :mac_addr where account_number = :account"
)
conn.execute(sql, mac_addr=mac_addr, account=username)
self.update_user_cache(username)
def key_where(self, objdata):
""" 组装主键查询条件，只有同步的版本大于数据库的版本时才会更新
"""
key_where = ' and '.join(
("%s='%s'" % (k, v) for k, v in objdata.pkeys.items()))
key_where = _sql(key_where)
return key_where
def update_user_balance(self, username, balance):
with self.db_engine.begin() as conn:
sql = _sql(
"update slc_rad_account set balance = :balance where account_number = :account"
)
conn.execute(sql, balance=balance, account=username)
self.update_user_cache(username)
def get_user_attrs(self, username):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql(
"select * from slc_rad_account_attr where account_number = :account "
),
account=username)
return cur.fetchall()
def _raw_get(self, key, **kwargs):
raw_data = None
with self.dbengine.begin() as conn:
try:
cur = conn.execute(_sql("select _value, _time from system_session where _key = :key "),key=key)
session = cur.fetchone()
if session:
_time = int(session['_time'])
if (time.time() - _time) > self.session_timeout:
conn.execute(_sql("delete from system_session where _key = :key "),key=key)
else:
raw_data = session['_value']
except:
import traceback
traceback.print_exc()
return raw_data
def get(self, key):
raw_data = None
_del_func = self.delete
with self.dbengine.begin() as conn:
try:
cur = conn.execute(
_sql("select _value, _time from %s where _key = :key " %
self.cache_table),
key=key)
_cache = cur.fetchone()
if _cache:
_time = int(_cache['_time'])
if _time > 0 and time.time() > _time:
reactor.callLater(
0.01,
_del_func,
key,
)
else:
raw_data = _cache['_value']
except:
import traceback
traceback.print_exc()
try:
if raw_data:
return self.decode_data(raw_data)
except:
self.delete(key)
return None
def get_nas_onlines_byuser(self, account_number):
with self.db_engine.begin() as conn:
sql = _sql(
'select * from slc_rad_online where account_number = :account'
)
cur = conn.execute(sql, account=account_number)
return cur.fetchall()
def update_param_cache(self):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql("select param_name from slc_param "))
for param in cur:
cache.invalidate(self.get_param, 'get_param',
str(param['param_name']))
cache.invalidate(self.get_param, 'get_param',
unicode(param['param_name']))
def get_param(self, param_name):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql(
"select param_value from slc_param where param_name = :param_name"
),
param_name=param_name)
param = cur.fetchone()
return param and param['param_value'] or None
def count(self):
with self.dbengine.begin() as conn:
try:
cur = conn.execute(_sql('select count(_key) as count from %s ' % self.cache_table))
return int(cur.fetchone()['count'])
except:
self.log.error('cache count error')
return 0
def delete(self, key):
self.delete_total += 1
with self.dbengine.begin() as conn:
try:
conn.execute(_sql('delete from %s where _key = :key ' % self.cache_table), key=key)
except:
import traceback
traceback.print_exc()
def get_user_attr(self, username,attr_name):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql("""select attr_value from slc_rad_account_attr
where account_number = :account
and attr_name = :attr_name"""),
account=username,attr_name=attr_name)
b = cur.fetchone()
return b and b['attr_value'] or None
def get_user_flow_length(self, username):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql(
"select flow_length from slc_rad_account where account_number = :account "
),
account=username)
b = cur.fetchone()
return b and b['flow_length'] or 0
def count(self):
with self.dbengine.begin() as conn:
try:
cur = conn.execute(_sql("select count(_key) as count from %s " % self.cache_table))
return int(cur.fetchone()["count"])
except:
self.log.error("cache count error")
return 0
def _raw_replace(self, key, value, timeout, **kwargs):
with self.dbengine.begin() as conn:
_time = int(time.time()) + int(timeout)
try:
conn.execute(_sql('update system_session \n set _value=:value, _time=:time\n where _key=:key'), key=key, value=value, time=_time)
except:
import traceback
traceback.print_exc()
def get_online(self, nas_addr, acct_session_id):
with self.db_engine.begin() as conn:
sql = _sql(
'select * from slc_rad_online where nas_addr = :nas_addr and acct_session_id = :session_id'
)
cur = conn.execute(sql,
nas_addr=nas_addr,
session_id=acct_session_id)
return cur.fetchone()
def check_expire(self, first_delay=0):
if first_delay > 0:
reactor.callLater(first_delay, self.check_expire)
with self.dbengine.begin() as conn:
try:
conn.execute(_sql("delete from %s where _time > 0 and _time < :time" % self.cache_table),time=int(time.time()))
except:
pass
reactor.callLater(120.0, self.check_expire)
def getRadius(self, host):
cache_key = 'RadiusLoader.radius.%s' % (host)
cache = self.cache.get(cache_key)
if cache:
return cache
with self.dbengine.begin() as conn:
cur = conn.execute(_sql("""select * from tt_radius where ip_addr = :ip_addr """),ip_addr=host)
radius = self.warp(cur.fetchone())
self.cache.set(cache_key,radius, expire=60)
return radius
def add_ticket(self,ticket):
_ticket = ticket.copy()
for _key in _ticket:
if _key not in ticket_fds:
del ticket[_key]
with self.db_engine.begin() as conn:
keys = ','.join(ticket.keys())
vals = ",".join(["'%s'"% c for c in ticket.values()])
sql = _sql('insert into slc_rad_ticket (%s) values(%s)'%(keys,vals))
conn.execute(sql)
def execute_sqls(config, sqlstr):
sh.info("exec sql >> %s" % sqlstr)
results = []
with get_engine(config).begin() as conn:
try:
results = conn.execute(_sql(sqlstr))
except Exception as err:
return sh.err("exec sql error: %s" % str(err))
sh.info("exec sql done")
print_result(results)
def add_ticket(self,ticket):
_ticket = ticket.copy()
for _key in _ticket:
if _key not in ticket_fds:
del ticket[_key]
with self.db_engine.begin() as conn:
keys = ','.join(ticket.keys())
vals = ",".join(["'%s'"% c for c in ticket.values()])
sql = _sql('insert into slc_rad_ticket (%s) values(%s)'%(keys,vals))
conn.execute(sql)
def execute_sqls(config,sqlstr):
sh.info('exec sql >> %s'%sqlstr)
results = []
with get_engine(config).begin() as conn:
try:
results = conn.execute(_sql(sqlstr))
except Exception as err:
return sh.err('exec sql error: %s'%str(err))
sh.info('exec sql done')
print_result(results)
def get_user_attr(self, username, attr_name):
with self.db_engine.begin() as conn:
cur = conn.execute(
_sql("""select attr_value from slc_rad_account_attr
where account_number = :account
and attr_name = :attr_name"""),
account=username,
attr_name=attr_name)
b = cur.fetchone()
return b and b['attr_value'] or None
def getMasterRadius(self):
cache_key = 'RadiusLoader.master_radius'
cache = self.cache.get(cache_key)
if cache:
return cache
with self.dbengine.begin() as conn:
cur = conn.execute(_sql("select * from trw_radius where serv_type = 1"))
radius = self.warp(cur.fetchone())
self.cache.set(cache_key,radius, expire=600)
return radius
def add_column(config, tablename, column, ctype = 'VARCHAR', defval = ''):
try:
db_engine = get_engine(config)
sqlstr = u"ALTER TABLE {0} ADD COLUMN {1} {2} DEFAULT '{3}';"
sqlstr = sqlstr.format(tablename, column, ctype, defval)
with db_engine.begin() as conn:
conn.execute(_sql(sqlstr))
except Exception as e:
import traceback
traceback.print_exc()
def _raw_replace(self, key, value, timeout,**kwargs):
with self.dbengine.begin() as conn:
_time = int(time.time()) + int(timeout)
try:
conn.execute(_sql("""update system_session
set _value=:value, _time=:time
where _key=:key"""),
key=key,value=value,time=_time)
except:
import traceback
traceback.print_exc()
def process(self):
table_name = "log_{0}".format((datetime.datetime.now() + datetime.timedelta(hours=1)).strftime("%Y%m%d%H"))
sqlstr = self.sql_tpl.format(table_name)
with self.dbengine.begin() as conn:
try:
conn.execute(_sql(sqlstr))
log.msg("create table {0} success;".format(table_name))
except Exception as err:
log.msg('create table error {0}'.format(err.message))
reactor.callLater(60 * 20, self.process, )
def update(self, key, value, expire=0):
raw_data = self.encode_data(value)
with self.dbengine.begin() as conn:
_time = expire>0 and (int(time.time()) + int(expire)) or 0
try:
conn.execute(_sql("""update %s
set _value=:value, _time=:time
where _key=:key""" % self.cache_table),
key=key,value=raw_data,time=_time)
except:
import traceback
traceback.print_exc()
def update_billing(self,billing,time_length=0,flow_length=0):
with self.db_engine.begin() as conn:
# update account
balan_sql = _sql("""update slc_rad_account set
balance = :balance,
time_length=:time_length,
flow_length=:flow_length
where account_number = :account
""")
conn.execute(balan_sql,
balance=billing.balance,
time_length=time_length,
flow_length=flow_length,
account=billing.account_number
)
# update online
online_sql = _sql("""update slc_rad_online set
billing_times = :billing_times,
input_total = :input_total,
output_total = :output_total
where nas_addr = :nas_addr and acct_session_id = :session_id
""")
conn.execute(online_sql,
billing_times=billing.acct_session_time,
input_total=billing.input_total,
output_total=billing.output_total,
nas_addr=billing.nas_addr,
session_id=billing.acct_session_id
)
# update billing
keys = ','.join(billing.keys())
vals = ",".join(["'%s'"% c for c in billing.values()])
billing_sql = _sql('insert into slc_rad_billing (%s) values(%s)'%(keys,vals))
conn.execute(billing_sql)
self.update_user_cache(billing.account_number)
def get_roster(self,mac_addr):
if mac_addr:
mac_addr = mac_addr.upper()
roster = None
with self.db_engine.begin() as conn:
cur = conn.execute(_sql("select * from slc_rad_roster where mac_addr = :mac_addr "),mac_addr=mac_addr)
roster = cur.fetchone()
print roster
if roster:
now = create_time = datetime.datetime.now()
roster_start = datetime.datetime.strptime(roster['begin_time'],"%Y-%m-%d")
roster_end = datetime.datetime.strptime(roster['end_time'],"%Y-%m-%d")
if now < roster_start or now > roster_end:
return None
return roster
def check_online_over(self):
onlines = []
with self.db_engine.begin() as conn:
sql = _sql('select acct_start_time,nas_addr,acct_session_id from slc_rad_online')
onlines = conn.execute(sql)
for online in onlines:
start_time = datetime.datetime.strptime(online['acct_start_time'],"%Y-%m-%d %H:%M:%S")
_datetime = datetime.datetime.now()
if (_datetime - start_time).seconds > 3600 * 3:
self.unlock_online(
online['nas_addr'],
online['acct_session_id'],
settings.STATUS_TYPE_CHECK_ONLINE
)
def update_online(self,online):
with self.db_engine.begin() as conn:
online_sql = _sql("""update slc_rad_online set
billing_times = :billing_times,
input_total = :input_total,
output_total = :output_total
where nas_addr = :nas_addr and acct_session_id = :session_id
""")
conn.execute(online_sql,
billing_times = online['billing_times'],
input_total = online['input_total'],
output_total = online['output_total'],
nas_addr = online['nas_addr'],
session_id = online['acct_session_id']
)
def get(self, key):
raw_data = None
_del_func = self.delete
with self.dbengine.begin() as conn:
try:
cur = conn.execute(_sql("select _value, _time from %s where _key = :key " % self.cache_table),key=key)
_cache = cur.fetchone()
if _cache:
_time = int(_cache['_time'])
if _time > 0 and time.time() > _time:
reactor.callLater(0.01, _del_func, key,)
else:
raw_data = _cache['_value']
except:
import traceback
traceback.print_exc()
return raw_data and self.decode_data(raw_data) or None
def process(self):
# log.msg("fetch queue message")
job = self.beanstalk.reserve()
msg_dict = json.loads(job.body)
_date = datetime.datetime.strptime(msg_dict['time'], self.date_format)
table_name = "log_{0}".format(_date.strftime("%Y%m%d%H"))
insert_sql = _sql("""insert into {0} (host,time,facility,priority,username,message)
values (:host,:time,:facility,:priority,:username,:message)""".format(table_name))
with self.dbengine.begin() as conn:
# if self.config.defaults.debug:
# log.msg("start write message to db")
try:
conn.execute(insert_sql,
host=msg_dict.get("host"),
time=msg_dict.get("time"),
facility=msg_dict.get("facility"),
priority=msg_dict.get("priority"),
username=msg_dict.get("username"),
message=msg_dict.get("message"))
job.delete()
self.msg_num += 1
# if self.config.defaults.debug:
# log.msg("write syslog success")
except Exception as err:
log.err(err, 'write syslog error')
job.release()
reactor.callLater(0.001, self.process, )
ctime = time.time()
total_time = (ctime - self.sum_time)
if total_time >= 5:
per_num = self.msg_num / total_time
log.msg("[pid:%s] Total msg: %s; Time total: %s sec; Msg per second: %s;" % (os.getpid(), self.msg_num, total_time, per_num))
self.msg_num = 0
self.sum_time = time.time()
def get_user_attrs(self,username):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql("select * from slc_rad_account_attr where account_number = :account "),account=username)
return cur.fetchall()
def get_user(self,username):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql("select a.*,p.product_policy from slc_rad_account a,slc_rad_product p \
where a.product_id = p.id and a.account_number = :account "),account=username)
return cur.fetchone()
def get_bas(self,ipaddr):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql("select * from slc_rad_bas where ip_addr = :ip_addr"),ip_addr=ipaddr)
bas = cur.fetchone()
return bas
def list_bas(self):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql("select * from slc_rad_bas"))
return [bas for bas in cur]
def update_param_cache(self):
with self.db_engine.begin() as conn:
cur = conn.execute(_sql("select param_name from slc_param "))
for param in cur:
cache.invalidate(self.get_param,'get_param', str(param['param_name']))
cache.invalidate(self.get_param,'get_param', unicode(param['param_name']))
