Mar 102011
 

From time to time, ports/security/vuxml/vuln.xml gets a date field containing an invalid date. FreshPorts stores these entries in a table that looks like this:

freshports.org=# \d vuxml
                                Table "public.vuxml"
     Column     |     Type     |                     Modifiers
----------------+--------------+----------------------------------------------------
 id             | integer      | not null default nextval('vuxml_id_seq'::regclass)
 vid            | text         | not null
 topic          | text         | not null
 description    | text         | not null
 date_discovery | text         |
 date_entry     | text         |
 date_modified  | text         |
 status         | character(1) | not null
Indexes:
    "vuxml_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "vuxml_affected" CONSTRAINT "$1" 
           FOREIGN KEY (vuxml_id) REFERENCES vuxml(id)
           ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "commit_log_ports_vuxml" CONSTRAINT "$1"
            FOREIGN KEY (vuxml_id) REFERENCES vuxml(id) 
            ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "vuxml_references" CONSTRAINT "$1" 
            FOREIGN KEY (vuxml_id) REFERENCES vuxml(id)
            ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=#

Of note is the three fields stored as TEXT values:

  1. date_discovery
  2. date_entry
  3. date_modifed

I do not know why this table was designed with text values. They should be date fields. But because they are text, errors such as this can slip in:

<discovery>2011-03-FIXME</discovery>

FreshPorts happily stores these values, but will later encounter a problem:

This message was generated by the FreshPorts Daemon.

The database is freshports.org
at http://www.freshports.org/

Could not execute SQL statement
--
  SELECT count(DISTINCT CLPV.port_id) AS ports,
         count(DISTINCT V.id)              AS vulns
    FROM commit_log_ports_vuxml CLPV, vuxml V
   WHERE CLPV.vuxml_id = V.id
     AND greatest(V.date_modified, V.date_entry, V.date_discovery)::date >= (current_date - interval '14 days')::date
--
... maybe invalid?

--
hugs+kisses
FreshPorts Daemon

The above SQL was created quite some time after the table was designed an in use.

I recently converted the vuxml table in my development copy of FreshPorts:

freshports.org=# begin;
BEGIN
freshports.org=# alter table vuxml alter column date_discovery type date using date_discovery::date;
ALTER TABLE
freshports.org=# alter table vuxml alter column date_entry type date using date_entry::date;
ALTER TABLE
freshports.org=# alter table vuxml alter column date_modified type date using date_modified::date;
ALTER TABLE
freshports.org=# commit;
COMMIT

I’ll monitor how things go.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

  One Response to “Storing dates in date fields”

  1. Today it caught another 2011-03-FIXME. At least it caught it at commit phase instead of later. Soon, I’ll write the code to notify the committer instead of just me.