Re: [sqlite] Java wrapper for both windows and linux

[BardzoTajneKonto]

> I have been unable to get this package to compile under Linux. Do you use it
> in Linux, or Windows?

I remember I also had problem compiling this. It didnt find some executable. 
I'v downloaded missing executable, put it in PATH and there were no other 
problems.
But actually it isn't necessary if you want only JNI version. By default both 
version are compiled, and most time is spent building NestedVM version - it 
is also the one that causes problems. Even if there is no single target in 
the make that makes JNI version, it shouldn't be hard to remove all stuff 
required for NestedVM (JNI version only needs SQlite and downloaded 
source).

--
Nawet 50MB w jednym mailu >>> http://link.interia.pl/f19e7


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Java wrapper for both windows and linux

[Randy J. Ray]

On 3/29/07, Xavier RAYNAUD <[EMAIL PROTECTED]> wrote:


I currently use this one:
http://www.zentus.com/sqlitejdbc/

o The nestedVM will work for both windows and linux, but you will loose
performance.
o The native JNI libraries are faster, but do not work for both windows
and linux.



I have been unable to get this package to compile under Linux. Do you use it
in Linux, or Windows?

--
Randy J. Ray / [EMAIL PROTECTED]
Campbell, CA

Re: [sqlite] Retrieve Database Metadata

[Joel Cochran]

Thank you both... I'll be looking at those...

Joel

On 3/29/07, Griggs, Donald <[EMAIL PROTECTED]> wrote:


Hi Joel,


Have you already looked at:
 http://www.sqlite.org/pragma.html#schema

(as well as sqlite_master as mentioned earlier)

-Original Message-
From: Joel Cochran [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 29, 2007 1:55 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Retrieve Database Metadata

Hi all,

Is there a way (through SQL) to retrieve metadata about an SQLite
database?
Specifically I would like to find lists of tables, indexes, views, and
table schema.

Thanks,

--
Joel Cochran


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Optimize my database

[Ken]

jonas,
 
 here is a quick stab at normailzation for you:
 
 This is your file table. It only contains the file infomration !
 CREATE TABLE Files ( id integer primary key,
   ,  path VARCHAR(255)
   ,  title VARCHAR(255)  ?? song name?
   ,  track INTEGER
   , length INTEGER
   , bitrate INTEGER
   ,playcount INTEGER
   ,changed INTEGER
   , size INTEGER   ?? is this really 
needed, possibly dup of length?
   , tagged INTEGER
   , extensionVARCHAR(5)
   , file_exists INTEGER)";
 
 crate table artist (id integer primary key
   , artitst_firstvarchar(60)
   , artist_last varchar(60) ) ;
 
 crate table genre (id integer primary key
   , genre varchar (255) );
  
 
 CREATE TABLE album( id integer primary key 
 , idFile integer
, artitstId  integer 
 , genreId integer
, name  VARCHAR(255)
, comment  VARCHAR(255) );
 
 
 so say you want a list of all files for an album?
 
 select  path, length, bitrate 
from files f , album a, 
where   f.id  = a.idFile
   and a.name = 'xyz' ;
 
 That should  do it
 similar stuff for genres, artitst etc
 
 and indices where appropriate on say 
  file.title
  album.artistid
  album.name
   album.genreId
 
  artitst.last_name, artitst.first_name
 
 
 the above may not be syntactically correct but you should get the idea.
 

Jonas Sandman <[EMAIL PROTECTED]> wrote: Hello again,

I am quite new at database and how to set them up properly so keep in mind
that the obvious might stare into your face but just point it out to me :-)

I am having this database with mediafiles (even anyone recall my previous
mails) and I create it like this:

CREATE TABLE Files (path VARCHAR(255) PRIMARY KEY, title VARCHAR(255),
artist VARCHAR(255), album VARCHAR(255), genre VARCHAR(255), comment
VARCHAR(255), track INTEGER, year INTEGER, length INTEGER, bitrate INTEGER,
playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER, extension
VARCHAR(5), file_exists INTEGER)";

and I add some indexation like this:
setup = "CREATE INDEX title Files (title, artist, album, genre, comment,
extension)";

I insert data into the database and I can query it kinda fast, but when
there is lots of files (> 15000) the database seems sluggish and doesn't
return the results at all as fast as I would expect (or like!).

Is there something I can do with this that can make it faster?

Best regards,
Jonas

RE: [sqlite] Retrieve Database Metadata

[Griggs, Donald]

Hi Joel,


Have you already looked at:
 http://www.sqlite.org/pragma.html#schema

(as well as sqlite_master as mentioned earlier) 

-Original Message-
From: Joel Cochran [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 29, 2007 1:55 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Retrieve Database Metadata

Hi all,

Is there a way (through SQL) to retrieve metadata about an SQLite
database?
Specifically I would like to find lists of tables, indexes, views, and
table schema.

Thanks,

--
Joel Cochran

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] RE: Crash in prepSelectStmt

[Dave Gierok]

Ahh, sorry about that stack.  It's not actually a trashed call stack.  The 
variables are not accurate here because it is an optimized build's call stack.  
The reason I included it was merely to show the function-call-stack, which is 
accurate.


From: Dave Gierok
Sent: Thursday, March 29, 2007 10:18 AM
To: 'sqlite-users@sqlite.org'
Subject: Crash in prepSelectStmt

I'm getting a very occational crash in prepSelectStmt when trying to run the 
following statement:

CREATE TABLE tmpdb.[Profile0_Career_UnlockedCars] AS SELECT * FROM 
[Profile0_Career_UnlockedCars];

Where another tmpdb has been attached to the main db via ATTACH.

My call stack is:

*  forza2_ply.dll!prepSelectStmt(Parse * pParse=0x6a99bbd0, Select * 
p=0x)  line 1326 + 0x18 bytes   C
*  forza2_ply.dll!sqlite3SelectResolve(Parse * pParse=0x, 
Select * p=0x, NameContext * pOuterNC=0x)  line 2524 + 0xC 
bytesC
*  forza2_ply.dll!sqlite3Select(Parse * pParse=0x, Select * 
p=0x, int eDest=0x, int iParm=0x, Select * 
pParent=0x, int parentTab=0x, int * pParentAgg=0x, char 
* aff=0x)  line 2811 + 0x10 bytes C
*  forza2_ply.dll!sqlite3EndTable(Parse * pParse=0x, Token * 
pCons=0x, Token * pEnd=0x, Select * pSelect=0x)  line 
1464C
*  forza2_ply.dll!yy_reduce(yyParser * yypParser=0x, int 
yyruleno=0x)  line 156 + 0x18 bytes C
*  forza2_ply.dll!sqlite3Parser(void * yyp=0x, int 
yymajor=0x, Token yyminor={...}, Parse * pParse=0x)  line 3234 
+ 0xC bytesC
*  forza2_ply.dll!sqlite3RunParser(Parse * pParse=0x, const 
char * zSql=0x, char * * pzErrMsg=0x)  line 444   C
*  forza2_ply.dll!sqlite3_prepare(sqlite3 * db=0x, const char * 
zSql=0x, int nBytes=0x, sqlite3_stmt * * ppStmt=0x, 
const char * * pzTail=0x)  line 492 C

It is crashing (Access violation reading invalid pointer) on the line:
setToken(&pRight->token, zName);

Is there a known bug?  Could we be setting up something wrong?  Anyone have any 
advice on how to debug this?  I'd hate to ship our game with this but we're 
getting down to the wire and it looks like we may need to do just that.

Thanks,
Dave

Re: [sqlite] Retrieve Database Metadata

[P Kishor]

On 3/29/07, Joel Cochran <[EMAIL PROTECTED]> wrote:

Hi all,

Is there a way (through SQL) to retrieve metadata about an SQLite database?
Specifically I would like to find lists of tables, indexes, views, and table
schema.




http://www.sqlite.org/faq.html#q9


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Retrieve Database Metadata

[Joel Cochran]

Hi all,

Is there a way (through SQL) to retrieve metadata about an SQLite database?
Specifically I would like to find lists of tables, indexes, views, and table
schema.

Thanks,

--
Joel Cochran

Re: [sqlite] Crash in prepSelectStmt

[drh]

Dave Gierok <[EMAIL PROTECTED]> wrote:
> I'm getting a very occational crash in prepSelectStmt when 
> trying to run the following statement:
> 
> CREATE TABLE tmpdb.[Profile0_Career_UnlockedCars] AS SELECT * FROM 
> [Profile0_Career_UnlockedCars];
> 
> My call stack is:
> 
> *  forza2_ply.dll!sqlite3_prepare(sqlite3 * db=0x, const char 
> * zSql=0x, int nBytes=0x, sqlite3_stmt * * ppStmt=0x, 
> const char * * pzTail=0x)  line 492 C
> 
> 
> Is there a known bug? 

All known bugs are listed on the SQLite website.  This is
not one of them that I recall, though without knowing what
version of SQLite you are using, it is difficult to say.

> Could we be setting up something wrong?

Very possibly.  It appears that something is trashing your stack.
Possibly another thread?

>  Anyone have any advice on how to debug this?
> 

Mentioning what version of SQLite you are using won't hurt.

Since this is an intermittent crash, that suggests a threading
problem.  Those are notoriously difficult to debug.  If you
can come up with a test case that fails reliability, we might
be able to help you.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Crash in prepSelectStmt

[Dave Gierok]

I'm getting a very occational crash in prepSelectStmt when trying to run the 
following statement:

CREATE TABLE tmpdb.[Profile0_Career_UnlockedCars] AS SELECT * FROM 
[Profile0_Career_UnlockedCars];

Where another tmpdb has been attached to the main db via ATTACH.

My call stack is:

*  forza2_ply.dll!prepSelectStmt(Parse * pParse=0x6a99bbd0, Select * 
p=0x)  line 1326 + 0x18 bytes   C
*  forza2_ply.dll!sqlite3SelectResolve(Parse * pParse=0x, 
Select * p=0x, NameContext * pOuterNC=0x)  line 2524 + 0xC 
bytesC
*  forza2_ply.dll!sqlite3Select(Parse * pParse=0x, Select * 
p=0x, int eDest=0x, int iParm=0x, Select * 
pParent=0x, int parentTab=0x, int * pParentAgg=0x, char 
* aff=0x)  line 2811 + 0x10 bytes C
*  forza2_ply.dll!sqlite3EndTable(Parse * pParse=0x, Token * 
pCons=0x, Token * pEnd=0x, Select * pSelect=0x)  line 
1464C
*  forza2_ply.dll!yy_reduce(yyParser * yypParser=0x, int 
yyruleno=0x)  line 156 + 0x18 bytes C
*  forza2_ply.dll!sqlite3Parser(void * yyp=0x, int 
yymajor=0x, Token yyminor={...}, Parse * pParse=0x)  line 3234 
+ 0xC bytesC
*  forza2_ply.dll!sqlite3RunParser(Parse * pParse=0x, const 
char * zSql=0x, char * * pzErrMsg=0x)  line 444   C
*  forza2_ply.dll!sqlite3_prepare(sqlite3 * db=0x, const char * 
zSql=0x, int nBytes=0x, sqlite3_stmt * * ppStmt=0x, 
const char * * pzTail=0x)  line 492 C

It is crashing (Access violation reading invalid pointer) on the line:
setToken(&pRight->token, zName);

Is there a known bug?  Could we be setting up something wrong?  Anyone have any 
advice on how to debug this?  I'd hate to ship our game with this but we're 
getting down to the wire and it looks like we may need to do just that.

Thanks,
Dave

Re: [sqlite] Optimize my database

[Jonas Sandman]

On 3/29/07, Rich Shepard <[EMAIL PROTECTED]> wrote:


On Thu, 29 Mar 2007, Jonas Sandman wrote:

> I guess I could split it into more tables, but would that really make
> anything faster?

   It would make upgrades, changes, and searching faster.

> The whole key is the filename, I query for files in specific filepaths,
so
> it doesn't matter if something is mis-spelled. Rather I encourage the
> possibility that it can be.

   So you never query by artist, title, genre, or any other field in the
table?



Yeah I do,  but  the path is always the most important part and the only
thing that is ALWAYS queried.


So what should I use as a primary key then? The path?

   That's what you wrote that you use. I would make the title the primary
key, unless you have the same title by different artists. In that case,
I'd
use a numeric key.



No, I use a full path (path+filename). Title doesn't even exists for all
(and for none before the metadata has been scanned). What do I gain by using
a numeric key instead of the path?


Since one file is one row in the database I rather liked the idea that I
> didn't have to scatter around updating a lot of tables. I am using the
> database instead of a large memory table like I used before.

   Then you might as well use a spreadsheet. With a relational database,
multiple tables are related by foreign keys (which sqlite3 implements as
triggers). If you have an interface other than the sqlite3 command line
editor then you enter information in the appropriate data entry widgets
and
use your middleware code to store/retrieve from appropriate tables.



I just used a binary file which I read up into a memory table before but
figured it was better to use a database to gain some query functionality for
free. I just didn't see the need for it in this case to split it into
several tables (though I know that's how it supposed to be done usually),
unless there is a gain of splitting the data for performance. I always query
all the data, and I always run LIKE on title, artist, album etc. to
determine if a keyword match what's in there.

--

Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax:
503-667-8863


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Optimize my database

[Rich Shepard]

On Thu, 29 Mar 2007, Jonas Sandman wrote:


I guess I could split it into more tables, but would that really make
anything faster?


  It would make upgrades, changes, and searching faster.


The whole key is the filename, I query for files in specific filepaths, so
it doesn't matter if something is mis-spelled. Rather I encourage the
possibility that it can be.


  So you never query by artist, title, genre, or any other field in the
table?


So what should I use as a primary key then? The path?


  That's what you wrote that you use. I would make the title the primary
key, unless you have the same title by different artists. In that case, I'd
use a numeric key.


Since one file is one row in the database I rather liked the idea that I
didn't have to scatter around updating a lot of tables. I am using the
database instead of a large memory table like I used before.


  Then you might as well use a spreadsheet. With a relational database,
multiple tables are related by foreign keys (which sqlite3 implements as
triggers). If you have an interface other than the sqlite3 command line
editor then you enter information in the appropriate data entry widgets and
use your middleware code to store/retrieve from appropriate tables.


--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Java wrapper for both windows and linux

[Steven E. Harris]

Xavier RAYNAUD <[EMAIL PROTECTED]> writes:

> I use native JNI libraries, and choose at runtime the native library
> to open.  (on eclipse, with OS-dependant fragments, it's easy to do)

Can you elaborate on how you make this selection, and how you package
the fragment?

-- 
Steven E. Harris

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Python-Sqlite Unicode characters

[Dennis Cote]

Igor Tandetnik wrote:

Kostas5904  wrote:

But...

When I ask a value from the user and I try to store it into the same
table,
I run the following code
 
 item=dialog.GetValue()
 table="names1"
cols="id,descr,type"
values=("null",item,3)
 a="insert into %s (%s) values %s" % (table,cols,values)
 cursor.execute(a)

and I get the error:

pysqlite2.dbapi2.OperationalError: near
"'\u03b1\u03bd\u03c4\u03ce\u03bd\u03b7\u03c2'": syntax error


Inspect the value of 'a' variable right before execute() call. You'll 
find it's all wrong. The values must be in parentheses, string 
literals must be quoted. While we are at it, in SQL string literals 
should be quoted with single quotes. SQLite allows double quotes as an 
extension, but it is bad practice to rely on that.

Actually it's not as bad as it looks at first. :-)

He is using Python to convert the tuple "values" into its string 
representation. This adds the required parentheses and quotes the value 
of the string member item appropriately. The null value will be quoted 
as a string literal as well, instead of a bare keyword null, which is a 
problem. The real problem is that the string representation of a unicode 
string in Python is not recognized by SQLite. It simply doesn't 
understand the \u escape sequences.


Because of this I don't think this technique can be used to build a 
suitable statement this way. You could try


v="(%s, '%s', %s)" % values
a="insert into %s (%s) values %s" % (table,cols,v)
cursor.execute(a)

which will embed the unicode string in another string, v, which avoid 
the builtin conversion from a tuple to a string.


Better still, use parameterized query and bind your values to 
parameters. Your approach is wide open to SQL injection attack. 
Consider what happens if I type the following into the dialog field:


sometext", 3); delete from names1; --

See what kind of statement you are going to construct and run given 
this input.


Even if I'm not being malicious, consider what happens if I innocently 
put text containing quotes into the description field.



This really is a better idea, but I'm not sure how you do it from the 
Python wrapper.


HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Optimize my database

[Jonas Sandman]

I should add that I am scanning a folder-tree for the files and are storing
the metadata in the database like that so I don't have to read the actual
files. That's why I used the path as primary key to begin with. No duplicate
entries.

On 3/29/07, Jonas Sandman <[EMAIL PROTECTED]> wrote:


On 3/29/07, Rich Shepard <[EMAIL PROTECTED]> wrote:
>
> On Thu, 29 Mar 2007, Jonas Sandman wrote:
>
> > I am quite new at database and how to set them up properly so keep in
> mind
> > that the obvious might stare into your face but just point it out to
> me
>
> Jonas,
>
>I strongly recommend that you read a book on database design. You
> have a
> single, flat-file database that should be a multiple table, relational
> database (e.g., multiple titles for the same artist: what if you
> mis-spell
> an artist's name? Or that name changes?)


I guess I could split it into more tables, but would that really make
anything faster?
The whole key is the filename, I query for files in specific filepaths, so
it doesn't matter if something is mis-spelled. Rather I encourage the
possibility that it can be.

   Having the path as the primary key means that each database record is
> in a
> different subdirectory, and that they're all unique. What value does the
> path provide?


So what should I use as a primary key then? The path?

   How do you handle having a title (or artist) classified in more than a
> single genre? What if one of those changes, can you be sure you've made
> all
> the changes scattered throughout the database?


Since one file is one row in the database I rather liked the idea that I
didn't have to scatter around updating a lot of tables. I am using the
database instead of a large memory table like I used before.

> CREATE TABLE Files (path VARCHAR(255) PRIMARY KEY, title VARCHAR(255),
> > artist VARCHAR(255), album VARCHAR(255), genre VARCHAR(255), comment
> > VARCHAR(255), track INTEGER, year INTEGER, length INTEGER, bitrate
> INTEGER,
> > playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER,
> extension
> > VARCHAR(5), file_exists INTEGER)";
>
>Stop what you're doing and learn how to do it properly and more
> efficiently.
>
> Rich

Re: [sqlite] Optimize my database

[Jonas Sandman]

On 3/29/07, Rich Shepard <[EMAIL PROTECTED]> wrote:


On Thu, 29 Mar 2007, Jonas Sandman wrote:

> I am quite new at database and how to set them up properly so keep in
mind
> that the obvious might stare into your face but just point it out to me

Jonas,

   I strongly recommend that you read a book on database design. You have
a
single, flat-file database that should be a multiple table, relational
database (e.g., multiple titles for the same artist: what if you mis-spell
an artist's name? Or that name changes?)



I guess I could split it into more tables, but would that really make
anything faster?
The whole key is the filename, I query for files in specific filepaths, so
it doesn't matter if something is mis-spelled. Rather I encourage the
possibility that it can be.

  Having the path as the primary key means that each database record is in

a
different subdirectory, and that they're all unique. What value does the
path provide?



So what should I use as a primary key then? The path?

  How do you handle having a title (or artist) classified in more than a

single genre? What if one of those changes, can you be sure you've made
all
the changes scattered throughout the database?



Since one file is one row in the database I rather liked the idea that I
didn't have to scatter around updating a lot of tables. I am using the
database instead of a large memory table like I used before.


CREATE TABLE Files (path VARCHAR(255) PRIMARY KEY, title VARCHAR(255),
> artist VARCHAR(255), album VARCHAR(255), genre VARCHAR(255), comment
> VARCHAR(255), track INTEGER, year INTEGER, length INTEGER, bitrate
INTEGER,
> playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER,
extension
> VARCHAR(5), file_exists INTEGER)";

   Stop what you're doing and learn how to do it properly and more
efficiently.

Rich

Re: [sqlite] Optimize my database

[Rich Shepard]

On Thu, 29 Mar 2007, Jonas Sandman wrote:


I am quite new at database and how to set them up properly so keep in mind
that the obvious might stare into your face but just point it out to me


Jonas,

  I strongly recommend that you read a book on database design. You have a
single, flat-file database that should be a multiple table, relational
database (e.g., multiple titles for the same artist: what if you mis-spell
an artist's name? Or that name changes?)

  Having the path as the primary key means that each database record is in a
different subdirectory, and that they're all unique. What value does the
path provide?

  How do you handle having a title (or artist) classified in more than a
single genre? What if one of those changes, can you be sure you've made all
the changes scattered throughout the database?


CREATE TABLE Files (path VARCHAR(255) PRIMARY KEY, title VARCHAR(255),
artist VARCHAR(255), album VARCHAR(255), genre VARCHAR(255), comment
VARCHAR(255), track INTEGER, year INTEGER, length INTEGER, bitrate INTEGER,
playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER, extension
VARCHAR(5), file_exists INTEGER)";


  Stop what you're doing and learn how to do it properly and more
efficiently.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Python-Sqlite Unicode characters

[Igor Tandetnik]

Kostas5904  wrote:

But...

When I ask a value from the user and I try to store it into the same
table,
I run the following code
 
 item=dialog.GetValue()
 table="names1"
cols="id,descr,type"
values=("null",item,3)
 a="insert into %s (%s) values %s" % (table,cols,values)
 cursor.execute(a)

and I get the error:

pysqlite2.dbapi2.OperationalError: near
"'\u03b1\u03bd\u03c4\u03ce\u03bd\u03b7\u03c2'": syntax error


Inspect the value of 'a' variable right before execute() call. You'll 
find it's all wrong. The values must be in parentheses, string literals 
must be quoted. While we are at it, in SQL string literals should be 
quoted with single quotes. SQLite allows double quotes as an extension, 
but it is bad practice to rely on that.


Better still, use parameterized query and bind your values to 
parameters. Your approach is wide open to SQL injection attack. Consider 
what happens if I type the following into the dialog field:


sometext", 3); delete from names1; --

See what kind of statement you are going to construct and run given this 
input.


Even if I'm not being malicious, consider what happens if I innocently 
put text containing quotes into the description field.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Optimize my database

[Jonas Sandman]

Hello again,

I am quite new at database and how to set them up properly so keep in mind
that the obvious might stare into your face but just point it out to me :-)

I am having this database with mediafiles (even anyone recall my previous
mails) and I create it like this:

CREATE TABLE Files (path VARCHAR(255) PRIMARY KEY, title VARCHAR(255),
artist VARCHAR(255), album VARCHAR(255), genre VARCHAR(255), comment
VARCHAR(255), track INTEGER, year INTEGER, length INTEGER, bitrate INTEGER,
playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER, extension
VARCHAR(5), file_exists INTEGER)";

and I add some indexation like this:
setup = "CREATE INDEX title Files (title, artist, album, genre, comment,
extension)";

I insert data into the database and I can query it kinda fast, but when
there is lots of files (> 15000) the database seems sluggish and doesn't
return the results at all as fast as I would expect (or like!).

Is there something I can do with this that can make it faster?

Best regards,
Jonas

Re: [sqlite] Hashing question

[Eduardo Morras]

At 16:20 29/03/2007, you wrote:
>Eduardo Morras <[EMAIL PROTECTED]> wrote:
>> Hello:
>> 
>>   I'm trying to use altivec simd code to SQLite. Has read hash.c
>> but it looks like hashing is done one by one when insert data.
>> Is it true? When is called rehash function? If i insert data with
>> index i think hashing happens one by one, but if i insert all 
>> data and after that make an index rehash works. Can anyone confirm
>> this? Rehash function is the only one i can convert to altivec 
>> (with it's own hashing functions).
>> 
>
>The hash.c code is only used for some internal housekeeping
>details within SQLite.  (Symbol tables and that kind of thing.)
>It does not come into play when actually executing SQL statements.
>SQL data is stored using B-Trees only.  No hashing.
>
>--
>D. Richard Hipp  <[EMAIL PROTECTED]>

Thanks D.Richard, i'm trying to speed up the "create index" code and thought 
that it creates a hash from data and then insert it (the hash/index) in the 
btree. AFAIK Tree functions (insert, delete, move) cannot be convert to simd, 
but other code yes. For example in

CREATE TABLE tba1 (one varchar(15), two int, three real)
CREATE INDEX ind1 ON tba1(one, three)

an index ind1 is created. That's where i think a hash is done, 
ind1=function(one,three) and it can be simd by working with 4(one,three) pairs 
at same time.

Any how, thanks again. 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] RE: Maintaining Master-Child relationships

[Arora, Ajay]

Thanks Kishore/Stephen for your suggestions.

Regards
Ajay

-Original Message-
From: stephan nies [mailto:[EMAIL PROTECTED] 
Sent: 29 March 2007 15:54
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RE: Maintaining Master-Child relationships


CREATE TRIGGER sync_child_with_parent_update
  BEFORE UPDATE ON parent
FOR EACH ROW BEGIN
  UPDATE child
 SET
  field_name_a = NEW.field_name_a,
 field_name_b,
  field_name_c)
  VALUES
 (NEW.field_name_a,
  NEW.field_name_b,
  NEW.field_name_c);
END;

On 3/29/07, stephan nies <[EMAIL PROTECTED]> wrote:
>
> I think using triggers would be a good solution:
> You good just trigger on inserts and updates to parent
> and do whatever  you  like to child.
>
> Example:
>
> CREATE TRIGGER sync_child_with_parent_insert
>   BEFORE INSERT ON parent
> FOR EACH ROW BEGIN
>   INSERT INTO child
>  (field_name_a,
>   field_name_b,
>   field_name_c)
>   VALUES
>  (NEW.field_name_a,
>   NEW.field_name_b,
>   NEW.field_name_c);
> END;
>
> CREATE TRIGGER sync_child_with_parent_update
>   BEFORE UPDATE ON parent
> FOR EACH ROW BEGIN
>   INSERT INTO child
>  (field_name_a,
>   field_name_b,
>   field_name_c)
>   VALUES
>  (NEW.field_name_a,
>   NEW.field_name_b,
>   NEW.field_name_c);
> END;
>
> On 3/29/07, P Kishor <[EMAIL PROTECTED]> wrote:
> >
> > On 3/29/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> > > Thanks for your reply.
> > >
> > > I'm writing my application in Perl using SQLite as file based
> > database.
> > > Multiple users would be accessing the database simultaneously.But
I'll
> > > lock the file prior to any updations to the database. I'm planning
to
> > > use a third table to generate ids for the master table. But I
don't
> > know
> > > how to select id INTO a variable which can be used while inserting
> > data
> > > into both the tables. Is it possible?
> >
> >
> > Maybe you are not explaining your problem completely, else, I don't
> > see what the problem is. Here is some Perl code to do the above
> > without requiring a third table. You can adjust it accordingly if
you
> > insist on a third table.
> >
> > # get your values to insert somehow
> > my $field1 = "my field one";
> > my $name   = "my name";
> >
> > # enable transactions, if possible
> > $dbh->{AutoCommit => 0, RaiseError => 1};
> >
> > eval {
> >   my $sth_master = $dbh->prepare(qq{
> > INSERT INTO Master ( field1 ) VALUES ( ? )
> >   });
> >   $sth_master->execute( $field1 );
> >
> >   my $master_id = $dbh->func('last_insert_rowid');
> >
> >   my $sth_child = $dbh->prepare(qq{
> > INSERT INTO Child ( id, name ) VALUES ( ?, ? )
> >   });
> >   $sth_master->execute( $master_id, $name );
> >
> >   # commit the changes if we get this far
> >   $dbh->commit;
> > };
> >
> > if ($@) {
> > warn "Transaction aborted because $@";
> >
> > # now rollback to undo the incomplete changes
> > # but do it in an eval{} as it may also fail
> > eval { $dbh->rollback };
> >
> > # add other application on-error-clean-up code here
> > }
> >
> > >
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of P
> > > Kishor
> > > Sent: 28 March 2007 17:42
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] RE: Maintaining Master-Child relationships
> > >
> > >
> > > On 3/28/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> > > > Can anyone please look into my query,
> > > >
> > > > I've tables
> > > >
> > > > Master ( id integer primary key,
> > > >  field1 text)
> > > >
> > > > And
> > > >
> > > > Child (id integer, name text)
> > > >
> > > > My application receive values for field1 and name.  I need to
> > populate
> > > > master and child with incoming values using the same id.
> > > >
> > >
> > > 1. Insert "field1" in Master;
> > > 2. Get id of record inserted from #1 above;
> > > 3. Insert id from #2 and "name" in Child.
> > >
> > > That is about all the help I can provide given that you have not
> > > provide any further context... what language you are using, how
you
> > > are developing the application, etc.
> > >
> > > SQLite does provide a 'last_insert_rowid' capability. Look on the
> > > sqlite.org website.
> > >
> > > if you are 100% sure that no one else will be fiddling with your
> > > database, you can probably use something like
> > >
> > > INSERT INTO Child( id, name)
> > > VALUES ( (SELECT Max(id) FROM Master), )
> > >
> > >
> > > > A quick reply will be highly appreciated.
> > > >
> > > > Regards
> > > > Ajay
> > > >
> > > > -Original Message-
> > > > From: Arora, Ajay
> > > > Sent: 28 March 2007 15:04
> > > > To: ' sqlite-users@sqlite.org'
> > > > Subject: Maintaining Master-Child relationships
> > > >
> > > >
> > > > Hi,
> > > >
> > > > I've two tables in my database, a master and a child with ID  as
a
> > > > common key.
> > > >
> > > > I'

Re: [sqlite] RE: Maintaining Master-Child relationships

[stephan nies]

sorry hit send by accident this should be:

CREATE TRIGGER sync_child_with_parent_update
 BEFORE UPDATE ON parent
   FOR EACH ROW BEGIN
 UPDATE child
SET
 field_name_a = NEW.field_name_a,
 field_name_b = NEW.field_name_b,
 field_name_c = NEW.field_name_c,
   WHERE
 field_name_a = NEW.field_name_a
END;

// assuming  field_name_a is the  id field

On 3/29/07, stephan nies <[EMAIL PROTECTED]> wrote:


CREATE TRIGGER sync_child_with_parent_update
  BEFORE UPDATE ON parent
FOR EACH ROW BEGIN
  UPDATE child
 SET
  field_name_a = NEW.field_name_a,
 field_name_b,
  field_name_c)
  VALUES
 (NEW.field_name_a,
  NEW.field_name_b,
  NEW.field_name_c);
END;

On 3/29/07, stephan nies <[EMAIL PROTECTED]> wrote:
>
> I think using triggers would be a good solution:
> You good just trigger on inserts and updates to parent
> and do whatever  you  like to child.
>
> Example:
>
> CREATE TRIGGER sync_child_with_parent_insert
>   BEFORE INSERT ON parent
> FOR EACH ROW BEGIN
>   INSERT INTO child
>  (field_name_a,
>   field_name_b,
>   field_name_c)
>   VALUES
>  (NEW.field_name_a,
>   NEW.field_name_b,
>   NEW.field_name_c);
> END;
>
> CREATE TRIGGER sync_child_with_parent_update
>   BEFORE UPDATE ON parent
> FOR EACH ROW BEGIN
>   INSERT INTO child
>  (field_name_a,
>   field_name_b,
>   field_name_c)
>   VALUES
>  (NEW.field_name_a,
>   NEW.field_name_b,
>   NEW.field_name_c);
> END;
>
> On 3/29/07, P Kishor < [EMAIL PROTECTED]> wrote:
> >
> > On 3/29/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> > > Thanks for your reply.
> > >
> > > I'm writing my application in Perl using SQLite as file based
> > database.
> > > Multiple users would be accessing the database simultaneously.ButI'll
> > > lock the file prior to any updations to the database. I'm planning
> > to
> > > use a third table to generate ids for the master table. But I don't
> > know
> > > how to select id INTO a variable which can be used while inserting
> > data
> > > into both the tables. Is it possible?
> >
> >
> > Maybe you are not explaining your problem completely, else, I don't
> > see what the problem is. Here is some Perl code to do the above
> > without requiring a third table. You can adjust it accordingly if you
> > insist on a third table.
> >
> > # get your values to insert somehow
> > my $field1 = "my field one";
> > my $name   = "my name";
> >
> > # enable transactions, if possible
> > $dbh->{AutoCommit => 0, RaiseError => 1};
> >
> > eval {
> >   my $sth_master = $dbh->prepare(qq{
> > INSERT INTO Master ( field1 ) VALUES ( ? )
> >   });
> >   $sth_master->execute( $field1 );
> >
> >   my $master_id = $dbh->func('last_insert_rowid');
> >
> >   my $sth_child = $dbh->prepare(qq{
> > INSERT INTO Child ( id, name ) VALUES ( ?, ? )
> >   });
> >   $sth_master->execute( $master_id, $name );
> >
> >   # commit the changes if we get this far
> >   $dbh->commit;
> > };
> >
> > if ($@) {
> > warn "Transaction aborted because $@";
> >
> > # now rollback to undo the incomplete changes
> > # but do it in an eval{} as it may also fail
> > eval { $dbh->rollback };
> >
> > # add other application on-error-clean-up code here
> > }
> >
> > >
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
> > P
> > > Kishor
> > > Sent: 28 March 2007 17:42
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] RE: Maintaining Master-Child relationships
> > >
> > >
> > > On 3/28/07, Arora, Ajay < [EMAIL PROTECTED]> wrote:
> > > > Can anyone please look into my query,
> > > >
> > > > I've tables
> > > >
> > > > Master ( id integer primary key,
> > > >  field1 text)
> > > >
> > > > And
> > > >
> > > > Child (id integer, name text)
> > > >
> > > > My application receive values for field1 and name.  I need to
> > populate
> > > > master and child with incoming values using the same id.
> > > >
> > >
> > > 1. Insert "field1" in Master;
> > > 2. Get id of record inserted from #1 above;
> > > 3. Insert id from #2 and "name" in Child.
> > >
> > > That is about all the help I can provide given that you have not
> > > provide any further context... what language you are using, how you
> > > are developing the application, etc.
> > >
> > > SQLite does provide a 'last_insert_rowid' capability. Look on the
> > > sqlite.org website.
> > >
> > > if you are 100% sure that no one else will be fiddling with your
> > > database, you can probably use something like
> > >
> > > INSERT INTO Child( id, name)
> > > VALUES ( (SELECT Max(id) FROM Master), )
> > >
> > >
> > > > A quick reply will be highly appreciated.
> > > >
> > > > Regards
> > > > Ajay
> > > >
> > > > -Original Message-
> > > > From: Arora, Ajay
> > > > Sent: 28 March 2007 15:04
> > > > To: ' sqlite-users@sqlite.org'

[sqlite] Python-Sqlite Unicode characters

[Kostas5904]

Hi, everybody. I am new in Python and Sqlite and I have a problem when I'm
using unicode strings. Actually I'm working with WxPython and pysqlite (to
connect to sqlite).

The problem is as follows:

When I execute this: 

cursor.execute('INSERT INTO names1 (id,decr,type)
VALUES (null,"Βασίλης",7)') 
   
everything works fine. 

The values are stored in the table and when I retrieve them ("select * from
names1..."), everything is OK. (If you cannot read the literal value: is in
Greek). The values are shown in a wxpython list control perfect.

But...

When I ask a value from the user and I try to store it into the same table,
I run the following code
 
 item=dialog.GetValue()
 table="names1"
 cols="id,descr,type"
 values=("null",item,3)
 a="insert into %s (%s) values %s" % (table,cols,values)
 cursor.execute(a)

and I get the error:

pysqlite2.dbapi2.OperationalError: near
"'\u03b1\u03bd\u03c4\u03ce\u03bd\u03b7\u03c2'": syntax error

I do understand that this one happens because item=
u'\u03b1\u03bd\u03c4\u03ce\u03bd\u03b7\u03c2'. 

But how can I go trhought this???

I tried to use:  values=("null",item.encode('utf-8'),3). The result is that
I store in the database the string without "u" in front of the string
('\u03b1\u03bd\u03c4\u03ce\u03bd\u03b7\u03c2') but...

When I execute "Select * from names1..." then I get this as the string I
stored:

  '\u03b1\u03bd\u03c4\u03ce\u03bd\u03b7\u03c2' 

and not the equivalent unicode string.

Can anybody help?








-- 
View this message in context: 
http://www.nabble.com/Python-Sqlite-Unicode-characters-tf3486826.html#a9735074
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] RE: Maintaining Master-Child relationships

[stephan nies]

CREATE TRIGGER sync_child_with_parent_update
 BEFORE UPDATE ON parent
   FOR EACH ROW BEGIN
 UPDATE child
SET
 field_name_a = NEW.field_name_a,
field_name_b,
 field_name_c)
 VALUES
(NEW.field_name_a,
 NEW.field_name_b,
 NEW.field_name_c);
END;

On 3/29/07, stephan nies <[EMAIL PROTECTED]> wrote:


I think using triggers would be a good solution:
You good just trigger on inserts and updates to parent
and do whatever  you  like to child.

Example:

CREATE TRIGGER sync_child_with_parent_insert
  BEFORE INSERT ON parent
FOR EACH ROW BEGIN
  INSERT INTO child
 (field_name_a,
  field_name_b,
  field_name_c)
  VALUES
 (NEW.field_name_a,
  NEW.field_name_b,
  NEW.field_name_c);
END;

CREATE TRIGGER sync_child_with_parent_update
  BEFORE UPDATE ON parent
FOR EACH ROW BEGIN
  INSERT INTO child
 (field_name_a,
  field_name_b,
  field_name_c)
  VALUES
 (NEW.field_name_a,
  NEW.field_name_b,
  NEW.field_name_c);
END;

On 3/29/07, P Kishor <[EMAIL PROTECTED]> wrote:
>
> On 3/29/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> > Thanks for your reply.
> >
> > I'm writing my application in Perl using SQLite as file based
> database.
> > Multiple users would be accessing the database simultaneously.But I'll
> > lock the file prior to any updations to the database. I'm planning to
> > use a third table to generate ids for the master table. But I don't
> know
> > how to select id INTO a variable which can be used while inserting
> data
> > into both the tables. Is it possible?
>
>
> Maybe you are not explaining your problem completely, else, I don't
> see what the problem is. Here is some Perl code to do the above
> without requiring a third table. You can adjust it accordingly if you
> insist on a third table.
>
> # get your values to insert somehow
> my $field1 = "my field one";
> my $name   = "my name";
>
> # enable transactions, if possible
> $dbh->{AutoCommit => 0, RaiseError => 1};
>
> eval {
>   my $sth_master = $dbh->prepare(qq{
> INSERT INTO Master ( field1 ) VALUES ( ? )
>   });
>   $sth_master->execute( $field1 );
>
>   my $master_id = $dbh->func('last_insert_rowid');
>
>   my $sth_child = $dbh->prepare(qq{
> INSERT INTO Child ( id, name ) VALUES ( ?, ? )
>   });
>   $sth_master->execute( $master_id, $name );
>
>   # commit the changes if we get this far
>   $dbh->commit;
> };
>
> if ($@) {
> warn "Transaction aborted because $@";
>
> # now rollback to undo the incomplete changes
> # but do it in an eval{} as it may also fail
> eval { $dbh->rollback };
>
> # add other application on-error-clean-up code here
> }
>
> >
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> > Kishor
> > Sent: 28 March 2007 17:42
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] RE: Maintaining Master-Child relationships
> >
> >
> > On 3/28/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> > > Can anyone please look into my query,
> > >
> > > I've tables
> > >
> > > Master ( id integer primary key,
> > >  field1 text)
> > >
> > > And
> > >
> > > Child (id integer, name text)
> > >
> > > My application receive values for field1 and name.  I need to
> populate
> > > master and child with incoming values using the same id.
> > >
> >
> > 1. Insert "field1" in Master;
> > 2. Get id of record inserted from #1 above;
> > 3. Insert id from #2 and "name" in Child.
> >
> > That is about all the help I can provide given that you have not
> > provide any further context... what language you are using, how you
> > are developing the application, etc.
> >
> > SQLite does provide a 'last_insert_rowid' capability. Look on the
> > sqlite.org website.
> >
> > if you are 100% sure that no one else will be fiddling with your
> > database, you can probably use something like
> >
> > INSERT INTO Child( id, name)
> > VALUES ( (SELECT Max(id) FROM Master), )
> >
> >
> > > A quick reply will be highly appreciated.
> > >
> > > Regards
> > > Ajay
> > >
> > > -Original Message-
> > > From: Arora, Ajay
> > > Sent: 28 March 2007 15:04
> > > To: ' sqlite-users@sqlite.org'
> > > Subject: Maintaining Master-Child relationships
> > >
> > >
> > > Hi,
> > >
> > > I've two tables in my database, a master and a child with ID  as a
> > > common key.
> > >
> > > I've created a table with one column to generate the sequence
> > number.How
> > > can I insert related records into both the tables using same ID?
> > >
> > > Thanks
> > > Ajay
> > >
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
> -
> collaborate, communicate, compete
> =
>
>

Re: [sqlite] RE: Maintaining Master-Child relationships

[stephan nies]

I think using triggers would be a good solution:
You good just trigger on inserts and updates to parent
and do whatever  you  like to child.

Example:

CREATE TRIGGER sync_child_with_parent_insert
 BEFORE INSERT ON parent
   FOR EACH ROW BEGIN
 INSERT INTO child
(field_name_a,
 field_name_b,
 field_name_c)
 VALUES
(NEW.field_name_a,
 NEW.field_name_b,
 NEW.field_name_c);
END;

CREATE TRIGGER sync_child_with_parent_update
 BEFORE UPDATE ON parent
   FOR EACH ROW BEGIN
 INSERT INTO child
(field_name_a,
 field_name_b,
 field_name_c)
 VALUES
(NEW.field_name_a,
 NEW.field_name_b,
 NEW.field_name_c);
END;

On 3/29/07, P Kishor <[EMAIL PROTECTED]> wrote:


On 3/29/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> Thanks for your reply.
>
> I'm writing my application in Perl using SQLite as file based database.
> Multiple users would be accessing the database simultaneously.But I'll
> lock the file prior to any updations to the database. I'm planning to
> use a third table to generate ids for the master table. But I don't know
> how to select id INTO a variable which can be used while inserting data
> into both the tables. Is it possible?


Maybe you are not explaining your problem completely, else, I don't
see what the problem is. Here is some Perl code to do the above
without requiring a third table. You can adjust it accordingly if you
insist on a third table.

# get your values to insert somehow
my $field1 = "my field one";
my $name   = "my name";

# enable transactions, if possible
$dbh->{AutoCommit => 0, RaiseError => 1};

eval {
  my $sth_master = $dbh->prepare(qq{
INSERT INTO Master ( field1 ) VALUES ( ? )
  });
  $sth_master->execute( $field1 );

  my $master_id = $dbh->func('last_insert_rowid');

  my $sth_child = $dbh->prepare(qq{
INSERT INTO Child ( id, name ) VALUES ( ?, ? )
  });
  $sth_master->execute( $master_id, $name );

  # commit the changes if we get this far
  $dbh->commit;
};

if ($@) {
warn "Transaction aborted because $@";

# now rollback to undo the incomplete changes
# but do it in an eval{} as it may also fail
eval { $dbh->rollback };

# add other application on-error-clean-up code here
}

>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> Kishor
> Sent: 28 March 2007 17:42
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] RE: Maintaining Master-Child relationships
>
>
> On 3/28/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> > Can anyone please look into my query,
> >
> > I've tables
> >
> > Master ( id integer primary key,
> >  field1 text)
> >
> > And
> >
> > Child (id integer, name text)
> >
> > My application receive values for field1 and name.  I need to populate
> > master and child with incoming values using the same id.
> >
>
> 1. Insert "field1" in Master;
> 2. Get id of record inserted from #1 above;
> 3. Insert id from #2 and "name" in Child.
>
> That is about all the help I can provide given that you have not
> provide any further context... what language you are using, how you
> are developing the application, etc.
>
> SQLite does provide a 'last_insert_rowid' capability. Look on the
> sqlite.org website.
>
> if you are 100% sure that no one else will be fiddling with your
> database, you can probably use something like
>
> INSERT INTO Child( id, name)
> VALUES ( (SELECT Max(id) FROM Master), )
>
>
> > A quick reply will be highly appreciated.
> >
> > Regards
> > Ajay
> >
> > -Original Message-
> > From: Arora, Ajay
> > Sent: 28 March 2007 15:04
> > To: 'sqlite-users@sqlite.org'
> > Subject: Maintaining Master-Child relationships
> >
> >
> > Hi,
> >
> > I've two tables in my database, a master and a child with ID  as a
> > common key.
> >
> > I've created a table with one column to generate the sequence
> number.How
> > can I insert related records into both the tables using same ID?
> >
> > Thanks
> > Ajay
> >


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Hashing question

[drh]

Eduardo Morras <[EMAIL PROTECTED]> wrote:
> Hello:
> 
>   I'm trying to use altivec simd code to SQLite. Has read hash.c
> but it looks like hashing is done one by one when insert data.
> Is it true? When is called rehash function? If i insert data with
> index i think hashing happens one by one, but if i insert all 
> data and after that make an index rehash works. Can anyone confirm
> this? Rehash function is the only one i can convert to altivec 
> (with it's own hashing functions).
> 

The hash.c code is only used for some internal housekeeping
details within SQLite.  (Symbol tables and that kind of thing.)
It does not come into play when actually executing SQL statements.
SQL data is stored using B-Trees only.  No hashing.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Hashing question

[Eduardo Morras]

At 11:27 29/03/2007, you wrote:
>Hello:
>
>  I'm trying to use altivec simd code to SQLite. Has read hash.c but it looks 
> like hashing is done one by one when insert data. Is it true? When is called 
> rehash function? If i insert data with index i think hashing happens one by 
> one, but if i insert all data and after that make an index rehash works. Can 
> anyone confirm this? Rehash function is the only one i can convert to altivec 
> (with it's own hashing functions).
>
>Thanks 

Ok, i was wrong, it can be done but needs a rework from other files too. The 
"void *sqlite3HashInsert(Hash *pH, const void *pKey, int nKey, void *data)" 
function must be changed for get 4 pKey,nKey pairs and an int to say if they 
are 4, 3, 2 or 1 keys or a special pKey,nKey pair with "no-op" meaning.

The best way i think is to make a *sqlite3HashInsertSimd or similar that checks 
which simd (altivec, mmx, sse or similar) works at startup or compile time and 
the function caller/user of sqlite3HashInsert decides based on the amount of 
data to hash if uses the simd or the normal function. I expect a x4 to x7 speed 
increment using altivec. Don't know for other simd architectures. 





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Store and retreive 0D0A (CRLF) in string field

[Martin Jenkins]

fangles wrote:

When I have text pasted into an sqlite string field, it is stored okay but
when I retrieve a string, it is truncated at the first CR (0D).


Hi fangles,

Did you get this sorted?

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using the Sqlite Btree Backend directly

[P Kishor]

On 3/29/07, Ludvig Strigeus <[EMAIL PROTECTED]> wrote:

Hi,

I'm looking at using Sqlite as a storage backend for a program. Using SQL is
a little bit overkill and much more than we need. How complicated would it
be to interface to the btree subsystem directly? Sqlite seems very modular
from the looks of it, but has anyone attempted anything like this before?

The functionality I need is key->value maps with support for lookup by an
exact key, insertion, iteration of all keys in the database, removal.
Transactions would also preferably be needed.




why bother with SQLite then? Use the right tool for the job -- use BerkeleyDB.


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] RE: Maintaining Master-Child relationships

[P Kishor]

On 3/29/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:

Thanks for your reply.

I'm writing my application in Perl using SQLite as file based database.
Multiple users would be accessing the database simultaneously.But I'll
lock the file prior to any updations to the database. I'm planning to
use a third table to generate ids for the master table. But I don't know
how to select id INTO a variable which can be used while inserting data
into both the tables. Is it possible?



Maybe you are not explaining your problem completely, else, I don't
see what the problem is. Here is some Perl code to do the above
without requiring a third table. You can adjust it accordingly if you
insist on a third table.

# get your values to insert somehow
my $field1 = "my field one";
my $name   = "my name";

# enable transactions, if possible
$dbh->{AutoCommit => 0, RaiseError => 1};

eval {
 my $sth_master = $dbh->prepare(qq{
   INSERT INTO Master ( field1 ) VALUES ( ? )
 });
 $sth_master->execute( $field1 );

 my $master_id = $dbh->func('last_insert_rowid');

 my $sth_child = $dbh->prepare(qq{
   INSERT INTO Child ( id, name ) VALUES ( ?, ? )
 });
 $sth_master->execute( $master_id, $name );

 # commit the changes if we get this far
 $dbh->commit;
};

if ($@) {
   warn "Transaction aborted because $@";

   # now rollback to undo the incomplete changes
   # but do it in an eval{} as it may also fail
   eval { $dbh->rollback };

   # add other application on-error-clean-up code here
}



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: 28 March 2007 17:42
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RE: Maintaining Master-Child relationships


On 3/28/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> Can anyone please look into my query,
>
> I've tables
>
> Master ( id integer primary key,
>  field1 text)
>
> And
>
> Child (id integer, name text)
>
> My application receive values for field1 and name.  I need to populate
> master and child with incoming values using the same id.
>

1. Insert "field1" in Master;
2. Get id of record inserted from #1 above;
3. Insert id from #2 and "name" in Child.

That is about all the help I can provide given that you have not
provide any further context... what language you are using, how you
are developing the application, etc.

SQLite does provide a 'last_insert_rowid' capability. Look on the
sqlite.org website.

if you are 100% sure that no one else will be fiddling with your
database, you can probably use something like

INSERT INTO Child( id, name)
VALUES ( (SELECT Max(id) FROM Master), )


> A quick reply will be highly appreciated.
>
> Regards
> Ajay
>
> -Original Message-
> From: Arora, Ajay
> Sent: 28 March 2007 15:04
> To: 'sqlite-users@sqlite.org'
> Subject: Maintaining Master-Child relationships
>
>
> Hi,
>
> I've two tables in my database, a master and a child with ID  as a
> common key.
>
> I've created a table with one column to generate the sequence
number.How
> can I insert related records into both the tables using same ID?
>
> Thanks
> Ajay
>



--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using the Sqlite Btree Backend directly

[Martin Jenkins]

[EMAIL PROTECTED] wrote:

Martin Jenkins <[EMAIL PROTECTED]> wrote:


Very true.  Furthermore, the B-Tree interface layer is not a part 
of the published API and as such it is subject to change without 
notice and between point releases.


I nearly said that the B-Tree interface was unpublished but didn't 
because I didn't know for sure. Even assuming that it wasn't published I 
didn't know how (un)stable it was. As reasons against using it go



the interface to the B-Tree layer has changed twice in the past 24 hours.


is as good as you'll get. ;)

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using the Sqlite Btree Backend directly

[drh]

Martin Jenkins <[EMAIL PROTECTED]> wrote:
> Ludvig Strigeus wrote:
> > Hi,
> > 
> > I'm looking at using Sqlite as a storage backend for a program. Using 
> > SQL is a little bit overkill...
> 
> ... I reckon learning that SQL would be a lot easier than
> learning to use and/or coding against the BTree interface.
> 

Very true.  Furthermore, the B-Tree interface layer is not a part 
of the published API and as such it is subject to change without 
notice and between point releases.  As it happens the interface
to the B-Tree layer has changed twice in the past 24 hours.  Do
you really want to code to a moving target like that?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Difference in these indices?

[Stephen Toney]

John,

Thanks for the useful info. Unfortunately it sounds as if this is more
than I have time for right now. 

Stephen


On Wed, 2007-03-28 at 16:29 -0600, John Stanton wrote:
> Perl would not do a good job.  You need to use the Sqlite page 
> structures and they are defined in C terms.
> 
> If you want to make such a program I can give you a template in simple 
> ANSI C.  It builds to a different data structure from Sqlite but the 
> algorithms are there.  It uses a quicksort as I recall and draws heavily 
> on Knuth's Sorting and Searching volume.
> 
> If you build a B-Tree by insertions it is continually splitting nodes. 
> A B-Tree grows by extending the root which makes it somewhat self 
> balancing but keeps it busy.  Enhanced B-Trees will merge nodes to 
> minimize splitting and checkerboarding and enhance balance.  The 
> splitting is expensive and even a simple insertion can be fairly 
> expensive because it may change the interior nodes.
> 
> To build one bottom-up you calculate how much space you need based on 
> the size and count of keys and how many levels you will have.  Then you 
> sort the keys and start filling leaf nodes.  As you fill a node you 
> insert an entry into its parent and as you fill a parent you insert into 
> ints parent and so on.  Eventually you will have a fully populated tree 
> with the root less than full.  You never read a node and only write one 
> when it is full so I/O activity is limited.  As a buffer you have a 
> stack of nodes with a depth equal to the depth of the tree.
> 
> You can add some optimization to the tree by making interior nodes 
> contiguous etc.
> 
> By using more modern OS capabilities (POSIX) you could build it faster 
> by extending the Sqlite file by the size of the index, memory mapping 
> that area and using it as the buffer.  When you are finished you unmap 
> the area and the index is complete.  Using that method you perform no 
> writes and get a 20-50% speed improvement compared to using the write 
> API call.
> 
> Stephen Toney wrote:
> > I may work on such a program, if time permits. If successful I will
> > share it. It would be in Perl using DBI::ODBC, so may not be amazingly
> > fast.
> > 
> > I am pretty good at C++ but have phased it out for most work, so I am
> > still using the antique Sybase compiler, and I doubt the SQLite C++
> > library would work with that. Otherwise it would, of course, be a better
> > choice for such a utility. Anyone ever tried that combination?
> > 
> > John, could you clarify what you mean by "building it bottom-up"? I'm
> > not sure how to build a b-tree any way but by insertions. 
> > 
> > Best regards,
> > Stephen
> > 
> > 
> > On Wed, 2007-03-28 at 11:46 -0600, John Stanton wrote:
> > 
> >>I proposed such a program earlier in this discussion.  I would envisage 
> >>a seperate program which strips out a list of keys from the database, 
> >>sorts it then allocates space in the DB file for the resulting index and 
> >>builds it bottom up.  It would be an off-line process but fast and would 
> >>make raising indices on large databases time efficient.
> >>
> >>Based on our experience of building a B-Tree with such a program 
> >>compared to successive insertions a speed improvement in raising an 
> >>index of at least an order of magnitude could be expected.
> >>
> >>By making it an independent program it can be lean, mean and fast and 
> >>not touch the regular Sqlite library.
> >>
> >>Stephen Toney wrote:
> >>
> >>>On Wed, 2007-03-28 at 08:23 -0600, Dennis Cote wrote:
> >>>
> >>>
> >>>
> It might make sense to create a separate standalone utility program 
> (like sqlite3_analyzer) that reuses some the sqlite  source to do bulk 
> inserts into a table in a database file as fast a possible with out 
> having to worry about locking or journaling etc.
> >>>
> >>>
> >>>That would solve my problem too (thread: "CREATE INDEX performance" on
> >>>indexing a 5.8-million record table). I'd love something like that!
> >>>
> >>>
> >>
> >>
> >>-
> >>To unsubscribe, send email to [EMAIL PROTECTED]
> >>-
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
-- 

Stephen Toney
Systems Planning
[EMAIL PROTECTED]
http://www.systemsplanning.com


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using the Sqlite Btree Backend directly

[Martin Jenkins]

Ludvig Strigeus wrote:

Hi,

I'm looking at using Sqlite as a storage backend for a program. Using 
SQL is

a little bit overkill and much more than we need. How complicated would it
be to interface to the btree subsystem directly? Sqlite seems very modular
from the looks of it, but has anyone attempted anything like this before?

The functionality I need is key->value maps with support for lookup by an
exact key, insertion, iteration of all keys in the database, removal.
Transactions would also preferably be needed.


You wouldn't need much SQL to meet those requirements and I reckon 
learning that SQL would be a lot easier than learning to use and/or 
coding against the BTree interface.


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] RE: Maintaining Master-Child relationships

[Martin Jenkins]

Arora, Ajay wrote:

Can anyone please look into my query,

I've tables
 
Master ( id integer primary key,

 field1 text)

And 


Child (id integer, name text)


Might be worth looking for Celko's "Hierarchies in SQL". I've not read 
it but I've seen good reviews and Celko seems well regarded.


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Difference in these indices?

[Martin Jenkins]

John Stanton wrote:
Perl would not do a good job.  You need to use the Sqlite page 
structures and they are defined in C terms.


But Tcl might. The test suite pokes about with SQLite internals.

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Hashing question

[Eduardo Morras]

Hello:

  I'm trying to use altivec simd code to SQLite. Has read hash.c but it looks 
like hashing is done one by one when insert data. Is it true? When is called 
rehash function? If i insert data with index i think hashing happens one by 
one, but if i insert all data and after that make an index rehash works. Can 
anyone confirm this? Rehash function is the only one i can convert to altivec 
(with it's own hashing functions).

Thanks 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Using the Sqlite Btree Backend directly

[Ludvig Strigeus]

Hi,

I'm looking at using Sqlite as a storage backend for a program. Using SQL is
a little bit overkill and much more than we need. How complicated would it
be to interface to the btree subsystem directly? Sqlite seems very modular
from the looks of it, but has anyone attempted anything like this before?

The functionality I need is key->value maps with support for lookup by an
exact key, insertion, iteration of all keys in the database, removal.
Transactions would also preferably be needed.

Thanks,
Ludvig

Re: [sqlite] Java wrapper for both windows and linux

[Xavier RAYNAUD]

Hi Jim,

Jim Dodgen a écrit :

Hello,

first off I am not a java guy. I'm a perl C guy.

I am getting involved in a upcoming project that needs a java wrapper that will 
work for both windows and linux. 


Any preferences?

I currently use this one:
http://www.zentus.com/sqlitejdbc/

o The nestedVM will work for both windows and linux, but you will loose 
performance.
o The native JNI libraries are faster, but do not work for both windows and 
linux.

Personnally, I use native JNI libraries, and choose at runtime the native 
library to open.
(on eclipse, with OS-dependant fragments, it's easy to do)

Xavier


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] RE: Maintaining Master-Child relationships

[Arora, Ajay]

Thanks for your reply.

I'm writing my application in Perl using SQLite as file based database.
Multiple users would be accessing the database simultaneously.But I'll
lock the file prior to any updations to the database. I'm planning to
use a third table to generate ids for the master table. But I don't know
how to select id INTO a variable which can be used while inserting data
into both the tables. Is it possible?

Regards
Ajay.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: 28 March 2007 17:42
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RE: Maintaining Master-Child relationships


On 3/28/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> Can anyone please look into my query,
>
> I've tables
>
> Master ( id integer primary key,
>  field1 text)
>
> And
>
> Child (id integer, name text)
>
> My application receive values for field1 and name.  I need to populate
> master and child with incoming values using the same id.
>

1. Insert "field1" in Master;
2. Get id of record inserted from #1 above;
3. Insert id from #2 and "name" in Child.

That is about all the help I can provide given that you have not
provide any further context... what language you are using, how you
are developing the application, etc.

SQLite does provide a 'last_insert_rowid' capability. Look on the
sqlite.org website.

if you are 100% sure that no one else will be fiddling with your
database, you can probably use something like

INSERT INTO Child( id, name)
VALUES ( (SELECT Max(id) FROM Master), )


> A quick reply will be highly appreciated.
>
> Regards
> Ajay
>
> -Original Message-
> From: Arora, Ajay
> Sent: 28 March 2007 15:04
> To: 'sqlite-users@sqlite.org'
> Subject: Maintaining Master-Child relationships
>
>
> Hi,
>
> I've two tables in my database, a master and a child with ID  as a
> common key.
>
> I've created a table with one column to generate the sequence
number.How
> can I insert related records into both the tables using same ID?
>
> Thanks
> Ajay
>
>
> THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE
PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM
DISCLOSURE. If the reader of this message is not the intended recipient,
you are hereby notified that any dissemination, distribution, copying or
use of this message and any attachment is strictly prohibited. If you
have received this message in error, please notify us immediately by
replying to the message and permanently delete it from your computer and
destroy any printout thereof.
>
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, 
CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader 
of this message is not the intended recipient, you are hereby notified that any 
dissemination, distribution, copying or use of this message and any attachment 
is strictly prohibited. If you have received this message in error, please 
notify us immediately by replying to the message and permanently delete it from 
your computer and destroy any printout thereof.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-