Minimal Table

What are the minimal features of a "table"?

These are general "ranked" feature-levels for suggested light-duty table implementations. The tighter the implementation constraints, the less deep one should go down the list. Ideally, one should be able to replace a light-duty table with a RDBMS table/query/view if more powerful features are eventually needed with little or no code change.

Add the following, and you start to venture into full DBMS territory.

Other features which don't seem to fit an easy ranking:

The biggest implementation DiscontinuitySpike is probably the introduction of Boolean expressions. That requires implementing expression parsing.


A far more generic definition of a table can be given thusly:

That pretty much sums up a table. We can embellish tables by adding the concept of CandidateKeys? (all tables, of course, have at least one CandidateKey consisting of all the attributes in the header - otherwise it would be a bag and not a set). We might embellish it in other ways, such as table invariants.

But nowhere in the definition does it mention a "database". A RelationalDatabase obviously is a database composed of relations; but relations can exist outside of a database.

The RelationalAlgebra (doing joins, projects, selects, unions, intersections, etc.) applies to tables; a database isn't required for that.

Things like transactions, persistence, ACID semantics, security, concurrency, etc., are database concerns, on the other hand - but are orthogonal to the RelationalModel. You can have a NavigationalDatabase that provides ACID and security, etc. You probably wouldn't want one for your business data as that's an established AntiPattern; but chances are the software that provides directory services for a large-scale network has a NavigationalDatabase (or something resembling it) under the hood.
Minimal Query Language

I have been considering a way to produce basic filtering queries without having to parse a complex query and expression language. This is to keep the query system small and easy to implement in constrained devices. One approach is to have a query table with the follow columns:

A query is simply multiple sets (rows) of these AND'ed together and would represent the form:

  op1(a,b) and op2(c,d) and op3(e,f) and op4(g,h) ....
To create 'OR' conditions, one can use operator "isIn". Here is a set of possible operators:

Generally the "list" in the isIn operator would be considered a single string operator of the format: "'aa','bb','cc','etc.'". A bit of parsing may be required to separate the elements internally. Or perhaps the number of operands can be considered open-ended to accommodate. That is an implementation detail left to the implementer. I would suggest making it case-insensitive to avoid the need for case conversion operators.

Of course this is not as powerful as full-blown expressions, but the point here is too keep the internals simple. It is a way to get 80 percent of the power of a WHERE clause with only 10 percent of the implementation size.

You make it sound like boolean expressions are difficult or something, it's not nearly as difficult as you make it sound, there's really no reason to not include full blown expressions.

One may have to do some additional filtering in their processing loop if the query sub-language cannot do everything. But, this is usually the case with queries anyhow. The following expression is shown in SQL-like syntax, prefix, its table form, and API form:

  SQL-based:

state in ('CA','NY','TX') and wages < 3000 and name LIKE '%rog%'

Prefix (except for AND):

isIn(state, 'CA','NY','TX') and lessThan(wages, 3000) and contains(name,'rog')

Proper prefix EssExpression style

(and (isIn 'state' 'CA' 'NY' 'TX') (lessThan 'wages' 3000) (contains 'name' 'rog'))

Table:

operator column operand -------- ------- -------------- isIn state 'CA','NY','TX' lessThan wages 3000 contains name 'rog'

OO-API:

result = Query.new() .isIn('state','CA','NY','TX') .lessThan('wages', 3000) .contains('name', 'rog') .execute();

Which form is chosen is up to the implementer. Since it is a sub-set of fuller query languages, such as SQL, existing queries would still work if a larger database system was later used, assuming drivers or translators were created. Also note that some QueryByExample processors use a similar arrangement.

         :   -- WilliamUnderwood


ColdFusionLanguage has a built-in table type (it calls it a "query" object) which can use a limited set of SQL. However, it is buggy in that it appears the owning company adapted a typed database engine, probably from another company, into the more or less untyped Cold-Fusion language. This has odd side-effects that make them unusable in some circumstances. Maybe they will fix it in future editions.

They went the other way and added column types to the CF-native Query object in CF 7. A big pain, because an application I wrote made extensive use of hand-crafted query objects, sometimes with "magic values" to represent queries that either needed to be saved to the DB or tht hadn't yet been loaded from there. New typing system caused all sorts of conflicts when I merged the hand-crafted and real queries. -- ChrisMellon?

Too bad they didn't take a cue from SqLite and make typing optional but available. But it sounds like the change is better than what they had before: "content-based guessed types". It was nearly useless for internally-defined tables. For example, if a string "kind of" looked like a date, it would start treating the entire column as dates out of the blue. It changed its spots during run-time more often than a female in a furniture store.

I'm not sure the "optional but available" is the right wording. I think SqLite's typing is a joke, or a lie. It's not really optional, it is more like a pretend typing that doesn't really do anything, but it lies/pretends that it is there. How would you like it if the equality sign didn't really do equality checks, it was just optional, but available, for pretend sake. What the F**k.

Specific examples would be helpful.

It doesn't enforce integrity, and hence it is a fake joke bogus type system. It's like having stop signs all around town at the intersection, but no one actually pays attention to them, and no one cares if you go through them. It is a lie, a scam. The reason SqLite offers "optional" typing as people call it (which I reject) is to remain compatible with SQL syntax. If they removed the fake bogus typing from SqLite completely, since it doesn't do anything for people, then the SQL would be incompatible. It's a bogus backward compatible thing. You can send SqLite some SQL, but it just ignores some of your SQL and pretends it doesn't matter. It would be like declaring an int in CeeLanguage and the compiler just ignores it and lets you turn that int into a string which is not an int. CeeLite? let's call it. Would that be an improvement over CeeLanguage, to have a Cee that ignores int? If so, why hasn't someone created such a wonderful language like CeeLight?. Typeless CeeLight?. There is ForthLanguage I guess.

Having optional types may indeed be added for compatibility purposes. It doesn't have to be there for "integrity". I'm not a fan of SqLite's style of dynamic typing, but probably not for the same reasons as you. Dynamic and "lite" typing is generally a nice fit for "lite" tools in my opinion, based on what they are typically used for. There's a time and place for both heavy typing and light typing. -t

Maybe one can use the native expression evaluator of the language rather than reinvent the wheel, if it has one. Just stick with a standard record/object/array naming convention.

  for aRow = eachRow(tableX) {
    matches = myQueryRow(aRow, "a.foo > a.bar + 3");
    saveMatches(matches)
  }
  ...
  function myQueryRow(a: array, expression: string) {
    if (evaluate(expression)) {
      return(a);
    } else {
      return(empty);
    }
  }


There was a recent proposal to add SQL-like constructs (but of course generalized so) to HaskellLanguage list comprehensions:

  http://research.microsoft.com/Users/simonpj/papers/list-comp/index.htm

This moves functional languages one more step into the directions of the relational paradigm (or rather makes clear, that relational can be fully subsumed by the functional paradigm).

Noe: Please move is post to a more appropriate place if you know of one (I'm not that well versed in all these TOPical pages).


Is a struct in CeeLanguage a minimal table or minimal row? I find in programming languages people reinvent minimal tables by using structs in Cee, and associative arrays in PHP, and lists along with plain arrays in other languages. With an array you iterate to access data, which is very error prone since array can go out of bounds - no such thing happens with relational queries. With associative arrays you have a nice data structure that you can access via a key, but still not as nice as a table that could act as a minitature temporary database inside your program.
See also: NimbleDatabase, AreTablesGeneralPurposeStructures, WhereAndAnd, MultiParadigmDatabase, EmulatingTables
CategoryLowEnd

EditText of this page (last edited February 10, 2012) or FindPage with title or text search