Page tree
Skip to end of metadata
Go to start of metadata

  

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 ((warning) 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';
  • No labels