[sqlite] Fwd: Expression syntax
Thanks you Jay and other for the clarification. The fact that the table can only have one column is what was missing from my understanding of how this works. Jay, I think you just persuaded me to buy your book! Pete On Tue, May 1, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 5 Date: Mon, 30 Apr 2012 12:03:59 -0500 From: Jay A. Kreibich j...@kreibi.ch To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Fwd: Expression syntax Message-ID: 20120430170359.gc45...@dfjk.org Content-Type: text/plain; charset=us-ascii On Mon, Apr 30, 2012 at 09:50:34AM -0700, Pete scratched on the wall: Anyone? -- Forwarded message -- From: Pete p...@mollysrevenge.com Date: Thu, Apr 26, 2012 at 10:55 AM Subject: Expression syntax To: sqlite-users@sqlite.org The syntax diagram for an expression using the IN/NOT IN operators shows an option to specify database.tablename as the right operand. What does database.tablename evaluate to? Using SQLite, Appendix D (Expression Reference), IN, p355: The last way to define the test group is by providing a table name. The table must consist of only a single column. You cannot provide a table and column, it must be a single-column table. This final style is most frequently used with temporary tables. If you need to execute the same test multiple times, it can be more efficient to build a temporary table (for example, with CREATE TEMP TABLE...AS SELECT), and use it over and over, rather than using a subquery as part of the IN expression. If you provide a reference to a single-column table, the contents of the table will be used as the right-hand list of the IN expression. It's a short cut for the sub-select (SELECT * FROM database.tablename). -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Expression syntax
Anyone? -- Forwarded message -- From: Pete p...@mollysrevenge.com Date: Thu, Apr 26, 2012 at 10:55 AM Subject: Expression syntax To: sqlite-users@sqlite.org The syntax diagram for an expression using the IN/NOT IN operators shows an option to specify database.tablename as the right operand. What does database.tablename evaluate to? -- Pete -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 column widths
Hi TIm, sqlite3 already includes the ability to define the width manually with the .width command. I was hoping that there might be a way to use the defined string length as part of the default when .width is not used. Pete On Thu, Apr 26, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Nothing to stop you adding some code to the sqlite3 CLI program to have extra commands, allowing you to define column widths for display purposes. E.g: sqlite3 .colwidth x 27 where x is the name of a column in some table. -- Cheers -- Tim -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 column widths
So I guess that's a No then? I'm fully aware of how sqlite3 treats datatypes and column width specifications, but surely if someone defines a column with a specific max width, it's reasonable to assume that's the max width they want, otherwise why bother defining it? That would seem to be a lot less arbitrary than the current logic for determining the default column width. On Wed, Apr 25, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 23 Date: Tue, 24 Apr 2012 13:43:25 -0700 From: Roger Binns rog...@rogerbinns.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3 column widths Message-ID: 4f97106d.7080...@rogerbinns.com Content-Type: text/plain; charset=ISO-8859-1 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/04/12 13:26, Pete wrote: Is there way to have sqlite3 display the columns in the result of a SELECT statement using their defined widths in the schema? For example if a column is defined as Name(40) TEXT, I want the column to be 40 chars wide. Those numbers you put in the schema are ignored by SQLite and do not limit or provide any hints to any SQLite code. How SQLite deals with types is detailed here: http://www.sqlite.org/datatype3.html Separately from that the SQLite source code is public domain and you have the full rights to modify it, redistribute it etc. You can modify the code to work however you want. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk+XEG0ACgkQmOOfHg372QRCpgCdEVfimkFYA8kx3WQixtfEVEAs DpEAoLY1FVYSbQVNZoRM0dTa1fmUbsKj =uuvW -END PGP SIGNATURE- -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggestion about hard-coded time string format YYYY-MM-DD
Folks, I'm a bit late to this discussion but what are the new PRAGMAs referred to here? I don't see them listed in the documentation - do they exist or are they a suggestion for future implementation? On Tue, Apr 3, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 2 Date: Mon, 2 Apr 2012 20:38:37 +0400 From: Alexey Pechnikov pechni...@mobigroup.ru To: sqlite-users@sqlite.org Subject: [sqlite] Suggestion about hard-coded time string format -MM-DD Message-ID: CANMYFJn6nktjH=mgbgpp6dx6nyzva7scsesnuv4gk0xnazf...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 Why we can't control this? As example, in Russia the date format is DD.MM. and is needed the patch http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565cv2=720cb1015e95af7a I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for internationalization. These may be used for parsing and formatting dates. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.7.11
I think it's my brain that needs refreshing not my browser :-) Yes I see that now but in fairness, the first line of the description still says The first form (with the VALUES keyword) creates a single new row in an existing table. Still interested in the improvements to csv file handling. I seem to remember there were issues with embedded commas and single quotes - is that one of the areas of improvement? Thanks, Pete On Sat, Apr 14, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 12 Date: Fri, 13 Apr 2012 17:53:01 -0400 From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Version 3.7.11 Message-ID: CALwJ=MxhVefZsRWWXFNCLp5pQ2z=9tv_stdmgc8ltcv2s50...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 On Fri, Apr 13, 2012 at 4:54 PM, Pete p...@mollysrevenge.com wrote: A couple of things in the Release Notes for 3.7.11 caught my eye: - ability to insert muyltiple rows in one INSERT command - improvements to the handling of csv inputs in sqlite3 Is there more detailed information available about these changes. For example,the INSERT syntax diagram/description doesn;t seem to cover the new feature. Yeah it does. (Do you need to press Reload on your browser?) It used to have VALUES - ( goes - expr in a loop - ). Now it has another loop from the ) back to the (. -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Version 3.7.11
A couple of things in the Release Notes for 3.7.11 caught my eye: - ability to insert muyltiple rows in one INSERT command - improvements to the handling of csv inputs in sqlite3 Is there more detailed information available about these changes. For example,the INSERT syntax diagram/description doesn;t seem to cover the new feature. Thanks, -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT INTO with SELECT
Thank you Igor and Simon, I think I understand this now. On reading the
docs more closely, it looks like the scond test case (NOT NULL with a
DEFAULT) could be solved by using INSERT OR REPLACE. I'm wondering if
there might be a way to solve the first test case by using a trigger?
Pete
On Thu, Apr 5, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote:
Message: 10
Date: Thu, 5 Apr 2012 01:38:57 +0100
From: Simon Slavin slav...@bigfraud.org
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT INTO with SELECT
Message-ID: bb6e260a-182b-4fac-a95d-fb9b115cd...@bigfraud.org
Content-Type: text/plain; charset=us-ascii
On 5 Apr 2012, at 1:36am, Pete p...@mollysrevenge.com wrote:
Here's my test:
CREATE TABLE t2 (Col1 text,Col2 text);
insert into t2 (Col1) values('xxx');
select * from t2
xxx|
CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
insert into t3 SELECT * FROM t2;
select * from t3;
xxx|
Why does t3.Col2 not have it's default value of 'abc'?
Because you fed it a value for the second column: NULL. If you want the
second column to have a default value you might find that
insert into t3 SELECT Col1 FROM t2;
works.
Simon.
--
Message: 11
Date: Wed, 4 Apr 2012 17:48:05 -0700
From: Pete p...@mollysrevenge.com
To: sqlite-users@sqlite.org
Subject: [sqlite] Variation on INSERT with SELECT issue
Message-ID:
CABx6j9=88w76nafvhl6f+hbp2efnsjqha6gqpftdu+exb2m...@mail.gmail.com
Content-Type: text/plain; charset=ISO-8859-1
The test this time was:
sqlite create table t1 (Col1,Col2);
sqlite insert into t1 (Col1) values ('xxx');
sqlite select * from t1;
xxx|
sqlite create table t2 (Col1, col2 not null default 'abc');
sqlite insert into t2 SELECT * from t1;
SQL error: t2.col2 may not be NULL
--
Pete
--
Message: 12
Date: Wed, 04 Apr 2012 20:52:58 -0400
From: Igor Tandetnik itandet...@mvps.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Variation on INSERT with SELECT issue
Message-ID: jliqe2$q3k$1...@dough.gmane.org
Content-Type: text/plain; charset=UTF-8; format=flowed
On 4/4/2012 8:48 PM, Pete wrote:
The test this time was:
sqlite create table t1 (Col1,Col2);
sqlite insert into t1 (Col1) values ('xxx');
sqlite select * from t1;
xxx|
sqlite create table t2 (Col1, col2 not null default 'abc');
sqlite insert into t2 SELECT * from t1;
SQL error: t2.col2 may not be NULL
Default clause applies when you omit a column from the list in INSERT
(like you did when inserting into t1). It doesn't apply when you attempt
to insert NULL explicitly.
--
Igor Tandetnik
--
Pete
Molly's Revenge http://www.mollysrevenge.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variation on INSERT with SELECT issue
Thank you Michael, that works.
Pete
On Thu, Apr 5, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote:
Message: 17
Date: Thu, 5 Apr 2012 12:31:58 +
From: Black, Michael (IS) michael.bla...@ngc.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Subject: Re: [sqlite] Variation on INSERT with SELECT issue
Message-ID: 56DF5186-D679-4E16-9CF2-8CFAF0036CFD@mimectl
Content-Type: text/plain; charset=iso-8859-1
You need 2 inserts to do what you want. Hopefully the order in the table
doesn't matter to you.
sqlite CREATE TABLE t2 (Col1 text,Col2 text);
sqlite insert into t2 (Col1) values('xxx');
sqlite insert into t2 values('yyy','def');
sqlite select * from t2;
xxx|
yyy|def
sqlite
sqlite
sqlite CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
sqlite insert into t3 SELECT * FROM t2 where Col2 is not null;
sqlite select * from t3;
yyy|def
sqlite insert into t3 (Col1) SELECT Col1 FROM t2 where Col2 is null;
sqlite select * from t3;
yyy|def
xxx|abc
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
--
Pete
Molly's Revenge http://www.mollysrevenge.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT INTO with SELECT
Hi SImon,
Sorry if my replies seem a little disjointed - I get a daily digest of the
list so I don't see any responses to my posts until the next day.
Anyway, turns out this solution doesnt work - the INSERT fails because
there are two columns in t3 but only 1 column named in the SELECT. The
solution is per Michael's later reply which is :
INSERT into t3 (Col1) SELECT Col1 FROM t2;
Thanks to everyone for the solution.
Pete
On Thu, Apr 5, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote:
Message: 10
Date: Thu, 5 Apr 2012 01:38:57 +0100
From: Simon Slavin slav...@bigfraud.org
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT INTO with SELECT
Message-ID: bb6e260a-182b-4fac-a95d-fb9b115cd...@bigfraud.org
Content-Type: text/plain; charset=us-ascii
On 5 Apr 2012, at 1:36am, Pete p...@mollysrevenge.com wrote:
Here's my test:
CREATE TABLE t2 (Col1 text,Col2 text);
insert into t2 (Col1) values('xxx');
select * from t2
xxx|
CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
insert into t3 SELECT * FROM t2;
select * from t3;
xxx|
Why does t3.Col2 not have it's default value of 'abc'?
Because you fed it a value for the second column: NULL. If you want the
second column to have a default value you might find that
insert into t3 SELECT Col1 FROM t2;
works.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT INTO with SELECT
I'm seeing something that doesn't look right when using the SELECT form of
the INSERT statement.
Here's my test:
CREATE TABLE t2 (Col1 text,Col2 text);
insert into t2 (Col1) values('xxx');
select * from t2
xxx|
CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
insert into t3 SELECT * FROM t2;
select * from t3;
xxx|
Why does t3.Col2 not have it's default value of 'abc'?
Pete
--
Pete
Molly's Revenge http://www.mollysrevenge.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Variation on INSERT with SELECT issue
The test this time was:
sqlite create table t1 (Col1,Col2);
sqlite insert into t1 (Col1) values ('xxx');
sqlite select * from t1;
xxx|
sqlite create table t2 (Col1, col2 not null default 'abc');
sqlite insert into t2 SELECT * from t1;
SQL error: t2.col2 may not be NULL
--
Pete
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key Problems
Thanks you SImon. I see this is because the version of sqlite3 I have does not support foreign keys. I am running OS X 10.6.8 and sqlite3 comes with the OS. Does anyone know where I can get a version of sqlite3 for OS X that does support foreign keys? It would have to be a compiled binary since I don't access to compilers (or the skills to use them). Thanks, Pete On Tue, Apr 3, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 5 Date: Mon, 2 Apr 2012 17:58:28 +0100 From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Foreign Key Problems Message-ID: 922a3407-7604-4f64-87bc-07221c066...@bigfraud.org Content-Type: text/plain; charset=us-ascii On 2 Apr 2012, at 5:56pm, Pete p...@mollysrevenge.com wrote: Enabling foreign keys in my application works fine and INSERTs thast violate a fkey constraint fail. Is this a know problem with sqlite3? I'm using version 3.6.12 on a Mac. http://sqlite.org/foreignkeys.html This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19. Simon. -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key Problems
Thanks Pavel and Dan, that was the problem. In the past, I've always used a primary key as the parent of a foreign key link so hadn't run into this issue. Pete On Mon, Apr 2, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 12 Date: Mon, 02 Apr 2012 15:33:20 +0700 From: Dan Kennedy danielk1...@gmail.com To: sqlite-users@sqlite.org Subject: Re: [sqlite] Foreign Key Problems Message-ID: 4f796450.9030...@gmail.com Content-Type: text/plain; charset=ISO-8859-1; format=flowed On 04/02/2012 07:22 AM, Pete wrote: I'm running into an issue with foreign keys where no matter what value I supply for a child key, I get a foreign key mismatch error. Here are my test tables. foreign key mismatch indicates a schema problem. Usually a missing index. See here: http://www.sqlite.org/foreignkeys.html#fk_indexes It's likely you need to create a UNIQUE index on t1.RefColumn. -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key Problems
I have a couple of follow up questions on this. sqlite3 doesn't seem to recognise the foreign_keys PRAGMA. If I execute PRAGMA foreign_keys=1, I don't get an error. If I then execute PRAGMA foreign_keys to get the setting, nothing is returned. If I INSERT a record that violates the foreign key constraint, it is inserted without an error. Enabling foreign keys in my application works fine and INSERTs thast violate a fkey constraint fail. Is this a know problem with sqlite3? I'm using version 3.6.12 on a Mac. Also, it appears that the foreign_keys setting only applies for the life of a db connection and they have to be enabled every time a db is opened. Is that correct? Do all PRAGMA settings work like that? Thanks, Pete On Mon, Apr 2, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 12 Date: Mon, 02 Apr 2012 15:33:20 +0700 From: Dan Kennedy danielk1...@gmail.com To: sqlite-users@sqlite.org Subject: Re: [sqlite] Foreign Key Problems Message-ID: 4f796450.9030...@gmail.com Content-Type: text/plain; charset=ISO-8859-1; format=flowed On 04/02/2012 07:22 AM, Pete wrote: I'm running into an issue with foreign keys where no matter what value I supply for a child key, I get a foreign key mismatch error. Here are my test tables. foreign key mismatch indicates a schema problem. Usually a missing index. See here: http://www.sqlite.org/foreignkeys.html#fk_indexes It's likely you need to create a UNIQUE index on t1.RefColumn. -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign Key Problems
I'm running into an issue with foreign keys where no matter what value I
supply for a child key, I get a foreign key mismatch error. Here are my
test tables.
CREATE TABLE t1 (RefColumn TEXT ,Data TEXT )
CREATE TABLE t2 (FKeyColumn TEXT REFERENCES t1(RefColumn),Data
TEXT )
PRAGMA foreign_keys is set to 1.
Put a couple of entries into t1:
SELECT * FROM t1
RefColumn Data
-- --
a aaa
b bbb
Now insert a row into t2
INSERT INTO t2 VALUES ('a','aaa');
I get a foreign Key mismatch error. No matter what value I supply for
FKeyColumn, even NULL, I get the same error.
I disabled foreign keys, then the INSERT worked. Enabled foreign keys
again, it fails again.
And even more concerning DELETE FROM t2 also produces a foreign key
mismatch error.
What am I doing wrong?
--
Pete
Molly's Revenge http://www.mollysrevenge.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUMing large DBs
Interesting. Does that mean any open transaction other than the VACUUM transaction? I'm still confused. Pete On Tue, Mar 27, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 5 Date: Mon, 26 Mar 2012 10:25:49 -0700 (PDT) From: Peter Aronson pbaron...@att.net To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] VACUUMing large DBs Message-ID: 1332782749.22198.yahoomai...@web180307.mail.gq1.yahoo.com Content-Type: text/plain; charset=iso-8859-1 Actually, it can't be in a transaction.? To quote: A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.? (See http://www.sqlite.org/lang_vacuum.html). Best regards, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUMing large DBs
SHould a VACUUM command be wrapped in a transaction, or is that done automatically? -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Manager question
After recent postings, I have a demo version of SQLite manager. I'm noticing that the same simple query (SELECT * FROM table) on a table with around 50k rows takes anywhere from 3-10 times longer in the SQL tab than the Manage tab according to the timings that SQLite Manager shows. Any ideas why that might be? The longer times are still very fast but I'm curious to know if there are some ways of retrieving data that are faster than others for the same query. Thanks -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Constraint Error Messages
Thanks for all the responses - I seem to have opened up a can of worms! Looking into his further, I see differences in how constraint failures are reported. In the case of a NOT NULL constraint, I got an error table.column may not be NULL. In the case of a UNIQUE constraint, the error was column column is not unique. But if a CHECK constraint failed, the error was simply constraint failed with no reference to the column name or that it was a CHECK constraint that failed. I could probably deal with any error message that included the column name in terms of reinterpreting it for display to a user, just as I would if the constraint name was reported, but the bare bones constraint failed error is hard to deal with. Maybe I will use a trigger with RAISE() instead of check constraint. It also seems that only one error is reported even if multiple constraint violations occur. Maybe that would change depending on the ON CONFLICT action specified? I guess all I can do is add my vote to the list of people who would like improvements to the way constraint violations are reported, perhaps with a PRAGMA to control the new logic. Thanks, -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Constraint error messages
I would like to include as much error checking as possible in my database schema. The problem I have is that the error messages that come back from constraint violations are extremely generic (e.g. constraint failed) and would mean nothing to a user. I tried including a name for constraints hoping I could check the error message for the name and translate it into a meaningful user message, but the name isn't returned in the error message. Are there any tricks by which to get meaningful error messages when a constraint fails? I saw the RAISE command - perhaps that could be used in a CHECK constraint, but it feels like I would be duplicating built in constraints if I do that, e.g CHECK (Col1 IS NOT NULL) for a NOT NULL constraint. Thanks, -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Column names including spaces
I seem to have stumbled upon what looks like a bug in SQLite. I accidentally created a column in a table that included a space in its name,eg Col 1. The CREATE TABLE command accepted without an error but if I try to access that column in any way, I get an error, no matter whether I specify the column name with no quotes, single quotes or double quotes. For example if I SELECT Col 1 FROM... I get a syntax error near 1 (which I'd expect). If I try 'SELECT CoL 1 FROM..., I get and error no such column Col1 - notice there is no space in the column name listed in the error message. I'm not unduly concerned since this column name should never have had a space in it in the first place but perhaps CREATE TABLE should flag an error in this situation if the column can't be accessed? -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with CHECK Constraint
Thanks, probably a foreign key would handle the example I gave. I'm really asking the general question what is possible within the CHECK constraint? Is it possible to base the check on a SELECT statement on another table? Pete In the example, what I am trying to check is if the value of the column is On Sun, Feb 19, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 2 Date: Sun, 19 Feb 2012 01:29:52 +0100 From: Petite Abeille petite.abei...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Help with CHECK Constraint Message-ID: 92bf7b2e-fe21-4cd3-a69c-44573b621...@gmail.com Content-Type: text/plain; charset=us-ascii On Feb 19, 2012, at 1:24 AM, Pete wrote: is it possible to check if the value of Col1 exists in a column in a different table? Perhaps you are looking for foreign constraints: http://www.sqlite.org/foreignkeys.html -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with CHECK Constraint
I;m trying to figure out just what I can do within the limits of a CHECK constraint. Is it possible to check the value of the column containing the CHECK constraint for presence in a column in a different table. For example, assuming the CHECK constraint is on TableA.Col1, is it possible to check if the value of Col1 exists in a column in a different table? Perhaps something like SELECT count(rowid) FROM TableB WHERE TableA.Col1=TableB.Col1. I've been trying this and getting syntax errors but not sure if I am getting the SELECT statement wrong or if it's just not possible to do this in a CHECK statement and perhaps I need to use a trigger instead. Thanks, -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating user written functions into the sqlitelibrary
OK, back to square 1 then I guess. Pete On Fri, Feb 17, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 8 Date: Thu, 16 Feb 2012 21:44:36 -0500 From: Igor Tandetnik itandet...@mvps.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] Incorporating user written functions into the sqlitelibrary Message-ID: jhkeuo$jge$1...@dough.gmane.org Content-Type: text/plain; charset=iso-8859-1 Pete p...@mollysrevenge.com wrote: I just noticed the load_extension(x) core function that seems like it would solve this problem since I can name the library file to be loaded. Only if the application enabled extensions with sqlite3_enable_load_extension. -- Igor Tandetnik -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating user written functions into the sqlite library
I just noticed the load_extension(x) core function that seems like it would solve this problem since I can name the library file to be loaded. Pete On Thu, Feb 16, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 1 Date: Wed, 15 Feb 2012 09:38:43 -0800 From: Pete p...@mollysrevenge.com To: sqlite-users@sqlite.org Subject: Re: [sqlite] Incorporating user written functions into the sqlite library Message-ID: cabx6j9nnnuxcsenmudsv1ssjbw+ckuuatwpuurkd9q1jtog...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 Thanks. I'll check on that but I'm not optimistic. Pete On Wed, Feb 15, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 7 Date: Tue, 14 Feb 2012 19:16:33 +0100 From: Stephan Beal sgb...@googlemail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Incorporating user written functions into the sqlite library Message-ID: CAKd4nAhH3BLSQ57hAXhi1EN33q4b1rw9v0SjG= 28dsvm51l...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 On Tue, Feb 14, 2012 at 7:10 PM, Pete p...@mollysrevenge.com wrote: the framework. Any help on how to make these user written functions available to the sqlite3 library in the framework would be much appreciated. If your framework provides a function which returns the raw sqlite3 handle then you can use http://www.sqlite.org/capi3ref.html#sqlite3_create_function on that handle to register the functions (do this right after opening the db if at all possible). If it does not provide such a handle then you're out of luck :/. -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating user written functions into the sqlite library
Thanks. I'll check on that but I'm not optimistic. Pete On Wed, Feb 15, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 7 Date: Tue, 14 Feb 2012 19:16:33 +0100 From: Stephan Beal sgb...@googlemail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Incorporating user written functions into the sqlite library Message-ID: CAKd4nAhH3BLSQ57hAXhi1EN33q4b1rw9v0SjG=28dsvm51l...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 On Tue, Feb 14, 2012 at 7:10 PM, Pete p...@mollysrevenge.com wrote: the framework. Any help on how to make these user written functions available to the sqlite3 library in the framework would be much appreciated. If your framework provides a function which returns the raw sqlite3 handle then you can use http://www.sqlite.org/capi3ref.html#sqlite3_create_function on that handle to register the functions (do this right after opening the db if at all possible). If it does not provide such a handle then you're out of luck :/. -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Surprising INTEGER PRIMARY KEY Behavior
When creating a new table, I accidentally assigned a default value of CURRENT_DATE to an INTEGER PRIMARY KEY AUTOINCREMENT field and was surprised that I didn't get an error on CREATE TABLE as a result. It seems that the default is ignored as an INSERT with DEFAULT VALUES assigned the correct value to the primary key field. IS this expected behavior? -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accessing temporary tables
I'd like to get some guidance on accessing and using temporary tables, i.e. those created using the TEMPORARY keyword. How do I get a list of any temporary tables in a database? They don't appear in the sqlite_master table. Once I have a list, can I use some form of PRAGMA table_info to get a list of the columns in a temp table? Thanks, -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 question
Thanks Larry. I had always been under the impression that forward slashes weren't acceptable in Windows path names so I'm glad to hear they are. The project I'm working on has to run on Macs as well as Windows and the forward slash is acceptable on both platforms. Pete On Tue, Jan 31, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 16 Date: Mon, 30 Jan 2012 21:12:59 -0800 From: Larry Brasfield larry_brasfi...@iinet.com To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3 question Message-ID: 4f27785b.7030...@iinet.com Content-Type: text/plain; charset=ISO-8859-1; format=flowed If you look at shell.c in the code implementing sqlite3.exe, you will find a procedure named resolve_backslashes(char *z) which performs a substitution of '\'-escaped characters that is similar to the C/C++ interpretation of string literals. That is what has sucked up those backslashes. Getting to what you should do: I advise using plain '/' as the path separator, unless you are fond of using '\\'. The OS is perfectly willing to accept '/'. (I curse IBM's insistence that the backwards convention started in CPM had to be prolonged into MS-DOS. There are fewer and fewer contexts where programs are silly enough to insist on seeing '\' rather than the '/' the rest of the world has settled upon. Cheers, -- Larry Brasfield -- Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 question
I'm not sure if this is an sqlite3 question or WIndows. I am trying to use the .output command to send the output from a SELECT statement to a file on WIndows 7. The .output command looks like: .output C:\Users\Pete\AppData\Local\Temp\sqlite3out.txt When the SELECT statement is executed the output ends up in a file named UsersPeteAppDataLocalTempsqlite3out.txt in whatever directory I am running in. The file name gets all the \ chars stripped out and the result used as a local filename. Unqualified filenames work as expected. I have run the same statements on a Mac (with the appropriate temp directory and forward slashes instead of backslashes) and the file ends up in the correct directory. Not being a Windows expert, I am at a loss to explain this. -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 49, Issue 27
I think you misunderstood my question. I simply want to run sqlite3 on Windows to create a database and use it's other functionality, not call it from another application. My question is simply what is the conventional place to put it on a Windows computer. Pete Message: 10 Date: Thu, 26 Jan 2012 19:09:35 + From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3 on Windows Message-ID: 75609007-23a7-4e84-be70-e1222f4c0...@bigfraud.org Content-Type: text/plain; charset=us-ascii On 26 Jan 2012, at 6:49pm, Pete wrote: I have been using sqlite on OS X for some time and sqlite3 comes pre-installed. I'm now looking at running some apps on Windows 7 and I don't think sqlite3 is pre-installed on that platform. I see there is a precompiled binary available of sqlite3 available for download - what directory should this be installed in? If you haven't already guessed from the question, I'm not vey familiar with WIndows! The precompiled application called sqlite3.exe on Windows, and sqlite3 on the Mac, is a stand-alone application that has no part to play when you are running other apps. It's a command-line program that lets you type SQLite commands and, just like every other application that uses SQLite3, has its own copy of the SQLite functions. Each application has its own copy of the SQLite3 functions. Normally they're built into the application itself and require no extra installation. If they come as a separate file that needs your attention the app itself should have instructions. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 on Windows
I have been using sqlite on OS X for some time and sqlite3 comes pre-installed. I'm now looking at running some apps on Windows 7 and I don't think sqlite3 is pre-installed on that platform. I see there is a precompiled binary available of sqlite3 available for download - what directory should this be installed in? If you haven't already guessed from the question, I'm not vey familiar with WIndows! -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Column Constraints
The diagrams for CREATE TABLE indicate that multiple column constraints can be defined for a column. I understand that a column can have multiple constraints of different types (UNIQUE, NOT NULL, PRIMARY KEY, etc) but not quite sure about foreign keys. Is it valid for one column to reference multiple table/column pairs? -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE INDEX
If I create an index for a column in a table with existing data in it, is the index automatically populated or do I have to use the REINDEX command? Thanks, -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign Key MATCH keyword
I may not have searched diligently enough but I can't find any information about the purpose of the foreign key MATCH keyword. Is it documented somewhere? Thanks, -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql server management studio like tool for SQLite
Opinions on the best one for OS X? Pete Message: 6 Date: Sat, 05 Nov 2011 15:46:36 -0500 From: John Horn pagemeis...@sbcglobal.net To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] sql server management studio like tool for SQLite Message-ID: 4eb5a0ac.8050...@sbcglobal.net Content-Type: text/plain; charset=ISO-8859-1; format=flowed Kit, I've tried many of the tools listed @ http://www.sqlite.org/cvstrac/wiki?p=ManagementTools. My hands-down vote is for SQLiteExpert Professional @ http://sqliteexpert.com/http://sqliteexpert.com/. In my opinion spending $59 for the Pro version is a **no-brainer** for many reasons. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Referencing column aliases
Thanks for the information and the VIEW suggestion. Since SQLite already provides an extension to reference column aliases in WHERE and JOIN clauses, is there any likelihood that it might be further extended to allow them in the SELECT statement itself? Thanks, Pete Message: 14 Date: Tue, 1 Nov 2011 08:26:17 -0400 From: Igor Tandetnik itandet...@mvps.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29 Message-ID: j8oogj$dib$1...@dough.gmane.org Content-Type: text/plain; charset=iso-8859-1 Pete p...@mollysrevenge.com wrote: Thanks. I guess I'd like to confirm just where column aliases can be referenced. I think they cannot be referenced within the list of column names in which they are defined, and they can be referenced in any other clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY, HAVING. Is that correct? Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause. -- Igor Tandetnik -- Message: 15 Date: Tue, 1 Nov 2011 12:36:27 + From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29 Message-ID: 2822a5a5-cce0-4bb2-90a7-7e6177c06...@bigfraud.org Content-Type: text/plain; charset=us-ascii On 1 Nov 2011, at 12:26pm, Igor Tandetnik wrote: Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause. Am I right that the most convenient way to do this might be by using a VIEW ? One could define a VIEW which had a number of columns like totalPrice = numItems * itemPrice then use this totalPrice column for things like sorting, right ? Simon. *** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29
Thanks. I guess I'd like to confirm just where column aliases can be referenced. I think they cannot be referenced within the list of column names in which they are defined, and they can be referenced in any other clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY, HAVING. Is that correct? Pete -- Message: 11 Date: Fri, 28 Oct 2011 16:34:15 -0400 From: Igor Tandetnik itandet...@mvps.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] Referring to column alias Message-ID: j8f3o3$mle$1...@dough.gmane.org Content-Type: text/plain; charset=UTF-8; format=flowed On 10/28/2011 4:28 PM, Pete wrote: I have another variation of this issue: SELECT col1 - col2 as Total, Total * price FROM tst ... gives an error no such column: Total. I can just repeat col1 - col2 of course, but wondering if there is a way to refer to Total within the SELECT. This is by design, blessed by SQL-92 standard. The closest you can get is something like SELECT Total, Total * price FROM (select col1 - col2 as Total, price from tst); This will likely be noticeably slower though. -- Igor Tandetnik -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Referring to column alias
I have another variation of this issue: SELECT col1 - col2 as Total, Total * price FROM tst .. gives an error no such column: Total. I can just repeat col1 - col2 of course, but wondering if there is a way to refer to Total within the SELECT. Thanks, Pete Message: 6 Date: Sun, 23 Oct 2011 21:02:07 +0200 From: Kees Nuyt k.n...@zonnet.nl To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23 Message-ID: kvo8a7lnardp6tmk7jtm0qpnc9eesqu...@dim53.demon.nl Content-Type: text/plain; charset=us-ascii On Sun, 23 Oct 2011 10:26:14 -0700, Pete p...@mollysrevenge.com wrote: Apologies, I omitted what is the real cause of the problem. This simplified SELECT illustrates the error: SELECT sum( colc * cold ) as total from tst where total 1000 The error message is misuse of aggregate: sum(). No error if I remove the where clause. A condition on an aggregate is expressed with a HAVING clause, not a WHERE clause. That is because WHERE and HAVING work on different stages of the SELECT statement: WHERE decides which rows to include in the aggregate, HAVING decides which results to present after aggregation. -- ( Kees Nuyt ) c[_] 5 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail
The one attraction of a forum to me is that it's searchable so I'd be able to check for any discussions before posting to the mailing list. Is there an archive for the mailing list somewhere which could serve the same prupose? Pete -- Message: 27 Date: Thu, 27 Oct 2011 16:45:12 +0100 From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] How about a proper forum rather than an e-mail list Message-ID: 41d980cd-ae28-46a3-85d0-f2789b9fb...@bigfraud.org Content-Type: text/plain; charset=us-ascii On 27 Oct 2011, at 4:41pm, Yves Goergen wrote: On 23.10.2011 16:05 CE(S)T, Simon Slavin wrote: Part of the attraction of this list is that I don't have to think Oh, I want to read a lot of SQLite-related stuff now !. What do you mean? I don't get it. If I had to go to a separate forum for my SQLite thoughts, I wouldn't bother to go very often. Because most of the time there's nothing there that interests me. An advantage of a mailing list is that the SQLite messages roll in gradually, mixed with other stuff that requires less concentration to understand. I'm not put off by the idea that I'll now have to wade through 20 posts I'm not interested in. Simon. -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail
Thanks Alex, that seems to work fine. One of the results that I got was on Nabble so looks like you can go to Nabble and search there to. Pete On Thu, Oct 27, 2011 at 11:17 AM, Alek Paunov a...@declera.com wrote: This was said above in the thread - try this google query: site:mail-archive.com inurl:sqlite-users How about a BTW, Some day I would be happy to use FTS powered search across the mail archives, maybe with additional feature (authorized with list-member credentials) for tagging and assigning additional related bookmarks (to the lines in source revisions, documentation and relevant blog articles) to some messages. On 27.10.2011 20:35, Pete wrote: The one attraction of a forum to me is that it's searchable so I'd be able to check for any discussions before posting to the mailing list. Is there an archive for the mailing list somewhere which could serve the same prupose? Pete -- Message: 27 Date: Thu, 27 Oct 2011 16:45:12 +0100 From: Simon Slavinslav...@bigfraud.org To: General Discussion of SQLite Databasesqlite-users@sqlite.**orgsqlite-users@sqlite.org Subject: Re: [sqlite] How about a proper forum rather than an e-mail list Message-ID:41D980CD-AE28-**46A3-85D0-F2789B9FB5AD@**bigfraud.org41d980cd-ae28-46a3-85d0-f2789b9fb...@bigfraud.org Content-Type: text/plain; charset=us-ascii On 27 Oct 2011, at 4:41pm, Yves Goergen wrote: On 23.10.2011 16:05 CE(S)T, Simon Slavin wrote: Part of the attraction of this list is that I don't have to think Oh, I want to read a lot of SQLite-related stuff now !. What do you mean? I don't get it. If I had to go to a separate forum for my SQLite thoughts, I wouldn't bother to go very often. Because most of the time there's nothing there that interests me. An advantage of a mailing list is that the SQLite messages roll in gradually, mixed with other stuff that requires less concentration to understand. I'm not put off by the idea that I'll now have to wade through 20 posts I'm not interested in. Simon. -- __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] SELECT on aggrgate
Pete Molly's Revenge http://www.mollysrevenge.com Thanks Kee, that explains it. Message: 6 Date: Sun, 23 Oct 2011 21:02:07 +0200 From: Kees Nuyt k.n...@zonnet.nl To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23 Message-ID: kvo8a7lnardp6tmk7jtm0qpnc9eesqu...@dim53.demon.nl Content-Type: text/plain; charset=us-ascii On Sun, 23 Oct 2011 10:26:14 -0700, Pete p...@mollysrevenge.com wrote: Apologies, I omitted what is the real cause of the problem. This simplified SELECT illustrates the error: SELECT sum( colc * cold ) as total from tst where total 1000 The error message is misuse of aggregate: sum(). No error if I remove the where clause. A condition on an aggregate is expressed with a HAVING clause, not a WHERE clause. That is because WHERE and HAVING work on different stages of the SELECT statement: WHERE decides which rows to include in the aggregate, HAVING decides which results to present after aggregation. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23
Apologies, I omitted what is the real cause of the problem. This simplified SELECT illustrates the error: SELECT sum( colc * cold ) as total from tst where total 1000 The error message is misuse of aggregate: sum(). No error if I remove the where clause. Pete Message: 2 Date: Sat, 22 Oct 2011 17:38:23 +0100 From: Simon Davies simon.james.dav...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Question re use of column alias in SELECT Message-ID: CANG6AhTGUUk0UwoZoYtqQEtfVPU+AxYOhU5SFnetW=skjgy...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 On 22 October 2011 17:28, Pete p...@mollysrevenge.com wrote: If I have a SELECT statement like: SELECT cola, colb, sum(tableb.colc * tableb.cold) AS Total FROM TableA GROUP BY cola ORDER BY Total What is tableb? ...I get an error, I think because of referring to Total in the ORDER BY clause. ?Is it not possible to refer to column aliases anywhere within a SELECT statement other than in the AS clause? ?If not, is there any other way to achieve this without repeating the sum expression? sqlite create table tst( id integer primary key, cola integer, colb integer, colc integer, cold integer ); sqlite SELECT cola, colb, sum( colc * cold ) as total from tst group by cola order by total; works for me, so I don't think that the alias is your problem. Pete Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question re use of column alias in SELECT
If I have a SELECT statement like: SELECT cola, colb, sum(tableb.colc * tableb.cold) AS Total FROM TableA GROUP BY cola ORDER BY Total ...I get an error, I think because of referring to Total in the ORDER BY clause. Is it not possible to refer to column aliases anywhere within a SELECT statement other than in the AS clause? If not, is there any other way to achieve this without repeating the sum expression? Pete Molly's Revenge http://www.mollysrevenge.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Creating a database with a script or SQLite command
I have a need to create a database if it doesn't already exist. The obvious solution is to just use: sqlite3 newdatabase.db Except that it not only creates the db but also opens that db for commands. I am running this from a script so I want to just want to run the command from a script so that I know the database exists before issuing other commands. I searched around the Internet for what I thought would be an easy answer and didn't find one. I am running SQLite 3.3.13 from BusyBox 1.1.3 Thanks -- Pete Helgren Value Added Software, Inc www.asaap.com www.opensource4i.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating a database with a script or SQLite command
Thanks. I'll add a little more info This script is used to set up the initial DB in a programmable device that will then record data to the database and the database should never be replaced. So I just figured there would be a simple way to issue the sqlite commands in script. Even found an example using a createdb command, although I could never see where that was an SQLite command So, you suggest I script it like so: if [ -f /data/newdatabase.db]; then echo Nothing to do, database exists else cp newdatabase.db /data/newdatabase.db fi I am not much of a Linux guy so the scripting might be wrong. Pete Helgren Value Added Software, Inc www.asaap.com www.opensource4i.com On 8/30/2011 7:38 PM, Simon Slavin wrote: On 31 Aug 2011, at 2:36am, Pete Helgren wrote: I have a need to create a database if it doesn't already exist. The obvious solution is to just use: sqlite3 newdatabase.db Except that it not only creates the db but also opens that db for commands. Make yourself an empty database file and keep it somewhere safe. When you need a new one just copy this existing file, and rename and/or move it to the right folder. Simon. ___ 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] Creating a database with a script or SQLite command
The only issue I had was finding an example of how I could do all of what you describe below in bash script. For example, if I put this in a script: sqlite3 newdatabase.db and save that as createdb.sh and execute it then the script never completes because SQLite is at the sqlite prompt, waiting for commands. Hence that option is a non-starter. Pete Helgren Value Added Software, Inc www.asaap.com www.opensource4i.com On 8/30/2011 8:23 PM, Jay A. Kreibich wrote: Of course, I'm not sure what the big deal is. By default, if you attempt to open an SQLite database file that does not exist, the system will just go ahead and create it. This sounds like exactly the desired behavior. There is no need to pre-create the file. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating a database with a script or SQLite command
I may end up going this direction, at the moment I am not having much luck with the conditional copy in Busybox. Your suggestion: cp -n newdatabase.db /data/newdatabase.db Isn't supported in the version of Busybox that I am running. Also the script example I tried: if [ -f /data/newdatabase.db]; then echo Nothing to do, database exists else cp newdatabase.db /data/newdatabase.db fi delivers the error [:missing] So I'll have to work through the scripting. Sure would be nice to have something like sqlite3 newdatabase.db .exit work so that it would just create the DB and exit Pete Helgren Value Added Software, Inc www.asaap.com www.opensource4i.com On 8/30/2011 8:14 PM, Simon Slavin wrote: Forgot to mention: copying an existing database file also lets you set up the file the way you want without having to issue separate commands. For instance, you could create blank tables. Or set a specific page size. Or include some sort of DRM or security check in the 'blank' file. Simon. ___ 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
[sqlite] Simulating the BINARY data type
How can I store and retrieve data in the equivalent of mySQL's BINARY datatype? The collation sequence doesn't matter in this instance. Is BLOB the appropriate sqlite datatype? Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 42, Issue 30
Thanks Igor. I assume your comment about the two queries not returning the same results is because the LEFT JOIN query would return TableA rows with no matching TableB rows, whereas the Scalar query would not. How could I change the scalar query to emulate the LEFT JOIN query? Pete -- Message: 11 Date: Wed, 29 Jun 2011 14:57:28 -0400 From: Igor Tandetnik itandet...@mvps.org Subject: Re: [sqlite] Query Alternatives To: sqlite-users@sqlite.org Message-ID: iufsld$o6h$1...@dough.gmane.org Content-Type: text/plain; charset=UTF-8; format=flowed On 6/29/2011 12:53 PM, Pete wrote: Looking for opinions on the relative efiiciency of Scalar queries versus non-scalar with JOIN statements. For example, the following two queries would produce the same results but would one of them be significantly faster than the other? SELECT column1, tableB.column2 FROM TableA LEFT JOIN TableB ON TablebB.indexcolumn = TableA.primarykeycolumn OR SELECT column1, (SELECT column2 FROM TableB WHERE TableB.indexcolumn = TableA.primarykeycolumn) FROM TableA The two queries are not equivalent - the first one may return more rows. In those cases where they are equivalent, I strongly doubt you'll notice any performance difference. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query Alternatives
Looking for opinions on the relative efiiciency of Scalar queries versus non-scalar with JOIN statements. For example, the following two queries would produce the same results but would one of them be significantly faster than the other? SELECT column1, tableB.column2 FROM TableA LEFT JOIN TableB ON TablebB.indexcolumn = TableA.primarykeycolumn OR SELECT column1, (SELECT column2 FROM TableB WHERE TableB.indexcolumn = TableA.primarykeycolumn) FROM TableA Thanks, Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 42, Issue 27
Thanks Michael, that solves the problem.
Pete
Message: 1
Date: Sun, 26 Jun 2011 12:01:43 +
From: Black, Michael (IS) michael.bla...@ngc.com
Subject: Re: [sqlite] Substring question
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Message-ID: 71635118-DC41-416E-AE85-F788177BBC96@mimectl
Content-Type: text/plain; charset=iso-8859-1
It's not obvious but this works
CREATE TABLE x (s string);
INSERT INTO x VALUES('ab:cdef');
INSERT INTO x VALUES('ghij:klmn');
sqlite select
ltrim(ltrim(s,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'),':')
from x;
ltri
cdef
klmn
Just make sure your char set contains all possible chars left of the :
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Substring question
I need to select a substring of a column that starts 1 character after a colon in the column and continues to the end of the column. For example, if the column contained abc:xyz I want the select statement to return only xyz (the characters after the colon). The substr function requires specific character positions - is there a way to do this? Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Substring question
I need to select a substring of a column that starts 1 character after a colon in the column and continues to the end of the column. For example, if the column contained abc:xyz I want the select statement to return only xyz (the characters after the colon). The substr function requires specific character positions - is there a way to do this? Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 42, Issue 6
Hear, hear! We're all capable of making our own decisions. Last time I looked, the title of this forum was General Discussion of SQLIte database, hopefully we can get back on topic. Pete Message: 26 Date: Mon, 6 Jun 2011 01:26:54 +0100 From: Simon Slavin slav...@bigfraud.org Subject: Re: [sqlite] Do I need to migrate to MySQL? To: General Discussion of SQLite Database sqlite-users@sqlite.org Message-ID: 619f829a-7fd4-407a-980a-4a5f0452e...@bigfraud.org Content-Type: text/plain; charset=utf-8 On 6 Jun 2011, at 12:20am, Dagdamor wrote: Darren Duncan dar...@darrenduncan.net ?(?) ? ? ?? Mon, 06 Jun 2011 05:08:45 +0600: MySQL should not be considered as the default choice of a non-lite SQL DBMS, for projects not currently using it, when you have a choice between multiple SQL DBMSs; instead, the default non-lite choice should be Postgres. Wow, communistic regime is back! Thanks for telling me what I should do and what not, what I should use and what not. ;) Dude, it's just advice. That's all any of us do: post our opinions. Calm down. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG : round(x,y) not consistent
On 2011-04-30, at 13:36, Jos Groot Lipman wrote: The rounding problem is documented in the FAQ: http://www.sqlite.org/faq.html#q16 That reference does not address the issue of the four rounding modes of the Intel FPUs. SQLite being a library rather than a process, does SQLite control the FPU rounding mode or does it rely on the calling thread's FPU rounding mode? This is very important to anyone intending to use the REAL datatype for a financial transaction database. They would be well advised to get local government approval for the system design because not all countries may yet have relaxed their taxation laws that previously insisted on a CURRENCY datatype which uses round half to even aka Bankers' Rounding. http://en.wikipedia.org/wiki/IEEE_754-2008#Rounding_algorithms ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request for help with SQLite Query to return missing Date/Time Ranges
On 2011-04-21, at 17:13, H. Phil Duby wrote: [...] With possible 'adjustments' for switching to daylight savings time and back, depending on what timezone the original data is stored in. If it was UTC, then no problem. But if it was in you local timezone, that used daylight savings time, then daylight savings time changes will cause a 1 hour gap, and a 1 hour overlap in the data once a year [each]. That is one of the main reasons why database timestamps should be recorded only in UTC and placed in a column with UTC in its name. SQLite date and time functions do not support the full syntax of ISO 8601 (why should they), but a column may usefully be assigned a default value of CURRENT_TIMESTAMP, which will be UTC if the operating system time is synchronized via an NTP or SNTP client. I've only ever needed to store timestamps as integer unix timestamps because it reduces both data bandwidth and CPU overhead, and makes finding not logged records quite easy. This is just from memory so it's just the essence of a possible solution, but it's well worth refining while designing temporal database systems: 1. Create and populate ReferenceTable having a column r with all expected time entries. Once per minute would store the values of unix_timestamp DIV 60 (obviously each row increments by 1 up until the current moment or the last expected database entry). 2. ALTER TABLE LoggedData ADD COLUMN r INTEGER DEFAULT 0 3. UPDATE LoggedData SET r = function_returning_unix_timestamp(TimestampColumn) DIV 60 WHERE r=0 Note DIV means integer division: either add a user defined function or synthesize it from built-in functions. 4. SELECT function_returning_UTC_string(ReferenceTable.r * 60) FROM ReferenceTable LEFT OUTER JOIN LoggedData USING (r) WHERE LoggedData.r IS NULL Now we have a lists of each minute for which there was no data logged. It should be easier to report not logged time spans by using external programming logic than by constructing endless SQL. This and other solutions will not work unless the logging is supposed to be unique to each minute. For data logged approximately each minute the phase of the sampling must be adjust to suit. If the following are valid and expected time values 00:00:05 00:00:55 [apparent gap] 00:02:10 00:03:15 00:03:45 [apparent gap] 00:05:10 then the sampling point would be better placed on 30 second boundaries instead of zero second boundaries. In this case the integer division function DIV must be modified to account for the offset, as must the SELECT query. Regards, Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corruption on many Windows XP hosts
On 2011-04-13, at 15:25, Richard Hipp wrote: On Wed, Apr 13, 2011 at 9:53 AM, Pavel Ivanov paiva...@gmail.com wrote: The fact that one engineer installed a site, began operating the app, then saw it become corrupt minutes later rules out power loss or hard resets in at least that case. An operating system level problem should have been noticed by now given it's Windows XP... And the file is locally held too. I don't know how Windows's disk cache works but theoretically when you set synchronous to OFF (0) OS can write database pages to disk in any order it likes, disregarding the order SQLite requires. So while one process writes those pages, another process can read those pages and because of random writing order second process can read inconsistent data, meaning it sees corrupted database. Pavel, please tell me you are wrong. Surely windows maintains disk cache coherency even in the absence of explicit FlushFileBuffers() calls? Can any windows experts comment on this? Of course OS disk caches maintain coherency. The only exceptions to this is power failure and hardware malfunction. FlushFileBuffers() may return before all pending writes have been written to the disk surface rendering the function useless. Some drives acknowledge a flush request once the data has been written to their internal cache instead of waiting until the data has been written to the disk surface. Many IDE drives are notorious for this problem, which is why robust systems used to have SCSI drives. [...] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corruption on many Windows XP hosts
On 2011-04-13, at 15:53, Richard Hipp wrote: On Wed, Apr 13, 2011 at 10:33 AM, Simon Slavin slav...@bigfraud.org wrote: On 13 Apr 2011, at 12:14pm, James Green wrote: sync=full does not work well for our app (no transactions). Far too slow. If you're not syncing, then section 3.2 of the page Richard probably indicates what's causing your corruption. We have assumed that disabling synchronous requires a power failure or hard reset in order to cause corruption. But that is based on the premise that operating system disk case is coherent. In other words, if two processes running on the same machine have the same disk file open, and if process A writes pages 1 and 2, in that order, and process B reads pages 1 and 2 at the same time, then process B will either see none of A's change, or B will see the changes to page 1 and not page 2, or B will see both change. It will never be the case that B will see the changes to page 2 but not the changes to page 1. Can somebody please confirm for me that windows works this way? If it turns out that I'm wrong and the windows disk cache is not coherent, then you will indeed need to set synchronous=FULL on windows in order to prevent corruption. Or, perhaps we can find some magic I/O barrier system call for windows that we can insert in place of the FlushFileBuffers() when synchronous is OFF. But for now, until I get better information, I'm going to assume that he windows disk cache is indeed coherent and that none of this is necessary. [...] Sorry to chip in again, Richard, but would you kindly send me a personal email on these issues. I know a thing or two about the intricacies of operating systems and have extensive experience of using SQLite (and many other databases) in non-robust environments. I would like to offer suggestions to help the original poster to solve the problem, but my lack of diction makes me unable to word my questions and suggestions in the right manner for this forum. Kindest regards to you all, Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 40, Issue 9
Yes, I already figured out what the problem was. I was asking for a solution. The SELECT scalar query gets me what I wanted. Pete Molly's Revenge http://www.mollysrevenge.com Message: 1 Date: Fri, 8 Apr 2011 08:17:22 -0400 From: Igor Tandetnik itandet...@mvps.org Subject: Re: [sqlite] GROUP BY Problem To: sqlite-users@sqlite.org Message-ID: inmue8$5nu$1...@dough.gmane.org Content-Type: text/plain; charset=iso-8859-1 Pete p...@mollysrevenge.com wrote: I am trying to use GROUP BY to summarise information from a main table and two sub tables, e.g.: SELECT c1,c2,sum(t2.c3),count(t3.c4) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1 LEFT JOIN t3.key3=t1.key1 GROUP BY t1.key1 The result is that the count column returns the count of (the number of t2 entries * the number of t3 entries), and the sum column returns (the t2 sum value * the count of entries in t3). Of course - you are doing your sums and counts on a cartesian product of these two tables. I suspect you want select c1, c2, (select sum(c3) from t2 where key2 = t1.key1), (select count(c4) from t3 where key3 = t1.key1) from t1; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GROUP BY Problem
I am trying to use GROUP BY to summarise information from a main table and two sub tables, e.g.: SELECT c1,c2,sum(t2.c3),count(t3.c4) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1 LEFT JOIN t3.key3=t1.key1 GROUP BY t1.key1 The result is that the count column returns the count of (the number of t2 entries * the number of t3 entries), and the sum column returns (the t2 sum value * the count of entries in t3). For example if the sum of t2.c3 is actually 1000 (from 3 rows) and the count of t3.c4 is 5, the sum column returns 5000 and the count column returns 15. If either of t2 or t3 has no qualifying entries, the calculation for the other table is correct. I guess GROUP BY isn't designed to deal with this type of situation. Can anyone suggest a way to do this? Thanks, Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY Problem
Thanks Pavel, that works. Pete Molly's Revenge http://www.mollysrevenge.com On Fri, Apr 8, 2011 at 4:36 AM, Pavel Ivanov paiva...@gmail.com wrote: Probably this could work: SELECT c1,c2,sum(t2.c3) / count(distinct t3.rowid),count(distinct t3.rowid) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1 LEFT JOIN t3 on t3.key3=t1.key1 GROUP BY t1.key1 And btw, you didn't say where your c1 and c2 come from but if they are from t1 and t1 has several rows with the same key1 but different c1 and c2 then you will get random data as a result of the query. Pavel On Thu, Apr 7, 2011 at 8:31 PM, Pete p...@mollysrevenge.com wrote: I am trying to use GROUP BY to summarise information from a main table and two sub tables, e.g.: SELECT c1,c2,sum(t2.c3),count(t3.c4) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1 LEFT JOIN t3.key3=t1.key1 GROUP BY t1.key1 The result is that the count column returns the count of (the number of t2 entries * the number of t3 entries), and the sum column returns (the t2 sum value * the count of entries in t3). For example if the sum of t2.c3 is actually 1000 (from 3 rows) and the count of t3.c4 is 5, the sum column returns 5000 and the count column returns 15. If either of t2 or t3 has no qualifying entries, the calculation for the other table is correct. I guess GROUP BY isn't designed to deal with this type of situation. Can anyone suggest a way to do this? Thanks, Pete ___ 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
