Amending imported entries in SQL

[Zirbelholz]

I have imported a lot of entries from an old MT blog and want to do various things. Can I automate these by querying the SQL database?

1. open all entries for comments 2. change the directory where my image files are (I am also wondering what happens if I had thumbnails in MT) 3. Adding categories.

Is there some command I can use to do 1. and 2.? I imagine 3 is too difficult as the categories vary.

This is from a message to me from Garvin on another topic:

Otherwise, you must upload all the pictures to your new blog and then adjust every entry with the new src-location. You might be able to do that via automatic search+replace by exporting your serendipity_entries database table as an SQL dump, editing that file, and then re-importing the SQL dump.

Do I have to use the SQL dump approach? Or can I not just do something without exporting as a dump?

TIA

Margaret

[garvinhicking]

Hi!

You would need yourself to get famiilar with SQL syntax and operate on a dump, yes.

1. Opening up comments is easy:

Code: Select all

UPDATE serendipity_entries SET allow_comments = 'true'

will allow all entries to be commentable.

2. You'll need to export the full dump of the serendipity_entries table and use a Texteditor with search/replace for that.

3. Adding categories can be done with n:m associations within the serendipity_entrycat, serendipity_categories and serendipity_entries DB tables. You might want to use the "Assign Categories" Event plugin instead for this, as it allows you to easier assign all your entries to categories on a single page. Depending on how many entries you have, that might be hard though.

HTH,
Garvin

[Zirbelholz]

Thanks, Garvin.
Do you mean I don't need a dump to open comments? (trying to interpret whether your 1. and 2. are my 1. and 2.) and the dump is to change the image directory.

Someone (Wordpress user) had suggested to me that I do UPDATE ... SET comments.status = 'open' WHERE comments.status = 'closed'.

I realize your syntax is slightly different, but do I not need that 'WHERE' bit?

I have got some stuff on SQL, apart from tutorials on the Web (a totally clean book a few years old called Webseiten programmieren und gestalten!). I just haven't worked out where this dump will appear and that kind of thing. It's one thing reading about the syntax and another seeing what happens.

As for the categories, I suppose I will have to do those by hand since they vary.

Margaret

[garvinhicking]

Hi!

Ah, sorry for that confiusion. The SQL code I gave you is independent from a Dump. You can execute that directly using phpmyadmin. You don't need the WHERE-part, because you can simply open ALL comments. If you open comments for an already opened entry, it doesn'T hurt.

HTH,
Garvin

[Zirbelholz]

Great, that's what I thought myself. I have to look at the php my admin stuff, I remember now. Fortunately domainfactory is good with this sort of thing.

Margaret

[Zirbelholz]

That was very easy with the comments. I just went in there and did it without reading anything first. I think that will not be the case with the rest!

Another question occurs to me (of course). In Movable Type, I had something like a plugin to mark blockquotes. It involved me writing 'bq. ' (i.e. with a space after it) at the beginning of every paragraph that was to be indented.

The blockquote in bulletproof is much better. I like the way you can see how much is quote and how much comment at a glance.

I suspect I should have considered changing some things before I exported, but it's too late now. Is there a conceivable way I could replace the bq. paragraphs by blockquote paragraphs in the SQL dump? (I assume not)

Margaret

[garvinhicking]

Hi!

You can export your whole database as an SQL dump. Save that file. Then search for "bq " and replace thatwith "<blockquote>". Now the harder path is that you also need to put "</blockquote>" at the end of every paragraph manually, because MT doesn'Thave a "be_end" tag.

You could use regularexpressions to match things like that, but this would be a bit harder to evaluate, and you need an editor thatsupports regexps...

Regards,
Garvin

[Zirbelholz]

Yes, I realize that's the problem. If it were in Word, I could do a macro that found bq. and then marked the following paragraph, then inserted something at the end, but paragraphs may not be so obvious in html. I note what you say about more sophisticated editors.

You are probably right that it's easiest to do the second <blockquote> manually. I'm just thinking over how much I should do in this exported dump, in view of the time involved. Better do the whole thing, however long it takes, but on the other hand the occasional bq. in the text is less of a problem than the lack of categories, and I would add the categories only after the SQL dump thing.

I now know how to pronounce SQL. Is it pronounced Sequel in German too? or just as three letters?

Margaret

[chickens]

<off topic>

I now know how to pronounce SQL. Is it pronounced Sequel in German too? or just as three letters?

The pronunciation varies from project to project. The mySQL team calls it My S-Q-L( Ess Que Ell) While the postgreSQL team calls theirs Post-re-Ess-Que-Ell

Personally I do call it sequel, but when taking to other people I will use whatever format they use. This way it is less confusing. Many Microsoft server admins get on me for not calling it S Q L, so when in the Windows world I call it that. Everywhere else its sequel for me.

http://channel9.msdn.com/ShowPost.aspx?PostID=18620

</off topic>

[Zirbelholz]

<off topic>

Thanks!

Margaret

[garvinhicking]

Hi Margaret!

How many entries do you have? Another idea would be to write a custom PHP plugin for you. That'd take an hour or two, but it might save you a lot of time?

Regards,
Garvin

[Zirbelholz]

I'd be very happy to pay for such work. I had thought of this. I would also be happy to learn more about SQL, but unfortunately I am supposed to be doing some urgent work too, and in these circumstances it's not really in my interest, even my financial interest, to spend ten hours doing something myself when someone else would be faster.

What do you mean by a plugin? There are several things I want to do. I presume you refer to the bq. / blockquote problem? (I had got as far as looking at names of editors and considering trying MS Word but the file is probably too big). The thing with the image file directory change I can do myself with search and replace on the dump (at the moment the old directory is still full of html files and xml files that probably belong to MT but 1% of them may be mine so I can't delete them all). I will also take the opportunity to check internal links that no longer work, using a search function. The categories I can do by hand.

transblawg.eu - has been running since 2003. I should think there are about 2500 entries that I haven't manipulated. I have already gone back to 13 January 2007, and done a few in November 2006, but the rest I haven't touched.

It would be great if someone could do this for me. I still have to do other things myself anyway, like a redirect, and before I do the redirect I have to look at this old archives directory that I moved to the new site, because it's full of old MT stuff and one of the pages is appearing where in my opinion an error page should be or I should put a redirect.

Margaret

[Zirbelholz]

The latest situation is that I created an SQL dump, found Crimson Editor, which shows the file in a much nicer format than the Windows editor, as everyone presumably knows, and changed the image files directory.

When I wanted to reimport the file, I got an error message. Investigation at domainfactory indicates that their technical department prefer to upload big dumps themselves.

I will therefore see if I can do more to the dump before I ask the technical department to upload it, since I wanted to make other alterations and I don't want to keep troubling the technical department every time I experiment.

Margaret

[Zirbelholz]

Garvin, the provider has now sorted out my photo directory for me. This was the easiest solution when I made an error in the SQL dump but the dump is so big that df insist on reimporting it themselves (which is a bit irritating - despite making a mistake this time, I wish I had the option to do it when I want to).

This leaves the bq. to blockquote problem, which is a bit tricky as there are sometimes several paragraphs, and the lack of comments. I think I might as well do these gradually by hand myself, since I think I need to do the comments by hand.

Thanks for offering.

Margaret