Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net)
Yes, you're free to consider this a peculiarity of the ADO driver. It might be easily fixable. But it's not a bug. I respectfully disagree with that. GetName() is supposed to return a _Column_ Name, not a _Table_ Name. Even more, it is supposed to return a Value to identify a Column, so in case of ambiguities it should return a Name in the Form Tablename.Columnname. So I'd call it a Bug. Gerd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net)
Simon Slavin wrote: you're free to consider this a peculiarity of the ADO driver. It's a peculiarity of SQLite itself (introduced in 5526e0aa3c). It might be easily fixable. But it's not a bug. The comment Dequote column names generated by the query flattener shows that combined table/column names were not considered. While the result is formally allowed by the documentation, this is not behaviour that was intended. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS full-text query vs. query by rowid
Milan Kříž wrote: 3) A query which should use a linear scan according to the SQLite documentation (http://www.sqlite.org/fts3.html#section_1_4) SELECT docId FROM ftsTable WHERE docId BETWEEN 20 AND 23 - gets a following query plan: SCAN TABLE ftsTable VIRTUAL TABLE INDEX 393216: - the documentation does not say a word about another indexes on an FTS table, so where is the index 393216 come from? This is an undocumented optimization. In recent versions, FTS also optimizes docid searches with less/greater than operators. (The index number is an implementation detail.) 4) The I have a query with both 'match ?' sub-clause and 'rowid=?' sub-clause. It is not clear to me which variant will be used. But according to definition of Full-text query it should use full-text query at first. And then? Will it use index to rowid after full-text query is performed? SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22, 23) - anyway from the query plan it seems that no full-text query is performed at all - or how to interpret it?: SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1: EXECUTE LIST SUBQUERY 1 INDEX 1 is the full-text search. The rowid values of the returned rows are then compared (by SQLite, outside of FTS) against the list. Could you please give me a clue how to guess whether a complex FTS query will use a full-text index and which one it will use? There is only one full-text index per table. The FTS module implements a search/lookup iff the EXPLAIN QUERY PLAN output shows VIRTUAL TABLE INDEX. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS full-text query vs. query by rowid
Clemens Ladisch wrote: 4) The I have a query with both 'match ?' sub-clause and 'rowid=?' sub-clause. It is not clear to me which variant will be used. But according to definition of Full-text query it should use full-text query at first. And then? Will it use index to rowid after full-text query is performed? SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22, 23) - anyway from the query plan it seems that no full-text query is performed at all - or how to interpret it?: SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1: EXECUTE LIST SUBQUERY 1 INDEX 1 is the full-text search. The rowid values of the returned rows are then compared (by SQLite, outside of FTS) against the list. Ok, it would be what I expect. But according to my first two queries 1) and 2), it looks like a full-text index is the *index 18*. 1) A*full-text query* SELECT docId FROM ftsTableWHERE ftsTable MATCH 'a*' - gets a following query plan: SCAN TABLE ftsTable VIRTUAL TABLE*INDEX**18*: 2) A*query by rowid* SELECT docId FROM ftsTable WHERE docid = 10 - gets a following query plan: SCAN TABLE ftsTable VIRTUAL TABLE*INDEX 1*: So maybe the query plan shows a wrong number? Or is the index number unrelated to a full-text index? I thought that INDEX 1 is an index to rowid, but maybe index numbers are somehow fuzzy? :-). Thanks, Milan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS full-text query vs. query by rowid
Milan Kříž wrote: Clemens Ladisch wrote: INDEX 1 is the full-text search. Sorry, that's wrong. The idxNum value is determined as follows: (see fts3Int.h) /* ** The Fts3Cursor.eSearch member is always set to one of the following. ** Actualy, Fts3Cursor.eSearch can be greater than or equal to ** FTS3_FULLTEXT_SEARCH. If so, then Fts3Cursor.eSearch - 2 is the index ** of the column to be searched. For example, in ** ** CREATE VIRTUAL TABLE ex1 USING fts3(a,b,c,d); ** SELECT docid FROM ex1 WHERE b MATCH 'one two three'; ** ** Because the LHS of the MATCH operator is 2nd column b, ** Fts3Cursor.eSearch will be set to FTS3_FULLTEXT_SEARCH+1. (+0 for a, ** +1 for b, +2 for c, +3 for d.) If the LHS of MATCH were ex1 ** indicating that all columns should be searched, ** then eSearch would be set to FTS3_FULLTEXT_SEARCH+4. */ #define FTS3_FULLSCAN_SEARCH 0/* Linear scan of %_content table */ #define FTS3_DOCID_SEARCH1/* Lookup by rowid on %_content table */ #define FTS3_FULLTEXT_SEARCH 2/* Full-text index search */ /* ** The lower 16-bits of the sqlite3_index_info.idxNum value set by ** the xBestIndex() method contains the Fts3Cursor.eSearch value described ** above. The upper 16-bits contain a combination of the following ** bits, used to describe extra constraints on full-text searches. */ #define FTS3_HAVE_LANGID0x0001 /* languageid=? */ #define FTS3_HAVE_DOCID_GE 0x0002 /* docid=? */ #define FTS3_HAVE_DOCID_LE 0x0004 /* docid=? */ Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS full-text query vs. query by rowid
Clemens Ladisch wrote: Milan Kříž wrote: Clemens Ladisch wrote: INDEX 1 is the full-text search. Sorry, that's wrong. So does it mean that the full-text search is not performed for the following query at all? And that only the docId index is used to get entries in the IN sub-clause and then a linear scan with a comparison to 'a*' is done? query: SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22, 23) query plan: SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1: EXECUTE LIST SUBQUERY 1 Is it possible to force SQLite to use the full-text search instead of the rowid search? I can think only about something like this: select docId from ( select docId from ftsTable where ftsTable match 'a*' ) where docId in (21, 22, 23, 24) query plan: SCAN TABLE nameFtsTable VIRTUAL TABLE INDEX 18: EXECUTE LIST SUBQUERY 1 It looks much better. But what does the 'EXECUTE LIST SUBQUERY 1' mean? You wrote that returned values are compared by SQLite (outside of FTS). But does it use some index (rowid index) or is it impossible for SQLite to use an index on the same table (even if the first one - full-text index - was used in a subquery)? Of course, in my real use-case I have much more complex docId condition and I have a lot of entries in an FTS table (about million entries), so I would like the full-text search to prune the results first and then filter results using docId. FTS condition is also much more complex than just 'a*' so I expect that many results will be filtered out by the full-text query. Thanks, Milan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
Thank you for the answer. Your custom collation function would be my prime suspect here. Yes, it was. Some time ago we really corrected a bug in the collation, which resulted in decreased number of user reports. Even later we switched to the ICU library, which - I suppose - should be relatively safe. I know that the last statement may not be 100% true (under Windows I found some exotic strings that violate CompareString() transitivity; I can't remember if this confirmed for ICU), but: a) I got access to the content that caused the corruption and found that no unusual strings are used. For example one indexed column with a corrupted index contained only ascii strings. b) Suppose there was a bug in the collation. Could that bug cause a corruption of an index that does not use that collation? Here is a real-life example: CREATE TABLE [account] ( [accountid] UNIQUEIDENTIFIERNOT NULL CONSTRAINT PK_account PRIMARY KEY ROWGUIDCOL DEFAULT (newid()), [address1_city] NVARCHAR(160) NULL COLLATE NOCASE, [address1_country] NVARCHAR(160)NULL COLLATE NOCASE, [address1_latitude] FLOAT NULL, [address1_line1] NVARCHAR(500) NULL COLLATE NOCASE, [address1_line2] NVARCHAR(500) NULL COLLATE NOCASE, [address1_line3] NVARCHAR(500) NULL COLLATE NOCASE, [address1_longitude] FLOAT NULL, [address1_postalcode] NVARCHAR(40) NULL COLLATE NOCASE, [address1_stateorprovince] NVARCHAR(100)NULL COLLATE NOCASE, [createdon] DATETIMENULL, [defaultpricelevelid] UNIQUEIDENTIFIER NULL, [emailaddress1] NVARCHAR(200) NULL COLLATE NOCASE, [fax] NVARCHAR(100) NULL COLLATE NOCASE, [modifiedon] DATETIME NULL, [name] NVARCHAR(320)NULL COLLATE NOCASE, [ownerid] UNIQUEIDENTIFIER NULL, [statuscode] INTNOT NULL DEFAULT(1), [telephone1] NVARCHAR(100) NULL COLLATE NOCASE, [transactioncurrencyid] UNIQUEIDENTIFIERNULL, [websiteurl] NVARCHAR(400) NULL COLLATE NOCASE, [defaultpricelevelidTarget] NVARCHAR(100) NULL COLLATE NOCASE, [owneridTarget] NVARCHAR(100) NULL COLLATE NOCASE, [transactioncurrencyidTarget] NVARCHAR(100) NULL COLLATE NOCASE ); CREATE INDEX [FK_account_defaultpricelevelid] ON [account](defaultpricelevelid); CREATE INDEX [FK_account_ownerid] ON [account](ownerid); CREATE INDEX [FK_account_transactioncurrencyid] ON [account](transactioncurrencyid); CREATE INDEX [FK_account_name] ON [account](name COLLATE NOCASE); If NOCASE is a custom collation, could a bug in that collation explain corruption of indexes FK_account_transactioncurrencyid and FK_account_ownerid? (Both have NUMERIC affinity and store blobs such as X'001C2300C5DF8BEA11DF8834FBDCD77E'.) So far I supposed that the answer is NOT and consequently excluded a collation bug. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77554.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] Integrity check
On Thu, Aug 28, 2014 at 6:11 AM, Jan Slodicka j...@resco.net wrote: If NOCASE is a custom collation, could a bug in that collation explain corruption of indexes FK_account_transactioncurrencyid and FK_account_ownerid? (Both have NUMERIC affinity and store blobs such as X'001C2300C5DF8BEA11DF8834FBDCD77E'.) So far I supposed that the answer is NOT and consequently excluded a collation bug. Correct. If corruptions are appearing in indexes that do not use custom collations, that would tend to rule out a problem with the collation function. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
Simon Slavin-3 wrote On 27 Aug 2014, at 4:21pm, Jan Slodicka lt; jano@ gt; wrote: - There is one potentionally risky operation that our app performs: The data exchange with a remote WebService. This can take long (10+ min). Users often switch to email reading or similar activity, the app then runs on the background and can be eventually killed by OS. This might happen during an unfinished transaction. Does this happen while the database is open and in use by the app ? Yes When you exchange data, do you exchange data accessed as records (e.g. accessed using the SQLite API) or do you exchange files ? DB tables are being synchronized. The server sends xml data over http. This data is parsed and converted into a collection of high level data records. These records are then stored in the DB. At the same time some of the local DB records are sent to the server. The details are complex. Main point is that during a single transaction (which may take a long time) new records are added (deleted, updated) for a single table. If this succeeds, the transaction is commited and the whole process is repeated with a new table. In case of any failure, the whole syncronization process is aborted. What is strange - the corruption may affect tables that were accessed through different transactions. However, this may be explained by this scenario: - App is killed = synchronization is killed in the middle = DB gets first corruption. - App is restarted, DB corruption unnoticed and the user starts synchronization again. Synchronization resumes with the table where it stopped the last time. (I would expect that the DB corruption is discovered now, but it does not happen for some reason.) Synchronization updates a few tables, when the app is killed again causing another DB corruption etc. If you exchange files do you exchange just the database file or also journal files ? Should be answered above. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77556.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] Integrity check
Have you reviewed the list of corruption causes at http://www.sqlite.org/howtocorrupt.html and eliminated them all as possibilities? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
Thanks, may I ask about PRAGMA synchronous=Normal? The worst-case scenario I can imagine is that the app is killed by the OS when a checkpoint operation is in process... -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77558.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] Integrity check
On Thu, Aug 28, 2014 at 7:31 AM, Jan Slodicka j...@resco.net wrote: Thanks, may I ask about PRAGMA synchronous=Normal? The worst-case scenario I can imagine is that the app is killed by the OS when a checkpoint operation is in process... That should be safe. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS full-text query vs. query by rowid
Milan Kříž wrote: So does it mean that the full-text search is not performed for the following query at all? SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22, 23) SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1: EXECUTE LIST SUBQUERY 1 No, it means that you are using a different version. (In any case, it is not possible to execute MATCH without the FTS index.) what does the 'EXECUTE LIST SUBQUERY 1' mean? It's how the rowid IN (...) is implemented. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
Richard Hipp-3 wrote Have you reviewed the list of corruption causes at http://www.sqlite.org/howtocorrupt.html and eliminated them all as possibilities? Multiple times, but I did it again. In general I can exclude only a few points... 1.0 File overwrite by a rogue thread or process Excluded on iPhone 2.0 File locking problems Excluded - just a single app accesses the database 3.0 Failure to sync In general I doubt that this would be the reason on iPhone/iPad, but you certainly know more about the subject than I do. 4.0 Disk Drive and Flash Memory Failures Hardly possible, I think the problem would manifest in many other ways 5.0 Memory corruption In managed environment? Hardly. 6.0 Other operating system problems Don't believe so -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77561.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] FTS full-text query vs. query by rowid
Dne 28.8.2014 14:54, Clemens Ladisch napsal(a):
Milan Kříž wrote:
So does it mean that the full-text search is not performed for the following
query at all?
SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22,
23)
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1:
EXECUTE LIST SUBQUERY 1
No, it means that you are using a different version.
(In any case, it is not possible to execute MATCH without the FTS index.)
Thanks and sorry for bothering you with such details. But I still cannot
understand that query plan.
Since for a simple rowid query it says:
explain query plan
select * from ftsTable where docId = 100
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1:
It is clear to me that index 1 is definitely a rowId index.
Then for complex query above, it says:
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1:
EXECUTE LIST SUBQUERY 1
So where is a mention about using an FTS index (full-text query)?
Or is it just an imperfection of 'explain query plan' that the usage of the full-text index is missing from the
query plan?
I use SQLite version 3.8.5 and for full table full-text search it gives a correct index according to comment in
the fts3Int.h:
FTS3_FULLTEXT_SEARCH+${NUMBER_OF_COLUMNS} = VIRTUAL TABLE INDEX 18 in my
ftsTable
Regards,
Milan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
dbase3 would give an error if you did not include all the non-aggregate fields in the Group By. (One could also step forward/backward in a row-set, so some crude windowing was available if one coded to do that.) on this: select id, category_id, name, min(price) as minprice from cat_pictures group by category_id; I'd be reluctant to write that query because it is non standard SQL and I can't easily (5 minutes of searching) point at a document that tells me the expected behavior. One usually codes to documented behavior because it it is less likely to change without notice. Thanks for the references about windowing functions. Very interesting. The point of what is heavy now vs in 2020 is well made. Is Windowing a Major endeavor, better for sqlite 4? Adam DeVita On Wed, Aug 27, 2014 at 8:25 PM, Keith Medcalf kmedc...@dessus.com wrote: On Wednesday, 27 August, 2014 13:17, Petite Abeille said: On Aug 26, 2014, at 2:09 AM, Keith Medcalf kmedc...@dessus.com wrote: select id, category_id, name, min(price) as minprice from cat_pictures group by category_id; Done. And no need for any windowing functions ... This peculiar behavior is very unique to SQLite. Not really. Sybase, SQL Server and DB2 do (or did do) the same thing. Most reasonable SQL engines will throw an exception when confronted with the above. SQLite calls it a feature. I personally see it as a misfeature. ( Ditto with tagging an implicit limit 1 to scalar queries. Anyway. ) Well, I kind of like the former (group by) behaviour. Tacking of an automatic limit 1 on a scalar subquery may lead one to make bad assumptions about the shape of one's data, however, if one actually knows what one is doing, I don't think this is a problem either. On the other hand, one could look at the current 'group by' behavior as exhibited by SQLite as a precursor to a proper, more formalize, handling of analytic functions :) Perhaps. On the other hand, I really do not understand why people want analytic functions -- we did perfectly well analyzing data long before they were invented. But then again I cannot understand why people think that Relational Databases using SQL are better for everything than good old-fashioned Network-Extended Navigational Databases. But then again, maybe I'm just an old fart ... ___ 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] Window functions?
Adam Devita wrote: select id, category_id, name, min(price) as minprice from cat_pictures group by category_id; I'd be reluctant to write that query because it is non standard SQL and I can't easily (5 minutes of searching) point at a document that tells me the expected behavior. The SQL standard does not allow it. SQLite allows it for bug compatibility with MySQL. (The returned values are from some random row.) In SQLite 3.7.11 or later, the behaviour is defined: http://www.sqlite.org/releaselog/3_7_11.html but IIRC this was the wish of a paying customer, and is not documented anywhere else. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
The wording in the cited link is that Queries of the form: SELECT max(x), y FROM table returns the value of y on the same row that contains the maximum x value. There is some question of whether min(x) is of the form max(x). On Thu, Aug 28, 2014 at 10:28 AM, Clemens Ladisch clem...@ladisch.de wrote: Adam Devita wrote: select id, category_id, name, min(price) as minprice from cat_pictures group by category_id; I'd be reluctant to write that query because it is non standard SQL and I can't easily (5 minutes of searching) point at a document that tells me the expected behavior. The SQL standard does not allow it. SQLite allows it for bug compatibility with MySQL. (The returned values are from some random row.) In SQLite 3.7.11 or later, the behaviour is defined: http://www.sqlite.org/releaselog/3_7_11.html but IIRC this was the wish of a paying customer, and is not documented anywhere else. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
Sorry, I meant unixDelete My guess is that because you are using the host filing system vxWorks will be setting yet another error code for a file that doesn’t exist. So it will need another check adding to unixDelete Regards Andy Ling From: 王庆刚 [mailto:2004wqg2...@163.com] Sent: 28 August 2014 15:01 To: Andy Ling Cc: sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？ Is unixUnlink an function? If it is , but I can not find the funtion in sqlite3.c. Regards Wang Qinggang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Thu, Aug 28, 2014 at 10:38 AM, Gabor Grothendieck ggrothendi...@gmail.com wrote: The wording in the cited link is that Queries of the form: SELECT max(x), y FROM table returns the value of y on the same row that contains the maximum x value. There is some question of whether min(x) is of the form max(x). It is. That behavior is defined and tested for min() and max(). But it doesn't work for any other aggregate function. Note also that if you have multiple min() and/or max() aggregate functions in the same query, then the result will be from the row in which one of them is the min() or the max(), but which one is arbitrary. So it is only well-defined if you have a single min() or a single max(). -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS full-text query vs. query by rowid
Milan Kříž wrote: Dne 28.8.2014 14:54, Clemens Ladisch napsal(a): Milan Kříž wrote: So does it mean that the full-text search is not performed for the following query at all? No, it means that you are using a different version. But I still cannot understand that query plan. Then try with 3.8.6. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
On 28 Aug 2014, at 12:23pm, Jan Slodicka j...@resco.net wrote: Simon Slavin-3 wrote When you exchange data, do you exchange data accessed as records (e.g. accessed using the SQLite API) or do you exchange files ? DB tables are being synchronized. The server sends xml data over http. This data is parsed and converted into a collection of high level data records. These records are then stored in the DB. At the same time some of the local DB records are sent to the server. Okay, thanks for that. It tells me several things not to worry about, and several things that won't matter at all. Most people who think they do sync do it by exchanging files and run into problems. Your solution seems to do it by exchanging data accessed using the SQLite API so you shouldn't have that sort of problem. What is strange - the corruption may affect tables that were accessed through different transactions. However, this may be explained by this scenario: - App is killed = synchronization is killed in the middle = DB gets first corruption. - App is restarted, DB corruption unnoticed I assume that you aren't using any PRAGMAs which speed up SQLite at the expense of safety, for instance, keeping journals in memory or turning off synchrony. If you're letting SQLite handle journalling properly then DB corruption caused by crashes should always be noticed and rectified, with the database restored to the most recent transaction boundary. Any failure to do that properly is a fault in SQLite and the dev team will fix it. and the user starts synchronization again. Synchronization resumes with the table where it stopped the last time. (I would expect that the DB corruption is discovered now, but it does not happen for some reason.) Synchronization updates a few tables, when the app is killed again causing another DB corruption etc. It would be nice to think that corruption which happens while writing to a particular table would corrupt only that table. Unfortunately, that's not the case. You may have an app which writes to only one table in the database, and then suffer power-loss. If the database is corrupted because your hardware flipped bits during power-loss and overwrote the wrong part of the disk, the corruption might well be in a different table. Or a different file ! I am slightly concerned about your mention of App is killed. Is this part of your routine operations or are you just being careful to mention that it's possible ? If your app runs on an iPhone it should always get notification before it is put into the background or terminated, even in low-battery conditions. You should respond to those notifications appropriately. Normal practise these days is to close database connections when your app is backgrounded and reopen them when they are needed again, unless your app has functionality which needs to continue when it's in the background. And you need to call _close() when your gets termination notification, of course. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On 28 Aug 2014, at 3:45pm, Richard Hipp d...@sqlite.org wrote: On Thu, Aug 28, 2014 at 10:38 AM, Gabor Grothendieck ggrothendi...@gmail.com wrote: The wording in the cited link is that Queries of the form: SELECT max(x), y FROM table returns the value of y on the same row that contains the maximum x value. There is some question of whether min(x) is of the form max(x). It is. That behavior is defined and tested for min() and max(). But it doesn't work for any other aggregate function. Note also that if you have multiple min() and/or max() aggregate functions in the same query, then the result will be from the row in which one of them is the min() or the max(), but which one is arbitrary. So it is only well-defined if you have a single min() or a single max(). Similarly, it's possible that two or more rows in the table will have the maximum value for column x. There's no rule about which of them will be chosen to have its y value returned or even that SQLite will be consistent about it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
Is unixUnlink an function? If it is , but I can not find the funtion in sqlite3.c. Regards Wang Qinggang At 2014-08-28 00:03:23, Andy Ling andy.l...@quantel.com wrote: So you are using a host file system. You should be able to make the open work by using rc = sqlite3_open(host:D:/WindRiver/SqliteOne.db,db); You have to use a path and file name that works in the vxWorks domain. It shouldn’t be necessary to use –DSQLITE_ENABLE_LOCKING_STYLE=1. In fact it has been said this will probably not work in other areas. So it would be good to find out what is causing the disk I/O error. Did you try putting some debug in unixUnlink? Regards Andy Ling From:王庆刚 [mailto:2004wqg2...@163.com] Sent: 27 August 2014 16:17 To: Andy Ling Cc:sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？ Firstly: I fixed the disk I/O error by adding -DSQLITE_ENABLE_LOCKING_STYLE=1 to the build macro. Secondly: rc = sqlite3_open(D:\\WindRiver\\SqliteOne.db,db); I change it to the following cd(host:D:/WindRiver); rc = sqlite3_open(SqliteOne.db,db); can fix the problem : unable to open the database. At 2014-08-26 08:55:31, Andy Ling andy.l...@quantel.com wrote: As I said before, that doesn’t look like a vxWorks path to a file. Are you sure D:\\WindRiver really exists? In general vxWorks uses the forward slash (/) as a path separator. Is this a remote mounted host file system you are trying to use? What is your current directory when it works the second time? i.e. what is the output from the “pwd” command? Can you use that directory as part of a full pathname? I’m glad you have fixed the disk I/O problem. What did you have to do? Are there any more changes that need feeding back into the source? Regards Andy Ling From:王庆刚 [mailto:2004wqg2...@163.com] Sent: 26 August 2014 13:44 To: Andy Ling Cc:sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？ hi, Andy Ling: The error disk I / o error I have resolved ; but when I used the following code: rc = sqlite3_open(D:\\WindRiver\\SqliteOne.db,db); it tell me can not find the data base; when I used the following code: rc = sqlite3_open(SqliteOne.db,db); It is OK How could I resolve the prolblem? At 2014-08-25 11:29:57, Andy Ling andy.l...@quantel.com wrote: That file name in the first error doesn't look like a vxWorks file. What devices have you got mounted. You need to specify a file path that points to one of the vxWorks file IO devices. By just specifying the file name in your second example it is being created in the current directory. The second disk I / O error is what I had before applying the patch to unixUnlink. The error returned by vxWorks when deleting a file that doesn't exist depends on the underlying file system. If it is a POSIX file system it should return ENOENT. For dosFS it returns S_dosFsLib_FILE_NOT_FOUND, which in vxWorks 6.9 is 0x380003 So if you are using yet another file system, maybe you are getting a different error code being set. start by adding a printf to unixUnlink to find out if that is your problem. Regards Andy Ling From:王庆刚 [2004wqg2...@163.com] Sent: 25 August 2014 13:13 To:sqlite-users@sqlite.org; Andy Ling; Jan Nijtmans Subject: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？ hi I modified the code sqlite3.c according to you method, as follow http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9to=b68f65bb69a098a1 or http://fossil-scm.org/index.html/info/c2d4bd7365 I test you method in workbench3.2(vxworks6.8) , the build macros which I used in build properties is : -DOS_VXWORKS=1 -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -DHAVE_UTIME but it still have some problems. 1. if I do as follows: rc = sqlite3_open(D:\\WindRiver\\SqliteOne.db,db); it tell me the error can not open the database. 2. if I do as follows: rc = sqlite3_open(SqliteOne.db,db);this will be ok . but when I do the following thing sql = create table stu(i int, name text);; rc = sqlite3_exec(db,sql,NULL,NULL,err); it tell me the error : disk I / O error. 在 2014-08-12 08:10:13，Jan Nijtmans jan.nijtm...@gmail.com 写道： 2014-08-03 9:56 GMT+02:00 Jan Nijtmans jan.nijtm...@gmail.com: 2014-08-02 16:00 GMT+02:00 王庆刚 2004wqg2...@163.com: hi ， Can Sqlite3.c and sqlite.h be compiled in Workbench3.2 for Vxworks6.8 ? When I compile them , there have so many problems . You can find the necessary changes here: http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9to=b68f65bb69a098a1 thanks to Andy Ling. Still has to be reviewed by the SQLite developers for inclusion in 3.8.6 (not tested yet on other platforms than vxworks, win32/64 and Linux, there it works fine) New attempt here, base on
Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
Sorry, I meant unixDelete My guess is that because you are using the host filing system vxWorks will be setting yet another error code for a file that doesn’t exist. So it will need another check adding to unixDelete Regards Andy Ling From: 王庆刚 [mailto:2004wqg2...@163.com] Sent: 28 August 2014 15:01 To: Andy Ling Cc: sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？ Is unixUnlink an function? If it is , but I can not find the funtion in sqlite3.c. Regards Wang Qinggang At 2014-08-28 00:03:23, Andy Ling andy.l...@quantel.commailto:andy.l...@quantel.com wrote: So you are using a host file system. You should be able to make the open work by using rc = sqlite3_open(host:D:/WindRiver/SqliteOne.db,db); You have to use a path and file name that works in the vxWorks domain. It shouldn’t be necessary to use �CDSQLITE_ENABLE_LOCKING_STYLE=1. In fact it has been said this will probably not work in other areas. So it would be good to find out what is causing the disk I/O error. Did you try putting some debug in unixUnlink? Regards Andy Ling From: 王庆刚 [mailto:2004wqg2...@163.commailto:2004wqg2...@163.com] Sent: 27 August 2014 16:17 To: Andy Ling Cc: sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？ Firstly: I fixed the disk I/O error by adding -DSQLITE_ENABLE_LOCKING_STYLE=1 to the build macro. Secondly: rc = sqlite3_open(D:\\WindRiver\\SqliteOne.db,db); I change it to the following cd(host:D:/WindRiver); rc = sqlite3_open(SqliteOne.db,db); can fix the problem : unable to open the database. At 2014-08-26 08:55:31, Andy Ling andy.l...@quantel.commailto:andy.l...@quantel.com wrote: As I said before, that doesn’t look like a vxWorks path to a file. Are you sure D:\\WindRiver really exists? In general vxWorks uses the forward slash (/) as a path separator. Is this a remote mounted host file system you are trying to use? What is your current directory when it works the second time? i.e. what is the output from the “pwd” command? Can you use that directory as part of a full pathname? I’m glad you have fixed the disk I/O problem. What did you have to do? Are there any more changes that need feeding back into the source? Regards Andy Ling From: 王庆刚 [mailto:2004wqg2...@163.commailto:2004wqg2...@163.com] Sent: 26 August 2014 13:44 To: Andy Ling Cc: sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？ hi, Andy Ling: The error disk I / o error I have resolved ; but when I used the following code: rc = sqlite3_open(D:\\WindRiver\\SqliteOne.db,db); it tell me can not find the data base; when I used the following code: rc = sqlite3_open(SqliteOne.db,db); It is OK How could I resolve the prolblem? At 2014-08-25 11:29:57, Andy Ling andy.l...@quantel.commailto:andy.l...@quantel.com wrote: That file name in the first error doesn't look like a vxWorks file. What devices have you got mounted. You need to specify a file path that points to one of the vxWorks file IO devices. By just specifying the file name in your second example it is being created in the current directory. The second disk I / O error is what I had before applying the patch to unixUnlink. The error returned by vxWorks when deleting a file that doesn't exist depends on the underlying file system. If it is a POSIX file system it should return ENOENT. For dosFS it returns S_dosFsLib_FILE_NOT_FOUND, which in vxWorks 6.9 is 0x380003 So if you are using yet another file system, maybe you are getting a different error code being set. start by adding a printf to unixUnlink to find out if that is your problem. Regards Andy Ling From: 王庆刚 [2004wqg2...@163.commailto:2004wqg2...@163.com] Sent: 25 August 2014 13:13 To: sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org; Andy Ling; Jan Nijtmans Subject: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？ hi I modified the code sqlite3.c according to you method, as follow http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9to=b68f65bb69a098a1 or http://fossil-scm.org/index.html/info/c2d4bd7365 I test you method in workbench3.2(vxworks6.8) , the build macros which I used in build properties is : -DOS_VXWORKS=1 -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -DHAVE_UTIME but it still have some problems. 1. if I do as follows: rc = sqlite3_open(D:\\WindRiver\\SqliteOne.db,db); it tell me the error can not open the database. 2. if I do as follows: rc = sqlite3_open(SqliteOne.db,db);this will be ok . but when I do the following thing sql = create table stu(i int, name text);; rc = sqlite3_exec(db,sql,NULL,NULL,err); it tell me the error : disk I / O error. 在 2014-08-12 08:10:13，Jan Nijtmans
Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net)
Zachary Yates wrote:
There's a lot more detail to this question posted at:
http://stackoverflow.com/questions/25534898/trouble-with-sqlexpressiont-join
-and-column-names
I'm unable to replicate this issue using System.Data.SQLite. Here is what
I've tried so far:
set db [sql open -type SQLite {Data Source=:memory:;}]
sql execute $db {
CREATE TABLE Country(
Id,
Code,
Title,
ShortTitle,
ModifiedOn,
ModifiedBy
);
CREATE TABLE Mid(
Id,
CountryCode
);
CREATE TABLE ProgrammingMapView(
Mid,
ProductId
);
INSERT INTO Country (
Id, Code, Title, ShortTitle, ModifiedOn, ModifiedBy)
VALUES (
1, 'US', 'United States of America', 'United States',
'2014-01-01', 'Joe'
);
INSERT INTO ProgrammingMapView (Mid, ProductId) VALUES (1, 87);
INSERT INTO Mid (Id, CountryCode) VALUES (1, 'US');
SELECT Country.Id, Country.Code, Country.Title,
Country.ShortTitle, Country.ModifiedOn,
Country.ModifiedBy
FROM Country
INNER JOIN ProgrammingMapView
ON (Mid.Id = ProgrammingMapView.Mid)
INNER JOIN Mid
ON (Country.Code = Mid.CountryCode)
WHERE (ProgrammingMapView.ProductId = 87)
ORDER BY Country.Title ASC;
}
parray rows
The above uses the EagleShell from the Externals directory in the
source tree. Here, the final line of output from the above script
is:
rows(names) = Id Code Title ShortTitle ModifiedOn ModifiedBy
Underneath, this ends up using the exact same IDataRecord.GetName
method.
Do you have any further information on this issue that might be
useful?
--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS full-text query vs. query by rowid
Dne 28.8.2014 16:48, Clemens Ladisch napsal(a): Then try with 3.8.6. Ouuu . .sorry again. I have tested it with 3.8.6 and the query plan looks ok now. SCAN TABLE nameftsTable VIRTUAL TABLE INDEX 18: EXECUTE LIST SUBQUERY 1 But I also tested it with my version again and I'm getting the same (correct) query plan now. I cannot understand how it is possible :-). The only thing I changed is that in the meantime I've recreated my ftsTable. So it was either in some strange state I cannot reproduce now or I am simply overworked :-). Or maybe I accidentally used '=' or 'is' instead of 'match'. However, many thanks for your support, now it all seems much more clear. Milan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
Thanks, Simon. Simon Slavin-3 wrote Your solution seems to do it by exchanging data accessed using the SQLite API so you shouldn't have that sort of problem. Yes, only standard SQLite API is used. I assume that you aren't using any PRAGMAs which speed up SQLite at the expense of safety, for instance, keeping journals in memory or turning off synchrony.. No tricks are done. My first post contains all SQLite settings used. ...If the database is corrupted because your hardware flipped bits during power-loss and overwrote the wrong part of the disk, the corruption might well be in a different table... Clear. I just wonder that integrity_check first performs a number of checks that validate that all tables are basically ok. As part of this process also the indexes are formally declared as correct tables. Only then, when the contents of the indexes is checked (thing that cannot be done for ordinary tables), some problems are found. I would expect a different report if some random bits are overwritten. Moreover, the error report suggests the hypothesis as if the the data transaction was correctly completed on all data tables, but the indexes were for some reason not updated. I am just looking for a feasible explanation, I don't know how SQLite works internally. I am slightly concerned about your mention of App is killed. Is this part of your routine operations or are you just being careful to mention that it's possible ? Killing can be done only by iOS. Suppose our app is running and the user switches to email reading. The app continues running on background and roughly after 10 min the system sends a notification that it is going to be frozen. In reaction our app tries to interrupt the data communication with the server (Problem1 - this may fail), closes the database (Problem2 - possible NullReferenceException some time later) etc. Then iOS freezes the application. What happens then, depends on user activities. If he opens say huge PDF, iOS may decide to kill the app. If not, the app will be resumed after some time. Many things can happen in this process (lousy programming, I have to improve lots of things...). The worst scenario is that the app is killed by OS during some SQLite action. -- I apologize in advance: I'll be out of office until next Tuesday. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77575.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] Possible bug in System.Data.Sqlite.Core (ADO.Net)
Joe Mistachkin wrote: I'm unable to replicate this issue Your query did not involve the query flattener. Try this: select t.x from (select x from (select 1 x)) t; Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integrity check
On 28 Aug 2014, at 4:57pm, Jan Slodicka j...@resco.net wrote: Simon Slavin-3 wrote ...If the database is corrupted because your hardware flipped bits during power-loss and overwrote the wrong part of the disk, the corruption might well be in a different table... Clear. I just wonder that integrity_check first performs a number of checks that validate that all tables are basically ok. As part of this process also the indexes are formally declared as correct tables. Only then, when the contents of the indexes is checked (thing that cannot be done for ordinary tables), some problems are found. I'm answering only some points here. If I don't comment on something assume I can't see why it might be causing your problem or anything wrong with what you're doing. It might be worth knowing that PRAGMA integrity_check is not perfect. It doesn't check every single little thing that might be wrong with the database file. It concentrates on making sure that if you wrote a program that read every row of every database, using any index for each table, you would get every row of data. It is good at checking that you could DUMP your data to SQL commands and read it back in again, but it's not a good exhaustive check that every byte of your database file is what it should be. That would take a lot longer to execute. Killing can be done only by iOS. Suppose our app is running and the user switches to email reading. The app continues running on background and roughly after 10 min the system sends a notification that it is going to be frozen. iOS expects only the frontmost app to be using significant CPU or IO. It is normal in iOS applications not to do heavy work when your application is backgrounded unless doing so is a mainstay of your application, e.g. an email program checking for new mail. If possible, you should try to do your synchronisation when your app is frontmost only. However, I understand that this may not be appropriate for your app. In reaction our app tries to interrupt the data communication with the server (Problem1 - this may fail) Exactly. If your app is backgrounded when this happens you cannot give feedback to your user because your app is backgrounded. The interruption of communication with the server would normally come when your app is notified it's going to be backgrounded. This means that iOS will wait patiently for your app to cope with its problems before doing something intense like displaying a PDF. Similar problem: on an iPhone iOS will also kill your process if it tries to hog CPU or IO during a phone call. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net)
Clemens Ladisch wrote: Your query did not involve the query flattener. Try this: I used the query as originally reported. However, as others have stated, without AS clauses, the column names are not well-defined. In this case, the IDataRecord.GetName method of the SQLiteDataReader class is a very thin wrapper around the underlying sqlite3_column_name() API. Basically, it just returns whatever the sqlite3_column_name() API returns verbatim. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net)
Here is the DDL for the scenario: create table Country ( Id integer primary key, Code text, Title text, ShortTitle text, ModifiedOn text, ModifiedBy integer ); create table Mid ( Id integer primary key, CountryCode text, ModifiedOn text, ModifiedBy integer ); create view ProgrammingMapView as select p.Id ProductId , pt.Id ProtocolId , m.Id Mid from Mid m join MidProduct mprod on (mprod.RegisteredMid = m.Id) join Product p on (p.Id = mprod.ProductId) join MidProtocol mprot on (mprot.RegisteredMid = m.Id) join ProtocolType pt on (pt.Id = mprot.ProtocolId) join ProductProtocol pp on (pp.ProductId = p.Id and pp.ProtocolTypeId = pt.Id) ; -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Thursday, August 28, 2014 10:07 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net) Clemens Ladisch wrote: Your query did not involve the query flattener. Try this: I used the query as originally reported. However, as others have stated, without AS clauses, the column names are not well-defined. In this case, the IDataRecord.GetName method of the SQLiteDataReader class is a very thin wrapper around the underlying sqlite3_column_name() API. Basically, it just returns whatever the sqlite3_column_name() API returns verbatim. -- Joe Mistachkin ___ 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] blob + rowID Insert question
Hi All, I have a table with one unique column of the type BLOB. Because I am using the direct SQLite functions to read and write BLOBS, the access is made referencing the ROW IDs. Is there any way to replace a BLOB for a give ROW ID? If I perform DELETE and INSERT I am not sure the ROW IDS will keep sequential and I cannot call VACCUM for performance reasons in the application. Thanks Carlos ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob + rowID Insert question
On 28 Aug 2014, at 7:06pm, Carlos Ferreira car...@csiberkeley.com wrote: Is there any way to replace a BLOB for a give ROW ID? Use the UPDATE command: UPDATE myTable SET theBlob = whatever WHERE id = theID Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob + rowID Insert question
Thanks :) :) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: quinta-feira, 28 de Agosto de 2014 19:08 To: General Discussion of SQLite Database Subject: Re: [sqlite] blob + rowID Insert question On 28 Aug 2014, at 7:06pm, Carlos Ferreira car...@csiberkeley.com wrote: Is there any way to replace a BLOB for a give ROW ID? Use the UPDATE command: UPDATE myTable SET theBlob = whatever WHERE id = theID 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] blob + rowID Insert question
One other question: I use the SQLite3_BindBlob to add a new blob to a given record. From this point on I know how to change the BLOB ( but not its size ) using the incremental BLBO direct Access functions. Using your suggestion of the UPDATE Statement, and assuming I have a block of memory with pointer p and size s ( different from the previous size ), that I want to use as the data of my new blob, do you suggest I create a very long string as the statement? In the next line: UPDATE myTable SET theBlob = whatever WHERE id = theID The whatever is a long string containing the data? How should I serialize it? What kind of encoding? Thanks Carlos -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: quinta-feira, 28 de Agosto de 2014 19:08 To: General Discussion of SQLite Database Subject: Re: [sqlite] blob + rowID Insert question On 28 Aug 2014, at 7:06pm, Carlos Ferreira car...@csiberkeley.com wrote: Is there any way to replace a BLOB for a give ROW ID? Use the UPDATE command: UPDATE myTable SET theBlob = whatever WHERE id = theID 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] blob + rowID Insert question
On 28 Aug 2014, at 7:18pm, Carlos Ferreira car...@csiberkeley.com wrote: Using your suggestion of the UPDATE Statement, and assuming I have a block of memory with pointer p and size s ( different from the previous size ), that I want to use as the data of my new blob, do you suggest I create a very long string as the statement? That's the simplest way. You can bind your pointer length to the blob using binding, or include it in a string in the format x'0500' . See section 2.3 of http://www.sqlite.org/datatype3.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net)
Zachary Yates wrote: create view ProgrammingMapView as select p.Id ProductId , pt.Id ProtocolId , m.Id Mid from Mid m join MidProduct mprod on (mprod.RegisteredMid = m.Id) join Product p on (p.Id = mprod.ProductId) join MidProtocol mprot on (mprot.RegisteredMid = m.Id) join ProtocolType pt on (pt.Id = mprot.ProtocolId) join ProductProtocol pp on (pp.ProductId = p.Id and pp.ProtocolTypeId = pt.Id) ; Thanks. It should be noted that the above view refers to other tables as well. Anyhow, the best I can come up with at this point is that the ORM package(s) really should include AS clauses in the generated SQL in order to get the desired column names, per the SQL standard. This is not a bug in SQLite nor System.Data.SQLite. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Wed, 27 Aug 2014 23:04:40 +0200 Petite Abeille petite.abei...@gmail.com wrote: On Aug 27, 2014, at 10:57 PM, Eduardo Morras emorr...@yahoo.es wrote: Sorry, don't understand why others will throw an exception in the group by, perhaps I'm misunderstanding the group by, but that should work on others engines. Because not all expressions are accounted for, i.e.: not a GROUP BY expression Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause. Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.” Yep, it's true, I didn't notice it and asked too early. Try it. See what happen. It bangs, as expected, in PostgreSQL 9.2, adding the other selects columns solves it. Thanks. --- --- Eduardo Morras emorr...@yahoo.es ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net)
Thanks for looking into it! I'll follow up with the ORM package authors. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Thursday, August 28, 2014 11:41 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Possible bug in System.Data.Sqlite.Core (ADO.Net) Zachary Yates wrote: create view ProgrammingMapView as select p.Id ProductId , pt.Id ProtocolId , m.Id Mid from Mid m join MidProduct mprod on (mprod.RegisteredMid = m.Id) join Product p on (p.Id = mprod.ProductId) join MidProtocol mprot on (mprot.RegisteredMid = m.Id) join ProtocolType pt on (pt.Id = mprot.ProtocolId) join ProductProtocol pp on (pp.ProductId = p.Id and pp.ProtocolTypeId = pt.Id) ; Thanks. It should be noted that the above view refers to other tables as well. Anyhow, the best I can come up with at this point is that the ORM package(s) really should include AS clauses in the generated SQL in order to get the desired column names, per the SQL standard. This is not a bug in SQLite nor System.Data.SQLite. -- Joe Mistachkin ___ 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] String compare scoring functions
Hello, I need a function that gives a real result from 0 to 100 representing the similarity of two input strings. I need to compare a dataset of addresses with a dataset of addresses with house numbers, having coordinates, to get a geolocalization of the addresses. Since the addresses aren't identical to the addresses of the street map dataset, I wish to get a score to measure the similarity of the two strings. I've found http://stackoverflow.com/questions/653157/a-better-similarity-ranking-algorithm-for-variable-length-strings and I hope there is some implementation of a similar function to be used in Sqlite queries. Any suggestion, any help? Thanks, Maurizio ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob + rowID Insert question
On 8/28/2014 2:18 PM, Carlos Ferreira wrote: In the next line: UPDATE myTable SET theBlob = whatever WHERE id = theID The whatever is a long string containing the data? The whatever should be ? (question mark) - a parameter placeholder (theID could be another one). Prepare the statement, bind the data to the parameter using sqlite3_bind_blob (which you have already discovered), then execute the statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob + rowID Insert question
Thanks!! -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: sexta-feira, 29 de Agosto de 2014 00:20 To: sqlite-users@sqlite.org Subject: Re: [sqlite] blob + rowID Insert question On 8/28/2014 2:18 PM, Carlos Ferreira wrote: In the next line: UPDATE myTable SET theBlob = whatever WHERE id = theID The whatever is a long string containing the data? The whatever should be ? (question mark) - a parameter placeholder (theID could be another one). Prepare the statement, bind the data to the parameter using sqlite3_bind_blob (which you have already discovered), then execute the statement. -- 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] HELP sqlite3 used in vxworks has someproblem？
i find it not enter the unixdelete. 发自我的 iPhone 在 2014年8月28日，22:42，Andy Ling andy.l...@quantel.com 写道： Sorry, I meant unixDelete My guess is that because you are using the host filing system vxWorks will be setting yet another error code for a file that doesn’t exist. So it will need another check adding to unixDelete Regards Andy Ling From: 王庆刚 [mailto:2004wqg2...@163.com] Sent: 28 August 2014 15:01 To: Andy Ling Cc: sqlite-users@sqlite.org; Jan Nijtmans Subject: Re:RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？ Is unixUnlink an function? If it is , but I can not find the funtion in sqlite3.c. Regards Wang Qinggang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reseting total_changes() to 0
Greetings! Can total_chages() be set to zero or reset? thanks. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
