Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
<?php
$tables = array(
'mytable' => array(
'cols' => array(
'description' => 'LONGTEXT NULL DEFAULT NULL',
),
'index' => array(
'fulltext_search' => array('description'),
)
),
);
function quote($str) {
return "`$str`";
}
foreach ($tables as $table => $settings) {
$last_col = array_keys($settings['cols'])[count($settings['cols']) - 1];
ob_start(); ?>
-- Create table with only the needed fields
DROP TABLE IF EXISTS <?php echo $table ?>_search;
CREATE TABLE `<?php echo $table ?>_search` (
`id` int(11) NOT NULL,
<?php foreach ($settings['cols'] as $col => $def): ?>
<?php echo "`$col` $def,"; ?>
<?php endforeach ?>
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- First populate without indices for fast inserts
INSERT INTO <?php echo $table ?>_search (
`id`,
<?php echo implode(",\n ", array_map('quote', array_keys($settings['cols']))) ?>
) SELECT
source.`id`,
source.<?php echo implode(",\n source.", array_map('quote', array_keys($settings['cols']))) ?>
FROM <?php echo $table ?> source
;
<?php foreach ($settings['index'] as $index => $cols): ?>
CREATE FULLTEXT INDEX `<?php echo $index ?>` ON <?php echo $table ?>_search (<?php echo implode(',',array_map('quote', $cols)) ?>);
<?php endforeach ?>
-- Percona cluster does not support replication of inline NEW.*
-- So we must declare variables
DELIMITER //
DROP TRIGGER IF EXISTS `<?php echo $table ?>_search_insert`//
CREATE TRIGGER `<?php echo $table ?>_search_insert` AFTER INSERT ON `<?php echo $table ?>`
FOR EACH ROW BEGIN
DECLARE id_val INT;
<?php foreach ($settings['cols'] as $col => $def): ?>
DECLARE <?php echo "{$col}_val ".preg_replace('/ NULL.*$/', '', $def).";"; ?>
<?php endforeach ?>
SET id_val = NEW.`id`;
<?php foreach ($settings['cols'] as $col => $def): ?>
SET <?php echo "{$col}_val = NEW.`{$col}`;"; ?>
<?php endforeach ?>
INSERT INTO <?php echo $table ?>_search (
`id`,
<?php echo implode(",\n ", array_map('quote', array_keys($settings['cols']))) ?>
) VALUES (
id_val,
<?php foreach ($settings['cols'] as $col => $def): ?>
<?php echo "{$col}_val"; if ($col != $last_col) echo ','; ?>
<?php endforeach ?>
);
END
//
DELIMITER ;
DELIMITER //
DROP TRIGGER IF EXISTS `<?php echo $table ?>_search_update`//
CREATE TRIGGER `<?php echo $table ?>_search_update` AFTER UPDATE ON `<?php echo $table ?>`
FOR EACH ROW BEGIN
DECLARE id_val INT;
<?php foreach ($settings['cols'] as $col => $def): ?>
DECLARE <?php echo "{$col}_val ".preg_replace('/ NULL.*$/', '', $def).";"; ?>
<?php endforeach ?>
SET id_val = NEW.`id`;
<?php foreach ($settings['cols'] as $col => $def): ?>
SET <?php echo "{$col}_val = NEW.`{$col}`;"; ?>
<?php endforeach ?>
UPDATE <?php echo $table ?>_search SET
<?php foreach ($settings['cols'] as $col => $def): ?>
<?php echo "`$col` = {$col}_val"; if ($col != $last_col) echo ','; ?>
<?php endforeach ?>
WHERE `id` = id_val;
END
//
DELIMITER ;
DELIMITER //
DROP TRIGGER IF EXISTS `<?php echo $table ?>_search_delete`//
CREATE TRIGGER `<?php echo $table ?>_search_delete` BEFORE DELETE ON `<?php echo $table ?>`
FOR EACH ROW BEGIN
DECLARE id_val INT;
SET id_val = OLD.`id`;
DELETE FROM <?php echo $table ?>_search WHERE `id` = id_val;
END
//
DELIMITER ;
-- Insert missing data that could have be inserted in original table since the first insert
INSERT INTO <?php echo $table ?>_search (
`id`,
<?php echo implode(",\n ", array_map('quote', array_keys($settings['cols']))) ?>
) SELECT
source.`id`,
source.<?php echo implode(",\n source.", array_map('quote', array_keys($settings['cols']))) ?>
FROM <?php echo $table ?> source
ON DUPLICATE KEY UPDATE
<?php foreach ($settings['cols'] as $col => $def): ?>
<?php echo "`$col` = source.`$col`"; if ($col != $last_col) echo ','; ?>
<?php endforeach ?>
;
<?php
$sql = ob_get_contents();
ob_end_clean();
// echo $sql;
file_put_contents(__DIR__ . "/$table.sql", $sql);
}
-- Create table with only the needed fields
DROP TABLE IF EXISTS mytable_search;
CREATE TABLE `mytable_search` (
`id` int(11) NOT NULL,
`description` LONGTEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- First populate without indices for fast inserts
INSERT INTO mytable_search (
`id`,
`description`
) SELECT
source.`id`,
source.`description`
FROM mytable source
;
CREATE FULLTEXT INDEX `fulltext_search` ON mytable_search (`description`);
-- Percona cluster does not support replication of inline NEW.*
-- So we must declare variables
DELIMITER //
DROP TRIGGER IF EXISTS `mytable_search_insert`//
CREATE TRIGGER `mytable_search_insert` AFTER INSERT ON `mytable`
FOR EACH ROW BEGIN
DECLARE id_val INT;
DECLARE description_val LONGTEXT;
SET id_val = NEW.`id`;
SET description_val = NEW.`description`;
INSERT INTO mytable_search (
`id`,
`description`
) VALUES (
id_val,
description_val
);
END
//
DELIMITER ;
DELIMITER //
DROP TRIGGER IF EXISTS `mytable_search_update`//
CREATE TRIGGER `mytable_search_update` AFTER UPDATE ON `mytable`
FOR EACH ROW BEGIN
DECLARE id_val INT;
DECLARE description_val LONGTEXT;
SET id_val = NEW.`id`;
SET description_val = NEW.`description`;
UPDATE mytable_search SET
`description` = description_val
WHERE `id` = id_val;
END
//
DELIMITER ;
DELIMITER //
DROP TRIGGER IF EXISTS `mytable_search_delete`//
CREATE TRIGGER `mytable_search_delete` BEFORE DELETE ON `mytable`
FOR EACH ROW BEGIN
DECLARE id_val INT;
SET id_val = OLD.`id`;
DELETE FROM mytable_search WHERE `id` = id_val;
END
//
DELIMITER ;
-- Insert missing data that could have be inserted in original table since the first insert
INSERT INTO mytable_search (
`id`,
`description`
) SELECT
source.`id`,
source.`description`
FROM mytable source
ON DUPLICATE KEY UPDATE
`description` = source.`description`
;
-- Example of the new query
SELECT t.*
FROM `mytable` t
JOIN `mytable_search` s ON (s.id = t.id)
WHERE MATCH(s.description) AGAINST ('mysearch');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment