Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
SQL query to export MLP content?
Dear Textpatrons,
I want to export my MLP content into a single SQL table using MySQL. Since the last post on the MLP thread from 2019 hasn’t been answered, I thought it might be wiser to create a new thread for this specific question.
I described the details in the linked post. In short, I have renditions in English (en-us) and Spanish (es-es). The database appears to have content across four tables: l10n_articles, l10n_txp_en_us, l10n_txp_es_es, and textpattern, but I don’t follow exactly how they are linked.
Can anyone advise me how to construct an approrpiate SQL query so that each record contains all the relevant information for each article, across all renditions and without duplicate fields?
Thank you in advance!
Offline
Re: SQL query to export MLP content?
i am no MySQL expert by any means, nor do i play one on TV. But might this be of any help with your case?
Also as i believe this is not strictly a TxP issue perhaps asking on a MySQL forum might render some other solutions…
…. texted postive
Offline
Re: SQL query to export MLP content?
Hi,
I don’t think there are still many users of MLP and doubt it works in recent txp versions. It would be useful to see l10n_txp_ tables structure before trying to help with the export. And the export format (i.e. one record per rendition or across all renditions) depends on what you intend to do afterwards.
Offline
Re: SQL query to export MLP content?
Thank you!
I was hoping that someone here had a grasp on the l10n_ scheme. But if it could help someone else in the future, I don’t mind probing it and posting my findings here.
What I notice right away using DESCRIBE queries is that the l10n_txp_ tables essentially copy the structure of the textpattern table, including all the custom fields. Importantly, all the tables include 2 additional fields for localization: l10n_lang, and l10n_group.
Not only that, it appears that all the entries in the textpattern table are duplicated in either l10n_txp_en_us OR l10n_txp_es_es. COUNT(*) confirms that the number of entries in each l10n_txp_ table matches the number of entries in the textpattern table that have the designated l10n_lang. Even the ids of each article are identical in both the textpattern and l10n_txp_ tables.
That leaves the l10n_articles table, which has 3 columns. This appears to be the glue that links the different language renditions of an article together. Here, the id column corresponds to l10n_group input on the textpattern and l10n_txp_ tables. The name column appears to be the given article’s title in English, and the members column is a JSON string that contains the ids of the article renditions.
I think some concrete examples are necessary to explain this.
Let’s take article id 710 from the textpattern table, which also exists as id 710 in the l10n_txp_en_us table:
mysql> select id, l10n_lang, l10n_group from textpattern where id = 710;
+-----+-----------+------------+
| id | l10n_lang | l10n_group |
+-----+-----------+------------+
| 710 | en-us | 387 |
+-----+-----------+------------+mysql> select id, l10n_lang, l10n_group from l10n_txp_en_us where id = 710;
+-----+-----------+------------+
| ID | l10n_lang | l10n_group |
+-----+-----------+------------+
| 710 | en-us | 387 |
+-----+-----------+------------+That article also has a rendition in Spanish, which happens to be article id 711 in the textpattern and l10n_txp_es_es tables:
mysql> select id, l10n_lang, l10n_group from textpattern where id = 711;
+-----+-----------+------------+
| id | l10n_lang | l10n_group |
+-----+-----------+------------+
| 711 | es-es | 387 |
+-----+-----------+------------+mysql> select id, l10n_lang, l10n_group from l10n_txp_es_es where id = 711;
+-----+-----------+------------+
| ID | l10n_lang | l10n_group |
+-----+-----------+------------+
| 711 | es-es | 387 |
+-----+-----------+------------+Articles 710 and 711 both have the same l10n_group id because they represent the same content in different languages. Here is their entry in the l10n_articles table:
mysql> select * from l10n_articles where id = 387;
+-----+------------------------------+--------------------------------------------+
| ID | names | members |
+-----+------------------------------+--------------------------------------------+
| 387 | [Title of English rendition] | a:2:{s:5:"en-us";i:710;s:5:"es-es";i:711;} |
+-----+------------------------------+--------------------------------------------+The upshot is, I think I can ignore the l10n_articles table, because each record has the same id in both the textpattern table, each record’s language is designated by a field, and each record has a language “group” field which links it to all other renditions of the same article.
I might be able to ingore the l10n_txp_ tables too.
That leaves me with the question: Can I construct a SQL query that selects each l10n_group id as a single item, with language-specific suffixes on each field?
I’m imagining something like this as the output…
| Title_en | Title_es | Body_en | Body_es | etc_en | etc_es | … |My aim is to import the content into a ProcessWire instance, as described here.
Offline
Re: SQL query to export MLP content?
Yikes, that looks complicated. Basically, you need to join two tables, e.g.
SELECT l10n_txp_en_us.Title AS Title_en, l10n_txp_es_es.Title AS Title_es, ... (etc) ...
FROM l10n_txp_en_us, l10n_txp_es_es
WHERE l10n_txp_en_us.l10n_group = l10n_txp_es_es.l10n_groupand insert the result into a new table (assuming you have already created it):
INSERT INTO export_table (Title_en, Title_es, ...)
SELECT l10n_txp_en_us.Title AS Title_en, l10n_txp_es_es.Title AS Title_es, ...
FROM l10n_txp_en_us, l10n_txp_es_es
WHERE l10n_txp_en_us.l10n_group = l10n_txp_es_es.l10n_groupI have not tested it, so please be prudent and back up first.
Offline
Re: SQL query to export MLP content?
Amazing, thank you @etc!
This is almost exactly what I need!
SELECT l10n_txp_en_us.Title AS Title_en, l10n_txp_es_es.Title AS Title_es, ... (etc) ...
FROM l10n_txp_en_us, l10n_txp_es_es
WHERE l10n_txp_en_us.l10n_group = l10n_txp_es_es.l10n_groupThis gives me all the articles that have renditions in BOTH languages (and therefore have rows in bot l10n_ tables. I also need to import the content that exists only in one language (English).
I’m guessing that would mean constructing a separate SQL query that goes something like this:
SELECT …
FROM l10n_txp_en_us
WHERE [the l10n_group does not appear in the l10n_txp_es_es table]Is it reasonable to modify the SELECT query you wrote so it returns these articles in addition to those it already makes available? Is it simpler to query these articles separately?
Last edited by johnstephens (2021-10-08 19:38:19)
Offline
Re: SQL query to export MLP content?
johnstephens wrote #331768:
This gives me all the articles that have renditions in BOTH languages (and therefore have rows in bot
l10n_tables. I also need to import the content that exists only in one language (English).
If English versions always exist, you can try to use LEFT JOIN:
INSERT INTO export_table (Title_en, Title_es, ...)
SELECT l10n_txp_en_us.Title AS Title_en, l10n_txp_es_es.Title AS Title_es, ...
FROM l10n_txp_en_us LEFT JOIN l10n_txp_es_es
ON l10n_txp_en_us.l10n_group = l10n_txp_es_es.l10n_groupMissing Spanish fields will have NULL value, so export_table must allow them.
Offline