Re: [sqlite] how to install sqlite3 in windows+python2.5

[jerome.mag...@bluewin.ch]

Download the zip archive containing "sqlite3.dll" for latest sqlite version. 
Then replace the existing library inside 
python 2.5 install. directory with the one downloaded.


Message d'origine
De: zealx...@hotmail.com
Date: 
10.06.2010 06:04
À: "General Discussion of SQLite Database"
Objet: Re: [sqlite] how to install 
sqlite3 in windows+python2.5

google told me, this is the real version, but it still old, i need 3.6.23
please help me.


>>> import sqlite3
>>> sqlite3.sqlite_version
'3.3.4'

--
From: "zeal" 

Sent: Thursday, June 10, 2010 11:54 AM
To: "General Discussion of SQLite Database" 
Subject: [sqlite] how to install sqlite3 in windows+python2.5

> hi,
>
>i installed python2.5, it 
seems wrapped sqlite3, but the version is 
> 2.3.2.
>sqlite3 2.3.2 could not use function group_concat, so i want 
to use 
> latest version of sqlite3.
> i know how to install sqlite3 in linux/unix, but i have no idea 
> install 
sqlite3 on windows.
> i have download the binary of sqlite3.exe, but it could not be used in 
> python scripts.
>
> 
please, help me.
> Thanks Daisy
> ___
> sqlite-users mailing list
> sqlite-
us...@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] how to install sqlite3 in windows+python2.5

[zeal]

google told me, this is the real version, but it still old, i need 3.6.23
please help me.

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.3.4'

--
From: "zeal" 
Sent: Thursday, June 10, 2010 11:54 AM
To: "General Discussion of SQLite Database" 
Subject: [sqlite] how to install sqlite3 in windows+python2.5

> hi,
>
>i installed python2.5, it seems wrapped sqlite3, but the version is 
> 2.3.2.
>sqlite3 2.3.2 could not use function group_concat, so i want to use 
> latest version of sqlite3.
> i know how to install sqlite3 in linux/unix, but i have no idea 
> install sqlite3 on windows.
> i have download the binary of sqlite3.exe, but it could not be used in 
> python scripts.
>
> please, help me.
> Thanks Daisy
> ___
> 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] how to install sqlite3 in windows+python2.5

[zeal]

hi, 

i installed python2.5, it seems wrapped sqlite3, but the version is  2.3.2.
sqlite3 2.3.2 could not use function group_concat, so i want to use latest 
version of sqlite3.
 i know how to install sqlite3 in linux/unix, but i have no idea install 
sqlite3 on windows. 
 i have download the binary of sqlite3.exe, but it could not be used in 
python scripts. 

 please, help me.
Thanks Daisy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use sqlite on flash file system like yaffs

[Wei Kai]

Richard, thank you for pointing to WAL. It seems interesting.

On Wed, Jun 9, 2010 at 11:58 PM, Richard Hipp  wrote:

> On Wed, Jun 9, 2010 at 11:26 AM, Jay A. Kreibich  wrote:
>
> > On Wed, Jun 09, 2010 at 01:40:30PM +0100, Simon Slavin scratched on the
> > wall:
> > >
> > > On 9 Jun 2010, at 9:48am, Wei Kai wrote:
> > >
> > > > I have a problem here. As we know sqlite creates a journal every time
> a
> > > > write transaction happens. On a flash file system, when a write to
> > database
> > > > failed because of the lack of enough disk spac
> > >
> > > Sorry, but if your program crashes because you ran out of disk space,
> > > you're out of luck.  There's no way to have a program recover from
> > > the situation because you have no space to put the recovered
> > > information in.  You need an intelligent human to decide what can
> > > be deleted.
> >
> >   I think the point he was making is that a developer cannot assume
> >  writing data to the middle of an existing file will not return an
> >  out-of-space error.
> >
> >  I'd be very surprised if SQLite makes any such assumptions, however.
> >
>
> SQLite makes no such assumption.
>
> On the other hand, if SQLite is unable to rollback a failed transaction,
> then it is stuck until the error is resolved.  Until sufficient space
> becomes available on the device to complete the rollback, the database will
> be unreadable.  There isn't much one can do about this when using a
> rollback
> journal.
>
> The problem does not come up with http://www.sqlite.org/draft/wal.html -
> the
> database continues to be readable after a failed checkpoint.
>
>
>
>
> >  This condition is hardly unique to flash filesystems.  Journaled
> >  filesystems can have the same issue, as do sparse files (although I
> >  doubt SQLite would ever create a sparse file).  Reallocation of
> >  blocks and automatic defragmentation can also contribute to
> >  similar issues.
> >
> > > If you are concerned about this situation, make a file called
> > > 'wasteofspace' which takes up 10 kilobytes of space.  When you
> > > need emergency space to recover from a crash, delete this file.
> >
> >   That's not necessarily useful.  See above.
> >
> >   -j
> >
> > --
> > Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> >
> > "Intelligence is like underwear: it is important that you have it,
> >  but showing it to the wrong people has the tendency to make them
> >  feel uncomfortable." -- Angela Johnson
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> -
> D. Richard Hipp
> d...@sqlite.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] [BUG] Adding an index changes query result

[Richard Hipp]

On Wed, Jun 9, 2010 at 3:34 PM, Israel Lins Albuquerque <
israel...@polibrasnet.com.br> wrote:

>
>
> I make the possible soluction I did't know if this is the best but is this:
>


The correct fix is checked in here:
http://www.sqlite.org/src/vinfo/6eb058dda8

-- 
-
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] Adding an index changes query result

[Israel Lins Albuquerque]

How this solve the bug? 

The function binaryCompareP5 is called (in query compile time) to know what 
affinity will be used to make the comparition between the expression "(c2 <= 
'2')", 
but that function are returning when index not exists SQLITE_AFF_INTEGER and 
not SQLITE_AFF_NONE because affinity of left expression "c2" (is a collumn) is 
INTEGER and 
rigth expression "'2'" is TEXT! 


- Mensagem original - 
De: "Israel Lins Albuquerque"  
Para: "General Discussion of SQLite Database"  
Enviadas: Quarta-feira, 9 de Junho de 2010 16:34:22 
Assunto: Re: [sqlite] [BUG] Adding an index changes query result 



I make the possible soluction I did't know if this is the best but is this: 

//
 
Index: D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c 
=== 
--- D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (revisão 323) 
+++ D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (cópia de trabalho) 
@@ -52,6 +52,18 @@ 
assert( pExpr->pTab && jpTab->nCol ); 
return pExpr->pTab->aCol[j].affinity; 
} 
+ if( op==TK_REGISTER ){ 
+ op = pExpr->op2; /* This only happens with SQLITE_ENABLE_STAT2 */ 
+ } 
+ if (op == TK_STRING) { 
+ return SQLITE_AFF_TEXT; 
+ } 
+ if (op == TK_INTEGER) { 
+ return SQLITE_AFF_NUMERIC; 
+ } 
+ if (op == TK_FLOAT) { 
+ return SQLITE_AFF_REAL; 
+ } 
return pExpr->affinity; 
} 

@@ -124,10 +136,10 @@ 
char sqlite3CompareAffinity(Expr *pExpr, char aff2){ 
char aff1 = sqlite3ExprAffinity(pExpr); 
if( aff1 && aff2 ){ 
- /* Both sides of the comparison are columns. If one has numeric 
+ /* Both sides of the comparison are columns. If both has numeric 
** affinity, use that. Otherwise use no affinity. 
*/ 
- if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){ 
+ if( sqlite3IsNumericAffinity(aff1) && sqlite3IsNumericAffinity(aff2) ){ 
return SQLITE_AFF_NUMERIC; 
}else{ 
return SQLITE_AFF_NONE; 
//
 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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


-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] [BUG] Adding an index changes query result

[Israel Lins Albuquerque]

I make the possible soluction I did't know if this is the best but is this: 

//
 
Index: D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c 
=== 
--- D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (revisão 323) 
+++ D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (cópia de trabalho) 
@@ -52,6 +52,18 @@ 
assert( pExpr->pTab && jpTab->nCol ); 
return pExpr->pTab->aCol[j].affinity; 
} 
+ if( op==TK_REGISTER ){ 
+ op = pExpr->op2; /* This only happens with SQLITE_ENABLE_STAT2 */ 
+ } 
+ if (op == TK_STRING) { 
+ return SQLITE_AFF_TEXT; 
+ } 
+ if (op == TK_INTEGER) { 
+ return SQLITE_AFF_NUMERIC; 
+ } 
+ if (op == TK_FLOAT) { 
+ return SQLITE_AFF_REAL; 
+ } 
return pExpr->affinity; 
} 

@@ -124,10 +136,10 @@ 
char sqlite3CompareAffinity(Expr *pExpr, char aff2){ 
char aff1 = sqlite3ExprAffinity(pExpr); 
if( aff1 && aff2 ){ 
- /* Both sides of the comparison are columns. If one has numeric 
+ /* Both sides of the comparison are columns. If both has numeric 
** affinity, use that. Otherwise use no affinity. 
*/ 
- if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){ 
+ if( sqlite3IsNumericAffinity(aff1) && sqlite3IsNumericAffinity(aff2) ){ 
return SQLITE_AFF_NUMERIC; 
}else{ 
return SQLITE_AFF_NONE; 
//
 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] compiling in foreign key support

[Jay A. Kreibich]

On Wed, Jun 09, 2010 at 03:15:32PM -0400, Sam Carleton scratched on the wall:
> >From looking at the compile page (http://www.sqlite.org/compile.html), I am
> not seeing any compile options to turn on foreign key support.  Was it left
> out of the documentation or does the feature really not exist yet?  If the
> feature isn't there yet, it would be really nice, there will never be a time
> when I don't want foreign key support.  I would imagine this is the norm for
> most embedded system, they either want it on all the time, or off all the
> time.

  In the current 3.6 version it is always there, but always turned off
  by default.  You need to use a PRAGMA to turn it on.

  People have asked about a compile flag to make it on by default, but
  it isn't there.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] compiling in foreign key support

[Sam Carleton]

>From looking at the compile page (http://www.sqlite.org/compile.html), I am
not seeing any compile options to turn on foreign key support.  Was it left
out of the documentation or does the feature really not exist yet?  If the
feature isn't there yet, it would be really nice, there will never be a time
when I don't want foreign key support.  I would imagine this is the norm for
most embedded system, they either want it on all the time, or off all the
time.

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

Re: [sqlite] database development - correct way?

[Tim Romano]

Oliver,
Your first solution

CREATE TABLE customer(
  idINTEGER PRIMARY KEY AUTOINCREMENT,
  customernumberINTEGER,
  customeroriginINTEGER,
  name  TEXT,
  UNIQUE(customernumber,customerorigin)
  );

is the better of the two because it simplifies foreign keys: the OrderHeader
table would contain a single-column reference to CUSTOMER rather than two
columns.  Either approach is legitimate as far as RDBMS design is concerned;
however some client-side application frameworks and middleware libraries do
not support multi-column primary keys.

Regards
Tim Romano
Swarthmore PA



On Wed, Jun 9, 2010 at 1:37 PM, Oliver Peters  wrote:

> Rich Shepard  writes:
>
> >
> > On Wed, 9 Jun 2010, Oliver Peters wrote:
> >
> > > So I assume that it is not(!) a mistake not(!) to use a composite PK in
> my
> > > table "customer" (customernumber,customerorigin) and to refer to it
> from
> > > the table "order" where I had to use these fields as a composite FK?
> >
> > Oliver,
> >
> >Too many negatives there for me to really follow what you're asking.
>
>
> sorry, I try my very best:
>
> adverted to the table customer I've 2 possible solutions and I ask myself
> if the
> first one is incorrect - the reason why I ask lies in the behaviour of my
> frontend (OpenOffice Base) that has problems to handle UNIQUE-Constraints
> under
> special circumstances (main-subform-connections)
>
> solution 1
> --
> CREATE TABLE customer(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>   UNIQUE(customernumber,customerorigin)
>   );
>
>
> solution 2
> --
> CREATE TABLE customer(
>customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>PRIMARY KEY(customernumber,customerorigin)
>   );
>
> thx for your patience
> Oliver
>
> ___
> 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] copy data from one db to another

[Vivien Malerba]

On 9 June 2010 20:44, Rich Shepard  wrote:
> On Wed, 9 Jun 2010, Vivien Malerba wrote:
>
>>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>>> there's no source file to copy.  Though a backup might be
>>> interesting ...
>
>> You can use Libgda's gda-sql tool in which you can:
>> * open a connection to the PostgreSQL db (for example named db1)
>> * open a connection to the SQLite db (for example named db2)
>> * bind those 2 connections into a 3rd one, and execute statements like
>> "insert into db2.table_one_name select * from db1.table_one_name ;"
>
>   Why not do a database dump from postgres, then read the .sql file into
> SQlite? As long as you use standard SQL in the data development language
> (DDL) you'll get ASCII SQL files for each table's schema with INSERT
> statements for each row of each table.

Because it's easier to set up, you won't have any problem with dates,
and you can do much more complicated statements (for example to do
comparisons, partial updates, ...)

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

Re: [sqlite] copy data from one db to another

[Rich Shepard]

On Wed, 9 Jun 2010, Vivien Malerba wrote:

>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>> there's no source file to copy.  Though a backup might be
>> interesting ...

> You can use Libgda's gda-sql tool in which you can:
> * open a connection to the PostgreSQL db (for example named db1)
> * open a connection to the SQLite db (for example named db2)
> * bind those 2 connections into a 3rd one, and execute statements like
> "insert into db2.table_one_name select * from db1.table_one_name ;"

   Why not do a database dump from postgres, then read the .sql file into
SQlite? As long as you use standard SQL in the data development language
(DDL) you'll get ASCII SQL files for each table's schema with INSERT
statements for each row of each table.

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

Re: [sqlite] copy data from one db to another

[Vivien Malerba]

On 9 June 2010 18:58, Scott Frankel  wrote:
>
> On Jun 9, 2010, at 12:22 AM, Vivien Malerba wrote:
>
>> On 8 June 2010 22:02, Scott Frankel  wrote:
>>>
>>> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>>>

> What's the best way to copy data from one db to another?
>
> Given 2 databases with identical schemas, one full of data and the
> other empty, the brute force way would be to perform selects on the
> source db, then for each row, perform an insert into the
> destination
> db.  Is there a more efficient way?

 The easiest is either to simply copy the file as Igor suggested or
 use
 the backup API (very easy too).
>>>
>>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>>> there's no source file to copy.  Though a backup might be
>>> interesting ...
>>
>> You can use Libgda's gda-sql tool in which you can:
>> * open a connection to the PostgreSQL db (for example named db1)
>> * open a connection to the SQLite db (for example named db2)
>> * bind those 2 connections into a 3rd one, and execute statements like
>> "insert into db2.table_one_name select * from db1.table_one_name ;"
>
> While Libgda looks very interesting, I need a solution that's
> accessible from common Linux, OSX, and Windows base installs.

Libgda (and associated tools) is available for those 3 OSes.

Regards,

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

Re: [sqlite] database development - correct way?

[Oliver Peters]

Rich Shepard  writes:

> 
> On Wed, 9 Jun 2010, Oliver Peters wrote:
> 
> > So I assume that it is not(!) a mistake not(!) to use a composite PK in my
> > table "customer" (customernumber,customerorigin) and to refer to it from
> > the table "order" where I had to use these fields as a composite FK?
> 
> Oliver,
> 
>Too many negatives there for me to really follow what you're asking.


sorry, I try my very best:

adverted to the table customer I've 2 possible solutions and I ask myself if the
first one is incorrect - the reason why I ask lies in the behaviour of my
frontend (OpenOffice Base) that has problems to handle UNIQUE-Constraints under
special circumstances (main-subform-connections)

solution 1
--
CREATE TABLE customer(
   idINTEGER PRIMARY KEY AUTOINCREMENT,
   customernumberINTEGER,
   customeroriginINTEGER,
   name  TEXT,
   UNIQUE(customernumber,customerorigin)
   );


solution 2
--
CREATE TABLE customer(
   customernumberINTEGER,
   customeroriginINTEGER,
   name  TEXT,
   PRIMARY KEY(customernumber,customerorigin)
   );

thx for your patience
Oliver

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

Re: [sqlite] database development - correct way?

[Rich Shepard]

On Wed, 9 Jun 2010, Oliver Peters wrote:

> So I assume that it is not(!) a mistake not(!) to use a composite PK in my
> table "customer" (customernumber,customerorigin) and to refer to it from
> the table "order" where I had to use these fields as a composite FK?

Oliver,

   Too many negatives there for me to really follow what you're asking.

   Whenever possible, the primary key for a table should be a 'natural' value
that uniquely describes that entity. For example, a vehicle identification
number, license registration number, or a person's passport number. For a
customer table there is no natural identifier so you make one up: the ID
column. This could be a sequential number or a compisite based on the
customer's name. The customer table stands alone and can be used in various
applications so you want only the single ID attribute as the primary key.

   If it is possible for a specific named customer to have several origins,
then you would want a composite primary key. But, if each customer has only
a single origin then you should have a simple primary key, the customer ID.

   The order table should have its own ID column as a primary key. This way
you assign each new order a different primary key even if the same customer
places two or more orders on the same date. For example:

order_numbercustomer_id date
1   1   9 June 2010
2   3   9 June 2010
3   1   9 June 2010

This makes each order unique regardless of customer, date, or items ordered.

HTH,

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

Re: [sqlite] copy data from one db to another

[Black, Michael (IS)]

Assuming postgres can load SQL from a file you should be able to use named 
pipes on all 3.
 
Here's windows:
sqlite> create table t (i integer);
sqlite> insert into t values(1);
sqlite> insert into t values(2);
sqlite> .output \\.\pipe\foo
sqlite> .dump
 
2nd window:
sqlite> .read \\.\pipe\foo
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t (i integer);
INSERT INTO "t" VALUES(1);
INSERT INTO "t" VALUES(2);
COMMIT;
 
OSX and LInux named pipes are probably more familair to people just using 
"mkfifo".
http://www.macosxhints.com/article.php?story=20041025103920992
 
so:
mkfifo foo
sqlite> create table t (i integer);
sqlite> insert into t values(1);
sqlite> insert into t values(2);
sqlite> .output foo  
sqlite> .dump
 
2nd window:
sqlite> .read foo  

 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Scott Frankel
Sent: Wed 6/9/2010 11:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] copy data from one db to another




On Jun 9, 2010, at 12:22 AM, Vivien Malerba wrote:

> On 8 June 2010 22:02, Scott Frankel  wrote:
>>
>> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>>
>>>
 What's the best way to copy data from one db to another?

 Given 2 databases with identical schemas, one full of data and the
 other empty, the brute force way would be to perform selects on the
 source db, then for each row, perform an insert into the 
 destination
 db.  Is there a more efficient way?
>>>
>>> The easiest is either to simply copy the file as Igor suggested or 
>>> use
>>> the backup API (very easy too).
>>
>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>> there's no source file to copy.  Though a backup might be
>> interesting ...
>
> You can use Libgda's gda-sql tool in which you can:
> * open a connection to the PostgreSQL db (for example named db1)
> * open a connection to the SQLite db (for example named db2)
> * bind those 2 connections into a 3rd one, and execute statements like
> "insert into db2.table_one_name select * from db1.table_one_name ;"

While Libgda looks very interesting, I need a solution that's 
accessible from common Linux, OSX, and Windows base installs.  Looks 
like the record-by-record approach is the best option for now.

Thanks
Scott



>
> If you want more info, tell me.
>
> Regards,
>
> Vivien
> ___
> 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] database development - correct way?

[Rich Shepard]

On Wed, 9 Jun 2010, Tim Romano wrote:

> Typically, Orders are divided into OrderHeader and OrderDetail tables:
>
> OrderHeader
> id integer primary key
> orderdate
> customerid
>
> OrderDetail
> id
> orderid  references OrderHeader(id)
> articleid references article(id)
> quantity int
>
> And you could then place a unique composite index on (orderid, articleid) in
> OrderDetail if you wanted to prevent the same article from appearing on more
> than one line-item of the order.

   To generalize this, when designing a database schema it is best to look at
the relationships involved. For example, the customer-order relationship is
1-to-many (each customer may have many separate orders). However, orders and
items are a many-to-many relationship (each order can have many items, and
each item can be on many orders).

   Usually, many-to-many relationships require an intermediate table that
uses the primary keys from both tables as a composite primary key.

   And, as Tim wrote, separating the order header from the line item details
makes both creation and maintenance much easier.

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

Re: [sqlite] database development - correct way?

[Oliver Peters]

Adam DeVita  writes:

> 
> I wouldn't advise using an SQL keyword as a table name: "Order"
> 
> I presume that your order collection table example is shorter than the real
> one for the sake of the example?

[...]

yes - and the content has nothing to do with my real tables. The example should
be only a well known for experienced db-designers I assume (I'm not an
experienced db-designer ;-) ).

So I assume that it is not(!) a mistake not(!) to use a composite PK in my table
"customer" (customernumber,customerorigin) and to refer to it from the table
"order" where I had to use these fields as a composite FK?

greetings
Oliver




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

Re: [sqlite] database development - correct way?

[Tim Romano]

Placing a unique composite index on (customer, article) in the Orders table
prevents recurring purchases of the same article by the same customer.  Acme
might buy a widget in June and then place another order for a widget in
September, but the order would be rejected as a duplicate.

Typically, Orders are divided into OrderHeader and OrderDetail tables:

OrderHeader
id integer primary key
orderdate
customerid

OrderDetail
id
orderid  references OrderHeader(id)
articleid references article(id)
quantity int

And you could then place a unique composite index on (orderid, articleid) in
OrderDetail if you wanted to prevent the same article from appearing on more
than one line-item of the order.

Regards
Tim Romano
Swarthmore PA



Regards
Tim Romano
.


On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters  wrote:

> Hello,
>
> despite it's just a question about construction I hope somebody is willing
> to
> push me into the right direction if necessary.
>
> my simplified case
> --
> I've the 3 tables customer, article and order
>
> my thoughts about the table customer:
> the customernumber can be from 3 different sources with possible
> overlappings
> (i.e. I can get 3 from source A and 3 from source B) so I adopt the
> field customerorigin to make a difference
> For simplicity I created a field id that is taking the part of the Primary
> Key
> and just declared "UNIQUE(customernumber,customerorigin)"
>
>
> the SQL-Code
> 
> CREATE TABLE customer(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>   UNIQUE(customernumber,customerorigin)
>   );
>
> CREATE TABLE article(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   name  TEXT
>   );
>
> CREATE TABLE order(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   id_customer   INTEGER,
>   id_articleINTEGER,
>   UNIQUE(id_customer,id_article),
>   FOREIGN KEY(id_customer) REFERENCES customer(id),
>   FOREIGN KEY(id_article)  REFERENCES article(id)
>   );
>
>
> simple question
> ---
> Is this a correct way or do I make a mistake?
>
> greetings
> Oliver
>
> ___
> 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] copy data from one db to another

[Scott Frankel]

On Jun 9, 2010, at 12:22 AM, Vivien Malerba wrote:

> On 8 June 2010 22:02, Scott Frankel  wrote:
>>
>> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>>
>>>
 What's the best way to copy data from one db to another?

 Given 2 databases with identical schemas, one full of data and the
 other empty, the brute force way would be to perform selects on the
 source db, then for each row, perform an insert into the  
 destination
 db.  Is there a more efficient way?
>>>
>>> The easiest is either to simply copy the file as Igor suggested or  
>>> use
>>> the backup API (very easy too).
>>
>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>> there's no source file to copy.  Though a backup might be
>> interesting ...
>
> You can use Libgda's gda-sql tool in which you can:
> * open a connection to the PostgreSQL db (for example named db1)
> * open a connection to the SQLite db (for example named db2)
> * bind those 2 connections into a 3rd one, and execute statements like
> "insert into db2.table_one_name select * from db1.table_one_name ;"

While Libgda looks very interesting, I need a solution that's  
accessible from common Linux, OSX, and Windows base installs.  Looks  
like the record-by-record approach is the best option for now.

Thanks
Scott



>
> If you want more info, tell me.
>
> Regards,
>
> Vivien
> ___
> 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] Minor WAL document typo

[Jim Morris]

http://www.sqlite.org/draft/wal.html
'a' should be 'as' in the text "located in the same directory or folder 
a the original database file"
Should be "located in the same directory or folder as the original 
database file"

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

Re: [sqlite] Use sqlite on flash file system like yaffs

[Richard Hipp]

On Wed, Jun 9, 2010 at 11:26 AM, Jay A. Kreibich  wrote:

> On Wed, Jun 09, 2010 at 01:40:30PM +0100, Simon Slavin scratched on the
> wall:
> >
> > On 9 Jun 2010, at 9:48am, Wei Kai wrote:
> >
> > > I have a problem here. As we know sqlite creates a journal every time a
> > > write transaction happens. On a flash file system, when a write to
> database
> > > failed because of the lack of enough disk spac
> >
> > Sorry, but if your program crashes because you ran out of disk space,
> > you're out of luck.  There's no way to have a program recover from
> > the situation because you have no space to put the recovered
> > information in.  You need an intelligent human to decide what can
> > be deleted.
>
>   I think the point he was making is that a developer cannot assume
>  writing data to the middle of an existing file will not return an
>  out-of-space error.
>
>  I'd be very surprised if SQLite makes any such assumptions, however.
>

SQLite makes no such assumption.

On the other hand, if SQLite is unable to rollback a failed transaction,
then it is stuck until the error is resolved.  Until sufficient space
becomes available on the device to complete the rollback, the database will
be unreadable.  There isn't much one can do about this when using a rollback
journal.

The problem does not come up with http://www.sqlite.org/draft/wal.html - the
database continues to be readable after a failed checkpoint.




>  This condition is hardly unique to flash filesystems.  Journaled
>  filesystems can have the same issue, as do sparse files (although I
>  doubt SQLite would ever create a sparse file).  Reallocation of
>  blocks and automatic defragmentation can also contribute to
>  similar issues.
>
> > If you are concerned about this situation, make a file called
> > 'wasteofspace' which takes up 10 kilobytes of space.  When you
> > need emergency space to recover from a crash, delete this file.
>
>   That's not necessarily useful.  See above.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
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] Adding an index changes query result

[Richard Hipp]

On Wed, Jun 9, 2010 at 11:29 AM, Pavel Ivanov  wrote:

> > Perhaps this is the way it was supposed to work.  But presence of index
> > does affect something, so I assumed it somehow messes affinity (what
> else?).
>
> Comparison of numbers and strings without affinities is supposed to
> work this way, yes. In this case though I think you're right -
> comparison on second column in index forgets to apply affinity rules
> when there's equality condition on the first column in index.
>

Close, but not quite the problem.  This issue is in range constraints:  c2>0
AND c2<'2'.  Turns out that if the no affinity transformation is required on
the first part of the constraint (c2>0) then none is applied to the second
(c2<'2') even if it is required. Hence all of the following work:

   SELECT * FROM t1 WHERE c1=5 AND c2>'0' AND c2<'2';
   SELECT * FROM t1 WHERE c1=5 AND c2>0 AND c2<2;
   SELECT * FROM t1 WHERE c1=5 AND c2>'0' AND c2<2;

Only the one case reported fails:

   SELECT * FROM t1 WHERE c1=5 AND c2>0 AND c2<'2';

Dan will be checking in a fix shortly.



>
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER);
> sqlite> INSERT INTO t (c1, c2) VALUES (5, 1);
> sqlite> INSERT INTO t (c1, c2) VALUES (5, 5);
> sqlite> INSERT INTO t (c1, c2) VALUES (5, -1);
> sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2);
> sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
> 5|1
> 5|5
> sqlite>
>
>
> Pavel
>
> On Wed, Jun 9, 2010 at 11:18 AM, Tomash Brechko
>  wrote:
> > 2010/6/9 Pavel Ivanov 
> >
> >> You can see that these 2 cases compare the same way. They both show
> >> that string is always greater than number and thus '11' > 2 and '2' >
> >> 11. And no affinity rules are applicable here because you use
> >> constants which don't have any affinity.
> >
> >
> > Perhaps this is the way it was supposed to work.  But presence of index
> > does affect something, so I assumed it somehow messes affinity (what
> else?).
> > ___
> > 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
>



-- 
-
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] Adding an index changes query result

[Pavel Ivanov]

> Perhaps this is the way it was supposed to work.  But presence of index
> does affect something, so I assumed it somehow messes affinity (what else?).

Comparison of numbers and strings without affinities is supposed to
work this way, yes. In this case though I think you're right -
comparison on second column in index forgets to apply affinity rules
when there's equality condition on the first column in index.

SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER);
sqlite> INSERT INTO t (c1, c2) VALUES (5, 1);
sqlite> INSERT INTO t (c1, c2) VALUES (5, 5);
sqlite> INSERT INTO t (c1, c2) VALUES (5, -1);
sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2);
sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
5|1
5|5
sqlite>


Pavel

On Wed, Jun 9, 2010 at 11:18 AM, Tomash Brechko
 wrote:
> 2010/6/9 Pavel Ivanov 
>
>> You can see that these 2 cases compare the same way. They both show
>> that string is always greater than number and thus '11' > 2 and '2' >
>> 11. And no affinity rules are applicable here because you use
>> constants which don't have any affinity.
>
>
> Perhaps this is the way it was supposed to work.  But presence of index
> does affect something, so I assumed it somehow messes affinity (what else?).
> ___
> 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] Use sqlite on flash file system like yaffs

[Jay A. Kreibich]

On Wed, Jun 09, 2010 at 01:40:30PM +0100, Simon Slavin scratched on the wall:
> 
> On 9 Jun 2010, at 9:48am, Wei Kai wrote:
> 
> > I have a problem here. As we know sqlite creates a journal every time a
> > write transaction happens. On a flash file system, when a write to database
> > failed because of the lack of enough disk spac
> 
> Sorry, but if your program crashes because you ran out of disk space,
> you're out of luck.  There's no way to have a program recover from
> the situation because you have no space to put the recovered
> information in.  You need an intelligent human to decide what can
> be deleted.

  I think the point he was making is that a developer cannot assume
  writing data to the middle of an existing file will not return an
  out-of-space error.

  I'd be very surprised if SQLite makes any such assumptions, however.
  This condition is hardly unique to flash filesystems.  Journaled
  filesystems can have the same issue, as do sparse files (although I
  doubt SQLite would ever create a sparse file).  Reallocation of
  blocks and automatic defragmentation can also contribute to
  similar issues.

> If you are concerned about this situation, make a file called
> 'wasteofspace' which takes up 10 kilobytes of space.  When you
> need emergency space to recover from a crash, delete this file.

  That's not necessarily useful.  See above.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] Adding an index changes query result

[Tomash Brechko]

2010/6/9 Pavel Ivanov 

> You can see that these 2 cases compare the same way. They both show
> that string is always greater than number and thus '11' > 2 and '2' >
> 11. And no affinity rules are applicable here because you use
> constants which don't have any affinity.


Perhaps this is the way it was supposed to work.  But presence of index
does affect something, so I assumed it somehow messes affinity (what else?).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] Adding an index changes query result

[Black, Michael (IS)]

Pavel -- did you miss my test here?
 
sqlite> select * from t where c1>=5 and c2>0 and c2<='2';
sqlite> select * from t where c1<=5 and c2>0 and c2<='2';
sqlite> select * from t where c1=5 and c2>0 and c2<='2';
5|5
sqlite> drop index t_c1_c2;
sqlite> select * from t where c1<=5 and c2>0 and c2<='2';
sqlite> select * from t where c1>=5 and c2>0 and c2<='2';
sqlite> select * from t where c1=5 and c2>0 and c2<='2';
sqlite>

Just the "=" operator breaks when you add the index...that appears to be a bug 
to me.
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Wed 6/9/2010 9:35 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [BUG] Adding an index changes query result



Eduardo,
You should never check correctness of what SQLite is doing in
postgresql or mysql. They are different database engines with
different principles. SQLite does exactly the right thing in this case
and exactly how it's documented. (I don't mean dependence of query
result on index existence of course.)

Tomash,

>>  sqlite> select '11' > 2;
>>  1
>>  sqlite> select 11 > '2';
>>  0
>>
>> It seems that last two cases should compare the same way, no matter
>> what the actual affinity rules are.

You can see that these 2 cases compare the same way. They both show
that string is always greater than number and thus '11' > 2 and '2' >
11. And no affinity rules are applicable here because you use
constants which don't have any affinity.


Pavel

2010/6/9 Eduardo Pérez Ureta :
> 2010-06-09 Tomash Brechko :
>> With SQLite 3.6.23.1 I see the following:
>>
>>  $ ./sqlite3 /tmp/a.sqlite
>>  SQLite version 3.6.23.1
>>  Enter ".help" for instructions
>>  Enter SQL statements terminated with a ";"
>>  sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER);
>>  sqlite> INSERT INTO t (c1, c2) VALUES (5, 5);
>>  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
>>  sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2);
>>  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
>>  5|5
>>
>> Note that the same query may or may not return the result based on
>> whether there's an index present.  Apparently on second invocation the
>> comparison is 5 <= '2', and numbers compare before strings.
>>
>> It's not clear to me which result should be considered correct though:
>>
>>  sqlite> select 11 > 2;
>>  1
>>  sqlite> select '11' > '2';
>>  0
>>  sqlite> select '11' > 2;
>>  1
>>  sqlite> select 11 > '2';
>>  0
>>
>> It seems that last two cases should compare the same way, no matter
>> what the actual affinity rules are.
>
> I just tested MySQL 5.1.47 :
> mysql> select 11 > 2;
> ++
> | 11 > 2 |
> ++
> |  1 |
> ++
> 1 row in set (0.40 sec)
>
> mysql> select '11' > '2';
> ++
> | '11' > '2' |
> ++
> |  0 |
> ++
> 1 row in set (0.00 sec)
>
> mysql> select '11' > 2;
> +--+
> | '11' > 2 |
> +--+
> |1 |
> +--+
> 1 row in set (0.00 sec)
>
> mysql> select 11 > '2';
> +--+
> | 11 > '2' |
> +--+
> |1 |
> +--+
> 1 row in set (0.00 sec)
>
>
> And PostgreSQL 8.4.4 :
> Type "help" for help.
>
> postgres=# SELECT 11 > 2;
>  ?column?
> --
>  t
> (1 row)
>
> postgres=# SELECT '11' > '2';
>  ?column?
> --
>  f
> (1 row)
>
> postgres=# SELECT '11' > 2;
>  ?column?
> --
>  t
> (1 row)
>
> postgres=# SELECT 11 > '2';
>  ?column?
> --
>  t
> (1 row)
>
> I consider MySQL and PostgreSQL are doing the correct thing so I
> consider this a bug in SQLite.
> Could you open a bug at the tracker? (If there is a bug open, what id it is?)
> ___
> 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] database development - correct way?

[Adam DeVita]

I wouldn't advise using an SQL keyword as a table name: "Order"

I presume that your order collection table example is shorter than the real
one for the sake of the example?  One often sees a date or time of some sort
associated with an order so that one can create reports based on dates.
(How many sales did we make this month?)

regards,
Adam




On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters  wrote:

> Hello,
>
> despite it's just a question about construction I hope somebody is willing
> to
> push me into the right direction if necessary.
>
> my simplified case
> --
> I've the 3 tables customer, article and order
>
> my thoughts about the table customer:
> the customernumber can be from 3 different sources with possible
> overlappings
> (i.e. I can get 3 from source A and 3 from source B) so I adopt the
> field customerorigin to make a difference
> For simplicity I created a field id that is taking the part of the Primary
> Key
> and just declared "UNIQUE(customernumber,customerorigin)"
>
>
> the SQL-Code
> 
> CREATE TABLE customer(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>   UNIQUE(customernumber,customerorigin)
>   );
>
> CREATE TABLE article(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   name  TEXT
>   );
>
> CREATE TABLE order(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   id_customer   INTEGER,
>   id_articleINTEGER,
>   UNIQUE(id_customer,id_article),
>   FOREIGN KEY(id_customer) REFERENCES customer(id),
>   FOREIGN KEY(id_article)  REFERENCES article(id)
>   );
>
>
> simple question
> ---
> Is this a correct way or do I make a mistake?
>
> greetings
> Oliver
>
> ___
> 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

Re: [sqlite] [BUG] Adding an index changes query result

[Pavel Ivanov]

Eduardo,
You should never check correctness of what SQLite is doing in
postgresql or mysql. They are different database engines with
different principles. SQLite does exactly the right thing in this case
and exactly how it's documented. (I don't mean dependence of query
result on index existence of course.)

Tomash,

>>  sqlite> select '11' > 2;
>>  1
>>  sqlite> select 11 > '2';
>>  0
>>
>> It seems that last two cases should compare the same way, no matter
>> what the actual affinity rules are.

You can see that these 2 cases compare the same way. They both show
that string is always greater than number and thus '11' > 2 and '2' >
11. And no affinity rules are applicable here because you use
constants which don't have any affinity.


Pavel

2010/6/9 Eduardo Pérez Ureta :
> 2010-06-09 Tomash Brechko :
>> With SQLite 3.6.23.1 I see the following:
>>
>>  $ ./sqlite3 /tmp/a.sqlite
>>  SQLite version 3.6.23.1
>>  Enter ".help" for instructions
>>  Enter SQL statements terminated with a ";"
>>  sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER);
>>  sqlite> INSERT INTO t (c1, c2) VALUES (5, 5);
>>  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
>>  sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2);
>>  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
>>  5|5
>>
>> Note that the same query may or may not return the result based on
>> whether there's an index present.  Apparently on second invocation the
>> comparison is 5 <= '2', and numbers compare before strings.
>>
>> It's not clear to me which result should be considered correct though:
>>
>>  sqlite> select 11 > 2;
>>  1
>>  sqlite> select '11' > '2';
>>  0
>>  sqlite> select '11' > 2;
>>  1
>>  sqlite> select 11 > '2';
>>  0
>>
>> It seems that last two cases should compare the same way, no matter
>> what the actual affinity rules are.
>
> I just tested MySQL 5.1.47 :
> mysql> select 11 > 2;
> ++
> | 11 > 2 |
> ++
> |      1 |
> ++
> 1 row in set (0.40 sec)
>
> mysql> select '11' > '2';
> ++
> | '11' > '2' |
> ++
> |          0 |
> ++
> 1 row in set (0.00 sec)
>
> mysql> select '11' > 2;
> +--+
> | '11' > 2 |
> +--+
> |        1 |
> +--+
> 1 row in set (0.00 sec)
>
> mysql> select 11 > '2';
> +--+
> | 11 > '2' |
> +--+
> |        1 |
> +--+
> 1 row in set (0.00 sec)
>
>
> And PostgreSQL 8.4.4 :
> Type "help" for help.
>
> postgres=# SELECT 11 > 2;
>  ?column?
> --
>  t
> (1 row)
>
> postgres=# SELECT '11' > '2';
>  ?column?
> --
>  f
> (1 row)
>
> postgres=# SELECT '11' > 2;
>  ?column?
> --
>  t
> (1 row)
>
> postgres=# SELECT 11 > '2';
>  ?column?
> --
>  t
> (1 row)
>
> I consider MySQL and PostgreSQL are doing the correct thing so I
> consider this a bug in SQLite.
> Could you open a bug at the tracker? (If there is a bug open, what id it is?)
> ___
> 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] Adding an index changes query result

[Eduardo Pérez Ureta]

2010-06-09 Tomash Brechko :
> With SQLite 3.6.23.1 I see the following:
>
>  $ ./sqlite3 /tmp/a.sqlite
>  SQLite version 3.6.23.1
>  Enter ".help" for instructions
>  Enter SQL statements terminated with a ";"
>  sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER);
>  sqlite> INSERT INTO t (c1, c2) VALUES (5, 5);
>  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
>  sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2);
>  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
>  5|5
>
> Note that the same query may or may not return the result based on
> whether there's an index present.  Apparently on second invocation the
> comparison is 5 <= '2', and numbers compare before strings.
>
> It's not clear to me which result should be considered correct though:
>
>  sqlite> select 11 > 2;
>  1
>  sqlite> select '11' > '2';
>  0
>  sqlite> select '11' > 2;
>  1
>  sqlite> select 11 > '2';
>  0
>
> It seems that last two cases should compare the same way, no matter
> what the actual affinity rules are.

I just tested MySQL 5.1.47 :
mysql> select 11 > 2;
++
| 11 > 2 |
++
|  1 |
++
1 row in set (0.40 sec)

mysql> select '11' > '2';
++
| '11' > '2' |
++
|  0 |
++
1 row in set (0.00 sec)

mysql> select '11' > 2;
+--+
| '11' > 2 |
+--+
|1 |
+--+
1 row in set (0.00 sec)

mysql> select 11 > '2';
+--+
| 11 > '2' |
+--+
|1 |
+--+
1 row in set (0.00 sec)


And PostgreSQL 8.4.4 :
Type "help" for help.

postgres=# SELECT 11 > 2;
 ?column?
--
 t
(1 row)

postgres=# SELECT '11' > '2';
 ?column?
--
 f
(1 row)

postgres=# SELECT '11' > 2;
 ?column?
--
 t
(1 row)

postgres=# SELECT 11 > '2';
 ?column?
--
 t
(1 row)

I consider MySQL and PostgreSQL are doing the correct thing so I
consider this a bug in SQLite.
Could you open a bug at the tracker? (If there is a bug open, what id it is?)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] Adding an index changes query result

[Black, Michael (IS)]

This is a bit weird...it appears it's just the = operator causing this...
 
sqlite> select * from t where c1>=5 and c2>0 and c2<='2';
sqlite> select * from t where c1<=5 and c2>0 and c2<='2';
sqlite> select * from t where c1=5 and c2>0 and c2<='2';
5|5
sqlite> drop index t_c1_c2;
sqlite> select * from t where c1<=5 and c2>0 and c2<='2';
sqlite> select * from t where c1>=5 and c2>0 and c2<='2';
sqlite> select * from t where c1=5 and c2>0 and c2<='2';
sqlite>
 
So the index is messing up the '=' operator.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Tomash Brechko
Sent: Wed 6/9/2010 8:52 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] [BUG] Adding an index changes query result



Hello,

With SQLite 3.6.23.1 I see the following:

  $ ./sqlite3 /tmp/a.sqlite
  SQLite version 3.6.23.1
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER);
  sqlite> INSERT INTO t (c1, c2) VALUES (5, 5);
  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
  sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2);
  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
  5|5

Note that the same query may or may not return the result based on
whether there's an index present.  Apparently on second invocation the
comparison is 5 <= '2', and numbers compare before strings.

It's not clear to me which result should be considered correct though:

  sqlite> select 11 > 2;
  1
  sqlite> select '11' > '2';
  0
  sqlite> select '11' > 2;
  1
  sqlite> select 11 > '2';
  0

It seems that last two cases should compare the same way, no matter
what the actual affinity rules are.


Regards,

--
   Tomash Brechko
___
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] Storing AVCHD files

[P Kishor]

On Wed, Jun 9, 2010 at 8:49 AM, Jean-Denis Muys  wrote:
>
> On 6/9/10 14:37 , "Simon Slavin"  wrote:
>
>>
>> On 9 Jun 2010, at 12:18pm, Navaneeth Sen B wrote:
>>
>>> I would like to know how i can store an AVCHD file(It has a folder
>>> structure) having size greater than 4GB.
>>
>> It is unlikely that whatever filesystem you're using will allow any file to 
>> be
>> this big.  Therefore you cannot have a database file this big either.  Leave
>> the folder the way it is, and put the filenames into your database.
>>
>
> Modern file systems allow files with sizes weighing in TB. HFS+ for example,
> which we have been using for a number of years already, has a single file
> size limit of 8 Exbibytes.
>


Indeed. See http://support.apple.com/kb/HT2422

"The theoretical maximum file size for a Mac OS Extended file system
is millions of terabytes. In practice, the maximum file size is
equivalent to the maximum volume size, except for a small amount of
disk space reserved for file system information."

Even in version 10.0 of Mac OS X (about 8 or 9 years ago), the max
file size was 2 TB.

Re. OP's question, if the AVCHD "file" is really a folder, you would
probably want to tar-gzip it into a file if you want to store it in a
db. That said, it is probably not a good idea to store it in a db.
Instead, store the metadata for the file in a db while keeping the
file on the file system, and then use the metadata to locate the file.
All of Jean-Denis' earlier reasons apply.

The general rule of thumb -- if you have lots and lots of tiny binary
files, store them in the db. This would be especially efficient if the
size of each file is less than the page size in the db, so the files
don't span pages. And, this would be especially useful if you don't
want to come up with a naming and storing structure for lots and lots
of such files.

An example of the above might be thumbnails of photos (not actual,
full size photos, but just the thumbnails), or 30 second samples of
music, etc. The max page size allowed in sqlite is 32 K, so that is a
good limit to impose.

On the other hand, if you have a few very large files (or a lot of
very large files) then it is better to store the metadata for those
files in the db, but store the files in the file system.




> Now your suggestion is sound, as I argued previously.
>
> Jean-Denis
>
> ___
> 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
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [BUG] Adding an index changes query result

[Tomash Brechko]

Hello,

With SQLite 3.6.23.1 I see the following:

  $ ./sqlite3 /tmp/a.sqlite
  SQLite version 3.6.23.1
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER);
  sqlite> INSERT INTO t (c1, c2) VALUES (5, 5);
  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
  sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2);
  sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2';
  5|5

Note that the same query may or may not return the result based on
whether there's an index present.  Apparently on second invocation the
comparison is 5 <= '2', and numbers compare before strings.

It's not clear to me which result should be considered correct though:

  sqlite> select 11 > 2;
  1
  sqlite> select '11' > '2';
  0
  sqlite> select '11' > 2;
  1
  sqlite> select 11 > '2';
  0

It seems that last two cases should compare the same way, no matter
what the actual affinity rules are.


Regards,

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

Re: [sqlite] Storing AVCHD files

[Jean-Denis Muys]

On 6/9/10 14:37 , "Simon Slavin"  wrote:

> 
> On 9 Jun 2010, at 12:18pm, Navaneeth Sen B wrote:
> 
>> I would like to know how i can store an AVCHD file(It has a folder
>> structure) having size greater than 4GB.
> 
> It is unlikely that whatever filesystem you're using will allow any file to be
> this big.  Therefore you cannot have a database file this big either.  Leave
> the folder the way it is, and put the filenames into your database.
> 

Modern file systems allow files with sizes weighing in TB. HFS+ for example,
which we have been using for a number of years already, has a single file
size limit of 8 Exbibytes.

Now your suggestion is sound, as I argued previously.

Jean-Denis

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

[sqlite] database development - correct way?

[Oliver Peters]

Hello,

despite it's just a question about construction I hope somebody is willing to
push me into the right direction if necessary.

my simplified case
--
I've the 3 tables customer, article and order

my thoughts about the table customer:
the customernumber can be from 3 different sources with possible overlappings
(i.e. I can get 3 from source A and 3 from source B) so I adopt the
field customerorigin to make a difference
For simplicity I created a field id that is taking the part of the Primary Key
and just declared "UNIQUE(customernumber,customerorigin)"


the SQL-Code

CREATE TABLE customer(
   idINTEGER PRIMARY KEY AUTOINCREMENT,
   customernumberINTEGER,
   customeroriginINTEGER,
   name  TEXT,
   UNIQUE(customernumber,customerorigin)
   );

CREATE TABLE article(
   idINTEGER PRIMARY KEY AUTOINCREMENT,
   name  TEXT
   );

CREATE TABLE order(
   idINTEGER PRIMARY KEY AUTOINCREMENT,
   id_customer   INTEGER,
   id_articleINTEGER,
   UNIQUE(id_customer,id_article),
   FOREIGN KEY(id_customer) REFERENCES customer(id),
   FOREIGN KEY(id_article)  REFERENCES article(id)
   );


simple question
---
Is this a correct way or do I make a mistake?

greetings
Oliver

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

Re: [sqlite] Use sqlite on flash file system like yaffs

[Simon Slavin]

On 9 Jun 2010, at 9:48am, Wei Kai wrote:

> I have a problem here. As we know sqlite creates a journal every time a
> write transaction happens. On a flash file system, when a write to database
> failed because of the lack of enough disk spac

Sorry, but if your program crashes because you ran out of disk space, you're 
out of luck.  There's no way to have a program recover from the situation 
because you have no space to put the recovered information in.  You need an 
intelligent human to decide what can be deleted.

If you are concerned about this situation, make a file called 'wasteofspace' 
which takes up 10 kilobytes of space.  When you need emergency space to recover 
from a crash, delete this file.

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

Re: [sqlite] Storing AVCHD files

[Simon Slavin]

On 9 Jun 2010, at 12:18pm, Navaneeth Sen B wrote:

> I would like to know how i can store an AVCHD file(It has a folder 
> structure) having size greater than 4GB.

It is unlikely that whatever filesystem you're using will allow any file to be 
this big.  Therefore you cannot have a database file this big either.  Leave 
the folder the way it is, and put the filenames into your database.

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

Re: [sqlite] Date format definition on bulk csv insert

[Simon Slavin]

On 9 Jun 2010, at 9:34am, twoblink wrote:

> in the csv; the field is defined like:
> 
> 2010.03.31 16:01:24.284  For a datetime field.

SQLite does not have any such field type.

> I would like to tell sqlite that this is in 
> .mm.dd hh:mm:ss.sss
> 
> How can I define this in sqlite's program so it imports the datetime
> correctly?

You can't.  Import it just as it is into a TEXT field.  Then write some code to 
convert it into whatever date/time format you actually want (the format you 
want to store it as will depend on what you want to use it for).

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

Re: [sqlite] Storing AVCHD files

[Navaneeth Sen B]

Thanks Jean,

That was a really great explanation..

Regards,
Sen
/
///


On 6/9/2010 5:39 PM, Jean-Denis Muys wrote:
> On 6/9/10 13:55 , "Navaneeth Sen B"  wrote:
>
>
>> Thanks Simon.
>>
>> Hi Jean,
>> I dint understand the below given statement. Could you please explain
>> this to me?
>>  
>>> possibility for the user to tamper with the files behind your
>>> application's back (though if you are using SQLite, s/he can do so in the
>>> monolithic case as well).
>>>
>>>
> What I mean is that the user can manipulate the video file herself, rather
> than letting your application do it. Depending on the said user action, your
> application might then misbehave, unless it's programmed defensively (which
> is always a good idea).
>
> Examples could be:
>
> 1- the user deletes the video file. Your database then contains a dangling
> reference. You as the programmer must be sure the be ready to handle a
> missing file situation.
>
> 2- different action, same result: the user put off line the hard disk with
> some video file(s). The file is missing to the application, and the
> reference is dangling.
>
> 3- the user edits the video file on its own with a movie editor. Your
> application sees the file, but it's been changed. For example, its duration
> is now different. If you store and use the duration, your application must
> be ready to have an out of date duration.
>
> 4- the user moves or renames a video file. Good OS'es have file reference
> that don't care about those user actions. They will still point to the
> correct file even after it's moved/renamed (even to a different volume). But
> you might not be running on such an OS. In that case, your application will
> see this situation as a missing media file.
>
> 5- The user moved the database from her office machine to her laptop, but
> forgot to copy the media file as well. Again, your app will now face massive
> missing media files.
>
> Note: it's possible for the app to differentiate between the different file
> missing situations, and possibly to act differently (at least in how it
> alerts the user and suggest a correction).
>
> I hope that clarified the point.
>
> Jean-Denis
>
> ___
> 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] Storing AVCHD files

[Jean-Denis Muys]

On 6/9/10 13:55 , "Navaneeth Sen B"  wrote:

> Thanks Simon.
> 
> Hi Jean,
> I dint understand the below given statement. Could you please explain
> this to me?
>> possibility for the user to tamper with the files behind your
>> application's back (though if you are using SQLite, s/he can do so in the
>> monolithic case as well).
>> 

What I mean is that the user can manipulate the video file herself, rather
than letting your application do it. Depending on the said user action, your
application might then misbehave, unless it's programmed defensively (which
is always a good idea).

Examples could be:

1- the user deletes the video file. Your database then contains a dangling
reference. You as the programmer must be sure the be ready to handle a
missing file situation.

2- different action, same result: the user put off line the hard disk with
some video file(s). The file is missing to the application, and the
reference is dangling.

3- the user edits the video file on its own with a movie editor. Your
application sees the file, but it's been changed. For example, its duration
is now different. If you store and use the duration, your application must
be ready to have an out of date duration.

4- the user moves or renames a video file. Good OS'es have file reference
that don't care about those user actions. They will still point to the
correct file even after it's moved/renamed (even to a different volume). But
you might not be running on such an OS. In that case, your application will
see this situation as a missing media file.

5- The user moved the database from her office machine to her laptop, but
forgot to copy the media file as well. Again, your app will now face massive
missing media files.

Note: it's possible for the app to differentiate between the different file
missing situations, and possibly to act differently (at least in how it
alerts the user and suggest a correction).

I hope that clarified the point.

Jean-Denis

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

Re: [sqlite] Storing AVCHD files

[Navaneeth Sen B]

Thanks Simon.

Hi Jean,
I dint understand the below given statement. Could you please explain 
this to me?
> possibility for the user to tamper with the files behind your
> application's back (though if you are using SQLite, s/he can do so in the
> monolithic case as well).
>
>

Thanks & Regards,
Sen



On 6/9/2010 5:14 PM, Jean-Denis Muys wrote:
> possibility for the user to tamper with the files behind your
> application's back (though if you are using SQLite, s/he can do so in the
> monolithic case as well).
>
>

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

Re: [sqlite] Storing AVCHD files

[Jean-Denis Muys]

On 6/9/10 13:26 , "Pavel Ivanov"  wrote:

>> So i assume i will have to
>> split my AVCHD file into small files. Ok.. i am fine with that, but how
>> will i maintain the folder structure of these split files.?
> 
> If maximum supported blob size is 1Gb and you want to store a bigger
> data chunk then you split your data into several *blobs* and store
> them in separate rows in the database. But SQLite database is one
> file, so there's no folder structure to maintain.
> 
> 
> Pavel
> 
> On Wed, Jun 9, 2010 at 7:18 AM, Navaneeth Sen B
>  wrote:
>> Hi All,
>> 
>> I would like to know how i can store an AVCHD file(It has a folder
>> structure) having size greater than 4GB.
>> The reason behind this question is like, from the documentation i found
>> that the maximum supported BLOB size is 1GB. So i assume i will have to
>> split my AVCHD file into small files. Ok.. i am fine with that, but how
>> will i maintain the folder structure of these split files.?
>> 

You don't tell us about your use case, but in many (most?) use cases, it's a
good idea to keep your AVCHD files outside the database, and store only a
reference to that file in the database.

Benefits:
- simplicity
- no need to split anything
- files are still there even if the database gets corrupted
- some/most of the data can be salvaged more easily in case of media failure
- files can be played by an outside player (eg VLC), whether directed by
your program or through an external workflow (including, but not limited to
manual user intervention).
- friendly to OS-level incremental backup schemes (such as Time Machine).
- friendly to OS-level indexing and searching mechanisms (though for video,
this is probably limited to metadata).
- makes it possible to distribute your video files across several hard
disks.
- makes it easier to let the user work on "light" (eg thumbnails) videofiles
in a constrained environment (eg mobile), only to switch back to full
definition video when back at the office.

Drawback:
- possibility for the user to tamper with the files behind your
application's back (though if you are using SQLite, s/he can do so in the
monolithic case as well).

Regards,

Jean-Denis

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

Re: [sqlite] Storing AVCHD files

[Pavel Ivanov]

> So i assume i will have to
> split my AVCHD file into small files. Ok.. i am fine with that, but how
> will i maintain the folder structure of these split files.?

If maximum supported blob size is 1Gb and you want to store a bigger
data chunk then you split your data into several *blobs* and store
them in separate rows in the database. But SQLite database is one
file, so there's no folder structure to maintain.


Pavel

On Wed, Jun 9, 2010 at 7:18 AM, Navaneeth Sen B
 wrote:
> Hi All,
>
> I would like to know how i can store an AVCHD file(It has a folder
> structure) having size greater than 4GB.
> The reason behind this question is like, from the documentation i found
> that the maximum supported BLOB size is 1GB. So i assume i will have to
> split my AVCHD file into small files. Ok.. i am fine with that, but how
> will i maintain the folder structure of these split files.?
>
> -- /
> Thanks & Regards,
> Sen///
>
>
> ___
> 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] Concurrency for in-memory scenarios

[Pavel Ivanov]

> Could you please explain what you meant in this line?
>> Of course I don't consider option of concurrency from
>> different processes.

I guess I meant that my above words are related to the case when you
access the database from the same process, and if one talks about
different processes it's a little bit different story.
But to be honest it's really hard to remember what I meant after 2 months... :)


Pavel

On Wed, Jun 9, 2010 at 7:04 AM, Navaneeth Sen B
 wrote:
> Hi Pavel,
> Could you please explain what you meant in this line?
>> Of course I don't consider option of concurrency from
>> different processes.
> Thanks & Regards,
> Sen
>
>
> On 4/6/2010 8:33 PM, Pavel Ivanov wrote:
>>> Can anyone confirm whether concurrent
>>> access to an in-memory database is supported?
>>>
>> No, SQLite doesn't support full concurrent access to any database. The
>> only concurrency you can earn is having on-disk database without
>> shared cache (so actually having several copies of the database in
>> memory). Of course I don't consider option of concurrency from
>> different processes.
>>
>>
>> Pavel
>>
>> On Tue, Apr 6, 2010 at 9:43 AM, Kent Boogaart  
>> wrote:
>>
>>>   Hi there,
>>>
>>>   I've recently been struggling with concurrency for an in-memory
>>> SQLite scenario. Basically, I want to be able to perform concurrent
>>> reads against an in-memory SQLite database, thus using multiple CPUs
>>> to good effect.
>>>
>>>   I've tried everything I could think of and find in various blog posts
>>> to get this working, but SQLite insisted on synchronizing the reads.
>>> Switching from an in-memory DB to an on-disk DB immediately rectified
>>> the issue.
>>>
>>>   What I'm wondering is whether SQLite flat out doesn't support
>>> concurrent access to an in-memory database, or perhaps whether I'm
>>> just doing something wrong. Can anyone confirm whether concurrent
>>> access to an in-memory database is supported?
>>>
>>>   Thanks,
>>>   Kent
>>>
>>> ___
>>> 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

[sqlite] Storing AVCHD files

[Navaneeth Sen B]

Hi All,

I would like to know how i can store an AVCHD file(It has a folder 
structure) having size greater than 4GB.
The reason behind this question is like, from the documentation i found 
that the maximum supported BLOB size is 1GB. So i assume i will have to 
split my AVCHD file into small files. Ok.. i am fine with that, but how 
will i maintain the folder structure of these split files.?

-- /
Thanks & Regards,
Sen///


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

Re: [sqlite] Concurrency for in-memory scenarios

[Navaneeth Sen B]

Hi Pavel,
Could you please explain what you meant in this line?
> Of course I don't consider option of concurrency from
> different processes.
Thanks & Regards,
Sen


On 4/6/2010 8:33 PM, Pavel Ivanov wrote:
>> Can anyone confirm whether concurrent
>> access to an in-memory database is supported?
>>  
> No, SQLite doesn't support full concurrent access to any database. The
> only concurrency you can earn is having on-disk database without
> shared cache (so actually having several copies of the database in
> memory). Of course I don't consider option of concurrency from
> different processes.
>
>
> Pavel
>
> On Tue, Apr 6, 2010 at 9:43 AM, Kent Boogaart  wrote:
>
>>   Hi there,
>>
>>   I've recently been struggling with concurrency for an in-memory
>> SQLite scenario. Basically, I want to be able to perform concurrent
>> reads against an in-memory SQLite database, thus using multiple CPUs
>> to good effect.
>>
>>   I've tried everything I could think of and find in various blog posts
>> to get this working, but SQLite insisted on synchronizing the reads.
>> Switching from an in-memory DB to an on-disk DB immediately rectified
>> the issue.
>>
>>   What I'm wondering is whether SQLite flat out doesn't support
>> concurrent access to an in-memory database, or perhaps whether I'm
>> just doing something wrong. Can anyone confirm whether concurrent
>> access to an in-memory database is supported?
>>
>>   Thanks,
>>   Kent
>>
>> ___
>> 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] Date format definition on bulk csv insert

[Pavel Ivanov]

> I would like to tell sqlite that this is in
> .mm.dd hh:mm:ss.sss
>
> How can I define this in sqlite's program so it imports the datetime
> correctly?

SQLite doesn't have datetime type and thus doesn't have any datetime
format to use in the import. Your datetime is imported as simple
string, whatever format it has. But if you've got nulls in all rows
then you have some other problem.


Pavel

On Wed, Jun 9, 2010 at 4:34 AM, twoblink  wrote:
>
> Hi,
>
> I have a few gigs of csv information I need to import; using SqliteMaestro
> works but imports about 1000 rows / sec and I have a few hundred million
> rows, so that's not a real solution.
>
> I did the .mode csv, .import from the sqlite3 program, and everything
> imported fine except the datetime field.  It came up null.
>
> in the csv; the field is defined like:
>
> 2010.03.31 16:01:24.284  For a datetime field.
>
> I would like to tell sqlite that this is in
> .mm.dd hh:mm:ss.sss
>
> How can I define this in sqlite's program so it imports the datetime
> correctly?
>
> Thanks.
> Albert
> --
> View this message in context: 
> http://old.nabble.com/Date-format-definition-on-bulk-csv-insert-tp28827421p28827421.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] Use sqlite on flash file system like yaffs

[Wei Kai]

hi list,
I have a problem here. As we know sqlite creates a journal every time a
write transaction happens. On a flash file system, when a write to database
failed because of the lack of enough disk space, the journal file just
remain there, and the next time  when user try to access the database,
sqlite will try to recover some part of the db file from the journal first.
But, writing to a flash file system need more free blocks even the writing
doesn't increase the total size of the file. When this happens, database
fail to recover the db file and any access to the db will fail. I want to
know if there is any solution to this problem. Any Suggestions and comments
are welcome.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Date format definition on bulk csv insert

[twoblink]

Hi,

I have a few gigs of csv information I need to import; using SqliteMaestro
works but imports about 1000 rows / sec and I have a few hundred million
rows, so that's not a real solution.

I did the .mode csv, .import from the sqlite3 program, and everything
imported fine except the datetime field.  It came up null.

in the csv; the field is defined like:

2010.03.31 16:01:24.284  For a datetime field.

I would like to tell sqlite that this is in 
.mm.dd hh:mm:ss.sss

How can I define this in sqlite's program so it imports the datetime
correctly?

Thanks.
Albert
-- 
View this message in context: 
http://old.nabble.com/Date-format-definition-on-bulk-csv-insert-tp28827421p28827421.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 and Qt

[Sylvain Pointeau]

build your qt sqlite as a plugin
then recompile this plugin with the version you want.

http://doc.trolltech.com/4.6/sql-driver.html#qsqlite-for-sqlite-version-3-and-above

best
regards,
Sylvain

On Wed, Jun 9, 2010 at 4:32 AM, Sam Carleton  wrote:

> On Tue, Jun 8, 2010 at 10:25 PM, Bill King  wrote:
>
> >
> > >
> > >1. How do I control the version of SQLite used with Qt?
> > >
> > ./configure -system-sqlite will use the sqlite compiled for your system.
> >
>
> No, not how do I compile SQLite into Qt, I have done that.  I want to
> control the VERSION.  I don't know if the distro of Qt is using the latest
> version of SQLite or not.  Also, if I opt to NOT upgrade the Apache server,
> I would like Qt to use the same version of SQLite that the Apache server is
> using ;)
>
> Sam
> ___
> 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] copy data from one db to another

[Vivien Malerba]

On 8 June 2010 22:02, Scott Frankel  wrote:
>
> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>
>>
>>> What's the best way to copy data from one db to another?
>>>
>>> Given 2 databases with identical schemas, one full of data and the
>>> other empty, the brute force way would be to perform selects on the
>>> source db, then for each row, perform an insert into the destination
>>> db.  Is there a more efficient way?
>>
>> The easiest is either to simply copy the file as Igor suggested or use
>> the backup API (very easy too).
>
> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
> there's no source file to copy.  Though a backup might be
> interesting ...

You can use Libgda's gda-sql tool in which you can:
* open a connection to the PostgreSQL db (for example named db1)
* open a connection to the SQLite db (for example named db2)
* bind those 2 connections into a 3rd one, and execute statements like
"insert into db2.table_one_name select * from db1.table_one_name ;"

If you want more info, tell me.

Regards,

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