Slogging Data and Accessing Functions via HTTP POST

Help / Slogging Data and Accessing Functions via HTTP POST

Note: As we go through some examples, we're assuming you've already created a project and a data table(s) to slog data to. Also, the following code snippets can be generalized and adapted for other languages other than Perl and UNIX.

Slogging CSV File (UNIX) Example
Slogging XML File (UNIX) Example
Slogging Image Files (Perl) Example

New Features (In beta, please let us know about any bugs)

Create a Table through POST example

Giving a user project access through POST example

Giving a user table access through POST example

Removing user's project access through POST example

Removing user's table access through POST example

Retrieve project structure through POST example

Retrieve table structure through POST example

Update an existing row through POST example

Get list of users with permission for a Project

Get list of users with permission for a Table

Delete a Project

Delete a Table

Get SBID

Inner Join SELECT queries

Left Join SELECT queries

Right Join SELECT queries

Straight Join SELECT queries

Set Column as UNIQUE

Unset Column as UNIQUE

For the impatient, we'll start with a basic example of how you can slog a CSV file via an HTTP POST.

Slogging CSV File (UNIX) Example

If you have a traditional CSV file with a header and all, you can use the below code. All you have to do is replace EMAIL, PASSWORD, PROJECT_ID, and TABLE_NAME with your own SensorBase information. You also will need to change the data_file field to the correct path (yeah, the "@" is required) to your CSV file.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=EMAIL' --form 'password=PASSWORD' \
   --form 'project_id=PROJECT_ID' --form 'table_name=TABLE_NAME' \
   --form 'data_file=@path/to/data.csv' --form 'type=csv' \
   http://sensorbase.org/upload.php

Slogging XML File (UNIX) Example

Similar to the above CSV example, you can also slog XML files in the following format:

<table>
    <row>
        <field name="User_Name">myUser</field>

        <field name="Demo_Name">urbanCens</field>
        <field name="Demo_Author">author_name</field>
        <field name="Affilliation">ucla</field>

        <field name="Num_Of_Visitors">3</field>
        <field name="Rate_Of_Demo">8</field>
        <field name="Comment">great</field>

    </row>
    <row>
        .
        .
        .
    </row>
</table>

When you have XML files in this format, you can use something similar to that of the CSV upload:

curl --connect-timeout 10 --max-time 120 -s -S \
    --form 'email=EMAIL' --form 'password=PASSWORD' \
    --form 'project_id=PROJECT_ID' --form 'table_name=TABLE_NAME' \
    --form 'data_file=@path/to/data.xml' --form 'type=xml' \
    http://sensorbase.org/upload.php

Notice that the only difference between uploading CSV and XML is the type field and the file that you are uploading.

Slogging Image Files (Perl) Example

The following Perl example is courtesy of Shaun Ahmadian (thanks, Shaun!). Again, you have to replace EMAIL, PASSWORD, PROJECT_ID, and TABLE_NAME with your own SensorBase information.

#!/usr/bin/perl

use strict;

my $dir=$ARGV[0];
my $url="http://sensorbase.org/upload.php";
my $project_id=PROJECT_ID;
my $sensorbase_user=EMAIL;
my $sensorbase_password=PASSWORD;
my $table_name=TABLE_NAME; 

foreach my $imageFile (<$dir/*>) {
    if ($imageFile=~/mote(\d+)_date\d+_(\d{4})_(\d{2})_(\d{2})_(\d{2})_(\d{2})/) {
        my($mote_id, $year,$mon,$day, $hour,$min)=($1,$2,$3,$4,$5,$6);
        if ($year<2004)
        { next; } # We don't want 1970!

        my $datetime="$year-$mon-$day $hour:$min:00";
             
        my $CURL="curl --connect-timeout 10 --max-time 120 -s -S 
            --form 'email=$sensorbase_user' --form 'password=$sensorbase_password' 
            --form 'project_id=$project_id' --form 'table_name=$table_name' 
            --form 'MAX_FILE_SIZE=2097152' --form 'blob=1' --form 'datetime=$datetime' 
            --form 'location_id=$mote_id' --form 'image=\@$imageFile' $url";
        my $result=`$CURL 2>&1`;
        
        my $error=`echo $?`;
        print $CURL;
        if ($error > 0){
            print STDERR "File: $imageFile : $error \n";
            die "Executing of $CURL failed:\n$result\n";
        }
    print "RESULT: $result\n";
    }
}

The main portion of the above code is where the $CURL statement is formed. The beginning parts of the statement are the same as previous examples where email, password, project_id, and table_name are specified.

After table_name is MAX_FILE_SIZE and blob, which must be specified, where the latter indicates that you are uploading media (e.g. images, audio). The remaining fields are specific to the table where data was being slogged. In this example, an image is getting slogged to a table with fields named "datetime", "location_id", and "image". Note that the "image" field in this example is of media type and so is a pointer to an image file.

Create a Project through POST example

Create a new project using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=create_project' --form 'project_name=PROJECT_NAME' \
   --form 'project_description=PROJECT_DESCRIPTION' --form 'project_address=ZIP_CODE' \
   --form 'project_tags=SPACE SEPARATED TAGS' \
   http://sensorbase.org/post_request.php

Create a Table through POST example

Create a table for an existing project using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=create_table' --form 'project_id=PROJECT_ID' \
   --form 'columns=col_name1 type1(length)|type1 [UNSIGNED/SIGNED] [1] [description1],col_name2 
          type2(length)|type2 [UNSIGNED/SIGNED] [1] [description2],...' \
   --form 'table_name=TABLE_NAME' --form 'table_description=TABLE_DESCRIP' \
   http://sensorbase.org/post_request.php

The columns field is an optional parameter to define the columns of the newly created table, however without it, your table is pretty useless since there is no add_column command currently to modify existing tables. To define columns in the columns parameter, you must provide a comma separated list of columns with each column definition containing a space between each column parameter. The format for defining a column is as follows:

    	You must start out by specifying a column name, followed by a space.
    	You then declare the data type of the column, such as "int", "varchar(80)", "tinytext", etc..., followed by a space.
    	You can then optionally declare if the data will be "UNSIGNED" or "SIGNED", followed by a space.
    	You can then optionally declare if the column will be the primary key by entering "1", followed by a space.	
    	You can then optionally add a column description with spaces between words. 
    		Note, this means that your description cannot start with the words "UNSIGNED", "SIGNED", or "1" due to how this 
    		command is processed.
    	After entering this column information, add a comma to add more columns.

An example: --form 'columns=rice text is a text,carrots varchar(80),id int 1 this is an id' will create a table with three columns, one named "rice" of data type "text" and a description "is a text", another named "carrots" of type "varchar(80)" with no description, and a last one named "id" of type "int" with a description "this is an id" and set as a primary key. For a list of acceptable data types check out: Table Creation Guidelines and refer to the mysql language standard for their representation.

Below you can find a PHP curl example for creating a table , courtesy of Matthew Mayernik. The process should be similar for dealing with the other functions outlined in this help page.

    		$ch = curl_init("http://sensorbase.org/post_request.php");
			curl_setopt($ch, CURLOPT_POSTFIELDS,
			  array('email'=>"USERNAME",
			        'password'=>'PASSWORD',
			        'cmd'=>'create_table',
			        'project_id'=>'000',
			        'columns'=>'TEMP float SIGNED Temperature,TIME time UNSIGNED Sample period',
			        'table_name'=>"TEST TABLE",
			        'table_description'=>'CURLPHP TEST'));
			curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
			$postResult = curl_exec($ch);
			curl_close($ch);
			print "$postResult";

The table_description field is an optional parameter used to give a textual description to the table.

Give a user project access through POST example

Give users access to projects using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=project_access_grant' --form 'project_id=PROJECT_ID' \
   --form 'email2=USER_EMAIL' --form 'permission=PERMISSION_INT' \
   http://sensorbase.org/post_request.php

The permission field is an optional parameter giving only read access to USER_EMAIL if not included, or any value besides 4,3, or 2.

If PERMISSION_INT is 4, user is given build permission. If PERMISSION_INT is 3, user is given write permission. If PERMISSION_INT is 2, user is given write only permission. Any other value for PERMISSION_INT or if it is not provided will result in user being given read permission.

Give a user table access through POST example

Give users access to tables using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=table_access_grant' --form 'table_id=TABLE_ID' \
   --form 'email2=USER_EMAIL' --form 'permission=PERMISSION_INT' \
   http://sensorbase.org/post_request.php

The permission field is an optional parameter giving only read access to USER_EMAIL if not included, or any value besides 4 or 3.

If PERMISSION_INT is 4, user is given write permission. If PERMISSION_INT is 3, user is given write only permission. Any other value for PERMISSION_INT or if it is not provided will result in user being given read permission.

Remove user project access through POST example

Removes user's access to projects using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=project_access_delete' --form 'project_id=PROJECT_ID' \
   --form 'email2=USER_EMAIL'\
   http://sensorbase.org/post_request.php

Alternatively, one code use this code to accomplish the same thing

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=project_access_delete' --form 'project_id=PROJECT_ID' \
   --form 'user=USER_ID'\
   http://sensorbase.org/post_request.php

Remove user table access through POST example

Removes user's access to table using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=table_access_delete' --form 'table=TABLE_ID' \
   --form 'email2=USER_EMAIL'\
   http://sensorbase.org/post_request.php

Alternatively, one code use this code to accomplish the same thing

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=table_access_delete' --form 'table=TABLE_ID' \
   --form 'user=USER_ID'\
   http://sensorbase.org/post_request.php

Retrieve structure of existing project through POST example

Retrieve names of tables within selected project using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=get_project_structure' --form 'project_id=PROJECT_ID' \
   http://sensorbase.org/post_request.php

Note: You must have at least read access to the project or it must be set to PUBLIC in order to successfully retrieve data.

Retrieve structure of existing table through POST example

Retrieve column names of an existing table using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=get_table_structure' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' \
   http://sensorbase.org/post_request.php

Note: You must have at least read access or the table must be set to PUBLIC in order to successfully retrieve data.

Update an existing row through POST example

Update an existing row using POST without having to access the UI.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=update_row' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' --form 'row_id=ROW_ID'\
   --form 'row_cols_vals=COLUMN_VALUES_STRING'\
   http://sensorbase.org/post_request.php

Note: The COLUMN_VALUES_STRING value must be inputted in a specific format following this convention: COLUMN_NAME1 | VALUE1 , COLUMN_NAME2 | VALUE2 , COLUMN_NAME3 | VALUE3 , ...

So in the example above, the code might look like this

...

--form 'row_cols_vals=TextDescription|The grass was wet,HumidityIndex|53'  

...

View list of users with permissions for this project

Retrieve a list of the owner and users who have build,write,and/or read access to this project.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=get_project_permissions' --form 'project_id=PROJECT_ID' \
   http://sensorbase.org/post_request.php

You must have build access for the project to use this command.

Output Format:

Success: Owner: 'OWNER_EMAIL' User: 'USER_EMAIL1' (BUILD_VALUE,WRITE_VALUE,READ_VALUE) , User: 'USER_EMAIL2' (BUILD_VALUE,WRITE_VALUE,READ_VALUE) , ...

If the list of users is empty, there there are no permissions assigned besides those to the owner. BUILD_VALUE,WRITE_VALUE,READ_VALUE are returned as either 1 or 0 to specify permission level.

View list of users with permissions for this table

Retrieve a list of the owner and users who have build,write,and/or read access to this table.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=get_table_permissions' [ --form 'table_id=TABLE_ID' \
   || --form 'project_id=PROJECT_ID -form 'table_name=TABLE_NAME' ] \
   http://sensorbase.org/post_request.php

You must have build access for the project to use this command. You can either supply a table_id value or both project_id and table_name values for this command.

Output Format:

Same as get_project_permissions command.

Delete a Project

Completely delete a Project from Sensorbase.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=delete_project' --form 'project_id=PROJECT_ID' \
   http://sensorbase.org/post_request.php

You must have build access for the project to use this command.

Delete a Table

Completely delete a Table from Sensorbase.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=delete_table' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME'
   http://sensorbase.org/post_request.php

You must have build access for the table or the parent project to use this command.

Get SBID

Retrieve the SBIDS of row(s) within a table which have the specified column and value combination.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=get_sbid' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' --form 'column=COLUMN_NAME' \
   --form 'value=COLUMN_VALUE'
   http://sensorbase.org/post_request.php

You must have read access for the table or the parent project to use this command. If there are multiple rows returned, the results are displayed as comma separated values.

Inner Joins

Peform an Inner Join SELECT query between two different tables by specifying two columns after an ON.

curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=select_join' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' --form 'project_id2=PROJECT_ID2' \
   --form 'table_name2=TABLE_NAME2' --form 'on_colums=ON_COLUMNS  \
   --form 'select_columns=SELECT_COLUMNS' 
   '
   http://sensorbase.org/post_request.php

You must have read access for both tables or the parent projects to use this command. Since actual database tables names are formatted different, if you want to specify your SELECT_COLUMNS, the format should be as follows:

table1.COLUMN_NAME,table2.COLUMN_NAME2,table1.COLUMN_NAME3 ...

where table1 is used whenever you want to specify a column within the first table specified by PROJECT_ID/TABLE_NAME and likewise for table2.
The order in which column names are listed here will determine the order of listing in the result from the server.
SELECT_COLUMNS is an optional parameter; leaving it blank will return all columns in both tables. ON_COLUMNS is specified as so:

ColumnFromTable1:ColumnFromTable2

where these columns are the comparision ON columns for each table.

NOTE:Currently, join statements are rather basic in that they don't allow for WHERE modifiers, only apply to SELECT queries, and don't allow for nested joins. Hopefully these, along with other limitations, will be changed in the future.

Left Joins

Perform a Left Join SELECT query between two different tables.

    
curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=select_join' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' --form 'project_id2=PROJECT_ID2' \
   --form 'table_name2=TABLE_NAME2' --form 'on_colums=ON_COLUMNS  \
   --form 'select_columns=SELECT_COLUMNS' --form 'type=JOINTYPE'
   '
   http://sensorbase.org/post_request.php

You can use the same function as described in the Inner Join section to perform a Left Join Select simply by adding the
optional parameter type and setting it to left.
NOTE: setting type to inner will result in the select_join command returning as a inner join query.

Right Joins

Perform a Right Join SELECT query between two different tables.

    
curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=select_join' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' --form 'project_id2=PROJECT_ID2' \
   --form 'table_name2=TABLE_NAME2' --form 'on_colums=ON_COLUMNS  \
   --form 'select_columns=SELECT_COLUMNS' --form 'type=JOINTYPE'
   '
   http://sensorbase.org/post_request.php

You can use the same function as described in the Inner Join section to perform a Right Join Select simply by adding the
optional parameter type and setting it to right.
NOTE: setting type to inner will result in the select_join command returning as a inner join query

Straight Joins

Perform a Right Join SELECT query between two different tables with or without an ON join condition.

    
curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=select_join' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' --form 'project_id2=PROJECT_ID2' \
   --form 'table_name2=TABLE_NAME2' --form 'on_colums=ON_COLUMNS  \
   --form 'select_columns=SELECT_COLUMNS' --form 'type=JOINTYPE'
   '
   http://sensorbase.org/post_request.php

You can use the same function as described in the Inner Join section to perform a Straight Join Select simply by adding the optional parameter type and setting it to straight.
If you do not want to specify ON_COLUMNS in your query, you must leave it blank.
NOTE: setting type to inner will result in the select_join command returning as a inner join query

Set Column as UNIQUE

Modify an existing column to be UNIQUE only if existing values within rows are already unique and a unique key does not already exist.

    
curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=set_unique' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' --form 'column=COLUMN_NAME'
   '
   http://sensorbase.org/post_request.php

You must have build access to use this command.

UnSet Column as UNIQUE

Modify an existing column to no longer be UNIQUE only if existing an unique key already exists.

    
curl --connect-timeout 10 --max-time 120 -s -S \
   --form 'email=AUTHENTICATION_EMAIL' --form 'password=PASSWORD' \
   --form 'cmd=unset_unique' --form 'project_id=PROJECT_ID' \
   --form 'table_name=TABLE_NAME' --form 'column=COLUMN_NAME'
   '
   http://sensorbase.org/post_request.php

You must have build access to use this command.