Upvise Client Library: Query Class (.NET)

Constructor & Login static string login(email, password) static string guid(str) Query(token) long lastServerDate
Select Operation JSONObject[] select(table, where) JSONObject[] selectSince(table, where, sinceDate) JSONObject[] selectArchivedOrDeletedSince(table, where, sinceDate) JSONObject selectId(table, id)
Insert / Update / Delete Operations void insert(table, values) void updateId(table, id, values) void deleteId(table, id) 		Batch Operation void beginBatch() void commitBatch()
File Operation File[] selectFiles(linkedtable, linkedid) byte[] downloadFile(id) void insertFile(fileinfo, byte[] content)
Archive & Restore Operations JSONObject[] selectDeleted(table, where) JSONObject[] selectArchived(table, where) void restoreId(table, id) void archiveId(table, id)

Download Upvise Client Library

Overview

The Query class of the Upvise Client Library allows you to make direct calls to the UpviseJS Cloud Database, the same way the native app does, but from any client program. runnning on a desktop or server machine.

They are 2 Query classes in Upvise : the .NET/Java version, part of the Upvise Client Library (the one we are talking about) and a Javascript version to build mobile+web applications
Note:

Requirements and Setup

Obtaining a token

using UpviseClient;
...
String token = null;
try {
	token = Query.Login("youremail", "yourpassword");
} catch (UnknownHostException e) {
	System.out.println("No Internet Connection or Upvise Server unavailable");
	return;
} catch (Exception e) {
	System.out.println(e.getMessage());
	return;
}
		
if (token == null) {
	System.out.println("token null : bad email or password");
	return;
}

Selecting records

Creates an instance of the Query class, the token string parameter comes from a previous call to login(email, password) static method
Use the select(table, where) method of the Query class to retrieve records mathing a set of conditions on a specific table.

Java 
Query query = new Query(token);
String where = "status=1 AND date>=" + new Date().getTime();
JSONbject[] records = query.select("myapp.mytable", where);

foreach (JSON record in records) {
    Console.WriteLine(record.getString("id"));
    Console.WriteLine(record.getString("name"));
}

Selecting new or modified records since a given date

If you query the Upvise Cloud on a regular basis, you should query the modified or newly created records since the last time you call the samy query for maxiumn performance. Use selectSince(table, where, sinceDate) method. The sinceDate value should come from the last query.lastServerDate value obtained after the previous call to selectSince. You should save persistantly and update the last sinceDate value.

Java 
Query query = new Query(token);
String where = "status=1 AND date>=" + new Date().getTime();

// This returns all records because sinceDate = 0
long sinceDate = 0;
JSONbject[] records = query.selectSince("myapp.mytable", where, sinceDate);
Console.WriteLine("Number of records: " + records.length);

// Store the date we did the last selectQuery
// In a real situation, this value would be store persistantly and reloaded
    sinceDate = query.lastServerDate;

// Perform the same query again immediately, it should return no records
records = query.selectSince("myapp.mytable", where, sinceDate);
Console.WriteLine("New reocrds since last select: " + records.length);

Inserting or replacing a record

JSONObject values = new JSONObject();
values.put("id", "56754");
values.put("name", "My Task");
values.put("status", "0");
      
Query query = new Query(token);
query.insert("myapp.mytable", values);

Updating one record

Use updateId(table, id, values) method

Query query = new Query(token);
JSONObject values = new JSONObject();
values.put("name", "Publish the web site");
values.put("status", 0);
values.putDate("duedate", Date.now.AddDays(2));
query.updateId("Tasks.tasks", "56754", values);

Deleting one record

Query query = new Query(token);
query.deleteId("myapp.mytable", "56754");
Batch Operation 
// Create a bunch of tasks using Batch operation
query.beginBatch();
for (int i = 0; i < 10; i ++) {
    JSONObject task = new JSONObject();
    task.put("id", "TASKID_" + i);
    task.put("name", "My Task " + i);
    task.putDate("duedate", DateTime.Now.AddDays(2));
    query.insert("Tasks.tasks", task);
}
// Execute one batch HTTPS call for all insert requests
query.commitBatch();

File Operation

Select & download all files linked to a given record C# 
    using UpviseClient;
    ....
    string recordId = "aRecordId"; // obtain actual value from a previous database call
    
    Query fileQuery = new Query(token);
    JSONOject where = new JSONObject();
    FileInfo[] list = query.selectFiles(null, "aRecordId");
    foreach(FileInfo info in list) {
        byte[] content = query.downloadFile(info.id);
        Files.writeAllBytes(@"c:\\temp\\" + info.name, content);
    }
Upload a new File linked to an existing Database record C# 
    using UpviseClient;
    ....
    string recordId = "aRecordId"; // obtain actual value from a previous database call
    
    Query query = new Query(token);
    FileInfo fileInfo = new FileInfo();
    fileInfo.id= "TESTFILE1";
    fileInfo.name = "testFile.pdf";
    fileInfo.mime = "application/pdf";
    fileInfo.linkedid = recordId;
    byte[] content = File.ReadAllBytes(@"c:\\temp\testFile.pdf");
 
    query.uploadFile(fileInfo, content);

static string Login(string email, string password)

Performs a login into the Upvise account identified by its email and password.

Query(String token) Constructor

Creates an instance of the Query class, the token string parameter comes from a previous call to Query.login(email, password) static method.

JSONObject[] select(string table, string where)

Performs a select query on the specified table.

JSONObject[] selectSince(string table, string where, long sinceDate)

Performs a select query on the specified table but only returns records created or modified after the sinceDate.

JSONObject[] selectArchivedOrDeletedSince(string table, string where, long sinceDate)

Performs a select query on the specified table but only returns records archived or deleted after the sinceDate.

JSONObject selectId(string table, string id)

Return the record identified by its table and primary key id

void insert(string table, JSONObject values)

Inserts a new record into the table.

void updateId(string table, string id, JSONObject values)

Updates one record identified by its table and primary key id with values

void deleteId(string table, string id)

Delete one record identified by its table and primary key

void beginBatch()

Start a new batch operation.

void commitBatch()

Commit the batch operation and peform a unique HTTPS web service call to execute all previous insert, update and delete queries from beginBatch()

long lastServerDate

Readonly value containing the server date stamp

JSONObject[] selectDeleted(string table, string where)

Performs a select query on the specified table returning deleted records

JSONObject[] selectArchived(string table, string where)

Performs a select query on the specified table returning archived records

void restoreId(string table, string id)

Restores one previously deleted record identified by its table and primary key

void archiveId(string table, string id)

Archives one record identified by its table and primary key

File[] selectFiles(string linkedtable, string linkedid)

Returns a list of files attached to the record identified by its table (linkedtable) and id (linkedid)

byte[] downloadFile(string id)

Returns a the file content for an given file id primary key

void insertfile(File file, byte[] content)

insert a new file and link it to an existing record

    using UpviseClient;
    ....
    string jobid = "aRecordId"; // obtain actual value from a previous database call
    
    Query query = new Query(token);
    File file = new File();
    file.name = "testFile.pdf";
    file.mime = "application/pdf";
    file.linkedid = jobid;
    file.linkedtable = "jobs.jobs";

    byte[] content = System.IO.File.ReadAllBytes(@"c:\\temp\testFile.pdf");
    query.uploadFile(file, content);