Báo điểm tự động
Bài 2: Cài đặt trên RPi

Ứng dụng bao gồm 2 module chính

1. Tự động tải về dữ liệu từ MS-SQL Server
2. Tự động đọc và trả lời SMS/email hỏi điểm

Ta cần cài đặt trên RPi

1. Ứng dụng gởi/nhận SMS qua GSM modem: gammu-smsd (Xem SMS gateway)
2. Dịch vụ gởi/nhận email: dùng mailgun.com (Xem Sử dụng và Đăng ký mailgun)
3. Web Server: Nginx + PHP + MySQL (Xem Web Server)
4. Giao tiếp với MS SQL Server: dùng FreeTDS

Script giúp cài đặt nhanh các ứng dụng cần thiết (Bài 5 Cài đặt nhanh)

A. Để có thể giao tiếp với MS SQL Server trên nền web, chỉ cần cài đặt php5-sybase

apt-get install php5-sybase

Soạn thảo file cấu hình /etc/freetds/freetds.conf như sau

[global]
tds version = 7.1
timeout = 10
connect timeout = 10
text size = 64512

[mssqlserver]
host = <IP_của_SQL_Server>
port = 1433

Khi đó dòng lệnh kết nối MS-SQL sever trong PHP là

$msConn = new PDO ('dblib:host=mssqlserver;', $usr, $pwd);

B. Tuy nhiên khi truy xuất khổi lượng lớn dữ liệu qua PHP, tốc độ có thể chậm đáng kể. Chúng ta cần cài đặt công cụ dòng lệnh isql

apt-get install unixodbc  tdsodbc

File cấu hình /etc/odbcinst.ini như sau

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Setup = /usr/lib/arm-linux-gnueabihf/odbc/libtdsS.so
Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
CPTimeout =
CPReuse =
FileUsage = 1

File cấu hình /etc/odbc.ini như sau

[sqldatasource]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = mssqlserver

Khi đó, có thể lấy toàn bộ dữ liệu thuộc một table của MS-SQL database cất dưới dạng CSV, tương tự như sau

echo 'SELECT * FROM VEMIS_Student.dbo.HS_BangDiemChiTietHocKy' | isql sqldatasource usr pwd -q -b -x0x2C > /DiemThi/csv/VEMIS_Student/HS_BangDiemChiTietHocKy

C. Cập nhật dữ liệu từ file CSV vào CSDL MySQL

Để tăng tốc độ nhập dữ liệu, chúng ta dùng câu lệnh LOAD DATA INFILE

Có thể chạy qua PHP PDO

$myConn->exec("LOAD DATA INFILE '$filename' REPLACE INTO TABLE $table FIELDS TERMINATED BY ',' ENCLOSED BY '\"'");

hay dùng công cụ dòng lệnh mysqlimport

mysqlimport --user=root --password=****** --fields-enclosed-by=\" --fields-terminated-by=, --local --replace --lock-tables --force VEMIS_Student HS_BangDiemChiTietHocKy

D. Theo dõi sự thay đổi dữ liệu

MS-SQL Server Express 2005 không có cơ chế ghi chép các dòng dữ liệu bị thay đổi. Chúng ta không được phép tạo trigger trên SQL Server, nên có lẽ phải dùng đến hàm sum(‘DiemSo’) để đánh dấu sự thay đổi dữ liệu.

• Tạo cronjob định kỳ kiểm tra sự thay đổi dữ liệu và tải dữ liệu mới về nếu cần. Mặc định chỉ tải về bảng điểm mà thôi.

#!/bin/bash
# $1=full
usr="test"
pwd="****"
csv_dir="/DiemThi/csv"
Common1="DM_NamHoc DM_HocKy DM_MonHoc DM_LoaiDiem"
Student1="HS_HoSoHocSinh HS_LopHoc HS_PhanLop HS_BangDiemChiTietHocKy"
Student2="HS_BangDiemChiTietHocKy"

chk=$(echo "SELECT sum(DiemSo) FROM VEMIS_Student.dbo.${Student2}" | isql sqldatasource "$usr" "$pwd" -b|grep -o -E '[0-9]+\.[0-9]*')
[ "$?" -ne 0 ] && { rm -f $mark; exit 1; } || touch $mark
[ -f '/tmp/chk' ] && oldchk=$(<'/tmp/chk') || oldchk=0
if [ "$chk" != "$oldchk" ]; then
    printf "$chk" > '/tmp/chk'
    [ "$1" ] && DBs="Common1 Student1" || DBs="Student2"
    for db in $DBs; do
        db0="${db%?}"
        for tbl in ${!db}; do
            #chép dữ liệu về RPi
            [ -d "$csv_dir/VEMIS_${db0}" ] || mkdir "$csv_dir/VEMIS_${db0}"
            echo "SELECT * FROM VEMIS_${db0}.dbo.$tbl" | isql sqldatasource "$usr" "$pwd" -q -b -x0x2C > "$csv_dir/VEMIS_${db0}/${tbl}.csv"
        done
    done
fi

Dữ liệu chép về lưu theo tên qui ước /DiemThi/csv/tên_CSDL/tên_bảng

• Tạo cronjob thứ hai định kỳ xem xét thư mục /DiemThi/csv/tên_CSDL, nếu có file .csv thì cập nhật CSDL trên RPi và xóa file đi

#!/bin/bash
usr="root"
pwd="****"
csv_dir="/DiemThi/csv"

for d in "$csv_dir"/*; do
    db="${d#$csv_dir/}"
    for f in "$d"/*.csv; do
        if [ "$f" != "$d/*.csv" ]; then
            mysqlimport -u$usr -p$pwd --fields-enclosed-by=\" --fields-terminated-by=, -l -r -f -s -L $db $f
            rm $f
        fi
    done
done

Cài đặt nhanh

Xem phần cuối Bài 4