Re: Small patch for PostgreSQL installs

Hi Marc,

Great work, as with the other mail do you think you could do a github 
pull request?

https://github.com/technion/maia_mailguard

~~Jason S


On 2015-05-07 18:44, Marc Fournier wrote:
> 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

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