Re: [sqlite] Valid characters for indentifiers

[Clemens Ladisch]

Jens Alfke wrote:
> On Nov 11, 2017, at 6:31 AM, Simon Slavin  wrote:
>> There is no documentation for this.  Which means that even if you find
>> that, say, macrons are allowed in this version, they might not be allowed
>> in the next version.
>
> Um, really? That sounds bad for compatibility. Why isn’t it documented,
> or stable?

The documentation claims ANSI SQL 1992 compatiblity; all identifiers
conforming to the standard are supported.

Extensions (e.g., dollar signs in the middle of identifiers; underscore
at the start; Unicode characters that are not letters, syllables, or
ideographs) are undocumented but will probably never be removed to avoid
breaking backwards compatibility.


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

Re: [sqlite] Best way to develop a GUI front-end

[Bart Smissaert]

As you are familiar with VBA I can see two other options:

1. Use Christian Werner's ODBC driver from Access (or Excel):
http://www.ch-werner.de/sqliteodbc/

2. Use Olaf Schmidt's COM dll with Access (or Excel):
http://www.vbrichclient.com/#/en/About/

RBS


On Mon, Nov 13, 2017 at 9:54 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> Hi,
>
> I have been using SQLite without any programming language so far.  I
> maintain the data in SQLite and use SqliteStudio (www.sqlitestudio.pl) for
> inputting data into it and running queries to look at outputs.  However
> SqliteStudio does not have a programmable back-end that allows me to take
> the outputs of a query and do things with it (such as calculating running
> sums and averages, for instance).  So, if things become too expensive for
> standard SQL, I just do without.
>
> In the past, I have maintained my data in an Access database and
> developed a pretty extensive set of forms and reports to input and extract
> data.  I moved to SQLite because I wanted to move off a proprietary
> platform onto a free one.  And SQLite is faster and includes more SQL
> functionality like CTE's also.
>
> But I miss the nice front-end with forms to take inputs (especially
> parent and child forms to input data related to foreign keys) and the
> reports to format my outputs the way I wanted them.  I could also do
> running sums and averages, percentile scores and other things that require
> looking at a data dump of an entire columns of contents to do efficiently.
>
> I am familiar with programming in C and Java, but none of my
> programming has been for the windows platform (except for the programming
> inside MS Access, which was done in VBA).  I have never developed GUI front
> ends with forms, buttons, etc.
>
> Is there a third party free tool like MS Access that would allow me to
> connect to a SQLite db in the back-end and enable me to create a custom
> front-end to it with forms and reports?  All my searches for this kind of
> tool only lead me to tools like SqliteStudio, which is a GUI front end for
> SQLite, but not a programmable one like I want.  If anyone can point me
> towards a programmable GUI front-end development tool (preferrably
> open-source, but just free and well-maintained is sufficient), I would
> appreciate it greatly.
>
> Thank you very much.
>
> Balaji Ramanathan
> ___
> 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 Search using LIKE or something else

[Stephen Chrzanowski]

I'm not using a C compiler.  Delphi Berlin doesn't compile C, AFAIK.
I'll play around with that tonight as I've got a project I need to
start up tonight.

Third party libraries include those like LiteDAC, or DISQLite3, or
Synopse mORMot framework.


On Mon, Nov 13, 2017 at 1:34 PM, Jens Alfke  wrote:
>
>
>> On Nov 11, 2017, at 3:41 PM, Stephen Chrzanowski  wrote:
>>
>> Although there are pay for, and free to use, libraries that can be
>> linked into my applications, I've never been satisfied with the code
>> provided.
>
> Hm, I didn’t say anything about 3rd party libraries, so I’m not sure what 
> this is a response to.
>
>> I've tried taking the amalgamation and convert it into something that
>> I can directly embed into my applications (Which would be great), but,
>> I've yet to be successful.
>
> Just add sqlite3.c as a source file in your project/makefile/whatever.
>
> —Jens
> ___
> 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] Best way to develop a GUI front-end

[Tim Streater]

On 13 Nov 2017, at 21:54, Balaji Ramanathan  wrote:

> Is there a third party free tool like MS Access that would allow me to
> connect to a SQLite db in the back-end and enable me to create a custom
> front-end to it with forms and reports?  All my searches for this kind of
> tool only lead me to tools like SqliteStudio, which is a GUI front end for
> SQLite, but not a programmable one like I want.  If anyone can point me
> towards a programmable GUI front-end development tool (preferrably
> open-source, but just free and well-maintained is sufficient), I would
> appreciate it greatly.

There is Xojo (www.xojo.com) which has SQLite built in and has reports (which I 
have never used). It's cross-platform and you create your own GUI front end 
with windows, buttons etc. It's free to use for development, but if you want to 
compile and build a stand-alone application (.exe or .app) then you buy a 
licence. There is a Lite licence to build for one platform which is not a high 
cost.



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

Re: [sqlite] Best way to develop a GUI front-end

[Richard Hipp]

On 11/13/17, Peter Da Silva  wrote:
> Since sqlite originated as a Tcl extension it’s got excellent Tcl bindings,
> and Tcl has an excellent platform-independent GUI in Tk, so it seems to me
> that would be the best and simplest way to create a GUI front end for
> sqlite.

+1

-- 
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] Best way to develop a GUI front-end

[Peter Da Silva]

Since sqlite originated as a Tcl extension it’s got excellent Tcl bindings, and 
Tcl has an excellent platform-independent GUI in Tk, so it seems to me that 
would be the best and simplest way to create a GUI front end for sqlite.

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

Re: [sqlite] Valid characters for indentifiers

[Simon Slavin]

On 13 Nov 2017, at 7:33pm, Jens Alfke  wrote:

> On Nov 11, 2017, at 6:31 AM, Simon Slavin  wrote:

> 
>> There is no documentation for this.  Which means that even if you find that, 
>> say, macrons are allowed in this version, they might not be allowed in the 
>> next version.
> 
> Um, really? That sounds bad for compatibility. Why isn’t it documented, or 
> stable?

One for the Dev team to answer.  If it was documented, it would be stable, at 
least within what the documentation admits to.

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

[sqlite] Best way to develop a GUI front-end

[Balaji Ramanathan]

Hi,

I have been using SQLite without any programming language so far.  I
maintain the data in SQLite and use SqliteStudio (www.sqlitestudio.pl) for
inputting data into it and running queries to look at outputs.  However
SqliteStudio does not have a programmable back-end that allows me to take
the outputs of a query and do things with it (such as calculating running
sums and averages, for instance).  So, if things become too expensive for
standard SQL, I just do without.

In the past, I have maintained my data in an Access database and
developed a pretty extensive set of forms and reports to input and extract
data.  I moved to SQLite because I wanted to move off a proprietary
platform onto a free one.  And SQLite is faster and includes more SQL
functionality like CTE's also.

But I miss the nice front-end with forms to take inputs (especially
parent and child forms to input data related to foreign keys) and the
reports to format my outputs the way I wanted them.  I could also do
running sums and averages, percentile scores and other things that require
looking at a data dump of an entire columns of contents to do efficiently.

I am familiar with programming in C and Java, but none of my
programming has been for the windows platform (except for the programming
inside MS Access, which was done in VBA).  I have never developed GUI front
ends with forms, buttons, etc.

Is there a third party free tool like MS Access that would allow me to
connect to a SQLite db in the back-end and enable me to create a custom
front-end to it with forms and reports?  All my searches for this kind of
tool only lead me to tools like SqliteStudio, which is a GUI front end for
SQLite, but not a programmable one like I want.  If anyone can point me
towards a programmable GUI front-end development tool (preferrably
open-source, but just free and well-maintained is sufficient), I would
appreciate it greatly.

Thank you very much.

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

Re: [sqlite] Running sums and averages

[Balaji Ramanathan]

OK, thanks for the short and sweet answer!  I just wanted to make sure I
wasn't missing something.

Balaji Ramanathan


> -- Forwarded message --
> From: Simon Slavin 
> To: SQLite mailing list 
> Cc:
> Bcc:
> Date: Sun, 12 Nov 2017 16:37:24 +
> Subject: Re: [sqlite] Running sums and averages
>
>
> On 12 Nov 2017, at 4:05pm, Balaji Ramanathan 
> wrote:
>
> > Is there anything I can do to reduce the time taken?
>
> Do it in your favourite programming language rather than SQL.
> Congratulations on finding a way of doing it in SQL, but running sums and
> averages are trivial in a programming language and will be far faster and
> you can be as fussy as you like about whether nulls are counted and such
> things.
>
> Simon.
>
>
> -- Forwarded message --
> From: "Joseph R. Justice" 
>


> I am *FAR* from a SQLite (or SQL) guru, but it seems obvious to me that
> your calculations for sumF1, avgF1, OverallAvgF1, etc as written are
> running in quadratic time based on the number of rows in table T.  Worse
> yet, you're recalculating all these values entirely from scratch every time
> you're recalculating them, rather than use the previously calculated value.
>
> Is there any way you can calculate table T with just RowNumber, F1, F2,
> ..., F10, and then as you subsequently step through T row by row calculate
> all the sums and averages as you step, saving the calculations for each row
> as you calculate them in temporary variables for use in calculating the
> values for the next row?  This might be doable in pure SQL code, or you
> might have to do it in whatever language you're making calls to SQLite
> from.  That should be linear time, at worst 2 times the number of rows in T
> (once to construct T, once to step through it).
>
> Be well.
>
>
>
> Joseph
>
>
>
> -- Forwarded message --
> From: Dennis Clarke 
> > < Simon correctly advised >
> > Do it in your favourite programming language rather than SQL.
>
> Let me be even more clear :
>
> Memory is cheap and most servers have plenty.
>
> Processors are fast and most servers have multiple with many cores.
>
> Select the entire table of columns you need into memory.
> Write a little code.
>
> No it won't scale very well into millions of rows but I could easily run
> a test and I will bet many mnay many dollars that processing the sums in
> memory is orders of magnitude faster than SQL.
>
>
> Dennis
>
> ps: if your db is MySQL or Oracle db then the problem is trivial with
>  the C  API
>
>
>
> -- Forwarded message --
> From: Keith Medcalf 
> Subject: Re: [sqlite] Running sums and averages
>
> EXPLAIN QUERY PLAN
>
> is the first step.
>


> -- Forwarded message --
> From: Graham Holden 
>


> You shouldn't even need to read the entire table (or view) into
> memory: just read row-by-row, and for each field, keep a running
> total and the count of non-NULL values. From these you can calculate
> your total and both types of average.
>
> Graham
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Valid characters for indentifiers

[Jens Alfke]

> On Nov 11, 2017, at 6:31 AM, Simon Slavin  wrote:
> 
> There is no documentation for this.  Which means that even if you find that, 
> say, macrons are allowed in this version, they might not be allowed in the 
> next version.

Um, really? That sounds bad for compatibility. Why isn’t it documented, or 
stable?

(My situation: my library generates SQL queries that sometimes use identifiers 
created by the library’s client, i.e. some 3rd party app. It’s very useful to 
be able to use these identifiers as-is by prefixing some sort of namespace 
(like “fts::”) and wrapping the token in quotes. (Of course I have to check 
that the identifier doesn’t itself contain an ASCII double-quote.))

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

Re: [sqlite] Simple Search using LIKE or something else

[Jens Alfke]

> On Nov 11, 2017, at 3:41 PM, Stephen Chrzanowski  wrote:
> 
> Although there are pay for, and free to use, libraries that can be
> linked into my applications, I've never been satisfied with the code
> provided.

Hm, I didn’t say anything about 3rd party libraries, so I’m not sure what this 
is a response to.

> I've tried taking the amalgamation and convert it into something that
> I can directly embed into my applications (Which would be great), but,
> I've yet to be successful.

Just add sqlite3.c as a source file in your project/makefile/whatever.

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

Re: [sqlite] Running sums and averages

[David Raymond]

As other folks have mentioned, doing it in an external language is going to be 
easiest.

That being said, CTE's are almost a full language in themselves.

I'm just gonna go with 1 value here for the example, but how about something 
like...

with recursive foo (RowNumber, F1, sumF1, OverallAvgF1, avgF1, F1notNull)
as(
  select
  rowNumber,
  F1,
  ifnull(F1, 0.0),
  ifnull(F1, 0.0),
  ifnull(F1, 0.0),
  F1 is not null
  from mytable where RowNumber = 1
  
  union all
  
  select
  mytable.RowNumber,
  mytable.F1,
  foo.sumF1 + ifnull(mytable.F1, 0.0),
  (foo.sumF1 + ifnull(mytable.F1, 0.0)) / mytable.RowNumber,
  (foo.sumF1 + ifnull(mytable.F1, 0.0)) / (foo.F1notNull + (mytable.F1 is not 
null)),
  foo.F1notNull + (mytable.F1 is not null)
  
  from
  
  foo inner join mytable on mytable.RowNumber = foo.RowNumber + 1)

select RowNumber, F1, sumF1, OverallAvgF1, avgF1 from foo;



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Balaji Ramanathan
Sent: Sunday, November 12, 2017 11:06 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Running sums and averages

Hi,

I have a table (mytable) with several numerical fields.  Let us call
them F1, F2, etc., etc.  The table also has a row number field (RowNumber)
that I make sure has incremental values from 1 through the number of rows
in the table.

In addition to printing out the values of F1 through Fn, I also want to
print out, for each row of the table, the running sums and averages of
these fields.  There are two averages:  one that uses only the count of
non-null values of each field as the denominator and another that uses the
count of both null and non-null values of each field.  So, I wrote a query
as below to calculate and print those values:

select RowNumber,
F1,
(select sum(F1) from mytable where mytable.RowNumber <= T.RowNumber) as
sumF1,
(select avg(F1) from mytable where mytable.RowNumber <= T.RowNumber) as
avgF1, --average excluding null values
(select sum(F1) from mytable where mytable.RowNumber <=
T.RowNumber)/T.RowNumber as OverallAvgF1, --average including null values
F2,
(select sum(F2) from mytable where mytable.RowNumber <= T.RowNumber) as
sumF2,
(select avg(F2) from mytable where mytable.RowNumber <= T.RowNumber) as
avgF2,
(select sum(F2) from mytable where mytable.RowNumber <=
T.RowNumber)/T.RowNumber as OverallAvgF2,
--Other fields, their running sums and averages go here
from mytable as T order by RowNumber

I have about 3000 rows in my table and about 10 fields I am doing this
for.  When I select F1 through F10 alone, the query runs in negligible
time.  But when I run the above query with running sums and averages
included (total of 40 columns instead of 10 columns), the time increases to
well over 5 minutes.

When I run the query at the SQLite command line, the first 1000 or so
records are produced very fast, but as the row number increases, I can see
SQLite struggling to produce outputs.  By the time I get to the end of the
query, the records are being produced at about a rate of only 1 or 2 per
second.

Is there anything I can do to reduce the time taken?  Is this the most
efficient way to query for running sums and averages?  Any other ideas to
get what I need any quicker?

The added wrinkle in all this is that mytable is actually not a table,
but a view.  F1 through F10 are computed from various tables and brought
together in this view.  So, I don't think I can create indexes on any of
these fields.  I have thought about creating a temp table out of this view
so that I can index RowNumber (I don't know whether it will help) but it
goes against my minimalist instincts.

Thank you.

Balaji Ramanathan
___
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