Server Interaction

• Example 1 – TCP server-specific transfer

at+zipcall=1                                       open
at+zipopen=1,0,www.dbunplugged.com,9999            open connection to server (TCP)
at+zipsend=1                                       execute send
put/<username>/<password>/testNum/1/testStr/TCP    data to send
<cntr+z>                                           send data (same syntax as 3.1 URL Routing including get,put,del)
at+zipcall=0                                       close

• Example 2 – UDP server-specific transfer

at+zipcall=1                                       open
at+zipopen=1,1,www.dbunplugged.com,9999            open connection to server (UDP)
at+zipsend=1                                       execute send
put/<username>/<password>/testNum/2/testStr/UDP    data to send
<cntr+z>                                           send data (same syntax as 3.1 URL Routing including get,put,del)
at+zipcall=0                                       close

/

• Access the free SparqEE development environment at the above address.
• Add a user/password for yourself to provide access to your own table.
• You can edit your table (add/remove columns), dump it to view all the data, as well as manipulate it using the CELL and client commands.
• New to Databases? If you’ve never used a database before, the SparqEE development server can be thought of as an excel spreadsheet.
  • Each page of the spreadsheet is a table each user is assigned one table.
  • Columns are predefined by name and category (int = number, varchar = characters, datetime = timestamp) while rows are retrieved/inserted/deleted through either the CELL or the Client in order to exchange data between the CELL and Client.
• Follow the examples to see it in action:

Test server setup: 
The commands within the CELL and Client sections can be used alongside this example to manipulate server data.  
At any point you can "dump" the contents of your table in order to view the changes.

1. Go to the development server website
2. New User/Pass: Create an account by typing in a Username and Password, making sure "New User" is selected, then click Submit.
3. Edit: Enter your Username and Password, select "Edit", and click "Submit"
4. Add Entry: In a new address bar, enter the following URL in order to generate a single entry into your database table:
"/put/<username>/<password>/id/1"
5. Add a column named "testNum"  in "Field Name" and select "int" then click Submit
6. Add a column named "testStr" in "Field Name" and select "varchar" then click Submit
7. Add a column named "testTime"  in "Field Name" and select "datetime" then click Submit

This sets up a basic table with 4 columns, the id column, an int column named testNum, a varchar (character) field called testNum, and a datetime (timestamp) field called testTime.

Your own sever:

In case you want to spin up your own server side environment, all the code we’re using for the test servers are available for free. This includes the following:

1. Server Code – CodeIgniter base system: Unzip this file and put it up on your own webserver. 2 simple modifications required are listed in the readme.txt.
   1. Client-side frontend – allows users to create a user/pass, db table, view their table
   2. Client interface – allows for get/put into a user’s database (POST, URL routed)
2. Server Scripts – allows minimal packet size to get/put into a user’s database (TCP/UDP scripts)
3. SQL – the basic admin database for keeping track of users. Simply execute the SQL on your own database to generate the table.

There are a few different ways to access your table from your client.

3.1 URL Routing

3.1.1 Get – get an entry/entries from your table

Format: /put/<username>/<password>/<filters...>
    Required: username/password and at least one filter
    Optional: More than one filters are allowed
    Description: Each get returns a set of rows based on the filters
    Filters (work in combination):
        1. limit: retrieve a certain maximum number of rows plus assign an offset
            -Requires 2 fields: "limit/<max rows>/<offset>"
        2. order: sort the response by a column, ascending or descending
            -Requires 2 fields: "order/<column>/<'asc'|'desc'>"
        3. field: add any column and value
            -Requires 2 fields: "field/<column>/<value>"
        NOTES:
            -to get the first X elements, use "limit/X/0"
            -to get the last X elements, use "order/<column>/desc" with "limit/X/0"
    Response: JSON encoded array
        Ex: Query: /get/<user>/<pass>/order/id/desc/limit/3/1
            Response:
             [{"id":"6","testNum":"2147483647","testStr":"Hello world.","testTime":"2014-04-11 14:32:58"},
              {"id":"5","testNum":"2147483647","testStr":"Hello world.","testTime":"2014-04-11 14:32:58"},
              {"id":"1","testNum":"0","testStr":"","testTime":"0000-00-00 00:00:00"}]

3.1.2 Put – add an entry to your table

Format: /put/<username>/<password>/<column 1>/<value 1>/<column 2>/<column 2>/...
    Required: username/password and at least one column/value pair
    Optional: More than one column/value pair are allowed
    Description: Each put adds one entry to the table and includes all of the column/value paired information provided

Ex: /put/<username>/<password>/id/1/testNum/12345/testStr/Hello world./testTime/2014-04-11 14:32:58
    id = 1: 
        ID should typically be omitted as it simply counts upward and will automatically be inserted
    testNum = 12345: 
        Max length: 11
    testStr = Hello world.
        Max length: 100
    testTime = 2014-04-11 14:32:58
        Format: YYYY-MM-DD HH:MM:SS

Errors:
    1: missing URL segments - must have at minimum username/password and a key/value pair
    2: improper number of arguments - key/value must come in pairs
    3: query failed - put failed

3.1.3 Delete – delete an entry from your table

Format: /del/<username>/<password>/<column 1>/<value 1>/<column 2>/<column 2>/...
    Required: username/password and at least one column/value pair
    Optional: More than one column/value pair are allowed
    Description: Each delete removes one entry to the table which matches all of the column/value pair information together
    Custom: View the "where" section to create custom column filters

Ex: /del/<username>/<password>/id/1/testNum/12345
    id = 1
    testNum = 12345
    Both the above arguments must match the item in table for the delete execute

Errors:
    1: missing URL segments - must have at minimum username/password and a key/value pair
    2: improper number of arguments - key/value must come in pairs
    3: query failed - delete failed

3.2 URL Routing + POST variables

3.2.1 Get – get an entry/entries from your table

Format: /get
    "data" format is the following options separated by a forward slash
    -limit/<max rows>/<offset>
    -order/<column>/<'asc'|'desc'>
    -field/<column>/<value>"

Post Variables: Required: "user", "pass", one key/value pair Optional: "data" >1 key/value pairs

CURL Example:
    curl --data "user=<user>&pass=<pass>&data=order/id/desc" /get

Errors: Same as 3.1.1

3.2.2 Put – add an entry to your table

Format: /put
    "data" format is "column 1/value 1/column 2/value 2/..."

Post Variables: 
    Required: "user", "pass", "data" - one key/value pair
    Optional: "data" >1 key/value pairs 

CURL Example:
    curl --data "user=<user>&pass=<pass>&data=testNum/12345" /put

Errors: Same as 3.1.2

3.2.3 Delete – delete an entry from your table

Format: /del
    "data" format is "column 1/value 1/column 2/value 2/..."

Post Variables: 
    Required: "user", "pass", one key/value pair
    Optional: "data" >1 key/value pairs

CURL Example:
    curl --data "user=<user>&pass=<pass>&data=testNum/12345" /del

Errors: Same as 3.1.3