| Throughout this article we will refer to root checkout directory for YesCart as YC_HOME. |
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 |
| 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. |
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:
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
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; |
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'; |