Re: [sqlite] debugging sqlite with gdb

[Mohammad Reaz Uddin]

Thanks everybody.

On Mon, Aug 17, 2009 at 5:22 AM, Simon Slavin
wrote:

>
> On 17 Aug 2009, at 12:46am, Miroslav Zagorac wrote:
>
> > Mohammad Reaz Uddin wrote:
> >> I downloaded 'sqlite-amalgamation-3.6.16.tar.gz' and used makefile to
> >> compile it.
> >
> > sqlite is a shell script, wrapper to a compiled binary in .libs
> > directory
>
> wrapped in a mystery, inside an enigma.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards,
Md. Reaz Uddin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems with max(datestamp) in subquery

[David Bicking]

On Mon, 2009-08-17 at 15:05 -0700, Leo Freitag wrote:
> David Bicking-2 wrote:
> > 
> > As written, you were selecting any record with the correct date
> > regardless of Ensemble or Steuck.
> > 
> > David

> The following seem to work:
> 
> SELECT * FROM tblZO_Haupt AS hpt
> WHERE
> hpt.zo_tblEnsemble = 
> AND hpt.zo_tblStueck = ...
> AND hpt.datum = (
> SELECT MAX(hpt_sub.datum)
> FROM tblZO_Haupt AS hpt_sub
> WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger
> AND hpt_sub.zo_tblEnsemble = hpt.zo_tblEnsemble
> AND hpt_sub.zo_tblStueck = hpt.zo_tblStueck
> AND hpt_sub.datum <= '2009-08-03 00:00:00'); 
> 
> But I didn't really get the deeper meaning of the three conditions in the
> subquery: hpt_sub.xyz = hpt.xyz?
> 
> Leo
> 
> 
That will work because it forces the subquery and main query to look at
the same values for tblSaenger, tblEnsemble and tblStueck.

David


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

[sqlite] What does SQLITE_IOERR_TRUNCATE mean, exactly?

[John Belli]

I have a broken database file, with a journal. It is opened read-only
(via sqlite3_open_v2()). The statement "PRAGMA user version" is
prepared successfully. When sqlite3_step() is called, the return code
is SQLITE_IOERR. The extended code is SQLITE_IOERR_TRUNCATE. What
exactly does this mean?


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

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

Re: [sqlite] Functions to convert dates with non-English month name?

[Gilles Ganault]

On Mon, 17 Aug 2009 07:44:15 -0400, "Igor Tandetnik"
 wrote:
>update membres set dateinscription=
>substr(dateinscription, -4) || '-' ||
>(case substr(dateinscription, 4, length(dateinscription) - 8)
> when 'January' then '01' when 'February' then '02' ...
> when 'December' then '12' end) || '-' ||
>substr(dateinscription, 1, 2);
>
>Substitute correct month names. Converting dateconnexion is left as an 
>exercise for the reader.

Thanks, I'll give it a shot.

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

Re: [sqlite] [Duplicates] How to keep only one row?

[Gilles Ganault]

On Mon, 17 Aug 2009 13:05:53 +0200, Martin Engelschalk
 wrote:
>If you are looking for a delete - command, then you have to decide which 
>of the duplicate rows you want to keep. Are they all the same even in 
>the other fields?
>
>Perhaps you want to do something like
>
>delete from members where exists (select rowid from members m2 where 
>m2.name = members .name and m2.rowid < members .rowid)
>
>This statement deletes all rows where there exists a row with the same 
>name and a lowe rowid. Rowid is an internal field of sqlite.

Thanks for the code :)

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

Re: [sqlite] Problems with max(datestamp) in subquery

[Leo Freitag]

David Bicking-2 wrote:
> 
> As written, you were selecting any record with the correct date
> regardless of Ensemble or Steuck.
> 
> David
> 


The following seem to work:

SELECT * FROM tblZO_Haupt AS hpt
WHERE
hpt.zo_tblEnsemble = 
AND hpt.zo_tblStueck = ...
AND hpt.datum = (
SELECT MAX(hpt_sub.datum)
FROM tblZO_Haupt AS hpt_sub
WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger
AND hpt_sub.zo_tblEnsemble = hpt.zo_tblEnsemble
AND hpt_sub.zo_tblStueck = hpt.zo_tblStueck
AND hpt_sub.datum <= '2009-08-03 00:00:00'); 

But I didn't really get the deeper meaning of the three conditions in the
subquery: hpt_sub.xyz = hpt.xyz?

Leo


-- 
View this message in context: 
http://www.nabble.com/Problems-with-max%28datestamp%29-in-subquery-tp24905950p25015087.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] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Angus March]

Shane Harrelson wrote:
> To the original question though, with PRAGMA synchronous=OFF, SQLite will
> NOT do explicit fsync()'s.  A exception to this occurs with attached DB's
> and a transaction; when the transaction is committed and the master journal
> is deleted, SQLite fsyncs the directory that contained the journal to ensure
> the delete is "flushed".   The only way around this fsync() is to compile
> with SQLITE_DISABLE_DIRSYNC.  This might be changed in the future.
>   

Right. If the only time fsync would be called is... whatever that
jargon is up there, then there should be a problem, since I don't think
I'm using that. Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Shane Harrelson]

On Mon, Aug 17, 2009 at 11:53 AM, D. Richard Hipp  wrote:

>
> On Aug 17, 2009, at 11:41 AM, Matt Sergeant wrote:
> >
> > Kernels will fflush when a file handle is closed
>
> Not according to Ted Ts'o (creator of the Ext2/3/4 filesystems).  See,
> for example, the extensive discussions of this at
>
>
> http://thunk.org/tytso/blog/2009/03/12/delayed-allocation-and-the-zero-length-file-problem/
> http://thunk.org/tytso/blog/2009/03/15/dont-fear-the-fsync/
>
> Ted says that it is widely believed among programmers that close()
> will sync a file, but in fact nothing in POSIX requires this and in
> fact Linux does not do it.  Some hacks were added to ext4 in the
> 2.6.30 kernel release to mitigate the damage following a power loss
> when programs fail to fsync() prior to close().  But everybody agrees
> those changes are an ugly hack.
>
> In POSIX, the bottom line is this:  The *only* way to force data to
> oxide is to call sync() or fsync().  Some kernels and/or some
> filesystems might sync at other times, but it is not something that
> you can rely on.
>
> D. Richard Hipp
> d...@hwaci.com
>


To the original question though, with PRAGMA synchronous=OFF, SQLite will
NOT do explicit fsync()'s.  A exception to this occurs with attached DB's
and a transaction; when the transaction is committed and the master journal
is deleted, SQLite fsyncs the directory that contained the journal to ensure
the delete is "flushed".   The only way around this fsync() is to compile
with SQLITE_DISABLE_DIRSYNC.  This might be changed in the future.

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

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Simon Slavin]

On 17 Aug 2009, at 3:47pm, Angus March wrote:

> I was concerned
> that the documentation might be playing fast and loose, saying that
> fflush (or fsync, or fdatasync) won't be called, when it really means
> that it won't be called during any call to step() or finalize(), while
> it would be called when the session is closed. I wasn't sure, so I
> thought I'd ask, because it'll matter to my app.


Although the SQLite code might not be syncing, it is still issuing the  
commands to write data to the database file (at least I assume it  
does).  Various events can send a synchronisation event all the way up  
(or down) the chain of command.  For instance, a hard disk which goes  
to sleep due to inactivity will trigger a sync before they sleep; a  
laptop which is about to sleep due to low battery will often flush  
queued writes; in some operating systems the OS will issue a sync for  
files handled by a process about to be swapped out of active memory.

So no, you cannot depend on no writing to disk just because you've  
told SQLite not to synchronise every transaction.  If you don't want  
your record written to disk, don't issue the INSERT command.  Perhaps  
you could use a virtual table or something.

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

Re: [sqlite] debugging sqlite with gdb

[Simon Slavin]

On 17 Aug 2009, at 12:46am, Miroslav Zagorac wrote:

> Mohammad Reaz Uddin wrote:
>> I downloaded 'sqlite-amalgamation-3.6.16.tar.gz' and used makefile to
>> compile it.
>
> sqlite is a shell script, wrapper to a compiled binary in .libs  
> directory

wrapped in a mystery, inside an enigma.

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

Re: [sqlite] 3.6.17 test failure

[Ken]

--- On Sat, 8/15/09, Dan Kennedy  wrote:

> From: Dan Kennedy 
> Subject: Re: [sqlite] 3.6.17 test failure
> To: "General Discussion of SQLite Database" 
> Date: Saturday, August 15, 2009, 12:36 AM
> 
> On Aug 15, 2009, at 2:14 AM, Ken wrote:
> 
> > I'm not sure if this an issue or not.  make test
> failed with the  
> > following:
> >
> > 2 errors out of 40872 tests
> > Failures on these tests: rollback-2.3 tkt3457-1.4
> > All memory allocations freed - no leaks
> > Memory used:         
> now          0  max 
>    102680  max-size 
>    
> > 2800336
> > Page-cache used:      now   
>       0  max     
>    13  max-size     
>   
> > 4096
> > Page-cache overflow:  now     
>     0  max    3071416
> > Scratch memory used:  now     
>     0  max         
> 0
> > Scratch overflow:     now 
>         0  max     
> 33296  max-size       
> > 33296
> > Maximum memory usage: 102680 bytes
> > Current memory usage: 0 bytes
> > Number of malloc()  : -1 calls
> > make: *** [test] Error 1
> 
> Both tests use Tcl to access a journal file in the
> file-system.
> What additional information is found in the body of the
> test
> log? (search for the strings "rollback-2.3" and
> "tkt3457-1.4").
> 
> Dan.
> 
> 

rollback-2.1... Ok
rollback-2.2... Ok
rollback-2.3...
Expected: [0]
 Got: [1]
rollback-2.4... Ok
Memory used:  now 16  max2775504  max-size 10
Page-cache used:  now  0  max 13  max-size   4096
Page-cache overflow:  now  0  max2643024
Scratch memory used:  now  0  max  0
Scratch overflow: now  0  max  33296  max-size  33296


tkt3457-1.1... Ok
tkt3457-1.2... Ok
tkt3457-1.3... Ok
tkt3457-1.4...
Expected: [1 {unable to open database file}]
 Got: [0 {1 2 3 4 5 6}]
tkt3457-1.5... Ok
Memory used:  now 16  max3874864  max-size102
Page-cache used:  now  0  max 13  max-size   4096
Page-cache overflow:  now  0  max3071416
Scratch memory used:  now  0  max  0
Scratch overflow: now  0  max  33296  max-size  33296

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

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Angus March]

Matt Sergeant wrote:
> On Mon, 17 Aug 2009 10:47:23 -0400, Angus March wrote:
>   
>>> Because yes, that's what synchronous=OFF means. It stops SQLite from 
>>> issuing fflush calls (effectively).
>>>   
>>>   
>> Right, and this is implied by the documentation, but I was concerned
>> that the documentation might be playing fast and loose, saying that
>> fflush (or fsync, or fdatasync) won't be called, when it really means
>> that it won't be called during any call to step() or finalize(), while
>> it would be called when the session is closed. I wasn't sure, so I
>> thought I'd ask, because it'll matter to my app.
>> 
>
> Kernels will fflush when a file handle is closed, which will happen 
> when you close the database handle.
>   

Actually, looking at the man pages for fflush just the user-space
buffers are flushed, and not the write-behind buffer in the kernel. If
that's all SQLite does, that's ok.
So again, if anyone knows that synchronous=OFF means that SQLite
will not *deliberately* flush the kernel's write-behind cache *at any
time* for the *rest of the session*, please let me know.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Matt Sergeant]

On Mon, 17 Aug 2009 10:47:23 -0400, Angus March wrote:
>> Because yes, that's what synchronous=OFF means. It stops SQLite from 
>> issuing fflush calls (effectively).
>>   
> Right, and this is implied by the documentation, but I was concerned
> that the documentation might be playing fast and loose, saying that
> fflush (or fsync, or fdatasync) won't be called, when it really means
> that it won't be called during any call to step() or finalize(), while
> it would be called when the session is closed. I wasn't sure, so I
> thought I'd ask, because it'll matter to my app.

Kernels will fflush when a file handle is closed, which will happen 
when you close the database handle.

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Angus March]

Matt Sergeant wrote:
> On Fri, 14 Aug 2009 12:33:30 -0400, Angus March wrote:
>   
>> I want my INSERT done right away, I just don't want it to be flushed
>> from the filesystem's write-behind cache until the kernel decides, not
>> when SQLite decides.
>> 
>
> Did you mean you do "want it to be flushed from the filesystem's 
> write-behind cache when the kernel decides (rather than when SQLite 
> decides)"?
>   

That is one implication, yes.

> Because yes, that's what synchronous=OFF means. It stops SQLite from 
> issuing fflush calls (effectively).
>   
Right, and this is implied by the documentation, but I was concerned
that the documentation might be playing fast and loose, saying that
fflush (or fsync, or fdatasync) won't be called, when it really means
that it won't be called during any call to step() or finalize(), while
it would be called when the session is closed. I wasn't sure, so I
thought I'd ask, because it'll matter to my app.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Shane Harrelson]

On Mon, Aug 17, 2009 at 3:15 AM, Dan Kennedy  wrote:

> >> The INDEXED BY feature was introduced to address concerns that SQLite
> >> might
> >> suddenly start using a different plan for a query in the field than
> >> it
> >> did
> >> in the office during testing. Either because somebody ran ANALYZE, or
> >> because
> >> the SQLite version was upgraded. In this situation, some users
> >> consider it
> >> better to throw an exception than to run the query with a different,
> >> possibly
> >> slower, plan.
> >
> > Confusion reigns supreme. Your second last paragraph says (about your
> > last scenario) that it uses index i1 instead of the apparently better
> > index i2 -- no exception throwing. Your last paragraph indicates
> > that in
> > this case an exception would be thrown.
>
> I guess I got that wrong then. Said users considered it better to throw
> an error if the index that the author of the SQL query expected it to
> use had been removed or radically altered.
>
> Dan.
>


Sorry for muddying the waters.  I was just trying to make clear that INDEXED
BY isn't intended to be used as a tuning mechanism for index selection.  My
statement should have been clearer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with sqlite3_column_origin_name and AS Clause

[Igor Tandetnik]

Dinu Scheppelmann (DAISY) wrote:
> Unfortunately when I get the column names by function
> sqlite3_column_origin_name(), the result columns have the names
> "DokId", "Name" and "Name" again - instead of "Id", "PatientName" and
> "Name"!!

You want sqlite3_column_name

Igor Tandetnik 



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

Re: [sqlite] Functions to convert dates with non-English month name?

[Igor Tandetnik]

Gilles Ganault wrote:
> Before I go ahead and write a script to loop through all the rows, I
> was wondering if SQLite supports functions to convert DD MM  into
> the MySQL-friendly -MM-DD, and whether those functions are
> localized so that it understands month names in languages other than
> English?

SQLite supports such functions in the sense that you can write a custom 
function that does anything you want, and use it in your statements.

> Here's an example:
>
> SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
> 26 Mai 2007|17 Août 2009 - 09h20
>
> I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
> respectively.

If you need to do it once, you can do something like

update membres set dateinscription=
substr(dateinscription, -4) || '-' ||
(case substr(dateinscription, 4, length(dateinscription) - 8)
 when 'January' then '01' when 'February' then '02' ...
 when 'December' then '12' end) || '-' ||
substr(dateinscription, 1, 2);

Substitute correct month names. Converting dateconnexion is left as an 
exercise for the reader.

Igor Tandetnik 



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

Re: [sqlite] debugging sqlite with gdb

[Miroslav Zagorac]

Mohammad Reaz Uddin wrote:
> I downloaded 'sqlite-amalgamation-3.6.16.tar.gz' and used makefile to
> compile it.
> 

sqlite is a shell script, wrapper to a compiled binary in .libs directory.

-- 
Zaga

You have worked and not worked.  Not working is the hardest work of all.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Duplicates] How to keep only one row?

[Martin Engelschalk]

Hi,

If you are looking for a delete - command, then you have to decide which 
of the duplicate rows you want to keep. Are they all the same even in 
the other fields?

Perhaps you want to do something like

delete from members where exists (select rowid from members m2 where 
m2.name = members .name and m2.rowid < members .rowid)

This statement deletes all rows where there exists a row with the same 
name and a lowe rowid. Rowid is an internal field of sqlite.

Martin

Gilles Ganault wrote:
> Hello
>
> I have a table that has a lot of duplicates in the Name column. I'd
> like to only keep one row for each.
>
> The following lists the duplicates, but I don't know how to delete the
> duplicates and just keep one:
>
> SELECT name FROM members GROUP BY name HAVING COUNT(*) > 1;
>
> Thank you.
>
> ___
> 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] Functions to convert dates with non-English month name?

[Gilles Ganault]

Hello,

Before I go ahead and write a script to loop through all the rows, I
was wondering if SQLite supports functions to convert DD MM  into
the MySQL-friendly -MM-DD, and whether those functions are
localized so that it understands month names in languages other than
English?

Here's an example:

SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
26 Mai 2007|17 Août 2009 - 09h20

I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
respectively.

Thank you.

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

[sqlite] Problem with sqlite3_column_origin_name and AS Clause

[Dinu Scheppelmann (DAISY)]

Hello to all SQLite people
(that's my first post - please be patient:-))

I already searched the mailings from 2008/2009 but could not find a post
that describes this problem.

I have a select over a few tables, and since many tables use identical names
for some columns (like "Id"), I rename the column using AS clause:

SELECT Dokument.DokId AS Id, Patient.Name AS PatientName, Einsender.Name
FROM Dokument, Patient, Einsender
WHERE Dokument.AkteId = Patient.Id
AND Dokument.SenderId = Einsender.Id;

Unfortunately when I get the column names by function
sqlite3_column_origin_name(), the result columns have the names "DokId",
"Name" and "Name" again - instead of "Id", "PatientName" and "Name"!!

For me this is severe because I pass back the whole result set of a query as
one nice "object" and the column names have to be unique! (hope you
understand what I'm saying)

Is it possible to fix this "bug" or is there a workaround? (a VIEW does not
help either)

Thanks for any help!
Dinu
==
DAISY Archivierungssysteme GmbH
Preysingstr. 2
68526 Ladenburg
www.adakta.de

Tel: +49-6203-40400
Fax: +49-6203-404020
Mail: dinu.scheppelm...@adakta.de

HRB: 432841 Amtsgericht Mannheim
Geschäftsführer: Dr. Dinu Scheppelmann
==

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

[sqlite] querying r-trees

[Martin Pfeifle]

Dear all,

I would like to discuss a new feature in the SQLite R-tree which is not very 
difficult to implement but would improve
query performance a lot for use cases where the MBR (minimum bounding 
rectangle) of the query object leads to
a too large candidate set.

First of all the data structure in the database can stay at it is, also the 
R*-tree splitting algorithm.

The only thing I would like to see is that it is possible to register a 
function at the R-tree module 
which returns true or false and which gets as parameters the currently to be 
evaluated R*-tree rectangle and a void pointer 
representing the query object. Thus it would be possible to evaluate more 
complex query geometries during R-tree traversal.

The disadvantage of the approach is that the intersection tests between more 
complex query objects and an r-tree rectangle is more time consuming
(higher cpu cost for one test) but on the other hand the resulting candidate 
set would be much smaller (smaller i/o cost and less cpu-intersection tests).

The current approach could also be modelled this way, if the testing simply 
compares two rectangles and returns true if they intersect.

So the question is whether the intersection testing between query object and 
R-tree bounding rectangle 
cannot be passed to the r-tree module as user-defined  function and then be 
applied by the r-tree module during tree traversal.

Any thoughts on this?

Best Martin


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

[sqlite] [Duplicates] How to keep only one row?

[Gilles Ganault]

Hello

I have a table that has a lot of duplicates in the Name column. I'd
like to only keep one row for each.

The following lists the duplicates, but I don't know how to delete the
duplicates and just keep one:

SELECT name FROM members GROUP BY name HAVING COUNT(*) > 1;

Thank you.

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

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Dan Kennedy]

>> The INDEXED BY feature was introduced to address concerns that SQLite
>> might
>> suddenly start using a different plan for a query in the field than  
>> it
>> did
>> in the office during testing. Either because somebody ran ANALYZE, or
>> because
>> the SQLite version was upgraded. In this situation, some users
>> consider it
>> better to throw an exception than to run the query with a different,
>> possibly
>> slower, plan.
>
> Confusion reigns supreme. Your second last paragraph says (about your
> last scenario) that it uses index i1 instead of the apparently better
> index i2 -- no exception throwing. Your last paragraph indicates  
> that in
> this case an exception would be thrown.

I guess I got that wrong then. Said users considered it better to throw
an error if the index that the author of the SQL query expected it to
use had been removed or radically altered.

Dan.


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

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[John Machin]

On 17/08/2009 2:37 PM, Dan Kennedy wrote:
> On Aug 17, 2009, at 11:05 AM, John Machin wrote:
> 
>> On 17/08/2009 11:41 AM, Shane Harrelson wrote:
>>> INDEXED BY doesn't allow you to specify which index to use.  It  
>>> just causes
>>> the query to fail if SQLite thinks it should use an index different  
>>> then the
>>> one specified by the INDEXED BY clause.
>> Oh. The docs say "If index-name does not exist or cannot be used for  
>> the
>> query, then the preparation of the SQL statement fails." Please  
>> consider
>>  submitting a docs bug report (with evidence).
> 
> The two statements are not incompatible.

AFAICT, NONE of the scenarios you outline below fit Shane's statement 
"It just causes the query to fail if SQLite thinks it should use an 
index different then the one specified by the INDEXED BY clause."

For a scenario to match that statement, there must be at least TWO 
indexes on the table.

Only one scenario has two indexes. In that case, whether SQLite "thinks" 
(looks at the analyze results) or not, you say that the result is that 
it goes with the index in the INDEXED BY clause ... so your description 
of that scenario doesn't match Shane's statement at all.

On the other hand, all your scenarios match the above doc excerpt -- 
provided of course that the "use" is interpreted as "use 
beneficially/advantageously".

> 
> What happens in the code is that for a table with an "INDEXED BY" clause
> attached, SQLite does not consider any other indexes or a linear scan  
> when
> planning a query.

"SQLite does not consider any other indexes" is NOT compatible with 
Shane's "SQLite thinks it should use an index different ...".

> SQLite will not do a full scan of an index (unless  
> this
> helps with an ORDER BY clause). If this means no valid plan is found,  
> query
> compilation fails.
> 
> So if you try this:
> 
>CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a);
>SELECT * FROM t1 INDEXED BY i1;
> 
> Then the SELECT statement fails (to prepare) as SQLite cannot find a  
> plan
> where it can use index i1 (advantageously).

Yes, indeed, that select statement constitutes a user bug; it is a 
nonsense, quite irrespective of the presence/absence of i1 or any other 
index, creating/deleting ANALYZE results, or SQLite version changes.

  But if you do this:
> 
>SELECT * FROM t1 INDEXED BY i1 ORDER BY a;
> 
> Then this will prepare and run fine. The following will also work:
> 
>CREATE INDEX i2 ON t1(b);
>SELECT * FROM t1 INDEXED BY t1 WHERE b=10 ORDER BY a;
> 
> In this case, depending on the stats collected by any ANALYZE command,  
> SQLite
> will normally use index i2 to optimize the b=10 constraint. But with the
> INDEXED BY, it uses index i1 to optimize the ORDER BY instead.  
> Presumably the
> user knows something about the contents of table t1 that has allowed  
> her to
> conclude that using index i1 will be more efficient in this case.
> 
> The INDEXED BY feature was introduced to address concerns that SQLite  
> might
> suddenly start using a different plan for a query in the field than it  
> did
> in the office during testing. Either because somebody ran ANALYZE, or  
> because
> the SQLite version was upgraded. In this situation, some users  
> consider it
> better to throw an exception than to run the query with a different,  
> possibly
> slower, plan.

Confusion reigns supreme. Your second last paragraph says (about your 
last scenario) that it uses index i1 instead of the apparently better 
index i2 -- no exception throwing. Your last paragraph indicates that in 
this case an exception would be thrown.

The docs are likewise confused -- after the early piece that I quoted 
(which supports using the INDEXED BY index unless it has vanished or 
would result in a full scan), we find this "The intent of the INDEXED BY 
clause is to raise a run-time error if a schema change, such as dropping 
or creating an index, causes the query plan for a time-sensitive query 
to change. The INDEXED BY clause is designed to help detect undesirable 
query plan changes during regression testing."

SUMMARY:
Three worries with "INDEXED BY i1":

(1) i1 doesn't exist => no argument, raise an exception.

(2) i1 cannot be used advantageously e.g. there is nothing in a WHERE 
clause or ORDER BY clause that fits i1 => no argument, raise an exception.

(3) i1 exists and could be used (as in better than no index at all) but 
there is another index i2 that looks even better => option (a) use i1; 
(b) raise an exception

IMHO whichever of (a) and (b) actually happens, some clarification in 
the docs might be useful.

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