Re: [sqlite] Building SQLite on Windows Embedded Compact 7(WEC7)

[Joe Mistachkin]

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)

[Caleb A. Austin]

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

[Nico Williams]

On Fri, Oct 12, 2012 at 5:14 PM, Simon Slavin  wrote:
> 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

[Simon Slavin]

On 12 Oct 2012, at 10:23pm, Nico Williams  wrote:

> 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

[Nico Williams]

On Fri, Oct 12, 2012 at 4:08 PM, Simon Slavin  wrote:
> 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

[Simon Slavin]

On 12 Oct 2012, at 10:01pm, Nico Williams  wrote:

> 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.

[Jonathan Engle]

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

[Nico Williams]

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.

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

[Simon Slavin]

On 12 Oct 2012, at 6:00pm, Nico Williams  wrote:

> 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)

[Christian Smith]

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)

[Joe Mistachkin]

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)

[Caleb A. Austin]

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?

[Igor Tandetnik]

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

[Bob Price]

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)

[Joe Mistachkin]

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)

[Caleb A. Austin]

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

[Nico Williams]

On Fri, Oct 12, 2012 at 2:58 AM, Dan Kennedy  wrote:
> 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?

[Frank Chang]

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

[Pavel Ivanov]

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)

[Simon Davies]

On 11 October 2012 15:07, Alan Frankel  wrote:
> 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

[Black, Michael (IS)]

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)

[Simon Slavin]

On 11 Oct 2012, at 3:07pm, Alan Frankel  wrote:

> 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

[Joe Mistachkin]

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

[Vincent DARON]

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

[Christoph Hellwig]

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)

[Alan Frankel]

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

[Joe Mistachkin]

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

[Dan Kennedy]

On 10/11/2012 11:38 PM, Nico Williams wrote:

On Wed, Oct 10, 2012 at 12:48 PM, Richard Hipp  wrote:

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