SQL programming / SQL portability |
Creating a database from within a program requires special consideration.
CREATE DATABASE mydb WITH BUFFERED LOG
Such instruction performs an implicit connection to the database server (i.e., no CONNECT TO or DATABASE is required before a CREATE DATABASE), and leads to a default connection.
When using a database server engine, the creation of a database entity is not a trivial operation. The process usually requires additional tasks such as data storage configuration, database user creation, data access policy, and so on. These tasks are typically left to the database administrator.
Database creation statements such as CREATE DATABASE, CREATE DBSPACE, and DROP DATABASE can be used in programs connected to an IBM® Informix® server, but these statements are not portable. Use database creation statements only for development or testing purpose.Mobile applications usually create their database at first execution. Database creation on a mobile device is a much simpler operation than database creation on a database server. For example, with SQLite, creating a database only requires creating an empty file.
The SQLite database file must be created in the application sandbox, in a writable directory. This directory is specific to the type of mobile device, and can be found in programs with the os.Path.pwd method.
To build the full path to the database file, get the current working directory (os.Path.pwd()) and add this path to the database file name. This defines the source specification in the database connection parameters, to build the string used for the CONNECT instruction:
IMPORT os ... DEFINE dbfile, source, connstr VARCHAR(256) FUNCTION init_connection_strings() LET dbfile = "contacts.dbs" LET source = os.Path.join(os.Path.pwd(), dbfile) LET connstr = SFMT("contacts+source='%1'", source) IF NOT base.Application.isMobile() THEN -- Add db driver spec when in development mode LET connstr = connstr, ",driver='dbmsqt'" END IF END FUNCTIONIf not specified, the source connection parameter (i.e., the path to the database file) defaults to the database name specification in the CONNECT instruction. Thus, the source='dbpath' parameter is usually omitted, and dbpath is specified directly as the database name. In this case, however, the identifier of the database connection is the complete path to the SQLite database file. For more details about database connection parameters, see Database connections.
IMPORT os ... CALL init_connection_strings() IF os.Path.exists(source) THEN CONNECT TO connstr AS "c1" ELSE CALL create_empty_file(source) CONNECT TO connstr AS "c1" CALL create_database_tables() END IF ... FUNCTION create_empty_file(fn) DEFINE fn STRING DEFINE ch base.Channel() LET ch = base.Channel.create() CALL ch.openFile(fn,"w") CALL ch.close() END FUNCTION
Instead of creating an empty database file, it is also possible to prepare a template (pre-configured) SQLite database file on the development platform, deploy the template database with the other program files, and copy the template file from the program files directory (base.Application.getProgramDir) into the working directory (os.Path.pwd) on the first application execution (i.e. when the database file in the working directory does not yet exist):
IMPORT os ... CALL init_connection_strings() IF NOT prepare_database("template.dbs", source) THEN ERROR "Could not prepare database" EXIT PROGRAM 1 END IF CONNECT TO connstr AS "c1" ... FUNCTION prepare_database(template, target) DEFINE template, target STRING DEFINE tplpath STRING IF os.Path.exists(target) THEN RETURN TRUE END IF LET tplpath = os.Path.join(base.Application.getProgramDir(), template) IF NOT os.Path.exists(tplpath) THEN ERROR "Database template file not found" RETURN FALSE END IF RETURN os.Path.copy(tplpath, target) END FUNCTION