Re: [sqlite] How can we use POWER(2,32) like function in SQLite

On Thu, Jan 20, 2011 at 8:23 AM, Sunil Bhardwaj < sbhard...@ipolicynetworks.com> wrote: > Hi > > Please suggest, How can we use POWER(2,32) like function in SQLite? > > This particular call or any with base 2 can be replaced with bitwise shift SELECT 1 << 32 Max

Re: [sqlite] Committing to a savepoint

ges (the only place to know sqlite file format), i.e. there's a known last actions about writing some fields when the transaction ends. When xWrite filter detects this special write, it writes EndOfTransaction record to the log. The following undo operations limits resto

Re: [sqlite] How to track a record if the aliased id is changed

On Fri, Jan 14, 2011 at 1:57 PM, Philip Graham Willoughby < phil.willoug...@strawberrycat.com> wrote: > > On 14 Jan 2011, at 10:45, Max Vlasov wrote: > > > Phil, it's an interesting solution. I will probably use it, but the > current > > problem is that I have to r

Re: [sqlite] how to put the database in cache without waiting the system do it by himself ?

(your select here)" and execute it when the program starts. In this case you don't have to save this temporary result (it's a single row and single column) and the system and sqlite will read the same parts of the file that will be needed later. Max Vlasov

Re: [sqlite] How to track a record if the aliased id is changed

On Fri, Jan 14, 2011 at 1:20 PM, Philip Graham Willoughby < phil.willoug...@strawberrycat.com> wrote: > On 13 Jan 2011, at 17:27, Nicolas Williams wrote: > > > On Thu, Jan 13, 2011 at 10:59:29AM +, Simon Slavin wrote: > >> His problem is that he doesn't know which rows are aliased to rowid, >

Re: [sqlite] Committing to a savepoint

, such implementation would help to implement you "commit to save point'. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to track a record if the aliased id is changed

On Thu, Jan 13, 2011 at 1:59 PM, Simon Slavin wrote: > > On 13 Jan 2011, at 10:48am, Simon Davies wrote: > > > CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId, Id ON > TestTable > > BEGIN > > UPDATE UpdatedRowId Set Value = new.rowid; > > END; > > His

Re: [sqlite] How to track a record if the aliased id is changed

On Thu, Jan 13, 2011 at 1:48 PM, Simon Davies <simon.james.dav...@gmail.com>wrote: > On 13 January 2011 10:28, Max Vlasov <max.vla...@gmail.com> wrote: > > CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE OF RowId, Id ON > TestTable > BEGIN > UPDATE Upda

Re: [sqlite] How to track a record if the aliased id is changed

On Thu, Jan 13, 2011 at 12:46 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 13 Jan 2011, at 8:53am, Dan Kennedy wrote: > > > On 01/13/2011 02:31 AM, Max Vlasov wrote: > >> Hi, > >> for queries like UPDATE ... WHERE rowid=... one can in mos

Re: [sqlite] How to track a record if the aliased id is changed

On Thu, Jan 13, 2011 at 2:28 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 12 Jan 2011, at 10:54pm, Max Vlasov wrote: > > > On Thu, Jan 13, 2011 at 1:07 AM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > >> [snip] If you're just > >

Re: [sqlite] How to track a record if the aliased id is changed

On Thu, Jan 13, 2011 at 1:07 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 12 Jan 2011, at 9:57pm, Max Vlasov wrote: > > > Simon, your reply led me to the following sequence: > > - I know the rowid of the record I'm changing. I remember all integers > (and

Re: [sqlite] How to track a record if the aliased id is changed

On Thu, Jan 13, 2011 at 12:21 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 12 Jan 2011, at 9:02pm, Max Vlasov wrote: > > > It's about the utility, when the data is presented with a grid and every > > cell of opened db and * fields of table can be edited.

Re: [sqlite] How to track a record if the aliased id is changed

On Wed, Jan 12, 2011 at 11:35 PM, Simon Slavin wrote: > If you're writing an arbitrary SQL utility, I think the answer depends on > why you want to keep track of a particular record. You either want to > refresh the display or you don't, and either way the connection

[sqlite] How to track a record if the aliased id is changed

the aliased fields but there's at least one exception in the docs... Any thoughts? Thanks, Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.7.4 possible restore bug

On Wed, Jan 12, 2011 at 3:48 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Jan 12, 2011 at 5:27 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > > On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov <max.vla...@gmail.com> > wrote: > > > > > Hi, > &

Re: [sqlite] 3.7.4 possible restore bug

On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov <max.vla...@gmail.com> wrote: > Hi, > I experimented with artificial power loss (using hd box) and 3.7.4 both > library and shell didn't restore the files to the initial state. 3.6.10 > restores successfully. > This is a kind of

Re: [sqlite] Propose minor incompatible API change

tself is bad, the problem is that "minor change syndrome" will never die and life teaches me this again and again :) Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] More descriptive error

ze() or sqlite3_reset(). Occasionally I had to fix this in order for user functions to supply error strings (without this it's just simple default error). I checked two recent versions of my admin with this fix and without and they supply exactly two different errors as you described Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VFS and hot journals

Hi, I recently discovered that for vfs implemented writable formats that actually change the data of the sqlite base (encryption, compression), there should be a complex logic behind the restoration from the hot journal, at least some versions ago. For correct password handling initially I relied

Re: [sqlite] 3.7.4 possible restore bug

On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov <max.vla...@gmail.com> wrote: > Hi, > I experimented with artificial power loss (using hd box) and 3.7.4 both > library and shell didn't restore the files to the initial state. 3.6.10 > restores successfully. I don't know wh

[sqlite] 3.7.4 possible restore bug

/dbwithjournal.zip The steps to reproduce: unzip both files anywhere, open the db in sqlite shell (3.7.4), do the .quit and both the db and journal are still there. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin

Re: [sqlite] Rollback transaction if error

te3_get_autocommit() and do rollback optionally (or do it anyway with a error being not error). Anyway, I think that getting rid of transaction commands make you queries more portable since you will be able to concatenate them before wrapping into begin/end Max Vlasov _

Re: [sqlite] SQLite and Windows 95

to make statically linked 3.6.10 working on Windows 98. I'm not sure about 95, but at least you can try. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

On Tue, Dec 21, 2010 at 4:44 PM, Philip Graham Willoughby < phil.willoug...@strawberrycat.com> wrote: > On 21 Dec 2010, at 12:16, Max Vlasov wrote: > > > Simon, I read both your suggestion and the Richard's good explanation > about > > network problems. I think t

Re: [sqlite] SQLite server

raightforward way to implement this with conventional client/server dbms. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Windows 95

tFileAttributesExW is not found. If I > remove this function in the SQLite code, the same problem appears with > another function. > > Looking at the name of the function that ends with W, the questions is: have you installed Microsoft Layer for Unicode ( http://msdn.microsoft.com/en-us/g

Re: [sqlite] Windows performance problems associated with malloc()

ore'; Result: 1539, mem requests: 10157 44 ms. SELECT count(docid) FROM WikiFTS3 where Abstract Match '"of the previous"' Result: 403, mem requests: 1,627,732 3400 ms I don't know what it means, maybe that some optimization might be possible in the intern

Re: [sqlite] Windows performance problems associated with malloc()

mes needs more memory (for example when resolving 'and' or phrase search) Just curious. I'm not so familiar with sqlite internals. Can someone mention some other cases when sqlite really needs to allocate memory frequently in order to affect the overall performance? Max Vlasov

Re: [sqlite] Pragma integrity_check takes a lot of time to complete

istent way to know that a restoring operation occurred after the open? I suggested name analizing prior to optn (searching for -journal file), but what about wal mode? is there any visual indication that the job was stopped during the checkpointing? Max Vlasov ___

Re: [sqlite] Pragma integrity_check takes a lot of time to complete

oday, I could not interrupt the query while any other general query can be interrupted with progress callbacks. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] assert crash in wal

e, works like a charm. Sure, memory usage can increase in this case, but if one compares footprints of modern libraries, frameworks and so on with sqlite, even with several connections sqlite is still the winner Max Vlasov ___ sqlite-users mailing list sq

Re: [sqlite] Increase the datafile file size to limit the file fragmentation

On Sat, Dec 11, 2010 at 1:52 AM, Vander Clock Stephane < svandercl...@yahoo.fr> wrote: > yes, i agree except that the file fragmentation of the file database > cause also the file fragmentation of some other files... that is logique > when more than 2 file grow a little at a time ! and the other

Re: [sqlite] Increase the datafile file size to limit the file fragmentation

lite-users@sqlite.org/msg56083.html (I gave a linke to my post since root message doesn't contain the full discussion for some reasons). So, VACUUM from time to time is better overall than Os defrag (imho) Max Vlasov ___ sqlite-users mailing list sqlite-user

Re: [sqlite] enums

ind_in_set is good in this way since the comma-separated list can be stored as an element in a settings table. As I recall I implemented a reverse function to find_in_set, but had to come up with a name since did not find the corresponding function in mysql. Max Vlasov ___

Re: [sqlite] Concern over runtime memory growth?

m > watching the memory growth of the application grow and grow. > Lynton , please report your OS and sqlite version Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] enums

s internally by sqlite? Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database size (again)

loat-to-a-32-bit-integerto map it in your reading/writing code to save space for floats. In worst case every 8 bytes float will be 4(5)-byte integer (5 possible due to the internal packed format of sqlite), so maybe you will save 10-15 bytes per record (I a

Re: [sqlite] SQLite Documentation

On Sat, Dec 4, 2010 at 11:51 AM, Dagdamor wrote: > Hello. > > Would you like to have a downloadable version of the SQLite Documentation? > I've created one in Windows HTML Help (.chm) format. It seems to be useful, > single-file (no need to zip/unzip it), compact and easy to

[sqlite] User function instead of custom tokenizer

function). Just wondering, apart from some performance degradation working with temporary buffer, are there other disadvantages of using such approach? Thanks, Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin

Re: [sqlite] Confused about Multiple Indexes

On Fri, Nov 26, 2010 at 1:34 PM, Swithun Crowe < swit...@swithun.servebeer.com> wrote: > Hello > > MS> The second index should be: > MS> CREATE INDEX IDX2 on tx(type, name ASC); > > MS> What I had meant to ask was whether there is any benefit in having two > MS> indexes when one of the indexes is

Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

ssword, sqlite just by trying reading this pragma returned an error about something like "encrypted or bad" Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

are/hardware configurations will possibly read it just once accessing this data from the cache when your second and later indexes are created. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite uses xAccess to check the main db existence?

On Wed, Nov 24, 2010 at 6:01 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Nov 24, 2010 at 6:15 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > > Hi, > > > > I'm trying to implement read-only access to sqlite database saved in the > > Win

[sqlite] Does sqlite uses xAccess to check the main db existence?

to "unable to open" error). But also the debugging shows that xAccess never checks for the main file existence, only supplemental ones. Can I rely on that and return FALSE unconditionally? Currently it works, but it might give a new failure in the future. Thanks

Re: [sqlite] sqlite3_get_table() vs sqlite3_step

o assuming RAM wasn't an issue, > why not use sqlite3_get_table()? Why is its usage being discouraged? > > > Tito, looking at the sources... sqlite3_get_table seems to be sqlite3_exec call with a callback and sqlite3_exec is sqlite3_prepare/sqlite3_step wrapper. So, probably no special

Re: [sqlite] page cache vs OS cache

g disabling system cache for this particular file. If it's implemented in sqlite, this will give the developer the way to rely fully on the sqlite cache and save some resources. Max Vlasov, maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.

Re: [sqlite] Idea for one of the testing suites

ce, measuring the timings for one version and another. if the difference is significant, then stop, otherwise go further. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3_progress_handler and automatic rollback

On Thu, Oct 28, 2010 at 2:03 PM, Max Vlasov <max.vla...@gmail.com> wrote: > > The problem is that (tested with 3.6.10 and 3.7.2) if : > - I interrupt a query from Sqlite3_progress_handler > - I didn't invoke this error-processing rollback, > > ...sqlite after

[sqlite] Sqlite3_progress_handler and automatic rollback

ogic or missing...") in order to detect this special case in my code Thanks, Max Vlasov maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing Date Format

(d1, "-", "-0"), "-00", "-0") Max Vlasov maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL file growth concern

he first client SELECT Count(Null) FROM TestTable, TestTable if this query is long enough (5 seconds and more) we will see -wal file growing steadily until client 2 finishes. In my tests I con confirm growing to 8M, 23M Max Vlasov maxerist.net ___ sqlite-us

Re: [sqlite] programmatic way of determining fragmentation?

On Sat, Oct 23, 2010 at 2:28 AM, Dustin Sallings <dus...@spy.net> wrote: > > On Oct 22, 2010, at 15:12, Max Vlasov wrote: > > > As for your initial question, I think fragmentation evaluation is > possible > > with the help of VFS. I'd keep a total sum of of

Re: [sqlite] programmatic way of determining fragmentation?

, can someone perfom similar tests on linux? I heard it have a different cache system, i wonder whether it can show better performance in this artificial tests. Thanks in advance Max Vlasov maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite

Re: [sqlite] Full text search FTS3 of files

ower space for the same pool of articles. So there's a real chance that even if you implement something that doesn't store the texts, you will end up with a bigger index Max Vlasov maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://

Re: [sqlite] Comma-delimited field to rows (Once again)

On Sat, Oct 16, 2010 at 2:47 AM, Jim Morris wrote: > Not much help but this removes the multiplication: > SELECT B1.B + B2.B + B3.B + B4.B FROM > (SELECT 0 AS B UNION SELECT 1 AS B) AS B1, > (SELECT 0 AS B UNION SELECT 2 AS B) AS B2, > (SELECT 0 AS B UNION SELECT 4 AS B)

[sqlite] Comma-delimited field to rows (Once again)

Hi, from time to time I try to solve well-known task of making rows from a comma-delimited list and the best I could do was this: - create a user-function returning zero-based Nth item from the list (let's call it GetItemFromSet) - make a complex query like this (this one allows up to 16 elements

Re: [sqlite] How can I build the FTS3 extension as a Windows DLL?

On Thu, Oct 14, 2010 at 1:04 AM, Nate Silva wrote: > (Although FTS can be compiled into SQLite, I would like a DLL so I can > dynamically load the extension into environments where I have a pre-compiled > SQLite that doesn’t have full text search.) > > Nate, I think compile-time

Re: [sqlite] Create Read-only Database

On Sun, Oct 10, 2010 at 11:04 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 10/10/2010 11:53 AM, Max Vlasov wrote: > > Also if you're not content with this option, you can always open the file > > (just as a

Re: [sqlite] Create Read-only Database

/Win32, I'm sure a similar option should exist in Linux. In this case any attempt to write will lead to OS-level error that finally will be passes as some sqlite error to your code. Max Vlasov maxerist.net ___ sqlite-users mailing list sqlite-users@sql

Re: [sqlite] Speed up DELETE of a lot of records

On Thu, Oct 7, 2010 at 8:05 PM, Michele Pradella wrote: > Hi all, I have a question about how to speed up a DELETE statement. > I have a DB of about 3GB: the DB has about 23 millions of records. > The DB is indexed by a DateTime column (is a 64 bit integer), and >

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

On Thu, Oct 7, 2010 at 4:05 PM, Richard Hipp wrote: > On Thu, Oct 7, 2010 at 8:00 AM, Dan Kennedy wrote: > > > > > > > It seems that something changed in the query optimizer. > > > > Thanks for the report. We think this has been fixed in > > fossil

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

On Thu, Oct 7, 2010 at 2:50 PM, Shopsland gmail wrote: > Hi, > > Given this simple query with a subquery in FROM and a join with a FTS3 > table: > > SELECT news1.number, fts_news.title > FROM (SELECT number FROM news LIMIT 50) as news1, fts_news > WHERE

Re: [sqlite] unable to open database file/Disc I/O error

On Thu, Oct 7, 2010 at 1:03 PM, Serena Lien wrote: > > If you find any vista OS patches that solve this problem, please let > me know, we are looking ourselves in the hopes that microsoft has > resolved this issue.. > > Serena, How about this patch :

Re: [sqlite] Delete from A what is not in B

On Sat, Oct 2, 2010 at 4:02 PM, Igor Tandetnik wrote: > Fadhel Al-Hashim wrote: > > I have two tables that contain about 5 million records. I am trying to > write > > an SQL command to delete rows from table A with PK (x,y,z) where PK > (x,y,z) > > is not

Re: [sqlite] SQLite working with C++'s iostream

utside the current container size and after the extensive usage of a VFS-filtered query, the first sql query that actually touched xTruncate was VACUUM. Max Vlasov maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:80

Re: [sqlite] Massive performance regression in 3.7.x

On Sat, Sep 25, 2010 at 10:34 PM, Richard Hipp <d...@sqlite.org> wrote: > On Sat, Sep 25, 2010 at 10:54 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > > On Sat, Sep 25, 2010 at 6:35 PM, Richard Hipp <d...@sqlite.org> wrote: > > > > > > >

Re: [sqlite] Massive performance regression in 3.7.x

On Sat, Sep 25, 2010 at 6:35 PM, Richard Hipp wrote: > > You have a very unusual data distribution in your tables. SQLite does not > know this and so it chooses a query plan (in 3.7.1 and later) that assumes > a > different and more typical data distribution. The new query

Re: [sqlite] Performance problems and large memory size

1024 for everything except Windows. The Windows filesystem module > attempts to match the page size to the minimum write block of the > filesystem. For a typical NTFS volume, that's usually 4K. > > Jay, small correction, the default page_size on windows is still 1024 (checked it), there's

Re: [sqlite] Strange cache behavior

On Wed, Sep 22, 2010 at 7:12 PM, Pavel Ivanov wrote: > > Is it ok for cache to behave like this or some optimization is possible > to > > fix this? > > For this particular case I believe you can do some optimization by > making your own implementation of cache. > Also I

[sqlite] Strange cache behavior

Hi, playing with my admin about cache size (there was a question today related to the cache size), noticed a strange thing with cache. It's Windows, initially it was v3.6.10, but the same is for 3.7.2 I sometimes mentioned that I can track vfs requests for every select in my admin. In this case

Re: [sqlite] grouping inserts vs. one large tranaction

On Sun, Sep 19, 2010 at 8:18 PM, Erik Fears wrote: > This helps with the locking issues. > > I believe the slowness was actually related to the index on the 'name' > column. > This is a price you have to pay. The fastest scenario is when you don't have an index on a table and

Re: [sqlite] VACUUM can actually increase the DB file?

> just a few minutes ago I ran a VACUUM on a DB file and the size before > was 2089610240 and afterwards 2135066624. Is this normal? > > If you have a copy of the old db and new db, can you check the results on both databases of the following pragmas: PRAGMA freelist_count; PRAGMA

Re: [sqlite] returning smaller subset of columns: index vs trigger

On Sun, Sep 12, 2010 at 2:15 AM, Stephen Oberholtzer < oliverkloz...@gmail.com> wrote: > > Stephen, are you telling that is' smaller in any situation? When I > mentioned > > the trigger in case of fast reading of rowid/id, I thought that in this > case > > there can be a separated table with sing

Re: [sqlite] is there a program that converts sqlite database from windows-1252 to utf-8 ?

> > is there a program that converts sqlite database from windows-1252 to utf-8 > ? > It the base is not big, I think you can dump the db to sql file with the sqlite shell, convert this text file to UTF-8 (even with notepad) and feed it to new db Max

Re: [sqlite] reading directly from indices (was: returning smaller subset of columns: index vs trigger)

On Sat, Sep 11, 2010 at 10:26 PM, Drake Wilson <dr...@begriffli.ch> wrote: > Quoth Max Vlasov <max.vla...@gmail.com>, on 2010-09-11 22:16:14 +0400: > > although index at least should not be worse in any situation > > I wouldn't be quite so sure. Would there not be use

Re: [sqlite] returning smaller subset of columns: index vs trigger

On Sat, Sep 11, 2010 at 8:38 PM, Stephen Oberholtzer < oliverkloz...@gmail.com> wrote: > 2. They contain only a subset of the columns in the main table, so > they are smaller -- so reading through the entire index is faster than > reading through the entire table. > > Stephen, are you telling

Re: [sqlite] returning smaller subset of columns: index vs trigger

On Sat, Sep 11, 2010 at 8:54 PM, Simon Slavin wrote: > > On 11 Sep 2010, at 5:38pm, Stephen Oberholtzer wrote: > > > It would be an interesting attempt to try efficiently patching SQLite > > to recognize this situation and read data directly out of the index. > > But I've

Re: [sqlite] returning smaller subset of columns: index vs trigger

On Sat, Sep 11, 2010 at 2:05 PM, Cory Nelson <phro...@gmail.com> wrote: > On Sat, Sep 11, 2010 at 12:36 AM, Max Vlasov <max.vla...@gmail.com> wrote: > > Hi, > > > > What other observations can be made about these two types of solutions > that > > I may

[sqlite] returning smaller subset of columns: index vs trigger

Hi, sometimes it is necessary to return only part of columns (maybe even only id/rowid) and sure SELECT Col1, Col2 works. But as the real tests some time ago showed, sqlite actually reads all the data from db (sure with an exception if record is bigger than a page). One of the approaches to

Re: [sqlite] memory wasted shm mapped file (3.7.2)

On Fri, Sep 10, 2010 at 6:32 PM, Shane Harrelson wrote: > I tried to reproduce this, and could not. > > There are some questions inline below.Additionally, I want to > verify that you've tried this with a version of SQLite containing the > previously linked fix. > > Shane,

Re: [sqlite] memory wasted shm mapped file (3.7.2)

On Fri, Sep 10, 2010 at 5:07 PM, Michele Pradella < michele.prade...@selea.com> wrote: > > what I worry about is that the Addressed space of > sqlite (during an operation of DELETE or UPDATE a lot of data in WAL > mode use case described before) could grow up till 2GB. This cause the >

Re: [sqlite] memory wasted shm mapped file (3.7.2)

On Fri, Sep 10, 2010 at 3:52 PM, Michele Pradella < michele.prade...@selea.com> wrote: > After some tests, with the new sqlite3.c source, seams that the > behavior is better than before. So I see the -shm and -wal file grow up, > and in VMMap I can see the Mapped File of -shm growing up, but not

Re: [sqlite] memory wasted shm mapped file (3.7.2)

On Fri, Sep 10, 2010 at 12:53 PM, Dan Kennedy wrote: > > The bug is fixed by Shane's patch linked earlier in the thread. With > the patch, an 11MB -shm file is mapped into memory in around 350 chunks > with an average size of 48KB. Total address space used is around >

Re: [sqlite] memory wasted shm mapped file (3.7.2)

On Fri, Sep 10, 2010 at 11:29 AM, Michele Pradella < michele.prade...@selea.com> wrote: > I forgot to tell you something: in my situation the -shm file is > always small in size (about 6-7MB) even when I got 2GB of Virtual Bytes. > But it seams that is mapped too much times. > I see something

Re: [sqlite] memory wasted shm mapped file (3.7.2)

> But as a side effect I got not expected result in other area, when I tried > to append 1,500,000 records to this 1.7G file having 5G of free space on the > disk, I got the error a user reported recently about win7 64bit, "Disk I/O > error".

Re: [sqlite] memory wasted shm mapped file (3.7.2)

On Thu, Sep 9, 2010 at 7:16 PM, Dan Kennedy wrote: > > > > > Dan, don't know whether it is related, but I detected memory leak in > > 3.7.2 > > related to handle count increasing. > > I think there will be one open handle for each 32KB of > shared-memory space in use. Or

Re: [sqlite] memory wasted shm mapped file (3.7.2)

On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy wrote: > > On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote: > > > Hi, do you have some news about the wasted memory? have you found the > > reason for the windows backend? > > Fixed here: > >

Re: [sqlite] Detecting storage class from C

On Thu, Sep 9, 2010 at 1:01 AM, Andrew Wood wrote: > Because I'm writing glue code between the SQLite API and a higher level > library which provides a standard API across several DBMSs. > > Have in mind that storage class is not a 'type', it's more like an effective way to

Re: [sqlite] Trigger Questions

On Fri, Sep 3, 2010 at 12:29 AM, Igor Tandetnik wrote: > > The correct syntax is > > > > UPDATE sites SET createTime = DATETIME('NOW'), updateTime = > > DATETIME('NOW') > > WHERE rowid = new.rowid; > > > It's interesting, I once did a similar error with UPDATE (typing

Re: [sqlite] upstreaming Chromium patches for file handle passing support

> I wonder whether it would be possible to extend the VFS in a way that would > make our use case possible (transferring a file handle over process > boundary). Please note that we do it on all platforms. On POSIX we pass an > integer file descriptor, and on Windows we pass a HANDLE. > > I used

Re: [sqlite] Feature request: copying vacuum

> I agree with Jay - while it is tempting to have SQLite bite off > optimizing this kind of thing, it's pretty far out of scope. Next > we'll be talking about running SQLite on raw partitions! > Scott, thought about it, ironically sqlite vfs is flexible enough to implement raw partition sqlite

Re: [sqlite] Feature request: copying vacuum

> This yields two benefits: > >A less fragmented db > >~50% vacuum speedup since the data is only copied once > > Currently we can copy the data to a new file, but it is a pretty > invasive change to swap all of the current sqlite connections to the new > file. Things like prepared

Re: [sqlite] next value in sequence

On Tue, Aug 31, 2010 at 9:07 AM, Eric Smith wrote: > > You probably don't want 'SELECT max(foo_id)+1' because I think that does > a full table scan. > > you're right, but slightly modified version SELECT (SELECT max(foo_id) FROM MxVft_FTIndex)+1 does not Max

Re: [sqlite] Is there a design doc for the virtual machine re-write?

or example when the page is already in the cache), this might affect the performance in general. Also you can try to compare EXPLAIN QUERY result from a version prior to 3.5.5 to some of the current one for the same sql query. I wanted to do this myself, but seems like can not access sqlite download page

Re: [sqlite] Implementation check request

Simon, I don't know whether my posts have a delay with delivery (replied several hours ago to the discussion you mentioned), but actually I tested 100 rows before and after with a similar query (ORDER BY LIMIT) and it definitely shows that sqlite saves time and doesn't go further. Do you have

Re: [sqlite] implementing a row cache

RDER BY Word DESC LIMIT 100) UNION SELECT * FROM (SELECT * FROM MxVft_FTWords WHERE Word >= 'TAKE' ORDER BY Word LIMIT 100) ORDER BY Word I tried it on a real table containing about 1,3M records, EXPLAIN QUERY PLAN successfully reported about index usage, also vfs read was about 8 kilobytes, so no

Re: [sqlite] partial index?

> Maybe it'll be clearer if I describe my (quite simple) use case. Our > app is caching what are basically csv files. Hundreds of files, about 2m > records per file. Sometimes we want to delete all the cache rows for one > of the files. We know ahead of time which file it will be -- let's say

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

> In my case (which is certainly not typical), a (several GB) large > database is built up in several batches, one table at a time, while in > parallel many intermediate files on the disk are created. This resulted > in a very fragmented database file. After that, also several times, the > data is

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

Whether SQLITE_FCNTL_CHUNKS_SIZE is useful is a different discussion. > I myself would love to see features exposed via pragmas whenever > possible, for the simple reason that I don't use the C API and can't > make use of the features otherwise. I would assume that since the > SQLite developers

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

On Fri, Aug 20, 2010 at 12:27 AM, Taras Glek wrote: > Hi, > I really appreciate that sqlite got this feature to reduce > fragmentation, but why not expose this as a pragma? > Taras, I think that you're overestimating the feature. On the OS level it won't matter how far the

<    1   2   3   4   5   >