Woozle: extracted from parent page

2017-04-08T14:32:40Z

extracted from parent page

New page

It proved impossible to simply alter the `contacts` table in place because of invalid DATETIME VALUES. Instead I had to rename the old table, create the new one (modified from the official spec), then copy the old data into the new table.
{|
|
| valign=top |
==create table==
<mysql>CREATE TABLE IF NOT EXISTS `contact` (
`id` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL DEFAULT 0,
`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`self` tinyint(1) NOT NULL DEFAULT 0,
`remote_self` tinyint(1) NOT NULL DEFAULT 0,
`rel` tinyint(1) NOT NULL DEFAULT 0,
`duplex` tinyint(1) NOT NULL DEFAULT 0,
`network` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`nick` varchar(255) NOT NULL DEFAULT '',
`location` varchar(255) NOT NULL DEFAULT '',
`about` text,
`keywords` text,
`gender` varchar(32) NOT NULL DEFAULT '',
`xmpp` varchar(255) NOT NULL DEFAULT '',
`attag` varchar(255) NOT NULL DEFAULT '',
`avatar` varchar(255) NOT NULL DEFAULT '',
`photo` text,
`thumb` text,
`micro` text,
`site-pubkey` text,
`issued-id` varchar(255) NOT NULL DEFAULT '',
`dfrn-id` varchar(255) NOT NULL DEFAULT '',
`url` varchar(255) NOT NULL DEFAULT '',
`nurl` varchar(255) NOT NULL DEFAULT '',
`addr` varchar(255) NOT NULL DEFAULT '',
`alias` varchar(255) NOT NULL DEFAULT '',
`pubkey` text,
`prvkey` text,
`batch` varchar(255) NOT NULL DEFAULT '',
`request` text,
`notify` text,
`poll` text,
`confirm` text,
`poco` text,
`aes_allow` tinyint(1) NOT NULL DEFAULT 0,
`ret-aes` tinyint(1) NOT NULL DEFAULT 0,
`usehub` tinyint(1) NOT NULL DEFAULT 0,
`subhub` tinyint(1) NOT NULL DEFAULT 0,
`hub-verify` varchar(255) NOT NULL DEFAULT '',
`last-update` datetime DEFAULT NULL,
`success_update` datetime DEFAULT NULL,
`failure_update` datetime DEFAULT NULL,
`name-date` datetime DEFAULT NULL,
`uri-date` datetime DEFAULT NULL,
`avatar-date` datetime DEFAULT NULL,
`term-date` datetime DEFAULT NULL,
`last-item` datetime DEFAULT NULL,
`priority` tinyint(3) NOT NULL DEFAULT 0,
`blocked` tinyint(1) NOT NULL DEFAULT 1,
`readonly` tinyint(1) NOT NULL DEFAULT 0,
`writable` tinyint(1) NOT NULL DEFAULT 0,
`forum` tinyint(1) NOT NULL DEFAULT 0,
`prv` tinyint(1) NOT NULL DEFAULT 0,
`contact-type` int(11) unsigned NOT NULL DEFAULT 0,
`hidden` tinyint(1) NOT NULL DEFAULT 0,
`archive` tinyint(1) NOT NULL DEFAULT 0,
`pending` tinyint(1) NOT NULL DEFAULT 1,
`rating` tinyint(1) NOT NULL DEFAULT 0,
`reason` text,
`closeness` tinyint(2) NOT NULL DEFAULT 99,
`info` mediumtext,
`profile-id` int(11) NOT NULL DEFAULT 0,
`bdyear` varchar(4) NOT NULL DEFAULT '',
`bd` date DEFAULT NULL,
`notify_new_posts` tinyint(1) NOT NULL DEFAULT 0,
`fetch_further_information` tinyint(1) NOT NULL DEFAULT 0,
`ffi_keyword_blacklist` text,
PRIMARY KEY(`id`),
INDEX `uid_name` (`uid`,`name`),
INDEX `self_uid` (`self`,`uid`),
INDEX `alias_uid` (`alias`(32),`uid`),
INDEX `pending_uid` (`pending`,`uid`),
INDEX `blocked_uid` (`blocked`,`uid`),
INDEX `uid_rel_network_poll` (`uid`,`rel`,`network`,`poll`(64),`archive`),
INDEX `uid_network_batch` (`uid`,`network`,`batch`(64)),
INDEX `addr_uid` (`addr`(32),`uid`),
INDEX `nurl_uid` (`nurl`(32),`uid`),
INDEX `nick_uid` (`nick`(32),`uid`),
INDEX `dfrn-id` (`dfrn-id`),
INDEX `issued-id` (`issued-id`)
) DEFAULT CHARSET=utf8mb4;</mysql>
All but one of the DATETIME fields are now DEFAULT NULL. I decided `create` should be DEFAULT CURRENT_TIMESTAMP, because how far wrong can you go with that in a creation-timestamp field? I guess we'll find out...
|
==copy data==
<mysql>INSERT INTO `contact`(
`id`,
`uid`,
`created`,
`self`,
`remote_self`,
`rel`,
`duplex`,
`network`,
`name`,
`nick`,
`attag`,
`photo`,
`thumb`,
`micro`,
`site-pubkey`,
`issued-id`,
`dfrn-id`,
`url`,
`nurl`,
`addr`,
`alias`,
`pubkey`,
`prvkey`,
`batch`,
`request`,
`notify`,
`poll`,
`confirm`,
`poco`,
`aes_allow`,
`ret-aes`,
`usehub`,
`subhub`,
`hub-verify`,
`last-update`,
`success_update`,
`name-date`,
`uri-date`,
`avatar-date`,
`term-date`,
`priority`,
`blocked`,
`readonly`,
`writable`,
`forum`,
`prv`,
`hidden`,
`archive`,
`pending`,
`rating`,
`reason`,
`closeness`,
`info`,
`profile-id`,
`bdyear`,
`bd`,
`notify_new_posts`,
`fetch_further_information`,
`ffi_keyword_blacklist`
)
SELECT
`id`,
`uid`,
`created`,
`self`,
`remote_self`,
`rel`,
`duplex`,
`network`,
`name`,
`nick`,
`attag`,
`photo`,
`thumb`,
`micro`,
`site-pubkey`,
`issued-id`,
`dfrn-id`,
`url`,
`nurl`,
`addr`,
`alias`,
`pubkey`,
`prvkey`,
`batch`,
`request`,
`notify`,
`poll`,
`confirm`,
`poco`,
`aes_allow`,
`ret-aes`,
`usehub`,
`subhub`,
`hub-verify`,
IF(UNIX_TIMESTAMP(`last-update`)=0,NULL,`last-update`),
IF(UNIX_TIMESTAMP(`success_update`)=0,NULL,`success_update`),
IF(UNIX_TIMESTAMP(`name-date`)=0,NULL,`name-date`),
IF(UNIX_TIMESTAMP(`uri-date`)=0,NULL,`uri-date`),
IF(UNIX_TIMESTAMP(`avatar-date`)=0,NULL,`avatar-date`),
IF(UNIX_TIMESTAMP(`term-date`)=0,NULL,`term-date`),
`priority`,
`blocked`,
`readonly`,
`writable`,
`forum`,
`prv`,
`hidden`,
`archive`,
`pending`,
`rating`,
`reason`,
`closeness`,
`info`,
`profile-id`,
`bdyear`,
IF(UNIX_TIMESTAMP(`bd`)=0,NULL,`bd`),
`notify_new_posts`,
`fetch_further_information`,
`ffi_keyword_blacklist`
FROM `icms-fka`.`contact-original`;</mysql>
The "UNIX_TIMESTAMP()" function was necessary because I would get errors even when comparing the field to '0000-00-00 00:00:00'. If there were any illegal values other than that, I couldn't find them even by doing a SELECT which explicitly filtered for them (SELECT UNIX_TIMESTAMP(`last-update`), `last-update` FROM `contact-original` WHERE UNIX_TIMESTAMP(`last-update`)=0 AND `last-update` != '0000-00-00 00:00:00').
|}

User:Woozle/Friendica/2017 upgrade/contact - Revision history

Woozle: extracted from parent page