Commits

Guido Draheim committed 28b588d

channelsconf with sql orm

Comments (0)

Files changed (2)

src/dvbcronrecording/channelsconf.py

 from translate import Translate
 from tuning import tuning_adapter_list
 
+from db.session import db_cnx, commit #@UnresolvedImport
+from db.schema import RecordingChannelsConf
+import sqlalchemy as _as
+
+
 PACKAGE = u'dvbcronrecording'
 NAV = u'recordings'
 URL = u'recording'
 
 DEBUG = False
 
-from db import schema
-def affinity(table_name, column_name):
-    global tables
-    for table in schema.tables:
-        if table.name == table_name:
-            for column in table.columns:
-                if column.name == column_name:
-                    return column.type
-    return None
-def aff(value, table_name, column_name):
-    if value is None: return None
-    typed = affinity(table_name, column_name)
-    if typed in [ "int", "INT", "integer", "INTEGER"]:
-        return intnull(value)
-    return ustr(value)
-def intnull(value, default = None):
-    if value is None: return default
-    try: return int(value)
-    except Exception: return default
-def lookup(lists, entry, default = None):
-    if entry is None: return default
-    idx = intnull(entry)
-    if idx is None or idx >= len(lists): return default
-    return lists[idx]
 def ustr(text):
     if type(text) is unicode: return text
     if type(text) is str: return unicode(text)
         translate = Translate("channelsconf", req.locale)
 
         # getting cursor
-        db = self.env.get_db_cnx()
-        cursor = db.cursor()
         page = req.args['page']
         if not page: page = "show"
         if page in [ "save" ]:
             text = req.args["text"]
-            result = self.savetext(cursor, req, text)
-            db.commit()
+            result = self.savetext(req, text)
+            commit()
             data = {}
             data['title'] = translate("Saved")
             data['messages'] = ['(%s)' % text, result]
-            data['adapterlist'] = self._adapter_list(cursor, req)
-            data['text'] = self.loadtext(cursor, req)
+            data['adapterlist'] = self._adapter_list(req)
+            data['text'] = self.loadtext(req)
             data['_'] = translate
             add_stylesheet(req, PACKAGE+'/css/recordingchannelsconf.css')
             add_script(req, 'common/js/trac.js')
             data = {}
             data['title'] = translate("Edit")
             data['messages'] = [ translate('use the traditional dvbscan/zap syntax') ]
-            data['adapterlist'] = self._adapter_list(cursor, req)
-            data['text'] = self.loadtext(cursor, req)
+            data['adapterlist'] = self._adapter_list(req)
+            data['text'] = self.loadtext(req)
             data['_'] = translate
             add_stylesheet(req, PACKAGE+'/css/recordingchannelsconf.css')
             add_script(req, 'common/js/trac.js')
             return ('channelsconf_editor.html', data, None)
         if page in [ "take", "takeover" ]:
-            channels = self._channelsconf_selectby(cursor, req, ['adapter', 'title'])
+            channels = self._channelsconf_selectby(req, ['adapter', 'title'])
             data = {}
             data['title'] = translate("Takeover")
             data['messages'] = [ "" ]
-            data['adapterlist'] = self._adapter_list(cursor, req)
+            data['adapterlist'] = self._adapter_list(req)
             data["_pagenum"] = req.args.get("_pagenum", "0")
             data["_pagesize"] = req.args.get("_pagesize", "10")
             data['datalist'] = Paginator(channels, int(data["_pagenum"]), int(data["_pagesize"]))
         message = ""
         
         if page in [ 'newentry', 'append' ]:
-            message = self._channelsconf_append(cursor, req)
+            message = self._channelsconf_append(req)
         if page in [ 'update' ]:
-            message = self._channelsconf_update(cursor, req)
+            message = self._channelsconf_update(req)
         if page in [ 'delete' ]:
-            message = self._channelsconf_delete(cursor, req)
+            message = self._channelsconf_delete(req)
         
         # database commit and return page content
-        db.commit()
+        commit()
 
         add_stylesheet(req, 'common/css/wiki.css')
         add_stylesheet(req, PACKAGE+'/css/recordingchannelsconf.css')
         message += "[%s]" % channeltype
         message += "{%s}" % page
         
-        newchannels_list = self._newchannels_list(cursor, req)
-        channels_list = self._channelsconf_list(cursor, req, channeltype)
+        newchannels_list = self._newchannels_list(req)
+        channels_list = self._channelsconf_list(req, channeltype)
         channeltypelist = ["all channel types",
                            "data channels", 
                            "radio channels", 
         data['message'] = message
         data['title'] = translate('Channel List')
         data['polaritylist'] = polaritylist
-        data['adapterlist'] = self._adapter_list(cursor, req)
+        data['adapterlist'] = self._adapter_list(req)
         data["channeltype"] = channeltype
         data["channeltypelist"] = channeltypelist
         data['new_datalist'] = newchannels_list
     """
       Returns list of the adapter settings
     """
-    def _adapter_list(self, cursor, req, defaults = [ 0 ]):
+    def _adapter_list(self, req, defaults = [ 0 ]):
         return tuning_adapter_list(self.env, defaults)
     
     """
       Returns list of the channels.conf
     """
-    def _channelsconf_selectby(self, cursor, req, selectby = ['adapter', 'title']):
-        view = selectby
-        cols = ['adapter', 'title','frequency','polarity','source','symbolrate','vpid', 'apid', 'tpid']
-        sql = "SELECT %s FROM recording_channelsconf" % (",".join(cols))
-        order_sql = " ORDER BY title, adapter, source LIMIT 11"
-        view_vals = []
-        view_cols = []
-        for col in view:
-            if req.args.has_key(col):
-                view_vals += [ aff(req.args[col], "recording_channelsconf", col) ]
-                view_cols += [ col ]
-        if view_cols:
-            view_sql = " WHERE " + (" AND ".join([" %s = %%s" % col for col in view_cols ]))
-            logg.info("sql = %s", sql + view_sql)
-            cursor.execute(sql + view_sql + order_sql, view_vals)
-        else:
-            logg.info("sql = %s;", sql + order_sql)
-            cursor.execute(sql)
-        entries = []
-        for entry in cursor:
-            entry = dict(zip(cols, entry))
-            entries.append(entry)
-        return entries
+    def _channelsconf_selectby(self, req, selectby = ['adapter', 'title']):
+        session = db_cnx(self.env)
+        q = session.query(RecordingChannelsConf)
+        for col in selectby:
+            if col in req.args:
+                q = q.filter_by(**{col : req.args.get(col)})
+        q = q.order_by("title", "adapter", "source")
+        return q.all()
     
-    def _newchannels_list(self, cursor, req):
+    def _newchannels_list(self, req):
         _ = ['adapter', 'title','frequency','polarity','source','symbolrate','vpid', 'apid', 'tpid']
         translate = Translate("channelsconf", req.locale)
         defs = {}
     """
       Returns list of the channels.conf
     """
-    def _channelsconf_list(self, cursor, req, channeltype = None):
+    def _channelsconf_list(self, req, channeltype = None):
+        session = db_cnx(self.env)
+        q = session.query(RecordingChannelsConf)
+        q = q.order_by("title", "adapter", "source")
         view = ['adapter']
-        cols = ['adapter', 'title','frequency','polarity','source','symbolrate','vpid', 'apid', 'tpid']
-        sql = "SELECT %s FROM recording_channelsconf" % (",".join(cols))
-        order_sql = " ORDER BY title, adapter, source"
-        view_vals = []
-        view_cols = []
-        type_cols = []
         for col in view:
             if req.args.has_key(col):
-                view_vals += [ aff(req.args[col], "recording_channelsconf", col) ]
-                view_cols += [ col ]
+                q = q.filter_by(**{ col : req.args.get(col)})
         if channeltype in [ "audio", "video", "radio channels", "video channels" ]:
-            type_cols += ["apid > 0"]
+            q = q.filter("apid > 0")
         elif channeltype in [ "data", "data channels"]:
-            type_cols += ["apid = 0"]
+            q = q.filter("apid = 0")
         if channeltype in [ "video", "video channels" ]:
-            type_cols += ["vpid > 0"]
+            q = q.filter("vpid > 0")
         elif channeltype in [ "audio", "radio channels"]:
-            type_cols += ["vpid = 0"]
-        if view_cols:
-            view_sql = " WHERE " + (" AND ".join([" %s = %%s" % col for col in view_cols ]))
-            if type_cols:
-                view_sql += " AND " + " AND ".join(type_cols)
-            logg.info("sql = %s", sql + view_sql)
-            cursor.execute(sql + view_sql + order_sql, view_vals)
-        else:
-            view_sql = ""
-            if type_cols:
-                view_sql += " WHERE " + " AND ".join(type_cols)
-            logg.info("sql = %s;", sql + order_sql)
-            cursor.execute(sql + view_sql + order_sql)
-        entries = []
-        for entry in cursor:
-            entry = dict(zip(cols, entry))
-            entries.append(entry)
-        return entries
+            q = q.filter("vpid = 0")
+        return q.all()
 
     """
       Appends a new entry into channels.conf list
     """
-    def _channelsconf_append(self, cursor, req):
-        cols = ['adapter', 'title','frequency','polarity','source','symbolrate','vpid', 'apid', 'tpid']
-        vals = [] # { "author" : req.authname or 'anonymous' }
-        for col in cols:
-            if req.args.has_key(col):
-                vals += [ aff(req.args[col], "recording_channel", col) ]
-            else:
-                vals += [ None ]
-        sql = "INSERT INTO recording_channelsconf (%s)" % ",".join(cols)
-        sql += " VALUES (%s)" % (",".join(["%s"] * len(cols)))
-        cursor.execute(sql, vals)
+    def _channelsconf_append(self, req):
+        item = RecordingChannelsConf()
+        item.frequency = req.args("frequency")
+        item.polarity = req.args("polarity")
+        item.source = req.args("source")
+        item.symbolrate = req.args("symbolrate")
+        item.vpid = req.args("vpid")
+        item.apid = req.args("apid")
+        item.tpid = req.args("tpid")
+        item.title = req.args.get("newtitle")
+        session = db_cnx(self.env)
+        session.add(item)
+        session.flush() 
         return "OK"
 
     """
       Updates an entry in the channels.conf list
     """
-    def _channelsconf_update(self, cursor, req):
+    def _channelsconf_update(self, req):
         keys = ['adapter', 'title']
-        cols = ['frequency','polarity','source','symbolrate','vpid', 'apid', 'tpid']
-        rename = { "newtitle" : "title" }
-        vals = [] # { "author" : req.authname or 'anonymous' }
-        for col in cols:
-            if req.args.has_key(col):
-                vals += [ aff(req.args[col], "recording_channelsconf", col) ]
-            else:
-                vals += [ None ] 
-        where = []
+        q = db_cnx(self.env).query(RecordingChannelsConf)
         for col in keys:
             if req.args.has_key(col):
-                where += [ aff(req.args[col], "recording_channelsconf", col) ]
-            else:
-                where += [ None ]
-        for col in rename:
-            if req.args.has_key(col):
-                col2 = rename[col]
-                vals += [ aff(req.args[col2], "recording_channelsconf", col) ]
-                cols += col2
-        sqlX = ""
+                q = q.filter_by(**{ col : req.args.get(col) })
         try:
-            sql = "UPDATE recording_channelsconf " 
-            sql += " SET "+(",".join([" %s = %%s" % col for col in cols]))
-            sql += " WHERE " + (" AND ".join([" %s = %%s" % col for col in keys ]))
-            sqlX = sql + " | " + str( vals + where )
-            cursor.execute(sql, vals + where)
+            for item in q.all():
+                item.frequency = req.args("frequency")
+                item.polarity = req.args("polarity")
+                item.source = req.args("source")
+                item.symbolrate = req.args("symbolrate")
+                item.vpid = req.args("vpid")
+                item.apid = req.args("apid")
+                item.tpid = req.args("tpid")
+                if req.args.get("newtitle"):
+                    item.title = req.args.get("newtitle") 
         except Exception, e:
-            return "ERROR: %s\n%s" % (str(e), sqlX)
+            return "ERROR: %s\n%s" % (str(e), q)
         return "OK"
 
     """
       Deletes entry from recordings
     """
-    def _channelsconf_delete(self, cursor, req):
+    def _channelsconf_delete(self, req):
+        session = db_cnx(self.env)
         keys = ['adapter', 'title']
-        key_vals = []
+        q = session.query(RecordingChannelsConf)
         for col in keys:
             if req.args.has_key(col):
-                key_vals += [ aff(req.args[col], "recording_channelsconf", col) ]
-            else:
-                key_vals += [ None ]
-        key_sql = " WHERE " + (" AND ".join([" %s = %%s" % col for col in keys ]))
-        cursor.execute("DELETE FROM recording_channelsconf "+key_sql, key_vals)
+                q = q.filter_by(**{ col : req.args.get(col) })
+        q.delete()
 
 
     # ---------------------------------------------------------------
-    def savetext(self, cursor, req, text):
+    def savetext(self, req, text):
         if not req.args.has_key("adapter"):
             return "no adapter given in update"
+        session = db_cnx(self.env)
         adapter = req.args["adapter"]
         sql = "DELETE FROM recording_channelsconf WHERE adapter = %s"
-        cursor.execute(sql, adapter)
+        session.execute(sql, adapter)
         msg = []
         for line in text.split("\n"):
-            msg += [ self.savetextline(cursor, req, unicode(line)) ]
+            msg += [ self.savetextline(req, unicode(line)) ]
         return "(%s lines = %s)" % (len(msg), str(msg))
-    def savetextline(self, cursor, req, line):
+    def savetextline(self, req, line):
         cols = ["title", "frequency", "polarity", "source", "symbolrate", "vpid","apid","tpid"]
         # VIVA PLUS:12551:v:0:22000:171:172:12120
         m = re.match(r"([^:]+):([^:]+):([^:]+):([^:]+):([^:]+):([^:]+):([^:]+):([^:]+)", line)
                     val = vals[n]
                     if val: val = val.strip()
                     req.args[ cols[n] ] = val
-            return self._channelsconf_append(cursor, req)
+            return self._channelsconf_append(req)
         else:
             return "NO."
-    def loadtext(self, cursor, req):
-        return "\n".join(list(self.loadtextlines(cursor, req)))
-    def loadtextlines(self, cursor, req):
+    def loadtext(self, req):
+        return "\n".join(list(self.loadtextlines(req)))
+    def loadtextlines(self, req):
         cols = ["title", "frequency", "polarity", "source", "symbolrate", "vpid","apid","tpid"]
-        entries = self._channelsconf_list(cursor, req)
+        entries = self._channelsconf_list(req)
         for entry in entries:
             vals = []
             for col in cols:
-                if col in entry:
-                    vals += [ ustr(entry[col]) ]
-                else:
-                    vals += ""
+                vals += [ ustr(getattr(entry, col))]
             yield (":".join(vals))

src/dvbcronrecording/db/schema.py

 
 RecordingTuning = db4.RecordingTuning
 RecordingChannels = db4.RecordingChannels
+RecordingChannelsConf = db4.RecordingChannelsConf