Re: [sqlite] how can I import CSV file into SQLite quickly

[ronggui wong]

Thanks to all give response to help.
This is my solution using the luanguage I familiar.(http://www.r-project.org).

I use the code to read a 11819x807 csv file and it takes 10 minus.I think is
not too slow .(My PC:1.7G,512M RAM)

#code begins
rm(list=ls())
f<-file("D:\\wvsevs_sb_v4.csv","r")#134M
i <- 0
done <- FALSE
library(RSQLite)
con<-dbConnect("SQLite","c:\\sqlite\\database.db3")
tim1<-Sys.time()

while(!done){
i<-i+1
tt<-readLines(f,2500)
if (length(tt)<2500) done <- TRUE
tt<-textConnection(tt)
if (i==1) {
 assign("dat",read.table(tt,head=T,sep=",",quote=""));
  # to make the variable names elegent
  nam<-names(dat);
  nam<-gsub("^X.","",nam);
  nam<-tolower(gsub(".$","",nam))
 names(dat)<-nam
 #
}
else assign("dat",read.table(tt,head=F,sep=",",quote=""))
close(tt)
ifelse(dbExistsTable(con, "wvs"),dbWriteTable(con,"wvs",dat,append=T),
 dbWriteTable(con,"wvs",dat) )
}
close(f)
#cal the time require
Sys.time()-tim1

#code end.

Re: [sqlite] Closing sqlite and deleting the .db file

[Eduardo]

At 22:01 04/01/2006, you wrote:

I'm trying to close a database and then delete the .db file.

I call sqlite3_finalize(...) and then sqlite3_close(...) but when I 
try to delete the .db file I get an exception.


This is in Win32.

Thanks,

Steve


Just wait a few minutes. Windows is updating the index of the file. 
It's done while system is idle, so if you run another app or some 
tasks are in background it may take longer.



HTH 

Re: [sqlite] Feature Request: Open from fd

[Steve Lhomme]

[EMAIL PROTECTED] wrote:

Peter Bierman <[EMAIL PROTECTED]> wrote:

Related to a project I'm working on, it would be useful for me to be 
able to open a database file via passing an already open file 
descriptor to the sqlite open() call. sqlite3_openfd().





That is not possible.  SQLite needs to know the name of
the file so that it can create an appropriate rollback
journal in order to do atomic commits. 


But it's totally possible to do that in memory too. I would just like to 
avoid using a RamDisk. As I will probably need that feature I might 
commit it and send a patch here.


Steve

Re: [sqlite] Feature Request: Open from fd

[drh]

Peter Bierman <[EMAIL PROTECTED]> wrote:
> Related to a project I'm working on, it would be useful for me to be 
> able to open a database file via passing an already open file 
> descriptor to the sqlite open() call. sqlite3_openfd().
> 

That is not possible.  SQLite needs to know the name of
the file so that it can create an appropriate rollback
journal in order to do atomic commits. 
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Feature Request: Open from fd

[Peter Bierman]

At 3:53 PM -0600 1/4/06, Aaron Laffin wrote:

On 1/4/06, Peter Bierman <[EMAIL PROTECTED]> wrote:

 Related to a project I'm working on, it would be useful for me to be
 able to open a database file via passing an already open file
 descriptor to the sqlite open() call. sqlite3_openfd().


It seems to me that this would cause problems with the creation of the
journal file.  SQLite places it in the same directory as the db file
and it is transient.  SQLite wouldn't know where to put it if all it
had were a file descriptor for the main db file.  It also wouldn't
know where to find it for a potential rollback on open.  The design
scheme of the journal would have to change to support this.



Hmm, good point. Perhaps I could also pass the fd of the journal (if 
I find one)?


I really want to separate the privileged operations I need to perform 
from the bulk of my code. (This is for a setuid program.) The 
privileged operations I need to perform are fairly minimal.


-pmb

Re: [sqlite] Feature Request: Open from fd

[Steve Lhomme]

Aaron Laffin wrote:

On 1/4/06, Peter Bierman <[EMAIL PROTECTED]> wrote:


Related to a project I'm working on, it would be useful for me to be
able to open a database file via passing an already open file
descriptor to the sqlite open() call. sqlite3_openfd().



It seems to me that this would cause problems with the creation of the
journal file.  SQLite places it in the same directory as the db file
and it is transient.  SQLite wouldn't know where to put it if all it
had were a file descriptor for the main db file.  It also wouldn't
know where to find it for a potential rollback on open.  The design
scheme of the journal would have to change to support this.


Another good option to add would be to have the DB in memory (including 
the journal) that can be saved back to a file when the DB is closed. 
That would definitely boost the performance. And could at least be used 
for read-only access to a DB.


Steve

Re: [sqlite] Feature Request: Open from fd

[Aaron Laffin]

On 1/4/06, Peter Bierman <[EMAIL PROTECTED]> wrote:
> Related to a project I'm working on, it would be useful for me to be
> able to open a database file via passing an already open file
> descriptor to the sqlite open() call. sqlite3_openfd().

It seems to me that this would cause problems with the creation of the
journal file.  SQLite places it in the same directory as the db file
and it is transient.  SQLite wouldn't know where to put it if all it
had were a file descriptor for the main db file.  It also wouldn't
know where to find it for a potential rollback on open.  The design
scheme of the journal would have to change to support this.

--aaron

Re: [sqlite] specifying field type, any benefit?

[Mark Wyszomierski]

Got it, thank you very much all,

Mark


On 1/4/06, Henry Miller <[EMAIL PROTECTED]> wrote:
>
> On Wednesday 04 January 2006 02:54 pm, Mark Wyszomierski wrote:
> > Hi all,
> >
> > I switched to sqlite from mysql awhile ago, I maintained the field types
> > in my sqlite implementation such as:
> >
> > create table students (first_name TEXT, age INTEGER);
> >
> > I'm just wondering if there is any point to specifying the field type as
> if
> > I try adding a string type into the age field, it seems to be accepted
> ok:
> >
> > insert into students values('hello');
> >
> > Does sqlite have any problem regarding setting a field defined as
> INTEGER
> > from a text string (any limits etc?), are there any performance gains to
> be
> > had with specifying the field type?
>
> sqlite does not care about types.   You can insert BLOBs into INTEGER
> fields
> if it makes you happy.  sqlite will not care.
>
> I recommend you place them in your definitions anyway, for two reasons.
> First, you might want to switch to a different database latter.  Second,
> it
> is sometimes helpful to tell your successors what you intend a field to
> be.
>
> Even though sqlite doesn't care, it is a good idea to be more strict
> yourself.
>
> As the other response said, sqlite used NULL for the unspecified
> parameters.
>

[sqlite] Feature Request: Open from fd

[Peter Bierman]

Related to a project I'm working on, it would be useful for me to be 
able to open a database file via passing an already open file 
descriptor to the sqlite open() call. sqlite3_openfd().


I need this because I'd prefer to resolve some filesystem permissions 
access issues in a separate process from my database code.


Is this the appropriate place to make this request, or is there a 
better way to ask? ;-)


-pmb

Re: [sqlite] specifying field type, any benefit?

[Henry Miller]

On Wednesday 04 January 2006 02:54 pm, Mark Wyszomierski wrote:
> Hi all,
>
> I switched to sqlite from mysql awhile ago, I maintained the field types
> in my sqlite implementation such as:
>
> create table students (first_name TEXT, age INTEGER);
>
> I'm just wondering if there is any point to specifying the field type as if
> I try adding a string type into the age field, it seems to be accepted ok:
>
> insert into students values('hello');
>
> Does sqlite have any problem regarding setting a field defined as INTEGER
> from a text string (any limits etc?), are there any performance gains to be
> had with specifying the field type?

sqlite does not care about types.   You can insert BLOBs into INTEGER fields 
if it makes you happy.  sqlite will not care.

I recommend you place them in your definitions anyway, for two reasons.  
First, you might want to switch to a different database latter.  Second, it 
is sometimes helpful to tell your successors what you intend a field to be.

Even though sqlite doesn't care, it is a good idea to be more strict yourself.

As the other response said, sqlite used NULL for the unspecified parameters.

Re: [sqlite] specifying field type, any benefit?

[drh]

Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> I switched to sqlite from mysql awhile ago, I maintained the field types
> in my sqlite implementation such as:
> 
> create table students (first_name TEXT, age INTEGER);
> 
> I'm just wondering if there is any point to specifying the field type as if
> I try adding a string type into the age field, it seems to be accepted ok:
> 
> insert into students values('hello');
> 
> Does sqlite have any problem regarding setting a field defined as INTEGER
> from a text string (any limits etc?), are there any performance gains to be
> had with specifying the field type?
> 

If you have a table like this:

   CREATE TABLE ex1(part_number INTEGER);

And then you insert text that looks like a number but has
leading zeros:

   INSERT INTO ex1 VALUES('000123');

The string gets converted into an integer.  You can read it
back out as a string if that is what you want, but the leading
zeros will get lost.  If you want to preserve the leading zeros,
you have to make sure the type of the column is TEXT:

   CREATE TABLE ex1(part_number TEXT);

Other than some really strange corner cases like the above,
the type of the column does not matter in SQLite.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] specifying field type, any benefit?

[Jim C. Nasby]

On Wed, Jan 04, 2006 at 03:54:49PM -0500, Mark Wyszomierski wrote:
> Hi all,
> 
> I switched to sqlite from mysql awhile ago, I maintained the field types
> in my sqlite implementation such as:
> 
> create table students (first_name TEXT, age INTEGER);
> 
> I'm just wondering if there is any point to specifying the field type as if
> I try adding a string type into the age field, it seems to be accepted ok:
> 
> insert into students values('hello');
> 
> Does sqlite have any problem regarding setting a field defined as INTEGER
> from a text string (any limits etc?), are there any performance gains to be
> had with specifying the field type?

Well, what's actually happening is that since you didn't supply a value
for age, it's being set to NULL. While that's fine for testing and
what-not, in your code you should really provide a list of fields that
you're inserting into, so there's no ambiguity. IE:

INSERT INTO students(first_name) VALUES('John');

As for inserting text into an int, presumably it will cast it if it can.
So 

INSERT INTO students(age) VALUES('John');

would fail, but

INSERT INTO students(age) VALUES('18');

should work. Of course, you're ultimately just making the database do
more work; you should really just insert the int as an int and be done
with it...

INSERT INTO students(age) VALUES(18);
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

Re: [sqlite] specifying field type, any benefit?

[Kurt Welgehausen]

http://www.sqlite.org/datatype3.html

[sqlite] Closing sqlite and deleting the .db file

[Steven D. Leeke]

I'm trying to close a database and then delete the .db file.

I call sqlite3_finalize(...) and then sqlite3_close(...) but when I try to 
delete the .db file I get an exception.


This is in Win32.

Thanks,

Steve

[sqlite] specifying field type, any benefit?

[Mark Wyszomierski]

Hi all,

I switched to sqlite from mysql awhile ago, I maintained the field types
in my sqlite implementation such as:

create table students (first_name TEXT, age INTEGER);

I'm just wondering if there is any point to specifying the field type as if
I try adding a string type into the age field, it seems to be accepted ok:

insert into students values('hello');

Does sqlite have any problem regarding setting a field defined as INTEGER
from a text string (any limits etc?), are there any performance gains to be
had with specifying the field type?

Thanks,
Mark

[sqlite] [PATCH] WinCE compilation

[Steve Lhomme]

Hi everyone,

I'm a happy user of SQlite for a project I just started to make a 
multimedia database (a bit like the DB in iTunes). The idea is to make 
it as cross-platform as possible and free.


I tried to make it work under Windows CE but run into a few problems 
when compiling with Embedded Visual C++ 4. Although WinCE has a very 
similar API to Windows, it lacks some of the features.


- only the unicode API is present, so I disabled all the xxxA() API calls

- localtime() is defined but doesn't exist, so I coded one with the 
existing API


- LockFile(Ex) and UnlockFile(Ex) are not supported, only the Ex API is 
available on WinCE 5 but I need bigger support so I just made the code 
blank on WinCE. Is there any drawback to that ?


- FILE_ATTRIBUTE_TEMPORARY and FILE_FLAG_DELETE_ON_CLOSE are not 
supported for CreateFile. So I removed the flags for WinCE. But that 
means I have to delete the auto-deleting files on close. I modified the 
API to do that when calling sqlite3OsClose(). I realise changing the API 
is not so good, so calling sqlite3OsDelete() where the close is called 
could be a better option.


All of the changes are included in the following patch.

Steve

--
robUx4 on blog 
Index: os.h
===
--- os.h(revision 1)
+++ os.h(working copy)
@@ -182,7 +182,7 @@
 int sqlite3OsSyncDirectory(const char*);
 int sqlite3OsTempFileName(char*);
 int sqlite3OsIsDirWritable(char*);
-int sqlite3OsClose(OsFile*);
+int sqlite3OsClose(OsFile*, const char*);
 int sqlite3OsRead(OsFile*, void*, int amt);
 int sqlite3OsWrite(OsFile*, const void*, int amt);
 int sqlite3OsSeek(OsFile*, i64 offset);
Index: os_unix.c
===
--- os_unix.c   (revision 1)
+++ os_unix.c   (working copy)
@@ -1278,7 +1278,7 @@
 /*
 ** Close a file.
 */
-int sqlite3OsClose(OsFile *id){
+int sqlite3OsClose(OsFile *id, const char*){
   if( !id->isOpen ) return SQLITE_OK;
   if( CHECK_THREADID(id) ) return SQLITE_MISUSE;
   sqlite3OsUnlock(id, NO_LOCK);
Index: os_win.c
===
--- os_win.c(revision 1)
+++ os_win.c(working copy)
@@ -33,6 +33,64 @@
 ** Include code that is common to all os_*.c files
 */
 #include "os_common.h"
+
+#if defined(_WIN32_WCE)
+#include 
+struct tm * __cdecl localtime(const time_t *t)
+{
+  static struct tm y;
+  FILETIME uTm, lTm;
+  SYSTEMTIME pTm;
+  uTm.dwLowDateTime = *t & 0x;
+  uTm.dwHighDateTime= *t >> 32;
+  FileTimeToLocalFileTime(,);
+  FileTimeToSystemTime(,);
+  y.tm_year = pTm.wYear - 1900;
+  y.tm_mon = pTm.wMonth - 1;
+  y.tm_wday = pTm.wDayOfWeek;
+  y.tm_mday = pTm.wDay;
+  y.tm_hour = pTm.wHour;
+  y.tm_min = pTm.wMinute;
+  y.tm_sec = pTm.wSecond;
+  return 
+}
+
+#ifndef LOCKFILE_EXCLUSIVE_LOCK
+#define LockFileEx(a,b,c,d,e,f) (1)
+#define UnlockFileEx(a,b,c,d,e) (1)
+#endif
+
+BOOL LockFile(
+  HANDLE hFile,
+  DWORD dwFileOffsetLow,
+  DWORD dwFileOffsetHigh,
+  DWORD nNumberOfBytesToLockLow,
+  DWORD nNumberOfBytesToLockHigh
+)
+{
+  OVERLAPPED ovlp;
+  ovlp.Offset = dwFileOffsetLow;
+  ovlp.OffsetHigh = dwFileOffsetHigh;
+  ovlp.hEvent = 0;
+  return LockFileEx(hFile, LOCKFILE_EXCLUSIVE_LOCK|LOCKFILE_FAIL_IMMEDIATELY, 
0, nNumberOfBytesToLockLow, nNumberOfBytesToLockHigh, );
+}
+
+
+BOOL UnlockFile(
+  HANDLE hFile,
+  DWORD dwFileOffsetLow,
+  DWORD dwFileOffsetHigh,
+  DWORD nNumberOfBytesToUnlockLow,
+  DWORD nNumberOfBytesToUnlockHigh
+)
+{
+  OVERLAPPED ovlp;
+  ovlp.Offset = dwFileOffsetLow;
+  ovlp.OffsetHigh = dwFileOffsetHigh;
+  ovlp.hEvent = 0;
+  return UnlockFileEx(hFile, 0, nNumberOfBytesToUnlockLow, 
nNumberOfBytesToUnlockHigh, );
+}
+#endif
 
 /*
 ** Do not include any of the File I/O interface procedures if the
@@ -66,14 +124,18 @@
 ** WinNT/2K/XP so that we will know whether or not we can safely call
 ** the LockFileEx() API.
 */
-static int isNT(void){
+static int isNT(void){
+#if !defined(_WIN32_WCE)
   if( sqlite3_os_type==0 ){
 OSVERSIONINFO sInfo;
 sInfo.dwOSVersionInfoSize = sizeof(sInfo);
 GetVersionEx();
 sqlite3_os_type = sInfo.dwPlatformId==VER_PLATFORM_WIN32_NT ? 2 : 1;
   }
-  return sqlite3_os_type==2;
+  return sqlite3_os_type==2;
+#else
+  return 1;
+#endif
 }
 
 /*
@@ -131,9 +193,11 @@
   if( zWide ){
 DeleteFileW(zWide);
 sqliteFree(zWide);
+#if !defined(_WIN32_WCE)
   }else{
 DeleteFileA(zFilename);
-  }
+#endif
+  }
   TRACE2("DELETE \"%s\"\n", zFilename);
   return SQLITE_OK;
 }
@@ -147,8 +211,10 @@
   if( zWide ){
 exists = GetFileAttributesW(zWide) != 0x;
 sqliteFree(zWide);
+#if !defined(_WIN32_WCE)
   }else{
-exists = GetFileAttributesA(zFilename) != 0x;
+exists = GetFileAttributesA(zFilename) != 0x;
+#endif
   }
   return exists;
 }
@@ -201,6 +267,7 @@
   *pReadonly = 0;
 }
 sqliteFree(zWide);
+#if !defined(_WIN32_WCE)
   }else{

[sqlite] Sqlite 3.3 schedule?

[Jeff West]

I was evaluating Sqlite for use in an application but ran into the
problem mentioned in posting to this group that is listed here…

 

http://www.mail-archive.com/sqlite-users@sqlite.org/msg12137.html

 

“Beginning with version 3.3.0, you will be able to configure SQLite
so that multiple connections running in the same thread will be
able to select READ UNCOMMITED isolation relative to one another.
Note that this will only be possible for connections in the same
thread - it will not be possible for connections in different
threads of the same process.  With READ UNCOMMITTED isolation,
you can still only have a single connection writing to the database
at a time, but writers will not block readers nor will readers block
writers.  This allows a much higher level of concurrency.”
 
I was wondering if there is an alpha/beta build of 3.3 available or if
anyone knows when this version/fix might be available?
 
Thanks,
 
Jeff
 

 

 

[sqlite] RE: [RBL] [sqlite] JDBC driver

[Steve O'Hara]

Just a word of advice, make your binding to SQLite dynamic - it's more
code but ultimately you won't have to keep releasing new versions of
your driver everytime SQLite changes.

Steve

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
rg] On Behalf Of gnorman22
Sent: 31 December 2005 22:38
To: sqlite-users@sqlite.org
Subject: [RBL] [sqlite] JDBC driver

I've been working on a JDBC driver for the latest version of Sqlite
(3.2.8).  I know that someone has already written 1 for an older
version, and I really didn't intend to reinvent the wheel.  However, I
wanted to learn Sqlite and how to write a JDBC driver and this sort of
evolved.  If this is this something that anyone would be interested in
looking at, I'd be happy to share what I have.  

Let me know.