Re: [sqlite] SQLite won't import 53, 066, 244 row 11 gigabyte CSV file

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Walter Dnes wrote:
>   Am I making some glaring noob error here?

The most likely cause is that the SQLite shell was compiled without support
for large files.  (The SQLite library itself has large files on by default
but the shell is separately compiled code.)

The shell uses the fopen standard library function to open the CSV file.
You can find out what the error is by looking for a "cannot open file" error
print in shell.c and adding 'perror(NULL);' as the previous line.

You can recompile the shell setting the flags (-D or #define): _LARGE_FILE,
_FILE_OFFSET_BITS=64 and _LARGEFILE_SOURCE

Alternatively use split to make the input a series of files each less than
4GB in size.  Or use a 64 bit host.

I've added a ticket to the tracker about the error reporting:

  http://www.sqlite.org/src/info/3671265894

If using a file smaller than 4GB fixes the issue for you then please let me
know and I'll add a ticket about large file support too.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksQi2IACgkQmOOfHg372QTlvQCgvpLhykiAcbZFWxnY5+TSYG4A
FuQAoImQ2UPQhoCz5yrn6BLxOPeHkuKc
=8JXy
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite won't import 53,066,244 row 11 gigabyte CSV file

[Walter Dnes]

  I'm running SQLite 3.6.18 on Gentoo linux.  The filesystem is
Reiserfs, so 11 gigs should not be a problem.  Here's a screen scrape...

===
[d531][waltdnes][~/SQLite] ll dly04.csv
-rw-r--r-- 1 waltdnes users 11143911240 Nov 27 15:34 dly04.csv

[d531][waltdnes][~/SQLite] sqlite3 dlyxx.sqlite
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode csv dly04
sqlite> .import dly04.csv dly04
cannot open file: dly04.csv
sqlite>
===

  Am I making some glaring noob error here?  I've successfully imported,
and tested out smaller files using the same method.  The table dly04 has
already been created, and it's just a matter of importing from the CSV
file.  Is there a known maximum line-count or file-size?  If necessary,
I can run "split -l  dly04.csv", and import the separate
(smaller) pieces.

  Am I correct in assuming that multiple ".import" statements will
append to an existing table, rather than overwriting? (Yes, I am new to
SQLite).

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

Re: [sqlite] Location of Sqlite Files

[Simon Slavin]

On 27 Nov 2009, at 7:54pm, mr_orange wrote:

> Hey, I am kind of new to SQLite. I was wondering if anyone could help me with
> a couple things:
> 1) Where are the SQLite databases stored? I was told in the C:\WINDOWS\temp
> folder, but I can't seem to find any SQLite-related files there.
> 2) How do you delete a SQLite database?

Mr. Orange is obviously used to SQL engines which manage many files in a 
designated folder, for example MySQL.

SQLite handles databases very simply.  The first SQLite call you issue will 
probably be one that opens a database file or creates a new one if the file 
doesn't already exist.  In this call you supply the full path and filename.  So 
you could tell SQLite to open the file "C:\MyFiles\databases\animals.db" and 
that's one file will hold all the data.  Once your application has closed the 
connection that file is just a file: move it from folder to folder, copy it to 
a Flash drive, or delete it as you wish.

One note: while your application has the database open you may see a journal 
file, in the same folder as the database file with almost the same name.  Once 
your application has closed the database file, the journal file will disappear. 
 If the application crashes, when you next open the databases file SQLite will 
notice the journal file and use what it finds to update the database file 
without any corruption.

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

Re: [sqlite] Location of Sqlite Files

[Artur Reilin]

1) I store my sqlite files at directories where i use it. Where do you
know to store sqlite files in the temp directory? I use sqlitefront - like
phpmyadmin - and there are also my files stored for better editing.

2. Ehm .. right click > delete? Or you mean to delete a whole database?
use php function unlink for this.

with best wishes

Artur

>
> Hey, I am kind of new to SQLite. I was wondering if anyone could help me
> with
> a couple things:
> 1) Where are the SQLite databases stored? I was told in the
> C:\WINDOWS\temp
> folder, but I can't seem to find any SQLite-related files there.
> 2) How do you delete a SQLite database?
>
> Any help is much appreciated, thank you.
> --
> View this message in context:
> http://old.nabble.com/Location-of-Sqlite-Files-tp26545375p26545375.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
>
>


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

Re: [sqlite] Location of Sqlite Files

[Adam DeVita]

1) save the db wherever is appropriate for your application. (network drives
are generally considered to be a bad idea)
2) An sqllite db is a file. There are lots of ways to delete files

Adam


On Fri, Nov 27, 2009 at 2:54 PM, mr_orange  wrote:

>
> Hey, I am kind of new to SQLite. I was wondering if anyone could help me
> with
> a couple things:
> 1) Where are the SQLite databases stored? I was told in the C:\WINDOWS\temp
> folder, but I can't seem to find any SQLite-related files there.
> 2) How do you delete a SQLite database?
>
> Any help is much appreciated, thank you.
> --
> View this message in context:
> http://old.nabble.com/Location-of-Sqlite-Files-tp26545375p26545375.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
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Location of Sqlite Files

[mr_orange]

Hey, I am kind of new to SQLite. I was wondering if anyone could help me with
a couple things:
1) Where are the SQLite databases stored? I was told in the C:\WINDOWS\temp
folder, but I can't seem to find any SQLite-related files there.
2) How do you delete a SQLite database?

Any help is much appreciated, thank you.
-- 
View this message in context: 
http://old.nabble.com/Location-of-Sqlite-Files-tp26545375p26545375.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] sqlite-users Digest, Vol 23, Issue 26

[Pavel Ivanov]

> reasons.  Can primary key values available because of deletes get re-
> used at some point?

I guess you're talking about rowids not about general primary keys.
For rowids reuse is possible only if rows with maximum values of
rowids are deleted and only if your INTEGER PRIMARY KEY column doesn't
declared as AUTOINCREMENT.

> Also, in a multi-user environment, is it possible
> that another user could insert a record into the same table after my
> insert but before my SELECT max statement, or would locking prevent
> that possibility?

If you don't do select in the same transaction as insert then it is
possible to catch other user's id. Locking works only inside
transaction, it cannot work between transaction boundaries.

> The description of last_insert_rowid says it "returns the rowid of the
> most recent successful INSERT into the database from the database
> connection in the first argument". and the only parameter is the
> database connection.  In a multi-user environment, it sounds like this
> could be the rowid from a different table or, once again, does locking
> take care of that possibility?

"Multi-user environment" that shares the same connection between
multiple users? Doesn't that sound weird? If you've written your
application in that way and try to be sure that last rowid comes from
the correct table then it's the matter of your application
correctness.


Pavel

On Thu, Nov 26, 2009 at 1:09 PM, Peter Haworth  wrote:
>
>
> On Nov 26, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote:
>
>> Message: 28
>> Date: Thu, 26 Nov 2009 06:38:52 +
>> From: Simon Slavin 
>> Subject: Re: [sqlite] Getting the rowid after an insert
>> To: General Discussion of SQLite Database 
>> Message-ID: <643124a9-e4df-4287-a1bd-357126996...@bigfraud.org>
>> Content-Type: text/plain; charset=us-ascii
>>
>>
>> On 26 Nov 2009, at 5:54am, Edward Diener wrote:
>>
>>> I have a table with an integer primary key as the first type. My
>>> understanding is that this is an alias for the rowid. When I insert a
>>> row in this table using _sqlite3_prepare and then sqlite3_step I
>>> need to
>>> retrieve the rowid for the row I have just inserted. Is there an SQL
>>> statement I can use, or a way, to do that ?
>>
>> http://www.sqlite.org/c3ref/last_insert_rowid.html
>>
>> Simon.
>
> I have this question too.  I'm not programming in C so don;t have
> access to the sqlite_last_insert_rowid so have been using a SELECT max
> statement to get the maximum value of the primary key in the table I
> just inserted a row into.
>
> Never been entirely comfortable with that method for a couple of
> reasons.  Can primary key values available because of deletes get re-
> used at some point?  Also, in a multi-user environment, is it possible
> that another user could insert a record into the same table after my
> insert but before my SELECT max statement, or would locking prevent
> that possibility?
>
> The description of last_insert_rowid says it "returns the rowid of the
> most recent successful INSERT into the database from the database
> connection in the first argument". and the only parameter is the
> database connection.  In a multi-user environment, it sounds like this
> could be the rowid from a different table or, once again, does locking
> take care of that possibility?
>
> Pete
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler invariancy

[Dan Kennedy]

On Nov 27, 2009, at 6:52 AM, Christo Christoff wrote:

>
> Hi
> I report a prblem when compiling the recent fossil-based sqlite  
> sources with BorlandC.
> '
> sqlite3.c:
> Error E2140 sqlite3.c 99710: Declaration is not allowed here in  
> function fts3TermSelect
> *** 1 errors in Compile ***
> '
>
> I Use the amalgamation of the 3.6.21
> The Borland C compiler complains about the inline variable declaration
> Fts3SegReader **pArray ...
> in the finction `static int fts3TermSelect`
> In order to achieve a compiler-invariancy I Suggest moving the  
> problematic declaration
> upwards.

Thanks. Fixed here:

  http://www.sqlite.org/src/vinfo/97d332416069d2fbce323740b276d0e75235

> Best regards: Chris
>   
> _
> Windows Live: Friends get your Flickr, Yelp, and Digg updates when  
> they e-mail you.
> http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_3:092010
> ___
> 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] Compiler invariancy

[Christo Christoff]

Hi
I report a prblem when compiling the recent fossil-based sqlite sources with 
BorlandC.
'
sqlite3.c:
Error E2140 sqlite3.c 99710: Declaration is not allowed here in function 
fts3TermSelect
*** 1 errors in Compile ***
'

I Use the amalgamation of the 3.6.21
The Borland C compiler complains about the inline variable declaration
Fts3SegReader **pArray ...
 in the finction `static int fts3TermSelect`
In order to achieve a compiler-invariancy I Suggest moving the problematic 
declaration 
upwards.
Best regards: Chris
  
_
Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail 
you.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_3:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] open transaction

[Simon Davies]

2009/11/27  :
> How could I know if a transaction is already open? Does a specific
> command exist?
> Cheers

http://www.sqlite.org/c3ref/get_autocommit.html

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

[sqlite] open transaction

[galeazzi]

How could I know if a transaction is already open? Does a specific  
command exist?
Cheers
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users