Merging Textpattern databases?

jayrope · 2022-02-19 17:29:00

I’d love to merge databases of 3 or 4 different sites/txp installations and i am wondering: Has anyone done that before & how did you approach this?

Thank you for any hints!

colak · 2022-02-19 17:34:43

Best way is exporting all textpattern tables of the dbs in raw mysql, changing the ids, and re-importing them to one db. The issue lies with images which you will need to re-upload.

jakob · 2022-02-19 22:23:01

I’ve done this a couple of times and it depends very much on the setup of the sites, particularly the url scheme, and your categories, sections and custom fields, and how similar/different they are. I don’t know of an automatic way to import articles and have them automatically renumber, but perhaps there’s a way I’m not aware of. I’m sure the programmers here have some tips to semi-automate this but in the end I’ve always done this manually working carefully and making backups as I go.

You need to identify where the sites overlap and prepare each site so that when you merge them, entries and data within them don’t collide. For all the Textpattern content types – articles, image, links, files, comments – you’ll need to change the IDs of all but one of the sites so that they don’t overlap, e.g. site1 might be articles 1-345, site2 articles 400-485, site3 articles 500-556… or whatever. It doesn’t matter if there’s a gap in the numbering and starting each site from a new 100-round number can be helpful because …

… anywhere where you have linked to a specific article / image / link / file id number you will need to amend that in the respective articles. That could be a link or image link in the article body, it could be the article_image field, or it could be in an article_custom id="…" or a txp:image id="…" tag in your forms, etc. Same for files and links etc. That’s where having round numbers comes in: if you just need to up your article numbers by 400, you can search and replace 100 at a time, e.g. 1-99 to 401-499, 100-199 to 500-599 etc.

If you are using the same custom field in two or more sites for different purposes, .e.g. custom_2 is used for different things on two sites, you will need to shift one of them to a custom field number that is vacant on all your sites.

What else:

Make backups while you go and name them clearly and work on a copy so you have a way back to the previous successful step if something doesn’t work.
Try and make your table collations match before you start with the merge. You can often edit that in a database editor (see below).
Before merging, create all the categories in the structure you need them on your base site. As that database table includes how the categories related to each other, you can’t simply copy stuff across, so if it’s feasible, do that by hand first making sure the category names (not the titles) match exactly what you have in your articles.
Work on one database table at a time, especially if you’re working with sql files or a csv/excel export.
You don’t necessarily have to merge the textpattern table of sites 1, 2, 3 and 4 into a one big .sql file in a text editor, which can get cumbersome for large sites. You can also import the textpattern table from sites 2, 3 and 4 (after you’ve renumbered them) one after the other into site 1. If you do this, make sure you remove the DROP TABLE and CREATE TABLE from those .sql file so that you only have INSERT INTO … VALUES lines remaining, otherwise you will overwrite the table each time.
When Textpattern saves an article, it pre-renders the textile in the body and excerpt fields to html and stores that in the body_html and excerpt_html fields. If you use a text-editor or spreadsheet to manually change the body and excerpt fields, you still won’t see the changes until those articles are re-saved. The plugin sed_textile_upgrade (updated version) can batch re-textile all the articles. If you have lots of articles, it can unfortunately timeout and you may need to set your server to temporarily have a longer timeout period for it to complete successfully.

How to make the edits / tools to use:

You can do all this table by table using a spreadsheet program like excel or apple numbers, but I’ve found that only works well if you don’t have long articles with lots of carriage returns. You only need one not properly escaped carriage return and your import is borked. Your db-export->spreadsheet-input and spreadsheet-export->db-import should ideally properly escape all quotes and carriage returns and wrap the fields in quotes. In practice not all programs work satisfactorily. I’ve found that works best for tables that rarely have carriage returns in the field such as the txp_image table.
For example: you can renumber an entire ID column by changing id#1 to id#400 and then for rows with ids 2-… paste a formula with “={cell above} +1” formula in all the cells beneath it. The numbers will now be consecutive but the cells still hold the formula. To convert those back to the numbers, copy that entire column and paste in the computed values (“Formelergebnisse einsetzen” in German). That replaces the formula with the actual resulting number.

You can also work directly using a database editor like phpmysql or sequel ace on a Mac. There’s probably a similar program out there for windows. They generally have an SQL tab where you can enter MySQL statements to do things like copy the contents of custom_2 to custom_6, etc. You can do also modify the collation of table fields here too, and most importantly, you can also export and import individual tables or csv / sql files using these tools.

You can work in a text editor that has regex-search on an exported .sql file. This is a good way of replacing any instances of hard-coded links and numbers directly in the sql file. You do need to work methodically and think carefully about your search and replaces so you do not affect anything else, but it can be very effective.

A batch file renamer app can be helpful for things like images (or else you rename on the command line). If you use the principle of 100s above for images too, you just need to increment the numbers of your image files to make them match the values in the txp_image table. That saves reimporting them and matching them up again. Some batch renamers (A Better Finder Renamer on the Mac for example) can handle more complex scenarios and rename files according to a table of matching source-destination names that you provide as a tab delimited file (and prepare in your spreadsheet first).

Finally there are dedicated tools that can help with this: EasyDataTransform for example can merge data from different database (e.g. using the stack transform) and much more, but it comes at a price.

That all sounds terribly complicated but in the end it depends on your sites and how they are setup. If they are very similar in structure, mostly use the regular txp:permlink function and sections/categories for organisation or images, and perhaps not too much else, it can be quick.

Bloke · 2022-02-20 01:14:48

All excellent advice. To make things easier on yourself with ID values, try this:

Edit your backup files (SQL/csv/whatever) and remove the ID column completely.
Import or establish your first database.
Note the last ID in your textpattern table after import.
Choose a suitable value higher than this. E.g. if you had 376 articles in db1, choose 401. The ‘1’ helps if all your articles began at 1 and were sequential, because if you need to change any values in tags or stuff, you can just add 400. In practice, you’ll never be that lucky but it might save you a bit of hassle.
Execute the following command before you import the next database:

alter table textpattern auto_increment=startingValue;

(where startingValue is the ID you want next). Then import the next textpattern table from db2.

Repeat for each table. You can use the same trick for images, files, etc to set the next auto increment ID value. As long as there’s no incoming ID column, MySQL will import sequentially starting from the value you set.

jayrope · 2022-03-12 20:43:22

Very detailed and in-depth information, thank you.

Concluding this i guess it can be said, that it’s a lot of work either way, manually or semi-automated in the database. I guess i’ll set up a test site for this first, also to find out, how to handle different domains/URLs pointing to parts of a common database, image & file directories at last.
The reason why this came up is, that i want to move my complete US-hosted shared account with multiple domains and probably around 20 Textpattern installs into something EU-hosted (local to me), which idealistically only needs one TXP update in the future.

Thank you all loads for the pointers!

Last edited by jayrope (2022-03-12 20:43:40)

jakob · 2022-03-12 22:02:29

jayrope wrote #332896:

… how to handle different domains/URLs pointing to parts of a common database, image & file directories at last.

This is untried, but two ideas:

if you’re running different homepages from the exact same database, you could try not filling out site_url in Admin › Preferences › Site and make your in-site links only link relative to the base url, i.e. /section/ and /section/url-title and /section/category/category-name without using txp:site_url.
If you have information in one database that you’d like to use in another for specific purposes, you can switch database connections mid-page with rah_swap (or this trick). You wouldn’t necessarily need to merge the databases.

The reason why this came up is, that i want to move my complete US-hosted shared account with multiple domains and probably around 20 Textpattern installs into something EU-hosted (local to me), which idealistically only needs one TXP update in the future.

You wouldn’t necessarily need to merge databases for that. Again, there are different possibilities.

If for some reason your hosts provides you with a very limited number of databases, you can give each a database prefix (in config.php) enabling you to have several textpattern installations side-by-side in the same database. You’ll have one database with:

site1_textpattern
site1_txp_image
…
site2_textpattern
site2_txp_image
…

That said, you can get web hosting with plenty of databases fairly cheaply nowadays, so you can avoid this quite easily.

What you can also do if you want to ease your update work is to run a multisite setup. With that you have one set of core installation files for Textpattern hooked up to multiple sites. Each site runs independently with its own admin area and own images and files but updating the core files updates all the sites running off it. The databases are still independent.

something EU-hosted (local to me)

There are a multitude of hosts out there. I can recommend all-inkl.com who are very local to you and have remained independent in the 20 years I’ve had an account there (unlike many others that were snapped up big conglomerates). Their 8€/month package has 25 databases and 5 included domains (i.e. annual fee included) and their 10€/month package has 50 databases and 10 included domains. These are shared servers and you can run many more sites from them, you just need to pay the annual domain fee for additional domains. You can also register your domains elsewhere but host them from all-inkl, i.e. you can transfer them to all-inkl but don’t have to.
Their website and admin panel look a bit antiquated but their server setup is actually more flexible than most I’ve used: you can use any directory setup you like and point domains and subdomains where you want, so multisite works fine. If you are running sites for other other people, you can create subaccounts for them to keep their access separate while managing your things centrally. Lets encrypt, ssh (e.g. for symlinking), multiple php versions, remote mysql access are all included and straightforward and email space is generous. For LAMP / Textpattern development just fine.

jayrope · 2022-03-13 15:59:02

jakob wrote #332899:

[…] I can recommend all-inkl.com who are very local to you and have remained independent in the 20 years I’ve had an account there […]

Thank you for the reminder! Been to their backnd recently just to do a DNS redirection for a client of mine. Turned out, that one cannot do this oneself there, but the support was attending this instantly. I’ll look more into this. Have a cpanel right now, let’s see if i can handle my usual things without that.

Last edited by jayrope (2022-03-13 16:00:04)

jakob · 2022-03-13 16:21:17

jayrope wrote #332905:

A DNS redirection for a client of mine. Turned out, that one cannot do this oneself there

Then your client probably has the smallest tarif (no SSL with that either, unfortunately). I can definitely edit DNS settings from my account. See KAS Tools › DNS-Werkzeuge.

but the support was attending this instantly.

I’ve only occasionally used their support but every time they’ve been quick and knowledgeable. Telephone support is also quickly reachable.

Have a cpanel right now, let’s see if i can handle my usual things without that

cPanel has indeed got better and less restrictive about directory paths over time. I still find it convoluted with all those “Go Back…” pages at the end of an operation and choosing the right option for domain / addon domain / subdomain / redirect has me confused every time.

Textpattern CMS

Textpattern CMS support forum

#1 2022-02-19 17:29:00