Release 1.1. User's Guide and Reference

(1)

User's Guide and Reference

Release 1.1

(2)

1. NuoDB API DataSource Reference Page . . . 6

2. NuoDB Online Documentation v1.1 . . . 7

2.1 Overview . . . 8

2.1.1 About the NuoDB Architecture . . . 10

2.1.2 Client Connections . . . 12

2.1.3 System Requirements and Support . . . 13

2.2 Installing NuoDB . . . 15

2.2.1 Linux Installation . . . 16

2.2.2 Mac OS X Installation . . . 18

2.2.3 Windows Installation . . . 19

2.2.4 Installing a New License . . . 21

2.2.5 Uninstalling NuoDB . . . 23

2.3 Getting Started . . . 24

2.3.1 QuickStart . . . 25

2.3.1.1 Run the QuickStart . . . 26

2.3.1.1.1 Console > Explorer QS . . . 31

2.3.1.2 Execute SQL Commands . . . 33

2.3.1.3 Web Console with the QuickStart . . . 36

2.3.1.4 Copy of Execute SQL Commands . . . 39

2.3.2 Using Command Line to Get Started . . . 45

2.3.2.1 Linux Command Line Usage . . . 46

2.3.2.2 Windows Command Line Usage . . . 49

2.3.3 Developing with NuoDB . . . 51

2.3.3.1 C++ with NuoDB—Getting Started . . . 52

2.3.3.2 Java Hibernate with NuoDB—Getting Started . . . 53

2.3.3.3 Java with NuoDB—Getting Started . . . 55

2.3.3.4 JRuby with NuoDB—Getting Started . . . 57

2.3.3.5 Sample Client Programs . . . 60

2.3.4 Migrating Databases to NuoDB . . . 61

2.4 APIs and Drivers . . . 62

2.4.1 NuoDB Drivers . . . 63

2.4.1.1 Available NuoDB Drivers . . . 64

2.4.1.2 NuoDB Drivers on GitHub . . . 66

2.4.1.3 JDBC Connections . . . 68

2.4.1.3.1 JDBC Connection Properties . . . 71

2.4.1.4 ODBC Connections . . . 72

2.4.1.4.1 Example: Using ODBC and Microsoft Excel to Read a NuoDB Database . . . 73

2.4.1.4.2 Example: Using ODBC and Open Office Calc to Read a NuoDB Database . . . 77

2.4.1.5 NuoDB ADO.NET Driver . . . 81

2.4.1.6 NuoDB PHP PDO Driver . . . 88

2.4.1.7 NuoDB Zend Framework Adapter . . . 90

2.4.2 APIs . . . 91

2.4.2.1 C++ API Reference . . . 92

2.4.2.2 JDBC API Reference . . . 96

2.4.2.3 JDBC API Requirements . . . 97

2.4.2.4 JDBC API Sample . . . 98

2.4.3 Understanding Client Connections . . . 104

2.4.4 Programming . . . 105

2.4.4.1 C++ with NuoDB . . . 106

2.4.4.2 Java With NuoDB . . . 107

2.5 Tools and Utilities . . . 108

2.5.1 Tools . . . 109

2.5.1.1 NuoDB Loader . . . 110

2.5.1.2 NuoDB Manager . . . 115

2.5.1.3 NuoDB Migrator . . . 127

(3)

2.5.1.3.6 PostgreSQL Migration . . . 138

2.5.1.3.7 Commands . . . 140

2.5.1.4 NuoDB SQL CLI . . . 147

2.5.1.4.1 nuosql Command . . . 150

2.5.1.4.2 Batch Processing from the Command Line . . . 152

2.5.1.4.3 Selecting from the DUAL Table . . . 154

2.5.1.4.4 Metadata . . . 155

2.5.1.4.5 Troubleshooting the NuoDB SQL Client . . . 156

2.5.1.5 NuoDB Web Console . . . 157

2.5.1.5.1 Start the NuoDB Web Console . . . 163

2.5.1.5.2 Start a Database Using the Web Console . . . 165

2.5.1.5.3 Specify Alert Settings . . . 169

2.5.1.5.4 webapp.properties . . . 170

2.5.1.5.5 Storefront Demo . . . 172

2.5.2 3rd Party Utilities . . . 185

2.5.2.1 DbVisualizer for NuoDB JDBC Driver . . . 186

2.5.2.2 OLD_DbVisualizer for NuoDB JDBC Driver . . . 188

2.5.2.3 SQL Workbench/J . . . 191

2.5.2.4 SQuirreL Universal SQL . . . 192

2.6 SQL Reference . . . 193

2.6.1 Summary of SQL Supported by NuoDB . . . 194

2.6.2 SQL Reference Pages . . . 202 2.6.2.1 ALTER DOMAIN . . . 204 2.6.2.2 ALTER SEQUENCE . . . 206 2.6.2.3 ALTER TABLE . . . 207 2.6.2.4 ALTER TRIGGER . . . 208 2.6.2.5 ALTER USER . . . 210 2.6.2.6 AUTOCOMMIT . . . 211 2.6.2.7 COMMIT . . . 213 2.6.2.8 CREATE DATABASE . . . 214 2.6.2.9 CREATE DOMAIN . . . 215 2.6.2.10 CREATE INDEX . . . 217 2.6.2.11 CREATE PROCEDURE . . . 218 2.6.2.12 CREATE ROLE . . . 219 2.6.2.13 CREATE SCHEMA . . . 220 2.6.2.14 CREATE SEQUENCE . . . 221 2.6.2.15 CREATE TABLE . . . 222 2.6.2.16 CREATE TRIGGER . . . 225 2.6.2.17 CREATE USER . . . 227 2.6.2.18 CREATE VIEW . . . 228 2.6.2.19 DELETE . . . 230 2.6.2.20 DROP DATABASE . . . 231 2.6.2.21 DROP DOMAIN . . . 232 2.6.2.22 DROP INDEX . . . 233 2.6.2.23 DROP PROCEDURE . . . 234 2.6.2.24 DROP ROLE . . . 235 2.6.2.25 DROP SCHEMA . . . 236 2.6.2.26 DROP SEQUENCE . . . 238 2.6.2.27 DROP TABLE . . . 239 2.6.2.28 DROP TRIGGER . . . 240 2.6.2.29 DROP USER . . . 241 2.6.2.30 DROP VIEW . . . 242 2.6.2.31 EXPLAIN . . . 244 2.6.2.32 GRANT . . . 246 2.6.2.33 INSERT . . . 248 2.6.2.34 RELEASE . . . 250 2.6.2.35 REPLACE . . . 251 2.6.2.36 REVOKE . . . 254 2.6.2.37 ROLLBACK . . . 255 2.6.2.38 SAVEPOINT . . . 257

(4)

2.6.2.39 SELECT . . . 260 2.6.2.40 SET . . . 266 2.6.2.41 SHOW . . . 268 2.6.2.42 START TRANSACTION . . . 271 2.6.2.43 TRUNCATE TABLE . . . 273 2.6.2.44 UPDATE . . . 274 2.6.2.45 UPGRADE . . . 275 2.6.2.46 USE . . . 277

2.6.3 SQL Language Elements and Transactions . . . 279

2.6.3.1 Datatypes Supported by NuoDB . . . 280

2.6.3.1.1 NuoDB Extensions . . . 281

2.6.3.1.2 Standard SQL Datatype Keywords Supported . . . 282

2.6.3.1.3 String Types . . . 285

2.6.3.1.4 Date, Time, and TimeStamp Conversions . . . 286

2.6.3.1.5 Numeric Types . . . 287

2.6.3.1.6 Datatypes [old] <HIDDEN> . . . 290

2.6.3.2 Domains . . . 293

2.6.3.3 Operators and Operations . . . 294

2.6.3.3.1 Logical Operators . . . 295 2.6.3.3.2 Comparison Operators . . . 296 2.6.3.3.3 Numeric Operations . . . 304 2.6.3.4 Functions . . . 307 2.6.3.4.1 CHAR_LENGTH Function . . . 309 2.6.3.4.2 SUBSTR Function . . . 310 2.6.3.4.3 String Concatenation . . . 311

2.6.3.4.4 Date and Time Functions . . . 312

2.6.3.5 Expressions . . . 315

2.6.3.5.1 Value Expressions . . . 316

2.6.3.5.2 Conditional Expressions . . . 317

2.6.3.6 Object Privileges . . . 318

2.6.3.7 Transactions and Isolation Levels . . . 319

2.6.3.7.1 Relationship to java.sql Levels . . . 322

2.6.3.8 Internationalization and Localization . . . 323

2.6.3.9 Migration Notes . . . 324

2.6.3.9.1 Distinction between a schema and a database . . . 325

2.6.3.10 Compliance with SQL Standard . . . 326

2.6.3.11 Summary of SQL in NuoDB . . . 333

2.6.4 Working With Triggers . . . 341

2.6.5 SQL . . . 344

2.7 Administration and Deployment . . . 346

2.7.1 Administration Tools . . . 347

2.7.1.1 nuoagent . . . 348

2.7.1.1.1 Commands to Start and Stop NuoDB Brokers and Agents . . . 350

2.7.1.1.2 Starting and Stopping NuoDB Agents/Brokers, Web Console and Tray Monitor . . . 352

2.7.1.2 nuodb . . . 354

2.7.1.3 default.properties . . . 358

2.7.2 Provisioning NuoDB . . . 363

2.7.2.1 Domain Administrators . . . 364

2.7.2.2 Data Durability in NuoDB . . . 365

2.7.2.3 Configuration Scenarios . . . 367

2.7.2.3.1 Single Host Configuration . . . 368

2.7.2.3.2 Multiple Host Configuration . . . 370

2.7.2.3.3 Amazon AWS . . . 377

2.7.2.3.4 Multiple Data Centers and Cloud Regions . . . 380

2.7.2.3.5 Automate Configuration Scenarios . . . 383

(5)

2.7.2.4.3 Redundancy and Failover . . . 403

2.7.2.4.4 Hadoop Distributed File System (HDFS) . . . 404

2.7.3 Troubleshooting NuoDB . . . 405

2.7.4 How To Scale Out a NuoDB Database . . . 408

2.7.5 Setup a Pilot Project on Multiple Hosts . . . 410

2.8 Glossary . . . 411

2.9 Release Notes . . . 413

2.9.1 Bug Fixes and Improvements . . . 414

2.9.2 Known Bugs and Limitations . . . 418

2.10 Acknowledgements . . . 421

(6)

NuoDB API DataSource Reference Page

JDBC API

NuoDB® Company Website

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(7)

NuoDB Online Documentation v1.1

Welcome to the NuoDB Documentation!

Installation and Getting Started

Installing NuoDB

Getting Started Overview

SQL Materials

SQL Reference

SQL Language Elements and Transactions

Programming and Administration

APIs and Drivers Tools and Utilities Administration Tools

(8)

Overview

A NuoDB system is designed for the configuration, deployment, and management of one or more databases on a cloud.

From a conceptual point of view, a NuoDB system has three layers: a Management Layer, an SQL Layer, and a Data Layer. Each NuoDB database is associated with multiple processes running in each of these layers.

Management Layer

NuoDB Broker

is a process that manages access to transaction engines and storage managers, independent of any particular database in the NuoDB system. By default, when you start a broker, the broker defines a NuoDB domain.

NuoDB Agent is a process that manages NuoDB processes running on a particular

computer. It starts and stops the NuoDB servers and communicates with a broker about

the state of the system.

NuoDB Manager

is a text-based tool that you can use interactively or non-interactively to

administer all aspects of NuoDB.

NuoDB Web Console is a web based GUI administration tool running by default on;

http:/

/localhost:8080

NuoDB System Tray is a GUI tool running on MAC OSX and Windows used to more

easily launch the NuoDB Console, QuickStart Wizard and SQL Prompt.

NuoDB Loader is a tool for importing and exporting data.

SQL Layer

NuoDB Transaction Engine provides access to a single database. It handles requests from clients, caches data, and coordinates transactions.

There may be any number of transaction engines running on a single machine or across a cluster.

Data Layer

NuoDB Storage Manager is a process that provides durability of the data that it handles (for example by writing to disk). There may be any

number of storage managers running on a single machine or across a cluster. Each storage manager is associated with exactly one database. Transaction engines and storage managers send asynchronous messages to each other while the system is running, in order to maintain copies of the current database.

NuoDB Domain

(9)

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(10)

1. 2. 3.

4.

About the NuoDB Architecture

Atomic Architecture, Latency and Storage Management

NuoDB uses a unique architecture to achieve high performance, elasticity, scalability, and ACID compliance.

Overview

How does NuoDB handle the case of two transactions, running on parts of a database that are separated by a long-latency network, where each transaction can run and commit before either transaction can determine the existence of the other? How do storage managers that ensure durability also maintain consistency with each other and with the current state of the database?

The NuoDB architecture makes it possible for transaction engines to maintain a copy of a database in memory, and for storage managers to stay synchronized with each other and with the transaction engines. All elements of a NuoDB database (data, indexes, metadata, and transaction state) are maintained in objects called atoms, which are of various types. The transaction manager atom (TMA) tracks the start events and commit events of all transactions in the system. Instances of the TMA receive and send messages to and from other TMAs, and to and from TEs and SMs. Each TE and each SM has a TMA instance.

Transactions

A NuoDB client application starts a transaction by recording a start event on a TE, which is referred to as the local TE. Similarly, when the transaction completes, the TE records a transaction end event. All TEs record all start and end events, making it possible for all transactions to be visible across the database.

All TEs for a particular database communicate with each other by means of batched, asynchronous messages; messages about transaction state changes require no acknowledgement. When two transactions are operating independently (for example, inserting values into different tables) they do not communicate at all.

How Conflicts Are Resolved

Conflicts can occur on specific pieces of data, such as trying to update to the same record, or inserting the same values into unique indexes. If multiple copies of an atom are in circulation, and there is a conflict, a single instance of the atom is designated as the atom chairman. The atom chairman acts as referee and resolves the conflict of operations on data. The steps in this kind of conflict resolution are as follows:

The local records or index atom instance sends a change request to the records or index atom chairman. The chairman receives change requests and determines if there is a conflict.

During this communication process, each change is stalled.

Consequently the transaction thread stalls until the atom chairman responds. If the change does not conflict with concurrent changes the transaction proceeds.

Commit Protocol

NuoDB database administrators can set a minimum level of durability for a database, and application developers can require a higher level of durability for specific applications. The commit protocol is specified as a command line option.

The remote commit with journaling protocol guarantees that at least one SM has copies of all of a transaction's changes before the transaction commits. The following list describes the steps in a commit process:

The transaction issues a commit request to the TE it is connected to (which is the local TE). The local TE sends a pre-commit message to the TMAs on SMs.

The SMs receive the pre-commit message and each one: Flushes its replication message journal, using direct I/O Sends a "CommitACK" message to the TMA on the local TE. The TMA on the local TE:

Receives at least one "CommitACK" message Sends a "committed" message to all other TMAs

Tells its TE that it can respond to the transaction saying its commit succeeded.

(11)

protocol, which requires receiving "commit received" messages from two SMs.

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(12)

1.

2.

3.

Client Connections

The following illustration shows a simple NuoDB database system and a client that wants to connect to it.

The steps in the process shown in this conceptual illustration are as follows:

An application that seeks to connect to the database calls to the broker on Host A.

Because the Broker communicates with all Agents in the Domain it knows which Transaction Engines are available and determines the best transaction engine for the application.

The broker communicates back to the client that it should connect to the Transaction Engine on Host B.

Once the Application knows which Host and Transaction Engine to communicate with, it will no longer communicate with the Broker. The client connects to the Transaction Engine on Host B.

About Client Connections and Transaction Engines

NuoDB distributes new requests for transactions from a client application to existing transaction engines that are connected to the client application. There is no mechanism for redistributing existing connections when a new transaction engine starts.

You must explicitly establish a new connection from the client application and send it to the newly started transaction engine.

If you add a transaction engine process while client applications are connected to transaction engines that are already running, and you want to have the same number of connections, and you want to balance the connections among all transaction engines, you must explicitly disconnect the existing client connections and reconnect clients to all transaction engines after you start the new transaction engine.

(13)

System Requirements and Support

NuoDB runs on Linux, Mac OS X, and Windows, and requires the Java SDK.

Supported Operating Systems

Linux (x64)

Ubuntu 11.10 and 12.x RHEL 5.9 and 6.x

SuSe Linux Enterprise Server 11 sp2 openSuSe 12.x

Amazon Basic EC2 MAC (x64) OS X 10.7.x or later Windows (x64) 7 or later

note: Client support only for x32

Additional Software Requirements

NuoDB requires Java 1.6 or later, and has been tested using the following software:

Oracle Java 1.6

MAC JDK 1.6

Open JDK 1.6

On Windows (x64), Java must also be (x64).

3rd Party Software

NuoDB will install Jetty as the embedded web server for NuoConsole:\

Jetty 8.1

Minimum Recommended Hardware

CPU Dual Core x86_64, 1.6GHz

Memory 4 GB

Unsupported Software

NuoDB does not support g++ on Windows

NuoDB Ruby drivers have not been tested on Windows NuoDB Go driver is not supported

(14)

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(15)

Installing NuoDB

NuoDB is supported on and provides installation packages multiple operating systems. Please select your platform of choice below for further details.

System Requirements and Support

Linux Installation

Mac OS X Installation

Windows Installation

Installing a New License

Bug Fixes and Improvements

Known Bugs and Limitations

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(16)

Linux Installation

To install and add new users you must have write access to the /opt directory.

RPM Installation

Run the RedHat Package Manager (RPM) using the NuoDB package

$ rpm --install nuodb-1.1.linux.x64.rpm

Installation Details

Installation Directory /opt/nuodb Path Variable /opt/nuodb/bin

NuoDB Broker Running Using options from the

default.properties file

Web Console Running http://localhost:8080

License 2 Hosts

DEB Installation

Run the Debian package utility, dpkg:

dpkg --i nuodb-1.1.linux.x64.deb

Installation Details

Installation Directory /opt/nuodb Path Variable /opt/nuodb/bin

License 2 Hosts

TAR.GZ Installation

opy the compressed file and expand it to the install directory you wish to use:

C

$ sudo cp nuodb-1.1.linux.x64.tar.gz /opt/

$ sudo tar -zxvf /opt/nuodb-1.1.linux.x64.tar.gz

(17)

$ export PATH=$PATH:/opt/nuodb/bin

Optionally, use init.d scripts to start NuoDB processes. NuoDB provides a sample init.d script. To experiment with the NuoDB SQL client and a sample NuoDB database see the QuickStart. You can also get started just by using the command line: see Linux Command Line Usage.

Installation Details

Installation Directory No Default Set during Installation Path Variable No Default must be set manually

NuoDB Broker Not Running Must be started manually

Web Console Not Running Must be started manually

http://localhost:8080

License 2 Hosts

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(18)

Mac OS X Installation

To install NuoDB you must have write access to the /opt directory.

Wizard Installation

NuoDB can be installed by double-clicking the .pkg file and walking through the installation prompts. Once the installation is complete, the QuickStart Wizard will appear.

Command Line Installation

The Command Line installation of NuoDB allows you bypass the installation Wizard and install using all of the default options. Please note that the QuickStart Wizard will appear once the Command Line installation is complete.

$ sudo installer -pkg ~/Downloads/nuodb-1.1.macosx.x64.pkg -target /

Installation Details

The following are the default installation details, regardless of the installation method (Wizard or Command Line). Installation Directory /opt/nuodb

Path Variable /opt/nuodb/bin

Default License 2 Hosts

Post Installation

Once the installation is complete you will be prompted to run the QuickStart Wizard which will introduce you to the elements of the NuoDB Architecture and automatically start a test database.

You can choose to cancel the QuickStart Wizard, and in that case we would suggest that you review the Linux Command Line Usage for examples of how to start a database manually.

The OS X Gatekeeper feature may cause an "Unidentified Developer" error to be thrown when downloading or installing apps that were not purchased via the Mac App Store. If you experience this behavior use the following to resolve the error:

1. Goto: System Preferences > Security and Privacy > General

(19)

Windows Installation

Prerequisites

Write permissions. To install NuoDB you must have write access to the C:\Program Files directory.

.NET Framework v4 or later must be installed, or the NuoDB ADO.NET driver, which is part of the NuoDB install package, will not install. To verify you have it, look for it in the list of installed programs in Control Panel (Start > Control Panel > Programs > Programs and Features). If it's not installed, download and install .Net Framework v4 before installing NuoDB.

Wizard Installation

NuoDB can be installed by double-clicking the .exe file and walking through the installation prompts. Once the installation is complete, the QuickStart Wizard launches.

Silent Installer

The silent installer suppresses all interactive dialog prompts and installs NuoDB to the default locations noted below.

Command

> nuodb-1.1.Windows.x64.exe /S

Installation Details

The following are the default installation details, regardless of the installation method (Wizard or Silent Install). Installation Directory c:\Program Files\nuodb

Path Variable c:\Program Files\nuodb\bin

Default License 2 Hosts

Important

64-bit installations are automatically upgraded as part of the NuoDB v1.1 installation process.

Previous versions of NuoDB 32-bit

installations however are not, and require the user to first uninstall the previous version before installing NuoDB v1.1.

When you install NuoDB on Windows you may see a User Account Settings dialog indicating that the installer is about to make changes to the computer. Our installer is a signed as NuoDB, Inc.

The installer does several things beyond unpacking the software, including: Sets up the proper registry entries for the NuoDB ODBC driver Checks that a valid version of Java is installed

(20)

Post Installation

Once the installation is complete you will be prompted to run the QuickStart Wizard which will introduce you to the elements of the NuoDB Architecture and automatically start a test database.

You can choose to cancel the QuickStart Wizard, and in that case we would suggest that you review the Windows Command Line Usage for examples of how to start a database manually.

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(21)

Installing a New License

The Free License is limited to a domain of two hosts only. The following instruction will walk you through obtaining and installing a new license.

Obtain a new license

License Type Cost Notes

Developer Free Requires online registration at the NuoDB

WebSite

Allows for unlimited hosts

Not supported for production environments. Professional Priced Requires a purchase by contacting the Nuo

DB Sales Organization

Supported for production environments

Regardless of how you obtain your new license, you will receive an email with your new license file as an attachment.

Installation Steps

Before you Begin

You must have a Broker running to successfully install your new license.

The following instruction assumes you are using default domain and password as defined in the etc/default.properties file.

Save your License

The license needs to be saved to and installed on the host running the Broker, only. There is no need to install the license to any other host in your NuoDB Domain.

Start the Broker

# By default, the NuoDB installation will automatically start a broker and domain

for you unless you installed using the TAR.GZ files.

UNIX

$ java -jar /opt/nuodb/jar/nuoagent.jar &

Windows

> start java -jar "c:\Program Files\nuodb\jar\nuoagent.jar"

(22)

# Always use the nuodbmanager utility when administering your domain and database

from the command line.

UNIX

$ java -jar /opt/nuodb/jar/nuodbmanager.jar --broker localhost --password bird

Windows

> java -jar "c:\Program Files\nuodb\jar\nuodbmanager" --broker localhost --password

bird

Install your new license

# The following command is for all platforms

nuodb [domain] > apply domain license licenseFile /path/to/your/License_File.txt

Verify your new license

# The following command is for all platforms

nuodb [domain] > show domain license

(23)

1. 2. 3. 1. 1.

Uninstalling NuoDB

To unistall NuoDB:

Windows 7 Control Panel

Navigate to Start > Control Panel > Programs > Uninstall a program Select NuoDB from the list of installed programs and click Unistall/Change. The NuodDB Uninstall dialog opens.

Click Uninstall to uninstall NuoDB.

Windows Command Line Interface

Open a CMD window (Start > Run > cmd).

Run the following commands:

> cd C:\Program Files\NuoDB\

> uninstall nuodb-sh

Linux/Mac OSX

Open a Terminal and run the following commands:

$ cd /opt/nuodb

$ sudo sh uninstall-nuodb.sh

$ Password:

$ This will uninstall NuoDB and remove ALL associated files and preferences.

$ Do you wish to proceed with the uninstall? (Y/n): y

$ Uninstalling NuoDB...

$ NuoDB successfully uninstalled.

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(24)

Getting Started

Get started with NuoDB by running a NuoDB startup script that creates a sample database for you to explore, or by creating your own database. Run a sample client that shows how to connect an application to a NuoDB database.

QuickStart

Using Command Line To Get Started

Developing with NuoDB

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(25)

QuickStart

Get started with NuoDB by running a NuoDB startup script that creates a domain, a database, and a sample schema. The script starts NuoDB Console and the NuoDB SQL client, so you can use NuoDB with a small sample project.

Run the QuickStart

Execute SQL Commands

Use NuoDB Web Console to Explore the Domain

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(26)

1.

2.

Run the QuickStart

Procedure to get the product up and running with a sample NuoDB database. The Quickstart runs as a part of the installation however, these instructions will guide you after the product is installed. This task is part of the NuoDB QuickStart, which offers an introduction to the basic features of NuoDB.

Run the QuickStart Wizard

Windows and Mac OS X Wizard

From the NuoDB System Tray select NuoDB Console. The NuoDB Console opens in a browser tab.

On the Console > Home tab, click Start QuickStart. Note

Currently, there is no system tray available for Linux installations. To run the QuickStart manually on a Linux install, see Command-Line instructions in the next section

(27)

3.

4.

5.

Once the database is created, click the Admin or Explorer link to login to the domain containing the database.

The Domain Login dialog appears.

(28)

5.

The Explorer link opens to the Console Explorer tab, showing the test database in the Data Hierarchy tree. Click here to expand...

(29)

5.

Run the QuickStart Command-Line

The quickstart can also be executed form the command line. Running from the command line accomplishes the same steps as the visual quickstart and provides you with a SQL prompt for further exploring.

LINUX/MAC

$ /opt/nuodb/run-quickstart

WINDOWS

> c:\Program Files\NuoDB\run-quickstart.cmd

QuickStart Tasks

On all platforms, running the Quickstart accomplishes the following: Configures your system path.

Creates a domain (which is named domain) using the settings in the default.properties file.

The initial administrator (with a user name of domain) has an initial password (bird), and a second domain administrator user is quickstart with a password of quickstart.

Starts a NuoDB database, named test, with its storage repository in WINDOWS: \tmp\test_archive

LINUX/MAC: /tmp/test_archive Creates sample schema (hockey). Creates a sample database (test). Imports data to the sample database from:

WINDOWS: %\nuodb\samples\quickstart\bruins.csv LINUX/MAC: $/nuodb/samples/quickstart/bruins.csv

Starts the NuoDB SQL client (nuosql), with user name and password of dba and goalie, respectively. Starts NuoDB Console as a process. By default it starts on http://localhost:8080.

After the script finishes:

Take a look at the remaining tasks in the Quick Start documentation to discover how you can experiment with the NuoDB SQL client, NuoDB Manager and NuoDB Console.

(30)

To understand the role of various NuoDB system components, look at the Overview topic.

Other topics explain how to start a pilot project of your own, how to set up and administer a system on the cloud, and connect a client to NuoDB.

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(31)

1.

2.

3.

4.

Console > Explorer QS

Double-click the database.

The Database Login dialog launches with the default username and password (dba, goalie).

If not already present, enter the username and password, and click Login. You are now logged into database as DBA.

To run a query against the database, click New. A tabbed New Query window opens.

Write a SQL query and click Execute Query.

Note

(32)

4.

The query executes.

NuoDB® Company Website queries, e.g., hockey.teams or system.connections. This is because the SQL Explorer uses the USE statement internally in the Database Hierarchy, and therefore its use in the Query window can produce SQL processing conflicts. Selecting a schema in the Database Hierarchy implements USE for your query.

(33)

Execute SQL Commands

Start NuoSQL from the Command-Line

To launch nuosql from the command line, open a terminal or command line window and cd to: Linux/Mac

opt/nuodb/bin

Windows

C:\Program Files\nuodb\bin Then issue the following command.

nuosql <DatabaseName>@<HostName> --user <UserName> --password <Password>

Linux/Mac Example... Linux/Mac Command

$ nuosql test@localhost --user dba --password goalie

Linux/Mac Result

Execute your SQL Commands Windows Example...

Windows Command

> cd "c:\Program Files\nuodb\bin"

> nuosql.exe test@localhost --user dba --password goalie

(34)

Execute your SQL Commands

Executing SQL Commands

Once NuoSQL is started you can execute SQL commands as you normally would. More information regarding the NuoDB SQL Syntax can be found here and here.

In the window where the SQL session is running issue the following command:

SQL> SHOW TABLES;

Tables in schema HOCKEY

MASTER

SCORING

TEAMS

To see what is in the table, run the following:

SQL> SELECT * FROM teams ORDER BY name;

YEAR TMID CONFID DIVID RANK PLAYOFF G W L T OTL NAME

- - --- -- - -- -- -- --

---

1916 TOB 0 0 4 0 14 7 7 0 0 228th Battalion

1972 ALB 0 WW 5 0 78 38 37 3 0 Alberta Oilers

2010 AND WC PC 2 CQF 82 47 30 0 5 Anaheim Ducks

<ETC>

(35)

SQL> help

ALTER DOMAIN Change definition of a domain (not yet implemented)

ALTER SEQUENCE Change definition of a sequence

ALTER TABLE Change definition of a table

ALTER TRIGGER Change definition of a trigger

ALTER USER Change definition of a user

<ETC>

Exit the NuoDB SQL client by typing quit at the SQL prompt:

SQL> quit

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(36)

1. 2.

a. b.

Web Console with the QuickStart

Use NuoDB Web Console to view, analyze, and manage a domain. This example uses the domain created by running the Quick Start.

Connect to the Web Console

By default the NuoDB Web Console runs on port 8080 and the install packages will configure the Web Console to automatically run in the background.

The examples shown here are based on the database that is created as a part of the QuickStart. If you have not run the QuickStart please see Run the QuickStart for information.

Open a browser and go to: http://localhost:8080/console.html. Login using the following:

Username: quickstart Password: quickstart

Domain Overview

(37)

View the Database

View the current Test database by selecting database link. The Databases page will view information such as;

Status of the database Transactions per second Number of Client Connections CPU and Memory usage

Click the name of the database to view more details about the database processes and status.

View Database Acitivity

(38)

View Host Information

To view information about the individual hosts in your domain, you must select host from the Domain Overview.

Next Select the individual host you want to work with to view detailed information.

Copyright © 2013 NuoDB, Inc. All rights reserved

(39)

Copy of Execute SQL Commands

NuoDB is bundled with both a browser based and text based analysis and management tools for your NuoDB database.

Launch SQL Explorer

1. Launch the following from your browser: http://localhost:8080/explorer.html 2. Login to your Domain.

Note: this example uses the domain credentials for the QuickStart domain (quickstart / quickstart)

3. Select your database from the Database Hierarchy and supply your database level credentials.

Note: this example uses the domain credentials for the QuickStart domain (dba / goalie)

4. Select "New" from the Database Hierarchy pane, to open a Query Tab and enter your SQL commands.

5. Go To Sample SQL Commands...

Launch NuoSQL from the System Tray

NuoSQL is the command line interface to the NuoDB SQL interface. Use the instruction below that matches your OS.

OSX Tutorial...

(40)

A Terminal window will open with the SQL> prompt.

Sample SQL Commands...

By default using the System Tray to launch the NuoDB SQL Prompt will attempt to connect to the test database that is created when the QuickStart is executed. If this database is not running you will receive an error similar to:

Connection Failed: no NuoDB nodes are available for database "test@localhost" If you receive this error, simply run the QuickStart to start the database.

(41)

A CMD window will open with the SQL> prompt

Sample SQL Commands...

Start NuoSQL from the Command-Line

You can also start NuoSQL directly from the command-line.

NuoSQL Syntax

By default using the System Tray to launch the NuoDB SQL Prompt will attempt to connect to the test database that is created when the QuickStart is executed. If this database is not running you will receive an error similar to:

Connection Failed: no NuoDB nodes are available for database "test@localhost" If you receive this error, simply run the QuickStart to start the database.

Currently, the Linux installation of NuoDB does not have a System Tray integration. On Linux, you must launch NuoSQL from the command line (below).

(42)

nuosql <DatabaseName>@<HostName> --user <UserName> --password <Password>

Unix Example... Unix Command

$ nuosql test@localhost --user dba --password goalie

Unix Result

Windows Example... Windows Command

(43)

Executing SQL Commands

Once NuoSQL is started you can execute SQL commands as you normally would. More information regarding the NuoDB SQL Syntax can be found here and here.

In the window where the SQL session is running issue the following command:

SQL> SHOW TABLES;

Tables in schema HOCKEY

MASTER

SCORING

TEAMS

To see what is in the table, run the following:

SQL> SELECT * FROM teams ORDER BY name;

YEAR TMID CONFID DIVID RANK PLAYOFF G W L T OTL NAME

- - --- -- - -- -- -- --

---

1916 TOB 0 0 4 0 14 7 7 0 0 228th Battalion

1972 ALB 0 WW 5 0 78 38 37 3 0 Alberta Oilers

2010 AND WC PC 2 CQF 82 47 30 0 5 Anaheim Ducks

<ETC>

Type help to show a list of the NuoDB SQL client commands.

SQL> help

ALTER DOMAIN Change definition of a domain (not yet implemented)

ALTER SEQUENCE Change definition of a sequence

ALTER TABLE Change definition of a table

ALTER TRIGGER Change definition of a trigger

ALTER USER Change definition of a user

<ETC>

Exit the NuoDB SQL client by typing quit at the SQL prompt:

SQL> quit

(44)

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(45)

Using Command Line to Get Started

Instead of using NuoDB Console to manage a domain you can use command line tools and programs. You must always start by running a broker to create a domain.

Using Command Line on Linux/Mac

Using Command Line on Windows

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(46)

Linux Command Line Usage

Guidelines for setting up a NuoDB database from the command line (Linux only)

Create a Data Directory

Because NuoDB does not have a default storage directory you are free to create your directory in almost any location. For this sample procedure, create the directory in the temp directory, as shown:

$ sudo mkdir /opt/nuodb/samples/testData

Start a NuoDB Broker

All NuoDB installer packages will configure a broker to start automatically. The .tar.gz package will not configure the broker to start automatically.

Starting a broker defines a NuoDB domain and the domain is required for you to run your NuoDB databases.

# Start a broker with the default host and password by running the following

command:

$ java -jar /opt/nuodb/jar/nuoagent.jar --broker &

Start NuoDB Manager

Use NuoDB Manager to set up and manage processes in the domain from the command line.

NuoDB Manager is a text-based tool to help domain administrators control, monitor, and analyze all aspects of NuoDB domains. Starting the tool requires a broker to be running.

$ java -jar /opt/nuodb/jar/nuodbmanager.jar --broker localhost --password bird

nuodb [domain] >

Start a Storage Manager

The Storage Manager is the process that controls reads and writes of your data on disk. In this sample procedure, the Storage Manager uses the directory that you created earlier for storage.

nuodb [domain] > start process sm host localhost database testDB

Process command-line options:

Archive directory: /opt/nuodb/samples/testData

Initialize archive: true Started process: ... [ pid = 9950 ]

Start a Transaction Engine

The Transaction Engine is the process that executes SQL requests from the client.

(47)

Launch NuoDB SQL

Launch the NuoDB SQL client: nuosql This is the default utility for editing and executing SQL..

$ /opt/nuodb/bin/nuosql gsDatabase@localhost --user dba --password dba

SQL> CREATE TABLE t1 (DATA varchar (100), CUR_TIME timestamp default('now'));

SQL> INSERT INTO t1 (DATA) VALUES ('Time of update is:');

SQL> SELECT * FROM t1;

DATA CUR_TIME

--- ---

Time of update is: 2012-10-09 16:52:49.875664

Linux Startup Script

The sample script named nuoagent is located in /opt/nuodb/etc/ that can be used on any UNIX platform, including instances of NuoDB on a cloud, such as Amazon EC2 or others.

During installation NuoDB creates both a user and a group named nuodb. You can set up the system to run the init script on startup, and also specify (independent of the script) that NuoDB should run as the user nuodb.

LSB Distributions (RHEL, Centos)

Copy and Confirgure the script to your /etc/init.d folder.

$ sudo cp ${$NUODB_ROOT}/etc/nuoagent /etc/init.d/nuoagent

$ chmod 755 /etc/init.d/nuoagent

$ chmod +x /etc/init.d/nuoagent

$ chkconfig --add nuoagent

$ chkconfig --level 2345 nuoagent on

Use the following commands to start and stop the service:

$ sudo service nuoagent start

$ sudo service nuoagent stop

$ sudo service nuoagent restart

$ sudo service nuoagent status

To delete the service

$ chkconfig --del nuoagent

LSB distributions (Debian, Ubuntu)

Install the service

(48)

$ sudo cp ${$NUODB_ROOT}/etc/nuoagent /etc/init.d/nuoagent

$ chmod 755 /etc/init.d/nuoagent

$ sudo update-rc.d nuoagent defaults 98 02

To delete the service

$ sudo update-rc.d -f nuoagent remove

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER Note: Using nuodbmanager to create a domain administrator causes the broker for the domain to write database account

(49)

Windows Command Line Usage

Guidelines for setting up a NuoDB database from the command line (Windows only)

Create an Archive Directory

NuoDB does not have a default Archive Directory so, you are free to create one in almost any location. For this sample procedure, create the directory in the \temp

directory, as shown:

> mkdir c:\temp\gsData

Start a NuoDB Broker

The NuoDB Windows installer will configure a broker to start automatically so the following if for informational purposes only. Starting a broker defines a NuoDB domain and the domain is required for you to run your NuoDB databases.

# Start a broker with the default host and password by running the following

command:

> start java -jar "c:\Program Files\nuodb\jar\nuoagent.jar" --broker

Start NuoDB Manager

Use NuoDB Manager to set up and manage processes in the domain from the command line.

NuoDB Manager is a text-based tool to help domain administrators control, monitor, and analyze all aspects of NuoDB domains. Starting the tool requires a broker to be running.

> java -jar "C:\Program Files\nuodb\jar\nuodbmanager.jar" --broker localhost

--password bird

nuodb [domain] >

Start a Storage Manager

The Storage Manager is the process that controls reads and writes of your data on disk. In this sample procedure, the Storage Manager uses the directory that you created earlier for storage.

nuodb [domain] > start process sm host localhost database testDB

Process command-line options:

Archive directory: c:\temp\gsData

Initialize archive: true Started process: ... [ pid = 9950 ]

Start a Transaction Engine

The Transaction Engine is the process that executes SQL requests from the client.

(50)

nuodb [domain] > start process te host localhost database testDB

Process command-line options: --dba-user dba --dba-password dba

Started process: ... [ pid = 9961 ]

Launch NuoDB SQL

Launch the NuoDB SQL client: nuosql This is the default utility for editing and executing SQL..

> "c:\Program Files\nuodb\bin\nuosql.exe" gsDatabase@localhost --user dba --password

dba

SQL> CREATE TABLE t1 (DATA varchar (100), CUR_TIME timestamp default('now'));

SQL> INSERT INTO t1 (DATA) VALUES ('Time of update is:');

SQL> SELECT * FROM t1;

DATA CUR_TIME

--- ---

Time of update is: 2012-10-09 16:52:49.875664

(51)

Developing with NuoDB

List of samples in Java, C++, Hibernate, and JRuby

You can find most of the programming samples in the \nuodb\samples folder (Windows), /nuodb/samples folder (Linux/Mac). The following table lists the samples.

Directory Description Language

java Simple “Hello” program. Java

cpp Simple “Hello” program. C++

hibernate Sample application that makes use of Hibernate and the NuoDB Hibernate dialect.

Java dialect

jruby Includes a sample application and Gems JRuby

C++ with NuoDB—Getting Started

Simple “Hello” program in C++

JRuby with NuoDB—Getting Started

A JRuby application that connects to NuoDB and uses NuoDB gems.

Java with NuoDB—Getting Started

A simple "Hello" Javs program

Java Hibernate with NuoDB—Getting Started

How to run the Hibernate sample client using processes created by the Quick Start script.

Copyright © 2013 NuoDB, Inc. All rights reserved

SPACER

(52)

C++ with NuoDB—Getting Started

A simple “Hello” program in C++

The sample program is located in:

%NUODB_ROOT%\samples\cpp (Windows) $NUODB_ROOT/samples/cpp (Linux/Mac)

Linux

Ensure that LD_LIBRARY_PATH includes /opt/nuodb/lib64

Then run the built executable:

> HelloDB test@localhost

cd /opt/nuodb/samples/cpp

gcc -I../../include HelloDB.cpp \

../../lib64/libNuoRemote.so -o HelloDB

Windows

cd C:\Program Files\NuoDB\samples\cpp

copy "C:\program files\NuoDB\bin\NuoRemote.dll"

cl/EHsc HelloDB.cpp \

"c:\program files\NuoDB\lib\NuoRemote.lib"

Mac OSX

g++ -Xlinker -rpath -Xlinker ../../lib64 -I../../include HelloDB.cpp \

../../lib64/libNuoRemote.dylib -o HelloDB

You should see the following to stdout:

Name with id of 12: Fred # 12

(53)

1. 2. 3. 4. 5. 6.

Java Hibernate with NuoDB—Getting Started

How to run the Hibernate sample client using processes created by the Quick Start script.

For Java users NuoDB supports a Hibernate dialect and provides a sample application that makes use of Hibernate and the NuoDB Hibernate dialect. NuoDB supports versions 3.6.6 and 4.1.x of Hibernate.

The files for the Hibernate sample application are in the installation area as follows: \samples\hibernate (Windows)

/samples/hibernate (UNIX) The hibernate dialect jar file is in:

\jar\nuodb-hibernate-1.0.jar (Windows) /jar/nuodb-hibernate-1.0.jar (UNIX) Prerequisite:

The example requires Maven, which is a software project management tool available from the Apache Maven Project ( http://maven.apac

).

he.org/

Steps:

Make sure you have Maven installed. Install the hibernate dialect for NuoDB. On UNIX

mvn install:install-file -DgroupId=com.nuodb -DartifactId=nuodb-jdbc

-Dversion=1.0 \

-Dpackaging=jar -Dfile=/opt/nuodb/jar/nuodbjdbc.jar

mvn install:install-file -DgroupId=com.nuodb -DartifactId=nuodb-hibernate

-Dversion=1.0 \

-Dpackaging=jar -Dfile=/opt/nuodb/jar/nuodb-hibernate-1.0.jar

On Windows

mvn install:install-file -DgroupId=com.nuodb -DartifactId=nuodb-jdbc

-Dversion=1.0 ^

-Dpackaging=jar -Dfile="c:\Program Files\NuoDB\jar\nuodbjdbc.jar"

mvn install:install-file -DgroupId=com.nuodb -DartifactId=nuodb-hibernate

-Dversion=1.0 ^

-Dpackaging=jar -Dfile="c:\Program Files\NuoDB\jar\nuodb-hibernate-1.0.jar"

Set up a sample database by launching the script for the QuickStart sample. Change to the directory where you installed NuoDB and run the following command.

run-quickstart

Exit from the NuoDB SQL client: SQL> quit

Change to the directory that has the sample application: \samples\hibernate (Windows)

/samples/hibernate (UNIX)

(54)

6.

mvn compile mvn exec:java

Results

The application adds two users, updates the name and address for each user to uppercase, and runs the query again. The results appear as shown:

Found 2 user records:

User (1/0), Username: fred, Name: Fred Flintstone, admin

home: Street: '301 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'

bill: Street: '301 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'

ship: Street: '1 Slate Drive', Zipcode: '00002', City: 'Granitetown'

User (2/0), Username: barney, Name: Barney Rubble, member

home: Street: '303 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'

bill: Street: '303 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'

ship: Street: '1 Slate Drive', Zipcode: '00002', City: 'Granitetown'

Found 2 user records:

User (1/1), Username: fred, Name: FRED FLINTSTONE, member

home: Street: '301 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'

bill: Street: '301 COBBLESTONE WAY', Zipcode: '00001', City: 'BEDROCK'

ship: Street: '1 Slate Drive', Zipcode: '00002', City: 'Granitetown'

User (2/1), Username: barney, Name: BARNEY RUBBLE, member

home: Street: '303 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'

bill: Street: '303 COBBLESTONE WAY', Zipcode: '00001', City: 'BEDROCK'

ship: Street: '1 Slate Drive', Zipcode: '00002', City: 'Granitetown'

If you have trouble compiling and running the sample, remove all NuoDB processes and run the Quick Start script again. You can remove processes using either operating system tools or NuoDB Manager.

(55)

Java with NuoDB—Getting Started

Sample Program

The sample Java program is installed by default in the following directory. Linux/Mac

/opt/nuodb/samples/java/HelloDB.java

Windows

c:\Program Files\NuoDB\samples\java\HelloDB.java

Run the Sample

Linux/Mac

$ sudo cd /opt/nuodb/samples/java

$ javac HelloDB.java

$ java -classpath .:../../jar/nuodbjdbc.jar HelloDB

Windows

> cd "c:\Program Files\NuoDB\samples\java"

> javac HelloDB.java

> java -classpath .;..\..\jar\nuodbjdbc.jar HelloDB

Expected Output

The table 'NAMES' was created.

New id=1 for column ID

New id=2 for column ID

New id=3 for column ID

New id=4 for column ID

New id=5 for column ID

New id=6 for column ID

New id=7 for column ID

New id=8 for column ID

New id=9 for column ID

New id=10 for column ID

New id=11 for column ID

New id=12 for column ID

New id=13 for column ID

New id=14 for column ID

New id=15 for column ID

The NAME with id of 12 is 'Fred # 12'

(56)

public class HelloDB {

/** The driver class provided by NuoDB. */

public static final String DRIVER_CLASS = "com.nuodb.jdbc.Driver";

/** The base URL for connecting to a local database server. */

public static final String DATABASE_URL = "jdbc:com.nuodb://localhost/";

// the established connection to a local server

private final Connection dbConnection;

/**

* Creates an instance of HelloDB and connects to a local server,

* as the given user, to work with the given named database

*

* @param user the user name for the connection

* @param password the password for the given user

* @param dbName the name of the database at the server to use

*/

public HelloDB(String user, String password, String dbName)

throws SQLException

{

Properties properties = new Properties();

properties.put("user", user);

properties.put("password", password);

properties.put("schema", "hello");

dbConnection =

DriverManager.getConnection(DATABASE_URL + dbName, properties);

}

/** Closes the connection to the server. */

public void close() throws SQLException {

dbConnection.close();

}

(57)

1. 2. 3.

4.

JRuby with NuoDB—Getting Started

A JRuby application that connects to NuoDB and uses NuoDB gems.

The sample is in the following file:

%NUODB_ROOT%\samples\jruby\sample.rb (Windows), $NUODB_ROOT/samples/jruby/sample.rb (UNIX)

The following code fragment shows the first several lines of sample.rb, which indicate how to make the connection to NuoDB:

require 'rubygems'

require 'active_record'

puts "Connecting to database..."

ActiveRecord::Base.establish_connection(

:adapter => 'nuodb',

:database => 'test',

:username => 'cloud',

:password => 'user'

)

The application creates a schema, creates several records using the schema definition, and reads them back. It requires the following files: NuoDB's Active Record gem - activerecord-jdbcnuodb-adapter-1.0.gem

NuoDB's JDBC gem - jdbc-nuodb-1.0.gem

If you haven't already, download and install the JRuby (http://jruby.org/). Instructions for building and installing the Gem may be found here.

The Gems used by the sample cannot be installed in /sample/jruby. In a different directory install them as follows:

> gem install hoe

> gem update --system

> gem install activerecord

> gem install jdbc-nuodb-1.0.gem

> gem install activerecord-jdbcnuodb-adapter-1.0.gem

(58)

4. 5.

> gem list

* LOCAL GEMS *

activemodel (3.2.6)

activerecord (3.2.6)

activerecord-jdbc-adapter (1.2.2)

activerecord-jdbcnuodb-adapter (1.0)

activesupport (3.2.6)

arel (3.0.2)

bouncy-castle-java (1.5.0146.1)

builder (3.0.0)

bundler (1.1.3)

i18n (0.6.0)

jdbc-nuodb (1.0)

jruby-launcher (1.0.13 java java)

jruby-openssl (0.7.6.1)

multi_json (1.3.6)

rake (0.9.2.2, 0.8.7)

rubygems-bundler (0.9.0)

rvm (1.11.3.3)

sources (0.0.1)

tzinfo (0.3.33)

(59)

5.

Connecting to database...

Create tables...

-- drop_table("users")

-> 0.1380s

-> 0 rows

-- drop_table("addrs")

-> 0.0030s

-> 0 rows

-- create_table("users")

-> 0.0080s

-> 0 rows

-- create_table("addrs")

-> 0.0070s

-> 0 rows

Create user records...

Created User(3), Username: fred, Name: Fred Flintstone, admin

Address: Addr(3:3) Street: 301 Cobblestone Way City: Bedrock Zip: 00001

Created User(4), Username: barney, Name: Barney Rubble, member

Address: Addr(4:4) Street: 303 Cobblestone Way City: Bedrock Zip: 00001

Print user records...

Found 2 records:

User(3), Username: fred, Name: Fred Flintstone, admin

Address: Addr(3:3) Street: 301 Cobblestone Way City: Bedrock Zip: 00001

User(4), Username: barney, Name: Barney Rubble, member

Address: Addr(4:4) Street: 303 Cobblestone Way City: Bedrock Zip: 00001

Modify user records...

Print user records...

Found 2 records:

User(3), Username: fred, Name: FRED FLINTSTONE, member

Address: Addr(3:3) Street: 301 COBBLESTONE WAY City: Bedrock Zip: 00001

User(4), Username: barney, Name: BARNEY RUBBLE, admin

Address: Addr(4:4) Street: 303 COBBLESTONE WAY City: Bedrock Zip: 00001

SPACER

(60)

Sample Client Programs

Several sample open source applications and demos are available on Github: Storefront Demo Cayenne ColdFusion JBoss Node.js Apache Tomcat Apache Tomcat Servlet

SPACER

(61)

Migrating Databases to NuoDB

The NuoDB Migrator tool provides a means to move data out of other databases and into NuoDB. To get started, see NuoDB Migrator as well as specific use cases for:

MSSQL Migration MySQL Migration, Oracle Migration PostgreSQL Migration.

Full details are documented in Load command Dump command, and Schema command.

SPACER

(62)

APIs and Drivers

Programming with NuoDB is like programming with any other database: you write your client program and connect the client executable to NuoDB using a database connection such as JDBC.

In this section you should find everything you need to get going: list of supported drivers with download links, APIs, sample client programs, and a client-host schematic that illustrates the NuoDB architecture.

NuoDB Drivers

NuoDB APIs

Sample Client Programs

Understanding Client Connections

SPACER

(63)

NuoDB Drivers

NuoDB drivers are available as part of the product and on GitHub. NuoDB also supports tools such as SQuirreL and DbVisualiser for working with databases.

Available NuoDB Drivers

NuoDB Drivers on GitHub

JDBC Connections

ODBC Connections

NuoDB ADO.NET Driver

NuoDB PHP PDO Driver

NuoDB Zend Framework Adapter

SPACER

(64)

Available NuoDB Drivers

NuoDB automatically installs drivers and supports database connections as shown in the following table. Additional drivers are available on GitHub.

Installed Drivers

NuoDB supports the following drivers as part of the product:

Driver Usage

JDBC The JDBC driver is installed automatically in the jar directory in the installation area.

ODBC The NuoDB installer automatically installs the 32-bit driver for ODBC connections on Windows platforms. NOTE: 64-bit Windows applications will not work properly with the 32-bit NuoDB ODBC driver.

.NET The NuoDB installer automatically installs the 32-bit driver for ODBC connections on Windows platforms. NOTE: 64-bit Windows applications will not work properly with the 32-bit NuoDB ODBC driver.

PHP PDO NuoDB PHP PDO Driver is available on GitHub and as part of the NuoDB product. The NuoDB Zend Framework adapter is also supported and depends on this driver. The NuoDB PHP PDO Driver on Linux supports PHP 5.4.x.

NuoDB Drivers on GitHub

See http://nuodb.github.com for information about language-specific drivers supported by means of GitHub. The drivers include the following:

Name of Driver Description URL

Node.js Node.js node-db compatible native driver for NuoDB

NuoDB database bindings for Node.js

PHP PHP compatible native driver for NuoDB Use Basic PHP Drivers on Microsoft Windows and Linux. This driver supports PHP 5.4.x.

PHO PDO PHP PDO driver for NuoDB Use PHP PDO Driver on Linux SilverStripe NuoDB for SilverStripe (BETA) SilverStripe Adapter for the PHP PDO

NuoDB Module

(65)

Ruby and JRuby Ruby native driver, JRuby active record Write Ruby Extensions in C/C++, Use ActiveRecord Driver, Use Native Driver, Use Ruby Rails

The NuoDB Ruby Driver requires Ruby 1.9.3.

NuoDB Ruby Native Database Driver JRuby ActiveRecord NuoDB Adapter Project

NuoDB Forum: Use NuoDB Ruby driver

Drupal NuoDB Drupal Database Driver (BETA) NuoDB Driver for Drupal

Go NuoDB Go Driver (BETA) NuoDB Go Driver (Beta)

Perl NuoDB DBD Module for Perl DBI. (BETA) NuoDB for Perl (Beta)

Tools and Programming Samples on GitHub

In addition to drivers on Github, NuoDB supports the following command line interfaces and programming examples on Github. Command line interface for migration to NuoDB database http://nuodb.github.com/migration-tools/

JNDI resource to use with the HockeyServlet class definition Files on Github for the sample

NuoDB Community Forum

NuoDB provides a community forum, where you can read and submit comments, questions, and answers related to all things NuoDB. http://www.nuodb.com/community/

For example, technical information about developing applications is at the following URL: http://www.nuodb.com/community/forumdisplay.php?19-Developing-Applications

See NuoDB Drivers on GitHub for a list of drivers available on GitHub.

SPACER

(66)

NuoDB Drivers on GitHub

Sources of information to assist with writing NuoDB client applications and drivers.

NuoDB Drivers on GitHub

See http://nuodb.github.com for information about language-specific drivers supported by means of GitHub. The drivers include the following:

Name of Driver Description URL

Node.js Node.js node-db compatible native driver for NuoDB

NuoDB database bindings for Node.js

PHO PDO PHP PDO driver for NuoDB

Includes support for Linux, Mac OS X and Windows

NuoDB PHP PDO Driver

SilverStripe NuoDB for SilverStripe (BETA) SilverStripe Adapter for the PHP PDO NuoDB Module

PHP Zend PHP PDO extension for Zend. (BETA) This adapter is developed and tested with the Zend Framework Version 1.11.12. The interface is written as a PHP PDO extension.

Zend Framework Data Adapter for NuoDB

Ruby and JRuby Ruby native driver, JRuby active record Write Ruby Extensions in C/C++, Use ActiveRecord Driver, Use Native Driver, Use Ruby Rails

The NuoDB Ruby Driver requires Ruby 1.9.3.

NuoDB Ruby Native Database Driver JRuby ActiveRecord NuoDB Adapter Project

NuoDB Forum: Use NuoDB Ruby driver

Drupal NuoDB Drupal Database Driver (BETA) NuoDB Driver for Drupal

Go NuoDB Go Driver (BETA) NuoDB Go Driver (Beta)

Perl NuoDB DBD Module for Perl DBI. (BETA) NuoDB for Perl (Beta)

Tools and Programming Samples on GitHub

In addition to drivers on Github, NuoDB supports the following command line interfaces and programming examples on Github. Command line interface for migration to NuoDB database http://nuodb.github.com/migration-tools/

JNDI resource to use with the HockeyServlet class definition Files on Github for the sample

NuoDB Community Forum

(67)

SPACER

(68)

JDBC Connections

NuoDB automatically installs drivers and supports database connections by means of JDBC. It also supports several tools that work with JDBC connections.

The JDBC driver is installed automatically in: Windows

\jar

%NUODB_ROOT% directory Linux/Mac

/jar

$NUODB_ROOT directory, where NUODB_ROOT is the folder where you installed NuoDB.

RDBMS Access

JDBC provides RDBMS access by allowing you to embed SQL inside Java code. The correct URL syntax for a NuoDB JDBC client is as follows:

jdbc:com.nuodb://{BROKER}:{PORT}/{DATABASE}

where

BROKER is the address for the NuoDB Broker agent, as IP address or DNS name string. PORT is the configured port for that broker; if not defined it is assumed to be 48004. DATABASE is the name of the NuoDB database you are connecting to and is required.