[sqlite] Problem saving datatable back to the sqlite database
Hello,
I would appreciate any help in finding why I get an error when saving back
to a database.
I am using vb.net in VS-2019 with sqlite.
The error is: "Concurrency violation: the UpdateCommand affected 0 of the
expected 1 records."
I have worked out that the error only occurs when my table has a date field
which I fill from the underlying database use a sqlitedatadapter as follows:
Public connstring As String = "Data
Source=c:\VS-2019\SaveTable\SaveTable\Data\TestDb.db"
Public conn As New SQLiteConnection
Public Sub FillMainTables()
conn.ConnectionString = connstring
conn.Open()
DTAllCustomers.Rows.Clear()
sql = "SELECT * FROM Customers"
AllCustomersAdapter = New SQLiteDataAdapter(sql, conn)
AllCustomersAdapter.Fill(DTAllCustomers)
End Sub
I then modify a few records in the datatable(DTAllCustomers) using this
code:
For i = 0 To DTAllCustomers.Rows.Count - 1
If DTAllCustomers.Rows(i).Item("sitecode") = "RESI" Then
DTAllCustomers.Rows(i).Item("Email") = "Changed"
End If
Next
I then try to save the modified table back to the underlying database with:
Dim cbAllCustomers As New SQLiteCommandBuilder(AllCustomersAdapter)
AllCustomersAdapter.UpdateCommand = cbAllCustomers.GetUpdateCommand
Try
AllCustomersAdapter.Update(DTAllCustomers)
Catch ex As Exception
MsgBox(ex.Message)
End Try
When I remove the DATETIME columns in DTAllCustomers the error disappears
and the update completes correctly. Does SQLite change the date
structure/value of the DateTime columns between filling the datatable and
saving back to the datatable?
I created the database using "sqlite expert personal". When I loop through
the DTAllCustomers table I inspect the value of a date field and it appears
as - #2/12/2020 12:00:00 AM#
Any help is greatly appreciated as I have spent weeks trying to solve this
through any number of forums.
Brad
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problematic new ALTER TABLE behaviour in 3.25.1
On 2018-09-19 2:30 p.m., Brad Spencer wrote: In sqlite-3.25.0, the release notes say the following: "Fix table rename feature so that it also updates references to the renamed table in triggers and views." ... However, because of this change, the "completely general" procedure on the ALTER TABLE page no longer works. >... Does anybody have any thoughts on whether there needs to be a new general procedure for performing table alterations that works with the new ALTER TABLE behaviour? $ ./sqlite3 :memory: < ~/external/sqlite3/trigger.sql Before modifications 1|aaa 1|aaa About to rename new_t2 to t2 Error: near line 34: error in trigger t1_trigger: no such table: main.t2 After modifications Error: near line 45: no such table: main.t2 1|aaa Error: near line 47: no such table: t2 Is there a bug in this new behaviour, or have I misunderstood the procedure? Note that this appears to be independent from the other ALTER TABLE issue that was repaired in 3.25.1. Thanks. -- Brad Spencer ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problematic new ALTER TABLE behaviour in 3.25.0
On 2018-09-19 2:30 PM, Brad Spencer wrote: Disclaimer: I haven't yet tried this with the pre-release version of sqlite-3.25.1, but I wanted to report it before the 24 hours of that release notice expired. I grabbed https://www.sqlite.org/2018/sqlite-src-3250100.zip and tried this and result appears to be the same. -- Brad Spencer ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problematic new ALTER TABLE behaviour in 3.25.0
Disclaimer: I haven't yet tried this with the pre-release version of sqlite-3.25.1, but I wanted to report it before the 24 hours of that release notice expired. In sqlite-3.25.0, the release notes say the following: "Fix table rename feature so that it also updates references to the renamed table in triggers and views." This is mentioned again on https://www.sqlite.org/lang_altertable.html: "Beginning with release 3.25.0 (2018-09-15), references to the table within trigger bodies and view definitions are also renamed." However, because of this change, the "completely general" procedure on the ALTER TABLE page no longer works. For example, the following works in sqlite-3.24.0 but not in sqlite-3.25.0: -- Create a pair of tables related by a trigger. create table t1(a integer not null primary key, b text not null); create table t2(c integer primary key, d text); -- Create a simple trigger on t1 that uses t2. create trigger t1_trigger after insert on t1 for each row begin insert into t2 (c, d) values (new.a, new.b); end; -- Demonstrate that it works. select 'Before modifications' as comment; insert into t1 values (1, 'aaa'); select * from t1; select * from t2; -- Follow the general procedure from -- https://www.sqlite.org/lang_altertable.html pragma foreign_keys = off; begin transaction; -- Change c and d to be "not null". create table new_t2(c integer not null primary key, d text not null); -- Copy data. insert into new_t2 (c, d) select c, d from t2; -- Drop old table. drop table t2; -- Rename table. select 'About to rename new_t2 to t2' as comment; alter table new_t2 rename to t2; -- Check FKs. pragma foreign_key_check; -- Finish. commit transaction; pragma foreign_keys = on; -- Demonstrate that it still works. select 'After modifications' as comment; insert into t1 values (2, 'bbb'); select * from t1; select * from t2; Output in sqlite-3.24.0: $ ./sqlite3 :memory: < ~/external/sqlite3/trigger.sql Before modifications 1|aaa 1|aaa About to rename new_t2 to t2 After modifications 1|aaa 2|bbb 1|aaa 2|bbb Output in sqlite-3.25.0: $ ./sqlite3 :memory: < ~/external/sqlite3/trigger.sql Before modifications 1|aaa 1|aaa About to rename new_t2 to t2 Error: near line 34: error in trigger t1_trigger: no such table: main.t2 After modifications Error: near line 45: no such table: main.t2 1|aaa Error: near line 47: no such table: t2 Note that the failure occurs before the ALTER TABLE page's procedure says to re-create any triggers. But even so, in this case, the triggers are "on" t1, not t2, so in previous SQLite versions they did not need to be re-created anyway. It seems like the new ALTER TABLE behaviour was intended to be an enhancement that makes it easier to make schema changes and that this is an unexpected consequence of the change. Personally, I found the older behaviour easier to work with: You re-created triggers on the table you were recreating, but left any (symbolic) references to the recreated table from other tables' triggers alone. Now, it looks like I will have to drop all triggers in the database that reference the recreated table and recreate them all, which seems to be the opposite of the intended effect. This also means going back and rewriting previous database schema patches that no longer run under sqlite-3.25.0. Perhaps at least there should be a pragma or configuration flag to turn this new behaviour off? -- Brad Spencer ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE statement without FROM clause
Is there some absolute requirement that it all be done in SQL? Depending on the number of "items", it'd probably be faster in a loop in code. Even in MSSQL Server using TSQL, you're better off using a cursor for that sort of thing. I only use UPDATE FROM when I need a join to formulate the WHERE clause. > On Jun 4, 2016, at 12:18, skywind mailing lists > wrote: > > Hi, > > why? At the moment I have to run something like: > > UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE > EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID); > > Using a FROM clause I just need one scan through B (at least in principle). > Now, I need N+1 scans. > > Regards, > Hartwig > >> Am 2016-06-04 um 15:33 schrieb Gerry Snyder : >> >> If SQLite implemented the FROM it would just be a translation into the >> complex and slow statements you want to avoid. >> >> Gerry Snyder >> On Jun 4, 2016 9:19 AM, "skywind mailing lists" >> wrote: >> >>> Hi, >>> >>> I am using quite often SQL statements that update the data of one table >>> with data from another table. This leads to some quite complex (and slow) >>> statements because SQLite3 is not supporting a FROM clause in update >>> statements. I am just wondering why the FROM clause is not supported by >>> SQLite3?! Is this too complex to implement or is there simply no demand for >>> these type of statements? >>> >>> Regards, >>> Hartwig >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Dotnets library System.Data.SQLite.Core doesn't convert DateTimes correcly
How are you storing the dates in the DB? Is the WHERE clause using the same format? Given SQLite's "duck" typing, I don't believe there is any built in definition of a date time type. MSSQL, for instance, has a built in "datetime" type, and when you build a WHERE clause with, e.g. a string, SQL Server automatically does the conversion. MySQL might be doing the same. SQLite doesn't do that, IME, so you must ensure that the writers and the readers are all using the same format, whether that be ticks, Julian date, ISO 8601, or whatever. > On Apr 27, 2016, at 07:23, Markus Amshove wrote: > > Hello, > > > > I'm using the "System.Data.SQLite.Core" lib to connect to a sqlite-database > and try to query a dataset by using DateTime in the WHERE clause. > > > > If I directly use the DateTime object as parameter to the WHERE clause I > don't get any result. > > If I reproduce this against, for example, a MySQL database it handles the > paramter correctly. > > > > I started a discussion at > > https://www.reddit.com/r/csharp/comments/4gexh6/cant_query_datetime_in_sqlit > edapper/ > > which let me to the conclusion that it might be a bug in the SQLite driver. > > > > It seems to be dependent on the culture that my application is running in. > > Here the gist: > > https://gist.github.com/anonymous/0e11f74f075231f5c5be3dcc0dd7510b > > > > The following nuget packages were used: > > Dapper, System.Data.SQLite.Core, FluentMigrator, FluentMigrator.Runner > > > > What I expect is that "QueryAndFilterDatabaseSide" also returns one record. > > > > Best regards > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Pascal (almost) style BLOBs
An ADO.NET DataTable *can* have type information for table columns, if you add a parameter to the WriteXml method to have it write the schema along with the data. Obviously, *their* data must be typed from their source, or be inferred, for this to work. See if your provider can do this for you. If they can, you should have little problem reading it, assuming you're not using .Net yourself. > On Apr 17, 2016, at 07:02, William Drago wrote: > > All, > > Any thoughts on using the first byte of a BLOB to indicate what kind of data > that BLOB contains? > > For example: > 0 = plain old bytes > 1 = 16 bit integers > 2 = 32 bit integers > 3 = singles > 4 = doubles, etc. > > I am forced to consider this approach because my function will be receiving > blobs but will have no idea what those blobs are. However, I can ask the > sender to prepend the blob with an indicator byte to provide the necessary > information. > > In the past I have used comments in the table structure and even used custom > types (e.g. i16BLOB, for a blob that contains 16 bit ints), but in this case > I will not have access to that information. The data is coming to me in the > form of an ADO.NET DataTable which does not contain such information. > > Of course tagging the blobs like this will increase the size of the database, > but not by much, and if this "feature" is not clearly documented someone in > the future will have a very hard time figuring out why the blobs don't make > sense. > > Is there anything else I should be aware of? > > Thanks, > Bill > > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite version 1.0.100.0 released
It's *all* 1s and 0s. > On Apr 15, 2016, at 12:46, Tim Uy wrote: > > that is a lot of 1s and 0s. > >> On Fri, Apr 15, 2016 at 10:42 AM, Joe Mistachkin >> wrote: >> >> >> System.Data.SQLite version 1.0.100.0 (with SQLite 3.12.1) is now available >> on the System.Data.SQLite website: >> >> https://system.data.sqlite.org/ >> >> Further information about this release can be seen at: >> >> https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki >> >> Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you >> encounter any problems with this release. >> >> -- >> Joe Mistachkin >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query on primary key not using primary key?
Without additional information, SQLite guesses that the data_idx index will narrow down the search to about 7 entries in the table. This is, of course, a guess, but it is a reasonable guess for most indices. The primary key, even though it is unique, has an IN clause with 50 entries, it SQLite guesses it will narrow the search down to 50 entries. SQLite picks the index that leads to the least amount of searching: 7 entries versus 50. In your case, I'm guessing that data_idx is really not a very good index and might ought to be dropped for doing little more than taking up space. What does the sqlite_stat1 entry for data_idx say? I simply narrowed down an example from my application. The data_idx actually represents an index with 3 columns for a very-commonly used query in the application which wasn't intended to be used for the query provided. It sounds like the solution is to just run ANALYZE, then both queries choose the right index. I was just shocked to find the primary key not used when referencing rows by primary key. Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query on primary key not using primary key?
On 06/07/2013 12:46 PM, Simon Slavin wrote: On 7 Jun 2013, at 5:37pm, Brad House wrote: I've modified my code to run an Analyze on startup to work around this, but it obviously takes time to run and slows down startup. I can't answer your question about why this happens in the first place, but I can tell you that the results of ANALYZE are saved in the database file through closing and reopening. If you have run ANALYZE once on data which looks like the data that will be in your database in normal use, then you don't have to run it again. SQLite will continue to use that information about table sizes and 'chunkiness' when devising query plans in the future. Unfortunately we can't easily predict when analyze might be useful to run due to transformations in the data that might affect the query planner. We just temporarily added it as a workaround until we come up with a better solution which might be something more cron-like. Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query on primary key not using primary key?
I would expect all queries which specify the primary key components in the WHERE clause to use the Primary Key in the query plan, regardless of if ANALYZE has been run or not. SQLite examines many different strategies for evaluating each query. For each strategy it tries to estimate the total run-time. It then selects the strategy that gives the least run-time. Whether or not the PRIMARY KEY is used as part of that strategy is not a consideration. ANALYZE does not change this. The purpose of ANALYZE is merely to provide additional information to help SQLite give a better estimate of the run-time for each of the query strategies under consideration. I guess I just don't understand how it would come up with a run-time strategy to NOT use a primary key (or any unique index) when the WHERE clause _exactly_ matches such an index. It also seemed to 'guess' that there'd be 2 result records without ANALYZE data and thus somehow chose a non-unique index utilizing fewer columns over a unique index ... The performance penalty is huge in my example, it's the difference of ~4s vs ~0.005s. Is this really not considered an issue/bug? Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query on primary key not using primary key?
I would expect all queries which specify the primary key components in the WHERE clause to use the Primary Key in the query plan, regardless of if ANALYZE has been run or not. I would also think it would assume any index which covers the most where-clause components would be the most efficient if analyze had never been ran. This doesn't appear to be the case... Example data: CREATE TABLE data (c1 INT, c2 INT, c3 INT, c4 INT, c5 TEXT, PRIMARY KEY(c1, c2)); CREATE INDEX data_idx ON data (c1, c3, c4); INSERT INTO data VALUES(1, 1, 0, 0, "test"); ...1,000,000 records later... INSERT INTO data VALUES(1, 100, 0, 0, "test"); Then: EXPLAIN QUERY PLAN UPDATE data SET c3 = 5, c4 = 3 WHERE c1 = 1 AND c2 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50); 0|0|0|SEARCH TABLE data USING INDEX data_idx (c1=?) (~2 rows) 0|0|0|EXECUTE LIST SUBQUERY 0 If I run ANALYZE, I get the expected result afterward: EXPLAIN QUERY PLAN UPDATE data SET c3 = 5, c4 = 3 WHERE c1 = 1 AND c2 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50); 0|0|0|SEARCH TABLE data USING COVERING INDEX sqlite_autoindex_data_1 (c1=? AND c2=?) (~50 rows) 0|0|0|EXECUTE LIST SUBQUERY 0 Tested on 3.7.15, 3.7.16, 3.7.17 ... didn't go back too far to see if this issue was introduced at some point or if it has always been this way. I've modified my code to run an Analyze on startup to work around this, but it obviously takes time to run and slows down startup. Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Programming API vs console
On 27/04/13 18:07, Igor Korot wrote: CREATE TABLE playersdrafted(playerid integer, id ineteger, Don't think ineteger is what you really mean.:) Seen it twice now so guessing it's actually in the code Why you say so? It is a foreign key representation, so integer is perfectly normal column type. I think the point is that "integer" might be, but the pasted code shows "ineteger", which isn't the same thing. Does the real code have "ineteger" or "integer"? Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] To import csv file in C#
On 09/02/13 13:49, mukesh kumar mehta wrote: Is there any option to import csv file into sqlite database with the help of System.Data.Sqlite.dll. As like shell command ".import file_name table_name". SpatiaLite can do this (either as a virtual table, or an import). There are probably other extensions that can do this too. As like "bulk insert" which uses in sqlserver. As like "Load Data" which uses in mysql. This doesn't make as much sense in SQLite, because you'd be better off just creating a new SQLite database and moving that around rather than some dump format. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger blocks a single transaction?
On Monday 24 December 2012 11:04:29 Alem Biscan wrote: > Hi, > > No, i do not execute begin/commit. It is VIEW'S INSTEAD OF UPDATE TRIGGER. > I am doing a regular update to the view from C#. Another thing is that view > doesn't return any row affected value. Well it makes sense somehow.. It > cannot know how many view's visible rows were affected. It lowers the > coolnes of views and instead of trigs. You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use an INSTEAD OF trigger on the view to accomplish the same thing. Views are removed with the DROP VIEW command. [http://www.sqlite.org/lang_createview.html] Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database sharing across processes
On Saturday 07 July 2012 01:00:01 Jonathan Haws wrote: > For example, let's say I have two processes that connect to the same > database file. One process wants to read from the database, but the other > process is in the middle of a write. Does the first process pend on the > read or not? If it does not, what does it return? Is it a successful > read? What about the reverse case when the first process wants to write > but the second process is reading? SQLite has locking. See http://www.sqlite.org/lockingv3.html Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.Sqlite extension function - performing INSERT
Hi, I'm trying to provide a not-really-spatialite toolset for C# users (since they often seem to have trouble with spatialite / extension loading). No problem with SELECT from an existing database / table. However I'd like to provide the capability to perform an INSERT of a newly created geometry into an existing spatialite geometry column. I can do that if there isn't an index. If there is an index, then the insertion will invoke triggers to update the RTree index table. Those triggers use two SQL functions that are provided by libspatialite - GeometryConstraints() and RTreeAlign(). I don't have libspatialite in this example, so I need to provide those functions myself. GeometryConstraints() is no problem. RTreeAlign is OK up to the point where I need to actually update the index table. Now I'm stuck because I don't have access to the connection. http://system.data.sqlite.org/index.html/annotate?checkin=2849c1b71384d52d&filename=System.Data.SQLite/SQLiteFunction.cs indicates that this is intentional: ec237b0123 2005-03-01 rmsimpson: /// Although there is one instance of a class derived from SQLiteFunction per database connection, the derived class has no access ec237b0123 2005-03-01 rmsimpson: /// to the underlying connection. This is necessary to deter implementers from thinking it would be a good idea to make database ec237b0123 2005-03-01 rmsimpson: /// calls during processing. I can read that two ways: 1. Most implementers aren't smart enough to get that right (probably true in my case). 2. No-one is smart enough to get that right - its a "Here Be Dragons" place. However, I still need to do it (or toss the code onto the "nice idea at the time" scrapheap). Is there a workaround to get at the underlying connection from my extension function class? Is there anything to be aware of in implementing a "SQLiteFunction2" that does allow access to the underlying connection? Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!
On 04/28/2012 09:36 AM, Richard Hipp wrote: On Sat, Apr 28, 2012 at 8:24 AM, Black, Michael (IS) wrote: Should another "disadvantage" of WAL mode be added to http://www.sqlite.org/draft/wal.html Something that says rolled back transactions will cause an abort on any reads in progress if shared cache is enabled. That would be a disadvantage to shared-cache mode. The effect is the same regardless of your journal mode setting. I agree that the docs for shared-cache mode need to be updated to reflect the disadvantage which causes unexpected (non-standard) behavior, and also emphasize that you probably should not be enabling the feature unless you are on a memory-limited device due to these behavioral differences. Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!
On 04/27/2012 04:38 PM, Richard Hipp wrote: Disable shared cache mode and you should be good to go. If two database connections share the same cache, and one connection rolls back, that means it will be changing cache content out from under the other database connection, so any queries ongoing in the other connection have to abort. Two database connections in shared cache mode behave like a single database connection in many ways, especially when you are talking about the cache that they share. Disabling shared cache mode definitely appears to fix it, but the behavior is not the same as it was with 3.7.10 and shared cache enabled. With 3.7.10, I would get a locked table error on the insert, which caused the test code to roll back the insert. But when I disable shared cache mode, the insert in the test case never gets blocked, it succeeds on the first attempt ... as does the select. The insert finishes before the select finishes, but the new row does not show up in the select either (not that I'm sure if it should or not, I guess that might be a dirty read). I'm not really sure if this is fully intended behavior or not ... I'd need to think about it a little to see if it might cause issues. Also, shared cache mode just talks about schema caching and more efficient locking, don't see anything really suggesting behavioral differences like this. I'd definitely be concerned that other applications in the wild might not expect the new behavior ... especially since this seems to be a fairly significant behavioral change for a minor version bump like this. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!
Ok, I guess attachments don't come through. I've uploaded it here: http://www.brad-house.com/other/sqlite_test.c -Brad On 04/27/2012 03:50 PM, Brad House wrote: On 04/27/2012 02:00 PM, Brad House wrote: Only the connection that does the rollback has its queries aborted. That is not the behavior I am seeing in 3.7.11, but was the behavior I saw in 3.7.10. If you are seeing other connections get queries aborted, that is something new that I have not seen before and will need to investigate. Correct. If you do a ROLLBACK in the middle of a query, why would you ever want to keep going with that query? What would you expect to see? I wouldn't expect to keep going on that query. I'll try to write a test case. -Brad As promised, I've attached a test case which uses the SQLITE amalgamation. Sorry about how ugly the code is, I know it is bad, but it should prove the point. I don't know if I'd consider this a _minimal_ test case, but I tried to simulate everything we do like the options used to build the amalgamation, and the fact that we register threading callbacks. This test case creates a table, adds 100 rows, then spawns 2 threads each with their own independent db handle. One is a reader, the other is a writer. It tries to make sure the reader obtains its read lock on the table first, and cycles through the rows. The writer simultaneously tries to insert another row... I've added some synchronization between the threads on sqlite3_step() so they go back and forth (failure happens quicker this way). What you'll see happen on 3.7.11 is the writer rolls back, and all of a sudden, the reader is aborted (with message: my_sqlite3_query(): sqlite3 returned 4: abort due to ROLLBACK). On 3.7.10, the writer does not cause the reader to rollback ... just the writer keeps rolling back until the reader finishes, then the writer is able to retry and complete. When compiling, copy sqlite3.c and sqlite3.h from the amalgamation into the same directory as the source file and run (on Linux): gcc -Wall -W -o sqlite_test sqlite_test.c -lpthread -ldl (yeah, it has an #include "sqlite3.c" ... bad, but if you look at it, you'll see why). Then to run it, just run: ./sqlite_test It will create a "./db.sqlite" database. This db must be removed for each subsequent run or it will error out with a create table failure. Thanks. -Brad ___ 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] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!
On 04/27/2012 02:00 PM, Brad House wrote: Only the connection that does the rollback has its queries aborted. That is not the behavior I am seeing in 3.7.11, but was the behavior I saw in 3.7.10. If you are seeing other connections get queries aborted, that is something new that I have not seen before and will need to investigate. Correct. If you do a ROLLBACK in the middle of a query, why would you ever want to keep going with that query? What would you expect to see? I wouldn't expect to keep going on that query. I'll try to write a test case. -Brad As promised, I've attached a test case which uses the SQLITE amalgamation. Sorry about how ugly the code is, I know it is bad, but it should prove the point. I don't know if I'd consider this a _minimal_ test case, but I tried to simulate everything we do like the options used to build the amalgamation, and the fact that we register threading callbacks. This test case creates a table, adds 100 rows, then spawns 2 threads each with their own independent db handle. One is a reader, the other is a writer. It tries to make sure the reader obtains its read lock on the table first, and cycles through the rows. The writer simultaneously tries to insert another row... I've added some synchronization between the threads on sqlite3_step() so they go back and forth (failure happens quicker this way). What you'll see happen on 3.7.11 is the writer rolls back, and all of a sudden, the reader is aborted (with message: my_sqlite3_query(): sqlite3 returned 4: abort due to ROLLBACK). On 3.7.10, the writer does not cause the reader to rollback ... just the writer keeps rolling back until the reader finishes, then the writer is able to retry and complete. When compiling, copy sqlite3.c and sqlite3.h from the amalgamation into the same directory as the source file and run (on Linux): gcc -Wall -W -o sqlite_test sqlite_test.c -lpthread -ldl (yeah, it has an #include "sqlite3.c" ... bad, but if you look at it, you'll see why). Then to run it, just run: ./sqlite_test It will create a "./db.sqlite" database. This db must be removed for each subsequent run or it will error out with a create table failure. Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??
Only the connection that does the rollback has its queries aborted. That is not the behavior I am seeing in 3.7.11, but was the behavior I saw in 3.7.10. If you are seeing other connections get queries aborted, that is something new that I have not seen before and will need to investigate. Correct. If you do a ROLLBACK in the middle of a query, why would you ever want to keep going with that query? What would you expect to see? I wouldn't expect to keep going on that query. I'll try to write a test case. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??
On 04/26/2012 04:38 PM, Jos Groot Lipman wrote: As far as I understand this means: you will not see changes made by other connections (committed or uncommited) after your transaction started. If another connections commits a change, you will not see it. I would expect: If another connections rollbacks the change, you will not see it either. Why whould anyone want an aborted read-transaction in this case? I would agree ... I'd like to hear the other side of the story here so we understand why this change was made if it was indeed intentional. What purpose does this behavior serve? Not saying it is wrong at this point, just lacking information. Also would need to understand the scope of this behavior. Does that mean if any connection rolls back that immediately all other connections abort? Or is it only one very specific case that this occurs? Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??
On 04/26/2012 05:11 PM, Richard Hipp wrote: On Thu, Apr 26, 2012 at 4:38 PM, Jos Groot Lipman wrote: From the docs: 'The default isolation level for SQLite is SERIALIZABLE' As far as I understand this means: you will not see changes made by other connections (committed or uncommited) after your transaction started. My understanding was that Brad is using a single database connection shared between both threads. You are correct that if he had been using separate database connections in each thread, this problem would not come up. As stated in my original request: "I've got 2 threads with different connections to the same database." The 2 threads are explicitly not sharing the same connection. We are using a connection "pool" where each thread that needs DB access will pull an available connection out of the pool. Also, we have set sqlite3_enable_shared_cache(1) and use WAL mode. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??
On 04/26/2012 04:09 PM, Black, Michael (IS) wrote: Would WAL mode prevent this? We're using WAL mode :) -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??
On 04/26/2012 03:44 PM, Richard Hipp wrote: On Thu, Apr 26, 2012 at 3:34 PM, Brad House wrote: I've got 2 threads with different connections to the same database. This is approximately what I am seeing: Thread 1: SELECT bar,baz FROM foo WHERE ...; Thread 2: BEGIN IMMEDIATE TRANSACTION; INSERT INTO foo VALUES (...); **sqlite3_step returns 6 (SQLITE_LOCKED) ROLLBACK TRANSACTION Thread 1: **sqlite3_step returns 4 (SQLITE_ABORT): abort due to ROLLBACK So why, if Thread 2 rolls back does Thread 1 get aborted? A rollback deletes content out from under other queries. So if you have a query pending in thread 1 and thread 2 tries to rollback, there are two options: (A) The rollback fails (B) The pending query is aborted. It used to be that we did (A). (If you had checked the return codes from your "ROLLBACK TRANSACTION" statement you would have been seeing it fail.) The latest code does (B) instead. Your software depends upon behavior (A). Other users prefer behavior (B). I don't know how to make everyone happy I just ran another test, ensuring I check return codes specifically on the ROLLBACK (And I was), and from what I can tell, I'm not getting a failure on either either 3.7.11 or 3.7.10, it is returning SQLITE_OK. NOTE: I'm using sqlite3_exec() to send the ROLLBACK... Rollback is actually the only time we don't use sqlite3_prepare/_step/etc. So I'm not exactly sure what you mean by I would see it failing. What logic should we be using if we receive an SQLITE_LOCKED and we should not ROLLBACK? Do we simply perform an sqlite3_reset() then retry the sqlite3_step() ... and keep doing that until it succeeds after the SELECT has released its locks (I'm assuming if we do that the select WILL succeed, right?)? The sqlite3_step() documentation provides no guidance on what to do if SQLITE_LOCKED is returned ... we had assumed the same guidance as SQLITE_BUSY applied which says explicitly to perform a ROLLBACK. That said, I'm still struggling to see the merit of a rollback in one thread causing an abort in another thread. We always took rollback to mean we are conceding execution to the other thread, not the other way around. We use MySQL, Oracle, Microsoft SQL Server, PostgreSQL ... never seen such a behavior. Sorry if I'm being dense here. Thanks! -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??
We just noticed a behavioral change in SQLite 3.7.11 that caused a regression in our software. This change did not exist in 3.7.10. Looking back at the changelog, I notice this: "Pending statements no longer block ROLLBACK. Instead, the pending statement will return SQLITE_ABORT upon next access after the ROLLBACK." But I do not know exactly what that means or why that change was made. I've got to assume this is not an intended behavioral change as it is very significant at least from our standpoint as we've been using the same logic since SQLite 3.3. I've got 2 threads with different connections to the same database. This is approximately what I am seeing: Thread 1: SELECT bar,baz FROM foo WHERE ...; Thread 2: BEGIN IMMEDIATE TRANSACTION; INSERT INTO foo VALUES (...); **sqlite3_step returns 6 (SQLITE_LOCKED) ROLLBACK TRANSACTION Thread 1: **sqlite3_step returns 4 (SQLITE_ABORT): abort due to ROLLBACK So why, if Thread 2 rolls back does Thread 1 get aborted? This is where the 3.7.11 logic doesn't make sense, Thread 2 rolls back specifically so Thread 1 can continue since it "got there first". I can probably write up a test case if necessary, but wanted to pass it by you all first. Thanks! -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
On Tue, Oct 18, 2011 at 8:04 AM, Igor Tandetnik wrote: > Frank Missel wrote: >> I think that the sqlite-users e-mail list has enough traffic to warrant a >> proper forum. > > For what it's worth, I'm using GMane (http://gmane.org/), which is a mailing > list-to-NNTP > gateway and happens to carry this list. I'm old-fashioned enough to believe > that an NNTP > newsgroup *is* the proper forum. Can't stand modern Web-based forum > interfaces. + a billion. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
> Mmmm. Looks like there's no elegant way to do it. I looked into this a couple > of years ago when designing the setup. So: > > 1) Leave things as they are. Downside is the unexplained error every few > months and it's a slightly clumsy method. Upside is if the schema changes > there's no extra work to do. > > 2) Gerry suggests listing out the columns explicitly. Upside is this > simplifies the move operation, downside is extra maintenance. I suppose I > could get clever and store the column names in a Settings database I already > have and use that to generate the SQL. > > 3) Use your suggestion. Upsides as you describe - simple move. Downside is an > extra column with the same value in it for all rows. The irritating part is > that there is a unique value for each database stored in another table in the > same db. But it appears I can't do: PRIMARY KEY (OTHERTABLE.ORIG, ABSID) > which would have been nice. 4. Use one of the myriad code generators available on the web to generate the code for this specific task. When the schema changes, regenerate the code. 5. Dynamically build your SQL statement based on the schema as it exists in the copy of the database you're using. This has the advantage of allowing the same code to service multiple database versions. Reading the schema and building the query could be done when the DB is opened and saved, perhaps even in the DB itself, rather than doing every time the query is called. /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compare time stamp
> • TEXT as ISO8601 strings ("-MM-DD HH:MM:SS.SSS").
I personally have had the best luck with this storage mechanism. I'm
lazy, and my platforms all understand this format readily, and it has
the advantage of being human readable as well.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] saving pragma states in database
You could always create a table that stores the pragma values in which you're interested, then have code that checks on start up to set those pragmas to those values. On Tue, Aug 23, 2011 at 1:24 PM, Erik Lechak wrote: > Hello all, > > Is there a way to save pragma states to the database? > > I have a delete trigger that I would like to fire off on a delete ( > actually a replace), but I need the database to maintain the "pragma > recursive_triggers=1" state. Otherwise the trigger does not get > fired. I would just like the database to remember that I set the > state to true. > > Here is some example code. If it works when "pragma > recursive_triggers=1", but not when "pragma recursive_triggers=0": > > > drop table abc; > drop table abc_history; > > create table if not exists abc (id integer primary key,a text, b text, c > text); > create table if not exists abc_history as select * from abc where rowid=-1; > > create trigger if not exists abc_delete_trigger > before delete on abc > begin > insert into abc_history select * from abc where rowid=old.rowid ; > end; > > create trigger if not exists abc_update_trigger > before update on abc > begin > insert into abc_history select * where rowid=old.rowid ; > end; > > insert into abc values(1,'xa','y','z'); > insert into abc values(2,'xb','y','z'); > insert into abc values(3,'xc','y','z'); > insert into abc values(4,'xd','y','z'); > > replace into abc values(1,'xg','y','z'); > > Thanks, > Erik Lechak > ___ > 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] EXT :Re: Last record in db
On Mon, Aug 22, 2011 at 10:08 AM, Black, Michael (IS) wrote: > Brad got it: >> select * from t1 where rowid = max( rowid ) ; >> Error: misuse of aggregate function max() > sqlite> select * from t1 where rowid = (select max(rowid) from t1); > 3|three > > Why is max(rowid) a "misuse". Seems perfectly logical to me. Not for an > update but should work for select. I'm guessing that "max(rowid)" all by itself is either evaluating "rowid" as a variable or value independent of a table, or failing entirely because there is no context for evaluating rowid in the failing instance. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
What happens when you do: select * from t1 where rowid = (select max( rowid ) from t1); or select * from t1 where rowid in (select max( rowid ) from t1); On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley wrote: > > Black, Michael (IS) wrote: > >> select * from table where rowid=max(rowid); > > $ sqlite3 m2d1.sql3 > -- Loading resources from /home/sk/.sqliterc > SQLite version 3.7.3 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > >> .tables > t1 t2 t3 > >> .schema t1 > CREATE TABLE t1(id INT,data TEXT); > >> select * from t1 ; > id data > -- -- > 1 one > 2 two > 3 tre > >> select * from t1 where rowid = max( rowid ) ; > Error: misuse of aggregate function max() > >> select max( rowid ) from t1 ; > max( rowid ) > > 3 > > > -- > Stanley C. Kitching > Human Being > Phoenix, Arizona > > > ___ > 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] Cannot load DLLs using NUnit with SQLite
Hello, My situation is that I am setting up our testing project for unit tests using NUnit. The requirements are developers must be able to run the tests from within visual studio and through the nunit exe, and also our continuous integration server must be able to run the unit tests after a check in. I found 4 versions of the binaries available for .net 4.0 These would be: mixed mode x86, non mixed mode x86, mixed mode x64, non mixed mode x64 No matter which one I use, I always get the same error: Could not find any resources appropriate for the specified culture or the neutral culture. Make sure "System.Data.SQLite.SR.resources" was correctly embedded or linked into assembly "System.Data.SQLite" at compile time, or that all the satellite assemblies required are loadable and fully signed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot add interop assembly
So many replies! Hard to reply to them all, but I understand the issue now. I didn't know it was native. >> Their beliefs do not impact how the code needs to be deployed in order to function properly. It does because it's my director. For us to deploy DLLs they "have to be added as a reference". They do not allow for deploy scripts to push DLLs to folders unless part of the project. But since it is an unmanaged DLL, I'll just tell them "either drop SQLite or let me copy as content". I'm sure they'll let me keep SQLite =) >> So the company does not use any purely native DLLs in their .NET apps? Correct, the company has absolutely 0 DLLs required to copy as content. >> Adding the non-mixed mode assembly to the GAC is problematic and not recommended. Okay, I won't add it to the GAC then. I'll just argue for my point of having it in a Lib folder and copy as content. Thank you for your help! I'll now be signing off. Brad -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Thursday, July 21, 2011 7:30 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Cannot add interop assembly Corr > > Yeah that is what I did in the interim to get it to work. > Ok, good. > > The issue being my company does not believe in the DLLs being in a lib folder inside a project. > Their beliefs do not impact how the code needs to be deployed in order to function properly. > > Instead they have a Library folder at the same level of the solution file, and everything is added by ref. > That will work for managed DLLs; however, the SQLite.Interop.dll is a purely native DLL. It cannot be added as a managed reference because it does not contain any managed code or metadata. > > So unless I add it by ref I have to break company coding standards. > So the company does not use any purely native DLLs in their .NET apps? > > Unless it is in the bin, SQLite errors saying it cannot find the interop. > Yes, this is why I suggested that it be added to the project as a "content" file and copied into the "bin" folder for deployment purposes. > > Guess I could add it to the GAC but that also breaks my companys coding standards. > Adding the non-mixed mode assembly to the GAC is problematic and not recommended. > > Isn't it odd that I cannot just add it as a ref? > No, it's not odd, it's a pure native DLL. Adding a reference requires the DLL to be a managed assembly. -- 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
Re: [sqlite] Cannot add interop assembly
Hello, Yeah that is what I did in the interim to get it to work. The issue being my company does not believe in the DLLs being in a lib folder inside a project. Instead they have a Library folder at the same level of the solution file, and everything is added by ref. So unless I add it by ref I have to break company coding standards. Unless it is in the bin, SQLite errors saying it cannot find the interop. Guess I could add it to the GAC but that also breaks my companys coding standards. Isn't it odd that I cannot just add it as a ref? A reference to 'C:\...\SQLite.Interop.dll' could not be added. Please make sure that the file is accessible, and that it is a valid assembly or COM component. Permissions are also ok, user has access to modify/read/etc. Thanks, Brad -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Thursday, July 21, 2011 6:29 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Cannot add interop assembly You should not need to add the interop assembly (i.e. SQLite.Interop.dll) as an actual "reference"; however, you may want to add it as a "content" file to your project and set it to be copied to the project output directory. You will want to add a reference to the [managed] "System.Data.SQLite" assembly instead. -- 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] Cannot add interop assembly
Hey, So I am running .Net 4.0 so I downloaded the binarys for 64 bit .Net 4.0, the non-mixed mode ones. If I try adding a reference to the interop visual studio says it can't add it as a reference. Anyone know of a way I can fix this? Thanks, Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_CANTOPEN
Dear SQL gurus - I have been using SQLite for several years and love it. Currently it is being used on a small embedded Linux device. Once in a while when we power on the device and start my application, this query fails: "SELECT storage_enum, mode, GPSDisplayDMS, UserRealID, UserEffID FROM Settings;" with the reason "unable to open database file" I suspect something didn't get closed properly when the unit powered off. Weird: 1. If I copy the db file to my PC, it opens and reads just fine. 2. If I just make a copy of it on the device itself (same folder and everything), then I can query the *copy* of the file just fine. sqlite3_open() always succeeds and returns SQLITE_OK. The failure is when sqlite3_exec() tries to run. Then we sqlite3_close() the db. I looked at the source code to see where CANTOPEN is returned and thought the open() was most likely, but a small prg to see if the db would open() always succeeds. open() at least for modes RDWR and RDONLY always succeed on the "unopenable" db file: fd = open(db_filename, O_RDWR); fd = open(db_filename, O_RDONLY); Google and Sqlite.org suggested: sqlite3_file_control(db, "main", SQLITE_LAST_ERRNO, &errno_value); so I added that after the call to sqlite3_exec() failed. That returned SQLITE_OK, but to my dismay, errno_value was 0 when that returned! Then I tried: sqlite3_config(SQLITE_CONFIG_LOG, &log_func, (void *)dbname) which returns a several different non-zero (error) values dep on where I call it, so logging is out too. There's plenty of disk space left and I don't see anything wrong with permissions (the copy in the same folder works fine). Any suggestions, or general thoughts on recovery? Thanks a lot Brad Brancke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.7.6.1 build error on platforms without pread/pwrite
It appears there is a bad check when setting up the
pread (and pwrite) callback:
#if defined(USE_PREAD) || defined(SQLITE_ENABLE_LOCKING_STYLE)
{ "pread",(sqlite3_syscall_ptr)pread, 0 },
#else
{ "pread",(sqlite3_syscall_ptr)0, 0 },
#endif
SQLite forces SQLITE_ENABLE_LOCKING_STYLE to _always_ be defined:
#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
# if defined(__APPLE__)
#define SQLITE_ENABLE_LOCKING_STYLE 1
# else
#define SQLITE_ENABLE_LOCKING_STYLE 0
# endif
#endif
So locking style is defined as '0', so I'm thinking the check
_should_ have been:
#if defined(USE_PREAD) || SQLITE_ENABLE_LOCKING_STYLE
There are 2 other instances of checks for
defined(SQLITE_ENABLE_LOCKING_STYLE)
that should also be changed in the code.
If you want, I can provide a patch against trunk to fix the build
issue. I've currently, though, only modified the amalgamation 3.7.6.1
build in my local repo.
Thanks.
-Brad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.7.6.1 build error on platforms without pread/pwrite
It appears there is a bad check when setting up the
pread (and pwrite) callback:
#if defined(USE_PREAD) || defined(SQLITE_ENABLE_LOCKING_STYLE)
{ "pread",(sqlite3_syscall_ptr)pread, 0 },
#else
{ "pread",(sqlite3_syscall_ptr)0, 0 },
#endif
SQLite forces SQLITE_ENABLE_LOCKING_STYLE to _always_ be defined:
#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
# if defined(__APPLE__)
#define SQLITE_ENABLE_LOCKING_STYLE 1
# else
#define SQLITE_ENABLE_LOCKING_STYLE 0
# endif
#endif
So locking style is defined as '0', so I'm thinking the check
_should_ have been:
#if defined(USE_PREAD) || SQLITE_ENABLE_LOCKING_STYLE
There are 2 other instances of checks for
defined(SQLITE_ENABLE_LOCKING_STYLE)
that should also be changed in the code.
If you want, I can provide a patch against trunk to fix the build
issue. I've currently, though, only modified the amalgamation 3.7.6.1
build in my local repo.
Thanks.
-Brad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Best Query And Index Structure
On Fri, Oct 16, 2009 at 3:07 PM, Pavel Ivanov wrote: > Yes, that's right. > Ta for the help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Best Query And Index Structure
On Fri, Oct 16, 2009 at 2:48 PM, Pavel Ivanov wrote: > Yes, pretty interesting results. I didn't expect that. :) > Query plan seems to suggest that SQLite executes query not in the way > you said but first takes tit table, joins epgdata to it and then joins > tit1 and tit2 to it. So it should be executed faster than you > thought... > > I've played with your queries a bit and found the only way to force > SQLite to execute query the way I've intended - to change table > epgdata so that id is not "integer primary key" but has a non-unique > (!) index on it. :) But of course that will not mean that query > execution would be the fastest in this case. > I'm surprised and impressed with SQLite's optimizer. :) > > BTW, to make your query fastest you need index on (lang, epgdata_id, > tittext) instead of (lang, tittext, epgdata_id). Even for this > particular query tittext shouldn't be in the index at all. > > > Pavel I think LIKE queries can use an index if they are prefix searches bar LIKE "FOO%" can use an index but bar LIKE "%FOO%" is that right? B ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Best Query And Index Structure
On Fri, Oct 16, 2009 at 2:08 PM, Brad Phelan wrote:
> On Fri, Oct 16, 2009 at 1:39 PM, Pavel Ivanov wrote:
>>> So if
>>> x has a very large range and a small probability of a match then
>>> we still have to do a full scan of 10,000 rows of A.
>>>
>>> Is there a better way to construct the query and or indexes so
>>> the result is faster.
>>
>> If your x has a small selectivity in B disregarding of A, i.e. for
>> each x you have pretty small amount of rows in B, then I'd suggest
>> instead of your index create these two:
>>
>> CREATE INDEX index_B on B (x, A_id);
>> CREATE INDEX index_A on A (id);
>>
>> And write your select in this way:
>>
>> select distinct *
>> from A join
>> (select B0.A_id as A_id
>> from B B0, B B1
>> where B0.x = 10
>> and B1.x = 20
>> and B0.A_id = B1.A_id) B2 on B2.A_id = A.id
>>
>>
>
> I've tried your pattern on my production code with some interesting
> results. The original pattern is below followed by your suggestion.
> However in this case I have used three terms. There is a LIKE "%FOO%"
> term in there which I really should replace with FTS3.
>
> select count(*) from epgdata
> JOIN tit AS tit0
> ON tit0.epgdata_id = epgdata.id
> AND ( (tit0.lang = "deu") AND ((tit0.tittext) LIKE ("%die%")) )
> JOIN tit AS tit1
> ON tit1.epgdata_id = tit0.epgdata_id
> AND ( (tit1.lang = "deu") AND ((tit1.tittext) LIKE ("%der%")) )
> JOIN tit AS tit2
> ON tit2.epgdata_id = tit1.epgdata_id
> AND ( (tit2.lang = "deu") AND ((tit2.tittext) LIKE ("%zu%")) )
>
> ---
>
> select count(*) from epgdata join
> ( select tit0.epgdata_id as epgdata_id
> from tit as tit0, tit as tit1, tit as tit2
> where tit0.lang="deu" and tit0.tittext LIKE "%die%"
> and tit1.lang="deu" and tit1.tittext LIKE "%der%"
> and tit2.lang="deu" and tit2.tittext LIKE "%zu%"
> and tit0.epgdata_id = tit1.epgdata_id
> and tit0.epgdata_id = tit2.epgdata_id
> ) as foo on foo.epgdata_id = epgdata.id
>
>
> generates almost identical sqlite bytecode using the EXPLAIN keyword. Some
> of the register numbers are different but the code structure is word for
> word the same. Unfortunately I can't make head or tail of the codes.
>
> The query plan for both of them is
>
> 0|1|TABLE tit AS tit0 WITH INDEX tit__lang__tittext__epgdata_id__
> 1|0|TABLE epgdata USING PRIMARY KEY
> 2|2|TABLE tit AS tit1 WITH INDEX tit__epgdata_id__
> 3|3|TABLE tit AS tit2 WITH INDEX tit__epgdata_id__
>
> I have indices
>
> (epgdata_id) -> tit__epgdata_id__
>
> and
>
> (lang, tittext, epgdata_id) -> tit__lang__tittext__epgdata_id__
>
> It seems that SQLite maps both queries to the same internal
> representation. Curious!!
>
> B
>
Actually as I look into it I am not surprised that both queries
translate to the same code. First the ON clause and the WHERE clause
in SQLite are equivalent for inner joins. As well
select count(*) from A, B
where A.id = B.A_id
is identical to
select count(*) from A
join B on A.id = B.A_id
The question seems to be what is the best order to run the join loop
in. From A to B or B to A. SQlite seems to have it's own idea on how
to do this. As can be seen from my production query the loop was
reordered. Perhaps I don't need to worry. All I need to make sure is
that I have the correct indices available to let SQLite run the loop
in whichever order it sees fit. It is all a bit black magic to me :)
Brad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Best Query And Index Structure
>> So if
>> x has a very large range and a small probability of a match then
>> we still have to do a full scan of 10,000 rows of A.
>>
>> Is there a better way to construct the query and or indexes so
>> the result is faster.
>
> If your x has a small selectivity in B disregarding of A, i.e. for
> each x you have pretty small amount of rows in B, then I'd suggest
> instead of your index create these two:
>
> CREATE INDEX index_B on B (x, A_id);
> CREATE INDEX index_A on A (id);
>
> And write your select in this way:
>
> select distinct *
> from A join
> (select B0.A_id as A_id
> from B B0, B B1
> where B0.x = 10
> and B1.x = 20
> and B0.A_id = B1.A_id) B2 on B2.A_id = A.id
>
>
I've tried your pattern on my production code with some interesting
results. The original pattern is below followed by your suggestion.
However in this case I have used three terms. There is a LIKE "%FOO%"
term in there which I really should replace with FTS3.
select count(*) from epgdata
JOIN tit AS tit0
ON tit0.epgdata_id = epgdata.id
AND ( (tit0.lang = "deu") AND ((tit0.tittext) LIKE ("%die%")) )
JOIN tit AS tit1
ON tit1.epgdata_id = tit0.epgdata_id
AND ( (tit1.lang = "deu") AND ((tit1.tittext) LIKE ("%der%")) )
JOIN tit AS tit2
ON tit2.epgdata_id = tit1.epgdata_id
AND ( (tit2.lang = "deu") AND ((tit2.tittext) LIKE ("%zu%")) )
---
select count(*) from epgdata join
( select tit0.epgdata_id as epgdata_id
from tit as tit0, tit as tit1, tit as tit2
where tit0.lang="deu" and tit0.tittext LIKE "%die%"
and tit1.lang="deu" and tit1.tittext LIKE "%der%"
and tit2.lang="deu" and tit2.tittext LIKE "%zu%"
and tit0.epgdata_id = tit1.epgdata_id
and tit0.epgdata_id = tit2.epgdata_id
) as foo on foo.epgdata_id = epgdata.id
generates almost identical sqlite bytecode using the EXPLAIN keyword. Some
of the register numbers are different but the code structure is word for
word the same. Unfortunately I can't make head or tail of the codes.
The query plan for both of them is
0|1|TABLE tit AS tit0 WITH INDEX tit__lang__tittext__epgdata_id__
1|0|TABLE epgdata USING PRIMARY KEY
2|2|TABLE tit AS tit1 WITH INDEX tit__epgdata_id__
3|3|TABLE tit AS tit2 WITH INDEX tit__epgdata_id__
I have indices
(epgdata_id) -> tit__epgdata_id__
and
(lang, tittext, epgdata_id) -> tit__lang__tittext__epgdata_id__
It seems that SQLite maps both queries to the same internal
representation. Curious!!
B
___
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] Fwd: Best Query And Index Structure
On Fri, Oct 16, 2009 at 1:39 PM, Pavel Ivanov wrote:
>> So if
>> x has a very large range and a small probability of a match then
>> we still have to do a full scan of 10,000 rows of A.
>>
>> Is there a better way to construct the query and or indexes so
>> the result is faster.
>
> If your x has a small selectivity in B disregarding of A, i.e. for
> each x you have pretty small amount of rows in B, then I'd suggest
> instead of your index create these two:
>
> CREATE INDEX index_B on B (x, A_id);
> CREATE INDEX index_A on A (id);
>
> And write your select in this way:
>
> select distinct *
> from A join
> (select B0.A_id as A_id
> from B B0, B B1
> where B0.x = 10
> and B1.x = 20
> and B0.A_id = B1.A_id) B2 on B2.A_id = A.id
>
>
I've tried your pattern on my production code with some interesting
results. The original pattern is below followed by your suggestion.
However in this case I have used three terms. There is a LIKE "%FOO%"
term in there which I really should replace with FTS3.
select count(*) from epgdata
JOIN tit AS tit0
ON tit0.epgdata_id = epgdata.id
AND ( (tit0.lang = "deu") AND ((tit0.tittext) LIKE ("%die%")) )
JOIN tit AS tit1
ON tit1.epgdata_id = tit0.epgdata_id
AND ( (tit1.lang = "deu") AND ((tit1.tittext) LIKE ("%der%")) )
JOIN tit AS tit2
ON tit2.epgdata_id = tit1.epgdata_id
AND ( (tit2.lang = "deu") AND ((tit2.tittext) LIKE ("%zu%")) )
---
select count(*) from epgdata join
( select tit0.epgdata_id as epgdata_id
from tit as tit0, tit as tit1, tit as tit2
where tit0.lang="deu" and tit0.tittext LIKE "%die%"
and tit1.lang="deu" and tit1.tittext LIKE "%der%"
and tit2.lang="deu" and tit2.tittext LIKE "%zu%"
and tit0.epgdata_id = tit1.epgdata_id
and tit0.epgdata_id = tit2.epgdata_id
) as foo on foo.epgdata_id = epgdata.id
generates almost identical sqlite bytecode using the EXPLAIN keyword. Some
of the register numbers are different but the code structure is word for
word the same. Unfortunately I can't make head or tail of the codes.
The query plan for both of them is
0|1|TABLE tit AS tit0 WITH INDEX tit__lang__tittext__epgdata_id__
1|0|TABLE epgdata USING PRIMARY KEY
2|2|TABLE tit AS tit1 WITH INDEX tit__epgdata_id__
3|3|TABLE tit AS tit2 WITH INDEX tit__epgdata_id__
I have indices
(epgdata_id) -> tit__epgdata_id__
and
(lang, tittext, epgdata_id) -> tit__lang__tittext__epgdata_id__
It seems that SQLite maps both queries to the same internal
representation. Curious!!
B
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Best Query And Index Structure
Hi all, I am curious on how to design a schema and indexes to best fit the following pattern. My data is tree like and stored normalized in the database. CREATE TABLE A ( id INTEGER PRIMARY ) CREATE TABLE B ( id INTEGER PRIMARY , A_ID INTEGER # Foreign key to A , x INTEGER ) Now I wish to make queries such as. All A where any A/B.x = 10 and any A/B.x = 20 This can be coded trivially in SQL as select distinct * from A join B as B0 on A.id = B0.A_id and B0.x = 10 join B as B1 on A.id = B1.A_id and B0.x = 20 My guess is that the suitable index to create is CREATE INDEX index on B ( A_id , x ) However my limited understanding of how SQLite works suggests that this will be implemented as for a in A: for b1 in B where b1.A_id = a.id and b1.x = 10: for b2 in B where b2.A_id = a.id and b2.x = 20: yield a Here the branching factor is quite small. There will be no more than 20 or so B's for every A but there may be about 10,000 A's. So if x has a very large range and a small probability of a match then we still have to do a full scan of 10,000 rows of A. In this case the index helps the joining but the search is still O(N) Is there a better way to construct the query and or indexes so the result is faster. Regards Brad Phelan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > >> I definitely don't agree here as we're talking about these additional >> locks existing _only_ in memory, not on disk. > > Which requires client/server architecture. Which SQLite3 doesn't > have. Once you require concurrent access features in your DBMS (i.e. > multi-user, lots of locking) the things you nned to implement start to > be easier with a client/server architecture, whether it's a standalone > client application that must be launched manually or just a unix-style > daemon running in the background which is launched automatically when > needed and quits when nothing has used it in a while. Uhh, no it doesn't. Unless your definition of client/server is completely different than mine in the context of what we're talking about here. In this context, the 'client and server' would share the same address space (they're the same process!), hence there is no client/server separation. It would mean the exact same amount of process separation as SQLite currently employs. It would just have additional code to optimize for concurrent writes by multiple threads just as sqlite3_enable_shared_cache() does for reads: http://sqlite.org/c3ref/enable_shared_cache.html http://sqlite.org/sharedcache.html What I'm suggesting would be an extension of that shared cache, but for managing access for writes. We're just talking finer-grained locks here ... we're not talking some elaborate scheme which requires IPC and client/server communication. It doesn't need to spawn off any other daemon process here, that would be just plain stupid to do within the context of what I'm talking about. I'm just wondering if you're confused on the difference between a thread and a process One of these days I just need to write a patch to do this and see if it gets accepted. Unfortunately, my spare time these days is around zilch. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
>> It could probably benefit a large number of integrations to >> have finer grained locking even if it could not be implemented for all >> integration types. > > It makes the system a great deal slower, since you need to keep > checking all the levels of lock you have implemented. For instance, > > UPDATE props SET colour='black',condition='poor' WHERE > description='fake sword' > > needs to check for locks on the file, the record, and three fields. > That's five operations before you can even start to modify the data. > Could easily double the amount of time it takes to perform the > update. And if you implement column locks there are even more. And > implementing fine-grain locks leads to lock-contention: if someone > locks a record and you try to lock a field in that record, what should > happen ? Now before trying to modify data and having locks interfere, > you're trying to modify locks and having lock-interaction interfere. I definitely don't agree here as we're talking about these additional locks existing _only_ in memory, not on disk. There'd be no reason to implement on-disk locking or even notifying the OS of sections of the file which are locked since we're only talking about multiple threads in the same process. Any other process would hit the OS file lock and be forced to wait. The overhead of in-memory locking going to be extremely minimal, and only affect those who specifically enable this fine-grained locking. That said, I do think the on-disk journal file format might need to change to accomplish even this, and I think that is probably the biggest show stopper. I'm not suggesting that this would be easy to implement either, and yes, you'd need to figure out if SQLite will block on a lock, or return BUSY, but by limiting the implementation scope to multithreaded applications, it at least makes the implementation feasible, and would provide great benefit to many users of SQLite. In our own synthetic benchmark of our application, which is extremely write-heavy, we see roughly 15 txns/sec with SQLite, but 1000 txns/sec with multiple connections to MySQL. Biggest difference here is MySQL allows multiple writers. (That said, I need to actually try to benchmark MySQL with only 1 connection to be able to normalize those numbers a bit). Typically though, those with large transaction volumes are going to go to some other database besides SQLite for other features of a server-based engine, like replication. Not complaining here though, SQLite definitely fits the bill for the default database of our application. What it does, it does well! -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
>> Fine for me. It seems to be everybody else that wants their favorite >> feature imbedded in the core :-) >> >> Fine grained locking would be a great "asset" I feel. Notice I did >> not >> request a "feature." > > > On my to-do list is to write a paper that explains why fine-grain > locking is not practical without either (1) a dedicated server process > to manage the locks or (2) enhancements to OS locking primitives that > are not currently available on any OS that I am aware of. There is a > widely held belief that since OSes provide byte-level locking of files > it should be a simple matter to provide row-level locking in a > serverless database engine. The proposed paper will explain why that > belief is incorrect. It could probably benefit a large number of integrations to have finer grained locking even if it could not be implemented for all integration types. I could see the implementation of a per-process lock (full DB lock), but with finer-grained row-level locking on a per-thread basis within the same application. Probably enabled through the use of sqlite3_enable_shared_cache(). This would actually be of great benefit to our use of SQLite which is strictly from a single multi-threaded process, no other application or process would touch the database simultaneously, just multiple threads from a single process would interact through multiple 'connections'. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_MUTEX_APPDEF doc issue
As of SQLite 3.6, SQLITE_MUTEX_APPDEF is no longer valid, but it is still referenced here: http://www.sqlite.org/c3ref/mutex_alloc.html Probably want to make that change in the docs, specifically the section which states: "If SQLite is compiled with the SQLITE_MUTEX_APPDEF preprocessor macro defined (with "-DSQLITE_MUTEX_APPDEF=1"), then no mutex implementation is included with the library." Should probably read: "If SQLite is compiled with the SQLITE_MUTEX_NOOP preprocessor macro defined (with "-DSQLITE_MUTEX_NOOP"), then a stub implementation will be included with the library." The remaining portion of that paragraph (not shown here) appears to be correct and relevant. This actually caught me off guard when migrating from 3.5 to 3.6, luckily this page: http://www.sqlite.org/35to36.html mentioned that SQLITE_MUTEX_APPDEF is no longer recognized. Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get PRIMARY KEY of newly INSERT INTO record
> Cool! That worked. The VB6 wrapper has a LastInsertAutoID that I never paid > attention / saw before. Be sure that it's doing what you think it is. If it's a general purpose wrapper, then it might not. If it's a SQLite specific wrapper, it might, but even if it's wired up correctly, you have to be careful to use it in the correct way. If more than one insert, for instance, to child tables, happens in a query, only the last id inserted will be returned. You don't get a stack of ids that were inserted since the last call. :) /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get PRIMARY KEY of newly INSERT INTO record
> 'Create the SQL command. > strSQLCommand = "INSERT INTO " + strDBTable + " VALUES (null" + > strUpdate + ");" http://www.sqlite.org/lang_corefunc.html Use last_insert_rowid() as a second statement in your query: 'Create the SQL command. strSQLCommand = "INSERT INTO " + strDBTable + " VALUES (null" + strUpdate + ");" strSQLCommand = strSQLCommand + "select last_insert_rowid();" 'DATABASE: Execute the query. bSuccess = Me.DBConnection.Execute(strSQLCommand) Though, you'll need to examine the resulting recordset, rather than check for a boolean value. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] set a Trigger on select
> Among the whole paramitration there are some "parameters" which are > actually reads to specific hardware addresses. If these values are to be read directly from the hardware, why involve the database at all? Why not simply have your application code read them directly, instead of trying to figure out a way for the database to do it? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is UPDATE with JOIN supported?
> I am trying to do an UPDATE of one table based on the aggregate > results of the different table. How can I do it in SQlite please? What have you tried that didn't work? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Group by week
Something like this seems the most obvious way:
SELECT strftime('%Y%W', date_col) AS w, other_data FROM my_table GROUP
BY w
See http://sqlite.org/lang_datefunc.html for usage. That doc also
includes some caveats related to precision and locale, etc.
Brad
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Longbotham
Sent: Thursday, February 05, 2009 1:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Group by week
Hi Moshe,
There are probably a number of ways to do this. One possibility would
be
to store your dates in binary date value within the sqlite database,
which I
believe is so many seconds since some date in 1969. You could then use
a
constant such as:
#define SecondsInWeek 60*60*24*7
You could then use the starting date also in this format to select where
the
value in the database was greater than the starting day and less than
the
starting day plus SecondInWeek. To select the next week add
SecondsInWeek
to your starting value and reiterate...
-Tom
On Thu, Feb 5, 2009 at 8:43 AM, John Stanton
wrote:
> You need a function which gives the week number. Note that this is
> calculated differently in the USA and Europe., so you need to use the
> correct rules to write the function.
>
> Moshe Sharon wrote:
> > Hi
> >
> > How can I select group by week
> >
> > moshe
> > ___
> > 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] drop table question ?
>> >> For my own edification, why the "order by 1" clause? >> > >> > To sort them in ascending order of table name, which might make >> > old-fashioned capers like visual scrutiny a little easier. >> >> OK then, why would one not use the column name? > > It does. No, I meant why not use the column name, instead of the number? Why use "order by 1" instead of "order by name"? I understand that "1" refers to the "'drop table ' || name || ';'" result column, but why bother? It seems simpler to me to use the actual column name. Is there a performance thing involved here, or simply a preference? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
On Tue, Feb 3, 2009 at 10:28 AM, Jay A. Kreibich wrote:
> On Tue, Feb 03, 2009 at 08:37:10AM -0500, Brad Stiles scratched on the wall:
>> >> For my own edification, why the "order by 1" clause?
>> >
>> > To sort them in ascending order of table name, which might make
>> > old-fashioned capers like visual scrutiny a little easier.
>>
>> OK then, why would one not use the column name?
>
> It does.
>
> The given statement (with a bit of editing) was:
>
> SELECT 'drop table ' || name || ';'
> FROM sqlite_master
> WHEREtype = 'table'
> ANDname GLOB 'X[0-9][0-9][0-9][0-9]'
> ORDER BY 1;
>
> You'll notice there are no commas between the SELECT and FROM... the
> SELECT statement only produces one column. That column consists of a
> series of strings in the format:
>
> 'drop table ;'
>
> Since each string starts with the exact same prefix ('drop table ')
> the end result is that it will sort by table name. And, because of
> the GLOB format, it will sort by the numeric value of the table name.
>
> -j
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs. We have
> a protractor." "I'll go home and see if I can scrounge up a ruler
> and a piece of string." --from Anathem by Neal Stephenson
> ___
> 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] drop table question ?
>> For my own edification, why the "order by 1" clause? > > To sort them in ascending order of table name, which might make > old-fashioned capers like visual scrutiny a little easier. OK then, why would one not use the column name? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
> sqlite> select 'drop table ' || name || ';' from sqlite_master where > type = 'table' and name glob 'X[0-9][0-9][0-9][0-9]' order by 1; For my own edification, why the "order by 1" clause? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How many tables can a database hold?
> Can someone tell me how many tables a given database can hold. Try here: http://www.sqlite.org/limits.html > I'm looking at an initial design of an application that could have a table > of data for each city in a state. This could be possibly more than a > thousand tables. Is the data stored for cities so different that it can't be stored in one set of tables for all cities, and keyed to another that has the name? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deleting duplicate records
> CREATE TABLE dup_killer (member_id INTEGER, date DATE); INSERT INTO > dup_killer (member_id, date) SELECT * FROM talks GROUP BY member_id, > date HAVING count(*)>1; > > But, now that I have the copies in the dup_killer table, I have not > been able to discover an efficient way to go back to the original > table (talks) and delete them. My plan was to delete all the records > from talks that match the criteria of the records now in dup_killer, > and then INSERT the records from dup_killer back into talks before > DROPPING dup_killer. At this point, I am stuck. Is there an > efficient method to do this with SQLite, or should I just use a shell > script? Add another column to your dup_killer table, and update that column from the talks table (after initially populating it). Then join the two tables to delete. CREATE TABLE dup_killer (member_id INTEGER, date DATE, dup_id); INSERT INTO dup_killer (member_id, date) SELECT * FROM talks GROUP BY member_id, date HAVING count(*)>1; UPDATE dup_killer set dup_id = talk.talk_id FROM talks WHERE duplicate determining columns are equal; DELETE talks FROM dup_killer where dup_killer.dup_id = talks.talk_id; Syntax not checked. :) Oh, and I don't remember if it's true for SQLite or not, but for some DBMS, "count(*)" is slower than "count(column_name)". If your table is wide, it might make a difference. /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nested transactions
> This will be a point release: 3.6.8. There are no > incompatibilities. An important aspect of our social contract is > that SQLite continues to be compatible moving forward. There are > hundreds of millions of SQLite3 databases in the world, and we do > not want to abandon them. Software companies around the world, please note the way you *should* be doing business... Thanks. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY
> That, unfortunately, leads directly to the follow-up question of > "can BIGINT PRIMARY KEY AUTOINCREMENT" be made to work the same as > INTEGER PRIMARY KEY AUTOINCREMENT". I believe the answer is yes, but > I wouldn't bet my life on it. If I knew anything at all about SQLite, I'd probably say the answer to that is something on the order of "How many other variations of 'INT' or 'INTEGER' would have to be implemented to be compatible with the plethora of other database systems out there? And why should SQLite be responsible for making sure it's interoperable with every single one of them? SQLite is SQLite, and no other." But then, I'm a jerk, so don't listen to closely to what I say. /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Audit
> I'd like ideas / recommendations on implementing and auditing to track delta > changes to tables. Here's one possibility for auditing: http://www.sqlite.org/cvstrac/wiki?p=UndoRedo Another possibility is to have audit tables are identical copies of the tables being tracked, except for some tracking fields, and implement a trigger that, after (or before, depending on one's needs) an update happens to a row, it creates a new row in the audit table and copies all the existing columns to it, along with values that show the action, date/time of the change, user, application, and any other information that might be relevant. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Audit
> I'd like ideas / recommendations on implementing and auditing to track delta > changes to tables. Here's one possibility for auditing. http://www.sqlite.org/cvstrac/wiki?p=UndoRedo Another possibility is to have audit tables are identical copies of the tables being tracked, except for some tracking fields, and implement a trigger that, after an update happens to a row, it creates a new row in the audit table and copies all the existing columns to it, along with values that show the action, date/time of the change, user, application, and any other information that might be relevant. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Struggling with datetime("now") > MAX(dtEndDate) query - Please Help
> SELECT *
> FROM MyTableWithDates
> WHERE datetime("now") > MAX(dtEndDate)
What is it that you are actually trying to do with this query? As
formulated (even if it were syntactically correct, which I don't think
it is), you are either going to get every row in the table, or no rows
at all. Since the current date ("now") is either greater than the
maximum date in the table, or it is not, and you're not comparing to a
column in each row, only the aggregate, the resulting condition will
either be true for every row in the table, or false for every row.
Brad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determine number of records in table
> > select max(rowid) from sometable; > > Looks good and is instantaneous. Thank you very much. And will only work if you never delete any rows from the table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Break compatibility with linux 2.4 in SQLite 3.6.7?
>> When you said breaking compatibility with 2.4, you meant >> NPTL vs LinuxThreads, right? > > My thought as well. There are still some architectures which do not > support NPTL, even with 2.6 kernels (hppa comes to my mind). But lack > of NPTL support causes pain in other areas, too, so it's probably time > to fix these architectures or ditch them. That's true, didn't even think of that. If it is infact an NPTL vs LinuxThreads issue to which D. Richard Hipp is referring, last I knew, uclibc didn't support NPTL either. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Break compatibility with linux 2.4 in SQLite 3.6.7?
As a follow-up, RHEL 3 did include the NPTL backport as per: http://www.redhat.com/docs/manuals/enterprise/RHEL-3-Manual/release-notes/as-x86/ RHEL 2 did not, it was first introduced in RHEL 3. RHEL AS 2.1 is still supported by Red Hat until May 31, 2009: http://www.redhat.com/security/updates/errata/ I believe that will the the longest standing supported Linux distro out there without NPTL since they 'support' it for 7 years. When you said breaking compatibility with 2.4, you meant NPTL vs LinuxThreads, right? Or am I totally going off on a tangent. That said, personally (selfishly?), my company does not support RHEL2 anyhow so if it is just a matter of requiring NPTL and not LinuxThreads (instead of as stated 2.4 vs 2.6 kernels), I'm fine with that. (For those wondering, we don't support RHEL2 because it uses glibc 2.2, and we only support glibc 2.3.2 or higher). Realistically though, you should probably wait until after May 31, 2009 to do the cleanups. When the cleanups are performed, you should definitely have a runtime check to see if the system is running on NPTL or not as someone could have defined LD_ASSUME_KERNEL=2.4.1 on an NPTL-capable system to force it to use LinuxThreads (I think a lot of Oracle install docs recommend setting that flag, so if someone set it globally, well, that wouldn't be good). Pretty sure RHEL5 was the first release to remove LinuxThreads support all-together, RHEL3/4 allowed the LD_ASSUME_KERNEL trick. As far as the run-time check, the only thing I can think of to do is to spawn a thread that calls getpid() and return it so pthread_join() can fetch it. If it matches the parent, it's running NPTL, otherwise, LinuxThreads and some failure should occur. Though getconf/sysconf(3) might be able to determine it as well, but I'm not sure. -Brad Brad House wrote: > I'm pretty sure both RHEL 2 & 3 both use 2.4 kernels and are > still actively supported by RedHat if that sways your decision. > I know I have clients that are on RHEL3 still so I'd prefer this > change not to be made if there will be negative impact. We do > share connections across threads, but not during a transaction, > we just use a 'connection pool' and it grabs an inactive connection. > > That said, I'm pretty sure RHEL backported NPTL to 2.4, so it > may not be relevant but I don't have the info on that currently, > I can look that up. > > Thanks. > -Brad > > D. Richard Hipp wrote: >> Many systems built on the linux 2.4 kernels contain a bug in their >> thread implementation: A posix advisory lock created by thread A >> could not be overridden or modified by thread B. In essence, linux >> was treating different threads within the same process as if they were >> different processes. Long-time users of SQLite may recall that we >> used to publish the restriction that SQLite database connections >> created in one thread could not be used in a different thread. That >> restriction was entirely a result of the afore mentioned bug in linux >> 2.4. >> >> The unix drivers for SQLite contain a pile of ugly code to work around >> this bug. I would very much like to delete that code for SQLite >> version 3.6.7, due out in December. My question: would this cause >> anyone any serious hardship? >> >> My impression is that everybody who runs linux upgraded to a version >> 2.6 kernel at least two years ago. And even for those rare people who >> have not, SQLite will still continue to work correctly provided that: >> >> (1) you do not attempt to move database connections across threads. >> (2) you do not open connections to the same database file in two >> different threads of the same process. >> >> So my questions is this, really: Is there anybody who runs SQLite on >> a linux 2.4 kernel who either moves database connections across >> threads or who opens multiple connections to the same database file in >> separate threads of the same process? >> >> I am hoping that the answer to the previous question is "no" because I >> really do want to simplify the SQLite unix drivers by deleting the >> code that implements the linux thread/posix-lock bug work-around. >> >> >> D. Richard Hipp >> [EMAIL PROTECTED] >> >> >> >> ___ >> 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] Break compatibility with linux 2.4 in SQLite 3.6.7?
I'm pretty sure both RHEL 2 & 3 both use 2.4 kernels and are still actively supported by RedHat if that sways your decision. I know I have clients that are on RHEL3 still so I'd prefer this change not to be made if there will be negative impact. We do share connections across threads, but not during a transaction, we just use a 'connection pool' and it grabs an inactive connection. That said, I'm pretty sure RHEL backported NPTL to 2.4, so it may not be relevant but I don't have the info on that currently, I can look that up. Thanks. -Brad D. Richard Hipp wrote: > Many systems built on the linux 2.4 kernels contain a bug in their > thread implementation: A posix advisory lock created by thread A > could not be overridden or modified by thread B. In essence, linux > was treating different threads within the same process as if they were > different processes. Long-time users of SQLite may recall that we > used to publish the restriction that SQLite database connections > created in one thread could not be used in a different thread. That > restriction was entirely a result of the afore mentioned bug in linux > 2.4. > > The unix drivers for SQLite contain a pile of ugly code to work around > this bug. I would very much like to delete that code for SQLite > version 3.6.7, due out in December. My question: would this cause > anyone any serious hardship? > > My impression is that everybody who runs linux upgraded to a version > 2.6 kernel at least two years ago. And even for those rare people who > have not, SQLite will still continue to work correctly provided that: > > (1) you do not attempt to move database connections across threads. > (2) you do not open connections to the same database file in two > different threads of the same process. > > So my questions is this, really: Is there anybody who runs SQLite on > a linux 2.4 kernel who either moves database connections across > threads or who opens multiple connections to the same database file in > separate threads of the same process? > > I am hoping that the answer to the previous question is "no" because I > really do want to simplify the SQLite unix drivers by deleting the > code that implements the linux thread/posix-lock bug work-around. > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Problem selecting the empty string in a column
> I am *sure* that I am overlooking the obvious... > > I have a need to identify blank columns in my tables, and have been unable > to find a suitable query, eg > > SELECT * from table WHERE column = ''; > > What am I doing wrong here? It depends on what "blank" means? Does it mean an empty string, a zero, a null, or something else? If "blank" means null, then your where clause needs to be "where column is null" Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Terrible performance for one of our tables
>> Out of interest why must it completely READ each entire row? Is it >> because '*' has been used? > > The database reads a page at a time. A page is, by default, 1024 > bytes. A single page might contain multiple rows, or a single large > row might be spread across multiple pages. > When rows are only about 100 bytes in size, they will all fit on a > single page, so the entire row ends up being read, though only the > header is decoded and interpreted. So what is the explanation for the 1.2 million row table taking 8 minutes, and the 5 million row table taking 40 seconds when the row in the larger, faster table is larger than the smaller, slower one? The OP claims that the "average" row size is about 100 bytes for the smaller table, and a "few hundred" for the larger table, which suggests that either his estimates of row size are incorrect, or something else is going on. Perhaps the variance from "average" accounts for the difference? I'm interested because I might have this problem shortly, and I'd like to understand the causes in order to attempt to avoid this type of problem. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OLE DB provider for SQLite
> The problem with performance is not caused directly by SQLite provider, but > by different > way of reading data from managed (ADO.NET) providers by Analysis Services. Ah. "Analysis Services". 'Nuff said. Missed that the first time around. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OLE DB provider for SQLite
> PS Managed (ADO.NET) providers do not satisfy requirements for > performance reasons. That's a pretty blanket statement. I've found Robert Simpson's ADO.NET provider to be very performant. He has a benchmarking suite he wrote to compare various DBs. It might still be available for download. http://sqlite.phxsoftware.com/forums/t/174.aspx (Desktop) http://sqlite.phxsoftware.com/forums/t/175.aspx (Mobile) http://sqlite.phxsoftware.com/forums/p/17/33.aspx#33 (Comparing C# to C++) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] offical full list of valid data types?
>I found that the ado.net provider for sqlite support some types that do > not really match the substrings here > http://sqlite.phxsoftware.com/forums/t/31.aspx That wrapper does some mapping between a type that's declared in the table definition, and the types used in .NET programs. So, if you declare a column as char(n), the wrapper will map that to a .NET string. If you declare a column as INT, it will map that to a .NET integer. It has nothing to do with what column types are "legal" or supported. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
>> Are you able to benchmark it using an actual PC's local hard drive? >> Just for comparison. To be fair, you'd have to use the same build of >> sqlite, or at one that was built the same way. > > That would be quite an effort. Just a thought. Since the build for your device is likely to be different than the one for the desktop, it's not really a meaningful comparison. You could post the commands that you're using to run your build and see if anyone can point out any problems or improvements there. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
> > What happens when you run the update inside a transaction? > I tried it like this: > > time sqlite3 kfzdb 'begin ; update kfz set musttrans=5 ; end' > No significant change in runtime either. Are you able to benchmark it using an actual PC's local hard drive? Just for comparison. To be fair, you'd have to use the same build of sqlite, or at one that was built the same way. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
> The problem is with bulk-updating: > > > # time sqlite3 kfzdb 'update kfz set musttrans=3' What happens when you run the update inside a transaction? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update question
> My stab at an SQL statement to pull this change off is as follows: > UPDATE > Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT > pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND Nights > != '') Why do you think you need the sub-select? Assuming that they syntax for the date function is correct, why would the below not work? UPDATE Events SET Return = date(departure, nights + ' days') WHERE Return = '' AND Nights != '' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] howto setup SQLite with Powershell ?
>> access ... dll from PowerShell ... PowerShell docs > > the procedure given there is to register the dll with installutil. The use of the word "register" implies to me that they might be assuming a COM dll or .NET com visible assembly. I don't believe the SQLITE3.DLL qualifies as either. :) As well, as far as I know, InstallUtil is only capable of installing assemblies that have been designed to be installed that way. It's not effective for original style DLLs. You might try revisiting the use of one of the .NET providers by installing it in the GAC, or perhaps even using REGASM. For those, though, I will repeat the need for asking on the forums dedicated to those products. If you want a robust, well supported one, Robert Simpson's ADO.NET provider, hosted at http://sqlite.phxsoftware.com, is excellent. I've used it in a number of programs, and it works quite well. Since PowerShell is highly .NET aware, using a provider designed for that platform seems more likely to result in success than using a plain dll. Installed into the GAC, I suspect that provider would work quite well under PowerShell. > What I'm wondering is, am I the only one who wants to use SQLite together > with Powershell? Starting to look that way, isn't it? Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] howto setup SQLite with Powershell ?
> The original problem is given in the topic. > The System.Data.SQLite provider (which I currently can't get running) is > hopefully only one solution. You mentioned the System.Data.SQLite provider in every message you posted, and presented it in such a way that I interpreted you to have asked "howto setup SQLite with Powershell using System.Data.SQLite". That's what I was responding to. > SQLite provides sqlite3.exe and sqlite3.dll. However you'd access any other non-.NET, non-COM, dll from PowerShell is probably going to be the way you access this one. Are the PowerShell docs not any help with this? > How can I use that with powershell? Not a clue, other than to execute the sqlite3.exe program with the appropriate command line parameters to accomplish what I want. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] howto setup SQLite with Powershell ?
> btw: if I rerun InstallUtil I now get a message: > No public installers with the RunInstallerAttribute.Yes attribute could be > found in the ...\System.Data.SQLite.dll assembly. You should really be asking these questions on the forum/list dedicated to the support of the product you are working with, i.e. the producer of the System.Data.SQLite provider. You will likely need to either install the assembly to the GAC yourself, or use some other method to tell PowerShell where the assembly is located. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?
D. Richard Hipp <[EMAIL PROTECTED]> wrote: > CREATE TABLE t1(a INTEGER, b INTEGER); > INSERT INTO t1 VALUES(1,2); > INSERT INTO t1 VALUES(9,8); MSSQL Server 2000 > SELECT a AS b, b AS a FROM t1 ORDER BY a; b a --- --- 1 2 9 8 > SELECT b AS a, a AS b FROM t1 ORDER BY a; a b --- --- 2 1 8 9 > SELECT a, b AS a FROM t1 ORDER BY a; a a --- --- 1 2 9 8 > SELECT a AS x, b AS x ORDER BY x; Msg 207, Level 16, State 3, Line 1 Invalid column name 'a'. Msg 207, Level 16, State 3, Line 1 Invalid column name 'b'. > SELECT a AS b, b AS a WHERE a=1; Msg 207, Level 16, State 3, Line 1 Invalid column name 'a'. Msg 207, Level 16, State 3, Line 1 Invalid column name 'b'. Msg 207, Level 16, State 3, Line 1 Invalid column name 'a'. > SELECT a AS b, b AS a WHERE a=2; Msg 207, Level 16, State 3, Line 1 Invalid column name 'a'. Msg 207, Level 16, State 3, Line 1 Invalid column name 'b'. Msg 207, Level 16, State 3, Line 1 Invalid column name 'a'. > SELECT a AS x, b AS x WHERE x=1; Msg 207, Level 16, State 3, Line 1 Invalid column name 'a'. Msg 207, Level 16, State 3, Line 1 Invalid column name 'b'. Msg 207, Level 16, State 3, Line 1 Invalid column name 'x'. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Currency Issues
> jonwood wrote: > I've done very little programming related to currency (dollars, > etc.) and I'm wondering if I need to worry about rounding errors. > Since SQLite doesn't appear to have a currency type, I had planned > on using REAL instead. But I have a lot of reports to print out and > I could see rounding errors with REAL. That's a very real possibility. I'll second your idea, and Dennis' recommendation, to use an integer type for this (unless you are using a compiler that has a scaled integer or dedicated currency type). In addition, you might consider storing at least one more digit than pennies, to keep your fractional pennies, if you need to do that. I once had a system where I was required to keep thousandths of pennies, and we used this method. The vast majority of numbers had no fractional pennies, so $10.00 looked like 100, but that's the way it had to be. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.5.8 database corruption
D. Richard Hipp wrote: > On Jul 23, 2008, at 1:08 PM, Brad House wrote: > >> I'm just investigating an issue now. This is the first ever >> incident of a corrupt database we've had on a few thousand >> installations, > > Have you read the background information at > > http://www.sqlite.org/atomiccommit.html > > See especially section 9.0: Things That Can Go Wrong. Yes, I've read that. We're using the VFS layer that sqlite provides for windows (since XP-E is just XP with a bunch of dlls and auxiliary applications removed). The database always resides on the same disk as the software accessing it (no network transfers). The database file wouldn't have been manipulated outside of our application. According to your section 9, that leaves a 'rouge' process (read: virus), or buffers not actually being flushed to disk (either because of a disk controller issue or a FlushFileBuffers() issue). Obviously other options exist such as hardware failure (RAM, harddrive), or an SQLite bug. Hardware failure definitely hasn't been ruled out here. The main reason for reporting this issue is to make sure if there is an issue, there is enough 'history' of it to justify researching it. I'm definitely not blaming SQLite at this point. Thanks. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.5.8 database corruption
Guess the mailing list stripped the attachment, I've uploaded it here: http://www.monetra.com/~brad/integrity_check.txt.bz2 Brad House wrote: > I'm just investigating an issue now. This is the first ever > incident of a corrupt database we've had on a few thousand > installations, though most of our installations are on > SQLite 3.4, our latest release is now using 3.5.8. > We have deployments on just about every OS... > > The OS that experienced the corruption was Windows XP-E > (embedded). > > The error message SQLite is returning is: > database or disk is full > > But I am told there are 45G free on the partition that > the database file resides. > > We do use SQLite in a multithreaded environment, and it > is compiled with Threadsafe. Infact, we modify the > amagalmation and put: > #define SQLITE_THREADSAFE 1 > At the top of the file just to make sure. > We also use 'sqlite3_enable_shared_cache(1)'. I don't > think it really provides that much benefit to us though > as we are more commit-heavy, so I can disable it if > it might be a point of concern. > > I've attached the output of PRAGMA integrity_check; > (which looks pretty bad)... > > I can make the database available if necessary. > > Thanks for any insight. > -Brad > > > > > ___ > 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 3.5.8 database corruption
I'm just investigating an issue now. This is the first ever incident of a corrupt database we've had on a few thousand installations, though most of our installations are on SQLite 3.4, our latest release is now using 3.5.8. We have deployments on just about every OS... The OS that experienced the corruption was Windows XP-E (embedded). The error message SQLite is returning is: database or disk is full But I am told there are 45G free on the partition that the database file resides. We do use SQLite in a multithreaded environment, and it is compiled with Threadsafe. Infact, we modify the amagalmation and put: #define SQLITE_THREADSAFE 1 At the top of the file just to make sure. We also use 'sqlite3_enable_shared_cache(1)'. I don't think it really provides that much benefit to us though as we are more commit-heavy, so I can disable it if it might be a point of concern. I've attached the output of PRAGMA integrity_check; (which looks pretty bad)... I can make the database available if necessary. Thanks for any insight. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64bit Version
> Hi Brad > Thanks for the prompt reply the error I am getting is that the sqlite files > (see below) could not be copied to the system32 folder? I have tried all > sorts, > I do have administrative rights but just have no luck with this... > > Sqliteodbc.dll > Sqliteodbcu.dll > Sqlite3.odbc.dll > > Any Ideas I've never used the SQLite ODBC Driver. Really can't help you there. Seems odd that you'd e-mail this list though since SQLite doesn't provide an ODBC driver interface, though there are 3rd party ODBC interfaces. That said, maybe you're confused and you somehow think it _is_ an ODBC driver on Windows? You might want to check out this Wiki page: http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc The only ways I've used it on Windows is to either have the standard sqlite dll file in the same directory as the program which depends on it, or use the amagalmation and compile it directly into my program. Both of those work fine. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64bit Version
Yes, we utilize 64bit versions of SQLite on: Linux (x86_64) FreeBSD (x86_64) Windows (x64 -- XP, Vista -- should also work on 2008) Solaris (sparc64) AIX (ppc64) All work fine. -Brad Nic wrote: > Hi All > > Has anyone got Sqlite to run on a 64bit Operating System ? I am having issues > with server 2008 > > Regards > Nic > ___ > 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] What is quicker?
> Is this how you expect the RTree tables to be used in a case like the OP > is interested in? > > create table City ( > id integer primary key, > nametext, > lat real, > longreal, > class integer > ); > > create virtual table CityLoc using rtree ( > id integer referneces City, > lat_min real, > lat_max real, > long_minreal, > long_maxreal > ); I would have expected the relationship to work the other way, e.g. create table City ( id integer primary key, loc integer references CityLoc(id), nametext, lat real, longreal, class integer ); create virtual table CityLoc using rtree ( id integer primary key, lat_min real, lat_max real, long_minreal, long_maxreal ); Or perhaps a junction table between the two, if it was possible to have a city with more than one location, or if one location could be within two entities. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
> You may see some performance increase by setting pragma page_size to a > larger value so that SQLite transfers fewer, but larger, blocks across > the network. I would try benchmark tests with page sizes of 8K and 32K > to see if there is a substantial difference. Good point Dennis, though you should probably mention that he would need to be using at least 3.5.8 and Vacuum after setting the page size pragma for it to actually take effect on an existing database. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
> I would disagree with this, unless I misunderstand. File copies (from the > Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% > of theoretical max on our Gbit LAN, whereas reading the records from the > same file via SQLite is 20-25x slower (—2MB/sec at best, terrible > performance). So there is plenty of raw I/O bandwidth across the LAN and > network drive, but for some reason SQLite access to its remote files is > extremely slow (to be clear: these are single users accessing single files). Peter, there is a lot more latency over a network than just hitting a local disk as well, so you've got potentially hundreds of requests from disk to perform a single select on the database (traversing the Btree, etc). Your OS may perform some read-aheads and caching which would reduce the latency to nearly nothing for the disk access (on a local machine), but you're having to deal with network latency and protocol overhead on _each_ of those hundreds of requests when you're working over a network. Raw sequential throughput you mentioned really has no relevance here at all. Like Richard said, use the right tool for the job. You need a database that resides on the server and communicates using its own network protocol. If you'd like to continue using SQLite you might check out some of the server/client wrappers out there: http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork You've got to realize that no other (non-server based) database would be able to perform better in this situation. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot get amalgamation built from CVS to compile
We ran into the same problem here. It seems as though maybe the amalgamation is hand-edited for distribution to remove the contents of the config.h to be system agnostic. When we built ours from CVS, we just did the same hand-edit and packaged it and it compiled fine on the dozen or so OS's we distribute binaries for (Windows (32 & 64), MacOSX, Linux, FreeBSD, Solaris, SCO, AIX, ...). I'd actually like to know the consequences of this though, especially in relation to the reentrant functions (HAVE_GMTIME_R, HAVE_LOCALTIME_R), also I'd be interested to know what it does without UINT64_T or UINTPTR_T... -Brad Samuel Neff wrote: > We're trying to build an amalgamation from CVS to use within our application > for the first time. However, when we try to compile we get an error on this > line: > > > #ifdef HAVE_STDINT_H > #include > #endif > > fatal error C1083: Cannot open include file: 'stdint.h': No such file or > directory > > > We tracked back the difference between that distribution and the > amalgamation that we build and the major changes start here > > From sqlite3.c in 3.5.8 distribution: > > #ifndef _CONFIG_H_ > #define _CONFIG_H_ > > /* We do nothing here, since no assumptions are made by default */ > > #endif > > > From sqlite3.c in our amalgamation built from CVS: > > > #ifndef _CONFIG_H_ > #define _CONFIG_H_ > > > > /* > ** Data types > */ > > /* Define as 1 if you have the int8_t type */ > #define HAVE_INT8_T 1 > > ... > > /* Define as 1 if you have the stdint.h header */ > #define HAVE_STDINT_H 1 > > ... > > /* End of header */ > #endif > > > Is this related to a change in the CVS source or is there something we're > doing wrong in building the amalgamation? > > We're building the amalgmation on Fedora Core release 4 (Stentz), > 2.6.17-1.2142_FC4smp #1 SMP i686 i686 i386 GNU/Linux > > We're compiling sqlite in Microsoft Visual Studio 2008 as part of > System.Data.SQLite (.NET) which uses sqlite3.c and compiles fine with > sqlite3.c from the 3.5.8 distribution on the sqlite.org website. > > Any help would be appreciated. > > Thanks, > > Sam > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] restricting access to sqlite database
I'm assuming you're using mod_php. For a virtual-hosted environment, I don't think that can be made 'secure'. You probably need to switch to suexec and fastcgi php. That way the php scripts are run as your user rather than the 'www' user. You might glance at this: http://www.k4ml.com/wiki/server/apache/php-fastcgi -Brad Thomas Robitaille wrote: >> Just because "apache" the user account on your compute can access the >> db, doesn't mean apache the webserver is serving that file. >> >> My webserver runs as user "www" >> >> My db is under ~/Data//database.db owned by me, but chmod- >> ed to 666 >> >> The webserver serves only files under ~/Sites// > > I understand what you mean, but if your database file is chmod-ed to > 666, any other user logged in to your web server can edit it, > correct? If you are the only user on your web server, then indeed > placing it outside the web directory is enough, but what I am asking > about is for cases when there are 100 or 1000 users that can all log > in to the same web server. > > Thomas > >> >> >>> Thomas >>> >>> >>> On 22 Apr 2008, at 15:14, P Kishor wrote: >>> >>> >>>> On 4/22/08, Thomas Robitaille <[EMAIL PROTECTED]> wrote: >>>> >>>>> Hi everyone, >>>>> >>>>> I am in the process of setting up a forum which uses SQLite on >>>>> a web >>>>> server which has ~50 other users. I can create a directory for the >>>>> sqlite database, which I chown to 'apache' (the user under >>>>> which the >>>>> web server is run). However, because the database is then >>>>> writable by >>>>> apache, could other users not potentially write web applications >>>>> which could edit that database (and potentially remove all >>>>> tables?). >>>>> In MySQL for example, this is not a problem because of the >>>>> different >>>>> users/privileges, but what is the common way around this in >>>>> SQLite? >>>>> >>>> Nothing specific to SQLite, but common good web programming >>>> practice. >>>> Don't keep the db in a web accessible path. >>>> >>>> My web root is /path/to/web/root/ >>>> >>>> my db is in >>>> >>>> /a/totally/different/path/to/db >>>> >>>> >>>> >>>> -- >>>> Puneet Kishor http://punkish.eidesis.org/ >>>> Nelson Institute for Environmental Studies http:// >>>> www.nelson.wisc.edu/ >>>> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ >>>> ___ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> -- >> Puneet Kishor http://punkish.eidesis.org/ >> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ >> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > > ___ > 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] Improvment suggestion for "BOOLEAN"-type fields
> Taking into consideration a declared close relativity between SQLite and > TCL, I would to suggest an improvement in boolean-type fields treatment. > In my opinion, field of that type should be treated equally, when it does > contain a values: "f", "false", 0, "no" - and, respectively: "t", "true", > 1, "yes". My understanding is that SQLite provides an extension mechanism for exactly this sort of thing, does it not? Besides which, where do you stop? "Present", "Absent", "on", "off", "here", "there", ".t.", ".f.", "da", "nein"? What about non-zero values generally representing true? Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why doesn't "where =" work for text field
[EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> select name, length(name), hex(name) >> from PerfTest1 >> where name like '%key5000%'; > Returns: 'key5000'|9|276B65793530303027 Does the value of the field really contain the single quotation marks, as opposed to being just a delimiter? That would explain it. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generating new rowid algo
> > I wanted to know the algorithm used by sqlite to generate the new > > rowid. Assume there can be N distinct rowid's possible, now insert N > > records, followed by random deletion of some records. Now what rowid > > will be assigned to a new row that is added? > http://www.sqlite.org/autoinc.html > > Note that N=2^63-1. A typical application would have to work for a very > long time to create N records. Not to mention the disk space it would consume, just for the keys: (2^63-1)*8 = 73786976294838206464 bytes / 1024 = 72057594037927936 kbytes / 1024 = 70368744177664 mbytes / 1024 = 68719476736 gbytes / 1024 = 67108864 tbytes Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binding values for IN ?
> I also had some rather complex piece of SQL from "The Art of SQL", which > took a string and dissected it in place with subqueries and a join to a > pivot table, but wondered if there was an easier way to do it? If you already have that information in an array in your application code, it might be simpler to create a temporary table, populate it with your entries, and then join on it in your main query, rather than using SQL code to pivot it. Creating a separate table, either using SQL or application code, and populating it with the search items, is really the only way to do what you are wanting to do besides the dynamic generation of the entire query, which has it's own drawbacks, security and performance among them. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] SQLite and VB.Net?
> I don't know anything about .Net, and I'd like to build a quick app
> with Visual Studio 2005 or 2008 to check how well it performs with
> SQLite. If performance and deployment prove to be good enough, we'll
> use VB.Net for new projects and finally dump VB6.
I have almost no experience with .Net (or any other microsoft-specific
technologies), but we've had to advise some clients on how to use
a library we provide from within Vb.Net and the InteropServices worked
for them. Not sure if they'd meet your needs or not, but you might
give them a shot, it's as close to native as you'll get.
Basic structure I think goes like this (though it should be easy
to google):
Imports System.Runtime.InteropServices
Declare Ansi Function sqlite3_open Lib "sqlite3.dll" _
(ByVal filename As String, ByRef db As IntPtr) _
As Integer
Public SQLITE3_OK = 0
Dim db As IntPtr
If sqlite3_open("my_sqlite3.db", db) != SQLITE3_O
K
Console.WriteLine("Failed to open")
End If
-Brad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to compile SQLite for Windows 32bit & 64bit?
> Please Help > > Is there a good tutorial that shows how to compile SQLite for both > Windows 32 bit & 64bit? > > If ICU is required then I need the files to be statically linked rather > than having any external dependencies. > > Anyone know of a good tutorial for building both a 32bit & 64bit DLL > with any decencies statically linked? Have you considered just adding the sqlite amalgamation to your project and compiling it in? That's how my company uses SQLite and supports both 32bit and 64bit windows. Doesn't get much easier than a single .c file and a single .h file. Also, there is a binary-form sqlite DLL that only depends on the standard windows C library MSVCRT.DLL (that comes with Visual Studio 6, but I think most versions of Windows ship with it too, afaik). I don't think you can get rid of that dependency without risking other issues with duplicate symbols. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
