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()