Re: [sqlite] Query critique

[Virgilio Alexandre Fornazin]

Calling UPPER() for each field/value while querying is a pain, if you can
assume the names are all in uppercase 
(forcing this in your program before passing them to SQlite), you could
speed up things a lot. But you need to 
analyze what´s happening inside SQlite to check if indices are used, etc.

SQlite command line app has a command that does this (does not remember if
it´s this:
".explain ON|OFFTurn output mode suitable for EXPLAIN on or off.\n")

'ANALYZE' SQlite command also performs some kind of prefetching of indexes
and loads results in a internal 
table, can help also to speed up.

Also, try to avoid JOINS like this

'SELECT a.* FROM globalRankingTable a, friendTable b WHERE upper(b.player) =
upper('?') AND upper(b.friend) = 
upper(a.name))'

It´s better to explicit use LEFT JOIN / INNER JOIN always.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: sábado, 10 de julho de 2010 17:39
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query critique

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 01:54 AM, Ian Hardingham wrote:
> And here is my query (written in a script language):

Are you actually getting correct answers from this?

>  upper('?') 

Unless your script language includes a SQL parser, that does not do what you
think it does.  It is seeing if the value matches upper cased question mark.
 It looks like you wanted to supply bound parameters so the question mark
should not be in quotes.

I'd also suggest using a better wrapper or scripting language since you are
providing three bindings but the query is only using one of them.  It should
complain.

> - yes I know I'm an idiot with the upper stuff, a refactor is needed on 
> that - I know all of my "name" fields should really be integers.

Or look into indices and collations.

In any event you should always post your exact actual query so we don't have
to figure out issues like the question marks are intentional or
transcription errors.

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

iEYEARECAAYFAkw42kcACgkQmOOfHg372QSgxQCfTtgfYFMZ9a4nHtbJn8gI7XEK
7wYAn3ghSgc3wPgl0bFGLq8oA2moTJcs
=aCUo
-END PGP SIGNATURE-
___
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] why is this table locked?

[Sam Carleton]

I am on Window 7, opening an existing database with these flags:

SQLITE_OPEN_EXCLUSIVE |
SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE

Then I get a value from one table, begin a transaction, create a temp
customer table, fill it with the current values from the customer table.
Here is that SQL run by calling sqlite3_exec():

SELECT ItemValue FROM DBLookup WHERE Category = 'SystemSettings' AND
ItemName = 'Version';

BEGIN TRANSACTION;

CREATE TABLE CustomerTemp ( CustomerId INTEGER NOT NULL, CustomerDetailId
INTEGER, IsInSlideShow INTEGER NOT NULL, Username VARCHAR(50) NOT NULL,
Password VARCHAR(50), IsDeleted INTEGER NOT NULL DE
FAULT 0, CopiedTo INTEGER, insertedby VARCHAR(50) NOT NULL, instertedon
TIMESTAMP NOT NULL, updatedby VARCHAR(50) NOT NULL, updatedon TIMESTAMP NOT
NULL, PRIMARY KEY (CustomerId), UNIQUE (username));

INSERT INTO CustomerTemp (CustomerId, IsInSlideShow, Username, Password,
insertedby, instertedon, updatedby, updatedon)
   SELECT CustomerId, IsInSlideShow, Username, Password, insertedby,
instertedon, updatedby, updatedon FROM Customer ORDER BY CustomerId;

This all works fine.  Then I go to drop the current customer table and I
always get a "database table is locked":

 DROP TABLE Customer;

I tried committing the transaction before the drop and that doesn't have any
impact.  I also have foreign keys turned off, I tried turning them off but
that also has no impact.  Low and behold, when I go into SQLite Manager (the
Firebird plugin), I am able to drop the table without any problems.

Any thoughts on the table is locked and how to unlock it?  I know for a fact
no other process has it open.

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

Re: [sqlite] Query critique

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 01:54 AM, Ian Hardingham wrote:
> And here is my query (written in a script language):

Are you actually getting correct answers from this?

>  upper('?') 

Unless your script language includes a SQL parser, that does not do what you
think it does.  It is seeing if the value matches upper cased question mark.
 It looks like you wanted to supply bound parameters so the question mark
should not be in quotes.

I'd also suggest using a better wrapper or scripting language since you are
providing three bindings but the query is only using one of them.  It should
complain.

> - yes I know I'm an idiot with the upper stuff, a refactor is needed on 
> that - I know all of my "name" fields should really be integers.

Or look into indices and collations.

In any event you should always post your exact actual query so we don't have
to figure out issues like the question marks are intentional or
transcription errors.

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

iEYEARECAAYFAkw42kcACgkQmOOfHg372QSgxQCfTtgfYFMZ9a4nHtbJn8gI7XEK
7wYAn3ghSgc3wPgl0bFGLq8oA2moTJcs
=aCUo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[Calimeron]

Eric Smith-29 wrote:
> 
> It could be that you want to create a real table that can be read from
> and written to.  I would then ask why you think you need to do that.

Thank you very much for your help! Now I've joined the tables using the
"join" query.

I think I need to create the new table because of the reasons I wrote
earlier, and:

I want to filter the new table based on a variable that is only present in
the "Chinese" table (frequency of usage). I don't know how I could filter
the "English" table using this variable in the "Chinese" table.

It just seems simpler, and I've done this filtering before.

-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29127987.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] Understanding how to use locking optimally

[Simon Slavin]

On 10 Jul 2010, at 8:27pm, Alan Chandler wrote:

> I am actually not using C, but the php routines for this.  I have my 
> suspicions, but I can't find it explicity stated anywhere that the 
> PHP::SQLITE3 module behaves exactly like the C routines  but that using 
> PHP::PDO abstraction layer handles the lock detection and retry itself

That's likely to be correct.   The PDO module has to work exactly the same as 
the calls to other SQL engines work: you should be able to switch from, say, 
MySQL to SQLite3 without changing a line of your code.  Consequently the PDO 
module has its own expected behaviour, set when the first few SQL engines were 
implemented in it.

On the other hand the SQLITE3 module was custom-written to suit SQLite users: 
it can have any functions in that suit how people use SQLite.  It can display 
whatever non-standard behaviour experienced SQLite users expect.  I would 
expect (but haven't read the source code) that Zend's implementation of SQLITE3 
consists of minimal shim functions just to make each C function of SQLite 
available as a PHP function. That's why the documentation for SQLITE3 is so 
small: anything worth looking up is already available in the documentation for 
SQLite3.

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

[sqlite] Understanding how to use locking optimally

[Alan Chandler]

I have just joined this mailing list, because searching the archives I 
have not found much related to locking.  I am not completely new to 
SQLite, having previously used it when I wrote my latest version of a 
chat program, where I migrated the database over to from mysql.

In my first attempt to do this, I tried to completely manage a single 
users transaction (write a message, move between rooms etc) in the 
apache thread which handled the original page or subsequent ajax 
requests and thought, but was subsquently proven wrong that SQLlite 
would queue when there was contention for a lock.  In the end I 
implemented a loop on BEGIN EXCLUSIVE which failed with a SQLITE_BUSY by 
sleeping for a random period of a few milliseconds and trying again.

  However once I got up to a few online test users (about 40 each 
speaking every 30 seconds or so) , interaction between the threads 
became too much.  I ended up in almost continual busy mode backing off 
and retrying when the locks were taken.  In the end I implemented a 
Server Process based approach to get round that (and it worked extremely 
well).

I am about to try and port two more web/ajax php/javascript applications 
from postgres to sqlite - the rationale being that having the local 
database file accessible directly rather than some amorphous set behind 
a server will be easier to manage.  Also both of these have the 
characteristic that there will be a lot less parallelism.  However there 
will not be none, and I will still have to allow for the fact that 
users  will be looking at the database in parallel.  In fact both of the 
applications have the property that they will do quite a bit of querying 
of the database to build up the web pages they look at and that normally 
a single user will conduct the majority of the update transactions.

One of these applications is a personal financial planning package where 
I expect normally a single user to update the data, although I don't 
want to rule out maybe a couple of people doing it.  I already have 
application level checks to check the integrity of data related to 
parallel updates but I need to plan for potentially the database 
clashing when two people attempt an update transaction at the same time. 
I think the answers related to the more complex application discussed 
below will enable me to do this.

The other is an (American) football results picking competition.  In 
this the majority of the update transactions are the performed by a 
(single) administrator with the majority of the users just performing 
queries to check for matches and past results.  Once a week (or maybe a 
two or three times if they decide the initial picks are wrong) each user 
in the competition will do a single transaction to update all their 
picks for the week.

I want to design an optimal locking strategy and a series of simple 
middleware functions to handle the occassional requirements to back off 
and retry.  However I am not sure from the documentation exactly when I 
might receive a SQL_BUSY failure in the cases where

a) The administrator commits a change he has setup on his web page.  I 
would expect to him (or rather the ajax request resulting from him 
hitting the SAVE button) to do a BEGIN TRANSACTION (not yet sure if its 
optimal to do an IMMEDIATE or EXCLUSIVE at this point).  What happens to
   i) This request if there are users still completing queries to read 
the data.  IE does it immediately fail SQLITE_BUSY or will it wait a 
short period whilst any current SELECT statements complete
  ii) Users who are performing a series of SELECT statements (with 
complex joins) and for which the Administrators BEGIN TRANSACTION occurs 
in the middle of the series. And as a follow on
  iii) Should I really be doing a BEGIN TRANSACTION before that series 
of SELECTS to hold off the administrator here and ensure the user gets 
the entire page displayed. When would the administrator see the 
SQLITE_BUSY if he did a BEGIN IMMEDIATE

b) As the user submits his picks I would again do a BEGIN TRANSACTION 
(or may be a BEGIN EXLUSIVE or BEGIN IMMEDIATE and other users and the 
administrator are doing their things in parallel.


I am aware of the staged locking process, what I am unclear about is 
that does failure to get the locks at ANY of these stages cause an 
SQLITE_BUSY to occur?


I am actually not using C, but the php routines for this.  I have my 
suspicions, but I can't find it explicity stated anywhere that the 
PHP::SQLITE3 module behaves exactly like the C routines  but that using 
PHP::PDO abstraction layer handles the lock detection and retry itself 
(my only evidence is that the php manual for

bool *PDO::setAttribute* ( int $attribute , mixed 
 
$value )

...

/PDO::ATTR_TIMEOUT/: Specifies the timeout duration in seconds. Not all 
drivers support this option, and it's meaning may differ from driver to 
driver. For example, 

Re: [sqlite] open db cx to fork(2)d children

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/10/2010 07:12 AM, Eric Smith wrote:
> Your wrapper is nice -- have you considered folding something like it 
> into the core (disabled by default, enabled by a compile-time flag) and 
> submitting it to drh for official adoption?  

DRH and the developers are aware of it as my approach was discussed on one
of the mailing lists, and I did the performance measurements in response to
their queries.

I'd have no problem contributing the code to SQLite, but there isn't very
much of it and it is an open issue as to how you report the cross fork usage
should it happen.  (In my case I know Python is being used.)

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

iEYEARECAAYFAkw4u5kACgkQmOOfHg372QTcOACfcWCMm4xnBWegCUDhCihTH6XO
YG0AoK3yw381ea7O0kS4/KZdXCjNFrra
=nVfb
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[Simon Slavin]

On 10 Jul 2010, at 5:39pm, Calimeron wrote:

> -- One table is more than 30.000 lines, the other is more than 50.000 lines.
> I've understood from the video tutorial that the program (I'm using
> http://www.sqlite.org/sqlite-3_6_23_1.zip) will first compile the entire
> list 30.000 x 50.000 = 1.5 BILLION lines, after which it filters it. I'm
> sure that's too many lines? (My firefox plugin repeatedly crashed, so I
> switched to this one.)

If you're talking about the command-line tool 'sqlite3', then it saves the 
table to disk as it works.  It does not generate the entire table to memory, 
then store it to disk as a single operation.

On 10 Jul 2010, at 6:10pm, Eric Smith wrote:

> But the view, as far as I understand, doesn't provide a performance 
> benefit over the query that defines it.  It only exists for simplified 
> understanding of your problem space and your code.  

In SQLite, a VIEW is a way of saving a SELECT command.  It saves the command 
itself, not the results of running it.  So yes, you're right: a VIEW would not 
involve storing the entire resulting table in memory.

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

Re: [sqlite] Noobie question about importing data into table

[Calimeron]

Thanks for your reply. Before I try to understand your post, because you are
here now and maybe not later:

I will take the result (of 50.000 + lines), and further filter it to 5.000 +
lines, and then split that into 13 tables of 400+ lines, which I'll be
manipulating in excel, and print out to study.

I think this is easiest, because I already know how to do it.



-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29127168.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] Noobie question about importing data into table

[Eric Smith]

Calimeron wrote: 

> >What do you mean, "save"?  
> 
> So that when you're done, you have 3 tables "Chinese," "English," "Merged" 
> or the original 2, but with data from the one into the other.  
> 
> I don't know the terms or the procedure, but I'd like to have a new table 
> that has the Chinese and English mixed, which I then can continue to 
> manipulate.  

The answer to that depends strongly on your application.  It could be
that you should just use do whatever manipulations you need in your main
language.

It could be that your use case will be simplified using what's called 
a "view".  This will essentially be a wrapper around your join query.  
You can read from the view but you can't write to it.  The view is 
always consistent with the underlying "real" tables.  

But the view, as far as I understand, doesn't provide a performance 
benefit over the query that defines it.  It only exists for simplified 
understanding of your problem space and your code.  

So if you think that you will run your JOIN query a lot, and you think 
that it will be a performance bottleneck, then a view is probably not 
the right way to go.

Here's what a view looks like (using my previous example code):

[hudson:~] $ sqlite3 ./foo.db 
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE chChar(id INTEGER PRIMARY KEY, char TEXT);
INSERT INTO "chChar" VALUES(1,'char1');
INSERT INTO "chChar" VALUES(2,'char2');
INSERT INTO "chChar" VALUES(3,'char3');
INSERT INTO "chChar" VALUES(4,'char4');
INSERT INTO "chChar" VALUES(5,'char5');
CREATE TABLE chCharDefn(id INTEGER PRIMARY KEY, chChar REFERENCES
chChar, defn TEXT);
INSERT INTO "chCharDefn" VALUES(1,1,'def1');
INSERT INTO "chCharDefn" VALUES(2,2,'def2');
INSERT INTO "chCharDefn" VALUES(3,2,'def3');
INSERT INTO "chCharDefn" VALUES(4,3,'def4');
INSERT INTO "chCharDefn" VALUES(5,4,'def5');
INSERT INTO "chCharDefn" VALUES(6,4,'def6');
INSERT INTO "chCharDefn" VALUES(7,5,'def7');
COMMIT;
sqlite> create view ChineseCharsAndDefns as select chChar.id, chChar.char, 
chCharDefn.id, chCharDefn.defn from chCharDefn join chChar ON 
(chCharDefn.chChar = chChar.id);
sqlite> select * from ChineseCharsAndDefns;
1|char1|1|def1
2|char2|2|def2
2|char2|3|def3
3|char3|4|def4
4|char4|5|def5
4|char4|6|def6
5|char5|7|def7
sqlite>

As you can see, I just prefixed my join query with "create view
ChineseCharsAndDefns as".  Now watch what happens when I change one of
the underlying tables:

sqlite> begin;
sqlite> update chCharDefn set defn='def7CHANGED' where id=7;
sqlite> select * from ChineseCharsAndDefns;
1|char1|1|def1
2|char2|2|def2
2|char2|3|def3
3|char3|4|def4
4|char4|5|def5
4|char4|6|def6
5|char5|7|def7CHANGED
sqlite> rollback;
sqlite> 

Note again that, as far as I know, views in SQLite only exist as 
'syntactic sugar' -- they don't provide any functional difference, 
just a textual difference in your code.  Others can confirm or refute
that.

It could be that you want to create a real table that can be read from
and written to.  I would then ask why you think you need to do that.

Eric

-- 
Eric A. Smith

Sendmail may be safely run set-user-id to root.
-- Eric Allman, "Sendmail Installation Guide"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[Calimeron]

>What do you mean, "save"?

So that when you're done, you have 3 tables "Chinese," "English," "Merged"
or the original 2, but with data from the one into the other.

I don't know the terms or the procedure, but I'd like to have a new table
that has the Chinese and English mixed, which I then can continue to
manipulate.  

-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29127021.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] Noobie question about importing data into table

[Eric Smith]

Calimeron wrote: 

> I've understood that the program (I'm 
> using http://www.sqlite.org/sqlite-3_6_23_1.zip) will first compile the 
> entire list 30.000 x 50.000 = 1.5 BILLION lines, after which it filters 
> it.  

One of SQLite's primary goals in life is to fit into a tiny memory 
footprint.  I don't know for sure, but strongly doubt that it will 
create the whole cross product of the two tables before filtering.
I guess it's more likely that either the video is wrong or you're
misunderstanding it.

> --After you've selected the lines you want, how do you save the 
> table (instead of only selecting it)?

What do you mean, "save"?

-- 
Eric A. Smith

gleemites, n.:
Petrified deposits of toothpaste found in sinks.
-- "Sniglets", Rich Hall & Friends
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reg: In Memory Database Using SQLite

[ca44]

Yes, please do send it to me. 

Thx 

-C 


- Original Message - 
From: "Subhadeep Ghosh"  
To: "General Discussion of SQLite Database"  
Sent: Saturday, July 10, 2010 7:42:03 AM 
Subject: Re: [sqlite] Reg: In Memory Database Using SQLite 

Hi Chris, 

I had attached the source code but it mysteriously vanished. I'm guessing 
that this mailing list does not support mail attachments. However, if you 
want I could mail it to you on your personal mail ID. 

Thank you and regards, 
Subhadeep. 

On Sat, Jul 10, 2010 at 8:09 PM,  wrote: 

> 
> 
> Hi Subhadeep, 
> 
> I'm not seeing the source code included anywhere in your email. 
> 
> 
> 
> Thx 
> 
> -Chris 
> 
> 
> - Original Message - 
> From: "Subhadeep Ghosh"  
> To: sqlite-users@sqlite.org 
> Sent: Wednesday, July 7, 2010 1:51:28 PM 
> Subject: [sqlite] Reg: In Memory Database Using SQLite 
> 
> Hello People, 
> 
> I finally managed to create a wrapper around the SQLite core to support the 
> creation of in-memory databases. The wrapper comprises of three functions - 
> one to serialize the database, one to de-serialize a database and the third 
> one to do the cleanup job. 
> 
> The function which serializes a database, copies the entire contents of the 
> database to a block of memory. The function which de-serializes takes a 
> block of memory as input and creates a database object out of it. 
> 
> I am including the source code for the interested souls and I've not put 
> comments as I hate doing it. 
> 
> I hope you guys find the code useful or at-least interesting. 
> 
> Thank you and regards, 
> Subhadeep Ghosh. 
> 
> ___ 
> 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

Re: [sqlite] Noobie question about importing data into table

[Calimeron]

Okay, so now I've gotten a bit further, but:

-- One table is more than 30.000 lines, the other is more than 50.000 lines.
I've understood from the video tutorial that the program (I'm using
http://www.sqlite.org/sqlite-3_6_23_1.zip) will first compile the entire
list 30.000 x 50.000 = 1.5 BILLION lines, after which it filters it. I'm
sure that's too many lines? (My firefox plugin repeatedly crashed, so I
switched to this one.)

--After you've selected the lines you want, how do you save the table
(instead of only selecting it)?
-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29126934.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] Noobie question about importing data into table

[Calimeron]

Thanks for your reply!

I'm now trying to get it to work, with the help of your post and this nice 
http://www.youtube.com/watch?v=NcrZoHselPk video tutorial about joining 
-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29126401.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] Reg: In Memory Database Using SQLite

[Subhadeep Ghosh]

Hi Chris,

I had attached the source code but it mysteriously vanished. I'm guessing
that this mailing list does not support mail attachments. However, if you
want I could mail it to you on your personal mail ID.

Thank you and regards,
Subhadeep.

On Sat, Jul 10, 2010 at 8:09 PM,  wrote:

>
>
> Hi Subhadeep,
>
> I'm not seeing the source code included anywhere in your email.
>
>
>
> Thx
>
> -Chris
>
>
> - Original Message -
> From: "Subhadeep Ghosh" 
> To: sqlite-users@sqlite.org
> Sent: Wednesday, July 7, 2010 1:51:28 PM
> Subject: [sqlite] Reg: In Memory Database Using SQLite
>
> Hello People,
>
> I finally managed to create a wrapper around the SQLite core to support the
> creation of in-memory databases. The wrapper comprises of three functions -
> one to serialize the database, one to de-serialize a database and the third
> one to do the cleanup job.
>
> The function which serializes a database, copies the entire contents of the
> database to a block of memory. The function which de-serializes takes a
> block of memory as input and creates a database object out of it.
>
> I am including the source code for the interested souls and I've not put
> comments as I hate doing it.
>
> I hope you guys find the code useful or at-least interesting.
>
> Thank you and regards,
> Subhadeep Ghosh.
>
> ___
> 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] Reg: In Memory Database Using SQLite

[ca44]

Hi Subhadeep, 

I'm not seeing the source code included anywhere in your email. 



Thx 

-Chris 


- Original Message - 
From: "Subhadeep Ghosh"  
To: sqlite-users@sqlite.org 
Sent: Wednesday, July 7, 2010 1:51:28 PM 
Subject: [sqlite] Reg: In Memory Database Using SQLite 

Hello People, 

I finally managed to create a wrapper around the SQLite core to support the 
creation of in-memory databases. The wrapper comprises of three functions - 
one to serialize the database, one to de-serialize a database and the third 
one to do the cleanup job. 

The function which serializes a database, copies the entire contents of the 
database to a block of memory. The function which de-serializes takes a 
block of memory as input and creates a database object out of it. 

I am including the source code for the interested souls and I've not put 
comments as I hate doing it. 

I hope you guys find the code useful or at-least interesting. 

Thank you and regards, 
Subhadeep Ghosh. 

___ 
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] importing data from file with 3 colums to table with 4 columns

[ca44]

Thanks Monte for your response. 

I am still pretty new to SQlite, but it looks very similar to what Simon 
suggested. 

I think it would work just as well. 



From my perspective the ideal way to do this would be to specify the columns 
names the following data in the file is to load into. 

Then have an option in the .import function to say that the 1st line in the 
file defines the column names. 



-Chris 


- Original Message - 
From: "Monte Milanuk"  
To: sqlite-users@sqlite.org 
Sent: Friday, July 9, 2010 10:11:41 AM 
Subject: Re: [sqlite]importing data from file with 3 colums to table 
with 4columns 

Just because I was bored ;) I decided to take a stab at this one as well 
(following Simon's excellent guidance). 

I had thought because of what it says in the FAQ here: 

http://sqlite.org/faq.html#q1 

that it should be possible to import the values directly into the final table 
and have sqlite auto-populate the primary key field as it went.  If it is 
possible, I haven't found any good examples of *how*. 

As it is... I came up with the following import-csv.sql file for importing your 
CSV data: 

CREATE TABLE t1(ID integer primary key autoincrement, Name varchar(40), 
Category varchar(40), Recommendation varchar(40)); 
CREATE TABLE t2(a, b, c); 
.separator "|" 
.import data.csv t2 
INSERT INTO t1(Name, Category, Recommendation) SELECT * FROM t2; 
DROP TABLE t2; 
.headers on 
.mode column 
.width 5 20 9 15 
SELECT * FROM t1; 

After that... running the following command should net the resulting output: 

E:\sqlite>sqlite3 temp.db ".read import-csv.sql" 
ID     Name                  Category   Recommendation 
-    -  --- 
1      Barracuda             seafood    No 
2      Catfish               seafood    No 
3      Caviar                seafood    No 
4      Conch                 seafood    No 
5      Herring(pickled)      seafood    No 
6      Lox(smoked salmon)    seafood    No 
7      Octopus               seafood    No 

E:\sqlite> 



Not sure if that was exactly what the OP was after, but it kept me 
entertained ;) 

___ 
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] open db cx to fork(2)d children

[Eric Smith]

Roger Binns wrote: 

> I'll bet you are actually getting exit(3) which means anything 
> registered with atexit will be run.  (SQLite does not register with 
> atexit.)  

I knew what Nico meant :) just repeated him for expositional clarity 
(deeming the distinction to be unimportant for my particular question,
and figuring that most people mean exit(3) when they say exit(2)).

> In my wrapper I provide functionality that can check SQLite objects are 
> not being used across processes.  
> 
> I'd recommend doing something like this 

My fork call is from Tcl -- you've convinced me to add a line in the 
child unsetting the Tcl variable containing the name of the parent's 
database connection.  :) 

Your wrapper is nice -- have you considered folding something like it 
into the core (disabled by default, enabled by a compile-time flag) and 
submitting it to drh for official adoption?  

Thanks much to both of you!

Eric 

-- 
Eric A. Smith

More people would come here if it weren't so crowded.
-- Yogi Berra
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[Eric Smith]

Calimeron wrote:

> Chinese Char. No.  Chinese Char.  English Def. No.English Def.
> 1 Char1   1   Def1
> 2 Char2   2   Def2
> 2 Char2   3   Def3
> 3 Char3   4   Def4
> 4 Char4   5   Def5
> 4 Char4   6   Def6
> 5 Char5   7   Def7
> 
> Can this be done? 

What you want is called a "join".

[hudson:~] $ sqlite3 
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table chChar(id INTEGER PRIMARY KEY, char TEXT);
sqlite> create table englCharDefn(id INTEGER PRIMARY KEY, chChar REFERENCES 
chChar, defn TEXT);
sqlite> INSERT INTO chChar VALUES(null, 'char1');
sqlite> INSERT INTO chChar VALUES(null, 'char2');
sqlite> INSERT INTO chChar VALUES(null, 'char3');
sqlite> INSERT INTO chChar VALUES(null, 'char4');
sqlite> INSERT INTO chChar VALUES(null, 'char5');
sqlite> INSERT INTO englCharDefn VALUES(null, 1, 'def1');
sqlite> INSERT INTO englCharDefn VALUES(null, 2, 'def2');
sqlite> INSERT INTO englCharDefn VALUES(null, 2, 'def3');
sqlite> INSERT INTO englCharDefn VALUES(null, 3, 'def4');
sqlite> INSERT INTO englCharDefn VALUES(null, 4, 'def5');
sqlite> INSERT INTO englCharDefn VALUES(null, 4, 'def6');
sqlite> INSERT INTO englCharDefn VALUES(null, 5, 'def7');
sqlite> select chChar.id, chChar.char, englCharDefn.id, englCharDefn.defn from 
englCharDefn join chChar ON (englCharDefn.chChar = chChar.id);
1|char1|1|def1
2|char2|2|def2
2|char2|3|def3
3|char3|4|def4
4|char4|5|def5
4|char4|6|def6
5|char5|7|def7

--
Eric A. Smith

I still maintain the point that designing a monolithic kernel in 
1991 is a fundamental error.  Be thankful you are not my student.  
You would not get a high grade for such a design.
-- Andrew Tanenbaum, to Linus Torvalds
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Jan Neerbek is out of the office.

[Jan Neerbek]

I will be out of the office starting  09-07-2010 and will not return until
02-08-2010.

I will respond to your message when I return. For urgent matters you may
contact BRE


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

Re: [sqlite] Noobie question about importing data into table

[Calimeron]

Another method would be to add a column to the English table, and in that
insert the Chinese character that corresponds with the number in "Chinese
Char. No."
-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29126166.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] Noobie question about importing data into table

[Calimeron]

Hi,

I'm a noobie. I would have liked to first search this forum, but I don't
even know the search terms I should use ...

I'm using the firefox plugin "Sqlite Manager" to manipulate the data.


Question:

I have two tables:

TABLE 1:
Chinese Char. No.  Chinese Char.
1 Char1
2 Char2
3 Char3
4 Char4
5 Char5

TABLE 2:
Chinese Char. No.  English Def. No.English Def.
11   Def1
22   Def2
23   Def3
34   Def4
45   Def5
46   Def6
57   Def7


So, these are two tables, one for the Chinese characters, and one for the
English definition of the characters. Some of the Chinese characters have
multiple definitions, so the "Chinese" table has 30.000 lines and the
"English" table has 50.000 lines.

I'd like to import the Chinese table into the English one:

Chinese Char. No.  Chinese Char.English Def. No.English Def.
1   Char1   1   Def1
2   Char2   2   Def2
3   Def3
3   Char3   4   Def4
4   Char4   5   Def5
6   Def6
5   Char5   7   Def7


And, I'd like the blank spaces filled with the value that is in the cell
above them:

Chinese Char. No.  Chinese Char.English Def. No.English Def.
1   Char1   1   Def1
2   Char2   2   Def2
2   Char2   3   Def3
3   Char3   4   Def4
4   Char4   5   Def5
4   Char4   6   Def6
5   Char5   7   Def7

Can this be done? 

Thank you very much for any reply!




-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29126116.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] importing data from file with 3 colums to tabl e with 4 columns

[Monte Milanuk]

Just because I was bored ;) I decided to take a stab at this one as well 
(following Simon's excellent guidance).

I had thought because of what it says in the FAQ here:

http://sqlite.org/faq.html#q1

that it should be possible to import the values directly into the final table 
and have sqlite auto-populate the primary key field as it went.  If it is 
possible, I haven't found any good examples of *how*.

As it is... I came up with the following import-csv.sql file for importing your 
CSV data:

CREATE TABLE t1(ID integer primary key autoincrement, Name varchar(40), 
Category varchar(40), Recommendation varchar(40));
CREATE TABLE t2(a, b, c);
.separator "|"
.import data.csv t2
INSERT INTO t1(Name, Category, Recommendation) SELECT * FROM t2;
DROP TABLE t2;
.headers on
.mode column
.width 5 20 9 15
SELECT * FROM t1;

After that... running the following command should net the resulting output:

E:\sqlite>sqlite3 temp.db ".read import-csv.sql"
ID Name  Category   Recommendation
-    -  ---
1  Barracuda seafoodNo
2  Catfish   seafoodNo
3  CaviarseafoodNo
4  Conch seafoodNo
5  Herring(pickled)  seafoodNo
6  Lox(smoked salmon)seafoodNo
7  Octopus   seafoodNo

E:\sqlite>



Not sure if that was exactly what the OP was after, but it kept me 
entertained ;)

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

[sqlite] Query critique

[Ian Hardingham]

Hey guys.

I have a query which is very slow, and was wondering if there was any 
advice you guys had on it.

Here are two table definitions:

CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, 
record TEXT);

CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);

And here is my query (written in a script language):

db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE 
upper(name) = upper('?') OR id < ? union all SELECT a.* FROM 
globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?') 
AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0, 
%client.username, %globId, %client.username);

The intention of this query is to, basically:

- get the top 10 scores in rankingTable
- get "my" score in rankingTable
- get all of my friends' scores in rankingTable

The query works, but is very slow.  This may just be because it's a 
complex query, but all feedback would be much appreciated.

For your extra information:

- globalRankingTable has several thousand rows
- friendTable has several thousand rows also, but each user will only 
have 0-10 friends (usually on the low part of that scale)
- yes I know I'm an idiot with the upper stuff, a refactor is needed on 
that - I know all of my "name" fields should really be integers.

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