Developing a SQLite3 DB remotely

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Developing a SQLite3 DB remotely

Stephen Chrzanowski
Right up front, I'm fully aware about the How To Corrupt document (Believe
me, I've preached about network access in this forum), and with the
development tools I have in Windows vs the destination OS and the purpose
of the DB, I'm asking for other peoples experience on remote developing a
database.

The SQLite editor of choice for me is SQLite Expert Pro (SEP).  The remote
system is a Linux based OS.  The databases job is to keep track of jobs,
hosts, last completed, priorities of the jobs, etc.  The Linux machine is
going to be running a BASH script that runs in an infinite loop,
periodically poking the database to decide what to run next based on a
schedule.  There will be frequent sleep periods between SQL calls.

While I'm developing the database, the infinite looping in the bash script
isn't going to exist.  The script runs, does its thing (To start, just ECHO
what I want it to do), update the database on successful completion, then
check for the next job if any are available.  When the scripts are done
running, I want to re-run a query in the SEP to confirm what I've done in
the BASH script did what it was supposed to do.

The question for the experienced multi-machine & multi-OS DB designers, has
anyone ever run into a problem where EXTREMELY LIGHT WEIGHT use of the
database causes corruption?  What would be a recommended way to setup the
connections for a DEV-only arena where the below paragraph describes?

By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
one permanent open file handle to the database via SEP, and that Linux OS
will only open a handle  periodically while I'm writing the script,
multiple accesses of reading or writing to the DB at the exact same time
just will not happen.  Once development stops, it'll be just this one BASH
script that will ever touch the database.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

Simon Slavin-3

On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski <[hidden email]> wrote:

> The remote
> system is a Linux based OS. […]

How 'remote' is this ?  What protocol is used to do the remote access ?

> What would be a recommended way to setup the
> connections for a DEV-only arena where the below paragraph describes?

… or is that what you’re asking for advice on here ?

> By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
> one permanent open file handle to the database via SEP, and that Linux OS
> will only open a handle  periodically while I'm writing the script,
> multiple accesses of reading or writing to the DB at the exact same time
> just will not happen.

Set a timeout of at least 10 seconds on all connections to the database.  Apart from that I can’t think of anything you haven’t mentioned.  I do more complicated things by using SQLite as a back end to a web-facing system without problems.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

Roman Fleysher
I do not have big experience in the area, but have some.

I think that light weight use is not the right thing to ask. I have seen NFS delays of 20 seconds: file was created on one machine and showed up on another after 20 seconds. This depends on how heavy OTHER things are, not how heavy SQLite access is.

GFS2 and GPFS supposedly solve file synchronization issue (by sharing disk inodes rather than files ). I never tested this (we have GPFS) and do not know about other file systems.

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Simon Slavin [[hidden email]]
Sent: Thursday, March 23, 2017 2:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Developing a SQLite3 DB remotely

On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski <[hidden email]> wrote:

> The remote
> system is a Linux based OS. […]

How 'remote' is this ?  What protocol is used to do the remote access ?

> What would be a recommended way to setup the
> connections for a DEV-only arena where the below paragraph describes?

… or is that what you’re asking for advice on here ?

> By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
> one permanent open file handle to the database via SEP, and that Linux OS
> will only open a handle  periodically while I'm writing the script,
> multiple accesses of reading or writing to the DB at the exact same time
> just will not happen.

Set a timeout of at least 10 seconds on all connections to the database.  Apart from that I can’t think of anything you haven’t mentioned.  I do more complicated things by using SQLite as a back end to a web-facing system without problems.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

Joshua J. Kugler
In reply to this post by Stephen Chrzanowski
Are you aware options for true network access?

https://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

Just thought I'd throw that out there.

j

--
Joshua J. Kugler - Fairbanks, Alaska
Azariah Enterprises - Programming and Website Design
[hidden email] - Jabber: [hidden email]
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

Roman Fleysher
No. I was not aware of these tools. Are any of them good? Maintained?

I am mostly using sqlite3 shell from bash scripts. Do you know if some of them are suitable replacements?

Is this off the topic of the  original  question?

Thank you,

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Joshua J. Kugler [[hidden email]]
Sent: Thursday, March 23, 2017 6:16 PM
To: SQLite mailing list
Subject: Re: [sqlite] Developing a SQLite3 DB remotely

Are you aware options for true network access?

https://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

Just thought I'd throw that out there.

j

--
Joshua J. Kugler - Fairbanks, Alaska
Azariah Enterprises - Programming and Website Design
[hidden email] - Jabber: [hidden email]
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

Stephen Chrzanowski
In reply to this post by Simon Slavin-3
For development, until I get the machine going, could be as far as a local
VM, or, a VM I build on one of our ESX boxes in the office.  The connection
would probably be via SMB while developing.

I'll start with the 10 seconds timeout on the remote.  I've stumbled across
a couple of examples Mr. Google has provided for me, but I didn't find much
in the lines of remote access settings for lightweight use.


On Thu, Mar 23, 2017 at 2:14 PM, Simon Slavin <[hidden email]> wrote:

>
> On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski <[hidden email]>
> wrote:
>
> > The remote
> > system is a Linux based OS. […]
>
> How 'remote' is this ?  What protocol is used to do the remote access ?
>
> > What would be a recommended way to setup the
> > connections for a DEV-only arena where the below paragraph describes?
>
> … or is that what you’re asking for advice on here ?
>
> > By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I
> have
> > one permanent open file handle to the database via SEP, and that Linux OS
> > will only open a handle  periodically while I'm writing the script,
> > multiple accesses of reading or writing to the DB at the exact same time
> > just will not happen.
>
> Set a timeout of at least 10 seconds on all connections to the database.
> Apart from that I can’t think of anything you haven’t mentioned.  I do more
> complicated things by using SQLite as a back end to a web-facing system
> without problems.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

Stephen Chrzanowski
In reply to this post by Roman Fleysher
Roman, I've never seen or heard of a file taking 20 seconds to show up,
except MAYBE in the case of a caching system that hasn't had the chance to
physically write the file out to the real destination, or a situation where
temp files are being written to somewhere else, then being dumped to
whatever you were monitoring when complete.  Either way, that kind of
action you're reporting about would make me queasy.

As for GFS or any flavor, yeah, that isn't going to happen for such a small
one-off project.  Dev, my manager, my bosses boss... they'd want my head on
a plate for introducing a completely unsupported FS. :]

On Thu, Mar 23, 2017 at 6:12 PM, Roman Fleysher <
[hidden email]> wrote:

> I do not have big experience in the area, but have some.
>
> I think that light weight use is not the right thing to ask. I have seen
> NFS delays of 20 seconds: file was created on one machine and showed up on
> another after 20 seconds. This depends on how heavy OTHER things are, not
> how heavy SQLite access is.
>
> GFS2 and GPFS supposedly solve file synchronization issue (by sharing disk
> inodes rather than files ). I never tested this (we have GPFS) and do not
> know about other file systems.
>
> Roman
>
> ________________________________________
> From: sqlite-users [[hidden email]] on
> behalf of Simon Slavin [[hidden email]]
> Sent: Thursday, March 23, 2017 2:14 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Developing a SQLite3 DB remotely
>
> On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski <[hidden email]>
> wrote:
>
> > The remote
> > system is a Linux based OS. […]
>
> How 'remote' is this ?  What protocol is used to do the remote access ?
>
> > What would be a recommended way to setup the
> > connections for a DEV-only arena where the below paragraph describes?
>
> … or is that what you’re asking for advice on here ?
>
> > By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I
> have
> > one permanent open file handle to the database via SEP, and that Linux OS
> > will only open a handle  periodically while I'm writing the script,
> > multiple accesses of reading or writing to the DB at the exact same time
> > just will not happen.
>
> Set a timeout of at least 10 seconds on all connections to the database.
> Apart from that I can’t think of anything you haven’t mentioned.  I do more
> complicated things by using SQLite as a back end to a web-facing system
> without problems.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

Bob Friesenhahn
In reply to this post by Roman Fleysher
On Thu, 23 Mar 2017, Roman Fleysher wrote:

> I do not have big experience in the area, but have some.
>
> I think that light weight use is not the right thing to ask. I have
> seen NFS delays of 20 seconds: file was created on one machine and
> showed up on another after 20 seconds. This depends on how heavy
> OTHER things are, not how heavy SQLite access is.

I have been using NFS daily since 1991 and have not seen the problem
you describe.  Sometimes such apparent problems are actually due to
improper time synchronization between computers (e.g. using NTP) so
that they don't agree on the time.

The quality of NFS lock managers varies (have improved dramatically
over the many years) and the quality of servers and clients also
varies.  A bad NFS server is likely a bad server in general (e.g. does
not synchronously persist data to underlying store when requested).  A
bad NFS client tries to improve apparent performance by intentionally
not obeying the rules.

Transaction performance over NFS may not be very good (depending on
properties of server and client) but that does not equate to
corruption or failed locking.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

Eduardo Morras-2
In reply to this post by Stephen Chrzanowski
On Thu, 23 Mar 2017 13:45:58 -0400
Stephen Chrzanowski <[hidden email]> wrote:

> Right up front, I'm fully aware about the How To Corrupt document
> (Believe me, I've preached about network access in this forum), and
> with the development tools I have in Windows vs the destination OS
> and the purpose of the DB, I'm asking for other peoples experience on
> remote developing a database.
>
> The SQLite editor of choice for me is SQLite Expert Pro (SEP).  The
> remote system is a Linux based OS.  The databases job is to keep
> track of jobs, hosts, last completed, priorities of the jobs, etc.
> The Linux machine is going to be running a BASH script that runs in
> an infinite loop, periodically poking the database to decide what to
> run next based on a schedule.  There will be frequent sleep periods
> between SQL calls.
>
> While I'm developing the database, the infinite looping in the bash
> script isn't going to exist.  The script runs, does its thing (To
> start, just ECHO what I want it to do), update the database on
> successful completion, then check for the next job if any are
> available.  When the scripts are done running, I want to re-run a
> query in the SEP to confirm what I've done in the BASH script did
> what it was supposed to do.
>
> The question for the experienced multi-machine & multi-OS DB
> designers, has anyone ever run into a problem where EXTREMELY LIGHT
> WEIGHT use of the database causes corruption?  What would be a
> recommended way to setup the connections for a DEV-only arena where
> the below paragraph describes?
>
> By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although
> I have one permanent open file handle to the database via SEP, and
> that Linux OS will only open a handle  periodically while I'm writing
> the script, multiple accesses of reading or writing to the DB at the
> exact same time just will not happen.  Once development stops, it'll
> be just this one BASH script that will ever touch the database.

I'll try this manner:

a) write a file with the query in the server
b) bash script pass it to sqlite3 command line interface and it writes output to other text file
c) read the file with the result
d) delete the files

You can grow or complicate the steps as you need.

For the network server code, repository has an example server http://www.sqlite.org/src/artifact/a2615049954cbb9c and timeline at http://www.sqlite.org/src/finfo?name=src/test_server.c 


---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Developing a SQLite3 DB remotely

jose isaias cabrera-3
In reply to this post by Stephen Chrzanowski

We have a 10 user SharedDB SQLite based app, and after correcting the
UPDATEs and INSERTS with BEGIN;s and END;s  We have never experienced
corruption.  Every so often we do get lockups, but, never corruptions.
There are a lot of checks for results, etc., so, ...


-----Original Message-----
From: Stephen Chrzanowski
Sent: Thursday, March 23, 2017 1:45 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Developing a SQLite3 DB remotely

Right up front, I'm fully aware about the How To Corrupt document (Believe
me, I've preached about network access in this forum), and with the
development tools I have in Windows vs the destination OS and the purpose
of the DB, I'm asking for other peoples experience on remote developing a
database.

The SQLite editor of choice for me is SQLite Expert Pro (SEP).  The remote
system is a Linux based OS.  The databases job is to keep track of jobs,
hosts, last completed, priorities of the jobs, etc.  The Linux machine is
going to be running a BASH script that runs in an infinite loop,
periodically poking the database to decide what to run next based on a
schedule.  There will be frequent sleep periods between SQL calls.

While I'm developing the database, the infinite looping in the bash script
isn't going to exist.  The script runs, does its thing (To start, just ECHO
what I want it to do), update the database on successful completion, then
check for the next job if any are available.  When the scripts are done
running, I want to re-run a query in the SEP to confirm what I've done in
the BASH script did what it was supposed to do.

The question for the experienced multi-machine & multi-OS DB designers, has
anyone ever run into a problem where EXTREMELY LIGHT WEIGHT use of the
database causes corruption?  What would be a recommended way to setup the
connections for a DEV-only arena where the below paragraph describes?

By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
one permanent open file handle to the database via SEP, and that Linux OS
will only open a handle  periodically while I'm writing the script,
multiple accesses of reading or writing to the DB at the exact same time
just will not happen.  Once development stops, it'll be just this one BASH
script that will ever touch the database.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...