2012-05-31

rrdtool lastupdate

A variety of tools use RRD databases which are ideal for recording a sequence of values over time.  So one interesting question is when the last time the rrd file was updated - rrdtool's lastupdate mode will tell you.
$ rrdtool lastupdate probe302Value_probe302Value.rrd
1334603585: 190.0
Text 1:   The most recent value in the RRD is 190.0 recorded at 1334603585
The value of the time is a UTC timestamp.  Converting this value to a readable date/time can be performed using the UN*X date command.
$ date -d '@1334603681'
Mon Apr 16 15:14:41 EDT 2012
Text 2: Converting a timestamp to a date/time.
With knowing the last update and most recent value you can verify that the database is being updated at the correct interval and with the expected values.

2012-05-16

Informix Dialect With CASE Derived Polymorphism

I ran into an interesting issue when using SQLAlchemy 0.7.7 with the Informix dialect.  In a rather ugly database (which dates back to the late 1980s) there is a table called "xrefr" that contains two types of records: "supersede" and "cross".  What those signify doesn't really matter for this issue so I'll skip any further explanation.  But the really twisted part is that while a single field distinquishes between these two record types - it does not do so based on a consistent value.  If the value of this field is "S" then the record is a "supersede", any other value (including NULL) means it is a "cross".  This makes creating a polymorphic presentation of this schema a bit more complicated.  But have no fear, SQLAlchemy is here!

When faced with a similar issue in the past, on top of PostgreSQL, I've created polymorphic presentations using CASE clauses. But when I tried to do this using the Informix dialect the generated queries failed. They raised the dreaded -201 "Syntax error or access violation" message. 
The Informix SQLCODE -201 is in the running for "Most useless error message ever!".  Currently it is tied with PHP's "Stack Frame 0" message.  Microsoft's "File not found" [no filename specified] is no longer in the running as she is being held at the Hague to face war crimes charges.
Rant#1: Why do developers get away with such lazy error messages?
The original [failing] code that I tried looked something like this:
class XrefrRecord(Base):
    __tablename__  = 'xrefr'
    record_id      = Column("xr_serial_no", Integer, primary_key=True)
    ....
    _supersede     = Column("xr_supersede", String(1))
    is_supersede   = column_property( case( [ ( _supersede == 'S', 1, ), ],
                                            else_ = 0 ) )
 
    __mapper_args__ = { 'polymorphic_on': is_supersede }   
   
   
class Cross(XrefrRecord): 
    __mapper_args__ = {'polymorphic_identity': 0} 
           
           
class Supsersede(XrefrRecord): 
    __mapper_args__ = {'polymorphic_identity': 1}
Text#1: Code snippet that does not work.
The generated query looked like:
  SELECT xrefr.xr_serial_no AS xrefr_xr_serial_no,
         .....
         CASE
           WHEN (xrefr.xr_supersede = :1) THEN :2 ELSE :3
           END AS anon_1
  FROM xrefr
  WHERE xrefr.xr_oem_code = :4 AND
        xrefr.xr_vend_code = :5 AND
        CASE
          WHEN (xrefr.xr_supersede = :6) THEN :7
          ELSE :8
         END IN (:9) <--- ('S', 1, 0, '35X', 'A78', 'S', 1, 0, 0)
Text#2: Query SQLAlchemy generates for that construct.
It would seem that this would work.  If you substitute the values for their place holders in an application like DbVisualizer - it works.

The condition raising the -201 error is the use of place holders in a CASE WHEN structure within the projection clause of the query statement; the DBAPI module / Informix Engine does not [or can not] infer the type [cast] of the values.  The SQL cannot be executed unless the values are bound to a type.  Why this results in a -201 and not a more specific data-type related error... that is beyond my pay-grade.
Notice that when used like this in the projection clause the values to be bound are both input and output values.
Aside#1: An existential dilemma
The trick to get this to work is to explicitly declare the types of the values when constructing the case statement for the polymorphic mapper. This can be accomplished using the literal_column expression.
from sqlalchemy import literal_column

class XrefrRecord(Base):
    _supersede    = Column("xr_supersede", String(1))
    is_supersede  = column_property( case( [ ( _supersede == 'S', literal_column('1', Integer) ) ],
                                               else_ = literal_column('0', Integer) ) )
 
    __mapper_args__     = { 'polymorphic_on': is_supersede }
Text#3: A working CASE based polymorphic declaration.
Visually if you log or echo the statements they will not appear to be any different than before; but SQLAlchemy is now binding the values to a type when handing the query off to the DBAPI informixdb module. 

Happy polymorphing!