Re: [sqlite] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 16 Jan 2018, at 2:25pm, Richard Hipp  wrote:

> On 1/16/18, Paul Sanderson  wrote:
> 
>> That terminal app is still sandboxed. AFAIAA you essentially get access to
>> the applictaios data folder and you can add, create, delete, etc files
>> within it.
> 
> Right.  And so it is apparently not possible to compile a command-line
> application (like "sqlite3" or "kvtest"), download that application to
> the phone, then run it from the terminal app.  If I am underestimating
> the capabilities of the terminal app, please correct me.

There is no 'Terminal' App.  None are supplied.  There’s no command-line access 
to Unix.  You cannot let the user run arbitrary programs like 'ps' because the 
user has no access to any command-line shells like 'sh'.

The following is slightly simplified for clarity, and includes stuff Dr H knows 
so it is useful for other readers of this list.  Where I write 'phone', I mean 
iPhones and iPads.

Installing Apps under iOS
-

If you’re registered as an Apple Developer, and.installing Apps on phones 
belonging to your own organisation, you can install your own Apps on those 
phones, using Apple’s software on your Mac.  If you do not qualify for the 
above, you can install Apps on the phone only by downloading them from Apple’s 
App Store.  You could go to a web site and download an App, but there’s no way 
to tell the phone to run it.

Apps submitted to Apple for the App Store are supplied as a source code package 
suitable for compiling with Xcode (not quite, but you get the idea).  Apple 
scans the source code, checking to see which Unix calls they make, rejecting 
those which do things Apple doesn’t like, like trying to access the kernel or 
hardware directly.  Apps which do make it to the Store are compiled then 
'signed' with a checksum using Apple’s process.  If iOS tries to start up an 
App which fails its checksum it’ll issue an error message saying it’s corrupt.

Apps running under iOS
--

Apps running under iOS think they have access to the full unix folder tree.  
But in reality they have access only to folders in their own sandbox, plus 
special read-only access to some commonly-used unix files.  Attempts to use 
standard unix calls to look around the drive/folder/file structure, to find 
other Apps and their data files, see only the current App’s sandbox.

Similar things are done with other Unix internals.  'kill', and its underlying 
Unix call, have no effect on processes which don’t belong to the App that calls 
'kill'.  I believe the last time I read anything about it, you could list other 
processes, you just couldn’t do anything to them.  You can’t use 'nice' to 
starve other processes of CPU time.  Stuff like that.

iOS supplies an API for moving files into and out of a common area, viewable in 
the GUI using an Apple App called "Files".  A special API must be used to 
access this area.  Each App can use that API to access the common area, list 
its contents, and copy to and from it.  An App has no way to find the shared 
area using normal drive/folder/file calls.  For two Apps to share a data file, 
one App would have to copy it into the common area and the other would have to 
copy it back out.

What would a 'Terminal' App do ?


So suppose you took a copy of 'bash', added a 25-line 80-column user-interface 
to it, and made an App out of it.  What would happen ?  The answer is that it 
would apparently work fine but be subject to the above restrictions.  
Operations to iterate through drives would fail.  Attempts to read the folder 
structure of the default drive would reveal only the folder structure of the 
sandbox.  Unix utility calls which interfere with other Apps/processes would 
return error messages.

So you might create a 'Terminal'-style application, and include in its 
application Bundle a copy of sqlite3 compiled for iOS.  The only database files 
it could access would be ones stores in the sandbox of your 'Terminal' App.

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[Richard Hipp]

On 1/16/18, Paul Sanderson  wrote:
> That terminal app is still sandboxed. AFAIAA you essentially get access to
> the applictaios data folder and you can add, create, delete, etc files
> within it.

Right.  And so it is apparently not possible to compile a command-line
application (like "sqlite3" or "kvtest"), download that application to
the phone, then run it from the terminal app.  If I am underestimating
the capabilities of the terminal app, please correct me.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Dominique Devienne]

On Tue, Jan 16, 2018 at 10:51 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> That terminal app is still sandboxed. AFAIAA you essentially get access to
> the application's data folder and you can add, create, delete, etc files
> within it.
>

Sounds good enough, no?

But really, what I'd *really* like, would be for Richard to introduce an
abstraction similar to SQLite's VFS
(or extending it) for a "virtual terminal", and have shell.c coded using
that abstraction. That way the shell,
which is feature packed, really good, and "official", could be used
anywhere that abstraction is implemented.
(and just like VFSs, there'd be a "terminal-based" default VFL impl with
pretty much the current code).

That would allow the shell to run outside a terminal, in a GUI windows for
example.
Or in a web-browser (via compilation for ASM.js or WebAssembly or PNaCl,
etc...)
w/o the need to also emulate a terminal.

No funny terminal business with escape codes for bold or red, make it part
of the abstraction, for example.
Same thing with completion or command line history, could be part of the
abstraction.

The VFS abstraction is a big part of SQLite's ubiquity, and a similar
abstraction to decouple the Shell
from stdin/stdout/stderr and open/close would be great IMHO. The shell.c
could assume UTF-8 everywhere
and rely on the VTL (virtual terminal layer) for conversion to a different
codepage in a terminal, or translating
filenames if necessary on the current platform, etc... (VFSs already do the
latter, no?)

There could then also be a new .mode that fully delegates the styling of
the resultset to the VTL,
to display it in a GUI table-control for example. DRH is great at this
stuff, we all know that.

Now this is all wishful thinking, and Richard has plenty other things he'd
rather do instead,
and might not care at all for the idea above (which is not even novel I'm
sure), but there's
a kinda bug missed opportunity here IMHO. FWIW :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Paul Sanderson]

That terminal app is still sandboxed. AFAIAA you essentially get access to
the applictaios data folder and you can add, create, delete, etc files
within it.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 16 January 2018 at 09:39, John G  wrote:

> I've not tried it, but this article from OSXdaily says you can get the
> command line (Terminal) in iOS.
>
> http://osxdaily.com/2018/01/08/get-terminal-app-ios-command-line/
>
> That probably does not solve the fork requirement, and I'm sure it is
> sandboxed.
>
> John G
>
>
>
> On 15 January 2018 at 15:00, Richard Hipp  wrote:
>
> > On 1/15/18, Shane Dev  wrote:
> > >
> > > Did the Apple engineers tell you why it is not possible to compile and
> > run
> > > the SQLite shell on iOS?
> > >
> >
> > You cannot get a command-line prompt on iOS, and the SQLite shell
> > requires a command-line prompt (like bash).  If i understand
> > correctly, iOS does not allow fork() as a security measure.
> >
> > The previous paragraph is not authoritative.  It is merely my
> > recollection.  I have not researched the issue.
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[John G]

I've not tried it, but this article from OSXdaily says you can get the
command line (Terminal) in iOS.

http://osxdaily.com/2018/01/08/get-terminal-app-ios-command-line/

That probably does not solve the fork requirement, and I'm sure it is
sandboxed.

John G



On 15 January 2018 at 15:00, Richard Hipp  wrote:

> On 1/15/18, Shane Dev  wrote:
> >
> > Did the Apple engineers tell you why it is not possible to compile and
> run
> > the SQLite shell on iOS?
> >
>
> You cannot get a command-line prompt on iOS, and the SQLite shell
> requires a command-line prompt (like bash).  If i understand
> correctly, iOS does not allow fork() as a security measure.
>
> The previous paragraph is not authoritative.  It is merely my
> recollection.  I have not researched the issue.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Can an SQL script be built from within sqlite?

[petern]

>Are you the developer / maintainer of the SQLite shell?

THE developer is D Richard Hipp.  He is the decisionmaker and principal
developer of the SQLite releases originated from sqlite.org.  For all
practical purposes, that is the official release.

On the other hand, because of the open license, anybody can make changes
their copy of the SQLite which best traverse the critical paths of their
own project(s).  There are also extension interfaces which enable writing
native target code that is forward compatible with later releases of
SQLite.  Wrapping your own native code in those extension interfaces is the
best way to implement special features for a project/product.

Speaking of critical paths, it seems that my question uncovered some
critical paths you weren't aware of.  To assume your application will run
everywhere without a supporting technology (or your own code) which tests
such a guarantee is a mistake.   SQLite shell makes no such guarantee.

My advice would be to figure out all of your project's critical paths
before plunging into development - except for the narrowest possible
development required to figure out a critical path.

Peter




On Mon, Jan 15, 2018 at 2:08 AM, Shane Dev  wrote:

> Hi Peter,
>
> Ideally, I would like to execute a series of "test cases" from within the
> SQLite shell without dependency on an external scripting language. These
> would not normally be executed by the application end user. Initially, I
> planned to implement this with triggers but I see now that the results of
> executing a given statement from within a trigger can be different from
> executing the same statement via the SQLite shell (which I assume is
> calling sqlite3_step or sqlite3_exec)
>
> For conditional logic, case expressions are currently sufficient for my
> needs. So far, I have not needed to execute a script periodically but it
> might useful in the future.
>
> Are you the developer / maintainer of the SQLite shell?
>
> On 15 January 2018 at 01:30, petern  wrote:
>
> > Shane.  That's very interesting considering the effort to make the one
> > thing happen exactly once without external software dependency.
> > Does the capability to write specially named local files but not have a
> > periodic loop nor network capability somehow get your application off the
> > ground?
> > Based on your problem statement, the user would have to initiate your
> > script and know when/if it is required to be run...
> >
> > I had in mind adding periodic and conditional dot commands to SQLite
> shell
> > - to simulate continuous operation of application code.  If you've
> figured
> > out a way around needing such things to make a useful standalone SQLite
> > application, I would be very interested to understand how that works.
> >
> > Peter
> >
> >
> >
> >
> >
> > On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev  wrote:
> >
> > > Hi Simon,
> > >
> > > I have found a way achieve this purely in the SQLite shell. The trick
> is
> > to
> > > make all rows in tcout1 SQL statements and then execute them.
> > >
> > > sqlite> CREATE TABLE tcout1(sql text);
> > > sqlite> CREATE TABLE tcout2(sql text);
> > > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > > '.headers off';";
> > > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > > '.once tc'||strftime('%s','now');";
> > > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > > 'select * from tc;';";
> > > sqlite> .once tcout1.sql
> > > sqlite> select * from tcout1;
> > > sqlite> .read tcout1.sql
> > > sqlite> select * from tcout2;
> > > .headers off
> > > .once tc1515968593
> > > select * from tc;
> > >
> > >
> > > On 13 January 2018 at 19:57, Simon Slavin 
> wrote:
> > >
> > > > On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:
> > > >
> > > > > Is there a way to execute the contents of certain rows (the second
> > row
> > > in
> > > > > this example) and replace it with its own result to create second
> > > table /
> > > > > view which could interpreted by the sqlite shell?
> > > >
> > > > Not inside the SQLite shell.
> > > >
> > > > Looks like you need to learn programming.  Or at least how to script
> > your
> > > > OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which
> > shell
> > > > are you using ?
> > > >
> > > > Simon.
> > > > ___
> > > > 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
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > 

Re: [sqlite] Can an SQL script be built from within sqlite?

[Peter Da Silva]

I would have thought that the logical platform-independent scripting language 
to use with sqlite would be tcl. By default tcl comes with sqlite built in, so 
you don’t even need to compile anything.

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 15 Jan 2018, at 3:33pm, J Decker  wrote:

> But; dropbear ssh sometimes works?  Some have issues making it work
> so a shell is possible; they just don't have a termianl in the store?
> Would be a pretty limited userbase

You can install a GUI program to telnet/ssh /out/ of the iPhone/iPad and access 
the command-line on a computer.  I’ve seen sysadmins use iPads that way.  You 
can’t write a program to shell into the iDevice.  It doesn’t run the 
appropriate daemons.

There is no command-line shell in iOS.  You might find 'sh' installed, but 
there’s no way, short of jailbreaking the phone, to run it.  And if you did run 
it via a GUI app, it would be sandboxed, with no way to access files outside of 
its own storage area.  Because sandboxing and strong security are what ensures 
nothing can interfere with the phone functions of the iPhone, which is a legal 
requirement.

Of course, you could jailbreak your iPhone.  But frankly it’s simpler to leave 
your iPhone all nice and secure and buy a cheap Android phone for hacking on.

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[J Decker]

But; dropbear ssh sometimes works?  Some have issues making it work
so a shell is possible; they just don't have a termianl in the store?
Would be a pretty limited userbase

On Mon, Jan 15, 2018 at 7:00 AM, Richard Hipp  wrote:

> On 1/15/18, Shane Dev  wrote:
> >
> > Did the Apple engineers tell you why it is not possible to compile and
> run
> > the SQLite shell on iOS?
> >
>
> You cannot get a command-line prompt on iOS, and the SQLite shell
> requires a command-line prompt (like bash).  If i understand
> correctly, iOS does not allow fork() as a security measure.
>
> The previous paragraph is not authoritative.  It is merely my
> recollection.  I have not researched the issue.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Can an SQL script be built from within sqlite?

[Richard Hipp]

On 1/15/18, Shane Dev  wrote:
>
> Did the Apple engineers tell you why it is not possible to compile and run
> the SQLite shell on iOS?
>

You cannot get a command-line prompt on iOS, and the SQLite shell
requires a command-line prompt (like bash).  If i understand
correctly, iOS does not allow fork() as a security measure.

The previous paragraph is not authoritative.  It is merely my
recollection.  I have not researched the issue.

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Interesting. SQLite is written in ANSI C. Objective-C is a strict superset
of ANSI C. Objective-C can be used to write software for OS X and iOS.

Did the Apple engineers tell you why it is not possible to compile and run
the SQLite shell on iOS?

On 15 January 2018 at 02:16, Richard Hipp  wrote:

> On 1/14/18, Simon Slavin  wrote:
> >
> > I’ve never seen anyone compile sqlite3 (the shell tool) for iOS.
> Android I
> > have even less idea about.  Can you run command-line tools on Android ?
> Has
> > anyone compiled sqlite3 for it ?
> >
>
> You can easily compile and run all of the SQLite command-line tools
> for Android.  There are simple instructions at the bottom of the
> https://www.sqlite.org/fasterthanfs.html document.
>
> The engineers at Apple tell me this is not possible on iOS.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Can an SQL script be built from within sqlite?

[Shane Dev]

Sorry, false alarm, the text is correct

On 15 January 2018 at 13:36, Shane Dev  wrote:

> Hi Simon,
>
> .selftest looks interesting
>
> I think there is a typo in section 13 -
>
> The .selftest command reads the rows of the selftest table in selftest.tno
> order.
>
> On 15 January 2018 at 12:06, Simon Slavin  wrote:
>
>>
>>
>> On 15 Jan 2018, at 10:08am, Shane Dev  wrote:
>>
>> > Ideally, I would like to execute a series of "test cases" from within
>> the
>> > SQLite shell without dependency on an external scripting language. These
>> > would not normally be executed by the application end user.
>>
>> Commands suited to that were added in a recent update to the shell tool.
>> Documentation is here:
>>
>> 
>>
>> See especially
>>
>> .check
>> .selftest
>> .sha3sum
>> .testcase
>>
>> and section 13.
>>
>> Simon.
>> ___
>> 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] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simon,

.selftest looks interesting

I think there is a typo in section 13 -

The .selftest command reads the rows of the selftest table in selftest.tno
order.

On 15 January 2018 at 12:06, Simon Slavin  wrote:

>
>
> On 15 Jan 2018, at 10:08am, Shane Dev  wrote:
>
> > Ideally, I would like to execute a series of "test cases" from within the
> > SQLite shell without dependency on an external scripting language. These
> > would not normally be executed by the application end user.
>
> Commands suited to that were added in a recent update to the shell tool.
> Documentation is here:
>
> 
>
> See especially
>
> .check
> .selftest
> .sha3sum
> .testcase
>
> and section 13.
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 15 Jan 2018, at 10:08am, Shane Dev  wrote:

> Ideally, I would like to execute a series of "test cases" from within the
> SQLite shell without dependency on an external scripting language. These
> would not normally be executed by the application end user.

Commands suited to that were added in a recent update to the shell tool.  
Documentation is here:



See especially

.check
.selftest
.sha3sum
.testcase

and section 13.

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Peter,

Ideally, I would like to execute a series of "test cases" from within the
SQLite shell without dependency on an external scripting language. These
would not normally be executed by the application end user. Initially, I
planned to implement this with triggers but I see now that the results of
executing a given statement from within a trigger can be different from
executing the same statement via the SQLite shell (which I assume is
calling sqlite3_step or sqlite3_exec)

For conditional logic, case expressions are currently sufficient for my
needs. So far, I have not needed to execute a script periodically but it
might useful in the future.

Are you the developer / maintainer of the SQLite shell?

On 15 January 2018 at 01:30, petern  wrote:

> Shane.  That's very interesting considering the effort to make the one
> thing happen exactly once without external software dependency.
> Does the capability to write specially named local files but not have a
> periodic loop nor network capability somehow get your application off the
> ground?
> Based on your problem statement, the user would have to initiate your
> script and know when/if it is required to be run...
>
> I had in mind adding periodic and conditional dot commands to SQLite shell
> - to simulate continuous operation of application code.  If you've figured
> out a way around needing such things to make a useful standalone SQLite
> application, I would be very interested to understand how that works.
>
> Peter
>
>
>
>
>
> On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev  wrote:
>
> > Hi Simon,
> >
> > I have found a way achieve this purely in the SQLite shell. The trick is
> to
> > make all rows in tcout1 SQL statements and then execute them.
> >
> > sqlite> CREATE TABLE tcout1(sql text);
> > sqlite> CREATE TABLE tcout2(sql text);
> > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > '.headers off';";
> > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > '.once tc'||strftime('%s','now');";
> > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > 'select * from tc;';";
> > sqlite> .once tcout1.sql
> > sqlite> select * from tcout1;
> > sqlite> .read tcout1.sql
> > sqlite> select * from tcout2;
> > .headers off
> > .once tc1515968593
> > select * from tc;
> >
> >
> > On 13 January 2018 at 19:57, Simon Slavin  wrote:
> >
> > > On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:
> > >
> > > > Is there a way to execute the contents of certain rows (the second
> row
> > in
> > > > this example) and replace it with its own result to create second
> > table /
> > > > view which could interpreted by the sqlite shell?
> > >
> > > Not inside the SQLite shell.
> > >
> > > Looks like you need to learn programming.  Or at least how to script
> your
> > > OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which
> shell
> > > are you using ?
> > >
> > > Simon.
> > > ___
> > > 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
> >
> ___
> 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] Can an SQL script be built from within sqlite?

[Richard Hipp]

On 1/14/18, Simon Slavin  wrote:
>
> I’ve never seen anyone compile sqlite3 (the shell tool) for iOS.  Android I
> have even less idea about.  Can you run command-line tools on Android ?  Has
> anyone compiled sqlite3 for it ?
>

You can easily compile and run all of the SQLite command-line tools
for Android.  There are simple instructions at the bottom of the
https://www.sqlite.org/fasterthanfs.html document.

The engineers at Apple tell me this is not possible on iOS.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Brian Curley]

I use Termux on Android. Very useful build of sqlite available there.

Regards.

Brian P Curley


On Jan 14, 2018 7:42 PM, "Simon Slavin"  wrote:

>
>
> On 15 Jan 2018, at 12:30am, petern  wrote:
>
> > Shane.  That's very interesting considering the effort to make the one
> > thing happen exactly once without external software dependency.
>
> On 13 Jan 2018, at 7:33pm, Shane Dev  wrote:
>
> > I use mainly Linux (bash) and Windows (powershell) but my target
> platforms
> > also include Android, iOS, IoT (anything that can link against the sqlite
> > library).
>
> I’ve never seen anyone compile sqlite3 (the shell tool) for iOS.  Android
> I have even less idea about.  Can you run command-line tools on Android ?
> Has anyone compiled sqlite3 for it ?
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 15 Jan 2018, at 12:30am, petern  wrote:

> Shane.  That's very interesting considering the effort to make the one
> thing happen exactly once without external software dependency.

On 13 Jan 2018, at 7:33pm, Shane Dev  wrote:

> I use mainly Linux (bash) and Windows (powershell) but my target platforms
> also include Android, iOS, IoT (anything that can link against the sqlite
> library).

I’ve never seen anyone compile sqlite3 (the shell tool) for iOS.  Android I 
have even less idea about.  Can you run command-line tools on Android ?  Has 
anyone compiled sqlite3 for it ?

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[petern]

Shane.  That's very interesting considering the effort to make the one
thing happen exactly once without external software dependency.
Does the capability to write specially named local files but not have a
periodic loop nor network capability somehow get your application off the
ground?
Based on your problem statement, the user would have to initiate your
script and know when/if it is required to be run...

I had in mind adding periodic and conditional dot commands to SQLite shell
- to simulate continuous operation of application code.  If you've figured
out a way around needing such things to make a useful standalone SQLite
application, I would be very interested to understand how that works.

Peter





On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev  wrote:

> Hi Simon,
>
> I have found a way achieve this purely in the SQLite shell. The trick is to
> make all rows in tcout1 SQL statements and then execute them.
>
> sqlite> CREATE TABLE tcout1(sql text);
> sqlite> CREATE TABLE tcout2(sql text);
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> '.headers off';";
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> '.once tc'||strftime('%s','now');";
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> 'select * from tc;';";
> sqlite> .once tcout1.sql
> sqlite> select * from tcout1;
> sqlite> .read tcout1.sql
> sqlite> select * from tcout2;
> .headers off
> .once tc1515968593
> select * from tc;
>
>
> On 13 January 2018 at 19:57, Simon Slavin  wrote:
>
> > On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:
> >
> > > Is there a way to execute the contents of certain rows (the second row
> in
> > > this example) and replace it with its own result to create second
> table /
> > > view which could interpreted by the sqlite shell?
> >
> > Not inside the SQLite shell.
> >
> > Looks like you need to learn programming.  Or at least how to script your
> > OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell
> > are you using ?
> >
> > Simon.
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 14 Jan 2018, at 10:33pm, Shane Dev  wrote:

> I have found a way achieve this purely in the SQLite shell. The trick is to
> make all rows in tcout1 SQL statements and then execute them.

That’s clever.  Nice one.

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[Tony Papadimitriou]

You can use .output file and .print for most cases (except those that need a 
calculated result) to simplify your script a bit.


-Original Message- 
From: Shane Dev


I have found a way achieve this purely in the SQLite shell. The trick is to
make all rows in tcout1 SQL statements and then execute them.

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simon,

I have found a way achieve this purely in the SQLite shell. The trick is to
make all rows in tcout1 SQL statements and then execute them.

sqlite> CREATE TABLE tcout1(sql text);
sqlite> CREATE TABLE tcout2(sql text);
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'.headers off';";
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'.once tc'||strftime('%s','now');";
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'select * from tc;';";
sqlite> .once tcout1.sql
sqlite> select * from tcout1;
sqlite> .read tcout1.sql
sqlite> select * from tcout2;
.headers off
.once tc1515968593
select * from tc;


On 13 January 2018 at 19:57, Simon Slavin  wrote:

> On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:
>
> > Is there a way to execute the contents of certain rows (the second row in
> > this example) and replace it with its own result to create second table /
> > view which could interpreted by the sqlite shell?
>
> Not inside the SQLite shell.
>
> Looks like you need to learn programming.  Or at least how to script your
> OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell
> are you using ?
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[J Decker]

I could do it in node.js pretty easy
2 ways (I think).
```
var sack = require( "sack.vfs" );
var db = sack.Sqlite( "test.db" );

function type1( condition ) {
db.do( `select sql from commands where
${db.escape(condition)}` ).forEach( record=>{ db.do( record.sql ) }` );
}

function type2( condition ) {
db.aggregate( "run", ( sql )=>{ db.do( sql ) } );
db.do( `select run(sql) from commands where ${condition}` );
}
```

using npmjs.org/packages/sack.vfs


On Sat, Jan 13, 2018 at 5:11 PM, Brian Curley  wrote:

> Bash can be found on prettt much all of these platforms if you're only
> prototyping in the shell. Even Windows offers Cygwin, Git, and Msys2
> versions of the bash shell and I've had good success in running these w
> SQLite. (I cannot speak for iOS and bash, but I'm sure there's an
> option...)
>
> You can then adopt forward to others like Python, Node.js, whatever...
>
> Regards.
>
> Brian P Curley
>
>
>
> On Jan 13, 2018 4:18 PM, "Shane Dev"  wrote:
>
> Yes, I want to create a timestamp in the file name. My goal is to test the
> prototype on at least Windows, Linux, iOS and Android. Of course this kind
> of string building is easily done in bash, powershell, python, etc but no
> single scripting environment is available on every target platform. It
> could be done in C/C++ on every target platform but I was hoping to avoid
> the complexities of the compiler toolchain and system programming languages
> at this stage.
>
> On 13 January 2018 at 21:09, Simon Slavin  wrote:
>
> >
> >
> > On 13 Jan 2018, at 7:54pm, Shane Dev  wrote:
> >
> > > What do you mean by 'indirect phase'?
> >
> > Having to execute a command to find the command you want to execute.
> >
> > > The results of execution -
> > >
> > > sqlite> select '.once tc'||strftime('%s','now');
> > > .once tc1515872821
> > > sqlite>
> > >
> > > obviously the numeric part of the file name will change depending on
> the
> > > time of statement execution - or do I misunderstand your question?
> >
> > So the purpose of this is to find compose a filename which includes a
> > timestamp ?
> >
> > For prototype purposes you should be able to do this in whatever shell
> > you’re using to run the SQLite shell tool.  For real project uses you
> > should be doing it in whatever language your programming in, of course.
> >
> > You can’t use the SQLite shell tool for real project purposes on multiple
> > platforms.  It won’t run on many IoT devices, of course.
> >
> > Simon.
> > ___
> > 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
> ___
> 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] Can an SQL script be built from within sqlite?

[Brian Curley]

Bash can be found on prettt much all of these platforms if you're only
prototyping in the shell. Even Windows offers Cygwin, Git, and Msys2
versions of the bash shell and I've had good success in running these w
SQLite. (I cannot speak for iOS and bash, but I'm sure there's an option...)

You can then adopt forward to others like Python, Node.js, whatever...

Regards.

Brian P Curley



On Jan 13, 2018 4:18 PM, "Shane Dev"  wrote:

Yes, I want to create a timestamp in the file name. My goal is to test the
prototype on at least Windows, Linux, iOS and Android. Of course this kind
of string building is easily done in bash, powershell, python, etc but no
single scripting environment is available on every target platform. It
could be done in C/C++ on every target platform but I was hoping to avoid
the complexities of the compiler toolchain and system programming languages
at this stage.

On 13 January 2018 at 21:09, Simon Slavin  wrote:

>
>
> On 13 Jan 2018, at 7:54pm, Shane Dev  wrote:
>
> > What do you mean by 'indirect phase'?
>
> Having to execute a command to find the command you want to execute.
>
> > The results of execution -
> >
> > sqlite> select '.once tc'||strftime('%s','now');
> > .once tc1515872821
> > sqlite>
> >
> > obviously the numeric part of the file name will change depending on the
> > time of statement execution - or do I misunderstand your question?
>
> So the purpose of this is to find compose a filename which includes a
> timestamp ?
>
> For prototype purposes you should be able to do this in whatever shell
> you’re using to run the SQLite shell tool.  For real project uses you
> should be doing it in whatever language your programming in, of course.
>
> You can’t use the SQLite shell tool for real project purposes on multiple
> platforms.  It won’t run on many IoT devices, of course.
>
> Simon.
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Yes, I want to create a timestamp in the file name. My goal is to test the
prototype on at least Windows, Linux, iOS and Android. Of course this kind
of string building is easily done in bash, powershell, python, etc but no
single scripting environment is available on every target platform. It
could be done in C/C++ on every target platform but I was hoping to avoid
the complexities of the compiler toolchain and system programming languages
at this stage.

On 13 January 2018 at 21:09, Simon Slavin  wrote:

>
>
> On 13 Jan 2018, at 7:54pm, Shane Dev  wrote:
>
> > What do you mean by 'indirect phase'?
>
> Having to execute a command to find the command you want to execute.
>
> > The results of execution -
> >
> > sqlite> select '.once tc'||strftime('%s','now');
> > .once tc1515872821
> > sqlite>
> >
> > obviously the numeric part of the file name will change depending on the
> > time of statement execution - or do I misunderstand your question?
>
> So the purpose of this is to find compose a filename which includes a
> timestamp ?
>
> For prototype purposes you should be able to do this in whatever shell
> you’re using to run the SQLite shell tool.  For real project uses you
> should be doing it in whatever language your programming in, of course.
>
> You can’t use the SQLite shell tool for real project purposes on multiple
> platforms.  It won’t run on many IoT devices, of course.
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 13 Jan 2018, at 7:54pm, Shane Dev  wrote:

> What do you mean by 'indirect phase'?

Having to execute a command to find the command you want to execute.

> The results of execution -
> 
> sqlite> select '.once tc'||strftime('%s','now');
> .once tc1515872821
> sqlite>
> 
> obviously the numeric part of the file name will change depending on the
> time of statement execution - or do I misunderstand your question?

So the purpose of this is to find compose a filename which includes a timestamp 
?

For prototype purposes you should be able to do this in whatever shell you’re 
using to run the SQLite shell tool.  For real project uses you should be doing 
it in whatever language your programming in, of course.

You can’t use the SQLite shell tool for real project purposes on multiple 
platforms.  It won’t run on many IoT devices, of course.

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simion,

What do you mean by 'indirect phase'?

The results of execution -

sqlite> select '.once tc'||strftime('%s','now');
.once tc1515872821
sqlite>

obviously the numeric part of the file name will change depending on the
time of statement execution - or do I misunderstand your question?


On 13 January 2018 at 20:40, Simon Slavin  wrote:

>
>
> On 13 Jan 2018, at 7:33pm, Shane Dev  wrote:
>
> > I use mainly Linux (bash) and Windows (powershell) but my target
> platforms
> > also include Android, iOS, IoT (anything that can link against the sqlite
> > library). At this stage, I am trying to prototype as much as possible
> using
> > only the SQLite shell.
>
> Your theory is sound.  You should be able to build a SQL command using the
> results from another SQL command.  But there must be a way to express your
> SELECT in a way which doesn’t require the indirect phase, and your command
> looks especially simple.
>
> Can you post what the results of
>
> select '.once tc'||strftime('%s','now');
>
> are expected to look like ?
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 13 Jan 2018, at 7:33pm, Shane Dev  wrote:

> I use mainly Linux (bash) and Windows (powershell) but my target platforms
> also include Android, iOS, IoT (anything that can link against the sqlite
> library). At this stage, I am trying to prototype as much as possible using
> only the SQLite shell.

Your theory is sound.  You should be able to build a SQL command using the 
results from another SQL command.  But there must be a way to express your 
SELECT in a way which doesn’t require the indirect phase, and your command 
looks especially simple.

Can you post what the results of

select '.once tc'||strftime('%s','now');

are expected to look like ?

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

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simon,

I use mainly Linux (bash) and Windows (powershell) but my target platforms
also include Android, iOS, IoT (anything that can link against the sqlite
library). At this stage, I am trying to prototype as much as possible using
only the SQLite shell.



On 13 January 2018 at 19:57, Simon Slavin  wrote:

> On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:
>
> > Is there a way to execute the contents of certain rows (the second row in
> > this example) and replace it with its own result to create second table /
> > view which could interpreted by the sqlite shell?
>
> Not inside the SQLite shell.
>
> Looks like you need to learn programming.  Or at least how to script your
> OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell
> are you using ?
>
> Simon.
> ___
> 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] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:

> Is there a way to execute the contents of certain rows (the second row in
> this example) and replace it with its own result to create second table /
> view which could interpreted by the sqlite shell?

Not inside the SQLite shell.

Looks like you need to learn programming.  Or at least how to script your OS 
shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell are you 
using ?

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