Mokka mit Schlag » Databases

Sometime over the last couple of weeks while I was in Cuba, MySQL corrupted the wp_posts table in my WordPress database. At first I thought I was hacked, but it looks like the database was just corrupted:

mysql> check table wp_posts;
+---------------------------+-------+----------+----------------------------------------------------------------------------------+
| Table                     | Op    | Msg_type | Msg_text                                                                         |
+---------------------------+-------+----------+----------------------------------------------------------------------------------+
| elharo_wordpress.wp_posts | check | warning  | Table is marked as crashed                                                       | 
| elharo_wordpress.wp_posts | check | warning  | Size of indexfile is: 1148225      Should be: 467968                             | 
| elharo_wordpress.wp_posts | check | error    | Can't read indexpage from filepos: 56320                                         | 
| elharo_wordpress.wp_posts | check | Error    | Incorrect key file for table './elharo_wordpress/wp_posts.MYI'; try to repair it | 
| elharo_wordpress.wp_posts | check | error    | Corrupt                                                                          | 
+---------------------------+-------+----------+----------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> repair table wp_posts;
+---------------------------+--------+----------+----------+
| Table                     | Op     | Msg_type | Msg_text |
+---------------------------+--------+----------+----------+
| elharo_wordpress.wp_posts | repair | status   | OK       | 
+---------------------------+--------+----------+----------+
1 row in set (0.34 sec)

mysql> check table wp_posts;
+---------------------------+-------+----------+----------+
| Table                     | Op    | Msg_type | Msg_text |
+---------------------------+-------+----------+----------+
| elharo_wordpress.wp_posts | check | status   | OK       | 
+---------------------------+-------+----------+----------+
1 row in set (0.08 sec)

I think I’ve fixed this now, but do let me know if you see any lingering problems. Hmm, it does look like the categories may be borked too. I’ll look at that next.
(more…)

The database schemas for my current project are getting hashed out, mostly in Visio. This is pretty but not very automatable. I’d like to see if we can follow a more agile, iterative approach to database development. In particular, I’d like to be able to check the database definition into source code control and build the whole thing, including database tables and sample databases for testing out of Ant. Requirements include:

1. Supports (at a minimum) MySQL and Derby
2. Can be read by Java and Python
3. Allows for inserting of data for unit testing; i.e. not just table definitions

It also wouldn’t hurt if it could reverse engineer existing SQL databases.

I’m tempted to write my own, probably using XML, but surely someone has already done this? I haven’t found a lot though. What I’ve got so far are these, none of which really meet the requirements:
(more…)

Is anyone aware of work on fundamentally safe database APIs for server side programs that completely avoid the possibility of SQL injection? What I envision is a somewhat limited API that does not execute raw SQL statements or provide any facility to do so. Instead you’d set up something like this:

Statement s = database.getSelectStatement();
s.setTable("customers");
s.addField("email");
s.addField("telephone");
s.addCondition(
  new EqualsCondition("id", "p17")
);
ResultSet r = s.execute();

The library would turn this into the usual SQL statement

SELECT email, telephone FROM customers WHERE id = "p17"

The library could verify the individual parts of the query before submitting it to the database. If you passed a string like "id = \"p17\" OR true; DELETE * FROM customers; SELECT * FROM customers WHERE " to EqualsCondition() it would throw an exception.
(more…)

To summarize what has gone before, I now have headless, network access to a Mac X86 Mini, and have successfully installed Apache 2.0.55. The next step is to install MySQL, PHP, and WordPress; then copy The Cafes and elharo.com from the old server to the Mini.
(more…)

in XML Schema there is a gMonthDay type which represents dates such as October 31; that is, Halloween but not in any particular year. How is this typically handled in SQL? The date type requires a year? Do I just have to define separate INT fields for month and day?

A related question: how are date ranges handled? e.g. the range from Halloween to Christmas (10-31 to 12-25)? Two columns for the start and two for the end? Or a start date followed by a number of days? Leap years make this tricky though.

And still a third question: ranges may extend for the entire year or a part thereof. Furthermore they may extend across the New Year’s boundary. e.g. their could be a range that goes from 11-3 to 3-15. How is that handled?

And finally, to really complicate matters the range may be discontiguous. That is, it could cover March 15 to May 15 and September 15 to October 15, but not the intervening dates. However, the potential discontiguity is limited. In my application, there are never more than two contiguous ranges within a year.

I can hack this together, but surely I’m not the first person to need something like this. Has anyone seriously worked through a problem like this and published a detailed analysis of the different approaches for modelling this in SQL tables, and the advantages and disadvantages of each? References appreciated.

XML schemas support a union type. For instance, you can say that an element must be an int or one of the strings “large”, “small”, or “huge”. Does SQL have any equivalent? That is, is it possible to type a field as containing either an int or one of an enumerated list of strings? Or do I just have to create two fields, one of which is null? How is this customarily handled in SQL?