第二十四個夏天後: sql

顯示具有 sql 標籤的文章。顯示所有文章

2022年5月26日星期四

Go 開發筆記 - 使用 database/sql 通用介面存取資料庫，以 SQLite3 為例

在 Golang 的世界，有定義資料庫存取的通用介面 database/sql ，但貌似官方沒有提供實作而是讓廣大的鄉民開發，並且標記哪些套件是有通過 go-sql-test 驗證的，因此，大部分就是挑哪些有標記的，或是直接看 github 有多熱門也行。

相關文件：

以下就連續動作，筆記一下。

程式碼：

package main

import (
"log"
"database/sql"
_ "github.com/mattn/go-sqlite3"
)

func main() {
db, err := sql.Open("sqlite3", "/tmp/sqlite3.db")
if err != nil {
log.Fatalln(err)
}
defer db.Close()

//
// http://go-database-sql.org/modifying.html
//
// via db.Exec with checking the error message only
if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS account (
uid INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(64) NULL
);
`); err != nil {
log.Println(err)
}

// Insert via db.Prepare
if stmt, err := db.Prepare("INSERT INTO account(username) VALUES(?)"); err == nil {
if res, err := stmt.Exec("changyy.org"); err != nil {
log.Println("Insert Exec Error:", err)
} else if lastId, err := res.LastInsertId() ; err != nil {
log.Println("Get LastInsertId Error:", err)
} else if rowCount, err := res.RowsAffected() ; err != nil {
log.Println("Get RowsAffected Error:", err)
} else {
log.Println("Insert Done, Last Insert Id:", lastId, ", RowsAffected: ", rowCount)

// Update via db.Prepare
if stmt, err := db.Prepare("UPDATE account SET username = ? WHERE uid = ?"); err != nil {
log.Println("Update Prepqre Error:", err)
} else if res, err := stmt.Exec("blog.changyy.org", lastId); err != nil {
log.Println("Update Exec Error:", err)
} else if rowCount, err := res.RowsAffected() ; err != nil {
log.Println("Get RowsAffected Error:", err)
} else {
log.Println("Update RowsAffected: ", rowCount)
}
}
} else {
log.Println("Prepqre Insert Error:", err)
}

//
// http://go-database-sql.org/retrieving.html
// https://pkg.go.dev/database/sql#DB.Query
//
// via db.Query with sql.Rows and error mesasge
rows, err := db.Query("SELECT * FROM account")
if err != nil {
log.Println(err)
} else {
defer rows.Close()
log.Println("Result:")
for rows.Next() {
var id int
var username string
if err := rows.Scan(&id, &username) ; err == nil {
log.Println(id, username)
} else {
log.Println(err)
}
}
if err := rows.Err() ; err != nil {
log.Println(err)
}
}
}

執行：

% go run main.go
2022/05/25 20:44:26 Insert Done, Last Insert Id: 1 , RowsAffected: 1
2022/05/25 20:44:26 Update RowsAffected: 1
2022/05/25 20:44:26 Result:
2022/05/25 20:44:26 1 blog.changyy.org

% file /tmp/sqlite3.db
/tmp/sqlite3.db: SQLite 3.x database, last written using SQLite version 3038005, file counter 3, database pages 3, cookie 0x1, schema 4, UTF-8, version-valid-for 3

% sqlite3 /tmp/sqlite3.db .schema
CREATE TABLE account (
uid INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(64) NULL
);
CREATE TABLE sqlite_sequence(name,seq);

2017年1月13日星期五

[NodeJS] 批次處理 Website snapshot 並存進 MySQL DB @ Ubuntu Server 14.04

延續之前 [NodeJS] 使用 WebShot 進行網頁截圖、顯示正確的中文(CJK)等編碼 @ Ubuntu 14.04 Server 的部分，稍微改幾行 code 就支援批次處理啦

前置環境：


$ sudo apt-get install nodejs npm xfonts-wqy xfonts-kaname

$ sudo ln -s /usr/bin/nodejs  /usr/bin/node

$ mkdir -p job/images && cd job

$ npm install webshot

程式主體：


$ vim build.js



var output_dir = 'images';

var concurrent_limit = 10;

var running_task = 0;

var total_task = [

 { domain: 'tw.yahoo.com', url: 'https://tw.yahoo.com' } ,

 { domain: 'facebook.com', url: 'https://facebook.com' } ,

];



function build_website_snapshot() {

 while(total_task.length > 0 && running_task < concurrent_limit) {

  var item = total_task.shift();

  var url = item.url;

  var domain = item.domain;



  // https://github.com/brenden/node-webshot

  webshot(url, output_dir+'/'+domain+'.png', {

   screenSize: {

    width: 320,

    height: 480,

   }, 

   shotSize: {

    width: 320,

    height: 320,

   },

   timeout: 20000,

   renderDelay: 3000,

   userAgent: 'Mozilla/5.0 (iPhone; U; CPU iPhone OS 3_2 like Mac OS X; en-us) AppleWebKit/531.21.20 (KHTML, like Gecko) Mobile/7B298g'



  }, function(err) {

   if(err)

    console.log(err);

   running_task--;

   if (running_task == 0)

    console.log('done');

   if (total_task.length > 0)

    build_website_snapshot();

  });

  running_task++;

 }

}


$ node build.js

如此一來，就稍微搞定批次產出了。若要把產出的東西存進 MySQL DB server，那可以再這樣做：


$ npm install mysql


$ vim import.js



var fs = require('fs');

var path = require('path');

var mysql = require('mysql');

var connection = mysql.createConnection({

  host     : 'localhost',

  user     : 'dbuser',

  password : 'dbpassword',

  database : 'dbname',

});



var scan_source_dir = 'images';

var files = [];

var sql_values = [];

fs.readdirSync(scan_source_dir).filter(function(file){

        //console.log(file);

        if (fs.statSync(path.join(scan_source_dir, file)).isFile() && file.lastIndexOf('.png') == (file.length - 4)) {

                var domain = file.substring(0, file.length - 4);

                //files[domain] = fs.readFileSync(path.join(scan_source_dir, file), {encoding: 'binary'});

                files[domain] = fs.readFileSync(path.join(scan_source_dir, file));



                sql_values.push([domain, files[domain], Math.round(new Date().getTime()/1000), Math.round(new Date().getTime()/1000)]);

        } 

});

// console.log (sql_values);

/*

CREATE TABLE `snapshot_table ` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `domain` varchar(64) NOT NULL DEFAULT '',

  `image` blob,

  `createtime` int(11) DEFAULT NULL,

  `updatetime` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `domain` (`domain`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

*/



var sql = "INSERT INTO snapshot_table (domain, image, createtime, updatetime) VALUES ? ON DUPLICATE KEY UPDATE image=VALUES(image), updatetime=VALUES(updatetime) ";

connection.query(sql, [sql_values], function(err) {

        console.log(err);

});

connection.end();

如此一來，就可以自動掃目錄下符合 *.png 的檔案，並將 binary data 紀錄至 db server 中。

2016年4月21日星期四

[SQL] 計算資料比數所佔的比例 @ MySQL 5.6

這個需求好像滿常見的？例如有一張表記錄著許多操作的流水帳，例如：


CREATE TABLE `play_action` (

 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

 `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

 `play_result` INT (10) NOT NULL

)

想要以天為單位：


SELECT DATE_FORMAT(timestamp, "%Y-%m-%d %a") AS d, play_result, count(*) FROM play_action GROUP BY d, play_result

就能得知一天當中，所有操作結果的次數，那如果是比例呢？只好要先 SUM 加總一下 XD 最後在 JOIN 一下即可。此例就用暫存表來處理，其中暫存表不予許同一個 SQL 內有多次存取，所以只好多開一張暫存表來處理，連續動作：

Step 1: 建立一張暫時表，以天為單位，根據播放後的狀態，依序是播放次數、播放日期、播放結果，其中播放結果 0 是正常的


CREATE TEMPORARY TABLE `play` (

 `play_count` INT (10) NOT NULL,

 `play_date` VARCHAR(32) NOT NULL,

 `play_result` INT (10) NOT NULL,

 UNIQUE KEY `play_date` (`play_date`, `play_result`)

);

Step 2: 從 play_action 撈資料來填補，此時可以用條件式挑選想要的資料，此例是 4 月之後的資料


INSERT INTO `play` (play_result, play_date, play_count) SELECT play_result, DATE_FORMAT(timestamp, "%Y-%m-%d %a") AS d, count(*) as c FROM play_action WHERE timestamp >= '2016-04-01 00:00:00' GROUP BY play_result, d ORDER BY d ON DUPLICATE KEY UPDATE play_count=VALUES(play_count);

Step 3: 建立另一張暫存表，單純以天記錄總播放次數


CREATE TEMPORARY TABLE `play_sum` (

 `play_count` INT (10) NOT NULL,

 `play_date` VARCHAR(32) NOT NULL,

 UNIQUE KEY `play_date` (`play_date`)

);




INSERT INTO `play_sum` (play_date, play_count) SELECT play_date, SUM(play_count) AS play_total FROM play GROUP BY play_date;

Step 4: 簡易運算，輸出為日期、播放成功次數的比例，總播放次數


SELECT play.play_date, play.play_result, play.play_count / play_sum.play_count * 100, play_sum.play_count AS play_total FROM play, play_sum WHERE play.play_date = play_sum.play_date;


+----------------+-------------+---------------------------------------------+------------+

| play_date      | play_result | play.play_count / play_sum.play_count * 100 | play_total |

+----------------+-------------+---------------------------------------------+------------+

| 2016-04-01 Fri |           0 |                                     54.9398 |        687 |

| 2016-04-02 Sat |           0 |                                     68.9445 |        972 |

| 2016-04-03 Sun |           0 |                                     75.6607 |       1748 |

| 2016-04-04 Mon |           0 |                                     71.3967 |       1112 |

| 2016-04-05 Tue |           0 |                                     70.2852 |        886 |

| 2016-04-06 Wed |           0 |                                     49.8085 |        880 |

| 2016-04-07 Thu |           0 |                                     53.2239 |        596 |

+----------------+-------------+---------------------------------------------+------------+

2015年1月8日星期四

[MySQL] 使用 FOREIGN KEY 筆記 @ MySQL 5.6

話說，2014年算是我最常用 SQL DB 的一年，在這之前我都是用 NOSQL 架構 Orz (扣除 SQLite 啦)。最近設計一些新服務的資料儲存，正在想如何使用 MySQL Relations 的特色，才發現之前都沒在用 FOREIGN KEY 啦 :P

簡短介紹 FOREIGN KEY 的功用：

當設計很多階層性的 table 時，如 user (上層), group (中層), data (下層)，其中 data 每一筆都有 user, group 資訊，而 group 裡每一筆都跟 user 有關。此時在 DELETE 事件發生時，如果有建立 Relations 時，可在上層資料刪除時，順便幫你把相關資料刪掉。此外，在新增下層資料時，也會幫你驗證資料的正確性，不會讓你隨意新增假資料。

對於要建立 FOREIGN KEY 的要件：

在上層(Parent)預計使用的參考欄位要有 index 屬性，如 Primary Key, Unique Key 或 Index 都行
在下層(Child)預計使用的欄位也要有 index 屬性
在上層跟下層對應的欄位型態要一樣，例如一樣為非負整數等

此外，則是可以設定 Action，例如上層刪除、更新資料時，下層是否也要一同更新，其選項：

Restrict: 拒絕 parent event，也將造成 parent 動作失敗
Cascade: 跟著 parent event 更新或是刪除
Set null: 收到 parent event 時，將參考欄位設定成 null
No action: 標準 SQL 語法且為預設選項，在 MySQL 環境上與 Restrict 等價

下次是操作時碰到的問題：

Q: Cannot add foreign key constraint
A: 仔細確認一下指定的欄位，其型態是不是一致的，或是對應的 Action 若為 Set Null 時，要確認資料欄位是否允許 Null

Q: Cannot add or update a child row: a foreign key constraint fails
A: 追蹤一下已存在的資料，是不是有不合理的地方，例如下層存在一筆資料，其對應上層的資料無法匹配。解法就是手動修正，或是乾脆清光資料來做也行。

整體上，建議有參考關係時，可以面對 Parent delete event 時，可以設定成 Cascade 處理，如此一來刪除資料就輕鬆許多，不必做多個處理。

2014年9月10日星期三

[SQL] SELECT IN SELECT 以及 Pagination 的使用 @ MySQL 5.6

使用 SQL 語法時，有時會需要從另一張 Table 取出清單，接著對清單內的資料做為基準再進行一次資料的擷取，直觀的想法大概是 SELECT something FROM Table1 WHERE id IN (SELECT id FROM Table2 WHERE ... )。

可惜上述語法是不行的 XD 要改成 JOIN 的做法：


SELECT something FROM Table1, (SELECT id FROM Table2) AS list WHERE Table1.id = list.id;

接著，偶爾會需要 pagination 的需求，加個 LIMIT 的用法，這時候又會想要回報全部有幾筆資料（對於一些搜尋引擎的設計，有些是採用預估的方式），以便前端可以估算有幾筆資料。

最簡單的解法是再用一個 SQL Query 去問 Table2 的 id 資料，但想要更快一點，就來試試 MySQL User-Defined Variables 吧!


SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id;

如此一來，結果都會有個 total 筆數跟著，雖然仍不夠好，但也不錯啦 XD 而搭配 LIMIT OFFSET,COUNT 時，total 的資訊是來自掃 Table2 的資料，所以也能正常顯示：


SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id LIMIT 0,10;

2014年7月25日星期五

[SQL] 依據條件取出指定欄位 SELECT IF/ELSE, CASE WHEN/ELSE 用法 @ MySQL 5.6

假設有一張 Table 有兩個欄位：


mysql> SELECT * FROM mtable;

+----+----+

| f1 | f2 |

+----+----+

| 1  | 2  |

| 4  | 3  |

| 5  | 6  |

+----+----+

想要撈出 f1 跟 f2 之中，數值最大者:


mysql> SELECT f1 AS result FROM mtable WHERE f1 > f2;

+--------+

| result |

+--------+

| 4      |

+--------+




mysql> SELECT f2 AS result FROM mtable WHERE f1 < f2;

+--------+

| result |

+--------+

| 2      |

| 6      |

+--------+

這時候，可透過條件判斷，透過 CASE WHEN/ELSE 的用法，就可以不用分兩次撈了：


mysql> SELECT

 CASE WHEN f1 > f2

  THEN f1

  ELSE f2

 END AS result

 FROM mtable;

+--------+

| result |

+--------+

| 2      |

| 4      |

| 6      |

+--------+

2014年7月17日星期四

[SQL] 透過 INNER JOIN 更新 Table 新增的欄位數值 @ MySQL 5.6

對於一些當作收集 log 用途的 table tb_log ，隨著時間增加後，通常會再整理另一個 tb_status 的 table，快速查詢各個狀態，設計上就會定期批次從 tb_log 取出資料，存進 tb_status 中。

假設 tb_log 有 5 個欄位，一開始只覺得需要 2 個欄位的資訊，就把 tb_status 設定為 2 個欄位，然而過一陣子後，想多記錄一個欄位時，只好變動 tb_status ，但新增的欄位沒有舊資料，就變成要從 tb_log 取出來再存進 tb_status 了

碰到這種問題，有一個解法就是使用 INNER JOIN 來處理：

先從 tb_status 找出欄位未有值的資料
從 tb_log 組出 tb_status 所需的資料
透過 Update 指令更新

情況敘述：


mysql> describe tb_log;

+--------+--------------+------+-----+---------+----------------+

| Field  | Type         | Null | Key | Default | Extra          |

+--------+--------------+------+-----+---------+----------------+

| f0     | int(11)      | NO   | PRI | NULL    | auto_increment |

| f1     | varchar(8)   | YES  |     | NULL    |                |

| f2     | varchar(8)   | YES  |     | NULL    |                |

| f3     | varchar(8)   | YES  |     | NULL    |                |

| f4     | varchar(8)   | YES  |     | NULL    |                |

+--------+--------------+------+-----+---------+----------------+



mysql> describe tb_status;

+--------+--------------+------+-----+---------+----------------+

| Field  | Type         | Null | Key | Default | Extra          |

+--------+--------------+------+-----+---------+----------------+

| f1     | varchar(8)   | YES  | PRI | NULL    |                |

| f2     | varchar(8)   | YES  |     | NULL    |                |

| f3     | varchar(8)   | YES  |     | NULL    |                |

+--------+--------------+------+-----+---------+----------------+

其中 tb_status.f3 則是新建出來，未有資料的。

第一步：先找出 tb_status.f3 是空的（新進資料會有 f3 數值，只有舊資料沒有）


mysql> SELECT f1 WHERE f3 IS NULL;

第二步：從 tb_log 組出 f3 資料，由於 tb_log 是流水帳，且 tb_status 本身也可以從 tb_log 查詢出來的，只需組出 tb_status 需要的欄位即可：


mysql> SELECT f1, f3 FROM tb_log GROUP BY f1;

第三步，把上述兩個資料 JOIN 起來：


SELECT tb1.f1, tb2.f3 FROM

( SELECT f1 WHERE f3 IS NULL ) AS tb1, (SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2

WHERE tb1.f1 = tb2.f2;

最後，追加更新 tb_status 的用法：


UPDATE tb_status AS tb4

INNER JOIN 



(

 SELECT tb1.f1, tb2.f3 FROM

  ( SELECT f1 WHERE f3 IS NULL ) AS tb1,

  (SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2

 WHERE tb1.f1 = tb2.f2

) AS tb3



ON tb4.f1 = tb3.f1




SET



tb4.f3 = tb3.f3;

2014年2月26日星期三

MongoDB 開發筆記 - Aggregate 之 Group BY Timestamp / GROUP BY ObjectID getTimestamp @ MongoDB Server v2.4.9

上回看文件時，上頭說系統預設 ObjectID(_id) 中，已經有時間戳記了，並且建議不需要額外在儲存一個時間:

ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
a 2-byte process id, and
a 3-byte counter, starting with a random value.

開發環境：MongoDB Server v2.4.9
目標：SELECT `timestamp`, count(*) AS count FROM `test` GROUP BY `timestamp`;

儘管 ObjectID 有提供 _id.getTimestamp() 方式存取時間，但是在用 aggregate 時卻無法動態取用($year, $month, $dayOfMonth)，如：


mongodb> db.test.aggregate({

 $group:{

  _id: {

   year: { $year: "$_id.getTimestamp()" } ,

   month: { $month: "$_id.getTimestamp()" } ,

   day: { $dayOfMonth: "$_id.getTimestamp()" }

  },

  count: {

   $sum:1

  }

 }

})


mongodb> aggregate failed: {

        "errmsg" : "exception: can't convert from BSON type EOO to Date",

        "code" : 16006,

        "ok" : 0

}

看來在 v2.4.9 版的應用時，還是需要建立一個 timestamp 出來，然而 db.collection.update 無法拿 document 自身資料來更新，例如從 ObjectID 抽出時間來用：


mongodb> db.test.update({timestamp: null }, { $set : { timestamp: _id.getTimestamp() } }, {multi:true})

ReferenceError: _id is not defined

需要改用 forEach 來一筆筆更新：


mongodb> db.test.find({timestamp:null}).snapshot().forEach(

 function (item) {

  item.timestamp = item._id.getTimestamp();

  //db.test.save(item);

  db.test.update(

    # query

    {

        '_id': item['_id']

    }, 

    # update

    {

        '$set':

        { 

            'timestamp': item['timestamp']

        }

    },

    upsert=False, multi=False

  );

 }

)

接著終於可以 GROUP BY DATE 了 Orz


mongodb> db.test.aggregate(

 {

  $group:{

   _id: {

    year: { $year: "$timestamp" } ,

    month: { $month: "$timestamp" } ,

    day: { $dayOfMonth: "$timestamp" }

   },

   count: {

    $sum:1

   }

  }

 }

)

{

        "result" : [

                {

                        "_id" : {

                                "year" : 2014,

                                "month" : 2,

                                "day" : 22

                        },

                        "count" : 23

                },

                {

                        "_id" : {

                                "year" : 2014,

                                "month" : 2,

                                "day" : 21

                        },

                        "count" : 15

                },

                {

                        "_id" : {

                                "year" : 2014,

                                "month" : 2,

                                "day" : 20

                        },

                        "count" : 200

                }

        ],

        "ok" : 1

}

2014年1月22日星期三

[Linux] MongoDB 與 PyMongo 初體驗 @ Ubuntu 12.04, Linode

架設：


http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/



$ sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10

$ echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list

$ sudo apt-get update



http://www.mongodb.org/downloads

$ sudo apt-get install mongodb-10gen=2.4.9

$ echo "mongodb-10gen hold" | sudo dpkg --set-selections



$ sudo service mongodb restart

mongodb stop/waiting

mongodb start/running, process ######



$ mongo

MongoDB shell version: 2.4.9

connecting to: test

Welcome to the MongoDB shell.

For interactive help, type "help".

For more comprehensive documentation, see

        http://docs.mongodb.org/

Questions? Try the support group

        http://groups.google.com/group/mongodb-user

防火牆存取限制：


$ sudo iptables --list-rules

-P INPUT ACCEPT

-P FORWARD ACCEPT

-P OUTPUT ACCEPT



$ sudo vim /etc/init.d/iptables-rule.sh

#!/bin/sh



# BIN

BIN_IPTABLES=`which iptables`



# reset rules

$BIN_IPTABLES -F

$BIN_IPTABLES -X

$BIN_IPTABLES -Z



# init policies

#$BIN_IPTABLES -P INPUT DROP

#$BIN_IPTABLES -P OUTPUT ACCEPT

#$BIN_IPTABLES -P FORWARD ACCEPT



# mongo db

$BIN_IPTABLES -A INPUT -j ACCEPT -p tcp --destination-port 27017 -s 127.0.0.1,IP1,IP2,IP3

# mongo db drop all

$BIN_IPTABLES -A INPUT -j REJECT -p tcp --destination-port 27017



$ sudo chmod 775 /etc/init.d/iptables-rule.sh

$ sudo update-rc.d -f iptables-rule.sh defaults

一些 mongo 常用指令：


顯示所有的 databases (SQL: show databases)

> show dbs



使用指定 collection (SQL: use dbname)

> use dbname



顯示目前 databases 中的所有 collections (SQL: show tables)

> show collections



更多對照指令：

SQL to MongoDB Mapping Chart

PHP: SQL to Mongo Mapping Chart

安裝 pymongo 套件：


$ git clone git://github.com/mongodb/mongo-python-driver.git pymongo

$ cd pymongo

$ sudo python setup.py install

使用 pymongo 新增範例：

from pymongo import MongoClient



client = MongoClient()
database = client[‘dbname’] # SQL: Database Name
collection = database[‘table’]   # SQL: Table Name



item = {"author":"changyy"}
collection.insert(item)

訂閱：文章 (Atom)

2022年5月26日 星期四

2017年1月13日 星期五

2016年4月21日 星期四

2015年1月8日 星期四

2014年9月10日 星期三

2014年7月25日 星期五

2014年7月17日 星期四

2014年2月26日 星期三

2014年1月22日 星期三

Subscribe Now