NextDB.net Developer's Guide V. 0.1


Preface

NextDB is a hosted database. 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. We designed NextDB to be simple enough that even if you have never used a database before, you can easily use NextDB.

Intro: the "hello world" of nextdb.net

The quickstart tour will demonstrate how to create a blog application. It should take 5 minutes to complete this tutorial.
Click here to watch a screencast of this intro.

Import the blog datamodel into your database

  1. Log into your account on nextdb.net
  2. create a new database called blogs
  3. import the blogs data model
    • From the dropdown list on the databases tab, select “blog module”
    • Press the import button below the dropdown
    • You can verify that the datamodel has been imported by selecting the 'tables' tab on the main navigation menu. You will see a table named BLOGGER and table named BLOG_POSTS.

Connect the AJAX blog application to the database

  1. Right click on the following link and save the html file to your local hard drive. http://nextdb.net/example-blog.html
  2. Open example-blog.html in a text editor and replace the connection's accountName and database information with your own. Be careful not to delete the double quotes around the "accountName" and "databaseName" when you enter your actual accountName and databaseName.
    var conn = new net.nextdb.Connection("accountName","databaseName");
  3. Save example-blog.html on you local hard drive

Test Drive the blogs application

  1. Open your local blog.html file in a web browser. The application will begin by inserting a blogger, and ten associated blog posts.
  2. Use the search field to search for blog entries by title.

Congratulations, you have successfully connected an AJAX application to your hosted database.

System Overview

 

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

If you ran the Quickstart you imported 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:

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>

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 a query returns a Date, the timezone of the date is set to Greenwich Mean Time (GMT). The JavaScript returns Dates as Localized JavaScript Date Objects. The JavaScript API also uses http://www.datejs.com/ which is a great date parsing library for JavaScript.

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 for 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. 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

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:8080/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){
					if(1 == 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 line1, is simply an identifier that uniquely identifies a query. The ROW keyword, declared on line 2, defines a candidate row from a particular table. In the example above, the candidate row on line 2 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 14 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 3 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=login; 
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=login; 
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=login; 
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:2352762368,
		title:"vacation – day 1", 
		content:"lorem ipsum ...", 
		date:"10-13-2007 16:43:54:323 GMT" 
	},
	blogger:{
		PK:84736293521,
		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'. Whenever a PK field is returned to the client, the PK is not the actual PK from the database. Instead, the PK that is returned is a surrogate primary key. Surrogate keys are secure proxies for the actual primary keys in the database. The surrogate primary key will uniquely identify a particular row only for the scope of the client's session with the server. Because the surrogate keys are very long random numbers, the value of a surrogate PK cannot be incremented or decremented to guess the values of other primary keys in the database and the surrogate key divulges no information about the size of the table or the age of a particular row in the table. The surrogate PK cannot be saved since it will expire as soon as the user's session expires.

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. A discussion of manual page positioning can be found here. (FIXME)

(FIXME)However, for most web based user interfaces you can simply wrap the Query Object in a QueryPageController, and wire the QueryPageController to “next” and “previous” buttons or links on your webpage. For rapid prototyping you can use the Table Object to display query results. The Table Object manages result set paging automatically by providing “next” and “previous” buttons for the user to click on.

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:2352762368,
			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:2352762368,
			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 ciomputed by adding together (aggregating) all the columns, then deviding 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. The folowwing NextQuery program orders the results by the title column of the post candidate.

-- Order posts by 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;
ORDER BY post.title;

Updating and Deleting Rows

Surrogate primary keys are returned to the client in order to facilitate, among other things, updating and deleting rows. Surrogate keys are long random numbers that are valid only for the scope of the session. On the server, the surrogate keys are associated with the set of operational permissions for the row. These operational permissions prevent rows from being deleted or update without your explicit permission. Line 12 of the following NextQuery program illustrates how to retrieve rows with the operational permissions 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 are these operational permissions tracked by the server? 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 all precompiled and stored on the server. Queries can only be created or altered through the NextDB administrative interfaces which require an administrative login and password, and operate over SSL.

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 (primary key surrogate) 
					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>

Updates With Change-checking (FIXME)

A common database scenario involves querying for some data, changing the data, then updating the database with the changed values. If more than one user can access the same data, the possibility exists that both users could simultaneously change the same data. In some situations, this could lead to problems. For example, consider a database for online movie ticket sales. The database might store the number of tickets available for a given showtime. When a user purchases a ticket, the application subtracts one from the number of tickets and updates the value. If one ticket is remaining, multiple clients could purchase the ticket. They would each blindly update the value to zero, leading to an overbooking. The solution is to use change-checking to insure that when the client updates the number of tickets to zero, that the number of tickets has not already been changed from one to zero by another client. The Update Object supports change-checking. Listing 22 shows how to use change-checking, and how to recover from the scenario in which the update fails>

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

Remove Relationship

©2008 NextDB.net