SQL | Hot Dorkage

Zend Framework and Doctrine

By Colleen, on November 19th, 2009%

doctrine_icon Well it’s looking like Doctrine is the heavy weight data mapping solution of the lot. Zend Framework was often criticized for having no serious Model methods. Models was just sort of where you put your nuts and bolts database code. Some folks like the structure of the Active Record model that you see in Rails some other PHP frameworks. Sure it makes it dead easy when your tables map pretty straightforwardly onto your objects but makes it harder to have complex objects involving many tables. Zend was working on their own Zend_Entity package to remedy this, but they have abandoned it in favor of integrating Doctrine. The Zend Roadmap for the future calls for tight integration of Doctrine with the framework, so there will probably be some additional automatic linkage, with the view to the framework user’s code being smaller. But both ZF 2.0 and Doctrine 2.? are for PHP5.3, which is some time down the pike for ordinary folks. On the ZF Wiki there is a proposal to integrate current versions, at least to some extent. The symfony camp are hollering that Doctrine automatically “just works” in symfony, and that’s great. But for now you do have to do a little work to integrate it with ZF.
Continue reading Zend Framework and Doctrine

Instant Access to 5 million row CSV for MySQL

By Colleen, on August 6th, 2009%

mysql I was investigating the CSV storage engine for mysql, having never used it. I found out, much to my relief, that it’s a relatively new thing — went standard with 5.1, but certainly not in the kits of luddites like me who have to stay four or five versions back because we can’t afford downtime caused by unexpected glitches with new releases.

The CSV engine is as I expected: It allows MySQL to directly access CSV files. There are probably boocoo performance issues and I don’t know how easy it is to later convert a table with its engine declared as CSV to something more standard such as MyISAM.

I think the big plus of the CSV engine is that you can quickly and easily leverage your existing CSV files from excel, as long as you are not looking for excellence in relational, normalized database design. Having been out in the trenches, I would guesstimate that 98 percent of MySQL users would not know normalized form if it bit them in the butt and they still manage to get work done. The CSV engine would probably give you a speed advantage for quick and dirty projects but I don’t think it is a great idea for a long standing, constantly updated database.
Continue reading Instant Access to 5 million row CSV for MySQL

MySQL CSV’s with column headings — Part 1

By Colleen, on August 4th, 2009%

mysql In a previous post I discussed the stumbling blocks and security concerns that the mysql documentation doesn’t tell you about having a mysql user create csv files on the filesystem.

I ran into yet another issue. In order to do what I want with these csv’s, they have to have column headers. I thought surely by now MySQL must have come up with some syntax in the query to do this, but Paul DuBois assured us all in a 2006 forum post, that there is no such thing. I thought maybe Zend Framework might have such a thing in it’s DB class, but I didn’t locate one. Correct me if I’m wrong–Zend Framework is huge, and I might have missed it.

So, what to do? We have several options:
Continue reading MySQL CSV’s with column headings — Part 1

How to make MySQL spit out CSV’s

By Colleen, on July 23rd, 2009%

mysql Don’t you hate it when you know you solved a problem three years ago but you can’t remember what the solution was? I ran into that recently. I was trying to make mysql generate a csv directly from query results. I remembered that you can do it, and I had an example of the correct syntax to use in my old code, and I had a query that did it just fine when running in the mysql CLI as root. I had to piece together the answer from several different sources; and none of them told the whole story. So here is the full story, along with all the stumbling blocks that I know of.
Continue reading How to make MySQL spit out CSV’s

MySQL InnoDB Create Table composite key problem and solution

By Colleen, on February 20th, 2009%

I was getting the nitpicky error 150 when trying to create an InnoDB table with foreign keys. I had very carefully checked several times that all the key types and names matched exactly including UNSIGNED (which is what gets most people), and that I had an index on every foreign key as required by my version of mySQL. That covers the majority of Error 150 situations but not mine. I was failing the “First in Composite KEY” constraint, which is easy to overlook.
Continue reading MySQL InnoDB Create Table composite key problem and solution

Column Comments in MySQL

By Colleen, on January 12th, 2009%

A while back I was lamenting that table comments in MySQL get overlooked, and did a post on it, both how to create them, how to update them, and how to view them. The fact that my table comment post remains quite popular indicates that mySQL hasn’t SEO’d their pages on it thoroughly enough, and people are obviously looking for information on it. So… I thought I should follow with a column comment post. As a matter of fact in mySQL you can also comment at the column level, and probably should whenever the function of a field is not obvious.
Continue reading Column Comments in MySQL

How to create and maintain table comments in MySQL

By Colleen, on August 7th, 2008%

How to create, see and update table comments so your database scheme is somewhat self documenting . . . → Read More: How to create and maintain table comments in MySQL

Hot Dorkage