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.

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 'table_name=TABLE_NAME' --form 'table_description=TABLE_DESCRIP' \
       http://sensorbase.org/post_request.php
        

    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.