Commits

Mike Miller  committed f49aede

fix: sort by primary key, not hardcoded to id. enh: add more robust type checking to table edit fields on keypress.

  • Participants
  • Parent commits 0eb3469

Comments (0)

Files changed (2)

     from utk import *
 
     appname     = 'dumbo'
-    __version__ = '0.50a'
+    __version__ = '0.60a'
     title       = ' %s %s ' % (appname.title(), __version__)
     inipath     = ('dumbo.ini', '~/.config/dumbo.ini', '/etc/dumbo.ini')
     debug       = False
 
-    # build up some defaults in case ini not found.
+    # build up some defaults in case .ini file not found.
     defdb = dict(type='postgres')
     defconfig = dict(dumbo=dict(theme='dark', db=defdb, use256=True))
     defpalette = [
                 # create query string and execute
                 q = gparent.config['dbtype']['query_update'] % dbinf
                 gparent.db.query(dbinf['dbname'], q, fetchresults=False)
+                self.set_edit_text(get_value_method())
+                self._orig = get_value_method() # in case blur before commit
+            elif input in ('tab','left','right','up','down'):  # restore
+                if hasattr(self, '_orig'): self.set_edit_text(self._orig)
+                return input
             else:
                 return input
 
     class EditDBStr(u.Edit, DBField):
         'An Edit widget for strings that may write to a Database.'
+        def __init__(self, *args, **kwargs):
+            self.maxlen = kwargs.pop('maxlen', None)
+            self.__super.__init__(*args, **kwargs)
+            self._orig = self.get_edit_text() # keep for later in case no commit
         def keypress(self, size, key):
             key = self.__super.keypress(size, key)
+            myvalue = self.get_edit_text()
+            if self.maxlen and len(myvalue) >= self.maxlen:
+                self.set_edit_text(myvalue[:self.maxlen])
             return self.unhandled_input(size, key, self.get_edit_text)
 
     class EditDBInt(u.IntEdit, DBField):
         'An Edit widget for integers that may write to a Database.'
+        def __init__(self, *args, **kwargs):
+            self.maxint = kwargs.pop('maxint', None)
+            self.__super.__init__(*args, **kwargs)
+            self._orig = self.value()  # keep for later in case no commit
+        def valid_char(self, ch):
+            return len(ch)==1 and ch in '-0123456789'
         def keypress(self, size, key):
+            if self.edit_text == '-0' and key == 'backspace':  # before handler
+                self.edit_text = ''
             key = self.__super.keypress(size, key)
+            if '-' in self.edit_text:
+                self.edit_text = '-' + self.edit_text.replace('-', '') # > 1
+                if self.edit_text == '-':   # long() doesn't like '-'
+                    self.edit_text = '-0'
+                    self.set_edit_pos(2)
+                elif self.edit_text == '-0':
+                    self.edit_text = ''
+            if self.maxint:
+                val = self.value()
+                if val >= self.maxint:
+                    self.set_edit_text(str(self.maxint - 1))
+                elif val <= (-self.maxint) - 1: #
+                    self.set_edit_text(str(-self.maxint))
+
             return self.unhandled_input(size, key, self.value)
 
 
 
         cur = self.conns[dbname].cursor()
         try:
-            log.info('%s against %s' % (querystr, dbname))
+            log.info('"%s" against %s' % (querystr, dbname))
             cur.execute(querystr)
             if fetchresults:    results = cur.fetchall()
             else:               results = None
-            log.debug('results: %.70r...' % results)
+            log.debug('results: %.512r...' % results)
             retval = results
         except (psycopg2.ProgrammingError, psycopg2.InternalError), e:  # fix trans
             cur.execute('rollback;')    # cannot recover without this
         if legend:
             log.debug('Using cached legend: %s' % legend)
         else:
-            try:
+            class mutable_list(list): pass  # in order to attach attribs
+            try: # get table legend
                 q = self.config['dbtype']['query_tablecols'] % tablename
                 results = self.db.query(dbname, q)
-                log.debug( repr( results ) )
-                legend = [ ('%s\n%s' % (x[1], x[2]))  # name, type
-                    for x in results ]
-                log.debug( repr(legend ) )
+                legend = mutable_list( results )
+                for colmd in legend:
+                    log.debug( repr(colmd) )
+
+                # attempt retrieval of primary key info
+                legend.pkeys = None
+                q2 = self.config['dbtype'].get('query_tablecols2')
+                if q2:
+                    q2 = q2 % tablename
+                    results = self.db.query(dbname, q2)
+                    if results:
+                        legend.pkeys = results
                 self.legendcache[key] = legend
             except Exception, e:
-                log.error('Legend not found: %s' % e)
+                import traceback
+                log.error('Legend not found: %s' % traceback.format_exc())
         return legend
 
     def list_db_cluster(self):
 
                 # get table legend and data
                 legend = self.get_tbl_legend(dbname, currname)
-                q = 'select * from %s order by id;' % currname
+                if legend.pkeys:
+                    pkeys = ','.join( (str(x[1]) for x in legend.pkeys ) )
+                    orderby = 'order by %s' % pkeys
+                else:
+                    orderby = 'order by id'
+
+                q = self.config['dbtype']['query_table'] % (currname, orderby)
                 results = self.db.query(dbname, q)
                 if results:
                     self.statxt.set_text(self.greeting)
-                elif results is False:  # try again
+                elif results is False:  # fallback, try again
                     q = 'select * from %s;' % currname
                     results = self.db.query(dbname, q)
                 self.populate_cont(results or [], dbname, currname, legend)
         else:
             if type(item) is LeafWidget:
-                legend = ('id',) + ('char',) * 8
+                legend = [[1, 'col01', 'character varying', None, 'YES', 255, None]]
+                for i in range(2,10):
+                    dummytype = legend[0][:]  # copy
+                    dummytype[0] = i
+                    dummytype[1] = 'col%02d' % i
+                    legend.append(dummytype)
                 dummy = ('The quick brown fox jumped over the lazy dog.'.split()
                     ,) * 20
                 self.populate_cont(dummy, legend=legend)
     def populate_cont(self, data, dbname=None, tbname=None, legend=None):
         'Load up the content pane.'
         rows = []
-        if legend:  # show name/type as column header & modest type enforcement
-            row = [ u.AttrMap(u.Text(x), 'logo') for x in legend ]
+        if legend:  # show name/type as column header
+            row = [ u.AttrMap( u.Text('%s\n%s' % (x[1],x[2])), 'logo' )
+                    for x in legend ]
             rows.append( u.Columns(row, 1, 0, 4, ) )
-
+            if hasattr(legend, 'pkeys') and legend.pkeys:
+                pkeys = legend.pkeys
+            else:
+                pkeys = ((1,'id'),)
+            log.debug('pkeys:' + repr(pkeys))
             # figure which col is which field, could be more efficient
-            nmfromi = dict( ((i,name.split()[0]) for i,name in enumerate(legend))  )
-            ifromnm = dict( ((name.split()[0], i) for i,name in enumerate(legend))  )
+            nmfromi = dict(( (i,leg[1]) for i,leg in enumerate(legend) ))
+            ifromnm = dict(( (leg[1], i) for i,leg in enumerate(legend) ))
 
             for row in data:
                 try:
                     newrow = []
-                    rowid = row[ifromnm['id']]
+                    # save primary key for later inside edit cell
+                    where = ''  #  pkstr = val, ...
+                    for j, pkindex in enumerate(pkeys):  # pkindex is 1-based
+                        pkindex, pkstr = pkindex
+                        if legend[pkindex-1][2] == 'integer':
+                            where += '%s = %s' % (pkstr, row[pkindex-1])
+                        else:
+                            where += "%s = '%s'" % (pkstr, row[pkindex-1])
+                        if j != len(pkeys) - 1:
+                            where += ' and '
+                    log.debug('where clause: ' + where)
+
+                    # populate pane, modest type enforcement
                     for i, field in enumerate(row):
-                        if legend[i].endswith('\ninteger'):
-                            editor = EditDBInt('', field)
+                        if legend[i][2] == 'integer':
+                            maxint = 2**(legend[i][6]-1)-1
+                            editor = EditDBInt('', field, maxint=maxint)
                         else:
-                            editor = EditDBStr('', str(field))
-                        editor._db_inf = { 'rid': rowid,
+                            editor = EditDBStr('', str(field), maxlen=legend[i][5])
+                        editor._db_inf = { 'where': where,
                             'dbname': dbname, 'tbname': tbname,
                             'fdname': nmfromi[i], 'gparent': self,
                         }
         if input in ('q', 'Q', 'esc'):
             if self.db: self.db.close()
             raise u.ExitMainLoop()
-        elif input == 'tab':
-            try:                    self.body.set_focus(i+1)
+        elif input in ('tab', 'right'):
+            try:
+                self.body.set_focus(i+1)
+                # keep focus out of legend, doesn't work with right key :/
+                if self.contpane.get_focus()[1] == 0:
+                    self.contpane.set_focus(1, coming_from='above')
             except AssertionError:  self.body.set_focus(0)
             log.debug('tab: col %s to %s' % (i, i+1))
 
     loop = u.MainLoop(df, df.palette, unhandled_input=df.unhandled_input)
     if df.config and df.config[appname]['use256'] and hicolor_avail:
         loop.screen.set_terminal_properties(colors=256)
-    loop.run()
+    try:
+        loop.run()
+    except Exception, e:    # try to close all db connections
+        print e.__class__.__name__, e, '.  Check log for details.\n'
+        import traceback
+        log.critical(traceback.format_exc())
+        if df.db:
+            df.db.close()
 
 
 [dbtype_postgres]
 module = psycopg2
-query_tablecols = SELECT ordinal_position,
+query_databases = select datname from pg_database;
+query_table = select * from %s %s;
+query_tables = select tablename from pg_tables;
+query_tablecols = select
+        ordinal_position,
         column_name,
         data_type,
         column_default,
         is_nullable,
         character_maximum_length,
         numeric_precision
-    FROM information_schema.columns
-    WHERE table_name = '%s'
-    ORDER BY ordinal_position;
-query_databases = select datname from pg_database;
-query_tables = select tablename from pg_tables;
-query_table = select * from %s order by id;
-query_update = UPDATE ONLY %(tbname)s
-    SET %(pair)s
-    WHERE id = %(rid)s ; COMMIT;
+    from
+        information_schema.columns
+    where
+        table_name = '%s'
+    order by
+        ordinal_position;
+query_tablecols2 = select
+        ordinal_position,column_name
+    from
+        information_schema.key_column_usage
+    where
+        table_name = '%s' and constraint_name like '%%_pkey';
+query_update = update only %(tbname)s
+    set
+        %(pair)s
+    where
+        %(where)s ; commit;