Documentation‎ > ‎

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: 


  • label - The name assigned to the resource
  • 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_levelpermission

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

<insert>key</insert>
    <where>
         <key_id>…</key_id>
        <expires>…</expires>
        <notes>…</notes>
    </where>


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
10A 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>


UID Resolve


S3DB Supports distributed URI dereferencing. If a deployment identifier is appended to an S3DB UID, such as, for example, D16057P126, URL for deployment D16057 must first be resolved. S3DB does this by by asking the mothership S3DB what is the URL for the deployment supplied by performing an HTTP GET request, such as, for example, http://root.s3db.org/URI.php?uid=D16057. If the mothership deployment does not have information regarding the provided Did, it will ask its own mothership. You can specify how many steps should be taken before a UID is rendered non-resolvable by using the uid_resolve keyword.

Example: 
http://mylocalhost/s3db/URI.php?uid=D123D456P789&uid_resolve=0 => Will try to resolve P789 in the local S3DB deployment but will not try to resolve it in D123 or D456
http://mylocalhost/s3db/URI.php?uid=D123D456P789&uid_resolve=1 => Will try to resolve P789 in D123 but not in D456
http://mylocalhost/s3db/URI.php?uid=D123D456P789&uid_resolve=2 => Will try to resolve P789 in D456 and resolve D456 in D123


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:
InsertWhere (Required)Where (Optional)
linkuid, 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:
UpdateWhere (Required)Where (Optional)
linklink_iduid, relation, value

 Example:

<S3QL>
    <update>link</update >
       <where>
            <link_id>123</link_id>
            <relation>rdfs:seeAlso</relation>
        </where>
</S3QL>

DeleteWhere (Required)
linklink_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:
FromWhere (Optional) 
namespaceqname, 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:
InsertWhere (Required)Where (Optional)
namespaceqname, 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
UpdateWhere (Required)Where (Optional)
namespacenamespace_idqname, url

 Example:

<S3QL>
    <update>namespace</update >
       <where>
            <qname>mged</qname>
            <url>http://mged.sourceforge.net/ontologies/MGEDOntologyv3.1.owl#</url>
        </where>
</S3QL>

DeleteWhere (Required)
namespacenamespace_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.
Č
ċ
s3ql_syntax.xsd
(7k)
Lena Deus,
Nov 23, 2009, 12:26 PM
Comments