存取google 的服務，都會用到
OAuth 的認證機制，所以使用
Google fusion tables當然也不例外，底下說明的使用的是OAuth 2.0 for Devices過程，一旦取得Access Token,就可以對Google fusion tables 執行新增資料、查詢資料、刪除資料等操作。
OAuth 2.0 for Devices的認證流程
注意:curl 必須支援HTTPS (安裝說明)
取得Authorization Code
root@raspberrypi:~# curl -d "client_id=530304002742-55mhfghci5o59tur1tgt183p38ceh08t.apps.googleusercontent.com&scope=https://www.googleapis.com/auth/fusiontables" https://accounts.google.com/o/oauth2/device/code
{
"device_code" : "ZMUM-BZYM4/b1grhomQLBtifJxKrATFJ-m8AoYV7tr3RfN1BLvOTlo",
"user_code" : "ZMUM-BZYM",
"verification_url" : "https://www.google.com/device",
"expires_in" : 1800,
"interval" : 5
}
The
user_code and
verification_url from the JSON object should be shown to your user. The idea is to ask the user to go to a browser, navigate to the verification_url URL, and enter the user_code. The user_code is case sensitive, so the user will need to enter the code exactly as it appears in the response.
=======================================
獲得access tokens
code為上一個請求所傳回的device code
root@raspberrypi:~# curl -d "client_id=530304002742-55mhfghci5o59tur1tgt183p38ceh08t.apps.googleusercontent.com&client_secret=xtogS1gi_MSl_7apvzfrsvmK&code=ZMUM-BZYM4/b1grhomQLBtifJxKrATFJ-m8AoYV7tr3RfN1BLvOTlo&grant_type=http://oauth.net/grant_type/device/1.0" https://www.googleapis.com/oauth2/v3/token
{
"access_token": "ya29.tAH13KudNYGAmG9rtdXLJjO_QNPjbJcvEkJEeG_C3MxqWh5T9sFbbrguWh77llT6JbVL",
"token_type": "Bearer",
"expires_in": 3600,
"refresh_token": "1/97_rdY40n2PusItQPVPENp5ECTZYqFvUobJZB09nOxlIgOrJDtdun6zK6XiATCKT"
}
==================================
#Access Token 過期時, 出現 401 "Invalid Credentials" 錯誤
root@raspberrypi:~# curl -H "Authorization: Bearer ya29.tAH13KudNYGAmG9rtdXLJjO_QNPjbJcvEkJEeG_C3MxqWh5T9sFbbrguWh77llT6JbVL" -d "sql=SELECT * FROM 1fV5mXuKgG5cCck1cAVQX0G7HVTjfdm1SqeYSdmXU WHERE number=43" "https://www.googleapis.com/fusiontables/v2/query"
{
"error": {
"errors": [
{
"domain": "global",
"reason": "authError",
"message": "Invalid Credentials",
"locationType": "header",
"location": "Authorization"
}
],
"code": 401,
"message": "Invalid Credentials"
}
}
=======================================
# Refresh Token
root@raspberrypi:~# curl -d "client_id=530304002742-55mhfghci5o59tur1tgt183p38ceh08t.apps.googleusercontent.com&client_secret=xtogS1gi_MSl_7apvzfrsvmK&refresh_token=1/97_rdY40n2PusItQPVPENp5ECTZYqFvUobJZB09nOxlIgOrJDtdun6zK6XiATCKT&grant_type=refresh_token" https://www.googleapis.com/oauth2/v3/token
{
"access_token": "ya29.tAHsfamfZ4CUSiMwXG0TztY9DYkcj3Zs988p9Wr_GrZz5vRbxkGHFoQiHxDMNsuEZf8-",
"token_type": "Bearer",
"expires_in": 3600
}
一旦取得Access Token並知道過期後如何更新Access Token, 接下接下來就是對Google fusion tables 執行新增資料、查詢資料、刪除資料對表格的操作。使用的方式使用HTTP POST，但必須在每一次的HTTPS請求，在Header中加入Access Token
#新增 "sql=INSERT INTO {table} (number,Address)VALUES(43,'IT robotics lab')"
root@raspberrypi:~# curl -H "Authorization: Bearer ya29.tAH13KudNYGAmG9rtdXLJjO_QNPjbJcvEkJEeG_C3MxqWh5T9sFbbrguWh77llT6JbVL" -d "sql=INSERT INTO 1fV5mXuKgG5cCck1cAVQX0G7HVTjfdm1SqeYSdmXU (number,Address)VALUES(43,'IT robotics lab')" "https://www.googleapis.com/fusiontables/v2/query"
{
"kind": "fusiontables#sqlresponse",
"columns": [
"rowid"
],
"rows": [
[
"5002"
]
]
}
==================================
#查詢 "sql=SELECT * FROM {table} WHERE number=43"
root@raspberrypi:~# curl -H "Authorization: Bearer ya29.tAH13KudNYGAmG9rtdXLJjO_QNPjbJcvEkJEeG_C3MxqWh5T9sFbbrguWh77llT6JbVL" -d "sql=SELECT * FROM 1fV5mXuKgG5cCck1cAVQX0G7HVTjfdm1SqeYSdmXU WHERE number=43" "https://www.googleapis.com/fusiontables/v2/query"
{
"kind": "fusiontables#sqlresponse",
"columns": [
"Date",
"Time",
"number",
"Location",
"Address"
],
"rows": [
[
"",
"",
"43",
"",
"IT robotics lab"
]
]
}
==================================
#查詢ROWID SELECT ROWID FROM {table} WHERE number=43
root@raspberrypi:~# curl -H "Authorization: Bearer ya29.tAHsfamfZ4CUSiMwXG0TztY9DYkcj3Zs988p9Wr_GrZz5vRbxkGHFoQiHxDMNsuEZf8-" -d "sql=SELECT ROWID FROM 1fV5mXuKgG5cCck1cAVQX0G7HVTjfdm1SqeYSdmXU WHERE number=43" "https://www.googleapis.com/fusiontables/v2/query"
{
"kind": "fusiontables#sqlresponse",
"columns": [
"rowid"
],
"rows": [
[
"5002"
]
]
}
==================================
#更新 sql=Update {table} Set Address='ittraining' WHERE ROWID='5002'
root@raspberrypi:~# curl -H "Authorization: Bearer ya29.tAH13KudNYGAmG9rtdXLJjO_QNPjbJcvEkJEeG_C3MxqWh5T9sFbbrguWh77llT6JbVL" -d "sql=Update 1fV5mXuKgG5cCck1cAVQX0G7HVTjfdm1SqeYSdmXU Set Address='ittraining' WHERE ROWID='5002'" "https://www.googleapis.com/fusiontables/v2/query"
{
"kind": "fusiontables#sqlresponse",
"columns": [
"affected_rows"
],
"rows": [
[
"1"
]
]
}
==================================
#再查詢一次
root@raspberrypi:~# curl -H "Authorization: Bearer ya29.tAH13KudNYGAmG9rtdXLJjO_QNPjbJcvEkJEeG_C3MxqWh5T9sFbbrguWh77llT6JbVL" -d "sql=SELECT * FROM 1fV5mXuKgG5cCck1cAVQX0G7HVTjfdm1SqeYSdmXU WHERE number=43" "https://www.googleapis.com/fusiontables/v2/query"
{
"kind": "fusiontables#sqlresponse",
"columns": [
"Date",
"Time",
"number",
"Location",
"Address"
],
"rows": [
[
"",
"",
"43",
"",
"ittraining"
]
]
}
#刪除 sql=Delete {table} WHERE ROWID='5002'
root@raspberrypi:~# curl -H "Authorization: Bearer ya29.tAHsfamfZ4CUSiMwXG0TztY9DYkcj3Zs988p9Wr_GrZz5vRbxkGHFoQiHxDMNsuEZf8-" -d "sql=Delete from 1fV5mXuKgG5cCck1cAVQX0G7HVTjfdm1SqeYSdmXU WHERE ROWID='5002'" "https://www.googleapis.com/fusiontables/v2/query"
{
"kind": "fusiontables#sqlresponse",
"columns": [
"affected_rows"
],
"rows": [
[
"1"
]
]
}
#確認該筆資料(number=43)已刪除
root@raspberrypi:~# curl -H "Authorization: Bearer ya29.tAHsfamfZ4CUSiMwXG0TztY9DYkcj3Zs988p9Wr_GrZz5vRbxkGHFoQiHxDMNsuEZf8-" -d "sql=SELECT ROWID FROM 1fV5mXuKgG5cCck1cAVQX0G7HVTjfdm1SqeYSdmXU WHERE number=43" "https://www.googleapis.com/fusiontables/v2/query"
{
"kind": "fusiontables#sqlresponse",
"columns": [
"rowid"
]
}
