How do I create settings for a database?
satya - 8/29/2020, 4:37:35 PM
Goal
1. As of 2020 document how you setup a database in aspire properties files
2. I will start with some relevant URLs where this may have been documented or related URLs
3. As the article progresses you will see the needed complete setup for setting up a database
4. Likely example is going to be using SQLServr
satya - 8/29/2020, 4:38:32 PM
You need to setup a ConnectionPool first. One of the connection pools is documented here
You need to setup a ConnectionPool first. One of the connection pools is documented here
satya - 8/29/2020, 4:40:28 PM
Here is an example of microsoft access database properties file
Here is an example of microsoft access database properties file
satya - 8/29/2020, 4:43:10 PM
A connection pool needs to be initialized
A connection pool needs to be initialized
satya - 8/29/2020, 4:43:30 PM
Here is how to use/configure initializers in Aspire
satya - 8/29/2020, 5:14:57 PM
Listing to initialize the connection pool for any JDBC database
#
# You can place these in database.properties
# it is merely a suggested name for the file.
#
#
# A connection pool implementation
# A scheduler is required for this pool to work
# The corresponding scheduler is defined as well
#
request.AppObjects.connectionManager.className=com.ai.db.cp4.ConnectionPoolConnectionManager4
request.AppObjects.scheduler.className=com.ai.scheduler.BasicScheduler
AppObjects.scheduler.timer_interval_in_milli_secs=120000
#Not necessary to preload connections
#request.AppObjects.connectionManager.preload-datasources=reportsDB
#Use this line in aspire.properties files
#If more than one initializer, use comma separated list
Aspire.startup.initializers=AppObjects.connectionManager
satya - 8/31/2020, 10:37:37 AM
Some notes
1. Preloading connections is meaningful in a webserver. for stand alone applications no need to preload.
2. For a stand alone application one may not need a pool. You may even use a SimpleConnectionManager which merely opens and closes a connection each time.
satya - 8/31/2020, 10:38:19 AM
You may want to put this in a properties file like database.properties
although name is arbitrary
satya - 8/31/2020, 10:39:05 AM
And then include that database.properties in the aspire.properties file
application.includeFiles=aspire:\\aspire-batch\\test-csv.properties,aspire:\\aspire-batch\\database.properties
satya - 8/31/2020, 10:41:27 AM
Nature of aspire.properties
1. This is the root properties file (name is arbitrary) that includes all other properties files as includes
2. Contains main objects to bootstrap the application
3. Typically contains Factory objects like
4. Configuration
5. Logging
6. Factories
7. Initializers
8. Connection and Http Event handlers
9. Security
10. etc.
satya - 8/31/2020, 10:42:36 AM
It also varies
1. from application to application
2. far more extensive for a full blown web app
3. A very simple one for command line applications
4. Database access adds a few more entries
satya - 8/31/2020, 10:42:57 AM
I will add a reference here from github soon for some representative files
I will add a reference here from github soon for some representative files
satya - 8/31/2020, 10:46:05 AM
Now back to specifying a database in the properties file
#**************************************************************************
# SQL Server database definition
#**************************************************************************
#Database.name = forecastdb
Database.forecastdb.jdbc_driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
Database.forecastdb.connection_string=\
jdbc:sqlserver://localhost\\MSSQLSERVER;database=db-name
Database.forecastdb.userid=
Database.forecastdb.password=
Database.forecastdb.expirationTimeInMin=1
Database.forecastdb.minimumNumberOfConnections=0
#**************************************************************************
#********** Database aliases
#**************************************************************************
Database.alias.testdb = forecastdb
satya - 8/31/2020, 10:47:02 AM
Few notes on backslashes above
1. Make sure there are no spaces for line continuation back slashes
2. Due to a current bug in my code I had to put here that extra space
satya - 8/31/2020, 10:48:58 AM
Note on aliases
1. Aliases allow to quickly switch the target database name like "testdb" to point to different databases that are defined. like between test, stage, and prod
2. It is the alias name "testdb" that is used in subsequent property file references to the database
satya - 8/31/2020, 10:49:12 AM
So in the above definitions the database name you will refer to is "testdb"
So in the above definitions the database name you will refer to is "testdb"
satya - 8/31/2020, 10:52:27 AM
This is a configuration for MS SQL Server
1. This will change from sql server to Oracle etc.
2. The change is as needed by the respective jdbc driver
3. Expect a good bit of time getting the jdbc spec right
satya - 8/31/2020, 10:56:08 AM
Here are notes on setting up JDBC drivers for MS SQL Server
satya - 8/31/2020, 10:57:07 AM
Here is now how you can test the database using Aspire database reads
#**************************************************************************
#********** Test database
#**************************************************************************
request.testdatabase.classname=com.ai.db.DBRequestExecutor2
request.testdatabase.db=testdb
request.testdatabase.stmt=select f_contact_name from t_vre_contacts
satya - 8/31/2020, 10:57:25 AM
You can use any SQL statement you like to test this
You can use any SQL statement you like to test this
satya - 8/31/2020, 11:00:47 AM
Briefly
1. com.ai.db.DBRequestExecutor2 is a "reusable part" that knows how to execute basice CRUD operations on a database based on the configuration supplied
2. Calling Java will invoke this with 2 arguments
3. Arg1: The name of the request "testdatabase"
4. Arg2: A map of key value pairs which can be used in the CRUD statements. The map have come from URL or any other context including property files themselves
5. This simple segment can get very elaborate and extensive delivering HDS (hierarchical data sets) that can be rendered as HTML, XML, JSON, Java etc.
satya - 8/31/2020, 11:02:02 AM
runTestCollection.cmd
1. There is a program called runTestCollection.cmd that can be used to test these property file database invocations
2. I will provide a github link soon
satya - 8/31/2020, 11:45:57 AM
The following files I have put them on github here
The following files I have put them on github here
aspire.properties
database.propeerties
runTestCollection.cmd