The general restsql.properties is set through a System Property, "org.restsql.properties". The value is an absolute path to your properties file, e.g. /etc/opt/restsql/restsql.properties. The WAR mode should use a context-param in the web.xml to set this (See Installation section later for details). The JAR mode will default to default-restsql.properties (source location: restsql/src/resources/properties) that is included in the jar.
Note: All path separators must use the forward slash, even on Windows. To refer to a path on Windows, for example c:\tools\restsql, use the form /tools/restsql, or if the app server is on a different drive, use file:///c:/tools/restsql.
The general restsql.properties contains the following configurations:
Logging configuration example:
# logging.facility=[log4j,java] # logging.config=relative/to/classpath # logging.dir=/absolute/path - this is only used by the /log service method to find logs logging.facility=log4j logging.config=com/business/config/log4j.properties logging.dir=/var/log/restsql
The location of SQL Resource definitions is critical. An example:
# sqlresources.dir=/absolute/path sqlresources.dir=/etc/opt/restsql/sqlresources
The Security configuration is optional. Here is an example:
# security.privileges=/absolute/path security.privileges=/etc/opt/restsql/privileges.properties
The Triggers configuration is optional. Here is an example:
# triggers.classpath=/absolute/path # triggers.definition=/absolute/path triggers.classpath=/etc/opt/restsql/triggers triggers.definition=/etc/opt/restsql/triggers.properties
The XML configuration is optional. The defaults are:
# request.useXmlSchema=[true, false] # response.useXmlDirective=[true, false] # response.useXmlSchema=[true, false]w request.useXmlSchema=false response.useXmlSchema=false response.useXmlDirective=false
The HTTP configuration is optional. See HTTP Configuration for more detail. The defaults are:
# http.response.cacheControl={cache-directive}, {cache-directive}, ...
http.response.cacheControl=no-cache, no-transform
The Database configuration is required. for a database with built-in support:
# database.driverClassName=x.x.x
# for MySQL use com.mysql.jdbc.Driver
# for PostgreSQL use org.postgresql.Driver
# database.url=jdbc:etc:etc
# for MySQL use jdbc:mysql://hostname:3306/
# for PostgreSQL use jdbc:postgresql://hostname:5432/{database-name}
# database.user=userName
# database.password=password
database.driverClassName=com.mysql.jdbc.Driver
database.url=jdbc:mysql://localhost:3306/
database.user=restsql
database.password=Rest00sql#
# DB-specific implementation classes - match the implementation to your database
# For MySQL:
# org.restsql.core.ColumnMetaData=org.restsql.core.impl.mysql.MySqlColumnMetaData
# org.restsql.core.SequenceManager=org.restsql.core.impl.mysql.MySqlSequenceManager
# org.restsql.core.SqlResourceMetaData=org.restsql.core.impl.mysql.MySqlSqlResourceMetaData
# org.restsql.core.SqlBuilder=org.restsql.core.impl.mysql.MySqlSqlBuilder
# org.restsql.tools.ResourceDefinitionGenerator=org.restsql.tools.impl.mysql.MySqlResourceDefinitionGenerator
# For PostgreSQL:
# org.restsql.core.ColumnMetaData=org.restsql.core.impl.ColumnMetaDataImpl
# org.restsql.core.SequenceManager=org.restsql.core.impl.postgresql.PostgreSqlSequenceManager
# org.restsql.core.SqlResourceMetaData=org.restsql.core.impl.postgresql.PostgreSqlSqlResourceMetaData
# org.restsql.core.SqlBuilder=org.restsql.core.impl.postgresql.PostgreSqlSqlBuilder
# org.restsql.tools.ResourceDefinitionGenerator=org.restsql.tools.impl.postgresql.PostgreSqlResourceDefinitionGenerator
org.restsql.core.ColumnMetaData=org.restsql.core.impl.mysql.MySqlColumnMetaData
org.restsql.core.SequenceManager=org.restsql.core.impl.mysql.MySqlSequenceManager
org.restsql.core.SqlResourceMetaData=org.restsql.core.impl.mysql.MySqlSqlResourceMetaData
org.restsql.core.SqlBuilder=org.restsql.core.impl.mysql.MySqlSqlBuilder
org.restsql.tools.ResourceDefinitionGenerator=org.restsql.tools.impl.mysql.MySqlResourceDefinitionGenerator
Implementation classes configuration is optional. The defaults are:
# Implementation classes - use these to customize the framework # org.restsql.core.Factory.ConnectionFactory=fully.qualified.class.name # org.restsql.core.Factory.RequestFactory=fully.qualified.class.name # org.restsql.core.Factory.RequestDeserializerFactory=fully.qualified.class.name # org.restsql.core.Factory.ResponseSerializerFactory=fully.qualified.class.name # org.restsql.core.Factory.SqlResourceFactory=fully.qualified.class.name # org.restsql.core.HttpRequestAttributes=fully.qualified.class.name # org.restsql.core.RequestLogger=fully.qualified.class.name # org.restsql.core.TableMetaData=fully.qualified.class.name # org.restsql.security.Authorizer=fully.qualified.class.name org.restsql.core.Factory.ConnectionFactory=org.restsql.core.impl.ConnectionFactoryImpl org.restsql.core.Factory.RequestFactory=org.restsql.core.impl.RequestFactoryImpl org.restsql.core.Factory.RequestDeserializerFactory=org.restsql.core.impl.serial.RequestDeserializerFactoryImpl org.restsql.core.Factory.ResponseSerializerFactory=org.restsql.core.impl.serial.ResponseSerializerFactoryImpl org.restsql.core.Factory.SqlResourceFactory=org.restsql.core.impl.SqlResourceFactoryImpl org.restsql.core.HttpRequestAttributes=org.restsql.core.impl.HttpRequestAttributesImpl org.restsql.core.RequestLogger=org.restsql.core.impl.RequestLoggerImpl org.restsql.core.TableMetaData=org.restsql.core.impl.TableMetaDataImpl org.restsql.security.Authorizer=org.restsql.security.impl.AuthorizerImpl org.restsql.service.monitoring.MonitoringManager=org.restsql.service.monitoring.MonitoringManagerImpl
Monitoring configuration is optional:
# Ganglia monitoring configuration monitoring.ganglia.host=hostName or ipAddress monitoring.ganglia.port=portNumber monitoring.ganglia.ttl=numberOfRouterHops monitoring.ganglia.udpMode=[unicast,multicast] monitoring.ganglia.frequency=seconds # Graphite monitoring configuration monitoring.graphite.host=hostName or ipAddress monitoring.graphite.port=portNumber monitoring.graphite.frequency=seconds
See the SDK for more detail on Security, Logging and Trigger configuration.
Access http://yourhost:port/restsql for links to the effective runtime configuration.
See restsql/service or restsql/service-sdk docker hub documentation.
Requirements: JEE Container, RDBMS, JAR tool
Properties Files: Create your two required properties files (restsql.properties and log4j.properties (or logging.properties), as above. Create your two optional privileges and triggers definitions if required. The restsql.properties can exist outside the restSQL webapp, however the log4j.properties/logging.properties must exist within the classpath in WEB-INF/classes. Note that it will not load properly if you put the logging properties in WEB-INF/lib. You do not have to create the logging directory or directories, e.g. /var/log/restsql. The logging frameworks will do this automatically, assuming the user running the java container can create the folder. In the previous case that would be write privilege on /var/log. Better to create it by hand and ensure it's writable to the user running the java container.
You can use this shortcut if you are using Tomcat, do not want restSQL Authentication or Authorization and Java Security Manager is disabled (the default for Tomcat). Add a Parameter entry that indicates your absolute path to your restsql.properties in your $TOMCAT_HOME/conf/context.xml, as in:
<Parameter name="org.restsql.properties" value="/etc/opt/restsql/restsql.properties" override="false" />
Place the unmodified WARs (but rename them restsql.war and restsql-sdk.war) in the $TOMCAT/webapps directory and bounce the server, or deploy the webapp using your favorite method.
This option avoids messing with the JAR tool (similar to the tar Unix/Linux tool) as in option 3. Drop the restsql-{version}.war in your $JEE_SERVER/webapps directory, but rename it to restsql.war so that the version number is not needed in URLs. Place restsql-sdk.war there as well, dropping the version number from the file name. Start/stop your JEE server. Most servers will expand be expand the WAR file into an exploded directory. Remove the WAR files just to play it safe.
Create the SDK database using the scripts (see the Installing the SDK section)
Edit the restsql/WEB-INF/web.xml pointing the
org.restsql.properties
parameter to the restsql-sdk/examples/properties/restsql-mysql.properties, assuming you are using MySQL. Edit the restsql-mysql.properties file, setting the
sqlresources.dir
property to restsql-sdk/examples/sqlresources.
Let's say the JEE server is Tomcat and is deployed in /usr/local/tomcat. The web.xml is found in /usr/local/tomcat/webapps/restsql/WEB-INF/web.xml. Set the
org.restsql.properties
parameter to /usr/local/tomcat/webapps/restsql-sdk/examples/properties/restsql-mysql.properties. Set the
sqlresources.dir
property to /usr/local/tomcat/webapps/restsql-sdk/examples/sqlresources.
Bounce your JEE server. See Getting Started section.
The following deployment option 3 discusses some other details you might want to consider for security and logging after you get it initially working.
The restsql-{version}.war contains the service and framework classes as well as dependencies. Extract it's contents to some temp area, e.g. /tmp/restsql. Use the standard jar tool that comes with your JRE/JDK. The command is jar -xf war-file-name. It extracts all contents in the current directory. The contents looks like:
restsql/
META-INF/
wadl/
WEB-INF/
index.html
web.xml: Change the restSQL WEB-INF/web.xml. The LifecycleManager needs to know where to load your restsql.properties. Here's the default:
<context-param>
<param-name>org.restsql.properties</param-name>
<param-value>/etc/opt/restsql/restsql.properties</param-value>
</context-param>
The default deployment descriptor (web.xml) contains login config (authentication method) and security constraints (authorization declarations). See the restSQL default deployment descriptor.
Disabling Authorization and Authentication: To disable authentication/authorization, simply remove or comment out the security-constraint and login-config elements in the web.xml. Security is disabled by default.
Enabling Authorization and Authentication: You may use the default security constraints and login config or change it to conform to your specific roles, realm and other requirements. More information on Web Application Security using deployment descriptors is available at http://java.sun.com/javaee/6/docs/tutorial/doc/bncbe.html. Or consult your container's documentation. Authentication mechanisms (credential management, user to role assigment) are typically container-specific/proprietary. You will also need to configure a privileges properties file and reference it in the restsql properties file. See the SDK's Security configuration for instructions.
Naming: You may deploy this as a single file or exploded war to your JEE container. Rename it from restsql-#.war to to restsql.war or webapps/restsql if you want the path to be http://yourhost:port/restsql. Containers generally use the war file name instead of the web.xml's id to name the web app. Additionally, the SDK's HTTP API Explorer will work without any customization.
Deploy: Copy your exploded war or war to your container's webapps dir and restart the container, or deploy the webapp in your preferred style. All third party dependencies are included in the war distribution in the WEB-INF/lib.
Java Security Manager: If Java Security is enabled in your container, permissions must be added to your container's policy file. restSQL requires:
However, restsql uses other libraries (jersey, jdbc, logging) which need some unknown combination of access permissions. The only configuration that has been demonstrated to work is to grant all permissions to restsql. For example for Tomcat, add this to the end of the ${TOMCAT_HOME}/conf/catalina.policy file:
grant codeBase "file:${catalina.base}/webapps/restsql/-" {
permission java.security.AllPermission;
};
Open
http://host:port/restsql
in your browser.
Note: If you receive a 500 response to any res query with the text "No suitable driver found", then the container cannot find your jdbc driver. This can usually be fixed by placing the database driver in some common server library location. This also occurs after deploying the restSQL.war to WebLogic using the console when the container is running. After a container restart, the driver is found.
Properties: Follow the instructions for Configuring restSQL.
Deploy: Copy jar to the classpath of your web app, e.g. WEB-INF/lib. The following third party dependencies also need to be in your classpath:
log4j is not necessary if your app uses Java Native Logging. restSQL has been tested with JRE 1.6 and Java Native Logging.
Additionally one of the following jdbc drivers is necessary for databases with built-in support:
Authorization and Authentication: restSQL will authorize SQL Resource operations. Your app will authenticate users and associate users with roles. You must provide a priviliges properties file and reference it in the restsql.properties. Your app will call restSQL's
Authorizer
and provide a
SecurityContext
implementation. See the SDK's Security configuration for more instructions.
Java Security Manager: If you run enable Java Security, restSQL requires read/write access (java.util.PropertyPermission) to the following system properties: org.restsql.properties, org.apache.commons.logging.Log and either log4j.configuration for log4j logging or java.util.logging.config.file for Java Native logging. Additionally it needs read access (java.io.FilePermission) to the various properties files, and SQL Resources and triggers directories.
The tool creates resource definition templates for all columns for each table in a database schema. The definitions are created in the user-provided subfolder. These resources can be immediately used, or modified and reloaded and moved as necessary.
The tool is accessed after deploying restsql from the Tools link on the home page, or from http://host:port/restsql/tools/. Enter the subfolder name (default is 'auto') and the database name. The definitions are created in the configured SQL Resources directory (in the restsql.properties file). These are viewable from the SQL Resources browser, i.e. http://host:port/restsql/res.
Requirements: JEE Container, JAR tool, MySQL or PostgreSQL, Web Browser
Database: The HTTP API Explorer requires access to an extended sakila database. It is extended for the restsql-sdk with new tables and data. Bash and Windows batch scripts are provided to create the base and extended database for MySQL and for PostgreSQL. The bash script is restsql-sdk/database/<database>/create-sakila.sh and the Windows batch script is restsql-sdk/database/<database>/create-sakila.bat, where database is either mysql or postgresql. You will need to change the user and password variables in the beginning of the script to an account that has database and table creation privileges.
Install restsql WAR mode as above
Deploy: Extract restsql-sdk-{version}.war to your container's webapps directory, e.g. /usr/local/tomcat/webapps/restsql-sdk.
Enable directory listings in your app container's config. For example for Tomcat, edit $TOMCAT_HOME/conf/web.xml section for the DefaultServlet or add the following to restsql-sdk/WEB-INF/web.xml, setting the directory listing value to true:
<servlet>
<servlet-name>default</servlet-name>
<servlet-class>org.apache.catalina.servlets.DefaultServlet</servlet-class>
<init-param>
<param-name>debug</param-name>
<param-value>0</param-value>
</init-param>
<init-param>
<param-name>listings</param-name>
<param-value>true</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
The SDK war contents are:
restsql-sdk/
api-explorer/
database/
defaults/
doc/
examples/
javadoc/
META-INF/
wadl/
WEB-INF/
You can either change restSQL's sqlresources.dir to point to the SDK's sqlresources path, or copy the SDK's resources to the existing restSQL's sqlresources.dir (default is /etc/opt/restsql/sqlresources). Copying is easier:
cp /usr/local/tomcat/webapps/restsql-sdk/examples/sdk /etc/opt/restsql/sqlresources/sdk
Restart the container or use your preferred deloyment method. To see if the sdk resources are available, check the restSQL resource listing in the console, at http://host:port/restsql/res/. You should see six resources with names like sdk.Country, sdk.film.Film, etc.
Troubleshooting: The HTTP API Explorer requires access to a restsql service instance. If you have not deployed restsql to the same host/port as the SDK and to the location /restsql, then you will need to make one small tweak. Change two Javascript variables in restsql-sdk/api-explorer/index.html. Here is an example:
var restsqlHost = "http://somehost:8080";
var restsqlBaseUri = "/restsql-0.8.10";
Requirements: restSQL project or deployed restSQL, JDK, Ant
The test project contains component test code, artifacts and a harness that exercise the framework and the service using the Java and HTTP APIs, respectively. The Java API tests use straight JUnit tests. The HTTP API tests use an XML-driven test case harness built with JUnit. The Java API tests require the restsql project, since it relies on its build file and source code. The HTTP API tests only require a deployed restsql service.
Database: If you have not deployed the SDK yet, you will need to deploy the extended sakila database. Bash and Windows batch scripts are provided to create the base and extended database for MySQL and PostgreSQL. The bash script is restsql-test/database/<database>/create-sakila.sh and the Windows batch script is restsql-test/database/<database>/create-sakila.bat, where database is mysql or postgresql. You will need to change the user and password variables in the beginning of the script to an account that has database and table creation privileges.
Execution: The tests are executed using the Ant build file (restsql-test/build.xml). Executing the default target, all, will run everything, but you can also run test-api (Java API) or test-service (HTTP API) to run one or either half. If the service is not running in the default location, http://localhost:8080/restsql/, then the System Property, org.restsql.baseUri, must be set. For example:
ant -Dorg.restsql.baseUri=http://somehost:8080/restsql-0.8.10/ test-service-http
Test results will appear on the console. Test detail is available in restsql-test/obj/test.
Open
http://host:port/restsql
in your browser to see all your resources.
Open
http://host:port/restsql-sdk/api-explorer
to interactively experiment with the HTTP API.
Open
http://host:port/restsql-sdk/doc/api
to explore the HTTP API.
Open
http://host:port/restsql-sdk/javadoc
to explore the Java API.