2015年10月18日 星期日

使用 Google fusion tables

存取google 的服務,都會用到OAuth 的認證機制,所以使用 Google fusion tables當然也不例外,底下說明的使用的是OAuth 2.0 for Devices過程,一旦取得Access Token,就可以對Google fusion tables 執行新增資料、查詢資料、刪除資料等操作。


OAuth 2.0 for Devices的認證流程
The user logs in on a separate device that has a browser.

使用 OAuth 2.0 , 必須先到Google Developers Console 建立一個專案以取得client ID及client secret. 註: 須於建立用戶端ID頁面, [已安裝的應用程式類型]請選擇"其他"

注意: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"
 ]
}

沒有留言 :

張貼留言