Throughout this article we will refer to root checkout directory for YesCart as YC_HOME.
Where Derby is located and how to use it?
Derby library
Yes Cart contains a ready to use Derby library which can be found in:
YC_HOME/env/derby
Tools
We also provide some nifty commands in the build scripts:
on Windows
YC_HOME/yc.bat
on *nix
YC_HOME/yc.sh
The scripts provide the following commands to work with Derby:
| Command | Notes |
|---|---|
| derbygo | starts the derby server, which the can be reached on jdbc:derby://localhost:1527 |
| derbygob | starts the derby server in background mode, which the can be reached on jdbc:derby://localhost:1527 |
| derbycon | opens ij command line client to interact with the server |
| dbiderby | allows to initialise derby database ( must run derbygo before you run this command ) |
We recommend that you use the above script commands. But should you run into trouble please consult manual operation instructions below
Prepare Derby for first use
This procedure sets up Derby database to initial state.
In command line window navigate to YC_HOME
on Windows
# start Derby server ./yc.bat derbygob # initialise the databases ./yc.bat dbiderby # shutdown the databases ./yc.bat derbyend
on *nix
# start Derby server ./yc.sh derbygob # initialise the databases ./yc.sh dbiderby # shutdown the databases ./yc.sh derbyend
Connecting to Derby
In command line window navigate to YC_HOME
on Windows
# start Derby server ./yc.bat derbygob # start derby IJ client and follow instructions for connecting to specific DB ./yc.bat derbycon # shutdown the databases when you finished working ./yc.bat derbyend
on *nix
# start Derby server ./yc.sh derbygob # start derby IJ client and follow instructions for connecting to specific DB ./yc.sh derbycon # shutdown the databases when you finished working ./yc.sh derbyend
You can also start derby in current window by using derbygo (without b on the end). Once you have executed ./yc.sh derbygo do not close that window since this will shutdown derby server.
Manual installation steps
Environment variables required to run manually
| Variable | Value |
|---|---|
| DERBY_INSTALL | YC_HOME/env/derby |
Open command line interface and navigate to Derby libs directory
$ cd YC_HOME/env/derby/lib
Export CLASSPATH variable
on Windows
C:\yc\> set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbytools.jar; %DERBY_INSTALL%\lib\derbyclient.jar;%DERBY_INSTALL%\lib\derbynet.jar;.
*nix
$ export CLASSPATH=$DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL/lib/derbytools.jar: $DERBY_INSTALL/lib/derbyclient.jar:$DERBY_INSTALL/lib/derbynet.jar:.
Run the Derby info command
$ java org.apache.derby.tools.sysinfo
The installation is good to go if you see something like:
------------------ Java Information ------------------ Java Version: 1.6.0_33 Java Vendor: Apple Inc. Java home: /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home Java classpath: YC_HOME/env/derby/lib/derby.jar:YC_HOME/env/derby/lib/derbytools.jar:. OS name: Mac OS X OS architecture: x86_64 OS version: 10.6.8 Java user name: denispavlov Java user home: /Users/denispavlov Java user dir: YC_HOME/env/derby/lib java.specification.name: Java Platform API Specification java.specification.version: 1.6 java.runtime.version: 1.6.0_33-b03-424-10M3720 --------- Derby Information -------- JRE - JDBC: Java SE 6 - JDBC 4.0 [YC_HOME/env/derby/lib/derby.jar] 10.8.1.2 - (1095077) [YC_HOME/env/derby/lib/derbytools.jar] 10.8.1.2 - (1095077) ------------------------------------------------------
Manual Derby server start
Open command line interface and navigate to Derby libs directory
$ cd YC_HOME/env/derby/lib
Export CLASSPATH variable
on Windows:
C:\yc\> set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbytools.jar; %DERBY_INSTALL%\lib\derbyclient.jar;%DERBY_INSTALL%\lib\derbynet.jar;.
on *nix
$ export $DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL/lib/derbytools.jar: $DERBY_INSTALL/lib/derbyclient.jar:$DERBY_INSTALL/lib/derbynet.jar:.
Run the Derby server start command
$ java -jar derbyrun.jar server start
Manual IJ Derby client start
Make sure the server is running
Open command line interface and navigate to Derby libs directory
$ cd YC_HOME/env/derby/lib
Export CLASSPATH variable
on Windows:
C:\yc\> set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbytools.jar; %DERBY_INSTALL%\lib\derbyclient.jar;%DERBY_INSTALL%\lib\derbynet.jar;.
on *nix
$ export $DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL/lib/derbytools.jar: $DERBY_INSTALL/lib/derbyclient.jar:$DERBY_INSTALL/lib/derbynet.jar:.
Run the Derby client command
$ java org.apache.derby.tools.ij
Now you can access databases from client (provided that you have initialised them). Type commands:
# to connect to main db connect 'jdbc:derby://localhost:1527/yes'; # to connect to payment gateway db connect 'jdbc:derby://localhost:1527/yespay'; # to disconnect from database disconnect;
Manual Derby dbinit
In order to setup the database you need to:
- Startup derby server
- Startup ij (in a separate window)
- Run the following commands (provided that you are doing all of the above from the YC_HOME/env/derby/lib)
connect 'jdbc:derby://localhost:1527/yes;create=true'; run '../../../persistence/sql/resources/derby/create-tables.sql'; run '../../../env/setup/dbi/initdata.sql'; disconnect; connect 'jdbc:derby://localhost:1527/yespay;create=true'; run '../../../core-modules/core-module-payment-base/src/main/resources/sql/derby/create-tables.sql'; run '../../../core-modules/core-module-payment-base/src/main/resources/sql/payinitdata.sql'; # then for each additional payment module that you need run '../../../core-modules/core-module-payment-XXXX/src/main/resources/sql/payinitdata.sql'; disconnect;
Tips and tricks
PK constraint violation
Some of our db init sql script contain explicit PK. Derby gets confused with the increment counter and fails to insert new rows if a PK already exists. To reset counter perform the following query on the required table.
ALTER TABLE table_name ALTER COLUMN auto_increment_column_name RESTART WITH 10000;
java.security.AccessControlException access denied
For some Java SDK you may encounter java security exception when starting Derby server:
Thu Jun 18 15:57:55 BST 2015 : Security manager installed using the Basic server security policy.
Thu Jun 18 15:57:56 BST 2015 : access denied ("java.net.SocketPermission" "localhost:1527" "listen,resolve")
java.security.AccessControlException: access denied ("java.net.SocketPermission" "localhost:1527" "listen,resolve")
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:457)
at java.security.AccessController.checkPermission(AccessController.java:884)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at java.lang.SecurityManager.checkListen(SecurityManager.java:1131)
at java.net.ServerSocket.bind(ServerSocket.java:374)
at java.net.ServerSocket.<init>(ServerSocket.java:237)
at javax.net.DefaultServerSocketFactory.createServerSocket(ServerSocketFactory.java:231)
at org.apache.derby.impl.drda.NetworkServerControlImpl.createServerSocket(Unknown Source)
at org.apache.derby.impl.drda.NetworkServerControlImpl.access$000(Unknown Source)
at org.apache.derby.impl.drda.NetworkServerControlImpl$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at org.apache.derby.impl.drda.NetworkServerControlImpl.blockingStart(Unknown Source)
at org.apache.derby.impl.drda.NetworkServerControlImpl.executeWork(Unknown Source)
at org.apache.derby.drda.NetworkServerControl.main(Unknown Source)
at org.apache.derby.iapi.tools.run.main(Unknown Source)
To fix this locate java.policy file in $JAVA_HOME/jre/lib/security/java.policy and add the following:
grant {
// permission to start Derby
permission java.net.SocketPermission "localhost:1527", "listen";
// Java 8 SDK also needs this permission to stop Derby
permission java.sql.SQLPermission "deregisterDriver";
};
Looking up indexes
Example looking up unique constraint on TCUSTOMER
select CONSTRAINTID, CONSTRAINTNAME, REFERENCECOUNT from SYS.SYSCONSTRAINTS c join SYS.SYSTABLES t on c.TABLEID = t.TABLEID where t.TABLENAME = 'TCUSTOMER' and c.TYPE = 'U';