[sqlite] fts3 column-specific phrase search?

[Russ Cox]

I find myself wanting to do an FTS3 phrase search
restricted to content in a specific column.  The docs
are pretty clear that this doesn't work (column prefixes
only apply to tokens), but I wonder how hard it would
be to make it work.  I'm happy to work on it if I can
be pointed in the right direction.  I found an unanswered
message in the sqlite-users archives from last September
asking for the same functionality and claiming that it had
worked in an earlier version of sqlite (3.5.4.2).

http://www.mail-archive.com/sqlite-users@sqlite.org/msg46548.html

Just to be concrete, this is what I'm trying:

sqlite> select ID, Name from text where text match '"Russ Cox"';
... output ...
sqlite> select ID, Name from text where text match 'Author:Russ';
... output ...
sqlite> select ID, Name from text where text match 'Author:"Russ Cox"';
Error: SQL logic error or missing database
sqlite> select ID, Name from text where text match 'Author:"Russ"';
Error: SQL logic error or missing database
sqlite>

Pointers to where I should look in the sqlite code, or
cautionary tales about why this changed and why it's
really hard to change back?

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

[sqlite] PRIMARY KEY - not unique?

[Steve Hurst]

Hi all, not sure if this is a bug or user error - any insight appreciated!

I have a schema that defines a two column primary key on a table - the docs 
indicate this will create a unique composite key.  However, code running 
"INSERT OR REPLACE INTO ..." many times over a long period of time eventually 
produces multiple rows that have the same primary key - see below.  

SQLite 3.3.6 on RHEL 5.4 from the RHN repo, I have a gzip of the db if it is 
helpful?

Thanks - Steve



- snip --

sqlite> .schema node_attributes
CREATE TABLE node_attributes
   (
   node_id  text,
   attributetext,
   valuetext,
   PRIMARY KEY ( node_id, attribute )
   );
CREATE INDEX node_attribute ON node_attributes (attribute);
sqlite> select count() as onlyone, node_id, attribute from node_attributes 
group by node_id, attribute having onlyone>1;
2|lcdre36821|CONTAINERNODE
336|lcdre36821|CONTAINERNODE
337|lcdre36821|STATE
337|lcdre36889|CONTAINERNODE
338|lcdre36889|STATE
338|r03idpx12|STATE
152|r03idpx14|STATE
679|r06idpx27|STATE
656|r06idpx29|STATE
sqlite> 

- snip --


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

Re: [sqlite] [sqlite-dev] SQLite 3.7.0 coming soon....

[Matthew L. Creech]

On Wed, Jun 30, 2010 at 1:21 PM, D. Richard Hipp  wrote:
> We are in the final phases of development for SQLite version 3.7.0.
> The key enhancement over version 3.6.23.1 is support for the use of
> write-ahead logs for transaction control.  See 
> http://www.sqlite.org/draft/wal.html
>  for additional information on how this might enhance your  use of
> SQLite.
>

For reference, I've got an embedded application (Linux/UBIFS on NAND)
that's write-heavy, and which has been pretty well optimized for
SQlite performance.  I replaced the library with the latest snapshot
and set journal_mode=WAL, no further tweaking of checkpointing or
anything, and log performance improved by ~30%.

Looking forward to 3.7.0!

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

Re: [sqlite] SQLite 3.7.0 coming soon....

[Vivien Malerba]

For what it's worth, I've just tested Libgda with the latest dev.
version of the 3.7.0 and found no problem at all with the NR tests
(note that Libgda uses a lot the virtual tables features).

Thanks a lot for your very good work in SQlite!

Regards,

Vivien

On 30 June 2010 19:21, D. Richard Hipp  wrote:
> We are in the final phases of development for SQLite version 3.7.0.
> The key enhancement over version 3.6.23.1 is support for the use of
> write-ahead logs for transaction control.  See 
> http://www.sqlite.org/draft/wal.html
>  for additional information on how this might enhance your  use of
> SQLite.
>
> The transition from 3.6.23.1 to 3.7.0 is a large one, and we would
> appreciate beta testing.  Recent snapshots of SQLite can be found at 
> http://www.sqlite.org/draft/download.html
>  and those snapshots pass all of our extensive tests.  But testing
> can only prove the existence of bugs, not their absence and so we are
> eager for others to try out the latest code changes, and in particular
> the new write-ahead log feature, and let us know your experiences,
> before we make the next release.
>
> Please provide feedback - positive, negative, or indifferent - to this
> mailing list.
>
> We are currently aiming to release 3.7.0 sometime during July.  Thank
> you in advance for your help in testing this important new release.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] SQLite 3.7.0 coming soon....

[Peter Kolbus]

This looks like an exciting enhancement, and I look forward to testing it out. 
http://www.sqlite.org/draft/c3ref/io_methods.html seems to be missing 
requirements for the xShm functions, though.  Can you update that?

Thanks,
Peter Kolbus


On Jun 30, 2010, at 12:21 PM, "D. Richard Hipp"  wrote:

> We are in the final phases of development for SQLite version 3.7.0.   
> The key enhancement over version 3.6.23.1 is support for the use of  
> write-ahead logs for transaction control.  See 
> http://www.sqlite.org/draft/wal.html 
>  for additional information on how this might enhance your  use of  
> SQLite.
> 
> The transition from 3.6.23.1 to 3.7.0 is a large one, and we would  
> appreciate beta testing.  Recent snapshots of SQLite can be found at 
> http://www.sqlite.org/draft/download.html 
>  and those snapshots pass all of our extensive tests.  But testing  
> can only prove the existence of bugs, not their absence and so we are  
> eager for others to try out the latest code changes, and in particular  
> the new write-ahead log feature, and let us know your experiences,  
> before we make the next release.
> 
> Please provide feedback - positive, negative, or indifferent - to this  
> mailing list.
> 
> We are currently aiming to release 3.7.0 sometime during July.  Thank  
> you in advance for your help in testing this important new release.
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> 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] minor website bug (was Re: SQLite 3.7.0 coming soon....)

[Eric Smith]

> Please provide feedback - positive, negative, or indifferent - to this  
> mailing list.

Minor: the link in about.html from "fopen()" to 
http://man.he.net/man3/fopen is broken.

Eric

--
Eric A. Smith

Mandelbug, n.:
A bug whose underlying causes are so complex and obscure as to
make its behavior appear chaotic or even non-deterministic.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

[Jay A. Kreibich]

On Wed, Jun 30, 2010 at 09:04:14AM -0400, Tim Romano scratched on the wall:

> Notwithstanding your insistence that INT and INTEGER are the same in SQLite,
> *with respect to use in the PRIMARY KEY definition* there are subtle
> differences.

  Yes.  However, CREATE TABLE ... AS SELECT doesn't carry over any
  constraints, including primary key definitions.  You can't get a
  PRIMARY KEY (of any type) out of CREATE TABLE ... AS SELECT, so
  the difference doesn't matter.

  CREATE TABLE ... AS SELECT creates a table from a result set, i.e.
  the output of a SELECT.  Result sets are not tables.  Result sets
  have no constraints.  The CREATE TABLE statement has no idea what
  the original table(s) is, where the data came from, what
  constraints were originally on it, or anything else.  It can only derive
  meaningful column affinities when the SELECT returns a direct column
  reference, and even that is a bit of a slight of hand.

  So the difference between INT and INTEGER doesn't matter, since there
  is no chance of the original PK constraint being defined in the new
  table.  For the purpose of a CREATE TABLE .. AS SELECT, they are
  identical in all respects.
 
> Corollary advice would be to eschew all use of the CREATE TABLE FOO AS
> SELECT syntax because it produces a table with INT PRIMARY KEY
> definition

  No, it doesn't.  It produces a table with an "INT" definition.  No
  "PRIMARY KEY", no "NOT NULL", no "UNIQUE", or any other constraint.
  Result sets do not have keys or constraints.  The result set returned
  by SELECT * FROM FOO is not the same as FOO.

> I mean cross-implementation portability not cross-platform portability.

  If I go and modify the source code so that a ROWID alias is only
  established when ORANGE PRIMARY KEY is used, I (and my customers)
  only have myself to blame.  It is, by definition, incompatible.
  Having the mainline strive for portability is pointless and the
  fix, IMHO, is for the modified code to use a different file header
  so the files simply cannot be moved back and forth.  Once you make
  a modification that deep, then for all intents and purposes it
  becomes a different platform.

> As I said, all hell can break loose because the queries don't break -- they
> simply return the wrong results which on their face may seem plausible and
> could therefore go undetected as erroneous until well after the damage
> (whatever it may be) has been done.

  This is shifting the topic from CREATE TABLE to foreign keys, but I'm
  still not convinced it is that big of a deal.  Foreign keys
  reference a column(s) by name, and that column is not even required to
  be a primary key.  If an "id" column shifts from a INTEGER PRIMARY KEY to
  a INT PRIMARY KEY the foreign key isn't going to care, and will still
  continue to function.

  And this has nothing to do with CREATE TABLE ... AS SELECT, since it
  will never produce a table with either a "PRIMARY KEY" or a "UNIQUE"
  constraint on it, so the created table can never be an FK target.  If
  you try, the database won't even load.



  I still think this gets back to what others have been saying all
  along... CREATE TABLE ... AS SELECT is not meant to clone tables.
  It is primarily used to create "instanced views", and almost always as
  temp tables.  They're especially common in data warehouse data mining
  techniques, where you might run a very complex query to pull out a
  set of central ID values, and then run a report on those IDs.  If the
  report is extensive enough, you might not want the initial query re-run
  each time, so you just do it once, save the results, and use that
  pre-selected list to join.

  In that role CREATE TABLE ... AS SELECT  serves its purpose very
  well producing the expected results.  Your concerns about PKs and
  FKs don't even come into play, as they can't happen-- even within
  the same implementation.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Accessing an sqlite db from two different programs

[Sylvain Pointeau]

to not block all users with updates.

On Tue, Jun 29, 2010 at 6:54 PM, Greg Burd  wrote:

> Why "the smallest update possible"?
>
> -grge
>
> > -Original Message-
> > From: Sylvain Pointeau [mailto:sylvain.point...@gmail.com]
> > Sent: Monday, June 28, 2010 6:36 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Accessing an sqlite db from two different
> > programs
> >
> > absolutely no problem with sqlite.
> > ensure you are doing smallest update as possible.
> > ___
> > 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] Slow query

[Israel Lins Albuquerque]

If your table doesn't have a primary key, this look like your table aren't 
normalized, maybe you can try broke this table in 2 tables like: 

Your definition: 
CREATE TABLE table1(id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year 
INTEGER); 

indexes : index1( name ), index2( id2 ), index3( 
name2 ); 

Will be: 
CREATE TABLE t_names(id INTEGER PRIMARY KEY, name TEXT); 
CREATE INDEX t_name_idx_001 ON (name, id); 

CREATE TABLE t_relation(id1 INTEGER REFERENCES t_name(id), id2 INTEGER 
REFERENCES t_name(id), year INTEGER); 
CREATE INDEX t_relation_idx_001 ON (year, id1, id2); 
CREATE INDEX t_relation_idx_002 ON (id1, id2); 

Where t_relation make the relationship between the 'names' (t_name). 

And the select: 
SELECT id, name, id2, name2, max(year) y 
FROM table1 
GROUP BY id, name2 
ORDER BY name2, y DESC 
LIMIT 0, 15 

Will be: 
SELECT t2.* 
, t3.* 
, MAX(t1.year) AS y 
FROM t_relation AS t1 
JOIN t_name AS t2 ON (t2.id = t1.id1) 
JOIN t_name AS t3 ON (t3.id = t1.id1) 
GROUP BY t2.id, t3.name 
ORDER BY t3.name, y DESC 
LIMIT 0, 15 


And: 
SELECT id, name, id2, name2 
FROM table1 
GROUP BY id 
ORDER BY name2, year DESC 
LIMIT 0,15 

Will be: 
SELECT t2.* 
, t3.* 
FROM t_relation AS t1 
JOIN t_name AS t2 ON (t2.id = t1.id1) 
JOIN t_name AS t3 ON (t3.id = t1.id1) 
GROUP BY t2.id 
ORDER BY t3.name, t1.year DESC 
LIMIT 0, 15 

to export the existing data to the new tables you can do: 
INSERT OR REPLACE INTO t_name 
SELECT DISTINCT * FROM (SELECT id, name FROM table1 
UNION ALL 
SELECT id2, name2 FROM table1); 

INSERT OR REPLACE INTO t_relation 
SELECT id, id2, year FROM table1; 



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

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

[David Bicking]

On Wed, 2010-06-30 at 09:04 -0400, Tim Romano wrote:
> 

> The EXAMPLE: If you create a database in the authoritative version of SQLite
> using INT PRIMARY KEY (rather than INTEGER PRIMARY KEY), when you share the
> database with your Adobe-using affiliate, all hell will break loose. I will
> repeat the example I gave above:
> 
> Let's say you had created this table in authoritative SQLite using INT
> rather than INTEGER PRIMARY KEY:
> 
> CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT)
> insert into FOO(1,'Groucho')
> insert into FOO(2,'Chico')
> Insert into FOO(999, 'Harpo')
> 
> And then you have another table MOVIES where FOOID is a foreign key pointing
> back to FOO.id:
> 
> MOVIES
> fooid|moviename
> 1|Duck Soup
> 2|Duck Soup
> 3|Duck Soup
> 
> 
> ** Note that Harpo's id is 999 in FOO and that table MOVIES contains no such
> foreign key.
> 
> Now your Adobe affiliate executes this query:
> 
> select MOVIES.moviename, FOO.name
> from MOVIES INNER JOIN FOO
> ON FOO.id = MOVIES.fooid
> 
> Unless Adobe has since changed the behavior they told me they were not going
> to change, the query above will produce this result in Adobe:
> 
> Duck Soup|Groucho
> Duck Soup|Chico
> Duck Soup|Harpo
> 
> But Harpo should *not* appear in the resultset. Adobe looks for the row in
> FOO whose rowid =3 and finds Harpo. If Adobe were following authoritative
> SQLite, it would look for the row in FOO whose "ordinary column" id = 3 and
> find no such row.
> 
> 

All things considered, I would say this is clearly and 100% a bug in
Adobe's code. The given SQL nowhere mentioned rowid, yet they are
joining on rowid... that has got to be an error.

And how is Adobe Air doing this? Did they write there own sql parser?
There own VDBE? (Hopefully I got those initials correct.) Are they
calling Sqlite or did they write their own library claiming it is
compatible with the Sqlite file format?

While I don't necessarily disagree with what you are saying, in this
example you appear to be asking the Sqlite developers to emulate an
error in someone else's code.

David

PS. Probably should remove my opinions from this, because the only
reason I am sending this is because I am curious what the hell Adobe is
doing that is causing this confusion.



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

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

[Tim Romano]

Jay,
Let me try to "scratch on the wall" one more time and perhaps my point will
make its way through.

Notwithstanding your insistence that INT and INTEGER are the same in SQLite,
*with respect to use in the PRIMARY KEY definition* there are subtle
differences. In the authoritative version of SQLite, an INTEGER PRIMARY KEY
is an alias for the rowid but an INT PRIMARY KEY (or smallint or any other
flavor of int) is not an alias for the rowid but an "ordinary" column.  From
the docs:

"The special behavior of INTEGER PRIMARY KEY is only available if the type
name is exactly "INTEGER" in any mixture of upper and lower case. Other
integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED
INTEGER" causes the primary key column to behave as an ordinary table column
with integer affinity  and a
unique index, not as an alias for the rowid."
http://www.sqlite.org/lang_createtable.html

I pointed out above that this subtle behavioral difference in the
authoritative version was *not* honored by Adobe in its implementation of
SQLite. The CAVEAT: Adobe's departure from the authoritative version in this
regard may prove to be a pitfall for anyone who may ever need to share a
SQLite database file they have created with someone who is using software
based on the Adobe implementation.

The RECOMMENDATION: My advice for anyone who may find themselves in that
position would be to eschew INT PRIMARY KEY and to use INTEGER PRIMARY KEY.
 Corollary advice would be to eschew all use of the CREATE TABLE FOO AS
SELECT syntax because it produces a table with INT PRIMARY KEY
definition even if the prototype table had INTEGER PRIMARY KEY. (@Puneet:
you see, I am hardly trying to promote the use of CREATE TABLE FOO AS
SELECT.. syntax)

The SIDEBAR: I am not saying that the CREATE TABLE FOO AS SELECT syntax must
do something other than what it is doing now, thought I certainly recommend
the architects consider such a change in light of the potential pitfalls of
 the current behavior in the wider context of "portability" of SQLite
database files.  I  mean cross-implementation portability not cross-platform
portability.

The EXAMPLE: If you create a database in the authoritative version of SQLite
using INT PRIMARY KEY (rather than INTEGER PRIMARY KEY), when you share the
database with your Adobe-using affiliate, all hell will break loose. I will
repeat the example I gave above:

Let's say you had created this table in authoritative SQLite using INT
rather than INTEGER PRIMARY KEY:

CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT)
insert into FOO(1,'Groucho')
insert into FOO(2,'Chico')
Insert into FOO(999, 'Harpo')

And then you have another table MOVIES where FOOID is a foreign key pointing
back to FOO.id:

MOVIES
fooid|moviename
1|Duck Soup
2|Duck Soup
3|Duck Soup


** Note that Harpo's id is 999 in FOO and that table MOVIES contains no such
foreign key.

Now your Adobe affiliate executes this query:

select MOVIES.moviename, FOO.name
from MOVIES INNER JOIN FOO
ON FOO.id = MOVIES.fooid

Unless Adobe has since changed the behavior they told me they were not going
to change, the query above will produce this result in Adobe:

Duck Soup|Groucho
Duck Soup|Chico
Duck Soup|Harpo

But Harpo should *not* appear in the resultset. Adobe looks for the row in
FOO whose rowid =3 and finds Harpo. If Adobe were following authoritative
SQLite, it would look for the row in FOO whose "ordinary column" id = 3 and
find no such row.

As I said, all hell can break loose because the queries don't break -- they
simply return the wrong results which on their face may seem plausible and
could therefore go undetected as erroneous until well after the damage
(whatever it may be) has been done.

CONCLUSION: wherever the SQLite architects find opportunities to tighten up
behaviors in this nexus, the tightening up effort would be well-spent, IMO.
 Which brings me back full circle to the subject line of this posting.

Regards
Tim Romano
Swarthmore PA





On Tue, Jun 29, 2010 at 12:30 PM, Jay A. Kreibich  wrote:

> On Tue, Jun 29, 2010 at 11:46:34AM -0400, Tim Romano scratched on the wall:
>
> > The core concern, at least as I see it, is the undesirable effects of
> > sharing data between implementations that do not handle INT and INTEGER
> > primary keys compatibly.
>
>   The only known program that can read SQLite database files is SQLite.
>  In the SQLite world, INT and INTEGER are the same.  The only way to
>  get the SQL generated by CREATE TABLE ... SELECT back out of an
>  SQLite database is to dump the data into a SQL text file.  If you read
>  that SQL back into SQLite, it will know exactly what to do with it.
>
>  If you read that SQL into any other database, all best are off, and
>  this is the very least of your compatibility concerns.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

[Tim Romano]

Puneet,
I am simply pointing out a potential pitfall.  Putting up a highway sign
that says "Soft Shoulder" is one way to go about things.  Widening the
shoulder and perhaps paving it is another.
Regards
Tim Romano
Swarthmore PA

On Tue, Jun 29, 2010 at 11:56 AM, P Kishor  wrote:

> On Tue, Jun 29, 2010 at 10:46 AM, Tim Romano 
> wrote:
> > Puneet,
> > I am not trying to give Adobe any sort of primacy; but I wouldn't call
> them
> > unimportant either.
> >
> > The core concern, at least as I see it, is the undesirable effects of
> > sharing data between implementations that do not handle INT and INTEGER
> > primary keys compatibly.
> >
>
> There is no incompatibility. CREATE TABLE t AS SELECT ... is not meant
> to clone a table. Period. Done.
>
> Don't depend on it, don't expect it, don't promote it.
>
>
>
>
>
> > I  don't use and won't use the "create table as select ... " syntax, but
> > others might, hence the advisory.
> >
> > Here's a little story:  years ago, back in the days of dumb terminals,
> > oncology patients were dying in abnormally high numbers not long after
> > receiving their radiation treatment. Turned out that the software that
> > controlled the radiation dosage was written to be used with a
> dumb-terminal
> > that did not have cursor-positioning keys. It might have been a VT100. I
> > don't recall. You had to hit RETURN to move from field to field. But the
> > hospital had installed a  "compatible" terminal that did have these
> > cursor-arrow keys.  The hospital technician, ignorant of the
> consequences,
> > were in the habit of using the arrow keys to move the cursor around the
> > radiation dosage and timing screen, and then would key in their values
> and
> > execute the program. The numbers they keyed in were actually hitting the
> > wrong input fields.  The users' eyes told them they were on the dosage
> field
> > but they were actually keying in the value for the time, or vice-versa,
> and
> > the patients were being given lethal doses of radiation.
> >
> > I saw this on 60 minutes or 20/20 or some show like that  -- I didn't
> write
> > that program or install the compatible terminal. But since then, whenever
> I
> > see the opportunity for things going FUBAR, I will say something.
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> >
> >
> >
> >
> >
> >
> > On Tue, Jun 29, 2010 at 11:10 AM, P Kishor  wrote:
> >
> >> On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano 
> >> wrote:
> >> > But there may be an argument for making the cloning more precise.
> >>
> >>
> >> The issue is that CREATE TABLE t AS SELECT... is not meant to clone a
> >> table. Not too long ago I encountered the same issue (search the mail
> >> archives). Igor, as usual, explained it succinctly and effectively --
> >>
> >> sqlite> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> >> sqlite> INSERT INTO t (b) VALUES ('foo');
> >> sqlite> INSERT INTO t (b) VALUES ('bar');
> >> sqlite> INSERT INTO t (b) VALUES ('baz');
> >> sqlite> SELECT * FROM t;
> >> a   b
> >> --  --
> >> 1   foo
> >> 2   bar
> >> 3   baz
> >> sqlite> CREATE TABLE u AS SELECT a + 0.5 AS a, b FROM t;
> >> sqlite> SELECT * FROM u;
> >> a   b
> >> --  --
> >> 1.5 foo
> >> 2.5 bar
> >> 3.5 baz
> >> sqlite> .s
> >> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> >> CREATE TABLE u(a,b TEXT);
> >> sqlite> SELECT Typeof(a) FROM u;
> >> Typeof(a)
> >> --
> >> real
> >> real
> >> real
> >> sqlite> CREATE TABLE v (a INTEGER PRIMARY KEY, b TEXT);
> >> sqlite> INSERT INTO v SELECT * FROM t;
> >> sqlite> SELECT * FROM v;
> >> a   b
> >> --  --
> >> 1   foo
> >> 2   bar
> >> 3   baz
> >> sqlite> DELETE FROM v;
> >> sqlite> INSERT INTO v SELECT a + 0.5, b FROM t;
> >> Error: datatype mismatch
> >> sqlite>
> >>
> >>
> >> In other words, don't use CREATE TABLE .. AS SELECT .. to clone.
> >> Instead, create the new table the way you want to, and then use INSERT
> >> to populate it with data from the old table.
> >>
> >>
> >> > It's a
> >> > bit of a mess, or at least it seems so to me because my first ten
> years
> >> of
> >> > database work was done with PICK, a database that was developed by
> PICK
> >> > Systems but licensed to many companies and marketed under different
> >> brands
> >> > with subtle functionality differences, yet applications that adhered
> to
> >> the
> >> > core PICK spec were completely portable across all implementations. I
> >> think
> >> > SQLite implementations should probably adhere to a core spec but I
> >> recognize
> >> > this as my bias, not dogma.
> >> >
> >> > Adobe (and possibly Google and some others who are involved in
> >> coordinating
> >> > their SQLite implementations --I'm not fully "up" on the details of
> who
> >> all
> >> > are involved in that cooperative effort, or 

Re: [sqlite] Slow query

[Pavel Ivanov]

> The year can also be different but Im interested only in the latest year. I
> use the GROUP because I want unique id and if I add year in GROUP BY I can
> get it repeated.

If you're interested in the latest year then your query is totally
wrong because it returns random year. If you saw what you want so far
then you were just lucky. Try to change your query like this:

SELECT id, name, id2, name2, max(year) y
GROUP BY id, name2
ORDER BY name2, y DESC
LIMIT 0, 15


Pavel

On Wed, Jun 30, 2010 at 6:21 AM, J. Rios  wrote:
> On Wed, Jun 30, 2010 at 2:40 AM, Pavel Ivanov  wrote:
>
>> > The query return the apropiate values as always the id -> id2 relation is
>> > the same and id -> name and id2 -> name2.
>>
>> So your id maps uniquely to id2, name and name2. But what about year?
>> What value of year do you want to be used in sorting?
>>
>> Anyway try to change query as "GROUP BY id, name2, year". If your id
>> maps uniquely to the year it won't affect results and along with
>> Simon's suggestion it should speed up your query. If your id doesn't
>> map uniquely to year then you are sorting by random number, so you
>> better remove that from ORDER BY clause.
>>
>>
>> Pavel
>>
>>
> There are many entries with the same id and id2 because there are other
> fields with different data but I talked about the ones I need in this query.
> The year can also be different but Im interested only in the latest year. I
> use the GROUP because I want unique id and if I add year in GROUP BY I can
> get it repeated.
>
> Guess my best option is to use a temp table.
> ___
> 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] Slow query

[J. Rios]

On Wed, Jun 30, 2010 at 2:40 AM, Pavel Ivanov  wrote:

> > The query return the apropiate values as always the id -> id2 relation is
> > the same and id -> name and id2 -> name2.
>
> So your id maps uniquely to id2, name and name2. But what about year?
> What value of year do you want to be used in sorting?
>
> Anyway try to change query as "GROUP BY id, name2, year". If your id
> maps uniquely to the year it won't affect results and along with
> Simon's suggestion it should speed up your query. If your id doesn't
> map uniquely to year then you are sorting by random number, so you
> better remove that from ORDER BY clause.
>
>
> Pavel
>
>
There are many entries with the same id and id2 because there are other
fields with different data but I talked about the ones I need in this query.
The year can also be different but Im interested only in the latest year. I
use the GROUP because I want unique id and if I add year in GROUP BY I can
get it repeated.

Guess my best option is to use a temp table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Table with corrupted indices returning incorrect results (sample attached)

[Richard Mao]

Hello,
 
Customers using our Sqlite based application have reported some anomolies. It 
appears that the indices for the table have become corrupted somehow. I've 
attached a sample database which exhibits the issue. Can anyone offer some 
insight into how the table could have got into this state? Any suggestions for 
how we can prevent this from happening in the future (so far, this has been 
reported twice by our customers)? 
 
The sample database has one table called xdfema_properties, with 2 columns, 
Name and Value. The Name column is a primary key. 
 
If you do a "SELECT * FROM xdfema_properties" on the table, you'll see one 
problem immediately. There are 4 rows with a 'Name' value of 'SyncStartTicks', 
depsite the fact that the Name column is a primary key. 
 
Some more wierdness: if you select any of the rows after the "SyncCookie" row, 
such as:  SELECT * FROM xdfema_properties WHERE Name = 'NextPollFrom',
you get the 'SyncCookie' Value column, not the 'NextPollFrom' Value column.
 
If you change the query to use LIKE instead however, you get the correct row:  
SELECT * FROM xdfema_properties WHERE Name LIKE 'NextPollFrom'
 
I inferred that the indices for the table are corrupted, since I can repair the 
problem by removing the duplicated 'SyncStartTicks' rows and then executing a 
"REINDEX" statement. After this, the strange behavior goes away.
 
We are using the System.Data.Sqlite ADO.NET provider (v1.0.66) which I believe 
uses SQLite 3.6.23.1.
 
Any insights would be appreciated.
 
Richard
 
 
 
 
 
 
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alternative to UNIQUE CONSTRAINT

[Oliver Peters]

Pavel Ivanov  writes:

[...]

> You should say at this point "That has to be an error in
> OpenOffice", go find some OpenOffice bug tracker or OpenOffice-related
> discussion list and bring this issue to their attention.

[...]


all people interested in solving this bug will find a registered openoffice
issue here http://www.openoffice.org/issues/show_bug.cgi?id=112790

Oliver

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