Professional Web Applications Themes

Dynamically generating multi-table SQL - PERL Miscellaneous

Jesse Sheidlower wrote: > I'm trying to automatically generate multi-table SQL > statements from a Web environment, and am having a lot of > difficulty doing it in a way that doesn't feel kludgy to > me.[...] > > I'd be grateful for any suggestions for approaches, or > pointers to sample code, etc. Check out Tangram or Alzabo for possible solutions....

  1. #1

    Default Re: Dynamically generating multi-table SQL

    Jesse Sheidlower wrote:
    > I'm trying to automatically generate multi-table SQL
    > statements from a Web environment, and am having a lot of
    > difficulty doing it in a way that doesn't feel kludgy to
    > me.[...]
    >
    > I'd be grateful for any suggestions for approaches, or
    > pointers to sample code, etc.
    Check out Tangram or Alzabo for possible solutions.

    J. Gleixner Guest

  2. #2

    Default Re: Dynamically generating multi-table SQL

    "Jesse Sheidlower" <jesterpanix.com> wrote in message
    news:bed7hg$mqr$1panix2.panix.com...
    >
    > I'm trying to automatically generate multi-table SQL
    > statements from a Web environment, and am having a lot of
    > difficulty doing it in a way that doesn't feel kludgy to
    > me. I've looked at various sites and books, but they tend to
    > focus on situations where multi-table joins are fixed--you
    > want the same results every time, so form of the query never
    > changes.
    This may give you some ideas. I use a subroutine "sql" to return an SQL
    statement in this example.I generate the various parts of the sql statement
    depending on various conditions.
    I use the sub{..}->() inline subroutine to add extra tables into the 'from'
    statement.
    The join(" and ", grep {$_} part adds extra 'and' into the' where' clause.


    sub sql {
    return "select \$column_list
    from ipo".
    sub {return ",ipo_underwriter " if !all
    $in{underwriter}}->().
    sub {return ' where visible="Y" and ' if (!all
    $in{underwriter})||(!all $in{industry})||(!all $in{year})}->().
    join(" and ", grep {$_}
    (
    sub {return "ipo.ipo_id=ipo_underwriter.ipo_id and
    ipo_underwriter.underwriter_id in {[sql_in($in{underwriter})]}" if !all
    $in{underwriter}}->(),
    sub {return "(industry_group_no in
    {[sql_in($in{industry})]} or sub_group_no in {[sql_in($in{industry})]})"
    if !all $in{industry}}->(),
    sub {return "year(ipo_dt) in {[sql_in($in{year})]}"
    if !all $in{year}}->())
    ).
    " order by \$order_by "
    }

    gtoomey


    Gregory Toomey Guest

  3. #3

    Default Re: Dynamically generating multi-table SQL

    [email]jesterpanix.com[/email] (Jesse Sheidlower) wrote in message news:<bed7hg$mqr$1panix2.panix.com>...
    > I'm trying to automatically generate multi-table SQL
    > statements from a Web environment, and am having a lot of
    > difficulty doing it in a way that doesn't feel kludgy to
    > me. I've looked at various sites and books, but they tend to
    > focus on situations where multi-table joins are fixed--you
    > want the same results every time, so form of the query never
    > changes.
    >
    > Suppose you have the archetypal CD database. If all the
    > information is in a single table, you just have "SELECT
    > artist, album, song FROM cd WHERE..." If you don't want the
    > song, or want the label, you just drop or add it to the SELECT
    > list. But if the data is in related tables, with, say, "album"
    > as the main table, "artist" and "label" keyed to the album id,
    > and "song" tied in via a separate join table, it gets much
    > worse. If you only want the album, you have "SELECT album
    > FROM album WHERE...", but if you want the artist too, it
    > becomes "SELECT artist.artist, album.album FROM artist, album
    > WHERE...AND artist.album_id = album.id", and so forth; if you
    > want to search on "artist" and "song" only, you have to know that
    > they can only be connected by bringing in "album" as well, so
    > you end up with a four-table join.
    >
    > And this is a very simple example; real ones could involve
    > larger sets of tables with more complex relationships that
    > could be searched in more different ways.
    >
    > The way I've been working so far is to have an array
    > tables_needed and push the table names onto it as they
    > are required, and then uniquing the array at the end; and
    > also having a table with the basic WHERE clause elements
    > for the join (e.g. "artist.album_id = album.id") and
    > similarly adding that to my constructed WHERE clause. But
    > while this works, it looks ugly and is hard to maintain,
    > and I also haven't been able to figure out (except by
    > hard-coding all possible examples) how to bring in
    > intermediate required tables, or how to handle the need
    > to write certain queries as LEFT JOINs rather than INNER
    > JOINs depending on the query.
    >
    > I'd be grateful for any suggestions for approaches, or
    > pointers to sample code, etc.
    >
    > Thanks.
    >
    > Jesse Sheidlower
    this is not a perl question.

    That said,
    before posting to a database forum you may want to decide how much
    mnormalization is needed for your data, how many entries would be
    possible in your application, estimated number of hits pe rtime period
    you expect to take.

    Programming multi table joins can be a pain, but assuming you know all
    of the tables you may need, plus which user input will cause a given
    table to be needed someting along the line of the following psuedo
    code would work.

    $from = "album_table" ## assume album is needed in all cases
    $where = "" ## start blank
    $fields = "album_name" # lets get the album name

    if (user_entered_artist)
    {
    $from = $from . ",artist_table";
    }
    if ($where eq "")
    {
    $where = "Where album_table.id = artist_table.album_id";
    }
    else
    {
    $where = "and album_table.id = artist_table.album_id";
    }


    and so on (welcome to the joys of database programming)
    good luck
    JD
    John D Guest

  4. #4

    Default Re: Dynamically generating multi-table SQL

    [email]jesterpanix.com[/email] (Jesse Sheidlower) wrote in message news:<bed7hg$mqr$1panix2.panix.com>...
    > I'm trying to automatically generate multi-table SQL
    > statements from a Web environment, and am having a lot of
    > difficulty doing it in a way that doesn't feel kludgy to
    > me. I've looked at various sites and books, but they tend to
    > focus on situations where multi-table joins are fixed--you
    > want the same results every time, so form of the query never
    > changes.
    >
    > Suppose you have the archetypal CD database. If all the
    > information is in a single table, you just have "SELECT
    > artist, album, song FROM cd WHERE..." If you don't want the
    > song, or want the label, you just drop or add it to the SELECT
    > list. But if the data is in related tables, with, say, "album"
    > as the main table, "artist" and "label" keyed to the album id,
    > and "song" tied in via a separate join table, it gets much
    > worse. If you only want the album, you have "SELECT album
    > FROM album WHERE...", but if you want the artist too, it
    > becomes "SELECT artist.artist, album.album FROM artist, album
    > WHERE...AND artist.album_id = album.id", and so forth; if you
    > want to search on "artist" and "song" only, you have to know that
    > they can only be connected by bringing in "album" as well, so
    > you end up with a four-table join.
    >
    > And this is a very simple example; real ones could involve
    > larger sets of tables with more complex relationships that
    > could be searched in more different ways.
    >
    > The way I've been working so far is to have an array
    > tables_needed and push the table names onto it as they
    > are required, and then uniquing the array at the end; and
    > also having a table with the basic WHERE clause elements
    > for the join (e.g. "artist.album_id = album.id") and
    > similarly adding that to my constructed WHERE clause. But
    > while this works, it looks ugly and is hard to maintain,
    > and I also haven't been able to figure out (except by
    > hard-coding all possible examples) how to bring in
    > intermediate required tables, or how to handle the need
    > to write certain queries as LEFT JOINs rather than INNER
    > JOINs depending on the query.
    >
    > I'd be grateful for any suggestions for approaches, or
    > pointers to sample code, etc.
    >
    > Thanks.
    >
    > Jesse Sheidlower

    Jesse,

    Writing queries that relate more than 2 tables can easily become very
    (too) complex. If you're dealing with an exisiting structure, then you
    maybe have no choice than using the data as they are provided. But you
    can avoid growing complexity in queries even then. When I have more
    than 2 tables to join, I generally use a more transparent construction
    by splitting the query up into hashes that hold the matching results
    from a table. But generally it depends on the sizes of the different
    tables what will be the best option for you.

    Here under an example. I suppose you connect through DBI since you
    posted on a perl ng.


    # find artistnames that match user input
    $query = $db->prepare("SELECT ArtistID from Artists WHERE ArtistName
    REGEXP '$userinput'");
    $query->execute; $numrows = $query->rows;
    while (array = $query->fetchrow_array)
    {
    push artistIDs,$array[0];
    $ARTIST{$array[0]}=$array[1];
    }
    $extendquery = "WHERE (ArtistID='";
    $extendquery. = join "' OR ArtistID='",artistIDs;
    $extendquery. = "')";


    # find the songs
    $query = $db->prepare("SELECT SongTitle, ArtistID from Songs
    $extendquery");
    $query->execute; $numrows = $query->rows;
    while (array = $query->fetchrow_array)
    {
    ($title, $artistID) = array;
    print "Song: $title, Artist: $ARTIST{$artistID} \n";
    }


    If you do this for CD names and labels, you will have a clear view of
    the queries and what they do.

    No loss of speed because the tables need to be searched anyway when
    you would have joined it. Alternatively, you could make the string
    $extendedquery out of hash %ARTISTS (you would save the memory of the
    array with that). However, you need to think about the structure of
    your data so that no tons of variables are generated that slow down
    the app.

    Other elements like select, radio, checkbox in your web form should
    not be handled like this because you can assign the ID's directly in
    their value-attributes.

    Another approach could be to alter the table structure before
    developing the API. If you have that possibility. Some time ago I did
    a similar job as you are doing now, but it was a structure yet to
    built up. Generally we had 2 choices: would we put the data in 1 table
    or relate them by putting it into more tables?
    We chose for option 1 and until today I never regretted that for a
    second.
    Considering good db design we should have put it in more tables, but
    in our case the speed, maintainability and code transparency was more
    important. Speed is better because queries need to search only 1
    table, however the table holds some identical data (such as here: CD
    titles, artist names). Maintainability and transparency: definitely
    better. Of course the general structure of the data must be somewhat
    convenient to put it into one single table. I have learned to not
    immediately think "This must definitely be in separate tables".

    Or, if you have the possibility, e.g. only relate the Artists and put
    the other stuff into 1 large table. Or why not make some perl job to
    create a new table for you exactly as you want to have it.

    Hope this helps
    Bart
    Bart Van der Donck Guest

  5. #5

    Default Re: Dynamically generating multi-table SQL

    [email]jesterpanix.com[/email] (Jesse Sheidlower) wrote in message news:<bed7hg$mqr$1panix2.panix.com>...
    > I'm trying to automatically generate multi-table SQL
    > statements from a Web environment, and am having a lot of
    > difficulty doing it in a way that doesn't feel kludgy to
    > me. I've looked at various sites and books, but they tend to
    > focus on situations where multi-table joins are fixed--you
    > want the same results every time, so form of the query never
    > changes.
    >
    Are you familiar with OO perl? If so, I would suggest you
    use a class to build the SQL statements. Im working on a project
    right now that creates a sql statement using fields and values
    submitted from an HTML form. Depending, on what fields are specified
    I will either use INNER of LEFT joins, and some fields will support
    multiple values i.e. "where id in (1,2,3)"


    I have one abstract class SimpleSQLBuilder. It has methods:

    - init
    * An empty method, which is used so child classes can
    initialize state.

    - addFieldCondition
    * 1st parameter is the field name and the rest are the values
    supports 1 to N values.
    * This method then calls a method set<FieldName> on itself
    passing the values to this method. A child class should
    define these methods.

    - getSql
    * A child class should define this method to return the
    SQL statement

    I then have a child class which inherits from this class

    In init it will set up variables for each part of the statement
    and the initial join types for each table.

    As each set method is called it will add the condition to the
    where clause. If a field is specified in a joined table,
    that table will be promoted from a LEFT join to an INNER join.
    The class also keeps track of whether or not AND's are needed.
    The methods may also provide validation.

    Finally, the getSql method assembles the parts.



    The driving code is very clean with this approach:
    It is something like:

    my $cgi = CGI->new;
    my $sqlbuilder = CktxSqlBuilder->new;
    foreach my $var (qw{merid acqid aba nacct dlstate dlnum}) {
    $sqlbuilder->addFieldCondition($var, $cgi->param($var));
    }
    my $sql = $sqlbuilder->getSql;

    All of the state information is stored in CktxSqlBuilder, and
    since the parent class forces you to define a method for
    each field condition in order to work, it keeps the code
    a little more manageable (IMHO).
    > Suppose you have the archetypal CD database. If all the
    <snip>
    >
    > And this is a very simple example; real ones could involve
    > larger sets of tables with more complex relationships that
    > could be searched in more different ways.
    >
    > The way I've been working so far is to have an array
    > tables_needed and push the table names onto it as they
    > are required, and then uniquing the array at the end; and
    That makes sense, it might be easier to use a hash since you
    are removing duplicate elments from the array at the end
    anyway. The tables_needed (array or hash) would be a data member of an
    SqlBuilder object in my description above.

    > also having a table with the basic WHERE clause elements
    > for the join (e.g. "artist.album_id = album.id") and
    > similarly adding that to my constructed WHERE clause. But
    > while this works, it looks ugly and is hard to maintain,
    This is probably hard to maintain if you have all of the state
    information mixed with the other parts of the code.
    Are you using a lot of global variables?
    > and I also haven't been able to figure out (except by
    > hard-coding all possible examples) how to bring in
    > intermediate required tables, or how to handle the need
    > to write certain queries as LEFT JOINs rather than INNER
    > JOINs depending on the query.
    Here are some snippets of stuff I was working on.
    (these parts are untested and just parts not whole
    for one there is no validation here)
    This kind of shows how I am handling the JOIN types.


    sub init {
    my $self = shift;

    .... other init stuff....

    # setup the default join types

    $self->{joining_tables} = {
    acquirer => {required=>1, join_type=>'LEFT'},
    micr => {required=>1, join_type=>'LEFT'},
    dl => {required=>1, join_type=>'LEFT'},
    terminal => {required=>0, join_type=>'LEFT'}
    };
    }

    # here is a method that would be called to set a
    # field that is in the micr table.
    # Since this is part of the search criteria
    # I will want an INNER join instead of a LEFT join.
    sub setAba {
    my $self = shift;
    $self->addCondition("aba = '".$_[0]."');
    $self->{joining_tables}{micr}{join_type} = 'INNER';
    }

    # I use this method just to keep track of whether or not
    # I need an AND or not
    sub addCondition {
    my $self = shift;
    if (!$self->{clauses}++) {
    $self->{sql_where} .= ' AND ';
    }
    $self->{sql_where} .= $_[0];
    }
    >
    > I'd be grateful for any suggestions for approaches, or
    > pointers to sample code, etc.
    >
    > Thanks.
    >
    > Jesse Sheidlower
    I hope this made some sense.
    Bryan Castillo Guest

  6. #6

    Default Re: Dynamically generating multi-table SQL

    In article <1bff1830.0307082155.13a4950bposting.google.com >,
    Bryan Castillo <rook_5150> wrote:
    >jesterpanix.com (Jesse Sheidlower) wrote in message news:<bed7hg$mqr$1panix2.panix.com>...
    >> I'm trying to automatically generate multi-table SQL
    >> statements from a Web environment, and am having a lot of
    >> difficulty doing it in a way that doesn't feel kludgy to
    >> me. I've looked at various sites and books, but they tend to
    >> focus on situations where multi-table joins are fixed--you
    >> want the same results every time, so form of the query never
    >> changes.
    >>
    >
    >Are you familiar with OO perl? [...]
    Thanks to all who replied, and sorry for taking so long to follow
    up myself.

    In fact, I'm just starting to program in OO Perl myself, and had
    been trying to figure out how to do this in an OO way.

    After doing a whole bunch of work, which was going fairly well,
    I then ran into yesterday's article on perl.com about doing
    this very simply using Template Toolkit and Class::DBI. Grr.
    I'm still not sure that this is necessarily better than rolling
    my own--if it takes just as much effort to learn two different
    new abstraction layers, where's the benefit?--but I'm considering
    something like this, or with Alzabo or something.

    Thanks again for the thoughts. Am still working away.

    Best,

    Jesse Sheidlower
    Jesse Sheidlower Guest

  7. #7

    Default Re: Dynamically generating multi-table SQL

    Jesse Sheidlower wrote:
    >
    > In article <1bff1830.0307082155.13a4950bposting.google.com >,
    > Bryan Castillo <rook_5150> wrote:
    > >jesterpanix.com (Jesse Sheidlower) wrote in message news:<bed7hg$mqr$1panix2.panix.com>...
    > >> I'm trying to automatically generate multi-table SQL
    > >> statements from a Web environment, and am having a lot of
    > >> difficulty doing it in a way that doesn't feel kludgy to
    > >> me. I've looked at various sites and books, but they tend to
    > >> focus on situations where multi-table joins are fixed--you
    > >> want the same results every time, so form of the query never
    > >> changes.
    > >>
    > >
    > >Are you familiar with OO perl? [...]
    >
    > Thanks to all who replied, and sorry for taking so long to follow
    > up myself.
    >
    > In fact, I'm just starting to program in OO Perl myself, and had
    > been trying to figure out how to do this in an OO way.
    >
    > After doing a whole bunch of work, which was going fairly well,
    > I then ran into yesterday's article on perl.com about doing
    > this very simply using Template Toolkit and Class::DBI. Grr.
    > I'm still not sure that this is necessarily better than rolling
    > my own--if it takes just as much effort to learn two different
    > new abstraction layers, where's the benefit?--but I'm considering
    > something like this, or with Alzabo or something.
    >
    > Thanks again for the thoughts. Am still working away.
    >
    > Best,
    >
    > Jesse Sheidlower
    That is a very valid question, if it's a one-shot deal, and learning new
    is slower than doing old, ???


    --
    "4,000 years ago I made a mistake." Elrond Half-Elven, in "Fellowship
    of the Ring"

    "I try not to be right any more than necessary". -- Larry Wall, author
    of the Perl Language
    Jim Agnew - VCU/MCV Neurosurgery Guest

  8. #8

    Default Re: Dynamically generating multi-table SQL

    In article <3F16C43F.AE39A9A4vcu.edu>,
    Jim Agnew - VCU/MCV Neurosurgery <jpagnewvcu.edu> wrote:
    >Jesse Sheidlower wrote:
    >> After doing a whole bunch of work, which was going fairly well,
    >> I then ran into yesterday's article on perl.com about doing
    >> this very simply using Template Toolkit and Class::DBI. Grr.
    >> I'm still not sure that this is necessarily better than rolling
    >> my own--if it takes just as much effort to learn two different
    >> new abstraction layers, where's the benefit?--but I'm considering
    >> something like this, or with Alzabo or something.
    [...]
    >That is a very valid question, if it's a one-shot deal, and learning new
    >is slower than doing old, ???
    But of course it isn't that simple. It's not a one-shot deal,
    in that I do expect to be doing other database programming for
    the Web, but it's the usefulness of any particular other layer
    that's a problem. That is, and leaving aside the issue of how
    much I'd learn by rolling my own, the main issue would seem to
    be putting the effort into learning Class::DBI, or Alzabo, or
    Tangram, or DBIx::RecordSet, or..., and then six months from
    now everyone has abandoned whatever I just chose, and I'm stuck
    with code in a system no one uses any more.

    Anyway I realize this is hardly a new concern, and I'm just
    nattering in a way that's probably not appropriate, so I'll
    stop.

    Jesse Sheidlower

    Jesse Sheidlower Guest

Similar Threads

  1. Generating table rows and columns in output?
    By Sir Fauntleroy in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 22nd, 05:17 PM
  2. Table of Contents--Generating in PDF
    By Diane_C_Ross@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 2
    Last Post: May 14th, 07:17 PM
  3. Generating graphs from a table
    By Ike in forum PHP Development
    Replies: 2
    Last Post: December 17th, 07:25 PM
  4. dynamically generating graphics
    By MJL in forum PERL Miscellaneous
    Replies: 2
    Last Post: September 17th, 07:02 AM
  5. Generating amortization table with FMPro
    By William Maslin in forum FileMaker
    Replies: 3
    Last Post: July 17th, 10:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139