RazorSQL SimpleDB SQL Support
RazorSQL provides the ability to connect to Amazon SimpleDB databases. It provides visual tools for creating and dropping domains, editing domain data, creating, editing, and dropping domain attributes, and more. RazorSQL also allows users to use SQL syntax to execute SQL selects, inserts, updates, and deletes against SimpleDB databases.
Listed below is information on the SQL syntax supported by RazorSQL with SimpleDB.
General Notes
The Amazon SimpleDB database is not a relational database and does not follow the same rules as traditional relational databases with regards to data. A SimpleDB domain is similar to a table in a traditional database, and attributes of a domain are similar to columns in a traditional database. Each "row" of the domain is defined by a unique item name. The item name can be identified in queries as itemName().
However, unlike traditional databases, attributes can have multiple values for a given itemName() value, and new attributes can be added at any time to an itemName(). Because of this, the data contained in a SimpleDB domain does not necessarily map out nicely to the spreadsheet like row / column format that is used to display data for a traditional database.
RazorSQL does try to display SimpleDB data in row / column format. For domains that have the same attributes for each itemName() and do not have multiple values per attribute, this display works correctly. To handle the problem of attributes containing multiple values for a given itemName(), RazorSQL separtes the values with the pipe | character. To figure out the attributes that a domain contains, RazorSQL inspects the first 100 itemNames of the domain and gathers the distinct attribute names of those items. It then displays them as columns.
Select
RazorSQL supports the select syntax that is supported by the Amazon SimpleDB select API operation.
The SimpleDB select API does not have support for such operations as SQL joins. Future versions
of RazorSQL may add support for more complex select syntax than is supported by the SimpleDB
select API.
Currently, the SimpleDB select operation supports the syntax listed below. For more information and
up to date Select support, see the Amazon SimpleDB documentation.
select output_list
from domain_name
[where expression]
[sort_instructions]
[limit limit]
The expression can be any of the following:
(select expression) intersection (select expression)
NOT (select expression)
((select expression))
(select expression) or (select expression)
(select expression) and (select expression)
(simple comparison)
Use the keyword itemName() to get the item names in the domain. For example:
select itemName() from domain
Note: The keyword itemName() can not be used when specifying
specific attributes to select in the select query. Using the
select * syntax will display both the itemName() and
all of the attribute names and values for the domain.
Insert
RazorSQL adds its own support for SimpleDB SQL insert statements. The SimpleDB API itself does not provide this support. This is an add-on operation supplied by the RazorSQL SimpleDB driver. RazorSQL requires the insert statement to be in a specific syntax to be accepted. The itemName() value must be specified in the insert for the insert to work correctly. Listed below is the required syntax.
INSERT INTO
<domain name> (itemName(), Attribute1, . . ., AttributeN)
VALUES
('<item name value>', 'Attribute1_Value', . . ., 'AttributeN_Value')
For Example:
INSERT INTO
test_domain (itemName(), Category, Color)
VALUES
('Item_01', 'Clothes', 'Orange');
To indicate that an attribute should have multiple values, separate each value with the | character. For example:
insert into test_domain (itemName(), Size) values ('Item1', 'Small|Medium|Large')
Update
RazorSQL adds its own support for SimpleDB SQL update statements. The SimpleDB API itselft does not provide this support. This is an add-on operation provided by the RazorSQL SimpleDB driver. RazorSQL requires the update statement to be in a specific syntax to be accepted. The itemName() value must be specified in the where clause of the update for the update to work correctly. Listed below is the required syntax.
UPDATE <domain_name> SET Attribute1 = 'value1', Attribute2 = 'value2', . . . WHERE
itemName() = 'item_value'
For example:
UPDATE test_domain SET Category = 'Clothes', Color = 'Orange'
WHERE itemName() = 'Item_01';
Delete
RazorSQL adds its own support for SimpleDB SQL delete statements. The SimpleDB API itself does not provide this suport. This is an add-on operation provided by the RazorSQL SimpleDB driver. RazorSQL requires the delete statement to be in a specific syntax to be accepted. The itemName() value must be specified in the where clause of the delete for the delete to work correctly. Listed below is the required syntax.
DELETE FROM <domain_name> WHERE itemName = 'item_value'
For example:
DELETE FROM test_domain WHERE itemName() = 'Item_01';
Create
RazorSQL adds its own support for a SimpleDB create domain statement. This will create a new domain in the SimpleDB database. The syntax is the following:
CREATE DOMAIN <domain_name>
For example:
create domain test_domain
Drop
RazorSQL adds its own support for a SimpleDB drop domain statement. This will delete an existing domain. The syntax is the following:
DROP DOMAIN <domain_name>
For example:
drop domain test_domain
