Re: [sqlite] server process gives better concurrency - why?

[Igor Tandetnik]

Iker Arizmendi wrote:
> A single server process can be used to track "global" information, but
> shared memory mapped by unrelated processes might do as well. For
> example, a common mmap'ed file might serve to track lock info for
> each process.
> Of course, such a scheme would have to support handling of crashed
> processes without burdening the common case. But assuming it did, is
> this the main obstacle?

A better question may be - what's the benefit? What's the point of the 
exercise? What's the advantage over the traditional server architecture?

Igor Tandetnik 



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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

A single server process can be used to track "global" information, but
shared memory mapped by unrelated processes might do as well. For example,
a common mmap'ed file might serve to track lock info for each process.
Of course, such a scheme would have to support handling of crashed
processes without burdening the common case. But assuming it did, is
this the main obstacle?

Iker

Igor Tandetnik wrote:
> Iker Arizmendi wrote:
>> The question is whether a client-server design is /necessary/ to
>> efficiently implement higher concurrency. It appears to be easier
>> to do so with a client-server model, but is such a model required?
>> Are there functions performed by a server process that cannot be
>> carried out at all without it?
> 
> On a high, theoretical level, the advantage of a single server process 
> is that it has more context. It knows intimate details about everything 
> going on in the system, and can manage concurrent tasks more efficiently 
> using this information (e.g. use fine-grained locks). On the other hand, 
> multiple cooperating processes share only a limited amount of 
> information; each process knows very little beyond what it itself is 
> doing.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

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

Re: [sqlite] server process gives better concurrency - why?

[Simon Slavin]

On 9 Sep 2009, at 4:35am, Igor Tandetnik wrote:

> Iker Arizmendi wrote:
>> The question is whether a client-server design is /necessary/ to
>> efficiently implement higher concurrency.

Until you specify how high you want to go, it's impossible to figure  
out if something is necessary.

>> It appears to be easier
>> to do so with a client-server model, but is such a model required?
>> Are there functions performed by a server process that cannot be
>> carried out at all without it?
>
> On a high, theoretical level, the advantage of a single server process
> is that it has more context. It knows intimate details about  
> everything
> going on in the system, and can manage concurrent tasks more  
> efficiently
> using this information (e.g. use fine-grained locks). On the other  
> hand,
> multiple cooperating processes share only a limited amount of
> information; each process knows very little beyond what it itself is
> doing.

Also, it requires the client programmers to understand how to exploit  
the concurrency facilities.  They have to write special bits in their  
software to call the locking routines, etc..  The advantage of not  
having fine-grained locks is that the programmers just write standard  
SQL, with BEGIN ... COMMIT, and the DBMS figures out how to implement  
it.  Makes the system usable for programmers who don't have a lot of  
time to become experts in your particular SQL engine.

There's a model for concurrency that /doesn't/ require locks.  What  
happens is that if a user does something that begins a transaction,  
they get their own private copy of the database.  If the transaction  
ends with COMMIT, the shared version is thrown away and the private  
version is the new shared version.  If the transaction ends with  
ROLLBACK (or an error) then the private version is thrown away.  Under  
this model nobody ever locks out anyone else.  No locking, no waiting,  
no chance of deadlock.  Fantastic.

Except that two users can be in the middle of transactions at the same  
time.  So you have to figure out a way to merge two private copies of  
a database into one.  And it may or may not work just to execute the  
SQL commands you journalled, depending on how you feel updates should  
work.

There's no right answer to the general problem: it's down to what  
works best for your particular requirements in writing your particular  
application that uses an SQL engine.

Simon.
___
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

[Benjamin Rutt]

On Mon, Sep 7, 2009 at 12:28 PM, P Kishor  wrote:

> 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;
>

When I do the select as you suggested, the process remains contained to a
small amount of memory, which is good, but the result set from the select is
huge.  As I wrote originally, about 50% of the table would be deleted by my
delete, so about 50% of the table would be selected were I to use your
select.  So yes, I could create a tmp table and insert into it, add the
missing index, drop the old table, and rename the old to the new.  This
would work, but seems to me is quite an expensive hack.  It would also
require 200% of the original table space on disk in the worst case, and may
necessitate an extra vacuum operation after the fact to conserve disk space
(the original db file is 8GB so I suspect it would double in size to 16GB in
the worst case).  So it is a workaround, but not a cheap one.

Thanks.
___
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

[Benjamin Rutt]

Tried that, it didn't help.

On Mon, Sep 7, 2009 at 11:40 AM, Simon Slavin
wrote:

>
> 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
>



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

Re: [sqlite] server process gives better concurrency - why?

[Igor Tandetnik]

Iker Arizmendi wrote:
> The question is whether a client-server design is /necessary/ to
> efficiently implement higher concurrency. It appears to be easier
> to do so with a client-server model, but is such a model required?
> Are there functions performed by a server process that cannot be
> carried out at all without it?

On a high, theoretical level, the advantage of a single server process 
is that it has more context. It knows intimate details about everything 
going on in the system, and can manage concurrent tasks more efficiently 
using this information (e.g. use fine-grained locks). On the other hand, 
multiple cooperating processes share only a limited amount of 
information; each process knows very little beyond what it itself is 
doing.

Igor Tandetnik 



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

Re: [sqlite] Redundant conditions - are they parsed out?

[Dennis Volodomanov]

> > Not sure how to test it exactly at the moment
> 
> You run the query with the redundancy, and the equivalent query
> without,
> and time them. Run the same query many times in a loop if a single
> execution is too fast to measure.

Yes, that's the easy way out if I hard-code one query and make a test 
application out of it. I will probably have to do that once I get to the stage 
where I'll have enough time to start looking at this problem. For now, we can 
survive the way it is.

   Dennis

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

Re: [sqlite] Redundant conditions - are they parsed out?

[Igor Tandetnik]

Dennis Volodomanov wrote:
>> I suspect that any inefficiency introduced by that will be
>> immeasurably small. But, if in doubt, test it.
>
> Not sure how to test it exactly at the moment

You run the query with the redundancy, and the equivalent query without, 
and time them. Run the same query many times in a loop if a single 
execution is too fast to measure.

Igor Tandetnik



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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

The question is whether a client-server design is /necessary/ to
efficiently implement higher concurrency. It appears to be easier
to do so with a client-server model, but is such a model required?
Are there functions performed by a server process that cannot be
carried out at all without it?

Iker

Simon Slavin wrote:
 > If SQLite was to be
 > designed to handle multiple processes 'properly', it would have to be
 > rewritten as a client/server system.
 >
 > This would, of course, kill all the advantages of SQLite: it could no
 > longer be tiny, fast, and ultra-portable.  So it would be a bad design
 > choice for SQLite (bowing, of course, to DRH's right to do whatever he
 > pleases with it).
 >
 > This is why I get uneasy when I see posts here that suggest spinning
 > off threads especially to deal with locking issues, or do other things
 > that solve concurrency or latency problems.  Often you find that
 > making such a change in your program just leads to one of the threads
 > immediately being blocked by another, defeating the point of threading
 > in the first place.  Software has to be designed around what is
 > possible with the tools you're using, not around some mythical idea of
 > the perfect generic SQL engine.
 >
 > Simon.

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

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

Re: [sqlite] Redundant conditions - are they parsed out?

[Dennis Volodomanov]

> In my experience, SQLite doesn't eliminate such redundant clauses, nor
> remove trivial conditions (like 1=1 or 1 != 0), nor shortcircuit
> logical
> operations, nor hoist common subexpressions (those that don't depend on
> the current row) out of the loop. An expression is translated into VDBE
> program in a very literal manner.

Thank you for the heads up. I'm going to have to plan to rewrite our SQL 
generation for the major version, as some of our SQL statements are becoming 
very long and at least half of that can be taken out.

> I suspect that any inefficiency introduced by that will be immeasurably
> small. But, if in doubt, test it.

Not sure how to test it exactly at the moment, but in any case, if there's any 
inefficiency and we're doing 200K+ lookups with that small inefficiency, it may 
become measurable :)

Best regards,

   Dennis

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

Re: [sqlite] server process gives better concurrency - why?

[Simon Slavin]

On 9 Sep 2009, at 1:36am, Pavel Ivanov wrote:

>> Multiple processes has nothing to do with it, other than the usual
>> increases in complexity that would be added to any app.
>
> Except that it's much-much easier to implement fine-grained locks
> working inside one process, not dealing with file system locks and
> being sure that nobody but yourself will write to the database.

To the point where there are SQL systems that offer locking on the  
record level, column level, and even field level.  {shudder}

This is one of those things that get more complicated the longer you  
look at it.  For instance, there's no real difference between several  
different applications (or even processes within one application)  
using a database at the same time, and several different computers  
talking to a database server at one time.  If SQLite was to be  
designed to handle multiple processes 'properly', it would have to be  
rewritten as a client/server system.

This would, of course, kill all the advantages of SQLite: it could no  
longer be tiny, fast, and ultra-portable.  So it would be a bad design  
choice for SQLite (bowing, of course, to DRH's right to do whatever he  
pleases with it).

This is why I get uneasy when I see posts here that suggest spinning  
off threads especially to deal with locking issues, or do other things  
that solve concurrency or latency problems.  Often you find that  
making such a change in your program just leads to one of the threads  
immediately being blocked by another, defeating the point of threading  
in the first place.  Software has to be designed around what is  
possible with the tools you're using, not around some mythical idea of  
the perfect generic SQL engine.

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

Re: [sqlite] Redundant conditions - are they parsed out?

[Igor Tandetnik]

Dennis Volodomanov 
wrote:
> I was wondering whether the SQLite parser will parse out redundant
> conditions out of an SQL statement or will it process them as if they
> are all unique?

In my experience, SQLite doesn't eliminate such redundant clauses, nor 
remove trivial conditions (like 1=1 or 1 != 0), nor shortcircuit logical 
operations, nor hoist common subexpressions (those that don't depend on 
the current row) out of the loop. An expression is translated into VDBE 
program in a very literal manner.

> I'm building dynamic SQL statements and sometimes I can't avoid
> (well, without re-working a lot of code) things like:
>
> SELECT * FROM TABLEA WHERE COLUMNA=1 AND COLUMNA=1 AND COLUMNA=1 AND
> ( COLUMNB=2 OR COLUMNC=3 )
>
> Does it hurt performance having 3 COLUMNA statements like that in any
> way?

I suspect that any inefficiency introduced by that will be immeasurably 
small. But, if in doubt, test it.

Igor Tandetnik 



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

[sqlite] Redundant conditions - are they parsed out?

[Dennis Volodomanov]

Hello all,

I was wondering whether the SQLite parser will parse out redundant conditions 
out of an SQL statement or will it process them as if they are all unique?

I'm building dynamic SQL statements and sometimes I can't avoid (well, without 
re-working a lot of code) things like:

SELECT * FROM TABLEA WHERE COLUMNA=1 AND COLUMNA=1 AND COLUMNA=1 AND ( 
COLUMNB=2 OR COLUMNC=3 )

Does it hurt performance having 3 COLUMNA statements like that in any way?

Thanks in advance!

   Dennis

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

Re: [sqlite] server process gives better concurrency - why?

[Pavel Ivanov]

> Multiple processes has nothing to do with it, other than the usual
> increases in complexity that would be added to any app.

Except that it's much-much easier to implement fine-grained locks
working inside one process, not dealing with file system locks and
being sure that nobody but yourself will write to the database.

Pavel

On Tue, Sep 8, 2009 at 8:23 PM, Cory Nelson wrote:
> On Tue, Sep 8, 2009 at 4:58 PM, Iker Arizmendi  wrote:
>> Hello all,
>>
>> One oft-cited method to address SQLite's limited concurrency support is to
>> use a client-server database. Such databases enjoy better concurrency thanks
>> to their use of a master, coordinating process which is hard to obtain
>> efficiently using unrelated processes like those of SQLite. What's the key
>> to this master process's increased concurrency that is difficult to emulate
>> using a scheme among unrelated processes? Does the primary benefit of the
>> master process lie in its ability to efficiently detect abnormal termination
>> of child processes and deal with their locks?
>
> The reason for sqlite's poor concurrency is due to it using a single
> global reader/writer lock.  Other dbs usually have much finer grained
> page- or row- level locking, and MVCC.
>
> Multiple processes has nothing to do with it, other than the usual
> increases in complexity that would be added to any app.
>
> --
> Cory Nelson
> http://int64.org
> ___
> 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] server process gives better concurrency - why?

[Cory Nelson]

On Tue, Sep 8, 2009 at 4:58 PM, Iker Arizmendi  wrote:
> Hello all,
>
> One oft-cited method to address SQLite's limited concurrency support is to
> use a client-server database. Such databases enjoy better concurrency thanks
> to their use of a master, coordinating process which is hard to obtain
> efficiently using unrelated processes like those of SQLite. What's the key
> to this master process's increased concurrency that is difficult to emulate
> using a scheme among unrelated processes? Does the primary benefit of the
> master process lie in its ability to efficiently detect abnormal termination
> of child processes and deal with their locks?

The reason for sqlite's poor concurrency is due to it using a single
global reader/writer lock.  Other dbs usually have much finer grained
page- or row- level locking, and MVCC.

Multiple processes has nothing to do with it, other than the usual
increases in complexity that would be added to any app.

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

[sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

Hello all,

One oft-cited method to address SQLite's limited concurrency support is to
use a client-server database. Such databases enjoy better concurrency thanks
to their use of a master, coordinating process which is hard to obtain
efficiently using unrelated processes like those of SQLite. What's the key
to this master process's increased concurrency that is difficult to emulate
using a scheme among unrelated processes? Does the primary benefit of the
master process lie in its ability to efficiently detect abnormal termination
of child processes and deal with their locks?

Regards,
Iker

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
w: http://research.att.com

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

Re: [sqlite] Importing data into SQLite - text files are not really portable

[Robert Citek]

Yes, one big, long line.

As for displaying, depending on the program you use, \r may get
displayed as ^M.  For example:

$ echo -ne '\r\n' | cat -A
^M$

$ echo -ne '\r\n' | od -An -abcx
  cr  nl
 015 012
  \r  \n
 0a0d

Regards,
- Robert

> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
> Sent: Tuesday, September 08, 2009 2:14 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Importing data into SQLite
>
> FYI: Mac excel does not separate rows with \r, but inserts a ^M instead.
> (I dont have a windows machine with me, I wonder if this is Mac specific)
> Sqlite does not like this because a large file with many rows appears as
> 1 huge infinite line to sqlite.
> Kavita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite

[Simon Slavin]

On 8 Sep 2009, at 9:48pm, Robert Citek wrote:

> Just a guess, but you may be running into issues with the end-of-line
> character as they are different under linux (\n), Mac (\r), and
> Windows/DOS (\r\n).
>
> Linux has a tool to convert Windows/DOS end-of-lines to linux-style
> called dos2unix.  There may be one for Mac, too, but I'm not sure.  If
> not, you could use tr.  For example:
>
> $ < mac.csv  tr '\r' '\n' > unix.csv
>
> Good luck and let us know how things go.

The Mac OS is just a pretty GUI built on the top of Unix.  Therefore  
'tr' is present on the Mac.  Also you can do 'hexdump -C filename' to  
see exactly what characters a file is using for returns.  Or there's a  
good GUI application called '0xED' which will let you do both jobs.

What we're missing is the information on which characters the sqlite3  
tool will considers to be a line-end.  I'm sure someone could read the  
source code and find out.

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

[Robert Citek]

Just a guess, but you may be running into issues with the end-of-line
character as they are different under linux (\n), Mac (\r), and
Windows/DOS (\r\n).

Linux has a tool to convert Windows/DOS end-of-lines to linux-style
called dos2unix.  There may be one for Mac, too, but I'm not sure.  If
not, you could use tr.  For example:

$ < mac.csv  tr '\r' '\n' > unix.csv

Good luck and let us know how things go.

Regards,
- Robert

On Tue, Sep 8, 2009 at 1:47 PM, Kavita
Raghunathan wrote:
> Yes, this works. Must have been my original csv file.
> I was using mac based excel and I'll now try using the windows based excel.
> Thanks to all for the awesome support.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite - text files are not really portable

[Rich Shepard]

On Tue, 8 Sep 2009, Ribeiro, Glauber wrote:

> Unfortunately, the 3 main families of small computer operating systems
> have 3 different definitions of what a text file is...
>
> This causes no end of trouble when moving text files between these kinds
> of systems.

   I've never worked with a Mac so I have no knowledge of them. However, for
years I have used dos2unix (and, occasionally unix2dos) to do the requisite
line termination changes. It's no big deal to run the text file through the
filter.

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] Importing data into SQLite - text files are not really portable

[Ribeiro, Glauber]

Unfortunately, the 3 main families of small computer operating systems
have 3 different definitions of what a text file is...

DOS/Windows (PC): lines are terminated with CR+LF
Unix: lines are terminated with LF
Macintosh: lines are terminated with CR

This causes no end of trouble when moving text files between these kinds
of systems. 

Java takes the approach that any of (CR, LF, or CR+LF) is a valid line
terminator, but many or perhaps most non-Java software insists on having
the correct line terminator for the platform where it's running.

g


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
Sent: Tuesday, September 08, 2009 2:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Importing data into SQLite

FYI: Mac excel does not separate rows with \r, but inserts a ^M instead.
(I dont
have a windows machine with me, I wonder if this is Mac specific)
Sqlite does not like this because a large file with many rows appears as
1 
huge infinite line to sqlite.
Kavita
- Original Message -
From: "Kavita Raghunathan" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, September 8, 2009 11:47:38 AM GMT -08:00 US/Canada
Pacific
Subject: Re: [sqlite] Importing data into SQLite

Yes, this works. Must have been my original csv file. 
I was using mac based excel and I'll now try using the windows based
excel.
Thanks to all for the awesome support.

Kavita


SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table test(name text, id integer);
sqlite> .separator ","
sqlite> .import data.csv test
sqlite> 
sqlite> select * from test
   ...> ;
"a",1
"b",2
"c",3
sqlite> 

 Original Message -
From: "Robert Citek" 
To: "General Discussion of SQLite Database" 
Sent: Monday, September 7, 2009 9:08:41 AM GMT -08:00 US/Canada Pacific
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
___
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] Importing data into SQLite

[Kavita Raghunathan]

FYI: Mac excel does not separate rows with \r, but inserts a ^M instead. (I dont
have a windows machine with me, I wonder if this is Mac specific)
Sqlite does not like this because a large file with many rows appears as 1 
huge infinite line to sqlite.
Kavita
- Original Message -
From: "Kavita Raghunathan" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, September 8, 2009 11:47:38 AM GMT -08:00 US/Canada Pacific
Subject: Re: [sqlite] Importing data into SQLite

Yes, this works. Must have been my original csv file. 
I was using mac based excel and I'll now try using the windows based excel.
Thanks to all for the awesome support.

Kavita


SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table test(name text, id integer);
sqlite> .separator ","
sqlite> .import data.csv test
sqlite> 
sqlite> select * from test
   ...> ;
"a",1
"b",2
"c",3
sqlite> 

 Original Message -
From: "Robert Citek" 
To: "General Discussion of SQLite Database" 
Sent: Monday, September 7, 2009 9:08:41 AM GMT -08:00 US/Canada Pacific
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite

[Kavita Raghunathan]

Yes, this works. Must have been my original csv file. 
I was using mac based excel and I'll now try using the windows based excel.
Thanks to all for the awesome support.

Kavita


SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table test(name text, id integer);
sqlite> .separator ","
sqlite> .import data.csv test
sqlite> 
sqlite> select * from test
   ...> ;
"a",1
"b",2
"c",3
sqlite> 

 Original Message -
From: "Robert Citek" 
To: "General Discussion of SQLite Database" 
Sent: Monday, September 7, 2009 9:08:41 AM GMT -08:00 US/Canada Pacific
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] DELETE only if there's no error before

[Igor Tandetnik]

Oliver Peters  wrote:
>> Can't your application check the return code when running INSERT, and
>> not perform a DELETE if INSERT failed?
>
> don't know, if I can do this with OpenOffice (Base) - but I think I'd
> to program this (Starbasic) and I'm not really fit in programming (I
> only script from time to time in awk). So my first idea was to solve
> the problem in SQL - I assume your answer is that there's no way to
> do it only with sqlite?

I'm not sure what you mean by "only with sqlite". SQLite doesn't do 
anything by itself, it requires a host application to drive it. How 
exactly do you run SQL statements? Is there no indication in your SQLite 
binding of whether the statement succeeded or failed?

If your application ignores errors from INSERT and proceeds with DELETE 
anyway, there's nothing SQLite can do to stop you. It can only assume 
that you know what you are doing, and do what you tell it to do.

Igor Tandetnik 



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

Re: [sqlite] Not able to properly inner join?

[Igor Tandetnik]

Aaron Drake  wrote:
> The column was originally populated with both numeric and NULL
> values. I
> did not specify what type of data would be in the column, preferring
> to
> let SQLite dynamically type it. However in this case it has come back
> to
> bite me since it appears that any column with both numeric and NULL
> values will be considered as TEXT.

The "type" of the column is not determined in any way by its contents. 
For details, see http://www.sqlite.org/datatype3.html

I bet you inserted your data as text in the first place, as in

INSERT INTO AlertErrors(..., subCode) VALUES (..., '0')

or something equivalent. Try running this:

update AlertErrors set subCode = cast(subCode as integer);

then run your ogirinal query again. Once you get this working, examine 
the parts of your program where you populate AlertErrors table (and 
perhaps other tables), and make sure you insert integers, and not 
strings, wherever you expect to get integers back later.

Igor Tandetnik



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

Re: [sqlite] Not able to properly inner join?

[Aaron Drake]

The column was originally populated with both numeric and NULL values. I
did not specify what type of data would be in the column, preferring to
let SQLite dynamically type it. However in this case it has come back to
bite me since it appears that any column with both numeric and NULL
values will be considered as TEXT.

Thanks taking a look at my SQL syntax. Such a silly problem that I've
caused.

Thanks again!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor
Sent: Tuesday, September 08, 2009 9:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Not able to properly inner join?

On Tue, Sep 8, 2009 at 11:20 AM, Aaron Drake wrote:
> The tokens were placeholders all fields are numeric.
>
> The query SELECT * FROM AlertErrors WHERE id = 10 and code = 50
returns
> two rows. The query SELECT * FROM AlertErrors WHERE id = 10 and code =
> 50 and subCode = 0 returns zero rows.

and therein lies your answer. You JOIN condition is returning no rows,
so no AlertErrors.error is being returned to JOIN with
ErrorsText.error. SQLite is performing correctly.

>
> However if I do SELECT * from alerterrors where id = 10 and code = 50
> and subcode = '0' I get two rows. How can it be that subcode is a
> character?

Dunno. Did you make is a character? SQLite does only what it is asked
to do (although sometimes it can be counterintuitive, but it is almost
always the operator's error).



> Can I recreate the table or alter the column so that it is a
> numeric value?
>

check your table definition. If subcode is not defined as INTEGER, you
can create a new table with the proper definitions and then populate
the table correctly.


> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor
> Sent: Tuesday, September 08, 2009 8:46 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Not able to properly inner join?
>
> On Tue, Sep 8, 2009 at 10:41 AM, Aaron Drake
wrote:
>> Greetings,
>>
>> I'm new to SQL and SQLite3, I usually create my SQL queries using a
>> query builder and then run it against the SQLite3 engine and tweak it
>> until I get my desired result.
>>
>> I'm trying to join two tables using this statement:
>>
>> SELECT ErrorsText.*, AlertErrors.extra FROM AlertErrors INNER JOIN
>> ErrorsText ON AlertErrors.error = ErrorsText.error WHERE
> (AlertErrors.id
>> = A) AND (AlertErrors.code = Foo) AND (AlertErrors.subCode = 0)
>>
>
> Assuming that 'A' and 'Foo' above are placeholders and neither actual
> strings nor column names, the query looks mostly fine.
>
> What does the following return?
>
> SELECT * FROM AlertErrors WHERE id = A and code = Foo and subCode = 0
>
> Of course, if A and Foo are actual strings, then you have to enclose
> them in single quotes.
>
>>
>> Two rows of data should be returned, I've tried this on other SQL
>> engines like SQL Server Compact and it returns both rows as expected.
> If
>> I remove the last AND clause the rows are returned as expected, but I
>> must have it because there are times that a subCode may be crucial in
>> retrieving the proper text.
>>
>> I thought that INNER JOIN was completely working in SQLite3, I don't
> see
>> it mentioned in the docs as one of the joins that is currently being
>> worked on.
>>
>> Is there some kind of error in my SQL statement? Does SQLite3 parse
> SQL
>> statements different than...say, a Microsoft product would? What
query
>> designer do most of you use to create complex queries? Which do you
>> recommend for someone like me?
>>
>> Thanks!
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not able to properly inner join?

[P Kishor]

On Tue, Sep 8, 2009 at 11:20 AM, Aaron Drake wrote:
> The tokens were placeholders all fields are numeric.
>
> The query SELECT * FROM AlertErrors WHERE id = 10 and code = 50 returns
> two rows. The query SELECT * FROM AlertErrors WHERE id = 10 and code =
> 50 and subCode = 0 returns zero rows.

and therein lies your answer. You JOIN condition is returning no rows,
so no AlertErrors.error is being returned to JOIN with
ErrorsText.error. SQLite is performing correctly.

>
> However if I do SELECT * from alerterrors where id = 10 and code = 50
> and subcode = '0' I get two rows. How can it be that subcode is a
> character?

Dunno. Did you make is a character? SQLite does only what it is asked
to do (although sometimes it can be counterintuitive, but it is almost
always the operator's error).



> Can I recreate the table or alter the column so that it is a
> numeric value?
>

check your table definition. If subcode is not defined as INTEGER, you
can create a new table with the proper definitions and then populate
the table correctly.


> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor
> Sent: Tuesday, September 08, 2009 8:46 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Not able to properly inner join?
>
> On Tue, Sep 8, 2009 at 10:41 AM, Aaron Drake wrote:
>> Greetings,
>>
>> I'm new to SQL and SQLite3, I usually create my SQL queries using a
>> query builder and then run it against the SQLite3 engine and tweak it
>> until I get my desired result.
>>
>> I'm trying to join two tables using this statement:
>>
>> SELECT ErrorsText.*, AlertErrors.extra FROM AlertErrors INNER JOIN
>> ErrorsText ON AlertErrors.error = ErrorsText.error WHERE
> (AlertErrors.id
>> = A) AND (AlertErrors.code = Foo) AND (AlertErrors.subCode = 0)
>>
>
> Assuming that 'A' and 'Foo' above are placeholders and neither actual
> strings nor column names, the query looks mostly fine.
>
> What does the following return?
>
> SELECT * FROM AlertErrors WHERE id = A and code = Foo and subCode = 0
>
> Of course, if A and Foo are actual strings, then you have to enclose
> them in single quotes.
>
>>
>> Two rows of data should be returned, I've tried this on other SQL
>> engines like SQL Server Compact and it returns both rows as expected.
> If
>> I remove the last AND clause the rows are returned as expected, but I
>> must have it because there are times that a subCode may be crucial in
>> retrieving the proper text.
>>
>> I thought that INNER JOIN was completely working in SQLite3, I don't
> see
>> it mentioned in the docs as one of the joins that is currently being
>> worked on.
>>
>> Is there some kind of error in my SQL statement? Does SQLite3 parse
> SQL
>> statements different than...say, a Microsoft product would? What query
>> designer do most of you use to create complex queries? Which do you
>> recommend for someone like me?
>>
>> Thanks!
>> ___
>> 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] Not able to properly inner join?

[Aaron Drake]

The tokens were placeholders all fields are numeric.

The query SELECT * FROM AlertErrors WHERE id = 10 and code = 50 returns
two rows. The query SELECT * FROM AlertErrors WHERE id = 10 and code =
50 and subCode = 0 returns zero rows.

However if I do SELECT * from alerterrors where id = 10 and code = 50
and subcode = '0' I get two rows. How can it be that subcode is a
character? Can I recreate the table or alter the column so that it is a
numeric value?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor
Sent: Tuesday, September 08, 2009 8:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Not able to properly inner join?

On Tue, Sep 8, 2009 at 10:41 AM, Aaron Drake wrote:
> Greetings,
>
> I'm new to SQL and SQLite3, I usually create my SQL queries using a
> query builder and then run it against the SQLite3 engine and tweak it
> until I get my desired result.
>
> I'm trying to join two tables using this statement:
>
> SELECT ErrorsText.*, AlertErrors.extra FROM AlertErrors INNER JOIN
> ErrorsText ON AlertErrors.error = ErrorsText.error WHERE
(AlertErrors.id
> = A) AND (AlertErrors.code = Foo) AND (AlertErrors.subCode = 0)
>

Assuming that 'A' and 'Foo' above are placeholders and neither actual
strings nor column names, the query looks mostly fine.

What does the following return?

SELECT * FROM AlertErrors WHERE id = A and code = Foo and subCode = 0

Of course, if A and Foo are actual strings, then you have to enclose
them in single quotes.

>
> Two rows of data should be returned, I've tried this on other SQL
> engines like SQL Server Compact and it returns both rows as expected.
If
> I remove the last AND clause the rows are returned as expected, but I
> must have it because there are times that a subCode may be crucial in
> retrieving the proper text.
>
> I thought that INNER JOIN was completely working in SQLite3, I don't
see
> it mentioned in the docs as one of the joins that is currently being
> worked on.
>
> Is there some kind of error in my SQL statement? Does SQLite3 parse
SQL
> statements different than...say, a Microsoft product would? What query
> designer do most of you use to create complex queries? Which do you
> recommend for someone like me?
>
> Thanks!
> ___
> 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
===
Sent from Madison, WI, United States
___
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] STL and SQLite

[Atul_Vaidya]

Are you planning on querying for values through SQLITE or are you just
using it for persistent storage? If it's just for storage then it's
really easy.

Thanks Teg-3,
I am planning to use it for storage
Regards,
Atul


-- 
View this message in context: 
http://www.nabble.com/STL-and-SQLite-tp25340733p25348961.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Not able to properly inner join?

[P Kishor]

On Tue, Sep 8, 2009 at 10:41 AM, Aaron Drake wrote:
> Greetings,
>
>
>
> I'm new to SQL and SQLite3, I usually create my SQL queries using a
> query builder and then run it against the SQLite3 engine and tweak it
> until I get my desired result.
>
>
>
> I'm trying to join two tables using this statement:
>
>
>
> SELECT ErrorsText.*, AlertErrors.extra FROM AlertErrors INNER JOIN
> ErrorsText ON AlertErrors.error = ErrorsText.error WHERE (AlertErrors.id
> = A) AND (AlertErrors.code = Foo) AND (AlertErrors.subCode = 0)
>
>

Assuming that 'A' and 'Foo' above are placeholders and neither actual
strings nor column names, the query looks mostly fine.

What does the following return?

SELECT * FROM AlertErrors WHERE id = A and code = Foo and subCode = 0


Of course, if A and Foo are actual strings, then you have to enclose
them in single quotes.


>
> Two rows of data should be returned, I've tried this on other SQL
> engines like SQL Server Compact and it returns both rows as expected. If
> I remove the last AND clause the rows are returned as expected, but I
> must have it because there are times that a subCode may be crucial in
> retrieving the proper text.
>
>
>
> I thought that INNER JOIN was completely working in SQLite3, I don't see
> it mentioned in the docs as one of the joins that is currently being
> worked on.
>
>
>
> Is there some kind of error in my SQL statement? Does SQLite3 parse SQL
> statements different than...say, a Microsoft product would? What query
> designer do most of you use to create complex queries? Which do you
> recommend for someone like me?
>
>
>
> Thanks!
>
> ___
> 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
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] STL and SQLite

[Teg]

Hello Atul_Vaidya,

Tuesday, September 8, 2009, 4:08:50 AM, you wrote:

A> It would be great if i can refer to any example for this
A> Regards,
A> Atul

A> Michal Seliga wrote:
>> 
>> Atul_Vaidya wrote:
>>> Hi,
>>> How to store a multimap in SQlite database ?
>>> My requirement is that i want to store a multimap in a column
>>> of
>>> the SQlite table
>>> Regards,
>>> Atul
>> 
>> but you have to use some additional
>> encoding (base64, bintohex, uuencode/uudecode, whatever you want) to make
>> sure
>> that stored text is pure ASCII and content won't be damaged
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 

It's really not much different than writing it to a flat file. Just
write a wrapper around the sqlite calls that you pass your map too,
enum the map, insert the values in a 2 column integer table and then
have another function that enumerates the table and re-constructs the
map entries when you want to load it back up again. You can go the
streaming route but, why bother? I don't see the point of streaming
for something really basic like this. Assuming both integers together
represent the "key" to the map, I'd "primary key" over both values.

Are you planning on querying for values through SQLITE or are you just
using it for persistent storage? If it's just for storage then it's
really easy.

-- 
Best regards,
 Tegmailto:t...@djii.com

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

[sqlite] Not able to properly inner join?

[Aaron Drake]

Greetings,

 

I'm new to SQL and SQLite3, I usually create my SQL queries using a
query builder and then run it against the SQLite3 engine and tweak it
until I get my desired result.

 

I'm trying to join two tables using this statement:

 

SELECT ErrorsText.*, AlertErrors.extra FROM AlertErrors INNER JOIN
ErrorsText ON AlertErrors.error = ErrorsText.error WHERE (AlertErrors.id
= A) AND (AlertErrors.code = Foo) AND (AlertErrors.subCode = 0)

 

Two rows of data should be returned, I've tried this on other SQL
engines like SQL Server Compact and it returns both rows as expected. If
I remove the last AND clause the rows are returned as expected, but I
must have it because there are times that a subCode may be crucial in
retrieving the proper text.

 

I thought that INNER JOIN was completely working in SQLite3, I don't see
it mentioned in the docs as one of the joins that is currently being
worked on.

 

Is there some kind of error in my SQL statement? Does SQLite3 parse SQL
statements different than...say, a Microsoft product would? What query
designer do most of you use to create complex queries? Which do you
recommend for someone like me?

 

Thanks!

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

Re: [sqlite] STL and SQLite

[Cory Nelson]

On Mon, Sep 7, 2009 at 11:51 PM,
Atul_Vaidya wrote:
>
> Hi,
>    How to store a multimap in SQlite database ?
> My requirement is that i want to store a multimap in a column of
> the SQlite table
> Regards,
> Atul

For those who have no idea what a multimap is, it's a key:value
mapping with non-unique keys.

If you really want to store it in a single column, I would take a look
at the Boost serialization library and store it in a blob.  If you
want to actually use the multimap from SQL, or if you want to make it
more portable, you can easily use SQL types directly.

create table foo(key integer, value integer);
create index foo_keys on foo(key);

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

Re: [sqlite] DELETE only if there's no error before

[Oliver Peters]

Hello

[...]
> 
> Can't your application check the return code when running INSERT, and 
> not perform a DELETE if INSERT failed?
> 
> Igor Tandetnik

don't know, if I can do this with OpenOffice (Base) - but I think I'd to 
program this (Starbasic) and I'm not really fit in programming (I only script 
from time to time in awk). So my first idea was to solve the problem in SQL - I 
assume your answer is that there's no way to do it only with sqlite?

Oliver Peters

Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate
für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/

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

Re: [sqlite] sqlite3_exec fails on arm

[D. Richard Hipp]

On Sep 8, 2009, at 3:16 AM, priyanka sharma wrote:

> Hi
> the sqlite3_exec is called from the following code snippet.
>
> char database_buffer[BUFSIZ];
>  rc = sqlite3_open (database_buffer, );


Please tell us what you were hoping to accomplish with the two  
consecutive lines of code shown above.

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] STL and SQLite

[Igor Tandetnik]

Atul_Vaidya wrote:
>How to store a multimap in SQlite database ?
> My requirement is that i want to store a multimap in a
> column of the SQlite table

That's a strange and unusual requirement to have. Why do you believe you 
have it? What are you really trying to achieve?

Igor Tandetnik 



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

Re: [sqlite] DELETE only if there's no error before

[Igor Tandetnik]

Oliver Peters wrote:
> /* the following actions should be
>
> 1. INSERT
>
> 2. DELETE everything in table a if the INSERT worked fine
>
> */

Can't your application check the return code when running INSERT, and 
not perform a DELETE if INSERT failed?

Igor Tandetnik



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

Re: [sqlite] Design for concurrency

[Chris]

On Tue, 2009-09-08 at 09:45 +0200, Marian Aldenhoevel wrote:
> C) A silly idea.
> 
> I could also split the database file into two copies.
> 
If the readers can tolerate data that is one update behind
currency then you could use two database files and have the
reading program(s) switch to the other database after each
round of updates.

In the background apply the same updates to the off-line copy.

No need to copy the database entirely except at the very
start of use.

By choosing the number of updates to make in each cycle you
have the possibility to limit the degree of "out of date".


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

Re: [sqlite] how to save image to sqlite..

[Sebastian Bermudez]

i made it encoding image with base64... 

--- El lun 7-sep-09, Eka Rudito  escribió:

> De: Eka Rudito 
> Asunto: [sqlite] how to save image to sqlite..
> Para: sqlite-users@sqlite.org
> Fecha: lunes, 7 de septiembre de 2009, 8:35 pm
> hello there
> 
> i am new bie in sqlite and this forum too..
> i want save image/blob in sqlite using c#, anybody have
> example or clue..
> 
> thanks
> 
> -- 
> 
> Regards..
> 
> Eka
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


  Yahoo! Cocina

Encontra las mejores recetas con Yahoo! Cocina.


http://ar.mujer.yahoo.com/cocina/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Design for concurrency

[Marian Aldenhövel]

Hi,

I am currently designing a system where I am planning to use SQLite3 and 
would like some advice on basic architecture. The system is for a 
point-of-sale-like semi-embedded application.

The program will be running on quite limited hardware with Compact Flash 
for storage. The database consists of currently about 350k records, 
on-disk that amounts to a 48MB SQLite database file. That number is 
likely to grow over the lifetime of the application, maybe by a factor 
of two or so.

The main operation will be single-record queries. The records being 
identified via a primary key or one of two other indexed columns. These 
reads are initiated via a user-interaction and it is not predictable 
when they occur. There may be a few per day, or one every minute.

These reads need to be quick, that is the overriding design criterion. 
Say they may take two or three seconds at most, an arbitrary upper bound 
for the sake of discussion. At first glance that poses no problem at 
all, those reads are very fast.

But there will be updates to the database as well. These are cyclic at 
preplanned times, several times a day and may want to update anything 
from a few hundred to a few thousand records. The data is fetched from a 
website as CSV and parsed and transformed into INSERT OR UPDATE statements.

Now the problem becomes one of concurrency: How can I ensure an upper 
bound on the time it takes to do the single-record reads in this scenario?

Technically my programm will be a single multithreaded executable 
written in C++. I can assume that this program will be the only program 
using the database, so synchronisation mechanism outside of SQLite are 
acceptable, but it would be nice to do without.

A) The naive approach.

For the update start a transaction, do all the INSERT OR UPDATES in one 
batch and then commit it.

I have implemented that in a predecessor-version that would not allow 
any other approach and is not concurrent. Users are locked out during 
the update which is unacceptable for the new design.

I have timed the COMMIT to take anything between 30 seconds and 3 
minutes, depending on the number of updated records.

As I understand SQLite locking no reads can be serviced in the time it 
actually takes to COMMIT the transaction. Right?

So that won't work.

B) The a little less naive approach.

Instead of batching all the updates into one transaction only batch 
some. Tune the size of the batches so that their individual COMMIT does 
not take too long so that the time-constraint for the reads can be met. 
ACIDity is not an issue here, partial updates (some records updated, 
others not) may be applied without any ill effect, the remaining records 
would then be updated as part of the next cycle.

I would have to make sure that reads get a chance even if the writing 
process starts fresh transaction in a tight loop. I think that can be 
done with the SQLite concurrency system if I understand it correctly.

The total time for the update would be greatly increased of course due 
to it being split into many transactions. By how long would remain to be 
tested, I have no idea. But that is not a big problem in itself as long 
as the reads still are being serviced.

C) A silly idea.

I could also split the database file into two copies.

One "active" copy that is used to service the reads. And one "in 
transit" copy that is being updated.

So at the start of an update cycle I would make a copy of the active 
database file. Either on the filesystem (would that be safe? A hot copy 
of a SQLite database file that is only read from?) or using SQLite itself.

Then I would update the copy in one transaction. Commit it. And after 
the commit somehow flag the copy as active so that the next read will be 
from that copy.

This approach would decouple reads and writes at the price of added 
complexity for the switch, making sure it's all well-synchronized etc.. 
Homemade concurrency.

Making a copy of the database on the CF card currently takes around two 
minutes. So that would have to be added on top of the update time and 
the single-batch commit time. But it's a constant (well, for a given 
database size only of course) and does not interfere with the reads. So 
no problem here.



Those are the strategies I was able to think of so far. Comments are 
welcome. Better ideas as well. Please point out my dumb errors in any case.

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

[sqlite] how to save image to sqlite..

[Eka Rudito]

hello there

i am new bie in sqlite and this forum too..
i want save image/blob in sqlite using c#, anybody have example or clue..

thanks

-- 

Regards..

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

[sqlite] DELETE only if there's no error before

[Oliver Peters]

Hello out there,

I've 2 tables


CREATE TABLE IF NOT EXISTS a(
first   INTEGER,
second  INTEGER
);

CREATE TABLE IF NOT EXISTS b(
first   INTEGER,
second  INTEGER,
PRIMARY KEY (first,second)
);

-- and INSERT data in table a


INSERT INTO a(first,second) VALUES(1,2);
INSERT INTO a(first,second) VALUES(1,2);


/* the following actions should be

1. INSERT

2. DELETE everything in table a if the INSERT worked fine

*/

INSERT INTO b(first,second)
SELECT first,second
FROM a;

DELETE
FROM a;


I already tried to solve my problem this way but it seems that I don't 
understand the concept of transactions because it doesn't stop the whole thing 
after the failed INSERT:

BEGIN TRANSACTION;
INSERT INTO b(first,second)
SELECT first,second
FROM a;

DELETE
FROM a;
COMMIT;

Thanks for your time
Oliver

Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate
für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/

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

Re: [sqlite] STL and SQLite

[Atul_Vaidya]

It would be great if i can refer to any example for this
Regards,
Atul

Michal Seliga wrote:
> 
> Atul_Vaidya wrote:
>> Hi,
>> How to store a multimap in SQlite database ?
>> My requirement is that i want to store a multimap in a column
>> of
>> the SQlite table
>> Regards,
>> Atul
> 
> but you have to use some additional
> encoding (base64, bintohex, uuencode/uudecode, whatever you want) to make
> sure
> that stored text is pure ASCII and content won't be damaged
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/STL-and-SQLite-tp25340733p25341643.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Design for concurrency

[Marian Aldenhoevel]

Hi,

I am currently designing a system where I am planning to use SQLite3 and 
would like some advice on basic architecture. The system is for a 
point-of-sale-like semi-embedded application.

The program will be running on quite limited hardware with Compact Flash 
for storage. The database consists of currently about 350k records, 
on-disk that amounts to a 48MB SQLite database file. That number is 
likely to grow over the lifetime of the application, maybe by a factor 
of two or so.

The main operation will be single-record queries. The records being 
identified via a primary key or one of two other indexed columns. These 
reads are initiated via a user-interaction and it is not predictable 
when they occur. There may be a few per day, or one every minute.

These reads need to be quick, that is the overriding design criterion. 
Say they may take two or three seconds at most, an arbitrary upper bound 
for the sake of discussion. At first glance that poses no problem at 
all, those reads are very fast.

But there will be updates to the database as well. These are cyclic at 
preplanned times, several times a day and may want to update anything 
from a few hundred to a few thousand records. The data is fetched from a 
website as CSV and parsed and transformed into INSERT OR UPDATE statements.

Now the problem becomes one of concurrency: How can I ensure an upper 
bound on the time it takes to do the single-record reads in this scenario?

Technically my programm will be a single multithreaded executable 
written in C++. I can assume that this program will be the only program 
using the database, so synchronisation mechanism outside of SQLite are 
acceptable, but it would be nice to do without.

A) The naive approach.

For the update start a transaction, do all the INSERT OR UPDATES in one 
batch and then commit it.

I have implemented that in a predecessor-version that would not allow 
any other approach and is not concurrent. Users are locked out during 
the update which is unacceptable for the new design.

I have timed the COMMIT to take anything between 30 seconds and 3 
minutes, depending on the number of updated records.

As I understand SQLite locking no reads can be serviced in the time it 
actually takes to COMMIT the transaction. Right?

So that won't work.

B) The a little less naive approach.

Instead of batching all the updates into one transaction only batch 
some. Tune the size of the batches so that their individual COMMIT does 
not take too long so that the time-constraint for the reads can be met. 
ACIDity is not an issue here, partial updates (some records updated, 
others not) may be applied without any ill effect, the remaining records 
would then be updated as part of the next cycle.

I would have to make sure that reads get a chance even if the writing 
process starts fresh transaction in a tight loop. I think that can be 
done with the SQLite concurrency system if I understand it correctly.

The total time for the update would be greatly increased of course due 
to it being split into many transactions. By how long would remain to be 
tested, I have no idea. But that is not a big problem in itself as long 
as the reads still are being serviced.

C) A silly idea.

I could also split the database file into two copies.

One "active" copy that is used to service the reads. And one "in 
transit" copy that is being updated.

So at the start of an update cycle I would make a copy of the active 
database file. Either on the filesystem (would that be safe? A hot copy 
of a SQLite database file that is only read from?) or using SQLite itself.

Then I would update the copy in one transaction. Commit it. And after 
the commit somehow flag the copy as active so that the next read will be 
from that copy.

This approach would decouple reads and writes at the price of added 
complexity for the switch, making sure it's all well-synchronized etc.. 
Homemade concurrency.

Making a copy of the database on the CF card currently takes around two 
minutes. So that would have to be added on top of the update time and 
the single-batch commit time. But it's a constant (well, for a given 
database size only of course) and does not interfere with the reads. So 
no problem here.



Those are the strategies I was able to think of so far. Comments are 
welcome. Better ideas as well. Please point out my dumb errors in any case.

Ciao, MM

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

[sqlite] First "SELECT" query fails

[purushotham.kotapalle]

Hi,
I am using SqLite DB as a message Queue for data share between 2 process.
I create a table in one process(say A) and then subcribe another process(say B) 
to extract data from the table everytime entry is added.
Creating the table and subcribing is successful.
However, there is a problem when trying to extract the an entry for the very 
first time form the table. Complete problem is as described below:

 - process A creates the table T, process B subcribes to entry events(like add) 
of table T.
 - process A adds an entry to table T, using "INSERT INTO..." command
 - process B catches this event and tries to extract the data from table T, 
using "SELECT *..." query. But the command fails with following error :
"no such table: responseQ Unable to execute statement"
 - however, subsequent entries added by process A are handled successfully by 
process B.

Since, I am simulating a "message queue" model, with this issue, one of the 
messages will remain in the queue(table) unused.
I tried to execute commit db and finish the sql query after creating table, but 
was not helpful to solve this issue.

Pls help me to find a solution to this problem.

Regards,
Reddy


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

Re: [sqlite] STL and SQLite

[Michal Seliga]

Atul_Vaidya wrote:
> Hi,
> How to store a multimap in SQlite database ?
> My requirement is that i want to store a multimap in a column of
> the SQlite table
> Regards,
> Atul

i don't use stl but i also work with more complex structures.
the best approach to save them in database is  encode it to text or binary
field. in binary field its simpler to do and it saves space. working with text
is sometimes simpler in database operations, but you have to use some additional
encoding (base64, bintohex, uuencode/uudecode, whatever you want) to make sure
that stored text is pure ASCII and content won't be damaged
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_exec fails on arm

[priyanka sharma]

Hi
the sqlite3_exec is called from the following code snippet.

get_entries_fromdatabase ()
{

  sqlite3 *db;
  char *zErrMsg = 0;
  int rc;

 char database_buffer[BUFSIZ];

  rc = sqlite3_open (database_buffer, );
  if (rc)
{
  fprintf (stderr, "Can't open database: %s\n", sqlite3_errmsg (db));
  sqlite3_close (db);
  return -1;
}
  rc = sqlite3_exec (db, command, (void  * )callback, 0, );
  if (rc != SQLITE_OK)
{
  fprintf (stderr, "SQL error: %s\n", zErrMsg);
  sqlite3_free (zErrMsg);
  return -1;
}
  sqlite3_close (db);
}

where command is
"select name_1, phones, unit_no, place_name_1, block_1, streetname_1,
x_addr, y_addr, logo_1, bitmap_1, bluetooth_file  from  DirectoryService
where (x_addr >=23800.00) AND (x_addr <=27800.00) AND (y_addr
>=28000.00) AND (y_addr <=32000.00) AND main_category='Hotel' AND
sub_category='4Star' order by name_1  "

The same code works fine on x86 ,and the given select command gives correct
result on  as on  ARM command line prompt.

The callback function is

static int
callback (void *NotUsed, void *NOTUsed, char *ch_strings_p2[COLUMN],
  char *ch_ColName_p2[COLUMN])
{
 unsigned int U32_i;
  unsigned long int sz;
  char *ptr;
  // gU32_count=0;

  for (U32_i = 0; U32_i < ENTRIES; U32_i++)
{
#ifdef DEBUG
  printf ("%s= %s\n", ch_ColName_p2[U32_i],
  ch_strings_p2[U32_i] ? ch_strings_p2[U32_i] : "NULL");
#endif
  if (ch_strings_p2[U32_i] == NULL)
ch_strings_p2[U32_i] = "NA";

  ptr = (char *) malloc (strlen (ch_strings_p2[U32_i]) + 1);
  strcpy (ptr, ch_strings_p2[U32_i]);
  gch_name_p2[gU32_index] = malloc (strlen (ptr) + 1);
  strcpy (gch_name_p2[gU32_index], ptr);
  gU32_index++;
  free (ptr);
}
  gU32_count++;
  return 0;

 }


Tried replacing the callback function with a simple function contaings just
some printfs . Yet the same error





On Tue, Sep 8, 2009 at 11:47 AM, Atul_Vaidya <
atul.vai...@prototechsolutions.com> wrote:

>
>
> Any help on this issue will be welcome,
>
> hi,
>   Can you please specify what statement you are passing in to the
> sqlite3_exec please ?
> Atul
>
> --
> View this message in context:
> http://www.nabble.com/sqlite3_exec-fails-on-arm-tp25293839p25340486.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] STL and SQLite

[Atul_Vaidya]

Hi,
How to store a multimap in SQlite database ?
My requirement is that i want to store a multimap in a column of
the SQlite table
Regards,
Atul
-- 
View this message in context: 
http://www.nabble.com/STL-and-SQLite-tp25340733p25340733.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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

[Dennis Volodomanov]

> SELECT * FROM TABLEB WHERE ID IN (
>   SELECT IDB FROM TABLEA WHERE IDC = 1 AND IDD IN (1, 3)
> )

Yes, I think you are right - I'll do some extensive testing of course, but it 
looks good logically.

Thank you!

   Dennis

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

Re: [sqlite] sqlite3_exec fails on arm

[Atul_Vaidya]

Any help on this issue will be welcome,

hi,
   Can you please specify what statement you are passing in to the
sqlite3_exec please ?
Atul

-- 
View this message in context: 
http://www.nabble.com/sqlite3_exec-fails-on-arm-tp25293839p25340486.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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

[P Kishor]

On Tue, Sep 8, 2009 at 12:58 AM, Dennis Volodomanov wrote:
> 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.


SELECT * FROM TABLEB WHERE ID IN (
  SELECT IDB FROM TABLEA WHERE IDC = 1 AND IDD IN (1, 3)
)


>
> 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
>



-- 
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
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users