瀏覽分類:

postgresql

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

 

 

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 $$;