Re: [sqlite] User Defined Types implementations ...
On Thursday, 23 May, 2019 14:39, Jens Alfke wrote: >> On May 22, 2019, at 8:16 PM, Keith Medcalf >wrote: >> Basically, User Defined Types (UDT) were implemented in a fashion >analgous to a C++ class (remember that at this time C++ was just a >pre-processor for C and a C++ class was nothing more than a struct >and mangled function names to operate on the class). >…and a vtable to provide polymorphic dispatch of virtual methods, >which is required for OOP. >> Basically, when you declared something as a UDT you were giving a >"blob" a type-domain. Whenever you tried to do something with a UDT >a "mangled function name" was generated that took that blob as the >first argument >Was DB2 using early or late binding of types to blobs? That is, was >the type of a blob declared as part of the table column, or were >individual blob values tagged with their own types? This makes a huge >difference. In the former (early) case the UDTs are just syntactic >sugar, while with late-binding they really do act like objects. Late- >binding is also required if user-defined functions are to be allowed >to return UDTs. The initial version was entirely early binding via column declarations of the type, so they were syntactic sugar, not real polymorphic objects (in the sense of how real objects and types work). Now I believe you can implement UDT's as true objects (but still early bound via the column declaration) as C++ or Java or whatever classes. It was a long time ago and I haven't touched DB2 for a long time ... I only remember because at the time I was doing Level 2.5 support for the IBM CSet++ Compiler package at TOROLAB6 and we had lots of interaction with the DB2 developers who were just up the road >> you merely implemented a bunch of functions with the appropriate >names that DB2 would use whenever you referred to that UDT type, >rather than using the builtin functions (this is similar to the way >languages like Python implement classes). >Not really; in Python (and Ruby, and Objective-C, and Smalltalk, and >sort-of JavaScript…) a class is a dictionary that maps method names >to anonymous functions. The functions themselves don't have names. >(Sorry to be pedantic, but I'm a bit of a language geek.) No need, but you are correct. I believe that in fact the implementation that was released was based on proper dictionary based method dispatch and that all the other "standard internal types" ended up being implemented in the same fashion (just with standardized builtin models). That is, even a standard "varchar(20)" or "integer" or "decimal(10,4)" was nothing more than a binary blob in the database that had a builtin method dictionary attached via the declaration. The nice part was that the system implementation was extensible by being able to provide your own binding dictionaries, and that everything, whether an "internal" or UDT was implemented using the same methods. I don't know if the types were ever actually polymorphic or supported multiple inheritance or not. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User Defined Types implementations ...
> On May 22, 2019, at 8:16 PM, Keith Medcalf wrote: > > Basically, User Defined Types (UDT) were implemented in a fashion analgous to > a C++ class (remember that at this time C++ was just a pre-processor for C > and a C++ class was nothing more than a struct and mangled function names to > operate on the class). …and a vtable to provide polymorphic dispatch of virtual methods, which is required for OOP. > Basically, when you declared something as a UDT you were giving a "blob" a > type-domain. Whenever you tried to do something with a UDT a "mangled > function name" was generated that took that blob as the first argument Was DB2 using early or late binding of types to blobs? That is, was the type of a blob declared as part of the table column, or were individual blob values tagged with their own types? This makes a huge difference. In the former (early) case the UDTs are just syntactic sugar, while with late-binding they really do act like objects. Late-binding is also required if user-defined functions are to be allowed to return UDTs. > you merely implemented a bunch of functions with the appropriate names that > DB2 would use whenever you referred to that UDT type, rather than using the > builtin functions (this is similar to the way languages like Python implement > classes). Not really; in Python (and Ruby, and Objective-C, and Smalltalk, and sort-of JavaScript…) a class is a dictionary that maps method names to anonymous functions. The functions themselves don't have names. (Sorry to be pedantic, but I'm a bit of a language geek.) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User Defined Types implementations ...
On Wed, 22 May 2019 21:16:04 -0600 "Keith Medcalf" wrote: > Basically, when you declared something as a UDT you were giving a > "blob" a type-domain. Whenever you tried to do something with a UDT > a "mangled function name" was generated that took that blob as the > first argument and you merely implemented a bunch of functions with > the appropriate names that DB2 would use whenever you referred to > that UDT type, rather than using the builtin functions (this is > similar to the way languages like Python implement classes). I see. First, declare a type U. Then define a function, say, U max( U, U ) . Now, if we have a table T( U u ) we can SELECT max(u) from T to invoke our function instead of the built-in max(). What about operators, then, as long as we're talking C++? U operator<(U u) ? If you could define equivalence and less-than, you can join on, sort by, or get the maximium. In fact (speaking of Python), you get those for free if there's an implicit conversion to string. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User Defined Types implementations ...
On Wednesday, 22 May, 2019 16:56, James K. Lowden wrote: >On Wed, 22 May 2019 14:20:11 -0600 >"Keith Medcalf" wrote: >> (such as was added to DB2 back in the late 80's early 90's, and >> which I do not think anyone else has implemented as nicely anywhere >> else) >That's an interesting aside. It would make an interesting OT thread, >if you're inclined to start it. ;-) >I've always thought user-defined types were unnecessary except as a >convenience. There are few new primitive types; most user-defined >types I can think of are "structures" -- sets of columns -- that one >might like to name and constrain as a new type that may appear in >many tables. About the only primitive type I can imagine are >mathematical: complex numbers or exact numeric representations. Well, User Defined Types as they were added to DB2 were a rather interesting way of doing the addition. Remember these were added in the late 80's early 90's when DB2 was just the a simple non-extensible RDBMS and not the thing that it is now. Basically, User Defined Types (UDT) were implemented in a fashion analgous to a C++ class (remember that at this time C++ was just a pre-processor for C and a C++ class was nothing more than a struct and mangled function names to operate on the class). DB2 UDTs were implemented on top of blobs. Basically, when you declared something as a UDT you were giving a "blob" a type-domain. Whenever you tried to do something with a UDT a "mangled function name" was generated that took that blob as the first argument and you merely implemented a bunch of functions with the appropriate names that DB2 would use whenever you referred to that UDT type, rather than using the builtin functions (this is similar to the way languages like Python implement classes). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User-defined types -- in Andl
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] User-defined types -- in Andl
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
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] 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] User-defined types -- in Andl
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
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 < <mailto:lists at darko.org> 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
<mailto:drh at sqlite.org> drh at sqlite.org
___
sqlite-users mailing list
<mailto:sqlite-users at mailinglists.sqlite.org>
sqlite-users at mailinglists.sqlite.org
<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User-defined types -- in Andl
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
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
> On Jun 9, 2015, at 2:53 PM, Jean-Christophe Deschamps > wrote: > > Most probably! I can imagine that you don't encounter such style in common > business-like environments. Just for ?corporate' fun: analytic recursive common table expression - oh, my? with Clock( start_at, end_at, interval, tick ) as ( select date '2015-01-01' as start_at, timestamp '2015-01-01 23:59:59' as end_at, interval '15' minute as interval, date '2015-01-01' as tick fromdual union all select start_at, end_at, interval, tick + interval as tick fromClock where tick + interval between start_at and end_at ) selectClock.tick, lead( Clock.tick ) over( order by Clock.tick ) as next_tick from Clock order by Clock.tick;
[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] 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] User-defined types -- in Andl
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
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
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] 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] 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-March/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] User-defined types -- in Andl
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
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] User-defined types -- in Andl
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
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 < <mailto:lists at darko.org> 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
<mailto:drh at sqlite.org> drh at sqlite.org
___
sqlite-users mailing list
<mailto:sqlite-users at mailinglists.sqlite.org>
sqlite-users at mailinglists.sqlite.org
<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User-defined types
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 < <mailto:lists at darko.org> 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
<mailto:drh at sqlite.org> drh at sqlite.org
___
sqlite-users mailing list
<mailto:sqlite-users at mailinglists.sqlite.org>
sqlite-users at mailinglists.sqlite.org
<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User-defined types -- in Andl
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
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] User-defined types -- in Andl
On 8 Jun 2015, at 6:28am, 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 will admit that I didn't want to go find the depository before I was sufficiently interested in the language. So the only site I looked through was www.andl.com. If you make one blog entry for each example, then tag them all with the same tag, then have one of your navigation links show all posts with that tag, that should allow you to present the examples to people who are only getting as far as your blog. Simon.
[sqlite] User-defined types -- in Andl
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
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
[sqlite] User-defined types -- in Andl
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] User-defined types -- in Andl
Any properly written documentation on any subject always begins with an executive summary (no more than a few pages), an overview (usually a dozen more pages), then gets into the nitty gritty. Consider if I want you to write a paragraph in Egyptian Hieroglyphics. So I provide you with a few "sample" sentences already written in Egyptian Hieroglyphics. Would you be able to both understand my examples, and write a proper paragraph in Egyptian Hieroglyphics? Regarding a formal definition. That should be the first thing you write when creating something new. That is where the details start to show collisions, issues, problems. To the contrary, when I start using something new, I do not want to sift through 22k pages of text just to get the concept. Very few manuals are written well. You need to be able to explain the entire language in "A Few" pages. A summary of the hieroglyphics. (operators. This is what, a dozen or so symbols) A one or two sentence description of each key word. (e.g. JOIN, SELECT, INSERT,... especially anything new) A one or two sentence explanation for each key word (or symbol) how it relates to the equivalent SQL. Any documentation on any topic should be structured as such. The need for a formal definition is obvious, but is usually used in the same fashion as a dictionary (the printed on paper kind). A few people will read the entire book. But most will just turn to the entry of interest skipping everything else. The trick is being able to find that one word quickly and getting "All" the needed information in a concise deliberate fashion. - Scott Doctor scott at scottdoctor.com - On 6/7/2015 10:28 PM, david at andl.org 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.
[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] 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] User-defined types
On 2015-06-04 03:04, Darko Volaric wrote: > Regarding PgSQL, an advantage of encoding your own binary types is that you > can copy them straight into your code and execute with them directly - I > use the same encoding/data structures throughout and they serve my code and > requirements instead of the database's or its API. That will only work if the data serialization format of a software is the same its your in-memory representation. Most likely you will have to deal with endianness and related issues. It is also a more general problem of "zero-copy" serialization formats. Text has its advantages and disadvantages of course. For my purpose a text format with a regular grammar is an acceptable format in terms of storage efficiency and serialization and deserialization performance. - Matthias-Christian
[sqlite] User-defined types -- in Andl
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] User-defined types -- with Andl
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] 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] User-defined types -- in Andl
So we are supposed to learn this new language by osmosis? Scott Doctor scott at scottdoctor.com On 6/7/2015 11:00 AM, Simon Slavin wrote: > 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
l > 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-bounces at 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://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
On Thu, 4 Jun 2015 15:11:55 -0700 Darko Volaric wrote: > Are you seriously saying that that SQL syntax is friendly? How can you > defend SQL syntax other than on grounds of history or > standardization? The first and best defense of SQL is that it has at least some basis in the relational model. It expresses relational project, select, join, union, and intersection directly, and with contortions relational division. Like Algol-60, it's an improvement on its predecessors and on many of its successors. > If you're more comfortable and familiar with JSON > the yes it is easier and you can avoid an unnecessary conversion step. I wonder how many applications you've profiled for which SQL generation and parsing were a significant share of the run time. Usually once the data are of any appreciable size I/O becomes the most important component. That's why modern DBMSs have so many features to minimize I/O. While you're working on your new syntax, I hope you'll keep Richard's query in mind. SQL, verbose as it is, is pretty clean compared to most ORM syntaxes I've seen. A syntax that can express his query more succinctly and is also "better" along the lines you describe would be an achievement. --jkl
[sqlite] User-defined types
On Fri, 5 Jun 2015 13:07:59 -0400 Stephen Chrzanowski wrote: > If N-Tier software development is 'annoying' and you are not happy, > either get help by other members of your team, or, find a different > hobby, because anything less than 3-tier programming dealing with > multiple languages, technologies and functionality just isn't going > away. Quite honestly, and this is just my opinion, but I think it is > absolutely wrong of you to go into a place of employment angry or > annoyed at ANY level because of the multiple languages and > technologies used to bring your product to life. Get mad at the > politics, not the tools being used. Whew! Most days this list is as well behaved as a mature poodle, and then once in a while someone writes a rant that segfaults on the first paragraph. You're saying complexity here to stay, it's inevitable. Simon is saying it's unnecesary (ergo annoying). The more you know about inherent and accidental complexity, the lower your threshold for being annoyed by the latter. There's no reason your 6-tier application couldn't be written in a single language. You could have one syntax for data structures, one representation (and semantics) for missing data, one binary representation for each type. You could throw an exception at tier-0 couldn't be caught in tier-5. The jumble of technologies we use is very much a happenstance accident of industrial history. No one designed it that way. Why else would we have no less than two completely different abstractions of the machine -- the OS and the browser -- connected by nothing more sophisticated than a stream of bytes? > Second... Come on... Really? This "switching...requires a lot of > effort" comment is a pretty weak sauce excuse coming from any > developer It's not an excuse, "sauce" (whatever that means) or otherwise. It's a fact. It's called "cognitive load" and it's been recognized since the dawn of software. --jkl
[sqlite] User-defined types
On 5 Jun 2015, at 3:05pm, Don V Nielsen wrote: > I do all kinds of --stuff-- > using Ruby and PHP. And the --stuff-- gets translated to SQL and sent to > my favorite db, Sqlite. I find it very annoying that in order to do good Web-facing systems I have to know all the following: HTML (and CSS if you consider that separate) JavaScript HTML5 APIs for sound/movies/drag&drop/forms/whatever PHP SQLite3 API SQL That's six sets of knowledge to do one thing. Granted, they do different things in different ways but it's still rather a lot to fit into m noggin. And it's annoying when you think you're debugging a JavaScript problem but it turns out that a SQL command doesn't mean what you thought it did: switching my brain between JavaScripting and SQLing seems to require a lot of effort. Simon.
[sqlite] User-defined types
Yes, the relational model is the key, that is my point. The SQL language is an entirely arbitrary syntax applied to it. You don't need it to work a relational database, just like you don't have to program in C to write a program for a typical processor. I don't care about how many applications have a performance problem with SQL generation. *My* application has a problem with it. I also don't care about Richard's query. *My* queries are much easier to form without contorting them into a SQL query. This is the entire point of my changes: avoid arbitrary bottlenecks and conventions that get in the way of performance and ease of use. It's not useful for your average person or typical application, but it's extremely useful for people who want to get the benefit of the database engine encapsulated within it. On Fri, Jun 5, 2015 at 2:10 PM, James K. Lowden wrote: > On Thu, 4 Jun 2015 15:11:55 -0700 > Darko Volaric wrote: > > > Are you seriously saying that that SQL syntax is friendly? How can you > > defend SQL syntax other than on grounds of history or > > standardization? > > The first and best defense of SQL is that it has at least some > basis in the relational model. It expresses relational project, > select, join, union, and intersection directly, and with contortions > relational division. Like Algol-60, it's an improvement on its > predecessors and on many of its successors. > > > If you're more comfortable and familiar with JSON > > the yes it is easier and you can avoid an unnecessary conversion step. > > I wonder how many applications you've profiled for which SQL generation > and parsing were a significant share of the run time. Usually once the > data are of any appreciable size I/O becomes the most important > component. That's why modern DBMSs have so many features to minimize > I/O. > > While you're working on your new syntax, I hope you'll keep Richard's > query in mind. SQL, verbose as it is, is pretty clean compared to > most ORM syntaxes I've seen. A syntax that can express his query more > succinctly and is also "better" along the lines you describe would be > an achievement. > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] User-defined types
On Fri, Jun 5, 2015 at 11:07 AM, Stephen Chrzanowski
wrote:
> First, who said that you had to keep all 6 sets of languages in your head
> at once? I've never been told that, and I've been doing software
> development since I was 8, taken several training courses in elementary,
> high school, college, and while employed by three different companies (At
> different times). I don't know VB6 all that well today, but, if I could
> find my CDs and install I'm sure I'd be able to figure out sprintf doesn't
> work. I've got a lot of junk in my head going back to Vic-20 Assembly, to
> V2 Basic, to VB3->6, to Borland Pascal 3 to Delphi 2010, to PHP, to bash,
> to freak'n nearly anything else dealing with web and internet
> technologies. It ain't all going to fit up in my nogin, but sure know
> where to get the info when I need it. php.net is my friend when I need to
> look up how strpos, substr and str_replace work several times an hour.
>
> Second... Come on... Really? This "switching...requires a lot of effort"
> comment is a pretty weak sauce excuse coming from any developer and is
>
{snipped}
This is a lot of commentary for an informal off the cuff remark. :) I mean,
I agree with what you're saying, but I also agree with Simon. Here's why:
I also have lots of mental state cruft that has accumulated in my noggin
over the past 30+ years, going back to a Commodore Pet at my school. Back
in the day, you could comfortably hold the entire state of the system in
your head, your program was a single tasking single user beast that took
over the entire computer, a team of one could easily write many types of
software, you used one language for the entire project, and data stores
were so small, and memory so constrained that you didn't use mega-flexible
systems like those based on SQL (whether embedded like SQLite or not). Of
course, there was no SQLite to use, or even SQL...
Today one person can't hope to keep the entire state of a system, has to
play well with others (as in your program won't have exclusive use of the
hardware in 99.999% of cases *and* must often be able to work in a team
environment where tasks are delegated so that things can be done in a
reasonable amount of time), must work with a variety of technologies &
languages that don't always play nice together (which complicates
debugging), and etc.
Task switching involves overhead, whether it is in a computer or in our
brains.
Sometimes I miss the simplicity of programming my Commodore 8-bit
computers. The rest of the time I love the world we live in where I have
far more computing power in my shirt pocket than I could have ever imagined
back in the day.
SDR
[sqlite] User-defined types
First, who said that you had to keep all 6 sets of languages in your head at once? I've never been told that, and I've been doing software development since I was 8, taken several training courses in elementary, high school, college, and while employed by three different companies (At different times). I don't know VB6 all that well today, but, if I could find my CDs and install I'm sure I'd be able to figure out sprintf doesn't work. I've got a lot of junk in my head going back to Vic-20 Assembly, to V2 Basic, to VB3->6, to Borland Pascal 3 to Delphi 2010, to PHP, to bash, to freak'n nearly anything else dealing with web and internet technologies. It ain't all going to fit up in my nogin, but sure know where to get the info when I need it. php.net is my friend when I need to look up how strpos, substr and str_replace work several times an hour. Second... Come on... Really? This "switching...requires a lot of effort" comment is a pretty weak sauce excuse coming from any developer and is borderline grasping at straws to try and make a statement, ESPECIALLY when it comes to COMPLETELY different technologies that have EXACTLY NOTHING to do with each other as individual components. Seriously, that is like saying you're having a hard time switching between using a ball-peen hammer and a chain saw, and not quite understanding where the screwdriver comes into play. They are ENTIRELY different technologies and tools. .. Ok... So all three devices can open a paint can, but, only one is going to keep the mess to a minimum, and it ain't the chainsaw. (Trust me... .. Tried it, and had fun, but hated the clean up) N-Tier programming is a freak'n (Awesomely wonderful, and sometimes completely evil) beast when it comes to decent development, and that is EXPECTED even at the most BASIC database driven web site, and I'm just talking a basic login page, which is at minimum a 3-tier application. (UI to get the creds from the user, logic to validate the form, access to a data source, be it flat file, LDAP or SQL database, then logic to check to see if what was provided by the UI is correct, then tell the UI to display something.) When I get into my "deep code runs" in Win32, I can easily escape into a wonderful world by writing a 6-tier application. UI, Authentication, Business Logic, Data Storage, error trapping and handling, and finally logging to see where the other 5 tiers screw up. Every tier 100% independent of the other, has the ability to talk to any tier as needed. If I want the UI to talk to the DB directly, so be it. I don't HAVE to know how all 6 levels work. I didn't write all of them. I SUCK at LDAP authentication. I'll NEVER retain how the 6 domain trees we have at work are named and structured, and I don't CARE to know. I just need to provide a way for a user/admin to do that config, and beg or bribe someone to give me a connection string that works so that I can do my coding. Thank goodness the coffee the company provides tastes like mud, because I love driving, and enjoy my Tim Hortons coffee, and I can usually bribe my way out of issues by buying coffee. That said, where it gets annoying is NOT the technology that is being used or to swap brains frequencies, but when one of the technologies isn't doing what I expect. I know the tools and how they work. Its when I pull the rope 30 times on the chainsaw and it not starting, only to realize I forgot to set the choke, prime the fuel, or pull the throttle while pulling. THAT gets annoying. Its also annoying when spending hours or days trying to figure out why a UI component isn't getting the proper information from the SQL server, only to find out I had typed the URI for the LDAP server wrong and the logic code was asking the database for information in the wrong table or database. Valid connection, just wrong point of interest. If you're going to work on something that looks fancy for the web, then yeah, you're going to need to deal with HTML and CSS. No way around it. If you're going to make a rock solid database, then of course, you're going to have to have a firm understanding of how a particular database language works to get the job done. Again, unavoidable. And if you want to just skip the whole understanding the high level HTML/CSS language and the low-level SQL calls, you can easily get into the middle of things and get into a framework type of scenario where you can deal with what the framework does best for you and not worry about HTML/CSS/SQL at all, except that they exist and function somehow, someway, and you have interfaces to each level of technology. Then you only need to be aware of how the framework works, and not the database language or the HTML markup. If N-Tier software development is 'annoying' and you are not happy, either get help by other members of your team, or, find a different hobby, because anything less than 3-tier programming dealing with multiple languages, technologies and functionality just i
[sqlite] User-defined types
> How can you defend SQL syntax other than on grounds of history or standardization? Short answer: QWERTY. Long answer: IBM mainframe DOS -> Z/OS. A 1960's o/s that is still supported by the inner workings of its most modern o/s. There's is nothing wrong with supporting the past. Sometimes things we don't like have long histories that we may not like. But like it, or not, those histories created a standard and got us to where we are today. Why forget the past? We can enhance and embrace at the same time. I do all kinds of --stuff-- using Ruby and PHP. And the --stuff-- gets translated to SQL and sent to my favorite db, Sqlite. Why? Because SQL works, and so much understands it. It has a legacy and an understanding and it is documented and it is well vetted and and and and. My 1 cent. On Fri, Jun 5, 2015 at 8:48 AM, Etienne Charland wrote: > What you're looking for seems similar to LINQ to SQLite > (System.Data.SQLite). When programming in C#, I don't code any SQL. I use a > strongly-typed interface that then generates SQL queries in the background. > > Besides LINQ, you could create another interface that suits your needs, > and that can then communicate with any database since all databases > recognize SQL. Nothing needs to change on SQLite's side. > > My 2 cents. > > > Etienne > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] User-defined types
What you're looking for seems similar to LINQ to SQLite (System.Data.SQLite). When programming in C#, I don't code any SQL. I use a strongly-typed interface that then generates SQL queries in the background. Besides LINQ, you could create another interface that suits your needs, and that can then communicate with any database since all databases recognize SQL. Nothing needs to change on SQLite's side. My 2 cents. Etienne
[sqlite] User-defined types
There's a bit of confusion as to what I'm actually proposing. I can't reply to everyone so I'll just post the APIs and/or patches when they're done and we can argue those on their merits. On Thu, Jun 4, 2015 at 5:03 PM, Darko Volaric wrote: > Well, I've been using SQL for about 30 years so I'm unlikely to change my > view, but I think you bring up a much more important point: instead of > arguing online I should get back to work! > > > On Thu, Jun 4, 2015 at 4:11 PM, Richard Hipp wrote: > >> 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] User-defined types
On 2015-06-05 12:11 AM, Darko Volaric wrote: > Are you seriously saying that that SQL syntax is friendly? How can you > defend SQL syntax other than on grounds of history or standardization? If > you're more comfortable and familiar with JSON the yes it is easier and you > can avoid an unnecessary conversion step. And I now regret using JSON as an example since everyone wants me to convert SQL to JSON for them now, but my point isn't any particular notation, I want an API of sorts instead of a notation or syntax. Then you can adapt anything you like and make it efficient with the platform you're using. So for example you send a native, binary JavaScript object (or record, whatever its called) as your query instead of SQL text. 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! I just want to skip that SQL bottleneck, because it has no technical justification other than "standardization" and pass my query straight through. The defense of SQL is much like the Defense of the English language. It isn't the best language out there, it is full of idiosyncrasies and vestigial bits of long dead habits. It just happens to be the language spoken by most people whom you wish to talk with. SQL is even better than that - not only does it present a language understood by all serious database engines, it is understood by people and even reads like English. Some of it can be better I suppose, but that is down to preference. I think you underestimate exactly how useful and efficient SQL is. It can produce infinitely many and widely differing, yet algebraically correct, data compilations from a very limited vocabulary. We are not stubbornly clinging to the old and deprecated - we actually think the way it works is quite neat. That said, I get your point. You want to send the Query parameters in an object or binary format because it's more like programming or OO than SQL is, and safer. The premise is wrong though. There is nothing intrinsically safer or more efficient about compiling and sending bits from an object than bits of text. The parser will parse it either way and produce an outcome. If you think that you can suggest to the parser sufficiently via a binary object to avoid a lot of parsing, then I have great news for you - you can already do that by simply adjusting the properties of the *stmnt object returned by sqlite3_prepareV2() in glorious bit-byte detail. An attempt to do so will quickly prove that making the SQL and letting the parser do its bit is by far the preferred method. The second premise is also wrong - there is nothing about the properties of an object that make them less prone to bugs/inefficiencies or human error than an SQL string. (JAVA objects do not even have proper templates, they are especially prone to error). Also, the lifetime of any SQL-text bug is limited to the first time the query is run. The only other thing I can think of might be that you do not test queries before implementing them or they are created on the fly by possibly a user process. In the latter case, any bugs there would equally likely be propagated through an object or binary representation of sorts. To be clear of what I intended to say: You might prove to have something useful still, and I'd be interested to see how it may work, but please note that if SQL is a bottleneck, it's an extremely low-cost unobtrusive one, and its only justification is NOT merely "standardization", not more than the use of Liters as a measure of volume is merely tolerated because it is standardized - it might not be particularly fantastic, but happens to be no worse a measure of volume than any other, and until the converse can be shown, it is likely to remain the standard.
[sqlite] User-defined types
On 5 Jun 2015, at 12:11am, Richard Hipp wrote: > 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. I'm on my third attempt now, I think. I've had three tries at designing a better NFS or a better Unix file-handling library and by the time I've got down to the nitty-gritty of configuration methods and error-handling the only result is a renewed respect for the elegance and efficiency of the original. Simon.
[sqlite] User-defined types
On 2015-06-04 11:16 PM, Darko Volaric 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"
> }
It's an interesting idea and I for one am willing to entertain the
thought, but I'm having difficulty seeing the "simpler" and "easier"
things you claim, or the memory saving for that matter.
Just take the above JSON query and consider that in SQL that would
simply look like:
REPLACE INTO blah (a,b,c) VALUES (1.3, 2.0, 3.1);
If we have to open a pole on which version seems simpler or use less
memory, the result would probably be indecisive if not plainly favouring
the latter.
I am willing to learn though, for instance, how do you see this next
query represented in the JSON way?:
INSERT INTO blah (1,b,c) VALUES
(1.1, 2.2, 3.3),
(3.1, 3.2, 3.4),
(5.1, 4.2, 3.5),
(7.1, 5.2, 3.6);
Or maybe this one:
SELECT MAX(A.Code), MAX(A.Name), B.Age, MAX(B.LastEditedDate) AS LastDT
FROM CodeNames AS A
LEFT JOIN Codehist AS B ON A.Code = B.Code AND B.Age > 30
WHERE A.Name LIKE 'SomeVal%'
GROUP BY B.Age
ORDER BY LastDT DESC
LIMIT 50;
(I'll forgo the "Having" clause for simplicity).
I'm finding it difficult to imagine a better layout for that query in a
JSON (or any other Markup-based) document - but I am quite willing (and
even interested) to be shown a way that makes more sense and satisfies
the claims of simplicity and memory efficiency.
Once a layout is found that works, I imagine it would be a whole other
can of spaghetti to make any program author the syntax sensibly, but
that is a worthy bridge to cross once the first question is answered well.
Alternatively, you might be able to show how the other notation might
ease the query-planner's work, or how it might help any other SQL
process work better or faster. Some significant improvement in
functionality or efficiency will make a much stronger case than "It's
easier to compose".
Ryan
[sqlite] User-defined types
On 4 Jun 2015, at 11:11pm, Darko Volaric wrote: > Are you seriously saying that that SQL syntax is friendly? How can you > defend SQL syntax other than on grounds of history or standardization? If > you're more comfortable and familiar with JSON the yes it is easier and you > can avoid an unnecessary conversion step. JSON is not a query language. You can devise a query language using JSON components (the one you showed was a SQL rip-off but would work for simple cases). Once you've done that you have to deal with possibilities for ambiguity and the difficulty of describing how to handle errors. And once you've done that you still have to prove it's competitive with the existing query language using text called SQL, which millions of people already know. Simon.
[sqlite] User-defined types
On 4 Jun 2015, at 10:16pm, Darko Volaric wrote:
> 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?
Why invent a new nonstandard notation for database operations when you have SQL
?
Given your JSON expression above it's easy to write code which turns the JSON
into a SQL command. So just do that (either outside SQLite or by creating a
loadable external function for SQLite) and then you can use SQLite exactly as
it is without having to keep modifying your project every time the developer
releases a bug-fix.
The hard work in creating a fork is not in the initial work but in the
maintenance every time the main project gets updated.
Simon.
[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] User-defined types
On 2015-06-05 12:11 AM, Darko Volaric wrote: >I now regret using JSON as an example since everyone wants me to >convert SQL to JSON for them now, but my point isn't any particular >notation, I want an API of sorts instead of a notation or syntax. Then >you can adapt anything you like and make it efficient with the platform >you're using. So for example you send a native, binary JavaScript >object (or record, whatever its called) as your query instead of SQL text. >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! >I just want to skip that SQL bottleneck, because it has no >technical justification other than "standardization" and pass my query >straight through. I think I fail to understand. Programmers write programs composed of code which operates upon an externality (data). Therefore, you have to generate the "program" which operates on the data by storing and retrieving it. Part of that programming task is writing the code to store the data in a file. Sometimes it may be apropos to use simple sequential I/O to text files, and sometimes you might read and write from a database. In either case it is you who are writing the code which performs the operation -- it does not write itself. So, how is it simpler to for you to generate JSON data which is executed as code to manipulate data as compared to writing SQL which operates on the data? You still have to write (program) the data manipulation task, no matter what language you write it in. Of course, it sounds like you may be referring to data which magically knows what to do with itself -- which is rather a bit of crusty old snake oil called Object Oriented Programming with an Object database. However, even in this case the programmer must still write the code which enables the data to "do" what it is asked.
[sqlite] User-defined types
If you really want your own types, you could always bundle with ASN.1 and store the result as a blob. On Thu, Jun 4, 2015 at 4:52 PM, Dominique Devienne wrote: > On Thu, Jun 4, 2015 at 3:04 AM, Darko Volaric wrote: > > > In my case I'm already modifying and maintaining my own version of > SQLite. > > [...]. The last time I brought these ideas up I was > > practically chased off by a mob waving pitchforks and torches. Apparently > > almost no-one thinks user defined types is a good idea so there is no > point > > sharing it. I don't expect anyone to help me maintain the code. > > > FWIW, I think UDTs are a great idea. But also > - optional static typing of columns; > - checksums of blocks;- > - blob two-tier storage (a la Oracle); > - native indexing of virtual table; > - native JSON support; > - etc... > > Yes, the community, just like the authors, of SQLite have a strong bias > against changes and to keep SQLite "lite". > > And can be brutal in how they say it (or ignore it) when someone rants > about his pet-peeves, or try to push forward his wish list (including me > above). > > But remember that SQLite didn't have FKs for a long time. Didn't have CTE. > Both of which are major enhancements. So there's hope long term IMHO :). > > Now unlike most (including me again), you go further and actually code it > up apparently. That's great. But it's hard to fork SQLite and get any > traction given the fast-paced refactoring/optimization the main code goes > through. And also UDTs can have widespread side effects within SQLite, hard > to gauge w/o having the whole code-base and design in ones head like DRH. > Might be good enough for you, but not for the high quality standards which > is a hallmark of SQLite IMHO. All I can suggest is continue communicating > and perhaps also OSS your changes on GitHub or similar, and you may get > help somehow. > > I suspect (hope really) first-class UDTs in SQLite (as Nico calls them) > haven't been dismissed, and it's more a question of finding the time and > funding to do them right, i.e. in a "lite" way that doesn't adversely > affect SQLite if you don't use them, and thoroughly tested as usual. My > $0.02. --DD > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Christopher Vance
[sqlite] User-defined types
On Thu, Jun 04, 2015 at 03:36:34PM -0700, Darko Volaric wrote: > I now regret using JSON as an example since everyone wants me to convert > [...] So you don't like the SQL language, but if you're after UDTs and your first stop is to design a different language (or merely make it easier for you to add such a language later) then your priorities are kinda wrong (I'm being generous). The way you're approaching an alternative front-end won't make any aspect of adding UDTs any easier; at best it's a get-to-know-the-core project.
[sqlite] User-defined types
On Thu, Jun 4, 2015 at 4:21 PM, Darko Volaric wrote:
> I'm saying that SQL is alien to the platform it's being used on and native
> is better. I'm trying to make a general point (in vain it seems), I don't
> use JSON.
>
{bunch of stuff snipped}
I understand where you're coming from, I think. I have many times wanted a
more procedural interface to SQLite. The common flow:
1. Construct a string from native data structures.
2. Compile (prepare) the string into SQLite data structures.
Seems inefficient at first blush. Instinct (not logic) tells us that things
would be more efficient if we could just build the data structures
ourselves and not have to go through the preparation phase each time we run
the program. The problem with this is that the SQL preparation phase hides
roughly a metric ton of implementation details that can and do change
frequently. Exposing the "raw" interface would likely result in one of two
outcomes: either the raw interface would be very brittle requiring far more
changes to user code with each update of the library, or the library would
be forced to stagnate so as to not break user code with each update.
Exposing so much functionality through the prepare API creates quite an
elegant OO style interface. Implementation details can and do change
frequently without breaking consumers of the library.
Still, I do get your point. A different interface is attractive for
multiple reasons. I have a different reason why I'd like a slightly
different interface. I dislike the fact that my compiler is able to tell me
about syntax issues in my C++ code but I don't know about syntax errors in
my SQL code until I actually run the program. I've worked off and on in the
past on a C++ interface that feels quite a bit like SQL but allows the
compiler to report some classes of errors. It still results in a
SQL-in-a-string that must be prepared, but I have a slightly higher level
of confidence that the code is "correct" (instead of the all too often
occurrence of missing whitespace because of string concatenation or some
such). I've just never had a chance to finish it.
SDR
[sqlite] User-defined types
On 6/4/15, Darko Volaric 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] User-defined types
Well, I've been using SQL for about 30 years so I'm unlikely to change my view, but I think you bring up a much more important point: instead of arguing online I should get back to work! On Thu, Jun 4, 2015 at 4:11 PM, Richard Hipp wrote: > 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] User-defined types
Just write your program in C. Use the C syntax to do whatever you want and you have full control over the minutiae. Scott Doctor scott at scottdoctor.com -- > 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! >> >
[sqlite] User-defined types
On Thu, Jun 04, 2015 at 02:16:22PM -0700, Darko Volaric wrote:
> {
> operation: "insert"
> table: "blah"
> columns: ["a", "b", "c"]
> values: [1.3, 2.0, 3.1]
> on-conflict: "replace"
> }
I do this all the time. It's trivial enough to generate SQL from that
sort of thing. If you have an AST then you can trivially map the
AST<->a JSON/XML/ASN.1/whatever schema.
But I don't think ease of alternative representation is the winning
argument for wanting the engine core to use an AST. It's only
convenient.
The winning argument is that working with an AST makes some tasks easy
that are otherwise hard (e.g., common sub-expression elimination).
> [...] Why are people who come from the websphere
> learning SQL syntax? [...]
Because it's standard.
> The feature I'm working on now, as a first step, basically feeds the parser
> tokens so I don't have to generate a query string. [...]
That seems rather basic, not really good enough. It must save some
allocations. But is it worth forking SQLite3 for this?! Whatever you
do with a fork, it's got to be worth it. Forking is quite hard, so
every change has got to be worth the effort.
Switching to an AST is going to require more allocations (and much more
developer effort), that's for sure. But then, this is in statement
compilation, which should not be the most critical component.
Anyways, this is all very far afield from UDTs. If you want to fork to
add UDTs, focus on that first.
Nico
--
[sqlite] User-defined types
I now regret using JSON as an example since everyone wants me to convert
SQL to JSON for them now, but my point isn't any particular notation, I
want an API of sorts instead of a notation or syntax. Then you can adapt
anything you like and make it efficient with the platform you're using. So
for example you send a native, binary JavaScript object (or record,
whatever its called) as your query instead of SQL text.
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!
I just want to skip that SQL bottleneck, because it has no technical
justification other than "standardization" and pass my query straight
through.
On Thu, Jun 4, 2015 at 3:13 PM, R.Smith wrote:
>
>
> On 2015-06-04 11:16 PM, Darko Volaric 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"
>> }
>>
>
> It's an interesting idea and I for one am willing to entertain the
> thought, but I'm having difficulty seeing the "simpler" and "easier" things
> you claim, or the memory saving for that matter.
>
> Just take the above JSON query and consider that in SQL that would simply
> look like:
>
> REPLACE INTO blah (a,b,c) VALUES (1.3, 2.0, 3.1);
>
> If we have to open a pole on which version seems simpler or use less
> memory, the result would probably be indecisive if not plainly favouring
> the latter.
>
> I am willing to learn though, for instance, how do you see this next query
> represented in the JSON way?:
>
> INSERT INTO blah (1,b,c) VALUES
> (1.1, 2.2, 3.3),
> (3.1, 3.2, 3.4),
> (5.1, 4.2, 3.5),
> (7.1, 5.2, 3.6);
>
>
> Or maybe this one:
>
> SELECT MAX(A.Code), MAX(A.Name), B.Age, MAX(B.LastEditedDate) AS LastDT
> FROM CodeNames AS A
> LEFT JOIN Codehist AS B ON A.Code = B.Code AND B.Age > 30
> WHERE A.Name LIKE 'SomeVal%'
> GROUP BY B.Age
> ORDER BY LastDT DESC
> LIMIT 50;
>
> (I'll forgo the "Having" clause for simplicity).
>
> I'm finding it difficult to imagine a better layout for that query in a
> JSON (or any other Markup-based) document - but I am quite willing (and
> even interested) to be shown a way that makes more sense and satisfies the
> claims of simplicity and memory efficiency.
>
> Once a layout is found that works, I imagine it would be a whole other can
> of spaghetti to make any program author the syntax sensibly, but that is a
> worthy bridge to cross once the first question is answered well.
>
> Alternatively, you might be able to show how the other notation might ease
> the query-planner's work, or how it might help any other SQL process work
> better or faster. Some significant improvement in functionality or
> efficiency will make a much stronger case than "It's easier to compose".
>
> Ryan
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] User-defined types
I'm saying that SQL is alien to the platform it's being used on and native
is better. I'm trying to make a general point (in vain it seems), I don't
use JSON.
On Thu, Jun 4, 2015 at 2:46 PM, Simon Slavin wrote:
>
> On 4 Jun 2015, at 10:16pm, Darko Volaric wrote:
>
> > 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?
>
> Why invent a new nonstandard notation for database operations when you
> have SQL ?
>
> Given your JSON expression above it's easy to write code which turns the
> JSON into a SQL command. So just do that (either outside SQLite or by
> creating a loadable external function for SQLite) and then you can use
> SQLite exactly as it is without having to keep modifying your project every
> time the developer releases a bug-fix.
>
> The hard work in creating a fork is not in the initial work but in the
> maintenance every time the main project gets updated.
>
> 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
Yes, you can do that but I'm trying to remove steps and conversions, not
add more. My point is that a more native interface is better than SQL.
Yes it's basic, but as I said, a first step. I'm making changes to one
part, making changes to another part makes no difference. The fork has been
forked, the die is cast.
Actually, I'm focusing on my own work which require all these things one
way or another.
On Thu, Jun 4, 2015 at 2:30 PM, Nico Williams wrote:
> On Thu, Jun 04, 2015 at 02:16:22PM -0700, Darko Volaric wrote:
> > {
> > operation: "insert"
> > table: "blah"
> > columns: ["a", "b", "c"]
> > values: [1.3, 2.0, 3.1]
> > on-conflict: "replace"
> > }
>
> I do this all the time. It's trivial enough to generate SQL from that
> sort of thing. If you have an AST then you can trivially map the
> AST<->a JSON/XML/ASN.1/whatever schema.
>
> But I don't think ease of alternative representation is the winning
> argument for wanting the engine core to use an AST. It's only
> convenient.
>
> The winning argument is that working with an AST makes some tasks easy
> that are otherwise hard (e.g., common sub-expression elimination).
>
> > [...] Why are people who come from the websphere
> > learning SQL syntax? [...]
>
> Because it's standard.
>
> > The feature I'm working on now, as a first step, basically feeds the
> parser
> > tokens so I don't have to generate a query string. [...]
>
> That seems rather basic, not really good enough. It must save some
> allocations. But is it worth forking SQLite3 for this?! Whatever you
> do with a fork, it's got to be worth it. Forking is quite hard, so
> every change has got to be worth the effort.
>
> Switching to an AST is going to require more allocations (and much more
> developer effort), that's for sure. But then, this is in statement
> compilation, which should not be the most critical component.
>
> Anyways, this is all very far afield from UDTs. If you want to fork to
> add UDTs, focus on that first.
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] User-defined types
Are you seriously saying that that SQL syntax is friendly? How can you
defend SQL syntax other than on grounds of history or standardization? If
you're more comfortable and familiar with JSON the yes it is easier and you
can avoid an unnecessary conversion step.
If you're using JavaScript you'd send JS objects (ie binary equivalent of
JSON) to the parser rather than SQL since it's frictionless and binary to
binary. Doing that is safer and more efficient, not to mention the savings
in programming and bugs from the SQL generation, which is the biggest win
of all.
On Thu, Jun 4, 2015 at 2:26 PM, Richard Hipp wrote:
> On 6/4/15, Darko Volaric 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
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? Why are people who come from the websphere
learning SQL syntax? It has no magic, the magic is in what it means, which
anyone can understand (tables, columns, joins, search criteria). The syntax
is completely arbitrary, from the 70's or 80's and probably ultimately
inspired by COBOL. There is of course a lot existing information based
around SQL syntax, but most people want to insert some data and do fairly
straight forward queries on it. SQL is probably mostly confusing to them.
The feature I'm working on now, as a first step, basically feeds the parser
tokens so I don't have to generate a query string. Even that gives me a big
saving (mostly in memory), without changing the syntax or introducing
subtle bugs. The next step is "rationalize" the syntax progressively so
that the sequence of tokens I need to pass is closer to the representation
I use internally (in my code). This is the least impact approach I think.
You could insert yourself into any point in the SQL to bytecodes process.
If you wanted to fully support XML queries or whatever that could be a part
of the process, whereby appropriate function calls are generated (say into
your XML query library), or virtual table instance are created or the query
is transformed appropriately.
On Thu, Jun 4, 2015 at 12:05 PM, Nico Williams
wrote:
> On Thu, Jun 04, 2015 at 11:45:28AM -0700, Darko Volaric wrote:
> > Which sort of leads me to my next feature, which is bypassing the SQL
> > language. [...]
>
> I like SQL, but sure, if the compiler worked by first parsing into an
> AST, and if the AST were enough of an interface (versioned, though not
> necessarily backward-compatible between versions), then one could:
>
> - write different front-end languages (though an AST isn't needed for
>this: you can always generate SQL)
>
> - write powerful macro languages
>
> - write alternative/additional optimizers (and linters, syntax
>highlighters, ...) that work at the AST level
>
> If the VDBE bytecode were also a versioned interface then one could
> write peep-hole optimizers as well.
>
> One might even want to generate IR code for LLVM, or use a JIT-er,
> though for SQL I don't think that would pay off. I suspect that most of
> the CPU cycles go to data-intensive tasks such as I/O, cache thrashing,
> and encoding/decoding. I'd be much more interested in SQLite4 being
> finished than an LLVM backend for SQLite3, and I'd be very interested in
> seeing if word-optimized variable-length encoding would have better
> performance than byte-optimized variable-length encoding. The point
> though is that using an AST would make the system more modular.
>
> >[...]. Why use that crusty old syntax when it's equally expressible in
> > JSON, XML or something else. Again I see it just as an API, [...]
>
> Now I'm confused. JSON and XML are not query languages. There exist
> query languages for them (e.g., XPath/XSLT for XML).
>
> I suppose you might have meant that SQL itself is a data representation
> language like JSON and XML are, and it is (data being expressed as
> INSERT .. VALUES ..; statements).
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] User-defined types
On Thu, Jun 04, 2015 at 11:45:28AM -0700, Darko Volaric wrote: > Which sort of leads me to my next feature, which is bypassing the SQL > language. [...] I like SQL, but sure, if the compiler worked by first parsing into an AST, and if the AST were enough of an interface (versioned, though not necessarily backward-compatible between versions), then one could: - write different front-end languages (though an AST isn't needed for this: you can always generate SQL) - write powerful macro languages - write alternative/additional optimizers (and linters, syntax highlighters, ...) that work at the AST level If the VDBE bytecode were also a versioned interface then one could write peep-hole optimizers as well. One might even want to generate IR code for LLVM, or use a JIT-er, though for SQL I don't think that would pay off. I suspect that most of the CPU cycles go to data-intensive tasks such as I/O, cache thrashing, and encoding/decoding. I'd be much more interested in SQLite4 being finished than an LLVM backend for SQLite3, and I'd be very interested in seeing if word-optimized variable-length encoding would have better performance than byte-optimized variable-length encoding. The point though is that using an AST would make the system more modular. >[...]. Why use that crusty old syntax when it's equally expressible in > JSON, XML or something else. Again I see it just as an API, [...] Now I'm confused. JSON and XML are not query languages. There exist query languages for them (e.g., XPath/XSLT for XML). I suppose you might have meant that SQL itself is a data representation language like JSON and XML are, and it is (data being expressed as INSERT .. VALUES ..; statements). Nico --
[sqlite] User-defined types
On Thu, Jun 04, 2015 at 10:54:16AM +0200, Dominique Devienne wrote: > On Thu, Jun 4, 2015 at 10:20 AM, Christopher Vance > wrote: > > If you really want your own types, you could always bundle with ASN.1 and > > store the result as a blob. FYI, Heimdal has a very nice, small, simple, featureful, and easily separable ASN.1 compiler with a BSD-type license. https://github.com/heimdal/heimdal/tree/master/lib/asn1 > Or Protobuf, or ... But you're back to option 1, you must store somewhere > that knowledge, and it's an app-convention, SQL and SQLite don't cooperate. I've never understood why Protocol Buffers. Its authors made all the same sorts of design choices that make DER a lousy encoding for ASN.1... Anyways, the biggest problem with any kind of encoding (ASN.1, JSON, whatever), is that if you want to be able to use decoded values in SQL statements, you end up having to decode and re-encode in every UDF. This could be avoided if SQLite3 had a sort of UDT that's just an opaque handle for a value and which includes an interface for coercing it to a native SQLite3 type (e.g., blobs): this way re-encoding can be avoided as much as possible. > You can also use a self-describing format like AVRO (many others I'm sure), > but you still need to know "a-priori" which blobs are avro encoded, and > even assuming that, that doesn't enforce a "schema" (structure) on the > column (i.e. "static typing"), which I think is necessary. Actually, maybe > a CHECK constraint works here. I've never tried to use a > custom-SQL-function-based check constraint in SQLite. Does that work? --DD Yes, it does. Nico --
[sqlite] User-defined types
On Wed, Jun 03, 2015 at 06:04:29PM -0700, Darko Volaric wrote: > Yep, references a another one. Just like the functions, you have to join on > the user type information, add it to constraints, etc. Once you're extending SQLite3 proper the referential integrity problem goes away (being no different than the "problem" SQLite3 has of tracking value types internally). > In my case I'm already modifying and maintaining my own version of SQLite. > My project is basically a database with a lot of extensions. Submitting > patches is not an issue. The last time I brought these ideas up I was > practically chased off by a mob waving pitchforks and torches. Apparently > almost no-one thinks user defined types is a good idea so there is no point > sharing it. I don't expect anyone to help me maintain the code. The > critical parts of SQLite (like the record read/write) are very stable and > updates hardly ever affect me. I'm not at all sure that UDTs are a bad idea. I do want JSON support, preferably using jq for expressing transformations (or merely addressing specific values in a JSON text) of JSON texts. And I'd like bigint (and bigfloat?) support. Those two types should be enough for easily dealing with a great many needs for UDTs without necessarily having support for arbitrary UDTs. A bitstring type would also be convenient for things like IP addresses and CIDR. Given that SQLite3 is in the public domain, you're not obliged to share your development. But who knows, in spite of all the reasons that using your "fork" would not be advisable, you might succeed in forming a community of users. So unless you have reasons not to share your work, I'd encourage you to share it. If you do share it, you'll want to give it a distinct name (it wouldn't be SQLite3, would it), and you'll want to very careful whose patches you accept: if you want to ever be able to contribute your changes back into the mainline, you'll need to be able to show that each contribution is permitted by the contributor's employer and so on. I have a long wishlist of features or changes that I don't think the SQLite3 developers are going to be very interested in at this time. You might not be either, but if there was a community of external developers that could pool its resources to make contributions that might be welcomed by the SQLite3 developers... Nico --
[sqlite] User-defined types
On Thu, Jun 4, 2015 at 1:54 AM, Dominique Devienne wrote: > On Thu, Jun 4, 2015 at 10:20 AM, Christopher Vance > wrote: >> If you really want your own types, you could always bundle with ASN.1 and >> store the result as a blob. > > Or Protobuf, or ... But you're back to option 1, you must store somewhere > that knowledge, and it's an app-convention, SQL and SQLite don't cooperate. > > That's far from first-class UDTs. Who else other than your app is going to read the data directly, though? Many other SQL systems end up becoming the de facto integration point between disparate backend and frontend systems. So you end up growing additional features to let everyone play in the database using the same set of conventions put in place by the DBA. That doesn't make it a really strong design for the overall system, though, even if it is convenient and incremental to build. I don't think the argument is as strong for things like this in SQLite, simply because often enough SQLite isn't really in a position to leverage any of it any better than your app code can, and just delegating it to your app code works out pretty well for SQLite in terms of support burden and getting bogged down by complexity. -scott
[sqlite] User-defined types
Which sort of leads me to my next feature, which is bypassing the SQL language. Why use that crusty old syntax when it's equally expressible in JSON, XML or something else. Again I see it just as an API, callable by whatever parser you want, or none at all, if your code generates queries directly. I'll definitely release some patches when it's done. Maybe I'll call it "ite" - SQLite without the SQL. On Thu, Jun 4, 2015 at 11:23 AM, Nico Williams wrote: > On Wed, Jun 03, 2015 at 06:04:29PM -0700, Darko Volaric wrote: > > Yep, references a another one. Just like the functions, you have to join > on > > the user type information, add it to constraints, etc. > > Once you're extending SQLite3 proper the referential integrity problem > goes away (being no different than the "problem" SQLite3 has of tracking > value types internally). > > > In my case I'm already modifying and maintaining my own version of > SQLite. > > My project is basically a database with a lot of extensions. Submitting > > patches is not an issue. The last time I brought these ideas up I was > > practically chased off by a mob waving pitchforks and torches. Apparently > > almost no-one thinks user defined types is a good idea so there is no > point > > sharing it. I don't expect anyone to help me maintain the code. The > > critical parts of SQLite (like the record read/write) are very stable and > > updates hardly ever affect me. > > I'm not at all sure that UDTs are a bad idea. I do want JSON support, > preferably using jq for expressing transformations (or merely addressing > specific values in a JSON text) of JSON texts. And I'd like bigint (and > bigfloat?) support. Those two types should be enough for easily dealing > with a great many needs for UDTs without necessarily having support for > arbitrary UDTs. A bitstring type would also be convenient for things > like IP addresses and CIDR. > > Given that SQLite3 is in the public domain, you're not obliged to share > your development. But who knows, in spite of all the reasons that using > your "fork" would not be advisable, you might succeed in forming a > community of users. So unless you have reasons not to share your work, > I'd encourage you to share it. If you do share it, you'll want to give > it a distinct name (it wouldn't be SQLite3, would it), and you'll want > to very careful whose patches you accept: if you want to ever be able to > contribute your changes back into the mainline, you'll need to be able > to show that each contribution is permitted by the contributor's > employer and so on. > > I have a long wishlist of features or changes that I don't think the > SQLite3 developers are going to be very interested in at this time. You > might not be either, but if there was a community of external developers > that could pool its resources to make contributions that might be > welcomed by the SQLite3 developers... > > Nico > -- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] User-defined types
On Thu, Jun 4, 2015 at 10:20 AM, Christopher Vance wrote: > If you really want your own types, you could always bundle with ASN.1 and > store the result as a blob. > Or Protobuf, or ... But you're back to option 1, you must store somewhere that knowledge, and it's an app-convention, SQL and SQLite don't cooperate. That's far from first-class UDTs. You can also use a self-describing format like AVRO (many others I'm sure), but you still need to know "a-priori" which blobs are avro encoded, and even assuming that, that doesn't enforce a "schema" (structure) on the column (i.e. "static typing"), which I think is necessary. Actually, maybe a CHECK constraint works here. I've never tried to use a custom-SQL-function-based check constraint in SQLite. Does that work? --DD
[sqlite] User-defined types
On Thu, Jun 4, 2015 at 3:04 AM, Darko Volaric wrote: > In my case I'm already modifying and maintaining my own version of SQLite. > [...]. The last time I brought these ideas up I was > practically chased off by a mob waving pitchforks and torches. Apparently > almost no-one thinks user defined types is a good idea so there is no point > sharing it. I don't expect anyone to help me maintain the code. FWIW, I think UDTs are a great idea. But also - optional static typing of columns; - checksums of blocks;- - blob two-tier storage (a la Oracle); - native indexing of virtual table; - native JSON support; - etc... Yes, the community, just like the authors, of SQLite have a strong bias against changes and to keep SQLite "lite". And can be brutal in how they say it (or ignore it) when someone rants about his pet-peeves, or try to push forward his wish list (including me above). But remember that SQLite didn't have FKs for a long time. Didn't have CTE. Both of which are major enhancements. So there's hope long term IMHO :). Now unlike most (including me again), you go further and actually code it up apparently. That's great. But it's hard to fork SQLite and get any traction given the fast-paced refactoring/optimization the main code goes through. And also UDTs can have widespread side effects within SQLite, hard to gauge w/o having the whole code-base and design in ones head like DRH. Might be good enough for you, but not for the high quality standards which is a hallmark of SQLite IMHO. All I can suggest is continue communicating and perhaps also OSS your changes on GitHub or similar, and you may get help somehow. I suspect (hope really) first-class UDTs in SQLite (as Nico calls them) haven't been dismissed, and it's more a question of finding the time and funding to do them right, i.e. in a "lite" way that doesn't adversely affect SQLite if you don't use them, and thoroughly tested as usual. My $0.02. --DD
[sqlite] User-defined types
That's an entirely valid point, but didn't come up in the discussions if memory serves. It was the "you don't know what you're doing and don't understand databases" which I thought was an odd response, but that's all irrelevant. I agree that feature bloat is not a good idea (hello, PgSQL!) and I don't want to add features but rather programming interfaces. I don't make extensive changes to the code at all. I basically try to provide hooks in the code that don't actually change any functionality. For instance my UDTs design just adds a few bits to the record header that associates a small, unsigned integer with a stored field, nothing more. These bits have no impact on any code that is not aware of them. It was the most minimal design I could think of. Similarly with the VM. I don't add opcodes nor do I change the form or semantics of the existing codes, but I do sneak "links" into unused opcode parameters which are interpreted by my own code, kind of like running a VM of my own alongside. In other places I just use private functions as if they were public APIs. I think this is the sort of approach is beneficial to SQLite, especially since it's an embedded database and it's very natural to want to extend it and intertwine it with your own code, if you need more than the stock functionality. I also understand that it makes the job of the developers harder and that they have no reason to put time into hooks or APIs used by a relative minority. I suspect most people who need extensions are like me and want something very particular and mix in their own proprietary code. For instance, I think programming in C is like a visiting a dentist who doesn't use lidocaine, so my actual functionality is in a completely different language. On Wed, Jun 3, 2015 at 11:52 PM, Dominique Devienne wrote: > On Thu, Jun 4, 2015 at 3:04 AM, Darko Volaric wrote: > > > In my case I'm already modifying and maintaining my own version of > SQLite. > > [...]. The last time I brought these ideas up I was > > practically chased off by a mob waving pitchforks and torches. Apparently > > almost no-one thinks user defined types is a good idea so there is no > point > > sharing it. I don't expect anyone to help me maintain the code. > > > FWIW, I think UDTs are a great idea. But also > - optional static typing of columns; > - checksums of blocks;- > - blob two-tier storage (a la Oracle); > - native indexing of virtual table; > - native JSON support; > - etc... > > Yes, the community, just like the authors, of SQLite have a strong bias > against changes and to keep SQLite "lite". > > And can be brutal in how they say it (or ignore it) when someone rants > about his pet-peeves, or try to push forward his wish list (including me > above). > > But remember that SQLite didn't have FKs for a long time. Didn't have CTE. > Both of which are major enhancements. So there's hope long term IMHO :). > > Now unlike most (including me again), you go further and actually code it > up apparently. That's great. But it's hard to fork SQLite and get any > traction given the fast-paced refactoring/optimization the main code goes > through. And also UDTs can have widespread side effects within SQLite, hard > to gauge w/o having the whole code-base and design in ones head like DRH. > Might be good enough for you, but not for the high quality standards which > is a hallmark of SQLite IMHO. All I can suggest is continue communicating > and perhaps also OSS your changes on GitHub or similar, and you may get > help somehow. > > I suspect (hope really) first-class UDTs in SQLite (as Nico calls them) > haven't been dismissed, and it's more a question of finding the time and > funding to do them right, i.e. in a "lite" way that doesn't adversely > affect SQLite if you don't use them, and thoroughly tested as usual. My > $0.02. --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
On Wed, Jun 03, 2015 at 03:55:04PM -0700, Darko Volaric wrote: > 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: A UDT can't be first-class without modifying SQLite3. Without first-class UDT support the application has to be responsible to some degree for adhering to whatever encoding conventions it chooses to use. User-defined functions, collations, and virtual tables can be used to move some of the burden from the application to the UDFs and VTs, but you can't move all of it (e.g., because whatever SQLite3 type you use to encode UDTs will often have other uses, leading to an aliasing problem that requires the application to avoid it). > - 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. It's cumbersome because it creates a referential integrity problem. > - 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. Right, you'd basically be talking about adding new first-class types to SQLite3. That's quite an understaking and not for the faint of heart. Even if you tackle this, chances are it'd be very difficult to get the SQLite3 dev team to accept the changes -- one would be forking SQLite3, and that requires serious (read: lots of experienced software engineer time) effort to develop and maintain. > - 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. [...] Encoding as text or blobs is about your only realistic option. Enums can be encoded as numbers too, as can small bitsets. > 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. At that point why not just switch to Postgres? Nico --
[sqlite] User-defined types
Yep, references a another one. Just like the functions, you have to join on the user type information, add it to constraints, etc. In my case I'm already modifying and maintaining my own version of SQLite. My project is basically a database with a lot of extensions. Submitting patches is not an issue. The last time I brought these ideas up I was practically chased off by a mob waving pitchforks and torches. Apparently almost no-one thinks user defined types is a good idea so there is no point sharing it. I don't expect anyone to help me maintain the code. The critical parts of SQLite (like the record read/write) are very stable and updates hardly ever affect me. Regarding PgSQL, an advantage of encoding your own binary types is that you can copy them straight into your code and execute with them directly - I use the same encoding/data structures throughout and they serve my code and requirements instead of the database's or its API. PgSQL is also a poor fit for me because it's huge and assumes a (huge) server, I'm running on small nodes with relatively little memory. On Wed, Jun 3, 2015 at 4:26 PM, Nico Williams wrote: > On Wed, Jun 03, 2015 at 03:55:04PM -0700, Darko Volaric wrote: > > 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: > > A UDT can't be first-class without modifying SQLite3. Without > first-class UDT support the application has to be responsible to some > degree for adhering to whatever encoding conventions it chooses to use. > > User-defined functions, collations, and virtual tables can be used to > move some of the burden from the application to the UDFs and VTs, but > you can't move all of it (e.g., because whatever SQLite3 type you use to > encode UDTs will often have other uses, leading to an aliasing problem > that requires the application to avoid it). > > > - 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. > > It's cumbersome because it creates a referential integrity problem. > > > - 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. > > Right, you'd basically be talking about adding new first-class types to > SQLite3. That's quite an understaking and not for the faint of heart. > Even if you tackle this, chances are it'd be very difficult to get the > SQLite3 dev team to accept the changes -- one would be forking SQLite3, > and that requires serious (read: lots of experienced software engineer > time) effort to develop and maintain. > > > - 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. [...] > > Encoding as text or blobs is about your only realistic option. Enums > can be encoded as numbers too, as can small bitsets. > > > 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. > > At that point why not just switch to Postgres? > > Nico > -- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[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] User-defined types
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] User-defined types
On 5/27/15, Matthias-Christian Ott wrote: > > Is there another way to define user-defined types despite this method > and virtual tables? > I know of no other. -- D. Richard Hipp drh at sqlite.org
