vFabric SQLFire is a memory-optimized distributed SQL database delivering dynamic scalability and high performance for data-intensive modern applications. In this demo we load the Australian Football Leagure 2012 Fixture data into an SQLFire distributed in memory database.
A common requirement for using SQLFire is offloading data from an existing data store and loading it into SQLFire. This example show how you can load data into a SQLFire distributed system using Spring Batch from a CSV file and then how to present the data from a simple Spring MVC application. Finally the data is persisted to each SQLFire member to ensure we only need to load the data once and it will always be retained upon restarts of the distributed system.
Demo Setup
1. Download the zip file below and extract onto your file system. The demo scripts are written for a unix environment and tested on a MAC. Please convert the SH files to BAT files if on windows. The ANT commands will work whether your on windows or unix.
Note: The downloaded zip provides the JAR files for spring core/batch so there is no need to download those.
2. Ensure you have the JDK 6 in your path. It should work with JDK 1.5 but was never tested this JDK 1.5
3. Ensure Apache Ant is installed.
| [Mon Jan 30 09:55:33 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ ant -version Apache Ant(TM) version 1.8.2 compiled on June 3 2011 |
4. Verify you have SQLFire installed as shown below.
| [Tue Feb 07 12:56:56 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101 ] $ d total 8 drwxr-xr-x 10 papicella staff 340 27 Jan 08:34 docs/ drwxr-xr-x 4 papicella staff 136 27 Jan 08:34 bin/ drwxr-xr-x 6 papicella staff 204 27 Jan 08:34 ../ drwxr-xr-x 15 papicella staff 510 27 Jan 08:34 quickstart/ drwxr-xr-x 19 papicella staff 646 27 Jan 08:34 lib/ drwxr-xr-x 7 papicella staff 238 27 Jan 08:35 examples/ -rw-r–r– 1 papicella staff 167 27 Jan 08:46 key.txt drwxr-xr-x 9 papicella staff 306 27 Jan 20:34 ./ drwxr-xr-x 7 papicella staff 238 5 Feb 20:38 pasdemos/ |
Running the Demo
1. Ensure you have a clean environment by running "ant clean" as shown below.
| [Mon Jan 30 09:59:12 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ ant clean Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml clean: BUILD SUCCESSFUL |
2. Edit setup.sh to ensure you have placed SQLFire into your path as shown below.
| export PATH=/Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/bin:$PATH
export CUR_DIR=`pwd` export CLASSPATH= |
3. Setup your environment by sourcing setup.sh as shown below.
| [Mon Jan 30 10:14:05 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ . ./setup.sh |
4. Run the "init" target to setup the required directories.
| [Mon Jan 30 10:19:29 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ ant init Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml init: BUILD SUCCESSFUL |
5. Package the application using the "package" target as shown below.
| [Mon Jan 30 10:24:30 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ant package Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml init: compile: package: BUILD SUCCESSFUL |
6. Start 2 SQLFire membors as follows. These members are using a multicast port for discovery instead of a locator.
| [Mon Jan 30 10:27:14 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ./start-servers.sh Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333] Starting network server for SQLFire Server at address localhost/127.0.0.1[1528] Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333] Starting network server for SQLFire Server at address localhost/127.0.0.1[1527] SQLFire Server pid: 657 status: running Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/server1/sqlfserver.log SQLFire Server pid: 658 status: running Distributed system now has 2 members. Other members: 10.117.85.24(657:datastore)<v0>:13038/49735 Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/server2/sqlfserver.log |
7. At this point lets ensure we can connect from a client using the script "test-connect.sh" if all went well it should show today's date.
| [Mon Jan 30 10:29:01 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ./test-connect.sh sqlf version 10.4 sqlf> sqlf> 1 ———- 2012-01-30 1 row selected |
8. Lets view our 2 SQLFire members using the script "view-members.sh". This will show you the PID for each member in the distributed system
| [Mon Jan 30 10:38:33 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ./view-members.sh sqlf version 10.4 sqlf> sqlf> > Id |NetServers |PID ——————————————————————————————- 10.117.85.24(657)<v0>:13038/49735 |localhost/127.0.0.1[1527] |657 10.117.85.24(658)<v1>:22550/49734 |localhost/127.0.0.1[1528] |658 2 rows selected |
9. Create the schema data running "create-schema-data.sh" as shown below. This will create the necessary tables and populate some of those with some known SQL data.
| [Mon Jan 30 10:33:09 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ./create-schema-data.sh …….. sqlf> all done.. |
10. At this point we need to load a comma sperated CSV file into a temporary table using spring batch. This is all the 2012 fixture data which we will then massage into the real tables once it's loaded. Run then target "run-loadfixture" as shown below.
| [Mon Jan 30 10:41:00 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ant run-loadfixture Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml init: compile: package: run-loadfixture: BUILD SUCCESSFUL |
11. Now we massage the fixture data loaded from CSV into the real application table called "games". We do this using the ant target "run-loadgames" as shown below.
| [Mon Jan 30 10:44:49 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ ant run-loadgames Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012/build.xml init: compile: package: run-loadgames: BUILD SUCCESSFUL |
Viewing the Data
Below are 3 different ways to view the data in the distributed system. They are as follows.
- Using the sqlf command line client
- Using DbVisualizer (RDBMS Graphical Tool)
- Spring MVC WebUI
1. Using the sqlf command line client
Here we simply connect using the command line client provided with SQLFire.
1.1. Type in the following : sqlf
1.2. Connect using : connect client 'localhost:1527';
1.3. Query the current list of teams using : select * from teams;
| [Mon Jan 30 10:50:35 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ sqlf sqlf version 10.4 sqlf> connect client 'localhost:1527'; sqlf> select * from teams; TEAM_ID |NAME |LONG_NAME |WEB_SITE ————————————————————————————————————- 18 |Giants |GWS GIANTS |http://www.gwsgiants.com.au/ 1 |Tigers |Richmond |http://richmondfc.com.au/ 16 |Bulldogs |Western Bulldogs |http://westernbulldogs.com.au/ 12 |Power |Port Adelaide |http://portadelaidefc.com.au/ 5 |Pies |Collingwood |http://collingwoodfc.com.au/ 17 |Suns |Gold Coast Suns |http://goldcoastfc.com.au/ 4 |Blues |Carlton |http://carltonfc.com.au/ 2 |Crows |Adelaide Crows |http://afc.com.au/ 10 |Roos |North Melbourne |http://kangaroosfc.com.au/ 13 |Saints |St. Kilda |http://saints.com.au/ 8 |Cats |Geelong Cats |http://gfc.com.au/ 14 |Swans |Sydney Swans |http://sydneyswans.com.au/ 15 |Eagles |West Coast Eagles |http://westcoasteagles.com.au/ 11 |Demons |Melbourne |http://demons.com.au/ 7 |Dockers |Fremantle |http://fremantlefc.com.au/ 9 |Hawks |Hawthorn |http://hawthornfc.com.au/ 3 |Lions |Brisbane Lions |http://lions.com.au/ 6 |Bombers |Essendon |http://bombersfc.com.au/ 18 rows selected |
2. Using DbVisualizer (RDBMS Graphical Tool)
2.1. Setup DbVisualizer as shown in the BLOG entry below. This describes how to load the SQLFire client driver required to make a connection to our distributed system.
http://theblasfrompas.blogspot.com/2012/01/using-dbvisualizer-to-connect-to.html
2.2. Issue a query as follows to view games for "Richmond".
| select g.round_ID, h.long_name "Home Team", a.long_name "Away Team", g.game_date, substr(v.name, 1, 20) "Venue", g.tv_network from games g, teams h, teams a, venues v where (g.HOME_TEAM_ID = 1 or g.away_team_id = 1) and g.home_team_id = h.team_id and g.away_team_id = a.team_id and g.venue_id = v.venue_id order by g.round_id |
Query Results
3. Spring MVC WebUI
It is assumed you already have a tcServer instance created and running, in this example the tcServer is named "sqlfire-demo"
3.1 Download the WAR file below onto your file system and unzip the file to reveal the WAR file.
3.2. Create an instance as shown below.
| [Mon Jan 30 12:10:42 papicella@:~/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE ] $ ./tcruntime-instance.sh create sqlfire-demo Creating instance 'sqlfire-demo' … Using separate layout Creating bin/setenv.sh Applying template 'base' Copying template's contents Applying fragment 'context-fragment.xml' to 'conf/context.xml' Applying fragment 'server-fragment.xml' to 'conf/server.xml' Applying fragment 'web-fragment.xml' to 'conf/web.xml' Applying fragment 'tomcat-users-fragment.xml' to 'conf/tomcat-users.xml' Applying fragment 'catalina-fragment.properties' to 'conf/catalina.properties' Applying template 'base-tomcat-7' Copying template's contents Applying fragment 'server-fragment.xml' to 'conf/server.xml' Applying fragment 'web-fragment.xml' to 'conf/web.xml' Applying fragment 'catalina-fragment.properties' to 'conf/catalina.properties' Applying template 'bio' Copying template's contents Applying fragment 'server-fragment.xml' to 'conf/server.xml' Configuring instance 'sqlfire-demo' to use Tomcat version 7.0.23.A.RELEASE Setting permissions Instance created Connector summary Port: 8080 Type: Blocking IO Secure: false |
3.3. Start the instance as shown below.
| [Mon Jan 30 12:31:44 papicella@:~/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE ] $ ./tcruntime-ctl.sh sqlfire-demo start INFO Instance name: sqlfire-demo INFO Script directory: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE INFO tc Runtime location:/Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE INFO Instance base: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE INFO Binary dir: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/tomcat-7.0.23.A.RELEASE INFO Runtime version: 7.0.23.A.RELEASE INFO Script version: 2.6.3.RELEASE Using CATALINA_BASE: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/sqlfire-demo Using CATALINA_HOME: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/tomcat-7.0.23.A.RELEASE Using CATALINA_TMPDIR: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/sqlfire-demo/temp Using JRE_HOME: /System/Library/Frameworks/JavaVM.framework/Versions/CurrentJDK/Home Using CLASSPATH: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/tomcat-7.0.23.A.RELEASE/bin/bootstrap.jar:/Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/tomcat-7.0.23.A.RELEASE/bin/tomcat-juli.jar Using CATALINA_PID: /Users/papicella/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/sqlfire-demo/logs/tcserver.pid |
3.4. Drop The WAR file into $CATALINA_HOME/sqlfire-demo/webapps directory. It should explode the contents automatically and deploy the application.
| [Mon Jan 30 12:32:57 papicella@:~/vmware/software/tcserver/vfabric-tc-server-standard-2.6.3.RELEASE/sqlfire-demo/webapps ] $ d total 8592 drwx—— 6 papicella staff 204 30 Jan 12:12 ROOT/ drwx—— 12 papicella staff 408 30 Jan 12:12 ../ -rw-r–r– 1 papicella staff 4396232 30 Jan 12:38 sqlfire-afl.war drwxr-xr-x 7 papicella staff 238 30 Jan 12:39 sqlfire-afl/ drwx—— 5 papicella staff 170 30 Jan 12:39 ./ |
3.5 Access the application as follows.
http://localhost:8080/sqlfire-afl/index.html
3.6 Click on "Show all teams" link
3.7. Click on "Tigers" link to show Richmond's 2012 games for the AFL 2012 season.
Exploring The Setup
- If you wish to stop the SQLFire distributed cluster use the script "stop-all.sh" as shown below.
| [Mon Jan 30 12:52:54 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2012 ] $ ./stop-all.sh Connecting to distributed system: mcast=/239.192.81.1:12333 Successfully shut down 2 members |
– In this howto the data which is stored in the distributed system is also persisted locally to each SQLFire server using "server1" and "server2" directories referenced in the start-servers.sh script . This enables it to be rehydrated across cluster restarts to avoid having to load the data back into the distributed system. This is achieved using SQL as follows when we create the tables.
| create diskstore STORE1;
CREATE TABLE TEAMS |
Learn More
For more information on vFabric SQLFire use the following link.
