使用 Google fusion tables

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

OAuth 2.0 for Devices的認證流程

使用 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"
 ]
}