You define the database parameters in two configuration files underneath your konakart installation directory at:

webapps\konakart\WEB-INF\classes\konakart.properties
  and
webapps\konakartadmin\WEB-INF\classes\konakartadmin.properties

(Therefore, the default on Windows will be at C:\KonaKart\webapps\konakart\WEB-INF\classes\konakart.properties).

Inside these files you will find: (the url parameter is broken into two lines for readability only - you should keep it on one line)

# -----------------------------------------------------------------------------------
#  D A T A B A S E   P R O P E R T I E S
#  Database Connection Parameters Set by Installer on 20-Jan-2009
# -----------------------------------------------------------------------------------

torque.applicationRoot = .

torque.database.default                     = store1

torque.database.store1.adapter              = mysql
torque.dsfactory.store1.connection.driver   = com.mysql.jdbc.Driver
torque.dsfactory.store1.connection.url      = 
   jdbc:mysql://localhost:3306/store1?zeroDateTimeBehavior=convertToNull&useSSL=false
torque.dsfactory.store1.connection.user     = fruit
torque.dsfactory.store1.connection.password = secret

-------------------------------------------------------------------------------------

Leave the torque.database.default equal to store1.

You need to set the five parameters appropriate for your environment:

If you wish you can encrypt the database credentials in the KonaKart properties files by using the CreatePassword utility that is provided in the Business or Enterprise Editions.

You can obtain usage information for the utility by issuing the "-?" parameter as follows:

@blackburn:~/konakart/utils/createPassword: ./createPassword.sh -?
==================================================================================================
KonaKart Create Password Utility
==================================================================================================

Usage: CreatePassword
     -f  properties-file        - the properties file where the results
                                  will be written
     -k                         - the key of the property to encrypt
     -p  new-value              - the new-value to encrypt (typically,
                                  this will be the username or password)
    [-en file encoding]         - file encoding (default is ISO-8859-1)
    [-ce console encoding]      - console encoding (default is utf-8)
    [-d]                        - outputs debug information
    [-?]                        - shows this usage information
    

A ReadMe.txt file is provided in the createPassword directory with instructions on how to use the utility.

After running the utility you end up with an encrypted password in the specified properties along with an associated encryption key.

KonaKart itself has been modified to recognise and decrypt these encrypted passwords by using the associated encryption keys.

If you create the encrypted passwords for copying into another properties file elsewhere, always remember that the encrypted database password needs its associated encryption key - so remember to copy both properties! These associated encryption key values are written on the line following the property itself so they are easy to locate.

If you specify the "-p new-value" parameter the utility will encrypt the specified "new-value". If you do not specify the "-p new-value" parameter at all, the utility will encrypt the current value of the property.

Note that it is also possible to encrypt the database username in exactly the same way as the password. KonaKart will also handle encrypted usernames in the properties files.

It is also possible to define your data source using JNDI. (For various optional configurations not covered here please refer to the Torque Database Configuration documentation on the Apache site.)

In this example Torque is configured to create a JNDI Data Source and deploy it into JNDI:

You would modify your konakart.properties files by commenting out the connection properties used by the default SharedPoolDataSourceFactory factory and replace them as follows:

torque.dsfactory.store1.factory              = org.apache.torque.dsfactory.JndiDataSourceFactory
torque.dsfactory.store1.jndi.path            = jdbc/konakart
torque.dsfactory.store1.jndi.ttl             = 60000
torque.dsfactory.store1.datasource.classname = org.apache.commons.dbcp.BasicDataSource

torque.database.store1.adapter               = mysql

torque.dsfactory.store1.jndi.java.naming.factory.initial = \
                                                     org.apache.naming.java.javaURLContextFactory

torque.dsfactory.store1.datasource.driverClassName = com.mysql.jdbc.Driver
torque.dsfactory.store1.datasource.url      = \
       jdbc:mysql://localhost:3306/store1?zeroDateTimeBehavior=convertToNull&useSSL=false
torque.dsfactory.store1.datasource.username = fruit 
torque.dsfactory.store1.datasource.password = secret

#For JNDI you also need to comment out the following line (as it has been replaced above):
#torque.dsfactory.store1.factory=org.apache.torque.dsfactory.SharedPoolDataSourceFactory

In this example Tomcat (v7.0.54) is configured to create a JNDI Data Source and deploy it into JNDI and the konakart.properties definition binds to that existing data source: (Note that different definitions will be required for other application servers so refer to the documentation on those for detailed configuration specifications).

In conf/context.xml:

<Resource name="jdbc/villa1" auth="Container" type="javax.sql.DataSource"
  maxActive="100" maxIdle="30" maxWait="10000"
  username="kkUser" password="fred" driverClassName="com.mysql.jdbc.Driver"
  url="jdbc:mysql://localhost:3306/villa1?zeroDateTimeBehavior=convertToNull&useSSL=false"
/>

In your konakart.properties and konakartadmin.properties files:

torque.dsfactory.store1.factory              = org.apache.torque.dsfactory.JndiDataSourceFactory
torque.database.default                      = store1

torque.dsfactory.store1.jndi.path            = java:/comp/env/jdbc/villa1
torque.dsfactory.store1.jndi.java.naming.factory.initial     \
                                             = org.apache.naming.java.javaURLContextFactory
torque.dsfactory.store1.jndi.java.naming.factory.url.pkgs    \
                                             = org.apache.naming
torque.dsfactory.store1.jndi.ttl             = 60000

#For JNDI you also need to comment out the following line (as it has been replaced above):
#torque.dsfactory.store1.factory=org.apache.torque.dsfactory.SharedPoolDataSourceFactory

For the database definition for the BIRT reporting engine you need to leave the original connection properties either in a separate properties file or in konakartadmin.properties as follows:

# Leave these for the BIRT reports

torque.dsfactory.store1.connection.driver   = com.mysql.jdbc.Driver
torque.dsfactory.store1.connection.url      = \
   jdbc:mysql://localhost:3306/store1?zeroDateTimeBehavior=convertToNull&useSSL=false
torque.dsfactory.store1.connection.user     = fruit
torque.dsfactory.store1.connection.password = secret

Note that if you chose to use a separate file, remember to add that file name to the "var dbPropsFile" definition in the "...\webapps\birtviewer\reports\lib\konakart.rptlibrary" file.

In addition to the settings above, you have to set the validationQuery for DB2 and Oracle slightly differently to the others, as follows. This section is defined just below the database parameter definitions in the two properties files (konakart.properties and konakartadmin.properties):

# The SQL query that will be used to validate connections from this pool before 
# returning them to the caller. If specified, this query MUST be an SQL SELECT 
# statement that returns at least one row. 
# Recommended settings:
# for MySQL/PostgreSQL/MS SQL use: SELECT 1
# for Oracle                  use: SELECT 1 from dual
# for DB2                     use: SELECT 1 FROM sysibm.sysdummy1

torque.dsfactory.store1.pool.validationQuery=SELECT 1
#torque.dsfactory.store2.pool.validationQuery=SELECT 1

-------------------------------------------------------------------

Note that the SQL that is optionally run at installation time uses the "DROP TABLE IF EXISTS TABLE-NAME;" command. This works fine for PostgreSQL 8.2 and above (which support "IF EXISTS") but not for earlier versions.

This is only a problem during the installation process; KonaKart performs well on versions of PostgreSQL prior to and after 8.2. To workaround this problem, for example for PostgreSQL 8.1, you will have to edit the database/konakart_demo.sql file and remove all the "DROP TABLE" commands then run this SQL manually. In addition to modifying the "IF EXISTS" syntax you will also have to add SQL statements to create sequences for all the SERIAL primary keys. For example, for the counter table, which is created like this:

CREATE TABLE counter (
  counter_id SERIAL,
  startdate char(8),
  counter integer,
  PRIMARY KEY (counter_id)
);

You have to create the SEQUENCEs with these conventions:

CREATE SEQUENCE <table>_<SERIAL column>_seq

for example:

.. for the counter_id column in the counter table above, you need to create a SEQUENCE like this:

CREATE SEQUENCE counter_counter_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

Another, preferable, alternative is to update to the latest version of PostgreSQL and run the standard GUI installation and have all the data loaded by the installer.

Note that the SQL that is optionally run at installation time uses the "DROP TABLE IF EXISTS" syntax. This works fine for MySQL 5 and above, but not on MySQL 4.1.

This is only a problem during the installation process; KonaKart performs well on MySQL 4.1 (and above) but you have to modify the konakat_demo.sql file and run it yourself manually. You have to edit the database/konakart_demo.sql file and remove all the "DROP TABLE" commands then run this SQL manually. After successfully running this SQL you will be able to run KonaKart with MySQL 4.1.

From v8.9.0.0 of KonaKart, MySQL 8.0 is supported but currently only if MySQL is configured to use legacy or mysql_native_password authentication mode.

From v9.6.0.0 of KonaKart, only MySQL 5.7 and 8.0 are supported.

The default database definition is fine if you don't need to support double-byte unicode characters in every column. If you do need to support double-byte unicode (eg. Chinese characters) in additional columns you will need to convert the selected varchar columns to nvarchar columns. This isn't done by default for every textual column in the schema in order to maximise performance for those cases where the special characters are not required. Note that the Unicode columns will take up twice as many bytes (two per "character") as the non-Unicode columns (one per character) so you may also need to increase the size of columns that you convert to Unicode.

From v8.1.0.0 of KonaKart the default set-up for SQL Server uses NVARCHAR for columns where it is expected that Unicode data may be required. For example, NVARCHAR is used for columns that are populated by users (such as first name, last name etc).

From v8.7.0.0 of KonaKart the default adapter ("torque.database.store1.adapter = msssql" in the database definition in the properties files) used for SQL Server takes advantage of the "OFFSET A ROWS FETCH NEXT B ROWS ONLY" syntax for improved paging performance. Note that this syntax was only introduced for SQL Server 2012 and won't work in earlier versions of that database. For earlier versions you must use the mssql2008 adapter (set "torque.database.store1.adapter = mssql2008" in the database definition in the properties files).