[sqlite] sqlite3_open16_v2 / ticket 2869
Is there any reason not to incorporate the diff posted for ticket 2869 into the main branch and have sqlite3_open16_v2()? - Sherief ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing data into SQLite
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 ? Thank you! Kavita - Original Message - From: "Timothy A. Sawyer" To: "General Discussion of SQLite Database" Sent: Monday, August 31, 2009 9:11:32 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Importing data into SQLite You can use the sqlite binary to import data from a CSV file - if you do it that way you have to make sure that your data fields in the SQLite database match exactly in order the data in the CSV file. That's been my experience. The other way is to do it programmatically (Java, C++, etc). The advantage of that is you have control over the data import. Though the sqlite binary does an excellent job of importing data, it might be better to use the latter option if you want more control over the data. It's not a difficult job to do. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Monday, August 31, 2009 10:02 AM To: sqlite-users@sqlite.org Subject: [sqlite] Importing data into SQLite Hi, I'm evaluating SQLite to be used as a small embedded database in a linux environment for Skyfiber Inc. What is the best way to import data into it ? I have a bunch of entities and attributes in an excel spreadsheet. Could I import CSV ? What should be the columns (where can I read about this etc) Regards, Kavita ___ 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] Sqlite uses a lot of memory during a delete operation
On 6 Sep 2009, at 11:23pm, 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
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:
sqlite> DELETE FROM old_conns WHERE end < 1251679819;
I have an observation though I don't think it's related. You're using
the word 'end' as a column name. In the table on
http://www.sqlite.org/lang_keywords.html
it's listed as a keyword. You might want to avoid this in case it
causes problems in the future, when you try to use the word in a
context where it's ambiguous. Perhaps use conn_start and conn_end ?
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite uses a lot of memory during a delete operation
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
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
