/**
* Alter the database in case any updates are present.
*/
function checkDBMigration($DBH)
{
/**
* Database option added in 1.3.0
* - Allows user to specificly add notifications for users
*/
$select = $DBH->prepare('SELECT column_name
FROM information_schema.columns
WHERE table_name=\'accounts\' and column_name=\'notify_actions\';');
$select->execute();
if ($select->rowCount() == 0) {
$alter = $DBH->prepare('ALTER TABLE accounts
ADD COLUMN notify_actions boolean NOT NULL DEFAULT false;');
$alter->execute();
if (!$alter) {
echo 'The following error occured when trying to upgrade DB:';
print_r($DBH->errorInfo());
exit;
}
}
/**
* Database option added in 1.3.6
* - Indexes for improved performance (up to 4x as fast), noteable on slow machines
* - Custom version table for better DB checking
*/
$select = $DBH->prepare('SELECT 1
FROM information_schema.tables
WHERE table_schema = \'public\'
AND table_name = \'whatsspy_config\'');
$select->execute();
if ($select->rowCount() == 0) {
$sql_update = '-- 1.3.6 Index updates.
CREATE INDEX index_account_id
ON accounts (id ASC NULLS LAST);
CREATE INDEX index_tracker_history_end
ON tracker_history ("end" ASC NULLS FIRST);
CREATE INDEX index_tracker_history_start
ON tracker_history ("start" DESC);
CREATE INDEX index_tracker_history_start_end_not_null
ON tracker_history ("start" DESC) WHERE "end" IS NOT NULL;
CREATE INDEX index_status_history_end_status
ON status_history (status ASC NULLS LAST, "end" ASC NULLS FIRST);
CREATE INDEX index_status_history_number_end
ON status_history ("number" ASC NULLS LAST, "end" ASC NULLS FIRST);
CREATE INDEX index_profilepicture_history_number
ON profilepicture_history ("number" ASC NULLS LAST);
CREATE INDEX index_statusmessage_number
ON statusmessage_history ("number" ASC NULLS LAST);
CREATE INDEX index_accounts_active_true_verified_true
ON accounts ("id") WHERE active = true AND verified = true;
CREATE INDEX index_status_history_number_end_is_null
ON status_history ("number") WHERE "end" = null;
CREATE INDEX index_status_history_number_status_true_end_not_null
ON status_history ("number") WHERE status = true AND "end" IS NOT NULL;
CREATE INDEX index_status_history_number_start_status_true_end_not_null
ON status_history ("number", "start") WHERE status = true AND "end" IS NOT NULL;
CREATE INDEX index_status_history_status_true
ON status_history ("status") WHERE status = true;
CREATE INDEX index_status_history_number_status_true_start_desc
ON status_history ("number", "start" DESC) WHERE status = true;
CREATE INDEX index_status_history_sid_start_end_status_true
ON status_history ("sid" DESC, "start" DESC, "end" DESC) WHERE status = true;
CREATE INDEX index_status_history_number_start_asc
ON status_history ("number", "start" ASC);
CREATE INDEX index_profilepicture_history_number_changed_at_desc
ON profilepicture_history ("number", "changed_at" DESC);
CREATE INDEX index_statusmessage_history_number_changed_at_desc
ON statusmessage_history ("number", "changed_at" DESC);
CREATE INDEX index_lastseen_privacy_history_number_changed_at_desc
ON lastseen_privacy_history ("number", "changed_at" DESC);
CREATE INDEX index_profilepic_privacy_history_number_changed_at_desc
ON profilepic_privacy_history ("number", "changed_at" DESC);
CREATE INDEX index_statusmessage_privacy_history_number_changed_at_desc
ON statusmessage_privacy_history ("number", "changed_at" DESC);
CREATE TABLE whatsspy_config
(
db_version integer
)
WITH (
OIDS = FALSE
);
INSERT INTO whatsspy_config (db_version)
VALUES (3);';
$upgrade = $DBH->exec($sql_update);
if ($DBH->errorCode() != '00000') {
echo 'The following error occured when trying to upgrade DB:';
print_r($DBH->errorInfo());
exit;
}
}
/**
* Database migration added in 1.4.0
* - Now update according to version number which saves countless queries.
*/
$select = $DBH->prepare('SELECT db_version
FROM whatsspy_config');
$select->execute();
$row = $select->fetch();
$version = $row['db_version'];
if ($version == 3) {
/**
* Database option added in 1.4.0
* - Tracker history now gives a reason why it closed.
* - Notifications can be specificly chosen.
*/
$version = doMigration($DBH, '-- Add tracker reason
ALTER TABLE tracker_history
ADD COLUMN reason character varying(255);
-- Add notification options
ALTER TABLE accounts RENAME notify_actions TO notify_status;
ALTER TABLE accounts
ADD COLUMN notify_statusmsg boolean NOT NULL DEFAULT false;
ALTER TABLE accounts
ADD COLUMN notify_profilepic boolean NOT NULL DEFAULT false;
CREATE INDEX index_account_notify_status
ON accounts (notify_status ASC NULLS LAST);
CREATE INDEX index_account_notify_statusmsg
ON accounts (notify_statusmsg ASC NULLS LAST);
CREATE INDEX index_account_notify_profilepic
ON accounts (notify_profilepic ASC NULLS LAST);
-- Add groups
CREATE TABLE groups
(
gid serial NOT NULL,
name character varying(255) NOT NULL,
CONSTRAINT pk_groups_gid PRIMARY KEY (gid)
)
WITH (
OIDS=FALSE
);
ALTER TABLE accounts
ADD COLUMN group_id integer;
ALTER TABLE accounts
ADD CONSTRAINT fk_group_id FOREIGN KEY (group_id) REFERENCES groups (gid) ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE INDEX index_account_group_id
ON accounts (group_id ASC NULLS LAST);
CREATE INDEX index_account_id_group_id
ON accounts (id ASC NULLS LAST, group_id ASC NULLS LAST);', 4);
}
if ($version == 4) {
/**
* Database option added in 1.4.1
* - Added multiple groups
*/
$version = doMigration($DBH, 'CREATE TABLE accounts_to_groups
(
"number" character(50) NOT NULL,
gid integer NOT NULL,
CONSTRAINT pk_account_to_group PRIMARY KEY (number, gid)
)
WITH (
OIDS=FALSE
);
INSERT INTO accounts_to_groups (number, gid) (SELECT id, group_id FROM accounts WHERE group_id IS NOT NULL);
ALTER TABLE accounts
DROP COLUMN group_id;
ALTER TABLE accounts
ADD COLUMN notify_timeline boolean NOT NULL DEFAULT false;', 5);
}
if ($version == 5) {
/**
* Database option added in 1.4.4
* - Added notify for privacy settings
*/
$version = doMigration($DBH, 'ALTER TABLE accounts
ADD COLUMN notify_privacy boolean NOT NULL DEFAULT false;', 6);
}
if ($version == 6) {
/**
* Database option added in 1.5.0
* - Generate read-only tokens for users/groups.
*/
$version = doMigration($DBH, 'ALTER TABLE accounts
ADD COLUMN read_only_token character varying(255);
ALTER TABLE groups
ADD COLUMN read_only_token character varying(255);
ALTER TABLE whatsspy_config
ADD COLUMN last_login_attempt timestamp with time zone;', 7);
}
if ($version == 7) {
/**
* Database option added in 1.5.1
* - Performance options
*/
$version = doMigration($DBH, 'ALTER TABLE whatsspy_config
ADD COLUMN account_show_timeline_length integer NOT NULL DEFAULT 14;
ALTER TABLE whatsspy_config
ADD COLUMN account_show_timeline_tracker boolean NOT NULL DEFAULT true;', 8);
}
if ($version == 8) {
/**
* Database option added in 1.5.7
* - Notification sound
*/
$version = doMigration($DBH, 'ALTER TABLE accounts
ADD COLUMN notification_sound character varying(255) DEFAULT \'default.mp3\';', 9);
}
}
createDbEntries();
break;
/* Optimize DB calls*/
/* Optimize DB calls*/
case 'optimizeDB':
optimizeDB();
break;
/* Migration calls */
/* Migration calls */
case 'showMigration':
HTML_RSGALLERY::RSGalleryHeader('cpanel', JText::_('Migration options'));
showMigration();
HTML_RSGALLERY::RSGalleryFooter();
break;
case 'doMigration':
doMigration();
break;
case 'test':
test();
break;
default:
HTML_RSGALLERY::RSGalleryHeader('cpanel', JText::_('MAINT_HEADER'));
showMaintenanceCP($option);
HTML_RSGALLERY::RSGalleryFooter();
break;
}
function test()
{
$gid = 1;
$gallery = rsgGalleryManager::_get($gid);
$images = $gallery->items();
