Wiki code for Databases

Hide Line numbers
1: 1 Databases, Data Widgets and Forms
2: 
3: <div style="background-color:#e5f0fe;
4: padding-top:2px;
5: margin:20px 0px 0px 20px;
6: padding:1px 20px 1px 0px; 
7: width:50%;">
8: #toc("2" "6" "false")
9: </div>
10: 
11: In order to access a database from your WaveMaker Studio application, you must first create a new data model or import an existing data model into the project for that application. When you import the database, you specify database connection information and then WaveMaker Studio creates a Data Model for you. Both the database connection and the Data Model itself are editable after the import.
12: 
13: WaveMaker Studio does not automatically synchronize the Data Model with the database on which it is based. If 
14: the database changes (for example, if you add a new table to the database) you must re-import the database. Similarly, if you make changes to the Data Model and you want your database to include those changes, then you must re-export the Data Model back to the database.
15: 
16: 1.1 Basic Database Concepts
17: 
18: WaveMaker makes it easy to create web-based forms that are connected to an underlying database.
19: 
20: 1.1.1 What is a Database?
21: A database stores information used in an application as a set of tables. Each table is similar to Excel spreadsheet and consists of rows and columns.
22: 
23: Create a new data model or import an existing data model in WaveMaker by selecting the Database Service option from the Services menu. This brings up the New Data Model window, which gives a choice of creating a new data model or importing an existing data model.
24: 
25: *Sample Data Model: Customers and Orders*\\
26: The Customer/Order data model will be used throughout this section to illustrate basic database concepts. The following example shows two tables, customers and orders:\\
27: {image:tables.gif}\\
28: 
29: *Database Field*\\
30: A field in a database is the same as a column in Excel. When the data model is created, each field is defined to contain a certain kind of data, such as characters, integers or dates. In addition a field may be allowed to be blank (NULL allowed) or must always have a value (NULL not allowed). Finally, a field may be defined to have a default value. 
31: 
32: To create a new table in WaveMaker, select the Database icon in the Model palette and then press the green plus icon, then enter a name for the new table.
33: 
34: Each table that you import from the database is represented in the Data Model as a ~~LiveTable~~. You can edit LiveTables in the LiveTables editor ([LiveTables>DataObjectsintheDataModel#HLiveTables]). If you choose, you can export your changes back to the database. If the database changes (for example, a new table is added) then you need to re-import ([Importing a Database>ImportingaDatabase]).
35: 
36: 1.1.1 Primary Keys and Foreign Keys
37: In this Customer/Order data model, each Customer is identified by a unique CustId and each Order is identified by a unique OrderId. These unique identifiers are called Primary Keys.
38: 
39: \\{image:fkeys.gif}\\
40: 
41: In WaveMaker, to identify a column as being part of the primary key, select the Primary Key check box for that column.
42: 
43: Every table must have a primary key and each primary key must be unique. One examples of primary keys from the real world include using a social security numbers to uniquely identify a person.
44: 
45: Because primary keys are so common, data models often use primary key values that are automatically generated by the database.
46: 
47: *Foreign Keys*\\
48: Looking at the Customer/Order data model, notice that each order is related to a corresponding customer by the column, RelCust. For example, the first and second Orders were both placed by Customer with CustId = 1, and the third order was placed by Customer 2. 
49: 
50: Columns that indicate a relationship between two tables are called foreign keys. The RelCust column of the Order table is a foreign key to the CustId column of the Customer table.
51: 
52: To create a foreign key in WaveMaker, add the appropriate foreign key column to a table, then select the foreign key column and press the plus icon near the relationships editor.
53: 
54: 1.1.1 One-to-Many Relationships and One-to-One Relationships
55: In the Customer/Order data model, the customer table has a one-to-many relationship with the order table. This means that for each customer, there can be many related orders.
56: 
57: In the Customer/Order data model, the order table has a one-to-one relationship with the customer table. This means that for each order, there is exactly one related customer.
58: 
59: WaveMaker automatically understands data model relationship, making it easy to create forms that show related data. For example, dragging a relatedEditor widget into a Customer LiveForm makes it easy to display a grid of reated orders for a particular customer. Similarly, dragging a relatedEditor widget into an Orders LiveForm make it easy to display editable customer data along with an order form.
60: 
61: 1.1.1 Database Views and Queries
62: A view can join information from several tables together, for example adding the CustomerName field to the the Order information.
63: 
64: Create views in WaveMaker by selecting the LiveView option from the Services menu.  LiveViews are collections of related tables. They are similar to views in databases. Use LiveViews when you want to display columns from related tables in a single widget, such as a LiveForm. LiveViews cannot be exported back to the database. ([LiveViews>DataObjectsintheDataModel#HLiveViews])
65: 
66: A query returns information from a table or set of tables that matches particular criteria. For example, a query could return all Orders made before 12/10/09. Queries can also join information from several tables together (these are called Join queries).
67: 
68: Create queries in WaveMaker by selecting the Query option from the Services menu. In WaveMaker Studio database queries are treated as objects in the Data Model. You can create, edit and delete queries in the Queries editor. ([Queries>DataObjectsintheDataModel#HQueries])
69: 
70: 1.1 Importing Data Models Into WaveMaker
71: 
72: The fastest way to start working with WaveMaker is to import an existing data model into WaveMaker. Once the data model is imported, WaveMaker makes it very easy to create forms based on this data model to create, read, update and delete information from the database.
73: 
74: This section provides three approaches to importing a data model into WaveMaker:
75: * Import one of the sample data models that ship with WaveMaker
76: * Install the open source MySQL database, create a sample data model and then import it into WaveMaker
77: * Import an existing data model from another database 
78: 
79: 1.1.1 Import Sample Data Model
80: 
81: The easiest way to get started with WaveMaker is to use the bundled HSQLDB database and one of the sample data models: cmdb, hrdb and userdb.
82: 
83: HSQLDB is a single user database that is included primarily for demo and testing purposes. [For more information on HSQLDB, click here>http://hsqldb.org/web/hsqlDocsFrame.html]
84: 
85: The benefit of HSQLDB is that it stores its data as part of a WaveMaker project. Using HSQLDB for a project makes it very easy to export that project to a zip file, then send that zip file to someone else. All the recipient has to do is unzip the project and they will automatically have all the data they need to run the project.
86: 
87: To implement the sample cmdb database into a project, follow these steps:
88: 1. Create a new WaveMaker project
89: 1. Choose the *Database Service* option in the *Services* menu\\{image:databaseservices.gif}\\
90: 1. The New Data Model dialog appears with the Import Data Model tab selected.\\{image:hsqlimport.gif}\\
91: 1. Set the Database System parameter to "HSQLDB"
92: 1. Set the File parameter to "cmdb"
93: 1. Leave the username = "sa" and the password blank
94: 1. Press the import button to import the data model
95: 1. Go the the palette and look under the Data section to see the Data Form Widgets that were created as part of the database import process. \\{image:dataform.gif}\\
96: 1. Drag the Customers widgets onto the canvas to see a master-detail form: a datagrid listing all the Customers in the database and associated form that shows detailed information for the currently selected Customer as well as the ability to create a new customer, update a customer or delete a customer.
97: 
98: *Note*: HSQLDB is meant primarily for development and testing purposes. It is not appropriate for multi-user or high transaction web applications.
99: 
100: 1.1.1 Install MySQL and Import Data Model
101: 
102: For novice developers who want a more scalable database than HSQLDB, MySQL is a good choice. 
103: 
104: Follow these steps to start using MySQL with WaveMaker:
105: 
106: 1. [Download the MySQL database here>http://dev.mysql.com/downloads/], choose the latest generally available (GA) release.
107: 1. Install the MySQL database on your computer
108: 1. The default login for the MySQL is username = root, password = root
109: 1. Run the MySQL Command Line Client application (this is included as part of the MySQL installation). At the Enter Password prompt, enter "root"
110: 1. Cut and paste the Sample MySQL Database DDL from below into the MySQL Command Line Client (right click in the  MySQL Command Line Client window, then select the paste option). Press return. MySQL should respond with the following message: "Query OK, 3 rows affected"
111: 1. Now start WaveMaker and create a new project.
112: 1. Choose the *Database Service* option in the *Services* menu\\{image:databaseservices.gif}\\
113: 1. The New Data Model dialog appears with the Import Data Model tab selected.\\{image:mysqlimport.gif}\\
114: 1. Set the Database System parameter to "MySQL"
115: 1. Set the Username parameter to "root" and Password parameter to "root"
116: 1. Set the Database parameter to "custdb"
117: 1. Press the import button to import the data model
118: 1. Go the the palette and look under the Data section to see the Data Form Widgets that were created as part of the database import process. \\{image:dataform2.gif}\\
119: 1. Drag the Customer widgets onto the canvas to see a master-detail form: a datagrid listing all the Customers in the database and associated form that shows detailed information for the currently selected Customer as well as the ability to create a new customer, update a customer or delete a customer.
120: 
121: *Sample MySQL Database DDL*
122: Copy and paste this code into the MySQL Command Line Client to create a sample database called "custdb".
123: {code}
124: create database if not exists `custdb`;
125: USE `custdb`;
126: /*Table structure for table `customer` */
127: DROP TABLE IF EXISTS `customer`;
128: CREATE TABLE `customer` (
129:   `CustId` int(11) NOT NULL auto_increment,
130:   `CustName` varchar(30) default NULL,
131:   PRIMARY KEY  (`CustId`)
132: ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
133: /*Data for the table `customer` */
134: insert  into `customer`(`CustId`,`CustName`) 
135: values (1,’ABC Books’),(2,’Border Books’);
136: /*Table structure for table `order` */
137: DROP TABLE IF EXISTS `order`;
138: CREATE TABLE `order` (
139:   `OrderId` int(11) NOT NULL auto_increment,
140:   `OrderDate` date NOT NULL,
141:   `RelCust` int(11) default NULL,
142:   PRIMARY KEY  (`OrderId`),
143:   KEY `FK_order` (`RelCust`),
144:   CONSTRAINT `FK_order` FOREIGN KEY (`RelCust`) 
145:      REFERENCES `customer` (`CustId`) 
146:      ON DELETE CASCADE ON UPDATE CASCADE
147: ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
148: /*Data for the table `order` */
149: insert  into `order`(`OrderId`,`OrderDate`,`RelCust`) 
150: values (1,’2009-11-14’,1),(2,’2009-12-02’,1),(3,’2009-12-14’,2);
151: /* End of DDL */
152: {code}
153: 
154: 1.1.1 Importing Existing Data Model
155: 
156: To use a database in WaveMaker, you first need to import that data model for that database into the WaveMaker project. To import a database, follow these steps:
157: 
158: 1. Choose the *Database Service* option in the *Services* menu\\{image:databaseservices.gif}\\
159: 1. The New Data Model dialog appears with the Import Data Model tab selected.\\{image:newdatamodel.gif|width=600}\\
160: 1. In the "Basic Options" section, select the Database System property from a list of options, such as MySQL and PostgreSQL.
161: 1. If the database is located on your computer, leave the Host property as "localhost". Otherwise, enter the hostname for the computer running the database.
162: 1. Do not change the Port number unless you are a database expert - the port number is usually set automatically when you select the Database System property.
163: 1. Enter the Username and Password properties to login to your database
164: 1. Enter the Database name - this is the name of the data model defined in your database
165: 1. In most cases, you should leave the Service Name property the same as the Database name
166: 1. Press the *Test Connection* button to test whether the parameters were entered correctly.
167: 1. Press the *Import* button to import the data schema. When the import is complete, the Data Model Editor shows you the tables defined in the schema that you have just created.
168: 1. Save the project.
169: 
170: *Note:* In most cases you will not need to worry about Advanced Settings. If you need them, see [Advanced Options>#HAdvancedOptions].
171: 
172: When you import the database, WaveMaker Studio creates a Data Model for you ([What is in the Data Model>#HBasicDatabaseConcepts]). Once the database has been imported, here are some things that you can do within WaveMaker:
173: * *Use Data Form Widgets*: for every table in the imported data model, there is a corresponding widget added to the Data section of the palette. Drag this widget onto the WaveMaker canvas to create custom grids and forms to create, read, update and delete database information. 
174: * *Create data model views*: use the WaveMaker View Editor to combine information from several related tables into a data view that can be displayed in datagrids and forms.
175: * *Create queries*: use the WaveMaker Query Editor to build custom database queries for custom reports and dashboards.
176: * *Create LiveData variables*: LiveData variables hold data from views and queries so that they can be connected to datagrids and forms.
177: 
178: WaveMaker Studio does not automatically synchronize the Data Model and the database. If the database changes (for example, a new table is added) then you need to re-import ([Re-Importing a Database>#HReImportingaDatabase]). Similarly, if you make changes to the Data Model that you want to see reflected in the database, then you need to export the Data Model ([Exporting a Database>#HCreatingNewData ModelsinWaveMaker]).
179: 
180: 1.1.1 Basic Settings For Database Import
181: 
182: The "Settings" area of the Import Database screen allows you to choose what type of database you want to import and configure the basic required settings.
183: 
184: The most important properties 
185: * *Host*: The network host for the database. The default value is localhost, meaning that the database is located on the same computer that WaveMaker studio is running on.
186: * *Port*: The port number for the database. Typically this is set automatically when the database type is selected and should not be changed.
187: * *Database*: The name of the database. Within a database system, a database name is similar to a directory name in the operating system - it is a way of organizing a set of tables.
188: 
189: When you choose your database type the Database drop-down menu, the configuration for that type of database appear. 
190: 
191: Some of the configuration fields are different for the different database types. Here are the specific differences:
192: 
193: 1. *HSQLDB*: HSQLDB is a file-based database. WaveMaker expects the HSQLDB file to be located in the project directory .~\webapproot~\data~\ with the name "dbname.script"
194: 1*. File property: enter the name of the HSQLDB file located in the .~\webapproot~\data~\ directory. If the name of the file is foo.script, just enter the file name "foo"
195: 1. *SQL Server*: SQL Server has a special property to specify the database instance. Normally, this should be set to the value "SQLExpress" if you are using SQL Server Express Edition.
196: 1*. Instance name property: by default, this should be set to "SQLExpress" 
197: 1. *Oracle*: Oracle has a System ID property that is one of the connection parameters. By default, the SID and the database name are the same.
198: 1*. SID: set this to the System ID for the Oracle database. By default, this is the same as the database name.
199: 
200: 1.1.1 Advanced Options
201: 
202: For advanced users, WaveMaker Studio provides some advanced configuration options.The following options are available:
203: 
204: * *Connection URL*: When you import a database using the standard configuration options on the Import Database screen, WaveMaker Studio creates a JDBC URL for you. This URL is shown here in the Connection URL field. If you know what you are doing and want to change this JDBC URL, you can do it here.
205: * *Java Package*: WaveMaker Studio generates Java classes for you when you import a database. If you want a different Java package name than the default name we generate, type it in here.
206: * *Table Filter*: By default WaveMaker Studio imports all the tables in the database. If you want to import only a subset of the tables, type in a comma-delimited list of regular expressions here.
207: * *Schema Filter*: When you are importing a database that supports schemas, WaveMaker Studio imports only the tables for the default schema. If you want to import tables from other schemas, add the schema names in this field.
208: * *Driver Class*: JDBC driver class name. WaveMaker Studio creates a JAR file for each database that uses a sepcifed dirver. If you want to use a different JAR file for connecting, specify it here (the JAR file must be in the Application Server’s class path).
209: 
210: 1.1 Creating New Data Models in WaveMaker
211: 
212: WaveMaker studio can be used to create a new data model. *Note*: this is an advanced feature. For novice users, it is best to create data models directly in the database using tools like [SQLYog>http://www.webyog.com/en/downloads.php] and then import the data model into WaveMaker.
213: 
214: Create a new data model in WaveMaker by selecting the Database Service option from the Services menu. This brings up the New Data Model window, which gives a choice of creating a new data model or importing an existing data model.
215: 
216: In creating a new data model, the steps are:
217: 1. Choose the *Database Service* option in the *Services* menu\\{image:databaseservices.gif}\\
218: 1. The New Data Model dialog appears with the Import Data Model tab selected.\\{image:createdatamodel.gif}\\
219: 1. *Create tables*: for each table add columns and identify the primary key
220: 1. *Create relationships*: for each relationship, add foreign keys and define the relationship
221: 1. *Save the data model*: press the diskette icon from the data model editor
222: 1. *Export the data model*: press the wrench icon from the data model editor to bring up the Database Connection Settings window and press the "Export" button.\\{image:wrench.gif}
223: 
224: 1.1.1 Changing Database Connection Settings
225: 
226: Whenever you import a new WaveMaker project, such as a sample application, it is important to update the database connection settings to work with your local database. To do this, follow these steps:
227: 
228: 1. Select the database to export from the Model palette. Next, select the wrench icon to bring up the Database Connection Settings window.\\{image:wrench.gif}
229: 1. Set the Database Connection properties required for your database, such as username and password.
230: 1. Press the "Test Connection" button at the bottom of the dialog to test that the database connection properties are valid.
231: 1. Press the "Save" button to save the new connection settings
232: 
233: 1.1.1 Exporting a Data Model
234: 
235: *Note*: exporting a database deletes any existing data in the database tables. Do not use WaveMaker to update the data model if you want to preserve the data in the tables.
236: 
237: The Table Editor allows you to change table definitions within the WaveMaker project. To export these changes to the database, follow these steps:
238: 
239: 1. Select the database to export from the Model palette. Next, select the wrench icon to bring up the Database Connection Settings window.\\{image:wrench.gif}
240: 1. Set the Database Connection properties required for your database, such as username and password.
241: 1. Press the "Test Connection" button at the bottom of the dialog to test that the database connection properties are valid.
242: 1. Click the Export button at the bottom of the dialog. An export confirmation dialog appears that displays the SQL syntax for this export. 
243: 1. Click *OK*.
244: 1. Any errors encountered in creating the database will be reported.
245: 1. Click *Close* in the Database Connections dialog.
246: 
247: *Exporting to MySQL*\\
248: When exporting to MySQL, the Database property in the Database Connections dialog translates to the MySQL schema name. Enter a name for the database that will contain all the tables defined in the schema.
249: 
250: *Exporting to Postgres*\\
251: When exporting to Postgres, the Database property in the Database Connections dialog translates to the Postgres schema. A good default choice is to set the Database property to "public".
252: 
253: 1.1.1 Deleting a DataModel
254: 
255: To delete a data model from a project:
256: 1. Select the data model to be deleted
257: 1. Use the delete button in the toolbar 
258: 
259: {image:DModelDelete.PNG}
260: 
261: 1.1 Changing Database Schema
262: 
263: It is common to change the underlying database schema during a project. There are two approaches to handle data schema changes: re-importing the data model and manually changing the schema in WaveMaker.
264: 
265: 1.1.1 Re-Importing a Database
266: 
267: The recommended approach for managing data model changes with WaveMaker is to use a database management tool such as PHPMyAdmin or SQLYog. 
268: 
269: If a data model changes after it has been imported, then it should be re-imported. Re-importing a data model updates all of the table definitions in the project. Existing views are not changed, however if they reference tables or columns that have changed, you might get errors at runtime.
270: 
271: Note that WaveMaker does not support re-importing a data model once you have defined custom HQL queries. This is a limitation of the Hibernate DDL import utility. The work-around is to copy your queries to a text editor and then delete them, re-import the data schema and re-create the queries.
272: 
273: To re-import, follow these steps:
274: 1. Make changes to the database schema using your choice of database administration tool
275: 1. If you have defined custom HQL queries, copy them into a text editor and delete them from the project (the Hibernate re-import facility will not work if you have queries defined)
276: 1. Select the database to re-import from the Model palette. Next, select the wrench icon to bring up the Database Connection Settings window.\\{image:wrench.gif}
277: 1. Set the Database Connection properties required for your database, such as username and password.
278: 1. Press the Test button at the bottom of the dialog to test that the database connection properties are valid.
279: 1. Click the Re-import button at the bottom of the dialog.
280: 1. Click the "OK" button.
281: 1. Recreate any custom HQL queries needed
282: 
283: 1.1.1 Changing the WaveMaker Data Schema Manually
284: 
285: It is often easier to manage data model changes by applying the changes twice: first using a database administration tool and next making the same changes within WaveMaker using the data model editor.
286: 
287: 1. Make the db schema changes using a db tool like SQLYog or PHPMyAdmin
288: 1.  Now open the WaveMaker project and use WaveMaker’s data model editor to make the same changes to each table. For relationship constraints, make the changes to the relationship definitions to the table which contains the foreign key - Wavemaker will automatically update the other side of the relationship.
289: 1. When you are done making the changes to the table, press the save button to save changes for that table. This will automatically regenerate the required Hibernate files.
290: 
291: 1.1 Editing Tables In WaveMaker 
292: 
293: The Table Editor in WaveMaker can be used to customize a data model in WaveMaker or to create an entirely new database schema.
294: * *Customizing an existing data model*: the data model editor can be used to create more understandable aliases for legacy table and column names (not that these changes are overwritten when the database is re-imported, so Views maybe a better solution).
295: * *Creating a new data model*: the data model editor can create new tables, columns and relationships. The new data model must be saved and then exported for the changes to take effect in the database.
296: 
297: When a database is imported into a project, WaveMaker Studio creates a Data Model tree that represents that database. To edit a table in the Data Model, follow these steps:
298: 
299: 1. Expand a table in the tree to see the columns for that table.\\{image:modeltree.gif}\\
300: 1. Select a table from the model Data Model tree brings up the table editor.\\{image:tableeditor.gif}\\
301: 
302: The right side of the Table editor has three main areas: "General", "Columns" and "Relationships". Each area allows the developer to view and edit a different aspect of the Table.
303: 
304: 1.1.1 Table Editor: General Properties
305: 
306: The "General" section of the WaveMaker Table Editor allows you to view and configure main properties of the LiveTable. In general, only the Entity Name property should be changed.
307: 
308: The four fields on the left of the screen are:
309: 
310: * *Package*: This shows the java package name for the Data Model and cannot be changed.
311: * *Schema*: this is the database schema property from the Database Connection Settings.
312: * *Catalog*: this is the database name property from the Database Connection Settings.
313: * *Table Name*: The value in this field is the name of the table in the database that this LiveTable references. 
314: * *Entity Name*: The value in this field is the name of the Table in WaveMaker. By default it is set to the same as the table name in the database but it can be changed to create an more "user friendly" name when working with legacy data schemas. Note that changes made to the Entity Name will be overwritten when you re-import the data schema, so it is often better to use Views to create project aliases for tables and column names.
315: 
316: The three check boxes on the right of the screen are all for retrieving values that were set by the database.
317: 
318: * *Dynamic Insert*: when the Dynamic Insert parameter is checked, the INSERT SQL statement that WaveMaker sends to the the database does not include empty fields.
319: * *Dynamic Update*: when Dynamic Update parameter is checked, the UPDATE SQL statement that WaveMaker sends to the database does not include empty fields.
320: * *Refresh Entity*: if checked, a SQL select  statement is run to fetch the row just inserted or updated to include any fields that are set by the underlying database (this is useful for TIMESTAMP columns set by the database)
321: 
322: *Note*: Dynamic Insert and Dynamic Update map directly to the Hibernate settings dynamic-insert and dynamic-update.
323: 
324: 1.1.1 Table Editor: Editing Columns
325: 
326: The "Columns" section of the WaveMaker Table Editor shows all the columns in the Table and the properties for each column. Add and delete columns using the add (+) and delete (-) buttons.
327: 
328: Changes made in this editor are not automatically made in the database that it represents. In order to change your database to reflect these changes, first save the changes by pressing the diskette icon, then export the data model Data Model ([Exporting a Data Model>#HExportingaDataModel]).
329: 
330: The following table explains the information that is displayed about each column. You can change any of these values and save your changes to the Data Model.
331: \\
332: {table}
333: Type|Description
334: Primary Key|A check box. Checked if the column is a primary key. 
335: Foreign Key|True or False value. True if the column is a foreign key, false if not. This column is set automatically when a relationship is defined.
336: Type|Data type of the column.
337: Not Null|A check box. Checked if the column can NOT hold a null value. If unchecked, the column value can be null.
338: Length|Maximum column length - used primarily for string values.
339: Precision|Refers to the maximum number of digits used by the selected (numeric) data type and the digits past the decimal point. For example, a precision of "10,2" specifies a data type with a maximum of 10 digits, 2 of which are to the right of the decimal point. 
340: Generator|If the column is a primary key, specifies the method of key generation ([Key Generation>#HPrimaryKeyGeneration]).
341: Params|If a generator is selected, specifies the parameters for that generator, if any.
342: {table}
343: 
344: *WaveMaker column data types*\\
345: {image:types.gif}
346: 
347: *Note*: for multi-column primary keys, it is sometimes useful to uncheck the primary key parameter for all but one of the primary key columns. This enables the table to be used in a LiveForm and also allows access to primary key columns that would otherwise not be accessible (hibernate does not allow access to composite primary key values).
348: 
349: 1.1.1 Primary Key Generation
350: 
351: For primary keys, it is common to use the database itself to generate unique key values.
352: * *For primary keys where the user will input a value*: set the generator parameter to "assigned." For example, this would work well if the primary key for a table were a user assigned value like social security number.
353: * *For primary keys where the database will automatically assign a value*: set the generator field to "identity." This is the default option for new tables and ensures that the database will take care of making each row have a unique identifier.
354: 
355: Here is a detailed description of the Generator parameter options:\\ 
356: {table}
357: Generator|Description \\ 
358: assigned|lets the application assign an identifier to the object before save() is called. This is the default strategy if no  element is specified.
359: identity \\ |supports identity columns in DB2, MySQL, MS SQL Server, Sybase andHypersonicSQL. The returned identifier is of type long, short or int. \\ 
360: sequence|uses a sequence in DB2, PostgreSQL, Oracle, SAP DB, McKoi or agenerator in Interbase. The returned identifier is of type long, shortor int
361: native \\ |picks identity, sequence or hilo depending upon the capabilities of the underlying database
362: {table}
363: If your Generator type requires an input parameter, enter it in the *Params* column next to the *Generator* column.
364: 
365: 1.1.1 Relationships and Foreign Keys
366: 
367: Relationships provide a way to connect data in one table with data in a nother table. 
368: The WaveMaker Table Editor shows the relationships for the selected Table. You can add and delete relationships here.\\
369: {image:relations.gif|width=600}\\
370: 
371: Changes you make in this editor are not automatically made in the database that it represents. In order to change your database to reflect these changes, you need to export your Data Model ([Exporting a Data Model>#HExportingaDataModel]).
372: 
373: To add a relationship, first create the two tables that will be related. Next determine which table will contain the foreign key. 
374: 
375: If two tables are related, such as Customers and Orders, the foreign key will need to go in the table that is on the "to-one" side of a relationship. Each Customer can have many related Orders, but each Order can have only one related Customer. Thus the foreign key should be added to the Order table and must have the same data type as the primary key of the Customer table.
376: 
377: The steps to create a relationship follow:
378: 1. Create the two related tables, each with a primary key
379: 1. In the table with the "to-one" side of the relationship, add a "foreign key" column of the same datatype as the primary key of the table with the "to-many" side of the relationship.
380: 1. Now select the foreign key column and click the "+" icon next to the Relationships section of the Table Editor
381: 1. Give the relationship a name such as relatedOrders
382: 1. Specify the table name of the related table, such as "Customer"
383: 1. Set the cascade actions parameter to "none" (the other options are for advanced users only).
384: 1. Save the data model by pressing the diskette icon after editing each table(remember to export the data model when all changes are complete).
385: 
386: 1.1 Views In WaveMaker
387: 
388: A View (also called a LiveView  in WaveMaker) is a collection of related Tables and their columns. Use Views when to combine data from several related tables. For example, a view could combine data from the customer table and the order table to show the customer name associated with each order.
389: 
390: 1.1.1 Creating a View
391: 
392: Create Views using the WaveMaker Views Editor. Views are implemented using WaveMaker’s underlying Hibernate component - they are not exported to the database.
393: 
394: A LiveView creates a new data type that can be used as the data type for a Live Variable and then bound to a datagrid or LiveForm. Each time a Data Form object is dragged off the palette and onto the canvas, a LiveView and LiveVariable are automatically created for that Data Form object.
395: 
396: To create a new View, follow these steps:
397: 1. Choose the *LiveView* option in the *Services* menu\\{image:databaseservices.gif}\\
398: 1. The New Live View dialog appears.\\{image:newliveview.gif}\\
399: 1. Select the Data Type (or Table) that will be the basis for this Live View. Always choose the Data Type which is on the to-one side of the relationship. For example, for a view that will include data from the Customer and Order tables, choose the Order table as the base table.
400: 1. The LiveView editor will appear. Expand the tree and select that attributes to include in the view. If you plan to use the LiveView in a LiveForm widget (e.g., to create and update database information), then you should select all the required fields.\\{image:orderview.gif}\\
401: 1. When you select a column, configuration options appear on the right side of the LiveViews editor. The following configuration options allow you to affect the default configuration for LiveForm and DataGrid widgets: 
402: 1*. *Caption*: Specify a default value for the caption property. Applies both LiveForms and DataGrids. 
403: 1*. *Order*: Specify the order in which this field should appear in the LiveForm or DataGrid. For example, if you type in a one (1) then the Editor for this field would be the first one in the LiveForm. The column for this field would be the first one in the DataGrid. 
404: 1*. *Type Hint*: Specify the default value of the display property for this Editor. The display property specifies the type of value you’re displaying. Applies to LiveForms and DataGrids. Note that some of the types in this menu are valid only for DataGrids and others are valid only for LiveForms. If you choose a Type Hint that is not valid for the field, your Type Hint is ignored. 
405: 1*. *AutoSize*: Specify that this column should be automatically sized. Applies only to DataGrids. *\\Width*: Specify a default value for the column’s width property. The width property defines the width of a column in a DataGrid. Applies only to DataGrids. 
406: 1*. *Required*: Specifies that a value is required in this field in order for the LiveForm to be submitted. Applies only to Editors in LiveForms.
407: 1. Save the LiveView by pressing the diskette icon.
408: 
409: The LiveView is now complete. Click the Preview checkbox to see a preview of the LiveView in a DataGrid.
410: 
411: *Note*: LiveViews cannot be exported back to the database. They reside only in the Data Model.
412: 
413: 1.1.1 Using a View
414: 
415: A LiveView defines a data type in WaveMaker. A LiveVariable is required to use that data type in an application either in a datagrid or a liveform.
416: 
417: Follow these steps to use a LiveView in an application:
418: 1. *Define a LiveView*
419: 1. *Create a LiveVariable*: choose the *LiveVariable* option in the *Insert* menu\\{image:livevar.gif}\\
420: 1. *Set the LiveSource property*: the LiveSource is the data type for this varibale, which must either be a table or LiveView. Select the appropriate data type from the drop down menu.
421: 1. *Set filter options*: each live variable can be filtered to only show results which match a certain data value. The following screenshot shows a live variable that will filter to only show orders for a particular date based on the input to a date editor widget. \\{image:filter.gif}\\
422: 1. *Set sort options*: each live variable can be sorted. The following screenshot shows a live variable that will sort the data in ascending order for the customer.custName field. \\{image:sort.gif}\\
423: 1. *Show LiveVariable data in a datagrid*: drag a datagid onto the canvas. Set its *dataSet* parameter to the name of the new LiveVariable. The datagrid will show the specified data using the captions and column ordering set in the LiveView as well as the filtering and sorting specified in the LiveVariable.
424: 1. *Connect the datagrid to a LiveForm*: drag a LiveForm widget onto the canvas.  Set its *dataSet* parameter to the datagrid.selected variable. Now the LiveForm will show the detailed information for the currently selected datagrid item. 
425: 1. *Show LiveVariable data in a LiveForm*: drag a LiveForm widget onto the canvas.  Set its *dataSet* parameter to the name of the new LiveVariable. The LiveForm will show detailed information for the first record from the database view.
426: 1. *Add data navigation to the LiveForm*: drag a DataNavigator widget into the LiveForm (note: the DataNavigator must be contained inside the LiveForm container to work). Now a user can navigate through a LiveView dataset record by record.
427: 
428: 1.1 Queries in WaveMaker
429: 
430: WaveMaker queries provide a way to retrieve selected information from the database for reporting and dashboards. WaveMaker has a query editor that provides a visual way to create and test queries. 
431: 
432: WaveMaker queries are implemented using the Hibernate Query Language (HQL), so they are stored as part of a WaveMaker project. This also means they do not need to be exported to the database.
433: 
434: [For an in-depth Hibernate tutorial using WaveMaker, click here>http://dev.wavemaker.com/wiki/bin/Dev/HqlTutorial]
435: 
436: 1.1.1 Defining a Query
437: 
438: To create a new View, follow these steps:
439: 1. Choose the *Query* option in the *Services* menu.\\{image:databaseservices.gif}\\
440: 1. The Query Editor appears.\\{image:query.gif|width=500}\\
441: 1. Enter a name for the query.
442: 1. In the query definition, enter an HQL query.
443: 1. In the query parameters section, enter a parameter for each named parameter in the query:
444: 1*. Enter a name which matches the parameter in the query, for example firstdate.
445: 1*. Enter a data type which matches the type for the parameter in the query, for example date
446: 1*. Press the ’+’ button to add the parameter to the parameter list.
447: 1*. Preface the input parameter in the query with a colon, for example :firstdate.
448: 1. Save the query by pressing the diskette icon. A Hibernate query will now be generated and any errors displayed in a popup window.  
449: 
450: 1.1.1 Using List Inputs
451: 
452: Inputs can be a list of values. Check the list check box for the bind parameter to use this option.\\
453: For example if our select is: \\
454: \\
455: SELECT name \\
456: FROM customer \\
457: WHERE name IN (:param) \\
458: \\
459: Specifying the Test parameter value as \[john, jane\] will return all customers where the name is john OR jane. \\
460: \\
461: 1.1.1 Testing the Query
462: 
463: To test a query, follow these steps:
464: 1. Create and save a query
465: 1. From the Query editor, enter values for each query parameter
466: 1. In the screenshot example, two parameters are required: firstdate and lastdate. Note that date parameters must be entered using a unix time format with three added trailing zeros. [Convert a date to a unix time here>http://www.onlineconversion.com/unix_time.htm] then add three trailing zeros to the resulting number. This is only required by the query test editor, not in the application itself!
467: 1. Press the lightning bolt icon to run the query and see the results, which are shown in the screenshot above.
468: 
469: 1.1.1 Using a Query
470: 
471: A Query defines a data service in WaveMaker. A Service Variable is required to use that query in an application either in a datagrid or a chart widget.
472: 
473: Follow these steps to use a Query in an application:
474: 1. *Define a Query*
475: 1. *Create a  Service Variable*: choose the *ServiceVariable* option in the *Insert* menu\\{image:livevar.gif}\\
476: 1. *Set the Service property*: the Service is the data service for this variable, which is the name of the imported data service.\\{image:svcvar.gif}\\
477: 1. *Set the Operation property*: the operation drop down shows the queries that have been defined for a particular data service. In this case, select the operation named getOrdersByRange.
478: 1. *Set the input parameters*: for each input parameter in the query, specify where that input will come from. In the screenshot example, the two parameters required for the getOrdersByRange query are coming from two date editor widgets: dateEditor2 and dateEditor3. Select the *input* properties for the service variable and then connect or bind each widget to the appropriate service variable input. \\{image:varinput.gif}\\
479: 1. *Set the startupdate checkbox*: if the startupdate property is checked, the query will automatically run when the application starts. For this query, it does not make sense to run the query before the two input parameters are entered.
480: 1. *Set the autoupdate checkbox*: if the autoupdate property is checked, the query will automatically run whenever one of the input values changes. 
481: 1. *Add a button to call the query*: drag a button onto the canvas. Connect \\{image:buttonquery.gif}\\
482: 1. *Show Service Variable data in a datagrid*: drag a datagid onto the canvas. Set its *dataSet* parameter to the name of the new Service Variable.
483: 
484: 1.1 Forms In WaveMaker
485: 
486: WaveMaker makes it easy to create Forms by simply dragging data elements from the palette.  Once a data model has been imported, each table from that data model is available in the Data section of the WaveMaker palette.  WaveMaker includes the following widgets for working with forms:
487: * *Data Form Widget*: these widgets are automatically created whenever a data model is imported. Each table in the data model has a corresponding Data Form widget in the Data section of the widget palette. Each Data Form is a composite widget includes a dataGrid widget that shows a master list of all the rows in a table, coupled with a LiveForm widget that shows the detailed information about the currently selected row in the dataGrid.
488: * *Live Form Widget*: a Live Form widget is similar to a Data Form widget but is not automatically bound to a particular table, giving the developer more control over building a form to create, read, update and delete data.
489: * *Related Editor Widget*: dragging a RelatedEditor widget inside a LiveForm allows the developer to display related data from both one-to-one and one-to-many relationships within the LiveForm.
490: * *Data Navigator Widget*: dragging a DataNavigator widget  inside a LiveForm provides controls that allow the user to navigate from one data row to another within the LiveForm.
491: 
492: 1.1.1 Using Data Form Widgets
493: These widgets are automatically created whenever a data model is imported. Each table in the data model has a corresponding Data Form widget in the Data section of the widget palette. Each Data Form is a composite widget includes a DataGrid widget that shows a master list of all the rows in a table, coupled with a LiveForm widget that shows the detailed information about the currently selected row in the DataGrid.
494: 
495: When a Data Form Widget is dragged onto the canvas, WaveMaker performs the following actions:
496: 1. Create a DataGrid to display a master list of all rows from an underlying database table
497: 1. Create a LiveForm to display a detailed form for the currently selected DataGrid row
498: 1. Create a View data type that can specify which related data from other tables should be included in this Data Form. The developer can edit the View data type to adjust what data is available to display.
499: 1. Create a LiveVariable to hold the data displayed in the DataGrid. The developer can edit the LiveVariable to filter and sort this data.
500: 1. Bind the LiveVariable to the DataGrid.
501: 1. Bind the LiveForm to the selected row in the DataGrid.
502: 
503: 1.1.1 Using Live Form Widgets
504: 
505: The LiveForm widget makes it easy to build forms that create, update and delete data in an underlying data table.
506: 
507: The LiveForm widget has built-in functionality for displaying, creating, deleting or updating a record. The LiveForm widget has an associated DataSet object that represents its data source. It also has a DataOutput object, which you can use to get the data out of the LiveForm.
508: 
509: When you specify the DataSet (Getting the DataSet), the LiveForm widget automatically generates Editor widgets for every item in the DataSet (except lists and substructures). The binding on these Editor widgets is already done for you, so this can save you a huge amount of time.
510: 
511: The Editor widgets that LiveForm generates for you are ordinary Editor widgets. You can configure them as you would any other widget and you are free to delete them. However, do not move these Editor widgets outside the LiveForm that generated them.
512: 
513: If you use a LiveVariable for the dataSet then LiveForm can automatically generate Create/Read/Update/Delete functionality for you. A LiveVariable can be based either on a LiveTable (LiveTables) or a LiveView (LiveViews). However, for LiveForms, use a LiveVariable that references a LiveView.
514: 
515: Note: If a table has composite keys, the developer must edit the key in the WaveMaker data model to have only one column value. This does not affect the underlying database schema, but allows the data model to be handled properly in the Live Form.
516: 
517: [For more information on LiveForm widgets>http://dev.wavemaker.com/wiki/bin/PropertyDocumentation/LiveForm]
518: 
519: 1.1.1 Using the EditPanel Widget to Create, Update and Delete Data
520: 
521: The EditPanel Widget is a specialized container must be placed inside a LiveForm and holds button widgets to perform create, update and delete operations.
522: 
523: [For more information on Edit Panel widgets>http://dev.wavemaker.com/wiki/bin/PropertyDocumentation/EditPanel]
524: 
525: 
526: 
527: 1.1 Forms and Relationships
528: 
529: Forms can be used to display and update data from related tables. For example, the employee table may be related to the timesheet table such that each employee has many timesheets. WaveMaker uses Related Editor Widgets to create composite forms, for example showing a list of timesheets within an editor form.
530: 
531: For more information, see the [Tutorial on Composite Forms and Relationships>http://dev.wavemaker.com/wiki/bin/FormsTutorial]
532: 
533: 1.1.1 Using Related Editor Widgets for One-To-One Relationships
534: Dragging a RelatedEditor widget inside a LiveForm allows the developer to display related data from both one-to-one and one-to-many relationships within the LiveForm. For a one-to-one relationship, the data is represented using a sub-form or lookup editor (similar to a select editor).
535: 
536: [For more information on Related Editor widgets>http://dev.wavemaker.com/wiki/bin/PropertyDocumentation/RelatedEditor]
537: 
538: 1.1.1 Using Related Editor Widgets for One-To-Many Relationships
539: 
540: Dragging a RelatedEditor widget inside a LiveForm allows the developer to display related data from both one-to-one and one-to-many relationships within the LiveForm.  For a one-to-many relationship, the data is represented in a grid. 
541: 
542: [For more information on Related Editor widgets>http://dev.wavemaker.com/wiki/bin/PropertyDocumentation/RelatedEditor]
543: 
544: 1.1.1 Using Data Navigator Widgets in Forms
545: Dragging a DataNavigator widget  inside a LiveForm provides controls that allow the user to navigate from one data row to another within the LiveForm. 
546: 
547: This is a specalized widget for use with LiveForms that allows the user to move through a list of records, similar to the record navigation feature of MS Access and FileMaker.
548: 
549: There are two ways to use the dataNavigator widget:
550: 
551:     * Record navigation in a LiveForm: this allow the user to show data in a LiveForm record by record, similar to a database cursor.
552:     * Paging in a Grid: this allows the user to page through data in a grid. This requires setting the byPage property checkbox in the dataNavigator and also setting the maxResults property of the LiveVariable bound to both the grid and the dataNavigator.
553: 
554: [For more information on Data Navigator widgets>http://dev.wavemaker.com/wiki/bin/PropertyDocumentation/DataNavigator]
555: 
556: 1.1 Setting Form Primary and Foreign Keys Automatically
557: 
558: In building an application, it is frequently useful to be able to set the primary or foreign key value of a new data object automatically. Usually, the primary or foreign key editor widget would be hidden on the form so that this happens transparently to the user.
559: 
560: 1.1.1 Insert operation clears all field values
561: 
562: The insert operation on a Live Form clears all the editor values, so it is not possible to use a normal binding to "preset" the value of a primary or foreign key editor. Instead, set the onBeginInsert() event for a form to call a Javascript function, and in that function manually setting the primary or foreign key editor value. 
563: 
564: \\{image:relcust.gif| |600} \\
565: 
566: 1.1.1 Multi-column primary keys
567: 
568: To work with a multi-column primary key, make sure that the LiveView that is bound to your form is set to display each column of the primary key separately. This will ensure that there is a separate editor in the live form for each primary key value. Use the onBeginInsert() event to set these editors. The example below shows setting the Lineitem live view to show the two primary key columns, itemid and orderid:
569: 
570: {image:primary.gif} 
571: 
572: 1.1.1 Adding related editor to set foreign key values
573: 
574: To set a foreign key relationship, first add a RelatedEditor widget to the form. For example, add a related editor widget to the Purchase form and set the formField property to "Customer". Next, remove all editors but the primary key editor for Customer, which is "custid".
575: 
576: {image:relatedcust.gif}
577: 
578: The steps to set a foreign key value using Javascript are as follows:
579: 
580: 1. Set the onBeginInsert event for a live form widget to call a Javascript function. This function will be called before the data is inserted.
581: 1. In the Javascript function that is created, set the appropriate foreign key value. Note that the required foreign key
582: 
583: \\
584: 
585: {code}
586: // Called by the onBeginInsert event for the purchaseLiveForm widget
587:   // Set foreign key for purchase to currently selected Customer
588:   purchaseLiveFormBeginInsert: function(inSender) {
589:     // Get primary key value of currently selected customer
590:     var custid = app.selectedCustomerLiveVar.getValue("custid");
591:     // Set related customer for this purchase to currently selected customer
592:     this.custidEditor1.setValue("dataValue", custid);     
593:   },
594: 
595: {code}
596: 
597: 1.1.1 Setting lookup editor for foreign key
598: 
599: It is common on inserting a new object with several relationships to allow the user to set those relationships using a lookup editor. To do this, follow these steps:
600: 
601: 1. *Add RelatedEditor widget:* add a RelatedEditor widget to the form. For example, add a related editor widget to the Purchase form and set the formField property to "Customer".
602: 1. *Change editingMode parameter to lookup:* with the RelatedEditor widget selected, change the editingMode parameter to lookup.\\{image:lookup.gif}
603: 1. *Remove un-needed editors from RelatedEditor widget:* by default, the RelatedEditor widget shows all editors for the related table. Remove the ones which are not needed (e.g., all the editors except for the lookup editor)
604: 1. *Choose columnto display in lookup editor:* the RelatedEditor widget should now just contain a single lookup widget (in this example, customerLookup1). Select the lookup widget, then select the editor property. Now clear the value in the displayExpression property and select the column to display from the displayField property.\\{image:custlookup.gif}\\
605: 1. *Add onBeginInsert event to form:* select the Purchase form and set the onBeginInsert event to call a Javascript function.
606: 1. *Set default lookup value:*  in the onBeginInsert Javascript function, set the lookupEditor’s displayValue property to a default value, as shown in the following code:
607: 
608: \\
609: 
610: {code}
611: this.addressLookupEditor1.setValue ("displayValue", "47 MySakila Drive"); 
612: 
613: {code}
614: 
615: \\
616: 
617: 1.1 See Also
618: 
619: * [Developer’s Documentation - Databases and Data Models>Dev.Databases]
620: * [Hibernate Query Language (HQL)>Dev.HQL]
621: * [HQL Tutorial>Dev.HqlTutorial]
622: * [Filters>Dev.Filters]
623: * [Reimporting a Data Model>ReImportingaDatabase]
Databases

Top Menu

Wiki code for Databases

Search

Main Menu
