Mark's Blog

I am currently ill, so don’t expect me writing anything creative here. Instead enjoy the writings at u9tupfing, start here:

• Erotische Komplimente – eine sensible Angelegenheit
• Anleitung zur aufrecht geheuchelten Zuversicht

But be warned, German humor ahead!

Tags: german, humor

I’ve found the Wordpress plugin Trackback Validator being effective. It simply checks whether my blog entry is really referenced in the page trackbacking – that’s what you should expect in legitimate trackbacks and what spammers often don’t do. Trackbacks missing that reference are immediately marked as spam.

As I let myself notify about new comments and Wordpress treats trackbacks as such, I receive emails with an excerpt of the spam-text. The plugin doesn’t surpress them. Annoying.

So, here’s a workaround for Apache to deny posting by clients with known User Agent strings:

BrowserMatch "^\-$" is_a_robot
BrowserMatch "^Jakarta" is_evil
BrowserMatch "^libghttp\/1" is_evil

<Directory />
<LimitExcept GET>
        Order deny,allow
        Deny from env=is_evil
        Deny from env=is_a_robot
</LimitExcept>
</Directory>

Tags: anti-spam, apache, spam, trackback, wordpress

Since my ATI 4850 graphics card is not only able to output video but also sound by HDMI 1.3 and my really old subwoofer seem to fail soon, I consider a major generation change in my sound hardware.

The Yamaha RX-V 663 specs read nice. This AV-receiver is able to play sound provided by the HDMI output, supports uncompressed Dolby True HD and DTS-HD. As a plus, should I later buy an bigger TFT display the Yamaha will be able to upscale s-video and composite video to the digital output! – Unfortunately it costs a bit too much for my budget. But within six months price has already fallen from 580€ to 400€…

For the speakers you should really take a look on Teufel’s “b-ware” to save some money. It is said that these refurbished/returned/cosmetically defective products are often at nearly mint condition. That’s what I will probably do to complement my existing equipment.

But, ATI graphics card seem to have issues with AV-receiver. Follow these threads:

• http://forums.whirlpool.net.au/forum-replies-archive.cfm/1005544.html
• http://www.avsforum.com/avs-vb/showthread.php?p=14164770#post14164770

Tags: ATI, audio, receiver, speakers

Yesterday I have been asked to help a good friend of mine with migrating her Serendipity and CuteNews blogs to Wordpress. As it were rather trivial installations, with only one author and no tweaks in the installations themselves, the whole work was limited to transform database tables and, in case of CuteNews, moving text-based data collections to MySQL. Here is how I did it all:

I did start with Serendipity. Of course, convertres already exist, but I have managed it by two SQL commands:

INSERT INTO wp_posts
SELECT s.id, 1 'post_author',
       FROM_UNIXTIME(s.timestamp) 'post_date', FROM_UNIXTIME(s.timestamp-2*3600) 'post_date_gmt',
       s.body 'post_content', s.title 'post_title', 0 'post_category', '' AS 'post_excerpt',
       IF(s.isdraft = 'false', 'publish', 'draft') AS 'post_status', 'closed' AS 'comment_status', 'closed' AS 'ping_status',
       '' AS 'post_password', CONCAT('old_s9y_', s.id) 'post_name',
       '' AS 'to_ping', '' AS 'pinged',
       FROM_UNIXTIME(s.last_modified) 'post_modified', FROM_UNIXTIME(s.last_modified-2*3600) 'post_modified_gmt',
       '' AS 'post_content_filtered', 0 'post_parent', CONCAT('http://example.de/diary/?p=', s.id) AS 'guid',
       0 AS 'menu_order', 'post' AS 'post_type', '' AS 'post_mime_type', s.comments AS 'comment_count'
FROM serendipity_entries s
ORDER BY s.id ASC;

INSERT INTO wp_comments
SELECT s.id 'comment_ID', s.entry_id 'comment_post_ID',
       s.author 'comment_author', s.email 'comment_author_email', s.url 'comment_author_url', s.ip 'comment_author_IP',
       FROM_UNIXTIME(s.timestamp) 'comment_date', FROM_UNIXTIME(s.timestamp-2*3600) 'comment_date_gmt',
       s.body 'comment_content',
       0 'comment_karma', IF(s.status = 'approved' , 1, 0) 'comment_approved',
       '' AS 'comment_agent', '' AS 'comment_type', s.parent_id 'comment_parent', 0 'user_id'
FROM serendipity_comments s
WHERE s.id < 188
ORDER BY s.id ASC;

Comments above 188 were spam, hence the WHERE clause. Then, I needed to update the comment count by:

UPDATE wp_posts p SET p.comment_count = (SELECT COUNT(*) FROM wp_comments c WHERE c.comment_post_ID = p.ID AND c.comment_approved = 1)

CuteNews stores its data in textfiles, with (roughtly) one blog entry (“news”) by line. Using MySQL’s Query Browser and its Regex Text Importer, you can obtain all entries as matchgroups by these regexp along with the following SQL commands to import them to previously created MySQL tables:

/* news: (\d+)\|([^\|]+)\|([^\|]+)\|([^\|]+)\|\|\|\|\| */
INSERT INTO cutenews_news(created, author, title, body)
VALUES(FROM_UNIXTIME($RegEx1.1), '$RegEx1.2', '$RegEx1.3', REPLACE('$RegEx1.4', '{nl}', "\n"));
/* comments: (\d+)\|([^\|\>]+)\|([^\|]+)\|([^\|]+)\|([^\|]+)\|\| */
INSERT INTO cutenews_comments(commented, author, email, IP, comment_text)
VALUES(FROM_UNIXTIME($RegEx1.1), '$RegEx1.2', '$RegEx1.3', '$RegEx1.4', '$RegEx1.5');

cutenews_news has to receive a primary id “news”, cutenews_comments has to have an additional column “comment” (INT PRIMARY AUTO_INCREMET) as well as “news”. By the latter we link comments and news as follows:

UPDATE cutenews_comments c SET c.news = (SELECT MAX(p.news) FROM cutenews_news p WHERE (p.created <= c.created))

Copying the Serendipity to Wordpress instructions for transforming tables we can finish migration from Cutenews:

INSERT INTO wp_posts
SELECT n.news + 96 'ID', 1 'post_author',
       n.created 'post_date', DATE_SUB(n.created,INTERVAL 2 HOUR) 'post_date_gmt',
       n.body 'post_content', n.title 'post_title', 0 'post_category', '' AS 'post_excerpt',
       'publish' AS 'post_status', 'closed' AS 'comment_status', 'closed' AS 'ping_status',
       '' AS 'post_password', CONCAT('old_cn_', n.news) 'post_name',
       '' AS 'to_ping', '' AS 'pinged',
       n.created 'post_modified', DATE_SUB(n.created,INTERVAL 2 HOUR) 'post_modified_gmt',
       '' AS 'post_content_filtered', 0 'post_parent', CONCAT('http://example.de/diary/?p=', n.news+96) AS 'guid',
       0 AS 'menu_order', 'post' AS 'post_type', '' AS 'post_mime_type',
       (SELECT COUNT(*) FROM cutenews_comments c WHERE c.news = n.news) AS 'comment_count'
FROM cutenews_news n
ORDER BY n.news ASC;

INSERT INTO wp_comments
SELECT NULL 'comment_ID', c.news + 96 'comment_post_ID',
       c.author 'comment_author', c.email 'comment_author_email', '' AS 'comment_author_url', c.ip 'comment_author_IP',
       c.commented 'comment_date', DATE_SUB(c.commented,INTERVAL 2 HOUR) 'comment_date_gmt',
       c.comment_text 'comment_content',
       0 'comment_karma', 1 'comment_approved',
       '' AS 'comment_agent', '' AS 'comment_type', 0 'comment_parent', 0 'user_id'
FROM cutenews_comments c
ORDER BY c.news ASC;

Voila!

Tags: blog, convert, MySQL, regexp, SQL