1: <?php
  2: /**
  3:  * Ulaform_Action_Sql Class provides a Ulaform action driver to submit the
  4:  * results of a form to database.
  5:  *
  6:  * Copyright 2004-2012 Horde LLC (http://www.horde.org/)
  7:  *
  8:  * See the enclosed file COPYING for license information (GPL). If you
  9:  * did not receive this file, see http://www.horde.org/licenses/gpl.
 10:  *
 11:  * @author  Vilius Šumskas <vilius@lnk.lt>
 12:  * @package Ulaform
 13:  */
 14: class Ulaform_Action_Sql extends Ulaform_Action {
 15: 
 16:     /**
 17:      * The database connection object.
 18:      *
 19:      * @var Horde_Db_Adapter
 20:      */
 21:     protected $_db;
 22: 
 23:     /**
 24:      * Charset
 25:      *
 26:      * @var string
 27:      */
 28:     protected $_charset;
 29: 
 30:     /**
 31:      * Construct a new SQL storage object.
 32:      *
 33:      * @param array $params    The connection parameters
 34:      *
 35:      * @throws InvalidArguementException
 36:      */
 37:     public function __construct($params = array())
 38:     {
 39:         if (empty($params['db'])) {
 40:             throw new InvalidArgumentException('Missing required connection parameter(s).');
 41:         }
 42:         $this->_db = $params['db'];
 43:         $this->_charset = $params['charset'];
 44:     }
 45: 
 46:     /**
 47:      * Actually carry out the action.
 48:      *
 49:      * @return boolean  True on success.
 50:      * @throws Ulaform_Exception
 51:      */
 52:     public function doAction($form_params, $form_data, $fields)
 53:     {
 54:         /* Check if table exists. */
 55:         if (!in_array($form_params['table'], $this->_db->tables())) {
 56:             try {
 57:                 $this->_createDataTable($form_params, $fields);
 58:             } catch (Horde_Db_Exception $e) {
 59:                 throw new Ulaform_Exception($e->getMessage());
 60:             }
 61:         }
 62: 
 63:         /* Submit data to database. */
 64:         $columns = array();
 65:         $values = array();
 66:         foreach ($fields as $field) {
 67:             switch ($field['field_type']) {
 68:             case 'file':
 69:             case 'image':
 70:                 if (count($form_data[$field['field_name']])) {
 71:                     $data = file_get_contents($form_data[$field['field_name']]['file']);
 72:                     if (Horde_String::lower($this->_db->adapterName()) == 'mssql' ||
 73:                         Horde_String::lower($this->_db->adapterName()) == 'pgsql') {
 74:                         $data = bin2hex($data);
 75:                     }
 76:                     $columns[] = $field['field_name'];
 77:                     $values[] = $data;
 78:                 }
 79:                 break;
 80: 
 81:             case 'set':
 82:                 $columns[] = $field['field_name'];
 83:                 $values[] = implode(', ', $form_data[$field['field_name']]);
 84:                 break;
 85: 
 86:             default:
 87:                 $data = $form_data[$field['field_name']];
 88:                 $columns[] = $field['field_name'];
 89:                 $values[] = Horde_String::convertCharset($data, 'UTF-8', $this->_charset);
 90:                 break;
 91:             }
 92:         }
 93:         $sql = sprintf('INSERT INTO %s (%s) VALUES (%s)',
 94:                        $form_params['table'],
 95:                        implode(', ', $columns),
 96:                        str_repeat('?, ', count($values) - 1) . '?');
 97: 
 98:         try {
 99:             $this->_db->insert($sql, $values);
100:         } catch (Horde_Db_Exception $e) {
101:             throw new Ulaform_Exception($e->getMessage());
102:         }
103: 
104:         return true;
105:     }
106: 
107:     /**
108:      * Identifies this action driver and returns a brief description, used by
109:      * admin when configuring an action for a form and set up using Horde_Form.
110:      *
111:      * @return array  Array of required parameters.
112:      */
113:     static public function getInfo()
114:     {
115:         $info['name'] = _("SQL");
116:         $info['desc'] = _("This driver allows to insertion of form results into a database.");
117: 
118:         return $info;
119:     }
120: 
121:     /**
122:      * Returns the required parameters for this action driver, used by admin
123:      * when configuring an action for a form and set up using Horde_Form.
124:      *
125:      * @return array  Array of required parameters.
126:      */
127:     static public function getParams()
128:     {
129:         $params = array();
130:         $params['table'] = array('label' => _("Table"), 'type' => 'text');
131: 
132:         return $params;
133:     }
134: 
135:     /**
136:      * Create table for submiting data.
137:      *
138:      * @return boolean  True on success.
139:      * @throws Ulaform_Exception
140:      */
141:     protected function _createDataTable($form_params, $fields)
142:     {
143:         /* Generate SQL query. */
144:         $columns = array();
145:         foreach ($fields as $field) {
146:             switch ($field['field_type']) {
147:             case 'file':
148:             case 'image':
149:                 // TODO: Use Horde_SQL
150:                 switch (Horde_String::lower($this->_db->adapterName())) {
151:                 case 'pgsql':
152:                     $columns[] = $field['field_name'] . ' TEXT';
153:                     break;
154: 
155:                 case 'mysql':
156:                 case 'mysqli':
157:                     $columns[] = $field['field_name'] . ' MEDIUMBLOB';
158:                     break;
159: 
160:                 default:
161:                     $columns[] = $field['field_name'] . ' BLOB';
162:                     break;
163:                 }
164:                 break;
165:             case 'address':
166:             case 'countedtext':
167:             case 'description':
168:             case 'html':
169:             case 'longtext':
170:             case 'set':
171:                 $columns[] = $field['field_name'] . ' TEXT';
172:                 break;
173:             default:
174:                 $columns[] = $field['field_name'] . ' VARCHAR(255)';
175:                 break;
176:             }
177:         }
178:         $sql = sprintf('CREATE TABLE %s (%s)',
179:                        $form_params['table'],
180:                        implode(', ', $columns));
181: 
182:         /* Create table. */
183:         try {
184:             $this->_db->execute($sql);
185:         } catch (Horde_Db_Exception $e) {
186:             throw new Ulaform_Exception($e);
187:         }
188:         return true;
189:     }
190: 
191: }
192: