Tql Data Alteration Operators

Data Alteration Operations

r = save(table [, name]) - Save a virtual table to an actual table. This would roughly match Microsoft dialect's SQL's INTO clause. Actual table name would be same as virtual table if no "name" parameter given.
r = insert(table, columnTable)
r = update(table, columnTable)
r = replace(table, columnTable)
r = export(table, fileName, 'formatType') // formats: CSV,TAB,XML,HTML,FIXED,FLIRT
r = import(fileName, 'formatType') // results go to r
r = delete(table, expression) // delete rows matching expression criteria. r is number of rows deleted.
r = drop(table) // remove entire table
r = create(tableName, table) // table is a DataDictionary

They use a Column Table (TqlColumnTable) with "col" being the column name and "expr" being value or expression. "Replace" is influenced from MySql's command of the same name. It updates a row if the primary key already exists, otherwise it inserts a new row. Note that one can use these to populate a virtual table also. [These may not be needed because Calc and Union can do the same thing. Pondering in progress. Return results still being worked out.]

Note that since SmeQl is designed to co-exist with existing RDBMS brands and vendors, not replace them, alteration operators are of secondary importance. The majority of production queries are read-only. Contrast this against RelProject, which wants to distance itself from existing RDBMS because of the (heated) "bag issue".

Are these operations available for all tables? In other words, will these operations be available to tables that are results of queries? What if they are grouped? Maybe, you might want to separate the concept of table and view.

Well, that is the dilemma. As mentioned above, Union and Calc can do such to virtual/intermediate tables. If you "alter" a virtual table, then nothing of lasting consequence happens anyhow. It is like changing a locally-scoped variable in a function: it dissappears when the function is done. If you do nothing with it, it matters not (other than wasting code or CPU).

It may be useful to include SAVE operation(s) at the end of a query for debugging. Being at the end, it's easy to delete or comment them out when we are done with them. This is especially where the named references come in handy.

  // debugging example 1
  x = calc(stuff, foo=7)
  y = filter(x, bar=99)
  z = join(x, y, a.id=b.id)
  save(y, "test_x")
  save(z, "test_y")

Note that by doing such, one may lose potential optimization steps that may otherwise not require creating entire tables in working (temporary) RAM and disk buffers.

For practical purposes, virtual table-space is considered somewhat separate from actual table-space. However, actual table space is used for input tables if there is no matching table in virtual table space. Perhaps some kind of name-space resolution should be introduced, such as "databaseX.tableY" or "databaseX::tableY".

For now, it's safe practice to only use them as the final/outer query expressions. Otherwise we "break" the rules of FunctionalProgramming within a given query expression. There is kind of an impedance mismatch between the "state world" (persistent tables) and the functional world that may require some less-than-ideal compromises or confusing rules. -t

Data Definition Language

I propose that schemas be altered by changing "system" schema tables instead of having dedicated DDL syntax. Tables are the best place anyhow to keep schema info in a RDBMS. See HowOtherQueryLanguagesAddressSqlFlaws for more on this. For advanced use, a declarative programming may be needed to add complex triggers. SmeQl's goal is not to be a full-blown application language and will thus assume that hooks to imperative application languages be made available. A few additional commands may be needed such as a conditional "Stop" statement so that flexible textual restore (crash repair) scripts can be built. -t

  r = stopIf(expression)

"Replace" does not affect any non-mentioned columns of existing rows. This may vary from similar operations from other DB vendors.

CategoryTql, TqlRoadmap