Ever wanted to have an easy way to get the most recent entry for any given key on a massive database? I ran into this problem a few days ago at work and I wanted to attempt to explain how I solved it.
We have a large database that contains the lifetime history of our trucks GPS locations. The data in these tables is entered once every 10 seconds per truck, given the fact that we have about 30 trucks on average there are about 31536000 entries into this table per year and we have been running this program for several years now. We need a quick way to get the most recent location for every truck on the fleet regardless of whether it was last reported 10 seconds ago or 10 days ago.
For a while we had created a simple SQL query that returned the MAX(date) record for each given truck and this seemed to work all right. That is until yesterday evening, at that time the query that I just mentioned was taking somewhere in the realm of 10-15 minutes to execute, just long enough for our program to time out. Our table now contains somewhere in the realm of two billion records and this query is just not efficient enough. We attempted to optimize the query and gained some performance but not nearly enough to make a sustainable difference in the future.
The program that relies on this query is a real time monitoring system and we cannot sit there and wait for this to process during several minutes. An idea I had was to intercept the incoming stream and tag it as “most-recent” before insertion thus allowing a simple query such as “SELECT * FROM TABLE WHERE MOST-RECENT=true” would work. The challenge with this approach was, the interception of the data. We knew that we could write a simple database trigger, but had no idea if would allow us to modify the incoming data stream.
Most of the database triggers happen after insertion, or update so this may have posed a problem. However it appears that Microsoft had thought about this for us and gave us “Instead of Insert” triggers. This triggers take the incoming data and allow you to do something with it other than actually inserting it into the database. We decided to take the incoming data, update the table to clear the “most-current” flag and then insert the data while updating the current records most-current flag. See the implementation below, we went from a 10 minute query to a milliseconds long query. And the overhead placed on the insert hasn’t posed a problem for the amount of data we receive.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jose C Gomez
-- Create date: 5/10/2010
-- Description: Trigger that allows us to keep track of the Vehicles most recent location
-- =============================================
CREATE TRIGGER InsteadofInsert
ON dbo.current_location
INSTEAD OF INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
--CLEAR THE OLDER MOST-RECENT FLAG
UPDATE current_location
SET most_current=0
WHERE most_current =1 AND Device_ID in (SELECT Device_ID from Inserted);
--INSERT THE RECORD WITH THE MOST-RECENT FLAF SET TO TRUE (1)
INSERT INTO current_location
SELECT Device_ID, Lat, Lon, date, ip_address, direction, speed ,1
FROM Inserted
END
GO
Tags: database, microsoft, performance, query, SQL, triggers
As a follow up to my earlier post, I would like to make a jar library available that should allow you to access any RESTFull service that uses JSON. Simply import this jar file into your project and you should be good to go.
Please note that the attached jar file contains a compiled copy of the GSON library available at http://code.google.com/p/google-gson/.
Click here to download the jar file.
Once you have downloaded the above file and added it to your project you may use the following code to access a JSON web service.
The example shown here returns a collection of JSON objects in the form
[
{"alertid":"1","alerttext":"This is test","alertdate":"2010-02-11 09:03:40"},
{"alertid":"2","alerttext":"Another alert","alertdate":"2010-02-11 09:11:04"},
{"alertid":"3","alerttext":"This is third test","alertdate":"2010-02-11 11:00:57"},
{"alertid":"4","alerttext":"message from starbucks","alertdate":"2010-02-11 18:00:22"}]
The class I implemented to hold these object is as follows
public class alerts {
public int alertid;
public String alerttext;
public String alertdate;
@Override
public String toString()
{
return "Alert ID: "+alertid+ " Alert Text: "+alerttext+ " Alert Date: "+alertdate;
}
}
With the above class implemented, we can used the provided library to access the web-service available at http://www.sumasoftware.com/alerts/GetAlerts.php and parse its JSON repose back into our above mentioned java class.
import josecgomez.com.android.webservices.json.WebService;
//Instantiate your web service call
WebService webService = new WebService("http://www.sumasoftware.com/alerts/GetAlerts.php");
//Pass the parameters needed to the service
Map<String, String> params = new HashMap<String, String>();
params.put("var","");
//Please note that this service doesn't have any required parameters and I am only passing these as an example
params.put("firstName", "yName");
params.put("isActive", false);
//If you need to pass a JSON object as a parameter you may do so as follows
params.put("requiredObject", WebService.Object(myObject));
//Using GET
String response = webService.webGet("", params);
//You may pass it a method name if there is one as such
//String response = webService.webGet("METHODNAME", params);
//To retrieve an collection of objects
Type collectionType = new TypeToken<List<alerts>>(){}.getType();
List<alerts> lst= new Gson().fromJson(response, collectionType);
//Note that the above object alerts has to match the structure of our JSON object, please see the beginning of the post for an example.
//To retrieve a single item instead of a collection
alerts alert = new Gson().fromJson(response, alerts.class);
//To use POST instead of get
webService.webInvoke("", params);
I hope this helps, feel free to use this library anywhere you want, the source code for it is available in an earlier post so if you’d like to modify it feel free to do so.
Tags: Android, JSON, RESTFull, Web Services
Following up to my last post, I would like to show you in detail how to get our custom List to be shown in a ListView follow along.
The first thing we need to do is create out layout files, I have for this example created two layout files, one called main and one called listitems the code is below.
main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
>
<ListView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:id="@+id/lstText"
/>
</LinearLayout>
listitems.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="fill_parent" android:layout_height="fill_parent"> <LinearLayout android:orientation="vertical" android:layout_width="0dip" android:layout_weight="1" android:layout_height="fill_parent"> <TextView android:layout_width="fill_parent" android:layout_height="wrap_content" android:id="@+id/txtAlertText" /> <TextView android:layout_width="fill_parent" android:layout_height="wrap_content" android:id="@+id/txtAlertDate" /> </LinearLayout> </LinearLayout>
Once you’ve done this you need to create an new ArrayAdapter class specific to your object as such
package josecgomez.com.android.dev.webservice;
import java.util.List;
import josecgomez.com.android.dev.webservice.objects.alerts;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.LinearLayout;
import android.widget.TextView;
public class AlertsAdapter extends ArrayAdapter<alerts> {
int resource;
String response;
Context context;
//Initialize adapter
public AlertsAdapter(Context context, int resource, List<alerts> items) {
super(context, resource, items);
this.resource=resource;
}
@Override
public View getView(int position, View convertView, ViewGroup parent)
{
LinearLayout alertView;
//Get the current alert object
alerts al = getItem(position);
//Inflate the view
if(convertView==null)
{
alertView = new LinearLayout(getContext());
String inflater = Context.LAYOUT_INFLATER_SERVICE;
LayoutInflater vi;
vi = (LayoutInflater)getContext().getSystemService(inflater);
vi.inflate(resource, alertView, true);
}
else
{
alertView = (LinearLayout) convertView;
}
//Get the text boxes from the listitem.xml file
TextView alertText =(TextView)alertView.findViewById(R.id.txtAlertText);
TextView alertDate =(TextView)alertView.findViewById(R.id.txtAlertDate);
//Assign the appropriate data from our alert object above
alertText.setText(al.alerttext);
alertDate.setText(al.alertdate);
return alertView;
}
}
Then on your main activity you may add this items to the list as shown below. Please keep in mind that this tutorial assumes you’ve correctly implemented my web service calls in a previous post.
package josecgomez.com.android.dev.webservice;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import josecgomez.com.android.dev.webservice.objects.alerts;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.ListView;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
public class main extends Activity {
/** Called when the activity is first created. */
//ListView that will hold our items references back to main.xml
ListView lstTest;
//Array Adapter that will hold our ArrayList and display the items on the ListView
AlertsAdapter arrayAdapter;
//List that will host our items and allow us to modify that array adapter
ArrayList<alerts> alrts=null;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
//Initialize ListView
lstTest= (ListView)findViewById(R.id.lstText);
//Initialize our ArrayList
alrts = new ArrayList<alerts>();
//Initialize our array adapter notice how it references the listitems.xml layout
arrayAdapter = new AlertsAdapter(main.this, R.layout.listitems,alrts);
//Set the above adapter as the adapter of choice for our list
lstTest.setAdapter(arrayAdapter);
//Instantiate the Web Service Class with he URL of the web service not that you must pass
WebService webService = new WebService("http://www.sumasoftware.com/alerts/GetAlerts.php");
//Pass the parameters if needed , if not then pass dummy one as follows
Map<String, String> params = new HashMap<String, String>();
params.put("var", "");
//Get JSON response from server the "" are where the method name would normally go if needed example
// webService.webGet("getMoreAllerts", params);
String response = webService.webGet("", params);
try
{
//Parse Response into our object
Type collectionType = new TypeToken<ArrayList<alerts>>(){}.getType();
//JSON expects an list so can't use our ArrayList from the lstart
List<alerts> lst= new Gson().fromJson(response, collectionType);
//Now that we have that list lets add it to the ArrayList which will hold our items.
for(alerts l : lst)
{
alrts.add(l);
}
//Since we've modified the arrayList we now need to notify the adapter that
//its data has changed so that it updates the UI
arrayAdapter.notifyDataSetChanged();
}
catch(Exception e)
{
Log.d("Error: ", e.getMessage());
}
}
}
I hope this helps
Tags: Android, JSON, ListView, Web Services
I just finished a huge project for school using the Google Android OS. The biggest hurdle I had to jump through we getting android to successfully talk to web services. I have put together a set of classes and procedures to do so that make it easy and reliable. I have taken some code from here and there and adapted my own. It was a while ago so I don’t recall where I found it all. For this example I am going to be using the web service http://www.sumasoftware.com/alerts/GetAlerts.php to read the alerts.
The first thing you’ll need to download and add to your project is the google GSON library http://code.google.com/p/google-gson/downloads/list
Add the jar file to your android project as an external jar
Download the following WebService.java class in order to interact with the web service and add it to your project
package josecgomez.com.android.dev.webservice;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.util.Map;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.params.ClientPNames;
import org.apache.http.client.params.CookiePolicy;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.params.BasicHttpParams;
import org.apache.http.params.HttpConnectionParams;
import org.apache.http.params.HttpParams;
import org.apache.http.protocol.BasicHttpContext;
import org.apache.http.protocol.HttpContext;
import org.apache.http.util.EntityUtils;
import org.json.JSONException;
import org.json.JSONObject;
import android.util.Log;
import com.google.gson.Gson;
public class WebService{
DefaultHttpClient httpClient;
HttpContext localContext;
private String ret;
HttpResponse response = null;
HttpPost httpPost = null;
HttpGet httpGet = null;
String webServiceUrl;
//The serviceName should be the name of the Service you are going to be using.
public WebService(String serviceName){
HttpParams myParams = new BasicHttpParams();
HttpConnectionParams.setConnectionTimeout(myParams, 10000);
HttpConnectionParams.setSoTimeout(myParams, 10000);
httpClient = new DefaultHttpClient(myParams);
localContext = new BasicHttpContext();
webServiceUrl = serviceName;
}
//Use this method to do a HttpPost\WebInvoke on a Web Service
public String webInvoke(String methodName, Map<String, Object> params) {
JSONObject jsonObject = new JSONObject();
for (Map.Entry<String, Object> param : params.entrySet()){
try {
jsonObject.put(param.getKey(), param.getValue());
}
catch (JSONException e) {
Log.e("Groshie", "JSONException : "+e);
}
}
return webInvoke(methodName, jsonObject.toString(), "application/json");
}
private String webInvoke(String methodName, String data, String contentType) {
ret = null;
httpClient.getParams().setParameter(ClientPNames.COOKIE_POLICY, CookiePolicy.RFC_2109);
httpPost = new HttpPost(webServiceUrl + methodName);
response = null;
StringEntity tmp = null;
//httpPost.setHeader("User-Agent", "SET YOUR USER AGENT STRING HERE");
httpPost.setHeader("Accept",
"text/html,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5");
if (contentType != null) {
httpPost.setHeader("Content-Type", contentType);
} else {
httpPost.setHeader("Content-Type", "application/x-www-form-urlencoded");
}
try {
tmp = new StringEntity(data,"UTF-8");
} catch (UnsupportedEncodingException e) {
Log.e("Groshie", "HttpUtils : UnsupportedEncodingException : "+e);
}
httpPost.setEntity(tmp);
Log.d("Groshie", webServiceUrl + "?" + data);
try {
response = httpClient.execute(httpPost,localContext);
if (response != null) {
ret = EntityUtils.toString(response.getEntity());
}
} catch (Exception e) {
Log.e("Groshie", "HttpUtils: " + e);
}
return ret;
}
//Use this method to do a HttpGet/WebGet on the web service
public String webGet(String methodName, Map<String, String> params) {
String getUrl = webServiceUrl + methodName;
int i = 0;
for (Map.Entry<String, String> param : params.entrySet())
{
if(i == 0){
getUrl += "?";
}
else{
getUrl += "&";
}
try {
getUrl += param.getKey() + "=" + URLEncoder.encode(param.getValue(),"UTF-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
i++;
}
httpGet = new HttpGet(getUrl);
Log.e("WebGetURL: ",getUrl);
try {
response = httpClient.execute(httpGet);
} catch (Exception e) {
Log.e("Groshie:", e.getMessage());
}
// we assume that the response body contains the error message
try {
ret = EntityUtils.toString(response.getEntity());
} catch (IOException e) {
Log.e("Groshie:", e.getMessage());
}
return ret;
}
public static JSONObject Object(Object o){
try {
return new JSONObject(new Gson().toJson(o));
} catch (JSONException e) {
e.printStackTrace();
}
return null;
}
public InputStream getHttpStream(String urlString) throws IOException {
InputStream in = null;
int response = -1;
URL url = new URL(urlString);
URLConnection conn = url.openConnection();
if (!(conn instanceof HttpURLConnection))
throw new IOException("Not an HTTP connection");
try{
HttpURLConnection httpConn = (HttpURLConnection) conn;
httpConn.setAllowUserInteraction(false);
httpConn.setInstanceFollowRedirects(true);
httpConn.setRequestMethod("GET");
httpConn.connect();
response = httpConn.getResponseCode();
if (response == HttpURLConnection.HTTP_OK) {
in = httpConn.getInputStream();
}
} catch (Exception e) {
throw new IOException("Error connecting");
} // end try-catch
return in;
}
public void clearCookies() {
httpClient.getCookieStore().clear();
}
public void abort() {
try {
if (httpClient != null) {
System.out.println("Abort.");
httpPost.abort();
}
} catch (Exception e) {
System.out.println("Your App Name Here" + e);
}
}
}
Based on the structure of your JSON file develop a class in your project to support the structure. For example for the above mentioned service I developed this class.
The JSON returned has this structure {“alertid”:”1″,”alerttext”:”This is test”,”alertdate”:”2010-02-11 09:03:40″}
package josecgomez.com.android.dev.webservice.objects;
public class alerts {
public int alertid;
public String alerttext;
public String alertdate;
@Override
public String toString()
{
return "Alert ID: "+alertid+ " Alert Text: "+alerttext+ " Alert Date: "+alertdate;
}
}
Once you’ve done this in your android activity you may execute the following code to access the web service
// Instantiate the Web Service Class with he URL of the web service not that you must pass
WebService webService = new WebService("http://www.sumasoftware.com/alerts/GetAlerts.php");
//Pass the parameters if needed , if not then pass dummy one as follows
Map<String, String> params = new HashMap<String, String>();
params.put("var", "");
//Get JSON response from server the "" are where the method name would normally go if needed example
// webService.webGet("getMoreAllerts", params);
String response = webService.webGet("", params);
try
{
//Parse Response into our object
Type collectionType = new TypeToken<List<alerts>>(){}.getType();
List<alerts> alrt = new Gson().fromJson(response, collectionType);
}
catch(Exception e)
{
Log.d("Error: ", e.getMessage());
}
Please note that the above code is for accessing collection of items, if you are attempting to access a single item there should be a slight modification to the code as follows
/* Replace
Type collectionType = new TypeToken<List<alerts>>(){}.getType();
List<alerts> alrt = new Gson().fromJson(response, collectionType);
with
*/
alerts alert = new Gson().fromJson(response, alerts.class);
The above method uses GET if you need to INVOKE POST there should be a slight modification to the above code as follows. I hope this helps.
/* Replace
String response = webService.webGet("", params);
with
*/
webService.webInvoke("", params);
Tags: Android, google, Gson, JSON, Web Services
This morning I woke up to find my Facebook page was completely re-designed. Over all it looks like an improvement except it will take me 10 days to figure out where everything is again. Here are some pics 
Here is the help page that tells you more about it http://www.facebook.com/sitetour/homepage.php
Tags: facebook, Redesign, screen, social network
This morning when I woke up Facebook suggested I upgrade my browser to IE 8, or Chrome or Firefox. The funny thing is that I was running the latest version of Chrome at that time. I tried opening IE 8 and Firefox and on both it gave me the same suggestion. Facebook I believe you had too much to drink 
Merry Christmas~
I’ve been busy at work lately one of the projects assigned to me is to create an application to redact and edit emails that will be sent to customers. This provided a great opportunity for me to get familiar with creating a custom control in C#. I needed a way to create rich content and although C# has the rich text control it provides everything in RTF which is difficult to work with and hard to embed. So I got to work on my very own WYSIWYG control that produces HTML. So I am making it open source and available to anyone have fun and enjoy if you make any cool modifications to it let me know. I would love to include spell check ability but I don’t have much time to work on it right now.
Download Here HTMLWYSIWYG
Sample Uses
private void button1_Click_1(object sender, EventArgs e)
{
//Gets the HTML Code generated by the control
Console.WriteLine( htmlwysiwyg1.getHTML())
//Getts the PLain Tex code generated by the control.
Console.WriteLine(htmlwysiwyg1.getPlainText());
}
private void frm_main_Load(object sender, EventArgs e)
{
//Sets the control to allow edits
htmlwysiwyg1.allowEdit(true)
//Loads the HTML into the control
htmlwysiwyg1.setHTML("<B>BOLD</B>");
//Loads aditional fonts into the control
htmlwysiwyg1.addFont("Cambria");
}
On July 7 google posted the following on their official blog.
It’s been an exciting nine months since we launched the Google Chrome browser. Already, over 30 million people use it regularly. We designed Google Chrome for people who live on the web — searching for information, checking email, catching up on the news, shopping or just staying in touch with friends. However, the operating systems that browsers run on were designed in an era where there was no web. So today, we’re announcing a new project that’s a natural extension of Google Chrome — the Google Chrome Operating System. It’s our attempt to re-think what operating systems should be.
It seems that the endless rumors about Google making an Operating System were not unfounded after all. After this announcement I went through the list of Google products, and I have found that basically Google’s model is simple. They take an existing product or function and create an improved version of it, in accordance with the company’s view. By making such a product the force the hand of the previous competitors, thus accelerating the evolution of said product and benefiting everyone.
Take Gmail for example, before Gmail came around all web based free email platforms had ridiculous restrictions 2-10 mb per account, no large attachments, poor POP and IMAP support etc… Then along comes Gmail and it blows all the caps and restrictions providing free unlimited space with POP and IMAP support excellent search and a snazzy interface. Immediately the competition reacts and now most of the web based email platforms support a wide range of features and unlimited space.
Google has done this with many of their products , Google Earth, Google News, Google Voice, the threat to buy the 700 mghz spectrum and many more. Google is playing a game and the thing is, that they are good at it. With this announcement about an OS they just threw the ball in Microsoft’s court. I guarantee you that the next version of “Windows” will take a lot from this.
