def write_users(self, user, password, salt, changed=None):
"""Insert or replace a users entry
Attributes: user ... text-the primary key - unique
password ... text-password
salt ... text-salt
changed ... datetime.utcnow-when changed
"""
query = ("INSERT OR REPLACE INTO users " +
"(user, password, salt, changed) " + "VALUES (?, ?, ?, ?) ")
# set changed timestamp to utcnow if not set
if changed is None:
changed = datetime.utcnow()
try:
smsdblock.acquire()
smsgwglobals.dblogger.debug("SQLite: Write into users" +
" :user: "******" :password-len: " +
str(len(password)) + " :salt-len: " +
str(len(salt)) + " :changed: " +
str(changed))
self.__cur.execute(query, (user, password, salt, changed))
self.__con.commit()
smsgwglobals.dblogger.debug("SQLite: Insert done!")
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to INSERT user! ", e)
finally:
smsdblock.release()
def update_sms(self, smslist=[]):
"""Updates Sms entries out of a list to reflect the new values
all columns of sms have to be set!
Attributes: smslsit ... list of sms in dictionary structure
(see read_sms)
"""
smsgwglobals.dblogger.debug("SQLite: Will update " +
str(len(smslist)) + "sms.")
# for each sms in the list
for sms in smslist:
smsgwglobals.dblogger.debug("SQLite: Update SMS: " + str(sms))
query = ("UPDATE sms SET " + "modemid = ?, " + "imsi = ?, " +
"targetnr = ?, " + "content = ?, " + "priority = ?, " +
"appid = ?, " + "sourceip = ?, " + "xforwardedfor = ?, " +
"smsintime = ?, " + "status = ?, " + "statustime = ? " +
"WHERE smsid = ?")
try:
smsdblock.acquire()
self.__con.execute(
query,
(sms['modemid'], sms['imsi'], sms['targetnr'],
sms['content'], sms['priority'], sms['appid'],
sms['sourceip'], sms['xforwardedfor'], sms['smsintime'],
sms['status'], sms['statustime'], sms['smsid']))
self.__con.commit()
smsgwglobals.dblogger.debug("SQLite: Update for smsid: " +
str(sms['smsid']) + " done!")
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to UPDATE sms! ", e)
finally:
smsdblock.release()
def raise_obsolete(self):
smsgwglobals.wislogger.debug("ROUTERDB: Raising Obsolete" +
" routing entries...")
try:
now = datetime.utcnow()
older = now - timedelta(0, 30)
smsgwglobals.wislogger.debug("ROUTER " + str(now))
smsgwglobals.wislogger.debug("ROUTER " + str(older))
query = ("UPDATE routing SET " + "obsolete = obsolete + 2 " +
"WHERE changed < ?")
rdblock.acquire()
result = self.cur.execute(query, [older])
counta = result.rowcount
query = ("UPDATE routing SET " + "obsolete = 14 " +
"WHERE obsolete = 1")
result = self.cur.execute(query)
countb = result.rowcount
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: " + str(counta) +
str(countb) +
" routing OBSOLETE RAISED!")
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to change obsolete! ", e)
finally:
rdblock.release()
def read_users(self, user=None):
smsgwglobals.dblogger.debug("SQLite: Read users" + " :user: "******"SELECT " + "user, " + "password, " + "salt, " + "changed " +
"FROM users")
try:
if user is None:
smsdblock.acquire()
result = self.__cur.execute(query)
else:
# user is set
smsdblock.acquire()
query = query + " WHERE user = ?"
result = self.__cur.execute(query, [user])
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT FROM users! ", e)
else:
# convert rows to dict
user = [dict(row) for row in result]
smsgwglobals.dblogger.debug("SQLite: " + str(len(user)) +
" user selected.")
return user
finally:
smsdblock.release()
def delete_old_sms(self, secs=34560000):
if secs is None:
secs = 34560000
days = int(secs) / 60 / 60 / 24
smsgwglobals.dblogger.debug("SQLite: Deleting sms older than " +
str(secs) + " sec. (" + str(days) +
" days).")
now = datetime.utcnow()
ts = now - timedelta(seconds=int(secs))
smsgwglobals.dblogger.info("SQLite: Deleting sms created before " +
str(ts) + "...")
query = ("DELETE FROM sms " + "WHERE smsintime < ?")
try:
smsdblock.acquire()
result = self.__con.execute(query, [ts])
count = result.rowcount
self.__con.commit()
smsgwglobals.dblogger.info("SQLite: " + str(count) +
" sms before: " + str(ts) + " deleted!")
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to DELETE sms! ", e)
finally:
smsdblock.release()
def read_sms_date(self, date=None):
if date is None:
date = datetime.utcnow().date().strftime("%Y-%m-%d") + "%"
smsgwglobals.dblogger.debug("SQLite: Read SMS" + " :date: " +
str(date))
query = ("SELECT " + "smsid, " + "modemid, " + "imsi, " +
"targetnr, " + "content, " + "priority, " + "appid, " +
"sourceip, " + "xforwardedfor, " + "smsintime, " +
"status, " + "statustime " + "FROM sms ")
try:
smsdblock.acquire()
query = query + "WHERE smsintime LIKE ?"
result = self.__cur.execute(query, [date])
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT FROM sms! ", e)
finally:
smsdblock.release()
sms = [dict(row) for row in result]
smsgwglobals.dblogger.debug("SQLite: " + str(len(sms)) +
" SMS selected.")
return sms
def read_sucsmsstats(self, timestamp=None):
smsgwglobals.dblogger.debug("SQLite: Read SMS stats" +
" with :timestamp: gt " + str(timestamp))
query = ("SELECT " + "smsid, " + "modemid, " + "imsi, " +
"targetnr, " + "content, " + "priority, " + "appid, " +
"sourceip, " + "xforwardedfor, " + "smsintime, " +
"status, " + "statustime " + "FROM sms " +
"WHERE (status = 4 OR status = 5)")
# status 4 or 5 -> successfully send sms
orderby = " ORDER BY statustime ASC;"
try:
smsdblock.acquire()
if timestamp is None:
query = query + orderby
result = self.__cur.execute(query)
else:
# greater than timestamp
query = query + "AND statustime > ?" + orderby
result = self.__cur.execute(query, [timestamp])
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT stats FROM sms! ", e)
else:
sms = [dict(row) for row in result]
smsgwglobals.dblogger.debug("SQLite: " + str(len(sms)) +
" SMS for stats selected.")
return sms
finally:
smsdblock.release()
def raise_heartbeat(self, routingid):
smsgwglobals.wislogger.debug("ROUTERDB: Raising Heartbeat" +
" routing entries...")
try:
now = datetime.utcnow()
smsgwglobals.wislogger.debug("ROUTERDB: NEW HEARTBEAT" + str(now))
query = ("UPDATE routing SET " + "changed = ? ," +
"obsolete = 0 " + "WHERE routingid = ? " +
"AND obsolete < 14")
rdblock.acquire()
result = self.cur.execute(query, [now, routingid])
count = result.rowcount
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: " + str(count) +
" routing HEARTBEAT updated!")
return count
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to change obsolete! ", e)
finally:
rdblock.release()
def read_routing(self, modemid=None, web=False):
smsgwglobals.wislogger.debug("ROUTERDB: Read routing entries")
if web:
query = ("SELECT " + "modemid, " + "sms_count, " + "sms_limit, " +
"account_balance, " + "imsi, " + "imei, " + "carrier, " +
"modemname, " + "sim_blocked " + "FROM routing")
else:
query = ("SELECT " + "wisid, " + "modemid, " + "regex, " +
"sms_count, " + "sms_limit, " + "account_balance, " +
"imsi, " + "imei, " + "carrier, " + "lbfactor, " +
"wisurl, " + "pisurl, " + "modemname, " +
"sim_blocked, " + "routingid, " + "obsolete, " +
"changed " + "FROM routing")
try:
if modemid is None:
rdblock.acquire()
result = self.cur.execute(query)
else:
query = query + " WHERE modemid = ?"
rdblock.acquire()
result = self.cur.execute(query, [modemid])
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT FROM routing! ", e)
else:
# convert rows to dict
routes = [dict(row) for row in result]
smsgwglobals.wislogger.debug("ROUTERDB: " + str(len(routes)) +
" routing entries selected.")
return routes
finally:
rdblock.release()
def db_connect(self, dbname="db.sqlite"):
try:
self.__con = sqlite3.connect(dbname, check_same_thread=False)
# change row-factory to get
self.__con.row_factory = sqlite3.Row
self.__cur = self.__con.cursor()
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: Unable to connect! " + "[EXCEPTION]:%s", e)
raise error.DatabaseError('Connection problem!', e)
def write_routing(self, route, changed=None):
"""Insert or replace a routing entry
Attributes: wisid ... text-1st of primary key
modemid ... text-serving modem number-2nd of primary key
regex ... text-regex to match numbers for modem
lbcount ... int-number of sms delivered
lbfactor ... int-factor if different contingets
wisurl ... text-url of wis
obsolete ... route got flag for deletion
modemname ... text-longtext of modem
changed ... datetime.utcnow-when changed
"""
query = ("INSERT OR REPLACE INTO routing " +
"(wisid, modemid, regex, lbcount, lbfactor, wisurl, " +
"pisurl, obsolete, modemname, routingid, changed) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ")
# read lbcount if exist
lbcount = 0
r = self.read_lbcount(route["routingid"])
if r is None or len(r) == 0:
self.reset_lbcount()
else:
lbcount = r[0]["lbcount"]
if changed is None:
changed = datetime.utcnow()
try:
smsgwglobals.wislogger.debug(
"ROUTERDB: Write into routing" + " :wisid: " + route["wisid"] +
" :modemid: " + route["modemid"] + " :regex: " +
route["regex"] + " :lbcount: " + str(lbcount) +
" :lbfactor: " + str(route["lbfactor"]) + " :wisurl: " +
route["wisurl"] + " :pisurl: " + route["pisurl"] +
" :obsolete: " + str(route["obsolete"]) + " :modemname: " +
route["modemname"] + " :routingid: " + route["routingid"] +
" :changed: " + str(changed))
rdblock.acquire()
self.cur.execute(query,
(route["wisid"], route["modemid"], route["regex"],
lbcount, route["lbfactor"], route["wisurl"],
route["pisurl"], route["obsolete"],
route["modemname"], route["routingid"], changed))
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: INSERT!")
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to INSERT routing entry! ", e)
finally:
rdblock.release()
def read_sms_count_by_imsi(self,
imsi=None,
real_sent=False,
all_imsi=False):
utc_timezone = pytz.timezone("UTC")
ua_timezone = pytz.timezone("Europe/Kiev")
today = datetime.utcnow().astimezone(ua_timezone).date()
start = datetime(today.year,
today.month,
today.day,
tzinfo=ua_timezone).astimezone(utc_timezone)
end = start + timedelta(1)
smsgwglobals.dblogger.debug("SQLite: Read SMS stats" +
" with :imsi: " + str(imsi) +
" for last 24 hours")
if all_imsi:
query = ("SELECT imsi, count(*) as sms_count " + "FROM sms " +
"WHERE statustime BETWEEN ? AND ?")
# Return already sent sms, not scheduled
if real_sent:
query = query + " AND status = 1"
query = query + " GROUP BY imsi"
else:
query = ("SELECT count(*) " + "FROM sms " + "WHERE imsi = ? " +
"AND statustime BETWEEN ? AND ?")
# Return already sent sms, not scheduled
if real_sent:
query = query + " AND status = 1"
try:
smsdblock.acquire()
if all_imsi:
result = self.__cur.execute(query, [start, end])
else:
result = self.__cur.execute(query, [imsi, start, end])
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT sms_count FROM sms! ",
e)
else:
if all_imsi:
sms_count = [dict(row) for row in result]
else:
sms_count = result.fetchone()[0]
smsgwglobals.dblogger.debug("SQLite: Sent " + str(sms_count) +
" SMS for IMSI " + str(imsi) + ".")
return sms_count
finally:
smsdblock.release()
def delete_one_user(self, user):
smsgwglobals.dblogger.debug("SQlite: Deleting user entry for user: "******"DELETE FROM users " + "WHERE user = ?")
try:
self.__con.execute(query, [user])
self.__con.commit()
smsgwglobals.dblogger.debug("SQLite: User: "******" deleted!")
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to DELETE from users! ", e)
def read_sms(self, status=None, modemid=None, smsid=None):
smsgwglobals.dblogger.debug("SQLite: Read SMS" + " :modemid: " +
str(modemid) + " :status: " + str(status) +
" :smsid: " + str(smsid))
query = ("SELECT " + "smsid, " + "modemid, " + "imsi, " +
"targetnr, " + "content, " + "priority, " + "appid, " +
"sourceip, " + "xforwardedfor, " + "smsintime, " +
"status, " + "statustime " + "FROM sms ")
orderby = " ORDER BY priority DESC, smsintime ASC;"
try:
if smsid is not None:
smsdblock.acquire()
query = query + "WHERE smsid = ?" + orderby
result = self.__cur.execute(query, [smsid])
elif modemid is None:
if status is None:
smsdblock.acquire()
query = query + orderby
result = self.__cur.execute(query)
else:
# status only
smsdblock.acquire()
query = query + "WHERE status = ?" + orderby
result = self.__cur.execute(query, [status])
else:
if status is None:
# modemid only
smsdblock.acquire()
query = query + "WHERE modemid = ?" + orderby
result = self.__cur.execute(query, [modemid])
else:
# status and modemid
smsdblock.acquire()
query = query + "WHERE status = ? AND modemid = ?" + orderby
result = self.__cur.execute(query, (status, modemid))
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT FROM sms! ", e)
else:
sms = [dict(row) for row in result]
smsgwglobals.dblogger.debug("SQLite: " + str(len(sms)) +
" SMS selected.")
return sms
finally:
smsdblock.release()
def delete_unittest_sms(self, modemid):
smsgwglobals.dblogger.debug("SQLite: Deleting" +
" unittest sms for :modemid: " + modemid)
query = ("DELETE FROM sms " + "WHERE modemid = ?")
try:
result = self.__con.execute(query, [modemid])
count = result.rowcount
self.__con.commit()
smsgwglobals.dblogger.debug("SQLite: " + str(count) +
" sms for modemid: " + modemid +
" deleted!")
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to DELETE sms! ", e)
def read_statstimestamp(self, intype='SUC_SMS_STATS'):
smsgwglobals.dblogger.debug("SQLite: Read stats " + " :type: " +
str(intype))
query = ("SELECT " + "type, " + "lasttimestamp " + "FROM stats " +
"WHERE type = ?")
try:
result = self.__cur.execute(query, [intype])
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT FROM stats! ", e)
else:
# convert rows to dict
stats = [dict(row) for row in result]
smsgwglobals.dblogger.debug("SQLite: " + str(len(stats)) +
" user selected.")
return stats
def reset_lbcount(self):
smsgwglobals.wislogger.debug("ROUTERDB: Reset lbcount")
try:
query = ("UPDATE routing SET " + "lbcount = ? ")
rdblock.acquire()
result = self.cur.execute(query, [0])
count = result.rowcount
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: " + str(count) +
" lbcount CHANGED!")
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to reset lbcount! ", e)
finally:
rdblock.release()
def read_wisurls_union(self):
smsgwglobals.wislogger.debug("ROUTERDB: Read wisurls union")
query = ("SELECT DISTINCT " + "wisurl " + "FROM routing")
try:
rdblock.acquire()
result = self.cur.execute(query)
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT FROM routing! ", e)
else:
# convert rows to dict
routes = [dict(row) for row in result]
smsgwglobals.wislogger.debug("ROUTERDB: " + str(len(routes)) +
" wis entries selected.")
return routes
finally:
rdblock.release()
def write_statstimestamp(self, timestamp, intype='SUC_SMS_STATS'):
"""Insert or replace a stats entry timestamp
"""
query = ("INSERT OR REPLACE INTO stats " + "(type, lasttimestamp) " +
"VALUES (?, ?) ")
# set changed timestamp to utcnow if not set
try:
smsgwglobals.dblogger.debug("SQLite: Write into stats" +
" :intype: " + str(intype) +
" :lasttimestamp: " + str(timestamp))
self.__cur.execute(query, (intype, timestamp))
self.__con.commit()
smsgwglobals.dblogger.debug("SQLite: Insert done!")
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to INSERT stats! ", e)
def delete_routing_wisurl(self, wisurl):
smsgwglobals.wislogger.debug("ROUTERDB: Deleting" +
" routing entries...")
try:
query = ("UPDATE routing SET " + "obsolete = 14 " +
"WHERE wisurl = ? " + "AND obsolete < 14")
rdblock.acquire()
result = self.cur.execute(query, [wisurl])
count = result.rowcount
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: " + str(count) +
" routing DELETE WISURL!")
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to DELETE from routing! ", e)
finally:
rdblock.release()
def read_sms_count(self, routingid):
smsgwglobals.wislogger.debug("ROUTERDB: Read routing entries")
query = ("SELECT " + "sms_count " + "FROM routing " +
"WHERE routingid = ?")
try:
rdblock.acquire()
result = self.cur.execute(query, [routingid])
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT FROM routing! ", e)
else:
# convert rows to dict
routes = [dict(row) for row in result]
smsgwglobals.wislogger.debug("ROUTERDB: " + str(len(routes)) +
" routing entries selected.")
return routes
finally:
rdblock.release()
def decrease_sms_count(self, modemid):
smsgwglobals.wislogger.debug("ROUTERDB: Decreasing sms_count")
try:
query = ("UPDATE routing SET " + "sms_count = sms_count - 1 " +
"WHERE modemid = ? ")
rdblock.acquire()
result = self.cur.execute(query, [modemid])
count = result.rowcount
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: " + str(count) +
" sms_count updated!")
return count
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to change sms_count! ", e)
finally:
rdblock.release()
def change_obsolete(self, routingid, obsolete):
smsgwglobals.wislogger.debug("ROUTERDB: Changing" +
" routing entries...")
try:
query = ("UPDATE routing SET " + "obsolete = ? " +
"WHERE routingid = ?")
rdblock.acquire()
result = self.cur.execute(query, (obsolete, routingid))
count = result.rowcount
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: " + str(count) +
" routing OBSOLETE CHANGEG!")
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to change obsolete! ", e)
finally:
rdblock.release()
def read_sms_stats(self):
query = (
"SELECT sum(case when status = 1 AND statustime BETWEEN ? AND ? then 1 else 0 end), "
+
"sum(case when status = 104 or status = 105 then 1 else 0 end) " +
"FROM sms;")
utc_timezone = pytz.timezone("UTC")
ua_timezone = pytz.timezone("Europe/Kiev")
today = datetime.utcnow().astimezone(ua_timezone).date()
start = datetime(today.year,
today.month,
today.day,
tzinfo=ua_timezone).astimezone(utc_timezone)
end = start + timedelta(1)
smsgwglobals.dblogger.debug(
"SQLite: Read SENT SMS stats" +
" for last 24 hours + Read RESEND SMS stats")
try:
smsdblock.acquire()
result = self.__cur.execute(query, [start, end])
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to SELECT sms_count FROM sms! ",
e)
else:
res = result.fetchone()
processed_sms_count = res[0] if res[0] is not None else 0
unprocessed_sms_count = res[1] if res[1] is not None else 0
return {
"processed_sms": processed_sms_count,
"unprocessed_sms": unprocessed_sms_count
}
finally:
smsdblock.release()
def raise_heartbeat(self, routingid):
smsgwglobals.wislogger.debug("ROUTERDB: Raising Heartbeat" +
" routing entries...")
try:
now = datetime.utcnow()
smsgwglobals.wislogger.debug("ROUTERDB: NEW HEARTBEAT " + str(now))
query = ("UPDATE routing SET " + "changed = ? ," +
"obsolete = 0 " + "WHERE routingid = ? " +
"AND obsolete < 14")
routes = self.read_routing()
current_route = [
route for route in routes if route['routingid'] == routingid
]
if current_route:
db = database.Database()
sms_count = db.read_sms_count_by_imsi(current_route[0]["imsi"])
# Looks like we enter new day - reset sms counter
if sms_count == 0:
self.reset_sms_count(routingid)
rdblock.acquire()
result = self.cur.execute(query, [now, routingid])
count = result.rowcount
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: " + str(count) +
" routing HEARTBEAT updated!")
return count
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to change obsolete! ", e)
finally:
rdblock.release()
def delete_routing(self, routingid=None):
smsgwglobals.wislogger.debug("ROUTERDB: Deleting" +
" routing entries...")
try:
if (routingid is None):
query = ("DELETE FROM routing " + "WHERE obsolete = ?")
rdblock.acquire()
result = self.cur.execute(query, [16])
else:
query = ("DELETE FROM routing " + "WHERE routingid = ?")
rdblock.acquire()
result = self.cur.execute(query, routingid)
count = result.rowcount
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: " + str(count) +
" routing DELETE MAIN!")
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to DELETE from routing! ", e)
finally:
rdblock.release()
def insert_sms(self,
modemid='00431234',
imsi='1234567890',
targetnr='+431234',
content='♠♣♥♦Test',
priority=1,
appid='demo',
sourceip='127.0.0.1',
xforwardedfor='172.0.0.1',
smsintime=None,
status=0,
statustime=None,
smsid=None):
"""Insert a fresh SMS out of WIS
Attributes: modemid ... string-countryexitcode+number (0043664123..)
imsi ... string-no SIM card IMSI
targetnr ... string-no country exit code (+436761234..)
content ... string-message
prioirty ... int-0 low, 1 middle, 2 high
appid ... sting (uuid) for consumer
sourceip ... string with ip (172.0.0.1)
xforwaredfor ... stirng with client ip
smsintime ... datetime.utcnow()
status ... int-0 new, ???
statustime ... datetime.utcnow()
"""
# check if smsid is empty string or None
if smsid is None or not smsid:
smsid = str(uuid.uuid1())
now = datetime.utcnow()
if smsintime is None:
smsintime = now
if statustime is None:
statustime = now
query = (
"INSERT INTO sms " +
"(smsid, modemid, imsi, targetnr, content, priority, " +
"appid, sourceip, xforwardedfor, smsintime, " +
"status, statustime) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" +
"ON CONFLICT(smsid) DO UPDATE SET " +
"modemid=excluded.modemid, imsi=excluded.imsi, statustime=excluded.statustime, status=excluded.status"
)
try:
smsdblock.acquire()
smsgwglobals.dblogger.debug("SQLite: Insert SMS" + " :smsid: " +
smsid + " :imsi: " + imsi +
" :modemid: " + modemid +
" :targetnr: " + targetnr +
" :content: " + content +
" :priority: " + str(priority) +
" :appid: " + appid + " :sourceip: " +
sourceip + " :xforwardedfor: " +
xforwardedfor + " :smsintime: " +
str(smsintime) + " :status: " +
str(status) + " :statustime: " +
str(statustime))
self.__con.execute(
query,
(smsid, modemid, imsi, targetnr, content, priority, appid,
sourceip, xforwardedfor, smsintime, status, statustime))
self.__con.commit()
smsgwglobals.dblogger.debug("SQLite: Insert done!")
except Exception as e:
smsgwglobals.dblogger.critical(
"SQLite: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to INSERT sms! ", e)
finally:
smsdblock.release()
def write_routing(self, route, changed=None):
"""Insert or replace a routing entry
Attributes: wisid ... text-1st of primary key
modemid ... text-serving modem number-2nd of primary key
regex ... text-regex to match numbers for modem
sms_count ... int-number of sms delivered
sms_limit ... int-number of sms limit to sent via this route
account_balance ... float-number of amount of money on SIM card
imsi ... text-serving SIM card IMSI
imei ... text-serving modem IMEI
carrier ... text-serving network carrier name/code
lbfactor ... int-factor if different contingets
wisurl ... text-url of wis
obsolete ... route got flag for deletion
modemname ... text-longtext of modem
changed ... datetime.utcnow-when changed
"""
query = (
"INSERT OR REPLACE INTO routing " +
"(wisid, modemid, regex, sms_count, sms_limit, account_balance, imsi, imei, carrier, lbfactor, wisurl, "
+
"pisurl, obsolete, modemname, sim_blocked, routingid, changed) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ")
# read sms_count if exist
db = database.Database()
sms_count = db.read_sms_count_by_imsi(route["imsi"])
if changed is None:
changed = datetime.utcnow()
try:
smsgwglobals.wislogger.debug(
"ROUTERDB: Write into routing" + " :wisid: " + route["wisid"] +
" :modemid: " + route["modemid"] + " :regex: " +
route["regex"] + " :sms_count: " + str(sms_count) +
" :sms_limit: " + str(route["sms_limit"]) +
" :account_balance: " + str(route["account_balance"]) +
" :imsi: " + route["imsi"] + " :imei: " + route["imei"] +
" :carrier: " + route["carrier"] + " :lbfactor: " +
str(route["lbfactor"]) + " :wisurl: " + route["wisurl"] +
" :pisurl: " + route["pisurl"] + " :obsolete: " +
str(route["obsolete"]) + " :modemname: " + route["modemname"] +
" :sim_blocked: " + route["sim_blocked"] + " :routingid: " +
route["routingid"] + " :changed: " + str(changed))
rdblock.acquire()
self.cur.execute(
query, (route["wisid"], route["modemid"], route["regex"],
sms_count, route["sms_limit"],
route["account_balance"], route["imsi"], route["imei"],
route["carrier"], route["lbfactor"], route["wisurl"],
route["pisurl"], route["obsolete"], route["modemname"],
route["sim_blocked"], route["routingid"], changed))
self.con.commit()
smsgwglobals.wislogger.debug("ROUTERDB: INSERT!")
except Exception as e:
smsgwglobals.wislogger.critical(
"ROUTERDB: " + query + " failed! [EXCEPTION]:%s", e)
raise error.DatabaseError("Unable to INSERT routing entry! ", e)
finally:
rdblock.release()
