[sqlite] SQLite 3.5.9 bug with journals and file locking

[Brian Dantes]

If a transaction is opened on a DB for which a journal file exists, and
fcntl() returns EACCES or EPERM on the attempt to acquire a write lock on
the DB to replay the journal, SQLite 3.5.9 quietly ignores the journal
without replaying it and continues on. This is a serious bug.

SQLite 3.6.14.2 keeps trying the write lock forever on EACCES or immediately
fails on EPERM, which is the proper behavior.

An example scenario is an abort during a user writing to the DB with access
to do so followed by another user trying to read the DB with no access to
write it.

Even though this appears to be fixed in 3.6.14.2, I thought this should be
documented and perhaps analyzed in the 3.5.9 source to make sure no similar
problems are still in 3.6.14.2.


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

[sqlite] Fast data duplication

[Vasil Boshnyakov]

Hello,

 

We need to implement fast

 

Insert into users ItemID, Name

Select ItemID, Name from users where itemActive = 1;

 

 

The short description is: we need to copy many records of a table in the
same table but changing the "Name" value. So we have added a new function
which process the names:

 

Insert into users ItemID, Name

Select ItemID, newName(Name) from users where itemActive = 1;

 

That works great but we need one more step: how to much the pairs "item
comes from the Select <-> new item result of the Insert". We need to track
the copy history: itemID -> newItemID.

 

One way is to select and insert the old ItemIDs (ordered by.) and then use
the same "order by" in the "Select". So every item in the select will much
the exact item in the already stored "itemIDs". I am not sure if that is a
correct logic.

 

Any help is very welcome!

 

 

Thanks.

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

[sqlite] Alignment bug in SQLIte 3.6.14.2

[Brian Dantes]

We've run into an alignment issue in SQLite 3.6.14.2 which only
seems to cause a problem under Solaris Sparc in our testing.

Failure narrowed down to:
src/rowset.c:186
pEntry->v = rowid
pEntry is 0xX4
__alignof(*pEntry) is 8 (because of an i64)

However sizeof(RowSet) is 28 (and 28%8 = 4), and pEntry starts 1 RowSet
after freshly allocated, 8-bytes aligned) memory (see sqlite3RowSetInit)
So it crashes. This is definitely a bug in sqlite.

Suggested patch that seems to work for us:

= sqlite/src/rowset.c 1.1 vs edited =
--- 1.1/sqlite/src/rowset.c2009-05-19 14:07:53 -07:00
+++ edited/sqlite/src/rowset.c
2009-05-26 15:43:56 -07:00
@@ -127,6 +127,7 @@
  */
  RowSet *sqlite3RowSetInit(sqlite3 *db, void *pSpace, unsigned int N){
RowSet *p;
+  int n;
assert( N >= sizeof(*p) );
p = pSpace;
p->pChunk = 0;
@@ -134,8 +135,14 @@
p->pEntry = 0;
p->pLast = 0;
p->pTree = 0;
-  p->pFresh = (struct RowSetEntry*)[1];
-  p->nFresh = (u16)((N - sizeof(*p))/sizeof(struct RowSetEntry));
+  /* Alignment must be a power of 2, and at least equal to
+   __alignof(struct RowSetEntry) */
+  #define MIN_ALIGNMENT 8
+  n = sizeof(*p);
+  /* Round up to next alignment */
+  n = (n - 1) / MIN_ALIGNMENT * MIN_ALIGNMENT + MIN_ALIGNMENT;
+  p->pFresh = (struct RowSetEntry*)((char *)p + n);
+  p->nFresh = (u16)((N - n)/sizeof(struct RowSetEntry));
p->isSorted = 1;
p->iBatch = 0;
return p;


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

Re: [sqlite] Slow Transaction Speed?

[Marcus Grimm]

> On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm 
> wrote:
> [..]
>> So the question is:
>> Is it somehow normal to have only 7 transactions per second?
>
> Yes

ehm... why?

>
> [..]
>> Any comment on this ?
>
> http://www.sqlite.org/faq.html#q19

the faq as well as the speed comparison speaks about a few dozen
of transaction per second... that's why I'm wondering why I'm
almost ten times slower on windows...

thanks for the reply

Marcus

>
>
> Regards,
> ~Nuno Lucas
>


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

[sqlite] suggested changes to file format document

[John Machin]

1. In the following, s/less than/less than or equal to/

"""
2.3.3.4 Index B-Tree Cell Format
[snip 2 paragraphs]

If the record is small enough, it is stored verbatim in the cell. A 
record is deemed to be small enough to be completely stored in the cell 
if it consists of less than:
 max-local := (usable-size - 12) * max-embedded-fraction / 255 - 23
bytes.
"""

2. The formula in the following is incorrect.

"""
[H31190] When a table B-Tree cell is stored partially in an overflow 
page chain, the prefix stored on the B-Tree leaf page consists of the 
two variable length integer fields, followed by the first N bytes of the 
database record, where N is determined by the following algorithm:
 min-local := (usable-size - 12) * 255 / 32 - 23
"""

It should be:
 min-local := (usable-size - 12) * 32 / 255 - 23

3. In description of first 100 bytes of file: """The number of unused 
bytes on each page (single byte field, byte offset 20), is always set to 
0x01.""" ... should be 0x00.

4. In section 2.3.2 Database Record Format, in the table describing 
type/size codes:

"""Even values greater than 12 are used to signify a blob of data (type 
SQLITE_BLOB) (n-12)/2 bytes in length, where n is the integer value 
stored in the record header."""

s/greater than/greater than or equal to/

5. In section 2.3.1 Variable Length Integer Format, in the examples
"""
Decimal HexadecimalVariable Length Integer
[snip]
-78056  0xFFFECD56 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFD 0xCD 0x56
"""
s/78056/78506/

6. In description of sqlite_master:

"""[H30300] If the associated database table is a virtual table, the 
fourth field of the schema table record shall contain an SQL NULL value."""

Looks like an integer zero to me:

DOS-prompt>sqlite3
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create virtual table foo using fts3(yadda yadda);
sqlite> select typeof(rootpage),* from sqlite_master where name = 'foo';
integer|table|foo|foo|0|CREATE VIRTUAL TABLE foo using fts3(yadda yadda)

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

Re: [sqlite] Question on errors - IOERR and CANTOPEN

[Dennis Volodomanov]

Hi Filip,

> what SQLite version and on what platform are you using? There was a
> bug in SQLite 3.6.13 on Windows where SQLITE_CANTOPEN was incorrectly
> returned during journal check when race condition between two threads
> was hit. Also there could be some other software interfering with the
> journal deletion, which could cause SQLITE_IOERR. Typically
> TortoiseSVN has the habbit of doing this. While there is mechanism in
> SQLite to workaround this, it's far from prefect. Known workaround is
> to use "pragma journal_mode=persist;". If this solves your problems,
> then you most probably hit this bug.

I'm using 3.6.14.2 on Windows (compiled in from the amalgamation). I do
have several processes (2 actually, one is an application and the other
is a service) trying to open the same database file. Whoever opens it
first, gets rights to read/write to it (to do this, I'm using a BEGIN
IMMEDIATE TRANSACTION in a loop). When that program exits, the other has
a chance to take over and carry on. This BEGIN sometimes causes the
errors above.

At the moment, I'm just sleeping and retrying and things seem to be ok,
but I'm not confident that it's the right approach.

I'll try the pragma you mention and see if it happens again.

Thanks!

   Dennis


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

Re: [sqlite] Slow Transaction Speed?

[Nuno Lucas]

On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm  wrote:
[..]
> So the question is:
> Is it somehow normal to have only 7 transactions per second?

Yes

[..]
> Any comment on this ?

http://www.sqlite.org/faq.html#q19


Regards,
~Nuno Lucas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create index on view

[BareFeet]

Hi Wying,

> Create View MyView1 as
> SELECT
>  t1.A + t2.A as Col1
> , t1.B + t2.B as Col2
> , t2.Cas Col3
> ...
>
> Create View MyView2 as
> SELECT
>  t1.A + t3.A as Col1
> , t1.B + t3.B as Col2
> , t3.Cas Col3
> ...

> In the users' query:-
> SELECT
>  ...
> FROM
>MyView1 v1
> JOIN MyView2 v2 ON v1.Col1 = v2.Col1
>AND v1.Col2 = v2.Col2

You can only index stored values, ie columns in a table. You can't  
index the results of an on the fly calculation (ie views), such as  
addition. This is not a limitation on views but on indexes. You can  
only index what's actually there (ie values stores in a row/column).  
So you would have the same problem without views.

Since you are searching on the sum of values in two columns equalling  
the sum of two other columns, you can only index those sums if you  
store the sums in a table.

One way to do this would be to use a table in place of your view:

create table MySums1 as
select
   T1.A + T2.A as Col1
, T1.B + T2.B as Col2
, T2.Cas Col3
from
  Table1 t1
, Table2 t2

Then you can index Col1, Col2, Col3 etc.

You could automatically update the sums table MySums1 using triggers  
on your primary tables.

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml



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

Re: [sqlite] Question on errors - IOERR and CANTOPEN

[Filip Navara]

Hi Dennis,

what SQLite version and on what platform are you using? There was a
bug in SQLite 3.6.13 on Windows where SQLITE_CANTOPEN was incorrectly
returned during journal check when race condition between two threads
was hit. Also there could be some other software interfering with the
journal deletion, which could cause SQLITE_IOERR. Typically
TortoiseSVN has the habbit of doing this. While there is mechanism in
SQLite to workaround this, it's far from prefect. Known workaround is
to use "pragma journal_mode=persist;". If this solves your problems,
then you most probably hit this bug.

BTW, I don't know how many people are affected by this journal
creation bug, but there's a reliable way to workaround it. The
solution would be to detect the "delete pending" state and rename the
journal file in that case. This will allow the new journal file to be
created and the old one will disappear as soon as the offending
application closes the last handle to it. Anyway... don't want to hick
your thread :)

Best regards,
Filip Navara

On Tue, May 26, 2009 at 7:51 AM, Dennis Volodomanov
 wrote:
> I sometimes get either a SQLITE_IOERR or a SQLITE_CANTOPEN when issuing
> BEGIN IMMEDIATE TRANSACTION or END TRANSACTION, however the database
> file is there and is being used by another thread. I thought I'd get the
> usual SQLITE_BUSY or SQLITE_LOCKED, but sometimes these file-related
> errors come up.
>
>
>
> Does anyone know why they come up and what should be the correct logic
> to continue? Should (and can it) the operation in question be retried,
> as if a BUSY/LOCKED was encountered?
>
>
>
> Thanks in advance,
>
>
>
>   Dennis
>
>
>
> ___
> 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] database file header: "schema layer file format" anomaly

[John Machin]

On 27/05/2009 3:03 AM, D. Richard Hipp wrote:
> John - what were you doing when you discovered this?
> 
> On May 26, 2009, at 10:57 AM, John Machin wrote:
> 
>> According to the file format document
>> (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block
>> starting at byte offset 44 of a well-formed database file, the schema
>> layer file format, contains a big-endian integer value between 1 and  
>> 4,
>> inclusive."
>>
>> However it is possible to end up with this being zero, e.g. by  
>> dropping
>> all tables/etc and then doing a VACUUM:

Eyeballing the following output from my code:

 assert 1 <= self.schema_layer_file_format <= 4
AssertionError

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

Re: [sqlite] Select on foreign key NULL

[Leo Freitag]

Thanks a lot. Both works fine.
Leo

John Machin schrieb:
> On 27/05/2009 1:09 AM, Leo Freitag wrote:
>   
>> Hallo,
>>
>> I got some problems with a select on a foreign key with value null.
>> I want to filter all male singers.
>>
>> CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT, 
>> 'fkvoice' INTEGER, 'sex' TEXT);
>> INSERT INTO "tblsinger" VALUES(1,'Anna Netrebko',1, 'f');
>> INSERT INTO "tblsinger" VALUES(2,'Hanna Schwarz',2, 'f');
>> INSERT INTO "tblsinger" VALUES(3,'Luciano Pavarotti', 3, 'm');
>> INSERT INTO "tblsinger" VALUES(4,'Robert Lloyd', 4, 'm');
>> INSERT INTO "tblsinger" VALUES(5,'Robby Williams', null, 'm');
>>
>> CREATE TABLE 'tblvoice' ('id' INTEGER PRIMARY KEY, 'voice' TEXT);
>> INSERT INTO "tblvoice" VALUES(1,'sopran');
>> INSERT INTO "tblvoice" VALUES(2,'alt');
>> INSERT INTO "tblvoice" VALUES(3,'tenor');
>> INSERT INTO "tblvoice" VALUES(4,'bass');
>>
>> SELECT tblsinger.name, tblsinger.sex, tblvoice.voice FROM tblsinger, 
>> tblvoice
>> WHERE tblsinger.sex = 'm' AND tblsinger.fkvoice = tblvoice.id;
>>
>> -- Result
>>
>> Luciano Pavarotti | m | tenor
>> Robert Lloyd  | m | bass
>>
>> -- How do I have to modify the select statement to get the result below:
>>
>> Luciano Pavarotti | m | tenor
>> Robert Lloyd  | m | bass
>> Robby Williams| m |
>> 
>
> sqlite> select s.name, s.sex, v.voice from tblsinger s left outer join 
> tblvoice v on s.fkvoice = v.id where s.sex = 'm';
> Luciano Pavarotti|m|tenor
> Robert Lloyd|m|bass
> Robby Williams|m|
>
> With "visible NULL":
>
> sqlite> select s.name, s.sex, ifnull(v.voice, 'UNKNOWN') from tblsinger 
> s left outer join tblvoice v on s.fkvoice = v.id where s.sex = 'm';
> Luciano Pavarotti|m|tenor
> Robert Lloyd|m|bass
> Robby Williams|m|UNKNOWN
>
> ___
> 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] Slow Transaction Speed?

[Marcus Grimm]

Hello List,

I have a slightly dissapointing issue here with the
update/insert speed of sqlite3 on win32 using
version 3.6.14.1:
On my system a single INSERT statement needs
appx. 150ms, the same is the case for DELETE statements.
I test this by doing single INSERTs in a loop.
Of course, the speed increases dramatically when I do this
test loop encapsulated by a transaction: In this case
the overall time is approximately the same as for the
single INSERT plus a little overhead of appx. 50ms for 200
inserts.

So the question is:
Is it somehow normal to have only 7 transactions per second?
The (old) Speed comparison from sqlite pages talks about
a insert speed of appx 13ms per insert.

The database is currently rather small: 490kb

I also made a little test and just copy the database file using
CopyFile("TestDB.DB", "CopyDB.DB", FALSE);

This takes 15ms... by far less than sqlite needs for a single
insert statement. This might not be fair because I don't
know if the win32 function flushes the file before returning
but it is suprising anyhow.

Any comment on this ?

I also have two indexes on the table where I insert
the test data, if that might be of importance ?

thank you

Marcus Grimm

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

Re: [sqlite] create indexed view

[Kees Nuyt]

On Tue, 26 May 2009 14:44:25 +0800, wying wy
 wrote:

>Hi
>
>May I know if we can create index on a View?

You can't create an index on a VIEW.
A VIEW can be seen as a stored SELECT statement. 

>Thanks in advance.
>wying
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a problem with sqlite3_get_table

[Igor Tandetnik]

Enrico Piccinini
 wrote:
> thanks Pavel, I tried but the problem persist.
>
> I used ostringstream in a lot of function and query in my sw. But the
> problem arise in thi function only when I substitute an INNER JOIN
> with thew
> LEFT JOIN. In fact, if I keep the INNER JOIN instead LEFT JOIN
> evrthing
> works as it would.
>
> Using  sqlite3_prepare, step_reset an error is thrown during
> sqlite3_step

What error code does sqlite3_step return? If you call sqlite3_reset or 
sqlite3_finalize right after sqlite3_step fails (and, most likely, 
returns SQLITE_ERROR), whar error code do they return?

Igor Tandetnik 



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

Re: [sqlite] a problem with sqlite3_get_table

[Igor Tandetnik]

Pavel Ivanov  wrote:
> Stringstream never puts 0 byte at the end of the string.

Does too. You might be thinking about ostrstream (whose str() method 
returns char* pointing to a non-NUL-terminated buffer), but the OP uses 
ostringstream, whose str() method returns an std::string (whose c_str(), 
in turn, always returns a pointer to NUL-terminated buffer).

Igor Tandetnik



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

Re: [sqlite] Getting last inserted rowid?

[Igor Tandetnik]

Nikolaus Rath  wrote:
> How can I determine the rowid of the last insert if I am accessing the
> db from different threads? If I understand correctly,
> last_insert_rowid() won't work reliably in this case.

Last inserted rowid is maintained per connection. Do your threads use 
the same connection, or each create their own?

If all threads share the same connection, it is your responsibility to 
make "insert then retrieve last rowid" an atomic operation, using thread 
synchronization mechanism of your choice. Just as with any access to 
shared data.

> I can't believe that I really have to do a SELECT on the data that I
> just INSERTed only to get the rowid...

I'm not sure how this helps, if another thread can insert more data 
between your INSERT and SELECT. Wouldn't that suffer from the same 
problem?

Igor Tandetnik 



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

Re: [sqlite] Getting last inserted rowid?

[Nuno Lucas]

On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
> Hello,
>
> How can I determine the rowid of the last insert if I am accessing the
> db from different threads? If I understand correctly,
> last_insert_rowid() won't work reliably in this case.

It should work if you do:

BEGIN
INSERT ...
last_insert_rowid()
END

If you don't do this then last_insert_rowid() could refer to an insert
happening in other thred:


INSERT ...

INSERT ...

last_insert_rowid()

last_insert_rowid()
<...>

Also (not really sure as I avoid threads) I believe it will work if
each thread has it's own db handle, but don't know what will happen
you use the shared cache feature.


Regards,
~Nuno Lucas

>
> I can't believe that I really have to do a SELECT on the data that I
> just INSERTed only to get the rowid...
>
>
> Thanks,
>
>   -Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
>
> ___
> 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] Getting last inserted rowid?

[Nikolaus Rath]

Hello,

How can I determine the rowid of the last insert if I am accessing the
db from different threads? If I understand correctly,
last_insert_rowid() won't work reliably in this case.

I can't believe that I really have to do a SELECT on the data that I
just INSERTed only to get the rowid...


Thanks,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] database file header: "schema layer file format" anomaly

[D. Richard Hipp]

John - what were you doing when you discovered this?

On May 26, 2009, at 10:57 AM, John Machin wrote:

> According to the file format document
> (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block
> starting at byte offset 44 of a well-formed database file, the schema
> layer file format, contains a big-endian integer value between 1 and  
> 4,
> inclusive."
>
> However it is possible to end up with this being zero, e.g. by  
> dropping
> all tables/etc and then doing a VACUUM:
>
> # Assume vacked.db doesn't exist
> DOS-prompt>sqlite3 vacked.db
> SQLite version 3.6.14
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table foo (x, y);
> sqlite> insert into foo values(1, 2);
> sqlite> drop table foo;
> sqlite> vacuum;
> sqlite> ^Z
>
> This seems very much a corner case and I don't imagine this is a  
> problem
> in practice; any concern about this number being when it is too high  
> for
> the software opening the file, and as far as I can guess there is no
> "too low" problem -- however in my opinion differences between such
> documents and reality should always be reported, so here it is.

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] a problem with sqlite3_get_table

[Enrico Piccinini]

thanks Pavel, I tried but the problem persist.

I used ostringstream in a lot of function and query in my sw. But the
problem arise in thi function only when I substitute an INNER JOIN with thew
LEFT JOIN. In fact, if I keep the INNER JOIN instead LEFT JOIN evrthing
works as it would.

Using  sqlite3_prepare, step_reset an error is thrown during sqlite3_step. I
printed the error message that is: "not an error".

Is this more helpful?

Enrico

On Tue, May 26, 2009 at 6:34 PM, Pavel Ivanov  wrote:

> Instead of
>
> exe_query(query.str());
>
> try to do this:
>
> size_t len = query.pcount();
> exe_query(string(query.str(), len).c_str());
>
> Stringstream never puts 0 byte at the end of the string.
>
> Pavel
>
> On Tue, May 26, 2009 at 12:12 PM, Enrico Piccinini
>  wrote:
> > Hy to all, I'm very new to database and sqlite.
> >
> > I'm writing a C++ code to execute some queries.
> >
> > A query of mine does a select with some inner join and a left join as
> > reported here:
> > //inline int getFunctList(const string& testName, vector& result,
> > int& nCol, int& nRow)
> > //{
> >
> > [...]
> > ostringstream query;
> > //  query << "SELECT
> > functs_tbl.Description,resources_tbl.Name,functs_tbl.FunctionalString
> FROM
> > seq.test_list_tbl "
> > //"INNER JOIN seq.functs_tbl on functs_tbl.FN_ID=test_tbl.FN_ID "
> > //"INNER JOIN seq.test_tbl on test_tbl.TL_ID=test_list_tbl.TL_ID
> "
> > //"LEFT JOIN conf.resources_tbl ON
> > resources_tbl.RS_ID=functs_tbl.RS_ID "
> > //"WHERE test_list_tbl.Name=\"" << testName << "\";" << ends;
> > //exe_query(query.str());
> > [...]
> > //}
> >
> > "exe_quey" is a simple wrapper of sqlite3_get_table.
> >
> > The execution of this line code makes may program to crash.
> >
> > The same query with the same parameter (testName) executed from tcl
> command
> > line works perfectly.
> >
> > I've also tried to execute this query with sqlite3_prepare, step_reset,
> but
> > the problem is staing on.
> >
> > I hope that some one can give me some advice because about the solution
> of
> > the problem.
> >
> > Thank you in advance.
> > Enrico
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a problem with sqlite3_get_table

[Pavel Ivanov]

Instead of

exe_query(query.str());

try to do this:

size_t len = query.pcount();
exe_query(string(query.str(), len).c_str());

Stringstream never puts 0 byte at the end of the string.

Pavel

On Tue, May 26, 2009 at 12:12 PM, Enrico Piccinini
 wrote:
> Hy to all, I'm very new to database and sqlite.
>
> I'm writing a C++ code to execute some queries.
>
> A query of mine does a select with some inner join and a left join as
> reported here:
> //inline int getFunctList(const string& testName, vector& result,
> int& nCol, int& nRow)
> //{
>
> [...]
> ostringstream query;
> //      query << "SELECT
> functs_tbl.Description,resources_tbl.Name,functs_tbl.FunctionalString FROM
> seq.test_list_tbl "
> //        "INNER JOIN seq.functs_tbl on functs_tbl.FN_ID=test_tbl.FN_ID "
> //        "INNER JOIN seq.test_tbl on test_tbl.TL_ID=test_list_tbl.TL_ID "
> //        "LEFT JOIN conf.resources_tbl ON
> resources_tbl.RS_ID=functs_tbl.RS_ID "
> //        "WHERE test_list_tbl.Name=\"" << testName << "\";" << ends;
> //exe_query(query.str());
> [...]
> //}
>
> "exe_quey" is a simple wrapper of sqlite3_get_table.
>
> The execution of this line code makes may program to crash.
>
> The same query with the same parameter (testName) executed from tcl command
> line works perfectly.
>
> I've also tried to execute this query with sqlite3_prepare, step_reset, but
> the problem is staing on.
>
> I hope that some one can give me some advice because about the solution of
> the problem.
>
> Thank you in advance.
> Enrico
> ___
> 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] a problem with sqlite3_get_table

[Enrico Piccinini]

Hy to all, I'm very new to database and sqlite.

I'm writing a C++ code to execute some queries.

A query of mine does a select with some inner join and a left join as
reported here:
//inline int getFunctList(const string& testName, vector& result,
int& nCol, int& nRow)
//{

[...]
ostringstream query;
//  query << "SELECT
functs_tbl.Description,resources_tbl.Name,functs_tbl.FunctionalString FROM
seq.test_list_tbl "
//"INNER JOIN seq.functs_tbl on functs_tbl.FN_ID=test_tbl.FN_ID "
//"INNER JOIN seq.test_tbl on test_tbl.TL_ID=test_list_tbl.TL_ID "
//"LEFT JOIN conf.resources_tbl ON
resources_tbl.RS_ID=functs_tbl.RS_ID "
//"WHERE test_list_tbl.Name=\"" << testName << "\";" << ends;
//exe_query(query.str());
[...]
//}

"exe_quey" is a simple wrapper of sqlite3_get_table.

The execution of this line code makes may program to crash.

The same query with the same parameter (testName) executed from tcl command
line works perfectly.

I've also tried to execute this query with sqlite3_prepare, step_reset, but
the problem is staing on.

I hope that some one can give me some advice because about the solution of
the problem.

Thank you in advance.
Enrico
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor errors in CREATE TABLE syntax diagrams

[John Machin]

On 27/05/2009 12:33 AM, Jim Wilcoxson top-posted:
> For my money, I'd prefer to have a smaller, faster parser that worked
> correctly on correct input at the expense of not catching all possible
> syntax errors on silly input.

Firstly, none of the examples that I gave are syntactically incorrect.
Secondly, a compiler that doesn't reject ill-formed syntax should not be
seen after first semester CS101 -- the very idea is a nonsense.
Thirdly, all I'm asking for is a few more lines to make the diagrams 
accord with what the SQL compiler is already doing.

>  There is a definite trade-off here, and
> I could see where a totally complete parser that caught every possible
> error in SQL grammer might be twice the size of the entire SQLite code
> base.
> 
> Of course, you don't want an SQL syntax typo to trash your database
> either, without warning.

Which is why you test your software ... so col1 is not supposed to 
permit NULLs so you need to test it whether you wrote the syntactically 
correct "col1 INTEGER NOTE NULL, ..." or the equally syntactically 
correct "col1 INTEGER, ..." -- both being practically wrong.


>  I'm assuming the SQLite developers have made
> reasonable decisions about which parsing errors are important, and
> which aren't.

I hope they don't have any /parsing/ errors at all.


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

Re: [sqlite] Select on foreign key NULL

[John Machin]

On 27/05/2009 1:09 AM, Leo Freitag wrote:
> Hallo,
> 
> I got some problems with a select on a foreign key with value null.
> I want to filter all male singers.
> 
> CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT, 
> 'fkvoice' INTEGER, 'sex' TEXT);
> INSERT INTO "tblsinger" VALUES(1,'Anna Netrebko',1, 'f');
> INSERT INTO "tblsinger" VALUES(2,'Hanna Schwarz',2, 'f');
> INSERT INTO "tblsinger" VALUES(3,'Luciano Pavarotti', 3, 'm');
> INSERT INTO "tblsinger" VALUES(4,'Robert Lloyd', 4, 'm');
> INSERT INTO "tblsinger" VALUES(5,'Robby Williams', null, 'm');
> 
> CREATE TABLE 'tblvoice' ('id' INTEGER PRIMARY KEY, 'voice' TEXT);
> INSERT INTO "tblvoice" VALUES(1,'sopran');
> INSERT INTO "tblvoice" VALUES(2,'alt');
> INSERT INTO "tblvoice" VALUES(3,'tenor');
> INSERT INTO "tblvoice" VALUES(4,'bass');
> 
> SELECT tblsinger.name, tblsinger.sex, tblvoice.voice FROM tblsinger, 
> tblvoice
> WHERE tblsinger.sex = 'm' AND tblsinger.fkvoice = tblvoice.id;
> 
> -- Result
> 
> Luciano Pavarotti | m | tenor
> Robert Lloyd  | m | bass
> 
> -- How do I have to modify the select statement to get the result below:
> 
> Luciano Pavarotti | m | tenor
> Robert Lloyd  | m | bass
> Robby Williams| m |

sqlite> select s.name, s.sex, v.voice from tblsinger s left outer join 
tblvoice v on s.fkvoice = v.id where s.sex = 'm';
Luciano Pavarotti|m|tenor
Robert Lloyd|m|bass
Robby Williams|m|

With "visible NULL":

sqlite> select s.name, s.sex, ifnull(v.voice, 'UNKNOWN') from tblsinger 
s left outer join tblvoice v on s.fkvoice = v.id where s.sex = 'm';
Luciano Pavarotti|m|tenor
Robert Lloyd|m|bass
Robby Williams|m|UNKNOWN

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

[sqlite] Select on foreign key NULL

[Leo Freitag]

Hallo,

I got some problems with a select on a foreign key with value null.
I want to filter all male singers.

CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT, 
'fkvoice' INTEGER, 'sex' TEXT);
INSERT INTO "tblsinger" VALUES(1,'Anna Netrebko',1, 'f');
INSERT INTO "tblsinger" VALUES(2,'Hanna Schwarz',2, 'f');
INSERT INTO "tblsinger" VALUES(3,'Luciano Pavarotti', 3, 'm');
INSERT INTO "tblsinger" VALUES(4,'Robert Lloyd', 4, 'm');
INSERT INTO "tblsinger" VALUES(5,'Robby Williams', null, 'm');

CREATE TABLE 'tblvoice' ('id' INTEGER PRIMARY KEY, 'voice' TEXT);
INSERT INTO "tblvoice" VALUES(1,'sopran');
INSERT INTO "tblvoice" VALUES(2,'alt');
INSERT INTO "tblvoice" VALUES(3,'tenor');
INSERT INTO "tblvoice" VALUES(4,'bass');

SELECT tblsinger.name, tblsinger.sex, tblvoice.voice FROM tblsinger, 
tblvoice
WHERE tblsinger.sex = 'm' AND tblsinger.fkvoice = tblvoice.id;

-- Result

Luciano Pavarotti | m | tenor
Robert Lloyd  | m | bass

-- How do I have to modify the select statement to get the result below:

Luciano Pavarotti | m | tenor
Robert Lloyd  | m | bass
Robby Williams| m |

Thanks in advance
Leo

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

Re: [sqlite] Minor errors in CREATE TABLE syntax diagrams

[Jim Wilcoxson]

For my money, I'd prefer to have a smaller, faster parser that worked
correctly on correct input at the expense of not catching all possible
syntax errors on silly input.  There is a definite trade-off here, and
I could see where a totally complete parser that caught every possible
error in SQL grammer might be twice the size of the entire SQLite code
base.

Of course, you don't want an SQL syntax typo to trash your database
either, without warning.  I'm assuming the SQLite developers have made
reasonable decisions about which parsing errors are important, and
which aren't.

Jim


On 5/26/09, John Machin  wrote:
>
> 1. SQLite allows NULL as a column-constraint.
>
> E.g. CREATE TABLE tname (col0 TEXT NOT NULL, col1 TEXT NULL);
>
> The column-constraint diagram doesn't show this possibility.
>
> Aside: The empirical evidence is that NULL is recognised and *ignored*;
> consequently there is no warning about sillinesses and typoes like in
> these examples of column-def:
> col1 INTEGER NOT NULL NULL
> col1 INTEGER NOTE NULL -- type="INTEGER NOTE", constraint="NULL"
>
> 2. According to the diagram for foreign-key-clause, there is no "express
> track" which allows skipping both "ON DELETE|UPDATE|INSERT etc" and
> "MATCH name". However SQLite does permit all of that to be skipped.
>
> E.g. CREATE TABLE tname(col0 TEXT PRIMARY KEY, col1 TEXT REFERENCES
> ftable(fcol));
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Select on foreign key NULL

[Simon Davies]

2009/5/26 Leo Freitag :
> Hallo,
>
> I got some problems with a select on a foreign key with value null.
> I want to filter all male singers.
>
> CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT,
> 'fkvoice' INTEGER, 'sex' TEXT);
> INSERT INTO "tblsinger" VALUES(1,'Anna Netrebko',1, 'f');
> INSERT INTO "tblsinger" VALUES(2,'Hanna Schwarz',2, 'f');
> INSERT INTO "tblsinger" VALUES(3,'Luciano Pavarotti', 3, 'm');
> INSERT INTO "tblsinger" VALUES(4,'Robert Lloyd', 4, 'm');
> INSERT INTO "tblsinger" VALUES(5,'Robby Williams', null, 'm');
>
> CREATE TABLE 'tblvoice' ('id' INTEGER PRIMARY KEY, 'voice' TEXT);
> INSERT INTO "tblvoice" VALUES(1,'sopran');
> INSERT INTO "tblvoice" VALUES(2,'alt');
> INSERT INTO "tblvoice" VALUES(3,'tenor');
> INSERT INTO "tblvoice" VALUES(4,'bass');
>
> SELECT tblsinger.name, tblsinger.sex, tblvoice.voice FROM tblsinger,
> tblvoice
> WHERE tblsinger.sex = 'm' AND tblsinger.fkvoice = tblvoice.id;
>
> -- Result
>
> Luciano Pavarotti | m | tenor
> Robert Lloyd      | m | bass
>
> -- How do I have to modify the select statement to get the result below:
>
> Luciano Pavarotti | m | tenor
> Robert Lloyd      | m | bass
> Robby Williams    | m |

Use a left join:

 SELECT tblsinger.name, tblsinger.sex, tblvoice.voice
 FROM tblsinger left join tblvoice
 ON tblsinger.fkvoice = tblvoice.id
 WHERE tblsinger.sex = 'm';

>
> Thanks in advance
> Leo
>

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

Re: [sqlite] create index on view

[wying wy]

Hi Tom

Thanks. Here you go:-

CREATE TABLE Table1 (A INTEGER ,B INTEGER)
(10 rows)

CREATE TABLE Table2 (A INTEGER ,B INTEGER, C TEXT, D TEXT)
(100 rows)

CREATE TABLE Table3 (A INTEGER ,B INTEGER, C TEXT)
(50 rows)


Create Index X1 on Table1 (A, B)
Create Index X2 on Table2 (A, B)
Create Index X3 on Table3 (A, B)


Create View MyView1 as
SELECT
  t1.A + t2.A as Col1
, t1.B + t2.B as Col2
, t2.Cas Col3
FROM
  Table1 t1
, Table2 t2
(1000 rows)

Create View MyView2 as
SELECT
  t1.A + t3.A as Col1
, t1.B + t3.B as Col2
, t3.Cas Col3
FROM
  Table1 t1
, Table3 t3
(500 rows)


(Assuming that users cannot access the actual tables, they are accessing the
data via views.)
In the users' query:-
SELECT
  v1.Col1
, v1.Col2
, v1.Col3
, v2.Col3 as Col4
FROM
MyView1 v1
JOIN MyView2 v2 ON v1.Col1 = v2.Col1
AND v1.Col2 = v2.Col2

Let's say we have a huge data set, the users' select stmt could be extremely
slow without indexing in the views.
Any suggestion?


On Tue, May 26, 2009 at 6:47 PM, BareFeet  wrote:

> Hi wying,
>
> > May I know if we can create index on a View?
> > Otherwise, is there any recommendation to speed up the query
> > involving join
> > between two Views?
>
>
> No you can't create an index on a view, but you can create an index on
> the underlying tables that the view uses.
>
> Two preliminary suggestions:
>
> 1. Stop repeating the same question.
>
> 2. Post the schema of your tables and views and the query.
>
> Tom
> BareFeet
>
> ___
> 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] database file header: "schema layer file format" anomaly

[Dan]

On May 26, 2009, at 9:57 PM, John Machin wrote:

> According to the file format document
> (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block
> starting at byte offset 44 of a well-formed database file, the schema
> layer file format, contains a big-endian integer value between 1 and  
> 4,
> inclusive."
>
> However it is possible to end up with this being zero, e.g. by  
> dropping
> all tables/etc and then doing a VACUUM:
>
> # Assume vacked.db doesn't exist
> DOS-prompt>sqlite3 vacked.db
> SQLite version 3.6.14
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table foo (x, y);
> sqlite> insert into foo values(1, 2);
> sqlite> drop table foo;
> sqlite> vacuum;
> sqlite> ^Z
>
> This seems very much a corner case and I don't imagine this is a  
> problem
> in practice; any concern about this number being when it is too high  
> for
> the software opening the file, and as far as I can guess there is no
> "too low" problem -- however in my opinion differences between such
> documents and reality should always be reported, so here it is.

As you say, probably not important in practice but still worth getting
right. Thanks for reporting this.

Dan.


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

[sqlite] database file header: "schema layer file format" anomaly

[John Machin]

According to the file format document 
(http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block 
starting at byte offset 44 of a well-formed database file, the schema 
layer file format, contains a big-endian integer value between 1 and 4, 
inclusive."

However it is possible to end up with this being zero, e.g. by dropping 
all tables/etc and then doing a VACUUM:

# Assume vacked.db doesn't exist
DOS-prompt>sqlite3 vacked.db
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo (x, y);
sqlite> insert into foo values(1, 2);
sqlite> drop table foo;
sqlite> vacuum;
sqlite> ^Z

This seems very much a corner case and I don't imagine this is a problem 
in practice; any concern about this number being when it is too high for 
the software opening the file, and as far as I can guess there is no 
"too low" problem -- however in my opinion differences between such 
documents and reality should always be reported, so here it is.

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

[sqlite] Minor errors in CREATE TABLE syntax diagrams

[John Machin]

1. SQLite allows NULL as a column-constraint.

E.g. CREATE TABLE tname (col0 TEXT NOT NULL, col1 TEXT NULL);

The column-constraint diagram doesn't show this possibility.

Aside: The empirical evidence is that NULL is recognised and *ignored*;
consequently there is no warning about sillinesses and typoes like in
these examples of column-def:
col1 INTEGER NOT NULL NULL
col1 INTEGER NOTE NULL -- type="INTEGER NOTE", constraint="NULL"

2. According to the diagram for foreign-key-clause, there is no "express
track" which allows skipping both "ON DELETE|UPDATE|INSERT etc" and
"MATCH name". However SQLite does permit all of that to be skipped.

E.g. CREATE TABLE tname(col0 TEXT PRIMARY KEY, col1 TEXT REFERENCES
ftable(fcol));

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

[sqlite] DBI sqlite usage problem: Insert seems asynchronous, and data lost at CTRL-C

[Rob Meijer]

I am using sqlite with DBI in a perl SMTP system. The program runs until
it gets terminated, currently using CTRL-C but in the future when all runs
fine, using kill of a daemon process.

It appears that when I do an insert in the code, the real insert is
sometimes delayed for quite a while, and is later on combined with
other inserts, as when I do the insert several times, and each time
from an sqlite3 session do a select count(*) on the table, after a number
of inserts without an increase of the count, the count than goes up.

This is not the biggest problem however. If the inserts would only get
delayed and would not get lost if the program is aborted (CTRL-C), that
could be something while far from ideal that I could live with.
The situation however seems to be that inserts are done asynchronously and
sometimes grouped, and may be dropped on CTRL-C.

Is there a way to stop sqlite/dbi from doing inserts asynchronously in
such a way? I tried adding the AutoCommit, but without result.
Or am I simply doing something stupid?


Some relevant pieces of my code:

$self->{"dbh"}=DBI->connect("dbi:SQLite:dbname=$path","","",{ RaiseError
=> 1, AutoCommit => 1 }) || die "Problem with sqlite db at $path";
my $dbh=$self->{"dbh"};
$self->{"add_to_queue"}=$dbh->prepare("INSERT INTO messagequeue
(msgid,fspath,recipient_id,queuename,system_id,person_id,creationtime)
VALUES (?,?,?,?,?,?,?)")or die "Couldn't prepare statement: " .
$dbh->errstr;

..
.

my $sth=$self->{"add_to_queue"};
$sth->execute($id,$file,$recipientid,$queue,$system_id,$person_id,time())
or die "Cannot execute: " . $sth->errstr();

Any pointers on how to get my program to do its inserts reliably and
properly would be very much appreciated.

T.I.A.

Rob

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

Re: [sqlite] FTS3

[Martin Pfeifle]

thank you, this helped a lot and confirmed what I expected.
Best Martin





Von: D. Richard Hipp 
An: General Discussion of SQLite Database 
Gesendet: Dienstag, den 26. Mai 2009, 12:27:59 Uhr
Betreff: Re: [sqlite] FTS3


On May 26, 2009, at 5:03 AM, Martin Pfeifle wrote:

> Dear all,
> we need full and fuzzy text search for addresses.
> Currently we are looking into Lucene and SQLite's FTS extension.
> For us it is crucial to understand the file structures and the  
> concepts behind the libraries.
> Is there a self-contained, comprehensive document for FTS3 (besides  
> the comments in fts3.c) ?

There is no information on FTS3 apart from the code comments and the  
README files in the source tree.

The file formats for FTS3 and lucene are completely different at the  
byte level.  But if you dig deeper, you will find that they both use  
the same underlying concepts and ideas and really are two different  
implementations of the same algorithm.  During development, we were  
constantly testing the performance and index size of FTS3 against  
CLucene using the Enron email corpus.  Our goal was for FTS3 to run  
significantly faster than CLucene and to generate an index that was no  
larger in size.  That goal was easily met at the time, though we have  
not tested FTS3 against CLucene lately to see if anything has changed.

One of the issues with CLucene that FTS3 sought to address was that  
when inserting new elements into the index, the insertion time was  
unpredictable.  Usually the insertions would be very fast.  But lucene  
will occasionally take a very long time for a single insertion in  
order to merge multiple smaller indices into larger indices.  This was  
seen as undesirable.  FTS3 strives to give much better worst-case  
insertion times by doing index merges incrementally and spreading the  
cost of index merges across many inserts.

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] create index on view

[BareFeet]

Hi wying,

> May I know if we can create index on a View?
> Otherwise, is there any recommendation to speed up the query  
> involving join
> between two Views?


No you can't create an index on a view, but you can create an index on  
the underlying tables that the view uses.

Two preliminary suggestions:

1. Stop repeating the same question.

2. Post the schema of your tables and views and the query.

Tom
BareFeet

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

Re: [sqlite] (no subject)

[Samuel Baldwin]

On Tue, May 26, 2009 at 6:34 PM, John Machin  wrote:
> Don't try that with your 100MB database without ensuring that your
> keyboard interrupt mechanism isn't seized up :-)
>
> Perhaps you meant
>
> .schema tablename

I did indeed. I even remember going, "oh, yeah, don't want dump in
this case", but yet my hands still wrote "dump". I blame the commies
and their fluoridation.

-- 
Samuel 'Shardz' Baldwin - staticfree.info/~samuel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[John Machin]

On 26/05/2009 7:58 PM, Samuel Baldwin wrote:
> On Tue, May 26, 2009 at 4:45 PM, Martin.Engelschalk
>  wrote:
>> select * from sqlite_master;
> 
> Or:
> .dump tablename

Don't try that with your 100MB database without ensuring that your 
keyboard interrupt mechanism isn't seized up :-)

Perhaps you meant

.schema tablename

Cheers,
John

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

[sqlite] create index on view

[wying wy]

Hi

May I know if we can create index on a View?
Otherwise, is there any recommendation to speed up the query involving join
between two Views?

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

Re: [sqlite] FTS3

[D. Richard Hipp]

On May 26, 2009, at 5:03 AM, Martin Pfeifle wrote:

> Dear all,
> we need full and fuzzy text search for addresses.
> Currently we are looking into Lucene and SQLite's FTS extension.
> For us it is crucial to understand the file structures and the  
> concepts behind the libraries.
> Is there a self-contained, comprehensive document for FTS3 (besides  
> the comments in fts3.c) ?

There is no information on FTS3 apart from the code comments and the  
README files in the source tree.

The file formats for FTS3 and lucene are completely different at the  
byte level.  But if you dig deeper, you will find that they both use  
the same underlying concepts and ideas and really are two different  
implementations of the same algorithm.  During development, we were  
constantly testing the performance and index size of FTS3 against  
CLucene using the Enron email corpus.  Our goal was for FTS3 to run  
significantly faster than CLucene and to generate an index that was no  
larger in size.  That goal was easily met at the time, though we have  
not tested FTS3 against CLucene lately to see if anything has changed.

One of the issues with CLucene that FTS3 sought to address was that  
when inserting new elements into the index, the insertion time was  
unpredictable.  Usually the insertions would be very fast.  But lucene  
will occasionally take a very long time for a single insertion in  
order to merge multiple smaller indices into larger indices.  This was  
seen as undesirable.  FTS3 strives to give much better worst-case  
insertion times by doing index merges incrementally and spreading the  
cost of index merges across many inserts.

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] (no subject)

[Samuel Baldwin]

On Tue, May 26, 2009 at 4:45 PM, Martin.Engelschalk
 wrote:
> select * from sqlite_master;

Or:
.dump tablename

-- 
Samuel 'Shardz' Baldwin - staticfree.info/~samuel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] create indexed view

[wying wy]

Hi

May I know if we can create index on a View?

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

[sqlite] FTS3

[Martin Pfeifle]

Dear all,
we need full and fuzzy text search for addresses.
Currently we are looking into Lucene and SQLite's FTS extension.
For us it is crucial to understand the file structures and the concepts behind 
the libraries.
Is there a self-contained, comprehensive document for FTS3 (besides the 
comments in fts3.c) ?
Best Martin


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

Re: [sqlite] (no subject)

[Martin.Engelschalk]

Hi,

select * from sqlite_master;

Martin

PS.: Please provide a subject which summarises your question.

Manasi Save schrieb:
> Hi All,
>
> Can anyone help me out with the command to see the SQLite table defination
> on command-line SQLite application.
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Manasi Save]

Hi All,

Can anyone help me out with the command to see the SQLite table defination
on command-line SQLite application.
-- 
Thanks and Regards,
Manasi Save
Artificial Machines Pvt Ltd.
manasi.s...@artificialmachines.com
Ph:- 9833537392



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

Re: [sqlite] Simple Outer Join question?

[Leo Freitag]

Hi Igor,

thanks, works fine!
Where do I find a tutorial that deals with 'IN', 'NOT IN' and subquerys 
in general?

Leo

Igor Tandetnik schrieb:
> "Kees Nuyt"  wrote in
> message news:8u3m151rqbbel40ilsvaatqmfhcnhsj...@dim53.demon.nl
>   
>> On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag
>>  wrote:
>> 
>>> I have a table 'person' and a table 'group'. Every person can join
>>> none, one or more groups.
>>> No I want to select all persons except those who are member in group
>>> 1. - Sounds simple, but not for me.
>>>   
>> This is an n:m relationship.
>> If group has more attributes (columns) than just its number,
>> you need a third table: person_group.
>> Then join person with person_group where group_id != 1;
>> 
>
> That would also pick people that are both in group 1 and group 2. You 
> would need something like
>
> select * from person
> where person_id not in (
> select person_id from person_group where group_id=1);
>
> Igor Tandetnik 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite case and accent insensitive searches

[Giulio Mastrosanti]

I'm porting to sqlite a database from MySQL.

on MYSQL, I was able, with the proper collation, to perform case  
insensitive and also accent insensitive searches on the db.

I mean, a search like this:

search * from table where description like '%cafe%'

matches the descriptions containing cafe and CAFE, but also cafè and  
CAFÈ.

now on sqlite not only I can't find the way to make the search accent  
insensitive ( so cafe will not match cafè ), but it seems from test  
and from some info discovered on the web that the search using LIKE is  
case insensitive only for ascii characters ( so cafe matches CAFE but  
cafè does not match CAFÈ ).

any workaround?

thank you so much,

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

[sqlite] create indexed view

[wying wy]

Hi

May I know if we can create index on a View?

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