Chapter 17
Building and Browsing Databases

[p]This chapter documents the Lasso Database Browser, which is a LassoApp included with Lasso Professional Server for interacting with all Lasso-configured databases and building custom MySQL databases. [/p]

• Overview describes the features of the Lasso Database browser.
• Database Browser describes how to search, add, update, view, and delete records in Lasso-enabled databases, and how to import and export data.
• Database Builder describes how the administrator can create databases, tables, fields, and indices within SQLite and MySQL data sources.

Overview

[p]The Lasso Database Browser allows administrators to interact with the records of all databases enabled in Site Administration. This includes searching, viewing, adding, updating, and deleting records in any database accessible by Lasso. The Lasso Database Browser also allows the administrator to build custom MySQL databases using a Web-based interface for use in Lasso solutions. [/p]

Accessing the Lasso Database Browser

[p]The Lasso Database Browser consists of the [code]DatabaseBrowser.LassoApp[/code] file located in the [code]LassoApps[/code] folder of the Lasso Professional Server application folder. The Lasso Database Browser is a LassoApp file, which is accessed via a Web browser similar to a standard HTML page. [/p]

[p]The Lasso Database Browser can be accessed in any Web browser that meets the Web browser requirements listed in the configuration chapters in this guide. [/p]

To access the Lasso Database Browser:

[p]In a Web browser, visit [code]http://www.example.com/Lasso/DatabaseBrowser.LassoApp[/code]. Replace [code]www.example.com[/code] with your domain name, IP address, or [code]127.0.0.1[/code] if on a local machine. If an error is displayed, make sure Lasso Service is running as described in the configuration chapters of this guide. [/p]

[p]The database browser for a specific host can be visited by loading Site Administration for that host and then using the link in the [code]Support[/code] section. [/p]

Figure 1: Lasso Database Browser

[note][b]Security [/b][b]Note[/b]: The Lasso Database Browser will prompt for the Lasso site administrator username and password. Only the site administrator may use the Lasso Database Browser. [/note]

Lasso Database Browser Features

[p]The [code]Browse[/code] section allows the administrator to test databases once they have been enabled, as well as modify records in existing databases. It is always recommend that databases be tested in the [code]Browse [/code]section of the Lasso Database Browser before attempting to interact with them via custom Lasso code. The [code]Browse[/code] section also allows the administrator to import and export data. [/p]

[p]The [code]Build[/code] section allows the administrator to create databases, tables, fields, and indices, and edit the schema of SQLite and MySQL database via a Web-based interface. No knowledge of SQL is required to build databases using the [code]Build[/code] section. [/p]

Database Browser

[p]The [code]Database[/code] section of the Lasso Database Browser allows administrators to interact with database records and includes the following pages: [/p]

• [def]Search[/def] – Allows the administrator to search records in a selected table.
• [def]Add[/def] – Allows the administrator to add records to a selected table.
• [def]List[/def] – Lists records within a selected table.
• [def]Detail [/def]– Displays the contents of a selected record.
• [def]Update[/def] – Allows the administrator to update or delete a record within a selected table.
• [def]Import[/def] – Allows the administrator to import a text data file to a selected table.
• [def]Export[/def] – Allows the administrator to export data from a selected table to a text format.

Selecting a Database to Work With

[p]The sidebar is where the data source connector, host, database, table, and key field of the records to be browsed are first specified. The administrator can use the sidebar on any page within the browse section. [/p]

Figure 2: Settings Sidebar

To select a connector, host, database, table, and key field:

1. In the sidebar, select a data source connector from the [code]Datasource[/code] pull-down menu. When a pull-down menu option is selected, the [code]Select[/code] page will automatically update to show the correct information for that option in the pull-down menus below.

2. Select a data source host from the [code]Host[/code] pull-down menu.

3. Select a database from the [code]Database[/code] pull-down menu.

4. Select a table from the [code]Table[/code] pull-down menu.

5. Select the [code]Key Field[/code] from the [code]Key Field[/code] pull-down menu. The key field of a table is typically the [code]ID[/code] field, and is used to store values unique to each record.

6. Select the [code]Select[/code] button.

Searching Records

[p]The [code]Search[/code] page allows the administrator to search records in a selected table. All fields are shown by default for the selected table, and both [code]Search[/code] and [code]Find All[/code] buttons return results in the [code]List [/code]page. The data type of each field is displayed to the right of each field by default. For more information on data types, consult the appropriate third-party documentation for the data source being used. [/p]

Figure 3: Search Page

To search records in a table:

1. Enter the information you wish to search for in the appropriate field next to each field name. If data is entered in more than one field, the found set will only contain records containing all values searched for.

• 2 Select [code]Search Records[/code]. If records matching the search parameters are found, the administrator will be taken to the [code]List [/code]page to view the found set. If no matching records are found, a [code]No Records Found[/code] message will be displayed in the [code]Search[/code] page.
• The sidebar displays the current database, table, and key field being used in the [code]Database[/code] section. These can be changed by selecting new values in each pull-down menu, and then selecting the [code]Select[/code] button. Selecting a new database or table will automatically change the fields in the [code]Search [/code]page to represent the new table.

[p]Selecting the [code]Show Options[/code] button displays the [code]Field View Options[/code] panel to the right with the first field selected. Here, the administrator may set field display options for each field. [/p]

Search Options

[p]Selecting the name of a field in the [code]Search Records[/code] panel displays the [code]Field View Options[/code] panel to the right for that field. [/p]

Figure 4: Search Options

[p]The following input and display options can be edited for each field. [/p]

• Field – Displays the name of the selected field in a pull-down menu. Selecting a different field will display the options for that field. Select a field here if the desired field is not currently visible in the [code]Search Records[/code] panel.
• Type – Displays the data type of the field. This is useful for determining an appropriate input type.
• Display on Page – Selecting [code]No[/code] causes the field to no longer be displayed in the [code]Search Records[/code] panel.
• Input Type – Designates what HTML input type will be used for the field in the [code]Search[/code] page. Options are as follows:
• [code]Automatic[/code] – Determines the input type based on the data type of the field. For example, a [code]VARCHAR[/code] or [code]INT[/code] type would be a text input, a [code]TEXT[/code] data type would be a text area input, and an [code]ENUM[/code] data type would be a pull-down menu.
• [code]Text [/code][code]Input[/code] – Uses the HTML input type [code]Text[/code] for the field.
• [code]Password [/code][code]Input[/code] – Uses the HTML input type [code]Password[/code] for the field.
• [code]Text [/code][code]Area[/code] – Uses the HTML input type [code]Textarea[/code] for the field.
• [code]Select [/code][code]List[/code] – Uses the HTML input type [code]List[/code] for the field.
• [code]Pull-Down [/code][code]Menu[/code] – Uses the HTML input type [code]Select[/code] for the field.
• [code]Check [/code][code]Boxes[/code] – Uses the HTML input type [code]Check[/code] for the field.
• [code]Radio [/code][code]Buttons[/code] – Uses the HTML input type [code]Radio[/code] for the field.
• Input Size – Determines the horizontal and/or vertical size of the field input. This option appears if the [code]Text Input[/code], [code]Password Input[/code], [code]Select List[/code], or [code]Pull-Down Menu[/code] options are selected, and the options are [code]Small[/code], [code]Medium[/code], and [code]Large[/code].
• Position – Allows a custom field display order to be set. Fields assigned a lower number will be displayed ahead of fields assigned a higher value. Once the [code]Position[/code] value has been changed, the [code]Position[/code] value of all other fields will adjust to accommodate for the new field value.
• Display Field Type – Selecting [code]No[/code] causes the field data type to no longer appear to the right of each field in the [code]Search Records[/code] panel.

[p]Options are changed by selecting the desired values from the pull-down menus and selecting the [code]Apply[/code] button. The [code]Reset[/code] button returns all options to their default values. Selecting the [code]Hide Options [/code]button hides the [code]Field View Options[/code] panel. [/p]

Adding Records

[p]The [code]Add[/code] page allows the administrator to add a record to the selected table. The [code]Add Record[/code] panel displays all fields within the table by default, and displays an input field to the right of each field name. The input type used for each field (text input, pull-down menu, radio buttons, etc.) can be customized in the [code]Field View Options[/code] panel, which is described later in this section. [/p]

Figure 5: Add Page

To add a record to table:

1. Enter the information you wish to add in the appropriate text field next to each field name. The data type of each field is displayed to the right by default.

• 2 Select [code]Add Record[/code]. The results of an added record are displayed in the [code]Detail [/code]page.

[p]The sidebar displays the current database, table, and key field being used in the [code]Databases[/code] section. The key field of the current record may be changed by selecting a new key field from the [code]Key Field[/code] pull-down menu, and then selecting the [code]Select[/code] button. The fields shown in the [code]Add[/code] page will change to reflect the new table if a new database or table is selected. [/p]

[p]Selecting the [code]Show Options[/code] button displays the [code]Field View Options[/code] panel to the right with the first field selected. Here, the administrator may set field display options for each field. [/p]

Add Options

[p]Selecting the name of a field in the [code]Add Record[/code] panel displays the [code]Field View Options[/code] panel to the right for that field. [/p]

Figure 6: Add Options

[p]The following input and display options can be edited for each field. [/p]

• Fields – Displays the name of the selected field in a pull-down menu. Selecting a different field will display the options for that field. Select a field here if the desired field is not currently visible in the [code]Add Record[/code] panel.
• Type – Displays the data type of the field. This is useful for determining an appropriate input type.
• Display on Page – Selecting [code]No[/code] causes the field to no longer be displayed in the [code]Add Record[/code] panel.
• Input Type – Designates what HTML input type will be used for the field in the [code]Add[/code] page. Options are as follows:
• [code]Automatic[/code] – Determines the input type based on the data type of the field. For example, a [code]VARCHAR[/code] or [code]INT[/code] type would be a text input, a [code]TEXT[/code] data type would be a text area input, and an [code]ENUM[/code] data type would be a pull-down menu.
• [code]Text [/code][code]Input[/code] – Uses the HTML input type [code]Text[/code] for the field.
• [code]Password [/code][code]Input[/code] – Uses the HTML input type [code]Password[/code] for the field.
• [code]Text [/code][code]Area[/code] – Uses the HTML input type [code]Textarea[/code] for the field.
• [code]Select [/code][code]List[/code] – Uses the HTML input type [code]List[/code] for the field.
• [code]Pull-Down [/code][code]Menu[/code] – Uses the HTML input type [code]Select[/code] for the field.
• [code]Check [/code][code]Boxes[/code] – Uses the HTML input type [code]Check[/code] for the field.
• [code]Radio [/code][code]Buttons[/code] – Uses the HTML input type [code]Radio[/code] for the field.
• Input Size – Determines the horizontal and/or vertical size of the field input. This option appears if the [code]Text Input[/code], [code]Password Input[/code], [code]Select List[/code], or [code]Pull-Down Menu[/code] options are selected from [code]Input Type[/code], and the options are [code]Small[/code], [code]Medium[/code], and [code]Large[/code].
• Position – Allows a custom field display order to be set. Fields assigned a lower number will be displayed ahead of fields assigned a higher value. Once the [code]Position[/code] value has been changed, the [code]Position[/code] value of all other fields will adjust to accommodate for the new field value.
• Display Field Type – Selecting [code]No[/code] causes the field data type to no longer appear to the right of each field in the [code]Add Record[/code] panel.

[p]Options are changed by selecting the desired values from the pull-down menus and selecting the [code]Apply[/code] button. The [code]Reset[/code] button returns all options to their default values. Selecting the [code]Hide Options [/code]button hides the [code]Field View Options[/code] panel. [/p]

Listing Records

[p]The [code]List[/code] page shows results from a [code]Search[/code] or [code]Find All[/code] action. Each row represents a record from the selected table, and each column represents one of the first eight fields from the selected table. Which fields are displayed in the[code] List[/code] page can be set in the [code]Field View Options[/code] panel, described later in this section. A number is displayed to the left of each record corresponding to its order in the found set. Selecting the record number link shows the [code]Detail[/code] page for the selected record by default. [/p]

Figure 7: List Page

[p]The sidebar displays the current database, table, and key field being used in the [code]Databases[/code] section. These can be changed by selecting new values in each pull-down menu, and then selecting the [code]Select[/code] button. Selecting a new database or table will automatically return all records in the new table in the [code]List[/code] page. [/p]

[p]The bottom right panel contains navigation controls allowing the administrator to view different sets of records from the found set. The [code]Next[/code] and [code]Previous[/code] buttons take the administrator to the next and previous sets in the found set. The [code]First[/code] and [code]Last[/code] buttons will take the administrator to the first and last record in the found set. The [code]Jump[/code] button allows the administrator to specify the record number from the found set in which to begin the list with. [/p]

[p]The bottom left panel contains sort options that allow the administrator to control how the records are sorted in the [code]List[/code] page. The [code]Sort Field[/code] pull-down menu contains the field by which the records are sorted, and the [code]Sort Order[/code] pull-down menu specifies whether the records are sorted in an ascending (A-Z, 1-9) or descending (Z-A, 9-1) order. The sort options can be changed by selecting a value from each pull-down menu and selecting the [code]Sort [/code]button. [/p]

Export

[p]Selecting the Export area in the sidebar allows the current results to be exported. Setting one or more options and then selecting Export Results will result in a text file being sent to the browser containg the current result set. [/p]

• Format – Specifies what format the export should be in. Options include [code]Tab Delimited[/code], [code]Comma Delimited[/code], [code]SQL Inserts[/code], or or [code]XML Data[/code].
• Field Names – Specifies whether field names should be included as the first line of tab or comma delimited files. For XML data this preference controls whether field names are used as the tag names surrounding each field value or if a generic field container is used.
• Line Endings – Sets the line endings for the export to [code]n[/code], [code]r[/code], or [code]rn[/code].

List Options

[p]Selecting the name of a field displays the [code]Field View Options[/code] panel in the lower left panel for that field. Doing so will limit the number of records shown in the [code]List[/code] page to one record while display options are set. [/p]

Figure 8: List Options

[p]The following input and display options can be edited for each field. [/p]

• Field – Displays the name of the selected field in a pull-down menu. Selecting a different field will display the options for the different field. Select a field here if the desired field is not currently visible in the [code]List[/code] page.
• Type – Displays the data type of the field.
• Display on Page – Selecting [code]No[/code] causes the field to no longer be displayed in the [code]List[/code] page. In the [code]List[/code] page, this only applies to fields in positions 1-8 (see [code]Position[/code] below).
• Display Size – Allows the maximum number of lines of data displayed for the selected field to be set. Options are [code]1 Line[/code], [code]4 Lines[/code], [code]7 Lines[/code], [code]10 Lines[/code], and [code]All Lines[/code].
• Position – Allows a custom field display order to be set. In the [code]List [/code]page, only the first eight fields will be displayed. Fields assigned a lower number will be displayed ahead of fields assigned a higher value. Once the [code]Position[/code] value has been changed, the [code]Position[/code] value of all other fields will adjust to accommodate for the new field value.
• List Records – Allows the number of records displayed in the [code]List[/code] page to be set. This can be [code]5[/code], [code]10[/code], [code]25[/code], [code]50[/code], or [code]100[/code].
• List Response – Determines where the administrator will be taken after a record is selected in the [code]List[/code] page. [code]Detail[/code] takes the administrator to the [code]Detail[/code] page when a record is selected. [code]Update[/code] takes the administrator to the [code]Update[/code] page when a record is selected. [code]No Details Links[/code] disallows records from being selected in the [code]List[/code] page.

[p]Options are changed by selecting the desired values from the pull-down menus and selecting the [code]Apply[/code] button. The [code]Reset[/code] button returns all options to their default values. Selecting the [code]Hide Options [/code]button hides the [code]Field View Options[/code] panel and returns the administrator to the regular[code] List[/code] view with all records displayed. [/p]

Displaying Record Details

[p]The[code] Detail [/code]page displays the field data for the selected record. All fields in the record are displayed by default. [/p]

Figure 9: Detail Page

Record Detail

[p]The [code]Record Detail[/code] panel shows the field data for the record. The data type of each field is displayed to the right of the data for that field. Selecting the [code]Update[/code] button will take the administrator to the [code]Update [/code]page, while seecting [code]Delete[/code] button will display a confirmation dialog asking the administrator to confirm the delete. Once this is done, the administrator will be returned to the [code]List[/code] page. If a record is deleted, it cannot be retrieved. [/p]

[p]The sidebar displays the current database, table, and key field being used in the [code]Databases[/code] section. The key field of the current record may be changed by selecting a new key field from the [code]Key Field[/code] pull-down menu, and then selecting the [code]Select[/code] button. Since the database or table of the current record displayed cannot be changed, selecting a new database or table will return the administrator to the [code]Select[/code] page. [/p]

[p]The bottom panel contains navigation controls allowing the administrator to instantly switch to another record in the found set to be displayed in the [code]Detail[/code] page. The [code]Next[/code] and [code]Previous[/code] buttons take the administrator to the next and previous records in the found set. The [code]First[/code] and [code]Last[/code] buttons will take the administrator to the first and last record in the found set. The [code]Jump[/code] button allows the administrator to specify the record number from the search results of the record to be displayed. [/p]

[p]Selecting the [code]Show Options[/code] button displays the [code]Field View Options[/code] panel to the right with the first field selected. Here, the administrator may set field display options for each field. [/p]

Detail Options

[p]Selecting the name of a field in the [code]Record Detail[/code] panel displays the [code]Field View Options[/code] panel to the right for that field. [/p]

Figure 10: Detail Options

[p]The following input and display options can be edited for each field. [/p]

• Field – Displays the name of the selected field in a pull-down menu. Selecting a different field will display the options for the different field. Select a field here if the desired field is not currently visible in the [code]Record Detail[/code] panel.
• Type – Displays the data type of the field. This is useful for determining an appropriate input type for the field.
• Display on Page – Selecting [code]No[/code] causes the field to no longer be displayed in the [code]Record Detail[/code] panel.
• Display Size – Changes the number of lines of data that are displayed for the selected field. Options are [code]1 Line[/code], [code]4 Lines[/code], [code]7 Lines[/code], [code]10 Lines[/code], and [code]All Lines[/code].
• Position – Allows a custom field display order to be set. Fields assigned a lower number will be displayed ahead of fields assigned a higher value. Once the [code]Position[/code] value has been changed, the [code]Position[/code] value of all other fields will adjust to accommodate for the new field value.
• Display Field Types – Selecting [code]No[/code] causes the field data type to no longer appear next to each field in the [code]Record Detail[/code] panel.

[p]Options are changed by selecting the desired values from the pull-down menus and selecting the [code]Apply[/code] button. The [code]Reset[/code] button returns all options to their default values. Selecting the [code]Hide Options [/code]button hides the [code]Field View Options[/code] panel. [/p]

Updating and Deleting Records

[p]The[code] Update[/code] page allows the administrator to update or delete records within a database, and displays all fields and their current values for the selected record by default. [/p]

Figure 11: Update Page

Updating Records

[p]The [code]Update Record[/code] panel shows the field data for the record within input fields. The input type that is used for each field can be specified in the [code]Field View Options[/code] page, described later in this section. The data type of each field is displayed to the right of the data for that field. [/p]

To update a record in a database:

1. Enter the information you wish to update in the appropriate text field next to each field name. The data type of each field is displayed to the right by default.

2. Select [code]Update Record. [/code]The results of an updated record are displayed in the [code]Detail [/code]page.

[p]To delete the current record from the table, select the[code] Delete [/code]button. A confirmation dialog will always be displayed asking the administrator to confirm the delete. Once this is done, the administrator will be returned to the [code]List[/code] page. If a record is deleted, it cannot be retrieved. [/p]

[p]The top and bottom panels of the [code]Update[/code] page act in the same manner as in the [code]Detail [/code]page. Selecting the [code]Show Options[/code] button displays the [code]Field View Options[/code] panel to the right with the first field selected. Here, the administrator may set field display options for each field. [/p]

Update Options

[p]Selecting the name of a field in the [code]Update Record[/code] panel displays the [code]Field View Options[/code] panel to the right for that field. [/p]

Figure 12: Update Options

[p]The following input and display options can be edited for each field. [/p]

• Field – Displays the name of the selected field in a pull-down menu. Selecting a different field will display the options for the different field. Select a field here if the desired field is not currently visible in the [code]Update Record[/code] panel.
• Type – Displays the data type of the field. This is useful for determining an appropriate input type for the field.
• Display on Page – Selecting [code]No[/code] causes the field to no longer be displayed in the [code]Update Record[/code] panel.
• Input Type – Designates what HTML input type will be used for the field in the [code]Update[/code] page. Options are as follows:
• [code]Automatic[/code] – Determines the input type based on the data type of the field. For example, a [code]VARCHAR[/code] or [code]INT[/code] type would be a text input, a [code]TEXT[/code] data type would be a text area input, and an [code]ENUM[/code] data type would be a pull-down menu.
• [code]Text [/code][code]Input[/code] – Uses the HTML input type [code]Text[/code] for the field.
• [code]Password [/code][code]Input[/code] – Uses the HTML input type [code]Password[/code] for the field.
• [code]Text [/code][code]Area[/code] – Uses the HTML input type [code]Textarea[/code] for the field.
• [code]Select [/code][code]List[/code] – Uses the HTML input type [code]List[/code] for the field. Any values defined in an [code]ENUM[/code] or [code]SET[/code] data type or FileMaker Pro value list for the field will automatically be displayed as selectable values.
• [code]Pull-Down [/code][code]Menu[/code] – Uses the HTML input type [code]Select[/code] for the field. Any values defined in an [code]ENUM[/code] or [code]SET[/code] data type or FileMaker Pro value list for the field will automatically be displayed as selectable values.
• [code]Check [/code][code]Boxes[/code] – Uses the HTML input type [code]Check[/code] for the field. Any values defined in an [code]ENUM[/code] or [code]SET[/code] data type or FileMaker Pro value list for the field will automatically be displayed as selectable values.
• [code]Radio [/code][code]Buttons[/code] – Uses the HTML input type [code]Radio[/code] for the field. Any values defined in an [code]ENUM[/code] or [code]SET[/code] data type or FileMaker Pro value list for the field will automatically be displayed as selectable values.
• Input Size – Determines the horizontal and/or vertical size of the field input. This option appears if the [code]Text Input[/code], [code]Password Input[/code], [code]Select List[/code], or [code]Pull-Down Menu[/code] options are selected from [code]Input Type[/code], and the options are [code]Small[/code], [code]Medium[/code], and [code]Large[/code].
• Position – Allows a custom field display order to be set. Fields assigned a lower number will be displayed ahead of fields assigned a higher value. Once the [code]Position[/code] value has been changed, the [code]Position[/code] value of all other fields will adjust to accommodate for the new field value.
• Display Field Types – Selecting [code]No[/code] causes the field data type to no longer appear to the right of each field in the [code]Update Record[/code] panel.

[p]Options are changed by selecting the desired values from the pull-down menus and selecting the [code]Apply[/code] button. The [code]Reset[/code] button returns all options to their default values. Selecting the [code]Hide Options [/code]button hides the [code]Field View Options[/code] panel. [/p]

Importing Records

[p]The [code]Import[/code] page allows data to be imported to the selected table from a text file in a comma-delimited, tab-delimited, or XML format. Files can be uploaded from the local machine or can be placed in the [code]Lasso Professional Server/LassoAdmin/Browse[/code] folder. Uploaded files are placed in this folder and then imported. This is the same folder which the [code]Export [/code]section uses. [/p]

[note][b]Note: [/b]The data in import files is always imported in the same order as the fields in the database. There is no option to match fields or import data out of order. [/note]

Figure 13: Import Page

[p]The sidebar displays the current database, table, and key field being used in the [code]Databases[/code] section. These can be changed by selecting new values in each pull-down menu, and then selecting the [code]Select[/code] button. Selecting a new database, table, and keyfield will change the table to which data is imported. [/p]

Import File

[p]The [code]Import File [/code]panel allows the administrator to upload or select the file to be imported. These options are described below: [/p]

• File Name – Allows the administrator to select the text file to be imported. The pull-down menu lists all files currently stored in the [code]Lasso Professional Server/lassoAdmin/Browse[/code] folder. The administrator must select a file and select the [code]Apply[/code] button before the remaining options can be used.
• Alternately, the file selector can be used to choose a file on the same machine as the Web browser. When the Apply button is selected the file will be uploaded into the [code]Lasso Professional Server/lassoAdmin/Browse[/code] folder.

Import Settings

[p]The [code]Import Settings [/code]panel allows the administrator to set import options and see a preview of the data to be imported. This panel is only available when a file is selected in the [code]Import [/code]File panel. The options available are described below: [/p]

• Type – Allows the data format of the text file to be explicitly specified. One of three formats may be selected:
• [code]Automatic[/code] – Site Administration automatically determines which format the text file is in and displays it in parentheses after [code]Automatic[/code]. This is the default setting.
• [code]Comma [/code][code]Delimited[/code] – Specifies that the file to be imported is in comma delimited format.
• [code]Tab [/code][code]Delimited[/code] – Specifies that the file to be imported is in tab delimited format.
• [code]XML[/code] – Specifies that the file to be imported is in XML format.
• Line Endings – Allows the administrator to specify which line ending characters are used in the text file. Supported line ending characters vary from operating system to operating system, and the options are as follows:
• [code]Automatic[/code] – Site Administration automatically determines which line endings are used in the text file is in and displays it in parentheses after [code]Automatic[/code]. This is the default setting.
• [code]UNIX/Mac [/code][code]OS X (/n)[/code] – Text file uses the [code]/n[/code] line ending, used in UNIX and Mac OS X operating systems.
• [code]Mac [/code][code]OS 9 (/r)[/code] – Text file uses the [code]/r[/code] line ending, used in the Mac OS 9 operating system.
• [code]Windows [/code][code](/r/n)[/code] – Text file uses the [code]/r/n[/code] line ending, used in Windows operating systems.
• Replace Matching Key Values – Selecting [code]Yes[/code] causes any data imported to the key field of the current table to be overwritten with new values. For example, if the key field is the[code] ID[/code] field in a MySQL table, selecting [code]Yes[/code] will cause whatever field that is matched with the [code]ID[/code] field in the current database to be overwritten with new [code]ID[/code] values, as if it were a new table and records are being added for the first time.
• Skip First Record – Selecting [code]Yes[/code] does not import the first line of the file, which sometimes includes the names of each field and does not contain record data.
• Import Preview – Shows the first five lines of the text file to be imported. This is useful in that it shows the first line of the file to be imported, allowing the administrator to determine if the data and format of the file are correct.

[p]Options are changed by selecting the desired values from the pull-down menus, selecting or deselecting a check box to the left of the desired options, and selecting the [code]Apply[/code] button. The [code]Reset[/code] button returns all options to their default values. [/p]

To import data to the current table:

1. In the [code]Import File [/code]panel, select a data file to import from the [code]File Name[/code] pull-down menu or upload a file from the local machine. Select the [code]Apply[/code] button if no other options are shown.

2. In the [code]Import Settings [/code]panel, select the data type of the text file from the [code]Type[/code] pull-down menu, or verify that the [code]Automatic[/code] type is correct by looking at the [code]Import Preview [/code]field.

3. Select the line endings used in the text file in the [code]Line Endings[/code] pull-down menu, or verify that the [code]Automatic[/code] type is correct by looking at the [code]Import Preview [/code]field. Knowing which operating system the file was created in is helpful for determining which line ending characters are used.

4. Select [code]Yes [/code]for [code]Replace Matching Key Values [/code]if you want the imported data to receive new key values in the current table. This is strongly recommended if data is being imported to a table with existing records, however, the field that is matched with the current key field will be overwritten.

5. Select [code]Yes [/code]for [code]Skip First Record [/code]if the first record in the text file contains field names as opposed to values. See the [code]Import Preview [/code]field to determine this.

6. Select the [code]Apply[/code] button.

7. Select the [code]Perform Import [/code]button.

Exporting Records

[p]The [code]Export[/code] page allows the administrator to export data from a table to a text file in a comma-delimited, tab-delimited, or XML format. This feature allows the records in the current found set to be instantly exported to a text format that can be interpreted by any number of other data sources. [/p]

[p]Files can be downloaded to the local machine or can be exported to the [code]Lasso Professional Server/lassoAdmin/Browse[/code] folder. Downloaded files are first written into this folder and then streamed to the Web client. This is the same folder which the [code]Import [/code]section uses. [/p]

Figure 14: Export Page

[p]The sidebar displays the current database, table, and key field being used in the [code]Databases[/code] section. These can be changed by selecting new values in each pull-down menu, and then selecting the [code]Select[/code] button. Selecting a new database or table will automatically find all records in the new table, which will be the found set that is exported. [/p]

Export Settings

[p]The [code]Export Settings [/code]panel allows the administrator to set the format options in which the current found set will be exported. The [code]Type[/code] pull-down menu allows the administrator to select from one of three data formats to export to. The remaining options (which the exception of [code]Line Endings[/code]) will vary depending on which format is selected. [/p]

• Type – Allows the text format to be set for the data which will be exported.
• [code]Comma [/code][code]Delimited[/code] – Exports data to text in a comma-delimited format.
• [code]Tab [/code][code]Delimited[/code] – Exports data to text in a tab-delimited format.
• [code]XML[/code] – Exports data to text in an XML format.
• Export Field Names – Selecting [code]Yes[/code] includes the names of each field to be included in the first line of data, similar to a spreadsheet header. Available for comma-delimited and tab-delimited formats.
• Root Tag Name (XML Only) – Allows the root XML tag name for the data file to be specified. All data in the text file will be contained in root tags.
• Record Tag Name (XML Only) – Allows the record XML tag name for the data file to be specified. All records will be contained in record tags.
• Field Tag Name (XML Only) – Allows the root XML tag name for the data file to be specified. All fields will be contained in field tags.
• Line Endings – Allows the administrator to specify which line ending character will be used in the text file. Supported line ending characters vary from operating system to operating system, and the options are as follows:
• [code]UNIX/Mac [/code][code]OS X (/n)[/code] – Uses the [code]/n[/code] line ending, used in UNIX and Mac OS X operating systems.
• [code]Mac [/code][code]OS 9 (/r)[/code] – Uses the [code]/r[/code] line ending, used in the Mac OS 9 operating system.
• [code]Windows [/code][code](/r/n)[/code] – Uses the [code]/r/n[/code] line ending, used in Windows operating systems.

[p]Options are changed by selecting the desired values from the pull-down menus, selecting or deselecting a check box to the left of the desired options, and selecting the [code]Apply[/code] button. The [code]Reset[/code] button returns all options to their default values. [/p]

[p]Export Preview [/p]

[p]The [code]Export Preview[/code] page displays the first five records of what the exported data will look like in the text file. To preview all data to be exported, select the [code]Preview Full Export[/code] button. [/p]

[p]The [code]Perform Export[/code] button exports data to the [code]Lasso Professional Server/lassoAdmin/Browse[/code] folder. The [code]Download Export [/code]button opens a new browser window and automatically downloads the data. [/p]

To export data:

1. In the [code]Export Format[/code] panel, select the data format to be exported to from the [code]Type[/code] pull-down menu.

2. Select options for the specified format below the [code]Type[/code] pull-down menu.

3. Select a line ending option from the [code]Line Endings[/code] pull-down menu.

4. Select [code]Apply[/code].

5. Examine the data to be exported in the [code]Export Preview[/code] panel to ensure it is the correct format needed for the data source that will import the file.

6. Enter a name for the text file to be created in the [code]File Name[/code] field. To have the file name end with the date and time of creation, select [code]Yes[/code] from the [code]Append Date to File Name[/code] pull-down menu.

7. Select the [code]Perform Export[/code] button or the [code]Download Export [/code]button.

[note][b]Note: [/b]The [code]Export[/code] page will only export the current found set from the [code]List[/code] page. To export all records in a table, make sure the [code]Find All Records[/code] button was selected previously in the [code]Select[/code] or [code]Search[/code] page. [/note]

[note][b]SQL [/b]Browser [/note]

[p]The Browse > SQL section of the Lasso Database Browser provides a Web-based interface that allows the administrator to issue SQL queries to Lasso-accessible SQL databases. This includes any SQLite, MySQL, or SQL-compliant JDBC database that has been set up and enabled in the Setup > Data Sources section of Site Administration. [/p]

Figure 15: SQL Browser

[p]The functionality of the Browse > SQL section is similar to that of the Utility > SQL section of Site Administration. For documentation on issuing SQL statements, see the Site Administration Utilities chapter. [/p]

Database Builder

[p]The [code]Build[/code] section of the Lasso Database Browser allows the administrator to create databases, tables, fields, and indices, and edit the schema of SQLite and MySQL database via a Web-based interface. This is useful for easily creating new MySQL databases for custom solutions, or for editing the schema (tables names, field names and types) of existing SQLite or MySQL databases. [/p]

Creating MySQL Databases

[p]The [code]Databases[/code] page is where the administrator can view all databases within a selected host and create new databases. The[code] Databases Listing[/code] panel shows a listing of all databases within the selected host and data source connector. [/p]

Figure 16: Databases Page

[p]The current data source connector and host can be changed using the [code]Connector[/code] and [code]Host[/code] pull-down menus. The administrator can view database details by selecting the database name, which will show the details to the right in the [code]Database Detail[/code] panel. [/p]

Creating Databases

[p]Selecting the [code]Create Database[/code] button displays the [code]Create Database[/code] panel to the right. The [code]Create Database[/code] page is where the administrator can create new databases within a specified data source host. [/p]

Figure 17: Create Database Panel

To create a new database:

1. Enter the name of your database in the[code] Name[/code] field.
2. [note][b]Note: [/b]Per naming conventions in SQLite and MySQL, database names may only consist of alpha-numeric characters plus the [code]_[/code] and [code]$[/code] characters. For more information on naming conventions, see the datasource documentation. [/note]

2. Select [code]Enabled[/code] or [code]Disabled[/code] from the [code]Status[/code] pull-down menu to enable or disable the database within Lasso.

3. Select [code]Create Database[/code].

Duplicating Databases

[p]Selecting the [code]Duplicate Database[/code] button shows the [code]Duplicate Database[/code] panel to the right. The [code]Duplicate Database[/code] panel is where the administrator can duplicate an existing database within a specified data source host. The administrator has the option to duplicate a database’s schema only (tables and fields), or the schema along with all data (records). [/p]

Figure 18: Duplicate Database Panel

To duplicate an existing database:

1. Select the data source connector of the source database from the [code]Connector[/code] pull-down menu. This can be either [code]Lasso Connector for SQlite[/code] or [code]Lasso Connector for MySQL. [/code]

2. Select the data source host of the source database from the [code]Host[/code] pull-down menu.

3. Select the source database from the [code]Database[/code] pull-down menu.

4. Select [code]Yes[/code] from the [code]Copy Data[/code] pull-down menu to copy all records from the source database to the new database. Selecting [code]No[/code] copies only the database schema (table and field structure) with no records.

5. Enter the name of the new database in the[code] Name[/code] field.
6. [note][b]Note: [/b]Per naming conventions in SQLite and MySQL, database names may only consist of alpha-numeric characters plus the [code]_[/code] and [code]$[/code] characters. For more information on naming conventions, see the datasource documentation. [/note]

6. Select [code]Enabled[/code] or [code]Disabled[/code] from the [code]Status[/code] pull-down menu to enable or disable the database within Lasso.

7. Select [code]Duplicate Database[/code]. The new database will appear in the [code]Database Listing[/code] panel to the left.

Updating and Deleting Databases

[p]The administrator may update or delete a database by selecting the [code]Update Database[/code] or [code]Delete Database[/code] button in the [code]Database Detail [/code]panel. To update a database, the administrator can change the name, alias, and status for the database via the [code]Name[/code], [code]Alias[/code], and [code]Status[/code] fields, and select the[code] Update Database[/code] button. Selecting the[code] Delete Database[/code] button will display a confirmation message for deleting the selected database, and selecting [code]OK[/code] will permanently delete the database. [/p]

Creating Tables

[p]The [code]Tables[/code] page is where the administrator can view all tables and create new tables within the selected database. The[code] Table Listing[/code] panel shows the current database and lists all tables within the database. The administrator can view table details by selecting a table name, which will show details to the right in the[code] Table Detail[/code] panel. [/p]

Figure 19: Tables Page

Creating Tables

[p]Selecting the [code]Create Table[/code] button show the [code]Create Table[/code] panel to the right. The [code]Create Table[/code] page is where the administrator can create tables in the selected database. [/p]

Figure 20: Create Table Panel

To create a table in the selected database:

1. Enter the name of your table in the[code] Table Name[/code] field.
2. [note][b]Note: [/b]Per naming conventions in SQLite and MySQL, table names may only consist of alpha-numeric characters plus the [code]_[/code] and [code]$[/code] characters. For more information on naming conventions, see the datasource documentation. [/note]

2. Select [code]Enabled[/code] or [code]Disabled [/code]from the [code]Status[/code] pull-down menu to enable or disable the table within Lasso.

3. Select [code]Create Table[/code].

[p]The [code]ID[/code] field is automatically created in each new table, which increments automatically with every new record added. There can only be one [code]ID[/code] field per table. [/p]

Duplicating Tables

[p]Selecting the [code]Duplicate Table [/code]button shows the [code]Duplicate Table [/code]panel to the right. The [code]Duplicate Table[/code] panel is where the administrator can duplicate an existing table within a specified database. [/p]

Figure 21: Duplicate Table Panel

To duplicate a table in the selected database:

1. Select the data source connector of the source table from the [code]Connector[/code] pull-down menu.[code] [/code]

2. Select the data source host of the source table from the [code]Host[/code] pull-down menu.

3. Select the database of the source table from the [code]Database[/code] pull-down menu.

4. Select the source table from the [code]Table[/code] pull-down menu.

5. Select [code]Yes[/code] from the [code]Copy Data[/code] pull-down menu to copy all records from the source table to the new database. Selecting [code]No[/code] copies only the table schema (field structure) with no records.

6. Enter the name of the new table in the[code] Name[/code] field. This new table will contain the information being duplicated, and will be created within the database currently selected in the [code]Table Listing[/code] panel.
7. [note][b]Note: [/b]Per naming conventions in SQLite and MySQL, table names may only consist of alpha-numeric characters plus the [code]_[/code] and [code]$[/code] characters. For more information on naming conventions, see the datasource documentation. [/note]

7. Select [code]Enabled[/code] or [code]Disabled[/code] from the [code]Status[/code] pull-down menu to enable or disable the table within Lasso.

8. Select [code]Duplicate Table[/code]. The new table will appear in the [code]Tables Listing[/code] panel to the left.

Updating and Deleting Tables

[p]The administrator may update or delete a table by selecting the [code]Update Table[/code] or [code]Delete Table[/code] button in the [code]Table Detail[/code] panel. To update a table, the administrator can change the name, alias, and status for the table via the [code]Name[/code], [code]Alias[/code], and [code]Status[/code] fields, and select the [code]Update Table[/code] button. Selecting the [code]Delete Table [/code]button will display a confirmation message for deleting the selected table, and selecting [code]OK[/code] will permanently delete the table from the selected database. [/p]

Creating Fields

[p]The [code]Fields [/code]page is where the administrator can view all fields within the selected table and database. The [code]Fields Listing[/code] panel includes a list of fields for the current table including all their editable attributes and the option to delete fields. [/p]

[p]The SQL statement that will be used to modify the table can be viewed by selecting the [code]Preview Change[/code] button. The modifications can be applied to the table by selecting the [code]Alter Table[/code] button. [/p]

[p]In addition to modifying the fields within the table, one field can be added by specifying its attributes in the [code]New[/code] line at the end of the table. If more fields need to be added the [code]Create Fields[/code] section can be used. [/p]

Figure 22: Fields Page

[p]Each field includes the following details. [/p]

• [def]Name[/def] – The name of the field.
• [def]Type[/def] – The type of the field. Since SQLite is typeless the field type is only used to determine sort order (alphabetic or numeric).
• [def]Display[/def] – An optional parameter which depends on the data type. In MySQL the display value is often required. See the MySQL documentation for more information.
• [def]Default[/def] – An optional default value for the field.
• [def]Options[/def] – The options for a field depend on the data source. The following options are commonly available.
• Not Null – If checked then the field cannot contain NULL values.
• Primary Key – One field in each table can be designated the primary key for th etable.
• Unique – Available on new fields controls whether the field should be constrained to only contain unique values.
• [code]Delete[/code] – This option deletes the field and all its contents.

Creating Fields

[p]The [code]Create Fields[/code] panel allows multiple fields to be created all at once. This section allows up to ten fields to be created at once. The SQL statement that will be used to modify the table can be viewed by selecting the [code]Preview Change[/code] button. The modifications can be applied to the table by selecting the [code]Alter Table[/code] button. [/p]

Figure 23: Create Field Panel

[p]The options for each field are the same as those for modifying fields in the [code]Field[/code] section. [/p]

Creating Indices

[p]The database browser allows the indices of SQLite and MySQL tables to be manipulated. [/p]

[p]An index in a MySQL database is an ordered list of the contents of a field or group of fields in a table (also known as the [code]MYI[/code] file in the MySQL database folder). Indices in MySQL databases allow faster searches to be performed for the fields specified, and up to 25 indices can be specified for a MySQL table. For more information on MySQL indices, see the MySQL documentation. [/p]

[p]The [code]Indices[/code] page allows to administrator to view the current indices for a selected table, as well as create new indices that contain one or more fields. [/p]

Figure 24: Indices Page

[p]The [code]Index Listing[/code] panel shows a list of current indices in the current table. The administrator can view details for an index by selecting the index name, which will show the details in the [code]Index Detail[/code] panel to the right. [/p]

Creating Indices

[p]Selecting the [code]Create Index[/code] button show the [code]Create Database[/code] panel to the right. The [code]Create Index[/code] panel is where the administrator can create new indices for the selected table. Unlike setting the index options for individual fields in the [code]Fields[/code] page, indices created in the [code]Index Detail[/code] panel may contain more than one field. A single index may contain up to 16 unique fields. To learn about the benefits associated with multiple field indexing, see the datasource documentation. [/p]

Figure 25: Create Index Panel

To create a new index:

1. Type a name for the index.

2. Select the fields that will comprise the index. In MySQL the entire lenght of [code]CHAR[/code] or [code]VARCHAR[/code] fields will be index and the first 255 characters of [code]TEXT[/code] fields.

3. Select the type of index. The following types are available.

• [code]Normal[/code] – The default index type. Available in SQLite and MySQL.
• [code]Unique[/code] – The index will constrain the fields to contain only unique values. Available in SQLite or MySQL.
• [code]Full Text[/code] – A full-text index. Available only in MySQL
• [code]Spatial[/code] – A spatial search index. Available only in MySQL.

4. Select the [code]Create[/code] button The new index should appear in the[code] Index Listing [/code]panel to the left.

[note][b]Note: [/b]Primary key fields can be established in the Fields section. [/note]

Index Detail

[p]The [code]Index Detail[/code] panel shows all information available from the database for the selecting index. The following information is displayed: [/p]

• Name – The name of the index.
• Field – The names of the fields that the index contains. Multiple fields are delineated as [code]Field 1[/code], [code]Field 2[/code], etc.
• Type – The index type. For a list of possible types, see the next section.

[p]The administrator may delete an index by selecting the [code]Delete[/code] button in the [code]Index Detail [/code]panel. Indices cannot be edited, only created and deleted. A confirmation message will always be displayed before an index is deleted. [/p]