Wednesday Apr 17, 2013

MySQL Community Reception - Celebrate with Oracle's MySQL Team!

Join Oracle’s MySQL team on April 22 to celebrate the general availability of MySQL 5.6. Get together in a relaxed atmosphere—make a toast, enjoy product demos and fun activities, and celebrate the MySQL community effort and contributions that made the latest release possible. Register here today!

Date: Monday, April 22, 2013

6:30 PM – 8:30 PM

Location: TechMart Santa Clara
5201 Great America Parkway
Santa Clara, CA, USA
(1-minute walk from Hyatt Regency Santa Clara)

Don't miss the opportunity to enjoy an evening of casual but informative conversation, with complimentary food and refreshments from Oracle! Walk-ins are also welcome.

MySQL Cluster 7.3 DMR2: Increasing Developer Flexibility and Simplicity

Highlights: Foreign Keys, NoSQL JavaScript Connector, MySQL 5.6 and Auto-Tuned Clustering

The MySQL team at Oracle are excited to announce the immediate availability of the second MySQL Cluster 7.3 Development Milestone Release (DMR)

Some might call MySQL Cluster 7.3 “the foreign keys release” – and sure enough it is a major engineering achievement to build a distributed database that enforces referential integrity across a shared-nothing cluster, while maintaining ACID compliance and cross-shard JOINs. But MySQL Cluster 7.3 is a lot more as well. 

The design focus has been on enabling developer agility – making it simpler and faster than ever to enhance new services with a highly scalable, fault tolerant, real-time database – with minimum development or operational effort. 

The key enhancements delivered by MySQL Cluster 7.3 are summarized below.

Foreign Keys: Strengthens data modeling and simplifies application logic by automatically enforcing referential integrity between different tables distributed on different shards, on different nodes…..even in different data centers 

NoSQL JavaScript Connector for Node.js: Enables a single programming language and a single tool-chain by extending JavaScript from the client to the server, all the way through to the database, bypassing the SQL layer to deliver lower latency and reduced development cycles.  

MySQL 5.6 Support: Developers can combine the InnoDB and MySQL Cluster NDB storage engines within a single database, using the very latest MySQL 5.6 release.

Connection Thread Scalability: Increases cluster performance and capacity by improving the throughput of each connection to the data nodes, thus reducing the number of connections that need to be provisioned, and enabling greater scale-out headroom.  Current testing is showing up to 3x higher throughput per connection, enabling more client threads to use each connection. 

Auto-Installer: Get it all up and running in minutes! Graphically configure and provision a production-grade cluster, automatically tuned for your workload and environment, without ever resorting to “RTFM”.  

MySQL Cluster: Automated Tuning and Configuration  

Read the MySQL Cluster 7.3 DMR2 DevZone article for more detail on all of the enhancements discussed above.

You can download the source and binaries for MySQL Cluster 7.3 DMR2 today (select the Development Releases tab).  


MySQL Cluster Tutorial: NoSQL JavaScript Connector for Node.js

This tutorial has been authored by Craig Russell and JD Duncan

The MySQL Cluster team are working on a new NoSQL JavaScript connector for MySQL. The objectives are simplicity and high performance for JavaScript users:

- allows end-to-end JavaScript development, from the browser to the server and now to the world's most popular open source database

- native "NoSQL" access to the storage layer without going first through SQL transformations and parsing.

Node.js is a complete web platform built around JavaScript designed to deliver millions of client connections on commodity hardware. With the MySQL NoSQL Connector for JavaScript, Node.js users can easily add data access and persistence to their web, cloud, social and mobile applications.

While the initial implementation is designed to plug and play with Node.js, the actual implementation doesn't depend heavily on Node, potentially enabling wider platform support in the future.

Changes since the previous blog:

- InnoDB is now supported via the mysql adapter (accesses data via the mysqld server)

- Auto-increment columns are now supported

- Default values in columns are now supported

- Multiple databases are now supported

- Column converters for JavaScript types that need special (user-written) mapping to database types are now supported

- Queries that specify all columns of a primary or unique key index are now supported

- When acquiring a connection or session, specific table or class metadata can be provided in order to pre-load database metadata and signal an error if not all metadata can be loaded

- Users can now get metadata for tables by using the session.getMetadata function

- The user interface to map JavaScript domain objects to database tables has been significantly simplified

Implementation

The architecture and user interface of this connector are very different from other MySQL connectors in a major way: it is an asynchronous interface that follows the event model built into Node.js.

To make it as easy as possible, we decided to use a domain object model to store the data. This allows for users to query data from the database and have a fully-instantiated object to work with, instead of having to deal with rows and columns of the database. The domain object model can have any user behavior that is desired, with the NoSQL connector providing the data from the database.

To make it as fast as possible, we use a direct connection from the user's address space to the database. This approach means that no SQL (pun intended) is needed to get to the data, and no SQL (and again) server is between the user and the data.

The connector is being developed to be extensible to multiple underlying database technologies, including direct, native access to both the MySQL Cluster "ndb" and InnoDB storage engines. The current release supports ndb via both native access and mysqld; and supports InnoDB via mysqld.

The connector integrates the MySQL Cluster native API library directly within the Node.js platform itself, enabling developers to seamlessly couple their high performance, distributed applications with a high performance, distributed, persistence layer delivering 99.999% availability.

The following sections take you through how to connect to MySQL, query the data and how to get started.


Connecting to the database

A Session is the main user access path to the database. You can get a Session object directly from the connector using the openSession function:

var nosql = require("mysql-js");

var dbProperties = {

    "implementation" : "ndb",

    "database" : "test"

};

nosql.openSession(dbProperties, null, onSession);

The openSession function calls back into the application upon creating a Session. The Session is then used to create, delete, update, and read objects.

Default database

Every session and connection to the database has a default database associated with it. When mapping domain objects to the database, or using a table name to identify a table, users can specify the database by using the explicit form for the table name: 'tableName.databaseName'. If users omit the databaseName, the default database associated with the session is used.

This feature supports multi-tenancy by allowing the database name to be specified during connection, while allowing the table to dynamically refer to the specific database in use.

Pre-load metadata for tables or domain objects

If your application requires specific tables or domain objects to be available, you can specify the tables and domain objects in your connect or openSession function. For example, if you need the table 't_basic' and the domain object 'Employee' to run the application, you can specify these during the connect or openSession functions.

nosql.openSession(dbProperties, ['t_basic', Employee], onSession);

If the t_basic table or the mapped Employee domain object are not able to be used by the session, then an error will be signaled and the onSession callback will report the specific error.

Getting Table metadata

If getting metadata associated with your application's tables is important, you can get the information by using the session function getMetadata. This function will return information about the specified table in the same format as used in mapTable. You can get the names and types of the table's columns and use the information to dynamically access tables and columns.

Reading data

The Session can read data from the database in a number of ways. If you simply want the data from the database, you provide a table name and the key of the row that you want. For example, consider this schema:

create table employee (

  id int not null primary key,

  name varchar(32),

  salary float

) ENGINE=ndbcluster;

Since the primary key is a number, you can provide the key as a number to the find function.

function onSession = function(err, session) {

  if (err) {

    console.log(err);

    ... error handling

  }

  session.find('employee', 0, onData);

};

function onData = function(err, data) {

  if (err) {

    console.log(err);

    ... error handling

  }

  console.log('Found: ', JSON.stringify(data));

  ... use data in application

};

If you want to have the data stored in your own domain model, you tell the connector which table your domain model uses, by specifying an annotation, and pass your domain model to the find function.

function Employee = function(id, name, salary) {

  this.id = id;

  this.name = name;

  this.salary = salary;

  this.giveRaise = function(percent) {

    this.salary *= percent;

  }

};

annotations.mapClass(Employee, {'table' : 'employee'});

function onSession = function(err, session) {

  if (err) {

    console.log(err);

    ... error handling

  }

  session.find(Employee, 0, onData);

};

Special Domain Object Property Types

If your domain object uses types that do not map directly to database types, you can use column converters to transform domain types to database types.

For example, if you have a domain type such as a MaritalStatus type that contains only values of type MARITAL_STATUS, you can define a conversion that translates domain object values into database values.

var MARITAL_STATUS = {

NEVER_MARRIED: {value: 0, code: 'N', name: 'NEVER_MARRIED'},

MARRIED: {value: 1, code: 'M', name: 'MARRIED'},

DIVORCED: {value: 2, code: 'D', name: 'DIVORCED'},

lookup: function(value) {

switch (value) {

case 0: return this.NEVER_MARRIED; break;

case 1: return this.MARRIED; break;

case 2: return this.DIVORCED; break;

default: return null; break;

}

}

};


// column converter for status

var statusConverter = {

toDB: function toDB(status) {

return status.value;

},

fromDB: function fromDB(value) {

return MARITAL_STATUS.lookup(value);

}

};

Updating data

You can update the emp instance in memory, but to make the changes persistent, you need to write it back to the database, using the update function.

function onData = function(err, emp) {

  if (err) {

    console.log(err);

    ... error handling

  }

  console.log('Found: ', JSON.stringify(emp));

  emp.giveRaise(0.12); // gee, thanks!

  session.update(emp); // oops, session is out of scope here

};

Using JavaScript can be tricky because it does not have the concept of block scope for variables. You can create a closure to handle these variables, or use a feature of the connector to remember your variables.

The connector api takes a fixed number of parameters and returns a fixed number of result parameters to the callback function. But the connector will keep track of variables for you and return them to the callback. So in the above example, change the onSession function to remember the session variable, and you can refer to it in the onData function:

function onSession = function(err, session) {

  if (err) {

    console.log(err);

    ... error handling

  }

  session.find(Employee, 0, onData, session);

};

function onData = function(err, emp, session) {

  if (err) {

    console.log(err);

    ... error handling

  }

  console.log('Found: ', JSON.stringify(emp));

  emp.giveRaise(0.12); // gee, thanks!

  session.update(emp, onUpdate); // session is now in scope

};

function onUpdate = function(err, emp) {

  if (err) {

    console.log(err);

    ... error handling

  }

Inserting data

Inserting data requires a mapped JavaScript user function (constructor) and a session. Create a variable and persist it:

function onSession = function(err, session) {

  var data = new Employee(999, 'Mat Keep', 20000000);

  session.persist(data, onInsert);

  }

};

Autoincrement Columns

Columns allow but do not require users to specify the values for autoincrement columns. If users want to specify values for autoincrement columns, for example to reset the autoincrement value for the table, the insert function allows specification of values for these columns.

But if users want to exploit the autoincrement functionality, they must avoid setting a value for autoincrement columns. When mysql-js detects that the user has not specified a value, the next value in sequence is used. In the callback for the insert operation, mysql-js has filled in the missing values.

Default Values

Columns that specify a default value allow but do not require users to specify the values for these columns. If users want to specify values for these columns, the insert function allows specification of 'undefined' for these columns. In these cases, mysql-js will use the default values for these columns.

Deleting data

To remove data from the database, use the session remove function. You use an instance of the domain object to identify the row you want to remove. Only the key field is relevant.

function onSession = function(err, session) {

  var key = new Employee(999);

  session.remove(Employee, onDelete);

  }

};

More extensive queries

Queries are defined using a builder pattern and then executed with parameters that can specialize the query and control the operation of the query.

To define a query, use the createQuery function of Session. Provide a constructor function of a mapped domain object or a table name. The resulting QueryDomainType is returned in the callback. The QueryDomainType is a specialized object that has a property for each property in the user's domain object, or a property for each column in the table. These properties are of type QueryField, and they implement functions that allow you to compare column values of database rows to parameters supplied when you execute the query.

session.createQuery(Employee, function(err, qdt) {

// build and execute the query using qdt

});

To build the query, use the query domain type to filter the results. If nothing else is specified, executing the query will return all rows in the table mapped by Employee as an array of instances of Employee.

To filter the results, similar to using a WHERE clause in SQL, specify a query predicate using the where function of the query domain type. To build a query predicate, you can compare fields in the query domain type to values provided as parameters, using common comparison functions such as equal, greater than, etc. To compare fields, use the query field functions that are created in the query domain type. You can combine predicates using AND and OR functions. For example,

var salaryLowerBound = qdt.param('floor'); // define the formal parameter floor

var compareSalaryLowerBound = qdt.salary.ge(salaryLowerBound); // compare the field salary to the floor

var salaryUpperBound = qdt.param('ceiling'); // define the formal parameter ceiling

var compareSalaryUpperBound = qdt.salary.le(salaryUpperBound); // compare the field salary to the ceiling

 

var combinedComparisons = compareSalaryLowerBound.and(compareSalaryUpperBound);

qdt.where(combinedComparisons); // specify the entire filter for the query

The query api supports a fluent style of query composition. The query can be written as:

qdt.where(qdt.salary.ge(qdt.param('floor')).and(qdt.salary.le(qdt.param('ceiling'))));

The above query filter compares the salary greater or equal to parameter floor and less or equal to ceiling.

Executing Queries

Once the query has been built, you can execute the query, providing the actual parameters to be used in the query. The results of the query are returned as an array in the callback.

qdt.execute({'floor': 40000, 'ceiling': 80000}, function(err, results) {

if (err) throw Error('query failed');

results.forEach(function(result) {

console.out('Employee', result.name, 'has salary', result.salary);

});

}

How to evaluate

The MySQL Connector for JavaScript is available for download and forking from GitHub

Since we are still in the development phase, feedback is especially valuable (so don't hesitate to leave comments on this blog, or head to the MySQL Cluster forum). Try it out and see how easy (and fast) it is to integrate MySQL Cluster into your Node.js platforms.

You can also learn more about other previewed functionality of MySQL Cluster 7.3 Development Milestone Release DevZone article.

About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« April 2013 »
SunMonTueWedThuFriSat
 
1
3
5
6
8
11
12
13
15
16
19
20
21
23
24
27
28
29
    
       
Today