HOWTO - Sashipa development

SQL views: How to display data from an SQL view.

You wish to make your software working with an SQL view.

In the environment part

In the Sashipa language, an SQL view is a schemaTable. But it won't be detected by Sashipa2Melba, so it has to be manually added in the Sashipa code. An example:

  <schemaTable name='tblViewTest' readOnly='yes'>
    <physicalName>VIEW_TEST</physicalName>
    <singularName>VIEW TEST</singularName>
    <pluralName>VIEW TEST</pluralName>
    <schemaColumnSet>
    <schemaColumn name='tblViewTest_PersonId' type='integer' notNull='no'>
      <physicalName>PersonId</physicalName>
      <singularName>Person Id</singularName>
      <guiConfigSchemaColumn letterCount='20' sort='asc' />
    </schemaColumn>
    <schemaColumn name='tblViewTest_Column2' type='integer' notNull='no'>
      <physicalName>Column2</physicalName>
      <singularName>Column 2</singularName>
      <guiConfigSchemaColumn letterCount='8' sort='desc' />
    </schemaColumn>
    </schemaColumnSet>
    <schemaFkSet>
     <schemaFk name='fk_tblViewTest_tblPerson' targetSchemaTable='tblPerson'>
        <schemaColumnRef schemaColumn='tblViewTest_PersonId' />
      </schemaFk>
    </schemaFkSet>
    <userKey>
      <userKeyColumn schemaColumn='tblViewTest_PersonId' visible='no' />
      <userKeyColumn schemaColumn='tblViewTest_Column2' />
    </userKey>
    <modifiedOn allSchemaTables='uid' />
  </schemaTable>

Here the schemaTable is declared as readOnly. It allows us to not define a primary key column for this table. Note: if you are sure that a column contains only unique values, then you can declare it as a primary key.

The element modifiedOn means that each time the software execute an UPDATE, INSERT or DELETE query for any table, then all data about the schemaTable 'tblViewTest' will be removed from the buffer.

The GUI of this example needs a foreign key definition for this table, that references the primary key of a SQL table Person.

Now this schemaTable can be used like any other schemaTable, except that each form that works with it has to be read-only.

In the GUI part

All usual forms can be defined for this schemaTable. They just have to be read-only. But for this example we just declare a listForm that will be placed in some screen.

  <listForm db='dbTest' delete='no'>
    <title>Liste de la vue</title>
    ... bounds ...
    <schemaTableRef schemaTable='tblViewTest' />
    <instanceColumnList>
      <instanceColumn schemaColumn='tblViewTest_Column2' />
    </instanceColumnList>
    <doubleClicScreen screen='SCtblPerson'>
      <filterWithInstanceFk schemaFk='fk_tblViewTest_tblPerson' />
    </doubleClicScreen>
  </listForm>

Remark: the attribute delete is used in order to desactivate deleting from the listForm. The displayable columns of our schemaTable are used like usually.

By double-clicking on a row, the user'll open the card of a real SQL table (here SCtblPerson). This behaviour is described in the HOWTO on listForms. The foreign key fk_tblViewTest_tblPerson has been declared to allow to open the card of another schemaTable, from a listForm of this view.

Top