How do I create settings for a database?

How do I create settings for a database?

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

You need to setup a ConnectionPool first. One of the connection pools is documented here

Here is an example of microsoft access database properties file

A connection pool needs to be initialized

Here is how to use/configure initializers in Aspire


#
# 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

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.

although name is arbitrary


application.includeFiles=aspire:\\aspire-batch\\test-csv.properties,aspire:\\aspire-batch\\database.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.

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

I will add a reference here from github soon for some representative files


#**************************************************************************
# 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

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

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

So in the above definitions the database name you will refer to is "testdb"

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

Here are notes on setting up JDBC drivers for MS SQL Server


#**************************************************************************
#********** Test database
#**************************************************************************
request.testdatabase.classname=com.ai.db.DBRequestExecutor2
request.testdatabase.db=testdb
request.testdatabase.stmt=select f_contact_name from t_vre_contacts

You can use any SQL statement you like to test this

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.

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

The following files I have put them on github here


aspire.properties
database.propeerties
runTestCollection.cmd