March 23, 2011 at 11:59Working with Google Fusion Tables - JS and PHP
Summary: The problem arose - to create a highly loaded web application with extremely limited server resources.
Google’s Fusion Tables was chosen as the data store.
What is described in the article: working with Fusion Tables from
a) Javascript - only fetching data;
b) PHP (Zend Framework) - selection, addition and updating;
c) the user interface from Google - the creation of tables and views.
What is not in the article:
a) load testing;
b) a detailed description of the application - the emphasis is on the use of Fusion Tables in their PHP + Js projects.
Google has a large number of different products. - without platitudes.
Only a lazy web programmer did not use Google maps in his projects. It simply plugs in, looks beautiful, does not load its own server, a convenient, well-documented (at least for v.2) API ,high at least not low speed - there are pluses. Maybe FT will be nice too, I thought. True, I did not check. And here is the result.
Recently, I had the idea to write some kind of application for the well-known social network - just as an experiment. After a little thought, the choice fell on a simple bulletin board - a place where you can offer someone the stuff you don't need. I will not describe the application here, I will not give the link until it is on moderation. I, unfortunately, “with you” with a flash, so I wrote on a standard bunch of PHP + Js. What is unusual in this application is that instead of the standard mySQL, the service from Google, Fusion Tables, was chosen as the storage. Why? Very simple - there are no guarantees of monetization. There are no free server capacities at hand - there is an old, poorly configured development server. I did not want to make any investments at the initial stage - let me remind you: the project was written just for fun.article about Fusion Tables .
Javascript will directly request data from the table. Those. you have to make the table public (at least for reading). I don’t know how js can be obfuscated so that a person who wants to break into a table cannot fetch a username and password from a script.
Accordingly, there is no desire to give everyone the right to write to the table. Therefore, data will be written to the table only on the server side. Those. the user adds an advertisement, js transfers the data entered by the user to the server, the php script picks them up, logs in to Google FT and inserts the data into the table.
I agree in advance with all the reproaches to the previous paragraph! I repeat - I just don’t know how to hide login / password in js.
So, let's go:
A table is created through the FT user interface . The interface is intuitive, does not require any specific knowledge. Subjectively - somewhat reminiscent of MS Access.
There you can insert several rows in the table. Usually, when working with mySQL, I use phpMyAdmin for this.
The video shows an example of creating a simple table with three fields.
Yes, I’ll say right away: in FT, table names are numbers. For example, for the created table it is 596524. “Data” is just an alias, it cannot be used in queries.
Immediately I’ll run ahead: I don’t need to create an id field (primary autoincrement key) for simple tables, each table will have a “hidden” ROWID field. Creating an id only makes sense if you plan on doing a JOIN across multiple tables. How to do it? In the top menu on the table page there is a button "Merge". The dialog that appears allows you to create something like a view (view in terms of mySQL).
Now you need to share the table. In order to receive data from js without authorization. The table will be read-only for all. Only the author can change / add data.
The table is ready. Here is a link to it .
On my almost server, the html page and js file are stored. When the page loads, the data to be displayed (list of ads) is downloaded directly from Google’s servers.
The data from the table is displayed using the visualization module. Taken from here .
To do this, add a link to javascript in the header index.html - www.google.com/jsapi , and in the file default.js the line
You can read more about loading Google API modules in Javascript here .
In addition, you need to query the table and specify the function - the response handler.
UPD: As dkukushkin noted , when using IE (up to the 9th), if the sample contains Cyrillic alphabet (probably not only!), The user sees "?" instead of characters. Therefore, we remove Google.vizualization and make a request on our own - praise jQuery, there is no need to write scary XMLHttpRequest with your hands
After that, the bug went away, and everything immediately became beautiful. Although I planned a long battle - working with headers for example like this , but ie this time surprised and displayed the Cyrillic alphabet normally. The format of the received data is slightly different from the one that was when using Vizualization, so the displayData function needs to be changed too. But there’s nothing wrong, a regular JSON object is returned, which has a table parameter containing 2 arrays of cols and rows - everything is simple and clear.
As already mentioned above - their IDs are used as table names in queries (by the way, you can see an interesting fact - on February 26 I created a table with ID = 505 xxx, and the data table created today received ID 596 524 - we can conclude: ~ 91 000 tables in less than a month have been added to FT).
And finally, the output function on the page
Example on jsFiddle
Now fly in the ointment. FT uses the SQL query language. Read more here . But! This language has several unexpected restrictions -for example, there is no OR operator in the conditions of the SELECT command (appeared, see UPD2) (and there is even no way to replace it with something like (a | b) =! (! A &! B), since there is no operator NOT), on this subject even created a proposal ). Although there are built-in functions for working with coordinates. FT generally aims to support maps from Google.
To avoid query caching (I did not understand exactly where they are cached - in the browser, visualization, or FT itself) on a real project, it makes sense to add something like “AND name not equal to 234567” to the query condition, where 234567 is a random number (see .Math.getRandomInt () ).
In a good way, of course, you would have to write your own class to send requests and process responses. If you look closely, there is nothing complicated. SELECT - sent by a GET request to a special address, the response from the server is received in JSON format. I did just that - I got a pretty crude code, which is embarrassing to post here. And literally today I came across a wonderful topic from trurl123 and realized that I still need to learn MVC in js and, possibly, abandon my developments.
When I took up this part of the work, there were no signs of trouble. Zend Framework contains a whole API for working with services from Google . But not everything turned out so rosy. In general, I had to write a class for working with FT myself, taking Zend_Gdata_Base as a basis and using an inoperative solution for Drupal .
The class file can be downloaded here . I am ashamed to admit, the file does not comply with the Zend standard, it contains 3 classes, poorly documented. But it works.
In order to use it, you need to connect the file itself, as well as Zend / Gdata / ClientLogin.php.
Example:
If my hands reach, I’ll bring the file to mind and upload it to Zend Proposal .
Another point: if there is no need to execute INSERT / UPDATE commands, you can use Zend_Base, for example, as described here . The fact is that according to the specification, only SELECT can be executed through the GET request ( http://code.google.com/intl/en/apis/fusiontables/docs/developers_guide.html#Updating "... To update a row, send an authenticated POST request ... ")
That's all. Anystones comments are welcome. Thanks.
PS: and of course, I understand that it makes no sense to keep a commercial project on ball services, but if my application becomes commercial (that is, if it has enough users and I’ll figure out how to make money from them), I’ll immediately migrate to own server. And at the moment I have:
- a pretty nice and fast-running application with a small initial outlay;
- I believe that everything will work just as fast and under stress.
PPS: Yes, I hope that code highlighter is buggy only when previewing
Links:
Fusion Tables API - http://code.google.com/intl/en/apis/fusiontables/docs/developers_guide.html
Fusion Tables SQL syntax - http: / /code.google.com/intl/en/apis/fusiontables/docs/developers_reference.html
Fusion Tables UI - http://tables.googlelabs.com/
Zend Gdata - http://framework.zend.com/manual/en/zend.gdata.html
PHP class for working with FT - http: // barahlo. semero.com/description/Zend_Gdata_Fusion.zip
UPD2: it's just some kind of holiday! Tonight updated the proposal and now there is an analogue of OR! I have not tested it myself yet, but I hope everything will be ok.
Google’s Fusion Tables was chosen as the data store.
What is described in the article: working with Fusion Tables from
a) Javascript - only fetching data;
b) PHP (Zend Framework) - selection, addition and updating;
c) the user interface from Google - the creation of tables and views.
What is not in the article:
a) load testing;
b) a detailed description of the application - the emphasis is on the use of Fusion Tables in their PHP + Js projects.
Only a lazy web programmer did not use Google maps in his projects. It simply plugs in, looks beautiful, does not load its own server, a convenient, well-documented (at least for v.2) API ,
Recently, I had the idea to write some kind of application for the well-known social network - just as an experiment. After a little thought, the choice fell on a simple bulletin board - a place where you can offer someone the stuff you don't need. I will not describe the application here, I will not give the link until it is on moderation. I, unfortunately, “with you” with a flash, so I wrote on a standard bunch of PHP + Js. What is unusual in this application is that instead of the standard mySQL, the service from Google, Fusion Tables, was chosen as the storage. Why? Very simple - there are no guarantees of monetization. There are no free server capacities at hand - there is an old, poorly configured development server. I did not want to make any investments at the initial stage - let me remind you: the project was written just for fun.article about Fusion Tables .
A little about access rights.
Javascript will directly request data from the table. Those. you have to make the table public (at least for reading). I don’t know how js can be obfuscated so that a person who wants to break into a table cannot fetch a username and password from a script.
Accordingly, there is no desire to give everyone the right to write to the table. Therefore, data will be written to the table only on the server side. Those. the user adds an advertisement, js transfers the data entered by the user to the server, the php script picks them up, logs in to Google FT and inserts the data into the table.
I agree in advance with all the reproaches to the previous paragraph! I repeat - I just don’t know how to hide login / password in js.
So, let's go:
Step 1 - Creating Tables
A table is created through the FT user interface . The interface is intuitive, does not require any specific knowledge. Subjectively - somewhat reminiscent of MS Access.
There you can insert several rows in the table. Usually, when working with mySQL, I use phpMyAdmin for this.
The video shows an example of creating a simple table with three fields.
Yes, I’ll say right away: in FT, table names are numbers. For example, for the created table it is 596524. “Data” is just an alias, it cannot be used in queries.
Immediately I’ll run ahead: I don’t need to create an id field (primary autoincrement key) for simple tables, each table will have a “hidden” ROWID field. Creating an id only makes sense if you plan on doing a JOIN across multiple tables. How to do it? In the top menu on the table page there is a button "Merge". The dialog that appears allows you to create something like a view (view in terms of mySQL).
Now you need to share the table. In order to receive data from js without authorization. The table will be read-only for all. Only the author can change / add data.
The table is ready. Here is a link to it .
Step 2 - Displaying Data - JavaScript
On my almost server, the html page and js file are stored. When the page loads, the data to be displayed (list of ads) is downloaded directly from Google’s servers.
The data from the table is displayed using the visualization module. Taken from here .
To do this, add a link to javascript in the header index.html - www.google.com/jsapi , and in the file default.js the line
google.load('visualization', '1');
You can read more about loading Google API modules in Javascript here .
In addition, you need to query the table and specify the function - the response handler.
//формирование запроса
var queryText = 'SELECT id, name FROM 596524;';
//url запроса
var reqUri = 'http://www.google.com/fusiontables/gvizdata?tq=' + encodeURIComponent(queryText);
//создаем объект visualization
var query = new google.visualization.Query(reqUri);
//посылаем запрос и указываем функцию - обработчик ответа
query.send(displayData);
UPD: As dkukushkin noted , when using IE (up to the 9th), if the sample contains Cyrillic alphabet (probably not only!), The user sees "?" instead of characters. Therefore, we remove Google.vizualization and make a request on our own - praise jQuery, there is no need to write scary XMLHttpRequest with your hands
var queryText = 'SELECT id, name FROM 596524;';
$.ajax({
url:'http://tables.googlelabs.com/api/query?sql='+queryText+'&jsonCallback=?',
type: 'GET',
success:function(resp){
displayData(resp);
},
dataType:'json'
});
After that, the bug went away, and everything immediately became beautiful. Although I planned a long battle - working with headers for example like this , but ie this time surprised and displayed the Cyrillic alphabet normally. The format of the received data is slightly different from the one that was when using Vizualization, so the displayData function needs to be changed too. But there’s nothing wrong, a regular JSON object is returned, which has a table parameter containing 2 arrays of cols and rows - everything is simple and clear.
As already mentioned above - their IDs are used as table names in queries (by the way, you can see an interesting fact - on February 26 I created a table with ID = 505 xxx, and the data table created today received ID 596 524 - we can conclude: ~ 91 000 tables in less than a month have been added to FT).
And finally, the output function on the page
function displayData(response) {
//более детальная информация об объекте response доступна здесь
//http://code.google.com/apis/visualization/documentation/reference.html#QueryResponse
numRows = response.getDataTable().getNumberOfRows();
numCols = response.getDataTable().getNumberOfColumns();
//собираем результат в строку
fusiontabledata = "";
for(i = 0; i < numCols; i++) {
fusiontabledata += response.getDataTable().getColumnLabel(i) + ",";
}
fusiontabledata += "
";
for(i = 0; i < numRows; i++) {
for(j = 0; j < numCols; j++) {
fusiontabledata += response.getDataTable().getValue(i, j) + ", ";
}
fusiontabledata += "
";
}
//вывод на странице
document.getElementById('echoer').innerHTML = fusiontabledata;
}Example on jsFiddle
Now fly in the ointment. FT uses the SQL query language. Read more here . But! This language has several unexpected restrictions -
To avoid query caching (I did not understand exactly where they are cached - in the browser, visualization, or FT itself) on a real project, it makes sense to add something like “AND name not equal to 234567” to the query condition, where 234567 is a random number (see .Math.getRandomInt () ).
In a good way, of course, you would have to write your own class to send requests and process responses. If you look closely, there is nothing complicated. SELECT - sent by a GET request to a special address, the response from the server is received in JSON format. I did just that - I got a pretty crude code, which is embarrassing to post here. And literally today I came across a wonderful topic from trurl123 and realized that I still need to learn MVC in js and, possibly, abandon my developments.
Step 3 - adding data - PHP + Zend Framework
When I took up this part of the work, there were no signs of trouble. Zend Framework contains a whole API for working with services from Google . But not everything turned out so rosy. In general, I had to write a class for working with FT myself, taking Zend_Gdata_Base as a basis and using an inoperative solution for Drupal .
The class file can be downloaded here . I am ashamed to admit, the file does not comply with the Zend standard, it contains 3 classes, poorly documented. But it works.
In order to use it, you need to connect the file itself, as well as Zend / Gdata / ClientLogin.php.
Example:
//авторизация (по крайней мере данные для авторизации указываются здесь)
$client = Zend_Gdata_ClientLogin::getHttpClient('your_login_here@gmail.com', 'your_pass_here', 'fusiontables');
//создание экземпляра класса
$base = new Zend_Gdata_Fusion($client);
//запрос на выборку
$sql = "SELECT ROWID FROM 596524 WHERE id = 1;";
$rowdata = $base->query($sql)->get_array();
print_r($rowdata);
//вставка строк - согласно API необходимо перечислить все столбцы таблицы
$newRowId = $base->insertRow('596524',array(
'id' => time(),
'name' => 'trird row',
'added' => date('n/j/y'),
) );
//обновление строки
$base->updateRow(
'596524', //ID таблицы
array('name' => 'new first row'), //ассоциативный массив значений
$rowdata[1][0] //ROWID полученный в запросе на выборку
);
If my hands reach, I’ll bring the file to mind and upload it to Zend Proposal .
Another point: if there is no need to execute INSERT / UPDATE commands, you can use Zend_Base, for example, as described here . The fact is that according to the specification, only SELECT can be executed through the GET request ( http://code.google.com/intl/en/apis/fusiontables/docs/developers_guide.html#Updating "... To update a row, send an authenticated POST request ... ")
That's all. Any
PS: and of course, I understand that it makes no sense to keep a commercial project on ball services, but if my application becomes commercial (that is, if it has enough users and I’ll figure out how to make money from them), I’ll immediately migrate to own server. And at the moment I have:
- a pretty nice and fast-running application with a small initial outlay;
- I believe that everything will work just as fast and under stress.
PPS: Yes, I hope that code highlighter is buggy only when previewing
Links:
Fusion Tables API - http://code.google.com/intl/en/apis/fusiontables/docs/developers_guide.html
Fusion Tables SQL syntax - http: / /code.google.com/intl/en/apis/fusiontables/docs/developers_reference.html
Fusion Tables UI - http://tables.googlelabs.com/
Zend Gdata - http://framework.zend.com/manual/en/zend.gdata.html
PHP class for working with FT - http: // barahlo. semero.com/description/Zend_Gdata_Fusion.zip
UPD2: it's just some kind of holiday! Tonight updated the proposal and now there is an analogue of OR! I have not tested it myself yet, but I hope everything will be ok.