Ejemplo n.º 1
0
 /**
  * Генератор INSERT/UPDATE/DELETE процедур для таблицы
  * @param $action
  */
 private function __storedProcedureGeneration($action)
 {
     $proc_query = '';
     $table_name = $this->getTable();
     $prefix = $this->getProcPrefix();
     $query = 'CALL show_table_columns(:tableName)';
     $params = array(':tableName' => $table_name);
     $fields = PdoWrap::select($query, $params);
     $fields_count = count($fields);
     if ($action == 'insert') {
         $proc_query = 'CREATE DEFINER=`' . PdoWrap::getCurrentDbName() . '`@`' . PdoWrap::getCurrentDbHost() . '` PROCEDURE `' . $prefix . '_' . $action . '`(';
         for ($i = 0; $i < $fields_count; $i++) {
             if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_edit') {
                 $proc_query .= '
                 IN `field_' . $fields[$i]['Field'] . '` ' . Text::strtoup(preg_replace('/unsigned/', '', $fields[$i]['Type'])) . ',';
             }
         }
         $proc_query = substr($proc_query, 0, -1);
         $proc_query .= '
         )
         BEGIN
         SET';
         for ($i = 0; $i < $fields_count; $i++) {
             if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_edit') {
                 $proc_query .= '
             @p' . $i . ' = field_' . $fields[$i]['Field'] . ',';
             }
         }
         $proc_query = substr($proc_query, 0, -1) . ';';
         $proc_query .= '
         INSERT INTO ' . $table_name . ' (';
         for ($i = 0; $i < $fields_count; $i++) {
             if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_edit') {
                 $proc_query .= '`' . $fields[$i]['Field'] . '`,';
             }
         }
         $proc_query = substr($proc_query, 0, -1) . ')';
         $proc_query .= '
         VALUES (';
         for ($i = 0; $i < $fields_count; $i++) {
             if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_edit') {
                 if ($fields[$i]['Field'] == 'date_add') {
                     $proc_query .= 'UNIX_TIMESTAMP(),';
                 } else {
                     $proc_query .= '@p' . $i . ',';
                 }
             }
         }
         $proc_query = substr($proc_query, 0, -1) . ');';
         $proc_query .= '
         SELECT last_insert_id() AS `id`;
         END;';
     } elseif ($action == 'update') {
         $proc_query = 'CREATE DEFINER=`' . PdoWrap::getCurrentDbName() . '`@`' . PdoWrap::getCurrentDbHost() . '` PROCEDURE `' . $prefix . '_' . $action . '` (';
         for ($i = 0; $i < $fields_count; $i++) {
             if ($fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_add') {
                 $proc_query .= '
                 IN `field_' . $fields[$i]['Field'] . '` ' . Text::strtoup(preg_replace('/unsigned/', '', $fields[$i]['Type'])) . ',';
             }
         }
         $proc_query = substr($proc_query, 0, -1);
         $proc_query .= '
         )
         BEGIN
         SET';
         for ($i = 0; $i < $fields_count; $i++) {
             if ($fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'date_edit' && $fields[$i]['Field'] != 'user_add') {
                 $proc_query .= '
             @p' . $i . ' = field_' . $fields[$i]['Field'] . ',';
             }
         }
         $proc_query = substr($proc_query, 0, -1) . ';';
         $proc_query .= '
         UPDATE `' . $table_name . '` SET';
         for ($i = 0; $i < $fields_count; $i++) {
             if ($fields[$i]['Extra'] != 'auto_increment' && $fields[$i]['Field'] != 'date_add' && $fields[$i]['Field'] != 'user_add') {
                 if ($fields[$i]['Field'] == 'date_edit') {
                     $proc_query .= '
                         `' . $fields[$i]['Field'] . '` = UNIX_TIMESTAMP(),';
                 } else {
                     $proc_query .= '
                         `' . $fields[$i]['Field'] . '` = @p' . $i . ',';
                 }
             } elseif ($fields[$i]['Extra'] == 'auto_increment') {
                 $update_id = '@p' . $i;
             }
         }
         $proc_query = substr($proc_query, 0, -1);
         $proc_query .= '
         WHERE `id` = ' . $update_id . ';
         END;';
     }
     if (!empty($proc_query)) {
         PdoWrap::execute($proc_query);
     }
 }