Brimbox Logo Brimbox Version 2.3.4 Released

Rebuild Data Table>>

This following code is used when a SELECT INTO is used to copy the Brimbox data_table into a work table. For instance:

SELECT * INTO data_table_bak FROM data_table

This will create a table without constraints or triggers to work on. Once you have worked on the work table (data_table_bak), you will want to insert it into a fresh data_table with constraints and triggers which you can install from dbinstall.php. Just delete the table original data_table (carefully) and run the install script. The dbinstall.php script will install only Brimbox tables which do not exist.

This following set of SQL commands will handle both the triggers and primary key sequence (id) when you insert the work table into a fully constrained data_table preserving all original information:

DROP SEQUENCE data_table_id_seq CASCADE;

ALTER TABLE data_table DISABLE TRIGGER ts1_modify_date;
ALTER TABLE data_table DISABLE TRIGGER ts2_create_date;

TRUNCATE TABLE data_table;

INSERT INTO data_table (id, row_type, key1, key2, c01, c02, c03, c04, c05, c06, c07,
c08, c09, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, archive, secure, create_date, modify_date, owner_name, updater_name, list_string)
SELECT id, row_type, key1, key2, c01, c02, c03, c04, c05, c06, c07, 
c08, c09, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, 
c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, 
c34, c35, c36,c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, archive, secure, create_date, modify_date, owner_name, updater_name, list_string
FROM data_table_bak;

CREATE SEQUENCE data_table_id_seq;

ALTER TABLE data_table 
ALTER COLUMN id 
SET DEFAULT NEXTVAL('data_table_id_seq');

ALTER SEQUENCE  data_table_id_seq OWNED BY data_table.id

SELECT setval('data_table_id_seq', (SELECT max(id) + 1 FROM data_table));

ALTER TABLE data_table ENABLE TRIGGER ts1_modify_date;
ALTER TABLE data_table ENABLE TRIGGER ts2_create_date;

Note: Full text search indexes will need to be rebuilt Using “Rebuild Indexes” either on the “Set Column Names” tab or the “Backup and Restore” tab.
 

Updated: 2016-09-05

Documents

Definitions