Re: [sqlite] Making blob as a sqlite database.

[Mohd Radzi Ibrahim]

Hi Don,
I am working on sales and stock management systems where all outlets run on
it's own local database. I was thinking of a solution for all outlets to
backup to central server and perhaps thru web interface do some queries on
the backup database. I know postgres has binary interface that allow
read/write to blob record which is quite promising to have a vfs system
that can just store database in that record.

But it seems that the blob in SQLite could not do that.


On Tue, Apr 30, 2019 at 10:44 PM Don V Nielsen 
wrote:

> Sorry to bother, Mohd. What is your use case? I mentioned this to
> developers around me and they are intrigued. You are storing a database
> file as blob in a database? We are curious as to the application. I am
> assuming the database being stored is a collection of sensor or event data?
>
> On Fri, Apr 26, 2019 at 5:48 PM Mohd Radzi Ibrahim 
> wrote:
>
> > Hi, is there a vfs that could be used to open a blob column as a
> database?
> >
> >
> > thanks.
> >
> > Radzi.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Making blob as a sqlite database.

[Mohd Radzi Ibrahim]

Hi, is there a vfs that could be used to open a blob column as a database?


thanks.

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

[sqlite] Problem with clause "where X in (...)"

[Mohd Radzi Ibrahim]

Hi,
I thought that I hit a bug with SQLite 3.28.0  and 3.27.1 when my in-list
contains too many items the code breaks here:

The sqlite3_step just exit without returning any error code.

best regards,
Radzi
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] de...@ukr.net

[Mohd Radzi Ibrahim]

select from (select ... )

must have alias. It should be

select from (select ... ) a





On Tue, May 20, 2014 at 9:38 AM, YAN HONG YE  wrote:

> Hi,
> Sorry, I use mysql, not in sqlite, in mysql ,I run this command, the
> console show this error: do you know why?
> Thank you very much!
>
> mysql> UPDATE adlbk20140520a
> -> SET pflopf = (
> -> SELECT pflopf
> -> FROM adl
> -> WHERE adl.ref = adlbk20140520a.ref)
> -> WHERE (
> -> SELECT COUNT(*)
> -> FROM (
> -> SELECT 1 FROM adl
> -> WHERE adl.ref = adlbk20140520a.ref
> -> LIMIT 2
> -> )
> -> ) = 1;
> ERROR 1248 (42000): Every derived table must have its own alias
> mysql>
>
> >   Message: 8
> >   Date: Mon, 19 May 2014 09:55:25 +0300
> >   From: Paul 
> >   To: sqlite-users@sqlite.org
> >   Cc: sqlite-users@sqlite.org
> >   Subject: Re: [sqlite] how to write this commands?
> >   Message-ID: <1400482276.625069613.b8rdb...@frv38.fwdcdn.com>
> >   Content-Type: text/plain; charset="UTF-8"
> >
> >
> >   > UPDATE adla1
> >   > SET pflopf = (
> >   > SELECT pflopf
> >   > FROM adl
> >   > WHERE adl.ref = adla1.ref)
> >   > WHERE (
> >   > SELECT COUNT(*)
> >   > FROM (
> >   > SELECT 1 FROM adl
> >   > WHERE adl.ref = adla1.ref
> >   > LIMIT 2
> >   > )
> >   > ) = 1;
> >   >
> >   > Not all sure what LIMIT 2 does there. I think a SQL-92 version
> >   > would be
> >   >
> >
> >   Limit, limits number of rows selected by ref. If we have found 2, no
> need to lookup further.
> >   That's why it was said to be a slightly optimized version. Generally
> this saves some disk reads.
> ___
> 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] Web application with SQLite

[Mohd Radzi Ibrahim]

fossil is a webserver by itself with sqlite as the database engine. AFAIK,
there's no scripting language used on the server. All written in C.



On Mon, Dec 30, 2013 at 1:02 AM, Tim Johnson  wrote:

> * Stephan Beal  [131229 07:48]:
> > On Sun, Dec 29, 2013 at 5:35 PM, Tim Johnson  wrote:
> >
> > >   :) And if I request http://www.sqlite.org/src/timeline/index.php
> > >   I get the same page, so Igor, Mr. Hipp uses PHP as the 'querying'
> > >   code, or so I presume.
> > >
> >
> > LOL! If you try /timeline/foo/bar/baz you'll get the same thing. That
> page
> > is written in C. The /timeline part of the request is intercepted by C
> code
> > and the rest is ignored.
> >
> > http://fossil-scm.org/index.html/timeline/foo/bar/baz
>   I stand (embarrassingly) corrected 
>
>   FYI: I started out doing web programming in 1995 using C, but
>   transitioned to scripting languages around 2001. I didn't know
>   anyone was still doing web application in C!
>
>   It stands to reason in terms of performance, for sure.
>
>   Thanks
> --
> Tim
> tim at tee jay forty nine dot com or akwebsoft dot com
> http://www.akwebsoft.com, http://www.tj49.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] Hints for the query planner

[ibrahim]

On 10.09.2013 21:26, Richard Hipp wrote:

SURVEY QUESTION:

The question for today is what to call this magic hint function:

(1)  unlikely(EXPR)
(2)  selective(EXPR)
(3)  seldom(EXPR)
(4)  seldom_true(EXPR)
(5)  usually_not_true(EXPR)




A function call like proposed is much better than adjusting the sql 
gramer for implementation details. Therefore this is a better solution.


(6) assume (EXPR, [%value%])
(7) approximate (EXPR, [%value%])

It would be nice to further have a pragma to adjust the probability 
expectations of the following query with text involved. I think people 
who have to deal with more than one database engine would prefer to use 
use such a pragma to adjust such implementation behaviour.


pragma set_text_match_probability %value%
pragma set_pattern_match_probability %value%


Ibrahim

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

Re: [sqlite] UPDATE question

[ibrahim]

Further comment :

If you want to implement a sequence of records in a table you can do it 
much faster with only on record to update when you insert a value in the 
middle of this sequence. For this purpose you shouldn't use a pseudo 
array but a single or double ended queue with only one trigger.


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

Re: [sqlite] UPDATE question

[ibrahim]

workaround for your problem :

create table t1 (pk integer primary key, name text, seq integer) ;
create unique index idxt1 on t1 (name,seq) ;

insert into t1 values (1, 'blue', 1) ;
insert into t1 values (2, 'blue', 2) ;
insert into t1 values (3, 'blue', 3) ;
insert into t1 values (4, 'blue', 4) ;
insert into t1 values (5, 'blue', 5) ;

before updating your unique index just drop and recreate it !

Behaviour till now :

sqlite> select *from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5

sqlite> update t1 set seq=seq+1 where pk in (select pk from t1 where seq 
>= 3 order by seq desc) ;

Error: columns name, seq are not unique

Behaviour with the workaround :

drop index idxt1 ;
sqlite> update t1 set seq=seq+1 where seq >= 3 ;
sqlite> create unique index idxt1 on t1(name,seq) ;
sqlite> select * from t1 ;
sqlite> insert into t1 (name, seq) values ('blue',3) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
6|blue|3
3|blue|4
4|blue|5
5|blue|6


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

Re: [sqlite] UPDATE question

[ibrahim]

On 06.09.2013 20:52, Igor Tandetnik wrote:

On 9/6/2013 1:05 PM, ibrahim wrote:

Perhaps you should really try it out cause my suggestion is totally
different from yours it uses a temporary table it works.

create table t1 (pk integer primary key, name text, seq integer) ;


You missed the whole "Name/Sequence pair of columns is defined as 
UNIQUE" part. Make it


create table t1 (pk integer primary key, name text, seq integer, 
UNIQUE(name, seq) ) ;


See how well your technique is working for you now.

I missed that part.

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

Re: [sqlite] UPDATE question

[ibrahim]

On 06.09.2013 18:30, Dominique Devienne wrote:

On Fri, Sep 6, 2013 at 5:41 PM, ibrahim <ibrahim.a...@googlemail.com> wrote:



You can let sqlite handle the creation of a temporary table by :

update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3
order by seq desc) ;

afterwards you can insert.


Unless I'm misunderstanding you, I already tried that in an earlier post of
this thread, and that didn't work. Give it a try. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Perhaps you should really try it out cause my suggestion is totally 
different from yours it uses a temporary table it works.


create table t1 (pk integer primary key, name text, seq integer) ;
insert into t1 (pk, name, seq) values (1, "blue", 1) ;
insert into t1 (pk, name, seq) values (2, "blue", 2) ;
insert into t1 (pk, name, seq) values (3, "blue", 3) ;
insert into t1 (pk, name, seq) values (4, "blue", 4) ;
insert into t1 (pk, name, seq) values (5, "blue", 5) ;

sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5


sqlite> update t1 set seq=seq+1 where pk in (select pk from t1 where seq 
>= 3 order by seq desc) ;

sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|4
4|blue|5
5|blue|6


sqlite> insert into t1 (pk, name, seq) values (6, 'blue', 3) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|4
4|blue|5
5|blue|6
6|blue|3


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

Re: [sqlite] UPDATE question

[ibrahim]

On 06.09.2013 18:30, Dominique Devienne wrote:

On Fri, Sep 6, 2013 at 5:41 PM, ibrahim <ibrahim.a...@googlemail.com> wrote:



You can let sqlite handle the creation of a temporary table by :

update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3
order by seq desc) ;

afterwards you can insert.


Unless I'm misunderstanding you, I already tried that in an earlier post of
this thread, and that didn't work. Give it a try. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Perhaps you should really try it out cause my suggestion is totally 
different from yours it uses a temporary table it works.


create table t1 (pk integer primary key, name text, seq integer) ;
insert into t1 (pk, name, seq) values (1, "blue", 1) ;
insert into t1 (pk, name, seq) values (2, "blue", 2) ;
insert into t1 (pk, name, seq) values (3, "blue", 3) ;
insert into t1 (pk, name, seq) values (4, "blue", 4) ;
insert into t1 (pk, name, seq) values (5, "blue", 5) ;

sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5


sqlite> update t1 set seq=seq+1 where pk in (select pk from t1 where seq 
>= 3 order by seq desc) ;

sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|4
4|blue|5
5|blue|6


sqlite> insert into t1 (pk, name, seq) values (6, 'blue', 3) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|4
4|blue|5
5|blue|6
6|blue|3



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

Re: [sqlite] UPDATE question

[ibrahim]

On 05.09.2013 20:20, Peter Haworth wrote:

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with
sequence >=3, sorting them by descending sequence, then a loop with an
UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes
6, 4 becomes 5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement
that could do this for me.  I can use the WHERE clause to select sequence
3,4, and 5 but  the UPDATE has to process the rows in descending sequence
order to avoid UNIQUE conflicts.


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


You can let sqlite handle the creation of a temporary table by :

update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3 
order by seq desc) ;


afterwards you can insert.

If you also want to change the pk order just reset that to in the update.

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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[ibrahim]

On 16.08.2013 18:03, Gary Weaver wrote:

Thanks to you and Richard for the links and info. Concurrency is not outlandish 
to expect, especially when it works in OS X and just not in the Ubuntu vm in 
Travis, but I now understand that these are expected problems.
___

You're right concurrency is not outlandish but from the viewpoint of 
sqlite it was therefore this answer and the link.


On the other side most database systems also use serialized access to 
the underlying filesystem while always exclusivly locking the whole 
file, disk aso. The magic behind concurrently accessing the database in 
those systems is to provide a client/server level where only the server 
process has real access to the underlying physical storage while the 
clients only have concurrent access to the server as a process. The fact 
that most filesystem implementations especialy the network file 
protocols are buggy and locking methods on OS es have those many 
problems is proof enough that you need a client server modell if you 
really want to support concurrent access.  Sqlite on the other side is 
for most applications a database engine library and an appreciated way 
to access large amounts of data in a flexible way.


Concurrency is not outlandish but I for myself prefer that sqlite is a 
simple library which at least assures that many applications trying to 
access the same file can do this in a serialized way without corrupting 
the file. And thats not a simple thing to achieve and get freely.


If you are interested on client server protocols wrapped around sqlite 
there are a few and even contributed on the website.


It was not my intention to blame your statement but it was surprising to 
hear it for sqlite.


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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[ibrahim]

On 15.08.2013 21:39, Gary Weaver wrote:

SQLite varies between file is encrypted/not a DB errors and database disk image 
is malformed. It would seem consistent with SQLite not handling concurrent 
processing in this particular environment or with the version of SQLite since 
3.7.7 is fine in OS X.

Is there anything that stands out as something that would keep 30 processes 
from being able to concurrently insert into the same tables?

30 processes being able to concurrently insert into the same tables ???

I suggest you should read this


http://www.sqlite.org/lockingv3.html


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

Re: [sqlite] select prepared statement always returning SQLITE_DONE

[ibrahim]

after sqlite3_reset () all bound parameters keep their values they are 
not unbound or nullified by default so for that purpose you would have 
to call sqlite3_call_bindings ()


see link http://www.sqlite.org/c3ref/clear_bindings.html

this is not necessary if you want to keep all bound values except of 
those you reset with new values.


On 02.08.2013 06:22, ngsbioinformat...@gmail.com wrote:

Hi all - I've got sqlite embedded in an iOS app.  I have a database with 1
table, and am creating a prepared select statement with a where clause on 1
field.  In my loop, my order of operations is: sqlite3_bind_text,
sqlite3_step, sqlite3_reset.

One the first iteration of the loop, sqlite3_step returns SQLITE_ROW and
all the correct values.  On the second and every subsequent iteration,
sqlite3_step returns SQLITE_DONE.  Why is this?  I thought I could call
sqlite3_reset then rebind a variable and called step again. All the
documentation points to this being correct.  Am I missing something?

Ryan



___
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] Where Clause

[ibrahim]

On 02.08.2013 10:14, techi eth wrote:

Come across one issue with conditional query execution.

Query: UPDATE COMPANY SET Name= 'test' WHERE ID = 2;
According to my understanding if no ID = 2 is present in table then error
should return but it return with SQLITE_OK however Name value is not
changed.

Cheers -
Techi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
If there is no column with id = 2 there are no rows to update and that 
does mean your query was executed without any errors (the syntax was 
correct and everything you intended to do could be done) even if nothing 
had to be done.


If you would like to know if changes occured you can use the api functions

sqlite3_changes ()

or

sqlite3_count_changes ()


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

Re: [sqlite] Comparing two tables column by column

[ibrahim]

On 30.07.2013 14:49, fnoyan...@yahoo.com wrote:

Yes, it is slow actually. But I could not do it with SQL. I wish I could do 
more with SQL than code, which would give a good performance improvement.

 From overall application point of view, I may be considering using threads to 
read from database, so that the performance will be improved a bit.

On 30/07/2013, at 4:26 PM, "Stadin, Benjamin" 
 wrote:


That can work, if performance is of no concern. Otherwise it will become 
miserably slow.


Von: Fehmi Noyan ISI 
Antworten an: Fehmi Noyan ISI 
Datum: Dienstag, 30. Juli 2013 01:54
An: Benjamin Stadin , General Discussion of 
SQLite Database 
Betreff: Re: [sqlite] Comparing two tables column by column

The approach I am using to compare tableA_old and tableA_new is;

typedef struct container_t {
// a structure to pass parameters into callbacks
} container;

static int callback_2(){
 // compare two values in the resulting table if they are different
 // write them into a resulting file
}

static int callback_1(){
 for (each column){
 char sql[256];
 sprintf(sql,"select %s from tableA_old where 
pkey=%s",columnName,primarykey);
 sqlite3_exec(db,sql,callback_2,,)
 }
}

int main(){
  // import CS file into DB
  // for each pair of files (each file is a table and files have
  // the same column names in the same order). Actually, these are
  // records for different dates
  char sql[256] = "select * from tableA_new;";
  container c;
  sqlite3_exec(db,sql,callback_1,(void*)c,null)
  
}

From: "Stadin, Benjamin" 
To: Fehmi Noyan ISI ; General Discussion of SQLite Database 

Sent: Tuesday, July 30, 2013 9:00 AM
Subject: Re: [sqlite] Comparing two tables column by column

If you like ruby, I have another idea to get you going (maybe without
needing to write much code):

- Use a registered function to SQLite to create MD5 or SHA1 keys for rows
in the table. Here is a ruby snippet that registers a SHA1 function:
http://www.copiousfreetime.org/articles/2009/01/10/writing-sql-functions-in
-ruby.html
- Join both tables by your primary key and select those where the SHA keys
don't match
- go threw each column programmatically in your ruby code and simply
compare column values with each other

If you then need performance and get by some means a trigger for your
newly inserted records, add a sha1 field to all of your tables and
precompute and index the sha1 for each table.

Benjamin Stadin

Am 30.07.13 01:07 schrieb "Fehmi Noyan ISI" unter :


EXCEPT query gave the different rows in tables, but what I am after is
the different values for existing records.

The column names are exactly the same, however the number of rows may
differ (with most of the records are the same).



From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Monday, July 29, 2013 9:10 PM
Subject: Re: [sqlite] Comparing two tables column by column



On 29 Jul 2013, at 12:36pm, Fabian Klebert
 wrote:


Wouldn't

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

solve this problem?
I think it does for the example provided. Not sure if it would work in
real-world environment.

There are two elements: making sure the same rows are present, and making
sure the contents of the rows match.  I would probably use EXCEPT
commands to find out entries in one database which weren't in the other,
then use INTERSECT command to check that the fields in the rows which
were in both.  But that's just at first glance.

Simon.
___
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
a) Store your table backups ordered by a primary key (not rowid but 
integer primary key).
b) Make a whole table select ordered by the same primary key for the 
current table.

c) Merge both tables.

This will be faster cause you won't need to trace through the BTREE 
(B+TREE) for each recordset.


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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[ibrahim]

On 16.07.2013 13:26, Bernd Lehmkuhl wrote:

On 16 Jul 2013, at 4:39am, Bernd Lehmkuhl  wrote:


Am 15.07.2013 22:26, schrieb Simon Slavin:

The following two statements do different things.

INSERT INTO myTable VALUES (01)
INSERT INTO myTable VALUES ('01')

Can you tell what's being done in your setup ? Is there a way using your API 
that you can stress that the value you're binding or inserting is text, not a 
number ?

As I use a parameterized query, I'm pretty certain that it should be '01' - the 
second case. Stepping through the code in VS Debugger also shows that DbType of 
that parameter is String and Value is '01'.

If the column in the table really is defined as TEXT, and the INSERT commands 
do have apostrophes around the values, then SQLite3 should not be losing that 
zero.

Can you open the database in some other tool (e.g. the sqlite3 command-line 
tool, available from the SQLite site) and see what the table schema says ? Your 
commands should be something like

sqlite3 myDatabaseFile
.schema
.quit

If you have lots of tables you can do ".schema mytable" instead of just 
".schema".

If you want to experiment you can manually type in an INSERT yourself, then do 
a SELECT and see whether the zeros were preserved.

Simon.

Hi Simon,

The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG String, 
BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String, 
IST_KATEGORIE_ESK_BETRIEBSSTOF String);

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select schluessel, typeof(schluessel)
...> from t_vwg_abfallverzeichnis
...> where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172', 
'6061A3864C2546C4A7DDA9FDB321459F');
1|integer
01 01|text
sqlite>

I *expected* to gain TEXT affinity through the use of System.Data.SQLite and 
it's strongly typed types (doppelt gemoppelt?), but apparently this is not 
totally effective. Maybe Joe Mistachkin can say something about that?

Bernd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
String is not a valid type specifier for TEXT colums see 
http://www.sqlite.org/datatype3.html type names which result in TEXT 
affinity.


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

Re: [sqlite] sqlite abnormal IO writing

[ibrahim]

On 21.04.2013 05:15, 刘运杰 wrote:

Ok,I do not make my means clearly. I mean 60 seconds after my program 
started,not token 60 seconds to load database file.

Now, I got the reason of sqlite abnormal IO writing,it about batch insert.
Here is my usecase:
One table about 4 column and 500 row,the content of every row no exceed 100 
byte, every time I update the whole table using batch query.

It should take about one second and 100k IO writing on estimate,BUT it 
sustained about 20 second and wrote about 2.5M actually.


Now,I modify the implement of batch query, it take about one second and 70k IO 
writing.So there are abnormal something in batch query indeed,Sqlite or Qt SQL 
module.



Can you give a table schema ?

It's important to know what kind of primary key and indexes you define 
(if present). The amount of data written leads to the assumption that 
you are using a primary key not of type integer primary key and you have 
a few indexes perhaps to make searching faster.


If you have indexes and another primary key than integer primary key 
than you can improve the performance by delaying index creation until 
after a insert / update or by dropping a index before a bulk update and 
recreating the index afterwards. Otherwise each raw can lead to more 
than x page inserts updates.


Your scheme would be of help


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

Re: [sqlite] SQL Logic error or missing database

[ibrahim]

On 05.04.2013 17:01, Dan Kennedy wrote:

On 04/05/2013 09:08 PM, Rob Collie wrote:

Yeap, I'm on Visual Studio 2012. I've created a console app:


  sqlite3 *oDatabase;
  int returnValue;
  returnValue = sqlite3_open_v2("file://C:/Newfolder/testing.db",
, SQLITE_OPEN_CREATE, NULL);
  if (returnValue != SQLITE_OK )
  {
   //sqlite3_close(oDatabase);
   return returnValue ;
  }
  int anyKey;
  return 0;

It returns 21. Checking the other project, the open actually does 
return 21

too.


This one is returning SQLITE_MISUSE because the SQLITE_OPEN_READWRITE
flag is not being passed. It seems quite odd that the other code
would do the same though.





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


Thanks Dan I just copied and paste his code.

correction :

returnValue = sqlite3_open_v2 ("C:\\Newfolder\\testing.db", , 
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL) ;


and to make the test just more simple :

returnValue = sqlite3_open ("C:\\Newfolder\\testing.xyz", ) ;

try the different file extension could be a problem on some systems.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error or missing database

[ibrahim]

On 05.04.2013 16:47, Rob Collie wrote:

sqlite3_open_v2("file://C:/Newfolder/testing.db",
> > > >& oDatabase, SQLITE_OPEN_CREATE, NULL);


Can you try :


returnValue = sqlite3_open_v2 ("C:\\Newfolder\\testing.db", , 
SQLITE_OPEN_CREATE, NULL) ;


Use double backslash also after "file://"

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

Re: [sqlite] SQL Logic error or missing database

[ibrahim]

On 05.04.2013 14:54, Rob Collie wrote:

Hello there,

For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.

Calling this from fortran...

CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?


extern "C"
{

  void EXECUTESQL(char *dataBase, char *query, int returnValue)
  {

   // Checking the incoming data from FORTRAN
   CStringW wName(dataBase);
   MessageBoxW( NULL, wName, L"Name: ", MB_OK );

   // Create the object
   sqlite3 *oDatabase;

   // Create the error objects
   char *sErrorMessage;
   // Open/create the table, if required
   returnValue = sqlite3_open_v2(dataBase, ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, "");
   if (returnValue != SQLITE_OK )
   {
sqlite3_close(oDatabase);
MessageBoxA(NULL, sqlite3_errstr(returnValue), "SQL Open Error", MB_OK);
return;
   }


Rob.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Can you change the file extension into something different ? "z45" as an 
example. Sometimes registered filetypes create issues on windows.



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

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

[ibrahim]

Those measurements asume that you store each blob in a seperate file. So 
the raw file access seems slower for smaller blob sizes.


If you use external blob storage do it in raw clusters like i suggested 
in a previous post (size limit 32/64 MB) and store your blobs on page 
boundaries (page size 4 k 8 k aso) this will always be faster cause you 
have no b-tree pages which are always fragmented but sequential stored 
image data.


Don't use file sizes larger than 32/64 MB because the pre fetch cache of 
modern HD's can read the whole file even if you only ask for a port of 
it and the fopen command will get slower when you open large files cause 
you have to read the pagelist into an internal library buffer.


The given link is only true if you store each blob in a seperate file.

I use for my similar project raw cluster modell and thats x times faster 
than storing image data in a b-tree organized database file.

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

Re: [sqlite] Performance with journal_mode = off

[ibrahim]

In reference to your needs one more suggestion :

>> If you put the blobs outside of a sqlite database and store your 
householding, indexing data inside your sqlite data i would suggest to 
use journal mode = on because your journal file and database file 
without the blobs has a small amount and if you loose your database due 
to a crash inside your application you would possibly loose the 
housekeeping data for your raw data in files and that would make the 
whole really big amount of storage useless.
>> Keep your indexes and housekeeping data secure (my advice) if you 
don't want to gamble !


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

Re: [sqlite] Performance with journal_mode = off

[ibrahim]

From :  http://www.sqlite.org/pragma.html#pragma_journal_mode

The OFF journaling mode disables the rollback journal completely. No 
rollback journal is ever created and hence there is never a rollback 
journal to delete. The OFF journaling mode disables the atomic commit 
and rollback capabilities of SQLite. The ROLLBACK 
 command no longer works; 
it behaves in an undefined way. Applications must avoid using the 
ROLLBACK  command when the 
journal mode is OFF. If the application crashes in the middle of a 
transaction when the OFF journaling mode is set, then the database file 
will very likely go corrupt.


Meaning :

You can use Transaction also with journal mode OFF.
>> then there will be no journal file
>> there will be no ROLLBACK command
>> no atomic commit (see description of commit mechanism from the prior 
sent link)
>> a transaction which is possible can leave you with a corrupt 
database if your application crashes due to software or power failures
>> if you are sure you don't need the security of a journal file cause 
your application is crash proof and you won't get a power disk failure 
(assumption is a gun to shoot your feet) you can ommit a journal file 
but you will be able to use transacations.
>> transactions improve your performance for bulk data transfer into a 
database significantly.


hope this will help ;)

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

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

[ibrahim]

On 29.03.2013 20:17, Jeff Archer wrote:

I have previously made an apparently bad assumption about this so now I
would like to go back to the beginning of the problem and ask the most
basic question first without any preconceived ideas.

This use case is from an image processing application.  I have a large
amount of intermediate data (way exceeds physical memory on my 24GB
machine).  So, I need to store it temporarily on disk until getting to next
phase of processing.  I am planning to use a large SSD dedicated to holding
this temporary data.  I do not need any recoverability in case of hardware,
power or other failure.   Each item to be stored is 9 DWORDs, 4 doubles and
2 variable sized BLOBS which are images

I could write directly to a file myself.  But I would need to provide some
minimal indexing, some amount of housekeeping to manage variable
sized BLOBS and some minimal synchronization so that multiple instances of
the same application could operate simultaneously on a single set of data.

So, then I though that SQLite could manage these things nicely for me so
that I don't have to write and debug indexing and housekeeping code that
already exists in SQLite.

So, question is:  What is the way to get the fastest possible performance
from SQLite when I am willing to give up all recoverability guarantees?
Or, is it simple that I should just write directly to file myself?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Suggestion : Put the fixed Data with small sizes into a sqlite 
database.  You won't search in the blobs with a database engine and the 
amount of data you have to process is large to make it fast you should 
write the image data into files. The other data which is necessary for 
processing ordering, indexing, searching comparision is best stored in a 
sqlite database.


To improve the speed of access for your images use full pages fill 
lesser images to the next page boundaries (as an example 4 k, 8 k ...) 
splitt long files into smaller clusters (16 to 64 MB) sequentially 
numbered this makes OS file operations faster because you have to cache 
the block index while opening and processing a file the positions can be 
indexed in sqlite.


I have a similar application for vectorized digitalization of 
handwritten old scripts and i use a database for searchable information 
while using external files (splitt as described) for raster images and 
vector files sqlite would be to slow for blobs like you need them put 
them outside but the indexes inside. Another advantage of this approach 
is that you can process many binary files simultanously while by putting 
them inside a database like sqlite you have only one writer.


The use of transactions makes inserting of data faster especially when 
you have indexes. Then try to create your indexes after fully inserting 
your data because that makes the process faster.



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

Re: [sqlite] Performance with journal_mode = off

[ibrahim]

On 29.03.2013 19:42, Jeff Archer wrote:

From: "James K. Lowden" 
To: sqlite-users@sqlite.org

Your experiment is telling you different: transaction control costs more

than I/O.
But shouldn't transactions be disabled when journal_mode = off?  Maybe that
is a faulty assumption.  If so, what is the point of journal_mode = off?
For this purpose, I am very happy to give all the ACID promises.

If I understand your point #2, I think you are saying that all of the
inserts within a single transaction are not written to the disk (database
or journal) until the transaction is committed.  But that can't quite be
the answer because if kept my transaction open long enough I would simple
run out of memory and that doesn't seem to happen even when I have 1
million plus inserts.
___

If you keep your transaction open look at the database file size you'll 
see that the changes aren't written to the File until you commit to disk.


1 Million records are to few for modern systems to reach the out of 
memory limit. Lets say your records have 1 k size that would make 1G of 
Memory with overhead and virtual memory why would you expect out of 
memory ???


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

Re: [sqlite] Performance with journal_mode = off

[ibrahim]

On 28.03.2013 14:03, Jeff Archer wrote:

On Thu, Mar 28, 2013 at 8:24 AM, ibrahim <ibrahim.a...@googlemail.com> wrote:

On 28.03.2013 13:09, Jeff Archer wrote:

On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin <slav...@bigfraud.org>
wrote:

Reasonable figures.  With 5764 writes to the disk in separates
transactions you have quite a lot of reading of data plus 5764 attempts to
update the database file.  The updates have to be done in the right order,
and each update has to wait for the disk to be rotated into the right
position, though each update changes only a small amount of data (probably
two sectors).

But my most basic question remains.  Why is single transaction faster
than PRAGMA journal_mode = off?

Seems to me that with no journal there should only be single set of
writes to the actual db and that journaling would double the number of
writes because data has to be written to journal file as well.

2.5 sec with journal
5.5 sec without journal   <= seems like this sould be the smaller number


You should read the sections 3 forward.

http://www.sqlite.org/atomiccommit.html

A single transaction happens mostly in memory then is flushed to the OS
Cache in one step. The Journal file (the amount of pages that will be
changed) is small while inserting new data into a database and the OS File
Cache is usually large enough ...


Yes, I have read this.  (And now re-read it)

So, since much more work must be done when using journal file, why
does it take longer to do the inserts when there is NO journal file?
___


You compare different approaches. You should compare

a) Journal mode=on  w/wo single transaction
b) Journal mode=off w/wo single transaction

Single transaction outweights journal mode in terms of performance. It 
reduces the amount of necessary physical read write operations on the OS 
File Cache and the Disk.



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

Re: [sqlite] Performance with journal_mode = off

[ibrahim]

On 28.03.2013 13:09, Jeff Archer wrote:

On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin  wrote:

Reasonable figures.  With 5764 writes to the disk in separates transactions you 
have quite a lot of reading of data plus 5764 attempts to update the database 
file.  The updates have to be done in the right order, and each update has to 
wait for the disk to be rotated into the right position, though each update 
changes only a small amount of data (probably two sectors).

But my most basic question remains.  Why is single transaction faster
than PRAGMA journal_mode = off?

Seems to me that with no journal there should only be single set of
writes to the actual db and that journaling would double the number of
writes because data has to be written to journal file as well.

2.5 sec with journal
5.5 sec without journal   <= seems like this sould be the smaller number


You should read the sections 3 forward.

http://www.sqlite.org/atomiccommit.html

A single transaction happens mostly in memory then is flushed to the OS 
Cache in one step. The Journal file (the amount of pages that will be 
changed) is small while inserting new data into a database and the OS 
File Cache is usually large enough ...



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

Re: [sqlite] Performance with journal_mode = off

[ibrahim]

On 27.03.2013 22:55, Jeff Archer wrote:

On Wed, Mar 27, 2013 at 5:46 PM, David King  wrote:

I am populating a database with 5764 records using the exact same data set
each time into a newly created file.
When I use no explicit transactions (default atomic commit) it takes 17.7
seconds.
When I set journal_mode = off, same operation takes 5.5 seconds.
If I do all 5764 inserts within a single transaction only 2.5 seconds.


That sounds about right, yeah. With journalling, most disk writes have to be 
done twice (once to the journal and once to the data file).

Which is why I expected journal_mode = off to make it faster.  But it
is 3 seconds faster when I leave journaling enabled and do all writes
within a single transaction.

Seems like you have defined some sort of index. You will get faster bulk 
inserts when you create your indexes afterwards.


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

Re: [sqlite] SQL:2003 -- Window Functions

[Mohd Radzi Ibrahim]

On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik wrote:

> On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote:
>
>> Too bad SQLite doesn't yet support SQL Window Functions.
>>
>> Are there any SQLite Extension Libraries that support "SQL:2003 type
>> Window Functions"?
>> I specifically need LEAD and LAG to calculate an event integer timestamp
>> delta between consecutive rows.
>> I've played with some self-join code but that's proving to be complicated.
>>
>
> The easiest approach is to maintain the window in your application code,
> as you iterate over a simple SELECT statement.
> --
> Igor Tandetnik
>
>
>
Could it not be done with inner select of ROWID-n and ROWID+n to get the
LEAD and LAG row ?

select
 logtime as timeNow,
 (select logtime from logtable where rowid=a.rowid-1) as timeBefore,
 (select logtime from logtable where rowid=a.rowid+1) as timeAfter
from logtime;

best regards,
Radzi.


__**_
> 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 in a Win7 Service Application

[Mohd Radzi Ibrahim]

On Wed, Aug 15, 2012 at 11:19 PM, markus ruettimann <
markus.ruettim...@trapezegroup.com> wrote:

> Hi,
> We are facing problems when the sqlite database () is used within an
> application that runs as a Win7 service under the local system account.
> The application is written in Java and we use the sqlite-jdbc-3.7.2 JDBC
> driver.
>
> How ever we can create a new database, reading and writing to it withount
> any problem. The only thing that is not working is the ATTACH DATABASE SQL
> command. We constantly receive a CAN_NOT_OPEN error code.
>
> When we start the same application under a local user everything works
> fine. I have to add the both databases that are to be attached are in the
> same folder and have been created by the Application itself. There are no
> access restrictions on the database folders or files.
>
> Does anyone know about this and / or has a solution?
>
>
Could you check the security issue or path for the attached database.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An interesting (strange) issue with selects

[Mohd Radzi Ibrahim]

Could it be that the .ext is used by the OS or other apps with some caching
scheme? Try different extension...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I consider this a bug. Anyone else?

[Mohd Radzi Ibrahim]

On Tuesday, June 26, 2012, Andrew Rondeau wrote:

> Because my time is valuable; and my peers' time is valuable as well.
>
> For example, if I make a Mac application, I can target the SQLites shipped
> with Mac. Problem solved, my valuable time can go to something else.
>
>
Since I don't want any outside library or DLLs interfere with my app, I
would rather have my sqlite library linked statically into my app. I just
need to have sqlite3.c and sqlite3.h in my project code.




> On Mon, Jun 25, 2012 at 12:35 PM, Roger Binns 
> >
> wrote:
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On 25/06/12 11:21, Andrew Rondeau wrote:
> > > IMO, I've been somewhat dissapointed with sqlite's deployment scenario
> > > on Windows. Perhaps this is an area that needs a bit more community
> > > involvement?
> >
> > You really are looking this the wrong way.  SQLite's source is as close
> to
> > public domain as you can get, and the amalgamation means there is only
> one
> > source file to deal with.
> >
> > You can do whatever you want - you don't need anyone else's permission or
> > cooperation.  Why do you want to gate your usage through other people
> with
> > their own priorities and availability?
> >
> > Roger
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.4.11 (GNU/Linux)
> >
> > iEYEARECAAYFAk/ovYEACgkQmOOfHg372QQrOACffzSBY6ZTR6/M6QrN4yxresMc
> > encAoLOLW8GQs7tVMHCur9jxt0uBCIra
> > =IV37
> > -END PGP SIGNATURE-
> > ___
> > 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] insert if record not exists without primary key

[Mohd Radzi Ibrahim]

Hi,

I guess, if you could sort your input data by col1 and col2; going thru the
list and insert only when col1 and col2 are different from previous row,
should speed up the loading too.

Regards,
Radzi.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Tuesday, 10 January, 2012 6:45 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] insert if record not exists without primary key


On 10 Jan 2012, at 10:34am, Durga D wrote:

> What about second approach.
> 
> create table if not exists emp (id integer primary key autoincrement, 
> col1 text, col2 text); //without unique.
> 
> I tried with insert or ignore into emp (col1, col2) values ('a', 'b');
> 
> I noticed, this is fast.

Here you are not asking SQLite to check for uniqueness every time you do an
INSERT.  So you are saving it a lot of work.  Of course, the result is a
database which may include a lot of duplicate rows.  Assuming you are
writing software, the simplest way to remove these duplicates would be to
execute

CREATE INDEX IF NOT EXISTS empCol1Col2 ON emp (col1,col2);

You can then write some software which does

SELECT id,col1,col2 FROM emp ORDER BY col1,col2;

and works its way down the list, deleting all rows where col1 and col2 are
the same as they were for the previous row.  (You can perhaps figure out a
single DELETE FROM command which will do all this, but I see no reason it
should take much less time than an efficiently written program.)

Of course this clean-up operation will probably take so much time you'd have
been better off with your first approach.

Simon.
___
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] Problem with FTS4 - Floating point error.

[Mohd Radzi Ibrahim]

On 25-Oct-2011, at 2:53 PM, Dan Kennedy wrote:

> This was fixed and then I forgot to follow up here. I should have.
> Sorry about that. It's fixed here:
> 
>  http://www.sqlite.org/src/ci/3126754c72?sbs=0
> 
> Either updating to the latest trunk or just applying the linked
> patch to fts3.c should fix your crash.
> 
> Dan.
> 

I've clone the fossil repository and tried to run make. I am having problem at 
linking stage with these errors:

/tmp/ccwXZcmF.o: In function `one_input_line':
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:393: undefined 
reference to `readline'
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:395: undefined 
reference to `add_history'
/tmp/ccwXZcmF.o: In function `main':
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:2949: undefined 
reference to `read_history'
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:2953: undefined 
reference to `stifle_history'
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:2954: undefined 
reference to `write_history'

I am running on Ubuntu 10.10 and have installed readline6.2.

Any suggestion?

thanks.

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

Re: [sqlite] Problem with FTS4 - Floating point error.

[Mohd Radzi Ibrahim]

On 18-Oct-2011, at 6:52 PM, Dan Kennedy wrote:

> 
> 
> On 10/18/2011 05:02 PM, Mohd Radzi Ibrahim wrote:
>> Hi,
>> 
>> This is my table schema:
>> 
>> CREATE VIRTUAL TABLE LocationFTS using FTS4
>> (
>>  name text,
>>  address text,
>>  email text,
>>  remark text,
>>  telno text,
>>  contact text,
>>  isDeleted text
>> );
>> 
>> This select statement gives floating point error in both sqlite3.exe and 
>> also my program (both my Ubuntu and Windows versions failed) :
>> 
>> select docid, name, address, email, remark, telNo, contact
>> from locationFTS
>> where locationFTS match 'suai plantation sdn bhd, p.o.box 730 97008 bintulu 
>> swk, tel 2 07-6637460 ah leng   06-9766061 a3.0';
>> 
>>  PharmacyServer.exe!GenericKedaiRuncitDB::GetLocationFTS(LocationRecord& 
>>  r)  Line 147 + 0x14 bytes  C++
>>  PharmacyServer.exe!wxSQLite3Statement::ExecuteQuery(bool 
>> transferStatementOwnership)  Line 1486 + 0xc bytes C++
>>  PharmacyServer.exe!sqlite3_step(sqlite3_stmt * pStmt)  Line 62029 + 0x9 
>> bytes   C
>>  PharmacyServer.exe!sqlite3Step(Vdbe * p)  Line 61954 + 0x9 bytes
>> C
>>  PharmacyServer.exe!sqlite3VdbeExec(Vdbe * p)  Line 3856 + 0x30 bytes
>> C
>>  PharmacyServer.exe!fts3FilterMethod(sqlite3_vtab_cursor * pCursor, int 
>> idxNum, const char * idxStr, int nVal, Mem * * apVal)  Line 51378 + 0x9 
>> bytesC
>>  PharmacyServer.exe!fts3EvalStart(Fts3Cursor * pCsr)  Line 52714 + 0x13 
>> bytesC
>>  PharmacyServer.exe!fts3EvalSelectDeferred(Fts3Cursor * pCsr, Fts3Expr * 
>> pRoot, Fts3TokenAndCost * aTC, int nTC)  Line 52634 + 0x2a bytesC
>> 
>> Does anybody know what's going on here?
> 
> Can you send me the database by email? Not via the list,
> as it will strip the attachment.
> 
> Dan Kennedy.
> 

Hi, 

I've been chasing this bug and tried couple of options and found out that these 
issues fix my problem:

1. The number of words in match string is capped at 16.
2. These characters in the match string I replaced with spaces.
case ',':
case '.':
case '/':
case '-':
case ':':
case '\'':
case '"':
case '(':
case ')':
case '\\':
case '@':

Was it a bug or was it the limitation? Or Perhaps I missed the documentation on 
FTS4. 

best regards,
Radzi.

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

[sqlite] Problem with FTS4 - Floating point error.

[Mohd Radzi Ibrahim]

Hi,

This is my table schema:

CREATE VIRTUAL TABLE LocationFTS using FTS4
(
name text, 
address text,
email text, 
remark text, 
telno text, 
contact text, 
isDeleted text
);

This select statement gives floating point error in both sqlite3.exe and also 
my program (both my Ubuntu and Windows versions failed) :

select docid, name, address, email, remark, telNo, contact 
from locationFTS 
where locationFTS match 'suai plantation sdn bhd, p.o.box 730 97008 bintulu 
swk, tel 2 07-6637460 ah leng   06-9766061 a3.0';


It runs OK without if nothing matches, but when I've populated the table with 
some 10,000 records, and this record is matching, the select statement failed. 
When I reduces the match string to 80, it's Ok. But it's not the length that 
causes it to fail, because there are other statement that has match string more 
that 100 characters.

On Windows (VS2010) I have this stack of calls that causes the error:

 
PharmacyServer.exe!GenericKedaiRuncitDB::GetLocationFTS(LocationRecord 
& r)  Line 147 + 0x14 bytes  C++
PharmacyServer.exe!wxSQLite3Statement::ExecuteQuery(bool 
transferStatementOwnership)  Line 1486 + 0xc bytes C++
PharmacyServer.exe!sqlite3_step(sqlite3_stmt * pStmt)  Line 62029 + 0x9 
bytes   C
PharmacyServer.exe!sqlite3Step(Vdbe * p)  Line 61954 + 0x9 bytes
C
PharmacyServer.exe!sqlite3VdbeExec(Vdbe * p)  Line 3856 + 0x30 bytes
C
PharmacyServer.exe!fts3FilterMethod(sqlite3_vtab_cursor * pCursor, int 
idxNum, const char * idxStr, int nVal, Mem * * apVal)  Line 51378 + 0x9 bytes   
 C
PharmacyServer.exe!fts3EvalStart(Fts3Cursor * pCsr)  Line 52714 + 0x13 
bytesC
PharmacyServer.exe!fts3EvalSelectDeferred(Fts3Cursor * pCsr, Fts3Expr * 
pRoot, Fts3TokenAndCost * aTC, int nTC)  Line 52634 + 0x2a bytesC

Does anybody know what's going on here?

Thanks.

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

Re: [sqlite] No error on selecting non-grouped column

[Mohd Radzi Ibrahim]

On 15-Sep-2011, at 2:55 AM, Magnus Thor Torfason wrote:

> 
> I then ran a query grouping employees by job:
> 
>  > select ename, job from emp group by job;
>  "ENAME", "JOB"
>  ==
>  "FORD", "ANALYST"
>  "MILLER", "CLERK"
>  "CLARK", "MANAGER"
>  "KING", "PRESIDENT"
>  "TURNER", "SALESMAN"
> 
> Now, I get a list of the jobs, and a random selection of employees. I would 
> have expected an error here. Of course, my actual 

It's not random selection, it's the last one from the list.

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

[sqlite] FTS4 reserved characters

[Mohd Radzi Ibrahim]

Hi,
I was searching for any reference to reserved characters used in FTS4, but 
failed to find any. 

I have problem with query with - ftstable match 'width 5" '

But it's ok with - ftstable match 'width 5'

to fix this, I replaces every double-qoute in query with empty space.

My question, is there any other characters that has special meaning in FTS 
match query?

thanks.

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

Re: [sqlite] Import data in SQLite from excel using C# code

[Mohd Radzi Ibrahim]

Hi,
Try use www.libxl.com that has C/C++/C#/Delphi interface to read excel files. 
The rest are just normal sqlite commands.


On 28-Mar-2011, at 9:17 AM, Deepti Marathe wrote:

> 
> Hi, 
> 
> I am new to SQLite and am using it for the first time. I need to create an
> application using C# that will export the data from EXCEL to SQLite.  Please
> can anybody guide me.
> Thanks in advance!
> 
> -- 
> View this message in context: 
> http://old.nabble.com/Import-data-in-SQLite-from-excel-using-C--code-tp31253267p31253267.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

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Mohd Radzi Ibrahim]

On 10-Mar-2011, at 6:52 AM, Jay A. Kreibich wrote:

> On Wed, Mar 09, 2011 at 11:37:46PM +0100, Armin Kunaschik scratched on the 
> wall:
>> On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik  wrote:
> 
>>> INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT.
> 
>> Is it really DELETE and INSERT internally?
> 
>  Yes and no.  "REPLACE" alters the conflict resolution on UNIQUE
>  constraints.  In all cases, the new row is INSERTed.  In the case of
>  a REPLACE, if the INSERT would cause a UNIQUE constraint violation
>  than any and all rows that are part of the conflict are first
>  deleted before the new row is INSERTed.  In some situations a single
>  INSERT OR REPLACE can actually cause multiple existing rows to be
>  deleted before the new row is inserted.
> 
>  So it is always an INSERT, but sometimes the INSERT triggers one or
>  more internal DELETEs first.


So, that's explain why my column ID INTEGER PRIMARY KEY, changes when I use 
REPLACE. I could not rely on the ID with this command. 

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

Re: [sqlite] Fwd: Re: Bug: Umlaut in database filename

[Mohd Radzi Ibrahim]

On 21-Dec-2010, at 5:02 AM, Richard Hipp wrote:
> (2) Create your own custom mini-SQL-server using SQLite and your own
> protocol for your applications to talk to that min-server over the network.


The key here is to define your own app-specific protocol for communication 
between client and server, where the client knows nothing about the SQLite and 
the server implement the protocol using SQLite. 

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

Re: [sqlite] Bundling sqlite database together with exe file.

[Mohd Radzi Ibrahim]

On 25-Oct-2010, at 1:28 PM, Neville Franks wrote:

> I'd also suggest option 3. And make sure you extract the file to a
> folder that the user has permission to access.
> 

Yes, that's possible. Will it be noticeably slow to write say 200MB db to 
another file? And also another reason, my client don't want anybody to copy the 
database, except together with the exe. 



> Monday, October 25, 2010, 1:17:13 AM, you wrote:
> 
> KN> On Sun, 24 Oct 2010 20:57:15 +0800, Mohd Radzi Ibrahim
> KN> <mra...@pc.jaring.my> wrote:
> 
>>> Hi,
>>> I am planning to deploy my sqlite database together
>>> with the exe file. Is there a way to open the sqlite
>>> database with a file handle and starting offset of
>>> the file, as read-only?
>>> 
>>> Thank you for any suggestion.
> 
> KN> Not out of the box.
> KN> I can think of three solutions:
> 
> KN> 1- Difficult: change the sqlite3 library in such a way so it
> KN> uses the whole executable as the "first page" of the
> KN> database
> 
> KN> 2- Easier: Store the output of the .dump command of the
> KN> sqlite3 shell in a 'resource' in the executable and on
> KN> execution of your program read the resource and load it in
> KN> an in-memory database (filename ":memory:" ) by calling
> KN> sqlite3_exec() on each of its statements.
> 
> KN> 3- Easier: Store the database file as-is in a resource in
> KN> the executable and on execution of your program write it to
> KN> disk and open it in the normal way. 
> 
> KN> HTH
> 
> 
> -- 
> Best regards,
>  Neville Franks, http://www.surfulater.com http://blog.surfulater.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] Bundling sqlite database together with exe file.

[Mohd Radzi Ibrahim]

Hi,
I am planning to deploy my sqlite database together with the exe file. Is there 
a way to open the sqlite database with a file handle and starting offset of the 
file, as read-only?

Thank you for any suggestion.


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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Mohd Radzi Ibrahim]

Have you not consider loading the whole rows into memory array and use simple 
string search or regexp? I'm sure 10,000 records could be search a blink.

best regards,
Radzi.
On 6-Aug-2010, at 3:42 AM, Sam Roberts wrote:

> I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or
> sqlite expert, so I might be thinking about it all wrong.
> 
> I have something like a (read-only) address book/rolodex, with interactive
> searching. As users type into the search box, I need to first know for each
> section how many rows match the substring typed so far.  I only display the
> rows that are visible on screen.
> 
> I have two queries:
> 
> (A) I count the rows in a letter group.
> 
> If they typed "e":
> 
> select substr(name,1,1), count(*) from my_table where name like '%e%'
> group by substr(name,1,1);
> A|94
> B|118
> C|131
> ...
> 
> This is too slow, ~3sec, with 2500 rows, and we want to have 1 rows.
> 
> Worse, when they type "es", the search is as slow after they type "s" as when
> they typed "e", even though the "es" rows are a sub-set of the rows that
> matched "e".
> 
> FTS3 only searches full terms/words by default, but I think if I built a 
> custom
> tokenizer that returned all the suffix trees for a name:
> 
> "fu bar" => [ "r", "ar", "bar", " bar", "u bar", "fu bar"]
> 
> That I could do rewrite query (A) like this:
> 
> select substr(name,1,1), count(*) from my_table where name match 'e*'
> group by substr(name,1,1);
> 
> Is this a reasonable approach? Is there a better way? Has somebody
> else done this?
> 
> 
> 
> (B) I access specific rows within a letter group.
> 
> For visible rows, I fetch them by offset into a letter group, so row 4 in the
> "g" section of names containing "e" would be:
> 
> select * from my_table where name like "g%" and name like "%e%" order
> by name limit 1 offset 4;
> 
> The performance for this is OK, right now, I think it's because the first LIKE
> can use the index, so the linear scan is over only a few hundred rows. Or it
> could be that the on-screen display of each row is slower than the DB search. 
> I
> think it might become a problem, though.
> 
> I'm not sure how I would rewrite this to use FTS3 if it turns out to be to 
> slow
> for a larger DB, maybe a tokenizer that puts the first letter of  the name as
> the first letter of every suffix?
> ___
> 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] EXTERNAL: setup sqlite in vc++

[Mohd Radzi Ibrahim]

Hi, 

You could also use another open source layer for SQLite3, wxSQLite3 project on 
sourceforge. It's uses wxWidgets cross-platform C++ toolkit.

best regards,
Radzi. 
On 7-Jul-2010, at 6:09 AM, smengl90 wrote:

> 
> I found out that including the header file alone is not enough. I need to
> link the sqlite lib to my project. But how can can I get the lib? 
> 
> 
> 
> smengl90 wrote:
>> 
>> Thanks for prompt replies. I did as you advised, and included the .h file
>> as a source file and copied your code. But when I compiled it, I got these
>> linking errors:
>> 
>> 1>Linking...
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol
>> _sqlite3_close referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_free
>> referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_exec
>> referenced in function _main
>> 1>TestSqlite.obj : error LNK2019: unresolved external symbol _sqlite3_open
>> referenced in function _main
>> 1>C:\Documents and Settings\My Documents\Visual Studio
>> 2008\Projects\TestSqlite\Debug\TestSqlite.exe : fatal error LNK1120: 4
>> unresolved externals
>> 
>> Any ideas what I did wrong?
>> 
>> Thanks
>> 
>> 
>> Black, Michael (IS) wrote:
>>> 
>>> You don't need C++ wrappers...just code in C.  There's lots more C
>>> examples out there than C++.
>>> 
>>> Easiest way for me to integrate is to download the amalgamation and
>>> include it as  a source file in your project.
>>> 
>>> The amalgamzation is at the top of the download page:
>>> 
>>> http://www.sqlite.org/download.html
>>> 
>>> Simple program for you to compile as C++ which I wrote from a question
>>> somebody had on the list here.
>>> 
>>> #include 
>>> #include 
>>> #include "sqlite3.h"
>>> int main()
>>> {
>>>sqlite3 *db;
>>>char *errmsg=NULL;
>>>int rc;
>>>int i;
>>>sqlite3_open("test.db",);
>>>rc=sqlite3_exec(db, "CREATE TABLE log (id_client varchar, utc
>>> number)",NULL,NULL,);
>>>if (rc != SQLITE_OK) {
>>>puts(errmsg);
>>>sqlite3_free(errmsg);
>>>}
>>>sqlite3_exec(db,"BEGIN",NULL,NULL,);
>>>for(i=0;i<10;i++) {
>>> int j=i*10;
>>> char sql[4096];
>>> sprintf(sql,"INSERT INTO log VALUES ('%d',%d)",i,j);
>>> rc=sqlite3_exec(db, sql,NULL,NULL,);
>>> if (rc != SQLITE_OK) {
>>> std::cout << errmsg << std::endl;
>>> sqlite3_free(errmsg);
>>> exit(-1);
>>> }
>>>}
>>>sqlite3_exec(db,"COMMIT",NULL,NULL,);
>>>sqlite3_close(db);
>>>return 0;
>>> }
>>> 
>>> 
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>> 
>>> 
>>> 
>>> 
>>> From: sqlite-users-boun...@sqlite.org on behalf of smengl90
>>> Sent: Tue 7/6/2010 2:33 PM
>>> To: sqlite-users@sqlite.org
>>> Subject: EXTERNAL:[sqlite] setup sqlite in vc++
>>> 
>>> 
>>> 
>>> 
>>> Hi guys,
>>> 
>>> I am trying to setup sqlite to be used with VC++ 2008. Can someone show
>>> me
>>> where I can find instructions on how to set it up? and do I need a c++
>>> wrapper to code in C++? If yes, can someone also show how to setup the
>>> wrapper?
>>> 
>>> Thanks
>>> --
>>> View this message in context:
>>> http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29086729.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
>>> 
>>> 
>>> 
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>>> 
>> 
>> 
> 
> -- 
> View this message in context: 
> http://old.nabble.com/setup-sqlite-in-vc%2B%2B-tp29086729p29090562.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

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

Re: [sqlite] Prohibitive indexing time with large tables

[Ibrahim A]

Am 14.02.2010 18:53, schrieb Max Vlasov:
>> This is appx. 500MB cache, why not trying with 2,000,000 cache size ? :-)
>>
>>
>>  
> Hmm, managed to increase it to only 1,000,000 (x1024) size, larger values
> bring to "Out of memory" finally, and this values (1G) allows up to
> 6,000,000 fast records for 100 bytes field per record index. Still good,
> such extreme cache method can work in many cases I think.
>
>
For such problems i would suggest a different solution practiced by many 
companies :

Simply buying a Battery buffered RAM Drive. The Price (as an Example for 
the ASUS Drive) is about 250 $. For a governmental Solution 250 $ + RAM 
Costs won't really matter. The speed improvement (RAM Access is at least 
1 times faster than HD access) will make the costs ignorable.

On the software side by simply inserting new Indexes a few thousand at a 
time in a completely new BTREE (memory) and Merging existing Pages with 
this new ones would make special sorting or other techniques 
unnecessary. Its the simplest and fastes solution.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] String Vs Integer Index

[Ibrahim A]

Am 10.02.2010 23:17, schrieb Simon Slavin:
> But that's true only if you're running a SELECT which actually uses 
> that column and only that column to do the searching. Which is why I 
> asked that question earlier on in this thread.
> Simon.
>
The implementation of sqlite uses a B+Tree for the stored data and a 
BTree for primary keys, indexes etc. If you create a table with a 
"INTEGER PRIMARY KEY" as "CREATE TABLE" states you will end up wit only 
one table where the "INTEGER PRIMARY KEY" is the reference for your 
B+Tree where your Data resists.

If you search for more columns than the primary key value you'll get a 
faster query because the engine won't need to look up references from 
your defined primary key to the rowid in a second Btree table but use 
the primary key directly as a reference to the B+Tree. The performance 
gain is not restricted only for select but also for update, delete, insert.

You can measure this easily with a table where you define a primary key 
with "int primary key" (results in at least one b+tree and one btree) 
and the same table with "integer primary key" (results in one b+tree).

The op asked for a performance difference using integer keys and string 
keys and for sqlite a "integer primary key" is the recommended solution 
due to implementation and documentation.

It's clear that you'll loose this performance gain when you don't use 
the primary key to access your data but that's not what the op asked for 
as far as i understood his question.

Ibrahim.

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

Re: [sqlite] String Vs Integer Index

[Ibrahim A]

Am 10.02.2010 18:19, schrieb Alberto Simões:
> Supose a table with a key that is a string (say, words from 1 to 10
> characters) or a table with a key of integers.
>
> How different is the efficiency on fetching one record on these tables?
>
>
If you look into the documentation for "create table" you'll find the 
right answer :

An "INTEGER PRIMARY KEY" is at least twice as fast as another type of 
PRIMARY KEY,
the reason is based on the implementation of the engine. An integer 
primary key substitutes the rowid column of a table.

While another type of primary key always results in a second reference 
table (key ==> rowid) the integer primary key doesn't need a second 
index table. This results in a performance gain because the engine 
hasn't to look up the rowid in the index table to find the correct 
rowsets assigned to a primary key but looks directly in the data table.

If you can't won't use a integer primary key as described in the 
documentation the performance difference between strings and numbers 
won't be a big deal not as long as your strings have a lenght between 1 
and 10 characters cause the greatest part of the time needed to look up 
a key ==> rowid pair is spent with io operations to read the fileblocks. 
When your strings get longer you'll find, that string keys will get 
slower because the number of key ==> rowid pairs fitting in a btree 
decreases with the length of the entries. This will only matter if you 
get more levels of pages counted from the root page. As long as you 
result in the same height of btrees you won't measure a big performance 
loss due to the use of strings as primary keys when you don't use the 
"integer primary key" feature.

The argument with Timing of assembler instructions would only matter if 
you use memory databases but even then you'll end up with "virtual 
memory paging" or "caching".

If there are no reasons to avoid "INTEGER PRIMARY KEY" you'll have a 
real performance gain by using this feature !!!

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

Re: [sqlite] any command to find last rowid in a table

[Ibrahim A]

Am 05.02.2010 22:33, schrieb Vasanta:
> Kittayya:
>
> My issue is, I already have imported table in the Database, there alreay
> around 1000 records in that table where ROWID is from 1 to 1000, now system
> generates new events, where ROWID again starts from beginning from 1, now
> these new events are overwriting the earlier imported events by "REPLACE
> INTO..", I made that change to instead REPLACE, I need INSERT, but now I
> need new ROWID (I need to update at the end of previous imported records. I
> don't want to overwrite original records.
>
>
>
First of all a few questions to make things clear :

1) you import your data with a script that inserts rows with existing 
rowid's ?

after that :

2) you try to insert new rows with INSERT ... but it doesn't work as you 
expect because you think you need to specify a new unused rowid ?

if so :

a) don't classify the field rowid in your insert command because sqlite 
then generates automatically new unused values.
b) if your script (from earlier posts) restores your database with sql 
commands (most likely) then try to avoid using rowid in that script at 
all. The default behaviour for rowid works fine especially for your 
problem with only a few thousand entries in the database
c) if rowid is your primary key to identify your data, then you depend 
on a internal feature of the engine and you should change this with a 
user defined field called id which could also be a autoincrement field 
similar to rowid for better performanc (integer primary key autoinc --- 
look at the documentation)

your problem is that you try to insert new rows with a rowid and you 
will only get performance problems when you have to search for the 
maximum value of a rowid each time you search for a new valid id. try to 
avoid this by defining your own primary key. At least don't try to 
specify a rowid value when you insert new rows.

Hope this could solve your problem ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Local data structures vs sqlite

[Mohd Radzi Ibrahim]

I think "worth" should be more accurate.


- Original Message - 
From: "Jean-Christophe Deschamps" 
To: "General Discussion of SQLite Database" 
Sent: Friday, November 06, 2009 9:47 AM
Subject: Re: [sqlite] Local data structures vs sqlite


> You read 'worse' instead of 'worst', of course!
> 
> 
> 
> ___
> 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] Question on index efficiency

[Ibrahim A]

Rael Bauer schrieb:
> 1) Is the efficiency (in terms of speed) of an index equal whether field is 
> INT/REAL/TEXT? (Where all data is of storage type INTEGER for INT field, REAL 
> for REAL, TEXT for TEXT)
>  
> I did some tests which showed that speed is equal. My real question is:
>  
> 2) If an indexed field contains records with a mix of storage classes (e.g. 
> some records are stored as REAL, some as TEXT), does this decrease speed 
> performance of the index in any way?
>  
> Thanks
> Rael
>   

If you have small tables the speed difference won't be large. But if 
your tables grow in size (number of rowsets) you'll measure a clear 
difference. The longer a index in bytes gets the less keys will fit on a 
single BTREE page so the depth of your BTREE where index relations are 
stored will get higher and you'll need more page accesses. As long as 
you don't get in the reagion of a depth higher than 2 levels (small size 
of rowsets) you won't measure a difference. But afterwards you'll see 
that Text > Reel > Int means also that the index access will reflect 
this relation. When you have indexes with more than one field the BTREE 
can store less keys on pages so the searchtime will increase.

For sqlite there is one more special thing to take into account. If you 
can use an int also as a primary key then do it because if not you'll 
always have a hidden column rowid (int) for each rowset while when you 
use your own unique integer as a primary key you will have one index 
table less and the B+Tree will use your integer. Less space, faster 
access without losing anything.

Hope this simple description will help.

Ibrahim

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

Re: [sqlite] trying to optimize left outer join for large data set (multiple indices needed?)

[Ibrahim A]

Hi, 

at the first sight i'd suggest that you reorder the rows of your index :

it is most likely that chr and strand will have many equal values in your 
example - especially chr. When chr is the first field of your Index than the 
path to find first differences in the btree to find the matching intervall will 
be longer. 

i'd try an index with reordered columns which wouldn't affect your application 
to much :

you can experiment for the right combination. 

i'd try this combinations for your index and measure the differences :

a) idx(start, stop, strand, chr)
b) idx(start, strand, chr, stop)
c) idx(stop, start, strand, chr)
d) idx(stop, strand, chr, start)
e) idx(strand, chr, start, stop)
f) idx(strand, chr, stop, start)

i think that all this indexes especially b and d will make your query faster.



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

Re: [sqlite] Db design question (so. like a tree)

[Ibrahim A]

Jan schrieb:
> thx Ibrahim. Give me some time to digest yours and other suggestions.
>
> But it seems I will end up with a adjunct list PLUS something. Of course 
> you are right: I need to store many other information for each animal. I 
> definitely need to use a database (sqlite of course). It will be used 
> for storing breeding informations for agriculture livestock (no lab mice 
> or so similar). So the generation cycle is not too short.
>
> Bye
> Jan
>
>   

A summarized solution for your problem would be :

A) Database entries :

CREATE TABLE tanimals (
animal_array_index INTEGER PRIMARY KEY,
animal_id INTEGER,
... further fields if needed ...
) ;

CREATE UNIQUE INDEX idx_tanimals_animal_id ON tanimals (animal_id) ;

B) Index File Structure :

struct TSANCESTORINFO {
int animal_id ;
int arridx_father ;
int arridx_mother ;
} ;

C) Comments :

This is the fastest way to implement your application using sqlite and a 
external index file. Even if you use other algorithms you'll only gain 
minimalistic speed while you resolve animal_id >> array_index. The main 
iteration is made with the fastest possible speed. Insteed of iterating 
through indexpages you directly access the parents of each animal in 
your array.

When you have resolved the animal_id >> array index association you find 
the real array indexes of the parents in the indexfile. After you have 
the needed animal_id's of the ancestors you can get all recordsets of 
them with a single select statement

SELECT * from tanimals where animal_id IN ( ... list of found 
animal_id's of the ancestors ...) ;


hope this was useful have fun ;)

Ibrahim

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

Re: [sqlite] Db design question (so. like a tree)

[Ibrahim A]

One further advice :

The fastest solution for your Problem would be to create a Array with 
fixed size Entries to describe the relationship between animals.

in C you would simply end up with a struct like :

struct ancestors {
integer id_father ;
integer id_mother ;
} ;

If you have a continuing range of animals this would create for 1 Mio 
animals you would end up with an array of 8 MB size. You can store this 
as a seperate file or a blob in a database and get the fastest possible 
accesstime for your problem.

The question is then if you have a continuing range of animal id's.

If you have no continuous range of animal_id's than i'd advice to create 
a indextable (symboltable) which translates animal_id's into array 
indexes. Sqlite could be used for this too where you declare animal_id 
as a numerical primary key instead of rowid. (UNIQUE PRIMARY KEY). No 
other fields should be included in this translation table so you'll have 
a maximum amount of key-index pairs on each page of your database and 
the translation so will require less page reads to get the correct array 
index. Well designed hash tables would be a bit faster for this goal but 
the difference would be minimal.

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

Re: [sqlite] Db design question (so. like a tree)

[Ibrahim A]

Jan schrieb:
> Hi,
>
> I am planning a database for animal breeding. I need to store the 
> relations between individuals and therefore I have to build something 
> like a tree structure. But of course with two parents (There wont be 
> cloned animals in the database .-) afaik)
>
> I read a little bit about
>
> - adjacency list (not very difficult to understand)
> - nested sets (hm, more difficult)
> - b tree (to difficult)
> - ? (something I missed?)
>
> Could anyone give me an advice what to use or what else to read? Maybe 
> someone has already done something similar e.g. genealogy.
>
> Bye
> Jan
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
The problem you're describing is one of the fundamental relational problems.

A)  I'd advice you to use a relational Table like you told before

A Table with at least the three fields animal_id_self, animal_id_father, 
animal_id_mother is necessary to note the different relations.


B) Is there something which would be more elegant ?

It depends on the amount of data you intend to store in the database. If 
you have a database for 1000 animals and about 7 generations of animals 
you could append a blob field to the definition of your table where you 
store the ancestors from the father and the mother in an array of 
integer values. This would avoid the interation over the tables.

But be warned. With each generation the size of this blob will get 
sizeof (ancestors_father+1) + sizeof (ancestors_mother+1). This means in 
the worst case you would get a factor of 2 per generation to avoid the 
iteration. I would say that 10 generations would be something were i'd 
take this risk if i have to avoid a iteration and break the rule of 
avoiding redundant data in tables. In the 10th generation you could have 
a size of this blob with 1 kByte per animal.

C) What is the reason for the lack of a proper algorithm ?

Change the point of view and look from the last children back than 
you'll see, that each animal is the root of an independent binary search 
tree. Because each animal has two parents, each parent two parents and 
so on. with each level you get two more entries per ancestor.

D) The relational model fits best.

This example is perfectly suited for the relational database modell. It 
asures that you can store the information with the least amount of space 
and without lose of data. But it also shows that in such situations you 
have to interate.

I would avoid a time saving field like the ancestors column in the 
database. Insteed it is the best to load this table into the memory and 
iterate through it.

E) A faster approach than with sqlite ?

Your animal_id's are integer values so the b-tree approach which is used 
in sqlite is fast enough to get good iteration results especially when 
you only store a few thousand values. You would get a bit faster results 
with hashing. But before you invent your proprietary engine i'd advice 
to use a relational database with sql. I think you'll store more than 
only parent information for the animals and then the true strength of 
the relational model appears.

Good luck,

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

Re: [sqlite] how to compose the sql sentence?

[Ibrahim A]

Assumptions : Your database scheme contains this declarations

CREATE TABLE data (
num INTEGER,
di CHAR(4),
data CHAR(12),
time1 INTEGER,
time2 INTEGER,
format CHAR(1)
);

CREATE UNIQUE INDEX i_data ON data (
num, di, time1
);


You want to do :


A) Insert or Replace without looking at the previous value if one existed :
***
INSERT OR REPLACE INTO data (num, di, data, time1, time2, format)
VALUES (12, '1290', '732e4a39', 8323000, 8323255, 22);



B) You want to see if an entry exists read that and edit it manually :
**
in this case it is best to search also for the hidden rowid which 
simplifies updates

SELECT rowid, num, di, data, time1, time2, format FROM data
WHERE num=12 AND di='1290' AND time1=8323000 ;

depending on the resultset :

B1) there was a result you'll get as the first column a rowid (lets say 
it was 894)then update

UPDATE data SET data='732e4a39' WHERE rowid=894 ;


B2) there was no result set and you want to insert a new one

INSERT INTO data (num, di, data, time1, time2, format)
VALUES (12, '1290', '732e4a39', 8323000, 8323255, 22);


Good luck ;)

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

Re: [sqlite] sqlite programmed in C++

[Ibrahim A]

Hi all,

I'd like to comment this topic as well.

I think C is a very good choice for projects like sqlite. The point is 
that C is in contrary to C++ very easily portable. If you have the bad 
luck that you have to port a programm on a new plattform for which no 
compiler suites exist than the choice of C for such a project pays of.

You'll find a few short understandable and editable C-Compiler 
implementations on the internet. As an example TCC, LCC or PCC. They are 
short enough to port to a new plattform. To port TCC to a new plattform 
as an example needs about one week when you have to start from zero 
because you only need to refine the code generation interface for the 
destined target system.

sqlite uses a few dozen standard function calls and the most of them are 
easily to implement in C itself. And don't forget that C is designed as 
a portable assembly language so it's really fast.

If you now look at C++ you'll find only two mentionable Compiler 
implementations which you can use for a porting task. And these 
toolchains require more than the knowledge of C with which you could 
port TCC or PCC.

When you look closer to the sqlite code you'll find, that the code uses 
objectoriented techniques on many places. Many of the interfaces to 
different functionality like vfs or fts are objects in a portable way. A 
C++ object is nothing different than a table of functions and data 
pointers and this is used in sqlite as well but the difference is, that 
this implementation is portable and interchangeable between compilers. 
If you would start to use C++ than you would end up compiling a 
interface with one compiler and being unable to use this interface from 
another compiler or even a compiler with a newer release date. C++ only 
supports compile time modularity and no runtime compatibility between 
libraries written in this language and compiled with different versions. 
The binary implementation of OOP abilities is always proprietary. The 
support of the code would be horrible because you'd have to deal with 
incompatibilities between different implementations.

To summarize my opinion :

C is the best choice for portable projects like sqlite. C is fast enough 
and if you don't have a compiler you can create one. If you don't have a 
standard library you can use free libraries.

I would wish that drh would go a step further and avoid using awk and 
tcl for the code creation process. This would solve the problem with 
Preprocessor definitions which can be set during compile time but where 
some have also to be set while you create lemon. It is possible to 
exchange each of the awk and tcl scripts which are used during code 
generation and the generation of the amalgamation with only C this would 
make the whole thing simpler to port and more independent from any 
external tools or plattforms and it would also make it possible to use 
all of the creation makros while you create the sourcecode.

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

Re: [sqlite] SQLite command line interpreter vs DLL

[Ibrahim A]

Fam. Sera schrieb:
> Is there a reason why the command line interpreter supports more functions 
> while the dll does not?
>
> I would like to create an application using SQLite with and in memory 
> database. I have some big text files (csv), which can be imported nicely by 
> the command line interpreter using the .import command. However when later 
> on, I'd like to manipulate data, using the DLL - or it's compiled version - 
> in my application I cannot access the in memory database created earlier 
> using the command line interpreter. Obviously the DLL open command creates a 
> new in-memory database instance. On the other hand, the DLL itself does not 
> offer the great .import or .output functionalities..
>
> I wonder why the DLL version does not support those dot commands supported by 
> the comand line version?
>
> Best regards,
>
> Zsolt Sera
> Vienna
>
>
>
>   
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
It's easy enoug to transform sqlite command line tool into a GUI-Window.

For short there are three different approaches :

A) You use Borland's C++ 5.0 which has a window control that emulates a 
command line window. I did this with other command line applications and 
it works.

B) You can use the code for sqlite and do the following :

Overwrite all standard functions for stdio with your own versions and 
translate them into calls for GUI routines. This sounds terrible but in 
reallity is is very easy. What you really need is an edit control and a 
minimal amount of own code for redirection.

When you use this method you'll end up with having a emulated command 
window in your application which allows always administration from a GUI 
application with the power of the sqlite command line and it's dot commands.

I think that many programmers would profit from a little changed sqlite 
command line tool in which each of the included standard function names 
would be Pointers to functions or to an "struct-object" with a function 
pointer table. That would make porting and such uses as a GUI command 
line much easier. I did it this way and have to search->rename each time 
a changed version appears.

C) The third way to keep the functionality of sqlite command line and 
integrate it in your application would be to seperate the integrated 
parser of this tiny useful tool and wrap all user interface routines 
with GUI or script-functions. This is also not very hard to achive 
because sqlite command line tool is prepared for this tasks and has a 
string parser so it doesn't really care from which source the string 
really comes.

If you really need such a administrative windows or a function interface 
in your gui application sqlite command line is very good and easily to 
transform into a gui application. Method B is the one i personaly 
prefered because when you once have a window which is able to simulate a 
console you can exchange your personal administrative dll with the 
regular code after search-rename. Some would advice to redefine standard 
function names with makros but that is dangerous. It's better to rename 
them like printf ==> myprintf and declare a function pointer for 
myprintf. With a Makro selection you can than declare this pointers as 
standard function pointers or substitute them with your own 
implementations.

Hope i could help you
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this legal SQL?

[Mohd Radzi Ibrahim]

You should use

sprintf(buf, "CREATE TABLE %s(x double, y double)", tableName);

then use prepare and execute using from that string.


- Original Message - 
From: "Mike McGonagle" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, January 13, 2009 3:43 AM
Subject: [sqlite] Is this legal SQL?


Hello all,

I am working on connecting SQLite up to another programming language,
and had a question about how SQLite (or SQL in general) would handle
this...

I want to be able to create some tables dynamically (same structure,
different name), and I thought this might work...

CREATE TABLE ?1 (x double, y double);

And then I would assign '?1' to a string...

BUT, it would appear that SQLite does not like this, because when I
try to 'prepare' the SQL, it complains about "error near '?1': syntax
error"...

Is there something that I can do, short of generating the SQL
dynamically (ie allow the use of placeholders for the table name)?

Thanks,

Mike


-- 
Peace may sound simple—one beautiful word— but it requires everything
we have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician
___
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] Adding data with periods

[Mohd Radzi Ibrahim]

It seems to works either way.

I'm just wondering is there any hidden reason that single quote is 
preferred? Portability?
Or is double-qoute has some kind of special meaning that we should use it 
for that special purpose?


-radzi-


- Original Message - 
From: "P Kishor" 
To: "General Discussion of SQLite Database" 
Sent: Monday, December 15, 2008 1:32 PM
Subject: Re: [sqlite] Adding data with periods


> On 12/14/08, aditya siram  wrote:
>> Thanks a lot. The issue has been fixed with:
>>  INSERT INTO TEST_TABLE(CONTENTS) VALUES ("Hello. World!");
>>
>
>
> Use single quotes to delimit text, not double quotes.
> ___
> 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] Adding data with periods

[Mohd Radzi Ibrahim]

The syntax is wrong.

INSERT INTO TEST_TABLE(CONTENTS) VALUES ("Hello. World!");

is the correct one.

-radzi-
- Original Message - 
From: "aditya siram" 
To: 
Sent: Monday, December 15, 2008 1:04 PM
Subject: [sqlite] Adding data with periods


> Hi all,
> I'm having trouble adding data with period characters in it. I tries to
> escape the period with a `'` but that didn' t seem to work. Here is an
> example interaction:
> 
> sqlite> create table test_table ("Contents" varchar);
> sqlite> insert into test_table "hello . world";
> SQL error: near ""hello . world"": syntax error
> sqlite> insert into test_table "hello '. world";
> SQL error: near ""hello '. world"": syntax error
> 
> Thanks ...
> deech
> ___
> 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] please help with NULL and NOTHING?

[Mohd Radzi Ibrahim]

You should handle this in your client program. Even in MS SQL or Oracle, it 
will not return any resultset.
If you were to use left join, you may get it as NULL for any missing links.

rgd,
Radzi.

- Original Message - 
From: "aivars" <[EMAIL PROTECTED]>
To: 
Sent: Monday, December 01, 2008 4:26 PM
Subject: [sqlite] please help with NULL and NOTHING?


> Hello,
>
> The simple query is like this:
> SELECT dbs as summadeb from bilance1 where account='13100' and pYear=?;
>
> Account number 13100 is not present in the table bilance1 when
> pYear=2005 and it should be like this and therefore dbs is also not
> present. Other years account number 13100 is present and query works
> OK.
>
> When the query is run with a parameter pYear='2005' it returns nothing
> - the resultset is empty or nothing? (I am doing it from python25)
>
> Even if I change the query to :
> SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
> and pYear='2005'; it still returns NOTHING, not 0.
>
> The same happens also on MS SQLServer 2005 so I think it should be
> like that according to sql standards. If there is no account number
> 13100 in the table then the result is NOTHING, not NULL or 0.
> Strictly speaking I think also the value of 0 is not correct in this
> case but I would like to have it.
> It seams that coalesce can handle NULL not NOTHING.
>
> My question is:
> Is there an SQL way to handle above query to return 0 or should I
> handle this in client program (python)? (presently I get TypeError:
> 'NoneType' object is unsubscriptable)
>
> Using sqlite 3.6.2, python2.5 and Windows XP
>
> Thanks in advance
>
> Aivars
> ___
> 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] Help for sqlite syntax

[Mohd Radzi Ibrahim]

Could you just use ORDER BY 1 ?

best regards
-- radzi --
- Original Message - 
From: "li yuqian" <[EMAIL PROTECTED]>
To: 
Cc: "Dimitar Penev" <[EMAIL PROTECTED]>; "Mark" <[EMAIL PROTECTED]>
Sent: Tuesday, February 12, 2008 1:56 PM
Subject: [sqlite] Help for sqlite syntax


> Hi guys,
>
> i am a newbie of sqlite3, we want porting the Freepbx(freepbx.org) to our
> project www.astfin.org, the freepbx can support sqlite3, but not very 
> well,
> now i got a problem about sqlite3
> -
> SELECT t.variable, t.value, d.value state FROM `globals` t JOIN (SELECT
> x.variable, x.value FROM globals x WHERE x.variable LIKE 'OUTDISABLE\_%') 
> d
> ON substring(t.variable,5) = substring(d.variable,12) WHERE t.variable 
> LIKE
> 'OUT\_%' UNION ALL SELECT v.variable, v.value,
> concat(substring(v.value,1,0),'off')
> state FROM `globals` v WHERE v.variable LIKE 'OUT\_%' AND
> concat('OUTDISABLE_',substring(v.variable,5)) NOT IN ( SELECT variable 
> from
> globals WHERE variable LIKE 'OUTDISABLE\_%' ) ORDER BY variable
> ---
>
> above sql working very well at mysql, but at sqlite3 will show:
> ---
> SQL error: ORDER BY term number 1 does not match any result column
> ---
>
> the globals table is
> --
> CREATE TABLE `globals` (
>  `variable` char(20) NOT NULL default '',
>  `value` char(50) NOT NULL default '',
>  PRIMARY KEY  (`variable`)
> ) ;
> -
> and insert some contents to this table
> --
> INSERT INTO `globals` VALUES ('CALLFILENAME','\"\"');
> INSERT INTO `globals` VALUES ('DIAL_OPTIONS','tr');
> INSERT INTO `globals` VALUES ('TRUNK_OPTIONS','');
> INSERT INTO `globals` VALUES ('DIAL_OUT','9');
> INSERT INTO `globals` VALUES ('FAX','');
> INSERT INTO `globals` VALUES ('FAX_RX','system');
> INSERT INTO `globals` VALUES ('FAX_RX_EMAIL','[EMAIL PROTECTED]');
> INSERT INTO `globals` VALUES ('FAX_RX_FROM','[EMAIL PROTECTED]');
> INSERT INTO `globals` VALUES ('INCOMING','group-all');
> INSERT INTO `globals` VALUES ('NULL','\"\"');
> INSERT INTO `globals` VALUES ('OPERATOR','');
> INSERT INTO `globals` VALUES ('OPERATOR_XTN','');
> INSERT INTO `globals` VALUES ('PARKNOTIFY','SIP/200');
> INSERT INTO `globals` VALUES ('RECORDEXTEN','\"\"');
> INSERT INTO `globals` VALUES ('RINGTIMER','15');
> INSERT INTO `globals` VALUES ('DIRECTORY','last');
> INSERT INTO `globals` VALUES ('AFTER_INCOMING','');
> INSERT INTO `globals` VALUES ('IN_OVERRIDE','forcereghours');
> INSERT INTO `globals` VALUES ('REGTIME','7:55-17:05');
> INSERT INTO `globals` VALUES ('REGDAYS','mon-fri');
> INSERT INTO `globals` VALUES ('DIRECTORY_OPTS','');
> INSERT INTO `globals` VALUES ('DIALOUTIDS','1');
> INSERT INTO `globals` VALUES ('OUT_1','ZAP/g0');
> INSERT INTO `globals` VALUES ('VM_PREFIX','*');
> INSERT INTO `globals` VALUES ('VM_OPTS','');
> INSERT INTO `globals` VALUES ('VM_GAIN','');
> INSERT INTO `globals` VALUES ('VM_DDTYPE','u');
> INSERT INTO `globals` VALUES ('TIMEFORMAT','kM');
> INSERT INTO `globals` VALUES ('TONEZONE','us');
> INSERT INTO `globals` VALUES ('ALLOW_SIP_ANON','no');
> INSERT INTO `globals` VALUES ('VMX_CONTEXT','from-internal');
> INSERT INTO `globals` VALUES ('VMX_PRI','1');
> INSERT INTO `globals` VALUES ('VMX_TIMEDEST_CONTEXT','');
> INSERT INTO `globals` VALUES ('VMX_TIMEDEST_EXT','dovm');
> INSERT INTO `globals` VALUES ('VMX_TIMEDEST_PRI','1');
> INSERT INTO `globals` VALUES ('VMX_LOOPDEST_CONTEXT','');
> INSERT INTO `globals` VALUES ('VMX_LOOPDEST_EXT','dovm');
> INSERT INTO `globals` VALUES ('VMX_LOOPDEST_PRI','1');
> INSERT INTO `globals` VALUES ('VMX_OPTS_TIMEOUT','');
> INSERT INTO `globals` VALUES ('VMX_OPTS_LOOP','');
> INSERT INTO `globals` VALUES ('VMX_OPTS_DOVM','');
> INSERT INTO `globals` VALUES ('VMX_TIMEOUT','2');
> INSERT INTO `globals` VALUES ('VMX_REPEAT','1');
> INSERT INTO `globals` VALUES ('VMX_LOOPS','1');
> INSERT INTO `globals` VALUES ('TRANSFER_CONTEXT','from-internal-xfer');
> -
>
> how i can change the sql for sqlite3, any idea, thanks
>
> -- 
> Li YuQian
> Your Astfin team
> ___
> uClinux/Asterisk distribution for Blackfin CPU
> http://www.ucpbx.com
> http://astfin.org
> http://sourceforge.net/projects/astfin/
> ___
> ___
> 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] Innovative examples / user stories

[Mohd Radzi Ibrahim]

Hi,

I used SQLite to move "data-cube" from server to client. The server hosts 
data in MS SQL databases. There is a server-app that run query based on some 
input send by client program. The server then run MSSQL query and generate a 
SQLite db consisting of some tables (fact, dimensions, etc), zip it and send 
back to the client. The client program then allows user to manipulate 
dimensions (to maybe slice/dice the result) without having to access the 
huge databases on the server anymore...


best regards,
radzi.


- Original Message - 
From: "Lars Aronsson" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, February 12, 2008 1:51 AM
Subject: [sqlite] Innovative examples / user stories


>
> Is there any documentation of how people use SQLite in odd ways in
> their everyday activities?  For example, do you e-mail SQLite DB
> files between you, as if they were Excel spreadsheets?  Or do you
> distribute SQLite database files via BitTorrent?  Even with multi
> table databases? That would be a kind of database use that was
> unheard of in the Oracle/DB2 era, but it certainly should be
> possible with SQLite.  SQLite databases files could be used in a
> "seti @ home" kind of application, where a screensaver downloads
> an existing DB file, processes it for some hours, and then uploads
> the resulting DB file again.  But are any such applications or
> user stories known? Is there a list already?
>
> I've read the Wikipedia article and its list of Google Gears,
> Android, Mac OS X Tiger, etc.  I've also seen the "well-known
> users of SQLite" page on sqlite.org/famous.html and the "When to
> use SQLite" page.
>
> I've used Oracle since version 7 and MySQL since 3.23 (which isn't
> very long at all, but anyway).  The switch from Oracle to MySQL
> was made possible for many people because their database was
> embedded behind a web application anyway (look, no DBA!), so full
> transaction handling wasn't really needed.  That shift in usage
> pattern opened up for a simpler and more affordable solution.
> Many other such technology shifts are described in an old book
> titled "The Innovator's Dilemma".
>
>
> -- 
>  Lars Aronsson ([EMAIL PROTECTED])
>  Aronsson Datateknik - http://aronsson.se
> ___
> 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] Version 3.5.5 Released

[Mohd Radzi Ibrahim]

Hi,

The Precompiled Binary for Windows : the command-line sqlite-3_5_5.zip still 
contain the 3.5.4 version


regards,
Radzi.
- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Friday, February 01, 2008 1:33 AM
Subject: [sqlite] Version 3.5.5 Released



SQLite version 3.5.5 is now available for download from the
SQLite website:

  http://www.sqlite.org/

The big change from version 3.5.4 is that the internal virtual
machine was reworked to use operands in registers rather than
pulling operands from a stack.  The virtual machine stack has
now been removed.  The removal of the VM stack will help prevent
future stack overflow bugs and will also facilitate new optimizations
in future releases.

There should be no user-visible changes to the operation of SQLite
in this release, except that the output of EXPLAIN looks different.

In order to make this change, about 8.5% of the core SQLite code
had to be reworked.  We thought this might introduce instability.
But we have done two weeks of intensive testing, during which time
we have increased the statement test coverage to 99% and during
which we have found and fixed lots of minor bugs (mostly things
like leaking memory following a malloc failure).  But for all of
that testing, we have not detected a single bug in the new
register-based VM.  And for that reason, we believe the new
VM, and hence version 3.5.5, is stable and ready for production
use.

As usual, please report any problems to this mailing list, or
directly to me.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I add primary key on existing table?

[Mohd Radzi Ibrahim]

On 21-Jan-2008, at 3:12 PM, Dan wrote:



On Jan 21, 2008, at 1:24 PM, Mohd Radzi Ibrahim wrote:


Hi,

I was trying to add a primary key to existing table but could not.
"alter table custsales add constraint pk_custsales primary key (id,  
type)"


Is this supported? Is there a different syntax to do this?


Using ALTER TABLE to add a constraint or a new column marked as
PRIMARY KEY is not supported. You can get pretty much the same
effect by doing:

 CREATE UNIQUE INDEX custsales_i ON custsales(id, type);

Dan.


Thanks for the suggestion. Will it still work for the REPLACE command  
to identify indentical record?


--radzi--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How do I add primary key on existing table?

[Mohd Radzi Ibrahim]

Hi,

I was trying to add a primary key to existing table but could not. 


"alter table custsales add constraint pk_custsales primary key (id, type)"

Is this supported? Is there a different syntax to do this?


best regards,
Radzi.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Re: How do I do this

[Mohd Radzi Ibrahim]

This is my 2 cents. Maybe  there's better way to do this:

select
id,
amount,
	(select amount from tablename where id=t.id and sno < t.sno limit 1)  
as oldAmount

from tablename t

br

Radzi.

On 14-Jan-2008, at 8:14 PM, Vishal Mailinglist wrote:


Hi




sno | id | amount

1| 1 |  200
2| 1 | 300
3   |  2 | 100
4  | 2 | 100
5 | 1 | 500

how could I subtract the sno 1 id 1 and sno 2 id 1 amount .


select
   (select amount from tableName where sno=1 and id=1) -
   (select amount from tableName where sno=2 and id=1);



What if I do not have control over sno i.e it is  random or
unpredictable , I want to subtract it in order of occurrence. Like
doing subtracion of sno 2 and 5 and so on may be next occurance for
id 1 is at sno 20 , then what.


I don't understand what you are trying to achieve. What would be the
correct output on your example?



The table is tracking price change of a commodity that is in id  
column . We
need to calculate rise or fall of that commodity as per the last  
price . and

the second last price and latest price keep changing like



sno | id | amount
1| 1 |  200 <<<  when I run query this is the first instance  
of the

commodity for id 1
2| 1 | 300 <<<  the first instance would be subtracted with  
the

second instance here
3   |  2 | 100
4  | 2 | 100
5 | 1 | 500   <<<  finally the fourth instance would be calculated
w.r.tthird for id 1



and So on , now since sno is autoincrement I do not have control  
over sno

and cannot run query w.r.t sno i.e I cannot predic in the table when
comodity price would change and at what serial number it would be.

Hoping I was success full this time , thanks for your time and  
patience .





--
Regards,
Vishal Kashyap.
Need help visit
http://help.vishal.net.in



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] disable transaction support

[Mohd Radzi Ibrahim]

Hi Rasanth,

I'm not an expert in SQLite. Not sure what kind of performance gain  
you want to achieve. Perhaps you could try "pragma synchronous=off".  
Or in-memory database...


I guess the journal is there to provide ACID db characteristic.

For me even with that out-of-the-box, the insert performance is  
already faster than MS SQL Server 2005; (I've been working with more  
than 1 million rows tables).


best regards,
Radzi.

On 26-Dec-2007, at 2:38 PM, Rasanth Akali Kandoth wrote:


Hi Radzi,
i do it with BEGIN and COMMIT. it is that, even in this case for  
transaction
support sqlite has to write into the journal files as well. i want  
to avoid

this too.

Thanks,
Rasanth

On Dec 26, 2007 11:34 AM, Mohd Radzi Ibrahim <[EMAIL PROTECTED]>  
wrote:



On the contrary, sqlite work much-much faster when insert/update is
done within BEGIN and COMMIT;

regards,
Radzi.
On 26-Dec-2007, at 12:14 PM, Rasanth Akali Kandoth wrote:


Hi All,
I have an application which inserts large number of rows into a
table, where
transaction support is not necessary. For performance reason, i need
to
disable the transaction support in sqlite version 3.3.17 .   How can
i do
it?  any help is highly appreciated.

--
Thanks,
Rasanth




-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] disable transaction support

[Mohd Radzi Ibrahim]

On the contrary, sqlite work much-much faster when insert/update is  
done within BEGIN and COMMIT;


regards,
Radzi.
On 26-Dec-2007, at 12:14 PM, Rasanth Akali Kandoth wrote:


Hi All,
I have an application which inserts large number of rows into a  
table, where
transaction support is not necessary. For performance reason, i need  
to
disable the transaction support in sqlite version 3.3.17 .   How can  
i do

it?  any help is highly appreciated.

--
Thanks,
Rasanth



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Compile error sqlite 3.4.2

[Mohd Radzi Ibrahim]

I'm using Visual Studio 2005 with wxSQLite3 wrapper. I have this  
error at this line :


SQLITE_PRIVATE const unsigned char sqlite3UpperToLower[];

the error says:

.\..\wxsqlite3\sqlite3\src\sqlite3.c(6187) : error C2133:  
'sqlite3UpperToLower' : unknown size



any suggestion on how to bypass this error?

thanks.

Radzi.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Saving an in-memory database to file

[Mohd Radzi Ibrahim]

How about dumping and import into new db?


- Original Message - 
From: "Colin Manning" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, July 25, 2007 7:05 AM
Subject: [sqlite] Saving an in-memory database to file



Hi

If I create an in-memory database (by opening with ":memory:"), then add 
tables etc to it, is it possible to then write it to a disk file so it can 
subsequently be used as a file-based db?


Thanks



--
No virus found in this outgoing message.
Checked by AVG. Version: 7.5.476 / Virus Database: 269.10.14/912 - Release 
Date: 22/07/2007 19:02




-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] storing floating point values in field

[Mohd Radzi Ibrahim]

I've run SQLite3 inWindows, it's store up it surely did not truncate the 
decimal point - up to 15 significant digits...


regards,
Radzi.

- Original Message - 
From: "folabi" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, July 10, 2007 3:20 AM
Subject: [sqlite] storing floating point values in field




Hi,

I stored a floating point number in a sqlite field with datatype REAL.

However, when i wanted to retrieve the data, sqlite returned a rounded 
value

back.
ie.
double val = 23.123567 stored as REAL
sqlite returns 23.1236

Could someone pls confirm if this is consistent behaviour in sqlite? If 
yes,

is there a way to retrieve the same floating point stored (23.123567)
without having to save the data as TEXT.

Cheers

Folabi

--
View this message in context: 
http://www.nabble.com/storing-floating-point-values-in-field-tf4051483.html#a11508159

Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Multiple connections to the same database and CREATE TABLE command

[Mohd Radzi Ibrahim]

Is C1 in transaction? If it is, commit will enable C2 'see' the new table.

regards,
Radzi.
- Original Message - 
From: "Vivien Malerba" <[EMAIL PROTECTED]>

To: 
Sent: Friday, May 11, 2007 4:19 PM
Subject: [sqlite] Multiple connections to the same database and CREATE TABLE 
command




Hi!

In a single process, I open two connections (C1 and C2) to the same
database (this is actually a corner case which could happen) and the
following sequence of operations fail:
1- on C1 execute "CREATE table actor (...)" => Ok
2- on C1 execute "SELECT * FROM actor" => Ok
3- on C2 execute "SELECT * FROM actor" => error because table "actor"
does not exist.

Is there a way to make this sequence of operations work, or should I
make sure one can never open two connections on the same DB file in
the same process?

Thanks,

Vivien

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] create table error

[Mohd Radzi Ibrahim]

Could it be that the data where *sql is pointing to is being re-used 
somewhere?



--radzi.

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Thursday, May 10, 2007 3:54 PM
Subject: [sqlite] create table error



the following is my test code. just create table.


rc = sqlite3_open("zieckey.db", );
char *sql = " CREATE TABLE SensorData(ID INTEGER PRIMARY KEY,SensorID
INTEGER,SiteNum INTEGER,Time VARCHAR(12),SensorParameter REAL);" ;
sqlite3_exec( db , sql , 0 , 0 ,  );
sqlite3_close(db);



when execute the sqlite3_exec function,

it corrupt at line 189, sqlite3StrICmp(pTab->aCol[j].zName,
pChanges->a[i].zName)==0 )

pChanges->a[i].zName is NULL;

anybody know the reason ?


how to trace the error? there is no clue for me to analyze the error.

thanks a lot

allen.zhang





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to add a new column quickly

[Mohd Radzi Ibrahim]

How about this?


update tablename set newcolname=(case rowid when 1 then 1 else 2 end);


best regards,
Radzi


- Original Message - 
From: "Tomash Brechko" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, May 05, 2007 8:09 PM
Subject: Re: [sqlite] how to add a new column quickly



On Sat, May 05, 2007 at 19:30:59 +0800, ronggui wong wrote:

Thanks. But there is no typo, what I want is a general solution.

2007/5/5, Tomash Brechko <[EMAIL PROTECTED]>:
>On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote:
>> . update tablename set newcolname=1 where ROWID=1
>> . update tablename set newcolname=2 where ROWID=2
>> . update tablename set newcolname=2 where ROWID=3


If there is no correlation between newcolname and other columns that
can be expressed as a formula, but rather you want to set newcolname
to some known Func(ROWID), you may register this function with
sqlite3_create_function() (or its equivalent for your language
bindings), and then do a single statement

 UPDATE tablename SET newcolname = Func(ROWID);

This will be faster then repeatedly searching for a row with a given
ROWID.


--
  Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] DB design questions

[Mohd Radzi Ibrahim]

My 2 cents:

create table Objects(id integer primary key, name text);
create table Attributes(id integer primary key, name text);
create table ObjectAttributes(objectID integer, attributeID integer, order 
integer, type integer, value text, version text);


- Original Message - 
From: "Michael Ruck" <[EMAIL PROTECTED]>

To: 
Sent: Friday, April 20, 2007 10:10 PM
Subject: [sqlite] DB design questions



Hello,

I'm currently modelling and designing a new database according the 
following
specifications. The general idea is the ability to store arbitrary 
objects,

which have attributes of various kinds. The attributes themselves may be
multivalued. The objects being stored need to be versioned, so that 
there's

a way to go back to previous versions of an object. The objects represent
metadata of media files and the data itself comes from various automated
sources and manual editing by a user.

My current idea was the following:

- CREATE TABLE objects (id TEXT, version TEXT)
- CREATE TABLE attributes (object_id TEXT, version TEXT, name TEXT, order
INT, type INT, value TEXT)

Is there anyone who has experience with this kind of design, do you have
better ideas on modelling this kind of data?

Thanks,
Mike


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] trim available ??

[Mohd Radzi Ibrahim]

Hi,
I'm running 3.3.15. No problem at all. Maybe your version does not have this 
implemented yet...


best regards,
Radzi.

- Original Message - 
From: "Stef Mientki" <[EMAIL PROTECTED]>

To: 
Sent: Monday, April 16, 2007 10:14 PM
Subject: [sqlite] trim available ??



hello,

As I read here
 http://www.sqlite.org/lang_expr.html
there are trim functions availabel as part of an expression.


But when I try to execute the next statement:
 SELECT App FROM PO WHERE LOWER(App_Type) = TRIM(LOWER('{ Afzuigapparaat } 
'))


I get the following error
/ ERROR **
Error executing SQL
"SELECT App FROM PO WHERE LOWER(App_Type) = TRIM(LOWER('{ Afzuigapparaat } 
'))

"
:: no such function: TRIM
**/

What am I doing wrong ?

thanks,
Stef Mientki

Kamer van Koophandel - handelsregister 41055629  / Netherlands Chamber of 
Commerce - trade register 41055629




-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Data structure

[Mohd Radzi Ibrahim]

Hi,

If you are using C++, then try hash_map. I've used this on strings with more 
that 50,000 records - in memory. Very fast. Much easier to program than 
BerkeleyDB.



- Original Message - 
From: "Lloyd" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, April 11, 2007 11:20 PM
Subject: Re: [sqlite] Data structure



On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote:

I think, looking from Lloyd's email address, (s)he might be limited to
what CDAC, Trivandrum might be providing its users.

Lloyd, you already know what size your data sets are. Esp. if it
doesn't change, putting the entire dataset in RAM is the best option.
If you don't need SQL capabilities, you probably can just use
something like BerkeleyDB or DBD::Deep (if using Perl), and that will
be plenty fast. Of course, if it can't be done then it can't be done,
and you will have to recommend more RAM for the machines (the CPU
seems fast enough, just the memory may be a bottleneck).


Sorry, I am not talking about the limitations of the system in our side,
but end user who uses our software. I want the tool to be run at its
best on a low end machine also.

I don't want the capabilities of a data base here. Just want to store
data, search for presence, remove it when there is no more use of it.

Surely I will check out BerkeleyDB. The data set must be in ram, because
the total size of it is very small. (Few maga bytes) I just want to
spped up the search, which is done millions of times.

Thanks,

LLoyd


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] RE: Maintaining Master-Child relationships

[Mohd Radzi Ibrahim]

Use sqlite3_last_insert_rowid

- Original Message - 
From: "Arora, Ajay" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 29, 2007 12:19 AM
Subject: [sqlite] RE: Maintaining Master-Child relationships



Can anyone please look into my query,

I've tables

Master ( id integer primary key,
field1 text)

And

Child (id integer, name text)

My application receive values for field1 and name.  I need to populate
master and child with incoming values using the same id.

A quick reply will be highly appreciated.

Regards
Ajay

-Original Message-
From: Arora, Ajay
Sent: 28 March 2007 15:04
To: 'sqlite-users@sqlite.org'
Subject: Maintaining Master-Child relationships


Hi,

I've two tables in my database, a master and a child with ID  as a
common key.

I've created a table with one column to generate the sequence number.How
can I insert related records into both the tables using same ID?

Thanks
Ajay


THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE 
PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM 
DISCLOSURE. If the reader of this message is not the intended recipient, 
you are hereby notified that any dissemination, distribution, copying or 
use of this message and any attachment is strictly prohibited. If you have 
received this message in error, please notify us immediately by replying 
to the message and permanently delete it from your computer and destroy 
any printout thereof.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] CREATE INDEX performance

[Mohd Radzi Ibrahim]

- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, March 28, 2007 7:42 AM
Subject: Re: [sqlite] CREATE INDEX performance


I retract the overflow page theory on your compelling evidence and now 
understand better what it is doing after looking at the VDBE.  By building 
an index by successive insertions the tree is splitting and balancing as 
it grows, and that is expensive.  Double the size of the key and you get 
twice as many leaf nodes and quite a few more interior nodes.


If the keys order is very random the keys are being inserted all over the 
tree which is slow.  Presenting the keys in sorted sequence should cut 
back on the fragmentation and will very likely build a more compact tree 
by ensuring that each leaf node is filled.


An optimization for building such a tree would be to extract the keys, 
sort them and build the tree bottom up.  By avoiding all splitting and 
jumping around the tree it should be an order of magnitude faster or 
better.  I took a quick look at the code and got the impression that a 
fast index option could be built by a motivated user as a seperate program 
and might be a handy tool for people managing very large Sqlite databases. 
Cutting back a 20 hour run to 1-2 hours can be a big win.


Sqlite is something of a victim of its success.  The embedded lite 
database is being asked to perform enterprise level tasks which stretch 
its envelope.




How do we do sorting prior to indexing? If it is the initial table it's OK, 
we can sort it before insert. But for existing table, how do we do that?


regards,
Radzi. 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Linking error with sqlite3SrcListAddAlias...

[Mohd Radzi Ibrahim]

Hi,
I'm using MS VS 2005 (SQLite 3.3.9) and having problem re-building the 
component. I've just included all the source code inside my project and 
compile. With version 3.3.8 it works fine, but with current (3.3.9) it's 
giving me missing sqlite3SrcListAddAlias.


Any files should be included which is not in SRC folder?

best regards,


Radzi

Re: [sqlite] How do I speed up CREATE INDEX ?

[Mohd Radzi Ibrahim]

Thanks for the suggestion. I'm a bit lost now. I've tried to load 80million 
rows now. It took 40 minutes to load into non-index tables; but creating 
index now take almost forever. It's already 12 hrs, not yet complete.


regards,
Radzi.

- Original Message - 
From: <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Sunday, December 03, 2006 8:21 PM
Subject: Re: [sqlite] How do I speed up CREATE INDEX ?



"Mohd Radzi Ibrahim" <[EMAIL PROTECTED]> wrote:

Hi,
I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6 
million rows (with no index). But then when I run CREATE INDEX it took me 
40 mins to do that. What could I do to speed up the indexing process ?




The reason index creation slows down when creating large
indices is a problem with locality of reference in your disk
cache.  I've learned a lot about dealing with locality
while working on full-text search, and I think I can
probably implement a CREATE INDEX that runs much faster
for a large table.  There are some plans in the works
that might permit me the time to do this in the spring.
But in the meantime, the only thing I can suggest is to
add more RAM to your machine so that you disk cache is
larger.  Or get a faster disk drive.
--
D. Richard Hipp  <[EMAIL PROTECTED]>





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I speed up CREATE INDEX ?

[Mohd Radzi Ibrahim]

- Original Message - 
From: "P Kishor" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Sunday, December 03, 2006 10:42 AM
Subject: Re: [sqlite] How do I speed up CREATE INDEX ?



On 12/2/06, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:


- Original Message -



Because I'm going to do it 24 times. I have 24 monthly files each of 
which

about the same size. A faster computer is not an answer at this time.


unless you are doing something really wrong (creating the wrong
indexes, for example), there is nothing wrong about CREATE INDEX
taking a relatively long time. That is the price you pay... you pay it
once, when creating the index. After that, your queries are quick
because they use the index. There really is no way around that.



I have 5 simple tables:
1. Transaction (id, NoOfPack, NoOfDX, NoOfOps, NoOfCure)
2. Cure(transID, cureID)
3. Diag(transID, diagID, Sequence)
4. Ops(transID, opsID)
5. Pack(transID, packID, qty, amt)

Tansaction has primary key on ID, and I've created index on each detail 
tables (Cure, Diag, Ops and Pack - one-to-many relationship with Trans) 
after loading the data. The indexes are Cure(CureID, TransID), Diag(diagID, 
transID), Ops(opsID, transID), Pack(packID, transID). Same goes with the 
other tables.


There are 6million rows in Pack, 2 million in Trans.

I was quite impressed with the loading part. Even faster than MSSQL Bulk 
Insert. But the indexing part is much slower than MSSQL. I got a better 
result (12 min) when using PRAGMA CACHE_SIZE=2. That probably match the 
time for MSSQL.  Increasing to 4 does not show any improvement.



regards,
Radzi. 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I speed up CREATE INDEX ?

[Mohd Radzi Ibrahim]

- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Sunday, December 03, 2006 9:12 AM
Subject: Re: [sqlite] How do I speed up CREATE INDEX ?



On 12/2/06, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:

Hi,
I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6 
million rows (with no index). But then when I run CREATE INDEX it took me 
40 mins to do that. What could I do to speed up the indexing process ?


Details of the schema would help.
Assuming you didn't create more index than you need, a faster computer
is the answer.
It only has to be done once so why is that a problem?



Because I'm going to do it 24 times. I have 24 monthly files each of which 
about the same size. A faster computer is not an answer at this time.





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How do I speed up CREATE INDEX ?

[Mohd Radzi Ibrahim]

Hi,
I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6 million 
rows (with no index). But then when I run CREATE INDEX it took me 40 mins to do 
that. What could I do to speed up the indexing process ?

Thanks.


Radzi.

Re: [sqlite] How do i do this UPDATE in SQLite?

[Mohd Radzi Ibrahim]

Thanks, that works.

The syntax i used is allowed in MS SQL.

best regards,
Radzi.

- Original Message - 
From: "Kees Nuyt" <[EMAIL PROTECTED]>

To: 
Sent: Monday, October 16, 2006 1:54 PM
Subject: Re: [sqlite] How do i do this UPDATE in SQLite?



On Mon, 16 Oct 2006 09:53:19 +0800, you wrote:


update i set val=qty*q.price
from salesrecord i
inner join historicalprice q on i.itemid=q.itemid


I would use a correlated subquery which returns one column,
instead of the join. I don't think you can use aliasses there.

update salesrecord set val = qty * (select price from
historicalprice where historicalprice.itemid ==
salesrecord.itemid);  {untested}

It depends on the version if you can use this.


thanks.

Radzi.

--
 (  Kees Nuyt
 )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How do i do this UPDATE in SQLite?

[Mohd Radzi Ibrahim]

update i set val=qty*q.price
from salesrecord i
inner join historicalprice q on i.itemid=q.itemid


thanks.

Radzi.

Re: [sqlite] Re: Terminating long query in C ?

[Mohd Radzi Ibrahim]

Thank you guys for the suggestion. That's what I really need. Which one is 
better, the sqlite3_interrupt() or sqlite3_progress_handler()? My gut 
feeling is that sqlite3_interrupt() should be better since it does not 
interfere with the running of the query until it is signaled.  Besides, the 
progress_handler does not actually give me the right proportion of 
completion... Or is there  a way I can know proportion of completion from 
it?


thanks again,

Radzi.

- Original Message - 
From: "Igor Tandetnik" <[EMAIL PROTECTED]>

To: "SQLite" 
Sent: Wednesday, October 11, 2006 8:43 AM
Subject: [sqlite] Re: Terminating long query in C ?



[EMAIL PROTECTED] wrote:

sqlite3_interrupt() has long been used by the sqlite3 shell program.
When you press control-C a posix signal handler invokes
sqlite3_interrupt() to stop the current query.  (This only works on
posix, of course.)  The original purpose of sqlite3_interrupt() was
to support ^C in the shell, and for that purpose the thread
restriction
was entirely reasonable.

It is easy to understand how this use of sqlite3_interrupt() might
have escaped the notice of users on non-posix systems.


One can register a Ctrl-C handler on Windows with SetConsoleCtrlHandler. 
But the handler is invoked on a background thread created by the system 
specifically for this purpose, so sqlite3_interrupt couldn't be used 
there, either.


Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Terminating long query in C ?

[Mohd Radzi Ibrahim]

Hi,

How do  I cancel a long running query? In OLEDB I can call pICommand->Cancel() 
from a different thread to kill the running query. How can I do the same in 
SQLite?

thanks.

Radzi.

Re: [sqlite] Sql lite new user

[ibrahim]

Thanks you It works fine now

[sqlite] Sql lite new user

[ibrahim]

Hello,


I m new to this mailing list , 


I currently try to add sql lite to a program I m working on

It is a quizz program , 

And I want the question to be incorporated into a sql lite database to enable 
search ...etc..

I m download the source code of version 2_8_15

And compiled it under Visual Studio .NET 2003 with problems (only warnings )

As a wrapper , I choose this one : http://dev.int64.org/sqlite.html

I compiled the sqllite-plus.lib with no problems 

Run the code samples test-select and test-insert with no problem

So I tried to add the both lib sqlite.lib and  sqllite-plus.lib   in my real 
project ( windows program )

I get a lot of errors , I found that the error was due to runtime library 
conflit (Multithread DLL / single threaded)

So I recompiled the both lib with  Multithread DLL  runtime ... no 
problem


run the samples test-select and test-insert ... no problem


So I tried to recompile my real project with the both lib ... no problem

but when i run the code , I get error at runtime : 

library routine called out of sequence   ( from ex.what() )

Exception de première chance à 0x77e4d756 dans Quizz.exe:Microsoft C++ 
exception: std::runtime_error @ 0x0012fe5c.
HEAP[Quizz.exe]: HEAP: Free Heap block 49d4758 modified at 49d487c after it was 
freed
Exception non gérée à 0x77f6f570 dans Quizz.exe:Point d'arrêt utilisateur.

Here is the code that causes the problem :

sql = sqlite::connection("mk.db");

try {

sqlite::reader reader=sql.executereader("select * from t_test;");

reader.close();
sql.close();
}

catch(exception ) {
fprintf(i,"%s\n",ex.what() );
fflush(i);
}



When I run it in console program no problem

But when I run it with my win32 project -> problem


Any help appreciated

Thanks in advance


Nicolas Raby
vmgames
France