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

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.
  • I prefer to name my primary key column `id` and use `foo_id` as the name for foreign key columns. Then I use `ON` explicitly.

    LEFT JOIN dead_things ON thing.id = dead_things.thing_id

    Overall the query becomes more verbose, but I prefer it that way. It makes my schemata much more easily readable when columns immediately reveal themselves for what they are by their name.

    • That still doesn't fix the bug :)

      • I don't actually see the bug, yet, but I'm still a little groggy this AM. I do agree with your principle. But you may have to help me along to see this specific bug. :)

        --
        J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
        • Major Hint: what are we joining on and what data structure is Perl using?

        • You know, this might be a MySQL-specific bug. I don't have other databases handy on which to test this. I tried it in SQLite and the problem doesn't appear:

          ~ $ sqlite3 foo
          SQLite version 3.2.8
          Enter ".help" for instructions
          sqlite> create table one (server, name);
          sqlite> create table two (server, value);
          sqlite> insert into one values(1,'bob');
          sqlite> insert into one values(2,'ovid');
          sqlite> insert into two values(1,'one');
          sqlite> select * from one left join two using(server);
          1|bob|one
          2|ov

          • I think it's definitely MySQL specific ... it sounds like MySQL doesn't actually know how to do that type of join properly?

            But I'm confused about something else ... I thought selectrow_hashref should give you a hash where the keys are the fieldnames and the values are the values in the record. You seem to be indicating a problem where one of the values is NULL and that gets used as a key in the hash? But I can't see how that would happen, unless I'm misunderstanding the expected results of selectrow_has

            --
            J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
            • You seem to be indicating a problem where one of the values is NULL and that gets used as a key in the hash?

              No, I misunderstood. Never mind. :)

              --
              J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
      • I’m saying that you got bitten this easily because you combined two bad ideas.

        Although now that I think of it, the naming scheme I use means that any join would lead to duplicate id columns.

        Good thing I avoid SELECT * like the pest, then. Hmm, looking at some of my own source code, I find I do use the star quite a lot in the older parts. However, it’s always qualified with a table name, which explains why I never ran into your bug.

  • Some DBs would return that as "Table.Column", unless it's DBI always turning it into just "Column".

    I agree on the principle though - SELECT * should probably only be used in the SQL-Shell for testing stuff out.
    • Ah, I can see how that would be a problem, if you wound up with two fields in different tables with the same name. But I'm used to the syntax of NATURAL JOIN and didn't look up the syntax of LEFT JOIN to see if it removes the duplicated column from the output or not.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
  • In the 4gl I used, when you had to explicitly declare variables, you could declare a record 'like' a table, then 'select *' into the record. This was extremely convenient on tables with many columns. Unfortunately, that meant that you had to recompile the program if the table changed.

    And Matt is correct in that some databases return 'table.column' as the column name (as the hash key or in $sth->{NAME}), which is annoying when switching between databases.