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