2006-09-09

How to remove comments from Roller 2.0-incubating

It's easy to prohibit people from adding comments to blog entries, but Roller 2.0 doesn't make it easy to delete them. Here's how I do it.

I installed Tomcat under /opt/apache-tomcat, which I'll call TOMCAT_HOME, and I installed Roller under TOMCAT_HOME/webapps/blog, which I'll call ROLLER_HOME. When I installed Roller, I used file ROLLER_HOME/WEB-INF/dbscripts/mysql/createdb.sql to create the MySQL database schema. In this file, I find:

create table roller_comment (
...

This looks like the table from which I will delete rows. Now I need to remember my MySQL username and passwsord for Roller. File TOMCAT_HOME/conf/Catalina/localhost/blog.xml contains this entry, in which I tell Roller my MySQL credentials:

 driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/roller?autoReconnect=true&useUnicode=true
&characterEncoding=utf-8&mysqlEncoding=utf8"
username="MyRollerUser"
password="MyRollerPass"
maxActive="20"
maxIdle="3"
removeAbandoned="true"
maxWait="3000" />

Then I log in to MySQL:

$ mysql roller -h localhost -u MyRollerUser -pMyRollerPass
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 111 to server version: 4.1.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

To see all the comments, I type:

mysql> select * from roller_comment;
...
11 rows in set (0.00 sec)

mysql>

In this case, the only comment worth keeping has id ff808081084877d1010c56353ddc02c4. I test a WHERE clause that I will use to delete all the other comments:

mysql> select * from roller_comment where id != 'ff808081084877d1010c56353ddc02c4';
...
10 rows in set (0.00 sec)

mysql>

The result is correct, so I delete the offensive comments and verify the result:

mysql> delete from roller_comment where id != 'ff808081084877d1010c56353ddc02c4';
Query OK, 10 rows affected (0.00 sec)

mysql> select * from roller_comment;
...
1 row in set (0.00 sec)

mysql>

All done. Now I can share my blog with the kids again.

No comments:

LinkWithin

Related Posts with Thumbnails