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

 

 

我常用的 vscode 功能

因為長期需要看紙本文件,紙本文件通常都是白底黑字,所以我非常討厭使用黑色佈景,另外白底在螢幕上又太亮,所以我慣性修改被景色為豆沙色。

ctrl + shift + p

選擇 open setting (json) 命令

選擇 user setting 視窗加入以下 json 即可

"workbench.colorCustomizations":{
        "editor.background": "#efffef"
    },

 

從 linux 連線 mssql

首先要安裝 mssql extension

ctrl + shift + p

選擇連線命令

MS SQL : connect

TCP 連線,最奇耙就是 port 要用逗號,跟 SSMS 一樣,若 TCP 連線有問題則需要在防火牆上設定 1433 port ,及MSSQL 也需要開啟 TCP 、允許遠端連線、組態管理員的 TCP 服務也要打開,最後還需要確認是否為 1433 port

可以參考這篇

最後設定跟 SSMS 一樣的連線 config 即可

192.168.0.1,1433

user name

pwd

執行查詢

MS SQL: Execute Query

 

 

 

 

 

 

 

 

賽專案 隨手速記

因為賽專案沒講好,導致之前做的前功盡棄,隨手紀錄救火過程

Repository

http://gn870988-blog.logdown.com/posts/7816198-example-of-c-dapper-unitofwork-repository-using-dappersimplecrud

Dapper.SimpleCRUD

https://github.com/ericdc1/Dapper.SimpleCRUD

忘了裝 web api 補救

https://blog.miniasp.com/post/2015/02/18/How-to-Add-Web-API-to-ASPNET-MVC

web api 跨域

https://dotblogs.com.tw/maduka/2018/01/29/214811

遇到 legacy code 一直用 where 1 = 1 記得 N 年前讀 sql 時有看過原理,不過現在忘得差不多了,幾乎已經是寫完就忘了。

https://blog.darkthread.net/blog/where-1-1-and-performance/

linqpad 快速產生對應類別

http://kevintsengtw.blogspot.com/2015/10/dapper-linqpad-sql-command.html

https://poychang.github.io/linqpad-sql-command-class-model/

若出現以下訊息,則是資料庫沒有連線到

CS1061 ‘UserQuery’ does not contain a definition for ‘Connection’ and no extension method ‘Connection’ accepting a first argument of type ‘UserQuery’ could be found (press F4 to add a using directive or assembly reference)

void Main()
{
	// 資料表名稱
	var nameOfTableAndClass = "TableName";
	// 這邊修改為您要執行的 SQL Command
	var sqlCommand = $@"SELECT * FROM {nameOfTableAndClass}";
	// 在 DumpClass 方法裡放 SQL Command 和 Class 名稱
	this.Connection.DumpClass(sqlCommand.ToString(), nameOfTableAndClass).Dump();
}

public static class LINQPadExtensions
{
	private static readonly Dictionary<Type, string> TypeAliases = new Dictionary<Type, string> {
		{ typeof(int), "int" },
		{ typeof(short), "short" },
		{ typeof(byte), "byte" },
		{ typeof(byte[]), "byte[]" },
		{ typeof(long), "long" },
		{ typeof(double), "double" },
		{ typeof(decimal), "decimal" },
		{ typeof(float), "float" },
		{ typeof(bool), "bool" },
		{ typeof(string), "string" }
	};

	private static readonly HashSet<Type> NullableTypes = new HashSet<Type> {
		typeof(int),
		typeof(short),
		typeof(long),
		typeof(double),
		typeof(decimal),
		typeof(float),
		typeof(bool),
		typeof(DateTime)
	};

	public static string DumpClass(this IDbConnection connection, string sql, string className = "Info")
	{
		if (connection.State != ConnectionState.Open)
		{
			connection.Open();
		}

		var cmd = connection.CreateCommand();
		cmd.CommandText = sql;
		var reader = cmd.ExecuteReader();

		var builder = new StringBuilder();
		do
		{
			if (reader.FieldCount <= 1) continue;

			builder.AppendFormat("public class {0}{1}", className, Environment.NewLine);
			builder.AppendLine("{");
			var schema = reader.GetSchemaTable();

			foreach (DataRow row in schema.Rows)
			{
				var type = (Type)row["DataType"];
				var name = TypeAliases.ContainsKey(type) ? TypeAliases[type] : type.Name;
				var isNullable = (bool)row["AllowDBNull"] && NullableTypes.Contains(type);
				var collumnName = (string)row["ColumnName"];

				builder.AppendLine(string.Format("\tpublic {0}{1} {2} {{ get; set; }}", name, isNullable ? "?" : string.Empty, collumnName));
				//builder.AppendLine();
			}

			builder.AppendLine("}");
			builder.AppendLine();
		} while (reader.NextResult());

		return builder.ToString();
	}
}

 

搬到 .net core 上

連線字串

https://dzone.com/articles/dynamic-connection-string-in-net-core

教學文章及影片

https://www.bilibili.com/video/av38392956?from=search&seid=4961455197797726185

發佈

http://www.secretgeek.net/dotnet_run

切換到專案目錄底下注意不是bin資料夾

dotnet run

會去搜尋 appsettings.json 設定並且啟動專案,預設 http://localhost:5000/

在專案資料夾 publish 系統會產生 bin\Debug\netcoreapp2.1\publish

dotnet publish

切換到該目錄執行專案的 dll 即可

dotnet xxx.dll

安裝在 linux 上

https://dotnet.microsoft.com/download/linux-package-manager/ubuntu16-04/sdk-current

產生文件 swagger

https://www.c-sharpcorner.com/article/using-swagger-in-asp-net-core/

要注意需要使用 rest 的命名規則 get , post , delete … 否則會出現 internal server error 500 找不到 json

搬舊版的 web api 到 .net core 上又遇到賽,不管怎麼下 post 參數都出現 null 最後在 api 的 function 上加入 [FromBody]

另外前端 ajax 呼叫時一定要加上以下這兩個參數

contentType: ‘application/json;charset=UTF-8’

data : JSON.stringify(obj)

$.ajax({url: '/ox/getOX',type: 'post',contentType: 'application/json;charset=UTF-8',data: JSON.stringify(obj),success: function(totalCount) {callback(totalCount);}});

參考這兩個老外

https://github.com/aspnet/Mvc/issues/7257

https://andrewlock.net/model-binding-json-posts-in-asp-net-core/

後來又搬一個單純只有 int 的 function 就不用加上 [FromBody] ,真是奇爬

 

 

 

 

 

postgresql 快速產生 c# 類別

https://github.com/fabiortsf/postgreTableToCSharp/blob/master/generate-script-postgre-to-csharp.sql

 

DO $$
DECLARE cnt integer;
DECLARE v_schemaname varchar;
DECLARE v_tablename varchar;

--
DECLARE v_tabela varchar;
DECLARE v_cursor_colunas record;
DECLARE v_nome_coluna varchar;
DECLARE v_classe VARCHAR;
DECLARE v_tipo VARCHAR;
DECLARE v_schema_name VARCHAR;
BEGIN
	cnt := 1;
	--修改這邊
	v_schema_name := 'yourschema';

	 WHILE cnt <= (select count(*) from pg_tables where schemaname = v_schema_name) LOOP
		
		v_tabela :=
		(SELECT tablename FROM (
		select ROW_NUMBER() OVER(ORDER BY tablename) ROWID , tablename
		from pg_tables
		where schemaname = v_schema_name ) mytables
		where ROWID = cnt);


		--- gen class here
		  select table_name INTO v_tabela from information_schema.tables where table_schema = v_schema_name
		  and table_type = 'BASE TABLE'
		  and table_name = v_tabela; 

		   v_classe := E'\r\n' || 'public class ' || v_tabela || ' {' ||  E'\r\n';
		   FOR v_cursor_colunas IN
			SELECT column_name as coluna, is_nullable as isnull, data_type as tipo, character_maximum_length as tamanho
			FROM information_schema.columns
			WHERE table_schema = v_schema_name
			AND table_name   = v_tabela
		   LOOP
		      --TIPOS
		      IF v_cursor_colunas.tipo='character varying' THEN
			v_tipo:= 'string';
		      ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho=1 THEN
			v_tipo:= 'char';
		      ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho<>1 THEN
			v_tipo:= 'string';
		      ELSIF v_cursor_colunas.tipo='timestamp with time zone' THEN
			v_tipo:= 'DateTime';
		      ELSIF v_cursor_colunas.tipo='boolean' THEN
			v_tipo:= 'bool';
		      ELSIF v_cursor_colunas.tipo='integer' THEN
			v_tipo:= 'int';
		      ELSIF v_cursor_colunas.tipo='numeric' THEN
			v_tipo:= 'double';
		      ELSIF v_cursor_colunas.tipo='text' THEN
			v_tipo:= 'string';
		      ELSE
			v_tipo:= 'another';
		      END IF;
		      
		      --ATRIBUTES
		      v_nome_coluna := v_cursor_colunas.coluna;
		      v_classe := v_classe || 'private ' || v_tipo || ' _' || v_cursor_colunas.coluna || ';' || E'\r\n';

		      --PROPERTIES
		      v_classe := v_classe || 'public ' || v_tipo || ' ' || v_cursor_colunas.coluna || '{' || E'\r\n';
		      v_classe := v_classe || '  get {' || ' return _' || v_cursor_colunas.coluna || '; }' || E'\r\n';
		      v_classe := v_classe || '  set {' || ' _' || v_cursor_colunas.coluna || ' = value; }' || E'\r\n';
		      v_classe := v_classe || '}' || E'\r\n';
		      
		   END LOOP;
		   v_classe := v_classe || E'\r\n' || '}';
		   
		  RAISE NOTICE '%' , v_classe;

		--raise notice '%' , v_print_tablename;
		cnt := cnt + 1 ; 


	 END LOOP ; 

END $$;

MS SQL 快速產生 C# 類別

https://habilisbest.com/creating-c-model-class-from-sql-query

 

DECLARE @cnt INT; 

SET @cnt = 1; 

DECLARE @tableCount INT; 

SET @tableCount = (SELECT Count(*) 
                   FROM   sys.tables 
                   WHERE  type = 'U') 

WHILE @cnt <= @tableCount 
BEGIN
	--逐一取得資料表的名稱
	declare @tbName nvarchar(200);
	set @tbName = (
	SELECT name FROM
	(SELECT  ROW_NUMBER() OVER(ORDER BY name) AS ROWID , name
	FROM sys.Tables
	WHERE type = 'U') mytable
	WHERE ROWID = @cnt)

--原作者的 code 產生 csharp 類別
--這段不要 format 產出來才好看
declare @TableName sysname = @tbName
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
	public ' + (CASE WHEN ColumnName = 'RowVersion' THEN 'byte[]' ELSE ColumnType END) + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
	select 
		replace(col.name, ' ', '_') ColumnName,
		column_id ColumnId,
		case typ.name 
			when 'bigint' then 'long'
			when 'binary' then 'byte[]'
			when 'bit' then 'bool'
			when 'char' then 'string'
			when 'date' then 'DateTime'
			when 'datetime' then 'DateTime'
			when 'datetime2' then 'DateTime'
			when 'datetimeoffset' then 'DateTimeOffset'
			when 'decimal' then 'decimal'
			when 'float' then 'float'
			when 'image' then 'byte[]'
			when 'int' then 'int'
			when 'money' then 'decimal'
			when 'nchar' then 'string'
			when 'ntext' then 'string'
			when 'numeric' then 'decimal'
			when 'nvarchar' then 'string'
			when 'real' then 'double'
			when 'smalldatetime' then 'DateTime'
			when 'smallint' then 'short'
			when 'smallmoney' then 'decimal'
			when 'text' then 'string'
			when 'time' then 'TimeSpan'
			when 'timestamp' then 'timestamp'
			when 'rowversion' then 'byte[]'
			when 'tinyint' then 'byte'
			when 'uniqueidentifier' then 'Guid'
			when 'varbinary' then 'byte[]'
			when 'varchar' then 'string'
			else 'UNKNOWN_' + typ.name
		end ColumnType,
		case 
			when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
			then '?' 
			else '' 
		end NullableSign
	from sys.columns col
		join sys.types typ on
			col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
	where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

	print @Result

	--遞增
	set @cnt += 1
END