Embrace Sql

Rather than converting back and forth between SQL and the app language, or twiddle with complicated wrapper/mappers that need dedicated experts; perhaps the industry should build an open-standard language that integrates well with it. PL/SQL and other vendor's competitors sort of do this, but they are highly proprietary, somewhat outdated, and do not have a dynamic/free-typing variation for those who enjoy loose typing.

In TableOrientedProgramming (TOP) thinking, most of the complexity of an app is in tables and to a lessor extent the queries. Simplifying table usage and querying is more important than simplifying app code if TOP is done right, or if it's for a data-centric task. "Bloat-ified" query syntax (ExpressionApiComplaints) gets in the way of this goal. I agree the repetitious parts of queries can be simplified via helper API's and tools, but don't try to bury entire queries behind goofy, unnatural API's.

What's an "unnatural" API?
See ExpressionApiComplaints for an example

(Note that I don't believe SQL is the ideal query language. I've even suggested an alternative, SmeQl. However, SQL is here to stay for a good while so we might as well learn to work better with it.)

--top

Issues raised about SmeQl and other SQL alternatives moved below under "Regarding Alternatives".

Okay, what sort of integration are you talking about that can't be built as a DSL inside an existing dynamic language or statically typed functional language? If you're talking about more mainstream languages like CsharpLanguage, I would say the answer is that they do no worse at SQL integration than anything else that requires dynamic behavior. --JesseMillikan

Off the top (no pun) of my head:

A type system that matches the DB's type system, perhaps adjusting to the vendor(s) involved. For example, many languages use floating point to represent decimal values, which can create problems. True decimals are not floating point.
- Not every environment handles it well. (I frequently wish the DotNet datetime type matched the SqlServer datetime type.) However, modern languages have different design forces than just interacting with databases. Most languages do provide the ability to define your own types and use them accurately, and then it's the trouble of library maintainers to do so if they really need to.
Result sets automatically create variables. Perhaps have the option of "regular" variables or mapped variables. Perhaps something like:

       // mapped - error-handling not shown here
       QUERY TO MYMAP
         SELECT * FROM addresses WHERE houseID = 7
       END QUERY
       print("zipcode: " & MYMAP.zipcode)  

       // variables
       QUERY TO VARIABLES
         SELECT * FROM addresses WHERE houseID = 7
       END QUERY
       print("zipcode: " & zipcode)

(continued)
- CommonLisp and EmacsLisp could certainly do this, though it might require compile-time database access. Few of the languages without syntactic macros allow a single-token access to a changeable environment like this, but Ruby would. (The global environment is excluded for sanity.) I don't think it's of high value anyhow.
- Lisp dialects won't get accepted into the mainstream, regardless of merit. I agree it is only a minor point, but all these non-DB-friendly minor points add up to anti-productivity hinderences.
- Mainstream languages are unfriendly toward any close integration with another domain or tool. If the point is that mainstream languages suck at high-level programming, we all know.
Loops and loop syntax readily geared toward SQL result sets.
- I'm not sure what is missing here. I work in AspDotNet, so I'm mostly either databinding (which is bogged down purely by AspDotNet) or occasionally looping over a set of entity objects, which is as simple as "foreach(Person p in people){..."
- AspDotNet's approach is too verbose for my tastes (unless maybe you only use Microsoft databases, which "permit" more shortcuts.)
Borrow language idioms from SQL, similar to ExBase's "FOR" (same as WHERE) and "TO" clause used on many operations.
Built-in table system with SQL for local processing.
- Putting this in the language would drive the language and environment complexity up wildly. I'm also not sure what situations would call for this local processing where you wouldn't do it on the database server.
- It was fairly common in the late 80's. Are we going backwards?
- If I don't need it, it doesn't really matter that it was common in the 80's. And I'm saying I don't need it.
- We don't "need" anything except machine language. But I want to make developers and companies more productive by giving them a database-friendly language.
Easy way to embed variables or expressions in the SQL that are converted to SQL at run-time.
- Not sure what you mean by this, either. Can you give an example?
- To borrow a Php idiom: "SELECT * FROM venders WHERE venderID = $vid". Here "$vid" is an application variable. Ideally, variables declared as a text type would automatically quote themselves when the conversion takes place.
Libraries that have commonly-needed operations such as table dump/displays (mostly for debugging, not production display), conversion/export utilities for to-and-from XML, delimited, etc., data dictionary extraction, creation of join clauses or other common or repetitious clauses using data dictionaries or table dictionaries.
- I use a library with a lot of that stuff (LLBLGen pro). As for table dump/displays, I do most of my data-heavy work in the database before messing with the app side; that could be a result of working in an environment with no useful REPL.
- But custom libraries result in a lack of standardization. Each shop reinvents their own and the developer has to learn a new set with its own oddities.
- Is that different outside of libraries for talking to databases?
- I don't understand the question. We want this new language to encourage a common way to perform typical DB-related tasks so that a developer who knows it can walk into a new shop that also uses this language and see the same operations and idioms rather than start over.
A parser that can optionally recognize and flag embedded SQL. This would also allow one to take inventory or do global substitutions more easily; SQL wouldn't have to be treated only as strings.
- It is an annoyance to have that stuff spread out everywhere. I would want to have this right in the DBMS client, with better support for creating stored procedures, functions and views without "leaving the zone" to work in a poor environment. I think the weakness should be fixed at a level where all clients of the database benefit, not just users of one language or environment. (That does lead to a discussion about VendorLockIn.)
- Perhaps DatabaseVendorLock is more appropriate. I generally disagree with the "scatter" complaints. SQL *is* a key app language, not something to hide a way in a dark corner.
- I don't think the trouble is that you have to do it one way or the other; the trouble is designing an environment that can really be built, used and supported. How, for instance, do you support (or not support) different SQL dialects if this support is built into the non-database IDE? That's why I would say take the simpler route - put SQL in a place where it can be realistically handled well.
- Often it doesn't make sense to pay the "isolation tax" described in the topic link if you are not going to switch vendors often. When I do scenario cost math, it usually does not favor isolation for projects I encounter. I agree that such a feature may be useful for some domains/projects, but I'd classify it different than this bullet topic.

I think LanguageIntegratedQueryProject solves all this problems. --unknown

I'd be interested to see an example, but I'm not sure that's a useful response to such a wide range of issues. --JesseMillikan

I'm weary of Microsoft though. I've been burned by them one too many. They subtely but determinedly lore you and others into their proprietary extensions and before you know it, you are married to Bill Gates yet again. And it still creates too big of a wall between "application" and "SQL".

Regarding Alternatives

Note that TopsQueryLanguage is a fantasy language, existing only on "paper", so to speak. A variety of efforts -- in various stages of implementation -- to create a RelationalLanguage that replaces SQL, and/or properly implements the RelationalModel, are found in the Projects section at http://thethirdmanifesto.com
Math is "fantasy" and should be ignored by your reasoning. More on the "lacking implementation" debate can be found at LearningWithoutImplementation and HowOtherQueryLanguagesAddressSqlFlawsDiscussion. Also, I put some complaints about TutorialDee near the bottom of TqlDesignGoals. This topic is not the place to compare the merits of non-SQL query languages.
Math and TutorialDee (as implemented in the RelProject) are both usable right now. TopsQueryLanguage isn't.
- Moved discussion to ExperimentalQueryLanguageDiscussion
Tutorial D is Grade C in my opinion. We can do better. I give SQL a C- and TD a C+, to be more explicit. That's not enough overcome the switching cost of switching ships. It's almost like selling a Yugo to buy a Pinto.
Your "grade evaluation" opinion of SQL and Tutorial Dee, by the way, is worth precisely the price we've paid for it, which is equal to the amount of attention it deserves.
The feeling's mutual. I explained my general assessment elsewhere.

More alternatives?

DuroProject?
Alphora D4 (limited to .Net though)

In DatabaseAbstractInterfaceDiscussion, influenced from ColdFusion, I've been kicking around a "section-based" syntax that may fit SQL well. Here's an example: -t

 query name="qry", foo=7, bar=flip, zog="trag",
       flig="nab", grog=firb:
   SELECT * FROM sample
   WHERE X=47 AND Y=$thing
 noResult:
   PRINT "You see this if no rows returned"
 colHead auto=yes:   // column headings
 eachRow rowvar="r":
   PRINT "Name: " || name || ", Rank: " || qry.currow.rank  // short and long name
   // Alt: PRINT "Name: $name, Rank: ${qry.currow.rank}"
   // Alt2: PRINT "Name: $r.name, Rank: ${r.rank}"  (uses rowvar)
   // Note: Brackets in ${...} are optionally used to prevent 
   //       ambiguity between var names and literals.
 footer:
   PRINT "End of list. Count: " || qry.rowCount
 error log=no:
   ? "Bleep happened: " || qry.errMsg   // ? is short for PRINT
 end query

I would normally add spacing between sections, but this wiki has a space bug. ( http://www.mckenna3d.com/cms/index.php?option=com_content&view=article&id=6:space-bug&catid=5:2d&Itemid=3 )

The order of the sections wouldn't matter such that "noResult" could be put at the bottom if we wanted. An "eachColumn" section is an optional idea to also consider so we can loop for each column within each row. We may also want an "eachHeadColumn".

Query info and results would go into a "map tree" structure. Example:

qry.text // text of the query, usually sql qry.textExpanded // sql with variable substitutions (if any) qry.rowcount // number of result rows qry.errorMsg qry.1.columnA // "columnA" on first row (see caveat) qry[1].columnA // same thing, longer format useful for variable-based map indexing. qry.currow.columnA // columnA of current row qry.dbType.columnA // Database type of columnA (such as VARCHAR).

Note that drivers/options that return cursor-based results (one row at a time) will not necessarily have position-indexable rows such that "qry.1.columnA" may not exist.

I realize that languages such as Ruby may be able to create a DomainSpecificLanguage kind of like this, but it would probably either be punctuation-happy or produce cryptic error messages for many syntax errors. Hard-wired DSL syntax tends to (potentially) produce friendlier syntax or structural error messages, whereas a Ruby error message would require knowing Ruby's syntax to understand.

I agree that with enough programming you might be able to make Rudy give meaningful error messages, but you are essentially inventing an interpreter and doing almost as much work as would be done making an interpreter written in C, which improves portability and speed over Ruby.

CategoryDatabase, CategoryProgrammingLanguage, CategoryQueryLanguage

JanuaryTwelve