Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

rjbs (4671)

rjbs
  (email not shown publicly)
http://rjbs.manxome.org/
AOL IM: RicardoJBSignes (Add Buddy, Send Message)
Yahoo! ID: RicardoSignes (Add User, Send Message)

I'm a Perl coder living in Bethlehem, PA and working Philadelphia. I'm a philosopher and theologan by training, but I was shocked to learn upon my graduation that these skills don't have many associated careers. Now I write code.

Journal of rjbs (4671)

Tuesday April 12, 2005
08:34 AM

choosing my poison: sqlite problems

[ #24143 ]

I really, really like DBD::SQLite, but I wish it didn't have such irritating little quirks. I'm not sure how much of the problem is SQLite and how much is DBD::SQLite, but it drives me batty. Here's today's example:

In Rubric, you can query for entries with a given set of tags, or you can specify you want exact_tags, and it queries for entries with /only/ those tags. I do that by using the normal "has these tags" query and then checking the number of total tags against the requested set. It's simple, though maybe not elegant.

I had written the count-checking clause like this:

        (SELECT COUNT(tag) FROM entrytags WHERE entry = entries.id) = $count

This should have worked, but with DBD::SQLite I kept getting the error that there was no such column as entries.id. I'm fairly certain that the problem is that the subselect is not run in the context of the containing select, so it can't see the entries table against the tuples of which it should be comparing entrytags tuples.

I've had to code around this subselection bug once or twice before, and I was sick of it. I saw there was a newer DBD::SQLite, so I updated.

Then another bug showed up. In Rubric::User, I say the following:

__PACKAGE__->set_sql(tags_counted => <<'' );
  SELECT DISTINCT tag, COUNT(*) AS count
  FROM entrytags
  WHERE entry IN (SELECT id FROM entries WHERE user = ?)
  GROUP BY tag
  ORDER BY tag

This has always worked fine, but with DBD::SQLite 1.08 it seemed to stop quoting usernames, so I get DBIx::ContextualFetch complaining that it's getting a non-numeric value. I don't know just what's going on there, but something that was working now isn't. Argh!

So, I just went back to 1.07 and rewrote my count as follows:

    id IN (SELECT entry FROM entrytags GROUP BY entry HAVING COUNT(tag) = $count)

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • SELECT DISTINCT tag, COUNT(*) AS count
    FROM entrytags
    WHERE entry IN (SELECT id FROM entries WHERE user = ?)
    GROUP BY tag
    ORDER BY tag
    That works for me. When you say it doesn't seem to quote user, do you mean user has spaces, or some other weird characters? I did try spaces in 'user' and it still seemed to work. Got any specific data?
    • I mean that the value being passed to replace the ? as a bind parameter is potentially not being quoted. I need to investigate further to be sure what's happening.
      --
      rjbs
  • How about keeping a window open on SQL As Understood By SQLite [sqlite.org]? expression [sqlite.org] shows that its parser expects subselects only in conjunction with IN, not =. (Is the latter even valid in SQL in general?)
    • I'll definitely keep an eye on that... but it doesn't explain why the query seemed to work as written in DBD::SQLite 1.08, which uses 3.1.3 instead of 3.0.8. 3.1.0 introduced correleated subqueries, which is what I want. The lang_expr page would, then, seem to be wrong.
      --
      rjbs
    • ...oh, and as for valid SQL! I'm not sure. I found some SQL92 validator that claimed that it was. It works in MSSQL and pgSQL. I just expected it would work in SQLite -- and it does, in the more recent versions.

      What I really need to do is track down the problem with the query under DBD::SQLite 1.08.
      --
      rjbs