[sqlite] [AGAIN] SQLite on network share
On Fri, Nov 13, 2015 at 11:15 PM, A. Mannini wrote: > Yes I use it in other contests but, as written in another message, in > need a serverless solutions. > A shared filesystem _is_ a network service! Since they have a system sharing a drive, they can just as easily install MySQL on it and completely bypass the file-sharing problems and corruption which _will_ happen if you try to use sqlite3 on a shared network filesystem. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] [AGAIN] SQLite on network share
> Why do you think that is a problem? (the x86_64)? > Yes there isn't a x64 Jet version. Or at least, there is the ACE x64 but can't be installed side-by-side to Office 32 bit. > Other suggests are welcomed? > Microsoft SQL Server is free (with a limitation of a 4GB database) and it > probably integrates the best with other Microsoft "technologies" (I use that > word very loosely). > Yes I use it in other contests but, as written in another message, in need a serverless solutions. Thanks A
[sqlite] [AGAIN] SQLite on network share
On 2015/11/13 10:52 PM, A. Mannini wrote: >> Basically the decision is easy - If you require either of: >> - Network data >> - User control >> >> Then you should use a suited Network DB and not a file-based DB. Best >> free (without limitations) choices are (In no particular order): >> - PostGres | http://www.postgresql.org/ >> - MariaDB / MySQL | https://www.mysql.com/ >> >> ... in fact, let me simply link you to a site with a listing already: >> http://tech.gaeatimes.com/index.php/archive/top-7-free-open-source-database-server/ >> >> >> Personally I just use the two above, MySQL especially for Web things, >> and PG for Networked systems (but that's just my preference, they both >> work either way). I use SQLite to store local data always. I don't >> wish to start a fanboy fight, but my feeling is that: MySQL is easier >> to code the broad-spectrum SQL for... Postgres is more stable, strict >> and secure. (My biased views). >> >> HTH, >> Ryan >> >> > Hi Ryan, > > your suggestions are good choices (that i use in other constests) but i > was oriented to server-less solutions because the application should be > able to work in a peer-to-peer environment without a server. Moreover I > need a solution that doesn't require much skills to setup. I would avoid > MySql/MSSQL/PostGRES/Other server setup, open ports or other possible > problems. Understandable, but that's a very odd use case, though not the first of its kind. What you need is your own file lock marshaling - perhaps write your own VFS for SQLite that do a bit of checking for file lock truth / exclusivity. The VFS capability is well-documented and many people have implemented it here, so any help you need should be easily had - but it will be quite a bit of work, and whatever method you use to ascertain file statuses over a network will be slower than not doing it. It might be worth it for your use-case. Someone might even have done it already and be willing to share the code. Ask here (in a different thread perhaps) and check google / github etc. Best of luck! Ryan
[sqlite] [AGAIN] SQLite on network share
On 2015/11/13 9:55 PM, A. Mannini wrote: > Ok, thanks for all your replies!!! > > First, i was asking to understand...before to start development in a > wrong direction. > > I don't have experience with SQLite and even less on a network share. I > would understand if corruption is a remote possibility or a certainty. > > Someone said that Access suffer the same problem... In my experience > even with 20-30 clients with low concurrency (management software) MS > Access file corruption is a rare event. > (the article you linked refer to a bug with an hotfix) > I can't use Access in my case because my application is x64. > > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.html#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... > > Other suggests are welcomed? Basically the decision is easy - If you require either of: - Network data - User control Then you should use a suited Network DB and not a file-based DB. Best free (without limitations) choices are (In no particular order): - PostGres | http://www.postgresql.org/ - MariaDB / MySQL | https://www.mysql.com/ ... in fact, let me simply link you to a site with a listing already: http://tech.gaeatimes.com/index.php/archive/top-7-free-open-source-database-server/ Personally I just use the two above, MySQL especially for Web things, and PG for Networked systems (but that's just my preference, they both work either way). I use SQLite to store local data always. I don't wish to start a fanboy fight, but my feeling is that: MySQL is easier to code the broad-spectrum SQL for... Postgres is more stable, strict and secure. (My biased views). HTH, Ryan
[sqlite] Casting ctype functions' arguments
Richard Hipp wrote:
> On 11/13/15, Michael McConville wrote:
> > Hi, everyone.
> >
> > I've been auditing the OpenBSD codebase for calls to ctype functions
> > with potentially signed chars. This is undefined on some platforms.
> > I found a number of instances in Sqlite, so I ran my Coccinelle
> > script on the repo.
>
> Thank you. You've already told us this once before.
I'm not sure if the first send ever got approved by a list moderator. It
never made it through to marc.info. I should have checked the commit
history before resending, though.
> All of your findings are either in test programs, programs used as
> part of the build process, or obsolete code that we keep around for
> historical reference but which is never in fact used. None of your
> findings are in the SQLite core. There are no security implications
> here. Nevertheless, I went through and fixed all of these cases (even
> the ones in code that is *never compiled*) a couple of weeks ago, and
> checked the changes into trunk:
>
> https://www.sqlite.org/src/info/34eb6911afee09e7
I wasn't trying to point fingers or start an argument, and I never
implied a significant security risk. Just pointing out undefined
behavior where I found it. When the change is this simple, it's easier
to just fix 'em all rather than reflect on the significance of each.
> I suppose it is too much to ask of Coccinelle to recognize that the
> following suggestion is pointless:
>
> > static int safe_isspace(char c){
> > - return (c&0x80)==0 ? isspace(c) : 0;
> > + return (c&0x80)==0 ? isspace((unsigned char)c) : 0;
> > }
Yup, too much to ask. Coccinelle does "semantic patching" - you script
the transformation you want done. It can parse C, so it's
syntax-agnostic, but it's restricted to the current transformation
you're doing. A completely different tool from general-purpose static
analyzers.
Here's the simple script I wrote:
@@
char x;
@@
(
isupper
|
isalnum
|
isalpha
|
isascii
|
isblank
|
iscntrl
|
isdigit
|
isgraph
|
islower
|
isprint
|
ispunct
|
isspace
|
isxdigit
|
toupper
|
tolower
)
- (x)
+ ((unsigned char)x)
@@
signed char x;
@@
(
isupper
|
isalnum
|
isalpha
|
isascii
|
isblank
|
iscntrl
|
isdigit
|
isgraph
|
islower
|
isprint
|
ispunct
|
isspace
|
isxdigit
|
toupper
|
tolower
)
- (x)
+ ((unsigned char)x)
[sqlite] [AGAIN] SQLite on network share
> Basically the decision is easy - If you require either of: > - Network data > - User control > > Then you should use a suited Network DB and not a file-based DB. Best > free (without limitations) choices are (In no particular order): > - PostGres | http://www.postgresql.org/ > - MariaDB / MySQL | https://www.mysql.com/ > > ... in fact, let me simply link you to a site with a listing already: > http://tech.gaeatimes.com/index.php/archive/top-7-free-open-source-database-server/ > > > Personally I just use the two above, MySQL especially for Web things, > and PG for Networked systems (but that's just my preference, they both > work either way). I use SQLite to store local data always. I don't > wish to start a fanboy fight, but my feeling is that: MySQL is easier > to code the broad-spectrum SQL for... Postgres is more stable, strict > and secure. (My biased views). > > HTH, > Ryan > > Hi Ryan, your suggestions are good choices (that i use in other constests) but i was oriented to server-less solutions because the application should be able to work in a peer-to-peer environment without a server. Moreover I need a solution that doesn't require much skills to setup. I would avoid MySql/MSSQL/PostGRES/Other server setup, open ports or other possible problems. Thanks Alessandro
[sqlite] Casting ctype functions' arguments
On 11/13/15, Michael McConville wrote:
> Hi, everyone.
>
> I've been auditing the OpenBSD codebase for calls to ctype functions
> with potentially signed chars. This is undefined on some platforms. I
> found a number of instances in Sqlite, so I ran my Coccinelle script on
> the repo.
Thank you. You've already told us this once before. All of your
findings are either in test programs, programs used as part of the
build process, or obsolete code that we keep around for historical
reference but which is never in fact used. None of your findings are
in the SQLite core. There are no security implications here.
Nevertheless, I went through and fixed all of these cases (even the
ones in code that is *never compiled*) a couple of weeks ago, and
checked the changes into trunk:
https://www.sqlite.org/src/info/34eb6911afee09e7
If you decide to run your analysis program again, I suggest running it
on the latest trunk check-in (which you can download from the
"Tarball" link here: https://www.sqlite.org/src/info/trunk). That
check-in will include all of the changes needed to silence the
warnings you have found. (Unless I missed one.)
I suppose it is too much to ask of Coccinelle to recognize that the
following suggestion is pointless:
> static int safe_isspace(char c){
> - return (c&0x80)==0 ? isspace(c) : 0;
> + return (c&0x80)==0 ? isspace((unsigned char)c) : 0;
> }
--
D. Richard Hipp
drh at sqlite.org
[sqlite] [AGAIN] SQLite on network share
On 13 Nov 2015, at 6:46pm, A. Mannini wrote: > 1) is there a list of FS where SQLite works fine? It's not usually the FS which is causing the problem. When your application tells the OS to write to a remote disk ... program calls OS API to write to a file OS calls Network FS on client Network Filing System communicates across the network NFS on server calls FS FS calls storage driver storage driver talks to hardware hardware performs the write hardware reads the sector just written hardware checks what was read matches what it was told to write hardware returns success/error code to storage driver storage driver returns success/error code to file system file system returns success/error code to server-side NFS Network Filing System communicates across the network Network FS on client returns success/error code to the OS OS API returns success/error code to the program program correctly handles success/error (Above is simplified. For example, any stage might try again if it gets an error.) Only in server-range setups (expensive, slow) is this correctly implemented. It takes a lot of time (a millisecond or two for each call) to do it right. If this setup was used for your desktop computer you'd be down to three or four characters a second in Word. I've set computers up this way for demonstrations and they are ridiculously slow and annoying to use. Like 25 minutes to from power-on to being able to type in Word. In standard desktop setups the pause for the check is skipped, either by the hardware (check your jumper settings) or in the storage driver. Instead of waiting for the check, it receives the command, returns "Command executed without error." and /then/ passes the command along to the lower level. Much faster. As you can see this occurs below file system level and is dependent on your hard drive and its driver, the DIP (jumper) settings set on your hard drive, and the mode your driver is loaded in. Worse still, manufacturers change the specification of a drive and what the jumpers mean without changing the model name. And it can happen even if both your FS and NFS are bugless. The whole thing's a nightmare. > 2) why there are SERVERLESS database (MS Access or VistaDB) that works > without FS restrictions? Both have similar problems. They all suffer from rare failures. You can use any of these databases for a year without problems and then get database corruption for no obvious reason. Simon.
[sqlite] Delta Compression in RBU
On 11/13/2015 08:06 AM, Philip Bennefall wrote: > Something I forgot in my prior post; I found the delta creation code > in sqldiff.c so my question really concerns the combined delta code > found in RBU and sqldiff.c (both creating and applying deltas). The versions of the delta creation and application code checked in to the sqlite source project are public domain. The original authors of the code re-licensed it. Dan.
[sqlite] Casting ctype functions' arguments
Hi, everyone.
I've been auditing the OpenBSD codebase for calls to ctype functions
with potentially signed chars. This is undefined on some platforms. I
found a number of instances in Sqlite, so I ran my Coccinelle script on
the repo.
The below diff was generated automatically, so formatting may be changed
and mistakes are possible (though unlikely).
Here's the relevant CERT entry:
https://www.securecoding.cert.org/confluence/display/c/STR37-C.+Arguments+to+character-handling+functions+must+be+representable+as+an+unsigned+char
Let me know what you think.
Thanks,
Michael
Index: autoconf/tea/win/nmakehlp.c
==
--- autoconf/tea/win/nmakehlp.c
+++ autoconf/tea/win/nmakehlp.c
@@ -603,15 +603,15 @@
sp = fopen(substitutions, "rt");
if (sp != NULL) {
while (fgets(szBuffer, cbBuffer, sp) != NULL) {
char *ks, *ke, *vs, *ve;
ks = szBuffer;
- while (ks && *ks && isspace(*ks)) ++ks;
+ while (ks && *ks && isspace((unsigned char)*ks)) ++ks;
ke = ks;
- while (ke && *ke && !isspace(*ke)) ++ke;
+ while (ke && *ke && !isspace((unsigned char)*ke)) ++ke;
vs = ke;
- while (vs && *vs && isspace(*vs)) ++vs;
+ while (vs && *vs && isspace((unsigned char)*vs)) ++vs;
ve = vs;
while (ve && *ve && !(*ve == '\r' || *ve == '\n')) ++ve;
*ke = 0, *ve = 0;
list_insert(&substPtr, ks, vs);
}
Index: ext/fts1/fts1.c
==
--- ext/fts1/fts1.c
+++ ext/fts1/fts1.c
@@ -203,17 +203,17 @@
** tokenizer-generated tokens rather than doing its own local
** tokenization.
*/
/* TODO(shess) Is __isascii() a portable version of (c&0x80)==0? */
static int safe_isspace(char c){
- return (c&0x80)==0 ? isspace(c) : 0;
+ return (c&0x80)==0 ? isspace((unsigned char)c) : 0;
}
static int safe_tolower(char c){
- return (c&0x80)==0 ? tolower(c) : c;
+ return (c&0x80)==0 ? tolower((unsigned char)c) : c;
}
static int safe_isalnum(char c){
- return (c&0x80)==0 ? isalnum(c) : 0;
+ return (c&0x80)==0 ? isalnum((unsigned char)c) : 0;
}
typedef enum DocListType {
DL_DOCIDS, /* docids only */
DL_POSITIONS, /* docids + positions */
Index: ext/fts1/simple_tokenizer.c
==
--- ext/fts1/simple_tokenizer.c
+++ ext/fts1/simple_tokenizer.c
@@ -136,11 +136,11 @@
for(ii=0; iipCurrent[ii];
-c->zToken[ii] = (unsigned char)ch<0x80 ? tolower(ch) : ch;
+c->zToken[ii] = (unsigned char)ch<0x80 ? tolower((unsigned char)ch) :
ch;
}
c->zToken[n] = '\0';
*ppToken = c->zToken;
*pnBytes = n;
*piStartOffset = (int) (c->pCurrent-c->pInput);
Index: ext/misc/amatch.c
==
--- ext/misc/amatch.c
+++ ext/misc/amatch.c
@@ -814,14 +814,14 @@
int nKey = (int)strlen(zKey);
int nStr = (int)strlen(zStr);
int i;
if( nStr0 && isspace(zOut[i-1]) ){ i--; }
Index: mptest/mptest.c
==
--- mptest/mptest.c
+++ mptest/mptest.c
@@ -185,14 +185,14 @@
}
c2 = *(zGlob++);
}
if( c2==0 || (seen ^ invert)==0 ) return 0;
}else if( c=='#' ){
- if( (z[0]=='-' || z[0]=='+') && isdigit(z[1]) ) z++;
- if( !isdigit(z[0]) ) return 0;
+ if( (z[0]=='-' || z[0]=='+') && isdigit((unsigned char)z[1]) ) z++;
+ if( !isdigit((unsigned char)z[0]) ) return 0;
z++;
- while( isdigit(z[0]) ){ z++; }
+ while( isdigit((unsigned char)z[0]) ){ z++; }
}else{
if( c!=(*(z++)) ) return 0;
}
}
return *z==0;
@@ -287,11 +287,11 @@
/*
** Return the length of a string omitting trailing whitespace
*/
static int clipLength(const char *z){
int n = (int)strlen(z);
- while( n>0 && isspace(z[n-1]) ){ n--; }
+ while( n>0 && isspace((unsigned char)z[n - 1]) ){ n--; }
return n;
}
/*
** Auxiliary SQL function to return the name of the VFS
@@ -442,11 +442,11 @@
if( p->n ) stringAppend(p, " ", 1);
if( z==0 ){
stringAppend(p, "nil", 3);
return;
}
- for(i=0; z[i] && !isspace(z[i]); i++){}
+ for(i=0; z[i] && !isspace((unsigned char)z[i]); i++){}
if( i>0 && z[i]==0 ){
stringAppend(p, z, i);
return;
}
stringAppend(p, "'", 1);
@@ -697,11 +697,11 @@
/*
** Return the length of the next token.
*/
static int tokenLength(const char *z, int *pnLine){
int n = 0;
- if( isspace(z[0]) || (z[0]=='/' && z[1]=='*') ){
+ if( isspace((unsigned char)z[0]) || (z[0]=='/' && z[1]=='*') ){
int inC = 0;
int c;
if( z[0]=='/' ){
inC = 1;
n = 2;
@@ -746,21 +746,21 @@
int i;
if( nIn<=0 ){
zOut[0] = 0;
return 0;
[sqlite] [AGAIN] SQLite on network share
Ok, thanks for all your replies!!! First, i was asking to understand...before to start development in a wrong direction. I don't have experience with SQLite and even less on a network share. I would understand if corruption is a remote possibility or a certainty. Someone said that Access suffer the same problem... In my experience even with 20-30 clients with low concurrency (management software) MS Access file corruption is a rare event. (the article you linked refer to a bug with an hotfix) I can't use Access in my case because my application is x64. About VistaDB it support use on network share look at http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.html#VistaDB_Introduction_SupportedPlatforms.html and confirmed from its support. Unfortunately i have not experiences with iti can't say how much this is true... Other suggests are welcomed? Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
You said you wanted something that didn't require too much skill to set up? PostgreSQL seems safer and easier than implementing VFS yourself. One is **slightly** less error-prone than the other. On Fri, Nov 13, 2015 at 7:08 PM, Keith Medcalf wrote: > You realize that the marketing translation of "support" is "make money from"? > It does not mean "works". > This. If you use SQLite or not, the list loses **very** little. But they could always use another customer. You shouldn't ask their support this questions, they are not giving you any formal guarantees. -- Bernardo Sulzbach
[sqlite] [AGAIN] SQLite on network share
Il 13/11/2015 19:31, Richard Hipp ha scritto: > On 11/13/15, A. Mannini wrote: >> Hi, >> >> i read SQLite FAQ and understood that use of SQLite on network share CAN >> corrupts database file. >> Fo me, it isn't clear if there is a way to safely use SQLite on a >> network share in contests with few clients (max 5 for ex) and low read / >> write concurrency.. >> > If your network filesystem implements file locks correctly, then > SQLite will work fine. Just be warned that there are many network > filesystems that claim to implement locks correctly, and do most of > the time, but sometimes mess up. > Ok, two questions: 1) is there a list of FS where SQLite works fine? 2) why there are SERVERLESS database (MS Access or VistaDB) that works without FS restrictions? Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
Hi, i read SQLite FAQ and understood that use of SQLite on network share CAN corrupts database file. Fo me, it isn't clear if there is a way to safely use SQLite on a network share in contests with few clients (max 5 for ex) and low read / write concurrency.. Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
On Fri, 13 Nov 2015 18:29:32 +, A. Mannini wrote: > > Hi, > > i read SQLite FAQ and understood that use of SQLite on network share CAN > corrupts database file. > Fo me, it isn't clear if there is a way to safely use SQLite on a > network share in contests with few clients (max 5 for ex) and low read / > write concurrency.. Alessandro, It's not just for you that it isn't clear. It's not clear for anyone else either. Typically, remote file systems give potentially misleading signals that a file write operation has completed, even though data are still "in flight" and may never arrive at their destination. As a consequence, there is a risk, in using SQLite or any other application, that what is stored on disk is not as intended. It's not very long ago that there was a discussion on this list about the risk of corruption on a local file-system using consumer-grade disks. For a remote file-system using similar technology, the risk cannot be less. The scale of this risk depends on how your particular remote file system and network connections are set up. The acceptability of the risk depends on what the consequences may cost in your case. People on this mailing list can't do your risk assessment or impact analysis for you. Best regards, Niall O'Reilly
[sqlite] Information passing between xBestIndex and xFilter
I think there is a flaw in information passing between the xBestIndex and xFilter methods in virtual tables. The information about the constraint operation in the aConstraint array can't reach xFilter. But how is xFilter to know how to set up the cursor when a statement like SELECT * FROM MyTable WHERE a > 10 is given? I can pass the index covering a and I can make SQLite pass the constant 10 to xFilter, but not the > operation. IMHO xBestIndex should be called after xOpen as opposed to before and have a cursor parameter so I can set up the whole filtering information on my terms. Any thoughts on that? Cheers, Johnny
[sqlite] [AGAIN] SQLite on network share
On Fri, Nov 13, 2015 at 5:04 PM, Niall O'Reilly wrote: > People on this mailing list can't do your risk assessment or > impact analysis for you. > > Best regards, > Niall O'Reilly > Seconded. You asked if there was a way to safely use it. I don't think there is. You also mentioned "max 5". In some cases your current "max" is far below what your true "max" is going to be. If you need a DB that works over a network, SQLite doesn't look like the best candidate to me. -- Bernardo Sulzbach
[sqlite] [AGAIN] SQLite on network share
> > Why do you think that is a problem? (the x86_64)? > Yes there isn't a x64 Jet version. Or at least, there is the ACE x64 but > can't be installed side-by-side to Office 32 bit. Ah, I see. Microsoft introduces artificial restrictions "because they can". Just like they could have fixed all the stupid bugs and programming errors in SQL Server in the 32-bit version, but refused to do so because they think more bits is better. Just like they cannot comprehend "data structures" and instead artificially limit all sorts of things by choosing stupid arbitrary limits that cannot be changed. You compiled your application as 64-bit and the Jet in-process com objects are 32-bit. Why not just recompile with a 32-bit compiler? > > Other suggests are welcomed? > > Microsoft SQL Server is free (with a limitation of a 4GB database) and > it probably integrates the best with other Microsoft "technologies" (I use > that word very loosely). > Yes I use it in other contests but, as written in another message, in > need a serverless solutions. There is an embedded version of SQL Server (forget what it is called) but it does not work with network filesystems either. Very little works for shared-update access over a network filesystem -- and if you turn off the "features" required to make them work (the same features that make network filesystems useable for any other purpose that does NOT include non-exclusive updating), they will then work about the same speed as a secretary with a rolodex and a pen (ie, speed measured in transactions/hour rather than transactions/second).
[sqlite] Delta Compression in RBU
Thanks for the clarification, Dan. Might be too picky but perhaps a short note should be added to the sources verifying this for people as paranoid as myself? :D Kind regards, Philip Bennefall From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at mailinglists.sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com] Sent: Friday, November 13, 2015 3:11 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Delta Compression in RBU On 11/13/2015 08:06 AM, Philip Bennefall wrote: > Something I forgot in my prior post; I found the delta creation code > in sqldiff.c so my question really concerns the combined delta code > found in RBU and sqldiff.c (both creating and applying deltas). The versions of the delta creation and application code checked in to the sqlite source project are public domain. The original authors of the code re-licensed it. Dan. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [AGAIN] SQLite on network share
On Fri, Nov 13, 2015 at 1:46 PM, A. Mannini wrote: > 2) why there are SERVERLESS database (MS Access or VistaDB) that works > without FS restrictions? If you think Access works reliably on a network share, you're going to run in to trouble sooner or later: https://support.microsoft.com/en-us/kb/2028965 (Among many other issues)
[sqlite] [AGAIN] SQLite on network share
> Ok, thanks for all your replies!!! > > First, i was asking to understand...before to start development in a > wrong direction. > > I don't have experience with SQLite and even less on a network share. I > would understand if corruption is a remote possibility or a certainty. > > Someone said that Access suffer the same problem... In my experience > even with 20-30 clients with low concurrency (management software) MS > Access file corruption is a rare event. > (the article you linked refer to a bug with an hotfix) > I can't use Access in my case because my application is x64. Why do you think that is a problem? (the x86_64)? > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.ht > ml#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... > > Other suggests are welcomed? Microsoft SQL Server is free (with a limitation of a 4GB database) and it probably integrates the best with other Microsoft "technologies" (I use that word very loosely).
[sqlite] [AGAIN] SQLite on network share
On Friday, 13 November, 2015 12:55 A. Mannini said: > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.ht > ml#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... You realize that the marketing translation of "support" is "make money from"? It does not mean "works". I don't know how you found that page, but it is not in the Table of Contents. If you are going to use their product and pay money for it, I would recommend that you get a warranty in writing that their product works for multiuser database access (including updates) via a shared filesystem using the embedded server. Often one must take with a boulder of salt most of the claims made by product marketing organizations. Most vendors will not provide you with a list of bugs, caveats, and restrictions prior to purchase, only afterwards when you have problems -- except for a very rare few.
[sqlite] Possible bug?
On 11/13/15, Quan Yong Zhai wrote: > SQLite version 3.9.2 2015-11-02 18:31:45 > sqlite> .header on > sqlite> select 0x1zzz; > zzz > 1 > sqlite> This is parsed as: SELECT 0x1 AS zzz; PostgreSQL the same thing (modulo the fact that postgres 7.3 does not support hexadecimal integer literals). So then according to the WWPD principle, this is not a bug. -- D. Richard Hipp drh at sqlite.org
[sqlite] [AGAIN] SQLite on network share
On 11/13/15, A. Mannini wrote: > Hi, > > i read SQLite FAQ and understood that use of SQLite on network share CAN > corrupts database file. > Fo me, it isn't clear if there is a way to safely use SQLite on a > network share in contests with few clients (max 5 for ex) and low read / > write concurrency.. > If your network filesystem implements file locks correctly, then SQLite will work fine. Just be warned that there are many network filesystems that claim to implement locks correctly, and do most of the time, but sometimes mess up. -- D. Richard Hipp drh at sqlite.org
[sqlite] [AGAIN] SQLite on network share
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/13/2015 11:55 AM, A. Mannini wrote: > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFram e.html#VistaDB_Introduction_SupportedPlatforms.html > > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... They don't list any supported network filesystems. Those various combinations of Windows they list speak different versions of SMB to each other. Some aren't even supported by Microsoft any more. And they don't actually say what they mean by "support". They also don't appear to provide any tool that lets you do the certification yourself. You should understand where we come from in the SQLite world. Data integrity matters. This is how things are tested: https://www.sqlite.org/testing.html - From that vantage point, other vendors can look sloppy :-) How about a tale from the past. I used to work on WAN optimizers. They intercept network traffic, compress it to reduce bandwidth, and cache plus write behind to reduce latency. One of our competitors had a shoddy implementation, that for example would paper over errors, incorrectly cache (or not flush cached) information and various other things. These conditions weren't hit too often, while the bandwidth and latency improvements were very noticeable. On talking to sites that had the competitor devices, we'd find they did notice increases in programs crashing and data file issues, but had written it off as the kind of thing that happens with Windows. ie expectations on data integrity for Windows is already pretty low, even though it wasn't at all at fault. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlZGU+UACgkQmOOfHg372QQQeQCfVIgWO1n/X7x9A0mUkMzRTvp8 9aUAn1Ma2DLPaGoQ3c9+9mIo02kGfXXR =arIX -END PGP SIGNATURE-
[sqlite] [AGAIN] SQLite on network share
On Fri, Nov 13, 2015 at 12:55 PM, A. Mannini wrote: > > Ok, thanks for all your replies!!! > > First, i was asking to understand...before to start development in a > wrong direction. > > I don't have experience with SQLite and even less on a network share. I > would understand if corruption is a remote possibility or a certainty. > > Someone said that Access suffer the same problem... In my experience > even with 20-30 clients with low concurrency (management software) MS > Access file corruption is a rare event. > (the article you linked refer to a bug with an hotfix) > I can't use Access in my case because my application is x64. > > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.html#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... The quote from that page reads: "Multi-user applications that access data on a shared network drive". The problem is that not all multi-user applications are created equal. For example, maybe there is a multi-user application that accesses data on a shared network drive, but the multiple users work different shifts and there is never more than one person using it at a time. Or perhaps each user accesses a particular customer account, and each customer account is stored in a different database or directory. In many (perhaps most) cases, there won't be any problems. You might run these types of applications for months or years without ever seeing a problem. Until one day when a problem rears its ugly head. At that point you won't really care who is at fault: SQLite, MS Access, SMB or NFS network shares, a buggy file system, a buggy operating system, buggy firmware on the drive, misconfigured hardware or software, ... the list is practically endless. The reason that SQLite warns against using network shares is because they have been a repeated source of problems. Many people use them successfully, but when they break, they break hard. Asking "which network file system is best for my data integrity" might be likened to asking "which brand of cigarettes are best for my health". You can probably answer them in some way, but the real answer is "none" in both cases. -- Scott Robison
[sqlite] shell tool is no longer dynamically linked
On Nov 12, 2015, at 1:25 PM, Lonnie Abelbeck wrote: > When upgrading from SQLite 3.8.9 to 3.9.2 I noticed our binary image grew by > about 600KB, the culprit was the /usr/bin/sqlite3 shell tool is now > statically linked instead of dynamically linked as before. Much thanks to Dan for providing an elegant solution, I configured with --disable-static-shell with this patch... (and regenerate autoconf) Add the "--enable-static-shell" option to the amalgamation autoconf script. If set (the default) the compiled shell tool is statically linked against sqlite3.o. Otherwise, it is linked against libsqlite3.so. http://www.sqlite.org/src/info/499a02a34316cada Works perfectly for both cases, but most importantly: --disable-static-shell Thanks for the prompt, elegant solution. Lonnie
[sqlite] unresolved external '__faststorefence' referenced from sqlite3.o
Hello, I am upgrading from SQLite 3.8.5 to 3.9.2, using the standard amalgamation source code (no changes, no conditional defines). The compiler I am using is Embarcadero C++Builder XE7, which uses a Clang-based (LLVM) 64-bit compiler. When building a 32-bit version of my MS-Windows-executable, the compiling and linking to SQLite3 is without problem. However, when doing this for a 64-bit version, the linking exits with an unresolved external '__faststorefence' referenced from sqlite3.o. It appears that 'MemoryBarrier()' in sqlite3MemoryBarrier(void) on lines 21025+ in sqlite3.c somehow gets translated as a macro to '__faststorefence', but only in the 64-bit compile, not in the 32-bit-compile. Version 3.8.5 has 'MemoryBarrier()' commented out, which is in 3.9.2. replaced by 'sqlite3MemoryBarrier()'. Do I need to explicitly add a library for the linking or do I need to adjust the SQLite3 source code? Is this a bug in the source code? Help is appreciated! (If this is important, my application makes use of qlite3_mutex_enter() and sqlite3_mutex_leave() for write access on the same database by parallel threads.) With kind regards, Johan Van Ooijen
[sqlite] [AGAIN] SQLite on network share
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/13/2015 10:46 AM, A. Mannini wrote: > 1) is there a list of FS where SQLite works fine? I don't know of any. Network filesystems are very hard to implement (so many corner cases), and there is a lot of complexity if you also want them to be performant. > 2) why there are SERVERLESS database (MS Access or VistaDB) that > works without FS restrictions? Vendor snake oil. Even a poorly implemented one will appear to work. As far as I can tell, Access does not have checksums or similar data integrity measures in its file format. Consequently, how would you even know? Here is a random page (possibly selling a "solution") describing how Access gets corrupted, especially on a network. Note how they say same stuff we say about using SQLite over a network: http://www.everythingaccess.com/tutorials.asp?ID=Access-Database-Corrupt ion-Repair-Guide I don't see any mention on the VistaDB pages that they support networked filesystems. Heck they claim to run on anything (eg also Mono), which by definition means they can't have tested every possibility. Unless a vendor can provide a guarantee of some kind, or at the very least some certified configuration, I wouldn't trust it either. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlZGNkMACgkQmOOfHg372QQdNQCfVECWQymsAzgikzQkuBjm01R/ rR4AnjyoSAfKBcF8hG3MxC2YXdNO0XWp =UtWN -END PGP SIGNATURE-
[sqlite] [AGAIN] SQLite on network share
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/13/2015 10:31 AM, Richard Hipp wrote: > Just be warned that there are many network filesystems that claim > to implement locks correctly, and do most of the time, but > sometimes mess up It is also worth mentioning that SQLite trusts the filesystem 100%. SQLite does not verify that what it thought was written out, is in fact the same as what just got read in[1]. Consequently it could be quite a while after corruption has happened before it is detected or effects found. Since SQLite doesn't keep redundant copies of information[2], you are unlikely to recover everything or even know what is missing/wron g. [1] Some sort of checksumming mechanism would help. It got rejected: http://www.sqlite.org/src/info/72b01a982a [2] Indexes are the exception, although recovering information from them isn't particularly practical Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlZGMC0ACgkQmOOfHg372QRb7QCeJOIRGKRWY0lFFYzz8Fn+8l6L IeUAoKzyOO51ldK6xm2f3XK9PuzUTuRG =wvUQ -END PGP SIGNATURE-
[sqlite] shell tool is no longer dynamically linked
2015-11-12 20:25 GMT+01:00 Lonnie Abelbeck:
> My current solution is to apply this build patch:
> And that seems to restore the previous behavior to dynamically link the
> sqlite3 shell tool.
Actually, Makefile.in is generated from Makefile.am, so the correct patch would
be as below in the SQLite sources. I noticed the same for the Cygwin build
(I'm Cygwin's SQLite package maintainer), and put the patch below in the
build system, so it will be applied with every new SQLite release. Problem
solved for me.
> It would be nice if like Fossil (--with-internal-sqlite), sqlite had a
> configure option to determine if the shell tool would be statically or
> dynamically linked, ex. --with-static-shell-tool
>
> I would expect sqlite would default to dynamically linked (if --enable-shared
> is passed), not sure why the change to static.
+1
If it would be configurable, that indeed would be best. Then it even
doesn't matter to me
what the default is.
My guess is the more distributions (e.g. Fedora, Ubuntu ) will
make (or have already
made) the same modification, since it's much easier to apply a patch than change
a distribution policy ;-)
Regards,
Jan Nijtmans
==
--- autoconf/Makefile.am
+++ autoconf/Makefile.am
@@ -4,16 +4,16 @@
lib_LTLIBRARIES = libsqlite3.la
libsqlite3_la_SOURCES = sqlite3.c
libsqlite3_la_LDFLAGS = -no-undefined -version-info 8:6:8
bin_PROGRAMS = sqlite3
-sqlite3_SOURCES = shell.c sqlite3.c sqlite3.h
-sqlite3_LDADD = @READLINE_LIBS@
-sqlite3_CFLAGS = $(AM_CFLAGS)
+sqlite3_SOURCES = shell.c
+sqlite3_LDADD = $(top_builddir)/libsqlite3.la @READLINE_LIBS@
+sqlite3_DEPENDENCIES = $(top_builddir)/libsqlite3.la
include_HEADERS = sqlite3.h sqlite3ext.h
EXTRA_DIST = sqlite3.1 tea
pkgconfigdir = ${libdir}/pkgconfig
pkgconfig_DATA = sqlite3.pc
man_MANS = sqlite3.1
[sqlite] Delta Compression in RBU
Something I forgot in my prior post; I found the delta creation code in sqldiff.c so my question really concerns the combined delta code found in RBU and sqldiff.c (both creating and applying deltas). Kind regards, Philip Bennefall
[sqlite] Delta Compression in RBU
Hi Richard and others, I am looking at the RBU extension, and the delta compression functionality in particular. I am interested in using the delta compression code (both as part of the RBU extension itself but possibly also externally by extracting it from RBU). I see that the delta compression was lifted from Fossil which is under the BSD license, but of course I also see that the RBU extension has a public domain dedication at the top just like all the other official SqLite extensions. So I really just wanted to verify that even though it is lifted from Fossil, the delta compression code has the same public domain clearance guarantee as the rest of sqLite? Is this assumption true, or could there potentially be contributions from other Fossil developers that are still under the terms of the BSD license? Thanks! Kind regards, Philip Bennefall
[sqlite] Array or set type or some other indexable data?
op 12-11-2015 17:35 schreef J Decker op d3ck0r at gmail.com: > On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma wrote: >> 12 nov 2015, om 07:02, J Decker: >> >>> So I've used CTE to solve a simple problem... I'm tempted to use it to >>> fix more problems... but I'm wondering how to select different values >>> at different levels. I know there's like 'select * from table where >>> column in ( set,of,things) ' but can I index into the set? is there >>> some sort of way I can specify an array of values? >>> >>> would like to do something like ' select value from option_map where >>> name is ["system","device","com port", "1" ] ' where the program >>> statement would look more like >>> >>> GetOptionValue ( "/system/device/com port/1" ) where the name is >>> really sort of registry like and variable in length... >>> >>> I could probably do some sort of indexing passing that exact string >>> through and parsing in SQL the substrings of interest based on the >>> level of the query... but would rather preparse the string. >> >> >> Below is another possible answer. This uses a recursive cte to split an >> input full path name into seperate names. ... >> with walk as ( ... >> > > was hoping to not have to do the substr part in the query > and would like the path to be more on the external usage of 'walk' in > this case than inside the expression > So something like "select value from option_map_view where path is "? A path name like '/system/device/com port/1' is used as an array of names here. Only the indexing with intst and substr is laborious. Maybe some future SQLite version includes a group_split function to make this easier. It seems impossible to me in plain SQL to write an efficient view for a query like this. As you say below the whole map need to be walked at each query to find a match. User defined functions, virtual tables or function based indexes may offer an efficient solution. > would be better than building up the address to get a comparison at > the end since the whole map would have to be walked. >
