Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7)
Caleb A. Austin wrote: > > The top file operation works, but the sqlite3_open does not... > > Wondering if I need to compile an option for SQLite to be using the > correct file io for WEC7 > What return code is coming back from sqlite3_open()? Can you enable logging via the SQLITE_CONFIG_LOG configuration option before calling any other SQLite APIs? http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfiglog Example: void logging_callback(void *notUsed, int iCode, const char *zErrmsg){ /* handle output here... */ fprintf(stdout, "%d: %s\n", iCode, zErrmsg); } sqlite3_config(SQLITE_CONFIG_LOG, logging_callback, 0); -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7)
Here is what I have come up with: FILE *fp; fp = fopen("\\release\\sql\\MYFILE.txt", "a"); fprintf(fp, "%s\n ", "Hello World, Where there is will, there is a way."); fclose(fp) ; sqlite3_open(("\\release\\sql\\count.db"), ); The top file operation works, but the sqlite3_open does not... Wondering if I need to compile an option for SQLite to be using the correct file io for WEC7 Caleb Austin -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Friday, October 12, 2012 10:39 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7) Caleb A. Austin wrote: > > Any ideas on why the database is being placed in root and not in local? > My understanding is that Windows CE does not support the concept of a current directory. Therefore, in order for a file to be created in a particular directory, the full path to the file must be specified (i.e. in the call to sqlite3_open()). -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ## CONFIDENTIALITY NOTICE: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. It may contain confidential, privileged, and/or proprietary information. Any review, dissemination, distribution, copying, printing, or other use of this email by persons or entities other than the addressee and his/her authorized agent is prohibited. If you have received this email in error please notify the originator of the message and delete the material from your computer. ## ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On Fri, Oct 12, 2012 at 5:14 PM, Simon Slavinwrote: > I think I understand what you're asking for, but I see no point in being > informed about D, because I can't see anything useful a program can do if the > transaction gets marked 'complete' but D doesn't succeed. Either you see D > as being part of a transaction, or you don't. A server application might not know which the client wants. And sure, the client could tell it. But I think that to be general and thus support arbitrary layering, having an option to pass this indication through is best. > By all means, use a PRAGMA to day whether you do need D or not. SQLite > already has a dozen ways to do this (though it is very puzzling to try to > work out what combination of PRAGMAs to use under your conditions). But > report D (or ignore D) as part of the result of 'COMMIT'. Don't make an > extra status for "committed but not durable'. We disagree then. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On 12 Oct 2012, at 10:23pm, Nico Williamswrote: > Here's some more examples of where delayed-D ACKs would be nice: > distributed services. These are really just a variant of my earlier > UI example, but still: a server might respond with an ACK as soon as a > transaction completes with ACI and again when D is achieved, thus > allowing different clients to choose when to demand D independently of > each other. I think I understand what you're asking for, but I see no point in being informed about D, because I can't see anything useful a program can do if the transaction gets marked 'complete' but D doesn't succeed. Either you see D as being part of a transaction, or you don't. By all means, use a PRAGMA to day whether you do need D or not. SQLite already has a dozen ways to do this (though it is very puzzling to try to work out what combination of PRAGMAs to use under your conditions). But report D (or ignore D) as part of the result of 'COMMIT'. Don't make an extra status for "committed but not durable'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On Fri, Oct 12, 2012 at 4:08 PM, Simon Slavinwrote: > If all you're doing is showing something on a display that's fine. But if > that's what you're doing I see no point in distinguishing between 'success' > and 'durable'. As far as I can see your program has nothing to do between > the two statuses. D.R. Hipp asks for a write barrier so that ACI (no D) can be implemented easily. It turns out that this is possible (as described earlier). I also suggest that "delayed D" might be of use. I don't have great examples where one might want delayed D, but still, I would rather have an API for learning when D is achieved. You propose not having such an API at all (or at least imply it). Why wait for someone to ask for what is clearly a sensible API to have? Sometimes the existence of an API can foster development of consumers. Sometimes an API w/o initial consumers dies on the vine. It's a matter of judgement whether to add an API before having definite consumers, but IMO it's worth doing in this case. Here's some more examples of where delayed-D ACKs would be nice: distributed services. These are really just a variant of my earlier UI example, but still: a server might respond with an ACK as soon as a transaction completes with ACI and again when D is achieved, thus allowing different clients to choose when to demand D independently of each other. A logging system might want ACID for critical messages and ACI for for all others. Durability is generally required in production systems. So good examples where it's not necessary are hard to come by. Yet another variant on my first example would be a mobile note taking app that uses color (or an icon) to indicate when the current state of the document is safely committed on disk (flash, really). The mobile device environment generally requires D but doesn't require D before proceeding. Perhaps this is the best example I have so far. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On 12 Oct 2012, at 10:01pm, Nico Williamswrote: > On Fri, Oct 12, 2012 at 3:53 PM, Simon Slavin wrote: >> That's an interesting idea. I have a question. Suppose your program >> received the 'success' result for a transaction and carried on to do other >> transactions. Later you test to see whether the transaction is durable and >> find that it isn't. What would be a useful thing to do at that point ? > > It depends. For most such applications I'd say this is just not > appropriate. But you might have an application where this is fine, > provided you have a way to reflect the D/not-D state of the initial > transaction in the dependent ones. To do that properly, you would need a multi-value variable. Because for every operation you actually have three statuses: 1) Operation hasn't been started yet. 2) Operation is just about to start ... here the computer actually tries to do the operation 3a) Operation complete and failed 3b) Operation complete and succeeded The problem with your process as stated is that the operation may be complete (correctly written to disk and therefore durable) but the software may not yet have set the flag to indicate that. If your software looks at the status and sees 'not durable' it might take 'not durable' actions on something which is actually durable. > What I had in mind was something more like a booking system: let the > user know that the transaction completed by updating the page so as to > make the form go away, but leave an indicator (e.g., animated > ellipsis) to indicate that the transaction is not quite completed. If all you're doing is showing something on a display that's fine. But if that's what you're doing I see no point in distinguishing between 'success' and 'durable'. As far as I can see your program has nothing to do between the two statuses. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] interesting deadlock.
Thanks for your help Dan, works like a charm (the work-around, haven't tried the new code yet). One question, usage-related. So the reason I'm all of this with the hot backup is that when certain tables are changed, we want to create a snapshot of the database. We do this by marking an 'IsDirty' field in a table, then set a timer for a few seconds down the road and run the backup in the timer (in a background thread). If another change happens before the timer is fired, we cancel the timer and set a new one. Works great except for the case where we have a transaction that takes longer than the timer timeout. On top of that, we have something that goes thru and checks for databases that are dirty that have been missed just to be sure. So here's my question. I found that I could easily end up in a state where, due to the amount of work going on, the backup is just going to restart over and over again. When running with shared-cache, we would get a bunch of BUSY or LOCKED results from backup_step. Now that it's using private cache, it never gets those. I ended up putting something that I feel like is a hack, using the sqlite3_backup_remaining call after each call to step, keep the last result and comparing that to determine if the number of pages remaining has increased and using that to indicate that the backup restarted. Then tossing the towel in after 10 tries, letting it get picked up at a less busy time. Is that a reasonable way to detect backups restarting? Is there a better way? On Oct 5, 2012, at 11:59 AM, Dan Kennedy wrote: > On 10/05/2012 04:29 AM, Jonathan Engle wrote: >> Ok, so here's a question (trying an experiment to see if this will >> work, but throwing it out here as well). >> >> What if the source db handle for the backup is opened to use private >> cache? Will this have any effect at all or is it the cache mode of >> db2 (using your example below)? > > That sounds like it will work around the problem. If db1 is > using a private cache the problem cannot occur. > > Fix is here: > > http://www.sqlite.org/src/info/89b8c377a6 > > Should appear in 3.7.15. > > > >> >> >> On Sep 28, 2012, at 1:49 AM, Dan Kennedy wrote: >> >>> On 09/28/2012 03:32 AM, Jonathan Engle wrote: I've been picking away at this for the last few days and have it narrowed down fairly well. It looks like if I turn off shared cache, it works fine (same application code). If I run with SQL_DEBUG enabled, the first issue I run into in an assertion in sqlite3BtreeEnter: assert( sqlite3_mutex_held(p->db->mutex) ); The call stack from it is sqlite3BackupUpdate backupOnePage sqlite3BtreeGetReserve(p->pSrc) sqlite3BtreeEnter Look up the stack, it looks like sqlite3BackupUpdate locks the mutex on the destination database but not the source. >>> >>> Say you have an active backup operation (one created by >>> backup_init() but not yet completed) using source database handle >>> db1. In non-shared-cache mode. The backup is half-way done - 50% of >>> the source database pages have been copied to the destination. >>> >>> If the source db is written by another process at this point, or >>> using a database handle other than db1, the backup operation has to >>> start over from the beginning on the next call to >>> sqlite3_backup_step(). >>> >>> However, if the app writes to the source database using handle db1, >>> SQLite will automatically update the backup database as well. So >>> that the backup operation doesn't have to restart. That's the call >>> to sqlite3BackupUpdate() above. As you say, the code assumes that >>> the mutex on the source database handle (i.e. db1) is already >>> held. >>> >>> Turns out that this assumption is only true in non-shared-cache >>> mode. Because of the way the code is structured, in shared-cache >>> mode, this call to sqlite3BackupUpdate() will be made even if the >>> source database is updated using a second database handle - db2. >>> But the backup code still calls routines that assume the db1 mutex >>> is held... Bug. >>> >>> In the deadlock scenario, all the threads are blocked in >>> lockBtreeMutex(). This routine is supposed to prevent deadlock by >>> ensuring that mutexes are only obtained in a globally defined >>> order. But that could malfunction in unpredictable ways if two >>> threads were running the lockBtreeMutex() code on behalf of the >>> same database connection simultaneously. The mutex on the database >>> handle is supposed to prevent that from happening, but since the >>> bug above allows lockBtreeMutex() to be called without actually >>> holding the mutex it easily might. >>> >>> I think the fix will likely be to have shared-cache mode work like >>> non-shared-cache mode - force the backup to start over if the >>> source database is written via a second database handle (i.e. >>> db2). >>> >>> Dan. >>> >>> >>> >>> >>> Tried as a test adding
Re: [sqlite] light weight write barriers
On Fri, Oct 12, 2012 at 3:53 PM, Simon Slavinwrote: > That's an interesting idea. I have a question. Suppose your program > received the 'success' result for a transaction and carried on to do other > transactions. Later you test to see whether the transaction is durable and > find that it isn't. What would be a useful thing to do at that point ? It depends. For most such applications I'd say this is just not appropriate. But you might have an application where this is fine, provided you have a way to reflect the D/not-D state of the initial transaction in the dependent ones. What I had in mind was something more like a booking system: let the user know that the transaction completed by updating the page so as to make the form go away, but leave an indicator (e.g., animated ellipsis) to indicate that the transaction is not quite completed. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On 12 Oct 2012, at 6:00pm, Nico Williamswrote: > I do think that applications should be able to request deferred > durability *and* find out when a given transaction has indeed become > durable. > > A distinction between success and durability in the API might bleed > into UIs too. Imagine a web browser interface where "submit" does > some XHR that causes a DB transaction to be run and committed, the > page to be updated to show that the transaction succeeded, and > finally, another XHR is used to find when the transaction is durable > and the page is then updated again to reflect as much. That's an interesting idea. I have a question. Suppose your program received the 'success' result for a transaction and carried on to do other transactions. Later you test to see whether the transaction is durable and find that it isn't. What would be a useful thing to do at that point ? Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on flash (was: [PATCH 00/16] f2fs: introduce flash-friendly file system)
On Wed, Oct 10, 2012 at 08:47:02AM -0400, Richard Hipp wrote: > [snip] > > We would also love to have guidance on alternative techniques for obtaining > memory shared across multiple processes that does not involve mmap() of > temporary files. shmget/shmat - Part of the SysV IPC primitives and portable to most (all?) UNIX-alikes. shm_open - Part of the POSIX realtime extensions. Regards, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7)
Caleb A. Austin wrote: > > I have tried: > > sqlite3_open("/release/sql/countries.db", ); > I'm not sure if it will make a difference; however, I think Windows CE may need backslashes, not forward slashes. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7)
Thanks Joe, I have tried: sqlite3_open("/release/sql/countries.db", ); but I get a 0 bytes countries.db in the "/release/sql/" folder and then a long list of SQL errors about no access to the database. I think this is a Windows CE issue... but asking here if someone has used SQLite on Windows CE to help fix the problem. Caleb Austin -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Friday, October 12, 2012 10:39 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7) Caleb A. Austin wrote: > > Any ideas on why the database is being placed in root and not in local? > My understanding is that Windows CE does not support the concept of a current directory. Therefore, in order for a file to be created in a particular directory, the full path to the file must be specified (i.e. in the call to sqlite3_open()). -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ## CONFIDENTIALITY NOTICE: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. It may contain confidential, privileged, and/or proprietary information. Any review, dissemination, distribution, copying, printing, or other use of this email by persons or entities other than the addressee and his/her authorized agent is prohibited. If you have received this email in error please notify the originator of the message and delete the material from your computer. ## ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF?
On 10/12/2012 11:23 AM, Frank Chang wrote: With the latest version of Sqlite, Is it possible to calculate the length of the longest increasing subsequence, also referred to as sortation percent, using a sqlite UDF, user defined function? Thank you. An algorithm described at http://en.wikipedia.org/wiki/Longest_increasing_subsequence#Efficient_algorithms should be possible to implement in the form of a user-defined aggregate function, if that's what you are asking. The algorithm is O(n) space - that is, it needs to store substantial portions of the sequence (the whole sequence, in the worst case). In light of this, I don't quite see what you expect to gain from turning it into a UDF, as opposed to simply loading the sequence from the database into memory with a vanilla SELECT statement, and working on it in your application code. What's the ultimate goal of the exercise, if you don't mind me asking? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 5 readers, 1 writer, shared cache and read uncommitted
This is a question to ask about a particular Sqlite configuration to see if it is appropriate or how to make it better. A brief sketch of the processing need is that I have one process managing a lot of "item" data in a Sqlite db, and occasionally there is a need to walk through all items and read some stuff from the db, do some substantial processing, and write back some new results. Each item can be processed independently. There is no other competition for accessing the database while this is going on. I have the following Sqlite 3.7.14.1 configuration that seems to work well, but it is likely not common so I would like some validation or alternatives. - one process that uses many threads to do the work - WAL journal mode on local Sqlite db - SQLITE_THREADSAFE=2 SQLITE_TEMP_STORE=2 - one read-write connection - 5 read-only connections - all 6 connections use one large shared cache - all connections are configured with read-uncommitted - processing is done in N threads with each processing one item at a time, and when needed each thread briefly acquires (with thread synchronization) one of the read connections or the write connection to read or write from the db and then immediately releases it for another thread to use - when all work is done then one thread will commit the write connection This is essentially having 6 connections all participate in a single very large Sqlite transaction. And, it seems to work well. But, is this a valid and safe usage of Sqlite? Are there alternatives or other configuration settings that would help? Thanks! Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7)
Caleb A. Austin wrote: > > Any ideas on why the database is being placed in root and not in local? > My understanding is that Windows CE does not support the concept of a current directory. Therefore, in order for a file to be created in a particular directory, the full path to the file must be specified (i.e. in the call to sqlite3_open()). -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7)
Thanks Joe, Yes, I will look at keeping the source code clean of any edits. This was just a way to prove that it worked. I have been able to run SQLite on the WEC7 board and create tables/ inserts/ etc. So it looks like things are working correctly. I do have a few questions though. In short: The database is created in the root directory, not in the local directory. And as such it does not find the database after being closed. I have the following setup: My working directory: /release/sql/ I run the following code in a program running from my working directory: Sqlite_test.exe .. sqlite3_open("main.db", ); .. but the data base shows up in the root directory. Root: /main.db Any ideas on why the database is being placed in root and not in local? Caleb Austin -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Thursday, October 11, 2012 12:35 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7) Caleb A. Austin wrote: > > Added near line 14092: > #define HAVE_LOCALTIME_S 0 > Since the SDK you are using does not appear to provide the localtime_s function, even though it uses MSVC and its associated CRT, this makes sense. This could be defined in the Makefile and/or project properties to avoid having to modify the source code. > > Added near 14109: > struct tm *__cdecl localtime(const time_t *t); > Since is included, this should not be required. I'm not sure exactly which SDK you are using; however, it appears that it may not be fully ANSI compliant? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ## CONFIDENTIALITY NOTICE: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. It may contain confidential, privileged, and/or proprietary information. Any review, dissemination, distribution, copying, printing, or other use of this email by persons or entities other than the addressee and his/her authorized agent is prohibited. If you have received this email in error please notify the originator of the message and delete the material from your computer. ## ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On Fri, Oct 12, 2012 at 2:58 AM, Dan Kennedywrote: > On 10/11/2012 11:38 PM, Nico Williams wrote: >> There is something you can do: [...] > > SQLite WAL mode comes close to that if you run your checkpoints > in the background. [...] Right. WAL mode comes close to being a COW on-disk format. > Omitting the D in ACID changes everything. With the D in, you need to > fsync() after every transaction. Without it, you need to fsync() before > reclaiming space (i.e. when overwriting old data with new - you need > to be sure that the old data will not be required following recovery > from a power failure, which means an fsync()). Exactly. You've put it more succinctly than I. I do think that applications should be able to request deferred durability *and* find out when a given transaction has indeed become durable. A distinction between success and durability in the API might bleed into UIs too. Imagine a web browser interface where "submit" does some XHR that causes a DB transaction to be run and committed, the page to be updated to show that the transaction succeeded, and finally, another XHR is used to find when the transaction is durable and the page is then updated again to reflect as much. For a user "success" and "durable" means that the opposite of success may be that they have to update a form and try again, so knowing that the transaction succeeded is useful, in many cases even if the transaction is not yet durable (because D failure is extremely rare). To the user "durable" means that the transaction is truly complete. Is this distinction something that users can be expected to understand? I believe that they can understand the distinction intuitively, so the UI presentation needs to be finely tuned, and having the ability to build such a UI means marking this distinction in the API. Is it valuable to expose this distinction to the user? I think that depends on the application -- it just shouldn't be precluded. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF?
With the latest version of Sqlite, Is it possible to calculate the length of the longest increasing subsequence, also referred to as sortation percent, using a sqlite UDF, user defined function? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
Well, an article on write barriers published in May 2007 can't contradict the statement that barriers don't exist these days. :) Pavel On Fri, Oct 12, 2012 at 5:38 AM, Black, Michael (IS)wrote: > There isn't Somebody sure wasted their time on this article then... > http://www.linux-magazine.com/w3/issue/78/Write_Barriers.pdf > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Advanced GEOINT Solutions Operating Unit > Northrop Grumman Information Systems > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Christoph Hellwig [h...@infradead.org] > Sent: Thursday, October 11, 2012 12:41 PM > To: ? Yang Su Li > Cc: linux-fsde...@vger.kernel.org; General Discussion of SQLite Database; > linux-ker...@vger.kernel.org; d...@hwaci.com > Subject: EXT :Re: [sqlite] light weight write barriers > > On Thu, Oct 11, 2012 at 11:32:27AM -0500, ? Yang Su Li wrote: >> I am not quite whether I should ask this question here, but in terms >> of light weight barrier/fsync, could anyone tell me why the device >> driver / OS provide the barrier interface other than some other >> abstractions anyway? I am sorry if this sounds like a stupid questions >> or it has been discussed before > > It does not. Except for the legacy mount option naming there is no such > thing as a barrier in Linux these days. > > ___ > 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] primary key with bulk insert (UNION SELECT)
On 11 October 2012 15:07, Alan Frankelwrote: > I have a table that uses an autogenerated id as primary key. I want to do > bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I > specify an id for each row: > > sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name > VARCHAR(25), distance REAL); > sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as > distance UNION SELECT 'Procyon', 500; > Error: table CelestialObject has 3 columns but 2 values were supplied > > If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY > AUTOINCREMENT") when I create the table, the error is the same. Can anyone > tell me whether there's a way to use a bulk insert without specifying an id > for each row? insert into CelestialObject( name, distance ) select 'Betelguese' as name, 200 as distance UNION SELECT 'Procyon', 500; > > Thanks, > Alan > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
There isn't Somebody sure wasted their time on this article then... http://www.linux-magazine.com/w3/issue/78/Write_Barriers.pdf Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Christoph Hellwig [h...@infradead.org] Sent: Thursday, October 11, 2012 12:41 PM To: ? Yang Su Li Cc: linux-fsde...@vger.kernel.org; General Discussion of SQLite Database; linux-ker...@vger.kernel.org; d...@hwaci.com Subject: EXT :Re: [sqlite] light weight write barriers On Thu, Oct 11, 2012 at 11:32:27AM -0500, ? Yang Su Li wrote: > I am not quite whether I should ask this question here, but in terms > of light weight barrier/fsync, could anyone tell me why the device > driver / OS provide the barrier interface other than some other > abstractions anyway? I am sorry if this sounds like a stupid questions > or it has been discussed before It does not. Except for the legacy mount option naming there is no such thing as a barrier in Linux these days. ___ 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] primary key with bulk insert (UNION SELECT)
On 11 Oct 2012, at 3:07pm, Alan Frankelwrote: > I have a table that uses an autogenerated id as primary key. I want to do > bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I > specify an id for each row: > > sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name > VARCHAR(25), distance REAL); Note that SQLite has no VARCHAR type and no limit to field length. > sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as > distance UNION SELECT 'Procyon', 500; There's no SELECT after UNION. But even then that format for the INSERT command is slow when you actually know the values you want to use. Use this instead: INSERT INTO CelestialObject (name, distance) VALUES ('Betelguese', 200), ('Procyon', 500); simon$ sqlite3 ~/Desktop/fred.sqlite SQLite version 3.7.12 2012-04-03 19:43:07 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name TEXT, distance REAL); sqlite> INSERT INTO CelestialObject (name, distance) VALUES ('Betelguese', 200), ('Procyon', 500); sqlite> SELECT * FROM CelestialObject; 1|Betelguese|200.0 2|Procyon|500.0 Note that even this format was implemented only in recent versions of SQLite. If it doesn't work in your version tell us which version you're using. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Field Name are surrounded by double quotes for Views
Vincent DARON wrote: > > It seems the IDataReader.GetName(int i) method return name surrounded by > double quotes for views. > > Example: > > For a table : "Id" > For a view : "\"Id\"" > > Is it the expected behaviour ? > Do you have some example SQL and/or C# code that demonstrates this issue? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite Field Name are surrounded by double quotes for Views
Hi all, It seems the IDataReader.GetName(int i) method return name surrounded by double quotes for views. Example: For a table : "Id" For a view : "\"Id\"" Is it the expected behaviour ? Thanks Vincent PS: In the same way, IDataReader.GetColumnIndex() will return -1 for view column name if you do not surround them with double quotes. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On Thu, Oct 11, 2012 at 11:32:27AM -0500, ? Yang Su Li wrote: > I am not quite whether I should ask this question here, but in terms > of light weight barrier/fsync, could anyone tell me why the device > driver / OS provide the barrier interface other than some other > abstractions anyway? I am sorry if this sounds like a stupid questions > or it has been discussed before It does not. Except for the legacy mount option naming there is no such thing as a barrier in Linux these days. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] primary key with bulk insert (UNION SELECT)
I have a table that uses an autogenerated id as primary key. I want to do bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I specify an id for each row: sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name VARCHAR(25), distance REAL); sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as distance UNION SELECT 'Procyon', 500; Error: table CelestialObject has 3 columns but 2 values were supplied If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY AUTOINCREMENT") when I create the table, the error is the same. Can anyone tell me whether there's a way to use a bulk insert without specifying an id for each row? Thanks, Alan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seemingly random Access violation errors
Matthew Dumbleton wrote: > > I have tried the new version you mentioned (via the ZIP archive) but this > doesn't seem to have resolved my issue. > I'm working on some more changes that might help if the issue is actually related to threads being aborted. > > I did remove the abort call previously (after your valid comments) to test > and this made no difference so I'm not sure this is the root of the problem. > Very odd. Are you able to get a more detailed (native & managed) stack trace? Is this still with the debug build? I'm also very curious why the assert() failures indicating a corrupt heap we saw in the previous debug output are no longer showing up. Heap corruption is probably the root cause of the issue you are seeing. The question is, what is causing it? Even if the Thread.Abort issue was impacting you, it would most likely not manifest itself in a corrupted heap, it would probably just leak native resources. Is the latest interop debug output (with my enhanced diagnostics) any more revealing? I would like to see it if you have it saved somewhere because it shows the connection associated with the statements being finalized. > > I am simply starting the background thread and then flicking between forms when > the crash occurs. > Does the processor on the device being tested support more than one thread executing simultaneously? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On 10/11/2012 11:38 PM, Nico Williams wrote: On Wed, Oct 10, 2012 at 12:48 PM, Richard Hippwrote: Could you list the requirements of such a light weight barrier? i.e. what would it need to do minimally, what's different from fsync/fdatasync ? For SQLite, the write barrier needs to involve two separate inodes. The requirement is this: ... Note also that when fsync() works as advertised, SQLite transactions are ACID. But when fsync() is reduced to a write-barrier, we loss the D (durable) and transactions are only ACI. In our experience, nobody really cares very much about durable across a power-loss. People are mainly interested in Atomic, Consistent, and Isolated. If you take a power loss and then after reboot you find the 10 seconds of work prior to the power loss is missing, nobody much cares about that as long as all of the prior work is still present and consistent. There is something you can do: use a combination of COW on-disk formats in such a way that it's possible to detect partially-committed transactions and rollback to the last good known root, and backgrounded fsync()s (i.e., in a separate thread, without waiting for the fsync() to complete). SQLite WAL mode comes close to that if you run your checkpoints in the background. Following a power failure, those transactions that have been checkpointed to the database file are assumed to have been synced. Then SQLite uses checksums to determine the subset of transactions in the WAL file that are intact. I say close, because if you keep on writing to the db while the checkpoint is running you end up with the WAL file growing indefinitely. So it doesn't quite work. Omitting the D in ACID changes everything. With the D in, you need to fsync() after every transaction. Without it, you need to fsync() before reclaiming space (i.e. when overwriting old data with new - you need to be sure that the old data will not be required following recovery from a power failure, which means an fsync()). Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users