''Date your abstractions, don't marry them''

I agree that there is a lot of tediousness in typical SQL, HTML, and CrudScreen usage, but I tend to focus on smaller-scale frameworks for dealing with those rather than strive for The Ultimate Generic Wrapper. This is because the "wrappability" of SQL and string-based protocols varies widely. Thus, I don't even strive to wrap everything any more. I have been burned too many times by the EightyTwentyRule and LeakyAbstraction''''''s. Using helpers gives me the abstraction/simplification where it works, but allows me to abandon it where it does not. I am then not married to one or the other. It is true that I don't completely hide from the "string-ness", but at least I hide from the common repetition. I have yet to see a good pure wrapper from anybody else either, not just me. Thus, the failure of pure wrappers is probably due to it being a nearly impossible goal, an unrealistic abstraction idealism.

For example, for insert or update queries, I sometimes put the name-value pairs in a map (associative) array, and then have a utility function to turn that map into SQL clauses. The problem is knowing the field types in order to know when to include SQL quotes and/or how to validate the values. Maps only have two "columns", so one has to store field-type info somewhere else or create nested messes. The ideal would be a data dictionary table, but most languages sadly don't easily support these. Thus, a compromise is to use a function such as:

  addClause("ssn", ssn, CHAR)

  or perhaps:

  addCharClause("ssn", ssn)
[The PHP world solved this problem a while ago, by using the dynamic type of the value passed in. See PEAR DB's autoInsert and autoUpdate functions. I almost never write raw INSERT or UPDATE queries any more. -- JonathanTang]

* Dynamic type? Many don't use explicit types in PHP or can't rely on them always coming out how one expects. Plus, the DB's type and the PHP type may not match well. I find it better to tell the API how to treat it in SQL. Another thing is that one can combine such type/format checking with validation in general so that SQL creation and validation can be combined. This is why roll-your-own mini-API's may be better: it kills two birds with one stone. Each shop or app tends to have a certain flavor of validation "types" and conventions.

''where's the string, don't over simplify your example while bloating the OOP one, that's dishonest.''

* It is a "regional" (module-level) variable, and thus does not have to be passed in as a parameter. If you want to add it as a parameter or object in your version, be my guest. Related: ResponsibilityDrivenDesignConflictsWithYagni.

This appends the key-value pair to an SQL string clause. Typical usage would be in an Update, Insert, or query-by-example screen.

OOP fans might complain the string(s) being appended to are not "encapsulated" in an object. However, I have never had to append to more than one SQL statement at a time, and so don't see a need. Thus, I keep the syntax simple. If by chance I later needed such a feature, then I could add a "handle" of some type:

  addClause(myClause, "ssn", ssn, CHAR)
But, if you want or must use OOP, then you can have something simple like this:

  myUpdate''''''Clause = new Update''''''Clause().addClause("ssn", ssn, Sql''''''Types.Char)
Or perhaps:

  myUpdateClause.addCharClause("ssn", ssn)
However, that is more tedious than what we are trying to fix.

''Doesn't look more tedious to me, looks quite simple, and far safer than appending strings since an OOP version would likely build a syntax tree from it than can be cleanly turned into paramaterized sql automatically.''

* Please clarify

By using "lite" wrappers for SQL parts, one can still use more powerful SQL structures and features. I am not forced into an all-or-nothing decision about whether to use raw SQL or a wrapper. I use both as needed. I wrap the easy-to-wrap aspects of SQL and leave the rest alone. The wrappers help the repetitive parts without prohibiting fancier SQL usage. They are just that: helpers. 

''Strawman, you keep assuming that the OOP versions are somehow less capable than your wrappers, they aren't. OOP query interfaces don't stop you from writing sql when you need to and don't in any way prohibit you from writing fancy Sql, please stop spreading FUD, as someone who admitedly never uses OOP techniques or query interfaces, you are in no position to critique them.''

* This is more about how to create wrappers than it is about OOP versus functions. By the way, what is a "query interface"?

''A query interface is a wrapper for the most common cases, just like yours. To show an example from one of Fowler's books in a statically typed language...''
  Query query = new Query(Person.Class).addCriteria(Critera.greaterThan("numberOfDependants",numDependants));
''Quite flexible, generic, takes care of the common cases, and with the exception of the column name, compiler checked.''

I find those verbose. See ExpressionApiComplaints.

''No more verbose than your method.''

 string myClause = "Select * from Person";
 addGreaterClause(myClause, "numberOfDependants", numDependants, NUM)
If your goal is to completely hide from SQL because you think it is ugly or evil, well, you are on your own. There have been a lot of creative attempts to wrap SQL completely behind imperative API's, but the cure was worse than the desease in every case I saw. I don't think that can happen until SQL is replaced by a better relational language. The API's I've shown are generally to simplify the '''repetitious parts of SQL, not all of SQL'''. INSERT and UPDATE statements are the primary example, but can also help with QueryByExample. The main goal is to reduce repetition, not change one language into another.

[For typesafe SQL statement generation, see also TypeSafeJdbcWrapper.]

''Well, Ruby's ActiveRecord implementation, while it can be a pain on some rare occasions (especially when deviating from its conventions), does the abstraction fairly well. Not as versed in Java to know, but .NET on the other hand is, while better than the Vanilla, still a pain in the ass. I would be skeptic of reasons to implement a custom, from scratch wrapper for mainstream languages.'' -- LeoBighetti

* I am not sure ActiveRecord qualifies as a "helper". A helper needs these features:
** Another framework or application is not "married" to it. You could do without it without major headache. I could be wrong, but too much of R-on-R seems to assume Active Record.
** Can easily alter the code for a different app that may have different needs. A.R. seems to be a large blob of code in order to be generic and handle all kinds of contingencies. One-size-fits-all is anti-helper.

Nor would I create a function/method for every operator (">", ">=", "<", etc.) if I even bothered to wrap those to begin with. I would just have it as a parameter. They are more visual so we might as well use them (see LispLacksVisualCues).

-- top

''OOP let's you create any relational language you like, and use it natively in your language, it can be translated to Sql under the hood and on the fly. Why wait for something better when you can have it now?''

I would enjoy an example.

''See TutorialDee. A language, any language, is just an abstract syntax tree, and can easily be represented by objects, which do trees very well, thus you can make up any language you like, and use it, instead of Sql.''

Well, turning expressions or languages into API's is not something I am fond of, as described in ExpressionApiComplaints. But, I am talking about using SQL-based RDBMS here. I agree that SQL has messy syntax, and have even proposed my own relational language under TopsQueryLanguage, but SQL is not going away anytime soon. It is the QWERTY and COBOL of relational query languages.

But even TutorialDee or TopsQueryLanguage is far more readable as strings than as expression API's, at least to me. I think most developers would agree with me. But, this is probably a topic to be moved or finished in ExpressionApiComplaints.

----
'''HTML Example'''

A common pattern in web-forms (CrudScreen''''''s) is a bunch of input fields of the form:

    Name: [      ]
    Date: [      ]  format mm/dd/yyy
    Category: [(select category) |v]      // meant to be a drop-down list
    ....
I used to try to make a generic API for such, but found it suffers from problems of pure wrappers described above. Thus instead I use helper functions that "sandwich" the HTML form elements.

  formHeading("My Sample Form", formName)

  rowStart("Name")
  <input type=text name="myname">
  rowEnd()

  rowStart("Date")
  <input type=text name="mydate">
  rowEnd("format mm/dd/yyyy")

  rowStart("Category")
  <select name="mycat">
  ....
The rowStart and rowEnd functions format the HTML table cells. If by chance I need something fancier for a particular section or row, I simply use strait HTML and abondon the helper functions for that area. I am not obligated to them because I am not attempting to entirely wrap or hide HTML.

-- top

----

Here is the interface to an example HelperFunction I often use for SQL INSERT and UPDATE statements. It also does basic validation.

  function addSqlClause(
    map,  // associative array, see description below
    theValue,   // value of column
    fieldName,  // column name
    fieldType,  // column type (T=text,N=num,D=date)
    minLength,  // minimum length allowed
    fieldTitle  // title used in error message (if any).
    options
    ) {....}
Parameter "fieldTitle" defaults to "fieldName" if empty. 
"options" are words embedded in parenthesis for easy parsing. Example: "(required)(trunc)". Here, "(trunc)" means that the value will be truncated if larger than given length. Without it, an error is given if oversize.
The 'map' parameter is a by-reference associative array. It contains or will contain these entries:

* ''setsClause'' - A clause string used after the SET sql keyword.
* ''columnsClause'' - A list of column names used in the sql INSERT command.
* ''valuesClause'' - A list of column values used for the VALUES sql INSERT command.
* ''errorMsg'' - The error message of any problem found. It is blank if none found. A fancier version could return multiple error messages and perhaps have a warning-only level. But this is often overkill. 
* Bonus items include ''escapeSingleQuote'' and ''escapeDoubleQuote''.

Example usage in a JavaScript-like language:

  clauses = newArray[];
  addSqlClause(clauses, empID, 'empID', 'N', 4, 'Employee ID');
  addSqlClause(clauses, empName, 'empNm', 'T', 2, 'Employee Name');
  ...
  if (! isBlank(clauses['errorMsg'])) {
    stop("ERROR: " + clauses['errorMsg']);
  } else {
    execute_SQL("Insert into Emps (" + clauses['columnsClause'] +
            ") VALUES (" + clauses['valuesClause'] + ")");
  }
It also escapes any quotes in text to avoid syntax errors or hacked strings ("SQL Injection"). There are fancier variations that can be added; this is just to wet your appetite.

-- AnonymousDonor

----

Named parameters are really helpful with "helper" API's because more features will often be needed down the road and we don't want to make a huge and growing parameter list. However, many languages don't support them directly. Alternatives include objects with optional/default attributes, dictionary arrays of settings, and a "parsed" option parameter with a format such as, "foo=1,bar='blah',zig='zag'" etc. One simple alternative that I find fairly easy to parse in both SQL and imperative languages is parenthesis-based boolean options such as "(foo)(bar)(toprint)". It looks funny, but works. This approach is a reasonable "poor man's named parameters". However, for non-boolean options you still need to add formal parameters. But I find that about 70% of new options can be stated as Boolean. -- top

(I am contemplating moving the above to AlternativesToNamedParameters.)

----
'''Form Lists Example'''

Here is an example for web-based pull-down lists, radio buttons, or checkboxes:

  function optionList(<listtype>,<web-var-name>,<default>,<paired-list>) {...}
Example call:

  print(optionList("radio","locat","NW","SW|south-west|NW|north-west|NO|north..."));
Parameters:

* ''List Type'' - "radio", "pulldown", or "checkbox". This is the type of list. The first two provide mutually-exclusive options.
* ''Web Variable Name'' - The HTTP/HTML form variable name. Needed to generate HTML.
* ''Default'' - The default value. Leave blank for no default, in which case the first will be selected for mutually-exclusive lists.
* ''Paired Options List'' - Value-description alternating pairs separated by a pipe character. If the value and description are the same, then simply make them the same. I tried commas, but ran into string conflicts.

I also use a helper function somtimes to grab the value-description pair string from database tables. My early list helper functions always grabbed such lists from tables, but later found this to be PrematureAbstraction. Separating the two provides more options without framework marriage. It is the mix-and-match spirit of helpers.

The nice thing about this approach is that one can change the presentation method, such as pull-down to check-box, without rewriting HTML. I've found a need to do such fairly often upon getting UI feedback.

----

I've found that leaving the abstractions a bit "leaky" makes them far more flexible. For example, suppose I have a function/method for generating menu items with links:

  function menuItem(title, url, isNewWindow) ...
The "isNewWindow" parameter is a boolean flag that tells it whether to use the HTML 'target="_blank"' tag (open a new browser window). However, I realized that there are other kinds of targets, such as framing targets, and even other features of the link. I thus changed the mini-API function to this:

  function menuItem(title, url, otherAttribs) ...

  menuItem('Foo Blog', 'www.foo.com', 'target="_blank"');  // example usage
This puts the "otherAttribs" value into the A (anchor) tag as is. This offers more flexibility. It permits more attributes besides "target", such as JavaScript "onAction" kinds of hooks. It could perhaps be considered a case of BargainFutureProofing.
-----------------
'''Dealing with Deviations from Framework'''

Here is a typical pattern for using helpers. The "standard" report would be the most common case. However, often there are one-off deviations (or some value below our DuplicationRefactoringThreshold). Rather than make that one case an option of some kind, one does something like the customReportX() function. Use the highest-level existing components available that fit our needs, but make custom code for the different part(s), which is the left-side of the footer in this case. We are free to not use the framework components will don't want to. (OOP version not shown, but usually would look similar, depending on coding style used.)

 func stdReport(title, query, totals) {
   heading(title);
   middle(query);
   footer(totals);
 }
 func customReportX(title, query, totals) {
   heading(title);
   middle(query);
   // footer
   customLeftSideCodeGoesHere(); // differs from std. here
   footMiddle(totals);
   footRightSide();
 }
 func heading(title) {...}
 func middle(query) {...}
 func footer(totals) {
   footLeftSide();
   footMiddle(totals);
   footRightSide();
 }

As a general rule, if you can't find the proper abstraction at level N in StepwiseRefinement, then you still try to use as many abstractions as possible from level N + 1 of (usually) the same branch. 
--top
--------
Somebody listed TypeSafeJdbcWrapper in the "see also". May I ask why? It appears to be "wrappers instead of helpers".

''Maybe it's the type-safety fans trying to stir up controversy? Or is that paranoia on my part?''

[If it really is "wrappers instead of helpers" then you could move it to a "compare" section.  But it does have a place in the links section.]

------
'''Click-able Thumbnail Anecdote'''

Another anti-wrapper anecdote. A coworker wrote a web-based application that put content into "summary lists" on a web-page with a thumbnail (or icon) of standard size to the left, a title, and a brief synopsis for each "cell". It's a common design on the web. However, I noticed that clicking on the thumbnail didn't work. One had to click on the title instead (both image and title should be click-able). I pointed out this flaw, but he never fixed it.

A few months later I got a chance to see the actual code for a feasibility study, and now I understand why the thumbnail clicking didn't work. When I make app-specific HTML-generator libraries, I usually base them on strings. You input a string(s) and the output is string (of markup) . However, this particular app was using Microsoft's HTML wrapper library (DotNet). It's based on element types, not on strings. The type checking limits what can be fed into what as type-checking is designed to do to prevent stupid errors (along with type-ahead coding suggestions and auto-complete).

However, this was a problem because it made appending an HTML element conceptually different than embedding an element. Knowledge of how to append is not necessarily applicable to embedding. The thumbnail image needed to be embedded into a hyperlink element. I studied the API, but couldn't figure out how to do such. The convention used for text hyperlinks wasn't carried over to image hyperlinks (probably because images were their own objects while a link title was not treated as such because it's simpler.) With enough Googling around one can eventually find out how to do it, but the point is that something that is obvious and simple with string-based API's is not obvious via the type-centric API such that one has to do special research. It wasn't just non-obvious to me, but also non-obvious to the original developer, otherwise it would have worked.

The API also made it hard to "escape out" into string-mode to do it via strings. Once you started using their library, you were pretty much married to it for smaller elements within: you couldn't mix and match tag strings and HTML objects within each other, at least not in a clear-cut way. 

Further, my string API's can work in just about any programming language (after translating into the native language). I don't have to relearn a different API from for say Java to IBM to Microsoft, etc. (Agreed, this may be an advantage trade-off between "language dedicated" programmers or shops and mixed-language shop programmers.)

Further 2, if an extended or newer version of HTML came out, Microsoft's API wouldn't be ready to accept new attributes, elements, and combos. The type-centric approach would prevent this because it would "not know about" the new elements. The lack of type-checking in my string API's allows me to add a tag set called "FOOGLERP" inside a hyperlink without complaining because a string is a string to it. But dedicated type-centric API's would error out, saying "FOOGLERP" is not a recognized element for this context" or the like. All the markup elements and their nesting rules have to be pre-known at library compile time at Microsoft.

----
EditWarDamageNotes
----
See also: HelperIsaCodeSmell, SeparateDomainFromPresentation, SubLanguage, LeakyAbstraction, GenericBusinessFrameworkUnobtainable, FrameworksShouldAutomateNotHide, ReuseGranularity
----
CategoryAbstraction, CategoryInterface, CategoryReuse, CategoryFramework, CategoryWebDesign