Re: [sqlite] SQLite JDBC driver performance

[Adam Megacz]

Christopher Mason  writes:
> [Apologies for posting this here, but the sqlitejdbc list appears to be 
> defunct.]

Google deleted it without warning or explanation:

  http://www.zentus.com/sqlitejdbc/google-group-disappearance.html

It has been relocated here:

  https://lists.hcoop.net/listinfo/sqlitejdbc

  - a

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how do I know for sure that my data hit the disk?

[Adam Megacz]

"Igor Tandetnik" <[EMAIL PROTECTED]> writes:
>>> Open a separate connection to the same database, perform the update
>>> on this connection.

My understanding is that this second connection will block and then
time out if the first connection has a read operation in progress, so
I don't think this will actually work.

At this point it looks like having a thread that "goes to sleep" with
a read operation in progress basically makes it impossible for any
process to complete a write to the database.  Let me know if this
isn't true.

  - a


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how do I know for sure that my data hit the disk?

[Adam Megacz]

"Igor Tandetnik" <[EMAIL PROTECTED]> writes:
>> I have an application that absolutely must not return from a certain
>> call until the results of an update are safely committed to disk.

> Open a separate connection to the same database, perform the update on 
> this connection.

I am in an environment where the underlying operating system does not
provide reliable interprocess file locking (in fact, it flat-out
lies).  Is this still safe?

  - a

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how do I know for sure that my data hit the disk?

[Adam Megacz]

>From http://www.sqlite.org/lockingv3.html

  If multiple commands are being executed against the same SQLite
  database connection at the same time, the autocommit is deferred
  until the very last command completes. For example, if a SELECT
  statement is being executed, the execution of the command will pause
  as each row of the result is returned. During this pause other
  INSERT, UPDATE, or DELETE commands can be executed against other
  tables in the database. But none of these changes will commit until
  the original SELECT statement finishes.

I have an application that absolutely must not return from a certain
call until the results of an update are safely committed to disk.  The
situation above would be considered "not safe".  How can I perform an
update and then wait until I am completely certain that the data is on
the disk, regardless of whether or not other selects are still in
progress?  Blocking for a long time is okay, returning early is not.

I've recently had problems with the above situation occurring, my
application declaring "the data is safely on the disk", and then the
process gets killed.  When the process comes back, the data is gone.

Thanks,

  - a



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test post via gmane

[Adam Megacz]

Hopefully now that sqlite-users is on mailman it will take posts sent
via gmane.  If this message shows up, then that is the case.

  - a


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] any way to SELECT the 100th-104th row of a table?

[Adam Megacz]

Hello.  This is probably a stupid question, but...

Is there any way to include some phrase in a SELECT clause that will
match only the Nth-Mth rows of a table, for some values of N and M?

Note that ROWID isn't what I'm looking for -- if you delete rows from
a table the ROWID no longer matches the "row number".

Thanks,

  - a

-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] why doesn't this work? (fts rowids)

[Adam Megacz]

Hello, I need to create a perpetually-unique column in an FTS2 table.
For example:

  create virtual table t using fts2(c);
  insert into t (c) values ('a');
  select rowid,c from t;
  1|a
  delete from t;
  insert into t (c) values ('b');
  select rowid,c from t;
  1|b
  
How can I get the last query to return some value other than "1" (the
column need not be called ROWID; any name will work)?

  - a
  
-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380
  

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: autoincrement and fts2?

[Adam Megacz]

>> > In fts tables all columns other than rowid are of type TEXT.  It
>> > doesn't matter what you put in the type, they will be of type TEXT.
>> > The rowid is the standard SQLite rowid, so it does provide an INTEGER
>> > PRIMARY KEY AUTOINCREMENT column.

>> Scott, thanks for your reply.  However, it seems that by default, the
>> magic ROWID column is of the eternally-unique variety; rather, it is
>> of the currently-unique variety:

> As it currently stands, fts2 (and fts1, if you ever do a delete) is
> broken if you use VACUUM.

Hi, Scott...

Thanks for your reply, but, I must not be getting something here...
What does VACUUM have to do with this?  Is the bit I'm missing that
currently-unique ROWID's are eternally-unique if you never VACUUM?

If this is the case, however, fixing FTS2 so it works with VACUUM
won't (I believe) actually solve my problem, will it?  How would I go
about creating a column in an FTS2 table such that each INSERT puts an
eternally-unique value in that cell, and these values never, ever get
reused for the life of the table?

Thanks again,

  - a



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: autoincrement and fts2?

[Adam Megacz]

"Scott Hess" <[EMAIL PROTECTED]> writes:
> In fts tables all columns other than rowid are of type TEXT.  It
> doesn't matter what you put in the type, they will be of type TEXT.
> The rowid is the standard SQLite rowid, so it does provide an INTEGER
> PRIMARY KEY AUTOINCREMENT column.

Scott, thanks for your reply.  However, it seems that by default, the
magic ROWID column is of the eternally-unique variety; rather, it is
of the currently-unique variety:

If no ROWID is specified on the insert, an appropriate ROWID is
created automatically. The usual algorithm is to give the newly
created row a ROWID that is one larger than the largest ROWID in
the table prior to the insert. If the table is initially empty,
then a ROWID of 1 is used. If the largest ROWID is equal to the
largest possible integer (9223372036854775807 in SQLite version
3.0 and later) then the database engine starts picking candidate
ROWIDs at random until it finds one that is not previously used.

...

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a
slightly different ROWID selection algorithm is used

http://www.sqlite.org/autoinc.html

What I was looking for is a column whose value is unique across the
lifetime of the table (not just over the rows currently present in
it).  It seems that adding AUTOINCREMENT to a column in the table
achieves this effect.  Is this not possible under FTS2?

Thanks again for your help.

  - a

-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] autoincrement and fts2?

[Adam Megacz]

Is there any way to use a INTEGER PRIMARY KEY AUTOINCREMENT on a table
that has FTS2?  Specifying it in the obvious manner looks like it
works, but the column just ends up with nulls in it.

  - a

-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: developers mailing list, ignored patches

[Adam Megacz]

[EMAIL PROTECTED] writes:
>> I also printed out and signed the copyright papers and mailed them in.

> Your copyright release and your patches arrived in today's post.  
> The postmark is smeared somewhat but it does appear to say 
> "2? DEC 2006" (where the ? is illegible.)

> So from Oakland, CA to Charlotte, NC in only 62 days.  And one
> wonders why the nobody sends letters anymore

Heh, not surprising.

If you do get a chance to look at the filesystem detection additions,
I would appreciate hearing back on that.  I can try to break it out
into a separate patch from the autoconf stuff.

  - a

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] garbage between end of SQL and COMMIT in a dump?

[Adam Megacz]

The following message is a courtesy copy of an article
that has been posted to gmane.comp.db.sqlite.general as well.


Using sqlite 3.3.10 I'm experiencing a strange situation where .dump
appears to repeat the last few lines of the dump (not including the
final "COMMIT") twice.

Has anybody else experienced this?

Trimming out the extra lines by hand is no trouble -- I'm mostly
concerned about whether or not I'm missing data.

  - a

-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: developers mailing list, ignored patches

[Adam Megacz]

The following message is a courtesy copy of an article
that has been posted to gmane.comp.db.sqlite.general as well.


[EMAIL PROTECTED] writes:
> It is true that I do not use autoconf much.  But I do use it now
> and then.  The main problem is that autoconf is broken on my
> SuSE 10.1 installation and I have not taken the time to try to
> fix it.

A few months ago, I posted a patch to make the autoconfigury deal with
HAVE_FSCTL and HAVE_STATFS_H, as well as add a configure option for
SQLITE_ENABLE_LOCKING_STYLE.

I also printed out and signed the copyright papers and mailed them in.

I never got a response on any of this; not even a "no thanks".

Could you please let me know what happened?  If the patch is
officially rejected, please say so.  It's pretty disheartening to go
to this trouble and be ignored.

  http://www.mail-archive.com/sqlite-users%40sqlite.org/msg20672.html

  http://www.sqlite.org/cvstrac/tktview?tn=2136
  (note that the first "remark" confuses this with something else)

Below is a copy of the patch updated to work against latest CVS.
Don't forget to run 'autoreconf' after applying.

I also have a patch which exposes a configure option for FTS1; I would
be happy to contribute it.

  - a


Index: Makefile.in
===
RCS file: /sqlite/sqlite/Makefile.in,v
retrieving revision 1.163
diff -u -r1.163 Makefile.in
--- Makefile.in 17 Feb 2007 14:46:31 -  1.163
+++ Makefile.in 25 Feb 2007 19:54:42 -
@@ -32,7 +32,7 @@
 # Omitting the define will cause extra debugging code to be inserted and
 # includes extra comments when "EXPLAIN stmt" is used.
 #
-TCC += @TARGET_DEBUG@ @XTHREADCONNECT@
+TCC += @TARGET_DEBUG@ @XTHREADCONNECT@ @ENABLELOCKINGSTYLE@
 
 # Compiler options needed for programs that use the TCL library.
 #
Index: configure.ac
===
RCS file: /sqlite/sqlite/configure.ac,v
retrieving revision 1.29
diff -u -r1.29 configure.ac
--- configure.ac17 Feb 2007 14:59:18 -  1.29
+++ configure.ac25 Feb 2007 19:54:42 -
@@ -217,6 +217,21 @@
 AC_SUBST(XTHREADCONNECT)
 
 ##
+# Do we want to allow different locking styles?
+#
+AC_ARG_ENABLE(locking-style, 
+AC_HELP_STRING([--enable-locking-style],[Enable different locking 
styles]),,enable_lockingstyle=no)
+AC_MSG_CHECKING([whether to allow connections to be shared across threads])
+if test "$enable_lockingstyle" = "no"; then
+  ENABLELOCKINGSTYLE=''
+  AC_MSG_RESULT([no])
+else
+  ENABLELOCKINGSTYLE='-DSQLITE_ENABLE_LOCKING_STYLE=1'
+  AC_MSG_RESULT([yes])
+fi
+AC_SUBST(ENABLELOCKINGSTYLE)
+
+##
 # Do we want to set threadsOverrideEachOthersLocks variable to be 1 (true) by
 # default. Normally, a test at runtime is performed to determine the
 # appropriate value of this variable. Use this option only if you're sure that
@@ -571,7 +586,35 @@
 # Redefine fdatasync as fsync on systems that lack fdatasync
 #
 
+AC_CHECK_HEADER([sys/statfs.h], [TARGET_CFLAGS="$TARGET_CFLAGS 
-DHAVE_SYS_STATFS_H=1"],)
+
 AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"])
+AC_CHECK_FUNC(fsctl, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FSCTL=1"])
+
+AC_CHECK_MEMBER(struct statfs.f_flags,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FLAGS=1"],,
+  [
+#include 
+#include 
+#include 
+  ])
+
+AC_CHECK_MEMBER(struct statfs.f_type,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_TYPE=1"],,
+  [
+#include 
+#include 
+#include 
+#include 
+  ])
+
+AC_CHECK_MEMBER(struct statfs.f_fstypename,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FSTYPENAME=1"],,
+  [
+#include 
+#include 
+#include 
+  ])
 
 #
 # Generate the output files.
Index: src/os_unix.c
===
RCS file: /sqlite/sqlite/src/os_unix.c,v
retrieving revision 1.117
diff -u -r1.117 os_unix.c
--- src/os_unix.c   6 Feb 2007 11:11:08 -   1.117
+++ src/os_unix.c   25 Feb 2007 19:54:44 -
@@ -52,6 +52,9 @@
 #ifdef SQLITE_ENABLE_LOCKING_STYLE
 #include 
 #include 
+#ifdef HAVE_SYS_STATFS_H
+#include 
+#endif /* HAVE_SYS_STATFS_H */
 #include 
 #endif /* SQLITE_ENABLE_LOCKING_STYLE */
 
@@ -479,6 +482,52 @@
 #define fcntl lockTrace
 #endif /* SQLITE_LOCK_TRACE */
 
+#ifdef SQLITE_ENABLE_LOCKING_STYLE
+/**
+ *  Check to see if the OS fcntl() byte-range locking call will "lie"
+ *  to us and grant a lock that is not enforced.  This happens notably
+ *  with files in AFS (OpenAFS client <1.5.0, all OSes but Linux).
+ *
+ *  Returns zero if byte-range locks appear to work as expected.
+ */
+static int testProcessLockingBehavior(int fd_orig){
+  int fd;
+  int result;
+  struct flock lock;
+  
+  fd = dup(fd_orig);
+  if( fd<0 ) return 1;
+  memset(, 0, sizeof(struct flock));
+  lock.l_type = F_WRLCK;
+  lock.l_len = 1;
+  lock.l_start = 

[sqlite] FTS1 dump/restore needs column names

[Adam Megacz]

It seems that when doing a dump/restore on databases with FTS1
enabled, the INSERT commands in the dump need column names.  That is,
if the dump looks like this:

  INSERT INTO "foo" VALUES (x,y,z)

You get the error

  SQL error: table foo has 4 columns but 3 values were supplied

Apparently if the dump looks like this, things work fine:

  INSERT INTO "foo" (a,b,c) VALUES (x,y,z)

So, question: is there a way to get the .dump command to emit dumps in
the latter form?

Thanks,

  - a

-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: [updated] detect afs, --enable-locking-style, non-darwin fs detection

[Adam Megacz]

For the record,

  "The author or authors of this code dedicate any and all copyright
  interest in this code to the public domain. We make this dedication
  for the benefit of the public at large and to the detriment of our
  heirs and successors. We intend this dedication to be an overt act
  of relinquishment in perpetuity of all present and future rights
  this code under copyright law."

I have mailed off the copyright release.

  - a

Adam Megacz <[EMAIL PROTECTED]> writes:
> In addition to the functionality in the previous patch, this patch
> includes a new function testProcessLockingBehavior(), which is
> conceptually similar to testThreadLockingBehavior but using fork()
> instead of pthread_create().
>
> This might sound obvious: lock the first byte of a file, fork() a
> child, and have the child attempt to lock that same byte -- this
> should always fail.  Unfortunately all currently-deployed non-Linux
> AFS clients will actually "grant" locks to both processes.
>
> The added code checks for this behavior empirically before proceeding
> with posixLockingStyle.  If nonsensical behavior is observed, it
> automatically falls back to whole-file locks.
>
>   - a
>
>
> Index: configure.ac
> ===
> RCS file: /sqlite/sqlite/configure.ac,v
> retrieving revision 1.26
> diff -B -u -b -r1.26 configure.ac
> --- configure.ac  3 Jun 2006 18:02:18 -   1.26
> +++ configure.ac  26 Dec 2006 01:25:40 -
> @@ -318,6 +318,21 @@
>  AC_SUBST(XTHREADCONNECT)
>  
>  ##
> +# Do we want to allow different locking styles?
> +#
> +AC_ARG_ENABLE(locking-style, 
> +AC_HELP_STRING([--enable-locking-style],[Enable different locking 
> styles]),,enable_lockingstyle=no)
> +AC_MSG_CHECKING([whether to allow connections to be shared across threads])
> +if test "$enable_lockingstyle" = "no"; then
> +  ENABLELOCKINGSTYLE=''
> +  AC_MSG_RESULT([no])
> +else
> +  ENABLELOCKINGSTYLE='-DSQLITE_ENABLE_LOCKING_STYLE=1'
> +  AC_MSG_RESULT([yes])
> +fi
> +AC_SUBST(ENABLELOCKINGSTYLE)
> +
> +##
>  # Do we want to set threadsOverrideEachOthersLocks variable to be 1 (true) by
>  # default. Normally, a test at runtime is performed to determine the
>  # appropriate value of this variable. Use this option only if you're sure 
> that
> @@ -673,7 +688,35 @@
>  # Redefine fdatasync as fsync on systems that lack fdatasync
>  #
>  
> +AC_CHECK_HEADER([sys/statfs.h], [TARGET_CFLAGS="$TARGET_CFLAGS 
> -DHAVE_SYS_STATFS_H=1"],)
> +
>  AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"])
> +AC_CHECK_FUNC(fsctl, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FSCTL=1"])
> +
> +AC_CHECK_MEMBER(struct statfs.f_flags,
> +  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FLAGS=1"],,
> +  [
> +#include 
> +#include 
> +#include 
> +  ])
> +
> +AC_CHECK_MEMBER(struct statfs.f_type,
> +  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_TYPE=1"],,
> +  [
> +#include 
> +#include 
> +#include 
> +#include 
> +  ])
> +
> +AC_CHECK_MEMBER(struct statfs.f_fstypename,
> +  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FSTYPENAME=1"],,
> +  [
> +#include 
> +#include 
> +#include 
> +  ])
>  
>  #
>  # Put out accumulated miscellaneous LIBRARIES
> Index: Makefile.in
> ===
> RCS file: /sqlite/sqlite/Makefile.in,v
> retrieving revision 1.160
> diff -B -u -b -r1.160 Makefile.in
> --- Makefile.in   21 Dec 2006 22:38:23 -  1.160
> +++ Makefile.in   26 Dec 2006 01:25:40 -
> @@ -32,7 +32,7 @@
>  # Omitting the define will cause extra debugging code to be inserted and
>  # includes extra comments when "EXPLAIN stmt" is used.
>  #
> -TCC += @TARGET_DEBUG@ @XTHREADCONNECT@
> +TCC += @TARGET_DEBUG@ @XTHREADCONNECT@ @ENABLELOCKINGSTYLE@
>  
>  # Compiler options needed for programs that use the TCL library.
>  #
> Index: src/os_unix.c
> ===
> RCS file: /sqlite/sqlite/src/os_unix.c,v
> retrieving revision 1.114
> diff -B -u -b -r1.114 os_unix.c
> --- src/os_unix.c 21 Dec 2006 01:29:23 -  1.114
> +++ src/os_unix.c 26 Dec 2006 01:25:41 -
> @@ -52,6 +52,9 @@
>  #ifdef SQLITE_ENABLE_LOCKING_STYLE
>  #include 
>  #include 
> +#ifdef HAVE_SYS_STATFS_H
> +#include 
> +#endif /* HAVE_SYS_STATFS_H */
>  #include 
>  #endif /* SQLITE_ENABL

[sqlite] [updated] detect afs, --enable-locking-style, non-darwin fs detection

[Adam Megacz]

In addition to the functionality in the previous patch, this patch
includes a new function testProcessLockingBehavior(), which is
conceptually similar to testThreadLockingBehavior but using fork()
instead of pthread_create().

This might sound obvious: lock the first byte of a file, fork() a
child, and have the child attempt to lock that same byte -- this
should always fail.  Unfortunately all currently-deployed non-Linux
AFS clients will actually "grant" locks to both processes.

The added code checks for this behavior empirically before proceeding
with posixLockingStyle.  If nonsensical behavior is observed, it
automatically falls back to whole-file locks.

  - a


Index: configure.ac
===
RCS file: /sqlite/sqlite/configure.ac,v
retrieving revision 1.26
diff -B -u -b -r1.26 configure.ac
--- configure.ac3 Jun 2006 18:02:18 -   1.26
+++ configure.ac26 Dec 2006 01:25:40 -
@@ -318,6 +318,21 @@
 AC_SUBST(XTHREADCONNECT)
 
 ##
+# Do we want to allow different locking styles?
+#
+AC_ARG_ENABLE(locking-style, 
+AC_HELP_STRING([--enable-locking-style],[Enable different locking 
styles]),,enable_lockingstyle=no)
+AC_MSG_CHECKING([whether to allow connections to be shared across threads])
+if test "$enable_lockingstyle" = "no"; then
+  ENABLELOCKINGSTYLE=''
+  AC_MSG_RESULT([no])
+else
+  ENABLELOCKINGSTYLE='-DSQLITE_ENABLE_LOCKING_STYLE=1'
+  AC_MSG_RESULT([yes])
+fi
+AC_SUBST(ENABLELOCKINGSTYLE)
+
+##
 # Do we want to set threadsOverrideEachOthersLocks variable to be 1 (true) by
 # default. Normally, a test at runtime is performed to determine the
 # appropriate value of this variable. Use this option only if you're sure that
@@ -673,7 +688,35 @@
 # Redefine fdatasync as fsync on systems that lack fdatasync
 #
 
+AC_CHECK_HEADER([sys/statfs.h], [TARGET_CFLAGS="$TARGET_CFLAGS 
-DHAVE_SYS_STATFS_H=1"],)
+
 AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"])
+AC_CHECK_FUNC(fsctl, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FSCTL=1"])
+
+AC_CHECK_MEMBER(struct statfs.f_flags,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FLAGS=1"],,
+  [
+#include 
+#include 
+#include 
+  ])
+
+AC_CHECK_MEMBER(struct statfs.f_type,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_TYPE=1"],,
+  [
+#include 
+#include 
+#include 
+#include 
+  ])
+
+AC_CHECK_MEMBER(struct statfs.f_fstypename,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FSTYPENAME=1"],,
+  [
+#include 
+#include 
+#include 
+  ])
 
 #
 # Put out accumulated miscellaneous LIBRARIES
Index: Makefile.in
===
RCS file: /sqlite/sqlite/Makefile.in,v
retrieving revision 1.160
diff -B -u -b -r1.160 Makefile.in
--- Makefile.in 21 Dec 2006 22:38:23 -  1.160
+++ Makefile.in 26 Dec 2006 01:25:40 -
@@ -32,7 +32,7 @@
 # Omitting the define will cause extra debugging code to be inserted and
 # includes extra comments when "EXPLAIN stmt" is used.
 #
-TCC += @TARGET_DEBUG@ @XTHREADCONNECT@
+TCC += @TARGET_DEBUG@ @XTHREADCONNECT@ @ENABLELOCKINGSTYLE@
 
 # Compiler options needed for programs that use the TCL library.
 #
Index: src/os_unix.c
===
RCS file: /sqlite/sqlite/src/os_unix.c,v
retrieving revision 1.114
diff -B -u -b -r1.114 os_unix.c
--- src/os_unix.c   21 Dec 2006 01:29:23 -  1.114
+++ src/os_unix.c   26 Dec 2006 01:25:41 -
@@ -52,6 +52,9 @@
 #ifdef SQLITE_ENABLE_LOCKING_STYLE
 #include 
 #include 
+#ifdef HAVE_SYS_STATFS_H
+#include 
+#endif /* HAVE_SYS_STATFS_H */
 #include 
 #endif /* SQLITE_ENABLE_LOCKING_STYLE */
 
@@ -479,6 +482,52 @@
 #define fcntl lockTrace
 #endif /* SQLITE_LOCK_TRACE */
 
+#ifdef SQLITE_ENABLE_LOCKING_STYLE
+/**
+ *  Check to see if the OS fcntl() byte-range locking call will "lie"
+ *  to us and grant a lock that is not enforced.  This happens notably
+ *  with files in AFS (OpenAFS client <1.5.0, all OSes but Linux).
+ *
+ *  Returns zero if byte-range locks appear to work as expected.
+ */
+static int testProcessLockingBehavior(int fd_orig){
+  int fd;
+  int result;
+  struct flock lock;
+  
+  fd = dup(fd_orig);
+  if( fd<0 ) return 1;
+  memset(, 0, sizeof(struct flock));
+  lock.l_type = F_WRLCK;
+  lock.l_len = 1;
+  lock.l_start = 0;
+  lock.l_whence = SEEK_SET;
+  
+  result = fcntl(fd, F_SETLK, );
+  if (result) {
+TRACE1("testProcessLockingBehavior(): initial lock in parent failed\n");
+close(fd);
+return 1;
+  }
+  
+  if (fork()==0) {
+result = fcntl(fd, F_SETLK, );
+exit(result);
+
+  } else {
+wait();
+if (!result) {
+  TRACE1("testProcessLockingBehavior(): parent and child both got 
locks\n");
+  close(fd);
+  return 1;
+}
+  }
+  
+  close(fd);
+  return 0;
+}
+#endif /* 

[sqlite] detect afs, --enable-locking-style, non-darwin fs detection

[Adam Megacz]

AFS (the Andrew FileSystem) supports whole-file locks but not
byte-range locks.  Unfortunately, it has a problematic "feature"
whereby it will claim to grant requests for byte-range locks, but not
actually perform any locking activity.  This unfortunately can easily
lead to corruption for applications like sqlite.

The patch below tells sqlite to always use whole-file locks for files
residing in a filesystem of type "afs".  I have confirmed that with
this patch (and SQLITE_ENABLE_LOCKING_STYLE), one can successfully
write concurrently to a single database file from two different AFS
clients without corruption.  Very cool!

The development release of OpenAFS (1.5) has "proper" support for
byte-range locks; when it is finalized I will contribute code to
detect versions which properly support byte-range locking.

This patch also adds --enable-locking-style to the configure options
and is capable of detecting many popular filesystem types on both
Darwin (nice API that returns strings) or Linux (which has no API for
getting the descriptive string, unfortunately).

Please let me know if any of this should be done differently.

  - a

Index: configure.ac
===
RCS file: /sqlite/sqlite/configure.ac,v
retrieving revision 1.26
diff -B -u -b -r1.26 configure.ac
--- configure.ac3 Jun 2006 18:02:18 -   1.26
+++ configure.ac25 Dec 2006 00:41:58 -
@@ -318,6 +318,21 @@
 AC_SUBST(XTHREADCONNECT)
 
 ##
+# Do we want to allow different locking styles?
+#
+AC_ARG_ENABLE(locking-style, 
+AC_HELP_STRING([--enable-locking-style],[Enable different locking 
styles]),,enable_lockingstyle=no)
+AC_MSG_CHECKING([whether to allow connections to be shared across threads])
+if test "$enable_lockingstyle" = "no"; then
+  ENABLELOCKINGSTYLE=''
+  AC_MSG_RESULT([no])
+else
+  ENABLELOCKINGSTYLE='-DSQLITE_ENABLE_LOCKING_STYLE=1'
+  AC_MSG_RESULT([yes])
+fi
+AC_SUBST(ENABLELOCKINGSTYLE)
+
+##
 # Do we want to set threadsOverrideEachOthersLocks variable to be 1 (true) by
 # default. Normally, a test at runtime is performed to determine the
 # appropriate value of this variable. Use this option only if you're sure that
@@ -673,7 +688,35 @@
 # Redefine fdatasync as fsync on systems that lack fdatasync
 #
 
+AC_CHECK_HEADER([sys/statfs.h], [TARGET_CFLAGS="$TARGET_CFLAGS 
-DHAVE_SYS_STATFS_H=1"],)
+
 AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"])
+AC_CHECK_FUNC(fsctl, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FSCTL=1"])
+
+AC_CHECK_MEMBER(struct statfs.f_flags,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FLAGS=1"],,
+  [
+#include 
+#include 
+#include 
+  ])
+
+AC_CHECK_MEMBER(struct statfs.f_type,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_TYPE=1"],,
+  [
+#include 
+#include 
+#include 
+#include 
+  ])
+
+AC_CHECK_MEMBER(struct statfs.f_fstypename,
+  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FSTYPENAME=1"],,
+  [
+#include 
+#include 
+#include 
+  ])
 
 #
 # Put out accumulated miscellaneous LIBRARIES
Index: Makefile.in
===
RCS file: /sqlite/sqlite/Makefile.in,v
retrieving revision 1.160
diff -B -u -b -r1.160 Makefile.in
--- Makefile.in 21 Dec 2006 22:38:23 -  1.160
+++ Makefile.in 25 Dec 2006 00:41:58 -
@@ -32,7 +32,7 @@
 # Omitting the define will cause extra debugging code to be inserted and
 # includes extra comments when "EXPLAIN stmt" is used.
 #
-TCC += @TARGET_DEBUG@ @XTHREADCONNECT@
+TCC += @TARGET_DEBUG@ @XTHREADCONNECT@ @ENABLELOCKINGSTYLE@
 
 # Compiler options needed for programs that use the TCL library.
 #
Index: src/os_unix.c
===
RCS file: /sqlite/sqlite/src/os_unix.c,v
retrieving revision 1.114
diff -B -u -b -r1.114 os_unix.c
--- src/os_unix.c   21 Dec 2006 01:29:23 -  1.114
+++ src/os_unix.c   25 Dec 2006 00:41:59 -
@@ -52,6 +52,9 @@
 #ifdef SQLITE_ENABLE_LOCKING_STYLE
 #include 
 #include 
+#ifdef HAVE_SYS_STATFS_H
+#include 
+#endif /* HAVE_SYS_STATFS_H */
 #include 
 #endif /* SQLITE_ENABLE_LOCKING_STYLE */
 
@@ -587,15 +590,18 @@
 
 #ifdef SQLITE_FIXED_LOCKING_STYLE
   return (sqlite3LockingStyle)SQLITE_FIXED_LOCKING_STYLE;
-#else
+#else /*  SQLITE_FIXED_LOCKING_STYLE */
   struct statfs fsInfo;
 
   if (statfs(filePath, ) == -1)
 return sqlite3TestLockingStyle(filePath, fd);
   
+#ifdef HAVE_STATFS_F_FLAGS
   if (fsInfo.f_flags & MNT_RDONLY)
 return noLockingStyle;
+#endif /* HAVE_STATFS_F_FLAGS */
   
+#ifdef HAVE_STATFS_F_FSTYPENAME
   if( (!strcmp(fsInfo.f_fstypename, "hfs")) ||
 (!strcmp(fsInfo.f_fstypename, "ufs")) )
return posixLockingStyle;
@@ -609,14 +615,40 @@
   if(!strcmp(fsInfo.f_fstypename, "smbfs"))
 return flockLockingStyle;
   
+  

[sqlite] Re: sqlite using whole-file (not byte-range) locking

[Adam Megacz]

[EMAIL PROTECTED] writes:
> In the latest versions of SQLite (3.3.0 and later) you can provide
> SQLite with customized locking code at run-time.  So you can
> easily add AFS support that uses whole-file locking instead of
> the goofy byte-range stuff I have to do for Win95.

That's fantastic.

One other thing -- and this is absolutely not SQLite's concern, and if
it isn't handled it's no big deal -- AFS's handling of whole-file
locks is great, but currently the way it handles byte-range locks is
extremely dangerous: it always "pretends to" grant them.  That's
right, it always returns success on a byte-range lock that covers
anything less than the whole file.  Terrifying.  I recently confirmed
that you can easily corrupt databases this way.

Would it be possible for the on-disk database file format to be able
ot signal that a particular kind of locking must be used?  Could it be
done in a way that would prevent older versions of SQLite from
accidentally opening the file (ie appear incompatible)?

If this is not possible or seems like a bad design choice, I
completely understand.  I am not at all happy about the fact that AFS
pretends to grant byte-range locks (the correct behavior should be to
always refuse them if they cannot be supported), and this is really
not SQLite's problem.

> If you can wait, you might want to add AFS locking to that
> module as another option.

I'd be happy to.  Please let me know when the right time to do this
would be.

  - a

-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380

[sqlite] sqlite using whole-file (not byte-range) locking

[Adam Megacz]

I'm interested in using SQLite with AFS (the Andrew FileSystem).
Unlike NFS, AFS has solid, reliable support for *whole-file* advisory
locking across the network.

AFS does some very sophisticated caching, so an SQLite database in AFS
accessed by a single reader/writer would be very efficient.  A second
reader/writer would cause performance to degrade by breaking callbacks
quite often, but as long as whole-file locking is used, no corruption
should occur.

I think a scenario where a database is accessed mostly by a single
process but occasionally updated by other clients (for example, for
administration) would work really well.  In a lot of applications
(like the one I'm considering) this would eliminate the need for an
"administration API" implemented on the main reader/writer --
administration could be done using the sqlite3 binary directly on the
database across AFS.

I read through the locking code:

  http://www.srcdoc.com/sqlite_3.2.2/os__unix_8c-source.html#l00911

It looks like it would be possible to do the locking with whole-file
locks on three separate files (less elegant, certainly) rather than
byte range locks if one gave up support for old versions of Windows.

Does this sound like it would work?  Can anybody see any way to do it
with less than three files?  AFS offers both read-locks and
exclusive-write-locks on all platforms via fcntl().

  - a

-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380