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
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 danielk1...@gmail.com 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 ser...@gmail.com 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
[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] 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
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] round documentation
Igor Tandetnik wrote: Matt Young youngsan...@gmail.com 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] round documentation
Bingo, I live and learn On 5/28/10, Igor Tandetnik itandet...@mvps.org wrote: Igor Tandetnik wrote: Matt Young youngsan...@gmail.com 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] 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 max.vla...@gmail.com Subject: Re: [sqlite] Aggregate and join query very slow To: General Discussion of SQLite Database sqlite-users@sqlite.org Message-ID: aanlktinqmk9csyaguoyllo2okpmsyq3di-tzbuahw...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 On Thu, May 27, 2010 at 3:07 PM, Michael Ash m...@econs.umass.edu 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 media3 ... 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] 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 max.vla...@gmail.com Subject: Re: [sqlite] Aggregate and join query very slow To: General Discussion of SQLite Database sqlite-users@sqlite.org Message-ID: aanlktinqmk9csyaguoyllo2okpmsyq3di-tzbuahw...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 On Thu, May 27, 2010 at 3:07 PM, Michael Ash m...@econs.umass.edu 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 media3 ... 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
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 media3 AND year=2006 GROUP BY facilitynumber Regards Tim Romano On Fri, May 28, 2010 at 10:57 AM, Michael Ash m...@econs.umass.edu 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 max.vla...@gmail.com Subject: Re: [sqlite] Aggregate and join query very slow To: General Discussion of SQLite Database sqlite-users@sqlite.org Message-ID: aanlktinqmk9csyaguoyllo2okpmsyq3di-tzbuahw...@mail.gmail.com Content-Type: text/plain; charset=ISO-8859-1 On Thu, May 27, 2010 at 3:07 PM, Michael Ash m...@econs.umass.edu 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 media3 ... 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
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
[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
[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...@nonymoususername =...@nonymoustoken=1CFC31A1D8191C84locale=depath=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
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
Re: [sqlite] [ODBC] - possible Bug
Simon Slavin slav...@... 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
[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] Syntax error on CREATE TRIGGER IF NOT EXISTS
Jim Terman jter...@tivo.com 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
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 jter...@tivo.com 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
[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] 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
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: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
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 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] 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
