S3QLSyntax (S3DB)

S3QLSyntax

Select

Please note, you may use regular expressions (see end of document) to specify your query.

Common to all S3DB entities are the following attributes:

description - A short description of the resource being instantiated
created_on - The timestamp at the time when the resource was created
created_by - The user_id of the user that created the resource
assigned_permission - The permission that the user asking the question has been given on the resource instantiated
effective_permission - The permission that the user asking the question effectivelly has on the resource (assigned + propagated)
uid - A unique S3DB Barcode ID, achieved by the concatenation of the deployment_id, user_id and id of the resource being instantiated
uri - The web-accessible URL containing metadata about the resource being instantiated
permission_level (deprecated) - Replaced with effective_permission

Attributes that are specific to each S3DB entity are as follows:

Projects

Select	From
project_id, name, project_name (deprecated), project_description (deprecated)	projects

Example Syntax


<S3QL>

<select>*</select>
        <from>projects</from>
            <where>
                <project_id>...</project_id>

                <project_name>...</project_name>
            </where>
</S3QL>

Rules

Select	From
rule_id, subject, verb, object, subject_id, verb_id, object_id, validation, project_id notes (deprecated)	rules

Example Syntax

<S3QL>
<select>*</select>
        <from>rules</from>

            <where>
                <subject_id>...</subject_id>
                <project_id>...</project_id>
            </where>

</S3QL>

Collections

Select	From
collection_id, name, project_id, notes (deprecated)	collections

Example Syntax

<select>*</select>
        <from>collections</from>

            <where>
                <collection_id>...</collection_id>
                <name>...</name>
            </where>

Items

Select	From
item_id, collection_id, resource_id (deprecated), instance_id (deprecated), resource_class_id (deprecated), notes (deprecated), entity (deprecated), project_id (deprecated), class_id (deprecated)	items

Example Syntax

<select>*</select>
   <from>items</from>
   <where>
   <collection_id>...</collection_id>
   </where>

<select>*</select>
     <from>items</from>
     <where>
   <collection_id>...</collection_id>
   <notes>myItem</notes>
     </where>

Please note: In order to retrieve items you will need to specify the collection_id where they were created.

Statements

Select	From
statement_id, item_id, rule_id, value, file_name, file_size, subject, subject_id, verb, verb_id, object, object_id, notes (deprecated), resource_id (deprecated), instance_id (deprecated), project_id (deprecated)	statements

Example Syntax

<select>*</select>
        <from>statements</from>
            <where>
                <rule_id>...</rule_id>
                <value>...</value>
            </where>

<select>*</select>
        <from>statements</from>
            <where>
                <item_id>...</item_id>
                 <rule_id>...</rule_id>
            </where>
PLEASE NOTE: The subject, subject_id, verb, verb_id, object and object_id returned by a query to the statement refer to the ones used in the rule.

Rulelog

Select	From
rule_id, project_id, action, action_by, action_timestamp, old_subject, old_verb, old_object, old_notes	rulelog

Example Syntax

<select>*</select>
        <from>rulelog</from>

            <where>
                <project_id>...</project_id>
            </where>

Users

Select	From
user_id, login, login, email, username, account_phone, city, state, postal_code, country, address, assigned_permissionOnEntity, effective_permissionOnEntity, permissionOnResource (deprecated) account_id (deprecated), account_lid (deprecated), acl (deprecated),	users

Example Syntax

<select>*</select>
        <from>users</from>


You may also use this syntax to find user permission on a given resource:
<select>*</select>
        <from>users</from>
        <where>
            <project_id>126</project_id>
        </where>

Note: assigned_permissionOnEntity and effective_permissionOnEntity specifies the permission that the user asking the question has in the entity specified in the query (for example, in project_id = 126)

Groups

Select	From
group_id, name accout_id (deprecated), account_lid (deprecated), account_uname (deprecated), group_name (deprecated)	groups

Example Syntax

<select>*</select>
        <from>groups</from>

Keys

NOTICE: The ability to query keys in the API was DEPRECATED for security reasons (please use apilogin.php to obtain a key using your username and password)

Permission

User Admin may perform queries on permissions.

Select	From
uid, shared_with, permission_level	permission

Example Syntax:

<select>*</select>

<from>permission</from>

<where>

<uid>P123</uid>

</where>

Insert, Update

User

Insert	Where (Required)	Where (Optional)
User	username, email	address, country, city, postal_code, state, account_phone, permission_level
User	user_id	username, email, address, country, city, postal_code, state, account_phone, permission_level
User	user_id	permission_level
User	user_id, project_id	permission_level
User	user_id, group_id

Update	Where (Required)	Where (Optional)
User	user_id	username, email, address, country, city, postal_code, state, permission_level

Example 1: Creating a new user

<insert>user</insert>
    <where>
        <login>hdeus</login>

        <email>hdeus@mail.org</email>
        <country>Portugal</country>
    </where>

Example 2: Updating an existing user

<update>user</update>
    <where>
        <user_id>3</user_id>

        <email>mynewMail@mail.org</email>
    </where>

Example 3: Giving user permission to view/edit a project

<insert>user</insert>
        <where>

            <user_id>5</user_id>
            <project_id>10</project_id>
            <permission_level>yns</permission_level>

        </where>

Remote User

Note: It is possible to create user Users with Accounts elsewhere.

You will need to specify the S3DB where user has an account or, in case it is not S3DB but another authentication authority such as Google or LDAP, you need to specify the protocol. Use the following syntax:

protocol : authority : username or email

Example 4: Inserting a user with an account on google

<insert>user</insert>
        <where>
            <user_id>D123|U4</user_id>

            <project_id>456</project_id>
        </where>

In this case, protocol is http but it can be ommited, as that is the protocol chosen by default.

<insert>user</insert>
        <where>
            <user_id>google:helenadeus@gmail.com</user_id>
        </where>

Example 5: Inserting a user with an account in another s3db deployment

In this case, protocol is s3db, and you must specify the name/deployment_id of the deployment where user can be authenticated.

<insert>user</insert>
        <where>
            <user_id>s3db:RPPA:hdeus</user_id>
        </where>

Group

Insert	Where (Required)	Where (Optional)
Group	name

Update	Where (Required)	Where (Optional)
Group	group_id	Groupname

Example 1: Inserting a group

<insert>group</insert>

    <where>
        <name>MyGroup</name>
    </where>

Example 2: Updating an existing group

<update>group</update>
    <where>
        <group_id>3</group_id>

        <name>MyNewGroup</name>
    </where>

Project

Insert	Where (Required)	Where (Optional)
project	project_name	project_description
project	project_id	project_name, project_description

Update	Where (Required)	Where (Optional)
project	project_id	project_name, project_description

Example 1:Inserting a new project

<insert>project</insert>
        <where>

           <name>...</name>
           <description>...</description>
        </where>

Example 2: Updating an existing project

<update>project</update>
        <where>
           <project_id>...</project_id>

           <name>...</name>
        </where>

Collection

Insert	Where (Required)	Where (Optional)
collection	project_id, name	notes
collection	collection_id
collection	project_id, collection_id

Update	Where (Required)	Where (Optional)
collection	collection_id	notes, entity

Example 1: Inserting a collection

<insert>collection</insert>

            <where>
                <project_id>...</project_id>
                <name>...</name>
                <notes>...</notes>

            </where>

Example 2: Updating an existing collection

<update>collection</update>
            <where>

                <collection_id>...</collection_id>
                <name>...</name>
                <notes>...</notes>

            </where>

Example 3: Inserting an existing collection in a remote project

<insert>collection</insert>
            <where>

                <collection_id>...</collection_id>
                <project_id>...</project_id>
            </where>

Item

Insert	Where (Required)	Where (Optional)
item	collection_id	notes
item	item_id, collection_id

Update	Where (Required)	Where (Optional)
item	item_id	notes

Example 1: Inserting an item

<insert>item</insert>
        <where>
            <collection_id>...</collection_id>
            <notes>...</notes>

        </where>

Example 2: Inserting an existing item in a remote collection

<insert>item</insert>
        <where>

            <collection_id>...</collection_id>
            <item_id>...</item_id>
        </where>

Rule

Insert	Where (Required)	Where (Optional)
rule	project_id, subject_id, verb_id/verb, object_id/object	notes, validation
rule	project_id, rule_id

Update	Where (Required)	Where (Optional)
rule	rule_id	subject, verb, object, notes, validation

Example 1: Inserting a new rule

<insert>rule</insert>
            <where>
                <project_id>...</project_id>
                <subject_id>12</subject_id>

                <verb>hasAge</verb>
                <object>Age</object>
                <validation>[0-9][0-9]</validation>

            </where>

Example 2: Inserting an existing rule in a remote project

<insert>rule</insert>
            <where>

                <project_id>...</project_id>
                <rule_id>...</rule_id>
            </where>

Statement

Insert	Where (Required)	Where (Optional)
statement	item_id, rule_id, value	notes
statement	item_id, statement_id
statement	rule_id, statement_id

Update	Where (Required)	Where (Optional)
statement	statement_id	notes, value

Example 1: Inserting a new statement

<insert>statement</insert>
            <where>
                <item_id>...</item_id>

                <rule_id>...</rule_id>
                <value>...</value>
                <notes>...</notes>

            </where>

Example 2: Inserting an existing statement in a remote rule

<insert>statement</insert>
            <where>

                <statement_id>...</statement_id>
                <rule_id>...</rule_id>
            </where>

File

Insert	Where (Required)	Where (Optional)
File	item_id, rule_id, value	file_name, notes
File	item_id, rule_id, filekey	notes

Example 1: Inserting a previously uploaded file - To know how to upload a file to S3DB, please see S3DB Uploads.

<insert>file</insert>
            <where>

                <item_id>...</item_id>
                <rule_id>...</rule_id>
                <filekey>...</filekey>

                <notes>...</notes>
            </where>

Note that, if you wish to send a string to a file, you can do it by using the "value" parameter to transfer the string. When using the following syntax, S3DB will create a file and insert the string specific in "value" as its content.

<insert>file</insert>
           <where>
                <item_id>...</item_id>
                <rule_id>...</rule_id>
                <value>contents to be written to the file</value>

<file_name>myfile.txt<file_name>
</where>

Key

Insert	Where (Required)	Where (Optional)
Key		key_id, expires, notes

Example 1: Inserting an existing statement in a remote rule

In case key_id is missing, default is to generate a random key. If expiration date is missing, default is defined as 24 hour validity.

Delete

Delete	Where (Required)	Flag (Optional)
resource_name	id	all/unlink

flags: all removes all resources that are dependent of id
unlink removes user from resource identified in id
flag defaults to unlink

Project

Delete	Where (Required)	Flag (Optional)
project	project_id	all/unlink
project	project_id, user_id	all/unlink
project	project_id, group_id	all/unlink

Example 1: Removing a user access to a project

<S3QL>
<delete>project</delete>

    <where>
        <project_id>123</project_id>
        <user_id>456</user_id>
    </where>

</S3QL>

Example 2: Removing a project

<S3QL>
<delete>project</delete>

    <where>
        <project_id>123</project_id>
    </where>
</S3QL>

Example 3: Removing a project and all rules and collections

<S3QL>
<delete>project</delete>
    <where>

        <project_id>123</project_id>
    </where>
    <flag>all</flag>
</S3QL>

Collection

Example 1: Deleting a collection from user view (leave collection available for others)

<S3QL>
<delete>collection</delete>

    <where>
        <collection_id>123</collection_id>
    </where>
</S3QL>

Example 2: Deleting a collection from project view (leave available for others)

<S3QL>
<delete>collection</delete>
    <where>

        <collection_id>123</collection_id>
        <project_id>10</project_id>
    </where>
</S3QL>

Rule

Example 1: Deleting a rule from user view (leave available for others)

<S3QL>
<delete>rule</delete>

    <where>
        <rule_id>123</rule_id>
    </where>
</S3QL>

Item

Example 1: Deleting an Item from user view (leave available for others)

<S3QL>
<delete>item</delete>
    <where>

        <item_id>123</item_id>
    </where>
</S3QL>

Statement

Example 1: Deleting a statement

<S3QL>
<delete>statement</delete>
    <where>
        <statement_id>123</statement_id>

    </where>
</S3QL>

User

Example 1: Deleting a user

<S3QL>
<delete>user</delete>
    <where>
        <user_id>123</user_id>
    </where>

</S3QL>

Example 2: Removing a user from a group

<S3QL>
<delete>user</delete>

    <where>
        <group_id>456</group_id>
        <user_id>123</user_id>
    </where>

</S3QL>

Group

Example 1: Deleting a group

<S3QL>
<delete>group</delete>

    <where>
        <group_id>123</group_id>
    </where>
</S3QL>

Key

<S3QL>
<delete>key</delete>

    <where>
        <key_id>abcd</key_id>
    </where>
</S3QL>

Regular Expressions

You may use regular expressions when specifying your query in any of the attributes.
Example: Matching a fraction of a collection name

<S3QL>
<delete>collection</delete>
    <where>
        <name>~Sample</name>
    
    </where>
</S3QL>

Note: Regular expression syntax will be slightly different for S3DB whether you are using MySQL or PostgreSQL.
Use the syntax of the engine where you installed your S3DB.

Errors

Error Code	Message
0	Successfull
1	Not a valid query
2	Unrecognized error
3	Missing information
4	Repeated action
5	No permission
6	Wrong query
7	Wrong input
8	No changes we made
9	No results from query
10	A part of the query failed

Permission Management

Permission management of users in s3db resources is inherited from project unless a permission is specified for specific resources. Users can be added/removed to projects, rules, collections, items and statements with a level of permission for view, change and add, specified through a permission code. The syntax to sharing a resource with another user is the same for project, collection, rule, item and statement:

Example of inserting a user on a rule:

<S3QL>
    <insert>user</insert>

    <where>
        <rule_id>...</rule_id>
        <user_id>...</user_id>
        <permission_level>...</permission_level>    </where>
</S3QL>

In a less broad sense, any resource can be given permission on any other upper resource:

Example of sharing a rule with a project:

<S3QL>
    <insert>rule</insert>

    <where>
        <project_id>12.</project_id>
        <rule_id>13</rule_id>
    </where>

</S3QL>

To un-share a rule from a project, you just need to specify the opposite query, that is, use "delete":

<S3QL>
    <delete>rule</delete>
    <where>
        <project_id>12.</project_id>
        <rule_id>13</rule_id>
    </where>

</S3QL>

Dictionary

A dictionary is a list of namespaces and random triples that can be added in reference to any S3DB UID. You may wish, for example, to specify the UID C186 is equivalent to a resource in a standard existing ontology, for example MGED.
You do this by inserting a "link" in the API function dictionary.php. Keep in mind that, if you are using a short namespace (for example, mged:BioSource), the qname (mged) must already exist as a namespace (use the dictionary.php to select all existing namespaces). By adding an extra argument to your S3QL query, "graph", S3DB will also query and output the dictionary terms related to the UID retrieved in your query:

Example:

<S3QL>
	<select>*</select>
	<from>collection</from>
	<where>
		<collection_id>186</collection_id>
	</where>
	<graph>on</graph>
</S3QL>

Select Link

An alternative query specific for the Dictionary will allow faster queries without the need to write several S3QL queries:

<S3QL>

	<select>*</select>
	<from>links</from>
</S3QL>

Insert, Update, Delete Link

The dictionary can be populated with links using a syntax developed for the purpose. In order to create an entry in the dictionary, use the following arguments:

Insert Where (Required) Where (Optional)
link uid, relation, value (no optional arguments)

 Example: 
<S3QL>
    <insert>link</insert>
       <where>
            <uid>C186</uid>
            <relation>rdfs:isDefinedBy</relation>
            <value>mged:BioSample</value>
        </where>
</S3QL>

Similar to the rest of S3QL, you can select, update or delete a link. You will need link_id, which will be returned when you perform a query on links:

Update Where (Required) Where (Optional)
link link_id uid, relation, value
 Example: 
<S3QL>
    <update>link</update >
       <where>
            <link_id>123</link_id>
            <relation>rdfs:seeAlso</relation>
        </where>
</S3QL>

Delete Where (Required)
link link_id
 Example: 
<S3QL>
    <delete>link</delete >
       <where>
            <link_id>123</link_id>
        </where>
</S3QL>

Select Namespace

A namespace is the means by which a short syntax, for example "mged" can be used as a surrogate for a longer URL, for example "http://mged.sourceforge.net/ontologies/MGEDOntology.owl#". When a Link is inserted in Dictionary, such as in the examples above, a namespace can be used instead of the complete URL. In order to use a namespace, it must already exist in the Dictionary. Select all the namespaces in the Dictionary by using the followin syntax:

From Where (Optional) 
namespace qname, url

Example:

<S3QL>
    <from>namespace</from>
       <where>
            <qname>mged</qname>
        </where>
</S3QL>

Insert, Update, Delete Namespace
If the qname does not exist, you can create it using the following:
Insert Where (Required) Where (Optional)
namespace qname, url (no optional arguments)
 Example: 
<S3QL>
    <insert>namespace</insert>
       <where>
            <qname>mged</qname>
            <url>http://mged.sourceforge.net/ontologies/MGEDOntology.owl#</url>
        </where>
</S3QL>

You may also chose to update or delete a namespace
Update Where (Required) Where (Optional)
namespace namespace_id qname, url
 Example: 
<S3QL>
    <update>namespace</update >
       <where>
            <qname>mged</qname>
            <url>http://mged.sourceforge.net/ontologies/MGEDOntologyv3.1.owl#</url>
        </where>
</S3QL>

Delete Where (Required)
namespace namespace_id
 Example: 
<S3QL>
    <delete>namespace</delete >
       <where>
            <qname>abc</qname>
        </where>
</S3QL>

Other Options
You can use the argument "options" to perform a query after another action has been performed.
For example, if you specify in you query that options=select, then following an "Insert", S3DB will  return to you information regarding the entity just inserted.
Example
<insert>item</insert>
    <where>
        <collection_id>1417</collection_id>
	<notes>tmp</notes>	
    </where>
    <options>select</options>

For now, only "select" is supported. Others will be added in a comma-separated style. 
Please note that this is only supported for output as structured formats -  XML and JSON.

Attachments (1)

s3ql_syntax.xsd - on Nov 23, 2009 12:26 PM by Lena Deus (version 1)
7k Download

S3DB

Navigation

Recent site activity

S3QLSyntax

Select

Projects

Rules

Collections

Items

Statements

Rulelog

Users

Groups

Keys

Permission

Insert, Update

User

Remote User

Group

Project

Collection

Item

Rule

Statement

File

Key

Delete

Project

Collection

Rule

Item

Statement

User

Group

Key

Regular Expressions

Errors

Permission Management

Dictionary

Select Link

Insert, Update, Delete Link

Select Namespace

Insert, Update, Delete Namespace

Other Options

Attachments (1)