pi-hole/advanced/Templates/gravity_copy.sql

44 lines
1.4 KiB
MySQL
Raw Permalink Normal View History

Improve gravity performance (#3100) * Gravity performance improvements. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not move downloaded lists into migration_backup directory. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not (strictly) sort domains. Random-leaf access is faster than always-last-leaf access (on average). Signed-off-by: DL6ER <dl6er@dl6er.de> * Append instead of overwrite gravity_new collection list. Signed-off-by: DL6ER <dl6er@dl6er.de> * Rename table gravity_new to gravity_temp to clarify that this is only an intermediate table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Add timers for each of the calls to compute intense parts. They are to be removed before this finally hits the release/v5.0 branch. Signed-off-by: DL6ER <dl6er@dl6er.de> * Fix legacy list files import. It currently doesn't work when the gravity database has already been updated to using the single domainlist table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Simplify database_table_from_file(), remove all to this function for gravity lost downloads. Signed-off-by: DL6ER <dl6er@dl6er.de> * Update gravity.db.sql to version 10 to have newle created databases already reflect the most recent state. Signed-off-by: DL6ER <dl6er@dl6er.de> * Create second gravity database and swap them on success. This has a number of advantages such as instantaneous gravity updates (as seen from FTL) and always available gravity blocking. Furthermore, this saves disk space as the old database is removed on completion. * Add timing output for the database swapping SQLite3 call. Signed-off-by: DL6ER <dl6er@dl6er.de> * Explicitly generate index as a separate process. Signed-off-by: DL6ER <dl6er@dl6er.de> * Remove time measurements. Signed-off-by: DL6ER <dl6er@dl6er.de>
2020-01-24 17:39:13 +00:00
.timeout 30000
ATTACH DATABASE '/etc/pihole/gravity.db' AS OLD;
BEGIN TRANSACTION;
DROP TRIGGER tr_domainlist_add;
DROP TRIGGER tr_client_add;
DROP TRIGGER tr_adlist_add;
Improve gravity performance (#3100) * Gravity performance improvements. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not move downloaded lists into migration_backup directory. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not (strictly) sort domains. Random-leaf access is faster than always-last-leaf access (on average). Signed-off-by: DL6ER <dl6er@dl6er.de> * Append instead of overwrite gravity_new collection list. Signed-off-by: DL6ER <dl6er@dl6er.de> * Rename table gravity_new to gravity_temp to clarify that this is only an intermediate table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Add timers for each of the calls to compute intense parts. They are to be removed before this finally hits the release/v5.0 branch. Signed-off-by: DL6ER <dl6er@dl6er.de> * Fix legacy list files import. It currently doesn't work when the gravity database has already been updated to using the single domainlist table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Simplify database_table_from_file(), remove all to this function for gravity lost downloads. Signed-off-by: DL6ER <dl6er@dl6er.de> * Update gravity.db.sql to version 10 to have newle created databases already reflect the most recent state. Signed-off-by: DL6ER <dl6er@dl6er.de> * Create second gravity database and swap them on success. This has a number of advantages such as instantaneous gravity updates (as seen from FTL) and always available gravity blocking. Furthermore, this saves disk space as the old database is removed on completion. * Add timing output for the database swapping SQLite3 call. Signed-off-by: DL6ER <dl6er@dl6er.de> * Explicitly generate index as a separate process. Signed-off-by: DL6ER <dl6er@dl6er.de> * Remove time measurements. Signed-off-by: DL6ER <dl6er@dl6er.de>
2020-01-24 17:39:13 +00:00
INSERT OR REPLACE INTO "group" SELECT * FROM OLD."group";
INSERT OR REPLACE INTO domain_audit SELECT * FROM OLD.domain_audit;
INSERT OR REPLACE INTO domainlist SELECT * FROM OLD.domainlist;
DELETE FROM OLD.domainlist_by_group WHERE domainlist_id NOT IN (SELECT id FROM OLD.domainlist);
Improve gravity performance (#3100) * Gravity performance improvements. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not move downloaded lists into migration_backup directory. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not (strictly) sort domains. Random-leaf access is faster than always-last-leaf access (on average). Signed-off-by: DL6ER <dl6er@dl6er.de> * Append instead of overwrite gravity_new collection list. Signed-off-by: DL6ER <dl6er@dl6er.de> * Rename table gravity_new to gravity_temp to clarify that this is only an intermediate table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Add timers for each of the calls to compute intense parts. They are to be removed before this finally hits the release/v5.0 branch. Signed-off-by: DL6ER <dl6er@dl6er.de> * Fix legacy list files import. It currently doesn't work when the gravity database has already been updated to using the single domainlist table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Simplify database_table_from_file(), remove all to this function for gravity lost downloads. Signed-off-by: DL6ER <dl6er@dl6er.de> * Update gravity.db.sql to version 10 to have newle created databases already reflect the most recent state. Signed-off-by: DL6ER <dl6er@dl6er.de> * Create second gravity database and swap them on success. This has a number of advantages such as instantaneous gravity updates (as seen from FTL) and always available gravity blocking. Furthermore, this saves disk space as the old database is removed on completion. * Add timing output for the database swapping SQLite3 call. Signed-off-by: DL6ER <dl6er@dl6er.de> * Explicitly generate index as a separate process. Signed-off-by: DL6ER <dl6er@dl6er.de> * Remove time measurements. Signed-off-by: DL6ER <dl6er@dl6er.de>
2020-01-24 17:39:13 +00:00
INSERT OR REPLACE INTO domainlist_by_group SELECT * FROM OLD.domainlist_by_group;
INSERT OR REPLACE INTO adlist SELECT * FROM OLD.adlist;
DELETE FROM OLD.adlist_by_group WHERE adlist_id NOT IN (SELECT id FROM OLD.adlist);
Improve gravity performance (#3100) * Gravity performance improvements. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not move downloaded lists into migration_backup directory. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not (strictly) sort domains. Random-leaf access is faster than always-last-leaf access (on average). Signed-off-by: DL6ER <dl6er@dl6er.de> * Append instead of overwrite gravity_new collection list. Signed-off-by: DL6ER <dl6er@dl6er.de> * Rename table gravity_new to gravity_temp to clarify that this is only an intermediate table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Add timers for each of the calls to compute intense parts. They are to be removed before this finally hits the release/v5.0 branch. Signed-off-by: DL6ER <dl6er@dl6er.de> * Fix legacy list files import. It currently doesn't work when the gravity database has already been updated to using the single domainlist table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Simplify database_table_from_file(), remove all to this function for gravity lost downloads. Signed-off-by: DL6ER <dl6er@dl6er.de> * Update gravity.db.sql to version 10 to have newle created databases already reflect the most recent state. Signed-off-by: DL6ER <dl6er@dl6er.de> * Create second gravity database and swap them on success. This has a number of advantages such as instantaneous gravity updates (as seen from FTL) and always available gravity blocking. Furthermore, this saves disk space as the old database is removed on completion. * Add timing output for the database swapping SQLite3 call. Signed-off-by: DL6ER <dl6er@dl6er.de> * Explicitly generate index as a separate process. Signed-off-by: DL6ER <dl6er@dl6er.de> * Remove time measurements. Signed-off-by: DL6ER <dl6er@dl6er.de>
2020-01-24 17:39:13 +00:00
INSERT OR REPLACE INTO adlist_by_group SELECT * FROM OLD.adlist_by_group;
INSERT OR REPLACE INTO client SELECT * FROM OLD.client;
DELETE FROM OLD.client_by_group WHERE client_id NOT IN (SELECT id FROM OLD.client);
Improve gravity performance (#3100) * Gravity performance improvements. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not move downloaded lists into migration_backup directory. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not (strictly) sort domains. Random-leaf access is faster than always-last-leaf access (on average). Signed-off-by: DL6ER <dl6er@dl6er.de> * Append instead of overwrite gravity_new collection list. Signed-off-by: DL6ER <dl6er@dl6er.de> * Rename table gravity_new to gravity_temp to clarify that this is only an intermediate table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Add timers for each of the calls to compute intense parts. They are to be removed before this finally hits the release/v5.0 branch. Signed-off-by: DL6ER <dl6er@dl6er.de> * Fix legacy list files import. It currently doesn't work when the gravity database has already been updated to using the single domainlist table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Simplify database_table_from_file(), remove all to this function for gravity lost downloads. Signed-off-by: DL6ER <dl6er@dl6er.de> * Update gravity.db.sql to version 10 to have newle created databases already reflect the most recent state. Signed-off-by: DL6ER <dl6er@dl6er.de> * Create second gravity database and swap them on success. This has a number of advantages such as instantaneous gravity updates (as seen from FTL) and always available gravity blocking. Furthermore, this saves disk space as the old database is removed on completion. * Add timing output for the database swapping SQLite3 call. Signed-off-by: DL6ER <dl6er@dl6er.de> * Explicitly generate index as a separate process. Signed-off-by: DL6ER <dl6er@dl6er.de> * Remove time measurements. Signed-off-by: DL6ER <dl6er@dl6er.de>
2020-01-24 17:39:13 +00:00
INSERT OR REPLACE INTO client_by_group SELECT * FROM OLD.client_by_group;
CREATE TRIGGER tr_domainlist_add AFTER INSERT ON domainlist
BEGIN
INSERT INTO domainlist_by_group (domainlist_id, group_id) VALUES (NEW.id, 0);
END;
CREATE TRIGGER tr_client_add AFTER INSERT ON client
BEGIN
INSERT INTO client_by_group (client_id, group_id) VALUES (NEW.id, 0);
END;
CREATE TRIGGER tr_adlist_add AFTER INSERT ON adlist
BEGIN
INSERT INTO adlist_by_group (adlist_id, group_id) VALUES (NEW.id, 0);
END;
Improve gravity performance (#3100) * Gravity performance improvements. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not move downloaded lists into migration_backup directory. Signed-off-by: DL6ER <dl6er@dl6er.de> * Do not (strictly) sort domains. Random-leaf access is faster than always-last-leaf access (on average). Signed-off-by: DL6ER <dl6er@dl6er.de> * Append instead of overwrite gravity_new collection list. Signed-off-by: DL6ER <dl6er@dl6er.de> * Rename table gravity_new to gravity_temp to clarify that this is only an intermediate table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Add timers for each of the calls to compute intense parts. They are to be removed before this finally hits the release/v5.0 branch. Signed-off-by: DL6ER <dl6er@dl6er.de> * Fix legacy list files import. It currently doesn't work when the gravity database has already been updated to using the single domainlist table. Signed-off-by: DL6ER <dl6er@dl6er.de> * Simplify database_table_from_file(), remove all to this function for gravity lost downloads. Signed-off-by: DL6ER <dl6er@dl6er.de> * Update gravity.db.sql to version 10 to have newle created databases already reflect the most recent state. Signed-off-by: DL6ER <dl6er@dl6er.de> * Create second gravity database and swap them on success. This has a number of advantages such as instantaneous gravity updates (as seen from FTL) and always available gravity blocking. Furthermore, this saves disk space as the old database is removed on completion. * Add timing output for the database swapping SQLite3 call. Signed-off-by: DL6ER <dl6er@dl6er.de> * Explicitly generate index as a separate process. Signed-off-by: DL6ER <dl6er@dl6er.de> * Remove time measurements. Signed-off-by: DL6ER <dl6er@dl6er.de>
2020-01-24 17:39:13 +00:00
COMMIT;