Digital Ninja

This little gem goes down as another of those "I've been using CF this long and never knew this important thing" scenarios.

When you're performing a query-of-query and using a LIKE operator, the match becomes case sensitive.ﾠ Here's an example.ﾠ Let's say you have a table, "chores" with these records in it (one column):

First start with a standard select out of the database.ﾠ Of course, this doesn't make sense, it's just for the example.

Now we'll select from that query (the famous query-of-query) but use the LIKE operator.ﾠ The objective is to get all the records with some variation of "wash" in them.ﾠ We expect to see records with ID 1 and 2, however, the following will only yield ID 2:

<cfquery name="qChores" dbtype="query">
SELECT ID, name
FROM qOriginalQuery
WHERE (name LIKE '%wash%')
</cfquery>

In order to retrieve records with ID 1 and 2, as you might have wanted, you can force everything to upper case to eliminate any value differences:

<cfquery name="qChores" dbtype="query">
SELECT ID, name
FROM qOriginalQuery
WHERE (UPPER(name) LIKE '%#UCase(wash)#%')
</cfquery>

Credit for this answer to my frustrations goes to my Google result, Just Skins.ﾠ Thanks!

I'm on a rampage to find the best, most affordable data modeling tool right now. My immediate need is MySQL, but I should probably remember that my decisions made during my freelance projects greatly influence my decisions made at the office where we use SQL Server 2005. But I've got to keep realistic here - I can't afford to invest in some of the massive architecting solutions I've found here and there. Speaking of solutions, let's have a run-down of where we are.

Requirements

I'm looking for a solution that can perform the following:

• Entity relationship diagrams (ERD)
  
• Reverse-engineer existing schemas into diagrams
• Generate new or alter existing schemas based on diagrams (sync)

Optionally, I'd like the following additional features:

• UML class diagramming
  
• Object Relational Mapping (ORM) that integrates the UML with the ERD
• Integration with source control, preferably Subversion

I don't need anyone to tell me that these are some pretty high and mighty features. At the very least, it's hard to find all of these in a package that costs less than four digits. So far I've looked at these two products with some level of detail.

MySQL Workbench

This is a nice product, especially because it comes straight from the heart of MySQL land. Best of all there is an open source version and a standard edition which only costs $99. This is extremely affordable. Workbench provides the reverse engineering and forward schema generation, which is really the most important pieces. The down side is that this is only for MySQL. I can't start jumping DB platforms. I've got to admin though - the diagrams it produces are beautiful and very easy to read! I give this one a thumbs up but with a regret about its targeted platform and no UML/ORM.

DB Visual Architect (by Visual Paradigm)

I'm pretty overwhelmed with this piece of work. I'm not officially a database engineer and I've never been fully immersed in a traditional SDLC. With my experience lacking in those areas I find myself falling behind with some of the terminology that's merely labeling some of the icons. I didn't know where to begin at first.

I made a few tables in an ERD and had the application push the tables out to my development DB server. It was almost too easy! I still need to take a closer look at the UML and ORM features, but I can tell they look pretty in-depth. There is also support for many types of major RDBMS players out there including SQL Server of course. Big plus right there. I think the only down sides to this program is that it's about four times the application I really need and there is a $700 price tag. Which is probably, as I consider it, an amazing deal.

Oracle SQL Developer

Nice product, completely Java-based, and its features out of the box seem to topple the MySQL Query Browser. I did have to manually install the latest JDK and the MySQL JDBC connector in order to get it to work. However, did I mention this is a freebie? I think that would have put it above the class, but it doesn't generate schema (only DDL) and it's a bit sluggish in the UI. My PC is a little old, so it's a good test for these things. There isn't any UML or ORM, either.

There was one thing with this one, though, that blew me away. They built SVN and CVS capabilities right into the application! You can even create a Subversion repository from within the program. Very nice, Oracle!

ﾠ

Well, I think that sums up my roundup for tonight. I want to take a look at some of the Redgate tools out there, I hear they have a wealth of good solid solutions. I'll know my search is over when I can feed a schema to Squidhead and have all my scaffold and beans when I return from making a coffee.

Here's just another example of why I personally choose to use a blog - look how someone else's blog helped me! ﾠMy database and MySQL knowledge just took a jump.

Read more...