瀏覽月:

5 月 2019

MADlib安裝過程

#download
#https://askubuntu.com/questions/831292/how-do-i-install-postgresql-9-6-on-any-ubuntu-version

#upgrading
#https://danielepestilli.com/en/blog/posts/3-upgrading-from-postgresql-9-5-to-9-6-on-ubuntu-16-04

#pl/python
#https://stackoverflow.com/questions/43984705/is-there-a-way-to-install-pl-python-after-the-database-has-been-compiled-without

#madlib
#https://cwiki.apache.org/confluence/display/MADLIB/Installation+Guide

#stop postgres
sudo /etc/init.d/postgresql stop

#install 9.6
sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

#step1
sudo pg_dropcluster --stop 9.6 main
sudo pg_upgradecluster -v 9.6 9.5 main

#step2
sudo pg_dropcluster --stop 9.5 main
sudo apt-get purge postgresql-9.5*
sudo apt-get autoremove

#step3
/etc/init.d/postgresql start

#install pl/python(2.7)
sudo apt-get install postgresql-plpython-9.6

#check pl/python install (option)
SELECT * FROM pg_available_extensions WHERE name like '%plpython%';

#run madlib it's work now!!
/usr/local/madlib/bin/madpack -s madlib -p postgres -c postgres/postgres@localhost:5432/test install

SQL Server Linked Postgres 筆記

參考網站
https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration–part-2/

檢查pg版本
select version();
“PostgreSQL 9.5.10, compiled by Visual C++ build 1800, 64-bit”

下載對應的版本jdbc driver 並安裝
https://www.postgresql.org/ftp/odbc/versions/msi/
本例為 psqlodbc_09_05_0100.zip 對應到 9.5.10

開啟 控制台 -> 系統管理工具 -> ODBC 資料來源 (64 位元) -> 系統資料來源名稱 -> 新增 -> PostgreSQL Unicode(x64)
Data Source : PostgreSQL
Database : test
Server : 127.0.0.1
User Name : postgres
Port : 5432
Password : postgres

開啟 sql server 管理工具
伺服器物件 -> 新增 -> 連結的伺服器
連結的伺服器:隨便寫等等用(PG95)
其他資料來源:Microsoft OLE DB Provider for ODBC Drivers
產品名稱:PostgreSQL(跟ODBC的 Data Source名稱要設定一樣他會去抓這個)
資料來源:PostgreSQL(跟ODBC的 Data Source名稱要設定一樣他會去抓這個)

伺服器選項(其他預設就可以)
RPC:true
RPC輸出:true

搞定後就可以下查詢參數
select 欄位
from [LinkServer名稱].[資料庫].[schema名稱].[資料表名稱]

select msg
from PG95.test.[public].qq

從sql server 插入現有資料到postgres LinkServer
INSERT INTO PG95.test.[public].qq(msg)
SELECT Email
FROM Members

解決 asp.net web api ttfb 很慢

參考自

https://www.itread01.com/article/1530756582.html

https://stackoverflow.com/questions/41458420/long-ttfb-on-simple-get-query-in-web-api

https://stackoverflow.com/questions/36326942/how-to-reduce-wait-time-ttfb-for-mvc-application

https://www.cnblogs.com/CreateMyself/p/5408400.html

https://stackoverflow.com/questions/9594229/accessing-session-using-asp-net-web-api

https://blog.darkthread.net/blog/mvc-slow-magic/

因為燙手的案子是硬用 asp.net + asp.net web api 掛在一起,導致很多莫名其妙的事發生,起初還以為是 sql 查詢的問題,但實際執行起來並沒有到想像中的那麼慢。

於是參考了一些文章並且把 session 進行調整,速度馬上有明顯提升。只是不曉得原本開發的那票人是否有用 session 在 code 裡就是,有的話就尷尬了。

解法就是在 Global.asax 這支程式內加上以下 code 性能會有飛快的提升

public override void Init()
    {
        PostAuthenticateRequest += MvcApplication_PostAuthenticateRequest;
        base.Init();
    }

    void MvcApplication_PostAuthenticateRequest(object sender, EventArgs e)
    {
        //HttpContext.Current.SetSessionStateBehavior(
        //  SessionStateBehavior.Required);

        System.Web.HttpContext.Current.SetSessionStateBehavior(SessionStateBehavior.ReadOnly);
    }

asp.net web api recaptcha v3

花了一陣子又搞不太起來,結果反而錯誤是出在 asp.net web api 一直無法接收到參數身上。

關於這個雷我已經踩很多次了,但每次趕時間就又忘了紀錄,當 web api 接收 1 個參數時要加上 [FromBody],前端的jquery 呼叫 ajax 時參數必須寫成 data: JSON.stringify(token), 而 contentType: “application/json” 這樣才可以正確的讓 web api 接收到參數。

參考連結

https://chengjhe.blogspot.com/2018/11/how-to-use-recaptcha-v3.html

https://dotblogs.com.tw/mantou1201/2013/05/29/105059

後端 code

[HttpPost]
public IHttpActionResult VerifyBot([FromBody]string token)
{
	using (var client = new HttpClient())
	{
		client.DefaultRequestHeaders.Accept.Clear();
		client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
		string url = string.Format(
			"https://www.google.com/recaptcha/api/siteverify?secret={0}&response={1}",
			"你的後端金鑰",
			token);
		HttpResponseMessage response = client.GetAsync(url).Result;

		string result = response.Content.ReadAsStringAsync().Result;

		if (response.IsSuccessStatusCode)
		{
			var reCAPTCHA = JsonConvert.DeserializeObject<ReCAPTCHA>(result);

			if (reCAPTCHA.score > 0.5)
			{
				return Ok(reCAPTCHA);
			}
		}

		return Ok("Verify Bot Fail");
	}
	
}

新增類別

public class ReCAPTCHA
    {
        public bool success { get; set; }
        public double score { get; set; }
        public string action { get; set; }
        public DateTime challenge_ts { get; set; }
        public string hostname { get; set; }
    }

前端 code

grecaptcha.ready(function () {

	//對 google發送取得 token
	grecaptcha.execute('前端金鑰', {
		action: '/api/VerifyBot'
	}).then((token) => {
		//跟自己的 server 後端組合 token
		$.ajax({
			type: 'POST',
			url: '/api/VerifyBot',
			data: JSON.stringify(token),
			contentType: "application/json",
			success: (res) => {
				console.log('res:');
				console.log(res);
			}
		});
	});
});

 

 

schemaspy 筆記

老樣子搞了老半天搞不起來筆記一下

可以到這裡下載

https://schemaspy.readthedocs.io/en/latest/installation.html

安裝需要 java8

並且要裝 graphviz 注意要下載 2.38穩定版

https://graphviz.gitlab.io/_pages/Download/Download_windows.html

並且要下載 sql server的 jdbc

https://docs.microsoft.com/zh-tw/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017

將 C:\Program Files (x86)\Graphviz2.38\bin 加入到環境變數

mssql 設定檔,比較重要就是mssql08這個參數要看自己的版本定義

# type of database. Run with -dbhelp for details
schemaspy.t=mssql08
# optional path to alternative jdbc drivers.
schemaspy.dp=D:\schemaspy\mssql-jdbc-7.2.2.jre8.jar
# database properties: host, port number, name user, password
schemaspy.host=127.0.0.1
schemaspy.port=1433
schemaspy.db=test
schemaspy.u=sa
schemaspy.p=test
# output dir to save generated files
schemaspy.o=D:\schemaspy\test
# db scheme for which generate diagrams
schemaspy.s=dbo

如果要用postgres
https://rieckpil.de/howto-simple-database-documentation-with-schemaspy/
postgres jdbc
https://jdbc.postgresql.org/download.html

# database type
schemaspy.t=pgsql
# path to the database JDBC driver
schemaspy.dp=postgresql-42.2.5.jar
schemaspy.host=127.0.0.1
schemaspy.port=5432
# database name
schemaspy.db=postgres
# database user
schemaspy.u=postgres
schemaspy.p=postgres
# output folder for the generated resukt
schemaspy.o=D:\schemaspy\pg
# database schema
schemaspy.s=public

執行
java -jar schemaspy-6.0.0.jar schemaspy.properties

切影片

切影片
https://www.cnblogs.com/codeAB/p/9184266.html

openssl
https://slproweb.com/products/Win32OpenSSL.html

命令
ffmpeg -y -i test.mp4 -hls_time 12 -hls_key_info_file enc.keyinfo -hls_playlist_type vod -hls_segment_filename “file%d.ts” playlist.m3u8

需要確定 Content-Length: 16
如果 Content-Length: 24 會動不了

public ContentResult Key()
{
//我的電腦是 Windows-1252
Encoding wind1252 = Encoding.GetEncoding(1252);
string result = “díq4 æÐÚÅJ¹òý&”;
return Content(result, “text/plain” ,wind1252);
}

web.config 要加
<staticContent>
<remove fileExtension=”.m3u8″ />
<mimeMap fileExtension=”.m3u8″ mimeType=”application/x-mpegURL” />
<remove fileExtension=”.ts” />
<mimeMap fileExtension=”.ts” mimeType=”video/MP2T” />
<remove fileExtension=”.key” />
<mimeMap fileExtension=”.key” mimeType=”text/plain” />
</staticContent>

Accept-Ranges: bytes
Content-Length: 16
Content-Type: text/plain
Date: Tue, 07 May 2019 07:40:39 GMT
ETag: “37c2eee7a04d51:0”
Last-Modified: Tue, 07 May 2019 06:48:40 GMT
Server: Microsoft-IIS/10.0
X-Powered-By: ASP.NET
X-SourceFiles: =?UTF-8?B?QzpcVXNlcnNcQVNVU1xzb3VyY2VccmVwb3NcTVZDSExTXE1WQ0hMU1xtb2dpc1xlbmMudHh0?=

Cache-Control: private
Content-Length: 24
Content-Type: text/plain; charset=utf-8
Date: Tue, 07 May 2019 07:41:23 GMT
Server: Microsoft-IIS/10.0
X-AspNet-Version: 4.0.30319
X-AspNetMvc-Version: 5.2
X-Powered-By: ASP.NET
X-SourceFiles: =?UTF-8?B?QzpcVXNlcnNcQVNVU1xzb3VyY2VccmVwb3NcTVZDSExTXE1WQ0hMU1xIb21lXEtleQ==?=

cache-control →private
content-length →16
content-type →text/plain; charset=Windows-1252
date →Tue, 07 May 2019 08:03:58 GMT
server →Microsoft-IIS/10.0
x-aspnet-version →4.0.30319
x-aspnetmvc-version →5.2
x-powered-by →ASP.NET
x-sourcefiles →=?UTF-8?B?QzpcVXNlcnNcQVNVU1xzb3VyY2VccmVwb3NcTVZDSExTXE1WQ0hMU1xIb21lXEtleQ==?=

PostgREST 喇賽測試

以前從沒在 ubuntu 裝 postgres 多半都用現成的,這次剛好紀錄一下

https://peterli.website/%E5%A6%82%E4%BD%95%E5%9C%A8ubuntu-16-04%E4%B8%8A%E5%AE%89%E8%A3%9D%E8%88%87%E5%AD%98%E5%8F%96postgresql/

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

裝 pgadmin3 , pgadmin4

https://o7planning.org/en/11353/installing-pgadmin-on-ubuntu

sudo apt-get install pgadmin3

用 psql 跟 pgadmin 都開不起來的鬼問題

psql -U postgres -h localhost -W

fatal: password authentication failed for user “postgres”

可以參考這篇

https://blog.csdn.net/wangyezi19930928/article/details/20358369

/etc/postgresql/9.x/main/pg_hba.conf

主要就是把 md5 改成 trust 就對了,修改完後記得重新啟動 postgres 服務

sudo /etc/init.d/postgresql reload
sudo /etc/init.d/postgresql restart

不改的話則需要使用下面命令,不過這方式綁手綁腳,就忘了吧

sudo su - postgres

另外還要開啟 postgres 允許對外部連線

https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

設定 pg_hba.conf

ADDRESS

0.0.0.0/0

設定 postgresql.conf

listen_addresses = ‘*’

設定 ubuntu 開 port 5432

https://www.arthurtoday.com/2013/12/ubuntu-ufw-add-firewall-rules.html

sudo ufw allow in 5432

設定完以後 reboot 比較保險

給實體機連線,只要在 virtualbox 上面新增一張橋接網卡即可

測試 PostgREST

http://postgrest.org/en/v5.2/tutorials/tut0.html

安裝 PostgREST 的 docker

docker pull postgrest/postgrest

連接實體的 Postgres 執行

sudo docker run –rm –net=host -p 3000:3000 \
-e PGRST_DB_URI=”postgres://authenticator:mysecretpassword@localhost/test” \
-e PGRST_DB_ANON_ROLE=”web_anon” \
-e PGRST_DB_SCHEMA=”api” \
postgrest/postgrest

別忘了要開 port 3000 對外才可以訪問

sudo ufw allow in 3000

參數說明

要撈的資料庫網址 -> PGRST_DB_URI

postgres://帳號:密碼@IP位置:PORT/資料庫名稱

範例

postgres://postgres:postgres@192.168.0.10:5432/my_data

使用的腳色 -> PGRST_DB_ANON_ROLE

使用的 SCHEMA -> PGRST_DB_SCHEMA