/** * Генератор 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); } }