Gossamer Forum
Quote Reply
GT::SQL
Could I suggest a little alteration to GT::SQL.

Currently with the select_options method, if you happen to pass in an ORDER clause after a LIMIT clause the query will fail as it expects the LIMIT last. Would it be possible to get GT::SQL to automatically handle this?

eg...

my $table = $DB->table('Links');

# Works
$table->select_option("ORDER BY ID ASC", "LIMIT 20");

# Fails
$table->select_option("LIMIT 20", "ORDER BY ID ASC");

Last edited by:

Paul: Mar 30, 2003, 8:08 AM
Quote Reply
Re: [Paul] GT::SQL In reply to
I can see where you're coming from Paul, but personally I think it's better the way it is. Allowing the user to be grammaticly incorrect in clause order inhibits the ability to learn the language properly.

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [fuzzy thoughts] GT::SQL In reply to
I think anyone using GT::SQL probably has a fair knowledge of SQL so it would just be a helpful time saver rather than something encouraging poor SQL.
Quote Reply
Re: [Paul] GT::SQL In reply to
Maybe if GT added something into the GT::SQL documentation? Just a note or something...alerting people to this potential problem.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Paul] GT::SQL In reply to
Another thing =)

It would be cool if GT::SQL was clever enough to know that a null column with a regex specified in the def file can still allow a blank value instead of returning an error.

eg...

Def:

'column' => {
not_null => 0,
regex => '^\d+'$
}


....now if you try to enter a blank value the regex will fail and you'll get an error but it should be allowed as the column is null.
Quote Reply
Re: [Paul] GT::SQL In reply to
Any comments on anything?
Quote Reply
Re: [Paul] GT::SQL In reply to
Quote:
Currently with the select_options method, if you happen to pass in an ORDER clause after a LIMIT clause the query will fail as it expects the LIMIT last. Would it be possible to get GT::SQL to automatically handle this?

Doubtful. What we really want to do is create a select object so that you can do is:

$select->limit(10);
$select->offset(5);
$select->order_by('abc');

or allow you to pass in a hash of options. Passing in raw sql causes problems like the one you describe, and having gt sql detect the sql you use really isn't the right fix to the problem.

Quote:
...now if you try to enter a blank value the regex will fail and you'll get an error but it should be allowed as the column is null.

I think that means your regex is wrong. =)

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] GT::SQL In reply to
Quote:
I think that means your regex is wrong. =)

No I mean a formatted error, not a server error. When your column is set to null, if a value is entered and you want, say all digits, your regex would be:

^\d+$

...however if I left the field blank I'd get an error saying "The column 'blah' cannot contain the value ''" ..but really it can because the column is null.

I can get around it by doing:

^(?:|\d+)$

...but that's ugly Unsure

Last edited by:

Paul: Apr 4, 2003, 9:59 AM
Quote Reply
Re: [Alex] GT::SQL In reply to
>>>$select->limit(10);
$select->offset(5);
$select->order_by('abc'); <<<

That looks like a great idea. The problem would be having to have functions for each SQL option (ORDER,LIMIT,OFFSET,WHERE and thats all I can think of at the moment Laugh I know there are a lot more though).

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] GT::SQL In reply to
No, I expect WHERE would still be handled by GT::SQL::Condition and passing in a hashref to the select/delete/update methods.

Select options are just like LIMIT, ORDER, GROUP etc.
Quote Reply
Re: [Andy] GT::SQL In reply to
In Reply To:
>>>$select->limit(10);
$select->offset(5);
$select->order_by('abc'); <<<

That looks like a great idea.

Sure - and it only requires a rewrite of GT::SQL Crazy.

In Reply To:
The problem would be having to have functions for each SQL option (ORDER,LIMIT,OFFSET,WHERE and thats all I can think of at the moment Laugh I know there are a lot more though).

Yes, there are many more - but the thing to consider here is that it should be kept database independant. That way, if someone wanted to write a text-based GT::SQL (or GT::DB, as the rewrite will probably be called) driver, they'll be able to _without_ having to parse or even know anything about actual SQL. Of course, that means the feature set has to increase fairly substantially as the current SQL "hacks" won't work anymore. Yet another big sub-project for the future Cool

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com
Quote Reply
Re: [Alex] GT::SQL In reply to
Hi

The object route is the way to go then you can just overload it.

The n-tier route is the way forward, seperate layers, presentation, data access,

business objects etc.

Correctly implemented, and knowing the way GT codeCool that

shouldn't be to hard, the underlying datasource doesn't matter.

Dregs2
Quote Reply
Re: [Paul] GT::SQL In reply to
Unimpressed