Re: [sqlite] Linux "managed only" issue(?)
Dave Edwards wrote: > > Anyone know of any bugs with Mono 2.6 or debian 6 that could cause this > as it might be there rather than here I should be looking? > I seem to recall that System.Data.SQLite does not work correctly on Mono less than about 2.8; however, I'm not exactly sure why. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linux "managed only" issue(?)
Joe Mistachkin wrote: How was the native SQLite library compiled? Was it compiled with SQLITE_THREADSAFE set to 1? Also see: https://www.sqlite.org/threadsafe.html Compiled in Debian build proc I suppose with the default of 1. I am trying to find the source file :) I will try a few tests with only 1 thread allowed and let you know. Only one thread at-a-time may safely use a SQLiteConnection object instance; however, you can create one of these on each thread and open the same database. -- Built an app with 1 form, 1 button, 1 command etc no threading and put a loop for inserting records and yep, same result so back to the drawing board. Not quite given up yet because I want to continue using SQLite on the app in windows and it would be great to get things going in Linux. Settings in the test were Dim connBuilder As New SQLiteConnectionStringBuilder connBuilder.Version = 3 'Set page size to NTFS cluster size = 4096 bytes connBuilder.PageSize = 4096 connBuilder.CacheSize = 2 connBuilder.Pooling = True connBuilder.LegacyFormat = True connBuilder.DefaultTimeout = 1000 connBuilder.JournalMode = SQLiteJournalModeEnum.Delete connBuilder.SyncMode = SynchronizationModes.Normal connBuilder.FailIfMissing = True Anyone know of any bugs with Mono 2.6 or debian 6 that could cause this as it might be there rather than here I should be looking? Cheers -- .. Dave Edwards (G7RAU) email: d...@g7rau.co.uk Web: http://g7rau.demon.co.uk/ .. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] using SQLITE_BUSY
In http://ideone.com/7lhIh is my example code. I would like to know if this would be an appropiate implemetation/use of BEGIN IMMEDIATE and sqlite3_busy_timeout. As I understood from the documentation and literature about Transaction and locks the sqlite3_step is the action state part so I enclosed sqlite3_step with BEGIN IMMEDIATE and COMMIT, but then occurred errors from the sqlite3_prepare_v2 Thats why i enclosed now all db-concerning steps with BEGIN IMMEDIATE and COMMIT. With this construction appear still some database lock errors but lesser then with a sqlite3_busy_timeout(db, 23) e.g. As I understood, the BEGIN IMMEDIATE would try to get the RESERVED lock once, and if it fails it gets a SQLITE_BUSY. The BEGIN IMMEDIATE will however try to get the RESERVED lock many times , if a sqlite3_busy_timeout is defined, but all tries happen within the time defined by the sqlite3_busy_timeout. Or is it like this, that BEGIN IMMEDIATE tries it again exactly once again after the timeout ? In literature it is also mentioned that the BEGIN IMMEDIATE has to be resetted. How and where do I have to do this in my example code ? I would appreciate some suggestions ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linux "managed only" issue(?)
Dave Edwards wrote: > > Still exactly the same issue though in Linux , around 300 rows imported > and it falls over. I wonder if it is thread related then? If it is then > I will have remove support on Linux as the app is multi-threaded and as > far as I was concerned thread safe (never errors). > How was the native SQLite library compiled? Was it compiled with SQLITE_THREADSAFE set to 1? Also see: https://www.sqlite.org/threadsafe.html > > I will try a few tests with only 1 thread allowed and let you know. > Only one thread at-a-time may safely use a SQLiteConnection object instance; however, you can create one of these on each thread and open the same database. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linux "managed only" issue(?)
On 07/07/2012 17:28, Joe Mistachkin wrote: Dave Edwards wrote: For Linux I have recompiled the managed only dll from the .081 source using: build.bat ReleaseManagedOnly (so no need for the interlop.dll to be placed in the root folder) And I have had to mod the built.bat so it runs: %_AECHO% Forcing the use of the .NET Framework 2.0... SET YEAR=2005 SET FRAMEWORKDIR=%windir%\Microsoft.NET\Framework\v2.0.50727 GOTO skip_netFxCheck Compiling the managed-only assembly for Mono requires some special compilation options, as documented here: https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki#mono Basically, you'll just need to add the following line to your custom batch file (prior to building): SET MSBUILD_ARGS=/property:UseInteropDll=false /property:UseSqliteStandard=true Another note; .isdisposed not in the sqlite.connection ? Testing for "is nothing" does not pick up that it "is disposed but not nothing" Since a SQLiteConnection object is supposed to be used by only one thread, there is normally no reason to test if the connection is disposed because that thread should already know. Hi Joe Many thanks, this is now compiled and my app can connect to an SQLite database in Linux. Still exactly the same issue though in Linux , around 300 rows imported and it falls over. I wonder if it is thread related then? If it is then I will have remove support on Linux as the app is multi-threaded and as far as I was concerned thread safe (never errors). I will try a few tests with only 1 thread allowed and let you know. Cheers -- .. Dave Edwards (G7RAU) email: d...@g7rau.co.uk Web: http://g7rau.demon.co.uk/ .. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding Foreign Key Contraints
I have a fairly complex web based application which helps manage the process of running an American Football results picking competition through the season. I am in the process of porting it to Sqlite ready for the start of next season. I just discovered a bug in my handling of a constraint violation that has made me want to rethink my strategy in this area. My original strategy was to completely cascade deletes, but the bug I discovered showed me a place in the user interface where that approach could be too dangerous, as it wasn't obvious that there would be side effects at the user interface level. So I want to replan my strategy. But given the complexity of the situation I want to make sure I don't make any mistakes. There is one particular pattern that occurs in several places, where what might happen is ambiguous (at least to me), and I would like this mailing lists view of what will happen and what is the right thing to do to make it so. [Note the application is web based with Ajax calls. Every single page request or ajax call opens the database and does a "PRAGMA foreign_keys = ON" as its first function] Let me list my key entities in this pattern At the top level there are three "Team" with primary key tid (which is a three character string - but that is probably irrelevant) "Participant" with primary key uid and "Competition" with primary key cid. There are then some secondary entities, for this example I need two "Registration" (user registers for a competition) which has primary key (cid,uid). Its foreign key constraints are defined as cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE ON DELETE CASCADE, -- Competition ID uid integer NOT NULL REFERENCES participant(uid) ON UPDATE CASCADE ON DELETE CASCADE, --User ID "Team_in_competition" with primary key (cid,tid). Its foreign key constraints are defined as cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- Competition ID tid character(3) NOT NULL REFERENCES team(tid) ON UPDATE CASCADE ON DELETE CASCADE, --TeamID And then a derived entity from the other two "Playoff_pick" with primary key (cid,uid,tid). Its the foreign key constrains on this one which is rather tricky I want to arrange my constraints so that. Deleting Competition or Participant Deletes everything below it Deleting Team_in_competition fails with a constrain violation when there is a playoff_pick that refers to it I am hoping that I can define the constraints so. FOREIGN KEY (cid,uid) REFERENCES registration(cid,uid) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (cid,tid) REFERENCES team_in_competition(cid,tid) What I am hoping is that if I delete the "Competition" (or "Participant") , then it deletes the "Registration" which in turn deletes the "Playoff_pick" immediately, but that because the deleting of "Team_in_competition" is deferred until commit time, by that time the commit happens there is no "Playoff_pick" to prevent the "Team_in_competition" from being deleted. Have I understood this right? -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linux "managed only" issue(?)
Dave Edwards wrote: > > For Linux I have recompiled the managed only dll from the .081 source > using: build.bat ReleaseManagedOnly (so no need for the interlop.dll to > be placed in the root folder) > And I have had to mod the built.bat so it runs: >%_AECHO% Forcing the use of the .NET Framework 2.0... >SET YEAR=2005 >SET FRAMEWORKDIR=%windir%\Microsoft.NET\Framework\v2.0.50727 >GOTO skip_netFxCheck > Compiling the managed-only assembly for Mono requires some special compilation options, as documented here: https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki#mono Basically, you'll just need to add the following line to your custom batch file (prior to building): SET MSBUILD_ARGS=/property:UseInteropDll=false /property:UseSqliteStandard=true > > Another note; .isdisposed not in the sqlite.connection ? Testing for "is > nothing" does not pick up that it "is disposed but not nothing" > Since a SQLiteConnection object is supposed to be used by only one thread, there is normally no reason to test if the connection is disposed because that thread should already know. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Linux "managed only" issue(?)
Hi I have been meaning to port my .net2.0 project to Linux for years and at last done and working (kind of.. testing on Mono 2.6.7.5 on Debian 6) Now the issue. I am using 1.0.66.0 System.Data.SQLite.dll in my project and in Windows everything runs fine Using the same .66 but the managed only version in Linux it runs fine for a bit but after about 300 inserts into the Db it fails saying database not found and by the look of it Mono totally loses the plot because all context menus stop working and all IO things cease to work. So, reading the web I see that .081 is advised due to instability on Mono with 1.0.66.0 so I have inc. this now in my project. In Windows everything runs fine once I realised that you swap 32/64 bit versions by purely swapping SQLite.Interop.dll 32/64 bit. For Linux I have recompiled the managed only dll from the .081 source using: build.bat ReleaseManagedOnly (so no need for the interlop.dll to be placed in the root folder) And I have had to mod the built.bat so it runs: %_AECHO% Forcing the use of the .NET Framework 2.0... SET YEAR=2005 SET FRAMEWORKDIR=%windir%\Microsoft.NET\Framework\v2.0.50727 GOTO skip_netFxCheck But still interlop errors when running in Linux: System.DllNotFoundException: SQLite.Interop.dll at (wrapper managed-to-native) System.Data.SQLite.UnsafeNativeMethods:sqlite3_config (int,System.Data.SQLite.SQLiteLogCallback,intptr) at System.Data.SQLite.SQLite3.StaticIsInitialized () [0x0] in unknown>:0 at System.Data.SQLite.SQLiteLog.Initialize () [0x0] in unknown>:0 at System.Data.SQLite.SQLiteConnection..ctor (System.String connectionString) [0x0] in :0 at System.Data.SQLite.SQLiteConnection..ctor () [0x0] in unknown>:0 at (wrapper remoting-invoke-with-check) System.Data.SQLite.SQLiteConnection:.ctor () at Live_MUF.ADO_SQLite.EXECUTE_SQL (System.String sqltext, DataBaseType databasetype) [0x0] in :0 ) I know the app works fine in Mono as I can also run it in mysql and no errors after running. Spent a long time on this and fed up enough to join the users list (which I should have done at the beginning!) so any pointers greatly appreciated. Another note; .isdisposed not in the sqlite.connection ? Testing for "is nothing" does not pick up that it "is disposed but not nothing" Regards Dave -- .. Dave Edwards (G7RAU) email: d...@g7rau.co.uk Web: http://g7rau.demon.co.uk/ .. ___ 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 6 Jul 2012, at 4:00pm, Jonathan Haws wrote: > So, then what is the best way to make a query from one process pend until the > database is available? > > 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? Yes. SQLite does database-level locking, trying its best not to lock the database until it knows that you're definitely going to make changes to it. The result is roughly what you describe. > Is this what I would use sqlite3_busy_timeout() for? Yes. Set a busy timeout for however long you want your app to wait before giving up. Ten seconds ? Five minutes ? If the second process can't get access in this time you get one of the result codes, probably SQLITE_BUSY or SQLITE_LOCKED. That's one of the reasons you collect and check the result code from every SQLite call. Simon. ___ 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
