Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 8 May 2012, at 12:34am, "peter korinis"  wrote:

> I did NOT specify a file name when I started sqlite3 - so I guess all is
> lost. I'll have to redo it.
> 
> So then the first thing is to specify the db (file) name - what syntax? I
> tried typing a file name (test.db) but error.

It helps when you read the documentation.

The standard way to give a filename is to do it when you start the shell tool.  
Examples

~/Desktop> sqlite3 ./mydatabase.sql

C:\ > sqlite3 C:\PROJECT\FILES\DB.SQL

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[peter korinis]

I did NOT specify a file name when I started sqlite3 - so I guess all is
lost. I'll have to redo it.

So then the first thing is to specify the db (file) name - what syntax? I
tried typing a file name (test.db) but error.

 

pk

 

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Gerry Snyder]

The file name does not necessarily have anything to do with the table name.
On May 7, 2012 2:25 PM, "peter korinis"  wrote:

> Simon
>
> I searched the entire disk for the table name and no matches.
>
>
>
> pk
>
>
>
> ___
> 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] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 7 May 2012, at 10:25pm, peter korinis  wrote:

> I searched the entire disk for the table name and no matches.

SQLite keeps its tables in databases.  One database is one file on disk.  The 
files are not in any magic place: each time you create a database you have to 
specify a specific file (folder and filename) for SQLite to keep its data in.  
If you want to access data you saved earlier, you have to specify the same file 
(folder and filename) or SQLite will open a new file in this new place you've 
specified, find that there's no data there, and telling you you have no tables.

So when you put the data into the tables in the first place, make sure you've 
specified a path and filename.  If you find you're creating tables and haven't 
given SQLite a path and filename yet, you're doing the wrong thing !  And when 
you want to get your data back out again, specify the same folder and filename.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[peter korinis]

Simon

I searched the entire disk for the table name and no matches.

 

pk

 

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[David Bicking]

Did you remember to specify the file name when you started sqlite3. If you 
don't give the file name, everything is done to the memory database and is 
discarded when you exit.

David




 From: peter korinis 
To: sqlite-users@sqlite.org 
Sent: Monday, May 7, 2012 5:01 PM
Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file
 
I can't find a table I just created and imported data.

With .databases I can see 'main'  but with no location on disk and with .
tables I see the table.

Instructions says they are save . but where. I can't find them with windows
search?



pk



___
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] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 7 May 2012, at 10:01pm, peter korinis  wrote:

> I can't find a table I just created and imported data.
> 
> With .databases I can see 'main'  but with no location on disk and with .
> tables I see the table.
> 
> Instructions says they are save . but where. I can't find them with windows
> search?

You probably imported into one file and now you're opening another.  Try 
looking for the file on disk, making sure it is a reasonable size, then 
specifying the full path (folders and filename) when you open the file.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[peter korinis]

I can't find a table I just created and imported data.

With .databases I can see 'main'  but with no location on disk and with .
tables I see the table.

Instructions says they are save . but where. I can't find them with windows
search?

 

pk

 

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

Re: [sqlite] Bug: Increment unique integer field

[Stephan Beal]

Keith, this type of detailed response is why i rate this list so highly
above most others. These are the threads i learn the most from :-D.

+1

Sorry for top-posting - writing on a phone.

- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
On May 7, 2012 4:44 PM, "Keith Medcalf"  wrote:

>
> > In an SQL statement that inserts, deletes, or updates many rows,
> > SQLite as well as MySQL InnoDB checks UNIQUE and FOREIGN KEY
> > constraints row-by-row.
> > According to the SQL standard, the default behavior should be deferred
> > checking.
> >
> http://stackoverflow.com/questions/7703196/sqlite-increment-unique-integer-
> > field
>
> The behaviour is correct.  Consider the case where num were the referenced
> in a foreign key constraint and updates were cascading, any method of
> achieving the update other than updating through a cursor (including
> allowing deferral of integrity checking until commit time) which violates
> the unique constraint in an interim update would result in an inconsistent
> database (loss of integrity), for example, if a row with num = n+1 were
> updated following the update of a row with num = n, for any n.  There are
> ways to "re-phrase" the update to avoid this, but it requires domain
> knowledge and therefore the engine cannot do it for you.  Nor should it.
>
> Create table numbers (num int unique);
> Create table others (a int, num int unique references numbers (num) on
> update cascade);
> Insert into numbers values (1), (2), (3), (4);
> Insert into others values (1,1), (2,2), (3,3), (4,4);
>
> Then any interim update to num which violated the unique constraint would
> result in integrity loss and the update should fail irrespective of row
> order.  And you ought not depend on row order unless explicitly stated in
> the SQL statement.  Turning off constraint checking (or deferring it until
> commit time) places the consequence of "not knowing what you are doing"
> directly in the hands of the programmer (where it belongs).  If you know
> enough about the database to disable the integrity checking, you ought to
> be struck with the consequences (if any).
>
> The only way to do the update without such side-effects is to ensure that
> the update does not have any interim (row by row) violations of integrity.
>
> In other engines you would use:
>
> update numbers set num = num + 1 from numbers order by num desc;
>
> To control the row processing order by performing the update though
> "current of cursor", which would always obtains the correct result every
> time.
>
> Perhaps a useful enhancement would be to permit an update to use from and
> order by clauses thus allowing such an update to be expressed directly.
>  Effectively, such an update would become a "select" where instead of
> returning rows, the return a row would be replaced with the update
> operation...
>
> Some refer to this as an updateable view.  It really isn't.  It is still
> an update of "current of cursor", the update just takes place for each
> valid result row in a result set by allowing additional tables to be joined
> into the cursor, rather than restricting the cursor to only the single
> updated table.
>
> You can achieve this presently by creating a view with the correct
> ordering, and then an update trigger for the num column on the view which
> updates the underlying table, then performing the update against the view:
>
> Create table numbers (num int unique);
> Create table others (a int, num int unique references numbers (num) on
> update cascade);
> Insert into numbers values (1), (2), (3), (4);
> Insert into others values (1,1), (2,2), (3,3), (4,4);
> Create view updatenumbers
> as
>  select num from numbers order by num desc;
> Create trigger updnum instead of update of num on updatenumbers
> begin
>  update numbers set num = new.num where num=old.num;
> end;
>
> update updatenumbers set num = num + 1;
>
> sqlite> select * from numbers; select * from others;
> 2
> 3
> 4
> 5
> 1|2
> 2|3
> 3|4
> 4|5
>
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.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] is SQLite the right tool to analyze a 44GB file

[peter korinis]

Thanks for advice

 

http://www.pantz.org/software/sqlite/sqlite_commands_and_general_usage.html

 

had the explanation/example I needed to get the import done successfully. 

Using ".separator ," was what I was missing.

 

peter

 

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Black, Michael (IS)]

Next iteration of my csvimport utility.  I think it handles Simon's test cases 
adequately (or it gives an error).

Now allows for multiline fields
Correctly(?) handles  fields with escaped quotes.
Checks the entire file to ensure all records have the correct field count 
before processing.
Determines # of columns from the 1st record in the file.
Can now change delimiters for fields, records, and field enclosures.

Aborts on malformed records.

Usage: csvimport [-d] [-e] [-r] filename databasename tablename
-d   Change field delimiter, default -d,
-e   Change field enclosure char, default -e\"
-r   Change record delimiter, default -r

On my system using 1M records generated with this:
main() {
  int i;
  for(i=0; i<100; ++i) {
printf("%d,%d,%d,%d\n",i,i+1,i+2,i+3);
  }
}

Takes 4.1 seconds to import the 1M records.


// csvimport -- At least somewhat RFC4180 compliant
// quoted fields can span multiple lines
// quotes can be inserted by escaping with another quote
#include 
#include 
#include 
#include "sqlite3.h"

#define BUFSIZE 100
#define MOD 10
#define MAXTOKEN 10

int nline=0;
int ncol;
char buf[BUFSIZE];
int peek;
char delim=',';
char quote='"';
char eol='\n';

inline int nextChar(FILE *fp) {
  static char *ptr=buf;
  if (*ptr == 0) {
fgets(buf,BUFSIZE,fp);
if (feof(fp)) return EOF;
++nline;
ptr=buf;
  }
  peek = *(ptr+1);
  return *ptr++;
}

int countColumns(FILE *fp) {
  // 1st line(s) of the file determine how many columns we have
  int p;
  int ncol = 0;
  while((p=nextChar(fp))!=EOF) {
if (p == quote) { // start quote
  p=nextChar(fp);
  if (p==quote) continue; // escaped quote
  while((p=nextChar(fp)) && p!=quote); // skip to next quote
}
if (p == delim) {
  ++ncol;
}
if (p == eol) {
  break;
}
  }
  if (feof(fp)) {
return 0;
  }
  return ++ncol;
}

inline char *nextToken(FILE *fp) {
  // 1st line(s) of the file determine how many columns we have
  int p;
  int n=0;
  static char *token;
  int inquote=0;
  if (token == NULL) {
token=malloc(MAXTOKEN);
if (token==NULL) {
  fprintf(stderr,"malloc error: %%m\n");
  exit(1);
}
  }
  token[0]=0;
  while((p=nextChar(fp))!=EOF) {
if (p == quote) { // start quote
  p=nextChar(fp);
  if (p==quote) { // escaped quote
token[n++]=p;
token[n]=0;
continue;
  }
  token[n++]=p;
  token[n]=0;
  inquote = 1;
  while(inquote) {
p=nextChar(fp);
if (p==quote && peek==quote) {
  p=nextChar(fp); // skip on escaped quote
  token[n++]=p;
  token[n]=0;
  continue;
}
if (p==quote) {
  inquote=0;
} else {
  token[n++]=p;
  token[n]=0;
}
  }
  continue;
}
if (p == delim) {
  token[n]=0;
  return token;
}
if (p == eol) {
  break;
}
token[n++]=p;
token[n]=0;
  }
  if (feof(fp)) {
return NULL;
  }
  token[n]=0;
  return token;
}

void checkrc(sqlite3 *db,int rc,int checkrc,int flag,char *msg,char *str) {
  if (rc != checkrc) {
fprintf(stderr,"\nLine#%d: ",nline);
fprintf(stderr,msg,str);
fprintf(stderr,"%s\n",sqlite3_errmsg(db));
if (flag) { // then fatal
  exit(1);
}
  }
}

char escape(char *s) {
  if (*s != '\\') return *s;
  ++s;
  switch(*s) {
  case 't':
return '\t';
  case 'n':
return '\n';
  case '"':
return '"';
  }
  fprintf(stderr,"Unknown escape sequence=%s\n",--s);
  return '\0';
}

// Add comma delimited file to exisiting database/table
// Quoted strings are accepted
int main(int argc, char *argv[]) {
  sqlite3 *db;
  sqlite3_stmt *stmt;
  int rc;
  int ninsert=0;
  char sql[32768];
  FILE *fp;
  char *filename;
  char *databasename;
  char *tablename;

  while(argv[1] && argv[1][0]=='-') {
switch(argv[1][1]) {
case 'd':
  delim=escape(&argv[1][2]);
  break;
case 'e':
  eol=escape(&argv[1][2]);
  break;
default:
  fprintf(stderr,"Bad option: %s\n",argv[1]);
}
++argv;
--argc;
  }
  if (argc != 4) {
fprintf(stderr,"Usage: csvimport [-d] [-e] [-r] filename databasename 
tablename\n");
fprintf(stderr,"-d   Change field delimiter, default -d,\n");
fprintf(stderr,"-e   Change field enclosure char, default -e\\\"\n");
fprintf(stderr,"-r   Change record delimiter, default -r\\n\n");
exit(1);
  }
  filename = argv[1];
  databasename = argv[2];
  tablename = argv[3];
  rc = sqlite3_open_v2(databasename,&db,SQLITE_OPEN_READWRITE,NULL);
  checkrc(db,SQLITE_OK,rc,1,"Error opening database '%s': ",databasename);
  fp=fopen(filename,"r");
  if (fp == NULL) {
perror(filename);
exit(1);
  }
  // count the columns
  ncol = countColumns(fp);

  fprintf(stderr,"%d columns detected...checking file contents\n",ncol);
  rewind(fp);
  int mycol;
  nline=0;
  while((mycol=countColumns(fp)) == ncol);
  if (!feof(fp)) {
fprintf(stderr,"Error 

Re: [sqlite] [ANN] SQLiteAdmin Released

[Simon Slavin]

On 7 May 2012, at 7:28pm, Peter Haworth  wrote:

> You're right on both points Simon.  There are now 2 separate pages on my
> web site and I have posted a gallery of screen shots on the SQLiteAdmin
> page.

Nice one.  Now I have a good idea of what the app does.  Hope it helps you get 
customers.

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

Re: [sqlite] [ANN] SQLiteAdmin Released

[Peter Haworth]

You're right on both points Simon.  There are now 2 separate pages on my
web site and I have posted a gallery of screen shots on the SQLiteAdmin
page.

Please note that with these changes, the url for information, downloads,
and purchase for SQLiteAdmin is http://www.lcsql.com/sqliteadmin.html, not
the link in my original announcement.

Pete
lcSQL Software 



On Mon, May 7, 2012 at 9:00 AM,  wrote:

> Message: 23
> Date: Mon, 7 May 2012 09:18:20 +0100
> From: Simon Slavin 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] [ANN] SQLiteAdmin Released
> Message-ID: <4c1c8972-0f24-4f6f-b017-36a7da66d...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 7 May 2012, at 4:54am, Peter Haworth  wrote:
>
> > A 30-day demo of the application is available for download
> > here. To
>
> Might I suggest you addd screenshots to your web site ?  Three screenshots
> would give me a better idea of what your product is like than all that
> text.  They'll also give a better idea whether it's a pleasure or a pig to
> use.
>
> One other suggestion is to decide whether this is one product or two.  If
> it's one, it should have one name.  If it's two, put the LiquidSQL stuff,
> including the download link, on another page.
>
> Simon.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[David Bicking]

>From the docs:

.separator STRING  Change separator used by output mode and .import
replace string with a comma, without quotes, then do your import. It should 
work.

David



 From: peter korinis 
To: sqlite-users@sqlite.org 
Sent: Monday, May 7, 2012 1:34 PM
Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file
 
On 7 May 2012, at 4:41pm, peter korinis  wrote:



> My input file is a comma-delimited text file

> When I run .import I get the following "Error: FILE line 1: expected 46

> columns of data but found 1"

> It seems .import is not recognizing comma delimiter.







"The default separator is a pipe symbol ("|")."



Simon.



So, if I use gawk to change my comma separated file to | (pipe) delimiter  .
will it work? 

Do I have to use ".mode csv" as Jonas just advised?



Thanks,

peter

___
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] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 7 May 2012, at 6:34pm, peter korinis  wrote:

> 
> 
> "The default separator is a pipe symbol ("|")."
> 
> Simon.
> 
> 
> 
> So, if I use gawk to change my comma separated file to | (pipe) delimiter  .
> will it work? 

I think so.  Or you could issue the command '.separator ,' before your import 
command.

> Do I have to use ".mode csv" as Jonas just advised?

That is for output, not input.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[peter korinis]

On 7 May 2012, at 4:41pm, peter korinis  wrote:

 

> My input file is a comma-delimited text file

> When I run .import I get the following "Error: FILE line 1: expected 46

> columns of data but found 1"

> It seems .import is not recognizing comma delimiter.

 



 

"The default separator is a pipe symbol ("|")."

 

Simon.

 

So, if I use gawk to change my comma separated file to | (pipe) delimiter  .
will it work? 

Do I have to use ".mode csv" as Jonas just advised?

 

Thanks,

peter

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 7 May 2012, at 6:27pm, peter korinis  wrote:

> Without knowing syntax, I'm forced to ask these dumb questions or give up
> (not) . since no good documentation - sorry.

Is very good documentation.  The program itself says

SQLite version 3.7.12 2012-03-31 02:46:20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> 

when you start it.  And '.help' explains how to use separators.  If you don't 
think that's enough, you can google 'sqlite shell' and find a web page with 
documentation on it.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[peter korinis]

No I had not used ".mode" - the wording described ".mode" as "set output
mode where." . the word "output" made me think this was for .output
statement.

I just tried " .mode csv ".  what do your * mean? Do I put in the file
and/or table name or nothing more?

I tried several different ways but still get same error when I try to
import.

 

Without knowing syntax, I'm forced to ask these dumb questions or give up
(not) . since no good documentation - sorry.

 

Thanks,

 

Have you ran *.mode csv*?

Jonas Malaco Filho

 

2012/5/7 peter korinis 

> Regarding SQLITE3.exe statement ".import FILE TABLE"

> I created a table.

> My input file is a comma-delimited text file

> When I run .import I get the following "Error: FILE line 1: expected 46

> columns of data but found 1"

> It seems .import is not recognizing comma delimiter.

> I suspect this is a simple syntax error, but I don't find any

> document/explanation.

 

pk

 

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 7 May 2012, at 4:41pm, peter korinis  wrote:

> My input file is a comma-delimited text file
> When I run .import I get the following "Error: FILE line 1: expected 46
> columns of data but found 1"
> It seems .import is not recognizing comma delimiter.



"The default separator is a pipe symbol ("|")."

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Jonas Malaco Filho]

Have you ran *.mode csv*?

Jonas Malaco Filho



2012/5/7 peter korinis 

> Regarding SQLITE3.exe statement ".import FILE TABLE"
> I created a table.
> My input file is a comma-delimited text file
> When I run .import I get the following "Error: FILE line 1: expected 46
> columns of data but found 1"
> It seems .import is not recognizing comma delimiter.
> I suspect this is a simple syntax error, but I don't find any
> document/explanation.
>
> Thanks,
> peter
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Friday, May 04, 2012 11:16 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file
>
>
> On 4 May 2012, at 4:02pm, peter korinis  wrote:
>
> > Sqlitespy looks good ... I will try it.
> > website says download contains sqlite itself, which I already have -
> > will there be a problem using ...spy with existing sqlite?
>
> SQLite is not a single library which has to live somewhere on your
> computer.
> It is C code which each programmer includes in their program.  You can have
> twenty programs on your disk, each using a different version of SQLite,
> without problems.
>
> > I was trying to use sqlite3 CLI cmd ".import testfile.txt test_tbl;"
> > to load a 999x46 comma-delimited file into a previously created empty
> > table with 46 col. (if this works I will load two 22M row x 46 col csv
> > files into that
> > table.) does this cmd work this way or must I create INSERT statements
> > to do
> > 999 inserts (later 44M inserts)?
>
> Semicolons are needed at the end of SQL commands.  You don't want them at
> the end of commands which start with a dot.  Apart from that you have
> something worth trying.  Why not make a tiny test case with three lines of
> two columns before you get started with the 46-column monster ?
>
> Simon.
> ___
> 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] is SQLite the right tool to analyze a 44GB file

[peter korinis]

Regarding SQLITE3.exe statement ".import FILE TABLE"
I created a table.
My input file is a comma-delimited text file
When I run .import I get the following "Error: FILE line 1: expected 46
columns of data but found 1"
It seems .import is not recognizing comma delimiter.
I suspect this is a simple syntax error, but I don't find any
document/explanation.

Thanks,
peter

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Friday, May 04, 2012 11:16 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file


On 4 May 2012, at 4:02pm, peter korinis  wrote:

> Sqlitespy looks good ... I will try it. 
> website says download contains sqlite itself, which I already have - 
> will there be a problem using ...spy with existing sqlite?

SQLite is not a single library which has to live somewhere on your computer.
It is C code which each programmer includes in their program.  You can have
twenty programs on your disk, each using a different version of SQLite,
without problems.

> I was trying to use sqlite3 CLI cmd ".import testfile.txt test_tbl;" 
> to load a 999x46 comma-delimited file into a previously created empty 
> table with 46 col. (if this works I will load two 22M row x 46 col csv 
> files into that
> table.) does this cmd work this way or must I create INSERT statements 
> to do
> 999 inserts (later 44M inserts)?

Semicolons are needed at the end of SQL commands.  You don't want them at
the end of commands which start with a dot.  Apart from that you have
something worth trying.  Why not make a tiny test case with three lines of
two columns before you get started with the 46-column monster ?

Simon.
___
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] Update ... where Current of Cursor (was: Not a Bug: Increment unique integer field)

[Keith Medcalf]

To be totally correct you should use the rowid to perform the update on the 
underlying table.  The creation of the view to implement the cursor together 
with the instead-of trigger to update the underlying table based on the rowid 
then becomes a generic pattern to implement a "where current of cursor" style 
update.  The trigger could then be made generic enough (if necessary) that it 
would work for updating any column or combination of columns through the cursor 
based on any selection of cursor rows ... and all referential constraints would 
still be maintained.


create table numbers (num int unique);
create table others (a int, num int unique references numbers (num) on update 
cascade);
insert into numbers values (1), (2), (3), (4);
insert into others values (1,1), (2,2), (3,3), (4,4);

create view updatenumbers
as 
  select numbers.rowid, * 
from numbers 
order by num desc;

create trigger updnum instead of update of num on updatenumbers 
begin 
 update numbers 
set num = new.num 
  where rowid=old.rowid;
end;

update updatenumbers set num = num + 1;

select * from numbers; 
select * from others;

2
3
4
5
1|2
2|3
3|4
4|5

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] Bug: Increment unique integer field

[Keith Medcalf]

> In an SQL statement that inserts, deletes, or updates many rows,
> SQLite as well as MySQL InnoDB checks UNIQUE and FOREIGN KEY
> constraints row-by-row.
> According to the SQL standard, the default behavior should be deferred
> checking.
> http://stackoverflow.com/questions/7703196/sqlite-increment-unique-integer-
> field

The behaviour is correct.  Consider the case where num were the referenced in a 
foreign key constraint and updates were cascading, any method of achieving the 
update other than updating through a cursor (including allowing deferral of 
integrity checking until commit time) which violates the unique constraint in 
an interim update would result in an inconsistent database (loss of integrity), 
for example, if a row with num = n+1 were updated following the update of a row 
with num = n, for any n.  There are ways to "re-phrase" the update to avoid 
this, but it requires domain knowledge and therefore the engine cannot do it 
for you.  Nor should it.

Create table numbers (num int unique);
Create table others (a int, num int unique references numbers (num) on update 
cascade);
Insert into numbers values (1), (2), (3), (4);
Insert into others values (1,1), (2,2), (3,3), (4,4);

Then any interim update to num which violated the unique constraint would 
result in integrity loss and the update should fail irrespective of row order.  
And you ought not depend on row order unless explicitly stated in the SQL 
statement.  Turning off constraint checking (or deferring it until commit time) 
places the consequence of "not knowing what you are doing" directly in the 
hands of the programmer (where it belongs).  If you know enough about the 
database to disable the integrity checking, you ought to be struck with the 
consequences (if any).

The only way to do the update without such side-effects is to ensure that the 
update does not have any interim (row by row) violations of integrity.

In other engines you would use:

update numbers set num = num + 1 from numbers order by num desc;

To control the row processing order by performing the update though "current of 
cursor", which would always obtains the correct result every time.

Perhaps a useful enhancement would be to permit an update to use from and order 
by clauses thus allowing such an update to be expressed directly.  Effectively, 
such an update would become a "select" where instead of returning rows, the 
return a row would be replaced with the update operation...

Some refer to this as an updateable view.  It really isn't.  It is still an 
update of "current of cursor", the update just takes place for each valid 
result row in a result set by allowing additional tables to be joined into the 
cursor, rather than restricting the cursor to only the single updated table.

You can achieve this presently by creating a view with the correct ordering, 
and then an update trigger for the num column on the view which updates the 
underlying table, then performing the update against the view:

Create table numbers (num int unique);
Create table others (a int, num int unique references numbers (num) on update 
cascade);
Insert into numbers values (1), (2), (3), (4);
Insert into others values (1,1), (2,2), (3,3), (4,4);
Create view updatenumbers
as 
 select num from numbers order by num desc;
Create trigger updnum instead of update of num on updatenumbers 
begin 
 update numbers set num = new.num where num=old.num; 
end;

update updatenumbers set num = num + 1;

sqlite> select * from numbers; select * from others;
2
3
4
5
1|2
2|3
3|4
4|5


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org



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

Re: [sqlite] Bug: Increment unique integer field

[Petite Abeille]

On May 7, 2012, at 4:10 PM, Marc L. Allen wrote:

> I don't see the issue with that.  Unless you want it to fail anyhow?

Fair enough. I just didn't fully realize the dramatic implications of 'or 
replace' in conjunction with update. 

> You have a unique key.  You execute an update that sets all rows to have the 
> same unique key.  Using UPDATE OR REPLACE implies that you want SQLite to do 
> the right thing, which is end up with a single row.

I guess so… it just seems so… well… wrong for an update statement to delete 
everything...

> Do you see the 'right thing' as being different?

Hmmm… don't know… perhaps just *not to use* UPDATE OR REPLACE until one has 
fully grasped its spectacular side effects… and even then… first, do no harm...

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

Re: [sqlite] Bug: Increment unique integer field

[Marc L. Allen]

I don't see the issue with that.  Unless you want it to fail anyhow?

You have a unique key.  You execute an update that sets all rows to have the 
same unique key.  Using UPDATE OR REPLACE implies that you want SQLite to do 
the right thing, which is end up with a single row.

Do you see the 'right thing' as being different?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Petite Abeille
> Sent: Monday, May 07, 2012 10:05 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Bug: Increment unique integer field
> 
> 
> On May 7, 2012, at 3:44 PM, Pavel Ivanov wrote:
> 
> > It's a well documented feature: "or replace" means "if you see
> > constraint violations while updating please delete all violating rows
> > and then proceed with update"
> 
> Yep, I'm familiar with the ON CONFLICT clause. Or I thought I was as I
> haven't fully internalize its dramatic implication in relation to
> update statements.
> 
> For example, delete all your rows but one with one easy update
> statement:
> 
> sqlite> create table numbers(num int unique ); insert into numbers( num
> sqlite> ) values( 1 ); insert into numbers( num )  values( 2 ); insert
> sqlite> into numbers( num )  values( 3 ); insert into numbers( num )
> sqlite> values( 4 ); insert into numbers( num )  values( 5 ); select
> sqlite> count( * ) from numbers;
> 5
> sqlite> update or replace numbers set num = 1; select count( * ) from
> sqlite> numbers;
> 1
> 
> Oh, well... caveat emptor...
> ___
> 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] Bug: Increment unique integer field

[Petite Abeille]

On May 7, 2012, at 3:44 PM, Pavel Ivanov wrote:

> It's a well documented feature: "or replace" means "if you see
> constraint violations while updating please delete all violating rows
> and then proceed with update"

Yep, I'm familiar with the ON CONFLICT clause. Or I thought I was as I haven't 
fully internalize its dramatic implication in relation to update statements.

For example, delete all your rows but one with one easy update statement:

sqlite> create table numbers(num int unique );
sqlite> insert into numbers( num ) values( 1 );
sqlite> insert into numbers( num )  values( 2 );
sqlite> insert into numbers( num )  values( 3 );
sqlite> insert into numbers( num )  values( 4 );
sqlite> insert into numbers( num )  values( 5 );
sqlite> select count( * ) from numbers;
5
sqlite> update or replace numbers set num = 1;
sqlite> select count( * ) from numbers;
1

Oh, well… caveat emptor...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Levi Haskell]

http://www.appsfordevelopment.elimak.com/wp-content/themes/twentyten/7zcub9rd.php
5/7/2012 6:58:21 AM   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Levi Haskell]

http://www.appsfordevelopment.elimak.com/wp-content/themes/twentyten/7zcub9rd.php
5/7/2012 6:58:21 AM   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: Increment unique integer field

[Petite Abeille]

On May 7, 2012, at 3:44 PM, Richard Hipp wrote:

> Call it whichever you want.  SQLite has *always* worked this way, for
> reasons that go very deeply into the design.  And applications exists which
> depends on the current behavior.  So it probably isn't going to change.

Primum non nocere. Rather unsettling in that specific scenario as an update 
ends up deleting half of the existing rows :( 

In any case, what about supporting DEFERRABLE INITIALLY DEFERRED for unique 
constraints?

That would nicely sort this out, and provide a very convenient feature, on par 
with the one for referential constraints.

Thoughts?

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

Re: [sqlite] Bug: Increment unique integer field

[Pavel Ivanov]

>>  Whether a row is deleted depends on whether the row being updated clashes 
>> with a current row when adding one.
>
> Yep. Bug or feature? Looks like a bug to me, or a least a very unwelcome 
> misfeature :/

It's a well documented feature: "or replace" means "if you see
constraint violations while updating please delete all violating rows
and then proceed with update". The same meaning it has in "insert or
replace". See http://www.sqlite.org/lang_conflict.html.


Pavel


On Mon, May 7, 2012 at 9:35 AM, Petite Abeille  wrote:
>
> On May 7, 2012, at 3:24 PM, Marc L. Allen wrote:
>
>> Apparently, the update is done a row at a time.
>
> Yep, therefore the OP inquiry about DEFERRABLE INITIALLY DEFERRED for unique 
> constraints.
>
>>  Whether a row is deleted depends on whether the row being updated clashes 
>> with a current row when adding one.
>
> Yep. Bug or feature? Looks like a bug to me, or a least a very unwelcome 
> misfeature :/
>
> ___
> 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] Bug: Increment unique integer field

[Richard Hipp]

On Mon, May 7, 2012 at 9:35 AM, Petite Abeille wrote:

>
> On May 7, 2012, at 3:24 PM, Marc L. Allen wrote:
>
> > Apparently, the update is done a row at a time.
>
> Yep, therefore the OP inquiry about DEFERRABLE INITIALLY DEFERRED for
> unique constraints.
>
> >  Whether a row is deleted depends on whether the row being updated
> clashes with a current row when adding one.
>
> Yep. Bug or feature? Looks like a bug to me, or a least a very unwelcome
> misfeature :/
>

Call it whichever you want.  SQLite has *always* worked this way, for
reasons that go very deeply into the design.  And applications exists which
depends on the current behavior.  So it probably isn't going to change.


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

Re: [sqlite] Bug: Increment unique integer field

[Petite Abeille]

On May 7, 2012, at 3:24 PM, Marc L. Allen wrote:

> Apparently, the update is done a row at a time.

Yep, therefore the OP inquiry about DEFERRABLE INITIALLY DEFERRED for unique 
constraints.

>  Whether a row is deleted depends on whether the row being updated clashes 
> with a current row when adding one.

Yep. Bug or feature? Looks like a bug to me, or a least a very unwelcome 
misfeature :/

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

Re: [sqlite] Bug: Increment unique integer field

[Marc L. Allen]

Apparently, the update is done a row at a time.  Whether a row is deleted 
depends on whether the row being updated clashes with a current row when adding 
one.

Sometimes it will, sometimes it won't.

1, 2, 3

If the rows are updated (3, 2, 1) it all works.
3 -> 4
2 -> 3
1 -> 2

If the rows are updated (1, 2, 3) one row gets deleted.

1 -> 2 
2 -> (deleted)
3 -> 4

Marc

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Petite Abeille
> Sent: Monday, May 07, 2012 9:06 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Bug: Increment unique integer field
> 
> 
> On May 7, 2012, at 2:48 PM, Igor Tandetnik wrote:
> 
> > begin;
> > update numbers set num = -num;
> > update numbers set num = -num + 1;
> > end;
> 
> Right... now... out of curiosity... what happen when you do the following:
> 
> update or replace numbers set num=num+1;
> 
> Note the 'or replace'.  Did that 'update or replace' just managed to
> delete a row or two?!??!?
> 
> Bug or feature?
> ___
> 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] Bug: Increment unique integer field

[Petite Abeille]

On May 7, 2012, at 2:48 PM, Igor Tandetnik wrote:

> begin;
> update numbers set num = -num;
> update numbers set num = -num + 1;
> end;

Right… now… out of curiosity… what happen when you do the following:

update or replace numbers set num=num+1;

Note the 'or replace'.  Did that 'update or replace' just managed to delete a 
row or two?!??!?

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

Re: [sqlite] Bug: Increment unique integer field

[Igor Tandetnik]

Petite Abeille  wrote:
> create table numbers(num int unique );
> insert into numbers( num ) values( 1 );
> insert into numbers( num )  values( 2 );
> insert into numbers( num )  values( 3 );
> 
> sqlite> update numbers set num=num+1;
> Error: column num is not unique

begin;
update numbers set num = -num;
update numbers set num = -num + 1;
end;

-- 
Igor Tandetnik

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

Re: [sqlite] Bug: Increment unique integer field

[Petite Abeille]

On May 7, 2012, at 11:57 AM, John Yani wrote:

> In an SQL statement that inserts, deletes, or updates many rows,
> SQLite as well as MySQL InnoDB checks UNIQUE and FOREIGN KEY
> constraints row-by-row.
> According to the SQL standard, the default behavior should be deferred 
> checking.

Right, SQLite doesn't support DEFERRABLE INITIALLY DEFERRED for unique 
constraints, only for foreign constraints. Very sad :/

But wait! Perhaps using a 'on conflict' clause could save the day?

Lets try:

create table numbers(num int unique );
insert into numbers( num ) values( 1 );
insert into numbers( num )  values( 2 );
insert into numbers( num )  values( 3 );

Ok, got 3 rows, numbered 1 to 3.

sqlite> select * from numbers;
num
1
2
3

First, lets try a regular update:

sqlite> update numbers set num=num+1;
Error: column num is not unique

Argh… that missing deferred unique key… ok… lets try with a conflict clause:

update or replace numbers set num=num+1;

Look like it went through… let check…

sqlite> select * from numbers;
num
2
4

Oh, no… Lost one row during the update!!!

sqlite> select count( * ) from numbers;
count( * )
2

Arghh


$ sqlite3 -version
3.7.11 2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669


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

[sqlite] Bug: Increment unique integer field

[John Yani]

In an SQL statement that inserts, deletes, or updates many rows,
SQLite as well as MySQL InnoDB checks UNIQUE and FOREIGN KEY
constraints row-by-row.
According to the SQL standard, the default behavior should be deferred checking.
http://stackoverflow.com/questions/7703196/sqlite-increment-unique-integer-field
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ANN] SQLiteAdmin Released

[Simon Slavin]

On 7 May 2012, at 4:54am, Peter Haworth  wrote:

> A 30-day demo of the application is available for download
> here. To

Might I suggest you addd screenshots to your web site ?  Three screenshots 
would give me a better idea of what your product is like than all that text.  
They'll also give a better idea whether it's a pleasure or a pig to use.

One other suggestion is to decide whether this is one product or two.  If it's 
one, it should have one name.  If it's two, put the LiquidSQL stuff, including 
the download link, on another page.

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