|
|
| Line 1: |
Line 1: |
| | 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. | | 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== | + | ==Table Modifications== |
| − | 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.
| + | * [[/contact]] - this is the first one I did, so notes are more explanatory |
| − | {|
| + | * [[/item]] |
| − | |
| |
| − | | 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').
| |
| − | |}
| |