Lambda the Ultimate

Just to be clear, you want to be able to query the data store using SQL?

so as to allow programmers to use standard tools, and reduce their, er.. how should I put it, cognitive burden

I read you paper Ehud and have been trying to come up with a principled model for "cognitive burden" in this context. I have a few ideas alread, but I let you know if I get them worked out. ;-)

This topic leads directly into what Scott Ambler calls the "object-realational impedence mismatch".

Interestingly, the recent mention of Baez's work in Category Theory got me to read some of his papers, and it gave me the beginnings of an interesting model for this phenomenon.

Baez has a notion of "decategorification" where you strip away the higher order morphisms of an n-category to get an (n-1)-category.

In a (perhaps not so) strange way, this matches what happens when you serialize ojects to a relational store: you preserve the first-order morphisms (the relational mappings) but strip away the operations that occur between those relations (methods that operate "over" those relations, providing additional structure).

So to answer your question, I don't think there is a natural (read "low cognitive burden" ;-)) way to use SQL to query a more general object store because there is often structure implicit in objects that would be lost in translation. (Leaving aside that most implemented version of SQL don't even handle all relational properties that well. ;-) )

Aye.

But you are looking too far afield. The writer of the API decides on the normalization (such as it is). You can assume a given and fixed data model, and rleational mapping.

Context. In our current time this is a very relevant topic. There is the concept of a unified storage management layer over all kinds of information stored in various manners, including in databases. Another concept for a unified storage management layer is to manage all kinds of information in a common physical manager (as the SQL Server is being transformed by Microsoft). And there is the version that is a hybrid of the two concepts.

API vs Query Language. Probably an unfair casting of the choice. The API likely assumes a QL. Can I design the API and assume the language? Thats difficult. The API is providing binding to results to a question. In QL it is often more than just a question; data is being combined and transformed -- reports being formed,, business objects being constructed, messages constructed, and documents composed.

My intuition is that a unified storage management query composes a view over disparate data. The view is likely hierarchical. Some subtrees within the hierarchy map to base storage managemnt models (gif image, relational data, xml).

The API must support asking the question and manipulating the results. The hierarchy in the results must be traversed via API. Each subtree should identify the model of its' contents via API. Contents from base storage managemnt models have handlers that are associated and accessed via API.

Can you, for example, do a "join" between a RDBMS table and a non-relational data store object, which might be considered a virtual table?

One more thing. In enterprise systems of this sort, data may reside on different machines. Does the messaging and/or propagation model assume relation data? An RDBMS?

I can't seem to things just right. ;-)

The writer of the API decides on the normalization (such as it is). You can assume a given and fixed data model, and relational mapping.

Reworded this way, this is a trivial problem, no?

Since the data model and relational mapping are fixed, it is fairly straight-forward to provide a simple SQL interpreter that converts between the two. If your concern is to allow cross-functionality with standard SQL tools, such a report generators or "business intelligence" tools, you can simply implement within a standard framework, such as JDBC or ODBC.

If you are too lazy for this, you can always just make your datastore capable of creating its fixed schema in some external relational store and the persisting its data to it using the fixed mapping. You can then let the relational store handle the SQL work.

The tricky part of these things tends to arise when you are trying to accommodate changing models and changing mappings.

From a pure "cognitive burden" point of view, I would argue that this embedded SQL approach is only easier when you want to give access to external tools (or users) that already use SQL. If the API is for programmer use, it is probably more consistent and straight-forward to use only the host language.

Experience with Oracle's SQLJ bears out that you end up with something that is not quite standard Java and not quite standard SQL, and thus probably more confusing than helpful.

That's exactly the problem. How can make it as painless as possible?

I am curious to know how many here worked at places that used this approach? Anyone at all?

Don't do it. ;-)

The normal route is to modularize the SQL off into its own "data mapper" and only access it from a "native" style interface in the host language. These kinds of solutions are discussed in detail in Fowler.

I'm inclined to believe that mixing two languages in the same source code (as with SQLJ) is just a bad idea with very few real advantages.

I doubt anyone has worked anywhere where there was a fixed data model and a fixed relational mapping. ;-)

But you are right about one thing, when this sort of API is designed one must implement a mechanical process the guarantees it remains consistent with changs to the data model. This, however, is not the hard part of this exercise.

That's why this example is of interest here: APIs based on current programming languages don't interoperate nicely with (the declerative) SQL and related tools.

The approach taken by Alphora's Dataphor looks quite nice (and has the additional advantage of implementing a query language that isn't SQL). The query engine hands off as much of the D4 query as it can to the provider (translating into SQL where the provider understands SQL), but can provide full relational querying over flat files, XML etc.

Finally, I think I've got you. (I think ;-))

I suppose the question is: is it the fault of the APIs or of SQL and the SQL tools?

Some of these difficulties are inherent in the nature of SQL, I think.

If you redesigned a query language that had compositional elements rather than a monolithic select statement, you would probably end up with more flexibility and conceptual interoperability with more general programming languages.

It would be slightly more verbose for simple queries probably, but more effective overall (performance may be an issue, since it would also reduce the ability of vendors to use slight of hand on the internals.)

Another subtler problem with SQL is that it treats data almost necessarily as text. The "natural" output of a SQL query is tabular text; the "natural" input is string encodings of other datatypes.

This is why something like SQLJ (or any other API) doesn't work so well; it isn't necessarily clear what has to go in and what you are going to get back, so there is a whole bunch of "unnatural" conversion that has to happen before and after.

If you had a query language with standard (in practice as well as theory) representations of the data that were equivalent to those in the host language, a whole lot of the problems would go away.

Yeah, I'm curious how well it performs in practice. Do you know of anyone who has tried it?

I have actually done exactly that.

We have a middle-ware application used for providing programmatic access to legacy systems that do not expose an API. The approach is to implement a virtual terminal and expose an API over that. We support both a low-level API (send keys, wait for text, etc) and a high-level API that wraps entire business transactions (essentially recorded sessions).

We had an idea of providing access to this functionality via a database access API, e.g. SQL over ODBC. We ended up doing SQL (limited) over OLEDB for technical reasons.

Some reasons we wanted to do this are:
1. As stated above - reduce the cognitive burden of learning a new API.
2. There are lots of tools and environments for accessing and displaying relational data. By providing a compatible API we allow the end-user to leverage these environments.
3. Easier integration with existing relation data.

There were numerous technical issues. Here are several notable ones:
1. Lack of sufficient documentation for building a universal OLEDB provider.
2. Modeling non-relational data as relational data.
3. State.
4. Joins (merging data from numerous sources)

The modeling issue was more or less circumvented by using hierarchical tables, some of them having only a single "record". This is obviously a hack, but it worked. Also, any transaction that required input parameters was exposed as a "stored procedure". Finally, transactions that provided intrinsic sorting or filtering capabilities had to implement a specific argument signature.

A bigger issue was state. Databases are stateless in that you can access any table or stored procedure at any time. The applications we are wrapping are obviously stateful in nature so only some transactions are accessible at any given time. Our solution was basically to ignore this issue, that is if you execute an SQL command that uses a transaction that is not accessible, the SQL command fails. We do however support the various discovery APIs so the user can determine at any given time which tables and procedures are available.

The bottom line is that this solution works very nicely and makes it very easy to create front-ends for our server from an environment like VB. We even have a patent pending for it.

Oh and we don't support joins yet.

Yeah. That's one of the important aspects (think SchemeQL).

The problem is, of course, that traditional APIs are also not that ease to nest and compose (which explains why FP is so tempting).

If you redesigned a query language that had compositional elements rather than a monolithic select statement, you would probably end up with more flexibility and conceptual interoperability with more general programming languages.

That would be D4, then (in theory at least). In practice I'm afraid I don't know; unfortunately Dataphor is a commercial product, not a research project developed in a university, and if you ask Date and Darwen (who formulated Tutorial D, the language D4 is based on) about things like performance they will tell you it's an implementation detail and refuse to comment further...

As far as I can see, the core difficulty in implementing a composable query language is that there are a lot of different ways of saying the same thing. As with SQL, you essentially need to find an execution path that will perform the query in the most (or at least a tolerably) efficient way; but SQL's "monolithic select statement" simplifies this considerably - which I suspect is the reason why it is the way it is.

That was partly why I was curious. Date seems to be rather extreme on these points. His insistance that a query language be based on a "pure" relational model and, by extention, on set theory seems to forget that a basic set theoretic operator, powerset, is necessarily O(2^n).

He must think that computer scientists are simply too lazy to find a way to handle that efficiently. ;-)

I am not sure this is a correct assessment.

Anyway, to connect this to PL research, notice that one element of optimizing queries concerns the creation of temporary tables. This is quite related to deforestation. A compositional approach can make use of the results concerning deforestation, the foldr/build rule etc.

This was the kind of thing I was thinking of when I suggested the compostional approach, but I also think that Dominic is onto something. The monolith select makes it easier for the DBMS vendors to "cheat", i.e. arbitrarily decide that things will work a certain way and make whatever optimizations they need to along that route, even if it "breaks the rules".

A strange example of this is in Oracle SQL (IIRC) where using "where a = b" and "where b = a" will get you radically different performance for certain a, b, even though logically there shouldn't be a difference. (I wouldn't be surprised if they actually gave different results for certain rare a, b with sketchy equivalence definitions.)

I think a more compositional implementation would either lose these asymmetrical performance optimizations or would make them more apparent to users (and hence perhaps less acceptable to them).

These kinds of mappings also exhibit what might be called the “generic fallacy”, i.e. the idea that you can treat different things the same way. The fact that your application uses an SQL server or a local file store is not a just some detail but is essential to its design. When using an SQL server, for example, there are networking issues to deal with. You will also need to optimize your application and you cannot do that if your data source is hidden.

Lastly, this doesn’t really spare the developer any effort. O/R mapping API’s are never simple and easy to learn. The developer can either learn some complicated proprietary API or standard SQL. And if he already knows SQL then he doesn’t even need to learn anything new.

Well, we do have real data: if these optimizations on general case compositional structures were sufficient to optimize for large data tasks, FPLs would be blazingly fast and there would be a stampede of C guys trying to learn them. ;-)

Can you, for example, do a "join" between a RDBMS table and a non-relational data store object, which might be considered a virtual table?

The QL could achieve "join-like" behavior. What do I mean? Join is an operator from the Relational Algebra. XQuery as supports joins using flower expressions -- FLWR (For-Let-Where-Return). In working with disparate data "Join" would require type mapping in some condition evaluations. The architecture could use a Data Provider Interface (DPI) where types are declared and mapped to types in the QL. In this case, you could have joins.

Join-like behavior is achievable. For instance given data sources A and B, apply conditions to A and B separately, producing A' and B', then form the cartesian product of A' and B', and you have the equivalent of a join. The benefit is you avoid the type mapping requirement, the cost is you push the problem up to the query writer.

One more thing. In enterprise systems of this sort, data may reside on different machines. Does the messaging and/or propagation model assume relation data? An RDBMS?

On assuming the common model. A DBMS has underlying storage management. The physical storage of data is managed and indexes about the data are maintained. With proper separation of concerns, RDBMS, OODBMS, XML DBMS, filesystems, and more can be implemented over a common storage management layer. Mixed modal query is possible by mapping the query to the common layer. Its especially practicable if indexes cover all queries. QL execution maps to the physical layer through indexes. If no index covers a query, all physical data must be examined -- the infamous table scan.

For general ad hoc query it is impossible to have index coverage, the possibilities are NP complete. But systems and enterprise applications always have index coverage -- because they must be performant. Performance requires index coverage and this is possible because the queries are well known.

think SchemeQL
I found this LtU discussion interesting, and also previously on LtU "Unifying Tables, Objects and Documents".

(Fun that a SQL select is used to help explain list comprehensions with records in the Clean Intro)

Could you explain this a bit? From my understanding of it, the relational theory can be used to model any and all kinds of data (ignoring physical implementation issues). You listed this as a technical issue, so was it due to the particular RDBMS or query language? Or if it really is a modelling issue, what kind of data?

I've been seeing similar statements increasingly often lately, but haven't had much success finding out what the authours meant by them. Most often they seem to be talking about things such as hierarchical structures (perhaps OO or XML's influence?). But in that particular instance, it is the limitations of SQL (lack of transitive closure, etc.) not relational theory itself that makes things so difficult.

Marc: "His insistance that a query language be based on a "pure" relational model and, by extention, on set theory seems to forget that a basic set theoretic operator, powerset, is necessarily O(2^n)."

From what I can tell from his recent writings, he seems to regret the emphasis placed upon set theory in relational theory literature. The Third Manifesto, for example, places much more emphasis upon predicate logic.

Can you elaborate or give some links? Thanks!

Some people won't change, no matter how much evidence you present them
Isn't it always the other people that won't change ;-)

concurrent programming
Well, if you'll forgive yet another dubious analogy: changing green threads to OS threads to Pico Threads in Java, is like replacing some plants in a garden; going from Java to Erlang is like replacing an ecosystem. There aren't that many people in a position to push through project changes of that magnitude, even when it seems reasonable to do so.

Maybe we'll hear some more about FLWRs ;-)

I'm not sure that's inherently so. I was a "C guy" once upon a time, and I switched to FPLs for a while. Aside from the fact they were cool ;-), one practical feature they had that wowed me was GC. I had spent way too much time doing memory management relative to solving my problems, and I had lost the optimize to the max mania of my younger years, which is the forte of the C world.

I will grant that my stampede comment was a bit gratuitous (one of my mottos is "always go for the joke" ;-), but the serious kernel of it is that people will embrace something new if it solves an old problem without giving them any new ones.

Has anyone used this for non-trivial data sets? How is the performance?

(I may just try it myself; I've sworn I'm going to learn Haskell properly as soon as I build a decent plugin for it in Eclipse. ;-) )

Marc: "His insistance that a query language be based on a "pure" relational model and, by extention, on set theory seems to forget that a basic set theoretic operator, powerset, is necessarily O(2^n)."

I don't believe that powerset is especially involved in the basic relational algebra...? For what query would it be necessary to compute the powerset of a relation?

Of course, we also want language interoperability: control flow in the system is likely to cross language boundaries, as well as operating systems, and communication networks. A soultion mustn't dictate the programming language for the entire enterprise in any major way.

Notice that CLI/JVM/Parrot are moving us in a direction of making this sort of interoperability easier, but there's still a long way to go. We'll get there eventually.

In practice this probably doesn't have any relevance, but it does show that what is a convenient tool for proofs in Math is not necessarily computationally tractable. As I interpret Date's attitude, he doesn't seem to be too concerned with this issue, at least not directly. He gives off more of a "one should be pure or die trying" vibe. ;-)

My intention was rather straightforward. Our application implements transactions as methods with input and output parameters. The input parameters are usually used to generate inputs that are "keyed" to a host system via a virtual keyboard.

Likewise the output values are usually generated from strings read of a virtual screen. These string values may be converted to other types, such as numbers, prior to being returned. Moreover, some values are returned discretely while others may be packaged as arrays. For example, a table displayed on the virtual screen might be parsed as a 2D array.

Since very few limitations are placed on the structure of the values returned by a transaction, representing these values as a result of an SQL query is obviously a bit of a stretch.

Overall we discovered that for a collection of transactions (what we term a service) to be usefully exposed through a database-like API, the transactions had to be built in a way the was conductive to that purpose.

Once successfuly exposed, it was very cool to see how easy it was to build a PC-based front end for existing legacy applications using VB, often using just mouse clicks without having to type any code. This is a real benefit for the type of customers we often work with.

I hope you find a thread where it's not OT, because I'd like to hear them. ;-)

open-source projects with one to five developers or teams led by experienced developers instead of PHBs
Would a project like OpenNMS be able to attract open source developers and users if the source code was Erlang rather than ubiquitous Java?

Sendmail Meets Erlang
Seems like this is an internal project in a commercial organisation? There's much less concern about keeping project staff. These are the kinds of projects that really can use non-mainstream tools.

I can see the simely perfectly well, but since this sort of argument came up quite a few times recently, I think it's time to debunk this line of argument completely.

This argument is basically an ad hominem tactic, that shifts the ground of the debate from a given technical issue to the alleged smugness and conceit of the person you don't agree with. That's bad enough, but in the case of the debates going on on LtU things are even worse.

This argument usually comes up when people argue the merits of different programming paradigms (and I'll leave debunking this term for another time). Often it is raised when someone write something against the mainstream paradigm of imperative proceduare or oop programming. Why this is so inappropriate for LtU? Two reasons.

(1) Most LtU readers are fleunt in several imperative language, know their advantages and often write code in them for a living. It is simply not the case that we don't "get" imperative programming. We do.

(2) People visit LtU beacuse they are interested in a variety of programming techniques and methods that are available in a wide range of languages. People here cherish the fact that they can learn and experiment with new techniques. If they say they like Haskell, it is most likely that they tried it, fighting public opionion, and decided they like it for their own reasons, and personal taste.

Reading (1) and (2) together you see that calling you average LtU reader biased is absurd. The people who simply "know" that one technique is better than others, without ever learning about them are the biased ones. Not that ones that made the journey.

A final comment. LtU is not a language advocacy site, and people that want religious wars about this sort of thing are likely to prefer other sites. We try to learn and understand programming languages and their programming styles. We then prefer making our own minds. Advocacy on LtU tends to backfire...

Yes. That's the sort of thing I have in mind.

However, in the system I was thinking aobut we don't need (or want) screen scraping. We have a well designed abstraction of the database, it is simply not "open" to SQL-based systems. That's the part that needs to be fixed (and no, normalization of the entire databse is not a very good option in this case, for many reasons most of them I can't discuss).

XQuery supports joins using flower expressions -- FLWR (For-Let-Where-Return). XQuery FLWR XQuery 1.0: An XML Query Language

In working with disparate data "Join" would require type mapping in some condition evaluations. Consider the SQL statement: select r.foo, s.baz from ruby r, solar s where r.bar = s.bar

In a world of mixed data sources where ruby and solar are disparate data sources the type of ruby.bar and solar.bar must be known and convertible to a common type in order for the condition of the join specified in the where clause to be evaluated.

I agree completely with you that LtU's readership seems on the whole to be very well-informed, thoughtful, reasoned and tolerant.

I don't think we can conclude that means we are unbiased. Everyone has biases, and being well-informed, thoughtful, reasoned and tolerant can actually disguise our biases, most especially to ourselves.

To use myself as the whipping boy for a moment, when I made my recent "cognitive burden" argument in another thread, you rightly "attacked" it as too unprincipaled for reasonable discussion, and brought to the surface the premise that caused me to bring it up: "Cognitive burden is a significant factor in the use and design of PLs."

You argued essentially that due to the overall complexity of programming, and without some kind of model for "cognitive burden", we should hold off on appealing to it.

I found this discussion quite useful and productive, and it is the very reason I choose to post here rather than lurk, or simply go off and read all the papers that are mentioned here: people can challenge your assumptions better than a text and discussing ideas with others often creates new challenges and new perspectives.

So in this spirit, if someone says: "PL feature X is superior because it ensures Y", is it not reasonable to challenge the importance or relevance of Y, especially if Y is left unstated explicitly? Sometimes a bit of humour or a provocative line of questioning is the best way to get Y out in the open so it can be discussed.

Certainly we don't want to descend into flamewars or holy wars, but I would think we also don't want to put a chill on potentially relevant topics or lines of inquiry in the theory and design of PLs because they are too "hot" or might offend the biases (hidden or not) of others.

If we all agree to remain civil and reasoned, and to interpret the posts of others as having the best intentions, I think we can achieve both goals.

Well, not really. ;-)

I suppose I am somewhat confused between when you are speaking "ex cathedra" as the moderator, and when you are speaking as "just Ehud".

Either way, it doesn't seem to deter me from posting. ;-)

Alternativly you could look at a solution from a company like Attunity.

Since Erlang is itself open source software, A reasonable conclusion is there are at least some such programmers.

What I'm trying to ask is why do programmers choose imperative languages when they're in position to pick FPLs?

Most don't know they're in a position to pick FPLs. Those that do probably also know there is far less support for software using those FPLs.

I'm not saying that every distributed/concurrent project should be written in Erlang, but it's interesting that so many aren't.

What's really interesting is why there do not seem to be any copies of the OTP framework in more popular languages. A better choice may be to implement the framework in XML-RPC or SOAP or some other common denominator distributed RPC/message mechanism.

JMS is more like Erlang's pattern-based message passing than any other part of J2EE. JMS has other qualities like persistence. But it doesn't have Mnesia.

A lot of people seem to think J2EE means "use EJB" rather than "use the right tool for the problem".

Often the right tool is simply servlets. The next step beyond that would be JMS or a tuple space like JavaSpaces, which granted isn't part of J2EE (but should be).

I'd expect a tuple space to be a simpler solution to many problems JDO addresses. JDO is to tuple spaces as EJB is to servlets.

People often exhibit difficulty in applying simple solutions to problems for various reasons. One reason is not understanding the tools, another is not understanding the problem.

I agree - basically it is ad hominem and it was lazy not to make a clear statement of what I disagreed with, which is simple enough to state: the occasional suggestion that it's their fault (whoever they are) for not seeing the light.

That way of thinking doesn't seem likely to produce any useful ideas. Ideas about removing barriers to adoption. Ideas about influencing decision makers. Ideas about taking responsibility for skunkworks prototypes built on your own dime. Ideas about being ready with a replacement solution when a project grinds towards failure.

Yes, that's basically we the approach we discussed. However it seems a bit too adventurous, so I suspect this will not be something management's going to like...

Attunity looks interesting. Obviously, things are much more complicated in real life, so a home grown solution may prove chaper in the final analysis.

I think that in the three years LtU is running I acted as moderator less than a down times. Mostly it occured when the site was spammed by obviously unrelated posts, and I blocked the address used. That's about it.

I see LtU as a community site, and I don't actively moderate it in any sense. I just try to recruit contributing editors (tha'ts the guys that post to the home page).

That may be so, but if you say "I don't think we should be discussing this here" it has quite a different weight, and meaning, than if I said it. ;-)