[sqlite] WAL on a separate filesystem?

[Dustin Sallings]

Is it possible without violating any assumptions that would lead to 
reliability problems to have a DB's WAL exist on a separate filesystem?

-- 
dustin sallings

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

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

[Nicolas Williams]

On Tue, Jan 18, 2011 at 10:13:10PM +0100, Florian Weimer wrote:
> * Richard Hipp:
> 
> > I don't think it makes sense in SQL (not just SQLite but SQL in
> > general) for an aggregate query to return columns that are not in
> > the GROUP BY clause.
> 
> Isn't this just what PostgreSQL implements as DISTINCT ON?  Then it
> *is* useful.

DISTINCT and DISTINCT ON effectively map to GROUP BY, no?  DISTINCT ON
explicitly results in unpredictable results unless an ORDER BY clause is
also used.

Incidentally, if there is a UNIQUE constraint for the {grouping columns}
_and_ the non-aggregated/non-grouped columns, then such a query also
makes sense.

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

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

[Florian Weimer]

* Richard Hipp:

> I don't think it makes sense in SQL (not just SQLite but SQL in
> general) for an aggregate query to return columns that are not in
> the GROUP BY clause.

Isn't this just what PostgreSQL implements as DISTINCT ON?  Then it
*is* useful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query or table

[Ian Hardingham]

Hi Phil, thanks for the information.

In general, my server is too slow.  It has to run many operations a 
second, and many DB operations, so the exact definition of "too slow" is 
a little arbitrary.

I'm intruiged that you feel a pre-computed table is not a good idea.  Is 
there an expectation that that would not be especially faster than my 
current method?

Thanks,
Ian

On 18/01/2011 14:07, Philip Graham Willoughby wrote:
> On 18 Jan 2011, at 13:51, Ian Hardingham wrote:
>
>> Hey guys.  I am currently doing the following to find out the "record"
>> between two players in my game:
>>
>> SELECT count(*) TotalGames, sum(score>  0) GamesWonByPlayer1, sum(score
>> <  0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE
>> complete=1 AND player1='Johnson' AND player2='Moonface'';
>> SELECT count(*) TotalGames, sum(score<  0) GamesWonByPlayer1, sum(score
>>> 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE
>> complete=1 AND player1= 'MoonFace' AND player2='Johnson';
>>
>> (then adding them up in my client).
>>
>> I am wondering if, to optimise, I should replace this with a
>> recordAgainst table with a load of player,player pairs which is updated
>> whenever players play against each other.
> Well:
> * Perhaps (if it's currently too slow). It wouldn't be my first choice of 
> optimisation.
> * No (if it is currently fast enough)
>
> You could use a UNION to perform both selects in a single call, but this is 
> unlikely to make much difference.
>
> You could create an index on multiturntable over (complete,player1,player2) 
> if you don't have one already.
>
> You could run ANALYZE if you have some indexes and you haven't run it since 
> populating the database.
>
> Best Regards,
>
> Phil Willoughby

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

Re: [sqlite] Query or table

[Philip Graham Willoughby]

On 18 Jan 2011, at 13:51, Ian Hardingham wrote:

> Hey guys.  I am currently doing the following to find out the "record" 
> between two players in my game:
> 
> SELECT count(*) TotalGames, sum(score > 0) GamesWonByPlayer1, sum(score 
> < 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE 
> complete=1 AND player1='Johnson' AND player2='Moonface'';
> SELECT count(*) TotalGames, sum(score < 0) GamesWonByPlayer1, sum(score 
>> 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE 
> complete=1 AND player1= 'MoonFace' AND player2='Johnson';
> 
> (then adding them up in my client).
> 
> I am wondering if, to optimise, I should replace this with a 
> recordAgainst table with a load of player,player pairs which is updated 
> whenever players play against each other.

Well:
* Perhaps (if it's currently too slow). It wouldn't be my first choice of 
optimisation.
* No (if it is currently fast enough)

You could use a UNION to perform both selects in a single call, but this is 
unlikely to make much difference.

You could create an index on multiturntable over (complete,player1,player2) if 
you don't have one already.

You could run ANALYZE if you have some indexes and you haven't run it since 
populating the database.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

[sqlite] Query or table

[Ian Hardingham]

Hey guys.  I am currently doing the following to find out the "record" 
between two players in my game:

SELECT count(*) TotalGames, sum(score > 0) GamesWonByPlayer1, sum(score 
< 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE 
complete=1 AND player1='Johnson' AND player2='Moonface'';
SELECT count(*) TotalGames, sum(score < 0) GamesWonByPlayer1, sum(score 
 > 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE 
complete=1 AND player1= 'MoonFace' AND player2='Johnson';

(then adding them up in my client).

I am wondering if, to optimise, I should replace this with a 
recordAgainst table with a load of player,player pairs which is updated 
whenever players play against each other.

Some facts:

 - multiturnTable has 100,000 - 10,000,000 rows
 - number of users is between 10,000 and... well, 1,000,000 I suppose.
 - the record between two players needs to be found very regularly
 - I can't change the structure of multiturn table to have player1, 
player2 in alphabetical order at this stage unfortunately (this would 
remove the need to call the above query twice).

Any help is much appreciated.

Thanks,
Ian

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

Re: [sqlite] How to execute SQL Query via text/sql file

[Sachin Gupta]

Great. Thanx.

I will try that out and get back to you ASAP

Thanks & Regards,
Sachin Gupta| iPolicy Networks

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Tuesday, January 18, 2011 6:06 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to execute SQL Query via text/sql file

Sachin Gupta  wrote:
> You mean read it line by line and pass it as const char* to sqlite3_exec?

I did mean sqlite3_exec, but it doesn't have to be line by line. sqlite3_exec 
accepts a batch of semicolon-separated commands in a sinlge string, and 
executes all of them. So just load the whole contents of the file in a single 
buffer, and pass that to sqlite3_exec (again, assuming the file is of a 
reasonable size).
-- 
Igor Tandetnik

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

Re: [sqlite] How to execute SQL Query via text/sql file

[Igor Tandetnik]

Sachin Gupta  wrote:
> You mean read it line by line and pass it as const char* to sqlite3_exec?

I did mean sqlite3_exec, but it doesn't have to be line by line. sqlite3_exec 
accepts a batch of semicolon-separated commands in a sinlge string, and 
executes all of them. So just load the whole contents of the file in a single 
buffer, and pass that to sqlite3_exec (again, assuming the file is of a 
reasonable size).
-- 
Igor Tandetnik

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

Re: [sqlite] How to execute SQL Query via text/sql file

[Sachin Gupta]

You mean read it line by line and pass it as const char* to sqlite3_exec? 

Regards
Sachin


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Tuesday, January 18, 2011 5:52 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to execute SQL Query via text/sql file

Sachin Gupta  wrote:
> We want that the SQLite schema and data be written in a text file. When the 
> application comes up, the SQL file can be executed to
> create the schema and import the data. The text file would have the Create 
> Table statements and also the insert into table
> statements. Similar thing was being done by our application previously. We 
> had a some SQL files and we used to execute these via
> code by creating a command invoking sqlplus and passing the file with a @.  
> 
> I was wondering whether the same could be achieved with SQLite (in Memory).

If the file is reasonably small, just load the whole thing in memory and pass 
it to sqlite3_execute in a single call.
-- 
Igor Tandetnik

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

Re: [sqlite] How to execute SQL Query via text/sql file

[Igor Tandetnik]

Sachin Gupta  wrote:
> We want that the SQLite schema and data be written in a text file. When the 
> application comes up, the SQL file can be executed to
> create the schema and import the data. The text file would have the Create 
> Table statements and also the insert into table
> statements. Similar thing was being done by our application previously. We 
> had a some SQL files and we used to execute these via
> code by creating a command invoking sqlplus and passing the file with a @.  
> 
> I was wondering whether the same could be achieved with SQLite (in Memory).

If the file is reasonably small, just load the whole thing in memory and pass 
it to sqlite3_execute in a single call.
-- 
Igor Tandetnik

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

Re: [sqlite] How to execute SQL Query via text/sql file

[Simon Slavin]

On 18 Jan 2011, at 11:02am, Sachin Gupta wrote:

> We want that the SQLite schema and data be written in a text file. When the 
> application comes up, the SQL file can be executed to create the schema and 
> import the data. The text file would have the Create Table statements and 
> also the insert into table statements.
> Similar thing was being done by our application previously. We had a some SQL 
> files and we used to execute these via code by creating a command invoking 
> sqlplus and passing the file with a @.

Use the SQLite command-line tool instead.



> I was wondering whether the same could be achieved with SQLite (in Memory). I 
> know can be done via code pretty easily, but still if we have the schema and 
> data in a text file, it would help a lot.

Well they are just SQL commands.  Just read them in and execute them line by 
line.

Or you can invoke the command-line tool and send the contents of your text file 
to it, something like

sqlite3 filename.sql3 < textfile.txt

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

[sqlite] Addition note about f1c2a689c6

[Arabak, Skyler]

Hello,

 

Just an additional note on bug f1c2a689c6, it looks like there is either
a typo or missing check after we have filled ac_cv_c_tclconfig in the #
First check to see if --with-tcl was specified. Section.

 

In the following section, # Start autosearch by asking tclsh, it looks
to me like 

if test x"$cross_compiling" = xno; then

should be

if test x"${ac_cv_c_tclconfig}" = x ; then

 

I haven't fully looked at the configure file so I don't know if cross
compiling is actually valid in this section, if it is, then the
ac_cv_c_tclconfig check should encapsulate this existing code to protect
from the -with-tcl value being over written, otherwise the
cross_compiling and xno can probably just be replaced.

 

Thanks!

-Skyler

 

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

Re: [sqlite] Drop Foreign Key

[BareFeetWare]

Oops I left out the insert:

> begin immediate;
> create temp table "My Table Backup" as select * from "My Table";
> drop table "My Table";
> create table "My Table"(  );

insert into "My Table" select * from "My Table Backup";
drop table "My Table Backup";

> create trigger ;
> create index ;
> commit or rollback; 


Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] creating unique indexes, good or bad?

[Simon Slavin]

On 18 Jan 2011, at 2:08am, Sam Carleton wrote:

> I am adding some indexes to an existing database to improve performance.  I
> am 99.9% sure they are unique, but...  it was a while ago that I was in that
> code.  Are there any performance reasons to make them unique or make them
> not unique?  From the stand point of risk, my inclination is to make them
> not unique.

Making an index UNIQUE is a tool you can use to make sure your data is sane.  
If the real-life situation ensures that no duplications can ever exist, then it 
can be nice to have a new record rejected at INSERT time (which is what will 
happen) rather than sneaking into the table to mess things up later.

If your data really is made up of unique records then it won't slow things 
down: no extra sorting will be required.

> While I am on the topic of indexes.  One of the indexes is for a systems
> lookup table, just has category, name, and value.  The size is fixed in
> development.  Currently there are about 25 rows.  Is there any advantage to
> an index or because of the small size, will that only hurt performance?

You are weighing stuff up but the changes are very small.  I don't think it 
will do you much harm, though it may not do you much good either.  But keeping 
your design principles even if they tables are small seems sensible.

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

Re: [sqlite] Drop Foreign Key

[BareFeetWare]

> due serious changes in the table layout of an existing database, I need
> to remove a foreign key. I could not find any SQL command in the docs,
> how to do that. But this must be possibe, since SQLiteExpert supports
> this. Could someone give me a hint?

begin immediate;
create temp table "My Table Backup" as select * from "My Table";
drop table "My Table";
create table "My Table"(  );
create trigger ;
create index ;
commit or rollback; 

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

[sqlite] How to execute SQL Query via text/sql file

[Sachin Gupta]

Hi,

We have been trying to embed SQLite into our application and have been quite 
successful so far. Thanks to all the support extended by you guys.

However, we are kind of stuck trying to do the following:

We want that the SQLite schema and data be written in a text file. When the 
application comes up, the SQL file can be executed to create the schema and 
import the data. The text file would have the Create Table statements and also 
the insert into table statements.
Similar thing was being done by our application previously. We had a some SQL 
files and we used to execute these via code by creating a command invoking 
sqlplus and passing the file with a @.

I was wondering whether the same could be achieved with SQLite (in Memory). I 
know can be done via code pretty easily, but still if we have the schema and 
data in a text file, it would help a lot.

Thanks & Regards,
Sachin Gupta

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

Re: [sqlite] Help on DELETE FROM...

[Marcus Grimm]

ok, just to close this. Both versions of course do work, thanks again to
Michael and Igor for the help. I currently use the NOT IN variant but
I think both are equivalent, maybe a speed difference but since that table
is small I didn't bother to test it out.

For those non-Sql folks like me, here are the two versions:

delete from THI where ID in (
   select t2.ID from THI t2 where t2.UserID=THI.UserID
   order by t2.TimeStamp desc limit 100 offset 10);

delete from THI where ID not in (
   select t2.ID from THI t2 where t2.UserID=THI.UserID
   order by t2.TimeStamp desc limit 10);

Best

Marcus

On 17.01.2011 18:38, Black, Michael (IS) wrote:
> Competing solutions...gotta' love it...I prefer the postive solution as it's 
> a list of what WILL be deleted.  Though there's some merit in "tell me what 
> will be left".
>
> delete from THI where ID in (
> select t2.ID from THI t2 where t2.UserID=UserID
> order by t2.TimeStamp desc limit 100 offset 10);
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Marcus Grimm
> Sent: Mon 1/17/2011 11:18 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM...
>
>
>
>
> On 17.01.2011 17:26, Black, Michael (IS) wrote:
>> I came up with the logical opposite which I think does what you want
>>
>> select a.id  from thi as a where a.id in (select thi.id from thi where 
>> a.userid=thi.userid order by timestamp limit 100 offset 10);
>>
>> You just need to set the limit value to some ridiculous number.
>
> ahh... nice trick!! Thank you!  ;)
>
> Hm.. but how do I place that into a "DELETE FROM ..." statement ?
> Here I can't use the alias syntax...
>
>
>
>
>>
>> Michael D. Black
>> Senior Scientist
>> Advanced Analytics Directorate
>> Northrop Grumman Information Systems
>>
>>
>> 
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of Marcus Grimm
>> Sent: Mon 1/17/2011 10:24 AM
>> To: General Discussion of SQLite Database
>> Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM...
>>
>>
>>
>>
>> On 17.01.2011 17:14, Igor Tandetnik wrote:
>>> Marcus Grimmwrote:
 I have a table to record some history data, for example
 items a user recently selected:

 CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID 
 INTEGER, DataID INTEGER);

 That table needs to trace only the last 10 events, thus I would like
 to remove entries from all users until each user has only 10 recent entries
 in that table.

 I can delete for a specific user (42), using this:

 DELETE FROM THI WHERE
 (UserID=42) AND
 (ID NOT IN (SELECT ID FROM THI WHERE UserID=42 ORDER BY TimeStamp DESC 
 LIMIT 10));

 But how do I do this for all users without using a surrounding loop
 on application level ?
>>>
>>> delete from THI where ID not in (
>>>select ID from THI t2 where t2.UserID = UserId
>>>order by t2.TimeStamp desc limit 10);
>>
>> Thanks Igor!
>> but a stupid question: Doesn't this basically remove
>> all entries from the table because of the "NOT IN" condition ?
>>
>> Marcus
>>
>>>
>> ___
>> 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] Drop Foreign Key

[TeDe]

Hello,

due serious changes in the table layout of an existing database, I need
to remove a foreign key. I could not find any SQL command in the docs,
how to do that. But this must be possibe, since SQLiteExpert supports
this. Could someone give me a hint?

Thanks,

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