Re: [sqlite] Simple web query tool

[Cecil Westerhof]

2017-02-04 5:54 GMT+01:00 Lindsay Lawrence :

> > "Sqlite just does it's thing with a minimum amount of fuss and minimal
> impact on system resources, can't rave enough"
>
> Yes! A while back, when I first tried what I outlined it worked so well I
> took it a step further.
>
> With nodejs async io, streams and a bit of javascript glue code, it is
> relatively easy to manage a small process pool of sqlite shells...
> eliminating the overhead of setup/teardown of cgi processes and still
> keeping the same simple pipe interface. The result, a simple, robust, high
> performance, lightweight db server over http/s with minimal external
> dependencies that runs on just about anything. Super flexible, more than
> scalable for anything I have thrown at it so far... and it just works.


​Could you share it with us?

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

Re: [sqlite] Simple web query tool

[Lindsay Lawrence]

> "Sqlite just does it's thing with a minimum amount of fuss and minimal
impact on system resources, can't rave enough"

Yes! A while back, when I first tried what I outlined it worked so well I
took it a step further.

With nodejs async io, streams and a bit of javascript glue code, it is
relatively easy to manage a small process pool of sqlite shells...
eliminating the overhead of setup/teardown of cgi processes and still
keeping the same simple pipe interface. The result, a simple, robust, high
performance, lightweight db server over http/s with minimal external
dependencies that runs on just about anything. Super flexible, more than
scalable for anything I have thrown at it so far... and it just works. (
https://en.wikipedia.org/wiki/Unix_philosophy).  NodeJS is not as
lightweight as it once was. The binary of recent versions is creeping up on
30M not too long ago it was 6Mb or so. SQLite has added some incredible
functionality in recent years and the shell cli still comes in under 1Mb
for a nicely feature-full build. Apples and oranges. Still...

/Lindsay


On Fri, Feb 3, 2017 at 2:13 PM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> >
> > Running the sqlite3 command-line shell via cgi works way better than you
> > may expect.
> >
>
> ​Yay verily, and that is really not doing a great tool justice. I've done a
> lot of similar things to what Lindsay outlines above both with web and
> application targets, Often these procedures are set up as proof of concept
> and in a sort of testing mode. I have found that the so-called test setup
> actually functioned more reliably and consistently than the application
> based code we eventually hacked up. Simple, reliable and very, very
> flexible. Sqlite just does it's thing with a minimum amount of fuss and
> minimal impact on system resources, can't rave enough. :-)​
>
>
> On 3 February 2017 at 18:29, Lindsay Lawrence 
> wrote:
>
> > Running the sqlite3 command-line shell via cgi works way better than you
> > may expect.
> > The command-line shell has a small footprint and works well with stdio in
> > batch mode.
> >
> > You can run a shell script that runs an instance of the cli shell and
> reads
> > and runs a .sql file.  The sql file and bash can be as complex as it
> needs
> > to be.  You can pass in params on the command-line by inserting env
> values
> > into a temp table and then using that table as necessary in subsequent
> sql.
> >
> > For example:
> > Configure your httpd for cgi then have a cgi script, say "*report.cgi*":
> >
> > #!/bin/bash
> > /path/to/sqlite3 -bail -batch "/path/to/my.s3db" ".read
> > /path/to/report.sql"
> >
> > and in *"/path/to/report.sql*"
> >
> > .mode html
> > .headers on
> > .print Content-Type: text/html
> > .print
> > .print 
> > select * from from report_view;
> > .print 
> >
> > For large datasets, or something you just want to import conveniently
> into
> > a spreadsheet, or another db, for further munging you could set csv mode
> > and/or force a download. As a note, unless you are sorting a very large
> > dataset the resource usage of all of this is quite low as sqlite just
> pipes
> > the dataset out over the http response as it is generated.
> >
> > /Lindsay
> >
> >
> > On Wed, Feb 1, 2017 at 8:10 AM, Jay Kreibich  wrote:
> >
> > > I'm looking for an *extremely* simple web tool that will allow me to
> > > configure a dozen or so stored queries, which people can then select
> and
> > > run on an internal server.  If the system supports a query variable or
> > two,
> > > that would be fantastic, but I don't even need that.  Any thoughts?  Or
> > do
> > > I dust off the PHP tutorials and spend an afternoon throwing something
> > > together?
> > >
> > >  -j
> > >
> > >
> > > --
> > > Jay A. Kreibich < J A Y @ K R E I B I.C H >
> > >
> > > "Intelligence is like underwear: it is important that you have it, but
> > > showing it to the wrong people has the tendency to make them feel
> > > uncomfortable." -- Angela Johnson
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple web query tool

[Michael Falconer]

>
> Running the sqlite3 command-line shell via cgi works way better than you
> may expect.
>

​Yay verily, and that is really not doing a great tool justice. I've done a
lot of similar things to what Lindsay outlines above both with web and
application targets, Often these procedures are set up as proof of concept
and in a sort of testing mode. I have found that the so-called test setup
actually functioned more reliably and consistently than the application
based code we eventually hacked up. Simple, reliable and very, very
flexible. Sqlite just does it's thing with a minimum amount of fuss and
minimal impact on system resources, can't rave enough. :-)​


On 3 February 2017 at 18:29, Lindsay Lawrence  wrote:

> Running the sqlite3 command-line shell via cgi works way better than you
> may expect.
> The command-line shell has a small footprint and works well with stdio in
> batch mode.
>
> You can run a shell script that runs an instance of the cli shell and reads
> and runs a .sql file.  The sql file and bash can be as complex as it needs
> to be.  You can pass in params on the command-line by inserting env values
> into a temp table and then using that table as necessary in subsequent sql.
>
> For example:
> Configure your httpd for cgi then have a cgi script, say "*report.cgi*":
>
> #!/bin/bash
> /path/to/sqlite3 -bail -batch "/path/to/my.s3db" ".read
> /path/to/report.sql"
>
> and in *"/path/to/report.sql*"
>
> .mode html
> .headers on
> .print Content-Type: text/html
> .print
> .print 
> select * from from report_view;
> .print 
>
> For large datasets, or something you just want to import conveniently into
> a spreadsheet, or another db, for further munging you could set csv mode
> and/or force a download. As a note, unless you are sorting a very large
> dataset the resource usage of all of this is quite low as sqlite just pipes
> the dataset out over the http response as it is generated.
>
> /Lindsay
>
>
> On Wed, Feb 1, 2017 at 8:10 AM, Jay Kreibich  wrote:
>
> > I'm looking for an *extremely* simple web tool that will allow me to
> > configure a dozen or so stored queries, which people can then select and
> > run on an internal server.  If the system supports a query variable or
> two,
> > that would be fantastic, but I don't even need that.  Any thoughts?  Or
> do
> > I dust off the PHP tutorials and spend an afternoon throwing something
> > together?
> >
> >  -j
> >
> >
> > --
> > Jay A. Kreibich < J A Y @ K R E I B I.C H >
> >
> > "Intelligence is like underwear: it is important that you have it, but
> > showing it to the wrong people has the tendency to make them feel
> > uncomfortable." -- Angela Johnson
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple web query tool

[Lindsay Lawrence]

Running the sqlite3 command-line shell via cgi works way better than you
may expect.
The command-line shell has a small footprint and works well with stdio in
batch mode.

You can run a shell script that runs an instance of the cli shell and reads
and runs a .sql file.  The sql file and bash can be as complex as it needs
to be.  You can pass in params on the command-line by inserting env values
into a temp table and then using that table as necessary in subsequent sql.

For example:
Configure your httpd for cgi then have a cgi script, say "*report.cgi*":

#!/bin/bash
/path/to/sqlite3 -bail -batch "/path/to/my.s3db" ".read /path/to/report.sql"

and in *"/path/to/report.sql*"

.mode html
.headers on
.print Content-Type: text/html
.print
.print 
select * from from report_view;
.print 

For large datasets, or something you just want to import conveniently into
a spreadsheet, or another db, for further munging you could set csv mode
and/or force a download. As a note, unless you are sorting a very large
dataset the resource usage of all of this is quite low as sqlite just pipes
the dataset out over the http response as it is generated.

/Lindsay


On Wed, Feb 1, 2017 at 8:10 AM, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.  If the system supports a query variable or two,
> that would be fantastic, but I don't even need that.  Any thoughts?  Or do
> I dust off the PHP tutorials and spend an afternoon throwing something
> together?
>
>  -j
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple web query tool

[Warren Young]

On Feb 1, 2017, at 11:45 AM, Brian Curley  wrote:
> 
> internal file shares are all that's needed to
> connect to a distributed file.

…as long as your networked file system does locking properly:

   https://www.sqlite.org/lockingv3.html#how_to_corrupt

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

Re: [sqlite] Simple web query tool

[Brian Curley]

Would the SQLite Manager extension on Firefox suffice? I don't know the
scope of your use case, but internal file shares are all that's needed to
connect to a distributed file. You can achieve variables through a
miscellaneous table and coalesce() as needed.

Regards.


On Wed, Feb 1, 2017 at 11:10 AM, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.  If the system supports a query variable or two,
> that would be fantastic, but I don't even need that.  Any thoughts?  Or do
> I dust off the PHP tutorials and spend an afternoon throwing something
> together?
>
>  -j
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple web query tool

[Doug Currie]

On Wed, Feb 1, 2017 at 11:10 AM, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.


While I wouldn't call it extremely simple, the Fossil source has a
"translate" tool that supports embedding SQLite queries and HTML generation
inline with C source code for a cgi program.

Description from: http://fossil-scm.org/index.html/artifact/33b65539a12abd07

** SYNOPSIS:
**
** Input lines that begin with the "@" character are translated into
** either cgi_printf() statements or string literals and the
** translated code is written on standard output.
**
** The problem this program is attempt to solve is as follows:  When
** writing CGI programs in C, we typically want to output a lot of HTML
** text to standard output.  In pure C code, this involves doing a
** printf() with a big string containing all that text.  But we have
** to insert special codes (ex: \n and \") for many common characters,
** which interferes with the readability of the HTML.
**
** This tool allows us to put raw HTML, without the special codes, in
** the middle of a C program.  This program then translates the text
** into standard C by inserting all necessary backslashes and other
** punctuation.
**
** Enhancement #1:
**
** If the last non-whitespace character prior to the first "@" of a
** @-block is "=" or "," then the @-block is a string literal initializer
** rather than text that is to be output via cgi_printf().  Render it
** as such.
**
** Enhancement #2:
**
** Comments of the form:  "|* @-comment: CC" (where "|" is really "/")
** cause CC to become a comment character for the @-substitution.
** Typical values for CC are "--" (for SQL text) or "#" (for Tcl script)
** or "//" (for C++ code).  Lines of subsequent @-blocks that begin with
** CC are omitted from the output.

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

Re: [sqlite] Simple web query tool

[Simon Slavin]

On 1 Feb 2017, at 4:10pm, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.  If the system supports a query variable or two,
> that would be fantastic, but I don't even need that.  Any thoughts?  Or do
> I dust off the PHP tutorials and spend an afternoon throwing something
> together?

I never found one, and ended up writing my own for a similar need, which I 
cannot share because it belongs to my employer.

PHP backend, using the sqlite3:: library which is a very thin shim over the 
standard C API, so there’s nothing to learn.  JavaScript front end, in .html 
and .js files stored on the server.  HTML5.  Given who I’m writing to I don’t 
need to tell you to sanitise your inputs.

My backend compares the IP address of the server the request comes from and the 
IP address of the server it’s running on.  If they don’t match it assumes it’s 
a hacking attempt.  I took a few other precautions like that.

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