Work with Google Fusion Tables - JS and PHP
Summary: There was a task to create a high-load web application with extremely limited server resources.
Fusion Tables by Google was chosen as the data store.
What is described in the article: working with Fusion Tables from
a) Javascript - data retrieval only;
b) PHP (Zend Framework) - selection, addition and update;
c) user interface from Google - creating tables and views.
What is not in the article:
a) load testing;
b) a detailed description of the application - the emphasis is precisely 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 is simply inserted, it looks beautiful, it does not load its own server, which is a convenient, well-documented (at least for v.2) API , at least not veryhigh speed is high , there are advantages. “Perhaps FT will also be pleasant,” I thought. True, did not check. And here is the result.
Recently, I had the idea to write some application for a 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 unnecessary trash to you. I will not describe the application here, I will not give the link until it is moderated. With a flash, I, unfortunately, "on you", so I wrote on a standard bundle of PHP + Js. Unusual in this application is that instead of the standard mySQL, a service from Google was chosen as storage - Fusion Tables. Why? Very simple - there are no guarantees of monetization. There is no free server capacity at hand - there is an old, poorly tuned developer server. I did not want to make any investments at the initial stage - let me remind you: the project was written just for the sake of interest. And while searching for an answer to a question on the Google Maps API, I came across an article about Fusion Tables .
')
Request data from the table will be directly javascript. Those. will have to make the table publicly available (at least for reading). I do not know how to obfustsirovat js so that a person who wants to hack the table could not get the login and password from the script.
Accordingly, there is no desire to give everyone the right to write to the table. Therefore, writing data to a table will occur only on the server side. Those. the user adds an ad, js sends the user-entered data to the server, a 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 via the FT user interface . The interface is intuitive, does not require any specific knowledge. Subjectively - something like MS Access.
You can also insert several rows into the table. Usually, when working with mySQL, I use phpMyAdmin for this.
In the video, an example of creating a simple table with three fields.
Yes, I will say right away: in FT, the names of the table are numbers. For example, for the created table it is 596524. “Data” is just an alias, it cannot be used in queries.
I’ll go straight ahead: for simple tables, the id field (primary autoincrement-key) is not required; each table will have a “hidden” ROWID field. Creating an id makes sense only if you plan to do a JOIN on several tables. How to do it? In the top menu on the page of the table there is a button "Merge". The dialog that appears will allow 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 everyone. Only the author can change / add data.
The table is ready. Here is a link to it .
On my server almost html-page and js file is stored. When the page loads, the data to display (a list of ads) is downloaded directly from Google servers.
The data from the table is displayed using the visualization module. Taken from here .
To do this, add the link to javascript - www.google.com/jsapi in the heading index.html, and in the file default.js
You can read more about loading Google API modules in Javascript here .
In addition, you need to perform a query to the table and specify the function - the response handler.
UPD: As dkukushkin noted, when using IE (up to the 9th) when there is Cyrillic in the sample (certainly 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 through, and everything immediately became beautiful. Although I was planning a long battle - working with headlines for example , but Ie this time surprised and displayed the Cyrillic alphabet normally. The format of the received data is slightly different from what was used when using Vizualization, so the displayData function should be changed too. But there is nothing terrible, a normal JSON object is returned, which has a table parameter containing 2 arrays of cols and rows - everything is simple and clear.
As it was already written 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 were added to FT).
And finally, the output function on the page
JsFiddle example
Now a fly in the ointment. FT uses SQL query language. You can read more here . But! This language has several unexpected limitations -for example, there is no OR operator under the conditions of the SELECT command (appeared, see UPD2) (and it is not even possible to replace it with something like (a | b) =! (! A &! B), since there is no operator NOT), even created a proposal for this). Although there are built-in functions for working with coordinates. FT generally aims to support maps from Google.
In order to avoid caching requests (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 to the query condition something like “AND name not equal to 234567”, where 234567 is a random number (see .Math.getRandomInt () ).
In a good way, of course, you would need to write your own class to send requests and process responses. If you look closely - there is nothing difficult. 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 raw code, which is embarrassing to upload here. And just today I stumbled upon a wonderful topic from trurl123 and realized that I still had to study MVC in js and, perhaps, give up on my own groundwork.
When I took up this part of the work - nothing foreshadowed trouble. Zend Framework contains a whole API for working with services from Google . But not everything turned out to be so rosy. In general, the class for working with FT had to be written by myself, taking Zend_Gdata_Base as a basis and taking advantage of a non-working solution under Drupal .
The file with the class can be downloaded here . I am ashamed to admit that the file does not conform to the Zend standard, it contains 3 classes, it is poorly documented. But it works.
In order to use it, you need to include the file itself, as well as Zend / Gdata / ClientLogin.php.
Example:
If hands reach, I will bring the file to my mind and upload it to Zend Proposal .
One more thing: 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 GET specification, you can only perform a SELECT query ( http://code.google.com/intl/ru/apis/fusiontables/docs/developers_guide.html#Updating "... To update a row, send an authenticated POST request ... ")
That's all. Anystones notes are welcome. Thank.
PS: and of course, I understand that there is no point in keeping a commercial project on ball services, but if my application becomes commercial (i.e., if it has a sufficient number of users and I figure out how to make money on them), I will immediately migrate to own server. And at the moment I have:
- a fairly beautiful and fast-running application with a small initial cost;
- the belief that everything will work just as quickly and under load.
PPS: Yes, I hope that the code highlighter is only buggy when previewing
References:
Fusion Tables API - http://code.google.com/intl/ru/apis/fusiontables/docs/developers_guide.html
Fusion Tables SQL syntax - http://code.google.com/intl/ru/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 a holiday of some kind! I renewed proposal tonight and now I have an analogue of OR! I myself have not tested it yet, but I hope everything will be ok.
Fusion Tables by Google was chosen as the data store.
What is described in the article: working with Fusion Tables from
a) Javascript - data retrieval only;
b) PHP (Zend Framework) - selection, addition and update;
c) user interface from Google - creating tables and views.
What is not in the article:
a) load testing;
b) a detailed description of the application - the emphasis is precisely 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 is simply inserted, it looks beautiful, it does not load its own server, which is a convenient, well-documented (at least for v.2) API , at least not very
Recently, I had the idea to write some application for a 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 unnecessary trash to you. I will not describe the application here, I will not give the link until it is moderated. With a flash, I, unfortunately, "on you", so I wrote on a standard bundle of PHP + Js. Unusual in this application is that instead of the standard mySQL, a service from Google was chosen as storage - Fusion Tables. Why? Very simple - there are no guarantees of monetization. There is no free server capacity at hand - there is an old, poorly tuned developer server. I did not want to make any investments at the initial stage - let me remind you: the project was written just for the sake of interest. And while searching for an answer to a question on the Google Maps API, I came across an article about Fusion Tables .
')
A little about access rights.
Request data from the table will be directly javascript. Those. will have to make the table publicly available (at least for reading). I do not know how to obfustsirovat js so that a person who wants to hack the table could not get the login and password from the script.
Accordingly, there is no desire to give everyone the right to write to the table. Therefore, writing data to a table will occur only on the server side. Those. the user adds an ad, js sends the user-entered data to the server, a 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 via the FT user interface . The interface is intuitive, does not require any specific knowledge. Subjectively - something like MS Access.
You can also insert several rows into the table. Usually, when working with mySQL, I use phpMyAdmin for this.
In the video, an example of creating a simple table with three fields.
Yes, I will say right away: in FT, the names of the table are numbers. For example, for the created table it is 596524. “Data” is just an alias, it cannot be used in queries.
I’ll go straight ahead: for simple tables, the id field (primary autoincrement-key) is not required; each table will have a “hidden” ROWID field. Creating an id makes sense only if you plan to do a JOIN on several tables. How to do it? In the top menu on the page of the table there is a button "Merge". The dialog that appears will allow 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 everyone. Only the author can change / add data.
The table is ready. Here is a link to it .
Step 2 - Data Mapping - JavaScript
On my server almost html-page and js file is stored. When the page loads, the data to display (a list of ads) is downloaded directly from Google servers.
The data from the table is displayed using the visualization module. Taken from here .
To do this, add the link to javascript - www.google.com/jsapi in the heading index.html, and in the file default.js
google.load('visualization', '1'); You can read more about loading Google API modules in Javascript here .
In addition, you need to perform a query to 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) when there is Cyrillic in the sample (certainly 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 through, and everything immediately became beautiful. Although I was planning a long battle - working with headlines for example , but Ie this time surprised and displayed the Cyrillic alphabet normally. The format of the received data is slightly different from what was used when using Vizualization, so the displayData function should be changed too. But there is nothing terrible, a normal JSON object is returned, which has a table parameter containing 2 arrays of cols and rows - everything is simple and clear.
As it was already written 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 were 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 = "<b>"; for(i = 0; i < numCols; i++) { fusiontabledata += response.getDataTable().getColumnLabel(i) + ","; } fusiontabledata += "</b><br />"; for(i = 0; i < numRows; i++) { for(j = 0; j < numCols; j++) { fusiontabledata += response.getDataTable().getValue(i, j) + ", "; } fusiontabledata += "<br />"; } // document.getElementById('echoer').innerHTML = fusiontabledata; } JsFiddle example
Now a fly in the ointment. FT uses SQL query language. You can read more here . But! This language has several unexpected limitations -
In order to avoid caching requests (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 to the query condition something like “AND name not equal to 234567”, where 234567 is a random number (see .Math.getRandomInt () ).
In a good way, of course, you would need to write your own class to send requests and process responses. If you look closely - there is nothing difficult. 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 raw code, which is embarrassing to upload here. And just today I stumbled upon a wonderful topic from trurl123 and realized that I still had to study MVC in js and, perhaps, give up on my own groundwork.
Step 3 - Add Data - PHP + Zend Framework
When I took up this part of the work - nothing foreshadowed trouble. Zend Framework contains a whole API for working with services from Google . But not everything turned out to be so rosy. In general, the class for working with FT had to be written by myself, taking Zend_Gdata_Base as a basis and taking advantage of a non-working solution under Drupal .
The file with the class can be downloaded here . I am ashamed to admit that the file does not conform to the Zend standard, it contains 3 classes, it is poorly documented. But it works.
In order to use it, you need to include 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 hands reach, I will bring the file to my mind and upload it to Zend Proposal .
One more thing: 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 GET specification, you can only perform a SELECT query ( http://code.google.com/intl/ru/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 there is no point in keeping a commercial project on ball services, but if my application becomes commercial (i.e., if it has a sufficient number of users and I figure out how to make money on them), I will immediately migrate to own server. And at the moment I have:
- a fairly beautiful and fast-running application with a small initial cost;
- the belief that everything will work just as quickly and under load.
PPS: Yes, I hope that the code highlighter is only buggy when previewing
References:
Fusion Tables API - http://code.google.com/intl/ru/apis/fusiontables/docs/developers_guide.html
Fusion Tables SQL syntax - http://code.google.com/intl/ru/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 a holiday of some kind! I renewed proposal tonight and now I have an analogue of OR! I myself have not tested it yet, but I hope everything will be ok.
Source: https://habr.com/ru/post/116035/
All Articles