Appboard/2.5/builder/data sources/database - Revision history

imported>Jason.nicholls: /* Database Query */

2015-03-26T09:58:31Z

Database Query

← Older revision		Revision as of 09:58, 26 March 2015
Line 13:		Line 13:
	This adapter provdies the ability to:		This adapter provdies the ability to:

	* ~~Using~~ expressibility of SQL to be very specific about the data returned.		* Use expressibility of SQL to be very specific about the data returned.
	* ~~Using~~ expressibility of SQL to perform complex joins, transformations, and grouping		* Use expressibility of SQL to perform complex joins, transformations, and grouping
	* Access to database functions to perform all variety of tasks such as data conversion / manipulation.		* Access to database functions in queries to perform all variety of tasks such as data conversion / manipulation.
	* Executing stored procedures.		* Executing stored procedures.
	* Dynamic queries to fetch specific data versus complete blanket data and then filtering within AppBoard - for various reasons including performance.		* Dynamic queries to fetch specific data versus complete blanket data and then filtering within AppBoard - for various reasons including performance.

imported>Jason.nicholls: /* Database Query */

2015-03-26T09:57:57Z

Database Query

← Older revision		Revision as of 09:57, 26 March 2015
Line 11:		Line 11:
	The Database Query data adapter is a frequently used and very flexible adapter as it provides a way to execute arbitrary SQL against an RDBMS and then consume the resulting data set. The set of SQL supported depends on the JDBC driver used, and the underlying database.		The Database Query data adapter is a frequently used and very flexible adapter as it provides a way to execute arbitrary SQL against an RDBMS and then consume the resulting data set. The set of SQL supported depends on the JDBC driver used, and the underlying database.

	* Using expressibility of SQL to be very specific about the data returned		This adapter provdies the ability to:

			* Using expressibility of SQL to be very specific about the data returned.
	* Using expressibility of SQL to perform complex joins, transformations, and grouping		* Using expressibility of SQL to perform complex joins, transformations, and grouping
	* Access to database functions to perform all variety of tasks such as data conversion / manipulation.		* Access to database functions to perform all variety of tasks such as data conversion / manipulation.

imported>Jason.nicholls at 09:49, 26 March 2015

2015-03-26T09:49:59Z

← Older revision		Revision as of 09:49, 26 March 2015
Line 1:		Line 1:
	{{DISPLAYTITLE:Database Data Adapters}}		{{DISPLAYTITLE:Database Data Adapters}}
	[[Category:AppBoard 2.5]]		[[Category:AppBoard 2.5]]
			== Overview ==

	This page provides details for configuring database adapters in AppBoard. For information on creating the database adapter, or other AppBoard adapters, see [[appboard/2.5/builder/data_sources\|AppBoard Data Sources]]		This page provides details for configuring database adapters in AppBoard. For information on creating the database adapter, or other AppBoard adapters, see [[appboard/2.5/builder/data_sources\|AppBoard Data Sources]]


	The two types of database adapters to select from are <b>Database Query</b> or <b>Database Table</b>.		The two types of database adapters to select from are <b>Database Query</b> or <b>Database Table</b>.

	==Database Query==		===Database Query===

	The Database Query data adapter is a frequently used and very flexible adapter as it provides a way to execute arbitrary SQL against an RDBMS and then consume the resulting data set. The set of SQL supported depends on the JDBC driver used, and the underlying database.		The Database Query data adapter is a frequently used and very flexible adapter as it provides a way to execute arbitrary SQL against an RDBMS and then consume the resulting data set. The set of SQL supported depends on the JDBC driver used, and the underlying database.
Line 16:		Line 17:
	* Dynamic queries to fetch specific data versus complete blanket data and then filtering within AppBoard - for various reasons including performance.		* Dynamic queries to fetch specific data versus complete blanket data and then filtering within AppBoard - for various reasons including performance.

	=== Dynamic Queries ===		==== Dynamic Queries ====

	It is possible to create queries with dynamic components that can depend on session information, evaluating some expression, or through user interaction via Server Side Filter actions. For more information refer to the [[appboard/2.5/builder/dynamic_queries\|Dynamic Queries]] documentation.		It is possible to create queries with dynamic components that can depend on session information, evaluating some expression, or through user interaction via Server Side Filter actions. For more information refer to the [[appboard/2.5/builder/dynamic_queries\|Dynamic Queries]] documentation.

	==Database Table==		===Database Table===

	This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. It is equivalent to executing <tt>select * from <table_name></tt> for the selected tables.		This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. It is equivalent to executing <tt>select * from <table_name></tt> for the selected tables.
Line 185:		Line 186:


	==~~== Detailed~~ Database Configuration ====		== Advanced Database Configuration ==

	There are a number of low-level database configuration parameters defined in <tt>[INSTALL_HOME]/server/webapps/enportal/WEB-INF/config/appboard.properties</tt>. Note that as with all property changes, the recommended approach is to make the change to the property in <tt>appboard-custom.properties</tt> so that the changes will persist across upgrades.		There are a number of low-level database configuration parameters defined in <tt>[INSTALL_HOME]/server/webapps/enportal/WEB-INF/config/appboard.properties</tt>. Note that as with all property changes, the recommended approach is to make the change to the property in <tt>appboard-custom.properties</tt> so that the changes will persist across upgrades.

imported>Jason.nicholls: /* Configure Database Connection Settings */

2015-03-26T09:45:48Z

Configure Database Connection Settings

Show changes

imported>Jason.nicholls: /* Database Table */

2015-03-26T09:43:40Z

Database Table

← Older revision		Revision as of 09:43, 26 March 2015
Line 24:		Line 24:
	This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. It is equivalent to executing <tt>select * from <table_name></tt> for the selected tables.		This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. It is equivalent to executing <tt>select * from <table_name></tt> for the selected tables.

			{{Warning\|Although this adapter can be convenient for exploring a database schema it is recommended to use the Database Query adapter for the reasons outlined in the section above. In addition some databases have extremely large schema, such as Oracle, and it is very important to limit the set of tables discovered using the ''Table Name Regex'' field.}}

	== Database Data Source Configuration ==		== Database Data Source Configuration ==

imported>Jason.nicholls: /* Database Query */

2015-03-26T09:41:03Z

Database Query

← Older revision		Revision as of 09:41, 26 March 2015
Line 8:		Line 8:
	==Database Query==		==Database Query==

	~~This~~ adapter ~~retrieves data from~~ a RDBMS ~~using SQL queries to create a~~ data set. ~~This is frequently~~ used ~~to transform data through~~ the ~~use of SQL to prepare data for use in AppBoard~~.		The Database Query data adapter is a frequently used and very flexible adapter as it provides a way to execute arbitrary SQL against an RDBMS and then consume the resulting data set. The set of SQL supported depends on the JDBC driver used, and the underlying database.

	When using the Database Table data adapter, you are presented with the visible tables and columns within those tables with little control over how they are formatted or filtered. This is quick and easy when the data ~~exists in the form it is needed, but often there is a need for more control. Examples of situations where the Database Query adapter is required include:~~		* Using expressibility of SQL to be very specific about the data returned
			* Using expressibility of SQL to perform complex joins, transformations, and grouping
	*~~the required data comes from a~~ SQL ~~stored procedure~~		* Access to database functions to perform all variety of tasks such as data conversion / manipulation.
	*~~some SQL calculations need~~ to ~~be done (~~such as ~~date calculations)~~		* Executing stored procedures.
	*~~complex joins need to be done in the source~~		* Dynamic queries to fetch specific data versus complete blanket data and then filtering within AppBoard - for various reasons including performance.
	*~~the raw~~ data ~~set is too large to handle in memory~~ and ~~you need to select a dynamic subset of that data~~

	=== Dynamic Queries ===		=== Dynamic Queries ===

	It is possible to create queries with dynamic components that can depend on session information, evaluating some expression, or through user interaction via Server Side Filter actions. For more information refer to the [[appboard/2.5/builder/dynamic_queries\|Dynamic Queries]] documentation.		It is possible to create queries with dynamic components that can depend on session information, evaluating some expression, or through user interaction via Server Side Filter actions. For more information refer to the [[appboard/2.5/builder/dynamic_queries\|Dynamic Queries]] documentation.

	==Database Table==		==Database Table==

imported>Jason.nicholls at 09:26, 26 March 2015

2015-03-26T09:26:58Z

← Older revision		Revision as of 09:26, 26 March 2015
Line 4:		Line 4:


	The two types of database adapters to select from are <b>Database ~~Table~~</b> or <b>Database ~~Query~~</b>.		The two types of database adapters to select from are <b>Database Query</b> or <b>Database Table</b>.


	~~==Database Table==~~

	~~This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. It is equivalent to executing <tt>select * from <table_name></tt> for the selected tables.~~


	==Database Query==		==Database Query==
Line 23:		Line 17:
	*the raw data set is too large to handle in memory and you need to select a dynamic subset of that data		*the raw data set is too large to handle in memory and you need to select a dynamic subset of that data

	=== ~~Using SHIM Expressions in~~ Queries ===		=== Dynamic Queries ===

			It is possible to create queries with dynamic components that can depend on session information, evaluating some expression, or through user interaction via Server Side Filter actions. For more information refer to the [[appboard/2.5/builder/dynamic_queries\|Dynamic Queries]] documentation.
			==Database Table==

	~~In many cases, you will want to pass~~ a ~~parameter value into~~ a ~~query rather than using a static query definition~~. ~~AppBoard provides a system of variable expressions, called SHIM, that can be used~~ for ~~this purpose. For instructions on this option, see [[appboard/2.5/builder/shim_expressions\|SHIM Expressions]]~~.		This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. It is equivalent to executing <tt>select * from <table_name></tt> for the selected tables.

imported>Jason.nicholls: /* Database Query */

2015-01-08T11:47:26Z

Database Query

← Older revision		Revision as of 11:47, 8 January 2015
Line 125:		Line 125:
	You can configure one or more queries in each Database Query adapter configuration. Each configured query will have a different name, and result in a different Data Collection with that name. When naming a query, only alphanumeric characters, underscores, and hyphens are permitted.		You can configure one or more queries in each Database Query adapter configuration. Each configured query will have a different name, and result in a different Data Collection with that name. When naming a query, only alphanumeric characters, underscores, and hyphens are permitted.

	By default only new or edited queries are actually run against the database to ensure the query is valid. This is done to prevent delays when editing the data source for cases where there are many queries. To force an existing query to be re-validated/discovered without changing it or losing any configuration then update the ''SQL Query'' with a trivial change such as adding a space character.		By default only new or edited queries are actually run against the database to ensure the query is valid. This is done to prevent delays when editing the data source for cases where there are many existing queries. To force an existing query to be re-validated/discovered without changing it or losing any configuration then update the ''SQL Query'' with a trivial change such as adding a space character.

	The table below details the settings you can configure for a <b>Database Query</b> Data Source:		The table below details the settings you can configure for a <b>Database Query</b> Data Source:

imported>Jason.nicholls: /* Database Query */

2015-01-08T11:46:47Z

Database Query

← Older revision		Revision as of 11:46, 8 January 2015
Line 123:		Line 123:
	==== Database Query ====		==== Database Query ====

	You can configure one or more queries in each Database Query adapter configuration. Each configured query will have a different name, and result in a different Data Collection with that name. When naming a query, only alphanumeric characters, underscores, and hyphens are permitted.		You can configure one or more queries in each Database Query adapter configuration. Each configured query will have a different name, and result in a different Data Collection with that name. When naming a query, only alphanumeric characters, underscores, and hyphens are permitted.

			By default only new or edited queries are actually run against the database to ensure the query is valid. This is done to prevent delays when editing the data source for cases where there are many queries. To force an existing query to be re-validated/discovered without changing it or losing any configuration then update the ''SQL Query'' with a trivial change such as adding a space character.

	The table below details the settings you can configure for a <b>Database Query</b> Data Source:		The table below details the settings you can configure for a <b>Database Query</b> Data Source:

imported>Jason.nicholls: /* Detailed Database Configuration */

2015-01-06T07:56:17Z

Detailed Database Configuration

← Older revision		Revision as of 07:56, 6 January 2015
Line 186:		Line 186:
	==== Detailed Database Configuration ====		==== Detailed Database Configuration ====

	There are a number of low-level database configuration parameters ~~that can be manipulated~~ in webapps/enportal/WEB-INF/config/appboard.properties. Note that as with all property changes, the recommended approach is to make the change to the property in appboard-custom.properties so that the changes will persist across upgrades.		There are a number of low-level database configuration parameters defined in <tt>[INSTALL_HOME]/server/webapps/enportal/WEB-INF/config/appboard.properties</tt>. Note that as with all property changes, the recommended approach is to make the change to the property in <tt>appboard-custom.properties</tt> so that the changes will persist across upgrades.

	Note that each parameter is documented in appboard.properties, and ideally the system defaults should be suitable for most users. However, under some high-load circumstances these are the parameters that may be of interest:		Note that each parameter is documented in appboard.properties, and ideally the system defaults should be suitable for most users. However, under some high-load circumstances these are the parameters that may be of interest: