Aurora til Drupal 5
Fra Harald Grovens wiki
Steg for importa av fotogalleri Artikler som skaper trøbbel
Bare så vi ikke glemmer det: "Importere statistikk" må oppdateres til også å legge inn i tabellen når det ikke er kommentarer på artikkelen
FROM 2__stories s INNER JOIN 2__comments c ON (s.NewID = c.NewID);
Oppdateres til
FROM 2__stories s LEFT JOIN 2__comments c ON (s.NewID = c.NewID);
Yngve
Her er dokumentasjonen for import av brukerdata, artikler og kommentarer til Drupal.
Følgende 4 tabeller fra Tromsøby's database må være tilgjengelig:
- 2__comments
- 2__stories
- 2__userinfo
- 2__users
- 2__events
- 2__emailcomments
Innhold |
Endringer i Tromsøbybasen
Rett tegnsett i tromsøbybasen
ALTER TABLE `2__annonse` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__blocks` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__commentcodes` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__commentmodes` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__comments` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__commentspeedlimit` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__commentsubmission` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__customspeedlimit` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__dateformats` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__emailcomments` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__eventlocations` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__eventpriority` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__events` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__eventsubmission` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__eventtopics` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__featurecodes` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__image` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__links` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__linksubmission` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__maillist` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__photos` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__photosubmission` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__pollanswers` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__pollquestions` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__pollvoters` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__postmodes` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__score` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__sortcodes` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__statuscodes` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__stories` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__storysubmission` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__submitspeedlimit` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__topics` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__tzcodes` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__usercomment` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__userindex` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__userinfo` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__userprefs` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__users` DEFAULT CHARSET=utf8 ; ALTER TABLE `2__vars` DEFAULT CHARSET=utf8 ; ALTER TABLE `haraldtest` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__access` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__accesslog` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__aggregator_category` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__aggregator_category_feed` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__aggregator_category_item` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__aggregator_feed` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__aggregator_item` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__authmap` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__blocks` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__blocks_roles` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__book` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__boxes` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__buddylist` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__buddylist_buddy_group` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__buddylist_groups` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__buddylist_pending_requests` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__cache` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__cache_filter` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__cache_menu` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__cache_page` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__comments` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_blog` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_book` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_event` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_forum` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_image` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_nodereview` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_page` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_poll` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_review` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_slideshow` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_story` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__content_type_usernode` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__devel_queries` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__devel_times` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__dynamicload_blocks` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__event` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__event_repeat` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__event_repeat_calendar_map` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__event_repeat_nodes` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__file_revisions` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__files` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__files_tmp` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__filter_formats` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__filters` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__flood` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__forum` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__forward_log` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__history` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__image_attach` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__img_assist_map` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__importexportapi_db_put_map` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__importexportapi_db_put_map_alt` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__locales_meta` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__locales_source` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__locales_target` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__menu` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__modr8_log` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_access` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_comment_statistics` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_counter` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_field` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_field_instance` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_group` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_group_fields` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_revisions` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__node_type` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__nodefamily` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__nodereview` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__nodereview_axes` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__nodereview_reviews` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__nodeteaser` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__permission` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__poll` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__poll_choices` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__poll_votes` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__profile_fields` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__profile_values` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__referral` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__relatedlinks` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__relatedlinks_tracker` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__review` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__role` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__search_dataset` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__search_index` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__search_total` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__sequences` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__sessions` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__slideshow` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__subscriptions` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__subscriptions_holding` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__system` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__taxonomy_breadcrumb_term` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__taxonomy_breadcrumb_vocabulary` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__term_data` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__term_hierarchy` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__term_node` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__term_relation` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__term_synonym` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__tinymce_role` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__tinymce_settings` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__url_alias` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__usernode` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__userpoints` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__users` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__users_roles` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__variable` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__view_argument` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__view_exposed_filter` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__view_filter` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__view_sort` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__view_tablefield` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__view_view` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__vocabulary` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__vocabulary_node_types` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__votingapi_cache` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__votingapi_vote` DEFAULT CHARSET=utf8 ; ALTER TABLE `tb2__watchdog` DEFAULT CHARSET=utf8 ;
Denne koden lager en ny kolonne der de 9 siste sifferene i det 17-sifferede IDnummeret legges inn.
-- legg til nytt felt ALTER TABLE `2__stories` ADD `NewID` VARCHAR( 20 ) NOT NULL ; UPDATE 2__stories SET newID = sid; UPDATE 2__stories SET newID = RIGHT(newID, 9); -- legg til nytt felt ALTER TABLE `2__comments` ADD `NewID` VARCHAR( 20 ) NOT NULL ; UPDATE 2__comments SET newID = sid; UPDATE 2__comments SET newID = RIGHT(newID, 9); -- legg til nytt felt for arrangementer ALTER TABLE `2__events` ADD `NewID` VARCHAR( 20 ) NOT NULL ; UPDATE 2__events SET newID = eid; UPDATE 2__events SET newID = RIGHT(newID, 9);
Hvilke kommentarer er kommentarer til meningsmålinger?
-- legge til et felt som sier at en kommentar er en meningsmåling ALTER TABLE `2__comments` ADD `isMeningsmaling` TINYINT( 1 ) NULL ; -- setter alle meningsmålinger til å ha verdien 1 i feltet isMeningsmaling -- slik at de kan skilles ut fra andre UPDATE 2__comments SET isMeningsmaling=0; UPDATE 2__comments SET isMeningsmaling=1 WHERE sid REGEXP '[[:alpha:]]+';
Brukerimport
IGNORE får importen til å skippe dersom det allerede finnes en post med samme primærnøkkel. DATEDIFF konverterer fra datoformat til Unix-formatet til Drupal.
DELETE FROM `tb2__users` WHERE uid > 1 ;
-- importstatement v.2.
-- Importerer til brukertabellen i databasen "drupal" fra databasen "tromsoby"
-- Siden det er en bug med dot-notasjon i phpmyadmin, er det umulig å overføre mellom databasene, kun innen en database
INSERT IGNORE INTO tb2__users
SELECT
U.uid AS uid,
U.username AS name,
U.passwd AS pass,
U.email AS mail,
0 AS mode,
0 AS sort,
0 AS threshold,
NULL AS theme,
U.sig AS signature,
(DATEDIFF(U.created, '1970-01-01 00:00:00')*86400) AS access,
(DATEDIFF(U.lastlogin, '1970-01-01 00:00:00')*86400) AS created,
0 AS login,
1 AS status,
3600 AS timezone,
"" AS language,
CONCAT("files/images/upload/", I.image, ".jpg") AS picture,
U.username AS init,
"" AS data
FROM 2__userinfo I, 2__users U WHERE I.uid=U.uid
;
Ikke noe bilde
Slett bildeinfo fra brukere som ikke har lasta opp noe bilde
-- UPDATE tb2__users SET `picture` = NULL WHERE `picture` = "files/pictures/0.jpg"; UPDATE tb2__users SET `picture` = NULL WHERE `picture` = "files/pictures/.jpg";
Profiler
Definisjonen av tabellen for biografiinfo i profilene
-- Tabellstruktur for tabell `profile_values` -- DROP TABLE tb2__profile_values; CREATE TABLE tb2__profile_values( `fid` int( 10 ) unsigned default '0', `uid` int( 10 ) unsigned default '0', `value` text, KEY `uid` ( `uid` ) , KEY `fid` ( `fid` ) ) ENGINE = MYISAM DEFAULT CHARSET = utf8;
DROP TABLE tb2__profile_fields; CREATE TABLE `tb2__profile_fields` ( `fid` int(11) NOT NULL auto_increment, `title` varchar(255) default NULL, `name` varchar(128) default NULL, `explanation` text, `category` varchar(255) default NULL, `page` varchar(255) default NULL, `type` varchar(128) default NULL, `weight` tinyint(4) NOT NULL default '0', `required` tinyint(4) NOT NULL default '0', `register` tinyint(4) NOT NULL default '0', `visibility` tinyint(4) NOT NULL default '0', `autocomplete` tinyint(4) NOT NULL default '0', `options` text, PRIMARY KEY (`fid`), UNIQUE KEY `name` (`name`), KEY `category` (`category`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ; -- -- Dataark for tabell `tb2__profile_fields` -- INSERT INTO `tb2__profile_fields` VALUES (2, 'Hjemmeside', 'profile_hjemmeside', 'brukernes hjemmesider', 'Personal information', '', 'url', 0, 0, 0, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (14, 'Biografi', 'profile_biografi', 'Noe du vil fortelle om deg selv?', 'Personal information', '', 'textarea', -4, 0, 1, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (4, 'Fødsesdato', 'profile_f_dato', 'Fyll ut fødselsdatoen din', 'Personal information', '', 'date', 0, 0, 1, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (15, 'Kjønn', 'profile_kjoenn', '', 'Personal information', '', 'selection', 4, 0, 1, 2, 0, 'Gutt\r\nJente'); INSERT INTO `tb2__profile_fields` VALUES (6, 'Gatenavn', 'profile_gate', '', 'Personal information', '', 'textfield', 0, 0, 1, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (9, 'Gatenummer', 'profile_gate_nr', '', 'Personal information', '', 'textfield', 0, 0, 1, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (8, 'Fullt navn', 'profile_navn', 'Fyll ut dit virkelige navn om du vil', 'Personal information', '', 'textfield', -5, 0, 1, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (10, 'Postnummer', 'profile_zip', '', 'Personal information', '', 'textfield', 0, 0, 1, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (11, 'Postadresse', 'profile_adresse', '', 'Personal information', '', 'textfield', 0, 0, 1, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (12, 'Motto', 'profile_motto', '', 'Personal information', '', 'textfield', 0, 0, 1, 2, 0, ''); INSERT INTO `tb2__profile_fields` VALUES (1, 'profile_about_Title', 'profile_about', '', 'Personal information', 'brukernes synsing og sjølskryt', 'textarea', 0, 0, 1, 2, 0, '');
Denne setter inn Tromsobyprofilene i profile_values. fid er satt til konstanten 1. Konaktinfoen slås sammen med sjølskrytsbio med tre mellarom mellom. Poster som er blanke i about eller pgpkey ignorereres.
INSERT IGNORE INTO tb2__profile_values SELECT 1 AS fid, uid, CONCAT(pgpkey, "\n\n\n", about) AS value FROM `2__userinfo` WHERE about IS NOT NULL XOR about NOT LIKE "" XOR about NOT LIKE " " OR pgpkey NOT LIKE "" ;
Feltet hjemmeside
-- Feltdefinisjon i drupal
-- importere hjemmeside
INSERT IGNORE INTO tb2__profile_values
SELECT 2 AS fid, uid, homepage AS value
FROM `2__users`
WHERE homepage IS NOT NULL XOR homepage NOT LIKE "" XOR homepage NOT LIKE " " ;
-- importere Postnummer
INSERT IGNORE INTO tb2__profile_values
SELECT 10 as fid, uid, postNr AS value FROM `2__users`
WHERE postNr IS NOT NULL XOR postNr NOT LIKE "" XOR postNr NOT LIKE " " ;
-- importere Poststed
INSERT IGNORE INTO tb2__profile_values
SELECT 11 as fid, uid, postAdr AS value FROM `2__users`
WHERE postAdr IS NOT NULL XOR postAdr NOT LIKE "" XOR postAdr NOT LIKE " ";
-- importere Motto
INSERT IGNORE INTO tb2__profile_values
SELECT 12 as fid, uid, sig AS value FROM `2__users`
WHERE sig IS NOT NULL XOR sig NOT LIKE "" XOR sig NOT LIKE " ";
-- importere Gatenr
INSERT IGNORE INTO tb2__profile_values
SELECT 9 AS fid, uid, adrNr AS value
FROM `2__users`
WHERE adrNr IS NOT NULL XOR adrNr NOT LIKE "" XOR adrNr NOT LIKE " ";
-- importere Gatenavn
INSERT IGNORE INTO tb2__profile_values
SELECT 6 AS fid, uid, adr AS value
FROM `2__users`
WHERE adr IS NOT NULL XOR adr NOT LIKE "" XOR adr NOT LIKE " ";
-- importere fullt navn
INSERT IGNORE INTO tb2__profile_values
SELECT 8 AS fid, uid, fullname AS value
FROM `2__users`
WHERE fullname IS NOT NULL XOR fullname NOT LIKE "" XOR fullname NOT LIKE " " ;
-- importere Biografi
INSERT INTO tb2__profile_values
SELECT 14 AS fid, uid, CONCAT( about, "\n\n\n", pgpkey ) AS value
FROM `2__userinfo` ;
-- Lager streng av bursdagen i drupals forpult sære format
INSERT IGNORE INTO tb2__profile_values
SELECT 4 AS fid, uid,
CONCAT("a:3:{s:3:\"day\";s:" , (CASE WHEN EXTRACT(MONTH FROM born) >= 10
THEN '2' WHEN EXTRACT(MONTH FROM born) < 10
THEN '1' END) , ":\"",
EXTRACT( DAY FROM born ) , "\";s:5:\"month\";s:" , (CASE WHEN EXTRACT(MONTH FROM born) >= 10
THEN '2' WHEN EXTRACT(MONTH FROM born) < 10
THEN '1' END) , ":\"",
EXTRACT( MONTH FROM born ) , "\";s:4:\"year\";s:4:\"",
EXTRACT( YEAR FROM born ) , "\";}"
) AS value
FROM 2__users;
Slette alt tidligere importert innhold
Skript for å slette alt tidligere importert innhold fra drupal-basen
-- Skript for å slette alt importert innhold DELETE FROM `tb2__node` WHERE nid > 1000 ; DELETE FROM `tb2__review` WHERE nid > 1000 ; DELETE FROM `tb2__node_revisions` WHERE nid > 1000 ; DELETE FROM `tb2__nodeteaser` WHERE nid > 1000 ; DELETE FROM `tb2__node_comment_statistics` WHERE nid > 1000 ; DELETE FROM `tb2__history` WHERE nid > 1000 ; -- event DELETE FROM `tb2__event` WHERE nid > 1000 ; DELETE FROM `tb2__content_type_event` WHERE nid > 1000 ;
Importere artikler
Kode for å importere artikler
-- insert statment
-- statuscode 10 i TB er upublisert, 0 er publisert
-- artikler uten terningkast
-- dvs score = 0
INSERT IGNORE INTO tb2__node
SELECT newID AS nid,
newID AS vid, 'story' AS TYPE,
title AS title,
uid AS uid,
(SELECT CASE WHEN statuscode =10
THEN 0
WHEN statuscode =0
THEN 1
ELSE 0
END) AS STATUS,
TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', date) AS created,
TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', date) AS changed,
2 AS COMMENT ,
1 AS promote,
0 AS moderate,
0 AS sticky
FROM 2__stories
WHERE score =0
ORDER BY sid
;
-- artikler med terningkast
-- score <> 0
INSERT IGNORE INTO tb2__node
SELECT
newID AS nid,
newID AS vid,
'review' AS type,
title AS title,
uid AS uid,
(SELECT CASE WHEN statuscode =10
THEN 0
WHEN statuscode =0
THEN 1
ELSE 0
END) AS STATUS,
TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', date) AS created,
TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', date) AS changed,
2 AS comment,
1 AS promote,
0 AS moderate,
0 AS sticky
FROM 2__stories
WHERE statuscode = 0
AND score <> 0
ORDER BY sid
;
INSERT IGNORE INTO tb2__node_revisions
SELECT
newID AS nid,
newID AS vid,
uid AS uid,
title AS title,
(CASE WHEN bodyimage NOT LIKE '0' THEN CONCAT("<img src=\"files/images/archive/", bodyimage, ".jpg\" class=\"bodyimage\">", bodytext) ELSE bodytext END) AS body,
introtext AS teaser,
'' AS log,
TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', date) AS timestamp,
3 AS format
FROM 2__stories
WHERE statuscode = 0
ORDER BY sid
;
-- tb2__review
CREATE TABLE `tb2__review` (
`nid` bigint(20) NOT NULL default '0',
`review_rate` mediumint(9) default NULL,
PRIMARY KEY (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT IGNORE INTO tb2__review
SELECT
newID AS nid,
score AS review_rate
FROM 2__stories
WHERE statuscode = 0
AND score <> 0
ORDER BY sid
;
-- tb2__nodeteaser
CREATE TABLE `tb2__nodeteaser` (
`nid` bigint(20) default NULL,
`teaser` text NOT NULL,
KEY `nid` (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT IGNORE INTO tb2__nodeteaser
SELECT
newID AS nid,
(CASE WHEN introimage NOT LIKE '0' THEN CONCAT("<img src=\"files/images/archive/", introimage, ".jpg\" class=\"introimage\">", introtext) ELSE introtext END) AS teaser
FROM 2__stories
WHERE statuscode = 0
ORDER BY sid
;
INSERT IGNORE INTO tb2__history
SELECT
uid AS uid,
NewID AS nid,
TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', date) AS timestamp
FROM `2__stories`
WHERE statuscode = 0
ORDER BY sid
;
-- legge til review
ALTER TABLE `tb2__comments` ADD `review` TINYINT( 4 ) NULL ;
INSERT IGNORE INTO tb2__comments
SELECT
c.cid AS cid,
c.pid AS pid,
c.newID AS nid,
c.uid AS uid,
c.title AS subject,
CONCAT(c.comment, "\n\n", (CASE WHEN c.name NOT LIKE '' THEN c.name ELSE '' END)) AS comment,
'' AS hostname,
TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', c.date) AS timestamp,
0 AS score,
0 AS status,
3 AS format,
'01.00/' AS thread,
'a:1:{i:0;i:0;}' AS users,
(CASE WHEN u.name NOT LIKE 'Anonym' THEN u.name ELSE '' END) AS name,
'' AS mail,
'' AS homepage,
c.score AS review
FROM 2__comments AS c, tb2__users u
WHERE u.uid=c.uid
;
-- importere anonyme kommentarer
-- dvs uid =-1
INSERT IGNORE INTO tb2__comments
SELECT
c.cid AS cid,
c.pid AS pid,
c.newID AS nid,
0 AS uid,
c.title AS subject,
CONCAT(c.comment, "\n\n", (CASE WHEN c.name NOT LIKE '' THEN c.name ELSE '' END)) AS comment,
'' AS hostname,
TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', c.date) AS timestamp,
0 AS score,
0 AS status,
3 AS format,
'01.00/' AS thread,
'a:1:{i:0;i:0;}' AS users,
c.name AS name,
'' AS mail,
'' AS homepage,
c.score AS review
FROM 2__comments AS c
WHERE c.uid=-1
;
Importkontroll
-- sjekke om alle artiklene kom med i importen SELECT t.* AS "Navn på faenskapet" FROM 2__stories t LEFT JOIN tb2__node d ON (t.NewID = d.nid) WHERE d.nid IS NULL
Importere taxonomier
Her er de 13 kategoriene til TromsoBy med ID-nøklene som Drupal har tillagt:
-- Dumping data for table `term_data` INSERT INTO `tb2__term_data` VALUES (3, 3, 'tromso_guide', '', 0); INSERT INTO `tb2__term_data` VALUES (4, 3, 'nyheter', '', 0); INSERT INTO `tb2__term_data` VALUES (5, 3, 'blogg', '', 0); INSERT INTO `tb2__term_data` VALUES (6, 3, '200_film', '', 0); INSERT INTO `tb2__term_data` VALUES (7, 3, 'om_tromsoby', '', 0); INSERT INTO `tb2__term_data` VALUES (8, 3, '300_musikk', '', 0); INSERT INTO `tb2__term_data` VALUES (9, 3, 'sport', '', 0); INSERT INTO `tb2__term_data` VALUES (10, 3, '400_restaurant', '', 0); INSERT INTO `tb2__term_data` VALUES (11, 3, '500_konsert', '', 0); INSERT INTO `tb2__term_data` VALUES (12, 3, 'Diskusjon', '', 0); INSERT INTO `tb2__term_data` VALUES (13, 3, 'english', '', 0); INSERT INTO `tb2__term_data` VALUES (14, 3, 'grus', '', 0); INSERT INTO `tb2__term_data` VALUES (15, 3, 'kultur_litteratur', '', 0);
term_node inneholder Drupal-artikkelIDen og fremmednøkkelen til IDen i term_data
INSERT IGNORE INTO tb2__term_node SELECT newID AS nid, ( SELECT CASE WHEN tid LIKE '200_film' THEN '6' WHEN tid LIKE '300_musikk' THEN '8' WHEN tid LIKE '400_restaurant' THEN '10' WHEN tid LIKE '500_konsert' THEN '11' WHEN tid LIKE 'blogg' THEN '5' WHEN tid LIKE 'Diskusjon' THEN '12' WHEN tid LIKE 'english' THEN '13' WHEN tid LIKE 'grus' THEN '14' WHEN tid LIKE 'kultur_litteratur' THEN '15' WHEN tid LIKE 'nyheter' THEN '4' WHEN tid LIKE 'om_tromsoby' THEN '7' WHEN tid LIKE 'sport' THEN '9' WHEN tid LIKE 'tromso_guide' THEN '3' END ) AS tid FROM `2__stories` ORDER BY `date` ASC
Importere statistikk
uvisst hva daycount gjør
INSERT IGNORE INTO tb2__node_counter SELECT NewID AS nid, hits AS totalcount, NULL AS daycount, NOW() AS timestamp FROM 2__stories ;
-- henter ut antall kommentarer pr artikkel, kjører det -- inn i tb2__node_comment_statistics sammen med unix-tid -- for den nyeste av kommentarene og brukerID en dersom den finnes -- om brukerID ikke finnes settes den til 0 -- hvis du skal slette den gamle avkommenter denne linja: -- DELETE FROM `tb2__node_comment_statistics` WHERE nid >10000 INSERT IGNORE INTO tb2__node_comment_statistics SELECT s.NewID , (SELECT MAX(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', m.date)) FROM 2__comments m WHERE m.NewID = s.NewID AND m.isMeningsmaling <>1 GROUP BY m.NewID) AS last_comment_timestamp, NULL AS last_comment_name, (SELECT CASE WHEN c.uid = -1 THEN 0 WHEN c.uid > 0 AND c.uid < 999999999 THEN c.uid ELSE 0 END) AS last_comment_uid, comments AS comment_count FROM 2__stories s LEFT JOIN 2__comments c ON (s.NewID = c.NewID);
Import av events
-- legg til nytt felt ALTER TABLE `2__events` ADD `NewID` VARCHAR( 20 ) NOT NULL ; UPDATE `2__events` SET newID = eid; UPDATE `2__events` SET newID = RIGHT(newID, 9);
INSERT IGNORE INTO tb2__node
SELECT newID AS nid,
newID AS vid, 'event' AS TYPE,
title AS title,
uid AS uid,
1 AS STATUS,
UNIX_TIMESTAMP(CONCAT(datestart, ' ', timestart)) AS created,
UNIX_TIMESTAMP(CONCAT(datestart, ' ', timestart)) AS changed,
2 AS COMMENT ,
0 AS promote,
0 AS moderate,
0 AS sticky
FROM 2__events
;
INSERT IGNORE INTO tb2__node_revisions
SELECT
newID AS nid,
newID AS vid,
uid AS uid,
title AS title,
(CASE
WHEN calendar_image LIKE '0' THEN description
WHEN calendar_image LIKE '' THEN description
else CONCAT("<img src=\"files/images/archive/", calendar_image, ".jpg\" class=\"calendar_image\">", description) END
)
AS body,
'' AS teaser,
'' AS log,
UNIX_TIMESTAMP(CONCAT(datestart, ' ', timestart)) AS timestamp,
3 AS format
FROM 2__events
ORDER BY eid
;
-- tb2__content_type_event
-- CREATE TABLE `tb2__content_type_event` (
-- `vid` int(10) unsigned NOT NULL default '0',
-- `nid` int(10) unsigned NOT NULL default '0',
-- `field_lenke_url` varchar(255) NOT NULL default '',
-- `field_lenke_title` varchar(255) NOT NULL default '',
-- `field_lenke_attributes` mediumtext,
-- `field_sted_value` varchar(100) NOT NULL default '',
-- PRIMARY KEY (`vid`)
-- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- OBS! Feiler scriptet nedenfor må dette kjøres, location feltet må kuttes fra 128 tegn til 100
-- ALTER TABLE `2__events` CHANGE `location` `location` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL
INSERT IGNORE INTO `tb2__content_type_event`
SELECT
newID AS `vid`,
newID AS `nid`,
url AS `field_lenke_url`,
title AS `field_lenke_title`,
'N;' AS `field_lenke_attributes`,
location AS `field_sted_value`
FROM 2__events
;
-- tb2__event
-- CREATE TABLE `tb2__event` (
-- `nid` bigint(20) NOT NULL default '0',
-- `event_start` int(10) unsigned NOT NULL default '0',
-- `event_end` int(10) unsigned NOT NULL default '0',
-- `timezone` int(10) NOT NULL default '0',
-- PRIMARY KEY (`nid`),
-- KEY `event_start` (`event_start`)
-- ) ENGINE=MyISAM DEFAULT CHARSET=utf8
-- ;
-- OBS: Ser ut som om windows har problemer med noen av de importerte datoene. Får feilmeldinger når man går inn på kalendersida
INSERT IGNORE INTO `tb2__event`
SELECT
newID AS `nid`,
UNIX_TIMESTAMP(CONCAT(datestart, ' ', timestart)) AS `event_start`,
UNIX_TIMESTAMP(CONCAT(dateend, ' ', timeend )) AS `event_end`,
'320' AS `timezone`
FROM 2__events
;
-- INSERT IGNORE INTO tb2__nodeteaser
INSERT IGNORE INTO tb2__history
SELECT
uid AS uid,
NewID AS nid,
UNIX_TIMESTAMP(CONCAT(datestart, ' ', timestart)) AS timestamp
FROM `2__events`
;
Importere statistikk
uvisst hva daycount gjør
INSERT IGNORE INTO tb2__node_counter SELECT NewID AS nid, hits AS totalcount, NULL AS daycount, NOW() AS timestamp FROM 2__events ;
-- henter ut antall kommentarer pr artikkel, kjører det INSERT IGNORE INTO tb2__node_comment_statistics SELECT NewID AS `nid`, 0 AS `last_comment_timestamp`, NULL AS `last_comment_name`, 0 AS `last_comment_uid`, comments AS `comment_count` FROM 2__events ;
Backup av `tb2__term_data`
Skal ikke kjøres
--
-- Dataark for tabell `tb2__term_data`
--
INSERT INTO `tb2__term_data` VALUES (17, 4, 'konsert', , 0); INSERT INTO `tb2__term_data` VALUES (18, 4, 'film', , 0); INSERT INTO `tb2__term_data` VALUES (19, 4, 'teater', , 0); INSERT INTO `tb2__term_data` VALUES (20, 4, 'klubb', , 0); INSERT INTO `tb2__term_data` VALUES (21, 4, 'litteratur', , 0); INSERT INTO `tb2__term_data` VALUES (22, 4, 'mote', , 0); INSERT INTO `tb2__term_data` VALUES (23, 4, 'idrett', , 0); INSERT INTO `tb2__term_data` VALUES (24, 4, 'Barn', , 0); INSERT INTO `tb2__term_data` VALUES (25, 4, 'utstilling', , 0); INSERT INTO `tb2__term_data` VALUES (26, 4, 'quiz', , 0); INSERT INTO `tb2__term_data` VALUES (27, 4, 'dans', , 0);
-- BØRE NØDVENDIG Å KJØRE NÅR TAXONOMIeNE IKKE FINNES -- term_node inneholder Drupal-artikkelIDen og fremmednøkkelen til IDen i term_data INSERT IGNORE INTO tb2__term_node SELECT newID AS nid, ( SELECT CASE WHEN tid LIKE 'konsert' THEN '17' WHEN tid LIKE 'film' THEN '18' WHEN tid LIKE 'teater' THEN '19' WHEN tid LIKE 'klubb' THEN '20' WHEN tid LIKE 'litteratur' THEN '21' WHEN tid LIKE 'mote' THEN '22' WHEN tid LIKE 'idrett' THEN '23' WHEN tid LIKE 'Barn' THEN '24' WHEN tid LIKE 'utstilling' THEN '25' WHEN tid LIKE 'quiz' THEN '26' WHEN tid LIKE 'dans' THEN '27' END ) AS tid FROM 2__events ;
Auroras "spammotor"
I Aurora er det tabellen "2__emailcomments" som gjør magien. I drupal er det modulen Subscriptions http://drupal.org/project/subscriptions
-- legg til nytt felt ALTER TABLE `2__emailcomments` ADD `NewID` VARCHAR(20) NOT NULL ; UPDATE 2__emailcomments SET NewID = sid; UPDATE `2__emailcomments` SET newID = RIGHT(NewID, 9) WHERE NewID > 1 ; -- Egentlig ikke nødvendig, men sikrer at NewID tolkes som et tall ALTER TABLE `2__emailcomments` CHANGE `NewID` `NewID` BIGINT( 9 ) NOT NULL ; -- finne de ca 50 luringene som har abonnerer anonymt, men senere har registrert seg og fått uid -- Unngå Unikhets-feilmelding under importen ALTER TABLE `2__emailcomments` DROP PRIMARY KEY ; -- Sette inn uid istedenfor epost hos folk som har brukerkonto UPDATE 2__emailcomments s, 2__users u SET s.uid = u.uid, s.email = '0' WHERE u.email=s.email AND s.email IS NOT NULL AND s.email <> '';
Importere abonementer
-- Setter inn registrerte brukeres abonnement på tråder -- 257 er haralds uid for å unngå spamflod ved et uhell! -- bytt linja til "uid AS uid," før reell import !! REPLACE INTO tb2__subscriptions SELECT NewID AS sid, uid, 'node' AS stype FROM 2__emailcomments WHERE uid > 0;
Fikse annonyme brukere
UPDATE `tb2__node` SET `uid` = '0' WHERE `tb2__node`.`uid` = -1; UPDATE tb2__node_revisions SET `uid` = '0' WHERE `uid` = -1; UPDATE tb2__history SET `uid` = '0' WHERE `uid` = -1;
Fikse sequense comments
UPDATE tb2__sequences S SET id=(SELECT MAX(C.cid) FROM tb2__comments C) WHERE name='tb2__comments_cid'
Setningen over; manuelt
SELECT MAX( C.cid ) FROM tb2__comments C;
UPDATE tb2__sequences S SET id=[[verdi fra statement over]] WHERE name='tb2__comments_cid'
Fiks av bildereferanser
UPDATE tb2__node_revisions SET teaser = REPLACE(teaser, 'files/images/archive','/images/upload') UPDATE tb2__nodeteaser SET teaser = REPLACE(teaser, 'files/images/archive','/images/upload') UPDATE tb2__node_revisions SET body = REPLACE(body, 'files/images/archive','/images/upload')
