科內伺服器雜記

1.升級作業
// 移除畢業生
DELETE FROM `users` WHERE `cls_id`=3;
// 原2年級升3年級
UPDATE `users` SET `cls_id`=3 WHERE `cls_id`=2;
// 原1年級升2年級
UPDATE `users` SET `cls_id`=2 WHERE `cls_id`=1;
// 新增新生1年級
INSERT INTO `users` (`user_id`, `level_id`, `school_id`, `cls_id`, `user_name`, `user_number`, `user_account`, `user_password`) VALUES (NULL, ‘1’, ‘1’, ‘1’, ‘姓名’, ‘座號’, ‘學號’, ‘密碼’);

2.題庫更新
下載勞中辦PDF,轉至文字檔。
A.全部用 Tab 隔開欄位
B.文章最後的句號拿掉,【。\0】 取代 【\0】
C.【’】 取代 【”】
D.【”】 取代 【”】
E.【$】 取代 【$】
F.置換成SQL語法
INSERT INTO `problem` (`pm_id`, `item_id`, `pm_number`, `pm_answer`, `pm_question`, `pm_op1`, `pm_op2`, `pm_op3`, `pm_op4`, `pm_image`) VALUES (NULL, ‘1’,’1′,’1′,’「全球資訊網(WWW)」的英文為何?’,’World Wide Web’,’Web Wide World’,’Web World Wide’,’World Web Wide’,NULL);
G.圖片截圖加入資料庫

91-1:迷宮搜尋系統

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _91_1
{
    public partial class Form1 : Form
    {
        int[,] m = new int[9, 9];
        int[,] dxy = new int[4, 2] { { -1, 0 }, { 1, 0 }, { 0, -1 }, { 0, 1 } };
        int sx, sy, ex, ey;
        int best_t = 999;
        string best_l;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            initMatrix();
            show();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            findLoad(sx, sy, 0, "");

            if (best_t != 999)
            {
                Console.WriteLine("********** best solution **********");
                Console.WriteLine("距離成本:{0}", best_t);
                Console.WriteLine("走法:");

                string[] l = best_l.Split(new char[] { ' ' });

                foreach (string item in l)
                    Console.WriteLine(item);

                Console.WriteLine();
            }
            else Console.WriteLine("********** No solution **********");

            show();

        }

        private void button3_Click(object sender, EventArgs e)
        {
            resetMatrix();
            show();
        }

        // 重新對陣列內容初始化
        void initMatrix()
        {
            // "狀態"及"值"的對應定義
            // 走過:0~81(成本)
            // 未走過:99
            // 牆:-1

            // r.Next(0,9)  -->  0~小於9的整數 或 由0開始的9種變化 -->  0~8
            Random r = new Random((int)DateTime.Now.Ticks);

            // 建立迷宮中的牆壁
            for (int i = 0; i < 9; i++)
                for (int j = 0; j < 9; j++)
                    if (r.Next(0, 2) == 1) m[i, j] = -1;
                    else m[i, j] = 99;
            
            // 定義起始點
            sx = r.Next(0, 9);
            sy = r.Next(0, 9);
            m[sx, sy] = 0;

            // 定義終止點
            ex = r.Next(0, 9);
            ey = r.Next(0, 9);
            m[ex, ey] = 99;

            // 清除最佳成本、路徑
            best_t = 999;
            best_l = "";
        }

        // 清除走過路徑
        void resetMatrix()
        {
            for (int i = 0; i < 9; i++)
                for (int j = 0; j < 9; j++)
                    if (m[i, j] != -1) m[i, j] = 99;

            m[sx, sy] = 0;
            m[ex, ey] = 99;

            best_t = 999;
            best_l = "";
        }

        // 顯示陣列內容
        void show()
        {
            for (int y = 0; y < 9; y++)
            {
                if (y == 0)
                {
                    Console.Write("{0} \t"," ");
                    for (int x = 0; x < 9; x++)
                        Console.Write("{0}\t", x);
                    Console.WriteLine();
                }

                for (int x = 0; x < 9; x++)
                {
                    if(x==0)
                        Console.Write("{0}:\t", y);

                    if (x == sx && y == sy)
                        Console.Write("{0}\t", "S");
                    else if (x == ex && y == ey)
                        Console.Write("{0}\t", "E");
                    else if (m[x,y]==99)
                        Console.Write("{0}\t", ".");
                    else if (m[x, y] == -1)
                        Console.Write("{0}\t", "*");
                    else
                        Console.Write("{0}\t", m[x, y]);
                }
                Console.WriteLine();
            }
            Console.WriteLine();
        }

        void findLoad(int x,int y,int t, string l)
        {
            // 更新記錄路徑
            l += string.Format("{0},{1} ",x, y);

            // 到達終點?
            if(x==ex && y==ey)
            {
                // 印出成本、路徑
                Console.WriteLine("{0}:{1}", t, l);

                // 有比目前最佳紀錄好嗎?
                if (t < best_t)
                {
                    // 更新最佳紀錄
                    best_t = t;
                    best_l = l.Trim();
                }
            }
            else
            {
                // 判斷上、下、左、右四個方向是否能通行
                for (int i = 0; i < 4; i++)
                {
                    // 設定下次前往位置
                    int nx = x + dxy[i, 0];
                    int ny = y + dxy[i, 1];

                    // 新座標在合理區間嗎?
                    if ((nx >= 0 && nx <= 8) && (ny >= 0 && ny <= 8))
                    {
                        // 是否可前進?及成本有比"舊"成本優嗎?
                        if (m[nx, ny] != -1 && m[nx, ny] > t + 1)
                        {
                            // 更新到該位置成本
                            m[nx, ny] = t + 1;
                            show();

                            // 遞迴至下一次位置
                            findLoad(nx, ny, t + 1, l);
                        }
                    }
                } 
            }

        }
    }
}

【樹梅派3】安裝及測試 Samba

// 切換最高權限
sudo su

// 安裝 samba 套件
apt-get -y install samba

// 備份原來的設定檔為 smb.conf.org
cp /etc/samba/smb.conf /etc/samba/smb.conf.org

// 建立新設定
nano /etc/samba/smb.conf

// 在設定檔中,加入新內容
[myusb]
comment = Mydrive
read only = no
locking = no
path = /media/myusb
guest ok = yes
force user = pi
writeable = Yes
only guest = Yes
create mask = 0777
directory mask = 0777

// 重新啟動
/etc/init.d/samba restart

// 也可以設定 samba 使用者及密碼(選擇性)
apt-get -y install samba-common-bin )
sudo smbpasswd -a 使用者名稱

參考資料:

Raspberry pi 上安裝 samba (網路芳鄰)

【樹梅派3】安裝及測試 lighttpd + PHP + SQL + phpMyAdmin

lighttpd 是一個輕量化的 Web Server,適合在樹梅派這種受限的環境執行,其相關的介紹如下:
lighttpd官方網站
WiKi – Lighttpd(中文說明)
Raspberry Pi Webserver Using lighttpd

// 切換為 root 進行安裝
sudo su

// 更新套件資訊
apt-get update

// 安裝 lighttpd
apt-get -y install lighttpd

// 安裝 PHP
apt-get -y install php5-common php5-cgi php5

// 修正 CGI 設定(有些網站說要做)
nano /etc/php5/cgi/php.ini
cgi.fix_pathinfo = 1  // 在最下面加入這一行

// 安裝 FastCGI 來載入 PHP (用來加快速度)
lighty-enable-mod fastcgi-php

// 安裝 MySQL (安裝時會需要設定密碼)
apt-get -y install mysql-server php5-mysql

// 安裝 phpMyAdmin(安裝兩次才成功)
apt-get -y install phpmyadmin

// 重新啟動 lighttpd
service lighttpd force-reload

// 讓 pi 使用者有權限能夠修改網頁主目錄(/var/www)
// 更改主目錄擁有者
chown pi:pi /var/www -R

// 將 /var/www 目錄連結至 pi 的家目錄(~/www)
exit
cd ~
sudo ln -s /var/www www

// 加入測試網頁
cd ~/www/html
nano phpinfo.php
<?php phpinfo(); ?>  // 加入程式碼

// 網頁瀏覽
http://IP位址/phpinfo.php

【樹梅派3】安裝及測試 MQTT Server – mosquitto

1.安裝 mosquitto 伺服器

// 切換為管理者
sudo su

// 下載 mosquitto 認證 KEY
wget http://repo.mosquitto.org/debian/mosquitto-repo.gpg.key

// 加入至 APT 伺服器中
apt-key add mosquitto-repo.gpg.key

// 加入清單列表
cd /etc/apt/sources.list.d/
wget http://repo.mosquitto.org/debian/mosquitto-jessie.list

// 更新 APT 伺服器,使其連結 mosquitto
apt-get update
apt-cache search mosquitto

// 安裝
apt-get install mosquitto  mosquitto-clients

2.測試 MQTT

// 假設:
// Topic 名稱為 myTopic
// MQTT 伺服器所在 IP 為 192.168.0.201

// 在電腦一執行:訂閱 myTopic 資料
mosquitto_sub -h 192.168.0.201 -t myTopic

// 在電腦二執行:發布訊息至 myTopic
mosquitto_pub -h 192.168.0.201 -t myTopic -m "testMsg"

// 此時在電腦一畫面便可發現 testMsg 訊息

MQTT01

Ameba 測試程式碼:

#include <WiFi.h>
#include <PubSubClient.h>

// AP 基地台資訊設定
char ssid[] = "your network SSID";
char pass[] = "your network password";
int status  = WL_IDLE_STATUS;    // the Wifi radio's status

// MQTT 伺服器網路位址、ID、輸出Topic、輸出訊息、輸入Topic設定
char mqttServer[]     = "192.168.0.201";
char clientId[]       = "amebaClient";
char publishTopic[]   = "outTopic";
char publishPayload[] = "hello world";
char subscribeTopic[] = "inTopic";

// 發生傳入訊息時,將會傳回的函式
void callback(char* topic, byte* payload, unsigned int length) {
  Serial.print("Message arrived [");
  Serial.print(topic);
  Serial.print("] ");
  for (int i=0;i<length;i++) {
    Serial.print((char)payload[i]);
  }
  Serial.println();
}

WiFiClient wifiClient;
PubSubClient client(wifiClient);

void reconnect() {
  // Loop until we're reconnected
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    // Attempt to connect
    if (client.connect(clientId)) {
      Serial.println("connected");
      // Once connected, publish an announcement...
      Serial.print("publish:");
      Serial.println(publishTopic);
      Serial.println(publishPayload);
      client.publish(publishTopic, publishPayload);
      // ... and resubscribe
      client.subscribe(subscribeTopic);
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      // Wait 5 seconds before retrying
      delay(5000);
    }
  }
}

void setup()
{
  Serial.begin(38400);

  while (status != WL_CONNECTED) {
    Serial.print("Attempting to connect to SSID: ");
    Serial.println(ssid);
    // Connect to WPA/WPA2 network. Change this line if using open or WEP network:
    status = WiFi.begin(ssid, pass);

    // wait 10 seconds for connection:
    delay(10000);
  }

  client.setServer(mqttServer, 1883);
  client.setCallback(callback);

  // Allow the hardware to sort itself out
  delay(1500);
}

void loop()
{
  if (!client.connected()) {
    reconnect();
  }
  client.loop();
}

注意問題:

1.MQTT 預設通訊埠為 1883,防火牆可能會阻擋。
2.Ameba 的發佈功能仍有問題。
3.如果發現 1883 被占用,可以使用 pkill mosquito 指令,將當在記憶體內部的 MQTT 強迫關閉。
可以用以下指令,查看系統中是否有應用程式在使用1883埠
ps -ef | grep mosq && netstat -tln | grep 1883

參考網址:

Mosquitto Debian repository
Installing MQTT Broker(Mosquitto) on Raspberry Pi
MQTT学习笔记——树莓派MQTT客户端 使用Mosquitto和paho-python
Ameba Arduino: 使用MQTT上傳與傾聽資料

【樹梅派3】安裝及測試 OpenCV

相關安裝文章可以參考:
Install guide: Raspberry Pi 3 + Raspbian Jessie + OpenCV 3
Raspberry Pi:Raspbian編譯OpenCV原始碼
http://vincecc.blogspot.tw/2013/09/opencv-extract-colors-hsv.html

0.請先切換至最高權限root

sudo su

1.更新系統

apt-get update

2.安裝轉譯程式

apt-get install build-essential

3.聲音與影像的編碼和解碼

apt-get install libavformat-dev

4.聲音與影像的轉碼
注意:在樹梅派3無法直接安裝ffmpeg,需要自行make。
參考網站1:Installing FFMPEG for Raspberry Pi
參考網站2:Raspberry Pi: Streaming video to Ustream – Ustream Support
參考網站3:ffmpeg – Debian Wiki

apt-get install git libav-tools
cd /usr/src
sudo mkdir ffmpeg
sudo chown pi:users ffmpeg
git clone git://source.ffmpeg.org/ffmpeg.git ffmpeg
cd ffmpeg
./configure
make
make install

5.Opencv基本函式庫
注意:版本須注意隨時會更新。

apt-get install libcv2.4
apt-get install libcvaux2.4
apt-get install libhighgui2.4

6.Opencv-python版套件(必要)

apt-get install python-opencv

7.Opencv技術文件

apt-get install opencv-doc

8.顯示更多用來編譯opencv的開發工具

apt-get install libcvaux-dev

9.顯示opencv標頭檔和靜態資料庫

apt-get install libcv-dev

10.另一個顯示opencv標頭檔和靜態資料庫

apt-get install libhighgui-dev

測試程式一:偵測彩色物件

#!/usr/bin/python

import cv2.cv as cv  #匯入opencv資料庫
import time

cv.NamedWindow("camera", 1)    #建立一個預覽視窗

capture = cv.CaptureFromCAM(0)   #建立攝影機與畫面的結構
cv.SetCaptureProperty(capture, 3, 360)   #畫面寬度
cv.SetCaptureProperty(capture, 4, 240)   #畫面高度
while True:
    img = cv.QueryFrame(capture)     #捕捉畫面並指定於img
    cv.ShowImage("camera", img)      #將img定位於視窗
    if cv.WaitKey(10) == 27:                # ESC key 跳出程式
        break
#cv.DestroyAllWindows()

測試程式二:偵測彩色物件

#!/usr/bin/python
import cv2.cv as cv
import time

cv.NamedWindow("camera", 1)

capture = cv.CaptureFromCAM(0)
cv.SetCaptureProperty(capture, 3, 360)
cv.SetCaptureProperty(capture, 4, 240)

while True:
    img = cv.QueryFrame(capture)

    cv.Smooth(img,img,cv.CV_BLUR,3)
    hue_img = cv.CreateImage(cv.GetSize(img), 8, 3)
    cv.CvtColor(img,hue_img, cv.CV_BGR2HSV)

    threshold_img = cv.CreateImage(cv.GetSize(hue_img), 8, 1)
    cv.InRangeS(hue_img, (38,120,60),(75, 255, 255), threshold_img)
    
    cv.ShowImage("Colour Tracking", img)
    cv.ShowImage("Threshold", threshold_img)
    if cv.WaitKey(10) == 27:
        break
#cv.DestroyAllWindows()

在OpenCV中,H、S、V 的意義及範圍如下:
H(色調):0~180
S(飽和):0~255
V(亮度):0~255

一般依照在網路上查到的H值是0~360,要除以2變成0-180的範圍,才能放到 OpenCV 濾出顏色。

舉例要過濾出藍色:
H值為240,因此120(filter:110~130)為OPENCV的合適值,那我會這樣下過濾條件:
InRangeS(hue_img, (110,120,120),(130, 255, 255), threshold_img)

線上查RGB轉HSV顏色
http://www.colorspire.com/

其他參考:
利用膚色加輪廓擷取手部區域
http://ccw1986.blogspot.tw/2013/10/opencv-extract-hand-region-using-skin.html

4-1:資料結構(樹)—輸出二元樹的後序拜訪的結果

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;

namespace _4_1
{
    class Node<T>
    {
        public Node<T> Left { get; internal set; }
        public Node<T> Right { get; internal set; }
        public Node<T> Parent { get; internal set; }
        public T Value { get; internal set; }
        public Node(T value)
        {
            Value = value;
        }
    }

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            run("in1.txt", "out.txt", false);
            run("in2.txt", "out.txt", true);
            run("in3.txt", "out.txt", true);
            run("in4.txt", "out.txt", true);
        }

        string retVisited = "";

        void run(string inFile, string outFile, bool append)
        {
            StreamWriter sw = new StreamWriter(outFile, append);
            List<Node<int>> ls;

            rdFile(inFile, out ls);

            for (int i = 0; i < ls.Count; i++)
            {
                retVisited = "";
                postOrder(ls[i]);

                retVisited = retVisited.Trim().Replace(' ', ',');
                sw.WriteLine(retVisited);
            }
            sw.WriteLine();
            sw.Close();
        }

        void postOrder(Node<int> thisNode)
        {
            // 先拜訪左樹
            if (thisNode.Left != null)
                postOrder(thisNode.Left);
            // 再拜訪右樹
            if (thisNode.Right != null)
                postOrder(thisNode.Right);

            retVisited += (thisNode.Value.ToString() + " ");
        }

        void addNode(Node<int> thisNode, int addValue)
        {
            if (addValue > thisNode.Value)
            { // 比根大,加入右樹
                if (thisNode.Right == null)
                    thisNode.Right = new Node<int>(addValue);
                else
                    addNode(thisNode.Right, addValue);
            }
                
            else
            { // 比根小,加入左樹
                if (thisNode.Left == null)
                    thisNode.Left = new Node<int>(addValue);
                else
                    addNode(thisNode.Left, addValue);
            }
                
        }

        void rdFile(string inFile, out List<Node<int>> ls)
        {
            StreamReader sr = new StreamReader(inFile);
            int rNum = int.Parse(sr.ReadLine());
            ls = new List<Node<int>>();

            for (int j = 0; j < rNum; j++)
            {
                sr.ReadLine();
                string[] row = sr.ReadLine().Split(new char[] { ',' });
                Node<int> r = new Node<int>(int.Parse(row[0]));

                for (int i = 1; i < row.Length; i++)
                    addNode(r, int.Parse(row[i]));

                ls.Add(r);
            }            
            sr.Close();
        }
    }
}

3-2:其他—矩陣的乘法

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;

namespace _3_2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            run("in1.txt", "out.txt", false);
            run("in2.txt", "out.txt", true);
            run("in3.txt", "out.txt", true);
            run("in4.txt", "out.txt", true);
        }


        void run(string inFile, string outFile, bool append)
        {
            StreamWriter sw = new StreamWriter(outFile, append);
            int[][,] A, B, AB;
            int m, r, n, _m, _r, _n;
            long sum = 0, div = 0;
            rdFile(inFile, out A, out B, out AB);

            for (int i = 0; i < A.Length; i++)
            {
                m = A[i].GetUpperBound(0) + 1;
                r = A[i].GetUpperBound(1) + 1;
                n = B[i].GetUpperBound(1) + 1;

                for (_m = 0; _m < m; _m++)
                {
                    for (_n = 0; _n < n; _n++)
                    {
                        sum = 0;
                        for (_r = 0; _r < r; _r++)
                            sum += A[i][_m, _r] * B[i][_r, _n];
                        
                        if (sum != AB[i][_m, _n])
                        {
                            sum = AB[i][_m, _n];
                            for (_r = 0; _r < r; _r++)
                            {
                                if(A[i][_m, _r]!=9999 && B[i][_r, _n]!=9999)
                                    sum -= A[i][_m, _r] * B[i][_r, _n];
                                else if(A[i][_m, _r] != 9999)
                                    div = A[i][_m, _r];
                                else
                                    div = B[i][_r, _n];
                            }
                            sum /= div;
                            sw.WriteLine(sum);
                            break;
                        }
                    }
                    if (_n < n) break;
                }
            }
            sw.WriteLine();
            sw.Close();
        }

        void rdFile(string inFile, out int[][,] A, out int[][,] B, out int[][,] AB)
        {
            StreamReader sr = new StreamReader(inFile);
            int rNum = int.Parse(sr.ReadLine());

            A = new int[rNum][,];
            B = new int[rNum][,];
            AB = new int[rNum][,];

            for (int i = 0; i < rNum; i++)
            {
                string[] sizeOfArray = sr.ReadLine().Split(new char[] { ',' });
                int m = int.Parse(sizeOfArray[0]);
                int r = int.Parse(sizeOfArray[1]);
                int n = int.Parse(sizeOfArray[3]);
                A[i] = new int[m, r];
                B[i] = new int[r, n];
                AB[i] = new int[m, n];

                int j, k;
                string[] ts;
                for (j = 0; j < m; j++)
                {
                    ts = sr.ReadLine().Split(new char[] { ' ', '\t' });
                    for (k = 0; k < r; k++)
                        A[i][j, k] = int.Parse(ts[k]);
                }

                for (j = 0; j < r; j++)
                {
                    ts = sr.ReadLine().Split(new char[] { ' ', '\t' });
                    for (k = 0; k < n; k++)
                        B[i][j, k] = int.Parse(ts[k]);
                }

                for (j = 0; j < m; j++)
                {
                    ts = sr.ReadLine().Split(new char[] { ' ', '\t' });
                    for (k = 0; k < n; k++)
                        AB[i][j, k] = int.Parse(ts[k]);
                }
            }

            sr.Close();
        }
    }
}

3-1:其他—計算位元為1 的個數

Problem 3:其他
子題 1:計算位元為1 的個數。(程式執行限制時間: 2 秒) 10 分
計算機概論中的數字系統轉換,內容是將一個十進位的數字,轉換成二進位的數字。現在請
你設計一個程式,計算由十進位數字(整數)轉換的二進位數字中,位元值為1 的位元個數。
輸入說明:
第一列的數字 n 代表有幾筆資料要測試, 。第二列起為測試資料,測試資料每一列
為一個十進位數字(整數) 。
輸出說明:
對每一列的十進位數字,分別以一列輸出,計算轉換成二位進數字中,位元值為1 的位元個
數。
輸入檔案 1:【檔名:in1.txt】
2
1025
65535
輸入檔案 2:【檔名:in2.txt】
2
0
3
輸出範例:【檔名:out.txt】
2
16
0
2

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;

namespace _3_1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            run("in1.txt", "out.txt", false);
            run("in2.txt", "out.txt", true);
            run("in3.txt", "out.txt", true);
            run("in4.txt", "out.txt", true);
        }

        void run(string inFile, string outFile, bool append)
        {
            StreamWriter sw = new StreamWriter(outFile, append);
            int[] dt;
            rdFile(inFile, out dt);

            for (int i = 0; i < dt.Length; i++)
            {
                int s = 0;
                while(dt[i]!=0)
                {
                    s += (dt[i] % 2);
                    dt[i] = dt[i] / 2;
                }
                sw.WriteLine(s);
            }

            sw.WriteLine();
            sw.Close();
        }

        void rdFile(string inFile, out int[] dt)
        {
            StreamReader sr = new StreamReader(inFile);
            int rNum = int.Parse(sr.ReadLine());
            dt = new int[rNum];
            for (int i = 0; i < rNum; i++)
                dt[i] = int.Parse(sr.ReadLine());
            sr.Close();
        }
    }
}