Re: [sqlite] Asking for SQL statement help

2009-09-07 Thread Dennis Volodomanov
Thank you for the quick replies and sorry for not being too clear.

I will try to state the problem more clearly, without my own attempts to solve 
it, as they are incorrect anyway.

The simplified schemas again:

CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD 
INTEGER );
CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );

And some sample data:

TABLEA
1|0|0|0
2|1|0|0
3|2|1|1
4|0|1|2
5|1|2|0
6|3|1|3

TABLEB, TABLEC, TABLED
1|A
2|B
3|C

The problem is that I need to grab rows from TABLEB where the ID of that row 
appears in TABLEA, at the same time satisfying other conditions such as IDC of 
that row also has multiple values (IDC=1 OR IDC=2, for example).

So, given:

IDC=1 AND (IDD=1 OR IDD=3)

I need to get rows 2 and 3 from TABLEB.

Hopefully this makes more sense :)

Best regards,

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Asking for SQL statement help

2009-09-07 Thread Simon Slavin

On 8 Sep 2009, at 6:31am, Dennis Volodomanov wrote:

> Let's say we've got 4 tables:
>
> CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC  
> INTEGER, IDD INTEGER );
> CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );


> What I'm trying to achieve is something like the following:
>
> SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT  
> JOIN TABLEB ON IDB=1 )
>
> But I also need to add additional constraints from TABLEC and TABLED  
> to narrow down the results. Basically, I only want results from  
> TABLEA with a given set of IDB, IDC and IDD (there could be multiple  
> of each of course).

You do not refer to your DATA columns in your SELECT command.  All you  
are doing is SELECTing on your IDs.  So can you explain why you can't  
use

SELECT * FROM TABLEB WHERE IDB = "xxx" AND IDC = "yyy" AND IDD = "zzz"

?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Asking for SQL statement help

2009-09-07 Thread P Kishor
On Tue, Sep 8, 2009 at 12:31 AM, Dennis Volodomanov wrote:
> Hello all,
>
> A small SQL problem, no doubt, for experts here.
>
> Let's say we've got 4 tables:
>
> CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD 
> INTEGER );
> CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );
>
> What I'm trying to achieve is something like the following:
>
> SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT JOIN 
> TABLEB ON IDB=1 )
>

The above statement doesn't make any sense. In your sub-select, you
are JOINing TABLEA to TABLEB, but you are not specifying the columns
on which to JOIN. You have to do like so...

TABLEA LEFT JOIN TABLEB ON TABLEA.some_column = TABLEB.some_column

then you can specify a WHERE clause for IDB = 1, but then, your query
doesn't make any sense.

> But I also need to add additional constraints from TABLEC and TABLED to 
> narrow down the results. Basically, I only want results from TABLEA with a 
> given set of IDB, IDC and IDD (there could be multiple of each of course).
>

Now things get even more confusing. You want results from TABLEA, so
you have to have

SELECT * FROM TABLEA
WHERE IDB IN (...) AND IDC IN (...) AND IDD IN (...)

(replace AND in the above statement with OR to get a bigger set back)

> Can you please help me out - any hints are greatly appreciated!

It would help to restate the problem more clearly, perhaps with some
example data, so the relationship between TABLEA and the other tables
is more clear.


>
> Thank you in advance,
>
>   Dennis
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Asking for SQL statement help

2009-09-07 Thread Darren Duncan
Dennis Volodomanov wrote:
> SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT JOIN 
> TABLEB ON IDB=1 )

First of all, "IDB=1" isn't a join condition; it doesn't compare a column from 
TABLEA with a column from TABLEB.  Did you mean to say "WHERE" rather than 
"ON"? 
  In which case you also had a cartesian product between TABLEA and TABLEB.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Asking for SQL statement help

2009-09-07 Thread Dennis Volodomanov
Hello all,

A small SQL problem, no doubt, for experts here.

Let's say we've got 4 tables:

CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD 
INTEGER );
CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );

What I'm trying to achieve is something like the following:

SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT JOIN 
TABLEB ON IDB=1 )

But I also need to add additional constraints from TABLEC and TABLED to narrow 
down the results. Basically, I only want results from TABLEA with a given set 
of IDB, IDC and IDD (there could be multiple of each of course).

Can you please help me out - any hints are greatly appreciated!

Thank you in advance,

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Importing data into SQLite

2009-09-07 Thread Robert Citek
Sounds good.  Let us know how things go.

P.S. one thing to try may be to use dos2unix to convert any text files
created in the Windows/DOS world to unix-format text files.

Regards,
- Robert

On Mon, Sep 7, 2009 at 7:22 PM, Kavita
Raghunathan wrote:
> The difference between what you did and what I did was perhaps that
>  I had excel make the .csv file, and maybe the excel version had something
> to do with it ?
> I'll retry with the exact same steps, and let you know the result. Thanks
> for your help!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Importing data into SQLite

2009-09-07 Thread Simon Slavin

On 8 Sep 2009, at 1:22am, Kavita Raghunathan wrote:

> I'm using the latest version downloaded last week: amalgamate 3-6-17.
>
> Robert,
> The difference between what you did and what I did was perhaps that
> I had excel make the .csv file, and maybe the excel version had  
> something
> to do with it ?
> I'll retry with the exact same steps, and let you know the result.  
> Thanks
> for your help!
>
> I was told I should get sqlmaestro or sqlexpert but I'm trying to do
> it without having to spend $100 and it sounds like that is possible.

There is no reason you should have to get a third-party tool just to  
import a .csv file.  The tools you have are designed to do it.

Take the file that cause the error and cut it down.  If you take the  
first line of the file, does it cause the error ?  if not, if you take  
the first 10 lines, do you get an error ?  What about the first 100  
lines ?  What about the first 1000 lines ?

Try to find the shorted file that causes this error.  You might be  
able to find something short enough that other people can test it for  
you and figure out the bug.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Importing data into SQLite

2009-09-07 Thread Kavita Raghunathan
Dan,
I'm using the latest version downloaded last week: amalgamate 3-6-17.

Robert,
The difference between what you did and what I did was perhaps that
 I had excel make the .csv file, and maybe the excel version had something
to do with it ? 
I'll retry with the exact same steps, and let you know the result. Thanks
for your help!

I was told I should get sqlmaestro or sqlexpert but I'm trying to do
it without having to spend $100 and it sounds like that is possible.

Regards,
Kavita
- Original Message -
From: "Robert Citek" 
To: "General Discussion of SQLite Database" 
Sent: Monday, September 7, 2009 11:08:41 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Importing data into SQLite

On Sun, Sep 6, 2009 at 9:32 PM, Kavita
Raghunathan wrote:
> Timothy and all,
> When I try to import a .csv, I get a segmentation fault:
> 1) First I set .seperator to ,
> 2) Then I type .import  
> 3) I see "Segmentation fault"
>
> Any ideas ?

Here's an example of how it worked for me.

$ cat data.csv
"a",1
"b",2
"c",3

$ sqlite3 sample.db .schema
CREATE TABLE data (foo text, bar int);

$ sqlite3 -separator , sample.db '.imp "data.csv" "data" '

$ sqlite3 -header -column sample.db 'select * from data ;'
foo bar
--  --
"a" 1
"b" 2
"c" 3

More details here, including caveats:

http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

Personally, I prefer to used tab-delimited files and then import by
specifying the separator as a tab:

$ sqlite3 -separator $'\t' sample.db '.imp "data.tsv" "data" '

This takes advantage of the bash shell's use of $'\t' to encode a tab.

Regards,
- Robert
___
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] Booleans in SQLite

2009-09-07 Thread Rich Shepard
On Mon, 7 Sep 2009, Jim Showalter wrote:

> Oracle doesn't have a native boolean type. You have to use INTEGER and
> interpret it.
>
> MySQL doesn't have a boolean type (it's just a synonym for TINYINT).
>
> SQL Server doesn't have a boolean type. You have to use BIT and
> interpret it.

   PostgreSQL has a boolean type. It can be 'true' or 'false'; unknown is
represented by the standard NULL type.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Booleans in SQLite

2009-09-07 Thread Jim Showalter
Oracle doesn't have a native boolean type. You have to use INTEGER and 
interpret it.

MySQL doesn't have a boolean type (it's just a synonym for TINYINT).

SQL Server doesn't have a boolean type. You have to use BIT and 
interpret it.

- Original Message - 
From: "Mark Hamburg" 
To: "General Discussion of SQLite Database" 
Sent: Monday, September 07, 2009 10:27 AM
Subject: Re: [sqlite] Booleans in SQLite


> The real argument for adding boolean support is not about space but
> about compatibility with dynamic languages with a boolean type that
> are exploiting SQLite's dynamic typing of values. Without a boolean
> type in SQLite, a glue layer has to guess whether a 0 means zero or
> false or a "NO" means the string "NO" or false or...
>
> Mark
>
> ___
> 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] Booleans in SQLite

2009-09-07 Thread Mark Hamburg
The real argument for adding boolean support is not about space but  
about compatibility with dynamic languages with a boolean type that  
are exploiting SQLite's dynamic typing of values. Without a boolean  
type in SQLite, a glue layer has to guess whether a 0 means zero or  
false or a "NO" means the string "NO" or false or...

Mark

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Sqlite uses a lot of memory during a delete operation

2009-09-07 Thread P Kishor
On Sun, Sep 6, 2009 at 5:23 PM, Benjamin Rutt wrote:
> I noticed sqlite is using a lot of memory (up to 300MB before it hits a data
> segment size ulimit and fails) during a delete operation.  This is
> reproducable using the 'sqlite3' executable distributed with sqlite.  My
> platform is sqlite 3.6.13 with the OS being solaris 10 on sparc
> architecture, but the problem is reproducible on linux as well if I copy the
> database file to a linux machine.  The relevant table schema is as follows:
>
>  CREATE TABLE old_conns (a TEXT, b INTEGER, c TEXT, d TEXT, e TEXT, start
> INTEGER, end INTEGER, f INTEGER, g INTEGER, h INTEGER, i INTEGER)
>  CREATE INDEX end_idx ON old_conns ( end )
>
> The delete operation fails as follows:
>
>  sqlite> DELETE FROM old_conns WHERE end < strftime('%s', 'now', '-7
> days');
>  SQL error: out of memory
>

Find out if the DELETEion is chewing up the memory or the SELECTion. Try

SELECT * FROM old_conns WHERE "end" >= strftime('%s', 'now', '-7 days');

If the above is quick, you can simply create a new table with that,
and then drop the old table.

CREATE TABLE new_conns AS SELECT * FROM old_conns WHERE "end" >=
strftime('%s', 'now', '-7 days');

DROP TABLE old_conns;

Of course, do change the name of the column "end" to something other
than a reserved keyword.


> I would have thought that the memory used would be small for a delete
> operation, but maybe there's some large temporary table being created in
> memory for this operation?  Perhaps the fact that it has to update the index
> along with the delete is causing memory usage where it wouldn't otherwise if
> there was no index?  It still fails if I set "pragma temp_store=1" which I
> believe instructs sqlite to put temporary tables on disk instead of memory.
>
> The sqlite file observable via 'ls -al' is about 8GB in size (although I've
> never vacuumed it), and the total size of the 'old_conns' table is about 68
> million rows.  I expect roughly 50% of them would be deleted by the above
> delete operation, but I have yet to see it succeed.
>
> Is there any obvious explanation for this?  Any administrative controls I
> can use to prevent it from happening?
>
> Thanks.
> --
> Benjamin Rutt
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Sqlite uses a lot of memory during a delete operation

2009-09-07 Thread Simon Slavin

On 7 Sep 2009, at 4:01pm, Benjamin Rutt wrote:

> Good idea.  Tried that, though, and it didn't help - the process  
> still grew
> and grew in memory.

Just in case, change the name of your column 'end' to something that  
isn't a keyword.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Importing data into SQLite

2009-09-07 Thread Robert Citek
On Sun, Sep 6, 2009 at 9:32 PM, Kavita
Raghunathan wrote:
> Timothy and all,
> When I try to import a .csv, I get a segmentation fault:
> 1) First I set .seperator to ,
> 2) Then I type .import  
> 3) I see "Segmentation fault"
>
> Any ideas ?

Here's an example of how it worked for me.

$ cat data.csv
"a",1
"b",2
"c",3

$ sqlite3 sample.db .schema
CREATE TABLE data (foo text, bar int);

$ sqlite3 -separator , sample.db '.imp "data.csv" "data" '

$ sqlite3 -header -column sample.db 'select * from data ;'
foo bar
--  --
"a" 1
"b" 2
"c" 3

More details here, including caveats:

http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

Personally, I prefer to used tab-delimited files and then import by
specifying the separator as a tab:

$ sqlite3 -separator $'\t' sample.db '.imp "data.tsv" "data" '

This takes advantage of the bash shell's use of $'\t' to encode a tab.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Importing data into SQLite

2009-09-07 Thread Dan Kennedy

On Sep 7, 2009, at 9:32 AM, Kavita Raghunathan wrote:

> Timothy and all,
> When I try to import a .csv, I get a segmentation fault:
> 1) First I set .seperator to ,
> 2) Then I type .import  
> 3) I see "Segmentation fault"

Which version of SQLite is this happening with?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Attached database

2009-09-07 Thread Griggs, Donald
 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Filipe Madureira

Is there a way to get a list of attached databases?
Either by SQL or by function call?
==

Greetings, Filipe,

PRAGMA database_list;
  http://sqlite.org/pragma.html#schema
Or, if you're using the commandline utility:
  .databases

You may want to read the entire section on PRAGMAs as there are many
useful ones.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Attached database

2009-09-07 Thread Filipe Madureira
Hi,

Is there a way to get a list of attached databases?
Either by SQL or by function call?

I want to attach several databases, at several points in time.
It can happen that I attempt to attach the same database several times, 
but I want to prevent different attaches to the same database if it is 
already attached.

I can create an internal structure of my own to manage this, but if it 
is already available in SQLite it is easier.


Thanks

Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
- 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Sqlite uses a lot of memory during a delete operation

2009-09-07 Thread Benjamin Rutt
>
> You're doing this on 68 million rows.  While it probably shouldn't
> give an error in this way, I can imagine various things that might
> cause it.
>
> To help with debugging, and also as a suggested fix until the problem
> can be investigated, could you pre-calculate your 'strftime' value,
> and use that in the command instead ?  It would help to see whether
> the calculation of this is the thing causing the error.  You can use
> SQLite to do the calculation
>
> sqlite> SELECT strftime('%s', 'now', '-7 days');
> 1251679819
>
> if you like.  Then take whatever value you get and plug it into the
> DELETE command:
>

Good idea.  Tried that, though, and it didn't help - the process still grew
and grew in memory.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users