TIP: Use Markdown or, <pre> for multi line code blocks / <code> for inline code.
These forums are read-only and for archival purposes only!
Please join our new forums at discourse.kohanaframework.org
Permit "INTO OUTFILE" SELECT queries
  • With regard to the Database module, I would like to be able to execute SELECT queries with INTO OUTFILE clauses, so I extended the select builder:

    class Database_Query_Builder_Select extends Kohana_Database_Query_Builder_Select
    {
        
        protected $_outfile;
    
        public function outfile($filename = NULL)
        {
            $this->_type = Database::UPDATE;
            if (!empty($filename))
            {
                $this->_outfile = (string) $filename;
            }
            return $this;
        }
    
        public function compile(Database $db)
        {
            parent::compile($db);
            if ( ! empty($this->_outfile))
            {
                $outfile = " INTO OUTFILE '$this->_outfile' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'";
                $this->_sql .= $outfile;
            }
            return $this->_sql;
        }
    }
    

    Note the weird change of the type of the query; this was to avoid having Kohana_Database_MySQL::query() try to return a result from OUTFILE queries (if it treats them as UPDATES, then it correctly returns the number of affected/exported rows).

    Also, there's the simplistic way of just appending the INTO OUTFILE clause onto the end of the SQL. This is fine, I think, because neither of FOR UPDATE or LOCK IN SHARE MODE are used.

    Of course, there's additional customisation to be done with the field termination and enclosure characters, but I'm just working towards CSV at the mo'.

    So, does anyone have a better idea of how this should be done?

    (I saw that something similar came up a couple of years ago, but wasn't resolved. That's the only mention of the topic I've been able to find. Sorry if I've missed previous discussions.)

    Thanks! :-)