Example for automatic link cell editors

Contains useful source code for specific use cases.

Example for automatic link cell editors

Postby Support@SIB » Mon Jan 16, 2012 2:39 pm

Our showcase has an example for automatic link cell editors. Here you find the source code and our database script.

The client code:

Code: Select all
/*
 * Copyright 2009 SIB Visions GmbH
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 *
 * History
 *
 * 27.11.2009 - [HM] - creation
 * 22.02.2010 - [JR] - changed column names for auto storages
 */
package com.sibvisions.apps.showcase.frames;

import java.io.ByteArrayOutputStream;
import java.io.IOException;

import javax.rad.genui.UIDimension;
import javax.rad.genui.UIInsets;
import javax.rad.genui.celleditor.UIDateCellEditor;
import javax.rad.genui.celleditor.UIImageViewer;
import javax.rad.genui.celleditor.UINumberCellEditor;
import javax.rad.genui.component.UIButton;
import javax.rad.genui.component.UILabel;
import javax.rad.genui.container.UIGroupPanel;
import javax.rad.genui.container.UIPanel;
import javax.rad.genui.container.UISplitPanel;
import javax.rad.genui.control.UIEditor;
import javax.rad.genui.layout.UIBorderLayout;
import javax.rad.genui.layout.UIFormLayout;
import javax.rad.io.IFileHandle;
import javax.rad.model.ColumnDefinition;
import javax.rad.model.ColumnView;
import javax.rad.model.ModelException;
import javax.rad.model.RowDefinition;
import javax.rad.model.condition.ICondition;
import javax.rad.model.condition.LikeIgnoreCase;
import javax.rad.model.datatype.StringDataType;
import javax.rad.model.reference.ReferenceDefinition;

import com.sibvisions.apps.showcase.Showcase;
import com.sibvisions.apps.showcase.components.NavigationTable;
import com.sibvisions.apps.showcase.components.SourceAccessFrame;
import com.sibvisions.rad.model.mem.DataRow;
import com.sibvisions.rad.model.remote.RemoteDataBook;
import com.sibvisions.rad.model.remote.RemoteDataSource;
import com.sibvisions.util.type.FileUtil;
import com.sibvisions.util.type.ImageUtil;

/**
 * The <code>ContactsAutoFrame</code> shows contacts, their detail information
 * and all educations. The automatic link celleditors will be detected automatically. It shows
 * how less code a developer has to write. You can compare the LoC with the
 * {@link ContactsFrame} which handles automatic link celleditors definition manually.
 *
 * @author Martin Handsteiner
 */
public class ContactsAutoFrame extends SourceAccessFrame
                               implements IDataScreen
{
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   // Class members
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   /** the default image when no image was found. */
   private static final String NO_IMAGE = "/com/sibvisions/apps/showcase/images/nobody.gif";
   
   
   /** the DataSource for fetching table data. */
   private RemoteDataSource dataSource = new RemoteDataSource();
   
   /** storage for contacts. */
   private RemoteDataBook rdbContacts = new RemoteDataBook();
   /** storage for contacts educations. */
   private RemoteDataBook rdbContEduc = new RemoteDataBook();
   
   /** search row. */
   private DataRow drSearch = null;
   
   /** the frames layout. */
   private UIBorderLayout blThis = new UIBorderLayout();
   /** the split between contacts and details. */
   private UISplitPanel splitMain = new UISplitPanel();
   /** the navigator for contacts. */
   private NavigationTable navContacts;
   /** the navigagor for showing educations. */
   private NavigationTable navContEdu;
   
   /** the layout for details. */
   private UIFormLayout flDetails = new UIFormLayout();
   /** the details. */
   private UIPanel panDetails = new UIPanel();
   /** the details. */
   private UIGroupPanel gpanDedails = new UIGroupPanel();
   /** the details. */
   private UIGroupPanel gpanEducations = new UIGroupPanel();

   /** contacts layout. */
   private UIBorderLayout blContacts = new UIBorderLayout();
   /** contacts panel. */
   private UIPanel panContacts = new UIPanel();
   /** search panel. */
   private UIPanel panSearch = new UIPanel();
   
   /** Label. */
   private UILabel lblSalutation = new UILabel();
   /** Label. */
   private UILabel lblAcademicTitle = new UILabel();
   /** Label. */
   private UILabel lblFirstName = new UILabel();
   /** Label. */
   private UILabel lblLastName = new UILabel();
   /** Label. */
   private UILabel lblStreet = new UILabel();
   /** Label. */
   private UILabel lblNr = new UILabel();
   /** Label. */
   private UILabel lblZip = new UILabel();
   /** Label. */
   private UILabel lblTown = new UILabel();
   /** Label. */
   private UILabel lblCountry = new UILabel();
   /** Label. */
   private UILabel lblBirthday = new UILabel();
   /** Label. */
   private UILabel lblSocialSecurityNr = new UILabel();
   /** Label. */
   private UILabel lblHealthInsurance = new UILabel();
   /** Label. */
   private UILabel lblFilename = new UILabel();
   /** labelSuchen. */
   private UILabel lblSearch = new UILabel();
   
   /** Editor. */
   private UIEditor edtSalutation = new UIEditor();
   /** Editor. */
   private UIEditor edtAcademicTitle = new UIEditor();
   /** Editor. */
   private UIEditor edtFirstName = new UIEditor();
   /** Editor. */
   private UIEditor edtLastName = new UIEditor();
   /** Editor. */
   private UIEditor edtStreet = new UIEditor();
   /** Editor. */
   private UIEditor editNr = new UIEditor();
   /** Editor. */
   private UIEditor edtZip = new UIEditor();
   /** Editor. */
   private UIEditor edtTown = new UIEditor();
   /** Editor. */
   private UIEditor edtCountry = new UIEditor();
   /** Editor. */
   private UIEditor edtBirthday = new UIEditor();
   /** Editor. */
   private UIEditor edtSocialSecurityNr = new UIEditor();
   /** Editor. */
   private UIEditor edtHealthInsurance = new UIEditor();
   /** Editor. */
   private UIEditor edtFilename = new UIEditor();
   /** editSuchen. */
   private UIEditor edtSearch = new UIEditor();

   /** contact image. */
   private UIEditor icoImage = new UIEditor();
   /** load image button. */
   private UIButton butLoadImage = new UIButton();

   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   // Initialization
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   /**
    * Constructs a new instance of <code>ContactsFrame</code>.
    *
    * @param pApplication the application.
    * @throws Throwable if the initialization throws an error
    */
   public ContactsAutoFrame(Showcase pApplication) throws Throwable
   {
      super(pApplication, "com.sibvisions.apps.showcase.frames.ContactsAuto");
      
      initializeModel();
      initializeUI();
   }
   
   /**
    * Initializes the model.
    *
    * @throws Throwable if the initialization throws an error
    */
   private void initializeModel() throws Throwable
   {
      dataSource.setConnection(getConnection());
      dataSource.open();
      
      rdbContacts.setDataSource(dataSource);
      rdbContacts.setName("contacts");
      rdbContacts.open();
      
      //set same labels as in details panel
      rdbContacts.getRowDefinition().getColumnDefinition("ACTI_ACADEMIC_TITLE").setLabel("Academic title");
      rdbContacts.getRowDefinition().getColumnDefinition("FIRSTNAME").setLabel("First name");
      rdbContacts.getRowDefinition().getColumnDefinition("LASTNAME").setLabel("Last name");
      rdbContacts.getRowDefinition().getColumnDefinition("ZIP").setLabel("ZIP");
      rdbContacts.getRowDefinition().getColumnDefinition("BIRTHDAY").setLabel("DoB");
      rdbContacts.getRowDefinition().getColumnDefinition("SOCIALSECNR").setLabel("Social security nr");
      rdbContacts.getRowDefinition().getColumnDefinition("HEIN_HEALTH_INSURANCE").setLabel("Health insurance");
      
      rdbContEduc.setDataSource(dataSource);
      rdbContEduc.setName("contEduc");
      rdbContEduc.setMasterReference(new ReferenceDefinition(new String[] {"CONT_ID"}, rdbContacts, new String[] {"ID"}));
      rdbContEduc.open();
      
      rdbContEduc.getRowDefinition().setColumnView(null, new ColumnView("EDUC_EDUCATION"));

      UIImageViewer imageViewer = new UIImageViewer();
      imageViewer.setDefaultImageName(NO_IMAGE);

      rdbContacts.getRowDefinition().getColumnDefinition("FILENAME").setReadOnly(true);
      rdbContacts.getRowDefinition().getColumnDefinition("IMAGE").getDataType().setCellEditor(imageViewer);

      rdbContacts.getRowDefinition().getColumnDefinition("SOCIALSECNR").getDataType().setCellEditor(new UINumberCellEditor("0000"));
      rdbContacts.getRowDefinition().getColumnDefinition("BIRTHDAY").getDataType().setCellEditor(new UIDateCellEditor("dd.MM.yyyy"));
      
      RowDefinition definition = new RowDefinition();
      definition.addColumnDefinition(new ColumnDefinition("SEARCH", new StringDataType()));
      
      drSearch = new DataRow(definition);
      drSearch.eventValuesChanged().addListener(this, "doFilter");
   }
   
   /**
    * Initializes the UI.
    *
    * @throws Throwable if the initialization throws an error
    */
   private void initializeUI() throws Throwable
   {
      lblSearch.setText("Search");
      edtSearch.setDataRow(drSearch);
      edtSearch.setColumnName("SEARCH");

      UIFormLayout layoutSearch = new UIFormLayout();
      
      panSearch.setLayout(layoutSearch);
      panSearch.add(lblSearch, layoutSearch.getConstraints(0, 0));
      panSearch.add(edtSearch, layoutSearch.getConstraints(1, 0, -1, 0));
      
      navContacts = new NavigationTable(getApplication().getLauncher(), getConnection(), rdbContacts);
      navContacts.getTable().setAutoResize(false);
      
      panContacts.setLayout(blContacts);
      panContacts.add(panSearch, UIBorderLayout.NORTH);
      panContacts.add(navContacts, UIBorderLayout.CENTER);
      
      navContEdu = new NavigationTable(getApplication().getLauncher(), getConnection(), rdbContEduc);
      navContEdu.getTable().setPreferredSize(new UIDimension(150, 150));
      navContEdu.eventNewDetail().addListener(this, "doNewEducations");
      
      icoImage.setPreferredSize(new UIDimension(75, 75));
      icoImage.setDataRow(rdbContacts);
      icoImage.setColumnName("IMAGE");
      
      lblSalutation.setText("Salutation");
      lblAcademicTitle.setText("Academic title");
      lblFirstName.setText("First name");
      lblLastName.setText("Last name");
      lblStreet.setText("Street");
      lblNr.setText("Nr");
      lblZip.setText("ZIP");
      lblTown.setText("Town");
      lblCountry.setText("Country");
      lblBirthday.setText("DoB");
      lblSocialSecurityNr.setText("Social security nr");
      lblHealthInsurance.setText("Health insurance");
      lblFilename.setText("Filename");
      
      edtSalutation.setDataRow(rdbContacts);
      edtSalutation.setColumnName("SALU_SALUTATION");
      edtSalutation.setPreferredSize(new UIDimension(75, 21));
      edtAcademicTitle.setDataRow(rdbContacts);
      edtAcademicTitle.setColumnName("ACTI_ACADEMIC_TITLE");
      edtAcademicTitle.setPreferredSize(new UIDimension(75, 21));
      edtFirstName.setDataRow(rdbContacts);
      edtFirstName.setColumnName("FIRSTNAME");
      edtLastName.setDataRow(rdbContacts);
      edtLastName.setColumnName("LASTNAME");
      edtStreet.setDataRow(rdbContacts);
      edtStreet.setColumnName("STREET");
      editNr.setDataRow(rdbContacts);
      editNr.setColumnName("NR");
      edtZip.setDataRow(rdbContacts);
      edtZip.setColumnName("ZIP");
      edtTown.setDataRow(rdbContacts);
      edtTown.setColumnName("TOWN");
      edtCountry.setDataRow(rdbContacts);
      edtCountry.setColumnName("CTRY_COUNTRY");
      edtBirthday.setDataRow(rdbContacts);
      edtBirthday.setColumnName("BIRTHDAY");
      edtSocialSecurityNr.setDataRow(rdbContacts);
      edtSocialSecurityNr.setColumnName("SOCIALSECNR");
      edtHealthInsurance.setDataRow(rdbContacts);
      edtHealthInsurance.setColumnName("HEIN_HEALTH_INSURANCE");
      edtFilename.setDataRow(rdbContacts);
      edtFilename.setColumnName("FILENAME");

      butLoadImage.setText("Upload");
      butLoadImage.eventAction().addListener(this, "doUpload");
      butLoadImage.setFocusable(false);
      
      flDetails.setMargins(new UIInsets(20, 20, 20, 20));
      
      gpanDedails.setText("Contact");
      
      gpanDedails.setLayout(flDetails);
      gpanDedails.add(icoImage, flDetails.getConstraints(0, 0, 1, 7));
      gpanDedails.add(butLoadImage, flDetails.getConstraints(0, 8));
      gpanDedails.add(edtFilename, flDetails.getConstraints(1, 8));
      
      flDetails.setHorizontalGap(15);
      gpanDedails.add(lblSalutation, flDetails.getConstraints(2, 0));
      flDetails.setHorizontalGap(5);
      gpanDedails.add(edtSalutation, flDetails.getConstraints(3, 0));
      gpanDedails.add(lblAcademicTitle, flDetails.getConstraints(2, 1));
      gpanDedails.add(edtAcademicTitle, flDetails.getConstraints(3, 1));
      gpanDedails.add(lblFirstName, flDetails.getConstraints(2, 2));
      gpanDedails.add(edtFirstName, flDetails.getConstraints(3, 2, -1, 2));
      gpanDedails.add(lblLastName, flDetails.getConstraints(2, 3));
      gpanDedails.add(edtLastName, flDetails.getConstraints(3, 3, -1, 3));
      
      gpanDedails.add(lblSocialSecurityNr, flDetails.getConstraints(2, 4));
      gpanDedails.add(edtSocialSecurityNr, flDetails.getConstraints(3, 4));
      gpanDedails.add(lblBirthday, flDetails.getConstraints(4, 4));
      gpanDedails.add(edtBirthday, flDetails.getConstraints(5, 4, -1, 4));
      
      gpanDedails.add(lblHealthInsurance, flDetails.getConstraints(2, 5));
      gpanDedails.add(edtHealthInsurance, flDetails.getConstraints(3, 5, -1, 5));

      gpanDedails.add(lblStreet, flDetails.getConstraints(2, 6));
      gpanDedails.add(edtStreet, flDetails.getConstraints(3, 6, -3, 6));
      gpanDedails.add(lblNr, flDetails.getConstraints(-2, 6));
      gpanDedails.add(editNr, flDetails.getConstraints(-1, 6));
      gpanDedails.add(lblZip, flDetails.getConstraints(2, 7));
      gpanDedails.add(edtZip, flDetails.getConstraints(3, 7));
      gpanDedails.add(lblTown, flDetails.getConstraints(4, 7));
      gpanDedails.add(edtTown, flDetails.getConstraints(5, 7, -1, 7));

      UIFormLayout layoutSchulung = new UIFormLayout();

      gpanEducations.setText("Schooling");
      gpanEducations.setLayout(layoutSchulung);
      gpanEducations.add(navContEdu, layoutSchulung.getConstraints(0, 0, -1, -1));
      
      UIFormLayout layout = new UIFormLayout();
      
      panDetails.setLayout(layout);
      panDetails.add(gpanDedails, layout.getConstraints(0, 0, -1, 0));
      panDetails.add(gpanEducations, layout.getConstraints(0, 1, -1, -1));
      
      splitMain.setDividerPosition(250);
      splitMain.setDividerAlignment(UISplitPanel.DIVIDER_TOP_LEFT);
      splitMain.setFirstComponent(panContacts);
      splitMain.setSecondComponent(panDetails);
      
      setTitle("Automatic Link Editors");
      setLayout(blThis);
      add(splitMain, UIBorderLayout.CENTER);
   }

   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   // Interface implementation
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   /**
    * {@inheritDoc}
    */
   public void save() throws ModelException
   {
      dataSource.saveAllDataBooks();
   }
   
   /**
    * {@inheritDoc}
    */
   public void reload() throws ModelException
   {
      dataSource.reloadAllDataBooks();
   }
   
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   // User-defined methods
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   /**
    * Saves the image to the contact.
    *
    * @param pFileHandle the file.
    * @throws Throwable if an error occures.
    */
   public void storeFile(IFileHandle pFileHandle) throws Throwable
   {
      String sFormat = FileUtil.getExtension(pFileHandle.getFileName().toLowerCase());
      
      if ("png".equals(sFormat)
         || "jpg".equals(sFormat)
         || "gif".equals(sFormat))
      {
         ByteArrayOutputStream stream = new ByteArrayOutputStream();
         
         ImageUtil.createScaledImage(pFileHandle.getInputStream(),
                              140,
                              185,
                                   true,
                                   stream,
                                   sFormat);
         
         stream.close();
         
         rdbContacts.setValue("FILENAME", pFileHandle.getFileName());
         rdbContacts.setValue("IMAGE", stream.toByteArray());

         try
         {
            rdbContacts.saveSelectedRow();
         }
         catch (Exception pException)
         {
            // Silent Save of current row.
         }
      }
      else
      {
         throw new IOException("Image format '" + sFormat + "' not supported. Use 'png', 'jpg' or 'gif'!");
      }

   }

   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   // Actions
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   /**
    * Starts the image upload.
    *
    * @throws Throwable if an error occures.
    */
   public void doUpload() throws Throwable
   {
      if (rdbContacts.getSelectedRow() >= 0)
      {
         getApplication().getLauncher().getFileHandle(this, "storeFile");
      }
   }

   /**
    * Opens the educations frame.
    *
    * @throws Throwable if the educations frame can not be opened
    */
   public void doNewEducations() throws Throwable
   {
      getApplication().openFrame(EducationsFrame.class);
   }
   
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   // Interface implementation
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   /**
    * Searches the contacts with the search text.
    *
    * @throws ModelException if the search fails
    */
   public void doFilter() throws ModelException
   {
      String suche = (String)drSearch.getValue("SEARCH");
      if (suche == null)
      {
         rdbContacts.setFilter(null);
      }
      else
      {
         ICondition filter = new LikeIgnoreCase("FIRSTNAME", "*" + suche + "*").or(
                        new LikeIgnoreCase("LASTNAME", "*" + suche + "*").or(
                        new LikeIgnoreCase("STREET", "*" + suche + "*").or(
                        new LikeIgnoreCase("TOWN", "*" + suche + "*"))));
         rdbContacts.setFilter(filter);
      }
   }
   
   
}   // ContactsAutoFrame


The server code:

Code: Select all
/*
 * Copyright 2009 SIB Visions GmbH
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 *
 * History
 *
 * 27.11.2009 - [HM] - creation
 */
package com.sibvisions.apps.showcase.frames;

import com.sibvisions.apps.showcase.Session;
import com.sibvisions.rad.persist.jdbc.DBStorage;

/**
 * The <code>ContactsAuto</code> class is the life-cycle object for <code>ContactsAutoFrame</code>.
 *
 * @author Martin Handsteiner
 */
public class ContactsAuto extends Session
{
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   // Members
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   /** storage for contacts. */
   private DBStorage dbsContacts = null;

   /** Storage for contacts educations. */
   private DBStorage dbsContEduc = null;

   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   // User-defined methods
   //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   /**
    * Returns the contacts storage.
    *
    * @return the Contacts storage
    * @throws Exception if the initialization throws an error
    */
   public DBStorage getContacts() throws Exception
   {
      if (dbsContacts == null)
      {
         dbsContacts = new DBStorage();
         dbsContacts.setDBAccess(getDBAccess());
         dbsContacts.setWritebackTable("CONTACTS");
         dbsContacts.setAutoLinkReference(true);
         dbsContacts.open();      
      }
      
      return dbsContacts;
   }

   /**
    * Returns the contacts educations storage.
    *
    * @return the contacts storage
    * @throws Exception if the initialization throws an error
    */
   public DBStorage getContEduc() throws Exception
   {
      if (dbsContEduc == null)
      {
         dbsContEduc = new DBStorage();
         dbsContEduc.setDBAccess(getDBAccess());
         dbsContEduc.setWritebackTable("CONT_EDUC");
         dbsContEduc.setAutoLinkReference(true);         
         dbsContEduc.open();
      }
      
      return dbsContEduc;
   }   
   
}   // ContactsAuto


The database script:

Code: Select all
-------------------------------------------------------------------------------
-- Tables
-------------------------------------------------------------------------------

create table SALUTATIONS
(
  ID         INTEGER IDENTITY,
  SALUTATION VARCHAR(200),
  constraint SALU_UK unique (SALUTATION)
)

create table ACADEMICTITLES
(
  ID             INTEGER IDENTITY,
  ACADEMIC_TITLE VARCHAR(200) not null,
  constraint ACTI_UK unique (ACADEMIC_TITLE)
)

create table COUNTRIES
(
  ID      INTEGER IDENTITY,
  COUNTRY VARCHAR(200) not null,
  EU      CHAR(1) default 'N' not null,
  constraint CTRY_UK unique (COUNTRY)
)

create table STATES
(
  ID      INTEGER IDENTITY,
  CTRY_ID INTEGER not null,
  STATE   VARCHAR(200) not null,
  constraint STAT_UK unique (STATE),
  constraint STAT_CTRY_ID_FK foreign key (CTRY_ID) references COUNTRIES (ID)
)

create table DISTRICTS
(
  ID       INTEGER IDENTITY,
  STAT_ID  INTEGER not null,
  DISTRICT VARCHAR(200),
  constraint DIST_UK unique (DISTRICT),
  constraint DIST_STAT_ID_FK foreign key (STAT_ID) references STATES (ID)
)

create table HEALTHINSURANCES
(
  ID               INTEGER IDENTITY,
  HEALTH_INSURANCE VARCHAR(200) not null,
  constraint HEIN_UK unique (HEALTH_INSURANCE)
)

create table EDUCATIONS
(
  ID        INTEGER IDENTITY,
  EDUCATION VARCHAR(200),
  constraint EDUC_UK unique (EDUCATION)
)

create table CONTACTS
(
  ID            INTEGER IDENTITY,
  SALU_ID       INTEGER,
  ACTI_ID       INTEGER,
  FIRSTNAME     VARCHAR(200) not null,
  LASTNAME      VARCHAR(200) not null,
  STREET        VARCHAR(200),
  NR            VARCHAR(200),
  ZIP           VARCHAR(4),
  TOWN          VARCHAR(200),
  CTRY_ID       INTEGER,
  BIRTHDAY      DATE,
  SOCIALSECNR   DECIMAL(4),
  HEIN_ID       INTEGER,
  FILENAME      VARCHAR(200),
  IMAGE         BINARY,
  constraint CONT_SALU_ID_FK foreign key (SALU_ID) references SALUTATIONS (ID),
  constraint CONT_CTRY_ID_FK foreign key (CTRY_ID) references COUNTRIES (ID),
  constraint CONT_ACTI_ID_FK foreign key (ACTI_ID) references ACADEMICTITLES (ID),
  constraint CONT_HEIN_ID_FK foreign key (HEIN_ID) references HEALTHINSURANCES (ID)
)

create table CONT_EDUC
(
  ID      INTEGER IDENTITY,
  CONT_ID INTEGER not null,
  EDUC_ID INTEGER not null,
  constraint COED_UK unique (CONT_ID, EDUC_ID),
  constraint COED_CONT_ID_FK foreign key (CONT_ID) references CONTACTS (ID),
  constraint COED_EDUC_ID_FK foreign key (EDUC_ID) references EDUCATIONS (ID)
)

create table FOLDERS
(
  ID        INTEGER IDENTITY,
  FOLDER    VARCHAR(256) not null,
  FOLD_ID   INTEGER,
  constraint FOLD_UK unique (FOLD_ID, FOLDER),
  constraint FOLD_FOLD_ID_FK foreign key (FOLD_ID) references FOLDERS (ID) ON DELETE CASCADE
)

create table FILES
(
  ID         INTEGER IDENTITY,
  TYPE       VARCHAR(50) not null,
  FILENAME   VARCHAR(256) not null,
  FILESIZE   INTEGER not null,
  CREATED    DATE not null,
  CREATED_BY VARCHAR(64),
  CHANGED    DATE not null,
  CHANGED_BY VARCHAR(64),
  FOLD_ID    INTEGER,
  constraint FILE_UK unique (FOLD_ID, FILENAME),
  constraint FILE_FOLD_ID_FK foreign key (FOLD_ID) references FOLDERS (ID) ON DELETE CASCADE
)

-------------------------------------------------------------------------------
-- Views
-------------------------------------------------------------------------------

CREATE VIEW V_COUNTRIES AS
select c.ID
      ,c.COUNTRY
      ,c.EU
  from COUNTRIES c
 order by c.COUNTRY

CREATE VIEW V_STATES AS
select s.ID
      ,s.CTRY_ID
      ,s.STATE
  from STATES s
 order by s.STATE
 
CREATE VIEW V_DISTRICTS AS
select d.ID
      ,d.STAT_ID
      ,d.DISTRICT
  from DISTRICTS d
 order by d.DISTRICT

CREATE VIEW V_EDUCATIONS AS
select e.ID
      ,e.EDUCATION
  from EDUCATIONS e
 order by e.EDUCATION

CREATE VIEW V_CONTACTS AS
select C.ID
      ,C.SALU_ID
      ,(select S.SALUTATION from SALUTATIONS S where S.ID = C.SALU_ID) SALUTATION
      ,C.ACTI_ID
      ,(select T.ACADEMIC_TITLE from ACADEMICTITLES T where T.ID = C.ACTI_ID) ACADEMIC_TITLE
      ,C.FIRSTNAME
      ,C.LASTNAME
      ,C.STREET
      ,C.NR
      ,C.ZIP
      ,C.TOWN
      ,C.CTRY_ID
      ,(select CO.COUNTRY from COUNTRIES CO where CO.ID = C.CTRY_ID) COUNTRY
      ,C.BIRTHDAY
      ,C.SOCIALSECNR
      ,C.HEIN_ID
      ,(select I.HEALTH_INSURANCE from HEALTHINSURANCES I where I.ID = C.HEIN_ID) HEALTH_INSURANCE
      ,C.FILENAME
      ,C.IMAGE
  from CONTACTS C
   
CREATE VIEW V_CONT_EDUC AS
select CE.ID
      ,CE.CONT_ID
      ,CE.EDUC_ID
      ,(select E.EDUCATION from EDUCATIONS E where E.ID = CE.EDUC_ID) EDUCATION
  from CONT_EDUC CE
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm

Return to Code snippets