[sqlite] sql syntax grammar: single-source
Hi everybody, I hope somebody could clarify this for me. According to the sql grammar definition, the single-source production does not allow the table-alias after the join-source See http://www.sqlite.org/syntaxdiagrams.html#single-source However, when I'm trying to run this query it works just fine: select t.* from (t1 join t2) as t; Is it an oversight in the definition or am I missing something? Thanks in advance Vitali ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql syntax grammar: single-source
On Tue, Apr 5, 2011 at 8:30 AM, Vitali Kiruta kir...@gmail.com wrote: Hi everybody, I hope somebody could clarify this for me. According to the sql grammar definition, the single-source production does not allow the table-alias after the join-source See http://www.sqlite.org/syntaxdiagrams.html#single-source However, when I'm trying to run this query it works just fine: select t.* from (t1 join t2) as t; Is it an oversight in the definition or am I missing something? This currently works. But because it is not part of the language spec, we do not guarantee that we will continue to support it. Thanks in advance Vitali ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql syntax grammar: single-source
This currently works. But because it is not part of the language spec, we do not guarantee that we will continue to support it. Thanks a lot for quick answer. Do you mean the sqlite language spec, or the sql standard? I would be very much in favor of keeping this behavior. It makes select statement more modular and simplifies sql code generation. Regards, Vitali ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL syntax diagrams
Hi, I really like the way the SQL syntax diagrams are done (e.g. at http://www.sqlite.org/syntaxdiagrams.html). What software did you use for it? Regards Andy _ We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now http://clk.atdmt.com/UKM/go/195013117/direct/01/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL syntax diagrams
On Jan 15, 2010, at 9:10 PM, Andy Gibbs wrote: Hi, I really like the way the SQL syntax diagrams are done (e.g. at http://www.sqlite.org/syntaxdiagrams.html) . What software did you use for it? http://wiki.tcl.tk/21708 Regards Andy _ We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now http://clk.atdmt.com/UKM/go/195013117/direct/01/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL syntax diagrams
On Jan 15, 2010, at 9:10 AM, Andy Gibbs wrote: Hi, I really like the way the SQL syntax diagrams are done (e.g. at http://www.sqlite.org/syntaxdiagrams.html) . What software did you use for it? http://wiki.tcl.tk/21708 Also: http://www.sqlite.org/faq.html#q25 and http://www.sqlite.org/search?q=syntax+diagrams D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
On 7/9/09 6:21 , Rick Ratchford r...@amazingaccuracy.com wrote: Okay, this worked, but I have NO IDEA why. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 2 While this returned the correct answers: 1988 2008 What I don't understand is why it didn't simply return: 1988 1988 Since there is at least 15 or more days in Month=1 (Jan). Anyone? Warning Noobie here /Warning I think the min and max functions by design return only one value. However, I'm afraid this query might not work in the general case. For example, if your data starts on january 20, 1988, then this query will think 1988 is a full year. Ditto if the data ends early in december. So it seems to me that it works for you by luck, because your data set doesn't start in january, nor ends in december. Unless of course if I am totally wrong, which happens often enough. Jean-Denis Muys ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
Rick Ratchford wrote: Okay, this worked, but I have NO IDEA why. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 2 Read about aggregate functions (min() is one of those) in your favorite SQL textbook, then drop the LIMIT clause. You may also want to use UNION ALL instead of UNION: otherwise, if you just have one full year's worth of data, you'll get a single row as UNION removes duplicates. In fact, it may be more convenient (definitely easier to reuse as a fragment in more complicated queries) to return this result as one row with two columns: select (select min(Year) ...) as firstFullYear, (select max(Year) ...) as lastFullYear; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
On Thu, 2009-07-09 at 10:47 +0200, Jean-Denis Muys wrote: On 7/9/09 6:21 , Rick Ratchford r...@amazingaccuracy.com wrote: Okay, this worked, but I have NO IDEA why. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 2 While this returned the correct answers: 1988 2008 snip snip However, I'm afraid this query might not work in the general case. For example, if your data starts on january 20, 1988, then this query will think 1988 is a full year. Ditto if the data ends early in december. So it seems to me that it works for you by luck, because your data set doesn't start in january, nor ends in december. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 And Day8 _ UNION ALL _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 And Day24 This should limit it to looking for something in the first week and last week. David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
Rick Ratchford wrote: Okay, this worked, but I have NO IDEA why. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 2 Read about aggregate functions (min() is one of those) in your favorite SQL textbook, then drop the LIMIT clause. You may also want to use UNION ALL instead of UNION: otherwise, if you just have one full year's worth of data, you'll get a single row as UNION removes duplicates. In fact, it may be more convenient (definitely easier to reuse as a fragment in more complicated queries) to return this result as one row with two columns: select (select min(Year) ...) as firstFullYear, (select max(Year) ...) as lastFullYear; Igor Tandetnik - SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 And Day8 _ UNION ALL _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 And Day24 This should limit it to looking for something in the first week and last week. David - Warning Noobie here /Warning I think the min and max functions by design return only one value. However, I'm afraid this query might not work in the general case. For example, if your data starts on january 20, 1988, then this query will think 1988 is a full year. Ditto if the data ends early in december. So it seems to me that it works for you by luck, because your data set doesn't start in january, nor ends in december. Unless of course if I am totally wrong, which happens often enough. Jean-Denis Muys - In one message, I'd like to thank you all for your help (Igor, David, Jean-Denis, ...). Igor, that's a good precaution to use UNION ALL, although in case of my code this would never happen since it has a minimum 5 years of data requirement even to run the procedure. Thanks for that pointer as well as the idea to condense by reply by having both years appear in one row. I've not reached the point in my 'book' where you group 'select' inside 'select' using parenthesis. Yours was actually the first time I've seen it. Thanks. :) David, thanks for your input on the addition of Day . :) Jean-Denis, newbie or not, thanks for your input. I did note this and had originally placed a Day 5 for the beginning year and Day 28 for the last year. But with all the tweeking I was doing to this, some things dropped, some added... I'll use David's 'first week, last week' numbers for the final. Thanks guys. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
Rick Ratchford wrote: Okay, this worked, but I have NO IDEA why. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 2 Read about aggregate functions (min() is one of those) in your favorite SQL textbook, then drop the LIMIT clause. You may also want to use UNION ALL instead of UNION: otherwise, if you just have one full year's worth of data, you'll get a single row as UNION removes duplicates. In fact, it may be more convenient (definitely easier to reuse as a fragment in more complicated queries) to return this result as one row with two columns: select (select min(Year) ...) as firstFullYear, (select max(Year) ...) as lastFullYear; Igor Tandetnik Igor, If I go with the condensed version you illustrate above, I assume that I would then need to use the LIMIT clause, right? When I tried it, I get a bunch of rows returned, all with the same information. 1988 2008 1988 2008 1988 2008 1988 2008 1988 2008 1988 2008 ... Here is the SQL used: Select (Select min(Year) From TmpTable Where Month=1 and Day8) as FirstYear, (Select max(Year) From TmpTable Where Month=12 and Day24) as LastYear FROM TmpTable LIMIT 1 ?? Thanks. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
Rick Ratchford r...@amazingaccuracy.com wrote: select (select min(Year) ...) as firstFullYear, (select max(Year) ...) as lastFullYear; If I go with the condensed version you illustrate above, I assume that I would then need to use the LIMIT clause, right? When I tried it, I get a bunch of rows returned, all with the same information. 1988 2008 1988 2008 1988 2008 1988 2008 1988 2008 1988 2008 ... Here is the SQL used: Select (Select min(Year) From TmpTable Where Month=1 and Day8) as FirstYear, (Select max(Year) From TmpTable Where Month=12 and Day24) as LastYear FROM TmpTable Do you see a FROM clause in my example? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
Cheers! Rick Ratchford ProfitMax Trading Inc. http://www.amazingaccuracy.com #-Original Message- #From: sqlite-users-boun...@sqlite.org #[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik #Sent: Thursday, July 09, 2009 1:15 PM #To: sqlite-users@sqlite.org #Subject: Re: [sqlite] SQL Syntax # #Rick Ratchford r...@amazingaccuracy.com #wrote: # select #(select min(Year) ...) as firstFullYear, #(select max(Year) ...) as lastFullYear; # # If I go with the condensed version you illustrate above, I #assume that # I would then need to use the LIMIT clause, right? When I #tried it, I # get a bunch of rows returned, all with the same information. # # 1988 2008 # 1988 2008 # 1988 2008 # 1988 2008 # 1988 2008 # 1988 2008 # ... # # Here is the SQL used: # # Select (Select min(Year) From TmpTable Where Month=1 and Day8) as # FirstYear, # (Select max(Year) From TmpTable Where Month=12 and Day24) as # LastYear FROM TmpTable # #Do you see a FROM clause in my example? # #Igor Tandetnik # select (select min(Year) ...) as firstFullYear, (select max(Year) ...) as lastFullYear; When I saw the '...', I assumed this was just a partial example and that I was to fill in the rest. Because I'm still a real newbie at this, I didn't realize that a 'select' does not always need to be followed by a table name. I feel like a real dummy now. :- Thanks again. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Syntax
I've been trying all kinds of different ways to accomplish the following, and my head is spinning. Problem: How do you return from the DB just the YEAR of the first and last YEAR that had dates from 1st week of January to last week of December? Clarification: Suppose you had a database that contained stock price data. You are only interested in the first and last year that was a complete year. A complete year is a year where you have price data from the very first weekday (not weekend or holiday) of the year to the very last weekday of that year. Usually, a complete year is from Jan 2, 3 or 4 to Dec 29, 30, 31. Result Desired: To simply return the YEAR of the first complete year, and the YEAR of the last complete year of the dataset. This should return only two years in a single column. If 1988 is the first complete year and 2008 is the last complete year, then it should only return: 1988 2008 Available columns are: DATE (complete date) YEAR MONTH DAY ... I'm trying to do something like this, but it won't work because it says you can only have one LIMIT clause. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 LIMIT 1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 1 Help would be appreciated. Thanks. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
Okay, this worked, but I have NO IDEA why. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 2 While this returned the correct answers: 1988 2008 What I don't understand is why it didn't simply return: 1988 1988 Since there is at least 15 or more days in Month=1 (Jan). Anyone? Thanks. Rick Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford Sent: Wednesday, July 08, 2009 11:17 PM To: 'General Discussion of SQLite Database' Subject: [sqlite] SQL Syntax I've been trying all kinds of different ways to accomplish the following, and my head is spinning. Problem: How do you return from the DB just the YEAR of the first and last YEAR that had dates from 1st week of January to last week of December? Clarification: Suppose you had a database that contained stock price data. You are only interested in the first and last year that was a complete year. A complete year is a year where you have price data from the very first weekday (not weekend or holiday) of the year to the very last weekday of that year. Usually, a complete year is from Jan 2, 3 or 4 to Dec 29, 30, 31. Result Desired: To simply return the YEAR of the first complete year, and the YEAR of the last complete year of the dataset. This should return only two years in a single column. If 1988 is the first complete year and 2008 is the last complete year, then it should only return: 1988 2008 Available columns are: DATE (complete date) YEAR MONTH DAY ... I'm trying to do something like this, but it won't work because it says you can only have one LIMIT clause. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 LIMIT 1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 1 Help would be appreciated. Thanks. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax
On 9/07/2009 2:21 PM, Rick Ratchford wrote: Okay, this worked, but I have NO IDEA why. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 2 While this returned the correct answers: 1988 2008 What I don't understand is why it didn't simply return: 1988 1988 Since there is at least 15 or more days in Month=1 (Jan). Anyone? Thanks. Rick Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford Sent: Wednesday, July 08, 2009 11:17 PM To: 'General Discussion of SQLite Database' Subject: [sqlite] SQL Syntax I've been trying all kinds of different ways to accomplish the following, and my head is spinning. Problem: How do you return from the DB just the YEAR of the first and last YEAR that had dates from 1st week of January to last week of December? Clarification: Suppose you had a database that contained stock price data. You are only interested in the first and last year that was a complete year. A complete year is a year where you have price data from the very first weekday (not weekend or holiday) of the year to the very last weekday of that year. Usually, a complete year is from Jan 2, 3 or 4 to Dec 29, 30, 31. Result Desired: To simply return the YEAR of the first complete year, and the YEAR of the last complete year of the dataset. This should return only two years in a single column. If 1988 is the first complete year and 2008 is the last complete year, then it should only return: 1988 2008 Available columns are: DATE (complete date) YEAR MONTH DAY ... I'm trying to do something like this, but it won't work because it says you can only have one LIMIT clause. SQLString = SELECT min(Year) FROM TmpTable _ WHERE Month=1 LIMIT 1 UNION _ SELECT max(Year) FROM TmpTable _ WHERE Month = 12 LIMIT 1 Help would be appreciated. Each SELECT will return only 1 result. Split that up into 2 queries (omit the LIMIT clause; it's redundant) and see for yourself. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL syntax issue?
I've come across the following SQL syntax issue in Sqlite that is maybe a bug (or maybe just a product of my poor SQL knowledge!) I have a database that from the command line will execute the following statement fine: SELECT clients.cl_lname, clients.cl_title, price_profiles.pp_year FROM clients LEFT JOIN price_profiles ON clients.cl_id = price_profiles.pp_client But gives and error with the following statement (it will run fine on the same tables in Access) SELECT clients.cl_lname, clients.cl_title, price_profiles.pp_year, prices.pr_mfd, prices.pr_price FROM (clients LEFT JOIN price_profiles ON clients.cl_id = price_profiles.pp_client) LEFT JOIN prices ON price_profiles.pp_id = prices.pr_pp SQL error: no such column: clients.cl_lname If I remove the table part of all the column names (luckily the column names are unique) then the statement will execute with no problems. SELECT cl_lname, cl_title, pp_year, pr_mfd, pr_price FROM (clients LEFT JOIN price_profiles ON cl_id = pp_client) LEFT JOIN prices ON pp_id = pr_pp Is this behaviour expected in Sqlite? Is there an alternative format I should use in the above case? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL syntax possibilities
uSQLite does not (generally) enter into the details of the query it is
passed. There is however an exception for security. uSQLite requires a
login and (depending on the user and network) assigns the user a level:
0: No access
1: Select only
2: Update/Insert only
3: Select/Update/Insert only
4: Power user
These rules are enforced by looking at the first word in a query, the
code is at the bottom of this post. My problem is that I allow multiple
queries to be concatented, and this is a useful feature for bulk inserts
and blocks of queries that must be executed atomicaly (Major hangup is
level 2 for remote updates inserts). However, with the present routine
I could do eg:
Select 1;Drop table foo
With user level 1. AFAIK it would be sufficient that I modify the
ChekLevel routine such that it will check the first word and each word
that follows a semicolon (but I must also skip quoted semicolons). Is
this correct? Or can anybody think of any scenarios where this would
not work?
int ChekLevel(char *query,int uselevel){
char *cur,*dur;
cur=query;
// strip lead
while((*cur' ')(*cur))cur++;
// strncasecmp is the same as strncmp except that
// it ignores case. It is a GNU extension to the
// Clib which many other libs have.
if(!strncasecmp(cur,SELECT,6))
return (uselevel1)||(uselevel==2)?0:1;
if(!strncasecmp(cur,UPDATE,6))
return uselevel2? 0:1;
if(!strncasecmp(cur,INSERT,6))
return uselevel2? 0:1;
return uselevel4? 0:1;
}
Re: [sqlite] SQL syntax possibilities
[EMAIL PROTECTED] wrote: uSQLite does not (generally) enter into the details of the query it is passed. There is however an exception for security. uSQLite requires a login and (depending on the user and network) assigns the user a level: 0: No access 1: Select only 2: Update/Insert only 3: Select/Update/Insert only 4: Power user These rules are enforced by looking at the first word in a query, the code is at the bottom of this post. My problem is that I allow multiple queries to be concatented, and this is a useful feature for bulk inserts and blocks of queries that must be executed atomicaly (Major hangup is level 2 for remote updates inserts). However, with the present routine I could do eg: I think you should look into using SQLite's authorizer callback mechanism; that way you don't have to parse syntax.
RE: [sqlite] SQL syntax possibilities
Original Message Subject: Re: [sqlite] SQL syntax possibilities From: Eric Bohlman [EMAIL PROTECTED] Date: Wed, November 16, 2005 10:54 am To: sqlite-users@sqlite.org [EMAIL PROTECTED] wrote: uSQLite does not (generally) enter into the details of the query it is passed. There is however an exception for security. uSQLite requires a login and (depending on the user and network) assigns the user a level: 0: No access 1: Select only 2: Update/Insert only 3: Select/Update/Insert only 4: Power user These rules are enforced by looking at the first word in a query, the code is at the bottom of this post. My problem is that I allow multiple queries to be concatented, and this is a useful feature for bulk inserts and blocks of queries that must be executed atomicaly (Major hangup is level 2 for remote updates inserts). However, with the present routine I could do eg: I think you should look into using SQLite's authorizer callback mechanism; that way you don't have to parse syntax. Yup. Will do. Thanks. (Repeat after me RTFM, RTFM,RTFM...)
Re: [sqlite] SQL syntax possibilities
At 1:34 AM -0700 11/16/05, [EMAIL PROTECTED] wrote: uSQLite does not (generally) enter into the details of the query it is passed. There is however an exception for security. uSQLite requires a login and (depending on the user and network) assigns the user a level: 0: No access 1: Select only 2: Update/Insert only 3: Select/Update/Insert only 4: Power user Maybe your list wasn't complete, but I notice that 'Delete' is conspicuously absent, yet it is necessary to do many common tasks. And before you say that it was left out from all but Power User because of its possible destructiveness, I would say that Update is just as destructive; an Update can blow away anything a Delete can, if you say update ... set foo = 0 for all fields and rows. I suggest that levels like this make more sense from a security standpoint: 0: No access 1: Select only (read-only) 2: Insert only (non-destructive drop-box) 3: Select/Insert only (fully non-destructive read and write) 4: Select/Insert/Update/Delete only (full read-write data, no ddl) 5: Power User -- Darren Duncan
RE: [sqlite] SQL syntax possibilities
Original Message Subject: Re: [sqlite] SQL syntax possibilities From: Darren Duncan [EMAIL PROTECTED] Date: Wed, November 16, 2005 10:41 pm To: sqlite-users@sqlite.org At 1:34 AM -0700 11/16/05, [EMAIL PROTECTED] wrote: uSQLite does not (generally) enter into the details of the query it is passed. There is however an exception for security. uSQLite requires a login and (depending on the user and network) assigns the user a level: 0: No access 1: Select only 2: Update/Insert only 3: Select/Update/Insert only 4: Power user Maybe your list wasn't complete, but I notice that 'Delete' is conspicuously absent, yet it is necessary to do many common tasks. Yes, you could reason that delete should be at level 3. I started of with Read only or read/write and added 2 because it is appropriate for remote automated data insertion, which is a prime application of the protocol. I did 3 and 4 to seperate nervous users form confident users:-) Perhaps it is just the way I define databases, but I find delete little use in normal operations, I keep everything :-) Having said that, I also kept things simple to mimimise the complications of parsing the SQL, but now I realise I can leave that to SQL by means of the authorise callback, I may let a few extra levels creep in or more likely do a bit field. Keeping remote access within necessary limits is just good sense. I agree with you about the damage causable by UPDATE however. I have long felt that SQL should make WHERE clauses obigatory. The UPDATE is particularly terrible as it is very easy to hit return prematurly after completing the values. IMHO, if you want to operate on all records then it should be necessary to put a WHERE ALL clause or something. Would a PRAGMA FORCE_WHERE option in SQLite be considered feature creep? Perhaps some notion allready exists, I am no expert on SQL syntax and standards.
RE: [sqlite] SQL syntax - please help
Ok, thank you Darren. It's good to know that, so I don't waste time trying to implement something that doesn't exist :-) Are there plans to implement this sometime in the future? Dennis -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, 1 July 2005 7:31 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL syntax - please help I don't think that SQLite supports what you want yet. As soon as the SQL:2003 WITH RECURSIVE ... SELECT ... syntax is supported, you should be able to do what you want elegantly. Meanwhile, you will have to do multiple selects to work with N recursions. -- Darren Duncan
[sqlite] SQL syntax - please help
Hello all, I'm trying to form a specific query, but not sure if that's possible at all and if it is, how it should look like. I'd really appreciate your help :-) I have 2 tables (simplest case, but once I know this, I can work out the rest): CREATE TABLE TableA( UID INTEGER PRIMARY KEY, Text, PARENTTABLEID, RECORDID) CREATE TABLE TableMain( UID INTEGER PRIMARY KEY, Text) Where TableA's RECORDID is in fact the UID of TableMain and PARENTTABLEID can be filled with TableA's UID to show hierarchical relationship, so they can go like this: TableMain |--- TableA |--- TableA Ok, I need to search through the Text field, so for simple tables I had this (filing with appropriate data of course using sqlite3_mprintf() function): SELECT * FROM TableMain WHERE Text LIKE '%%%q%%' and I had UNION if I needed to search on more than one field. So far so good, but now I have this hierarchy of TableA's, that also need to be searched through, and I'd like to have one SQL statement that does it, if possible. I'd like to know if I can do something like this (incorrect, but to show the idea): SELECT * FROM TableMain WHERE Text LIKE 'x' UNION SELECT * FROM TableA WHERE TableA.RECORDID=TableMain.UID AND Text LIKE 'x' ??? (and how could I go through recursive TableA's?) Thank you for reading this :-) I'd appreciate any comments, and I'd like to know if this is possible at all. Regards, Dennis
[sqlite] SQL Syntax from 2.8 to 3.0 not backward compatible?
Here is it: SQLite version 3.0.1 Enter .help for instructions sqlite BEGIN TRANSACTION ON CONFLICT ROLLBACK; SQL error: near ON: syntax error bye. -- Guillaume FOUGNIES - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQL Syntax from 2.8 to 3.0 not backward compatible?
A BEGIN cannot have an ON CONFLICT clause in sqlite version 3. Dan. --- Guillaume Fougnies [EMAIL PROTECTED] wrote: Here is it: SQLite version 3.0.1 Enter .help for instructions sqlite BEGIN TRANSACTION ON CONFLICT ROLLBACK; SQL error: near ON: syntax error bye. -- Guillaume FOUGNIES - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQL Syntax from 2.8 to 3.0 not backward compatible?
Ok. Perhaps it should be written in the documentation of SQLite Version 3 or in the chapter Transaction Control At The SQL Level of the Locking And Concurrency In SQLite Version 3. Thanks. bye. Fri, Jun 25, 2004 at 04:22:10AM -0700: Daniel K wrote: A BEGIN cannot have an ON CONFLICT clause in sqlite version 3. Dan. --- Guillaume Fougnies [EMAIL PROTECTED] wrote: Here is it: SQLite version 3.0.1 Enter .help for instructions sqlite BEGIN TRANSACTION ON CONFLICT ROLLBACK; SQL error: near ON: syntax error bye. -- Guillaume FOUGNIES - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
