[sqlite] sqlite native NuGet package maintainers?

[da...@andl.org]

I have raised an issue for cannot install Nuget native DLL into .NET 4.5
project.

I requested information about where this project is maintained and by whom.

Any response appreciated.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Leeber
Florian
Sent: Wednesday, 24 February 2016 2:40 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] sqlite native NuGet package maintainers?

Dear All,

wrote a mail to Taylor Southwick, but just wanted to confirm he is the right
guy for issues with the NuGet package of sqlite? Specifically the problem is
the inclusion of the Debug build of an old MSVCRT which requires Visual
Studio to be installed, in case you want to run your project in Debug
configurations...

Thx!


Kind regards,

Ing. Florian Leeber, Bakk.
ANDRITZ METALS
MERe
Project Engineer

ANDRITZ AG
Eibesbrunnergasse 20
1121 Vienna, Austria
Phone: +43.5.0805-55581
Mobile: +43.664.9614355
Fax: +43.5.0805-51018
florian.leeber at andritz.com
www.andritz.com




#

This message and any attachments are solely for the use of the intended
recipients. They may contain privileged and/or confidential information or
other information protected from disclosure. If you are not an intended
recipient, you are hereby notified that you received this email in error and
that any review, dissemination, distribution or copying of this email and
any attachment is strictly prohibited. If you have received this email in
error, please contact the sender and delete the message and any attachment
from your system.

Thank You.

Andritz AG
Rechtsform/ Legal form: Aktiengesellschaft/ Corporation Firmensitz/
Registered seat: Graz Firmenbuchgericht/ Court of registry: Landes- als
Handelsgericht Graz Firmenbuchnummer/ Company registration: FN 50935 f
DVR: 005 0008
UID: ATU28609707

#


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

[sqlite] NuGet problem

[da...@andl.org]

Can someone tell me where the NuGet package gets built, so I can have a look
and see what needs to be done?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Joe
Mistachkin
Sent: Thursday, 18 February 2016 12:27 PM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] NuGet problem


David M Bennett wrote:
> 
> "Could not install package 'sqlite.redist 3.8.4.2'. You are trying to 
> install this package into a project that targets 
> '.NETFramework,Version=v4.5', but the package does not contain any
assembly
> references or content files that are compatible with that framework. 
> For more information, contact the package author."
> 

That package still needs to be updated to deal with the NuGet 3.0 changes
for packages containing native binaries.  In the meantime, is it possible
for you to use the System.Data.SQLite.Core package to obtain the
"SQLite.Interop.dll"
files and P/Invoke against those?

--
Joe Mistachkin

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

[sqlite] NuGet problem

[da...@andl.org]

Not much help for an automated build. That's what I use now, and I *hate*
having to check third party DLLs into the source code tree.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Kees Nuyt
Sent: Friday, 19 February 2016 12:40 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] NuGet problem

On Thu, 18 Feb 2016 13:37:59 +1100,  wrote:

> Thanks for the answer. At least now I know why.
>
> I have already written all the P/Invoke code. It's written, tested, 
> working fine, using the sqlite3.dll size 658,797. Having the header 
> and EXE is convenient too, in a development context.
>
> I was able to install the package you suggest, but there is nothing in 
> it that looks like the DLL I've been using. If it's a drop-in 
> replacement then I'd happily go with it, but if it means rewriting low 
> level code, I'd rather not. I checked the documentation, and they really
look rather different.

If all you need is the sqlite3 C API, the Precompiled Binaries for Windows
on the download page provide just that:
http://www.sqlite.org/download.html


--
Regards,

Kees Nuyt

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

[sqlite] NuGet problem

[da...@andl.org]

Thanks for the answer. At least now I know why.

I have already written all the P/Invoke code. It's written, tested, working
fine, using the sqlite3.dll size 658,797. Having the header and EXE is
convenient too, in a development context.

I was able to install the package you suggest, but there is nothing in it
that looks like the DLL I've been using. If it's a drop-in replacement then
I'd happily go with it, but if it means rewriting low level code, I'd rather
not. I checked the documentation, and they really look rather different. 

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Joe
Mistachkin
Sent: Thursday, 18 February 2016 12:27 PM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] NuGet problem


David M Bennett wrote:
> 
> "Could not install package 'sqlite.redist 3.8.4.2'. You are trying to 
> install this package into a project that targets 
> '.NETFramework,Version=v4.5', but the package does not contain any
assembly
> references or content files that are compatible with that framework. 
> For more information, contact the package author."
> 

That package still needs to be updated to deal with the NuGet 3.0 changes
for packages containing native binaries.  In the meantime, is it possible
for you to use the System.Data.SQLite.Core package to obtain the
"SQLite.Interop.dll"
files and P/Invoke against those?

--
Joe Mistachkin

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

[sqlite] NuGet problem

[da...@andl.org]

Trying to use NuGet to install Sqlite. The error is:

"Could not install package 'sqlite.redist 3.8.4.2'. You are trying to
install this package into a project that targets
'.NETFramework,Version=v4.5', but the package does not contain any assembly
references or content files that are compatible with that framework. For
more information, contact the package author."

The project is C#, but I'm using P/Invoke to call the C-API directly. All I
need is the bare minimum so I can automate the build. I really don't need
ADO, LINQ or any of that other stuff. Just the DLL, and maybe the C header
and EXE.

Is this something I'm doing wrong, or a problem with the NuGet package, or
what?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Correlated subquery throwing an error

[da...@andl.org]

OK, I get it now.

You need a whole query to calculate a value for val, and then another query
to find the lowest distance match on val. You can't do that with simple
correlated queries or subqueries, except by repeating a lot of the work.

The only reasonable prospect I could see for efficiency is to use a CTE to
calculate the intermediate table containing VAL and DIST<25, a main query
for the final result with a value subquery to compute min(VAL). Or you could
use two queries and an explicit temporary table.

The reason for my interest is how to write this query in Andl, and since on
Sqlite Andl generates SQL I'd like to check that the SQL actually works. My
solution for this problem requires storing the intermediate results, which
is trivial in Andl and much harder to do in SQL.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Gary
Briggs
Sent: Monday, 15 February 2016 11:47 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Correlated subquery throwing an error

On Mon, Feb 15, 2016 at 11:11:26AM +1100, david at andl.org wrote:
> Why not
> 
> SELECT foo.*,
>   (SELECT id, DIST(foo.x, foo.y, nearest.x, nearest.y) AS d
>FROM foo AS nearest
>WHERE d < 25
>ORDER BY val, d
>LIMIT 1) AS id2
> FROM foo

That specific SQL gives the error:
"only a single result allowed for a SELECT that is part of an expression"
But when I removed the "id" column from the inner select, it worked. It's
when I replace "d" with the full DISTANCE() in just the ORDER BY clause that
it stops working.

Works:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE d < 25
ORDER BY val, d
LIMIT 1) AS id2
 FROM foo
LIMIT 5

Works:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0)  < 25
ORDER BY val, d
LIMIT 1) AS id2
 FROM foo
LIMIT 5

Doesn't work ["no such column: foo.x"]:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE d  < 25
ORDER BY val, DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0)
LIMIT 1) AS id2
 FROM foo
LIMIT 5


Thanks,
Gary

> Regards
> David M Bennett FACS
> 
> Andl - A New Database Language - andl.org
> 
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
> Gary Briggs
> Sent: Monday, 15 February 2016 10:43 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Correlated subquery throwing an error
> 
> I posted a question on stackoverflow, here:
> http://stackoverflow.com/questions/35382897/implementing-a-sql-query-w
> ithout
> -window-functions
> 
> In short, I have a table that I'm trying to query:
> CREATE TABLE foo (
>id INTEGER PRIMARY KEY,
>x REAL NOT NULL,
>y REAL NOT NULL,
>val REAL NOT NULL,
>UNIQUE(x,y));
> 
> I have a helper function, DISTANCE(x1,y1,x2,y2).
> The results I'm looking for are:
> 
> "For every row in that table, I want the entire row in that same table 
> within a certain distance [eg 25], with the lowest "val". For rows 
> with the same "val", I want to use lowest distance as a tie breaker."
> 
> The answer I got on stackoverflow included this correlated subquery, 
> but it's not working:
> SELECT foo.*,
>   (SELECT id
>FROM foo AS nearest
>WHERE DIST(foo.x, foo.y, nearest.x, nearest.y) < 25
>ORDER BY val, DIST(foo.x, foo.y, nearest.x, nearest.y)
>LIMIT 1) AS id2
> FROM foo
> 
> I get the error "no such column: foo.x"
> 
> I tried rewriting it to have the subquery in a WHERE clause instead 
> [not quite the same query, but I think the problem I have is the same]
> 
> SELECT outerfoo.*
>  FROM foo outerfoo
>  WHERE outerfoo.id=(SELECT id
>   FROM foo AS nearest
>   WHERE DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) <= 25
>   ORDER BY val, DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y)
>   LIMIT 1)
> 
> And now I get the error "no such column: outerfoo.x"
> 
> Can anyone advise on what I might do?
> 
> Thank-you very much,
> Gary
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Correlated subquery throwing an error

[da...@andl.org]

Why not

SELECT foo.*,
  (SELECT id, DIST(foo.x, foo.y, nearest.x, nearest.y) AS d
   FROM foo AS nearest
   WHERE d < 25
   ORDER BY val, d
   LIMIT 1) AS id2
FROM foo

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Gary
Briggs
Sent: Monday, 15 February 2016 10:43 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] Correlated subquery throwing an error

I posted a question on stackoverflow, here:
http://stackoverflow.com/questions/35382897/implementing-a-sql-query-without
-window-functions

In short, I have a table that I'm trying to query:
CREATE TABLE foo (
   id INTEGER PRIMARY KEY,
   x REAL NOT NULL,
   y REAL NOT NULL,
   val REAL NOT NULL,
   UNIQUE(x,y));

I have a helper function, DISTANCE(x1,y1,x2,y2).
The results I'm looking for are:

"For every row in that table, I want the entire row in that same table
within a certain distance [eg 25], with the lowest "val". For rows with the
same "val", I want to use lowest distance as a tie breaker."

The answer I got on stackoverflow included this correlated subquery, but
it's not working:
SELECT foo.*,
  (SELECT id
   FROM foo AS nearest
   WHERE DIST(foo.x, foo.y, nearest.x, nearest.y) < 25
   ORDER BY val, DIST(foo.x, foo.y, nearest.x, nearest.y)
   LIMIT 1) AS id2
FROM foo

I get the error "no such column: foo.x"

I tried rewriting it to have the subquery in a WHERE clause instead [not
quite the same query, but I think the problem I have is the same]

SELECT outerfoo.*
 FROM foo outerfoo
 WHERE outerfoo.id=(SELECT id
  FROM foo AS nearest
  WHERE DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) <= 25
  ORDER BY val, DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y)
  LIMIT 1)

And now I get the error "no such column: outerfoo.x"

Can anyone advise on what I might do?

Thank-you very much,
Gary

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

[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

[da...@andl.org]

>>>Every SQLite database file has a text encoding that applies to the entire
file:  one of utf8, utf16be, or utf16le.  The database text encoding is
stored in the header.  You can see the encoding for a particular database
using:

 sqlite3 DATABASE.db .dbinfo

>>>(NB: The ".dbinfo" command is relatively recent, so you'll want the
latest version of sqlite3.exe for this to work.)

Mine is 3.8.6 2014-08-15 11:46:33 9491ba7d738528f168657adb43a198238abde19e.
I guess I need something newer. That does explain why I couldn't find out.

>>>All text store in the database file uses the database text encoding.
*All Text*.  So if your database encoding is UTF16 and you do
"sqlite3_bind_text()" then SQLite automatically converts your UTF8 input
into UTF16 before storing it.  Or if you do "sqlite3_column_text()", then
SQLite will convert from UTF16 to UTF8 before returning the answer.

>>>Obviously, in order to avoid unnecessary conversions, it works best if
the database encoding matches the encoding most commonly used by your
application.

OK, I get it. I don't want to enforce an encoding, I just want it to work
with any database.

I use the C API from C# using P/Invoke, and there doesn't seem to be any
native way to do utf-8 marshalling. It would seem the best result might be
to use the text16() family [such as sqlite3_value_text16()]. There is
marshalling from 'Unicode', which is Windows for utf-16, and that should be
reasonably efficient (compared to trying to do it in managed space).I'll ask
that one over on Stack Overflow. Thanks for the tips.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

[da...@andl.org]

Thanks for the response. It helps.

> 1. Type name (string) to use in CREATE TABLE.
> 2. Affinity.
> 3. Datatype that will be returned by C API calls 4. Which C API call 
> to use to get and put data values.
>
> My current choices are:
> Bool: TINYINT, INTEGER, value_int

>>>Why not "BOOLEAN"?  (This would result in NUMERIC affinity, which would
not make any difference.)

Fair enough. Will do.

> Number: NUMERIC, NUMERIC, value_???
> should I choose text functions like sqlite3_value_text() and do all my 
> own conversions

>>>That would be silly.

Less code I think?

> or is there benefit in using a different function according to the 
> storage class/data type?

>>>If you do not know what type is _actually_ stored, call
sqlite3_column_type() first.

That looks like one call to get the type, a choice of 3 calls for INTEGER,
REAL or TEXT and a choice of 3 conversion/casts to get it to the final type,
plus unit tests for each range of values. Is it worth it, I ask myself?

> Time: DATETIME, NUMERIC, value_???.
> the only supported DATETIME format seems to be ISO8601, which has no 
> explicit C API support?

ISO8601 strings are strings, so they are just stored as TEXT.

>>>See .

Yes, I read that but that's for the SQL level. No such calls in C?

>>>Things like CURRENT_TIMESTAMP use the text format.  Numbers are
interpreted as Julian days by default; Unix time is used only when you
specify the 'unixepoch' modifier.

Yes, not much use to me.

> That looks like a lot of conversion overhead for something that is 
> easily stored in a 64-bit integer.

You need to store date/time values in one of the three supported formats
only if you want to use the built-in date functions, or if you want to be
compatible with data that already uses these formats.

>>>Quite a few Java programs use milliseconds since 1970.

And negative numbers for 'before Unix'? I guess that would do.

> all text values are UTF-8 by default?

>>>Yes.  It would be possible to configure databases to store text values as
UTF-16, but nobody does this, and they would be converted automatically when
using sqlite3_column_text().

Just asking. I got a few mojibakes and wondered if there were code pages
lurking around there anywhere. UTF-8 is fine.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

[da...@andl.org]

Hi Darren

Yes, I get that, but the idea is that as far as possible the underlying
database retains native values and types, so that (a) SQL queries work as
expected (b) non-Andl programs can access the data. I could simply encode
everything as my own private bit strings, but making maximum use of the
built in types would seem the way to go.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Darren
Duncan
Sent: Tuesday, 9 February 2016 12:21 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Andl: choices for generic types: bool, binary,
number/decimal, date/datetime, text/varchar

David, unless you're wanting to use SQLite's built-in datetime operators,
then just encode yours somehow and put them in another field type, and
decode them on retrieval into your own datetime types.  Depending what you
encode them as, pick the appropriate built-in type. -- Darren Duncan

On 2016-02-08 5:00 PM, david at andl.org wrote:
> Having read and understood the documentation on Sqlite data types, I'm
really just looking for a single recommendation on which choices to make.
>
> I need to store generic data in 5 types: bool, binary, number/decimal,
text/nvarchar, time/date/datetime. Decimal has more than 15 digits of
precision. Text is Unicode. Time is years - with fractional seconds.
>
> For each type I need to choose:
>
> 1. Type name (string) to use in CREATE TABLE.
> 2. Affinity.
> 3. Datatype that will be returned by C API calls 4. Which C API call 
> to use to get and put data values.
>
> My current choices are:
> Bool: TINYINT, INTEGER, value_int
> Binary: BLOB, BLOB, value_blob
> Number: NUMERIC, NUMERIC, value_???
> Text: TEXT, TEXT, Encoding utf-8, value_text
> Time: DATETIME, NUMERIC, value_???.
>
> The first two are easy enough. No problems.
>
> Number: should I choose text functions like sqlite3_value_text() and do
all my own conversions, or is there benefit in using a different function
according to the storage class/data type? Or is sqlite3_value_numeric_type()
the way to go?
>
> Text: I assume this just works, and all text values are UTF-8 by default?
>
> Time: the only supported DATETIME format seems to be ISO8601, which has no
explicit C API support? That looks like a lot of conversion overhead for
something that is easily stored in a 64-bit integer. What would
sqlite3_value_numeric_type() do?
>
> [Has there been any consideration of extending the range of types to 
> include decimal and a binary datetime?]
>
> Sorry if it's a bit scrappy, but I just need to make some choices and then
go away and write the code.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org

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

[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

[da...@andl.org]

Having read and understood the documentation on Sqlite data types, I'm really 
just looking for a single recommendation on which choices to make.

I need to store generic data in 5 types: bool, binary, number/decimal, 
text/nvarchar, time/date/datetime. Decimal has more than 15 digits of 
precision. Text is Unicode. Time is years - with fractional seconds.

For each type I need to choose:

1. Type name (string) to use in CREATE TABLE.
2. Affinity.
3. Datatype that will be returned by C API calls
4. Which C API call to use to get and put data values.

My current choices are:
Bool: TINYINT, INTEGER, value_int
Binary: BLOB, BLOB, value_blob
Number: NUMERIC, NUMERIC, value_???
Text: TEXT, TEXT, Encoding utf-8, value_text
Time: DATETIME, NUMERIC, value_???.

The first two are easy enough. No problems.

Number: should I choose text functions like sqlite3_value_text() and do all my 
own conversions, or is there benefit in using a different function according to 
the storage class/data type? Or is sqlite3_value_numeric_type() the way to go?

Text: I assume this just works, and all text values are UTF-8 by default?

Time: the only supported DATETIME format seems to be ISO8601, which has no 
explicit C API support? That looks like a lot of conversion overhead for 
something that is easily stored in a 64-bit integer. What would 
sqlite3_value_numeric_type() do?

[Has there been any consideration of extending the range of types to include 
decimal and a binary datetime?]

Sorry if it's a bit scrappy, but I just need to make some choices and then go 
away and write the code.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] User-defined types -- in Andl

[da...@andl.org]

Andl does contain an Sudoku solver, far shorter than Pasma's. See
http://www.andl.org/2015/06/recursive-queries-sudoku-solver/.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
Jean-Christophe Deschamps
Sent: Tuesday, 9 June 2015 5:16 PM
To: General Discussion of SQLite Database

Subject: Re: [sqlite] User-defined types -- in Andl

At 08:27 09/06/2015, you wrote:

>Andl is at a slightly higher level than SQL for writing simple queries.
>Where it shines is writing complex queries that involve user-defined 
>types, custom transformations and custom aggregations. For complex 
>relational operations there is nothing I know that can come close, 
>productivity wise.
`---

You call for complexity, you get it!

What I would find pretty convincing is seeing how andl would translate the
self-contained sudoku solver posted by E. Pasma in this post: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-Mar
ch/051982.html

Granted, this is far from the typical SQL you can find in routine use, but I
believe that andl being able to elegantly translate it would certainly
impress a number of readers and make many of us more interested in digging
further.

I'm not throwing such a mayhem challenge at you in the tone of "if andl
can't do that, then shut up". Maybe andl is not yet complete enough today to
achieve that and this wouldn't be a big issue. But if it can I'm sure andl
will attract more attention.

JcD 

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

[sqlite] Andl update

[da...@andl.org]

Just a note to say that Andl continues to move forward. Recent posts include
a Thrift interface, Workbench and new syntax.

Relevance: Andl is tightly integrated with Sqlite, and provides an
alternative query language to SQL.

Posts are here http://www.andl.org/posts/. Let me know if you have any
questions.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Slight problem with sqlite3_compileoption_get

[da...@andl.org]

I have been here many times. As I recall the steps interfacing to COM/IDL
are:

1. Find out what ACTUAL types and ACTUAL calling convention were used by the
DLL you are targeting. The C header file declarations are not definitive:
you need to examine the options used to compile the DLL and often the link
map to be sure you know what finished up in the DLL.

2. Match the IDL explicitly to the ACTUAL types and ACTUAL calling
convention used.

The commonest problem is the calling convention. If your DLL is compiled
with STDCALL, you must makes sure to match it; and vice versa. Unfortunately
with the wrong calling convention is it possible for some calls to work
others not. Even so, this is an odd one.

I checked the Sqlite source code and there is nothing special about this
function that I can see.

I didn't receive your original message (dunno why) so I don't know what the
'problem' was.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Bart
Smissaert
Sent: Sunday, 24 January 2016 12:37 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Slight problem with sqlite3_compileoption_get

> That is where you confused me.  C also has a long type!
Yes, that is why I said IDL long.

> File byte under "lucky" above.

OK, I can see that and replaced that with int.
What is different though about sqlite3_compileoption_get, so that long in
the IDL causes the mentioned problems and int doesn't?

RBS



On Sun, Jan 24, 2016 at 1:11 AM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 23/01/16 13:14, Bart Smissaert wrote:
> >> I am somewhat confused about what you wrote.
> >
> > This has to do with making a .tlb (type library) to access 
> > sqlite3.dll from a VB6 ActiveX dll.
>
> That much was clear.
>
> > Sofar I have mapped SQLite int with IDL long  ...
>
> That is where you confused me.  C also has a long type!  The C long 
> type will either be the same size as C int, or bigger[1].  Windows has 
> a long legacy, and that is how you ended up in this situation.  On 16 
> bit Windows, int was 16 bits and long 32 bits.  On 32 (and 64) bit 
> Windows, int and long are both 32 bits.
>
> Because of the 16 bit legacy of Visual Basic, you'll be getting this 
> advice about ints and longs and compatibility.  On Win32 where SQLite 
> says "int" it means a signed 32 bit number.
>
> Note you can get lucky with mismatches.  (I won't bore you with 
> details about promotion to int, caller vs callee cleanup, how little 
> endian helps with the luck).
>
> > .. and that is all working fine, except for 
> > sqlite3_compileoption_get. Instead here int or byte works fine.
>
> File byte under "lucky" above.
>
> sqlite3_compileoption definitely takes a 32 bit integer as its only 
> parameter.  If "long" in your idl also maps to a 32 bit integer, then 
> there is something else going on in your diagnosis of "working fine"
> :-)  Sadly you'll need to figure that one out.
>
> [1] There are rules about what standards conforming compilers are 
> allowed to do, there is practise over what they actually do, the 
> implementors of systems do various things (often for historical or 
> "compatibility" reasons).  This also spills over into ABIs and calling 
> conventions (eg exactly how types of parameters and return values are 
> passed on certain CPU architectures).  The list of considerations go 
> on and on.  It looks like Keith would be happy to discuss them :-)
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v2
>
> iEYEARECAAYFAlakJLQACgkQmOOfHg372QS7SACfboJV/o1apKA3q5UInT5sOY6/
> NUsAn2UbTS1004P5QnpJGRQcCTASMJaI
> =LMtI
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[da...@andl.org]

I agree with Duncan. The original SQL code and a list of bound values.

This is a problem we know well and have already solved exactly this way in a 
different context.

Yes, it would be a good new feature request.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Saturday, 9 January 2016 9:22 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Wish List for 2016: High Level API for Object Oriented 
Interactive Languages

Okay, I think this clears some things up.

On 2016-01-08 11:36 AM, Warren Young wrote:
> On Jan 8, 2016, at 12:39 AM, Darren Duncan  wrote:
>>
>> I interpreted your request as if current systems' error outputs at execute 
>> time were printing out the problematic SQL statement with placeholder names 
>> as originally prepared, and you wanted the error outputs to have the 
>> placeholders substituted with literals for the values passed to them at 
>> execute time interpolated into them.
>
> Yes.
>
>> one can just list the bound values separately / afterwards rather than 
>> having to rewrite the SQL to interpolate those values.
>
> Of course, but the question is not whether a caller *can* do this, it?s 
> whether the caller *should have to* do this.
>
>  From the caller?s perspective, it has already passed ownership of the values 
> off to SQLite via sqlite3_bind_*().  If an error occurs, the bind calls may 
> be a few levels separate from the sqlite3_step() call that actually causes 
> the error.  SQLite still owns the values, though, and could provide them in a 
> hypothetical sqlite3_preview() call, which assembles the effective SQL it 
> tried to execute and failed.
>
> You?re asking the caller to maintain separate ownership of data that SQLite 
> needs just for the error case.  SQLite has a much better reason to have a 
> copy of that data, so it should be providing the values to the error handler, 
> not the caller.

Actually, I agree with you that SQLite should be providing everything.  What I 
disagree with is making the interpolation necessary.  The hypothetical
sqlite3_preview() should output 2 things, the SQL as originally passed to 
prepare with placeholders intact, plus a list of placeholder names and their 
bound values that failed.

>> If your problem is that the output simply says an error occurred and doesn't 
>> print out the SQL, then fair enough, I misunderstood you.
>
> It?s a bit more than that.  The problem is that a given prepared statement is 
> necessarily generic.  Just from looking at the statement in a log file, you 
> can?t tell what values were used with it, which would help you understand the 
> context in which it was used.

To further explain, I see SQL as a programming language same as C or Perl or 
whatever.

Calling prepare is effectively invoking a compiler on SQL source code where 
that SQL source defines a routine that may have parameters.  Calling execute is 
then asking to execute that compiled routine where the bind parameters are the 
runtime-provided arguments to the routine.

Do you think it makes sense in any other common programming language that, if a 
routine fails with a particular set of arguments, that the debugging message 
includes say C source code rewritten to substitute literals where references to 
its parameters were?  Or does it make more sense for the debugging message to 
print the actual routine source plus a list of the passed argument values?  I 
am arguing for the latter, all done by SQLite.

I consider what is reasonable for SQL to be the same as for other languages.

-- Darren Duncan

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

[sqlite] How to see SQLite debugging information

[da...@andl.org]

On Windows you will get a console and standard output if you are running a
console application, and otherwise not.

I think you need a simple console app to call your ActiveX DLL, or find some
other way. Windows GUI app and standard output do not play well together.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



On 12/8/15, Bart Smissaert  wrote:
> So, what/where is that standard output channel?
> This is on a Win7 machine. How do I bring up that console window?
>

The standard output is what displays on your screen when you are in a DOS
box.

SQLite does not have any facilities for debugging in a GUI on Windows.

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

[sqlite] Tables and Columns of Database of Whatsapp

[da...@andl.org]

It's usually pretty safe to ignore what the law has to say about copyright,
because by the time that matters you would have to be dealing with lawyers.
That's not the problem.

What you seriously want to avoid is doing things that attract the attention
of lawyers, because they have a habit of making things expensive. The bigger
the company, the bigger the law firm and the more expensive they like to
make it.

Regardless of the law, if there is a serious risk of attracting the
attention of WhatsApp or their lawyers, you might want to reconsider.

And the best way to avoid attracting attention is not to tell anyone what
you're doing. There is a reasonable chance that just posting the application
name on a mailing list like this (which has a web-facing public interface)
will be enough that eventually it will get you noticed. With the
lawyer-related consequences mentioned above.

This is not the way I would recommend going about a project like this.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Darko
Volaric
Sent: Tuesday, 30 June 2015 1:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Tables and Columns of Database of Whatsapp

It should be kept in mind that lawyers routinely put things in these
"click-through agreements" that is unenforceable or illegal.

It may be perfectly legal to reverse engineer, for example: ( from
https://www.eff.org/issues/coders/reverse-engineering-faq )

"Courts have found that reverse engineering for interoperability, for
example, can be a fair use."

If it's being used for teaching it can also be fair use: ( from
http://www.teachingcopyright.org/handout/fair-use-faq )

"the court found that the biographer's use was fair, in part because the
biographer's purpose was to educate and inform the public"

Of course this reflects US law but it most likely also applies in Germany,
and is probably even more favorable for fair use there.


On Mon, Jun 29, 2015 at 6:42 AM, John McKown 
wrote:

> On Sun, Jun 28, 2015 at 7:24 AM,  wrote:
>
> > Hi,
> >
> > I teach pupils SQL in school.
> >
> > I want to create exercises about the SQLite database of Whatsapp.
> >
> > Can you tell me the names of tables and the names of columns?
> >
> > For the tables, I'll think of data.
> >
> > Thank you,
> >
> > Bob
> >
>
> I'm going to go a bit sideways on this, I hope it is not objectionable.
> Have you contacted Whatsapp about this? I ask because on their web 
> site at https://www.whatsapp.com/legal/, it specifically has legalese
saying "
> (iii) you will not attempt to reverse engineer, alter or modify any 
> part of the Service;" I am not any kind of a lawyer. But it _might_ be 
> argued (similar to Oracle vs. Google on the Java API) that the schema 
> of the SQLite data base is "part of the Service" and that, especially 
> by using it for teaching purposes, you are "reverse engineering" it. 
> Yes, likely a extreme position. But IP lawyers can be sharks. Just 
> myself, personally, I'd contact Whatsapp and simply ask permission, 
> perhaps explaining what you want to do and why you thought that their 
> DB would be a good teaching scenario for your students.
>
> Again, I'm just trying urge caution in today's litigious society. I 
> don't mean to imply that you are doing anything illegal or immoral (or 
> fattening ).
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a 
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will
be.
>
> My sister opened a computer store in Hawaii. She sells C shells down 
> by the seashore.
> If someone tell you that nothing is impossible:
> Ask him to dribble a football.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about the list

[da...@andl.org]

Some of the messages I receive have been cross-posted to two lists (eg from
jkl):

sqlite-users at mailinglists.sqlite.org
sqlite-users at sqlite.org

When I reply, the first list accepts my post and the second bounces it. That
suggests there are two different lists, but it's not obvious what the
purpose is.

Any idea what this means?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

Agreed. Two points.

1. Andl can never replace SQL in one step. The challenge is to find some
useful niche and demonstrate an end-to-end solution to a problem, and show
that as a total solution it's better than could be done with SQL. It should
have a cross-platform front end UI/UX (say JavaScript, Java or Ruby but NOT
C#/.NET) and (for now at least) a backend on SQLite, with Andl in the middle
and NO ORM.

2. Despite what you might think, Andl is an easy language to learn. However,
using it to do real work is a major challenge because the theoretic
underpinnings are unfamiliar to most people. Actually solving problems using
relations and operations on relations is new, even to very expert SQL users.
There are many interesting problems that can be solved in just a few lines,
but mentally constructing the steps to those few lines is tough. The time
taken to write the Sudoku solver on the  web site is way out of proportion
to the length.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of James K.
Lowden
Sent: Friday, 19 June 2015 7:11 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

On Wed, 17 Jun 2015 22:05:12 -0700
Darren Duncan  wrote:

> I also believe the world is ripe to have SQL alternatives, its just a 
> matter of ones appearing that are compelling to users for real work 
> and not just an academic exercise. The fact we're still generally with 
> SQL means this hasn't happened yet, but that doesn't mean it won't.

Yes, I've been keeping track of TTM-inspired projects, and tried to convince
Ingres to commercialize its D implementation.  What makes David's andl
unusual is that it's a new language atop a DBMS that is used in production.


Still, I'm not so sure the world is ready for a better SQL.  The evidence
stands against, certainly.  We already discarded one -- QUEL -- which hardly
anyone remembers.  A great deal of effort has gone into replacing SQL with
less powerful constructs with no theoretical foundation, and into neutering
the DBMS with ORM toys.  

Do not underestimate SQL's enormous inertia and network effect.
Recognize that even if the syntax is better, engineering challenges remain
if the promise of fidelity to the relational model is to be realized.  

The inertia stems from laziness and ignorance, never in short supply.  A
fraction of those who write SQL understand the math and logic underlying it.
Forums like Stack Overflow are rife with questions that demonstrate as much;
the answers are often not much better.  If you're not thinking in terms of
relational algebra and are unaware of the benefits of using logic to
accomplish your task, changing syntaxes will only compound your problems.
If you *are* thinking in those terms, it's still work to learn a new
language.  It's not clear that a better syntax -- if that's all you get --
would be seen as a worthwhile effort for very many people.  

The network effect is important, too.  The market recognizes knowledge of
SQL as a skill (even if it underestimates its value).  That skill is
portable across jobs and DBMS implementations.  It is a way that programmers
communicate to each other across the organization and time.  A new language
has to clear a threshold of "better" to be accepted.  

There are two engineering challenges that come to mind: performance and
set-theory semantics.  

As David outlines in his blog, it's no mean feat to swap out SQL from any
DBMS, including SQLite.  Because the query language is assumed/known to be
SQL, query planning, rewriting, transformation, and optimization is bound up
in the grammar of SQL itself.  Readers of this list appreciate how much work
goes into that.  

Set-theory touches on the implementation, too.  DISTINCT is implied in andl.
It was left out of SQL because it requires a sort.
The ramifications are nontrivial; consider joining on a nonkey and counting
the results.  Also remember that SQLite violates even SQL's set-theoretic
features, such as when 

update T set X = X + 1

fails if X is declared unique.  

So I would say replacing SQL with andl or similar is in fact not analogous
to replacing Perl with Python or C++ with Go.  The requisite knowledge in
the user community to appreciate the improvement is greater (and relatively
nonexistent).  And the engineering effort to build a DBMS is far more than
to build a compiler.  

There's a reason Larry Ellison can affort a yacht almost 500 feet long.  

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

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

Thank you for your comment. I take it seriously.

Andl uses very few special characters: only about 3 or 4 that would require any 
explanation. But I take your point: a distinctive feature is that it lacks 
familiar keywords that draw the eye and help guide understanding. It makes it 
very compact for me to write samples and test programs, but not so good for new 
readers.

Andl has close analogues to SELECT, WHERE, ORDER BY, GROUP BY, INSERT, UPDATE 
and DELETE, it already has JOIN, UNION, INTERSECT and MINUS (== EXCEPT) plus a 
number of other set and join variants, and it has no need for HAVING, ALL, 
DISTINCT, INTO and several others. It could use FROM, but this would add little.

The syntax could easily be modified to use those (familiar) words, which might 
indeed make it more acceptable to SQL users. I have avoided doing so to this 
point to avoided confusing debates on another list. It might also be confusing, 
because although the analogues are there, the actual semantics are different, 
perhaps to the point of confusion.

But it's worth thinking about.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Marc L. Allen
Sent: Friday, 19 June 2015 1:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

Frankly, I've avoided taking a close look at Andl because of its massive use of 
special characters.  I can usually work out a rough meaning on your examples 
because I know the SQL it came from.  If I became 'Andl proficient' it probably 
wouldn't be a problem, but I gotta ask.. what's the issue with just using 
keywords?  I feel like I'm looking at APL.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
da...@andl.org
Sent: Thursday, June 18, 2015 10:59 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

Yes, yours is the kind of situation Andl is aimed at. It does most of that 
stuff, but it's not ready for prime time yet. It's amazingly good at doing 
complex queries in just a few lines of code, but it lacks the external 
connections for it to be used for real applications.

I don't really understand the 'row access' or 'multiple row sets' in terms of 
the need being filled, and what Andl has to do to meet it. Hopefully this will 
come out if and when people try to use it to do real work.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Marc L. Allen
Sent: Thursday, 18 June 2015 11:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I don't understand all the issues with SQL as it is today.  I doubt I'm at the 
level of most of the posters in this group.  However, I do write a mess of it 
so I thought I'd give my two cents as a programmer.

For what it does, SQL does it really well.  That is, it takes a couple of sets 
of data, links them together and returns a set of data with given relations.  
But, that's just not enough.  

Recursive SQL is one of the best things to happen to SQL, though the syntax is 
rather wonky.  But, I think it's done that way because SQL is NOT a programming 
language and we keep trying to turn it into one.  Ok.. not turn it into one, 
per say, but we want to, quite often, do programming-like work on the data as 
part of the 'linking' process.  This is especially prevalent in the systems 
supporting stored procedures.  Personally, I find it a pain to combine SQL and 
programming in code.  First, I have to generate the SQL, then I have to do 
whatever is necessary to iterate over the result set, often requiring 
additional SQL and additional iteration.  So, if SPs are available, I tend to 
put a lot of logic in them and, frankly, SQL and it's variants really aren't 
good at that.  Furthermore, the SQL environment is not part of the controlling 
programming environment and that causes additional problems.  It's also slow to 
go back and forth, especially when the application and database are separated.

I guess, at the end, is that I  need a data language that has both set 
operations and iteration operations.  I would want a language or construct that 
gives me row access during the operations so that I can finely tune the 
actions.  Sort of an OnRow() function that works for all SQL operations.  Also, 
the ability to split the incoming row set into multiple row sets for different 
purposes. Also... (insert weird data flow operation t

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

Yes, yours is the kind of situation Andl is aimed at. It does most of that 
stuff, but it's not ready for prime time yet. It's amazingly good at doing 
complex queries in just a few lines of code, but it lacks the external 
connections for it to be used for real applications.

I don't really understand the 'row access' or 'multiple row sets' in terms of 
the need being filled, and what Andl has to do to meet it. Hopefully this will 
come out if and when people try to use it to do real work.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Marc L. Allen
Sent: Thursday, 18 June 2015 11:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I don't understand all the issues with SQL as it is today.  I doubt I'm at the 
level of most of the posters in this group.  However, I do write a mess of it 
so I thought I'd give my two cents as a programmer.

For what it does, SQL does it really well.  That is, it takes a couple of sets 
of data, links them together and returns a set of data with given relations.  
But, that's just not enough.  

Recursive SQL is one of the best things to happen to SQL, though the syntax is 
rather wonky.  But, I think it's done that way because SQL is NOT a programming 
language and we keep trying to turn it into one.  Ok.. not turn it into one, 
per say, but we want to, quite often, do programming-like work on the data as 
part of the 'linking' process.  This is especially prevalent in the systems 
supporting stored procedures.  Personally, I find it a pain to combine SQL and 
programming in code.  First, I have to generate the SQL, then I have to do 
whatever is necessary to iterate over the result set, often requiring 
additional SQL and additional iteration.  So, if SPs are available, I tend to 
put a lot of logic in them and, frankly, SQL and it's variants really aren't 
good at that.  Furthermore, the SQL environment is not part of the controlling 
programming environment and that causes additional problems.  It's also slow to 
go back and forth, especially when the application and database are separated.

I guess, at the end, is that I  need a data language that has both set 
operations and iteration operations.  I would want a language or construct that 
gives me row access during the operations so that I can finely tune the 
actions.  Sort of an OnRow() function that works for all SQL operations.  Also, 
the ability to split the incoming row set into multiple row sets for different 
purposes. Also... (insert weird data flow operation that doesn't exist 
natively).

As I read back through this, I realize how poorly stated it is.  But, I'm going 
to post it anyhow.  Maybe someone will say, "Hey, stupid... *this* exists.. try 
it."  Heck, maybe Andl does it.  I haven't looked.

Marc

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
da...@andl.org
Sent: Thursday, June 18, 2015 6:36 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I agree. It would be relatively easy to produce a new language with a syntax 
based on SQL, which was superficially familiar, but there are many necessary 
differences at the lower levels. One issue where bullet biting is needed is 
nulls and three-valued logic; another is duplicate rows and nameless or 
duplicate columns. The type system needs a complete overhaul too. C was a 
clean, polished and quite small language; SQL is none of those.

And the biggest thing? Most SQL is used as a data sub-language, but the need is 
for a complete database programming language and a way out of the ORM mess. You 
can't get that from putting a layer on top of SQL.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Thursday, 18 June 2015 5:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I disagree with the idea that a good SQL alternative would just be a superset 
of SQL as you propose.

That has already been done numerous times, the principal manifestations being 
each SQL DBMS that has its own small or large differences in syntax and 
features from each other.

SQL is already a very complex language due in part to most of its features each 
having their own custom syntax, often several variations per feature to boot, 
as well as lots of arbitrary limitations or specified inconsistent behaviors, a 
lot of these f

[sqlite] Andl: release of recursive queries

[da...@andl.org]

The latest release of Andl supports recursive queries. There are sample 
programs for org chart, Mandelbrot set and a Sudoku solver.

You can read about it as http://www.andl.org/posts/ or get it from GitHub 
https://github.com/davidandl/Andl.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

I agree. It would be relatively easy to produce a new language with a syntax 
based on SQL, which was superficially familiar, but there are many necessary 
differences at the lower levels. One issue where bullet biting is needed is 
nulls and three-valued logic; another is duplicate rows and nameless or 
duplicate columns. The type system needs a complete overhaul too. C was a 
clean, polished and quite small language; SQL is none of those.

And the biggest thing? Most SQL is used as a data sub-language, but the need is 
for a complete database programming language and a way out of the ORM mess. You 
can't get that from putting a layer on top of SQL.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Thursday, 18 June 2015 5:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I disagree with the idea that a good SQL alternative would just be a superset 
of SQL as you propose.

That has already been done numerous times, the principal manifestations being 
each SQL DBMS that has its own small or large differences in syntax and 
features from each other.

SQL is already a very complex language due in part to most of its features each 
having their own custom syntax, often several variations per feature to boot, 
as well as lots of arbitrary limitations or specified inconsistent behaviors, a 
lot of these for keeping backwards compatibility with various old or 
vendor-specific ways of doing things.

What a good SQL alternative would actually be is a much more self-consistent 
and less redundant than SQL.  It would still have all of SQL's expressive power 
and features so that any SQL code can be translated to it, including 
automatically, without too much circumlocution.  That is how you would simplify 
the transition and re-utilization of existing code.  The good alternative would 
actually be easier for a DBMS to implement also without losing any power.

-- Darren Duncan

On 2015-06-17 11:52 PM, ajm at zator.com wrote:
> Indeed, I'm agree with Darren, and continuing its thought, perhaps that 
> hypothetical new language would be a clean extensi?n of SQL in the same way 
> that C++ was respect to C, simplifying the transition and reutilization of 
> legacy code.
>
> Cheers.
>
> --
> A.J. Millan
>>
>>  Mensaje original 
>> De: 
>> Para:  "'General Discussion of SQLite 
>> Database'"
>> Fecha:  Thu, 18 Jun 2015 14:50:40 +1000
>> Asunto:  Re: [sqlite] Mozilla wiki 'avoid SQLite'
>>
>> The question for now is: does a new database programming language 
>> have a place?

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

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

I don't have enough experience in any FP language to be productive. C# has a
lot of FP like features (lambdas, LINQ, etc) that I do know how to use. And
ultimately, the C# version could be recoded in C++ if it's worth doing.

The question for now is: does a new database programming language have a
place?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Drago,
William @ CSG - NARDA-MITEQ
Sent: Wednesday, 17 June 2015 11:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- 
> users-bounces at mailinglists.sqlite.org] On Behalf Of david at andl.org
> Sent: Monday, June 15, 2015 2:28 AM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'
>
> I won't abuse the patience of our hosts by prolonging this debate, but 
> I disagree strongly with this theme.
>
> I have almost certainly written more C/C++ code than you or most of 
> the people on this list, and I never choose it first. I am personally 
> at least 3 times as productive in C# as I am in C (slightly narrower 
> margin in C++), and computers are far cheaper than brains.

If productivity is important is there any reason why you're not working in
F#? I don't know that language myself, but I've seen some impressive demos.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


>
> This theme is strongly reminiscent of arguments over moving from 
> assembly language, and it's basically wrong. The best tool is the one 
> that gets the required job done with maximal speed at minimal cost.
>
> And just for the record, C# does not compile into byte code. I suggest 
> you check your facts.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
> Aleksey Tulinov
> Sent: Monday, 15 June 2015 10:32 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'
>
> On 15/06/15 01:00, Simon Slavin wrote:
>
> Simon,
>
> > Perhaps the next generation of computer languages will be designed 
> > by
> computer, to let us speak to them in an efficient manner.
> >
>
> I'm sure computer would insist on C, if not, then it's apparently a 
> software bug.
>
> On a serious note, i think it's rather question of programming 
> computers or programming another programs. As you've mentioned, higher 
> level languages often compiles into byte-code which is then 
> interpreted by virtual machine.
> So you don't speak to machine, you speak to mediator who speak to 
> machine.
> This by definition an overhead, with growing complexity of the 
> program, overhead will grow accordingly, this is unavoidable.
>
> I think it's also fair to say that SQL is not for programming machines 
> (no offense), it's for programming SQLite and other database 
> implementations.
> Even if something is called "virtual machine", VM always behaves 
> somehow differently from The Machine, thus programmer'
> efforts has mediated effect on latter.
>
> Of course VM could do a good job in a specific domain, but each VM 
> limits the liberty of expressing yourself to the machine and vice 
> versa.
>
> In my opinion best database language would reflect the way in which 
> database works and best computer programming language would reflect 
> the way in which computer works, as close as reasonably possible.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
attachments are solely for the use of the addressee and may contain
information that is privileged or confidential. Any disclosure, use or
distribution of the information contained herein is prohibited. In the event
this e-mail contains technical data within the definition of the
International Traffic in Arms Regulations or Export Administration
Regulations, it is subject to the export control laws of the U.S.Government.
The recipient should check this e-mail and any attachments for the presence
of viruses as L-3 does not accept any liability associated with the
transmission of this e-mail. If you have received this communication in
error, please notify the sender by reply e-mail and immediately delete this
message and an

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

I agree.

SQL is quite deficient in terms of set-oriented updates. INSERT is more or
less UNION, but UPDATE and DELETE have no set-oriented forms.

The relational algebra describes operations on sets of tuples, where the
only operation on attributes is to compare them by name or equal value. SQL
implements most of the relational algebra directly, and all of it by
combining operations.

Updates should be semantically equivalent to an operation from the
Relational Algebra followed by assignment (new value replaces old). INSERT
works like that, but UPDATE and DELETE do not. Try writing a query to update
all the salaries for a company where all the new salaries are found in some
other table (ie a JOIN). Try writing a query to delete all the employees
listed in some other table (another JOIN).

Andl already has set-oriented UPDATE and DELETE, as well as the familiar
predicate and computed styles. Thank you for asking. As it happens I have
not been able to fully implement them on SQLite so far, because of
limitations in the underlying SQL.

Andl cannot perform any alterations on the columns of known tables, because
that would change its relational type. It's easy to create a new table and
copy data, but the issue of the proper way to handle versioning and
migrations is still open.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Wednesday, 17 June 2015 6:11 AM
To: sqlite-users at sqlite.org; General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'


On 16 Jun 2015, at 7:44pm, James K. Lowden  wrote:

>  wrote:
> 
>> What would make a database programming better, or best?
> 
> Two things I've often pointed to are namespaces and regular 
> expressions.  Another is compound datatypes.

I don't have your problem with namespaces since, to me, they really are just
prefixes.  I do agree that regular expressions are a problem.  They don't
really belong in the language but they are very convenient when they are
there.

SQLite has JOINs (or sub-selects, which amount to the same thing at a low
level) for INSERT and for SELECT but not for UPDATE.  A few times when
working with SQLite I've found myself writing UPDATE ... JOIN.  And then
having to do the job in my own code instead.  And if you add JOIN to UPDATE
you should probably add it to DELETE FROM too.

The other problem with SQLite is the lack of ALTER TABLE ... DROP COLUMN.
But to support it you need SQLite to have a proper internal model of which
columns are used for what, rather than to just store and reparse the CREATE
TABLE commands.

The thing I always found interesting about SQL was that it picks three
English words, INSERT, DELETE, UPDATE, and says that that's all you need to
do.  And it's right !  Is there something special about the 'three-ness' of
database operations ?  Or are you meant to think of it as two writing
operations (INSERT, DELETE) and a convenience operation which combines them
(UPDATE) ?  If there was another word, what would it be ?  REPLACE ?
DUPLICATE ?

Also, why is there only one English word needed for reading operations ?
What would a database language look like if it has more than one word ?
Would there be a difference between FIND and SCAN ?

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

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

A recursive function contains a computation and a decision: whether to
terminate or go deeper. Any recursive function/query will fail to terminate
if the termination condition is not satisfied.

Here are two similar CTEs. The first terminates, the second does not.

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<100)
SELECT x FROM cnt;

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x>0)
SELECT x FROM cnt;

A recursive query on DAG data will still not terminate if the recursive part
of the query keeps returning the same results instead of advancing through
the data. Of course that would not be a 'correct query'.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of James K.
Lowden
Sent: Wednesday, 17 June 2015 4:45 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is recursive CTE fully capable?

On Mon, 15 Jun 2015 11:03:17 +1000
 wrote:

> >>>Unless the recursion is circular, I don't see how an SQL query over 
> >>>a finite database could fail to terminate.
> 
> What does this mean? It is trivial to write a recursive CTE that does 
> not terminate, and the property of "circularity" is not what makes the 
> difference.

Hmm, for a correctly written recursive query not to terminate, is it not a
requirement that the data contain a cycle?  I can't prove it, but no
counterexample springs to mind.  

In the positive: a correct recursive query always terminates if the data
represent a directed acyclic graph.  

By "correct" I mean the CTE expresses a recursive relation.  If you recurse
over

with R (a, b) as (select 1 as a, 1 as b)

you have no right to expect termination.  But you might be able to fry an
egg on the processor.  

--jkl

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

[sqlite] What should a database language do? (was: RE: Mozilla wiki 'avoid SQLite')

[da...@andl.org]

Thank you for the comments.

Andl already has regular expressions and compound datatypes. They do
everything you list here. [Regex is pretty obvious, and user types are as
per TTM.]

Namespaces: interesting idea. I'm not sure a hierarchical model is the best
choice, but I can definitely see that 'packages' of data and code could be
useful. Andl has the concept of a catalog, to store persistent information
about relations (tables), operators and types (they have to go together).
The catalog name would make a natural namespace. Definitely one for the todo
list.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of James K.
Lowden
Sent: Wednesday, 17 June 2015 4:45 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

On Tue, 16 Jun 2015 09:56:38 +1000
 wrote:

> The question is: what should a database language do? Andl can already 
> match or surpass SQL on database programming tasks, but is that 
> interesting enough?
> 
> What would make a database programming better, or best?

Two things I've often pointed to are namespaces and regular expressions.
Another is compound datatypes.  

SQL and C both suffer from a single variable namespace.  We get around it by
using prefixes, e.g., "local_memcpy" or "annualized_returns".  

C++ added namespaces to the language.  I suggest SQL's successor do the
same, but use the Unix filesystem's hierarchical namespace as a model.
Putatively, 

ATTACH DATABASE 'foo.db' as /db/local/foo;
CHANGE DATABASE /db/local/foo;
CREATE TABLE annualized/returns ;

As far as I can tell, all the basic file and link management features of the
filesystem have analogous utility in a database.  (I would extend that idea
to permission bits, about which we could have a robust discussion if you're
interested.)  

Regular expressions likewise belong in a query language.  The LIKE operator,
an NIH relic of SQL's IBM origins, belongs on the ash heap of history.  Best
to follow Russ Cox's advice and restrict the regex syntax to constructs with
O(n) complexity.  

Finally, compound datatypes would simplify and encourage the use of natural
keys.  Something along these lines, 

CREATE UDT stock_key ( asof datetime, cusip char(8) );
CREATE TABLE prices( stock_key, price float );
CREATE TABLE returns( days int, return float, stock_key 
references prices );

Constraints defined on the compound user-defined type would of course apply
to whatever table it appears in.  

I thought I'd pass these along because you asked and because I don't
remember seeing them in TTM.  

I assume you're supporting row-generators.  Do you intend to support
table-comparison, too?  What about insert/update as assignment?  

--jkl

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

[sqlite] What should a database language do? [was: RE: Mozilla wiki 'avoid SQLite']

[da...@andl.org]

Thank you for your comments.

My target is developers, particularly those who are strong on the business
domain knowledge and UI/UX, but not so strong on the database stuff. My aim
is that they can write code to do sophisticated queries and data
manipulation without becoming an SQL guru and without needing to master an
ORM. Your mum is not on my list, sorry.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Jonathan
Moules
Sent: Wednesday, 17 June 2015 1:33 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

> The question is: what should a database language do? Andl can already
match or surpass SQL on database programming tasks, but is that interesting
enough?

As much as anything, that depends on what problem you're targeting, and even
your audience. At the risk of rekindling the High/low/assembly level
discussion, certainly at the high level, languages generally all have
different design goals, and because of this they appeal to different people.

This list appears to have a very high proportion of computer science types,
so we see discussions about recursions and "syntactic sugar" and all that
jazz; conversely, ask on a list for web-developers and you'll get a very
different set of answers.

Personally, as someone whose SQL-fu is weak, and who isn't a computer
scientist, one of the best things about SQL is that it's English-like and
there's a very low barrier to entry.

Consider this statement, which is about as complex as 90% of my SQL ever
gets:

SELECT * from buildings where height > 30 and colour = 'mauve'

With just 2 minutes of explaining I could probably get my mum to understand
what was going on there. Ok, that's an unrealistically low bar, but many
people who use SQL just have simple queries/problems. While I appreciate
andl doesn't have documentation yet, it doesn't look like it will pass the
"not a computer scientist" test for usability.
Just my 2c.

Cheers,
Jonathan


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
david at andl.org
Sent: Tuesday, June 16, 2015 12:57 AM
To: ajm at zator.com; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

>>>I think the best database language should reflect how earthlings 
>>>think
about the data, and the best computer programming language would reflect
easily the result we want get from them.

Care to expand on that?

I'm developing a new database language: Andl. My starting point has been the
relational model, Codd-Date-Darwen and The Third Manifesto.  My (only)
competitor seems to be SQL, which has a 40+ year lead. Nothing like a
challenge!

The question is: what should a database language do? Andl can already match
or surpass SQL on database programming tasks, but is that interesting
enough?

What would make a database programming better, or best?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org




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


This message has been scanned for viruses by MailControl -
www.mailcontrol.com



Click
https://www.mailcontrol.com/sr/43VYmEOYO7bGX2PQPOmvUj!GOBh06pKK8EdQhM6i4Fvmo
G0cFV2y0WTEr3dfsj1m6uKlINFphbL3KeH4!zzvzA== to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential
and legally privileged business communications. They do not of themselves
create legal commitments. Disclosure to parties other than addressees
requires our specific consent. We are not liable for unauthorised
disclosures nor reliance upon them.
If you have received this message in error please advise us immediately and
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered
in England No. 02562099


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

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

>>>I think the best database language should reflect how earthlings think
about the data, and the best computer programming language would reflect
easily the result we want get from them.

Care to expand on that?

I'm developing a new database language: Andl. My starting point has been the
relational model, Codd-Date-Darwen and The Third Manifesto.  My (only)
competitor seems to be SQL, which has a 40+ year lead. Nothing like a
challenge!

The question is: what should a database language do? Andl can already match
or surpass SQL on database programming tasks, but is that interesting
enough?

What would make a database programming better, or best?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] This mailing list seems to be public

[da...@andl.org]

I was under the impression that this mailing list was restricted to members.
However:

http://sqlite.1065341.n5.nabble.com/Under-what-circumstances-can-a-table-be-
locked-when-the-database-is-first-opened-td82371.html
http://comments.gmane.org/gmane.comp.db.sqlite.general/95119
http://osdir.com/ml/general/2015-06/msg09791.html

Is this official, or what?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

Ditto. My C# code routinely gets close to native C performance whenever I get 
around to benchmarking it, which is not often these days. And I can actually 
write code that runs safely on my 8 cores or on my teraflop GPU if I really 
need the speed.

But as I said, I really don't think this is the place. I hate seeing posts like 
that pass without response but I think I've said more than enough on the 
subject. The hazards of https://xkcd.com/386/ are ever-present.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Alex Bowden
Sent: Monday, 15 June 2015 7:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

Oh dear.  So you think that a high level language is one that does things by 
calling a lower level language?  Stop embarrassing yourself.

A high level language is one where the language designers are free to use 
whatever structural concepts best fit the problems that the language is 
designed to address,  whereas a low level language is one where the language 
designers are restricted to using structural concepts that map directly onto an 
assumed hardware model.

The assumption that a low level language compiles to assembler is nonsense.  I 
don?t remember any of them doing this before C, and even later,  many compilers 
still compile directly to machine code.

Assuming that higher level languages compile to lower level languages is also 
nonsense.  And even for ones that do, and a classic example of a language 
designed to do this would be Ratfor, then provided that the lower level 
language is compiling to machine code, then the only thing that is being slowed 
is the build process.  Not runtime execution.   

Now 20 years ago, folk like you, but better informed, would have argued that 
well written lower level language code was more efficient than higher level 
language code.  And sometimes they would have been correct.  But note the 
qualification of well written.  And even then, this was debatable.  If a 
language provides efficient higher level data structures, or fine grain 
synchronisation of multiple threads,  then the user is more likely to use them 
than if they have to create them themselves for each platform.

However today, it is rarely true.

Good modern optimisers can achieve efficiency that few, if any, low level 
language programmers will ever achieve.

And the thing is, that those compilers can do far broader optimisation of 
higher level languages that don?t expose features like the ability to write to 
a random address.  The optimisation potential of C, beyond very local 
optimisation, is minimal.

I?ve been running the same little benchmark across every hardware and language 
platform that I?ve used, for over 30 years.  

And I confess, I was very surprised when Java passed C in about 2005.  And yes, 
that was Java compiled to byte codes with runtime JIT compilation of the byte 
codes to machine code.  But it was faster.

The trend hasn?t reversed.



> On 14 Jun 2015, at 23:42, Scott Doctor  wrote:
> 
> On 6/14/2015 3:00 PM, Simon Slavin wrote:
>> The result is that that higher level the language you write in, the better.
> 
> I disagree. The use of languages higher than C result in slow bloated code. A 
> language that calls a language that calls a language. Simple programs become 
> multi-megabyte resource hogs. I agree that C compilers are able to optimize 
> assembler code to a level that hand-coded assembler probably could not 
> achieve. The problem is that higher level languages are not compiling to 
> assembler, but compiling to the language below it.
> 
> 
> Scott Doctor
> scott at scottdoctor.com
> --
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

As I said, I won't fuel the fire. This is the wrong place for this kind of
debate, and you should realise that. Your post reflects immaturity and
narrow experience, but I won't be the one to broaden your outlook. Your post
contains provable errors of fact, but I won't be correcting them. The
subject at hand is SQLite and the super-high-level language SQL, and it
would be discourteous of us to forget it.

Discussion closed.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
Doctor
Sent: Monday, 15 June 2015 5:03 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'


On 6/14/2015 11:28 PM, david at andl.org wrote:
> I won't abuse the patience of our hosts by prolonging this debate, but 
> I disagree strongly with this theme.

So you disagree with a disagreement?

> I have almost certainly written more C/C++ code than you or most of 
> the people on this list,

I doubt it.
> and I never choose it first. I am personally at least 3 times as 
> productive in C# as I am in C (slightly narrower margin in C++), and 
> computers are far cheaper than brains.
C# is just a bastardized version of C++ which is a bastardized version of C.
If you only program in C# then I guess you have only written code for PC's.
A whole other world exists beyond PC's and Microsoft. C# is in no way
portable, neither is C++. 
Only C is truly portable. Both C++ and C# require committing to a specific
compiler product which is the antithesis of C.

I am often forced to use C++ as the cross compiler platforms (such as C# or
Embarcadero (Borland) compiler) force such to use the system GUI, but all of
"My" code, as compared to the GUI code, that does the real work is written
in C. C++ is merely a wrapper around the C language. C and C++ co-mingle
very nicely.

> This theme is strongly reminiscent of arguments over moving from 
> assembly language, and it's basically wrong.

Well a bunch of very experienced programmers, with very diverse backgrounds
seem to disagree.
> The best tool is the one that gets the required job done with maximal 
> speed at minimal cost.
Which is more important:

How fast you can crank out code with minimal effort (which means you are
letting others write the canned code portion of your code),

or creating something new where you do the hard work so the end user has a
well designed efficient product?

> And just for the record, C# does not compile into byte code. I suggest 
> you check your facts.

Quite believable. Which is why Microsoft software is so efficient, fast,
small, and lacking of bugs.


Scott Doctor
scott at scottdoctor.com
--


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

[sqlite] Mozilla wiki 'avoid SQLite'

[da...@andl.org]

I won't abuse the patience of our hosts by prolonging this debate, but I
disagree strongly with this theme.

I have almost certainly written more C/C++ code than you or most of the
people on this list, and I never choose it first. I am personally at least 3
times as productive in C# as I am in C (slightly narrower margin in C++),
and computers are far cheaper than brains.

This theme is strongly reminiscent of arguments over moving from assembly
language, and it's basically wrong. The best tool is the one that gets the
required job done with maximal speed at minimal cost.

And just for the record, C# does not compile into byte code. I suggest you
check your facts.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Aleksey
Tulinov
Sent: Monday, 15 June 2015 10:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

On 15/06/15 01:00, Simon Slavin wrote:

Simon,

> Perhaps the next generation of computer languages will be designed by
computer, to let us speak to them in an efficient manner.
>

I'm sure computer would insist on C, if not, then it's apparently a software
bug.

On a serious note, i think it's rather question of programming computers or
programming another programs. As you've mentioned, higher level languages
often compiles into byte-code which is then interpreted by virtual machine.
So you don't speak to machine, you speak to mediator who speak to machine.
This by definition an overhead, with growing complexity of the program,
overhead will grow accordingly, this is unavoidable.

I think it's also fair to say that SQL is not for programming machines (no
offense), it's for programming SQLite and other database implementations.
Even if something is called "virtual machine", VM always behaves somehow
differently from The Machine, thus programmer' 
efforts has mediated effect on latter.

Of course VM could do a good job in a specific domain, but each VM limits
the liberty of expressing yourself to the machine and vice versa.

In my opinion best database language would reflect the way in which database
works and best computer programming language would reflect the way in which
computer works, as close as reasonably possible.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

>>>There are queries that cannot be formulated in first order predicate
logic, and recursion is the single capability of SQL that exceeds FOPL
power.  

True, wrt SQLite and its dialect, for which RCTE provides Turing
Completeness. Untrue for dialects of SQL that include PSM.

>>>Unless the recursion is circular, I don't see how an SQL query over a
finite database could fail to terminate.  

What does this mean? It is trivial to write a recursive CTE that does not
terminate, and the property of "circularity" is not what makes the
difference.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

>>>The task is to write some SQL code, including as many
INSERT/UPDATE/DELETEs as you want to make other tables with information
about the program, with a final SELECT which returns TRUE if and only if the
program will halt.

SQL with Recursive CTE is Turing Complete. The above is provably impossible.
But then you already knew that.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] User-defined types -- in Andl

[da...@andl.org]

If you have some test data I'll happily do that one. Meanwhile here is
something similar, using the test data from the SQLite CTE page.

orgchart

name  | boss
-
Alice |
Bob   | Alice
Cindy | Alice
Dave  | Bob
Emma  | Bob
Fred  | Cindy
Gail  | Cindy

ua := {{ name:= 'Alice', level := 0 }} 
  recurse({{ boss := name, level := level+1 }} 
  compose orgchart)
ua [{ t:=fill('.', level*3) & name }]

t
--
Alice
...Bob
...Cindy
..Dave
..Emma
..Fred
..Gail


Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Petite
Abeille
Sent: Monday, 15 June 2015 1:56 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl


> On Jun 14, 2015, at 4:01 PM, david at andl.org wrote:
> 
> First, I added a RECURSE() function to Andl, similar to the CTE in SQLite.

Nice.

> The Mandelbrot algorithm looks like this.

Could we see something more, hmmm, pedestrian? Perhaps a simple recursive
query, showing, say, all the managers of an employee given the following
structure: create table employee( id integer not null, manager_id integer,
constraint  employee_pk primary key( id ), constraint employee_manager_fk
foreign key( manager_id ) references employee( id )  )
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined types -- in Andl

[da...@andl.org]

Just a quick progress report, in case anyone is interested.

First, I added a RECURSE() function to Andl, similar to the CTE in SQLite.
The Mandelbrot algorithm looks like this.

xaxis := {{ x:=-2.0 }} recurse( {{ x:=x+0.05 }} [?(x<1.2)])
yaxis := {{ y:=-1.0 }} recurse( {{ y:=y+0.1 }} [?(y<1.1)])
m := ({{ iter:=0, x:=0, y:=0 }} join xaxis[{ cx:=x }] join yaxis[{ cy:=y }])
recurse( 
{{ iter:=iter+1, x := x*x-y*y+cx, y:=2*x*y+cy, cx, cy, }} [?(x*x+y*y<4.0
and iter<28)]
)
m2 := m[{ iter := fold(max,iter), cx, cy }] [$(cy,cx)]
m2 [ { cy, t := fold(&, right(left(' .+*#', 1 + iter div 6), 1)) }]

The output looks like this.

cy | t
-
  -1.0 | #   
  -0.9 |..#*..   
  -0.8 |  ..++.  
  -0.7 | ...++...   +
  -0.6 |..##+###*.   
  -0.5 |   .+.##*.   
  -0.4 |   .*###+.*  
  -0.3 |   ..+*.+#+*#+.  
  -0.2 |  ...+###++###.  
  -0.1 |   ...++*.   
   0.0 |  #...   
   0.1 |   ...++*.   
   0.2 |  ...+###++###.  
   0.3 |   ..+*.+#+*#+.  
   0.4 |   .*###+.*  
   0.5 |   .+.##*.   
   0.6 |..##+###*.   
   0.7 | ...++...   +
   0.8 |  ..++.  
   0.9 |..#*..   
   1.0 | #   

Still working on the Sudoku. The one in the SQLite documentation is a brute
force depth first search that got lucky on that particular puzzle. On most
puzzles I tried it takes forever.

The one provided by Pasma is faster, but really not a nice piece of code. My
hat goes off to the guy for getting it to work, but it's not something to
emulate.

My C# version solves all puzzles instantly, so I have the algorithms. The
question is: do they cross over to relation-land? The jury is still out on
that one.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
Jean-Christophe Deschamps
Sent: Tuesday, 9 June 2015 10:54 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl

At 13:50 09/06/2015, you wrote:

>BTW I don't remember the last time I saw SQL like this. Understanding 
>it might be the challenge
`---

Most probably! I can imagine that you don't encounter such style in common
business-like environments.

Take your time, this SQL piece is clearly beyond normal human understanding.
Perhaps getting in touch with the author could help. 
Everyone could possibly benefit of innovative views and avenues.

JcD 

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

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

Interesting.

SQL has been Turing complete since PSM was added to the 1992 standard. (Not
SQLite). I guess they mean "Turing complete with respect to the relation
datatype".

Andl already supports windowing (but not on SQLite). The Andl implementation
of recursion queries is nearly done.

I read the paper. It should provide a good source of sample queries for
Andl. However, I was not impressed by the "proof". Maybe you had to be
there.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Igor
Tandetnik
Sent: Friday, 12 June 2015 1:49 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Is recursive CTE fully capable?

On 6/11/2015 8:08 PM, david at andl.org wrote:
> The question I'm trying to ask is whether recursive CTE (either as 
> defined in the standard or as implemented in SQLite) carries the full 
> capability of evaluating recursive queries on appropriate data 
> structures, or are there queries that are beyond what it can do?

http://assets.en.oreilly.com/1/event/27/High%20Performance%20SQL%20with%20Po
stgreSQL%20Presentation.pdf
"With CTE and Windowing, SQL is Turing Complete."

--
Igor Tandetnik

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

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

Appropriate just means: set up the data structures any way you like in order
to capture the right info and support suitable queries.

I'm not trying to find a shortcut to solving NP complete problems. If I did,
I probably wouldn't post it here.

The question is: are there problems for which:
a) a recursive description and/or recursive data structure are a good match
b) interesting and/or useful queries can be formulated
c) those queries can be solved by general recursive solutions in other
programming languages
d) cannot be solved by the recursion provided by SQL CTE, because of
inherent limitations in the model/algorithm it supports?

There are lots of interesting things you can do with graphs (including
trees, lists, DAGs etc) including creating them, navigating them, modifying
them and measuring them. How far does this particular tool get you?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Friday, 12 June 2015 10:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is recursive CTE fully capable?


On 12 Jun 2015, at 1:08am, david at andl.org wrote:

> The question I'm trying to ask is whether recursive CTE (either as 
> defined in the standard or as implemented in SQLite) carries the full 
> capability of evaluating recursive queries on appropriate data 
> structures, or are there queries that are beyond what it can do?

I think your question can only be answered with "What you mean by
"appropriate" ?".  CTE is part of the 1999 SQL standard and as good a way as
any to implement directed graphs in SQL.

There are plenty of queries which can be expressed in a SQL database but
can't be answered without a computer which can reprogram itself, e.g. The
Halting Problem



or without a ridiculously long processing time, e.g. The Travelling Salesman
Problem



.  CTE is only one type of meta-programming and is not all-powerful.

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

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

The question I'm trying to ask is whether recursive CTE (either as defined
in the standard or as implemented in SQLite) carries the full capability of
evaluating recursive queries on appropriate data structures, or are there
queries that are beyond what it can do?

As far as I can see recursive CTE is very similar in capability (and
algorithm) to tail recursion. It's generally possible to code any loop and
most ordinary recursive functions in terms of tail recursion, which results
in highly efficient implementation. I'm interested to know where the limits
are, and what queries (if any) are beyond using this method.

If there is an academic paper or other reference that provides the answer
I'm happy to be pointed to that.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] User-defined types -- in Andl

[da...@andl.org]

That's about 30 hours from here. I might have to pass, for now. But if it's
on Youtube I'd be interested.

I think I understand recursive CTEs well enough now from the description in
the documentation and studying the code. I'm impressed at the brevity of
your solution, although in practice it does not perform well. The far more
complex implementation from Pasma performs better, I think largely because
it applies rules to narrow the search space.

My challenge now is to implement that feature in Andl.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
Hipp
Sent: Thursday, 11 June 2015 2:01 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl

On 6/9/15, david at andl.org  wrote:
> I don't remember the last time I saw SQL like this. Understanding it 
> might be the challenge...

I'll be giving a talk on CTEs this Saturday at the Southeastern Linuxfest
(http://www.southeastlinuxfest.org/) during which I will explain and
demonstrate how to write a simple CTE that solves a sudoku puzzle.  If you
cannot attend in person, I'm told that the talk will be streamed live to
youtube.

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

[sqlite] User-defined types

[da...@andl.org]

Here is my best effort at translating this query into Andl.



(

source_packages [?(release =~ 

'^(sid|stretch|jessie|wheezy|squeeze)$' ) 

{ name, release, subrelease, version }] join 

source_package_status [?(bug_name =~ '^(CVE-|TEMP-)')

{ rowid:=package, bug_name, vulnerable, urgency }] join 

bugs [{ bug_name := name }] join 

nvd_data [{ bug_name:=cve_name, cve_desc, range_remote }] join 

debian_cve [{ bug, bug_name }] join 

package_notes [{ rls := release }] [?(rls = release or 

rls = '' and and fixed_version <> '')

{ name := package, bug_name, fixed_version }] join 

package_notes_nodsa [{ name := package, comment }] 

) [%(name, bug_name, release, sub_release)]



Please note:

1.   Andl uses only natural joins and renaming. Since I don't have the
schema there could be name clashes.

2.   Andl will remove any nulls or duplicates (pure relational model
only)

3.   Andl uses regex rather than LIKE.



The Andl code is somewhat shorter than SQL, and it's more regular. The real
benefit comes when this query (or parts of it) are reused in combination
with others, because Andl is composable. That doesn't show up when it's just
emulating an SQL query.



If this database is available I would appreciate the opportunity to try this
out for real.



Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
Hipp
Sent: Friday, 5 June 2015 7:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types



On 6/4/15, Darko Volaric <  lists at darko.org> 
wrote:

> My point about JSON, etc is that there is no reason not to use that as 

> a query language if that makes it easier. If your system is efficient 

> with JSON, why not accept a query that is formatted as JSON? It's not 

> semantically different to SQL syntax. Here's an example (with a 

> roughly JSON notation):

> 

> {

>   operation: "insert"

>   table: "blah"

>   columns: ["a", "b", "c"]

>   values: [1.3, 2.0, 3.1]

>   on-conflict: "replace"

> }

> 

> That is equivalent to an INSERT SQL statement, but why form that SQL 

> string, possibly using memory and time, when your system can spit out 

> JSON (or whatever) effortlessly?



What is the JSON equivalent to the query shown below?  Can you honestly say
that the JSON equivalent (whatever it looks like) is somehow easier to
generate, read, parse, and/or debug than the SQL?



SELECT

  sp.name, st.bug_name,

  (SELECT cve_desc FROM nvd_data WHERE cve_name = st.bug_name),

  (SELECT debian_cve.bug FROM debian_cve

WHERE debian_cve.bug_name = st.bug_name

ORDER BY debian_cve.bug),

  sp.release,

  sp.subrelease,

  sp.version,

  (SELECT pn.fixed_version FROM package_notes AS pn

WHERE pn.bug_name = st.bug_name

  AND pn.package = sp.name

  AND(pn.release = sp.release OR (pn.release = '' AND fixed_version !=
''))),

  st.vulnerable,

  st.urgency,

  (SELECT range_remote FROM nvd_data WHERE cve_name = st.bug_name),

  (SELECT comment FROM package_notes_nodsa AS nd

WHERE nd.package = sp.name AND nd.release = sp.release

  AND nd.bug_name = st.bug_name) AS nodsa FROM

   source_package_status AS st,

   source_packages AS sp, bugs

WHERE

   sp.rowid = st.package

   AND st.bug_name = bugs.name

   AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )

   AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release =
'jessie'

  OR sp.release = 'wheezy' OR sp.release = 'squeeze' ) ORDER BY
sp.name, st.bug_name, sp.release, sp.subrelease;



--

D. Richard Hipp

  drh at sqlite.org

___

sqlite-users mailing list

 
sqlite-users at mailinglists.sqlite.org

 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined types -- in Andl

[da...@andl.org]

Yes, I had noticed those. Thank you.

Both these and the 'challenge' depend on the recursive CTE. As noted
elsewhere, I need to implement that before going any further with these.

If you have any other challenges I would still be interested.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Dominique
Devienne
Sent: Tuesday, 9 June 2015 9:57 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl

On Tue, Jun 9, 2015 at 1:50 PM,  wrote:

> Thank you. Exactly so. One of the problems with this kind of project 
> is finding 'good enough' challenges to tackle.
>

See also from the CTE doc:
- https://www.sqlite.org/lang_with.html#sudoku
- https://www.sqlite.org/lang_with.html#mandelbrot

Thanks, --DD
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined types -- in Andl

[da...@andl.org]

After some code review...

The main problem with this code is the use of integers to maintain bit flags
tracking cell usage. The advantage is that when the Sudoku search phase with
its associated backtracking has to make a copy of the game board, the amount
of data to copy is low. The disadvantage is that the code is unreadable.
Trying to manage a bit mask with 81 bits using 64 bit integers makes it at
least twice as bad.

But really the core of the algorithm is that it relies on 4 recursive CTEs.
These are to construct the cell layout bit masks and digits, one to process
the game rules, and one to do the backtracking search. They are crucial to
the query.

Currently Andl has recursive function calls but it does not have recursive
CTEs. A recursive CTE is something special because rather than being true
recursion it is actually a generator that populates a table with new rows
according to a query. It does this by means of a queue rather than a stack.
Andl could implement this algorithm using recursion, but it would be slow
and might well produce a stack overflow, and would not be a good outcome.

So the answer is: I plan to add 'recursive' queries to Andl. This may take a
little while.

Thanks for the challenge. If in the meantime you have any others that do not
use recursive CTEs I would be interested.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
david at andl.org
Sent: Tuesday, 9 June 2015 9:51 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] User-defined types -- in Andl

Thank you. Exactly so. One of the problems with this kind of project is
finding 'good enough' challenges to tackle.

I'll let you know how I get on.

[BTW I don't remember the last time I saw SQL like this. Understanding it
might be the challenge...]

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
Jean-Christophe Deschamps
Sent: Tuesday, 9 June 2015 5:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl

At 08:27 09/06/2015, you wrote:

>Andl is at a slightly higher level than SQL for writing simple queries.
>Where it shines is writing complex queries that involve user-defined 
>types, custom transformations and custom aggregations. For complex 
>relational operations there is nothing I know that can come close, 
>productivity wise.
`---

You call for complexity, you get it!

What I would find pretty convincing is seeing how andl would translate the
self-contained sudoku solver posted by E. Pasma in this post: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-Mar
ch/051982.html

Granted, this is far from the typical SQL you can find in routine use, but I
believe that andl being able to elegantly translate it would certainly
impress a number of readers and make many of us more interested in digging
further.

I'm not throwing such a mayhem challenge at you in the tone of "if andl
can't do that, then shut up". Maybe andl is not yet complete enough today to
achieve that and this wouldn't be a big issue. But if it can I'm sure andl
will attract more attention.

JcD 

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

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

[sqlite] User-defined types -- in Andl

[da...@andl.org]

Thank you. Exactly so. One of the problems with this kind of project is
finding 'good enough' challenges to tackle.

I'll let you know how I get on.

[BTW I don't remember the last time I saw SQL like this. Understanding it
might be the challenge...]

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
Jean-Christophe Deschamps
Sent: Tuesday, 9 June 2015 5:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl

At 08:27 09/06/2015, you wrote:

>Andl is at a slightly higher level than SQL for writing simple queries.
>Where it shines is writing complex queries that involve user-defined 
>types, custom transformations and custom aggregations. For complex 
>relational operations there is nothing I know that can come close, 
>productivity wise.
`---

You call for complexity, you get it!

What I would find pretty convincing is seeing how andl would translate the
self-contained sudoku solver posted by E. Pasma in this post: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-Mar
ch/051982.html

Granted, this is far from the typical SQL you can find in routine use, but I
believe that andl being able to elegantly translate it would certainly
impress a number of readers and make many of us more interested in digging
further.

I'm not throwing such a mayhem challenge at you in the tone of "if andl
can't do that, then shut up". Maybe andl is not yet complete enough today to
achieve that and this wouldn't be a big issue. But if it can I'm sure andl
will attract more attention.

JcD 

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

[sqlite] User-defined types -- in Andl

[da...@andl.org]

I think you'd be in a minority. I went through the first 50 questions about
SQLite on Stack Overflow and only one was C++. Android/Java are dominant,
with a smattering of C# and various other languages. Those are my target
users, eventually.

C/C++ is the drug of choice for low-level byte and bit twiddling like
implementing SQLite. I know: I've written many tens of thousands of lines of
the stuff but I can get more done faster in C#. The higher the language, the
faster you get results. Try coding your own joins, compared to just using
SQL.

Andl is at a slightly higher level than SQL for writing simple queries.
Where it shines is writing complex queries that involve user-defined types,
custom transformations and custom aggregations. For complex relational
operations there is nothing I know that can come close, productivity wise.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Nelson,
Erik - 2
Sent: Monday, 8 June 2015 11:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl

david at andl.org wrote on Monday, June 08, 2015 9:23 AM
> 
> Ultimately, I don't think it will really matter, because the role of 
> Andl is to be platform independent. Do you care what your SQL product 
> is written in?
> 
Absolutely.  I wouldn't be using SQLite if it wasn't C/C++, and I suspect
that I'm not the only one.  It wouldn't even make sense for me to spend time
looking at Andl, no matter how good it is.

Implementation technology is critical to anyone that embeds SQLite.  I'd
guess that the SQLite developers' choice to use C was not accidental.

Many people are perfectly productive using C/C++.

Erik

--
This message, and any attachments, is for the intended recipient(s) only,
may contain information that is privileged, confidential and/or proprietary
and subject to important terms and conditions available at
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined types -- in Andl

[da...@andl.org]

You need to see code to know if you're interested. But I take your point:
the grammar is here: http://www.andl.org/downloads/. 

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Eduardo
Morras
Sent: Tuesday, 9 June 2015 4:02 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] User-defined types -- in Andl

On Mon, 8 Jun 2015 15:28:11 +1000
 wrote:

> Thanks for pointing it out, but I knew that the best way to show off a 
> language is with examples. That's why there are nine sample Andl 
> scripts comprising dozens of individual examples in the Samples 
> folder. My guess is if that you're asking me to write examples, the 
> real lesson is that I didn't make them easy enough to find.
> 
> I have a formal grammar, but I don't expect anyone to read that. More 
> and better examples is the way to go.

No, a big bold No. If I want implement your language in some product I need
the formal grammar.

Learn by example means learn white rules (the dos), I need to know the black
rules too (the don'ts) to get full knowledge of the language.

> Regards
> David M Bennett FACS
> 
> Andl - A New Database Language - andl.org


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

[sqlite] User-defined types -- in Andl

[da...@andl.org]

I hope you do try it. I'm looking for feedback.

Sorry about the C#. Problem is, I'm way more productive in C# than any other 
language. C/C++ is just too slow to get things done and Java is still lagging. 
It would have taken far longer to do the SQLite C interface without .NET 
interop (JNI is seriously horrible). It was just the pragmatic choice.

Ultimately, I don't think it will really matter, because the role of Andl is to 
be platform independent. Do you care what your SQL product is written in? 

If Andl turns out to be a good direction, then porting the VM and libraries is 
a straightforward exercise. First I need to find out if it's a good idea!

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


On Sun, Jun 7, 2015 at 4:17 AM,  wrote:

> I've been reading this thread with great interest. It parallels the 
> project I've been working on: Andl.
>
> Andl is A New Database Language.
>
> Andl does what SQL does, but it is not SQL. Andl has been developed as 
> a fully featured database programming language following the 
> principles set out by Date and Darwen in The Third Manifesto. It 
> includes a full implementation of the Relational Model published by 
> E.F. Codd in 1970, an advanced extensible type system, database 
> updates and other SQL-like capabilities in a novel and highly expressive 
> syntax.
>
> The intended role of Andl is to be the implementation language for the 
> data model of an application. It is already possible to code the 
> business model of an application in an SQL dialect, but few people do 
> this because of limitations in SQL.  Andl aims to provide a language 
> free of these problems that works on all these platforms.
>
> The current implementation on SQLite uses a mixture of generated SQL 
> and a runtime VM. User-defined types are blobs, which the VM 
> understands. A future implementation could generate SQLite VM code 
> directly instead of SQL, which would save some overhead.
>
> The website is andl.org. The GitHub project is 
> https://github.com/davidandl/Andl. It's a work in progress. Any 
> feedback welcomed.
>

?Looks interesting. Too bad it's written in C#. I'm basically a Linux-only guy 
(use Windows at work under protest, so to speak). Yes, I can use Mono on Linux 
to compile C# and run it. And I may.?



>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
--
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

My sister opened a computer store in Hawaii. She sells C shells down by the 
seashore.
If someone tell you that nothing is impossible:
Ask him to dribble a football.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined types -- in Andl

[da...@andl.org]

There are nine sample scripts and dozens of examples in the Samples folder.
Obviously that's the area I need to work on.

Regards
David M Bennett FACS

MD Powerflex Corporation, creators of PFXplus
To contact us, please call +61-3-9548-9114 or go to
www.pfxcorp.com/contact.htm

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Monday, 8 June 2015 12:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl


On 8 Jun 2015, at 3:14am,   wrote:

> I suggest you just read the samples off GitHub. They cover the entire 
> language. Download the binary, run them and you see what they do.

Sorry but no.  You have it reversed.  Your code isn't going to touch my
computer unless you have already convinced me that it's worth me
investigating it.

Just describe a few examples on your site.  If you can show me "Look, it's a
whole page in SQL but only half a page in Andl." or some similar advantage
then so much the better.

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

[sqlite] User-defined types

[da...@andl.org]

Here is my best effort at translating this query into Andl.



(

source_packages [?(release =~ 

'^(sid|stretch|jessie|wheezy|squeeze)$' ) 

{ name, release, subrelease, version }] join 

source_package_status [?(bug_name =~ '^(CVE-|TEMP-)')

{ rowid:=package, bug_name, vulnerable, urgency }] join 

bugs [{ bug_name := name }] join 

nvd_data [{ bug_name:=cve_name, cve_desc, range_remote }] join 

debian_cve [{ bug, bug_name }] join 

package_notes [{ rls := release }] [?(rls = release or 

rls = '' and and fixed_version <> '')

{ name := package, bug_name, fixed_version }] join 

package_notes_nodsa [{ name := package, comment }] 

) [%(name, bug_name, release, sub_release)]



Please note:

1.   Andl uses only natural joins and renaming. Since I don't have the
schema there could be name clashes.

2.   Andl will remove any nulls or duplicates (pure relational model
only)

3.   Andl uses regex rather than LIKE.



The Andl code is somewhat shorter than SQL, and it's more regular. The real
benefit comes when this query (or parts of it) are reused in combination
with others, because Andl is composable. That doesn't show up when it's just
emulating an SQL query.



If this database is available I would appreciate the opportunity to try this
out for real.



Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
Hipp
Sent: Friday, 5 June 2015 7:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types



On 6/4/15, Darko Volaric <  lists at darko.org> 
wrote:

> My point about JSON, etc is that there is no reason not to use that as 

> a query language if that makes it easier. If your system is efficient 

> with JSON, why not accept a query that is formatted as JSON? It's not 

> semantically different to SQL syntax. Here's an example (with a 

> roughly JSON notation):

> 

> {

>   operation: "insert"

>   table: "blah"

>   columns: ["a", "b", "c"]

>   values: [1.3, 2.0, 3.1]

>   on-conflict: "replace"

> }

> 

> That is equivalent to an INSERT SQL statement, but why form that SQL 

> string, possibly using memory and time, when your system can spit out 

> JSON (or whatever) effortlessly?



What is the JSON equivalent to the query shown below?  Can you honestly say
that the JSON equivalent (whatever it looks like) is somehow easier to
generate, read, parse, and/or debug than the SQL?



SELECT

  sp.name, st.bug_name,

  (SELECT cve_desc FROM nvd_data WHERE cve_name = st.bug_name),

  (SELECT debian_cve.bug FROM debian_cve

WHERE debian_cve.bug_name = st.bug_name

ORDER BY debian_cve.bug),

  sp.release,

  sp.subrelease,

  sp.version,

  (SELECT pn.fixed_version FROM package_notes AS pn

WHERE pn.bug_name = st.bug_name

  AND pn.package = sp.name

  AND(pn.release = sp.release OR (pn.release = '' AND fixed_version !=
''))),

  st.vulnerable,

  st.urgency,

  (SELECT range_remote FROM nvd_data WHERE cve_name = st.bug_name),

  (SELECT comment FROM package_notes_nodsa AS nd

WHERE nd.package = sp.name AND nd.release = sp.release

  AND nd.bug_name = st.bug_name) AS nodsa FROM

   source_package_status AS st,

   source_packages AS sp, bugs

WHERE

   sp.rowid = st.package

   AND st.bug_name = bugs.name

   AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )

   AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release =
'jessie'

  OR sp.release = 'wheezy' OR sp.release = 'squeeze' ) ORDER BY
sp.name, st.bug_name, sp.release, sp.subrelease;



--

D. Richard Hipp

  drh at sqlite.org

___

sqlite-users mailing list

 
sqlite-users at mailinglists.sqlite.org

 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined types

[da...@andl.org]

Here is my best effort at translating this query into Andl.



(

source_packages [?(release =~ 

'^(sid|stretch|jessie|wheezy|squeeze)$' ) 

{ name, release, subrelease, version }] join 

source_package_status [?(bug_name =~ '^(CVE-|TEMP-)')

{ rowid:=package, bug_name, vulnerable, urgency }] join 

bugs [{ bug_name := name }] join 

nvd_data [{ bug_name:=cve_name, cve_desc, range_remote }] join 

debian_cve [{ bug, bug_name }] join 

package_notes [{ rls := release }] [?(rls = release or 

rls = '' and and fixed_version <> '')

{ name := package, bug_name, fixed_version }] join 

package_notes_nodsa [{ name := package, comment }] 

) [%(name, bug_name, release, sub_release)]



Please note:

1.   Andl uses only natural joins and renaming. Since I don't have the
schema there could be name clashes.

2.   Andl will remove any nulls or duplicates (pure relational model
only)

3.   Andl uses regex rather than LIKE.



The Andl code is somewhat shorter than SQL, and it's more regular. The real
benefit comes when this query (or parts of it) are reused in combination
with others, because Andl is composable. That doesn't show up when it's just
emulating an SQL query.



If this database is available I would appreciate the opportunity to try this
out for real.



Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
Hipp
Sent: Friday, 5 June 2015 7:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types



On 6/4/15, Darko Volaric <  lists at darko.org> 
wrote:

> My point about JSON, etc is that there is no reason not to use that as 

> a query language if that makes it easier. If your system is efficient 

> with JSON, why not accept a query that is formatted as JSON? It's not 

> semantically different to SQL syntax. Here's an example (with a 

> roughly JSON notation):

> 

> {

>   operation: "insert"

>   table: "blah"

>   columns: ["a", "b", "c"]

>   values: [1.3, 2.0, 3.1]

>   on-conflict: "replace"

> }

> 

> That is equivalent to an INSERT SQL statement, but why form that SQL 

> string, possibly using memory and time, when your system can spit out 

> JSON (or whatever) effortlessly?



What is the JSON equivalent to the query shown below?  Can you honestly say
that the JSON equivalent (whatever it looks like) is somehow easier to
generate, read, parse, and/or debug than the SQL?



SELECT

  sp.name, st.bug_name,

  (SELECT cve_desc FROM nvd_data WHERE cve_name = st.bug_name),

  (SELECT debian_cve.bug FROM debian_cve

WHERE debian_cve.bug_name = st.bug_name

ORDER BY debian_cve.bug),

  sp.release,

  sp.subrelease,

  sp.version,

  (SELECT pn.fixed_version FROM package_notes AS pn

WHERE pn.bug_name = st.bug_name

  AND pn.package = sp.name

  AND(pn.release = sp.release OR (pn.release = '' AND fixed_version !=
''))),

  st.vulnerable,

  st.urgency,

  (SELECT range_remote FROM nvd_data WHERE cve_name = st.bug_name),

  (SELECT comment FROM package_notes_nodsa AS nd

WHERE nd.package = sp.name AND nd.release = sp.release

  AND nd.bug_name = st.bug_name) AS nodsa FROM

   source_package_status AS st,

   source_packages AS sp, bugs

WHERE

   sp.rowid = st.package

   AND st.bug_name = bugs.name

   AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )

   AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release =
'jessie'

  OR sp.release = 'wheezy' OR sp.release = 'squeeze' ) ORDER BY
sp.name, st.bug_name, sp.release, sp.subrelease;



--

D. Richard Hipp

  drh at sqlite.org

___

sqlite-users mailing list

 
sqlite-users at mailinglists.sqlite.org

 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined types -- in Andl

[da...@andl.org]

Thanks for pointing it out, but I knew that the best way to show off a
language is with examples. That's why there are nine sample Andl scripts
comprising dozens of individual examples in the Samples folder. My guess is
if that you're asking me to write examples, the real lesson is that I didn't
make them easy enough to find.

I have a formal grammar, but I don't expect anyone to read that. More and
better examples is the way to go.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Monday, 8 June 2015 12:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl


On 8 Jun 2015, at 3:12am,   wrote:

> Is there a PDF? No, but that's a good idea. Did you check out the samples?
> They cover the entire language, and I could turn those into a PDF much 
> faster than a real language. It would take about a month to write a 
> decent tutorial and reference, but that might make a good shortcut.

My guess is that, if your objective is to attract readers, your time will be
best spent composing a few examples.  Formal grammar will be needed in the
long run but only the real geeks will read it.  Many people can read a few
examples and figure out whether it's worth investigating the language
further, whereas a formal grammar or a full tutorial would take more time to
read than they would be willing to invest.

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

[sqlite] User-defined types -- in Andl

[da...@andl.org]

I suggest you just read the samples off GitHub. They cover the entire
language. Download the binary, run them and you see what they do.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Monday, 8 June 2015 4:00 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl


On 7 Jun 2015, at 6:51pm, Scott Doctor  wrote:

> Do you have a PDF that explains the language?

There are plenty of blog entries which explain the language.  I spent more
time looking for some examples (I understand better from examples) and
eventually found one.

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

[sqlite] User-defined types -- in Andl

[da...@andl.org]

Thank you for your thoughts. You covered quite a few topics.

Is there a PDF? No, but that's a good idea. Did you check out the samples?
They cover the entire language, and I could turn those into a PDF much
faster than a real language. It would take about a month to write a decent
tutorial and reference, but that might make a good shortcut.

Why a new language? 

1. Because although I know over a hundred computer languages and dialects, I
don't know one that I could adapt to this job. The only possible candidates
are the functional languages (Haskell et al) and they come with too much
baggage (for some definition of baggage).

2. Because the aim is to do one thing and do it well. Andl is a small
language -- you can learn it all in a few hours. Don't be fooled -- it's
amazingly expressive.

3. Productivity. I have written over 100K lines of production C code and I
hope I never write another line -- it just takes too long to get stuff done.

4. Why not SQL? Because Andl does what SQL does, but better. It hides the
same things, but fixes the flaws and gaps.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
Doctor
Sent: Monday, 8 June 2015 3:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types -- in Andl


Do you have a PDF that explains the language?

My opinion is that I have seen many languages come and go. Consider general
programing languages. C is far superior to just about any language
available. In fact the underlying  code for most languages is written in C.
So the question becomes, why does everyone see a need to keep creating new
languages? Beyond the assembler instruction compiler for a new processor, a
C compiler is usually the first compiler written for that processor. It is
simple and straight forward to do, and I have done so many times. If you
look at the evolution of the C language (even considering that bastard child
C++) it has changed very little in 35 years, is available for all
architectures, properly written code will compile for any architecture
unmodified, and it has every hook needed to do any programming task.

Many decades ago, an attempt was made to make a more efficient keyboard to
replace the QWERTY keyboard. Some of the fledgling computer companies in the
1970's and 80's tried to get them accepted. From a straight technical
perspective, they are more efficient. They all failed to be accepted.

Consider what is easier. To train a new generation on the old stuff? or to
re-train half a dozen generations on the new stuff? Choices are usually made
on which is easier now versus the long term benefits later.

What I find interesting is how many of these "New" languages are so similar
to C. Java, PHP, and such, take the base constructs of C, then add "Special"
additions to do what is basically just a C function. They change some syntax
to make it more BASIC like, but the general technique still follows C. So
why not just use C?

In my current project, I debated just writing a bunch of C functions to
handle my data. Once I got into it, I realized that beyond simply
reading/writing some data structures, the code was getting complex very
fast. SQLite lets me offload the low level details and just write a query
with SQLite handling the parsing and search. Is it ideal, hardly. 
But the alternative is much more complicated and not worth the effort. 
If your data is just a few simple data structures, sure, just write some C
code. But the reality is that most well developed programs quickly branch
into ever increasing complexity.

Regarding SQL, many companies are attempting to replace SQL with their
flavor of an interface. Embarcadero (the old Borland) has in their
development system a "Universal" database interface to make accessing
databases "Universal". The idea being that a database designer just wants
their data and does not care about the underlying mechanisms. 
Wait, that is the entire concept behind every programming language. If
programmers cared about the underlying mechanism at every level and just
wanted to write the most optimal code possible (which is a far off concept
no longer desired for some reason) then all programs would be written in
assembler.  I used their system for a while. Now I just write the SQL
directly and just link in SQLite instead of using Embarcadero's stuff.
Although some of their constructs "seemed" to simplify some tasks, the
program as a whole was actually more complex.

My opinion why SQL has endured is that it actually hides from the programmer
the internal complexity required to implement a task. While some of the
syntax may be a bit quirky, so is talking to a teenager, but we adapt. If
SQL did not do what is needed then people would not use it. 
The reality is that SQL actually is a well thought out language, even if the
syntax can be a bit 

[sqlite] User-defined types -- in Andl

[da...@andl.org]

I've been reading this thread with great interest. It parallels the project
I've been working on: Andl.

Andl is A New Database Language.

Andl does what SQL does, but it is not SQL. Andl has been developed as a
fully featured database programming language following the principles set
out by Date and Darwen in The Third Manifesto. It includes a full
implementation of the Relational Model published by E.F. Codd in 1970, an
advanced extensible type system, database updates and other SQL-like
capabilities in a novel and highly expressive syntax.

The intended role of Andl is to be the implementation language for the data
model of an application. It is already possible to code the business model
of an application in an SQL dialect, but few people do this because of
limitations in SQL.  Andl aims to provide a language free of these problems
that works on all these platforms.

The current implementation on SQLite uses a mixture of generated SQL and a
runtime VM. User-defined types are blobs, which the VM understands. A future
implementation could generate SQLite VM code directly instead of SQL, which
would save some overhead. 

The website is andl.org. The GitHub project is
https://github.com/davidandl/Andl. It's a work in progress. Any feedback
welcomed.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Darko
Volaric
Sent: Thursday, 4 June 2015 8:55 AM
To: General Discussion of SQLite Database; ott at mirix.org
Subject: Re: [sqlite] User-defined types

I've tackled this problem from a couple of different angles. My goal was to
allow arbitrary user defined types, based on the builtin types (essentially
subtypes of the existing types), with a minimum of work and minimum
disruption of the normal/existing use of the database and API.

The approaches I considered were:

- encoding the user type codes for each data column in a separate column
dedicated to the purpose. This is a low impact but cumbersome, for instance
using a function that interprets the user type would have to have the user
type passed in for each argument, along with the actual data.

- modifying the data file format to carry user type information. There is
space in the record/row header where you can encode this information in a
backwards compatible way, but the source code for data record access is not
friendly, basically a dense blob of code with a lot of integer literals
which are all very important, but it's hard to be sure what they entail and
that you haven't introduced a subtle bug and ultimately data corruption.
Additionally the user type would have to be passed around internally - for
example in the sqlite3_value object - and tracking down all of those
reliably is a bit of work.

- using blobs. Although using text representation is friendly when looking
at the data with standard tools, it's slower and takes up more memory in
various places. I found that encoding some user types as blobs with a type
marker at their start (a single byte with extensions) and interpreting them
was a simple and low impact approach. I also split the standard integer type
four ways (negative and positive, odd and even) to get the scalar user types
I needed. User defined functions and collations need to be defined for
interpreting these user types of course.

The first option isn't very practical. The second option is the fastest and
most robust solution and my long term approach which I will be going back to
after development has progressed a bit more. Currently I'm using the third
approach as an interim measure. I'm supporting arbitrary prec ints and
reals, arrays and tuples and other types this way.



On Wed, May 27, 2015 at 3:48 AM, Matthias-Christian Ott 
wrote:

> I want to define user-defined types, i.e. types not SQLite has not 
> built-in and make sure that I didn't overlook something. Is it correct 
> that values of user-defined types should be stored as text and have a 
> collation defined if there is an order relation for the type if the 
> type cannot be represented as a subset of integer or float?
>
> Example:
> Suppose I want to store arbitrary precision integers in SQLite. I 
> would create a column with text affinity, (uniquely) serialize and 
> deserialize the integers to text (e.g. by converting them into decimal
> representation) and define and declare a collation that deserializes 
> the texts to arbitrary integers and compares the integers.
>
> Is there another way to define user-defined types despite this method 
> and virtual tables?
>
> - Matthias-Christian
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://ma

[sqlite] User-defined types -- with Andl

[da...@andl.org]

This is the challenge that I accept, with Andl.

SQL has been astonishingly successful, partly because of sound foundations
and partly because it's a monopoly. It's not a bad language, but on the
other hand it many ways it's not a language at all. Up until the 1992
version and including the SQLite dialect, there are many things that a
programming language should provide that it does not.

I have some specific criticisms of SQL, but I'm not here to bury it. I'd
just like to offer something better. That's the point of Andl.

I have reviewed the SQL 'challenge' you posted some little while back, and
there is no doubt that Andl can handle it, and (IMHO) the code is somewhat
shorter and somewhat cleaner than the SQL. I'll see what I can do to respond
to the challenge. I assume there is an SQLite database somewhere I can check
it on.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
Hipp
Sent: Friday, 5 June 2015 9:11 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types

On 6/4/15, Darko Volaric  wrote:
>
> What is motivating this for me is that I generate many unique queries 
> in my code for almost any operation. Converting those to SQL is error 
> prone and uses a lot of memory compared to the operation involved. The 
> database engine is so fast and efficient yet I'm wasting resources making
SQL!
>

You are welcomed to go off and try to come up with a new and better
interface.  That's the beauty of open-source.  Maybe you will come up with
some new and innovative ideas that will change the industry!
It's happened before!

I just want to ensure that if, after working on your new approach for a
while, you eventually decide that SQL isn't quite as bad a language as you
originally thought it was, that you don't come back and say I didn't warn
you.

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

[sqlite] Under what circumstances can a table be locked when the database is first opened?

[da...@andl.org]

Locking mode: Is this documented anywhere?

As I thought I said, it's a standalone program; run it to normal
termination; then run it again. Single connection, no flags enabled at open
(just the default).

Yes, I've been using Process Explorer for at least 10 years, since it was at
sysinternals. First place I looked and no, the database file is not locked.
If it was, I wouldn't have been able to delete it.

Regards
David M Bennett FACS

Andl - A New Data Language - andl.org
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Barry
Smith
Sent: Saturday, 23 May 2015 5:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Under what circumstances can a table be locked when
the database is first opened?

Hi,

Unless you are using shared cache, SQLite does not lock on a per table level
- only it locks the entire database.

Under what circumstances are you trying to access the database both times?
Are these multiple connections within the same process or are you shutting
down the process and then restarting? Do you have shared cached enabled?

Process Explorer is a windows tool that will tell you which process has open
handles on a particular file. I found it a right pita to use though.

Cheers,

Barry

> On 23 May 2015, at 1:42 pm, "Keith Medcalf"  wrote:
> 
> 
> 1)  Something else has the database open and locked.
> 2)  You are using Shared Cache
> 3)  Something forgot to finalize a select
> 4)  The database is stored on a non-locally-attached filesystem
> 5)  An issue in the version of SQLite you are using (and you did not 
> say which version you are using)
> 6)  A buggy filesystem driver (you did not say which one you are 
> using)
> 7)  Badly designed antivirus software
> 8)  Badly designed file syncronization software (for example, storing the
database in a directory that is being monitored and synced by badly designed
software (dropbox for example)).
> 
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org 
>> [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of 
>> david at andl.org
>> Sent: Friday, 22 May, 2015 21:13
>> To: 'General Discussion of SQLite Database'
>> Subject: [sqlite] Under what circumstances can a table be locked when 
>> the database is first opened?
>> 
>> Question: Under what circumstances can a table be locked when the 
>> database is first opened?
>> 
>> My program does:
>> 
>> DROP TABLE IF EXISTS
>> CREATE TABLE
>> INSERT INTO (multiple times)
>> SELECT * (for each row)
>> 
>> Run it once and it works perfectly. Run it twice and the DROP TABLE 
>> triggers the error:
>> 
>> SQLITE_LOCKED, database table is locked
>> 
>> Delete the database and run it again and it works. Just once.
>> 
>> Nothing in the documentation tells me how a table can be locked when 
>> the database is first opened. I'm using the raw C interface on 
>> Windows, so what can I be doing wrong?
>> 
>> The code is actually written in C#, but uses Interop to call the C 
>> API directly. The database open code looks like this. No open flags are
used.
>> 
>> LastResult = (Result)sqlite3_open(path, out _dbhandle);
>> 
>> Regards
>> David M Bennett FACS
>> 
>> Andl - A New Data Language - andl.org
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Under what circumstances can a table be locked when the database is first opened?

[da...@andl.org]

Thanks for the summary. Could be a useful addition to the docs.

1) single user.
2) I'm not 'using' anything. Just default open.
3) --> this could be it. How does this work exactly, and how do you avoid
it/correct it (after the event)?
4) N/A
5) Latest download.
6) Windows 8.1 NTFS.
7) None.
8) N/A.

Regards
David M Bennett FACS

Andl - A New Data Language - andl.org


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Keith
Medcalf
Sent: Saturday, 23 May 2015 1:42 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Under what circumstances can a table be locked when
the database is first opened?


1)  Something else has the database open and locked.
2)  You are using Shared Cache
3)  Something forgot to finalize a select
4)  The database is stored on a non-locally-attached filesystem
5)  An issue in the version of SQLite you are using (and you did not say
which version you are using)
6)  A buggy filesystem driver (you did not say which one you are using)
7)  Badly designed antivirus software
8)  Badly designed file syncronization software (for example, storing the
database in a directory that is being monitored and synced by badly designed
software (dropbox for example)).

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of 
> david at andl.org
> Sent: Friday, 22 May, 2015 21:13
> To: 'General Discussion of SQLite Database'
> Subject: [sqlite] Under what circumstances can a table be locked when 
> the database is first opened?
> 
> Question: Under what circumstances can a table be locked when the 
> database is first opened?
> 
> My program does:
> 
> DROP TABLE IF EXISTS
> CREATE TABLE
> INSERT INTO (multiple times)
> SELECT * (for each row)
> 
> Run it once and it works perfectly. Run it twice and the DROP TABLE 
> triggers the error:
> 
> SQLITE_LOCKED, database table is locked
> 
> Delete the database and run it again and it works. Just once.
> 
> Nothing in the documentation tells me how a table can be locked when 
> the database is first opened. I'm using the raw C interface on 
> Windows, so what can I be doing wrong?
> 
> The code is actually written in C#, but uses Interop to call the C API 
> directly. The database open code looks like this. No open flags are used.
> 
> LastResult = (Result)sqlite3_open(path, out _dbhandle);
> 
> Regards
> David M Bennett FACS
> 
> Andl - A New Data Language - andl.org
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] Under what circumstances can a table be locked when the database is first opened?

[da...@andl.org]

Question: Under what circumstances can a table be locked when the database
is first opened?

My program does:

DROP TABLE IF EXISTS
CREATE TABLE
INSERT INTO (multiple times)
SELECT * (for each row)

Run it once and it works perfectly. Run it twice and the DROP TABLE triggers
the error:

SQLITE_LOCKED, database table is locked

Delete the database and run it again and it works. Just once.

Nothing in the documentation tells me how a table can be locked when the
database is first opened. I'm using the raw C interface on Windows, so what
can I be doing wrong?

The code is actually written in C#, but uses Interop to call the C API
directly. The database open code looks like this. No open flags are used.

LastResult = (Result)sqlite3_open(path, out _dbhandle);

Regards
David M Bennett FACS

Andl - A New Data Language - andl.org