[sqlite] FTS3 IGNORE OR REPLACE????

[sorka]

Is there any way to have an intsert into an FTS3 table ignore a row if the
ROWID being inserted already exists? This is turning out to be quite
troublesome because I'm inserting thousands of records where just a few like
3 or 4 will have the same rowid as existing records. However, to do the test
prior to insertion to delete the duplicates first is quite expensive. It
would be much better if the FTS3 insertion routine had the option of
ignoring rather than failing on the constraint.
-- 
View this message in context: 
http://old.nabble.com/FTS3-IGNORE-OR-REPLACE-tp26191125p26191125.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] Ideal way to check whether a table has a specific column

[David Bicking]

You could try to prepare a statement like "SELECT YourColumnName FROM 
YourTable;" If prepare returns an error, then likely the error message will say 
the column doesn't exist. If no error, it does exist. Such a simple query 
shouldn't take long to prepare. Probably less time then to grab the results of 
the pragma and test the strings therein.

David

--- On Tue, 11/3/09, Shaun Seckman (Firaxis)  wrote:

> From: Shaun Seckman (Firaxis) 
> Subject: [sqlite] Ideal way to check whether a table has a specific column
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, November 3, 2009, 4:25 PM
> Hello all,
> 
>                 I
> just wanted to find out whether or not this is the
> most ideal method for determining if a column exists in a
> table.  My
> current technique is to do execute "pragma
> table_info(tableName)" then
> step through the results and perform a string comparison
> against the
> "name" column until I hit a match or I've finished stepping
> through the
> record set. 
> 
>  
> 
> Is there a better way?
> 
>  
> 
> Shaun Seckman
> 
> Firaxis Games
> Programmer
> 
>  
> 
> ___
> 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] Table within a table??

[John Crenshaw]

> Jay A. Kreibich wrote:
>> On Tue, Nov 03, 2009 at 02:51:23AM -0800, CityDev scratched on the
wall:
>>> That just seems so contrary to the original idea of the relational
>>> model that you shouldn't have any data whose meaning is not defined
>>> by data (in the case of an array you need to understand the
>>> significance of relative position - remember relations have no row
>>> or column order to stop you playing that game). 
>
> So what is a character string then?  An *ordered* sequence of
characters.  And 
> yet this coexists just fine with the relational model.  An "array" is
just a 
> generalization of this concept.

Except that the characters in a string lose all meaning when used
individually. Arrays on the other hand, while ordered, generally hold
sequences of data such that each element has substantial meaning
individually, and may need to be queried against. In fact, even storing
strings tends to cause problems, because often people want to query only
a slice of a string and this often requires a full table scan.

If you absolutely must store arrays, you can do this by storing them in
blobs. If you need to query against the array elements, use a custom
function (though, if you need to query against the elements in the
array, you should REALLY be using a relational table.)

As far as order goes, it is reasonably easy to order the rows using a
field for that purpose.

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

Re: [sqlite] Getting older version from website

[Edward Diener]

D. Richard Hipp wrote:
> On Nov 3, 2009, at 9:34 AM, Edward Diener wrote:
> 
>> Is it possible to retrieve an older version of sqlite from the web  
>> site
>> rather than the latest version ?
> 
> 
> http://www.sqlite.org/download.html#cvs

I have no idea how to find a release from that page. I go to a Fossil 
repository at http://www2.sqlite.org/src/timeline. I am looking for 
3.4.2 release. How am I supposed to find it ?

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

Re: [sqlite] Getting older version from website

[D. Richard Hipp]

On Nov 3, 2009, at 9:34 AM, Edward Diener wrote:

> Is it possible to retrieve an older version of sqlite from the web  
> site
> rather than the latest version ?


http://www.sqlite.org/download.html#cvs

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] User-defined infix functions

[John Crenshaw]

In your specific example you could simply define a custom "LIKE"
function, and LIKE could become Unicode aware without any goofy new
operators.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jean-Christophe
Deschamps
Sent: Tuesday, November 03, 2009 7:51 PM
To: SQLite mailing list
Subject: [sqlite] User-defined infix functions

Is it currently possible to specify that a user-defined function is of 
type infix, using the extension framework?

It would be really easier to use, say a Unicode-aware LIKE named LIKEU 
under the infix form:
 ... test LIKEU pattern ...
than
 ... LIKEU(pattern, test) ...

Also converting existing statements from, for instance, the native LIKE 
to the new LIKEU would be _so_ easier as well!

Would it be possible to have this feature someday, possibly as an 
optional parameter to the registering interface, or would it require 
too much deep surgery in the parser guts?



___
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] Table within a table??

[Darren Duncan]

Jay A. Kreibich wrote:
> On Tue, Nov 03, 2009 at 02:51:23AM -0800, CityDev scratched on the wall:
>> That just seems so contrary to the original idea of the relational
>> model that you shouldn't have any data whose meaning is not defined
>> by data (in the case of an array you need to understand the
>> significance of relative position - remember relations have no row
>> or column order to stop you playing that game). 

So what is a character string then?  An *ordered* sequence of characters.  And 
yet this coexists just fine with the relational model.  An "array" is just a 
generalization of this concept.

>   Yes, EXACTLY.
> 
>   And so it is for nearly any other compound datatype.
> 
>   Arrays have implied ordering.  You can't JOIN against an array
>   without extracting meta-data like that.  Even if you do that, you
>   have to pair that data together and carry it through any manipulation.
>   If the data gets separated or lost, both sets of data becomes
>   worthless.  Not Relational.  Same is true of any other container
>   with an inherent ordering or listing (which is most of them).

I see no problem supporting arrays in the relational model because you can 
define an array in terms of a relation.  For example, an array can be any 
relation that has an explicit attribute, say call it "index", which defines the 
conceptual order of the elements, which are defined using the remaining 
relation 
attributes.  Being that arrays *are* relations, you can use all the relational 
operators on them.  And so an array-valued attribute *is* an RVA.

Of course, you'll want to be careful in how you use the array-relations so that 
you keep the index-defining attribute where you need to.  But this is the same 
as with any other important attributes, such as person name or person id or SIN 
whatever you need in the context.

(Similarly, you can implement a bag/multiset over a relation by just having an 
explicit attribute to count instances, say call it "count".)

Using the above methodology, it is perfectly valid to have an "order by" in the 
relational model; the result of an order-by operation is a relation that is 
like 
its main input relation but with an extra indexing attribute added.  Note that 
the RANK operator one can find in math or SQL is essentially the same thing. 
And "limit" is just a slice/restriction/semijoin on a relation with an ordering 
attribute, that filters on the value of the indexing attribute being in a 
certain range.

Such is one way that my Muldis D language supports the actual relational model 
while at the same time supporting all the useful things programmers want to do, 
and that SQL can do.

>> So I still go back to point i) - you don't need to do this.
> 
>   I hope not, as my main point was that I feel they aren't allowed
>   under the Relational Model.  I agree with your thoughts, I just think
>   there is a more rigid way of showing it to be true.
> 
>   But that doesn't mean people don't want them.

Indeed.  There is a lot of data where it makes sense to be ordered.  But then, 
doing it properly in the relational model is encoding that desire as *data*, 
such as my above example does.

>   The point you're getting at is still valid, however.  One aspect of
>   the Model is that a system can *physically* store the data in all
>   kinds of interesting ways because all the transforms are 100%
>   reversible.  You don't really need to store the table, as defined, in
>   any physical way as long as you can compute it from the data you've
>   stored.  RVAs, as a data modeling idea, fit right into that because
>   the only real difference between an RVA and a non-RVA is how you
>   write the data out on the display.

Absolutely, and that's one of the best features of the relational model.  That 
it gives users a lot of flexibility while implementations have a lot of 
flexibility to optimize behind the scenes, while they can be confident this 
optimization won't change the results users get.  Not so much with SQL.

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

[sqlite] Ideal way to check whether a table has a specific column

[Shaun Seckman (Firaxis)]

Hello all,

I just wanted to find out whether or not this is the
most ideal method for determining if a column exists in a table.  My
current technique is to do execute "pragma table_info(tableName)" then
step through the results and perform a string comparison against the
"name" column until I hit a match or I've finished stepping through the
record set. 

 

Is there a better way?

 

Shaun Seckman

Firaxis Games
Programmer

 

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

[sqlite] triggers revisited

[Victor Mayevski]

Ok, I will rephrase my question although it appears that the answer is
has been already preconceived. I am writing an abstraction layer to
Sqlite where tables, views, triggers etc are presented as XOTcl
Classes/Objects. If two different scripts access the database and one
of them creates a new table, the other has no way of knowing about it
unless it does some kind of polling periodically. That new table
should appear as an XOTcl object in the running script. Well, the
script that creates the table will also create the object. The other
script has to do the polling unless it is possible to do it some other
way.
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] index optimal?

[Sylvain Pointeau]

Hi,

many thanks for your answer,

I just think that it could be useful to know if an index is optimal or not,
kind of a tool that could give you the best index for speed up a query.

Many thanks,
Sylvain

On Tue, Nov 3, 2009 at 2:52 PM, Griggs, Donald  wrote:

> Hi Sylvain,
>
>
> Regarding: can you describe an output of a "explain"?
>
> ==> A very short answer could be that, in the output of   EXPLAIN QUERY
> PLAN
>   "If you see the name of an index, then that index is used."
>
>
> Regarding:  is there something to tell us the best index to have for a
> specified query?
>
> ==> Your question might be interpreted as:
>   a) Given the indexes defined in my schema, which ones are ideal to
> use for a specific query?
> Or
>   b) How do I create ideal indexes?
>
> For each of these questions, the answer is "Both skill and craft are
> sometimes involved, therefore there will often not be an absolute rule."
>
> For question "a" (which to use), sqlite itself tries to determine this,
> and often does a very good job.  In some cases, the ANALYZE command
> helps sqlite make these decisions.
>
> For question "b" (how to create ideal indices) I am definitely not an
> expert, but I think some general guidance might be:
>   -- For a given SELECT, sqlite will use, at most, one index per table.
>   -- An index on a large table is usually more useful than an index on
> a tiny one.
>   -- An index on a column with many repeated values (low specificity)
> may be less useful.
>   -- Compound indexes are sometimes used to good effect, but remember
> that they are used from left to right
>   -- Your own testing in your particular database will give the most
> authoritative answers.
>   -- This list is for sqlite, but I suspect you'll want to seek out
> general SQL books and other resources.
>
> Maybe this helps,
>Donald
>
>
>
> ___
> 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] Ideal way to check whether a table has a specific column

[Adam DeVita]

Another way

SELECT name, sql FROM sqlite_master WHERE type='table' and name ='yourtable
name'

the field sql will give you the full table structure as a string.  Parse for
your desired table name.

No statements fail.




On Tue, Nov 3, 2009 at 4:37 PM, Igor Tandetnik  wrote:

> Shaun Seckman (Firaxis)
>  wrote:
> >I just wanted to find out whether or not this is the
> > most ideal method for determining if a column exists in a table.  My
> > current technique is to do execute "pragma table_info(tableName)" then
> > step through the results and perform a string comparison against the
> > "name" column until I hit a match or I've finished stepping through
> > the record set.
> >
> > Is there a better way?
>
> I guess you could just prepare a statement "select mycolumn from mytable;"
> (you don't need to actually run it). If the column doesn't exist, prepare
> will fail.
>
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ideal way to check whether a table has a specific column

[Igor Tandetnik]

Shaun Seckman (Firaxis)
 wrote: 
>I just wanted to find out whether or not this is the
> most ideal method for determining if a column exists in a table.  My
> current technique is to do execute "pragma table_info(tableName)" then
> step through the results and perform a string comparison against the
> "name" column until I hit a match or I've finished stepping through
> the record set.
> 
> Is there a better way?

I guess you could just prepare a statement "select mycolumn from mytable;" (you 
don't need to actually run it). If the column doesn't exist, prepare will fail.

Igor Tandetnik


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

Re: [sqlite] triggers revisited

[Simon Slavin]

On 3 Nov 2009, at 7:23pm, Victor Mayevski wrote:

> Ok, I will rephrase my question although it appears that the answer is
> has been already preconceived. I am writing an abstraction layer to
> Sqlite where tables, views, triggers etc are presented as XOTcl
> Classes/Objects. If two different scripts access the database and one
> of them creates a new table, the other has no way of knowing about it
> unless it does some kind of polling periodically. That new table
> should appear as an XOTcl object in the running script.

The simplest thing is to poll PRAGMA schema_version.  This will change  
if a TABLE/INDEX/TRIGGER is created/deleted.  Only if PRAGMA  
schema_version changes is it worth looking in detail to see what was  
done.



>  Well, the
> script that creates the table will also create the object.

The script that creates the table might be a different program,  
written in a language you don't know, that just happens to be running  
at the same time as your script.  Either you should be doing intra- 
process communication, in which case you don't need to use SQLite for  
communication, or you need to know about schema changes made by all  
programs, in which case you have to write some code that reacts to  
changes, in which case you might as well use that code in all your  
processes, whether it's the process that made the change or not.

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

Re: [sqlite] Table within a table??

[Jay A. Kreibich]

On Tue, Nov 03, 2009 at 02:51:23AM -0800, CityDev scratched on the wall:
> 
> Whilst it's true that SQL isn't essential for a relational database

  More to the point, any database that supports SQL does not, and
  cannot, support the data typing and data manipulation rules set out
  by the Relational Model.

  The Relational Model defines a set of structure and manipulation
  rules.  It is a mathematical system.  It does not define any kind
  of query language or symbolic expression system-- that is outside
  of the scope of the Model.  In fact, almost by definition a query
  language has to be outside of the scope of the Model in order to be
  practical (for example, some kind of ORDER BY option in a query).

  The SQL standard defines not only the language syntax, but also
  defines it's own data typing and data manipulation rules.  It defines
  it's own mathematical system-- kind of.  The data manipulation
  environment defined by SQL is not really rigid or self-consistent
  enough to call a "mathematical system."  The SQL system is strongly
  based off the Relational Model, but it is fundamentally incompatible
  in many small but important ways.

  So even if we ignore "SQL the language", what we have left in "SQL
  the data manipulation environment" is not the Relational Model.
  Siblings, perhaps, but not the same.

  So when you said, "SQL is a first-order language so it can't easily
  handle substructure," as a point of argument, my feeling is that
  A) that might be true, B) but it doesn't matter, C) and it really
  doesn't matter.

  A) Sure, why not.  SQL can't easily handle a lot of the things it
  tries to do.  But since the computational environment is not defined
  by the language, it B) doesn't really matter.  Just because my fancy
  calculator doesn't have a way to express and generate 4D graphs 
  doesn't mean 4D geometry doesn't exist as a full and mature system,
  and isn't useful for somethings.  It only means I need a better
  calculator (with a *way* better display...) or I need to do it "by
  hand" using the rules of the environment directly.
  
  But, C) it really doesn't matter, because the context of the discussion
  was the Relational Model, and you can't really make Relational Model
  arguments when thinking in SQL.  Like one of my computational
  professors used to say, "When you argue in English, you're wrong."
  It's kind of the same thing.  You can't really make Relational Model
  arguments in SQL.

> I'm interested in your remark that relational databases now cope with
> 'arrays'.

  Didn't say that.  I said several SQL databases do.  
  
  Although looking through some documentation, it looks like that
  number is a bit less than I first thought.

> That just seems so contrary to the original idea of the relational
> model that you shouldn't have any data whose meaning is not defined
> by data (in the case of an array you need to understand the
> significance of relative position - remember relations have no row
> or column order to stop you playing that game). 

  Yes, EXACTLY.

  And so it is for nearly any other compound datatype.

  Arrays have implied ordering.  You can't JOIN against an array
  without extracting meta-data like that.  Even if you do that, you
  have to pair that data together and carry it through any manipulation.
  If the data gets separated or lost, both sets of data becomes
  worthless.  Not Relational.  Same is true of any other container
  with an inherent ordering or listing (which is most of them).

  What about sets?  The Relational Model has a lot of set theory in it,
  but it isn't "pure" set theory.  For example, many "true" set
  operations will result in sets of sets.  Those operations in the
  Relational Model (applied to the sub-sections of relations) will
  always be "flattened" to just sets of elements.  So sets don't work
  within the Relational Model unless you modify their behavior.

  If you do that, you no longer have "real" sets.  You basically
  have... a relation.  Or something that acts like a relation in pretty
  much every way that counts.  So now you have a RVA, and we know those
  don't count because they can be seamlessly transformed into a non-RVA
  representation without losing data or meaning (or generating
  meta-data, which is what I meant by "meaning").



  And, BTW, SQL does consider table/result-set columns to be ordered.
  One more fundamental difference that causes all kinds of headaches
  for query optimizers, because of issues like technically A NATURAL
  JOIN B is NOT equal to B NATURAL JOIN A.

> So I still go back to point i) - you don't need to do this.

  I hope not, as my main point was that I feel they aren't allowed
  under the Relational Model.  I agree with your thoughts, I just think
  there is a more rigid way of showing it to be true.

  But that doesn't mean people don't want them.

  So why are they there?  Most SQL people aren't all that aware of 
  the theory behind database 

[sqlite] Getting older version from website

[Edward Diener]

Is it possible to retrieve an older version of sqlite from the web site 
rather than the latest version ?

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

[sqlite] Windows 7?

[Adam DeVita]

Good day,

Will a new DLL be required for Windows 7 ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] User-defined infix functions

[Jean-Christophe Deschamps]

´¯¯¯
>In your specific example you could simply define a custom "LIKE"
>function, and LIKE could become Unicode aware without any goofy new
>operators.
`---

Yes of course, but I'm doing so to keep the possibility to use the 
native operator as well.



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

Re: [sqlite] index optimal?

[Simon Slavin]

On 3 Nov 2009, at 7:33pm, Sylvain Pointeau wrote:

> I just think that it could be useful to know if an index is optimal  
> or not,
> kind of a tool that could give you the best index for speed up a  
> query.

Your brain.  A simplified explanation on how to decide what indices  
you need follows.

Each SELECT command uses exactly one INDEX.  For the SELECT to work  
fastest you make sure there's an index which is an ideal helper for  
the SELECT command.  A normal SELECT command (one without special  
things like JOIN and 'group') uses an index for three things:

SELECT rcol1, rcol2, rcol3 FROM table1
 WHERE wcol1<88 AND wcol2>'DEF' and wcol3=208
 ORDER BY ocol1, ocol2 DESC, ocol3

1) To reject all the records your SELECT does not want
2) To retrieve the records your SELECT /does/ want to the right order
3) To save the effort of having to read the row data from the actual  
TABLE

If you are making up an index especially to suite a particular query  
you start considering your variables from the beginning of the index ...

CREATE INDEX tab1_idx1 ON table1 (col1, col2, col3, col4, col5 ...)

The first columns you'll list will be the ones mentioned in your WHERE  
clause.
The next columns you'll list will be the ones used in your ORDER BY  
clause, in the order that that clause mentions them.
You might also list other columns which have values you want to  
return, but there is a payoff in that between database size, the speed  
your SELECT runs at, and the speed your INSERT and UPDATE commands run  
at.

So without knowing anything about the table, or the values in it, for  
the above SELECT I might guess that the best index would be

CREATE INDEX tab1_idx1 ON table1 (wcol1, wcol2, wcol3, ocol1, ocol2,  
ocol3, rcol1, rcol2, rcol3)

but that's purely a guess.  The ocols may be in a poor order.  There  
may be no point in listing the rcols.  In fact there may be no point  
in listing the ocols either.

It's worth noting that although the index is very important in making  
the SELECT command run quickly, careful arrangement of a WHERE clause  
can also make a big difference when the WHERE clause is complicated.   
However, guessing the best arrangement for the WHERE clause cannot be  
done without knowledge of example data in the table: it depends on  
whether the data is clumped in only a few distinct values, or spread  
evenly over a lot of different values.

You can read more about this, although it's not easy to understand  
without experience, here:



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

Re: [sqlite] What sort of programming errors would cause these strange results?

[Beau Wilkinson]

>Is it possible that the library is sorting the CUSTOMER.id list
>alphabetically not numerically, and jumping into the sorted PK list at
>the correct offset but finding the wrong value in that location as a
>result of the improper sort?

That seems plausible. But speaking as a programmer, I do not typically get much 
value from such "armchair" debugging. Sometimes I get bug reports, and from the 
information given I get the sense that a whole workgroup has spent the whole 
morning reverse engineering some problem in my code. It is almost always more 
efficient to just go ahead and report the error.

However, the economics change if you have the source code. You said that the 
library is coming from a "major player." If it is written using Microsoft .NET, 
you probably can decompile the source code very effectively using Lutz Roeder's 
.NET Reflector, which is shareware (maybe even freeware). Then, you will no 
longer be faced with a "black box" and you might even be able to extract the 
code and recompile it with the sorting bug patched.



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Tim Romano [tim.rom...@yahoo.com]
Sent: Tuesday, November 03, 2009 5:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] What sort of programming errors would cause these strange 
results?

I have a SQLite database working quite nicely. The three main tables
have 4 million rows and 275,000 rows respectively, and query response
times are excellent: I have used several GUI front ends to query the
data, and they jibe with each other, both with regard to the (accurate)
results they return and the time they take. Very happy with SQLite. But
all is not rosy.

I recently wrote a little app using a library I hadn't worked with
before (source code for it not available -- it's a black box) and this
library is making some very basic mistakes with the data. Their results
are always wrong. There is something fundamentally awry in the library code.

I will describe two basic problems, and maybe those two points will
create a line pointing to the problem. I hope the combined knowledge and
intuition of this group will allow me to suggest where the authors of
this library might look for bugs in their code. They are a major player.
Their library and only their library is having problems with my data.
Three other tools for SQLite (Razor, Maestro, .NET provider by Robert
Simpson) are returning correct results.


Bug #1
In a select involving a simple equijoin.
NOTE: ORDERS.custid and CUSTOMER.id are integer

select ORDERS.id, CUSTOMER.name
from ORDERS inner join CUSTOMER on ORDERS.custid = CUSTOMER.id
where ORDERS.ordertype in ('a','b','c')

The correct rows from ORDERS are being returned;  the WHERE ordertype
IN(...) condition is working.
But the CUSTOMER.name values are all wrong!
When I examine the rows in CUSTOMER that hold the (wrong) names returned
by the query,  I see that the PK of those rows (CUSTOMER.id) would be
found very  near the ORDER.custid value **if these CUSTOMER.id integer
values were being sorted AS TEXT**.  For example

ORDERS.custid...CUSTOMER.id of the wrong customer name returned by
the query
140..1400
160..1600
253025230
276027520

Is it possible that the library is sorting the CUSTOMER.id list
alphabetically not numerically, and jumping into the sorted PK list at
the correct offset but finding the wrong value in that location as a
result of the improper sort?

Bug#2
In a select against a table with 275,000 rows:

select pkcol, textcol
where textcol = 'x'

Some pkcol values are incorrect!  Some are correct.
There's a non-unique index on textcol, which is varchar.

Thanks for the help.









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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table within a table??

[P Kishor]

On Tue, Nov 3, 2009 at 4:51 AM, CityDev  wrote:
> I'm interested in your remark that relational databases now cope with
> 'arrays'. Personally I've never seen that in DB2, Jet or SQLite. That just
> seems so contrary to the original idea of the relational model that you
> shouldn't have any data whose meaning is not defined by data (in the case of
> an array you need to understand the significance of relative position -
> remember relations have no row or column order to stop you playing that
> game).

http://www.postgresql.org/docs/8.2/interactive/arrays.html



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] index optimal?

[Griggs, Donald]

Hi Sylvain,


Regarding: can you describe an output of a "explain"?

==> A very short answer could be that, in the output of   EXPLAIN QUERY
PLAN
   "If you see the name of an index, then that index is used."
   

Regarding:  is there something to tell us the best index to have for a
specified query?

==> Your question might be interpreted as:
   a) Given the indexes defined in my schema, which ones are ideal to
use for a specific query?
Or
   b) How do I create ideal indexes?

For each of these questions, the answer is "Both skill and craft are
sometimes involved, therefore there will often not be an absolute rule."

For question "a" (which to use), sqlite itself tries to determine this,
and often does a very good job.  In some cases, the ANALYZE command
helps sqlite make these decisions.

For question "b" (how to create ideal indices) I am definitely not an
expert, but I think some general guidance might be:
   -- For a given SELECT, sqlite will use, at most, one index per table.
   -- An index on a large table is usually more useful than an index on
a tiny one.
   -- An index on a column with many repeated values (low specificity)
may be less useful.
   -- Compound indexes are sometimes used to good effect, but remember
that they are used from left to right 
   -- Your own testing in your particular database will give the most
authoritative answers.
   -- This list is for sqlite, but I suspect you'll want to seek out
general SQL books and other resources.

Maybe this helps,
   Donald



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

[sqlite] Question regarding modified ATTACH command

[WClark]

Hello,

I have a question regarding a modification I've made to the ATTACH 
DATABASE command in Sqlite.  The attached diff file (set against version 
3.6.17) shows the modifications I've made, which I hope can be read quite 
simply.

The modification involves adding an optional "READONLY" keyword; for 
example ATTACH READONLY DATABASE "file.db" AS db, which as it would imply 
attaches the database "read-only" rather than using the open flags used 
when opening the initial database.  It does this by simply clearing the 
SQLITE_OPEN_READWRITE and SQLITE_OPEN_CREATE flags and setting 
SQLITE_OPEN_READONLY if the READONLY keyword is used in the ATTACH 
command.  If the initial database was opened read-only, then the READONLY 
keyword has no effect.

The modification as it stands "seems" to work - by which I mean that the 
test suite that is part of the Sqlite code still all passes, and my 
rudimentary testing also passes.  However, as a question to those much 
more knowledgeable in the internal workings of Sqlite, is this a valid 
thing to do?  Or have I overlooked something that will cause it to fail 
somewhere down the line?

Or... is there an altogether better way, and I should have just read the 
website more carefully?!?

Many thanks!

Will

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

[sqlite] What sort of programming errors would cause these strange results?

[Tim Romano]

I have a SQLite database working quite nicely. The three main tables 
have 4 million rows and 275,000 rows respectively, and query response 
times are excellent: I have used several GUI front ends to query the 
data, and they jibe with each other, both with regard to the (accurate) 
results they return and the time they take. Very happy with SQLite. But 
all is not rosy.

I recently wrote a little app using a library I hadn't worked with 
before (source code for it not available -- it's a black box) and this 
library is making some very basic mistakes with the data. Their results 
are always wrong. There is something fundamentally awry in the library code.

I will describe two basic problems, and maybe those two points will 
create a line pointing to the problem. I hope the combined knowledge and 
intuition of this group will allow me to suggest where the authors of 
this library might look for bugs in their code. They are a major player. 
Their library and only their library is having problems with my data. 
Three other tools for SQLite (Razor, Maestro, .NET provider by Robert 
Simpson) are returning correct results.


Bug #1
In a select involving a simple equijoin.
NOTE: ORDERS.custid and CUSTOMER.id are integer

select ORDERS.id, CUSTOMER.name
from ORDERS inner join CUSTOMER on ORDERS.custid = CUSTOMER.id
where ORDERS.ordertype in ('a','b','c')

The correct rows from ORDERS are being returned;  the WHERE ordertype 
IN(...) condition is working.
But the CUSTOMER.name values are all wrong!
When I examine the rows in CUSTOMER that hold the (wrong) names returned 
by the query,  I see that the PK of those rows (CUSTOMER.id) would be 
found very  near the ORDER.custid value **if these CUSTOMER.id integer 
values were being sorted AS TEXT**.  For example

ORDERS.custid...CUSTOMER.id of the wrong customer name returned by 
the query
140..1400
160..1600
253025230
276027520

Is it possible that the library is sorting the CUSTOMER.id list 
alphabetically not numerically, and jumping into the sorted PK list at 
the correct offset but finding the wrong value in that location as a 
result of the improper sort?

Bug#2
In a select against a table with 275,000 rows:

select pkcol, textcol
where textcol = 'x'

Some pkcol values are incorrect!  Some are correct.
There's a non-unique index on textcol, which is varchar.

Thanks for the help.









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