User:Woozle/Friendica/2017 upgrade

This isn't a complete description of everything I had to do in order to get the latest Friendica working with my existing data, but it shows how I solved some of the more thorny problems.

modify Contacts

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.

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').