Small patch for PostgreSQL installs

I reported this early, but didn’t send in a patch … the issue is that postgresql seems to store email addressses as bytes (no idea why, if anyone knows?):

CREATE TABLE maia_mail (
   id               SERIAL PRIMARY KEY,
   received_date    TIMESTAMP NOT NULL,
   size             INTEGER NOT NULL,
   sender_email     BYTEA NOT NULL,
   envelope_to      BYTEA NOT NULL,
   subject          VARCHAR(255) NOT NULL,
   contents         BYTEA NOT NULL,
   score            NUMERIC(7,3), -- only supplied for (S)pam
   autolearn_status VARCHAR(15) DEFAULT 'unavailable' NOT NULL -- 'ham', 'spam', 'no', 'disabled', 'failed', 'unavailable'
);
CREATE INDEX maia_mail_idx_received_date ON maia_mail(received_date);
CREATE INDEX maia_mail_idx_sender_email ON maia_mail(sender_email);
CREATE INDEX maia_mail_idx_subject ON maia_mail(subject);
CREATE INDEX maia_mail_idx_score ON maia_mail(score);

Which, in the database, looks like:

# select maia_mail.sender_email from maia_mail limit 5;
                                sender_email                                
----------------------------------------------------------------------------
 \x7466786f666661646d40747275737470726f66657373696f6e616c732e636f6d
 \x6d61696c61646d696e4063736d61696c2e68616e6e61616e64657273736f6e2e636f6d
 \x73635f61646d696e40736b696e6169742e636f6d
 \x
 \x70676275696c646661726d2d77656240737276302e70676275696c646661726d2e6f7267
(5 rows)

for display purposes, those fields need to be parsed as "encode( field, ‘escape’ )”, which the included patch fixes … 

Does anyone know *why* its storing as bytes?




*** /tmp/cache.php      Thu May  7 18:18:00 2015
--- cache.php   Thu May  7 18:36:35 2015
***************
*** 240,259 ****
          $this->select_stmt = "SELECT maia_mail.id, ";
          if (substr($this->dbtype,0,5) == "mysql") {
  
!            $this->select_stmt .= "DATE_ADD(maia_mail.received_date, INTERVAL " . $_SESSION["clock_offset"] . " SECOND) AS received_date, ";
  
          } elseif ($this->dbtype == "pgsql") {
  
!            $this->select_stmt .= "date_trunc('second', maia_mail.received_date + INTERVAL '" . $_SESSION["clock_offset"] . " SECOND') AS received_date, ";
  
          }
!         $this->select_stmt .= "maia_mail.score, maia_mail.sender_email, maia_mail.subject, maia_mail.envelope_to " .
                                "FROM maia_mail_recipients " .
                                "LEFT JOIN maia_mail " .
                                "ON maia_mail.id = maia_mail_recipients.mail_id " .
                                "WHERE " . $this->get_sort_stmt() .
                                "AND maia_mail_recipients.recipient_id = ? " .
                                " ORDER BY maia_mail." . $this->sortby['column'] . " " . $this->sort_order;
      }
      
      function confirm_cache($euid) {    
--- 240,260 ----
          $this->select_stmt = "SELECT maia_mail.id, ";
          if (substr($this->dbtype,0,5) == "mysql") {
  
!            $this->select_stmt .= "DATE_ADD(maia_mail.received_date, INTERVAL " . $_SESSION["clock_offset"] . " SECOND) AS received_date, maia_mail.sender_email, maia_mail.envelope_to, ";
  
          } elseif ($this->dbtype == "pgsql") {
  
!            $this->select_stmt .= "date_trunc('second', maia_mail.received_date + INTERVAL '" . $_SESSION["clock_offset"] . " SECOND') AS received_date, encode(maia_mail.sender_email, 'escape') AS sender_email, encode(maia_mail.envelope_to, 'escape') AS envelope_to, ";
  
          }
!         $this->select_stmt .= "maia_mail.score, maia_mail.subject " .
                                "FROM maia_mail_recipients " .
                                "LEFT JOIN maia_mail " .
                                "ON maia_mail.id = maia_mail_recipients.mail_id " .
                                "WHERE " . $this->get_sort_stmt() .
                                "AND maia_mail_recipients.recipient_id = ? " .
                                " ORDER BY maia_mail." . $this->sortby['column'] . " " . $this->sort_order;
+ 
      }
      
      function confirm_cache($euid) {    
***************
*** 549,555 ****
                  $rows[$count]['received_date'] = $row["received_date"];
                  $rows[$count]['sender_email'] = $magic_quotes ? stripslashes($row["sender_email"]) : $row["sender_email"];
                  $rows[$count]['score'] = $row['score'];
!  
                      $to_list = explode(" ", $row["envelope_to"]);
                      $rectmp = "";
                      foreach ($to_list as $recipient) {
--- 550,556 ----
                  $rows[$count]['received_date'] = $row["received_date"];
                  $rows[$count]['sender_email'] = $magic_quotes ? stripslashes($row["sender_email"]) : $row["sender_email"];
                  $rows[$count]['score'] = $row['score'];
! 
                      $to_list = explode(" ", $row["envelope_to"]);
                      $rectmp = "";
                      foreach ($to_list as $recipient) {
***************
*** 557,562 ****
--- 558,564 ----
                            $rectmp[] = $recipient;
                          }
                      }
+ 
                      $rows[$count]['recipient_email'] = $rectmp;
  
             $subject = $magic_quotes ? stripslashes($row['subject']) : $row['subject'];

Marc G Fournier
Owner, Hub.Org Hosting Solutions S.A.

_______________________________________________
Maia-users mailing list
[email protected]
http://www.renaissoft.com/cgi-bin/mailman/listinfo/maia-users