Mango 1.4.3 has been released, and is available via automatic update. As far as I know, the only difference between 1.4.2 and 1.4.3 is a fix for a bug I reported with the new logging functionality.
Version 1.4.2 of Mango introduced logging to the database and gets rid of the old web-accessible log files. Users of LogViewer will need to update the plugin in order to manage their logs.
This update to LogViewer reflects some changes to the way logging works. In particular, there are now several different types of errors, not just two. For now, all errors are displayed together -- but I may change that later to let you specify just one type to display. I may also add pagination, if there is a demand for it.
- Plugin:
- LogViewer
- Version:
- 1.1
- Requires:
- Mango Blog 1.4.3+
- Auto-install URL:
- http://fusiongrokker.com/get/LogViewer
Users still on an older version of Mango (pre 1.4.2) can still download LogViewer 1.0.
LogViewer source is now hosted on GitHub.
Posted in
ColdFusion |
Mango |
My projects
| 7 Responses
February 04 2010
This afternoon I have been having a bit of a group discussion on the perils of INNER JOINs in SQL. It started with a tweet that Inner Join's are dangerous and you should use them with caution. A bunch of people asked me what exactly I meant, and of course it's difficult to get that across in chunks of 140 characters or less, which brings us to the present and this blog post.
Consider that you have a relational database setup to store survey responses. Each respondent creates 1 ResponseSet, which gets a ResponseSetId, and then from there you can build up responses to the various questions. There are N questions. Each question can have M responses, all of them free-form text. There are probably a hundred ways to lay out the database to support this, but in the case of the database that inspired the tweet, I've got a Response table, with a foreign key to the ResponseSet table, which has a foreign key to the Survey table. A response belongs to a response set, which belongs to a survey.
In your mind, write some SQL to join these tables and get back the questions and answers for a given ResponseSet for a report (say, ResponseSetId 473240432, if that helps you think). Here is the first thing that pops into my head:
SELECT
QuestionId
, ResponseText
FROM
tblResponseSet rs
INNER JOIN tblResponse r ON rs.ResponseSetId = r.ResponseSetId
INNER JOIN tblQuestion q ON q.QuestionId = r.QuestionId
WHERE
rs.ResponseSetId = @responseSetId
GROUP BY
q.QuestionId
ORDER BY
q.QuestionSort
That's great, right? Unfortunately, no. At least, not in my case. Even assuming that all questions are mandatory (1+ responses required), this does not account for other buggy software I've written (we all do it!) that saves, copies, or modifies the data that may result in blank responses or no responses for a given question. Rather to the point, what I would rather see in the case of this poorly-maintained theoretical data (as modified by my buggy code) is the rows I expect, but with NULL values for missing responses.
In the event of the survey above, it would be more helpful to the developer trying to debug the application to know that NULLs are coming back as survey responses, than it would be to know that the question isn't being included in the result set. The join for the question data is fine -- at least in as much as it isn't the cause of this theoretical row being dropped -- it's the join on responses that's causing the problem. In effect, by eliminating the row altogether, we're creating a false-negative, or the appearance of a bug that doesn't exist. (The question table should be joined via outer join as well.)
Using this method of querying the data, each row in the result set will give the question and 1 of its responses. The question will repeat with each different response, making it easy to use the group attribute of ColdFusion's cfoutput tag to display each question with the various responses to it for the given Response Set. However, with the above query, if there are no responses for ResponseSetId 473240432 and QuestionId 42, then QuestionId 42 is dropped from the result set. That's a Bad Thing (TM)!
In essence, plan for the worst but hope for the best. Whenever I write a join condition, instead of asking myself "How is this data relationship supposed to work?" I should be asking myself "What do I want to happen if some data turns up missing?" By writing an inner join, I'm answering the second question with "drop the row". The answer I wanted to provide for that question was to "show nulls", which is written in SQL with an OUTER JOIN.
That is the inherant danger of INNER JOINs of which I was speaking.
Posted in
Best Practices |
Databases
| 4 Responses
February 02 2010
In January of 2009 I wrote down some goals for the year, so I figure it's appropriate to look back and see what I accomplished from that list and where I fell short; and then set some new goals for this year.
- Learn Transfer and CF9 Hibernate
Status? Fail!
Admittedly, this was a pretty ambitious goal. And while I didn't manage to learn both, I have learned the basics of CF9's new baked-in Hibernate functionality. That's a start. I won't say the lesson here is to be less ambitious, but perhaps a little more realistic?
- Finish Grub 1.0
Status? Fail miserably!
Alas, time got the best of me. While I haven't finished it, I am still working on it. I've renewed my interest by switching to using Git for version control for the project, which makes working on the train much easier. Now if only I could make all of those books, movies, and TV shows that consume my time on the train less interesting...
- Start Refactoring Grub
Status? Success!
Indeed, I have started refactoring. In addition to moving over to Git for version control, I've been rewriting the model behind Grub to use CF9's Hibernate ORM. Luckily, continued work on this goal will go hand in hand with continued work on Goal #1 above.
- Get started with Flex
Status? Great Success!
As a matter of fact, as of last week, a Flex application that I wrote from the ground up is running in production. I have another on the back burner while finishing up the first project, and it seems like I'm really getting the hang of Flex. I look forward to more projects that utilize it.
So yes, I failed. In some cases, spectacularly! Luckily, it's our failures that we learn the most from, not our successes. I can only hope to continue failing so spectacularly, because when I take stock of the last year I feel as though I've learned an incredible amount.
Goals for 2010:
- Get more better at ORM
Having a basic understanding shows me the power behind this beast. Now it's time to turn it up to 11 and see how I can increase my productivity.
- Ship Grub
Whatever the version number, the version control system, or the architecture of the model... just make it work, get it online, and make it public. Don't stress over the design so much, you can fix that in the next iteration. SHIP IT! SHIP SHIP SHIP SHIP!
- Continue to grow Philly CFUG back to a large regular attendance
I don't have any grandiose plans for how to accomplish this, I'm just going to try hard to plan compelling content, communicate well, and hope that if I build it, they will come.
- Get very comfortable with Git
The more I work with and learn about Git, the more I like it over Subversion. I don't want to say that it's a hammer capable of driving screws, but at this point I don't see a compelling reason to continue to use SVN, except when you are sharing a repository with other developers who don't know (or want to learn) Git. If nothing else, the GUI's and tools available for SVN are far more mature and compelling than those available for Git, but that will change over time.
Posted in
Meta
| 1 Response
February 01 2010