Re: [sqlite] SQLITE_OMIT_WSD (3.6.23.1)
Hi Dan, It doesn't matter that it will never be written to. Since the variable is a non-const static it will get mapped into the WSD portion of memory. There are actually a few other global static variables that are getting placed in the WSD section of memory. Here is a list of non-const statics that exist even with SQLITE_OMIT_WSD turned on: (taken from a .map file) sqlite3_temp_directory 0x00040d24 Data 4 sqlite3.o(.data) sqlite3PendingByte 0x00040d20 Data 4 sqlite3.o(.data) ata...@openstattable_0 0x00040d28 Data 8 sqlite3.o(.data) MemJournalMethods0x00040d30 Data 52 sqlite3.o(.data) smu...@sqlite3defaultmutex_0 0x00040d64 Data 36 sqlite3.o(.data) defaultmeth...@sqlite3pcachesetdefault_0 0x00040d88 Data 44 sqlite3.o(.data) detach_f...@sqlite3detach_0 0x00040db4 Data 32 sqlite3.o(.data) attach_f...@sqlite3attach_0 0x00040dd4 Data 32 sqlite3.o(.data) a...@sqlite3savepoint_00x00040df4 Data 12 sqlite3.o(.data) -Albert Dan Kennedy-4 wrote: > > > On May 28, 2010, at 1:11 AM, Kim, Albert wrote: > >> Hi, >> >> We are compiling sqlite 3.6.23.1 with the SQLITE_OMIT_WSD compile >> time flag turned on. We are using the amalgamation. We found that >> this didn't completely eliminate the writable static data in our >> binary, and some investigation led me to this static variable: >> >> SQLITE_PRIVATE VdbeOp *sqlite3VdbeGetOp(Vdbe *p, int addr){ >> static VdbeOp dummy; >> >> Is this is a known issue? > > That variable will never be written to. A pointer to it > may be returned to the caller, but the caller will only > read, never modify the contents of the structure. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/SQLITE_OMIT_WSD-%283.6.23.1%29-tp28705310p28712877.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a "rowid" from a view
On 29 May 2010, at 2:30am, Jim Terman wrote: > I want the 'rowid' of the view. In other words I'd like to now what row > John Smith is in the view. I can do it with a view that is ordered by > using count(*), but I wondered if there was a better way. I believe that a view is just a window into an existing table: a way of saving the terms of a 'SELECT' statement so you don't have to keep restating it. Therefore it has no data of its own, including no rowids of its own. Try doing 'SELECT rowid FROM phonebook_order' and see whether you get any result at all, and whether the numbers are in order. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a "rowid" from a view
I want the 'rowid' of the view. In other words I'd like to now what row John Smith is in the view. I can do it with a view that is ordered by using count(*), but I wondered if there was a better way. Simon Slavin wrote: > Or are you asking about the 'rowid' of the view ? I don't know whether > that's a valid idea. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a "rowid" from a view
On 29 May 2010, at 1:44am, Jim Terman wrote: > That's good to now about the automatic rowid. I can certainly create a > new column in phonebook that shows the row number with id INTEGER > PRIMARY KEY AUTOINCREMENT. However, I'd really would like to do the same > thing in a view. When you do your CREATE command, include the 'id' column of the phonebook with your other columns: CREATE VIEW phonebook_order AS SELECT id, first_name, last_name, phone_number FROM phonebook ORDER BY last_name, first_name; Or are you asking about the 'rowid' of the view ? I don't know whether that's a valid idea. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a "rowid" from a view
That's good to now about the automatic rowid. I can certainly create a new column in phonebook that shows the row number with id INTEGER PRIMARY KEY AUTOINCREMENT. However, I'd really would like to do the same thing in a view. Simon Slavin wrote: > On 29 May 2010, at 1:19am, Jim Terman wrote: > > >> CREATE VIEW phonebook_order AS SELECT first_name, last_name, >> phone_number FROM phonebook ORDER BY last_name, first_name; >> >> Now on the table phonebook I can do a query: >> >> SELECT rowid FROM phonebook where last_name = "Smith" and first_name = >> "John"; >> >> which will gave me the row number of John Smith. >> >> How do I do this for the view phonebook_order? >> > > Never depend on a rowid field you do not create yourself. Although you know > SQLite creates them internally do not depend on them. So equip phonebook > with an explicit INTEGER PRIMARY KEY AUTOINCREMENT field called, perhaps, id > and use that where you want it. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a "rowid" from a view
On 29 May 2010, at 1:19am, Jim Terman wrote: > CREATE VIEW phonebook_order AS SELECT first_name, last_name, > phone_number FROM phonebook ORDER BY last_name, first_name; > > Now on the table phonebook I can do a query: > > SELECT rowid FROM phonebook where last_name = "Smith" and first_name = > "John"; > > which will gave me the row number of John Smith. > > How do I do this for the view phonebook_order? Never depend on a rowid field you do not create yourself. Although you know SQLite creates them internally do not depend on them. So equip phonebook with an explicit INTEGER PRIMARY KEY AUTOINCREMENT field called, perhaps, id and use that where you want it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting a "rowid" from a view
Say I have a table of phone numbers CREATE TABLE phonebook (first_name TEXT, last_name TEXT, phone_number TEXT); I want to sort this by name, so I create a view CREATE VIEW phonebook_order AS SELECT first_name, last_name, phone_number FROM phonebook ORDER BY last_name, first_name; Now on the table phonebook I can do a query: SELECT rowid FROM phonebook where last_name = "Smith" and first_name = "John"; which will gave me the row number of John Smith. How do I do this for the view phonebook_order? Nearest I can determine would be to run the command SELECT COUNT(*) from phonebook_order WHERE last_name <= "Smith" AND first_name <= "John"; Is there an easier way? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax error on CREATE TRIGGER IF NOT EXISTS
sqlite 3.3.6. I didn't realize it was so old. Thanks. Igor Tandetnik wrote: > Jim Terman wrote: > >> It looks like to me that CREATE TRIGGER IF NOT EXISTS is recognized by >> sqlite, but I get the following from the command line: >> >> sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, value TEXT, >> timestamp DATE);sqlite> CREATE TRIGGER IF NOT EXISTS foo_log AFTER >> INSERT ON foo BEGIN INSERT INTO foo (timestamp) VALUES >> (DATETIME('NOW')); END; >> SQL error: near "NOT": syntax error >> sqlite> CREATE TRIGGER foo_log AFTER INSERT ON foo BEGIN INSERT INTO >> foo (timestamp) VALUES (DATETIME('NOW')); END; >> sqlite> >> > > My guess is, you are running an older version of SQLite that doesn't yet > support this syntax. What does this statement report: > > select sqlite_version(); > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax error on CREATE TRIGGER IF NOT EXISTS
Jim Terman wrote: > It looks like to me that CREATE TRIGGER IF NOT EXISTS is recognized by > sqlite, but I get the following from the command line: > > sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, value TEXT, > timestamp DATE);sqlite> CREATE TRIGGER IF NOT EXISTS foo_log AFTER > INSERT ON foo BEGIN INSERT INTO foo (timestamp) VALUES > (DATETIME('NOW')); END; > SQL error: near "NOT": syntax error > sqlite> CREATE TRIGGER foo_log AFTER INSERT ON foo BEGIN INSERT INTO > foo (timestamp) VALUES (DATETIME('NOW')); END; > sqlite> My guess is, you are running an older version of SQLite that doesn't yet support this syntax. What does this statement report: select sqlite_version(); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Syntax error on CREATE TRIGGER IF NOT EXISTS
It looks like to me that CREATE TRIGGER IF NOT EXISTS is recognized by sqlite, but I get the following from the command line: sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, value TEXT, timestamp DATE);sqlite> CREATE TRIGGER IF NOT EXISTS foo_log AFTER INSERT ON foo BEGIN INSERT INTO foo (timestamp) VALUES (DATETIME('NOW')); END; SQL error: near "NOT": syntax error sqlite> CREATE TRIGGER foo_log AFTER INSERT ON foo BEGIN INSERT INTO foo (timestamp) VALUES (DATETIME('NOW')); END; sqlite> Is this expected? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [ODBC] - possible Bug
Simon Slavin writes: [...] > > Neither mainforms nor subforms are part of SQLite. Your problem is either with ODBC or OpenOffice and > you'll probably get better advice elsewhere. [...] But Mr. Werner is part of the team (http://www.sqlite.org/crew.html) and so a working ODBC-Driver seems to be of common interest. greetings Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [ODBC] - possible Bug
On 28 May 2010, at 6:04pm, Oliver Peters wrote: > Despite using the very simple way to create the form (via assistant) I can't > connect mainform and subform. Neither mainforms nor subforms are part of SQLite. Your problem is either with ODBC or OpenOffice and you'll probably get better advice elsewhere. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ODBC] - possible Bug
Hello, configuration - OS- WinXP 32 sqlite- 3.6.23.1 odbc - 0.86 (http://www.ch-werner.de/sqliteodbc/) frontend - OpenOffice.org (3.2. and 3.2.1 RC2 tested) target -- In "OpenOffice Base" I need a form where a mainform controls the appearance of records in a subform. As a backend I use "sqlite" and to connect frontend and backend I use the ODBC-Driver created by Mr. Werner. problem --- Despite using the very simple way to create the form (via assistant) I can't connect mainform and subform. I tried the same with OpenOffice internal Database HSQLDB and had no problem to get the wanted result. wish anybody there who can confirm the problem? anybody there with a solution (I am not a programmer just a heavy user!)? for testing purposes you can download the following files from here --- please make 1 Line as URL --- https://smartdrive.web.de/qxclient/?userna...@nonymous&username =...@nonymous&token=1CFC31A1D8191C84&locale=de&path=sqliteodbc%20von%20 oliver.pet<=undefined - as 1 .zip (just double click on testfiles.zip) not working solution create_and_feed.sql test.db3 (result of < sqlite3 -bail test.db3 create_and_feed.sql > sqlite_odbc.odb (backend with created form but you've to install the odbc-Driver and create a DSN to use it) working solution hsqldb_noodbc.odb (you can see what I want) greetings Oliver Peters ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] No conflict clause in foreign key clause?
I notice that the foreign key clause (http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause) does not include a conflict clause (http://www.sqlite.org/syntaxdiagrams.html#conflict-clause). I always specify "ON CONFLICT ROLLBACK" with my other constraints, but what will happen when a foreign key violation occurs within a transaction? Will we see the default "ABORT" behavior as with other constraints? How do I get it to rollback the entire transaction in such a case? Thanks, -Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aggregate and join query very slow
On 28 May 2010, at 3:57pm, Michael Ash wrote: > Per suggestions, I indexed year and media on the big table. So I now > have separate indexes for the key variable (releasenumber) and for > year and for media.Would it make more sense to have a single > index for all three, thus: > > CREATE UNIQUE INDEX r ON release_cl (year,media,releasenumber); Correct. Each SELECT command uses zero or one index. It's pointless to create individual indexes for each column in your table. Look at the columns, ORDER BY and WHERE of your SELECT query and make up one index which is ideal for that query. For the ideal index in SQLite, you make the index up of first, the columns mentioned in the WHERE clause then, the columns mentioned in the ORDER BY clause finally any other columns you want retrieved. For each purpose you have to choose your preferred cutoff: it's usually not worth doing the last section of the above, and sometimes not even worth doing the ORDER BY section. The more indexes, and the longer they are, the longer each WRITE operation takes for that table. You could end up with a table which might be fast to search but need a very long time for each INSERT. Also, increasing the length of each index will increase the database filesize, and therefore cause operations which change the database file to take longer. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aggregate and join query very slow
How many distinct media-types are there? How many distinct facilities do you have? How many rows are typically returned by your FacilityScore subquery? SELECT facilitynumber,SUM(score_rev) AS score FROM release_cl WHERE media<3 AND year=2006 GROUP BY facilitynumber Regards Tim Romano On Fri, May 28, 2010 at 10:57 AM, Michael Ash wrote: > Thank you very much. Both replies pointed to indexes. So I changed > the indexes and markedly improved performance from 12 seconds to about > 1.5 seconds for the faster variant (using nested SELECTS) and about > 2.2 second for the slower variant. > > Per suggestions, I indexed year and media on the big table. So I now > have separate indexes for the key variable (releasenumber) and for > year and for media.Would it make more sense to have a single > index for all three, thus: > > CREATE UNIQUE INDEX r ON release_cl (year,media,releasenumber); > > I remain concerned that I am not using indexes as well as possible. > The query still takes many times longer than does the same query on > MySQL. > > Thank you very much again for the helpful responses. > > > Best, > Michael Ash > > > > > > 3. Re: Aggregate and join query very slow (Max Vlasov) > > Message: 3 > > Date: Thu, 27 May 2010 17:26:10 +0400 > > From: Max Vlasov > > Subject: Re: [sqlite] Aggregate and join query very slow > > To: General Discussion of SQLite Database > > Message-ID: > > > > Content-Type: text/plain; charset=ISO-8859-1 > > > > On Thu, May 27, 2010 at 3:07 PM, Michael Ash > wrote: > > > >> ...These are large tables (52,355 records in facility and 4,085,137 in > >> release_cl). > >> > >> ... > >> sqlite> explain query plan > >> ...> SELECT name,score > >> ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score > >> ...> FROM release_cl > >> ...> WHERE media<3 > >> ...> AND year=2006 > >> ...> GROUP BY facilitynumber) r > >> > > > > > > Michael, from what I see, if your release_cl table is not properly > indexed > > to be quickly aggregated (media and year field), this will lead to full > > table reading (so all the data of your 4M records). If it's properly > > indexed, and the result number of records of this select is big, consider > > adding non-indexed fields to this (or brand-new) index since otherwise > > sqlite quickly finds records with this index, but have to look up main > data > > tree to retrieve other fields. > > > > Max, > > maxerist.net > ___ > 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] Aggregate and join query very slow
You also need to increase your cache size to match the mysql performance pragma cache 10; Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Michael Ash Sent: Fri 5/28/2010 9:57 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Aggregate and join query very slow Thank you very much. Both replies pointed to indexes. So I changed the indexes and markedly improved performance from 12 seconds to about 1.5 seconds for the faster variant (using nested SELECTS) and about 2.2 second for the slower variant. Per suggestions, I indexed year and media on the big table. So I now have separate indexes for the key variable (releasenumber) and for year and for media.Would it make more sense to have a single index for all three, thus: CREATE UNIQUE INDEX r ON release_cl (year,media,releasenumber); I remain concerned that I am not using indexes as well as possible. The query still takes many times longer than does the same query on MySQL. Thank you very much again for the helpful responses. Best, Michael Ash > 3. Re: Aggregate and join query very slow (Max Vlasov) > Message: 3 > Date: Thu, 27 May 2010 17:26:10 +0400 > From: Max Vlasov > Subject: Re: [sqlite] Aggregate and join query very slow > To: General Discussion of SQLite Database > Message-ID: > > Content-Type: text/plain; charset=ISO-8859-1 > > On Thu, May 27, 2010 at 3:07 PM, Michael Ash wrote: > >> ...These are large tables (52,355 records in facility and 4,085,137 in >> release_cl). >> >> ... >> sqlite> explain query plan >> ...> SELECT name,score >> ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score >> ...> FROM release_cl >> ...> WHERE media<3 >> ...> AND year=2006 >> ...> GROUP BY facilitynumber) r >> > > > Michael, from what I see, if your release_cl table is not properly indexed > to be quickly aggregated (media and year field), this will lead to full > table reading (so all the data of your 4M records). If it's properly > indexed, and the result number of records of this select is big, consider > adding non-indexed fields to this (or brand-new) index since otherwise > sqlite quickly finds records with this index, but have to look up main data > tree to retrieve other fields. > > Max, > maxerist.net ___ 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] Aggregate and join query very slow
Thank you very much. Both replies pointed to indexes. So I changed the indexes and markedly improved performance from 12 seconds to about 1.5 seconds for the faster variant (using nested SELECTS) and about 2.2 second for the slower variant. Per suggestions, I indexed year and media on the big table. So I now have separate indexes for the key variable (releasenumber) and for year and for media.Would it make more sense to have a single index for all three, thus: CREATE UNIQUE INDEX r ON release_cl (year,media,releasenumber); I remain concerned that I am not using indexes as well as possible. The query still takes many times longer than does the same query on MySQL. Thank you very much again for the helpful responses. Best, Michael Ash > 3. Re: Aggregate and join query very slow (Max Vlasov) > Message: 3 > Date: Thu, 27 May 2010 17:26:10 +0400 > From: Max Vlasov > Subject: Re: [sqlite] Aggregate and join query very slow > To: General Discussion of SQLite Database > Message-ID: > > Content-Type: text/plain; charset=ISO-8859-1 > > On Thu, May 27, 2010 at 3:07 PM, Michael Ash wrote: > >> ...These are large tables (52,355 records in facility and 4,085,137 in >> release_cl). >> >> ... >> sqlite> explain query plan >> ...> SELECT name,score >> ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score >> ...> FROM release_cl >> ...> WHERE media<3 >> ...> AND year=2006 >> ...> GROUP BY facilitynumber) r >> > > > Michael, from what I see, if your release_cl table is not properly indexed > to be quickly aggregated (media and year field), this will lead to full > table reading (so all the data of your 4M records). If it's properly > indexed, and the result number of records of this select is big, consider > adding non-indexed fields to this (or brand-new) index since otherwise > sqlite quickly finds records with this index, but have to look up main data > tree to retrieve other fields. > > Max, > maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] round documentation
Bingo, I live and learn On 5/28/10, Igor Tandetnik wrote: > Igor Tandetnik wrote: >> Matt Young wrote: >>> I second that documentation confusion. There is no truncate to >>> integer, though I wish it would. >> >> Somewhat off-topic, but if you want truncation, this would do it: round(x >> - 0.5) . > > Actually, cast(x as integer) works better. It follows the usual C rules, > like (int)x for x declared as double. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] round documentation
Igor Tandetnik wrote: > Matt Young wrote: >> I second that documentation confusion. There is no truncate to >> integer, though I wish it would. > > Somewhat off-topic, but if you want truncation, this would do it: round(x - > 0.5) . Actually, cast(x as integer) works better. It follows the usual C rules, like (int)x for x declared as double. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using cache stats to balance transaction size for optimal performance
On May 28, 2010, at 5:42 PM, Pavel Ivanov wrote: >> It will retry the EXCLUSIVE lock each time a page that is not >> in the cache is required by SQLite (a "cache-miss"). > > If SQLite doesn't require to read any pages but only adds new pages to > the file does it count as cache-miss? Yes. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_WSD (3.6.23.1)
On May 28, 2010, at 1:11 AM, Kim, Albert wrote: > Hi, > > We are compiling sqlite 3.6.23.1 with the SQLITE_OMIT_WSD compile > time flag turned on. We are using the amalgamation. We found that > this didn't completely eliminate the writable static data in our > binary, and some investigation led me to this static variable: > > SQLITE_PRIVATE VdbeOp *sqlite3VdbeGetOp(Vdbe *p, int addr){ > static VdbeOp dummy; > > Is this is a known issue? That variable will never be written to. A pointer to it may be returned to the caller, but the caller will only read, never modify the contents of the structure. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OMIT_WSD (3.6.23.1)
Hi, We are compiling sqlite 3.6.23.1 with the SQLITE_OMIT_WSD compile time flag turned on. We are using the amalgamation. We found that this didn't completely eliminate the writable static data in our binary, and some investigation led me to this static variable: SQLITE_PRIVATE VdbeOp *sqlite3VdbeGetOp(Vdbe *p, int addr){ static VdbeOp dummy; Is this is a known issue? Thanks! -Albert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using cache stats to balance transaction size for optimal performance
> It will retry the EXCLUSIVE lock each time a page that is not > in the cache is required by SQLite (a "cache-miss"). If SQLite doesn't require to read any pages but only adds new pages to the file does it count as cache-miss? Pavel On Fri, May 28, 2010 at 1:41 AM, Dan Kennedy wrote: > > On May 27, 2010, at 8:07 PM, Pavel Ivanov wrote: > >>> That's true, except for the case when exclusive lock fails; at >>> least that's >>> what Dan Kennedy says to my question from a couple of years ago: >>> http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357 >> >> It's interesting feature, I didn't know about that. It would be also >> useful to know will acquiring of EXCLUSIVE lock be re-attempted after >> failure at some point during this transaction with indefinite cache >> growing? If yes then how often SQLite will retry? > > It will retry the EXCLUSIVE lock each time a page that is not > in the cache is required by SQLite (a "cache-miss"). > > Since at this point SQLite has a PENDING lock on the database, > no new readers can connect. So once all existing readers have > finished, the writer will be able to upgrade to an EXCLUSIVE > and free memory by writing dirty pages to the db file. In other > words, the cache will not continue growing after the readers > have unlocked the database. > > Dan. > > > > >> On Thu, May 27, 2010 at 8:48 AM, Igor Sereda wrote: >>> >>> Pavel, >>> >>> Thanks for the reply! I was afraid using pcache would be the only >>> way :) >>> >>> As for this: >>> >>> Pavel Ivanov-2 wrote: No way. Cache won't ever grow just because you have large transaction. It will only be spilled to disk and exclusive lock will be taken but never trigger unbound growth. >>> >>> That's true, except for the case when exclusive lock fails; at >>> least that's >>> what Dan Kennedy says to my question from a couple of years ago: >>> http://old.nabble.com/changes-in-cache-spill-locking-since-3.5.9--td20564357.html#a20564357 >>> >>> I guess that's an undocumented feature. >>> >>> Cheers, >>> Igor >>> -- >>> View this message in context: >>> http://old.nabble.com/Using-cache-stats-to-balance-transaction-size-for-optimal-performance-tp28690967p28693594.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > 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] [mlist] Re: round documentation
Matt Young schrieb: > I second that documentation confusion. There is no truncate to > integer, though I wish it would. > Try this, SELECT CAST(4.5 AS INTEGER), CAST(ROUND(4.5, 0) AS INTEGER) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users