put_="view"

<do action_="sql" put_="view" schema_="[SCHEMA]" objname_="[TABLE]" data_="{...}"/> updates a table with the specified properties.

Description

put_="view" updates the table properties specified in data_, and only those properties. The caller must be an admin of the schema [SCHEMA].

A magic table is a kind of SQL view, and it looks to the user (and to client software such as Power BI and Tableau) like a regular SQL view with the usual metadata. However, a SQL query on a magic table is treated differently. The definition of the view can specify that certain parts of the query, such as conditions in the FROM and WHERE clauses, are to be "pushed down" as parameters into the view. The magic table is defined using 1010data Macro Language in the xml_ attribute, or in the xml key of the data_ attribute of <do action_="sql" put_="view">. The effect of the magic table is an object that looks like a simple table to SQL clients such as BI tools, but that can encapsulate all of the behind-the-scenes power of 1010data.

Syntax

<dynamic>
    <do action_="sql" put_="view"
     schema_="[SCHEMA]"     
     objname_="[TABLE]"
     reorder_="[COLUMN1] [COLUMN2]...[COLUMNn]"
     xml_="[XML]"
     names_="labels"|"names"
     magic_="{...}"
     data_="{...}"/>
</dynamic>

Attributes for put_="view"

schema_
The schema where the view to update is located.
objname_
The table to update.
reorder_
A list of all the column names in the table, listed in the desired order. Comma-separated column names are accepted, but risky in case the names contain commas.
xml_
Changes the source code of the view to Macro Language. Source code may be specified as text or as a list of XML values.
names_
An optional argument that may be used to specify that the result of the MDB query should be imported into SQL using MDB column names instead of column labels ("labels" is the default value).
magic_
A dictionary that contains magic table functionality. The dictionary contains one or more of the following keys.
pushdown_aggregation
Specifies pushdown aggregation mode. Valid values are 1 (enable pushdown aggregation) or 0.
pushdown_orderby
Valid values are 1 and 0.
pushdown_where
A list of column names to push down as parameters into the SQL WHERE clause.

The empty string '' turns off pushdown of WHERE clauses.

pushdown_select
A list of column names to push down as parameters into the SQL SELECT clause.

The empty string '' turns off pushdown of SELECT targets.

Note: If pushdown_aggregation="1", then pushdown_select may not be used.
magic_fill
Specifies whether columns in the view schema that are not present in the result set returned by the XML query should be added automatically. Possible values are 0 (do not add) or 1 (add automatically).

(Available as of version 18.34)

data_
The dictionary specified in data_ is the same format as returned by get_="table" and may contain any of the following keys.
path
The 1010data pathname of the MDB table or query underlying the SQL table.
depth
A number that approximates the number of rows in the table for query optimization purposes. It does not automatically follow changes in the underlying table.
segs
A number that approximates the number of segments in the table.
columns
A package with columns containing metadata for each column in the table.
keys
A package with one column (columns) containing information about column groups that constitute keys in the table.
fkeys
A package with three columns (relation, foreign, local) containing information about foreign key relationships in the table.
seginfo
A package with two columns (columns, type) containing information about the segmentation (segby, sortseg) features of the table.
xml
A list of XML values where you can specify Macro Language source code, including code for magic tables.

(Available as of version 18.34)

names
Used to specify that SQL names should be derived from MDB column names instead of column labels (labels is the default value).

Example

The following example shows how to add a magic table to a schema. The query generates a date-by-hour cross tabulation of average temperature on weather data. You can then perform SQL queries on the magic table, just as you would any other table.
Note: Substitute your own schema name for "myschema".
<do>
  
  <setv name="xml">
    <quote>
      <switch>
        <case when_="{pkg_.contains(@sql_select;'year')}">
          <directory folder="pub.demo.weather"/>
          <sel value="beginswith(path;'pub.demo.weather.hourly')"/>
          <willbe name="year" value="strdrop(path;'';23)"/>
          <colord cols="year"/>
        </case>
        <case when_="{pkg_.contains(@sql_select;'state')}">
          <base table="pub.demo.weather.stations"/>
          <tabu breaks="state">
            <break col="state" relabel="state"/>
          </tabu>
        </case>
        <else>
          <let year="90" state="">
            <if test="{pkg_.contains(@sql_where;'year')}">
              <set year="{if(@sql_where.year>2000;@sql_where.year-2000;
                  @sql_where.year>1900;@sql_where.year-1900;@sql_where.year)}"/>
            </if>
            <base table="pub.demo.weather.hourly{@year}"/>
            <link table2="pub.demo.weather.stations" col="id"/>
            <if test="{pkg_.contains(@sql_where;'state')}">
              <set state="{@sql_where.state}"/>
              <sel value="state={qv(@state)}"/>
            </if>
            <amend name="temp" value="if(between(temp;-50;50);temp;na)"/>
            <tabu breaks="date" cbreaks="hour">
              <tcol source="temp" fun="avg"/>
            </tabu>
            <willbe name="year" value="{qv(@$year)}"/>
            <willbe name="state" value="{qv(@$state)}"/>
          </let>
        </else>
      </switch>
    </quote>
  </setv>
  
  <do action_="sql" analyze_="view" xml_="{@xml}" names_="labels" 
      vars_="{sql_where:{:},sql_select:{:}}" value_="@c"/>

  <do action_="sql" add_="view" schema_="myschema" objname_="magictable"/>

  <do action_="sql" put_="view" schema_="myschema" objname_="magictable" data_="{@c}"
      magic_="{pushdown_where:{'state','year'},pushdown_select:{'state','year'}}"/>

  <do action_="sql" reload_="catalog"/>

</do>
Once you add the magic table to the schema, you can perform SQL queries on the magic table, such as the following:
SELECT DISTINCT state FROM myschema.magictable;

SELECT DISTINCT year FROM myschema.magictable;

SELECT * FROM myschema.magictable WHERE state='CA';