User:Woozle/Friendica/2017 upgrade/contact

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search

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

Retrieved from "https://htyp.org/mw/index.php?title=User:Woozle/Friendica/2017_upgrade/contact&oldid=22477"