[sqlite] sqlite3_value LOST

[Mina R Waheeb]

Hi,
  I'm trying to pass object pointer from aggregate function to the result set.

The function set the result in this way:
Mem *pBest;
pBest = sqliteMalloc(sizeof(*pBest));
if( !pBest ) return;
pBest->i = toref(value); // i set
//pBest->r = 0;
//pBest->z = 0;
pBest->flags = MEM_Static;
pBest->type = MEM_Int;
pBest->xDel = 
sqlite3_result_value(context, pBest);

But when i try to fetch the value always get SQLITE_NULL with Mem->i = 1
After playing with the code for a while i didn't understand what is
the function of sqlite3VdbeMemShallowCopy(Mem *, Mem *, int) in
vdbemem.c the value i pass copied or duplicated by it or what happen
to the value i supplied.

My question is:  What happen to my sqlite_value?

Also i have note If Mem->xDel is not NULL SQLite pass only Mem->z why
not pass the whole Mem* .

I'm not expert in C but please advise me if I miss anything. I know
SQLite don't support custom "storage" datatype. but why not support
custom data fetching.

Thanks,
Mina.

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

Re: [sqlite] PRAGMA writable_schema=ON;

[Joe Wilson]

--- T <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> 
> > It appears that you can set
> >
> >   PRAGMA writable_schema=ON;
> >
> > Then do a manual UPDATE of the sqlite_master table to insert
> 
> > I tried it and it seems to work.  But it is dangerous.  If you mess  
> > up, you corrupt the database file.
> 
> Now that is interesting. I didn't realize we could change  
> sqlite_master directly, but have often thought it could be very handy.  
> I've often read from it (ie select from sqlite_master), but not  
> written (ie update or insert).

Even if you got the sqlite_master table entries right, the in-memory
sqlite schema data structures would not be in sync.

You'd be better off using the normal DROP/CREATE SQL statements and 
published sqlite APIs to do this sort of thing.



   

Be a better Globetrotter. Get better travel answers from someone who knows. 
Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list=396545469

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

[sqlite] PRAGMA writable_schema=ON;

[T]

[EMAIL PROTECTED] wrote:


It appears that you can set

  PRAGMA writable_schema=ON;

Then do a manual UPDATE of the sqlite_master table to insert


I tried it and it seems to work.  But it is dangerous.  If you mess  
up, you corrupt the database file.


Now that is interesting. I didn't realize we could change  
sqlite_master directly, but have often thought it could be very handy.  
I've often read from it (ie select from sqlite_master), but not  
written (ie update or insert).


I imagine that writing to a table or index entry would be disastrous,  
eg:


update sqlite_master
set sql = 'create table MyTable( Col1, Col2 )
where name = 'MyTable' and type = 'table'
;

since I think SQLite wouldn't set up the required table data. Correct?

But what about triggers and views? Since (AFAIK, since rootpage = 0)  
there's no data structure created by them in the file, can we safely  
manipulate them directly in sqlite_master?


For an example, I'll use the predicament from my earlier message  
"Altering views, keeping triggers". As a possible solution to keeping  
triggers when a view is changed, would it be safe to either:


1. Update the view in sqlite_master directly, thereby avoiding  
SQLite's deletion of associated triggers:


update sqlite_master
set sql = 'create view MyView as select Col1, Col2 from MyTable'
where name = 'MyView' and type = 'view'
;

or:

2. Cache the triggers before changing the view, then insert them  
directly into sqlite_master:


begin immediate
;
create temporary table Cache as
select * from sqlite_master where type = 'trigger'
;
drop view 'MyView'
;
insert into sqlite_master
select * from Cache
where name not in ( select name from sqlite_master where type =  
'trigger' )

;
commit;

I guess "insert or ignore" could be used instead of testing for  
existence, if sqlite_master enforces a unique( type, name), but I  
don't know if this is safe to assume.


Thanks,
Tom


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

RE: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Samuel R. Neff]

+1 for fts3 or fts2_1 :-) 

---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 14, 2007 8:22 PM
To: [EMAIL PROTECTED]
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

On 8/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> > to add one last bit, to upgrade older tables.
> >
> > Unfortunately, code of the form:
> >
> >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> >
> > is documented as not supported.
> > http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> > this means that there is no option to do a cheap schema upgrade to get
> > the correct semantics.  Am I missing a trick?
>
> It appears that you can set
>
>PRAGMA writable_schema=ON;
>
> Then do a manual UPDATE of the sqlite_master table to insert
> an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> I tried it and it seems to work.  But it is dangerous.  If you
> mess up, you corrupt the database file.

Ooh, I think that tips me away from fixing fts2, because it's scary
and Google Gears disables PRAGMA.

At least Joe threw in a vote for just versioning things to fts3 -
anyone want to vote against?

-scott


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[drh]

"Scott Hess" <[EMAIL PROTECTED]> wrote:
> >
> > It appears that you can set
> >
> >PRAGMA writable_schema=ON;
> >
> > Then do a manual UPDATE of the sqlite_master table to insert
> > an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> > I tried it and it seems to work.  But it is dangerous.  If you
> > mess up, you corrupt the database file.
> 
> Ooh, I think that tips me away from fixing fts2, because it's scary
> and Google Gears disables PRAGMA.
> 
> At least Joe threw in a vote for just versioning things to fts3 -
> anyone want to vote against?
> 

+1 in favor of fts3.

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


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

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Scott Hess]

On 8/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> > to add one last bit, to upgrade older tables.
> >
> > Unfortunately, code of the form:
> >
> >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> >
> > is documented as not supported.
> > http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> > this means that there is no option to do a cheap schema upgrade to get
> > the correct semantics.  Am I missing a trick?
>
> It appears that you can set
>
>PRAGMA writable_schema=ON;
>
> Then do a manual UPDATE of the sqlite_master table to insert
> an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> I tried it and it seems to work.  But it is dangerous.  If you
> mess up, you corrupt the database file.

Ooh, I think that tips me away from fixing fts2, because it's scary
and Google Gears disables PRAGMA.

At least Joe threw in a vote for just versioning things to fts3 -
anyone want to vote against?

-scott

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

Re: [sqlite] Re: very slow inserts

[Dennis Cote]

Günter Greschenz wrote:

hi,

thanks for the quick answer :-)
i always thought there is always an index for "rowid" ? thats the 
reason why i implemented it like this...
i need to store a maximum amount of data in the database (e.b. max 
10 entries or max 10 days)

any idea how i could implement it in another way ?

you're right, the pc has a lot of other tasks running !
e.g. there is a ms sql server installed and i dont know exactly how 
much disk-io this server is doing !

thanks a lot !





Gunter,

The following posts to this list show how to create an efficient FIFO 
table in sqlite 
http://article.gmane.org/gmane.comp.db.sqlite.general/16230/match=fifo 
and this one gives some more info on the fifo id reset problem 
http://article.gmane.org/gmane.comp.db.sqlite.general/16248/match=fifo


This should help you build a table with a maximum number of entries.

HTH
Dennis Cote

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

[sqlite] SQLITE_BUSY error in multi-threaded environment

[Mark Brown]

Hi-

I've got an application that has many different SQLite databases.  Each
database connection is opened in its own thread.  Each database has only one
connection.

I created some test cases that create a database and schema on the fly and
perform various SELECT, INSERTS, UPDATES on it.  The tests execute while the
rest of the system is running normally.

What I am seeing is that while I only have one database connection to my
test case database, and my operations on this database are done
sequentially, I have seen at random times a return of SQLITE_BUSY on either
a prepare or execute of a statement.

On a guess, I decided to stop all other database activity going on in the
system (db activity on different threads on different databases), and so
far, my test cases pass just fine.

What I was wondering is if there is any chance that database activity into
SQLite from other db connections could somehow influence my db activity on
my test database in returning a SQLITE_BUSY error.

I'm using SQLite 3.4.1 with the dotlock mechanism for thread protection on a
vxWorks custom hardware configuration.  With other problems I have had, they
turned out to be some file i/o method failing due to our custom h/w, so most
likely this is the problem, but just thought I would ask.

Thanks,
Mark



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

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> > to add one last bit, to upgrade older tables.
> > 
> > Unfortunately, code of the form:
> > 
> >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> > 
> > is documented as not supported.
> > http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> > this means that there is no option to do a cheap schema upgrade to get
> > the correct semantics.  Am I missing a trick?
> 
> It appears that you can set
> 
>PRAGMA writable_schema=ON;
> 
> Then do a manual UPDATE of the sqlite_master table to insert
> an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> I tried it and it seems to work.  But it is dangerous.  If you
> mess up, you corrupt the database file.

As long as we're on the topic of writable_schema = ON hacks, it 
seems you can have many tables/indexes point to the same underlying 
pages of another table/index.

Of course it won't survive a VACUUM, and you'll have problems with
pragma integrity_check, but what the heck...

$ ./sqlite3 hack.db
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> CREATE TABLE abc(a, b, c);
sqlite> CREATE INDEX abc_i on abc(c, a);
sqlite> insert into abc values(4,5,6);
sqlite> insert into abc values(1,2,3);
sqlite> insert into abc values(9,8,7);
sqlite> pragma writable_schema=on;
sqlite> .header on
sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|abc|abc|2|CREATE TABLE abc(a, b, c)
index|abc_i|abc|3|CREATE INDEX abc_i on abc(c, a)

Create an "alias" table and index sharing the data of 
the other table via the same rootpage...

sqlite> insert into sqlite_master values('table','xyz','xyz',2,'CREATE TABLE 
xyz(x, y, z, id
INTEGER PRIMARY KEY)');
sqlite> insert into sqlite_master values('index','xyz_i','xyz',3,'CREATE INDEX 
xyz_i on xyz(z,
x)');
sqlite> select * from abc;
a|b|c
4|5|6
1|2|3
9|8|7
sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|abc|abc|2|CREATE TABLE abc(a, b, c)
index|abc_i|abc|3|CREATE INDEX abc_i on abc(c, a)
table|xyz|xyz|2|CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY)
index|xyz_i|xyz|3|CREATE INDEX xyz_i on xyz(z, x)
sqlite> .q

# is there another way to force a reload on the schema from 
# the sqlite3 shell?

$ ./sqlite3 hack.db
SQLite version 3.4.2
Enter ".help" for instructions

Notice the same data in the "aliased" table xyz...

sqlite> select * from xyz;
4|5|6|1
1|2|3|2
9|8|7|3
sqlite> select * from abc;
4|5|6
1|2|3
9|8|7
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE abc(a, b, c);
INSERT INTO "abc" VALUES(4,5,6);
INSERT INTO "abc" VALUES(1,2,3);
INSERT INTO "abc" VALUES(9,8,7);
CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY);
INSERT INTO "xyz" VALUES(4,5,6,1);
INSERT INTO "xyz" VALUES(1,2,3,2);
INSERT INTO "xyz" VALUES(9,8,7,3);
CREATE INDEX abc_i on abc(c, a);
CREATE INDEX xyz_i on xyz(z, x);
COMMIT;

Notice that changing one table affects the other, since they
are sharing underlying data...

sqlite> insert into abc values(2,3,4);
sqlite> delete from xyz where x=1;
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE abc(a, b, c);
INSERT INTO "abc" VALUES(4,5,6);
INSERT INTO "abc" VALUES(9,8,7);
INSERT INTO "abc" VALUES(2,3,4);
CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY);
INSERT INTO "xyz" VALUES(4,5,6,1);
INSERT INTO "xyz" VALUES(9,8,7,3);
INSERT INTO "xyz" VALUES(2,3,4,4);
CREATE INDEX abc_i on abc(c, a);
CREATE INDEX xyz_i on xyz(z, x);
COMMIT;
sqlite> select * from abc order by c, a;
2|3|4
4|5|6
9|8|7
sqlite> select * from xyz order by z, x;
2|3|4|4
4|5|6|1
9|8|7|3

Oh well, it was good while it lasted...

sqlite> pragma integrity_check;
*** in database main ***
List of tree roots: 2nd reference to page 3
List of tree roots: 2nd reference to page 2



   

Pinpoint customers who are looking for what you sell. 
http://searchmarketing.yahoo.com/

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

RE: [sqlite] [Visual Basic] How do you work with SQLite?

[Gilles Ganault]

At 23:25 09/08/2007 +0100, RB Smissaert wrote:
One, via an ADO recordset after connecting to Interbase with ODBC. [...] 
Two, via a custom Delphi dll that moves data directly from Interbase to 
SQLite. This is maybe slightly faster than the first method, but not that 
much difference. So, no ODBC with this method and no ADO recordset.


Thanks for the tip.

G.


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

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Joe Wilson]

--- Scott Hess <[EMAIL PROTECTED]> wrote:
> This may mean that I'll need to branch fts2 to fts3 and deprecate
> fts1/2 as being not safe for use.  If the code is going to have to
> create new tables and populate them, then there's not a lot of gain
> versus just having the developer do that.

Is it a good thing to still call the upgraded module "fts2" if
its schema is not backwards compatible with older versions of 
sqlite/fts2?  This is similar in spirit to the sqlite 3.x file 
format change that was later reverted.

Just playing the devil's advocate - I don't use fts.



   

Looking for a deal? Find great prices on flights and hotels with Yahoo! 
FareChase.
http://farechase.yahoo.com/

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

RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File

[Rich Shepard]

On Tue, 14 Aug 2007, Lee Crain wrote:


I tried both of your suggestions. Both worked correctly.


  Yea, team! Glad you learned something useful, Lee.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File

[Lee Crain]

Rich,

I tried both of your suggestions. Both worked correctly.

Thanks for the extra information,

Lee Crain

__

-Original Message-
From: Rich Shepard [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 13, 2007 5:29 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Need To Export A Table From a SQLite Database as a
TAB Character Delimited Text File

On Mon, 13 Aug 2007, Rich Shepard wrote:

>  And does it work if you specify "\t" as the spacer?

Lee,

   Better yet, try this from the command line:

.m tabs
.o myfilename.txt
select * from mytable
.m list
.o stdout

   The .mode command allows you to specify tabs as the values separator,
just
like the .separator string does. If the latter is not working, the former
should.

Rich

-- 
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax:
503-667-8863

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



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

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[drh]

"Scott Hess" <[EMAIL PROTECTED]> wrote:
> I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> to add one last bit, to upgrade older tables.
> 
> Unfortunately, code of the form:
> 
>ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> 
> is documented as not supported.
> http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> this means that there is no option to do a cheap schema upgrade to get
> the correct semantics.  Am I missing a trick?
> 

It appears that you can set

   PRAGMA writable_schema=ON;

Then do a manual UPDATE of the sqlite_master table to insert
an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
I tried it and it seems to work.  But it is dangerous.  If you
mess up, you corrupt the database file.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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

[sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Scott Hess]

I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
to add one last bit, to upgrade older tables.

Unfortunately, code of the form:

   ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;

is documented as not supported.
http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
this means that there is no option to do a cheap schema upgrade to get
the correct semantics.  Am I missing a trick?

This may mean that I'll need to branch fts2 to fts3 and deprecate
fts1/2 as being not safe for use.  If the code is going to have to
create new tables and populate them, then there's not a lot of gain
versus just having the developer do that.

Thanks,
scott

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

Re: [sqlite] Problem loading extension in Delphi (FTS2)

[Ralf Junker]

Hello Roberto,

>Might be a typo, but your declaration defines the calling convention as 
>'stdcall' (instead of 'cdecl'), was your sqlite3.dll custom built with this 
>calling convention? I don't think much of sqlite would work with stdcall. 

DISQLite3 intentionally uses the 'register' calling convention, internally and 
externally.

Delphi prefers 'register' over 'stdcall' or 'cdecl' as the most efficient, 
since it usually avoids creation of a stack frame. This results in a measurable 
performance improvement compared to sqlite3.dll.

>The problem with DISQLite3 is that it is not free and the sources for the 
>component is not available.

DISQLite3 Personal is free for non-commercial use. Source code is available 
after registering DISQLite3 Pro. Both editions benefit from 'register' calling 
conventions, include full text search (FTS1 and FTS2) as well as ample Delphi 
additions like class wrappers and convenience functions.

Ralf 


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

[sqlite] Re: Re: very slow inserts

[Igor Tandetnik]

Günter Greschenz <[EMAIL PROTECTED]> wrote:

i always thought there is always an index for "rowid" ?


There is, but an index cannot be used for a complex condition like 
rowid%10=x. An index can only be used for comparisons like rowid=x, 
rowid>x, rowid in (x, y, z).


Imagine that you are asked to retrieve every 10's name from a phone 
directory. Would the fact that the book is alphabetically sorted help 
you any?



i need to store a maximum amount of data in the database (e.b. max
10 entries or max 10 days)
any idea how i could implement it in another way ?


Rotate the data between two tables. Insert into msgs table until you get 
10 records in it (keep track of the number of records in a separate 
table: "select count(*) from msgs" runs in linear time, too). Once the 
table is full, rename it to msgs_archive or something (use ALTER TABLE 
statement; drop any existing msgs_archive table first, of course), 
recreate msgs table and start filling it. Lather, rinse, repeat. You 
should be able to do all this with triggers.


That means that sometimes you would have up to twice as much data as you 
strictly need, but you never have less than what you need.


Igor Tandetnik 



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

Re: [sqlite] Re: very slow inserts

[Günter Greschenz]

hi,

thanks for the quick answer :-)
i always thought there is always an index for "rowid" ? thats the reason 
why i implemented it like this...
i need to store a maximum amount of data in the database (e.b. max 
10 entries or max 10 days)

any idea how i could implement it in another way ?

you're right, the pc has a lot of other tasks running !
e.g. there is a ms sql server installed and i dont know exactly how much 
disk-io this server is doing !

thanks a lot !

cu, gg




Igor Tandetnik wrote:

Günter Greschenz <[EMAIL PROTECTED]> wrote:

i've created this table and trigger (no index !):
   CREATE TABLE msgs (date ntext, type ntext, dir ntext, s integer, f
integer, msg ntext);
   CREATE TRIGGER delete_log after insert on msgs begin delete from
msgs where rowid%10=new.rowid%10 and rowid!=new.rowid; end;

my database is filled with about 4 rows


The condition in the trigger forces full scan of all the records in 
msgs table. So on every insert, you scan all the previously inserted 
records. This means that inserting N records takes O(N^2) time.


You should reconsider your design: what you have now can't be improved.


the problem now is: sometimes (only sometimes !!!) inserting into this
table is very slow:


My guess is, it's just an artefact of caching, or perhaps some other 
application doing disk I/O at the same time. Realize that on every 
insert SQLite has to read the whole 256MB file into memory.If you have 
enough memory, it might stay in the cache, which would speed up 
subsequent inserts. But under memory pressure the cache will be 
discarded.


Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







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

Re: [sqlite] Storing monetary values and calculations

[Joe Wilson]

--- Mikey C <[EMAIL PROTECTED]> wrote:
> John Stanton wrote:
> >  We have 
> > added a DECIMAL type to Sqlite.  This stores fixed point numbers in 
> > ASCII display format and performs accurate artithmetic and presents 
> > nicely from HTML.
> 
> In which release was the DECIMAL affinity added to SQLite? Can you specify
> the precision and scale as per SQL-92 standard? e.g. DECIMAL(9,2) ?

There is no support for an SQL-92 DECIMAL data type.
He is describing a convention he is using in his code.


   

Choose the right car based on your needs.  Check out Yahoo! Autos new Car 
Finder tool.
http://autos.yahoo.com/carfinder/

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

[sqlite] Re: very slow inserts

[Igor Tandetnik]

Günter Greschenz <[EMAIL PROTECTED]> wrote:

i've created this table and trigger (no index !):
   CREATE TABLE msgs (date ntext, type ntext, dir ntext, s integer, f
integer, msg ntext);
   CREATE TRIGGER delete_log after insert on msgs begin delete from
msgs where rowid%10=new.rowid%10 and rowid!=new.rowid; end;

my database is filled with about 4 rows


The condition in the trigger forces full scan of all the records in msgs 
table. So on every insert, you scan all the previously inserted records. 
This means that inserting N records takes O(N^2) time.


You should reconsider your design: what you have now can't be improved.


the problem now is: sometimes (only sometimes !!!) inserting into this
table is very slow:


My guess is, it's just an artefact of caching, or perhaps some other 
application doing disk I/O at the same time. Realize that on every 
insert SQLite has to read the whole 256MB file into memory.If you have 
enough memory, it might stay in the cache, which would speed up 
subsequent inserts. But under memory pressure the cache will be 
discarded.


Igor Tandetnik 



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

RE: [sqlite] Problem loading extension in Delphi (FTS2)

[Andre du Plessis]

No it is all cdecl, which is what I am seeing in the message? :)

-Original Message-
From: Roberto [mailto:[EMAIL PROTECTED] 
Sent: 13 August 2007 04:42 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem loading extension in Delphi (FTS2)

Might be a typo, but your declaration defines the calling convention
as 'stdcall' (instead of 'cdecl'), was your sqlite3.dll custom built
with this calling convention? I don't think much of sqlite would work
with stdcall.

On 13/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote:
>
> sqlite3_enable_load_extension : function (sqlite3_value: Pointer; AOnOff: 
> Integer): Integer; cdecl;
> sqlite3_load_extension : function (sqlite3_value: Pointer; AFileName, 
> AProcedure : PChar;
> var AErrorMsg : PChar): Integer; cdecl;
>
> var
>   Error : Pchar;
>   ConnectionHandle : Pointer; // Handle you got from call to sqlite3_open
> begin
> sqlite3_load_extension(ConnectionHandle, 'fts2.dll', nil, Error));
>
> This all worked very well for me.
>
> the problem as you say is that Aducom component does not expose this property 
> for you, but as far as I know you get the source for the component is 
> available so it's a small change for you to expose it.
>
> The problem with DISQLite3 is that it is not free and the sources for the 
> component is not available.
>
> Where fts and sqlite is and there are good documentation for fts.
>
>
> -Original Message-
> From: Ralf Junker [mailto:[EMAIL PROTECTED]
> Sent: 10 August 2007 03:14 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem loading extension in Delphi (FTS2)
>
> Hello Henrik Ræder,
>
> >I'm trying to load the FTS2 extension in Delphi, using the Aducom
> >components. Am really close, but still stuck, and thinking it's a problem
> >with the parameter to sqlite3_enable_load_extension().
>
> DISQLite3 (http://www.yunqa.de/delphi/sqlite3/) does not expose such problems.
>
> Look at the full text search demo project which incorporates both FTS1 and 
> FTS2 into a single *.exe application, with _no_ DLLs or external files needed.
>
> The new customizable tokenizer interface will be demonstrated by a 
> Unicode-aware Pascal tokenizer with the upcoming version of DISQLite3 as soon 
> as the FTS vacuum fix is official released.
>
> Ralf
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

[sqlite] very slow inserts

[Günter Greschenz]

hi everybody,

i have the following problem:

i've created this table and trigger (no index !):
   CREATE TABLE msgs (date ntext, type ntext, dir ntext, s integer, f 
integer, msg ntext);
   CREATE TRIGGER delete_log after insert on msgs begin delete from 
msgs where rowid%10=new.rowid%10 and rowid!=new.rowid; end;


my database is filled with about 4 rows and is about 265mb in size.
the problem now is: sometimes (only sometimes !!!) inserting into this 
table is very slow:


-- this took 59.098 sec:
insert into msgs values ('8/2/2007 4:41:00 AM', 'Primary', 'Out', 5, 1, 'L
   ALCD B: ''129'' = WARNING_ON (bit 8 set, class=1 (warning))
   ALID U4: ''2159''
   ALTX A[80]: ''PM01.M06.1.Gas_07.FlowDeviation.Warning_warning''
   ALTM A[14]: ''20070802044059''
   MODULEID A[27]: ''8ACBI01_IS01_SP01'' = EQP (Basic Equipment)
'
-- this took 72.251 sec:
insert into msgs values ('8/2/2007 8:31:05 AM', 'Primary', 'Out', 5, 1, 'L
   ALCD B: ''130'' = ERROR_ON (bit 8 set, class=2 (error))
   ALID U4: ''714''
   ALTX A[80]: ''AM01.M02.1.FLT_DRIVE_1_alarm''
   ALTM A[14]: ''20070802083103''
   MODULEID A[27]: ''8ACBI01_IS01_SP01'' = EQP (Basic Equipment)
'
--

any idea why its sometimes so slow ?
as programming language is use c# (.net 2.0), as db-layer i use 
"System.Data.SQLte.dll" (v1.0.44.0)

(before i forget it: i dont use transactions while inserting data !)

thankx, gg


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

Re: [sqlite] Storing monetary values and calculations

[Mikey C]

John Stanton wrote:
> 
>  We have 
> added a DECIMAL type to Sqlite.  This stores fixed point numbers in 
> ASCII display format and performs accurate artithmetic and presents 
> nicely from HTML.
> 


In which release was the DECIMAL affinity added to SQLite? Can you specify
the precision and scale as per SQL-92 standard? e.g. DECIMAL(9,2) ?



John Stanton wrote:
> 
> If you store money as an integer with an "implied decimal point" (a 
> familiar method for old-time COBOL programmers) it will work.  We have 
> added a DECIMAL type to Sqlite.  This stores fixed point numbers in 
> ASCII display format and performs accurate artithmetic and presents 
> nicely from HTML.
> 
> Floating point numbers for money is a perennial trap for young players.
> 
> Mikey C wrote:
>> Hi there,
>> 
>> Currently I am using a SQLite 3.x database that stores and calculates
>> currency values using the column type NUMERIC (which I believe has a
>> FLOAT
>> affinity).
>> 
>> However this leads to errors in storing values values in floating point
>> representation.
>> 
>> I guess there is no planned support for direct fixed point types, so what
>> is
>> the best approach?
>> 
>> Store the monetary values in an INTEGER column and multiply all values up
>> by
>> 100 to store in pence/cents?
>> 
>> Any advice?
>> 
>> Thanks,
>> 
>> Mike
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12140213
Sent from the SQLite mailing list archive at Nabble.com.


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