Re: [sqlite] New word to replace "serverless"

[Brian Curley]

"Serverless" has worked flawlessly since inception. Why change now...?

The marketing buzzword usage will disappear...long before Dr Hipp convinces
the list that email is dead even. 

Regards.

Brian P Curley



On Wed, Jan 29, 2020, 5:39 PM Jim Dodgen  wrote:

> I vote for ignoring the marketing types and stick with "serverless"
>
> Jim "Jed" Dodgen
> j...@dodgen.us
>
>
> On Wed, Jan 29, 2020 at 10:20 AM Thomas Kurz 
> wrote:
>
> > I would not choose a new wording. "Serverless" is correct, and just
> > because others start using "serverless" in a wrong manner, I don't see
> any
> > need for a change.
> >
> > Just my 2 cts.
> >
> >
> > - Original Message -
> > From: Richard Hipp 
> > To: General Discussion of SQLite Database <
> > sqlite-users@mailinglists.sqlite.org>
> > Sent: Monday, January 27, 2020, 23:18:45
> > Subject: [sqlite] New word to replace "serverless"
> >
> > For many years I have described SQLite as being "serverless", as a way
> > to distinguish it from the more traditional client/server design of
> > RDBMSes.  "Serverless" seemed like the natural term to use, as it
> > seems to mean "without a server".
> >
> > But more recently, "serverless" has become a popular buzz-word that
> > means "managed by my hosting provider rather than by me."  Many
> > readers have internalized this new marketing-driven meaning for
> > "serverless" and are hence confused when they see my claim that
> > "SQLite is serverless".
> >
> > How can I fix this?  What alternative word can I use in place of
> > "serverless" to mean "without a server"?
> >
> > Note that "in-process" and "embedded" are not adequate substitutes for
> > "serverless".  An RDBMS might be in-process or embedded but still be
> > running a server in a separate thread. In fact, that is how most
> > embedded RDBMSes other than SQLite work, if I am not much mistaken.
> >
> > When I say "serverless" I mean that the application invokes a
> > function, that function performs some task on behalf of the
> > application, then the function returns, *and that is all*.  No threads
> > are left over, running in the background to do housekeeping.  The
> > function does send messages to some other thread or process.  The
> > function does not have an event loop.  The function does not have its
> > own stack. The function (with its subfunctions) does all the work
> > itself, using the callers stack, then returns control to the caller.
> >
> > So what do I call this, if I can no longer use the word "serverless"
> > without confusing people?
> >
> > "no-server"?
> > "sans-server"?
> > "stackless"?
> > "non-client/server"?
> >
> >
> > --
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing data to a table that has generated-columns

[Brian Curley]

Is there a missed parsing on the input file? It's likely to be a bare word
situation where an extra delimiter is encountered in the record. It's
probably only on one record in particular, although I don't know if the
error message reads that back.

The shell is sensitive to these, as it's expected the input file to be CSV
compliant.

Regards.

Brian P Curley



On Sun, Jan 26, 2020, 1:37 PM Scott Robison  wrote:

> On Sun, Jan 26, 2020 at 11:01 AM chiahui chen 
> wrote:
>
> > Hi,
> >
> > After creating a table (total 8 columns including 1 generated column) , I
> > tried to import data from a csv file (each record has values for 7
> columns
> > that match the non-generated column names and data types, no headers ).
> >
> > The system issued " error:  table has 7 columns but 8 values were
> supplied.
> > ' I wonder why.
> >
> > After experimenting different ways to import data to a table that has a
> > generated column, so far I only found that  .read command with a .sql
> file
> > that was output  as a result of  'mode insert'  is successful. Is there
> any
> > other ways to import data to an existing generated-column table?
> >
>
> I would be inclined to import the csv as a temp table, then write an INSERT
> INTO ... SELECT ... query to move the appropriate columns from the temp
> table into the new table.
> ___
> 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] List of innocuous functions?

[Brian Curley]

separate but somewhat related question, based on the response:

   Has any thought been given to updating the documentation to cover those
pragmas that have been upgraded to selectable entities?

I've only been able to find a handful of references along the way, such as
pragma_table_info, pragma_index_info, and the aforementioned
pragma_function_list, at least that I recall. Any sort of listing of what's
available like these?

I'd think that it would be helpful in the peanut gallery. Thanks!

Regards.

Brian P Curley



On Fri, Jan 24, 2020 at 4:12 PM Richard Hipp  wrote:

> SELECT DISTINCT name
>FROM pragma_function_list
>  WHERE (flags & 0x20)!=0
>  ORDER BY name;
>
> On 1/24/20, Peter Kolbus  wrote:
> > Is there any documentation showing, or an easy way to generate, the exact
> > list of SQLite-provided functions that are innocuous?
> >
> > I’d like to turn on the new SQLITE_TRUSTED_SCHEMA but support a variety
> of
> > applications and am hoping for something to guide analysis.
> >
> > Thanks
> > -Peter
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> 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] Question about a query

[Brian Curley]

well...

It's not quite that categorical "no", although Simon's more than correct.

There's extensions that allow for regexp(), such as you might find in
SQLite Studio that allow for some really handy cross-functionality when
paired with group_concat(), for example. (It's addictive to have it handy
during development, in fact.) I understand that it can be rolled into your
own local build, if you want to use it, but it complicates things in terms
of portability. If you craft your SQL around that...you need to bring that
build along with it, and any headaches that it might include.

Otherwise, you'd want to leverage your application's handling to emulate
the same behavior.

Regards.

Brian P Curley


On Tue, Oct 9, 2018 at 10:04 AM Simon Slavin  wrote:

> On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas <
> oldbrain...@gmail.com> wrote:
>
> > Using SQLite, can you use masks (or regex) (like '% str%') inside
> > instr / substr, to delimit the output of a select, instead of me
> > determining the beginning and end of the substring?
>
> No.  Sorry.  You have to use string core functions to isolate the piece
> you want:
>
> 
>
> 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] Why trigger (UPDATE) does not work?

[Brian Curley]

You don't list your trigger definition if there's anything that might need
troubleshooting, but I will say that I've recently stopped using
SQLiteStudio for its somewhat erratic behavior.

While it's got a great regex implementation (which doesn't port to running
in pure CLI-based scripts) and a no-nonsense GUI, it also tends to flake
out with memory issues on relatively small databases. I also cannot find if
it's actively supported; no recent versions or activity on the forum.

You might need to crosscheck your results within alternates like
SQLiteAdmin or dbVis.

Regards.

Brian P Curley


On Sun, Aug 26, 2018, 3:41 AM Csányi Pál  wrote:

> Hi,
>
> I have a small database with schema attached in this mail.
> I have two triggers to update fields after an update occures.
>
> When I run the SQL code in sqlite3 CLI, or in SqliteStudio's SQL
> Editor, it output is that I expect. It gives the numbers of months and
> days so far:
> SELECT total(RemainingDays) FROM MyLengthOfService;
> total(RemainingDays)
> 63
>
> SELECT total(RemainingDays) % 30 FROM MyLengthOfService;
> total(RemainingDays) % 30
> 3
>
> But even if I run the trigger in SqliteStudio's SQL Editor alone, it
> does not give the expected output:
> UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN (
> SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN
> RemainingSummedDays = ( SELECT total(RemainingDays) FROM
> MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT
> total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
> The output is empty.
>
> When the trigger is fired, those numbers are not updated in the
> corresponding fields of the corresponding table.
> To be more precise, the AllYearsMonthsDays trigger does not work.
> The YearsRemainingMonthsDays trigger works.
>
> What am I missing here?
>
> --
> Best, Pali
> Üdvözlettel, Csányi Pál tanár
> ___
> 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] Mailing list shutting down...

[Brian Curley]

Doesn't the Fossil site already have a Capcha interface built into it that
could be adopted to enforce additional authentication around subscriptions?
Or a 2-step, email confirmation-type option, maybe? If they're robots
causing the problem, then they wouldn't be able to mive beyond the initial
attempt.

I signed up so long ago that I forget what the process involves.

Regards.

Brian P Curley


On Wed, Jun 13, 2018, 11:46 AM Richard Hipp  wrote:

> On 6/13/18, Chris Brody  wrote:
> > On Wed, Jun 13, 2018 at 10:44 AM jungle Boogie 
> > wrote:
> >> [...]
> >> http://spamassassin.apache.org/
> >
> > Maybe just add SpamAssassin to the existing GNU MailMan setup?
> >
> > http://www.jamesh.id.au/articles/mailman-spamassassin/
>
> That solves a different problem from the one we are having.
> --
> 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] sqlite problem - field names missing in select

[Brian Curley]

now that is awesome...

Of course, I first attempted it on SQLiteStudio using its 3.15 version of
sqlite. Looks like it missed it by one major release level; documentation
says it arrived as of 3.16.

I've tried it on my CLI version, which is 3.19+.

Good stuff. Thanks, Simon.

Regards.

Brian P Curley



On Fri, Feb 23, 2018 at 1:44 PM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 23 Feb 2018, at 6:28pm, Brian Curley <bpcur...@gmail.com> wrote:
>
> > If there's pragmas like table_info available...can these be made
> available
> > for parsing, or used as virtual tables for selection, etc?
>
> In current versions of SQLite, you can use the results of PRAGMAs as if
> they are tables.  See the "PRAGMA functions" section of
>
> <https://sqlite.org/pragma.html>
>
> For example, information about the columns in an index can be read using
> the index_info pragma as follows:
>
> sqlite> CREATE TABLE members (name TEXT COLLATE NOCASE,
>   phone TEXT COLLATE NOCASE,
>   weekrank INTEGER,
>   yearrank INTEGER);
> sqlite> CREATE INDEX m_ry ON members (yearrank, weekrank);
> sqlite> .headers ON
> sqlite> .mode column
> sqlite> PRAGMA index_info(m_ry);
> seqno   cid name
> --  --  --
> 0   3   yearrank
> 1   2   weekrank
>
> The same content can be read using a SELECT command:
>
> sqlite> SELECT * FROM pragma_index_info('m_ry');
> seqno   cid name
> --  --  --
> 0   3   yearrank
> 1   2   weekrank
>
> And, as with any other SELECT, you can modify the way the results are
> returned in the SELECT command ...
>
> sqlite> SELECT * FROM pragma_index_info('m_ry') ORDER BY cid;;
> seqno   cid name
> --  --  --
> 1   2   weekrank
> 0   3   yearrank
>
> 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] sqlite problem - field names missing in select

[Brian Curley]

Not exactly sure of the OP's full issue.

This does arise on occasion though, especially for when you try to maintain
a self-sustaining database. Ignoring the API for a moment, there is some
rationale for keeping metadata available for use. My primary use is as a
CLI toolkit, if not as a desktop workbench to convert data.

If there's pragmas like table_info available...can these be made available
for parsing, or used as virtual tables for selection, etc? (These could
facilitate some of the metadata questions, probably without much overhead,
to my knowledge at least.)

I love that sqlite_master can be wrangled to an extent; I can generate sql
to get table counts en masse, for example, but not without passing the
generated sql between UIs. If there's a tabular option available, it would
eliminate external reliance on code, if not simulate dynamic sql in some
cases.

On the other hand, if I am missing something in front of my face...I'm sure
that someone here will make me aware.

Regards.

Brian P Curley



On Feb 23, 2018 1:06 PM, "R Smith"  wrote:

>
> On 2018/02/23 2:46 PM, M wrote:
>
>> sqlite has an integral problem, field names cannot be selected each one
>> inside a program, there is way to do it, but it is not straight and
>> complicates the software programs, and makes the program complicated and
>> not readble.
>>
>> when you try to do something with the selected fields/columns, a lot of
>> code can be reduced, if only there where field names - the ability to work
>> with them directly.
>>
>
> Let me get this straight: Are you saying the query "SELECT Field1, Field2,
> Field3 FROM myTable;" does not work for you, but "SELECT * FROM myTable;"
> does?
>
> or, are you saying that both queries work fine, but you have no way of
> telling what the names (Field1, Field2, etc.) are once you receive the data?
>
> Both of these are of course utterly possible in SQLite and required in
> principle by any SQL RDBMS system - so if possible, could you perhaps post
> some small bit of code that illustrates what happens for you and say what
> it is that you expect to happen/be possible but that isn't working (even
> perhaps code that works for MySQL/PostGres/etc.).  Anything to make more
> clear what you mean so we will be able to show how it works in sqlite.
>
> Kindly also mention your programming platform, OS and any other relevant
> information you can think of. The more you say, the easier for us to help.
>
> Cheers,
> Ryan
>
>
> ___
> 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] Feature request for the Shell Tool: .mode json

[Brian Curley]

;'  #\
 cd ${my_path}

   elif [ ! -e ${my_file} ]; then
  printf "\nError:  %s \n"  "No input file found..."
   fi

   #  Turn off debug/verbose mode...IF it was us that did it...
   if [ "${1}" == "-v" ] \
  && [ ! -z "$(set |grep xtrace )" ]; then
 set +x
   fi

}

​
Regards.

Brian P Curley



On Sun, Jan 21, 2018 at 11:21 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
>
> On 21 Jan 2018, at 3:05pm, Brian Curley <bpcur...@gmail.com> wrote:
>
> > pipe it
> > through jq instead.
>
> I did not know jq existed.  Thanks.  Just gave the documentation a quick
> glance.
>
> jq is not installed on my platform (macOS) whereas sqlite3 is.
>
> Does jq do conversion both ways ?
>
> Can jq deduce the column names (SQL) / keys (JSON) from the output of
> SELECT without extra work from the programmer/scripter ?
>
> 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] Feature request for the Shell Tool: .mode json

[Brian Curley]

Well, I did oversimplify to just say 'pipe it through', but it's really
more like a sed usage.

You wouldn't see much difference if you'd pipe your delimited output
through sed or awk either, unless you threw in some directives, or a
script. It would require some planning on the part of the user, but there's
a cookbook on the jq site that covers this.


https://github.com/stedolan/jq/wiki/Cookbook#convert-a-csv-file-with-headers-to-json


There's other takes on this same recipe out there, on StackExchange, etc.

As with any such localized solution, once you get it working, you can use
it seamlessly as a function or an aliased call.

Regards.

Brian P Curley




On Jan 21, 2018 10:15 AM, "Luuk" <luu...@gmail.com> wrote:

On 21-01-18 16:05, Brian Curley wrote:
> Is there even a need to embed it into sqlite itself? Since you're on the
> shell, and in keeping with the whole 'do one thing well' mandate: pipe it
> through jq instead.
>
> Beautiful creature that jq...
>
> Regards.
>
> Brian P Curley
>
>
luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db
1
2
3
luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db | jq
1
2
3

Can you give an example please?
___
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] Feature request for the Shell Tool: .mode json

[Brian Curley]

Is there even a need to embed it into sqlite itself? Since you're on the
shell, and in keeping with the whole 'do one thing well' mandate: pipe it
through jq instead.

Beautiful creature that jq...

Regards.

Brian P Curley



On Jan 21, 2018 9:54 AM, "J Decker"  wrote:

> On Sat, Jan 20, 2018 at 9:54 PM, Simon Slavin 
> wrote:
>
> > Feature request for the Shell Tool: ".mode json".
> >
> > Output should be as a JSON array of objects, with one object for each row
> > of the table.  Output should start with the "[" character and end with
> > "]".  Rows should be separated with ",\n".  Quotes in strings should be
> > escaped for JSON, with a leading backslash.  NULL should be supported as
> > the four lower-case characters "null", ignoring ".nullvalue".
> >
> Numbers should be unquoted.
>
> NaN, Infinity are not definable through JSON.
>
>
> > The above setting should also affect the ".import filename [table]"
> > command as described in section 8 of  .
> > Columns should be created as necessary.  Signed zeros should be imported
> as
> > zero.
> >
> > The above facilities should be implemented whether or not
> > DSQLITE_ENABLE_JSON1 was enabled when compiling the shell tool.  They are
> > for export and import, not for handling JSON within SQLite.
> >
> > Implementation questions:
> >
> > I am not sure what the program should do if asked to import a value which
> > is an array or object.  Perhaps, for compatibility with the JSON1
> > extension, those should be imported as a string.
> >
> > I am not sure whether the program should respect the settings for
> > ".separator" for JSON mode, either for output or .import.
> >
> > I am not sure how BLOBs should be handled, either for output or .import.
> >
> could be arrays of bytes.  A reviver could covert it to UInt8Array when
> used on javascript side.  can't just use character strings; many
> combinations of bytes are invalid unicode code points.
> [0,1,2,255]
>
>
> >
> > 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?

[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?

[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] checking if a table exists

[Brian Curley]

This is for a bash function, but you can adapt it for perl as you need to.

PRAGMA table_info() will poll the schema for you, and you can grep for your
table name:

function sq3_info () {

   #  Debug/verbose mode...
   if [ "${1}" == "-v" ]; then
  set -x
   fi

   not_sqlite="Error: file is encrypted or is not a database"  2>&1

   if [ -e ${2:-${1}} ] ;then
 #  Invalid first argument...
#  STFU
 if [ "${1}" != "-v" ] \
&& [ "$(echo ${1} |cut -b 1 )" == "-" ]; then
   printf "\nError:  %s \n" "Invalid arg1... "
   return
 #  File exists, but...it's either zero byte or not a SQLite
file...
#  GTFO
 elif [ -z "$(head -1 ${2:-${1}} |grep 'Lite format' )" ] \
|| [ "$(sqlite3 ${2:-${1}} .tab 2>/dev/null )" ==
"${not_sqlite}" ] ;then
   printf "\nError:  %s \n" "${not_sqlite}"
   return
 fi
 #  Hey, look...a real file!
#  sed is :
# a) inserting additional column...only on Line 1
# b) inserting table name values...only on Line 2+
 hdr=$(for i in $(sqlite3 ${2:-${1}} '.tables' |tr -d [:cntrl:] )
  do sqlite3 -header ${2:-${1}} "pragma table_info(${i});" \
 |sed '1 s/^/table_nm|/g;1!s/^/'${i}'|/g;'
  done
  )
 #  Print hdr content...
#  Line 1 only
 printf "%s\n" ${hdr} |awk 'NR == 1 { print };'
#  Line 2 onward...minus repeats column headers and sorted by
object and field order...
 printf "%s\n" ${hdr} |awk 'NR != 1 { print };' |sort -t'|' -k1,1
-k2,2n |grep -v "^table_nm"
   elif [ ! -e ${1} ]; then
  printf "\nError:  %s \n"  "No SQLite file found..."
   fi

   #  Turn off debug/verbose mode...IF it was us that did it...
   if [ "${1}" == "-v" ] \
  && [ ! -z "$(set |grep xtrace )" ]; then
 set +x
   fi

}



Regards.

Brian P Curley
  cell: 845 548-4377


On Fri, Sep 22, 2017 at 5:01 PM, Igor Korot  wrote:

> Hi,
>
> On Fri, Sep 22, 2017 at 4:42 PM, mikeegg1  wrote:
> > I could use the “if not exist” syntax, and do in some places, but this
> is a SELECT. I need to verify the table exists before I attempt to pull
> data from it to prevent throwing and error.
>
> Try following query:
>
> SELECT 1 FROM sqlite_master WHERE  type = 'table' and name = ?;";
>
> And then bind the actual variable to the query.
>
> If the execution return a row the table exists.
>
> Thank you.
>
> >
> > Mike
> >
> >> On Sep 22, 2017, at 15:24, Igor Korot  wrote:
> >>
> >> Hi,
> >>
> >> On Fri, Sep 22, 2017 at 4:04 PM, David Raymond <
> david.raym...@tomtom.com> wrote:
> >>> Not familiar with perl, but...
> >>>
> >>> -You should never need to do writable_schema = on for any normal
> operation. You can always read the schema even without that.
> >>>
> >>> -To my untrained eye it looks like you made a "name" variable, but
> never actually bound it to the statement. Usually that would involve some
> form of bind function call or providing it as an argument to the execute
> function.
> >>>
> >>> For example Python: cur.execute(,  of values to bind>)
> >>> cur.execute("select count(*) from sqlite_master where type = 'table'
> and name = ?;", (,))
> >>>
> >>>
> >>> -Original Message-
> >>> From: sqlite-users [mailto:sqlite-users-bounces@
> mailinglists.sqlite.org] On Behalf Of mikeegg1
> >>> Sent: Friday, September 22, 2017 3:36 PM
> >>> To: SQLite mailing list
> >>> Subject: [sqlite] checking if a table exists
> >>>
> >>> I’m using the statement:
> >>>
> >>> select count(*) from sqlite_master where type = 'table' and name =
> ‘$NAME’;
> >>
> >> If you write the query like this it will check for the table called
> "$NAME".
> >>
> >>>
> >>> This statement works fine in the sqlite3 shell. This statement does
> not work in my API.
> >>> Is there a PRAGMA I need to issue so I can check for table existence?
> >>>
> >>> TIA
> >>>
> >>> Mike
> >>>
> >>> PERL code to check for table existence:
> >>>
> >>> sub tableexists($$) {
> >>>my $dbh = shift;
> >>>my $name = shift;
> >>>my $tableexists = 0;
> >>>
> >>>$dbh->do("pragma writable_schema = 'on';");
> >>>
> >>>my $sql = "select count(*) from sqlite_master where type = 'table'
> and name = '$name';";
> >>>my $stmt = $dbh->prepare($sql);
> >>>$stmt->execute or die "$0: verifying table name failed:
> $DBI::errstr";
> >>>while(my @row = $stmt->fetchrow_array) {
> >>>$tableexists = $row[0];
> >>>}
> >>>$stmt->finish;
> >>>
> >>>$dbh->do("pragma writable_schema = 'off';");
> >>>
> >>>return $tableexists;
> >>> }
> >>
> >> I think SQLite support "CREATE TABLE IF NOT EXIST" syntax.
> >> So you don't have to check for the table existence and just execute
> >> such query.
> >>
> >> Thank you.
> >>
> >>>
> >>> 

Re: [sqlite] CTE question...

[Brian Curley]

that is perfect.

Thanks, Clemens!

Regards.

Brian P Curley

On Sat, Sep 16, 2017 at 5:14 AM, Clemens Ladisch <clem...@ladisch.de> wrote:

> Brian Curley wrote:
> > WITH   cte_name  --(my_row, code_key)
> > AS (
> >SELECT-- Base record
> > 1my_row
> >   ,(SELECT
> >max(code_key)
> > FROM
> >_misc_log
> > )log_id
>
> This does not need a subquery:
>
>   SELECT 1, max(code_key) FROM _misc_log
>
> >UNION ALL
> >SELECT-- Recursion records
> > my_row + 1
> >   ,(SELECT DISTINCT
> >code_key
> > FROM
> >_misc_log
> > WHERE
> >code_key
> >   !=   (SELECT
> >max(code_key)
> > FROM
> >_misc_log )
> > ORDER BY 1 desc
> > )
> >FROM
> >  cte_name
>
> This does not refer to cte_name.code_key, so every step will return the
> same value.  And "key != max" works only for the first recursion step.
>
> The CTE needs to retrieve the largest key that is smaller than the
> previous key:
>
>   WITH cte_name(my_row, code_key) AS (
> SELECT 1,
>max(code_key)
> FROM _misc_log
>
> UNION ALL
>
> SELECT my_row + 1,
>(SELECT max(code_key)
> FROM _misc_log
> WHERE code_key < cte_name.code_key)
> FROM cte_name
> WHERE EXISTS (SELECT *
>   FROM _misc_log
>   WHERE code_key < cte_name.code_key)
>   )
>
>
> Regards,
> Clemens
> ___
> 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] CTE question...

[Brian Curley]

Admittedly it's a bit of a hack and there may be alternate approaches to
some of it, but I am curious if there's an issue within the WITH behavior.
It could just my approach.

DDL for my base table:

CREATE TABLE _misc_log (

   CODE_KEY  NOT NULL,

   CODE_VAL  NOT NULL,

   ATTRIB,

   PRIMARY KEY (

  CODE_KEY,

  CODE_VAL

   )

);


This _misc_log is an ad hoc logging table, where I throw a time-stamp,
table name, and a record count, respectively. I am just storing strings in
the code_* fields, so it can really be anything. The purpose is produce a
quick listing of the unique values in the code_key field, alongside of a
proper row number. (The native result of rowid from the table is not
useful, since they are typically hundreds of rows apart.)

Sample values:

code_key
20170914-1200
20170914-1822
20170915-0855
20170915-1718


I can get the recursion to work properly to give me the row sequence I
need, but it spins a little wide on sub-query content. Rather than give me
a nice listing of the recursive row number and the code_key string, it
works only for the first two lines...and then spirals into what appears to
be a Cartesian.

I'd expected:

my_row code_key

1 20170915-1718
2 20170915-0855
3 20170915-1822
4 20170915-1200
5 ...


I got:

my_row code_key

1 20170915-1718
2 20170915-0855
3 20170915-0855
4 20170915-0855
5 20170915-0855


CTE used:

   -- WITH   cte -- Name your "logical" table.
   -- AS (SELECT 1 AS n  -- Initialize your counter
value, plus any other columns...
   -- UNION ALL  --   (UNION rules...)
   -- SELECT n + 1   -- ...then recursively loop
through the rest of the logical table...
   -- FROM   cte -- ...using subqueries to
provide for content columns
   -- WHERE  n < 50  -- ...up to the point of the
max of n, or any other conditionals.
   --)   --
   -- SELECT n   -- Then SELECT...
   -- FROM   cte -- ...from the logical table.
   -- [ LIMIT x ]-- [Alternatively, or
additionally, limit output as needed.]
   -- ;  --

--CREATE VIEW vw_my_row_logid AS
WITH   cte_name  --(my_row, code_key)
AS (
   SELECT-- Base record
1my_row
  ,(SELECT
   max(code_key)
FROM
   _misc_log
)log_id
   UNION ALL
   SELECT-- Recursion records
my_row + 1
  ,(SELECT DISTINCT
   code_key
FROM
   _misc_log
WHERE
   code_key
  !=   (SELECT
   max(code_key)
FROM
   _misc_log )
ORDER BY 1 desc
)
   FROM
 cte_name
--   WHERE my_row <= 5
   )
   -- Resultset here...
SELECT   *
FROM cte_name
LIMIT5
;
===


Any thoughts on this?

(The internal WHERE and the external LIMIT are redundant.)

​​
Regards.

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Brian Curley]

They're using Fossil as the repository. You'll want to confirm the steps
required, but the main access point is as follow (I believe):

   https://www.sqlite.org/src/login

The main concern is that the functionality that you seek might not scale to
the broader user base. You can always extend it for yourself, and provide a
proof of concept back to the main trunk that may choose to adopt it too.

Otherwise, I think that there were a number of alternative approaches
outlined earlier in the thread. Some of these, such as assigning a
dedicated local database table in addition to sqlite_master, might actually
suffice for your need without impairing users who rely on the program in
the wild.

Regards.

Brian P Curley

On Wed, Jun 7, 2017 at 12:17 PM, PICCORO McKAY Lenz <mckaygerh...@gmail.com>
wrote:

> hello brian, what standars? reading deeply the sqlite site i not see the
> @issue buton@
>
> only see that all contact way must be in the mail list... its a 21 century
> and ID urls its the standar way of integration...
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.com
>
> 2017-06-07 12:12 GMT-04:00 Brian Curley <bpcur...@gmail.com>:
>
> > Not exactly.
> >
> > You're free to extend it yourself and submit it for consideration
> though. I
> > think that you'll just need to adopt the same standards as are in use
> > within the usual enhancements channels.
> >
> > Regards.
> >
> > Brian P Curley
> >
> >
> >
> > On Wed, Jun 7, 2017 at 12:09 PM, PICCORO McKAY Lenz <
> > mckaygerh...@gmail.com>
> > wrote:
> >
> > > 2017-06-07 9:59 GMT-04:00 Richard Hipp <d...@sqlite.org>:
> > >
> > > > I would suggest, then, that you grab a copy of the SQLite source
> code,
> > > > put it on github, and start your own fork.  You can then add whatever
> > > > new SQL commands you want.
> > > >
> > > > At this point, your chances of getting us to do your work for you are
> > > > very close to zero.
> > > >
> > > that's not was the topic.. but in any case its a response of the style:
> > > "works for me, doit yourselft"
> > >
> > >
> > > >
> > > > --
> > > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Brian Curley]

Not exactly.

You're free to extend it yourself and submit it for consideration though. I
think that you'll just need to adopt the same standards as are in use
within the usual enhancements channels.

Regards.

Brian P Curley



On Wed, Jun 7, 2017 at 12:09 PM, PICCORO McKAY Lenz 
wrote:

> 2017-06-07 9:59 GMT-04:00 Richard Hipp :
>
> > I would suggest, then, that you grab a copy of the SQLite source code,
> > put it on github, and start your own fork.  You can then add whatever
> > new SQL commands you want.
> >
> > At this point, your chances of getting us to do your work for you are
> > very close to zero.
> >
> that's not was the topic.. but in any case its a response of the style:
> "works for me, doit yourselft"
>
>
> >
> > --
> > 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] Filter results based on contents of another single field table

[Brian Curley]

I recently had a case where I needed to stack the strings that I did want,
minus those that I didn't. The in() and like() options weren't all that
flexible given their assumption of known strings and I needed to
accommodate near-hits. I found that group_concat() and regexp() work pretty
well together, so that you can have offsetting populations:

First, arrange two views, one with the strings you're seeking, then another
with those you're skipping. The subquery here uses a reference table
(tbl_misc) and so it's optional, but allows for an external maintenance
point...in case this is a persistent situation. One caveat though: regexp()
is intentionally limited in the general release, and a little flaky when it
comes to collations. It does handle ^string vs string$, but only if they're
placed accordingly at the head or back of the line...

SELECT DISTINCT

-- Select only column_name values that match [SEEK|SKIP] list in
tbl_misc...

column_name

FROM

vw_OD_distinct_columnnames

WHERE

column_name

-- Works as hard-coded list...

-- REGEXP ('_BEG_|addr|init|loan|merch|name|score|ssn|_END_')

-- Initially failed to match as a flattened resultset...

-- Resolved by appending a dummy string...and matching suffix (_BEG_ ..
_END_)

REGEXP (

SELECT

"'_BEG_|" || group_concat(code_val,"|") || "|_END_'" regex_arr

FROM (

SELECT

code_val

FROM

tbl_misc

WHERE

code_key

== 'SEEK'  -- alternatively 'SKIP'

AND attrib

IS NULL

GROUP BY

1

)

)

AND ( -- Only select columns that are well-formed, ie "_" or alphabetical
start...

unicode(lower(column_name))

== 95

OR

unicode(lower(column_name))

between 97

and 122

)

ORDER BY

-- Eliminate case factors in sorting...

CASE WHEN unicode(lower(column_name)) < 95

THEN unicode(lower(column_name)) + 32

ELSE unicode(lower(column_name))

END

,1


​Then a subsequent view joins the two together, using EXCEPT:

SELECT DISTINCT

-- Streamlined list of column_names, based on _seek and _skip...

column_name

FROM ( -- Outer query required to offset quirk that prevented ORDER BY
below...

SELECT

*

FROM

vw_sub_strings_seek

EXCEPT

SELECT

*

FROM

vw_sub_strings_skip

)

ORDER BY

CASE WHEN unicode(lower(column_name)) < 95

THEN unicode(lower(column_name)) + 300

ELSE unicode(lower(column_name))
END​


Regards.

Brian P Curley



On Wed, Apr 19, 2017 at 4:47 PM, R Smith  wrote:

>
> On 2017/04/19 9:12 PM, Stephen Chrzanowski wrote:
>
>> I'm attempting to get a report given by TrustWave trimmed down to results
>> that can be more easily managed.  I've taken the results of a report,
>> cleaned it up with Excel, then used SQLite Expert to import that result
>> into a database.
>>
>> Here are the two table DDLs:
>>
>> CREATE TABLE [SkipRemed] (
>>[Skip] CHAR);
>>
>>
>> CREATE TABLE [TWScan] (
>>[ExtIP] CHAR,   [IntIP] CHAR,   [Service] CHAR,   [VulnName] CHAR,
>> [Desc] CHAR,
>>[Remediation] CHAR,   [Port] CHAR,   [Severity] CHAR,   [CVE] CHAR,
>> [Ticket] INTEGER);
>>
>> There's only 2049 results in TWScan, so I'm not concerned about speed, and
>> there is no direct relationship between the two tables.  That is the
>> entire
>> DDL for the entire database.
>>
>> What I want to do is use SkipRemed to "Filter Out" or "Filter For" results
>> in TWScan based on the Remediation table.  The difference between Out and
>> For would be just in the calling query.  Right now, I want to filter OUT
>> results in SkipRemed so I can more easily see what reports are commonly
>> themed and result in one action being required to correct.  (IE: Upgrade
>> PHP or Apache).
>>
>> The query I've been messing with is this:
>>
>> *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
>> Remediation not like (select distinct Skip from SkipRemed) order by
>> upper(Desc),upper(Service)*
>>
>> I don't get the results I want, unless I use the actual full text of the
>> Remediation text.  I've changed Skip to '%'||Skip||'%' in the subquery but
>> that doesn't get me the results I want either.
>>
>> Somethings up with my logic, not sure where.  Ideas?
>>
>
> SELECT DISTINCT ExtIP, IntIP, Service, Remediation
>   FROM PMEScan
>  WHERE NOT EXISTS(SELECT 1 FROM SkipRemed WHERE Remediation LIKE Skip)
>AND ...
>  ORDER BY ...
>
>
> Should do it.
> Cheers,
> Ryan
>
>
> ___
> 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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Brian Curley]

The sqlite_master table will always preserve any comments embedded between
the "CREATE" and ";" keywords for a given table definition. Is this not
sufficient?

You can parse the sql for a table's record to retrieve comments, in
whichever format you're using. I know that SQLite supports both single line
("-- ..") and multi-line ("/* .. */") forms of comments, so your parsing
might need to handle either/both according to your style book.

Regards.

Brian P Curley



On Wed, Mar 15, 2017 at 7:40 AM, Chris Locke 
wrote:

> Just add a 'comments' table.  Seems a lot of extra work and 'extra tools'
> needed to read the comments, which could potentially be missed.
> Add a 'comments' table with a 'comment' field which you can even add dates,
> usernames, etc, to.
>
> Thanks,
> Chris
>
> On Wed, Mar 15, 2017 at 11:12 AM, Clemens Ladisch 
> wrote:
>
> > PICCORO McKAY Lenz wrote:
> > > an important feature in a DB its the column field that gives to
> > developers
> > > metadata info INDEPENDENT of the tecnologies used, due by this way
> with a
> > > simple text editor in generated script developer can read and use
> minimal
> > > info for understanding structure ...
> >
> > There is no widely accepted standard for comments in SQL, except /*
> actual
> > comments */, and neither is there one for metadata, except as actual data
> > in your own metadata table(s).  Adding some non-standard mechanism would
> > not allow anything that isn't already possible.
> >
> >
> > Regards,
> > Clemens
> > ___
> > 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] A CTE to count the records (rows) for each table

[Brian Curley]

Statistics, even if generated at run-time, might be useful.

One option, at least per the CLI, might be to output a variant of DRH's
last SQL to be read back in. I've no idea as to the portability of this
onto embedded systems, but it works "okay" on the CLI and on desktop
apps...my test file was fairly large and I'm including views, which add to
the overhead.

SELECT

CASE WHEN rowid == (select max(rowid) from sqlite_master where type in
('table','view') and name not like 'sqlite_%')

THEN printf('SELECT "%w", count(*) FROM "%w" ',name,name)

ELSE printf('SELECT "%w", count(*) FROM "%w" UNION',name,name)

END counted

FROM

sqlite_master

WHERE

type

IN ('table','view')

AND name

NOT LIKE 'sqlite_%'

;


Dynamic SQL would be very helpful here, but I haven't seen it on SQLite.
Maybe if an attached db could be leveraged...?

Regards.

Brian P Curley



On Mon, Mar 13, 2017 at 2:47 PM, Richard Hipp  wrote:

> On 3/13/17, Marco Silva  wrote:
> > Hi,
> >
> >  Does anyone knows a Common Table Expression (CTE) to be used with the
> >  sqlite_master table so we can count for each table how many rows it
> >  has.
>
> That is not possible.  Each table (potentially) has a different
> structure, and so table names cannot be variables in a query - they
> must be specified when the SQL is parsed.
>
> But you could do this with an extension such as
> https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
> an SQL function that submits new SQL text to the SQLite parser.  For
> example:
>
>   SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
>   FROM sqlite_master
>WHERE type='table' AND coalesce(rootpage,0)>0;
>
>
> --
> 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] SQLite Recursive Common Table Expression suggestion

[Brian Curley]

Maybe so. Even simpler recursion doesn't get executed, such as a quick poll
of the sqlite_master table to trigger a system-wide count(*) of all tables
isn't allowed, so it seems that it's held at the gate. Even if I mock up a
transaction or a thorough UNION set through a view, I need to output it
just to read in as an update.

Regards.

Brian P Curley



On Mar 7, 2017 7:30 AM, "Clemens Ladisch" <clem...@ladisch.de> wrote:

> Brian Curley wrote:
> > What I wonder though is if CTEs could actually serve as a stand-in for
> the
> > lack of Dynamic SQL
>
> Recursive CTEs make SQL Turing complete.
>
> But they cannot do everything.  For example, when you want to do a pivot
> operation, the number of columns is determined by the data, and you
> cannot construct and execute that query only from within SQLite.
>
>
> Regards,
> Clemens
> ___
> 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] SQLite Recursive Common Table Expression suggestion

[Brian Curley]

Reached back into the tape storage in my head for this one, but to
paraphrase a movie older than me: the future is in pipes.

   http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

Note that DRH likes to mention that SQLite is meant to replace fopen() more
than a full-bore RBDMS, but I think that the CLI is often overlooked. You
can use it ad hoc, or in tandem with existing DBs, just like you can stream
data to the shell for other commercial products, like sqlplus.

There is quite a bit out there.

Regards.

Brian P Curley



On Mar 7, 2017 7:04 AM, "Michael Tiernan" <michael.tier...@gmail.com> wrote:

> On Mar 7, 2017 6:56 AM, "Brian Curley" <bpcur...@gmail.com> wrote:
> > I have successfully coupled shell scripts and the CLI
>
> I'd love to see examples of this sort of use case and I suspect that
> there's others who would benefit from seeing how others approach solving
> some of the common problems.
>
> Does anyone know where knowledge like this is shared? (Specifically aimed
> towards users of SQLite?)
> ___
> 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] SQLite Recursive Common Table Expression suggestion

[Brian Curley]

...besides, one might argue that anyone who can programmatically predict
the best route for Minesweeper should actually focus on a tool that
predicted the lottery (or even elections... ;)

What I wonder though is if CTEs could actually serve as a stand-in for the
lack of Dynamic SQL, sort of how triggers can sometimes serve in place of a
procedural language. I have successfully coupled shell scripts and the CLI
but in cases where one is limited to desktop options, this would really be
pretty awesome.

Regards.

Brian P Curley



On Mar 7, 2017 3:46 AM, "Clemens Ladisch"  wrote:

> Simon Slavin wrote:
> > I’ve seen many amusing examples of using Common Table Expressions to
> > solve Sudoko puzzles.  Has anyone tried using one to suggest the best
> > next move for Minesweeper ?
>
> https://en.wikipedia.org/wiki/Minesweeper_(video_game)#
> Computational_complexity
>
> > have SQLite suggest a good next move.
>
> Define "good".  ;-)
>
>
> Regards,
> Clemens
> ___
> 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

[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] importing csv

[Brian Curley]

Windows requires that you escape the \ in the path, effectively doubling
them up.

Regards.

Brian P Curley


On Aug 24, 2016 8:24 PM, "Simon Slavin"  wrote:

> I hope someone else can help.
>
> Does that table already exist in the database file ?
>
> Is the first line of the csv file a line of data or a line of column names
> ?
>
> 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] [Query] Sqlite

[Brian Curley]

What's your question on SQLite?

(The sqlite-users@sqlite.org address is fairly straightforward.  It's sort
of like a forum style of help, where you ask your question via e-mail and
90-95% of the answers come from roughly 10 seemingly never-sleeping experts
from around the English-speaking world. The other 5-10% of the answers come
from dozens of other experts and heavy users, very few of which are
completely off-track.  Beyond that, it's all archived online for future
reference, so you might actually find your question was already posed and
answered in the past.)

On Thu, Nov 1, 2012 at 7:28 AM, Rajkumar  wrote:

> Sir/Madam,
>
> I am preparing to develop two applications using following technologies:
>
> 1. Adobe AIR + Android +Sqlite
> 2. QT + Embedded Linux + Sqlite
>
> Before I start my development, I would like to clarify few things on sqlite
> as I am new to this.
>
> Kindly acquaint me how getting help works with "sqlite-users@sqlite.org".
>
> --
> Regards,
> Raj
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards.

Brian P Curley
  home: 845 778-5937
  cell: 845 548-4377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Export Blob data from sqlite database to html

[Brian Curley]

I'd agree with the SQLite Expert option (Windows)...it ships, or at least
used to ship with sample data that presents a scenario like you're
describing (ie, jpeg and text as BLOB), plus it allows visual assists, such
as BLOB masking over a certain size of data in a column.  Otherwise, you'd
need to roll your own using the command-line tool...and something like awk
or perl.

On Thu, Nov 17, 2011 at 5:47 AM, Jean-Christophe Deschamps  wrote:

> At 10:02 16/11/2011, you wrote:
>
>  Basically my problem is exporting the database so the time stamps appear
>> in
>> hex the data blobs appear in either text (ascii) or as a jpeg image
>> depending on the data in type field.
>>
>> I have tried numerous sqlite browsers without success.
>>
>
> Looks like a job for SQLite Expert.
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
Regards.

Brian P Curley
  home: 845 778-5937
  cell: 845 548-4377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using dot commands with the command line binary

[Brian Curley]

Use of a heredoc to simulate a session...or staging it all into a file for
use via .read are good too.
On Aug 16, 2011 6:55 AM, "Ryan Henrie"  wrote:
> I finally figured out how to load multiple "dot commands" or settings
> from the command line tool. (Some users only have the default binary to
> rely on, ya know.) Since I have never found this information on the web
> before, I thought I would post it here to share the information.
>
> To load options before doing the query, they have to be separated from
> the query by a hard return, in the quoted string itself.
>
> So:
>
> > sqlite3 -header -column tmp.db ".width 5 30; select * from data;"
>
> doesn't really change the column widths.
>
> But, doing this does:
>
> > echo ".width 5 30\n select * from data;" | sqlite3 -header -column
tmp.db
>
> To load multiple options, you have to put them on different lines, with
> the query on the last line:
>
> > echo ".width 5 30\n.timeout 15000\n select * from data;" | sqlite3
> -header -column tmp.db
>
> Some systems I have tried this on /required /only a single hard return
> after the last option, with a semicolon between the multiple settings.
> Others required the hard returns without any semicolons. Experiment on
> your own platform/binary version.
>
> I don't know which versions/flavors this works on, but it works for me
> finally.
>
> Note: Your version of echo has to support turning '\n' into a hard
> return. Not all do. If not, you can do something like a perl -e to do
> it as well.
>
> All the examples on websites show applying the dot commands from a
> sqlite> prompt. Doing the hard returns within the string makes it look
> like a user typing the commands in, with the hard returns.
>
> If anyone could fix the parser in the sqlite3 source code, I'm sure many
> novices would greatly appreciate it. The above trick has eluded me for
> several months of working with sqlite3 from the command line, and is
> still cumbersome, although usable now.
>
> One other trick is to put the options in a .sqliterc file in your home
> directory, one dot command per line. This works, but is not really
> feasible in an environment where it will run on multiple hosts and you
> don't control other accounts that will run it. Also, you can't pick and
> choose which ones are loaded. They are global settings at that point.
>
> I hope this helps someone out there looking for this, as well as
> possibly getting the parser fixed to make it easier to use in the future.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shell doesn't do

[Brian Curley]

@Simon: I'd disagree, unless you mean to make it less specific, since it's
really an exercise for the reader to infer mark-up...not just HTML over
XHTML.  But that's just me...
On Aug 10, 2011 1:06 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:
>
> On 10 Aug 2011, at 5:58pm, Kit wrote:
>
>> 2011/8/10 Brian Curley <bpcur...@gmail.com>:
>>> Depending on your preferred shell...the sqlite CLI is just crazy
flexible.
>>> Just pipe your output through sed for upper/lower preferences.
>>
>> It is not entirely primitive. It needs only tags in lowercase.
>>
>> http://www.sqlite.org/sqlite.html;>
>> The last output mode is "html". In this mode, sqlite3 writes the
>> results of the query as an XHTML table.
>> 
>>
>> XHTML tags are in lowercase...
>
> Actually, Kit's right. If that format is meant to be XHTML (and not HTML,
as the in-app help says) then those tags MUST be lower case. The web page
needs changing.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shell doesn't do

[Brian Curley]

Then you'd need your sed regex to handle that mask accordingly. The CLI is
understandably your best friend in this case.

A rogue's gallery just awaiting you to tell it what to do...I prefer to
model my app using views, and feed it .import files. You might even replace
time and date retrieval logic by piping sqlite output into use.  It's much
better than some on this forum give it credit.

But as mentioned elsewhere: you've got the recompile option too...
On Aug 10, 2011 12:58 PM, "Kit" <kit.sa...@gmail.com> wrote:
> 2011/8/10 Brian Curley <bpcur...@gmail.com>:
>> Depending on your preferred shell...the sqlite CLI is just crazy
flexible.
>> Just pipe your output through sed for upper/lower preferences.
>
> It is not entirely primitive. It needs only tags in lowercase.
>
> http://www.sqlite.org/sqlite.html;>
> The last output mode is "html". In this mode, sqlite3 writes the
> results of the query as an XHTML table.
> 
>
> XHTML tags are in lowercase...
> --
> Kit
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shell doesn't do

[Brian Curley]

Depending on your preferred shell...the sqlite CLI is just crazy flexible.
Just pipe your output through sed for upper/lower preferences.
 On Aug 10, 2011 12:18 PM, "Kit"  wrote:
> 2011/8/10 Simon Slavin :
>> I've never tried using this before for some reason but in a recent OS X
version of the command-line shell I tried using
>> .mode html
>> today.  The content is fine, but it doesn't do  or .
>> Intentional ?  Bug ?  Oversight ?  Trying hard to believe I'm not the
first person who has tried this.
>> If someone feels like fixing this, then it should also include 
and  as well, but most browsers will infer these.
>> If someone claims 'no fix because we have users who rely on this' is
there any chance of another mode, perhaps 'htmlfull' which does this ?
>> Simon.
>
> Much more I dislike that the tags are uppercase on output. I prefer
> lowercase, so this functionality can not be used practically.
> --
> Kit
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger mask entities...

[Brian Curley]

Looking for some guidance or enlightenment on the available fields within a
given trigger.

In reading through the code as a non-C kind of guy, it looks like there's an
array of columnar values lumped together as old.* and new.* would need them,
but nothing that contains the individual field or column names.  Am I
missing something or is this deemed too specific/non-generic for the trigger
purposes?

My business case would be in the realm of logging, for example.  If I'm
using an audit-centric trigger, I'd probably want to store field and value,
as opposed to the entire row as only one or two fields might change and to
conserve space.  I could probably rig a rube goldberg series of triggers to
store and compare old.* and new.* values to reinsert elsewhere but if the
column names are already present it'd be a nice little shortcut.  My primary
use would be via the shell...

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