XYZ的筆記本

2021年5月9日星期日

Golang 安裝 xorm.io/reverse

xorm/reverse 可將資料表結構反轉成程式中給 xorm 使用的struct

[安裝 xorm.io/reverse 發生錯誤]

> go get xorm.io/reverse  
# github.com/mattn/go-sqlite3
cc1.exe: sorry, unimplemented: 64-bit mode not compiled in

原因：系統的 gcc 需支援 64 bit，而我早期裝的是 32 bit

> gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=c:/mingw/bin/../libexec/gcc/mingw32/4.7.2/lto-wrapper.exe
Target: mingw32
Configured with: ../gcc-4.7.2/configure --enable-languages=c,c++,ada,fortran,objc,obj-c++ --disable-sjlj-exceptions --with-dwarf2 --enable-shared --enable-libgomp --disable-win32-registry --enable-libstdcxx-debug --disable-build-poststage1-with-cxx --enable-version-specific-runtime-libs --build=mingw32 --prefix=/mingw
Thread model: win32
gcc version 4.7.2 (GCC)

[解決辦法：安裝 64 位元的 MinGW-w64]

下載網址

https://sourceforge.net/projects/mingw-w64/

安裝後，將 MinGW-w64 的 bin 目錄，加入使用者環境變數

C:\Program Files\mingw-w64\x86_64-8.1.0-win32-seh-rt_v6-rev0\mingw64\bin

如果暫時不想刪除舊的 C:\MinGW\bin，須將新的路徑，移到上方

重啟命令視窗，即可讀取到新的設定(若是在 VS code Terminal，須將所有 VS code 關閉重啟)

> gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=C:/Program\ Files/mingw-w64/x86_64-8.1.0-win32-seh-rt_v6-rev0/mingw64/bin/../libexec/gcc/x86_64-w64-mingw32/8.1.0/lto-wrapper.exe
Target: x86_64-w64-mingw32
Configured with: ../../../src/gcc-8.1.0/configure --host=x86_64-w64-mingw32 --build=x86_64-w64-mingw32 --target=x86_64-w64-mingw32 --prefix=/mingw64 --with-sysroot=/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64 --enable-shared --enable-static --disable-multilib --enable-languages=c,c++,fortran,lto --enable-libstdcxx-time=yes --enable-threads=win32 --enable-libgomp --enable-libatomic --enable-lto --enable-graphite --enable-checking=release --enable-fully-dynamic-string --enable-version-specific-runtime-libs --disable-libstdcxx-pch --disable-libstdcxx-debug --enable-bootstrap --disable-rpath --disable-win32-registry --disable-nls --disable-werror --disable-symvers --with-gnu-as --with-gnu-ld --with-arch=nocona --with-tune=core2 --with-libiconv --with-system-zlib --with-gmp=/c/mingw810/prerequisites/x86_64-w64-mingw32-static --with-mpfr=/c/mingw810/prerequisites/x86_64-w64-mingw32-static --with-mpc=/c/mingw810/prerequisites/x86_64-w64-mingw32-static --with-isl=/c/mingw810/prerequisites/x86_64-w64-mingw32-static --with-pkgversion='x86_64-win32-seh-rev0, Built by MinGW-W64 project' --with-bugurl=https://sourceforge.net/projects/mingw-w64 CFLAGS='-O2 -pipe -fno-ident -I/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64/opt/include -I/c/mingw810/prerequisites/x86_64-zlib-static/include -I/c/mingw810/prerequisites/x86_64-w64-mingw32-static/include' CXXFLAGS='-O2 -pipe -fno-ident -I/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64/opt/include -I/c/mingw810/prerequisites/x86_64-zlib-static/include -I/c/mingw810/prerequisites/x86_64-w64-mingw32-static/include' CPPFLAGS=' -I/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64/opt/include -I/c/mingw810/prerequisites/x86_64-zlib-static/include -I/c/mingw810/prerequisites/x86_64-w64-mingw32-static/include' LDFLAGS='-pipe -fno-ident -L/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64/opt/lib -L/c/mingw810/prerequisites/x86_64-zlib-static/lib -L/c/mingw810/prerequisites/x86_64-w64-mingw32-static/lib '
Thread model: win32
gcc version 8.1.0 (x86_64-win32-seh-rev0, Built by MinGW-W64 project)

確定設定生效後，即可正常安裝
安裝後會在 GOPATH 底下的 bin 目錄，產生 reverse.exe 執行檔

> go get xorm.io/reverse  
go get: added xorm.io/reverse v0.1.1

[xorm reverse 用法]
1.新增設定文件，例如檔名為 custom.yml，內容為

kind: reverse
name: aa #資料庫名稱
source:
  database: mysql #哪一種資料庫
  conn_str: 'root:密碼@tcp(127.0.0.1:3306)/aa' #連接資料庫設定
targets:
- type: codes
  language: golang
  output_dir: ./test_model #輸出在哪個目錄底下

2.執行將資料表轉換成程式碼結構的命令

> reverse -f custom.yml

結果會在 output_dir(./test_model)目錄下產生 models.go 檔

aa資料庫底下，有兩個資料表a1、b1

CREATE TABLE `a1` (
  `a_id` int(11) NOT NULL,
  `a_data` varchar(45) DEFAULT NULL,
  `a_dtime` datetime DEFAULT NULL,
  `a_cur_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='測試1';

CREATE TABLE `b1` (
  `b_id` int(11) NOT NULL,
  `b_num` int(10) UNSIGNED ZEROFILL DEFAULT NULL COMMENT '測試',
  `b_num2` int(11) NOT NULL,
  `b_data` decimal(11,2) NOT NULL,
  `b_add_dtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `a1`
  ADD PRIMARY KEY (`a_id`),
  ADD UNIQUE KEY `a_data` (`a_data`);

ALTER TABLE `b1`
  ADD PRIMARY KEY (`b_id`);


ALTER TABLE `a1`
  MODIFY `a_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `b1`
  MODIFY `b_id` int(11) NOT NULL AUTO_INCREMENT;

產生的 models.go 檔內容如下，轉成 xorm 的Column屬性定義：

https://gobook.io/read/gitea.com/xorm/manual-zh-CN/chapter-02/4.columns.html

package models

import (
	"time"
)

type A1 struct {
	AId      int       `xorm:"not null pk autoincr INT(11)"`
	AData    string    `xorm:"unique VARCHAR(45)"`
	ADtime   time.Time `xorm:"DATETIME"`
	ACurTime time.Time `xorm:"TIMESTAMP"`
}

type B1 struct {
	BId       int       `xorm:"not null pk autoincr INT(11)"`
	BNum      int       `xorm:"comment('測試') INT(10)"`
	BNum2     int       `xorm:"not null INT(11)"`
	BData     string    `xorm:"not null DECIMAL(11,2)"`
	BAddDtime time.Time `xorm:"not null default CURRENT_TIMESTAMP TIMESTAMP"`
}

跟原資料表結構相比，可發現有些細節消失了(資料表COMMENT、欄位UNSIGNED ZEROFILL屬性、...也許還會有其他的)

另外測試將產生的 models.go 內容，用engine.Sync2(new(A1), new(B1)) 自動建立資料表，這些細節也是沒有了。

engine.Sync2()能夠根據程式碼中的資料表結構，部分自動同步建立(修改)資料庫中的資料表，例如：新增欄位可以，但刪除欄位不可以。

參考：

https://gitea.com/xorm/reverse
xorm/reverse: A flexsible and powerful command line tool to convert database to codes - reverse - Gitea: Git with a cup of tea
https://zhuanlan.zhihu.com/p/206458663
Xorm reverse工具安装与使用根据数据库自动生成go代码 - 知乎
https://gobook.io/read/gitea.com/xorm/manual-zh-CN/
XORM操作手册
https://alexmav04.github.io/computer/windows-with-mingw-w64/
[C++]MinGW-w64安裝與設定

2021年5月8日星期六

Nginx 反向代理 WebSocket

設定 https://example.com:9999/socket.io
反向代理到 http://192.168.0.10:9999 的 WebSocket 服務

map $http_upgrade $connection_upgrade {
    default Upgrade;
    '' close;
}

upstream websocket {
    server 192.168.0.10:9999;
}

server {
    listen 9999 ssl;
    server_name example.com;
    
    #ssl
    ssl_certificate /etc/nginx/ssl/example.com.crt;
    ssl_certificate_key /etc/nginx/ssl/example.com.key;

    location /socket.io {
        proxy_pass http://websocket;
        #Version 1.1 is recommended for use with keepalive connections
        proxy_http_version 1.1; #WebSocket
        proxy_set_header Upgrade $http_upgrade; #WebSocket
        proxy_set_header Connection $connection_upgrade; #WebSocket       
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto https;
        proxy_set_header Cookie $http_cookie;
    }
    
    location = /favicon.ico {
        log_not_found off;
        access_log off;
    }
}

參考：

https://www.nginx.com/blog/websocket-nginx/
Using NGINX as a WebSocket Proxy
https://single9.net/2018/03/nginx-reverse-proxy-server-and-socket-io/
老闆！來一個能支援 Socket.io 的 NGINX Reverse Proxy Server 吧！ | Single.9
https://kknews.cc/code/pn42zoj.html
伺服器nginx下websocket nginx 配置心得分享
https://github.com/REBELinBLUE/deployer/issues/310
WebSocket connection handshake Error 502 · Issue #310 · REBELinBLUE/deployer · GitHub

2021年4月7日星期三

Windows 視窗置頂

C# 列出 windows 目前開啟的視窗(Process)，將選擇的視窗(Process)設為置頂，固定在最上層。

程式碼：

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

namespace WinTopMost
{
    public partial class Form1 : Form
    {
        [DllImport("user32.dll", SetLastError = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool SetWindowPos(IntPtr hWnd, int hWndInsertAfter, int x, int y, int cx, int cy, int uFlags);
        private const int HWND_TOPMOST = -1;
        private const int HWND_NOTOPMOST = -2;
        private const int SWP_NOMOVE = 0x0002;
        private const int SWP_NOSIZE = 0x0001;

        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowLong(IntPtr hWnd, int nIndex);
        const int GWL_EXSTYLE = -20;
        const int WS_EX_TOPMOST = 0x0008;

        private ArrayList ProcessDataArrList;

        public Form1()
        {
            InitializeComponent();
            LoadProcesses();
        }


        //取得Process
        private void LoadProcesses()
        {
            //this.listBox1.Items.Clear();
            Process[] allProcesses = Process.GetProcesses();

            this.ProcessDataArrList = new ArrayList();
            foreach (Process process in allProcesses)
            {
                if (this.chkWinTitleNoEmpty.Checked && "" == process.MainWindowTitle)
                {
                    //略過MainWindowTitle為空
                    continue;
                }
                bool isTop = IsWindowTopMost(process.MainWindowHandle);
                if (this.chkOnlyTopWin.Checked && !isTop)
                {
                    //略過非置頂
                    continue;
                }
                string isTopStr = isTop ? "Top" : "X  ";
                string name = isTopStr + " # " + process.MainWindowTitle + " # " + process.Id + " # " + process.ProcessName;
                this.ProcessDataArrList.Add(new ProcessData(process, name));
            }

            this.listBox1.DataSource = ProcessDataArrList;
            this.listBox1.ValueMember = "MyKey";
            this.listBox1.DisplayMember = "MyName";
        }

        //視窗是否置頂
        private bool IsWindowTopMost(IntPtr hWnd)
        {
            int exStyle = GetWindowLong(hWnd, GWL_EXSTYLE);
            return (exStyle & WS_EX_TOPMOST) == WS_EX_TOPMOST;
        }

        //置頂
        private void btnTopMost_Click(object sender, EventArgs e)
        {
            if (this.listBox1.SelectedIndex != -1)
            {
                ProcessData ProcessData = this.listBox1.SelectedItem as ProcessData;
                SetWindowPos(ProcessData.MyKey.MainWindowHandle,
                    HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE);

                LoadProcesses();
            }
        }

        //取消置頂
        private void btnNoTopMost_Click(object sender, EventArgs e)
        {
            if (this.listBox1.SelectedIndex != -1)
            {
                ProcessData ProcessData = this.listBox1.SelectedItem as ProcessData;
                SetWindowPos(ProcessData.MyKey.MainWindowHandle,
                    HWND_NOTOPMOST, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE);

                LoadProcesses();
            }
        }

        //更新列表
        private void btnRefreshList_Click(object sender, EventArgs e)
        {
            LoadProcesses();
        }

        //MainWindowTitle不為空
        private void chkWinTitleNoEmpty_CheckedChanged(object sender, EventArgs e)
        {
            LoadProcesses();
        }

        //只列出置頂視窗
        private void chkOnlyTopWin_CheckedChanged(object sender, EventArgs e)
        {
            LoadProcesses();
        }

        //連結網址
        private void linkLabelxyz_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string target = "https://xyz.cinc.biz";
            try
            {
                //System.Diagnostics.Process.Start(target);//系統找不到指定的檔案
                //https://stackoverflow.com/questions/21835891/process-starturl-fails
                System.Diagnostics.Process.Start(new ProcessStartInfo(target) { UseShellExecute = true });
            }
            catch (System.ComponentModel.Win32Exception noBrowser)
            {
                if (noBrowser.ErrorCode == -2147467259)
                    MessageBox.Show(noBrowser.Message);
            }
            catch (System.Exception other)
            {
                MessageBox.Show(other.Message);
            }
        }


    }

    public class ProcessData
    {
        private Process pKey;
        private string pName;

        public ProcessData(Process process, string name)
        {

            this.pKey = process;
            this.pName = name;
        }

        public Process MyKey
        {
            get
            {
                return pKey;
            }
        }

        public string MyName
        {

            get
            {
                return pName;
            }
        }
    }
}

執行畫面：

檔案下載：
https://drive.google.com/uc?export=download&id=11-f-e5sxz_yYdu-csaKu3CuUUzuItS--

SHA256：
ddebfe2fae4009667b2401d746f8113d57bb799f751c8056df2eec208c5e03ab

參考：

https://docs.microsoft.com/zh-tw/dotnet/api/system.diagnostics.process.getprocesses?view=net-5.0
Process.GetProcesses 方法 (System.Diagnostics)
https://stackoverflow.com/questions/36952600/determine-if-a-window-is-topmost-or-not
c# - Determine if a window is topmost or not - Stack Overflow
https://stackoverflow.com/a/61035650
c# - Process.Start(url) fails - Stack Overflow
https://docs.microsoft.com/zh-tw/troubleshoot/dotnet/csharp/start-internet-browser
使用 Visual c # 方法來啟動瀏覽器 - C#
https://www.codeproject.com/Tips/793032/Set-window-OnTop-in-running-programs-from-Csharp
Set window OnTop in running programs from C# - CodeProject
https://docs.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-setwindowpos
SetWindowPos function (winuser.h) - Win32 apps
https://docs.microsoft.com/zh-tw/dotnet/api/system.windows.forms.listcontrol.displaymember?view=net-5.0
ListControl.DisplayMember 屬性 (System.Windows.Forms)
https://blog.walterlv.com/post/use-shell-execute-in-process-start-info.html
C#/.NET 中启动进程时所使用的 UseShellExecute 设置为 true 和 false 分别代表什么意思？ - walterlv
https://www.twblogs.net/a/5d106b58bd9eee1ede049407
C#託管代碼和非託管代碼
https://earthcat99.pixnet.net/blog/post/36811169
DLLImport 使用方式 @ 神喵的筆記簿 :: 痞客邦 ::
https://docs.microsoft.com/zh-tw/dotnet/standard/native-interop/best-practices
原生互通性最佳做法 - .NET (DllImport 屬性設定)
https://docs.microsoft.com/zh-tw/dotnet/api/system.runtime.interopservices.dllimportattribute?view=net-5.0
DllImportAttribute 類別 (System.Runtime.InteropServices)
https://docs.microsoft.com/zh-cn/dotnet/api/system.runtime.interopservices.marshal?view=net-5.0
Marshal 类 (System.Runtime.InteropServices)
https://www.cnblogs.com/del/archive/2008/02/12/1067358.html
WinAPI: SetWindowPos - 改变窗口的位置与状态 - 万一 - 博客园

2021年3月31日星期三

Google Sheets 抓上櫃股票股價

在 Google Sheets 試算表中，上市股票股價可以使用 GOOGLEFINANCE() 取得股價。

上櫃OTC股票則需另外用指令碼處理。

新增一個工作表，假設名稱為「上櫃股票」，並填入股票代碼。
如下圖：
「工具」 -> 「指令碼編輯器」

填入以下程式碼，選擇執行「mainOTC()」

function getDataOTC(sheet_name, col_code, col_stock_name, col_price, col_updtime, col_err, row_start, row_end) {
  var spreadsheet = SpreadsheetApp.getActive();
  var rows = spreadsheet.getRange(sheet_name + "!" + col_code + row_start + ":" + col_code + row_end);
  var numRows = rows.getNumRows();
  var data = rows.getValues();
  var options = {
    //若來源網址SSL不安全(或過期)出現Exception: SSL Error，且確定來源資料安全時，可關閉validateHttpsCertificates
    //'validateHttpsCertificates': false
  };
  for (var i = 0; i < numRows; i++) {
    var code = data[i][0];
    //上市
    //https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=tse_2353.tw&json=1&delay=0
    //上櫃
    //https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=otc_6488.tw&json=1&delay=0
    var url = "https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=otc_" + code + ".tw&json=1&delay=0"
    //舊的
    //var url = "https://mis.tse.com.tw/stock/api/getStock.jsp?ch=" + code + ".tw&json=1&_=";
    try {
      var response = UrlFetchApp.fetch(url);
      var json = response.getContentText("UTF-8");
      var data_arr = JSON.parse(json);
      spreadsheet.getRange((sheet_name + "!" + col_stock_name) + (row_start + i)).setValue(data_arr.msgArray[0].n);//股票名稱
      // data_arr.msgArray[0].y 股價(昨天成交價)
      // data_arr.msgArray[0].z 股價(最近成交價)
      var stock_price = data_arr.msgArray[0].z.trim();
      if (isNaN(parseFloat(stock_price))) {
        throw ("股價數值異常:" + stock_price);//盤中可能抓不到股價，抓到"-"
      }
      spreadsheet.getRange((sheet_name + "!" + col_price) + (row_start + i)).setValue(stock_price);
      spreadsheet.getRange((sheet_name + "!" + col_updtime) + (row_start + i)).setValue("股價更新:" + Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss"));
      spreadsheet.getRange((sheet_name + "!" + col_err) + (row_start + i)).setValue("");
    } catch (e) {
      spreadsheet.getRange((sheet_name + "!" + col_err) + (row_start + i)).setValue("Err:" + Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss") + " " + e);
    }
    Utilities.sleep(1000);//等1秒再繼續下一個
  }
}

function mainOTC() {
  getDataOTC(
    "上櫃股票", //工作表名稱
    "A",//股票代碼在工作表的欄位
    "B",//股票名稱在工作表的欄位
    "C",//股價在工作表的欄位
    "D",//更新時間在工作表的欄位
    "E",//錯誤訊息在工作表的欄位
    2,  //資料從第幾列開始
    21  //資料在第幾列結束
  );
}

因為要在工作表自動填入資料，所以須授權程式可存取 Google Docs
若之後要取消授權，可於 https://myaccount.google.com/permissions?pli=1 取消。
抓取結果
可設定「觸發條件」，讓程式每隔一段時間自動執行，避免每次都要手動執行。

參考：

https://stackoverflow.com/questions/24867386/ssl-error-when-making-a-request-via-urlfetchapp
SSL Error when making a request via UrlFetchApp
https://zys-notes.blogspot.com/2020/01/api.html
證交所即時資訊API

2021年2月25日星期四

jQuery trggie 傳參數

$('#test').on('change', function (event, a1, a2) {
    console.log(event); //The event object is always passed as the first parameter to an event handler

    console.log(a1); //aaa
    console.log(a2); //bbb
});

//第二個列陣參數，可用來傳其它參數
$('#test').trigger('change', ['aaa', 'bbb']);

參考：
https://api.jquery.com/trigger/
https://stackoverflow.com/questions/16401538/passing-parameters-on-jquery-trigger

訂閱：文章 (Atom)

網頁

2021年5月9日 星期日