Tag: databases

Why you should teach yourself WordPress taxonomies

Alex King has a blog post describing the major differences between using custom fields and taxonomies in WordPress.  If you are familiar with the WordPress database, and if you think about it for a couple of minutes, you’ll probably realize why taxonomies are a better choice than custom fields for those situations where you can use both.

The reason to favor a custom taxonomy in these situations has to do with the WordPress database structure. Queries by taxonomy are well optimized as this is a primary front-end presentation feature in WordPress core. Conversely, querying by custom field key and value is slow. The value column in the custom field table is not indexed – you are basically doing a search through data that is not intended for that purpose.

The problem is that too many people, even those who are very well familiar with WordPress database structure, never really think about it.  And since custom fields are an older way of solving problems, many go for them without proper consideration.

MySQL prompt

I’ve been using MySQL for quite a few years by now, but only today I learned that it is possible to define MySQL prompt.  As per this blog post, all it takes is a couple of lines in .my.cnf file with something like:

[mysql]
prompt="\u@\h (\d)> "

That alone will help to prevent a gadzillion of destructive mistakes when you think that you are working with one database, when, in fact, you are working with a totally other.  On top of that, the blog post suggests using rlwrap tool, with which one could add some colors to the prompts as well.

CakePHP GraphViz Models

I have completely and totally rewritten my old script that generates a graph of CakePHP models and their relationships.  Instead of pasting the code in here, I pushed all of its development to GitHub where it now enjoys a new repository.  Please have a look, try it out, and let me know if it does or doesn’t work for you.

The major changes in this version are:

  • Rewritten as CakePHP Shell instead of being a standalone madness script.
  • Got rid of all dot markup. Utilized Image_GraphViz PEAR package instead.
  • Added support for old and new CakePHP versions (1.2, 1.3, and 2.0).
  • Added option for using only real models (via className property of the relationship) or sticking with the old behavior.
  • Added a bunch of options for tweaking GraphViz output.  And now it’s obvious where to edit them, in case you need more.
  • Improved the styling of the graph a bit – fonts and colors.
  • Improved documentation.

As a side effect improvement, now that it is a native CakePHP Shell, it’s trivial to add to your project build process.

MySQL export CSV into OUTFILE triggers “access denied” error

I came across a weird problem today.  Gladly, the web is full of solutions, but I’m going to post this anyway, just to have it nearby for the next time.  I needed to export the results of some query into a CSV file directly from MySQL.  I prepared my query, made sure that I can see the correct results and than changed it to export into the file. The query looked something like this:

SELECT id, field1, field2, field3
INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM data_table
WHERE field1 = 0;

I was quite surprised to find myself staring at:

ERROR 1045 (28000): Access denied for user ‘db_user’@’localhost’ (using password: YES)

My database user definitely had full access to the database.  I definitely could see the results of the query before the redirect to the file.  And I definitely had enough permissions to create files in /tmp directory.  And on top of that, I’m sure I used MySQL export functionality a gadzillion times and it always worked without any problems.   What’s wrong this time?

A quick search around got me to this Stack Overflow question.  Apparently, database user has to be given a FILE privilege, which is global (not per-database).   Here is what I did to solve the problem (you’ll need to use MySQL root user of course):

USE mysql;
UPDATE user SET File_priv = 'Y' WHERE User = 'db_user';
FLUSH PRIVILEGES;

I think that it worked for me before was because I exported as root, who does have this permission set to ‘Y’.

ORM Designer

Here is a tool that might help you with your MVC framework, like CakePHP, Symfony, and others – ORM Designer.  In essence, it is a graphical user interface for drawing a visual representation of your project (such as an Entity Relationship Diagram (ERD)) and than converting it into the code.  You can specify which framework and which  ORM you want to use and it will generate the appropriate bits and pieces.  What’s even more interesting is that it has import functionality, which means that you can start using it with an existing project.  Here is the video that shows and explains more.

[youtube=http://www.youtube.com/watch?v=FNlmU6zX5Ug]

Of course, I got excited about it, downloaded and installed.   Two things that disappointed me were:

  1. It’s a native Windows application, which runs on Linux through the wine emulator.  While it works fine, I’d much prefer a native application that I could integrate with the rest of my development environment.
  2. CakePHP import is not supported at this time.

Other than that though, it looks very promising.  I’ve seen quite a few applications that help with database design, and ORM Designer stands well in that row.  You can create entities, define fields, specify indexes, and associate entities with each other using relationships.  Many-to-many relationships are supported, as are entity inheritance.  While inheritance does make it for a bit more complicated structure of the project (with app/models/base/ folder for CakePHP), it’s very nice to have such support for bigger, more complex projects.

The project is commercial, with a 14 days evaluation version available for download. If you like it enough to buy, the price is very reasonable – 99 EUR per license.

Try and see if you like it, and provide some feedback to the guys who are developing it.  ORM Designer has all the chances of becoming an extremely useful tool and since it is still in its early development, your feedback would be of the most value.