Re: [sqlite] How to determine if database is currently insideatransaction

[Dennis Volodomanov]

> -Original Message-
> From: Igor Tandetnik [mailto:itandet...@mvps.org]
> Sent: Thursday, February 26, 2009 16:05
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to determine if database is currently
> insideatransaction
> 
> "Dennis Volodomanov"
>  wrote in
> message
> news:8501919721c3de4c81bca22846b08721899...@lazarus.conceiva.com
> > Is there any way to determine whether the database is currently
> > inside a BEGIN...END transaction block?
> 
> sqlite3_get_autocommit
> 
> Igor Tandetnik

Ah, thank you - that looks exactly right!

   Dennis

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

Re: [sqlite] How to determine if database is currently inside atransaction

[Igor Tandetnik]

"Dennis Volodomanov"
 wrote in
message
news:8501919721c3de4c81bca22846b08721899...@lazarus.conceiva.com
> Is there any way to determine whether the database is currently
> inside a BEGIN...END transaction block?

sqlite3_get_autocommit

Igor Tandetnik 



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

Re: [sqlite] Transactions and attached databases

[Tito Ciuro]

Hi Donald,

On Feb 25, 2009, at 3:39 PM, Griggs, Donald wrote:

> Greetings, Tito,
>
> Did you see page:
>http://www.sqlite.org/lang_attach.html
>
>
> Transactions involving multiple attached databases are atomic,  
> assuming
> that the main database is not ":memory:".
>
> It then goes on to say:
>
> If the main database is ":memory:" then transactions continue to be
> atomic within each individual database file. But if the host computer
> crashes in the middle of a COMMIT where two or more database files are
> updated, some of those files might get the changes where others might
> not.

No, I didn't look at that page... sorry about that. I was looking at  
the SQLite list instead for answers. Thank you for the help!

Regards,

-- Tito

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

[sqlite] How to determine if database is currently inside a transaction

[Dennis Volodomanov]

Hello,

 

I couldn't find an answer to my question, but maybe it's really
simple...

 

Is there any way to determine whether the database is currently inside a
BEGIN...END transaction block? The only way I can think of at the moment
is to check whether SQLITE_BUSY is returned, but I'm not sure whether
that's the correct way to go.

 

What I need to do, basically, is - I have a long transaction running
(processing thousands of files, committing every 1000 files, for
example), during that time, I may have some new files coming in from a
separate thread. I'd like to get those new files in, so I'd like to stop
the current running transaction (COMMIT), then insert the new records
inside a transaction and then BEGIN a new transaction to continue
processing those files (of course only if there was a transaction active
in the first place - that's why I need to check if there is one running
or not).

 

Any advice greatly appreciated - thanks in advance!

 

   Dennis

 

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

Re: [sqlite] meta-question: In what version did feature X first appear?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

John Machin wrote:
> However my point is that many people 
> are prevented for various reasons for *deploying* that new version into 
> production, and are constrained to remain on some earlier version of 
> SQLite.

This is only going to be the case if SQLite isn't incorporated into your
own code and instead is elsewhere on the system.  I can sure understand
not being able to modify the external version, but there is nothing
preventing you from changing an internal version.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkmmAIIACgkQmOOfHg372QTMmwCfRsA5/0EoBt/GfM5K4jWctK45
DtEAoLS4y0z1QJBaO8ejNHE97HFe01Pb
=EE9K
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] meta-question: In what version did feature X first appear?

[John Machin]

On 26/02/2009 11:55 AM, Roger Binns wrote:

Hi Roger,

> John Machin wrote:
>> In 
>> that situation, the next question to arise would be "What other 
>> currently-documented features must I avoid?"
> 
> The usual solution is for documentation for each API to include version
> information about when it was introduced.
> 
> In the short term you can go to http://www.sqlite.org/cvstrac/timeline
> and then scroll to the bottom of the page.  If you set number of days to
> 1,500 then you'll get the last 4 years of history.

That sounds good. I'll try it out later.

> However do note that the project makes it trivial to update to newer
> versions.  Best practise is to use the amalgamation directly in your
> project which means upgrading is as simple as updating one file.

I agree that SQLite makes it trivial to update to newer versions -- if 
being used directly in C; via language X's DBAPI interface and Linux 
distro Y might be another story :-) However my point is that many people 
are prevented for various reasons for *deploying* that new version into 
production, and are constrained to remain on some earlier version of 
SQLite. To quote the person asking about replace():
"""
Unfortunately though I don't have the option of updating it, since I am
using it on a very widespread set of machines which I don't have root
privileges on...
"""

My question was along the lines of making it easier for such a person to 
determine what set of features they should avoid when writing code.

Cheers,
John

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

Re: [sqlite] manual with sample C programs

[dcharno]

> Could you tell me where can I find such documentation, or can you
> recommend some books.

"The Definitive Guide to SQLite" by Michael Owens explains the SQLite 
API in detail and provides a number of samples in C.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] meta-question: In what version did feature X first appear?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

John Machin wrote:
> In 
> that situation, the next question to arise would be "What other 
> currently-documented features must I avoid?"

The usual solution is for documentation for each API to include version
information about when it was introduced.

In the short term you can go to http://www.sqlite.org/cvstrac/timeline
and then scroll to the bottom of the page.  If you set number of days to
1,500 then you'll get the last 4 years of history.

However do note that the project makes it trivial to update to newer
versions.  Best practise is to use the amalgamation directly in your
project which means upgrading is as simple as updating one file.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkml6IAACgkQmOOfHg372QQzAACaAy1kBPid0YqI2GcSNX+rt048
EzIAoIIMz7itgUfj0KOY/bW7MB2dz7ZR
=JRsP
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] manual with sample C programs

[mrobi002]

Hello users group,

I have been looking in the documentation for sample programs in C, but no
luck yet.

Could you tell me where can I find such documentation, or can you
recommend some books.

Thanks very much

Michael R

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

Re: [sqlite] .timer with windows cli

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Kim Boulton wrote:
> However, how do you get the thing to compile with MinGW?

You tried way too hard :-)

Use the .tar.gz version of the amalgamation as it includes the shell
source code (the .zip doesn't).  Then this command works (no need to use
Make or batch files):

  gcc -O2 -o shell.exe shell.c sqlite3.c

You'll need to add -D flags or edit the source as appropriate.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkml5yUACgkQmOOfHg372QS0WQCgzI70kEvfQ4gUIY4v4Y9Z4Dcy
OxcAn3TJrrkZIGig/V4gg4X4UysMjhDf
=RDiY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions and attached databases

[Griggs, Donald]

 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tito Ciuro
Sent: Wednesday, February 25, 2009 6:36 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Transactions and attached databases

Hello,

If I attach one or more databases and wrap a series of operations which
affect some/all of them, would ROLLBACK or COMMIT treat these operations
atomically?  For example:

Open database 'foo';
Attach database 'bar' as a1;

BEGIN TRANSACTION;
  INSERT INTO main.some_table ... ;
  DELETE FROM main.some_table WHERE ... ;
  INSERT INTO a1.another_table ... ; COMMIT/ROLLBACK TRANSACTION;

Would the insertions and deletions be either committed or rolled back
atomically? Is this supported?

Thanks,

-- Tito


===
===

Greetings, Tito,

Did you see page:
http://www.sqlite.org/lang_attach.html


Transactions involving multiple attached databases are atomic, assuming
that the main database is not ":memory:". 

It then goes on to say:

If the main database is ":memory:" then transactions continue to be
atomic within each individual database file. But if the host computer
crashes in the middle of a COMMIT where two or more database files are
updated, some of those files might get the changes where others might
not. 

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

[sqlite] Transactions and attached databases

[Tito Ciuro]

Hello,

If I attach one or more databases and wrap a series of operations  
which affect some/all of them, would ROLLBACK or COMMIT treat these  
operations atomically?  For example:

Open database 'foo';
Attach database 'bar' as a1;

BEGIN TRANSACTION;
  INSERT INTO main.some_table ... ;
  DELETE FROM main.some_table WHERE ... ;
  INSERT INTO a1.another_table ... ;
COMMIT/ROLLBACK TRANSACTION;

Would the insertions and deletions be either committed or rolled back  
atomically? Is this supported?

Thanks,

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

Re: [sqlite] Scrolling cursor with multiple keys...

[John Elrick]

John Elrick wrote:
> I have a situation where I need to retrieve the 'next' item in a table 
> sorted by an arbitrary number of keys. My current planned solution is to 
> create a table for the sorting which is recreated as needed with the 
> appropriate keys.  As a simplified example
>
> create table x (
>   x_id integer primary key,
>   some_value varchar
> );
>
> create table x_sorter (
>   x_id integer primary key,
>   sort_key1 integer,
>   sort_key2 integer,
>   sort_key3 integer
> );
>
> create index x_sorter_idx1 on x_sorter (sort_key1, sort_key2, sort_key3);
>
> My goal is, given an x_id I wish to locate in table x_sorter the next 
> x_id given x_sorter_idx1.
>
> Data example:
>
> x_sorter
> 1 | 100 | 101 | foo
> 2 | 100 | 100 | 100
> 3 | 101 | 100 | 100
> 4 | 100 | 101 | wibble
>
> Given x_id = 1, the next row should be x_id = 4
>
> If there were only one column, this would be a trivial exercise:
>
> 1 | 100
> 2 | 103
> 3 | 102
> 4 | 101
>
> select x_id from x_sorter where sort_key1 > (select sort_key from 
> x_sorter where x_id = ?) order by sort_key1 limit 0,1
>
> However, this query becomes problematic if there are more than one sort 
> key as we must somehow account for a different path if the first key is 
> equal (rows 1, 2 and 4 in sample data above).
>
> I have thought of and discarded the solution of concatenating the keys 
> into a single string.  While this would work for strictly alphanumeric 
> data, it is a customer requirement is that numeric data be treated as 
> numeric for sort purposes.
>
> What suggestions for approaching this problem are there which I have 
> overlooked?
>   

Solved it on my own.  Doh moment.:

select x_next.x_id
from x_sorter x_current, x_sorter x_next
where
  x_current.x_id = ? and
  x_next.sort_key3 > x_current.sort_key3 and
  x_next.sort_key2 >= x_current.sort_key2 and
  x_next.sort_key1 >= x_current.sort_key1
order by
  x_next.sort_key1,
  x_next.sort_key2,
  x_next.sort_key3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer with windows cli

[Kim Boulton]

Roger,

Thanks for that.

However, how do you get the thing to compile with MinGW?
I Installed mingw and msys, fixed a problem with the msys batch file, 
fixed the paths to mingw so that gcc was found, did a compile and then a 
make but get linker failures at that point.

To get this far I referred the compile guidelines on sqlite.org although 
that info seems a little out of date.

I have MS VC++6 stored away somewhere although I'd rather get minGW 
working if that's the way it's normally compiled.

All this just to turn the timer function on, it might be wiser for the 
sqlite bods to release windows binaries which have the optional 
functions available. The timer would be a popular feature :-)

Cheers.

kim

Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Kim Boulton wrote:
>   
>> Anywhere I can download one would be nice.
>> 
>
> You can get the free MinGW compiler from http://www.mingw.org - it is
> what I use to compile SQLite and other code on Windows.  MinGW is a
> Windows port of gcc plus other GNU tools.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkmlFmcACgkQmOOfHg372QS+DACeNpfb7gF67XkSJGMQ749YHH+A
> CYcAoKlA/O112SUGYrY4LUTMuHq0xjN3
> =rnfb
> -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

Re: [sqlite] PRAGMA page_cache=x doesnt seem to have an effect

[Dave Toll]

Try sqlite3_soft_heap_limit() or use memsys3/memsys5 -
http://www.sqlite.org/malloc.html

Cheers,
Dave.


-Original Message-
From: ed [mailto:epdm...@gmail.com] 
Sent: 25 February 2009 10:50
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA page_cache=x doesnt seem to have an effect

Does anyone have any experience or examples of reducing sqlite memory
usage through the pragma cache_size, page_size satements?
I am misunderstanding the intended use of the pragmas?

thanks,
ed

On Fri, Feb 20, 2009 at 5:58 PM, ed  wrote:
> Hello,
> I am using sqlite in an embedded environment with very limited memory.
> In an attempt to reduce memory consumed by sqlite, I am executing
> PRAGMA cache_size=new_size and PRAGMA default_cache_size=new_size upon
> opening all of my db's, which are both file based and in-memory. I
> have varied the value of new_size from 2000 (default), to 1000 to 500
> to 0 and none of these values seems to lower the amount of memory
> sqlite consumes.
>
> I have verified the new cache size settings were registered in the
> db's using PRAGMA cache_size.
> I then verify sqlite memory usage with the sqlite3_status() functions,
> which do not show a change in return value for
> SQLITE_STATUS_PAGECACHE_OVERFLOW or SQLITE_STATUS_MEMORY_USED (around
> 7.9MB and 8.2MB respectively)
>
> I have not changed the default memory allocation (ie no scratch, cache
> or heap memory allocation at startup).
> I have configured sqlite3_soft_heap_limit(8192000).
>
> Any help in figuring out how to limit the cache usage of memory would
> be appreciated.
>
> --
> thanks,
> ed
>

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

[sqlite] meta-question: In what version did feature X first appear?

[John Machin]

This is becoming a FAQ. E.g. here is a precis of a very recent thread:

OP: Error message is : 'SQL error :near "savepoint": syntax error'
Dan: Probably you need to upgrade. SAVEPOINT first appeared in 3.6.8.
OP: That was the problem; I had 3.6.4 version

Yesterday (my time) there was another, about the replace() function. The 
only clue I could find about that on the SQLite website was in the 
bugtracker, which led me to a date, but not a version. The 2 or 3 web 
pages about "what's changed between version Y and version Z" didn't 
mention "replace". Is there some part of the site that I missed?

In the replace() case, the OP was stuck with no upgrade capability. In 
that situation, the next question to arise would be "What other 
currently-documented features must I avoid?"

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

[sqlite] Scrolling cursor with multiple keys...

[John Elrick]

I have a situation where I need to retrieve the 'next' item in a table 
sorted by an arbitrary number of keys. My current planned solution is to 
create a table for the sorting which is recreated as needed with the 
appropriate keys.  As a simplified example

create table x (
  x_id integer primary key,
  some_value varchar
);

create table x_sorter (
  x_id integer primary key,
  sort_key1 integer,
  sort_key2 integer,
  sort_key3 integer
);

create index x_sorter_idx1 on x_sorter (sort_key1, sort_key2, sort_key3);

My goal is, given an x_id I wish to locate in table x_sorter the next 
x_id given x_sorter_idx1.

Data example:

x_sorter
1 | 100 | 101 | foo
2 | 100 | 100 | 100
3 | 101 | 100 | 100
4 | 100 | 101 | wibble

Given x_id = 1, the next row should be x_id = 4

If there were only one column, this would be a trivial exercise:

1 | 100
2 | 103
3 | 102
4 | 101

select x_id from x_sorter where sort_key1 > (select sort_key from 
x_sorter where x_id = ?) order by sort_key1 limit 0,1

However, this query becomes problematic if there are more than one sort 
key as we must somehow account for a different path if the first key is 
equal (rows 1, 2 and 4 in sample data above).

I have thought of and discarded the solution of concatenating the keys 
into a single string.  While this would work for strictly alphanumeric 
data, it is a customer requirement is that numeric data be treated as 
numeric for sort purposes.

What suggestions for approaching this problem are there which I have 
overlooked?

Thanks,


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

Re: [sqlite] SAVEPOINT : Seems don't work : SOLVED (version problem)

[REPKA_Maxime_NeufBox]

Very good answer : That was the problem; I had 3.6.4 version : It works with
the last version
Thank's a lot Dan
MaxMax14

-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]de la part de Dan
Envoye : mercredi 25 fevrier 2009 05:28
A : General Discussion of SQLite Database
Objet : Re: [sqlite] SAVEPOINT : Seems don't work



On Feb 25, 2009, at 2:31 AM, REPKA_Maxime_NeufBox wrote:

> I try for test as follow :
>
> on DOS : sqlite3.exe Database
>
> then type : SAVEPOINT spoint;
> Error message is : 'SQL error :near "savepoint": syntax error'

Probably you need to upgrade. SAVEPOINT first appeared in 3.6.8.

Dan.

___
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] Displaying Large Tables

[Thomas Briggs]

   Setting aside the fact that it seems silly to try to show people an
8 million row resultset...

   You could merge the two ideas: create a temp table containing just
the rowids you want, in the order that you want, and then use LIMIT
and OFFSET to get the particular ones you want.  Using those rowids,
retrieve the actual rows from the original table.  There are a couple
ways you can deal with the situation where a row gets deleted... I
leave that for you to ponder.

   Seriously though, I doubt that what you're trying to do is ever
really necessary, nor even desirable.  Nobody can actually look at,
comprehend or even do anything with 8 million rows of results...


On Wed, Feb 25, 2009 at 2:08 PM, Nikolas Stevenson-Molnar
 wrote:
> I'm working on a simple SQLITE database browser (part of a larger
> program) and I'm running into problems displaying larger (~8 million
> rows) tables. I originally set out using OFFSET and LIMIT to grab the
> rows I need to display on screen at any given time, but realized that
> this becomes horribly slow as one gets to the millionth row onward. I
> found this article: http://www.sqlite.org/cvstrac/wiki?
> p=ScrollingCursor which offers an alternative method, but I can't make
> use of it, as I have to allow for a variety of queries (and user-
> defined queries) and can't employ the constraints here. I also can't
> go by rowid, since I can't be certain that rows haven't been deleted.
>
> My temporary solution for the moment is to preload hold a couple
> thousand rows at a time in memory, which makes things somewhat
> bearable, and my only other idea is to create a temp table for every
> query which I can query by rowid, but I don't want to be copying
> potentially millions of rows all the time.
>
> Does anyone have any other ideas on how to do this?
>
> Thanks!
> _Nik
> ___
> 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] Displaying Large Tables

[Nikolas Stevenson-Molnar]

I'm working on a simple SQLITE database browser (part of a larger  
program) and I'm running into problems displaying larger (~8 million  
rows) tables. I originally set out using OFFSET and LIMIT to grab the  
rows I need to display on screen at any given time, but realized that  
this becomes horribly slow as one gets to the millionth row onward. I  
found this article: http://www.sqlite.org/cvstrac/wiki? 
p=ScrollingCursor which offers an alternative method, but I can't make  
use of it, as I have to allow for a variety of queries (and user- 
defined queries) and can't employ the constraints here. I also can't  
go by rowid, since I can't be certain that rows haven't been deleted.

My temporary solution for the moment is to preload hold a couple  
thousand rows at a time in memory, which makes things somewhat  
bearable, and my only other idea is to create a temp table for every  
query which I can query by rowid, but I don't want to be copying  
potentially millions of rows all the time.

Does anyone have any other ideas on how to do this?

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

Re: [sqlite] PRAGMA page_cache=x doesnt seem to have an effect

[ed]

Does anyone have any experience or examples of reducing sqlite memory
usage through the pragma cache_size, page_size satements?
I am misunderstanding the intended use of the pragmas?

thanks,
ed

On Fri, Feb 20, 2009 at 5:58 PM, ed  wrote:
> Hello,
> I am using sqlite in an embedded environment with very limited memory.
> In an attempt to reduce memory consumed by sqlite, I am executing
> PRAGMA cache_size=new_size and PRAGMA default_cache_size=new_size upon
> opening all of my db's, which are both file based and in-memory. I
> have varied the value of new_size from 2000 (default), to 1000 to 500
> to 0 and none of these values seems to lower the amount of memory
> sqlite consumes.
>
> I have verified the new cache size settings were registered in the
> db's using PRAGMA cache_size.
> I then verify sqlite memory usage with the sqlite3_status() functions,
> which do not show a change in return value for
> SQLITE_STATUS_PAGECACHE_OVERFLOW or SQLITE_STATUS_MEMORY_USED (around
> 7.9MB and 8.2MB respectively)
>
> I have not changed the default memory allocation (ie no scratch, cache
> or heap memory allocation at startup).
> I have configured sqlite3_soft_heap_limit(8192000).
>
> Any help in figuring out how to limit the cache usage of memory would
> be appreciated.
>
> --
> thanks,
> ed
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] running sqlite with gcc C

[Kees Nuyt]

On Wed, 25 Feb 2009 10:04:18 -0500 (EST), Michael
(mrobi...@cs.fiu.edu) wrote :

>Thank you, it works perfectly
>The links at the bottom of your email helped a lot.

I'm glad it does work.
By the way, the links were really easy to find. In my humble
opinion you would benefit from spending more time on the
site ;)
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL error: no such function: replace

[Igor Tandetnik]

BenJones12345 
wrote:
> Ah... its version 3.3.5
>
> Unfortunately though I don't have the option of updating it, since I
> am using it on a very widespread set of machines which I don't have
> root privileges on...
>
> Is there another way I can achieve the same effect without this
> function?

If it's always the second character that needs replacing, you could try 
something like this:

UPDATE TheTable
set TheNewField =
substr(TheOldField, 1, 1) ||
(case substr(TheOldField, 2, 1) when 'P' then '+' else '-' end) ||
substr(TheOldField, 3);

Hopefully, substr() function exists in the version you use.

Igor Tandetnik 



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

Re: [sqlite] SQlite3 problem with floating point for arm-linux

[Dave Flogeras]

On February 25, 2009 10:36:31 am D. Richard Hipp wrote:
> You probably need to compile on ARM with the -
> DSQLITE_MIXED_ENDIAN_64BIT_FLOAT=1 flag.  There is a comment in the
> code that explains this flag in more detail.  Use "grep" to find it.
>

Thank you very much, that worked perfectly.

Dave

-- 
The information contained in this e-mail may contain confidential  
information intended for a specific individual and purpose. The  
information is private and is legally protected by law. If you are not  
the intended recipient, you are hereby notified that any disclosure,  
copying, distribution or the taking of any action in reliance on the  
comments of this information is strictly prohibited. If you have  
received this communication in error, please notify the sender  
immediately by telephone or return e-mail.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] running sqlite with gcc C, continuation.....

[mrobi002]

Thank you very much it works fine


Michael

> Hi,
>
> you have to compile sqlite3.c, using something like
>
> gcc -c sqlite3.c
>
> making an object file, and then linking it to your own program.
> Your call combines compiler and linker using only your own source file.
>
> Also, it should not be necessary to define sqlite3_open and sqlite3_close
> (or any other sqlite function), because you already included sqlite3.h
>
> Martin
>
> mrobi...@cs.fiu.edu wrote:
>> Sorry,
>>
>> Maybe I should have included the following details:
>>
>>
>> This is the program:
>>
>> #include 
>> #include 
>> #include 
>>
>>
>>
>> int sqlite3_open(
>>   const char *filename,   /* Database filename (UTF-8) */
>>   sqlite3 **ppDb  /* OUT: SQLite db handle */
>> );
>>
>>
>> int sqlite3_close(sqlite3 *);
>>
>>
>> int main()
>> {
>> printf("hi\n");
>>
>> sqlite3 *db;
>> const char KDbName[] = "c:\\test\\Db1.db";
>> int rc = sqlite3_open(KDbName, &db);
>> if (rc)
>> {
>>  sqlite3_close (db);
>>  return 0;
>> }
>>
>>
>> exit (0);
>> }
>>
>>
>> =
>>
>> and these are the errors when compiling
>>
>>
>>
>> C:\SQLite>\gcc\gcc -o s.exe s.c
>> C:\DOCUME~1\Michael\LOCALS~1\Temp/ccigbaaa.o:s.c:(.text+0x71): undefined
>> referen
>> ce to `sqlite3_open'
>> C:\DOCUME~1\Michael\LOCALS~1\Temp/ccigbaaa.o:s.c:(.text+0x85): undefined
>> referen
>> ce to `sqlite3_close'
>> collect2: ld returned 1 exit status
>>
>>
>> Thanks for your help,
>>
>> Michael Robinson
>>
>> ___
>> 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] running sqlite with gcc C

[mrobi002]

Thank you, it works perfectly
The links at the bottom of your email helped a lot.


Michael



> On Wed, 25 Feb 2009 09:05:29 -0500 (EST),
> mrobi...@cs.fiu.edu wrote in General Discussion of SQLite
> Database :
>
>>Dear sqlite users group,
>>
>>I am new at SQlite3 and I would be very thankful for your help.
>>
>>I read that SQlite does not require installation, so I downloaded the
>>Precompiled Binaries For Windows and run sqlite3.exe in windows xp, no
>>problem, however, I have some gcc C programs that I run in windows xp
>>"dos" shell and in Linux and Unix, same code, just recompiled in its
>>corresponding OS. I would like to use SQlite3 with these gcc C programs.
>>
>>Reading thru the users group postings and in google in general, I came to
>>the conclusion that some kind of installation may be required, but I have
>>not been able to find instructions about it. Please guide me to get this
>>issued resolved so that I can start creating great apps with SQlite.
>
> Download the amalgamation.
> Compile it to create the SQLite library.
> Start without SQLITE_* defines, the defaults are Ok.
> Write your program.
> Link the SQLite library with it.
> Later, optimize SQLITE_* defined when needed.
> For platforms yet unknown to SQLite, you may have to write
> your own OS interface functions (sqlite3_vfs).
>
> http://www.sqlite.org/cvstrac/wiki?p=HowToCompile
> http://www.sqlite.org/cvstrac/wiki?p=TheAmalgamation
> http://www.sqlite.org/cvstrac/wiki?p=SqliteBuildProcess
> http://www.sqlite.org/docs.html
> http://www.sqlite.org/sitemap.html
> http://www.sqlite.org/cvstrac/wiki
>
>>Thank you very much
>>
>>Michael R
>
> HTH
> --
>   (  Kees Nuyt
>   )
> c[_]
> ___
> 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] UPDATE Field based on matching value in different Table

[Greg Robertson]

Igor,

I am using a temporary table where that condition will not occur but I
do appreciate the info as I was considering re-writing the SQL to see
if I could do it without using a temporary table and that would have
created the NULL condition you noted.

Thanks again for noting the potential problem with the SQL, it has
undoubtably saved me considerable time had I tried to re-write the SQL
without a temporary table.

And yes I did make a typo it should have been TableA.Field1.

Also thanks to all who helped me find a solution.


Greg


--

Message: 10
Date: Mon, 23 Feb 2009 20:35:41 -0500
From: "Igor Tandetnik" 
Subject: Re: [sqlite] UPDATE Field based on matching value in
   different Table
To: sqlite-users@sqlite.org
Message-ID: 

"Greg Robertson" 
wrote in message
news:151e70a00902231728j608612b8n491e84b11c70c...@mail.gmail.com
> That did it.
>
> Thanks
>
> Greg
>
> On Mon, Feb 23, 2009 at 7:05 PM, Griggs, Donald
>  wrote:
>> Hi Greg,
>>
>> Someone on the list may give a better reply, and I'm sending this to
>> you
>> directly, but I think the following will work:
>>
>> Update tableA
>>  set Field2 = ( select Field4 from tableB
>>where TableA.Field3=TableB.Field4 );

If there are any rows in tableA without a matching row in tableB, this
would set Field2 in all such rows to NULL. This may or may not matter in
your case, of course.

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

Re: [sqlite] running sqlite with gcc C, continuation.....

[Martin Engelschalk]

Hi,

you have to compile sqlite3.c, using something like

gcc -c sqlite3.c

making an object file, and then linking it to your own program. 
Your call combines compiler and linker using only your own source file.

Also, it should not be necessary to define sqlite3_open and sqlite3_close (or 
any other sqlite function), because you already included sqlite3.h

Martin

mrobi...@cs.fiu.edu wrote:
> Sorry,
>
> Maybe I should have included the following details:
>
>
> This is the program:
>
> #include 
> #include 
> #include 
>
>
>
> int sqlite3_open(
>   const char *filename,   /* Database filename (UTF-8) */
>   sqlite3 **ppDb  /* OUT: SQLite db handle */
> );
>
>
> int sqlite3_close(sqlite3 *);
>
>
> int main()
> {
> printf("hi\n");
>
> sqlite3 *db;
> const char KDbName[] = "c:\\test\\Db1.db";
> int rc = sqlite3_open(KDbName, &db);
> if (rc)
> {
>  sqlite3_close (db);
>  return 0;
> }
>
>
> exit (0);
> }
>
>
> =
>
> and these are the errors when compiling
>
>
>
> C:\SQLite>\gcc\gcc -o s.exe s.c
> C:\DOCUME~1\Michael\LOCALS~1\Temp/ccigbaaa.o:s.c:(.text+0x71): undefined
> referen
> ce to `sqlite3_open'
> C:\DOCUME~1\Michael\LOCALS~1\Temp/ccigbaaa.o:s.c:(.text+0x85): undefined
> referen
> ce to `sqlite3_close'
> collect2: ld returned 1 exit status
>
>
> Thanks for your help,
>
> Michael Robinson
>
> ___
> 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] running sqlite with gcc C, continuation.....

[mrobi002]

Sorry,

Maybe I should have included the following details:


This is the program:

#include 
#include 
#include 



int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb  /* OUT: SQLite db handle */
);


int sqlite3_close(sqlite3 *);


int main()
{
printf("hi\n");

sqlite3 *db;
const char KDbName[] = "c:\\test\\Db1.db";
int rc = sqlite3_open(KDbName, &db);
if (rc)
{
 sqlite3_close (db);
 return 0;
}


exit (0);
}


=

and these are the errors when compiling



C:\SQLite>\gcc\gcc -o s.exe s.c
C:\DOCUME~1\Michael\LOCALS~1\Temp/ccigbaaa.o:s.c:(.text+0x71): undefined
referen
ce to `sqlite3_open'
C:\DOCUME~1\Michael\LOCALS~1\Temp/ccigbaaa.o:s.c:(.text+0x85): undefined
referen
ce to `sqlite3_close'
collect2: ld returned 1 exit status


Thanks for your help,

Michael Robinson

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

Re: [sqlite] running sqlite with gcc C

[Martin Engelschalk]

Hello Michael,

download the source code, compile it with your compiler and link it to 
your program.
See http://www.sqlite.org/download.html. Its easiest to use the 
amalgamation: sqlite-amalgamation-3_6_11.zip 

There is indeed no installation

Martin

mrobi...@cs.fiu.edu wrote:
> Dear sqlite users group,
>
> I am new at SQlite3 and I would be very thankful for your help.
>
> I read that SQlite does not require installation, so I downloaded the
> Precompiled Binaries For Windows and run sqlite3.exe in windows xp, no
> problem, however, I have some gcc C programs that I run in windows xp
> "dos" shell and in Linux and Unix, same code, just recompiled in its
> corresponding OS. I would like to use SQlite3 with these gcc C programs.
>
> Reading thru the users group postings and in google in general, I came to
> the conclusion that some kind of installation may be required, but I have
> not been able to find instructions about it. Please guide me to get this
> issued resolved so that I can start creating great apps with SQlite.
>
> Thank you very much
>
> Michael R
>
> ___
> 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] running sqlite with gcc C

[Kees Nuyt]

On Wed, 25 Feb 2009 09:05:29 -0500 (EST),
mrobi...@cs.fiu.edu wrote in General Discussion of SQLite
Database :

>Dear sqlite users group,
>
>I am new at SQlite3 and I would be very thankful for your help.
>
>I read that SQlite does not require installation, so I downloaded the
>Precompiled Binaries For Windows and run sqlite3.exe in windows xp, no
>problem, however, I have some gcc C programs that I run in windows xp
>"dos" shell and in Linux and Unix, same code, just recompiled in its
>corresponding OS. I would like to use SQlite3 with these gcc C programs.
>
>Reading thru the users group postings and in google in general, I came to
>the conclusion that some kind of installation may be required, but I have
>not been able to find instructions about it. Please guide me to get this
>issued resolved so that I can start creating great apps with SQlite.

Download the amalgamation.
Compile it to create the SQLite library.
Start without SQLITE_* defines, the defaults are Ok.
Write your program.
Link the SQLite library with it.
Later, optimize SQLITE_* defined when needed.
For platforms yet unknown to SQLite, you may have to write
your own OS interface functions (sqlite3_vfs).

http://www.sqlite.org/cvstrac/wiki?p=HowToCompile
http://www.sqlite.org/cvstrac/wiki?p=TheAmalgamation
http://www.sqlite.org/cvstrac/wiki?p=SqliteBuildProcess
http://www.sqlite.org/docs.html
http://www.sqlite.org/sitemap.html
http://www.sqlite.org/cvstrac/wiki

>Thank you very much
>
>Michael R

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

Re: [sqlite] SQlite3 problem with floating point for arm-linux

[D. Richard Hipp]

On Feb 25, 2009, at 9:23 AM, Dave Flogeras wrote:

> Hi list,
>
> I am attempting to use sqlite-3.6.11 for a project using an embedded  
> board
> with a PXA255 processor. The data will then be transferred to a  
> regular x86
> desktop to be analyzed.
>
> I have compiled sqlite for both embedded/desktop platforms using the
> amalgamated sources.  I do nothing exotic in ./configure, but of  
> course for
> the arm build I have to use --host=arm-linux.
>
> If I create a table on the embedded machine, say with:
>
> CREATE TABLE test ( val FLOAT );
> INSERT INTO test VALUES( 1.0 );
> INSERT INTO test VALUES( 0.779 );
>
> When I transfer this database to the x86 desktop and open it, doing:
> SELECT * from test;
>
> yields:
> 1.0
> 1.28152653820806e+282
>
>
> It would seem that there is a cross-platform issue with how the arm  
> compiler
> represents its floats.  Introducing a fractional part seems to screw  
> it up,
> floats which are representable with whole numbers come out correctly.
>
> The embedded system uses a GCC based toolchain (3.4).  I have tried  
> with both
> 3.4.2 and 3.4.4, to no avail, same results.
>
> Has anyone seen this, or can offer a suggestion?


You probably need to compile on ARM with the - 
DSQLITE_MIXED_ENDIAN_64BIT_FLOAT=1 flag.  There is a comment in the  
code that explains this flag in more detail.  Use "grep" to find it.


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



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

[sqlite] SQlite3 problem with floating point for arm-linux

[Dave Flogeras]

Hi list,

I am attempting to use sqlite-3.6.11 for a project using an embedded board 
with a PXA255 processor. The data will then be transferred to a regular x86 
desktop to be analyzed.

I have compiled sqlite for both embedded/desktop platforms using the 
amalgamated sources.  I do nothing exotic in ./configure, but of course for 
the arm build I have to use --host=arm-linux.

If I create a table on the embedded machine, say with:

CREATE TABLE test ( val FLOAT );
INSERT INTO test VALUES( 1.0 );
INSERT INTO test VALUES( 0.779 );

When I transfer this database to the x86 desktop and open it, doing:
SELECT * from test;

yields:
1.0
1.28152653820806e+282


It would seem that there is a cross-platform issue with how the arm compiler 
represents its floats.  Introducing a fractional part seems to screw it up, 
floats which are representable with whole numbers come out correctly.

The embedded system uses a GCC based toolchain (3.4).  I have tried with both 
3.4.2 and 3.4.4, to no avail, same results.

Has anyone seen this, or can offer a suggestion?

Thanks,
Dave Flogeras

-- 
The information contained in this e-mail may contain confidential  
information intended for a specific individual and purpose. The  
information is private and is legally protected by law. If you are not  
the intended recipient, you are hereby notified that any disclosure,  
copying, distribution or the taking of any action in reliance on the  
comments of this information is strictly prohibited. If you have  
received this communication in error, please notify the sender  
immediately by telephone or return e-mail.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] running sqlite with gcc C

[mrobi002]

Dear sqlite users group,

I am new at SQlite3 and I would be very thankful for your help.

I read that SQlite does not require installation, so I downloaded the
Precompiled Binaries For Windows and run sqlite3.exe in windows xp, no
problem, however, I have some gcc C programs that I run in windows xp
"dos" shell and in Linux and Unix, same code, just recompiled in its
corresponding OS. I would like to use SQlite3 with these gcc C programs.

Reading thru the users group postings and in google in general, I came to
the conclusion that some kind of installation may be required, but I have
not been able to find instructions about it. Please guide me to get this
issued resolved so that I can start creating great apps with SQlite.

Thank you very much

Michael R

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

Re: [sqlite] How to size and position a scrollbar within a virtual listview

[Rich Rattanni]

I thought Puneet's reply was good.  When you make statements like
query the internal btree table to request at least the internal
pagenr w... I think you are asking too much of sqlite, right?  The
purpose of a database engine is to abstract the gory details and make
your life easier, just as is with any library API you may use.  Unless
this is a extremely resource limited or real-time project, why make
your life complicated?

On Wed, Feb 25, 2009 at 4:51 AM, Mail.sqlite  wrote:
> query the internal btree table to request at least the internal pagenr w
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

[Allan Edwards]

I must trade my works for currency.  "Will Code for FOOD" : - )  I
consult and architect systems professionally and really can't afford
to do much free work.  As much as I love to develop solutions, we are
not in the Star Trek age of we just live to better others and
ourselves!  If we were you would find me saying "Computer, lets work
up a new piece of software on Sqlite... etc. etc. etc."



On Tue, Feb 24, 2009 at 7:55 AM, P Kishor  wrote:
> On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards  
> wrote:
> ..
>>
>> I have personally written a socket based server on top of the database
>> and it works very well.  So I have actually scaled the database myself
>> as I preached in this email.  Don't be a WIMP and map shares to share
>> a database... write a socket based beauty like YOURS TRULY! hehe  For
>> "most" solutions it is wonderful.  After years and building millions
>> of lines of code keeping the business delivery requirements fulfilled
>> in the most simple manner seems to be the best approach for me.  If
>> you are the same, stay agnostic to all solutions available and run up
>> a strategy that will give you the best of all worlds.  And yes, at
>> times you have to write a little EXTRA code to get there! : - )
>>
>> Allan
>>
>> P.S.  If somebody does decide to build enterprise Sqlite, I would love
>> to throw in my 2 cents on how to write the stuff on the outside to add
>> in the big dog features.  I was working out tonight and while thinking
>> about this I believe you could actually maintain the wonderfulness of
>> the core engine, then scale the library from an outside piece of code.
>>  Then you can keep integrity on both sides of the fence and not make
>> sqlite into sqlitetoomuch.
>>
> ..
>
> Have you considered taking the "socket based server" that you wrote, I
> am assuming, on top of SQLite, and donating it to public domain/open
> source, putting it on the sqlite wiki, so others may benefit?
>
> Who knows, with a seed like that, someone may well build a
> SQLiteEnterprise (as much an oxymoron as that might be).
>
> --
> Puneet Kishor http://www.punkish.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> Sent from: Madison WI United States.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
W Allan Edwards
214-289-2959
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer with windows cli

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Kim Boulton wrote:
> Anywhere I can download one would be nice.

You can get the free MinGW compiler from http://www.mingw.org - it is
what I use to compile SQLite and other code on Windows.  MinGW is a
Windows port of gcc plus other GNU tools.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkmlFmcACgkQmOOfHg372QS+DACeNpfb7gF67XkSJGMQ749YHH+A
CYcAoKlA/O112SUGYrY4LUTMuHq0xjN3
=rnfb
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to size and position a scrollbar within a virtual listview

[Mail.sqlite]

Puneet,
 
Thank you for your help.
 
Yes your solution would work. Due to the fact that i have many (up to 10) keys 
within a huge table, I would have to recreate the temp table with a additional 
key for every index or 'order by' clause I would ever use. This must happen 
every day or at least every few days for these tables with up to 10 million 
rows. That could use quite some resources.
 
It would be much easier if there is a way to query the internal btree table to 
request at least the internal pagenr where the requested key is saved. so we 
got an estimate i.e. Key is saved in Page 1 from 2 = 50%. 
 
Due to the fact that this problem should have been faced by so many SQLite 
users before - may be I'm on the completly wrong way...
 
George
 

-Ursprüngliche Nachricht- 
Von: P Kishor  
An: General Discussion of SQLite Database  
Datum: 24-02-2009 15:05 
Betreff: Re: [sqlite] How to size and position a scrollbar within a virtual 
listview 

On Tue, Feb 24, 2009 at 3:16 AM, Mail.sqlite  wrote:
>
> Hi All,
> I searched trough many messages and docs but did not find a solution to this 
> trivial looking problem. It would be really nice if someone could point me 
> into the right direction.
>
> my ToDo:
> I have to use a virtual listview for a database with some medium and large 
> tables. I would give the user the opportunity to select the active displayed 
> sorting order with a click on the corresponding listview header.
> To get the necessary number of records for the table with high performance, 
> even for large files, this can be done with a trigger that updates a record 
> elsewhere with every delete or insert operation.
>
> My Problem:
> how to get the relative position for the selected row depending on the 
> selected “order by” clause as fast as possible? An estimate should be enough 
> to position the scrollbar.
>

If you had sequential IDs of some sort in your returned result set,
this would be a trivial problem.

Of course, you are likely to not have a continuous sequence in a
database table because some rows may have been deleted. But, you can
fool it by creating a temp table to store your result set... consider
the following --

sqlite> create table foo (a integer primary key, b text);
sqlite> insert into foo (b) values ('blah');
sqlite> insert into foo (b) values ('grop');
sqlite> insert into foo (b) values ('farc');
sqlite> insert into foo (b) values ('drat');
sqlite> select rowid, a, b from foo;
1|1|blah
2|2|grop
3|3|farc
4|4|drat
sqlite> delete from foo where a = 3;
sqlite> select rowid, a, b from foo;
1|1|blah
2|2|grop
4|4|drat
sqlite> create temp table bar as select a, b from foo;
sqlite> select rowid, a, b from bar;
1|1|blah
2|2|grop
3|4|drat
sqlite>


Now we are back to where your problem is trivial. You can use a
rowid/count(*) value to determine the "position" of a row in an
ordered set.




> Thanks for any hints.
>
> George
>




-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
Sent from: Madison WI United States.
___
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] .timer with windows cli

[Kim Boulton]

Hello,

Is there a compiled v3.6.11 sqlite3.exe with the .timer facility 
enabled. I don't have a C compiler on this machine.

Anywhere I can download one would be nice.

Thanks

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