Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-17 5:38 GMT+01:00 Cecil Westerhof :

> setsqliteVersion [sqlite3 -version]
>

​By the way, I think it is a good idea to amend:
https://sqlite.org/tclsqlite.html
to show this possibility.

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

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-16 22:20 GMT+01:00 Richard Hipp :

> On 11/16/17, Cecil Westerhof  wrote:
> > ​Is it possible to get the library version before connecting to a
> database?
>
> puts [sqlite -version]
>

​Combining yours and Eric's version, I made:
#!/usr/bin/env tclsh

package require sqlite3

setsqliteVersion [sqlite3 -version]
puts   ${sqliteVersion}
sqlite db ~/Databases/general.sqlite

In real life I will use it to checkthe SQLite verion if necessary.

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

Re: [sqlite] Starting with TCL

[Eric]

On Thu, 16 Nov 2017 21:28:10 +0100, Cecil Westerhof  
wrote:
> Is it possible to get the library version before connecting to a database?
> Now I do the following:
> #!/usr/bin/env tclsh
> 
> package require sqlite3
> 
> 
> sqlite3 db ~/Databases/general.sqlite
> 
> puts [db version]
> 
> 
> But I would prefer to check the version before connecting to a database. Is
> this possible?

Yes:

set ver [package require sqlite3]

puts $ver

Eric
-- 
ms fnd in a lbry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Richard Hipp]

On 11/16/17, Cecil Westerhof  wrote:
> ​Is it possible to get the library version before connecting to a database?

puts [sqlite -version]

-- 
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] Starting with TCL

[Cecil Westerhof]

​Is it possible to get the library version before connecting to a database?
Now I do the following:
#!/usr/bin/env tclsh

package require sqlite3


sqlite3 db ~/Databases/general.sqlite

puts [db version]


But I would prefer to check the version before connecting to a database. Is
this possible?

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

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-16 18:44 GMT+01:00 Peter Da Silva :

>
> On 11/16/17, 11:37 AM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
> > When I use:
> > db eval {SELECT * FROM teaInStock} {
> >puts $Tea, $Location
> > }
>
> puts takes a single string, so you can do {puts “$Tea\t$Location”.
> Arguments are separated by space, comma has no intrinsic meaning, and puts
> takes two arguments: the file handle to write on and the string to print.
> So it’s interpreting “$Tea,” as the name of a file handle.
>
> You probably want something like:
>
> db eval {SELECT * FROM teaInStock} {
> puts [format “%12s %12s %s” $Tea ${Last Used} $Location]
> }
>

​This is what I use:
puts [format "%-30s %-10s %2s" $Tea ${Last Used} $Location]

​Thanks.​

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

Re: [sqlite] Starting with TCL

[Richard Hipp]

On 11/16/17, Cecil Westerhof  wrote:
> puts $Tea, $Location

Everything in TCL is a function.  The syntax is "FUNCTIONNAME ARG1
ARG2 ARG3 ..." where the arguments are separated by white space.  The
"puts" function takes either one or two arguments.  The one-argument
form of "puts" outputs ARG1 to standard-output.  The two-argument form
of "puts" sends ARG2 to output stream specified by ARG1.

Your code above tries to invoke the two-argument form of "puts".
Equivalent javascript code would be:

 puts(Tea + ",", Location)

The error arises because there is no output channel named by the
result of Tea+",".  What you want is the one-argument form, equivalent
to this JS:

puts(Tea + ", " + Location)

To get that using TCL syntax, you can use quoting to make the two
separate arguments into one:

puts "$Tea, $Location"

The key point is that everything in TCL is of the format FUNCTION ARG1
ARG2   The processing steps are like this:

(1) Identify arguments separated by whitespace.  Note that all text
within "..." and within nested {...} is a single argument.

(2) Resolve quotes.  This means remove the outermost {...} from
arguments quoted using {...}.  Remove the "..." around double-quoted
argments, and also resolve any $variable name within the double
quotes.  The $variable name resolution does not happen with {...}

(3) Invoke the function with its arguments.

Note that *everything* is a function.  Even "control" statements.  In
Tcl when you see:

 if {$i<10} {
puts "yes"
 } else {
puts "no"
 }

That really is invoking the "if" function with 4 arguments.   Since
everything is a function, everything follows exactly the same quoting
rules.  This is an important feature of Tcl that programmers whose
prior experience has been exclusively using Algol-derived languages
such as C, Java, Javascript, and Python may have difficulty getting
their heads around.  But once you do "get it", it starts to seem very
natural.


-- 
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] Starting with TCL

[Peter Da Silva]

On 11/16/17, 11:37 AM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:
> When I use:
> db eval {SELECT * FROM teaInStock} {
>puts $Tea, $Location
> }

puts takes a single string, so you can do {puts “$Tea\t$Location”. Arguments 
are separated by space, comma has no intrinsic meaning, and puts takes two 
arguments: the file handle to write on and the string to print. So it’s 
interpreting “$Tea,” as the name of a file handle.

You probably want something like:

db eval {SELECT * FROM teaInStock} {
puts [format “%12s %12s %s” $Tea ${Last Used} $Location]
}
 

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

[sqlite] Starting with TCL

[Cecil Westerhof]

I just wanted to start using SQLite with TCL. How can I give a formatted
output?

When I use:
puts [db eval {SELECT * FROM teaInStock}]

I get:
Brandnetel {} 2017-11-16 1 Oolong {} 2017-10-29 2 Goudsbloem {} 2017-10-22
3 Jasmijn …


When I use:
db eval {SELECT * FROM teaInStock} {
puts $Tea
}

I get:
Brandnetel
Oolong
Goudsbloem
Jasmijn
…


When I use:
db eval {SELECT * FROM teaInStock} {
puts $Tea, $Location
}

I get:
can not find channel named "Brandnetel,"
while executing
"puts $Tea, $Location"
invoked from within
"db eval {SELECT * FROM teaInStock} {
puts $Tea, $Location
}"


I would like something like:
Brandnetel   2017-11-16   1
Oolong   2017-10-29   2
Goudsbloem   2017-10-22   3
Jasmijn  …


And probably another complication: one of the columns is called: "Last
Used".

-- 
Cecil Westerhof
___
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

[R Smith]

On 2017/11/15 11:56 PM, Simon Slavin wrote:
You are locking yourself into the Windows system. By all means use 
this solution as a prototype but if you ever find yourself saying "I’m 
now doing serious programming." do what you can to escape Windows, 
Office, and Visual-*. Otherwise you will continue to be a niche 
programmer with a very small niche, subject to panics every time your 
users upgrade their versions of Windows and Office.

Simon.


While I'm with you on the virtues of multi-platform environments and 
would like to urge the OP on in that regard too, I'm going to have to 
also nitpick on that statement's accuracy - while it may hold for /some/ 
Office-VB scripts, for most other Visual-* systems[1] that compile 
Windows .exe files, it's simply not true.


An exe you make today will be compatible and still run on the majority 
of Windows computers long after chickens grew teeth. Things designed in 
the 90's are still used today (I mean I wish people wouldn't use it, but 
they do). Never did I lose any sleep past all the hundreds of upgrades 
Windows had in that time, or woke up one day to find system X is no 
longer functioning (and this has actually happened lots on other 
platforms, PHP being the worst - anyone still remember mysql_xxx() 
functions [without the i]?).


And please understand me well, I'm not praising Windows for this, quite 
the opposite - One of my (and other-people's) main gripes with Windows 
is the stupid insistence on being so overly backward compatible that so 
much legacy API clunk up the core so that it can never match GUI 
intensive apps on other platforms, or use workarounds to allow old apps 
that were not made for the current security layers to still function 
(I'm looking at you "Windows Virtualization"). If your App was made for 
Apple/Android/Linux and it still tried non-conformant things in API 
security terms after security upgrades, it would just no longer work 
and, at a minimum, require a recompile (and rightly so! that's the 
entire point of security upgrades).


So no, I'm not a fan of how Windows does it, BUT, it does mean that it 
is extremely backward compatible, and saying that making something 
on/for a Windows platform makes you a "niche" programmer or gives you 
reason to panic when upgrades happen, is wholly unfair and just not true.


I don't have specific figures now, but I think in terms of desktop 
applications, Windows still outrank every other platform 50 to 1 in 
sheer volume of available apps and same holds for sheer number of users.


In fact, it's quite horribly banal - the very opposite of a niche.

Cheers,
Ryan
PS: I wish Windows dev teams were as efficient as their marketing teams 
- what a great World that would be!



[1] Including but not limited to: VB, VB.Net, Visual C, Visual C++, etc.


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

[sqlite] View is not flattened when inside an IN sub-query (incl complete sql)

[E . Pasma]

Hello, below are two equivalent delete statements. The difference is  
that the second version uses views, actually sub-queries, on the base  
tables. These are simple one to one views that could be flattened out,  
as in

   http://www.sqlite.org/optoverview.html#flattening
The second query plan has a full scan on t2 instead of direct access  
via the existing index sqlite_autoindex_t2_1. The view is apparently  
not flattened. Is it possible changing this? Thanks, E. Pasma


.version
SQLite 3.21.0 2017-10-02 02:52:54  
c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a

create table t1 (a, b, primary key(a,b));
create table t2 (b, c, primary key(b,c));
create table t3 (a, c, primary key(a,c));

.eqp on
delete from t3 where (a,c) in (
select  a,c
fromt1
joint2 using(b)
);
--EQP-- 0,0,0,SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?  
AND c=?)

--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 0
--EQP-- 0,0,0,SCAN TABLE t1
--EQP-- 0,1,1,SEARCH TABLE t2 USING COVERING INDEX  
sqlite_autoindex_t2_1 (b=?)


delete from t3 where (a,c) in (
select  a,c
from(select a,b from t1)
join(select b,c from t2) using(b)
);
--EQP-- 0,0,0,SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?  
AND c=?)

--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 0
--EQP-- 1,0,0,SCAN TABLE t1
--EQP-- 2,0,0,SCAN TABLE t2
--EQP-- 0,0,0,SCAN SUBQUERY 1
--EQP-- 0,1,1,SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX (b=?)

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

[sqlite] View is not flattened when inside an IN sub-query

[E.Pasma]

Hello, below are two equivalent delete statements. The difference is  
that the second version uses views, actually sub-queries, on the base  
tables. These are simple one to one views that could be flattened out,  
as in

http://www.sqlite.org/optoverview.html#flattening
The second query plan has a full scan on t2 instead of direct access  
via the existing index sqlite_autoindex_t2_1. The view is apparently  
not flattened. Is it possible changing this? Thanks, E. Pasma



.version
SQLite 3.21.0 2017-10-02 02:52:54  
c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a

create table t1 (a, b, primary key(a,b));
create table t2 (b, c, primary key(b,c));
create table t3 (a, c, primary key(a,c));

.eqp on
delete from t3 where (a,c) in (
select  a,c
fromt1
joint2 using(b)
);
--EQP-- 0,0,0,SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?  
AND c=?)

--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 0
--EQP-- 0,0,0,SCAN TABLE t1
--EQP-- 0,1,1,SEARCH TABLE t2 USING COVERING INDEX  
sqlite_autoindex_t2_1 (b=?)
"issue2.txt" 35L, 1393C
1,1   Top


___
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

[Balaji Ramanathan]

Thank you, Peter.  Perhaps it would be a good idea to update the SQLite
website and point links to tcl.tk rather than tcl-lang.org.

Balaji Ramanathan

-- Forwarded message --
From: Peter Da Silva 
To: SQLite mailing list 
Cc:
Bcc:
Date: Wed, 15 Nov 2017 13:11:56 +
Subject: Re: [sqlite] Best way to develop a GUI front-end
I contacted the Tcl core team and this is the response from Steve Landers:

> tcl-lang.org was a temporary measure a few years ago when the .tk DNS
went missing.  It wasn’t advertised but I guess it is now.

> I’ve fixed it

The official site is still at tcl.tk.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How not to let random be calculated again and again and

[Cecil Westerhof]

2017-11-06 11:11 GMT+01:00 Cecil Westerhof :

> 2017-11-06 10:39 GMT+01:00 Keith Medcalf :
>
>>
>> The easiest way is likely to make the query so that it cannot be
>> flattened by adding an ORDER BY (that does not reference the column
>> containing the non-deterministic function by name -- ie, use only OUTPUT
>> column position indicators (ordinals), not names or aliases).  This will
>> require the query planner to use a co-routine for the inner table so that
>> only the values will get passed up to the outer query.
>>
>
​It looks like that is not necessary.​

​I played again a little with it. It could be optimised a bit by sorting on
the date, so it will be almost sorted correctly in the inner sort. I should
just use 3 then.

But I just tried what happens if I order by name and that seems to work OK
also.

I have now:
SELECT *
,  Randomiser
,  Randomiser
FROM (
SELECT   *
,abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
FROM teaInStock
ORDER BY "Last Used"
)
ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
LIMIT5

And this gives:
"Goudsbloem""2017-10-22""3" "2""2""2"
"Groene Sencha" "2017-10-29""B6""0""0""0"
"Lemon" "2017-10-24""B2""6""6""6"
"Darjeeling""2017-10-30""5" "0""0""0"
"Ginger Lemon Chai" "2017-10-30""D4""1""1""1"
​

​So you can use the name. Something I prefer vastly above positional.
​


> Depending on the version of SQLite you are using, which you did not
>> mention.
>>
>> SELECT 
>>   FROM (  SELECT ...
>> FROM ...
>> ORDER BY 1)
>> ORDER BY ...
>> LIMIT ...
>> ;
>>
>
> ​That works likes a charm. I now use:
> SELECT "Last Used"
> ,  Randomiser
> ,  Randomiser
> ,  Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
> FROM (
> SELECT   *
> ,abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
>  FROM teaInStock
> ORDER BY 1
> )
> ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
> LIMIT5
>
> And this gives for example:
> ​
>
> ​"2017-10-17""2""2""2458045.5"
> "2017-10-20""0""0""2458046.5"
> "2017-10-19""3""3""2458048.5"
> "2017-10-18""6""6""2458050.5"
> "2017-10-19""5""5""2458050.5"​
>
> So problem solved. The double order is inefficient, but is only used
> during debugging, which now works. :-D
>
> Thanks.
>
>
> By the I am testing it in “DB Browser for SQLite” which uses 3.15.2.
>
> I am not sure in which programming language the real version will be
> implemented, but it will probably use a version near that one. For example
> my Java uses 3.20.0.
>

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

Re: [sqlite] SQLite Bytecode

[Richard Hipp]

On 11/15/17, aUser  wrote:
> Is it possible (and useful), to generate SQLite bytecode instead of a SQL
> statement?

It is neither possible nor useful.  The bytecode changes from one
release to the next - it is not stable. We treasure this design
freedom and will not yield it.  In the current bytecode design, if
arbitrary bytecode (that is to say, bytecode not generated by SQLite
itself) is supplied, then there are many paths that could result in
memory corruption, database corruption, assertion faults, segfaults,
memory leaks, and other bad things.  Adding the extra logic to defend
the bytecode against such problems would slow down SQLite for
everybody, which we are not willing to do.
-- 
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] SQLite Bytecode

[Richard Hipp]

On 11/15/17, aUser  wrote:
> Hello
>
> I developed an application-specific query language. Currently, I am
> converting a query to a rather difficult SQL statement.
>
> Is it possible (and useful), to generate SQLite bytecode instead of a SQL
> statement?
> If yes, could someone share some C code as an example, how it could be done?
> (F.i. Do I need to hold some locks?)
>
> Best Regards
>
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] SQLite Bytecode

[aUser]

Hello

I developed an application-specific query language. Currently, I am
converting a query to a rather difficult SQL statement.

Is it possible (and useful), to generate SQLite bytecode instead of a SQL
statement?
If yes, could someone share some C code as an example, how it could be done?
(F.i. Do I need to hold some locks?)

Best Regards




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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

[Chris Locke]

> For now, I am going to start
> with a windows forms application in vb.net or forms in OpenOffice.

I'd install SharpDevelop (
http://www.icsharpcode.net/opensource/sd/Default.aspx).  Download v4.4 if
you plan on using VB.Net, as the newer v5 doesn't support VB - only C#.
SharpDevelop is a 15 MB download, not a 4.6 GB download of Visual Studio.
I primarily write database apps using SQLite and VB.Net, so have a nice
database class 'wrapper', depending on your database skills... and what you
plan on building.
Happy to blog a beginners guide and whisk you along a SQLite coding journey.

As previously 'warned', it is a Windows only environment, so if you have
Linux friends, they won't be able to view your creations.  However,
building applications is quick and easy but immensely rewarding.  I love my
job.


Thanks,
Chris


On Wed, Nov 15, 2017 at 8:16 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> Thank you very much for all your suggestions.  For now, I am going to start
> with a windows forms application in vb.net or forms in OpenOffice.  Tcl/Tk
> is a steeper learning curve, and if someone can point me to some good
> resources that will walk a beginner through the development of a windows
> GUI on that platform, I would appreciate it.  Thank you again.
>
> 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