NextDB.net Developer's Guide V. 0.1

Preface

NextDB is a hosted database accessible from JavaScript. Unlike a traditional database, NextDB does not require installation, nor does it require a trained administrator nor knowledge of SQL programming. To begin using NextDB, simply create an account, and log in. Within your account you can create as many databases as you like. NextDB handles all of the complex tasks associated with database maintenance and tuning, automatically. NextDB is different from other database systems because it can be accessed directly from JavaScript running in the web browser. As a practical matter, what this means to you as a developer is that you can write your entire application in HTML and JavaScript, without having to write any servside software at all. In fact, the only development tool you need to get started is a text editor. If you know HTML and a little JavaScript, you have all the skills you need to build powerful data driven applications.

Quickstart

For the impatient, you can go directly to the simple examples page

System Overview

 

Programming Model

NextDB programs are written entirely in JavaScript that executes inside the web pages that compose your application. This is commonly called a Rich Internet Application (RIA), although other monickers such as AJAX apply as well. Technically, a NextDB application is not an AJAX application because although a NextDB application uses asynchronous JavaScript, the responses are JavaScript objects, not XML. Therefore, your program can deal with responses from the server as naturally as it would deal with any variable defined in your program - there is no parsing or unmarshalling required. NextDB has an object oriented JavaScript API which makes it simple to interact with your secure hosted databases. The programming model is easy: create a Connection object using the API, then execute Insert, Update, Query, or Delete operations through the Connection. Instead of blocking, which might freeze your entire application, the response will be provided to your application through an asychronous callback. If your objective is to create data-driven web pages, you won't find a simpler or more efficient system than NextDB.

Security

We all know that JavaScript is insecure, so how is it possible to access a hosted database from JavaScript? The answer lies in NextDB's patent pending "round trip" security technology. We call it a Secure Unique Result-set Identifier (SURID). When data leaves the database, it's accompanied by a digitally signed, tamper proof, and encrypted "ticket". Think of it as a primary key on steroids. The only way to update or delete data from the database is to provide a valid ticket.

NextDB Accounts

When you create your account with NextDB, you will choose a unique name for your account. Choose your name thoughtfully, and if possible, match your NextDB account name to an Internet Domain Name that you own. For example, if you own the Internet Domain Name “kungfu.net”, a good choice for your NextDB account name is “kungfu.net”. An account name, like an email address, cannot be selected if it is already in use by another party. An account name cannot include spaces.


Databases

Within your account, you will create your databases. You can choose any name for a database, but once again, it makes sense to put some thought toward your choice. For example, if you are creating an application to sell shoes online, a good name for your database might be “shoestore”. Database names cannot contain spaces.

Databases

Tables

Each database contains tables that you define. A table is used to group simple columns into a logical data structure. For example, a blogging application might create a table named BLOGGER to hold the information about the blogger. A separate table named BLOG_POSTS would hold the data for each blog post. If you are new to database programming you may wonder, “why should I use more than one table for my application?”. Good table design is a lot like a well organized file cabinet. By creating a table for each logical concept in your data model, you will avoid duplicating data, and you will be able to find data more easily.

Tables

Relationships

Databases can store many related pieces of information. By recording relationships between different pieces of information, the database is empowered to answer detailed search questions: How many friends are on my friend list? Which photos belong to 'partyFreak69'? Both of the questions above involves at least two related pieces of information. In the first question, the related pieces of information are 'friends' and a 'friend list'. In the second query, the related pieces of information are 'photos' and the user named 'partyFreak69'.


In the Tables section you learned that good table design leads to a well organized database. It is equally important to define meaningful relationships between your tables in order to effectively express your data model.

Relationships

Consider two tables, BLOGGER and BLOG_POSTS for a blogging application. One row in the BLOGGER table can be related to multiple rows in the BLOG_POSTS table. This is natural, since a blogger can make as many posts on his blog as he desires. The relationship between BLOGGER and his BLOG_POSTS is an example of a “ONE-TO-MANY” relationship. Although less common, relationships can also be ONE-TO-ONE. For example, in an online store, one shopper can have only one shopping cart. This notion of whether one row's relationship with another row is exclusive or not, is known as the cardinality of the relationship. A relationship's cardinality can be ONE-TO-ONE, or ONE-TO-MANY.

Data and Data Types


Columns

Each table must define columns. The following column datatypes are supported:

Helper columns

When a LONGBINARY column is created, three additional read-only columns are created to store the meta-data describing the binary data:

You may query these columns, but they are designed to exist as helpers; they cannot be seen or altered using the NextDB.net graphical admin tools. Deleting the LONGBINARY column will automatically remove these read-only columns as well.

Permalinks

Many web applications need to make files accessible, in the form of hyperlinks on an HTML page. These links work best if they are permanent and never “go stale”. A permanent link can be emailed to friends and can even be pasted into blog entries. NextDB supports “permalinks” (permanent links) for LONGBINARY columns.

The NextDB permalink format is: http://nextdb.net/files/<account>/<database>/<table>/<column>/<permanent-ID>

The value of <permanent-ID> will be equal to the value stored in the read-only <COLUMN_NAME>_FID column described in the previous section. For example, the following could be a permalink to a picture of a T-SHIRT in the IMAGE column of a TSHIRTS table, in a database named “catalog” in a account named “kungfu.net”. http://nextdb.net/files/kungfu.net/catalog/TSHIRTS/IMAGE/364927364548583

Dates

Dates in NextDB are composite date-time datatypes, represented in the following format: YYYY-MM-DD hh:mm:ss.mmm TIMEZONE
An example of a Date that you might insert, or update, is 2008-01-15 09:05:34.094 PST. The meaning of this date is “January 15, 2008 at 9:34 and 34 seconds and 94 milliseconds, Pacific Standard Time. When inserting a Date, be sure to provide the correct local time, as wellas the local timezone. The server will convert the local time to Greenwich Mean Time (GMT) according to Universal Coordinated Time (UTC) standard. When a query returns a Date, be sure to account for the fact that the Date is stored as GMT with a zero hour offset (also known as Zulu Time). The JavaScript application should convert the Date to the local timezone for display to the user.

The easiest way to understand this is to imagine a single clock hanging on the wall in the Royal Observatory in Greenwich England. Whenever a Date is inserted into the Database, NextDB simply records the time as it would be observed in the Royal Observatory at that exact moment. If you are in California, (GMT -7:00) and you insert a Date having a time component of 13:00:00.000 PST, the server will record the time component as 20:00:00:000 GMT.

Primary Key

One of the cornerstones of the relational database model is the concept of identity. “Identity” is a familiar concept to everyone who owns a credit card (the credit card number is the card's “identity”). Another form of identiy is a Social Security Number (SSN) that identifies you as a United States Citizen. “Identity” is simply a value that is used to keep track of data. Rows in a database table typically have an identity defined by the row's primary key (PK).In NextDB, each table automatically creates a read-only, autoincrementing, primary key column named 'PK'. The term 'Autoincrementing' means that whenever a row is inserted, NextDB will automatically pick a new unique value for the PK. You should not attempt to create your own primary key columns, since NextDB maintains the PK column automatically. When NextDB returns a primary key to the browser it is combined with a security policy, digitally signed, and encrypted into an opaque data structure called a Secure Unique Result-set Identifier (SURID). The SURID is a web-safe alternative to the primar key. If you are familiar with traditional relational databases, you are also familiar with Foreign Keys. NextDB internally creates and manages foreign keys automatically as part of a relationship. You will never have to deal with Foreign Keys in NextDB since they are completely managed internally.

Inserting Data From a Web Page 

<html><head>
<script src="http://www.nextdb.net/api.js"></script>
	<script>
      		var conn = new net.nextdb.Connection("myaccount","mydatabase"); 
      		var insert = new net.nextdb.Insert("USER"); //insert into USER Table
      		insert.setParameters({first_name:"Bob", last_name:"Jones", email:"bob@nextdb.net"});
      		conn.executeInsert(insert,
      			function(pk,error){ //anonymous callback function
     				if(error){
      					net.nextdb.Util.print(error.message);
      				} else {
      					alert("PK:"+pk);
      				}
      			}
      		); 
	</script>
</head></html>

CAPTCHA Inserts

In the example above we showed how easy it is to insert data into a table. Now we will describe how to protect your table to insure that malicious scripts to not flood your table with data. Have you ever created an account on a website and you had to enter text from an image? This is called CAPTCHA and it is designed to prevent hacker/spammer programs from inserting data. NextDB has automatic CAPTCHA protection built-in. If one of the requirements of your application is to provide account-creation web pages, NextDB's built-in CAPTCHA support is for you. The following is an example of a NextDB CAPTCHA image.

For any table you create in NextDB you can set the table to perform CAPTCHA. When you insert a row into the table, using the NextDB JavaScript API, you must collect the CAPTCHA text from your application's user, and submit the CAPTCHA text with the data you are inserting. If the CAPTCHA text entered by the user is valid, NextDB will allow the row to be inserted. Use CAPTCHA to protect tables in your data model that represent user accounts. Use this URL to generate CAPTCHA images like the one shown above:

http://www.nextdb.net/nextdb/captcha?width=200&height=100


Each time the CAPTCHA URL is accessed, a new CAPTCHA image is generated. Your application should present the image to the user, using an HTML <img> element, and provide a form input element where the user enters the text that he reads from the image. Listing 2, line 7 shows how to provide the user-entered CAPTCHA text along with the data to insert in the table. Notice that the CAPTCHA text is collected from a form input element that the user fills in. If the CAPTCHA text does not match the text in the image, NextDB will throw a SecurityException error that your program can handle. Line 12 shows how to handle the SecurityException error by presenting a message to the user of the application.

<html><head>
<script src="http://www.nextdb.net/api.js"></script>
	<script>
		var conn = new net.nextdb.Connection("myaccount","mydatabase"); 
		var insert = new net.nextdb.Insert("USER"); //insert into USER Table
		var userValues = {first_name:"Bob", last_name:"Jones", email:"bob@nextdb.net"};
		userValues["captcha"] = document.getElementById("captchaInput").value;
		insert.setParameters(userValues);
		conn.executeInsert(insert,
			function(pk,error){ //anonymous callback function
				if(error){
					(110 == error.code){
						Alert("The image validation text is incorrect. Please try again.");
					}else
						net.nextdb.Util.print(error.message);
					}
				} else {
					alert("PK:"+pk);
				}
			}
		); 
	</script>
</head></html>

Queries

When you look at most modern websites, virtually everything displayed to you is the result of a database query, whether it be blog posts or items for sale on craigslist. A database search is called a “query”, which is just a fancy name for “question”. A database query asks the database a question in order to find information, or related pieces of information, that satisfy the search criteria specified by the query. NextDB allows you to create queries using web based administration tools. Your queries are stored on the server, and can be called from your JavaScript applications. The queries are expressed in the NextQuery language. NextQuery has many advantages over SQL when it comes to security, simplicity, and database performance in an AJAX environment. The following sections explore the use of NextQuery for database searching.

Querying a Single Table

The simplest possible query consists of a name, and a candidate row. The following query, named “allBloggers” would find all rows in the BLOGGER table. Remember, this is not JavaScript. The queries are written in the NextQuery language, and are stored on the server. 


/*************************************************
*** This is a NextQuery program that selects 
*** all rows from the BLOGGER table.
*************************************************/
NAME = allBloggers;
ROW blogger FROM BLOGGER;

The first thing you notice about NextQuery program above is that it looks more structured than SQL. In fact, NextQuery looks a lot like a traditional program. A query's name, declared on line5, is simply an identifier that uniquely identifies a query. The ROW keyword, declared on line 6, defines a candidate row from a particular table. In the example above, the candidate row on line 6 is named 'blogger' and resides in the BLOGGER table. A candidate does not represent a single row. Rather, a candidate represents all rows that meet the search criteria of the query. In this case, no WHERE clause exists to refine the candidate, and therefore the candidate 'blogger' refers to all rows in the BLOGGER table. This is analogous to “SELECT * FROM BLOGGER AS blogger” in SQL. Later, we will see examples of how candidates are referenced from other lines of your query, in much the same way as variables can be referenced in other programming languages. After you have setup a query using the NextDB.net administrative web pages, you can easily create an HTML page that uses the NextDB JavaScript API to execute the query and display the results.

<html><head>
<script src="http://www.nextdb.net/api.js"></script>
	<script>
var conn = new net.nextdb.Connection("myaccount","blog"); 
var bloggerQuery = new net.nextdb.Query("allBloggers");
conn.executeQuery(bloggerQuery, 
	function(queryResults, error){
		if(error){
			alert("error "+ error.message);
		}else{
			var reflector = new net.nextdb.html.Reflector();
			reflector.reflect(queryResults).show(); //display the queryResults Array
		}
	}//end callback function
); //end executeQuery	
	</script>
</head></html>

WARNING: one of the most common mistakes in asynchronous JavaScript programming is to assume that the callback function is invoked immediately (synchronously). Callback functions are invoked ASYNCHRONOUSLY, which means they are called whenever the server returns with the data. You should never write code like the following, since it assumes the queryResults are available immediately. Instead of printing the query results, this code WILL FAIL on line 13 since 'result' has not been set when show is called.

<html><head>
<script src="http://www.nextdb.net/api.js"></script>
	<script>
/*********THIS PROGRAM DEMONSTRATES A COMMON BUG!!!!!***************/
nextdb.Connection("myaccount","blog"); 
var bloggerQuery = new net.nextdb.Query("allBloggers");
var result = null;
conn.executeQuery(bloggerQuery, 
	function(queryResults, error){
		if(error){
			alert("error "+ error.message);
		}else{
			result = queryResults; //BUG: faulty assignment to variable outside of callback scope
		}
	}//end function
); //end executeQuery
var reflector = new net.nextdb.html.Reflector();
reflector.reflect(result).show(); //BUG: result var is STILL NULL BECAUSE QUERY CALLBACK HAS NOT YET BEEN INVOKED	
	</script>
</head></html>

Parameterizing a Query

The 'WHERE' clause is used to specify a boolean expression that refines the query results to only those rows that would cause the booolean expression to evaluate to true. Here is a query that limits the candidate row from BLOGGER to candidates with 'bloggerName' and 'password' columns that match the runtime parameters. The WHERE clause on line 7 of this query program defines two runtime parameters, 'name' and 'pwd', that will be supplied at runtime by the JavaScript application. Query parameters are referred to using ${parameter} within the boolean WHERE expression.

/*******************************************************
*** This is a NextQuery program used to login a blogger, 
*** by checking the bloggerName and password.
********************************************************/
NAME=login; 
ROW blogger FROM BLOGGER;
WHERE(TEXT name, TEXT pwd){ //declare two parameters that must be passed in by JavaScript program
	blogger.bloggerName = ${name} AND //column 'bloggerName' must equal name parameter
	blogger.password = ${pwd} //column 'password' must equal pwd parameter
}

The folllowing JavaScript code shows how to execute a parameterized query from a web page.

<html><head>
<script src="http://www.nextdb.net/api.js"></script>
	<script>	 
		var conn = new net.nextdb.Connection("myaccount","blog"); 
		var bloggerQuery = new net.nextdb.Query("login");
		bloggerQuery.setParameters({name:"bob", pwd:"34ndjjre"});
		conn.executeQuery(bloggerQuery, 
			function(queryResults, error){
				if(error){
					alert("error "+ error.message);
				}else{
					var reflector = new net.nextdb.html.Reflector();
					reflector.reflect(queryResults).show();
				}
			}//end callback function
		); //end executeQuery	
	</script>
</head></html>

 

The WHERE clause

The WHERE clause of a NextQuery program must contain a valid boolean expression. All NextQuery keywords are case sensitive. The following operators are allowed:

The following operands are allowed:

The LIKE Operator And Wildcard TEXT Searching

The LIKE operator can be used in a NextQuery WHERE clause to perform comparisons of TEXT datatypes. Unlike the “=” operator, which always requires an exact match to evaluate to true, the LIKE operator can be used to perform more flexible comparisons. Two special wildcard characters exist for the purpose of flexible TEXT comparisons. The '%' character will match zero or more occurrences of any character. The '_' character will match exactly one occurrence of a single character. Suppose you want to find all bloggers whose blogger name ends with 'bones' or 'jones'. The expression below would match 'jones' , 'Bones', 'TomJones' and 'Cajones', but will not match 'ones'.

/*******************************************************
*** This is a NextQuery program used find rows from the
*** BLOGGER table whose bloggerName column ends in
*** "Bones", "Jones" etc.
********************************************************/
NAME=likeQuery01; 
ROW blogger FROM BLOGGER;
WHERE(){
	blogger.bloggerName LIKE '%_ones'
}

The following example shows how to use a dynamic query parameter to search for bloggers with a bloggerName that contains the ${name} query parameter. The double-pipe string concatenation operator is used to concatenate the wildcard '%' character at the beginning and end of the dynamically supplied ${name} parameter:

/*******************************************************
*** This is a NextQuery program used to find any row
*** in the BLOGGER table whose bloggerName column 
*** contains the parameter passed into the query
********************************************************/
NAME=likeQuery02; 
ROW blogger FROM BLOGGER;
WHERE(TEXT name){
	 blogger.bloggerName LIKE '%' || ${name} || '%
}

In the example below, the ESCAPE qualifier declares an escape character, in this case '~', that can be used to convert wildcard characters to literally interpreted characters. This can allow you to perform searches for the actual percent and underscore characters rather than treating '%' and '_' as wildcards. The following query would retrieve any bloggerName containing the percent symbol.

/*******************************************************
*** This is a NextQuery program used to find all rows
*** from the BLOGGER table whose bloggerName column
*** contains the percent symbol.
********************************************************/
NAME=likeQuery03; 
ROW blogger FROM BLOGGER;
WHERE(){
	blogger.bloggerName LIKE '%~%%' ESCAPE '~'
}

When the ESCAPE qualifier is used, NextDB will automatically scan query parameters, like ${name}, for the presence of '%' and '_' and will escape them using the declared escape character. This will prevent the '%' and '_' characters from being treated as wildcards when they are entered by a user as query parameters.

DATE comparisons

DATE values can be compared using equality and greater/lesser comparisons. The following NextQuery program shows how to find all blog posts more recent than a particular data that is passed into the query as a parameter. 

/*******************************************************
*** This is a NextQuery program used to find all 
*** the blogger's posts that are more recent than 
*** a certain date.
********************************************************/
NAME = postsByDate;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name, DATE date){
	blogger.bloggerName = ${name} 
	AND 
	post.date >= ${date} 
		
}

Semantic vs. Syntactic Exceptions

When you compile your query using the NextDB.net administrative tools, you will from time to time enter a typo, or make a logical mistake in your query. When you enter a typo, the tool will generate a SyntacticException. A SyntacticException indicates that the syntax of your query is wrong. For example, if you have mismatched parenthses. A different type of exception occurs when you have made a logical error in your program. For example, imagine you tried to use the LIKE operator to compare a column whose type is DATE to a column whose type is TEXT. SInce the LIKE operator requires both operands to be TEXT, this logical error would generate a SemanticException with an appropriate error message.

Finding Related Rows

The blogs application needs to retrieve all the posts made by any particular blogger. Recall from the discussion of relationships, that a ONE-TO-MANY relationship must be defined in order for the database to record the relationship between a particular blogger and his posts. The following NextQuery program shows how to retrieve related rows. 

/*******************************************************
*** This is a NextQuery program used to find all rows
*** from that are related to rows in the BLOG_POSTS table.
********************************************************/
NAME=posts;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name){
	blogger.bloggerName = ${name}
}

If you are used to performing joins in SQL, you may be surprised at how simple joins are in NextQuery. The 'RELATED' keyword is used to indicate that the candidate named 'blogger' is related to the candidate named 'post'. The VIA keyword identifies the name of the relationship through which the candidates are related. In this case, the VIA keyword identifies the relationship named 'bloggerPosts', which is a ONE-TO-MANY relationship between BLOGGER and BLOGGER_POSTS. The relationship named 'bloggerPosts' and its cardinality are declared outside the scope of the query using the NextDB administrative tools. Notice that at no place in the WHERE clause is there any use of foreign keys, primary keys (PKs), or other SQL-like join constructs. This makes it significantly easier to create queries in NextQuery than in SQL. There is no limit to how many relationships can be traversed (joined) by a single query. For example consider this social networking example in which a relationship named “friendListOwner” relates a user to his friend list. A second relationship named “friendListMember” associates users with membership in a friendlist.

/*******************************************************
*** This is a NextQuery program that uses an association
*** table named "FRIEND_LIST" to find all the members
*** of a particular user's friend list.
********************************************************/
NAME=friendOfMyFriend;
ROW me FROM USERS;
ROW myFriendList FROM FRIEND_LISTS;
ROW myFriend FROM USERS;
me RELATED myFriendList VIA friendListOwner;
myFriend RELATED myFriendList VIA friendListMember;
WHERE(TEXT name){
	me.username = ${name}
}

Accessing Query Results From JavaScript

Understanding the Query Results Array

JavaScript Object Notation (JSON) can be used to represent the structure of JavaScript Objects. In order to understand the parts of this document where we discuss the results of queries, it will be helpful to review the following JSON examples. In the JSON shown in the example below, there is an Array containing two empty Objects. Line 1 and line 4 are the opening and closing square brackets representing the array. All the examples show an Array as the outermost structure, because NextDB always returns an array of Objects, even if there is only one Object in the Array. Line 2 and line 3 are empty Objects:

[
	{},
	{}
]

In the following example there is a JSON Array containing an Object that has a field named 'phoneNumber' with a value of “555-1212”. On line 2 you can see the colon that is used to associate a field with its value.

[
	{phoneNumber:"555-1212"}
]

In the followign exampe there is a JSON Array containing an Object with a field named 'contactInfo'. The field value is itself an Object containing primitive fields named 'phoneNumber' and 'address'.

[{contactInfo:{
		phonNumber:"555-1212", 
		address:"San Francisco, CA"
	}
}]

We can use JSON to show an example of the results Array returned from the postsByDate query

[{
	post:{ 
		PK:"aZdm9i843xoR6z7c0DaWXSTHLwkO6z%2B5o7eF4sKJ5mVeuVrOHuv%2BaTDeYldTPHcbhWHR9s%2FW%2FTeN%0A1uH9uBlhtxMm92azGSbq5rGefYcS6Gk%3D",
		title:"vacation – day 1", 
		content:"lorem ipsum ...", 
		date:"10-13-2007 16:43:54:323 GMT" 
	},
	blogger:{
		PK:"Xz5sayBM6xd1VaLe%2FHOghOyrjUQPsVhYXhaz7f5EN%2BhoQvHEU7FrXIqpnSTh%2BndYjMA3bILNWSiG%0AIeWOxjSTVgE2ItmjQ4VWN0Vvj%2BoXelA%3D",
		bloggerName:"swampy", 
		email:"swampy53232@yahoo.com",
		password:"esk32das",
		picture:"http://nextdb.net/files/superblog/blog/BLOGGER/PICTURE/324123583"
	}
}]

Primary Keys and Security

Each database table automatically maintains a Primary Key column named PK. A Primary Key is a special number that uniquely identifies a row. As you will see in a later section, you can use these unique identifiers to update or delete rows from the database. As we all know, identity theft is a problem that plagues any system based on unique identifiers. If your Social Security Number or credit card number is stolen it can be impossible to “put the genie back in the bottle”. Similarly, if a Primary Key is accessible to an application, or malicious user, it can be saved and used for malicious purposes, like changing the price of a product, or altering data that should be secure. NextDB uses a special security mechanism to insure that the real Primary Key never leaves the database.

In the query result JSON structure, shown in the JSON above, you can see the field named 'PK' is not a simple number. Whenever a PK field is returned to the client, the PK is not the actual PK from the database. Instead, a Secure Unique Result-set Identifier (SURID) is returned.The SURID wraps the real primary key with strong encryption and tamper protection, combined with a security policy defined by your query (explained later). You should return the PK field when you intend to subsequently update or delete the row.

Accessing the Query Results Array From JavaScript

The JavaScript program fragment shown below in listing X, demonstrates how to access the query results Array that would be returned from executing the postsByDate query. In general, a query can return multiple results, therefore the queryResults object is as array. By default, each of the ROW candidates defined in the NextQuery is returned as an Object nested in the Array. These Objects can be accessed in a natural way from your JavaScript query callback function. For example, line 12 of the JavaScript program below demonstrates how to access the 'blogger' Object, and line 13 shows how to access the 'post' Object.

<html><head>
<script src="http://www.nextdb.net/api.js"></script>
	<script>	 
		var conn = new net.nextdb.Connection("myaccount","blog"); 
		var postsByDate = new net.nextdb.Query("postsByDate");
		bloggerQuery.setParameters({name:"swampy", date:"2007-01-15 00:00:00.000 PST"});
		conn.executeQuery(postsByDate, 
			function(queryResults, error){
				if(error){
					alert("error "+ error.message);
				}else{
					alert("blogged by: "+ queryResults[0].blogger.bloggerName);
					alert("blog post is titled: "+ queryResults[0].post.title); 
				}
			}//end callback function declaration
		); //end executeQuery	
	</script>
</head></html>

Query Result Paging

There can be thousands of rows in your database that match a query's criteria. For example, you might want to find all the shoes in your online shoe store. Naturally, you would not want to transfer all the information to the browser at once. The typical way to handle large result sets it to provide a “paging” metaphor on the client. For example, you might format the results in an HTML table and provide “next” and “previous” buttons to allow the user to move forward and backward through the query results. NextDB supports query paging for every query that you define, automatically. To control the number of results per query page, use the Query.setPageSize method. It is possible to manually control the position of the “page” using the Query.setStartAfter and Query.setStartAfterValue methods. To move forward to the next page of query results call Query.setStartAfter with the value of the last PK on the current page, then re-execute the query. If your query uses ORDER BY, you must also call Query.setStartAfterValue with the value of the ordered field from the last row of the current page. For example, if you order by a column called NAME, you should call setStartAfterValue(rows[rows.length-1].NAME);

Shaping Query Results

In looking at the JSON structure returned by the postByDate query, you may have observed that the blogger object is not needed in order to fulfill the objective of the query, which was simply to retrieve all the blog post made by “swampy” more recently than a certain date. If more than one post met the criteria, Swampy's blogger information would be redundantly repeated in each element of the result array. Continuing with the example of retrieving blog posts. We will refine the query so that it only returns the rows from the BLOGGER_POSTS table. This is accomplished on line 11 by using the RETRIEVE keyword. When the RETRIEVE keyword is used, only ROW candidates referenced in the RETRIEVE clause will appear in the results Array.

/*******************************************************
***Use RETRIEVE clause to return only the post candidate
********************************************************/
NAME=posts;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name){
	blogger.bloggerName = ${name}
}
RETRIEVE post;

By retrieving only the 'post' candidates, we reshape the results Array to retrieve a more focused JSON response that contains only the post candidate.

[
	{
		post:{
			PK:":"Xz5sayBM6xd1VaLe%2FHOghOyrjUQPsVhYXhaz7f5EN%2BhoQvHEU7FrXIqpnSTh%2BndYjMA3bILNWSiG%0AIeWOxjSTVgE2ItmjQ4VWN0Vvj%2BoXelA%3D"",
			title:"vacation – day 1", 
			content:"lorem ipsum ...", 
			date:"10-13-2007 16:43:54:323 GMT" 
		}
	}
]

Continuing with the example of retrieving blog posts, let's change the response to return only the title of the post. On line 11, simply alter the RETRIEVE specification to fetch only the title column of the post candidate.

/*******************************************************
***Use RETRIEVE clause to return only the post title
********************************************************/
NAME=posts;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name){
	blogger.bloggerName = ${name}
}
RETRIEVE post.title;

The following JSON is returned:

[
	{post:{title:“vacation – day 1”}}
]

The title can be accessed from a JavaScript program as result[0].post.title. We can further reshape the JSON response to make it even easier to access the title column, by using the 'into' keyword. In the following NextQuery program in which we alter the RETREIVE clause to return only the primitive column value into a field named 'caption':

/*******************************************************
*** Use RETRIEVE clause to return only the column named
*** 'title'. Use the 'into' keyword to return the title
*** column as a primitive field named 'caption'
********************************************************/
NAME=posts;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name){
	blogger.bloggerName = ${name}
}
RETRIEVE post.title into caption;

The following JSON is returned in which the title column can now be accessed as result[0].caption

[
	{caption:“vacation – day 1”}
]

Mixing Objects Together On The Fly

The 'into' keyword can be used to mix together different objects and fields, creating completely new object structures. On line 8 of listing 13, the entire 'post' ROW candidate is mixed together with the 'bloggerName' column of the 'blogger' candidate to create an entirely new Object structure named 'thePost'.

/*******************************************************
*** Use RETRIEVE clause to mix together fields of the
*** post and the blogger candidates into a new hybrid
*** Object placed in a field nmed 'thePost'
********************************************************/
NAME=posts;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name){
	blogger.bloggerName = ${name}
}
RETRIEVE post into thePost, blogger.bloggerName into thePost.author;

The following listing shows the JSON representation of a query results Array containing a single instance of the newly defined Object, 'thePost'.

[
	{
		thePost:{
			PK:":"Xz5sayBM6xd1VaLe%2FHOghOyrjUQPsVhYXhaz7f5EN%2BhoQvHEU7FrXIqpnSTh%2BndYjMA3bILNWSiG%0AIeWOxjSTVgE2ItmjQ4VWN0Vvj%2BoXelA%3D"",
			title:"vacation – day 1", 
			content:"lorem ipsum ...", 
			date:"10-13-2007 16:43:54:323 GMT",
			author:"swampy" 
		}
	}
]

Aggregates And Ordering

Aggregates

An aggregate is a value that, as the name implies, aggregates data from across many columns. For example, the average value of an integer-valued column is computed by adding together (aggregating) all the columns, then dividing by the number of columns. The RETRIEVE keyword can be used to retrieve aggregate information such as COUNT, AVG, MIN, MAX and SUM. Aggregates cannot be mixed with columns.

/*******************************************************
*** RETRIEVE COUNT into a primitive field, 'count'
********************************************************/
NAME=posts;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name){
	blogger.bloggerName = ${name}
}
RETRIEVE COUNT(post) into count;

The following JSON listing shows the primitive field, 'count' which will contain the count of the number of posts made by a given blogger.

[
	{count:24}
]

Ordering

NextQuery supports an “ORDER BY” expression with an optional ASCENDING or DESCENDING qualfier. The following NextQuery program orders the results by the title column of the post candidate.

/*******************************************************
*** Order posts by date
********************************************************/
NAME=posts;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name){
	blogger.bloggerName = ${name}
}
RETRIEVE post;
ORDER BY post.date DESCENDING; //display most recent post first

Updating and Deleting Rows With a SURID

The update and delete methods of the NextDB JavaScript Connection Object require the program to provide the rowId in order to udate or delete a row. Think of rowId, primary key, PK, and SURID as all being synonyms when it comes to using the API. In order to get the SURID for a row, your query needs to return the PK column. If your query returns the PK column, NextDB will wrap the PK field in a SURID, automatically. Included in the encrypted SURID data structure is a data write policy defined by your query. Line 12 of the following NextQuery program illustrates how to retrieve rows with the SURID write policy set to allow both updating and deleting of the retrieved rows. 

/*******************************************************
*** Make results updatable and deletable
********************************************************/
NAME=posts;
ROW blogger FROM BLOGGER;
ROW post FROM BLOG_POSTS;
blogger RELATED post VIA bloggerPosts;
WHERE(TEXT name, TEXT pwd){
	blogger.bloggerName = ${name} AND blogger.pwd = ${pwd}
}
RETRIEVE post;
FOR UPDATE, DELETE;

Why does the SURID include the write policy defined on Line 12 above? Consider a product catalog with prices for items in the catalog. You will want to create a query to display these prices in the browser. JavaScript is not a secure programming environment so we must assume that users of our applications include hackers. A hacker could use freely available tools to alter the JavaScript running in the browser. The altered code could try to update a row of the product catalog to change prices, delete items, or perform other unauthorized changes to data. In order to prevent these malicious “attacks” on your database tables, you must explicitly give permission for rows returned from a query to be updatable or deletable. You can accomplish this using the FOR UPDATE/DELETE keywords in NextQuery. If you don't use FOR UPDATE/DELETE in your query, the returned rows will be read-only, and therefore read-only is the default.

If your application allows a row(s) to be updated or deleted, you must ensure that the query only returns data “owned” by the end-user of the application. The safest way to do this is to write queries, like the one above, that are parameterized by a password or other token known only to the end user. With this strategy, a hacker cannot alter or delete the data of other users nor can he alter or delete application data such as product catalogs. As a further security mechanism, it is not possible to define or alter query definitions from the JavaScript API. This prevents malicious JavaScript from altering a query or creating ad-hoc queries. Queries are precompiled and stored on the server to prevent tampering. Queries can only be created or altered through the NextDB administrative interfaces which require an administrative login and password. The actual data exchanged between the browser and the server, when a query is executed, is encrypted.

Updating Rows From a Web Page

The following HMTL and JavaScript listing shows how to update the columns of a row. To “update” the columns means to change the column values in the database.

<html><head>
<script src="http://www.nextdb.net/api.js"></script>
	<script>	 
		var conn = new net.nextdb.Connection("examples","example1");
		var key; 
		var query = new net.nextdb.Query("QUERY1"); 
		conn.executeQuery(query, 
			function(rows,error){ 
				if(error){ 
					alert(error.message); 
				} else { 
					key=rows[0].TABLE1.PK; //get the row's identity (SURID) 
					var update = new net.nextdb.Update("TABLE1"); 
					update.setParameters({first_name:"Bob", last_name:"Jones"}); //here are the new columns/values
					update.setRowId(key);  //tell the Update Object the identity of the row to update
					conn.executeUpdate(update, 
						function(key,error){ 
							if(error){ 
								alert(error.message); 
							} else { 
								alert("ok"); 
							} 
						}
					); 
				} 
			}
		); 
	</script>
</head></html>

 

Lazy loading Rows With a SURID

The term "Lazy loading" means to load related rows at the time they are needed, as opposed to preemptively. Lazy loading can improve the performance of your application because it avoids complex joins and retrieval of data that might not be viewed. Earlier, we described a query named "posts" that retrieved all the posts belonging to a particular user. Recall that we emphasize that this query should join the BLOGGER table to the BLOGGER_POSTS table and should include the blogger's password as a parameter, which keeps the system stateless and insures that every query is secured. This technique works well if you only have two tables to join, but as you traverse multiple table relationships "away" from the table used to authenticate the end-user, you may find the complexity of your query growing.

We now present a technique using lazy loading and SURIDs which insures that your queries remain simple and secure, while allowing your user to traverse multiple related tables. The NextQuery expression below contains a new login query, named "suridLogin". Notice that this query retrieves the primary key, which NextDB wraps in a SURID, automatically. 

/*******************************************************
*** This is a NextQuery program used to login a blogger, 
*** by checking the bloggerName and password. NOTE THAT WE RETURN A SURID
********************************************************/
NAME=suridLogin; 
ROW blogger FROM BLOGGER;
WHERE(TEXT name, TEXT pwd){ //declare two parameters that must be passed in by JavaScript program
	blogger.bloggerName = ${name} AND //column 'bloggerName' must equal name parameter
	blogger.password = ${pwd} //column 'password' must equal pwd parameter
}

RETRIEVE blogger.PK into surid; //when you retrieve a PK, the server automatically returns a SURID instead of the raw primary key

Once a user has logged in using the suridLogin query (or a similar query), your JavaScript program can access the surid variable from the result set, for example rows[0].surid. Using that SURID the application can subsequently find pieces of information related to the user, through queries that accept the SURID as a parameter. Now we will show how to securely define a query that accepts a SURID as a parameter. 

/*******************************************************
*** This is a NextQuery program that uses the SURID from the suridLogin

*** query to retrieve all blog posts belonging to the owner of the SURID.
********************************************************/
NAME=postsBySURID; 
ROW blogger FROM BLOGGER;

ROW post FORM POSTS;

blogger RELATED post VIA bloggerPosts;
WHERE(SURID<suridLogin> token){ //the only parameter is the SURID from the suridLogin query
	blogger.PK = ${token}
}
RETRIEVE post;
FOR UPDATE, DELETE;

The query above introduces a new syntax for defining parameters to a query, when the parameter is a SURID. The syntax for defining a SURID parameter consists of 'SURID' followed by a pair of angle brackets. Inside the angle brackets, you can place a comma-separated list of query names. This list of query names is a security mechanism that insures that the SURID passed into the query originated from the query or queries that you specify. In the example above, the list consists only of 'suridLogin'. For the example above, this makes sense because we want to insure that only a logged-in user can access the postsBySURID query. There are cases when the SURID that you provide as a parameter was generated by inserting a row. To accept such a SURID, '+' may be used instead of a query name, for example: "WHERE(SURID<+> pk){.... "

 

Managing Relationships

Insert Row And Relate To Existing Row

Very often, when you create a new row, you will need to relate the new row to an existing row. For example, when you add a row to a table named BLOG_POSTS, you most likely need to relate the new blog post to a blogger in the BLOGGER table. The following JavaScript shows how can combine the insert operation with the creation of the relationship, into a single step.

<html><head>
<script src="http://www.nextdb.net/api.js"></script>
	<script>	 
		var conn = new net.nextdb.Connection("examples","blog");
		var query = new net.nextdb.Query("login"); 
		query.setParameters({bloggerName:"swampy", password:"b3493es"});
		conn.executeQuery(query, 
			function(rows,error){ 
				if(error){ 
					alert(error.message); 
				} else { 
					var key=rows[0].blogger.PK; //get the row's identity (primary key surrogate) 
					var Insert = new net.nextdb.Insert("BLOG_POSTS"); 
					update.setParameters({title:"my vacation", date:"now", content:"I had a great trip ..."}); //the new row to insert
					update.setRelationship(key, "bloggerPosts");  //tell the Update Object the identity of the row to update
					conn.executeInsert(insert, 
						function(key,error){ 
							if(error){ 
								alert(error.message); 
							} else { 
								alert("ok"); 
							} 
						}
					); 
				} 
			}
		); 
	</script>
</head></html>

Relate Existing Rows

This section incomplete. See JS API documentation for Connection.createRelationship.

Remove Relationship

This section incomplete. See JS API documentation for Connection.removeRelationship.


Error Handling

Every callback function invoked by methods of Connection has a parameter called 'error' of type Error. The Error Object has two fields that will always be present:
1.code – a numeric error code representing a classification of error type.
2.message – a context-specific error message

An additional field, named "causedBy" will be populated when the error applies to a specific column, parameter, or relationship. The causedBy column is usefulin situations like form validation, where the application needs to know, for example, that there was a problem with a particular field.

Error Codes
code Message Additional Fields Type Context
100 Could not access database '<DATABASE>' from account '<ACCOUNT>'. NONE SecurityException Query, Insert, Update, Delete
102 Account is not active NONE SecurityException Query, Insert, Update, Delete
105 Query named '<NAME>' does not exist in database '<DATABASE>'. NONE SecurityException Query
110 incorrect security CAPTCHA word NONE SecurityException Insert
115 Blocked HTTP Referer '<REFERER>' causedBy SecurityException Query, Insert, Update, Delete
120 The row could not be inserted without violating data integrity or constraints. <Context-specific message>

 causedBy IntegrityConstraintViolationException Insert
130 <Context-specific message>
 causedBy
DataFormatException
Insert, Update, Query
210 0 rows deleted
 NONE
Delete		 Delete
220 Row is not deletable.
 NONE
SecurityException
Delete
310
Query named '<query-name>' uses ORDER BY but query has been executed without setting mandatory startAfterValue.
 NONE MissingQueryParameterException
 Query
320
missing parameter '<paramName>'

 causedBy MissingQueryParameterException Query
410 Could not relate rows without violating one-to-one cardinality on relationship '<relationship-name>'.
causedBy IntegrityConstraintViolationException
 Relate Rows
510 Row is not updatable.
 NONE SecurityException
 Update
530 Update failed.
NONE ConcurrentModificationException
 Update

 

©2008 NextDB LLC. Patents pending