Converting tt_news to news

This guide shows you how to convert records from the old extension tt_news to the new news extension. It is done with pure SQL. It converts only the records, not the plugins and not the HTML Templates.

Please notice, that:

  • no_auto_pb from the news-table will not be respected (there is no option for disabling autopagebreak in EXT:news)
  • shortcut_target from the category-table will not be respected (there is no setting in the new categories)
  • title_lang_ol from the category-table will not be respected (no translations of categories will be created)

Preparation

  • Make a full backup of your database!
  • Install the extension 'news' in your TYPO3 system before you start.
  • Be sure, that you have permissons to run SQL statements and access to the filesystem (SFTP, SSH, ...).

PHPMyAdmin or mysql command?

If you don't have shell access to run a mysql script, follow the steps above via PHPMyAdmin. We tested it well. Important: Read and follow all notices carefully!

If you have the possiblilty to run the mysql command on your server, you can download the converter script and execute it on your shell after moving the folders /uploads/pics/ and /uploads/media/ to /fileadmin/*/ and indexing them (open Filelist and browse all pages or run the scheduler task 'File Abstraction Layer: Update storage index').
In this case you can ignore the following steps.

Convert the base fields of the records

Run the first SQL-Statement to insert the base fields into the new news table:

INSERT INTO tx_news_domain_model_news
(uid, pid, tstamp, crdate, cruser_id, deleted, hidden, starttime, endtime, externalurl, type, internalurl, archive, keywords, title, editlock, fe_group, datetime, teaser, bodytext, author, author_email, sys_language_uid, l10n_parent, l10n_diffsource,
t3ver_oid, t3ver_id, t3ver_wsid, t3ver_label, t3ver_state, t3ver_stage, t3ver_count, t3ver_tstamp, t3_origuid)
SELECT
uid, pid, tstamp, crdate, cruser_id, deleted, hidden, starttime, endtime, ext_url, type, page, archivedate, keywords, title, editlock, fe_group, datetime, short, bodytext, author, author_email, sys_language_uid, l18n_parent, l18n_diffsource,
t3ver_oid, t3ver_id, t3ver_wsid, t3ver_label, t3ver_state, t3ver_stage, t3ver_count, t3ver_tstamp, t3_origuid
FROM
tt_news;

Convert images to FAL

With the next statement we convert the comma separated filenames of tt_news to FAL (sys_file_reference).

Important: Fill 'Delimiter' in PHPMyAdmin with: $$

DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE pid INT DEFAULT 0;
DECLARE cruser_id INT DEFAULT 0;
DECLARE crdate INT DEFAULT 0;
DECLARE value TEXT;
DECLARE imagecaption TEXT;
DECLARE imagealttext TEXT;
DECLARE imagetitletext TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_image TEXT;
DECLARE splitted_imagecaption TEXT;
DECLARE splitted_imagealttext TEXT;
DECLARE splitted_imagetitletext TEXT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT tt_news.uid AS id, tt_news.image AS value, tt_news.imagecaption AS imagecaption, tt_news.imagealttext AS imagealttext, tt_news.imagetitletext AS imagetitletext, tt_news.pid, tt_news.cruser_id, tt_news.crdate FROM tt_news WHERE tt_news.image != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO id, value, imagetitletext, imagecaption, imagealttext, pid, cruser_id, crdate;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(value) - LENGTH(REPLACE(value, bound, '')) +1);
SET i=1;
WHILE i <= occurance DO
SET splitted_image = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i), LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ',', ''));
SET splitted_imagecaption = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(imagecaption, '\n', i), LENGTH(SUBSTRING_INDEX(imagecaption, '\n', i - 1)) + 1), ',', ''));
SET splitted_imagealttext = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(imagealttext, '\n', i), LENGTH(SUBSTRING_INDEX(imagealttext, '\n', i - 1)) + 1), ',', ''));
SET splitted_imagetitletext = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(imagetitletext, '\n', i), LENGTH(SUBSTRING_INDEX(imagetitletext, '\n', i - 1)) + 1), ',', ''));
INSERT INTO sys_file_reference (pid, title, description, alternative, uid_foreign, downloadname, sorting_foreign, tstamp, crdate, cruser_id, tablenames, fieldname, table_local) VALUES (pid, TRIM(BOTH '\n' FROM splitted_imagetitletext), TRIM(BOTH '\n' FROM splitted_imagecaption), TRIM(BOTH '\n' FROM splitted_imagealttext), id, splitted_image, i, '1439330400', crdate, cruser_id, 'tx_news_domain_model_news', 'fal_media', 'sys_file');
SET i = i + 1;
END WHILE;
END LOOP;
CLOSE cur1;
END; $$
CALL explode_table(',')$$
DROP PROCEDURE IF EXISTS explode_table $$

Move files from uploads to fileadmin

At next we move (or copy) the folder /uploads/pics/ to /fileadmin/pics/. After that be sure, that they get indexed by FAL. Simple open the Filelist module of TYPO3 and browse through the whole folder. If you have a lot of files you can also use the scheduler task 'File Abstraction Layer: Update storage index'.

Update sys_file_reference and convert categories

Now we can set the correct reference uid to the sys_file_reference table. We also convert tt_news categories zu sys_categories with the following statement:

UPDATE sys_file_reference SET uid_local = (SELECT uid FROM sys_file WHERE identifier = BINARY CONCAT('/pics/', downloadname) LIMIT 1), downloadname='' WHERE tstamp = 1439330400;

INSERT INTO sys_category
(uid, pid, tstamp, crdate, deleted, hidden, starttime, endtime, sorting, fe_group, title, parent, single_pid, description, shortcut)
SELECT
uid, pid, tstamp, crdate, deleted, hidden, starttime, endtime, sorting, fe_group, title, parent_category, single_pid, description, shortcut
FROM
tt_news_cat;

INSERT INTO sys_file_reference
(pid, uid_foreign, downloadname, tstamp, crdate, tablenames, fieldname, table_local)
SELECT
pid, uid, image, '1439330401', crdate, 'sys_category', 'images', 'sys_file'
FROM
tt_news_cat
WHERE
image != '';

UPDATE sys_file_reference SET uid_local = (SELECT uid FROM sys_file WHERE identifier = BINARY CONCAT('/pics/', downloadname) LIMIT 1), downloadname='' WHERE tstamp = 1439330401 AND tablenames = 'sys_category';

INSERT INTO sys_category_record_mm
(uid_local, uid_foreign, tablenames, fieldname, sorting_foreign)
SELECT
uid_foreign, uid_local, 'tx_news_domain_model_news', 'categories', sorting
FROM
tt_news_cat_mm;

INSERT INTO tx_news_domain_model_news_related_mm
(uid_local, uid_foreign, sorting_foreign)
SELECT
uid_foreign, uid_local, sorting
FROM
tt_news_related_mm;

Convert related files to FAL

With the next statement we convert the comma separated filenames of tt_news to FAL (sys_file_reference).

Important: Fill 'Delimiter' in PHPMyAdmin with: $$

DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))
BEGIN
DECLARE uid INT DEFAULT 0;
DECLARE pid INT DEFAULT 0;
DECLARE cruser_id INT DEFAULT 0;
DECLARE crdate INT DEFAULT 0;
DECLARE news_files TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_news_files TEXT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT tt_news.uid, tt_news.pid, tt_news.cruser_id, tt_news.crdate, tt_news.news_files FROM tt_news WHERE tt_news.news_files != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO uid, pid, cruser_id, crdate, news_files;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(news_files) - LENGTH(REPLACE(news_files, bound, '')) +1);
SET i=1;
WHILE i <= occurance DO
SET splitted_news_files = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(news_files, bound, i), LENGTH(SUBSTRING_INDEX(news_files, bound, i - 1)) + 1), ',', ''));
INSERT INTO sys_file_reference (pid, uid_foreign, downloadname, sorting_foreign, tstamp, crdate, cruser_id, tablenames, fieldname, table_local) VALUES (pid, uid, splitted_news_files, i, '1439330402', crdate, cruser_id, 'tx_news_domain_model_news', 'fal_related_files', 'sys_file');
SET i = i + 1;
END WHILE;
END LOOP;
CLOSE cur1;
END; $$
CALL explode_table(',')$$
DROP PROCEDURE IF EXISTS explode_table $$

Move files from uploads to fileadmin

At next we move (or copy) the folder /uploads/media/ to /fileadmin/media/. After that be sure, that they get indexed by FAL. Simple open the Filelist module of TYPO3 and browse through the whole folder. If you have a lot of files you can also use the scheduler task 'File Abstraction Layer: Update storage index (scheduler)'.

Update sys_file_reference

Now we can set the correct reference uid to the sys_file_reference table with the following statement:

UPDATE sys_file_reference SET uid_local = (SELECT uid FROM sys_file WHERE identifier = BINARY CONCAT('/media/', downloadname) LIMIT 1), downloadname='' WHERE tstamp = 1439330402 AND tablenames = 'tx_news_domain_model_news' AND downloadname != '';

Convert links to news link records

Use the following statement to split the line-by-line stored links to link-records.

Important: Fill 'Delimiter' in PHPMyAdmin with: $$

DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))
BEGIN
DECLARE uid INT DEFAULT 0;
DECLARE pid INT DEFAULT 0;
DECLARE cruser_id INT DEFAULT 0;
DECLARE crdate INT DEFAULT 0;
DECLARE sys_language_uid INT DEFAULT 0;
DECLARE links TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_links TEXT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT tt_news.uid, tt_news.pid, tt_news.cruser_id, tt_news.crdate, tt_news.sys_language_uid, tt_news.links FROM tt_news WHERE tt_news.links != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO uid, pid, cruser_id, crdate, sys_language_uid, links;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(links) - LENGTH(REPLACE(links, bound, '')) +1);
SET i=1;
WHILE i <= occurance DO
SET splitted_links = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(links, bound, i), LENGTH(SUBSTRING_INDEX(links, bound, i - 1)) + 1), '\n', ''));
INSERT INTO tx_news_domain_model_link (pid, tstamp, crdate, cruser_id, parent, uri, sorting, sys_language_uid) VALUES (pid, '1439330403', crdate, cruser_id, uid, splitted_links, i, sys_language_uid);
SET i = i + 1;
END WHILE;
END LOOP;
CLOSE cur1;
END; $$
CALL explode_table('\n')$$
DROP PROCEDURE IF EXISTS explode_table $$

After converting

After running all statements, you have a ready working backend with your news records. But don't forget to finish your work with:

  • change all your plugins from the tt_news to the news plugin
  • adapt your html templates of the news extension
  • check, migrate and clean up your tt_news TypoScript

If you like our work and it saved your time, feel free to support us. We are happy about donations. No matter if small or big amounts. And of course, you get an invoice for it. With your support we can create more converters or cool community stuff.

Don't forget to share this website on your social networks :-)