需求
- 开发一套跨平台的连接openGauss数据库的可视化的桌面版应用系统;
- 在应用系统与openGauss之间建立数据交互逻辑,实现数据的增、删、改、查;
![ec97ca46-0d79-4f57-97f0-736a726db38a.jpg](https://oscimg.oschina.net/oscnet/ec97ca46-0d79-4f57-97f0-736a726db38a.jpg)
环境准备
- 服务器
- openEuler-22.03-LTS-SP3
- 名称:localhost.localdomain
- IP地址:192.168.192.129
- openGauss 6.0.0-RC1
- 客户端
- UOS Desktop 20 Pro
- IP地址:192.168.192.128
- 银河麒麟桌面操作系统V10 (SP1)
- IP地址:192.168.192.142
- Windows 10 专业版,64位操作系统
- IP地址:192.168.1.3
- Windows
- Kylin
- UOS
- 语言:pascal
- 测试语句
drop table if exists staffs;
CREATE TABLE staffs
(
staff_id INT not null,
first_name VARCHAR(20),
last_name VARCHAR(25),
email VARCHAR(25),
phone_number VARCHAR(20),
hire_date DATE,
employment_id VARCHAR(10),
salary NUMERIC(8,2),
commission_pct NUMERIC(2,2),
manager_id NUMERIC(6),
section_id NUMERIC(4)
);
insert into staffs values (1, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', 'SH_CLERK', 2200.00, null, 124, 50);
insert into staffs values (2, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', 'SH_CLERK', 4000.00, null, 124, 50);
insert into staffs values (3, 'Jennie', 'Grant', 'SMITH', '650.507.9855', 'HZ_CLERK', 5000.00, null, 124, 51);
select * from staffs order by staff_id;
update staffs set first_name='Jack' where staff_id=2;
delete from staffs where staff_id=2;
openEuler环境
![f9ba3544-aba1-4de0-8bf4-6b86448986f7.png](https://oscimg.oschina.net/oscnet/f9ba3544-aba1-4de0-8bf4-6b86448986f7.png)
Windwos环境
![48f2381a-6d7b-46b0-9a11-c2ffca506af8.png](https://oscimg.oschina.net/oscnet/48f2381a-6d7b-46b0-9a11-c2ffca506af8.png)
Kylin环境
![4147e055-f5f6-4698-b326-e39c64d461a4.png](https://oscimg.oschina.net/oscnet/4147e055-f5f6-4698-b326-e39c64d461a4.png)
UOS环境
![fa8687c6-3707-42cf-9b99-87283a971de4.png](https://oscimg.oschina.net/oscnet/fa8687c6-3707-42cf-9b99-87283a971de4.png)
![faaf4ba4-4af9-41e6-aa9e-fc128104d4b5.jpg](https://oscimg.oschina.net/oscnet/faaf4ba4-4af9-41e6-aa9e-fc128104d4b5.jpg)
openGauss 服务设置
查看数据库启动状态
- 切换至omm用户
[root@localhost ~]# su omm
Welcome to 5.10.0-182.0.0.95.oe2203sp3.x86_64
System information as of time: Sun Jun 2 11:52:31 AM CST 2024
System load: 0.00
Processes: 216
Memory used: 6.2%
Swap used: 0%
Usage On: 8%
IP address: 192.168.192.129
Users online: 2
To run a command as administrator(user "root"),use "sudo <command>".
[omm@localhost root]$
![30572ebb-fabb-4717-887e-f666149706ad.png](https://oscimg.oschina.net/oscnet/30572ebb-fabb-4717-887e-f666149706ad.png)
- 查看数据库启动状态
root]$ gs_om -t status --detail
Cluster State ]
cluster_state : Unavailable
redistributing : No
current_az : AZ_ALL
Datanode State ]
node node_ip port instance state
------------------------------------------------------------------------------------------------------------
1 localhost.localdomain 192.168.192.129 15400 6001 /opt/huawei/install/data/dn P Primary Manually stopped
[omm@localhost root]$
![5f9709f9-8d68-4bf8-81eb-c07fdd0c2328.png](https://oscimg.oschina.net/oscnet/5f9709f9-8d68-4bf8-81eb-c07fdd0c2328.png)
- 启动openGauss
[omm@localhost root]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] localhost.localdomain
2024-06-02 11:54:14.370 665bece5.1 [unknown] 139716790724992 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2024-06-02 11:54:14.370 665bece5.1 [unknown] 139716790724992 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2024-06-02 11:54:14.395 665bece5.1 [unknown] 139716790724992 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3310 Mbytes) is larger.
=========================================
Successfully started.
[omm@localhost root]$
![5a7cf0e8-5c1b-4a84-8518-7848e119c1e2.png](https://oscimg.oschina.net/oscnet/5a7cf0e8-5c1b-4a84-8518-7848e119c1e2.png)
![715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif](https://oscimg.oschina.net/oscnet/715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif)
修改pg_hba.conf文件
目录:/opt/huawei/install/data/dn
[omm@localhost dn]$ pwd
/opt/huawei/install/data/dn
[omm@localhost dn]$
[omm@localhost dn]$ ll
total 5052
drwx------ 6 omm dbgrp 4096 May 31 23:40 base
-rw------- 1 omm dbgrp 4399 May 31 23:40 cacert.pem
-rw------- 1 omm dbgrp 72 Jun 2 11:54 gaussdb.state
drwx------ 3 omm dbgrp 4096 Jun 2 11:54 global
-rw------- 1 omm dbgrp 354 May 31 23:40 gs_gazelle.conf
-rw------- 1 omm dbgrp 4915200 May 31 23:40 gswlm_userinfo.cfg
-rw------- 1 omm dbgrp 21016 May 31 23:40 mot.conf
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_clog
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_csnlog
-rw------- 1 omm dbgrp 0 Jun 2 11:54 pg_ctl.lock
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_errorinfo
-rw------- 1 omm dbgrp 4620 Jun 1 16:25 pg_hba.conf
-rw------- 1 omm dbgrp 4587 May 31 23:40 pg_hba.conf.bak
-rw------- 1 omm dbgrp 1024 May 31 23:40 pg_hba.conf.lock
-rw------- 1 omm dbgrp 1636 May 31 23:40 pg_ident.conf
drwx------ 4 omm dbgrp 4096 May 31 23:40 pg_llog
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_location
drwx------ 2 omm dbgrp 4096 Jun 2 11:55 pg_logical
drwx------ 4 omm dbgrp 4096 May 31 23:40 pg_multixact
drwx------ 2 omm dbgrp 4096 Jun 2 11:54 pg_notify
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_replslot
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_serial
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_snapshots
drwx------ 2 omm dbgrp 4096 Jun 2 11:55 pg_stat_tmp
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_tblspc
drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_twophase
-rw------- 1 omm dbgrp 4 May 31 23:40 PG_VERSION
drwx------ 3 omm dbgrp 4096 May 31 23:40 pg_xlog
-rw------- 1 omm dbgrp 37355 Jun 1 16:29 postgresql.conf
-rw------- 1 omm dbgrp 37380 May 31 23:40 postgresql.conf.guc.bak
-rw------- 1 omm dbgrp 1024 May 31 23:40 postgresql.conf.lock
-rw------- 1 omm dbgrp 71 Jun 2 11:54 postmaster.opts
-rw------- 1 omm dbgrp 89 Jun 2 11:54 postmaster.pid
-rw------- 1 omm dbgrp 0 May 31 23:40 postmaster.pid.lock
-rw------- 1 omm dbgrp 4402 May 31 23:40 server.crt
-rw------- 1 omm dbgrp 1766 May 31 23:40 server.key
-rw------- 1 omm dbgrp 56 May 31 23:40 server.key.cipher
-rw------- 1 omm dbgrp 24 May 31 23:40 server.key.rand
drwx------ 5 omm dbgrp 4096 May 31 23:40 undo
[omm@localhost dn]$
![d921078a-964b-4c55-b772-06ba05698a3e.png](https://oscimg.oschina.net/oscnet/d921078a-964b-4c55-b772-06ba05698a3e.png)
- 增加需要访问计算机的IP地址
- 对所有IP地址进行开放:0.0.0.0/0
- 修改trust替换成md5加密方式
dn]$ vi pg_hba.conf
# PostgreSQL Client Authentication Configuration File
....................
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.192.129/32 md5
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication omm trust
#host replication omm 127.0.0.1/32 trust
#host replication omm ::1/128 trust
![9f9f322b-823c-4186-ae73-f7420d56da92.png](https://oscimg.oschina.net/oscnet/9f9f322b-823c-4186-ae73-f7420d56da92.png)
![715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif](https://oscimg.oschina.net/oscnet/715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif)
修改postgresql.conf文件
- 修改监听地址和加密方式
[omm@node0 dn]$ vi postgresql.conf
[omm@localhost dn]$ vi postgresql.conf
# -----------------------------------------------------------------------------
#
# postgresql_single.conf.sample
# Configuration file for centralized environment
#
# Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
....................
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
....................
password_encryption_type = 0 #Password storage type, 0 is md5 for PG, 1 is sha256 + md5, 2 is sha256 only
....................
![dd417165-3e2c-4f8c-9879-51358330fc8b.png](https://oscimg.oschina.net/oscnet/dd417165-3e2c-4f8c-9879-51358330fc8b.png)
![715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif](https://oscimg.oschina.net/oscnet/715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif)
重启服务
[omm@localhost dn]$ gs_om -t stop
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
[omm@localhost dn]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] localhost.localdomain
2024-06-02 12:07:45.835 665bf011.1 [unknown] 140209249028480 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2024-06-02 12:07:45.835 665bf011.1 [unknown] 140209249028480 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2024-06-02 12:07:45.837 665bf011.1 [unknown] 140209249028480 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3310 Mbytes) is larger.
=========================================
Successfully started.
[omm@localhost dn]$
![53f18be0-73ee-49b1-b194-9fcfb1f00fda.png](https://oscimg.oschina.net/oscnet/53f18be0-73ee-49b1-b194-9fcfb1f00fda.png)
![715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif](https://oscimg.oschina.net/oscnet/715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif)
创建数据库、远程连接角色
[omm@localhost dn]$ gsql -d postgres -p 15400
gsql ((openGauss 6.0.0-RC1 build ed7f8e37) compiled at 2024-03-31 12:44:26 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# create user testuser password 'openGauss!666';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
openGauss=#
openGauss=# create database testdb owner testuser;
CREATE DATABASE
openGauss=#
openGauss=# GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;
GRANT
openGauss=#
openGauss=# GRANT ALL PRIVILEGES TO testuser;
ALTER ROLE
openGauss=#
![b56a66a5-cdee-41f8-881f-69c27c9d4e55.png](https://oscimg.oschina.net/oscnet/b56a66a5-cdee-41f8-881f-69c27c9d4e55.png)
![715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif](https://oscimg.oschina.net/oscnet/715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif)
应用程序开发
文章中的开发代码仅做测试使用,实际开发中,需要根据实际情况进行严谨的逻辑分析。
界面布局
在窗体中标签、文本框、按钮等控件,以输入数据库的相关连接信息、进行数据库连接的测试;![6b8c9bf6-ef98-4eef-adaa-a0007c78d3b7.png](https://oscimg.oschina.net/oscnet/6b8c9bf6-ef98-4eef-adaa-a0007c78d3b7.png)
创建公共函数
//实现数据表的创建,数据的新增、删除、更新等操作
function FunExecSql(vSqlStr: string): Boolean;
var
tmpQuery:TZQuery;//uses ZDataset
begin
Result:=True;
try
tmpQuery := TZQuery.create(nil);
try
with tmpQuery do
begin
Connection := FConnection;
Active:=False;
SQL.Clear;
SQL.Text:=vSqlStr;
ExecSQL;
end;
finally
tmpQuery.Free;
end;
except
Result:=False;
tmpQuery.Free;
end;
end;
数据库连接
Connection := TZConnection.Create(Self);
Connection.Protocol := 'postgresql';
Connection.HostName := edtHostName.Text; //服务器IP
Connection.Database := edtDatabase.Text; //数据库名称
Connection.User := edtUserName.Text; //用户名
Connection.Password := edtPassword.Text; //密码
Connection.Port:=15400; //端口号
创建数据表遍历过程
//数据表记录的遍历
procedure FunGetOpenQuery(vSqlStr: string);
var
tmpQuery:TZQuery;//uses ZDataset
begin
try
tmpQuery := TZQuery.create(nil);
try
with tmpQuery do
begin
Connection := FConnection;
Active:=False;
SQL.Clear;
sql.Add(vSqlStr);
Active:=True;
First;
while not Eof do
begin
memlog.Lines.Add('staff_id='+FieldByName('staff_id').AsString);
memlog.Lines.Add('first_name='+FieldByName('first_name').AsString);
memlog.Lines.Add('last_name='+FieldByName('last_name').AsString);
memlog.Lines.Add('email='+FieldByName('email').AsString);
memlog.Lines.Add('phone_number='+FieldByName('phone_number').AsString);
memlog.Lines.Add('employment_id='+FieldByName('employment_id').AsString);
memlog.Lines.Add('salary='+FieldByName('salary').AsString);
memlog.Lines.Add('commission_pct='+FieldByName('commission_pct').AsString);
memlog.Lines.Add('manager_id='+FieldByName('manager_id').AsString);
memlog.Lines.Add('section_id='+FieldByName('section_id').AsString);
memlog.Lines.Add('*********************************');
Next;
end;
end;
finally
tmpQuery.Free;
end;
except
end;
end;
跨平台编译
界面布局完成,相关函数、过程创建成功,按钮的相关事件编写后,进行应用程序的跨平台编译,包括Windows、Linux等;![448a4ee3-7e1d-4666-9c18-6680c36f79f3.gif](https://oscimg.oschina.net/oscnet/448a4ee3-7e1d-4666-9c18-6680c36f79f3.gif)
执行结果
Windows 环境
- 连接 openGauss 数据库
![9a0ce17d-2a27-431e-9b2e-9c3c29c47efc.png](https://oscimg.oschina.net/oscnet/9a0ce17d-2a27-431e-9b2e-9c3c29c47efc.png)
- 创建数据表
![d0527c82-09a6-4927-85fa-c9f0ad072684.png](https://oscimg.oschina.net/oscnet/d0527c82-09a6-4927-85fa-c9f0ad072684.png)
- 插入数据
![eeea318f-d333-46f0-94da-2358bb9d66cf.png](https://oscimg.oschina.net/oscnet/eeea318f-d333-46f0-94da-2358bb9d66cf.png)
- 插入数据后遍历记录
![65277d33-d350-4017-9a22-e38b3f983aaf.png](https://oscimg.oschina.net/oscnet/65277d33-d350-4017-9a22-e38b3f983aaf.png)
- 更新数据
![5ad134d2-5db8-4fe0-b8b2-542560924cea.png](https://oscimg.oschina.net/oscnet/5ad134d2-5db8-4fe0-b8b2-542560924cea.png)
- 更新数据后遍历记录
![a9006222-fe50-443f-9481-e2cc987910f2.png](https://oscimg.oschina.net/oscnet/a9006222-fe50-443f-9481-e2cc987910f2.png)
- 删除数据
![66f1c542-76a7-4dd0-b5dd-f7700167971c.png](https://oscimg.oschina.net/oscnet/66f1c542-76a7-4dd0-b5dd-f7700167971c.png)
- 删除数据后遍历记录
![02290443-8055-4ff1-86c5-f3d03bb47127.png](https://oscimg.oschina.net/oscnet/02290443-8055-4ff1-86c5-f3d03bb47127.png)
![715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif](https://oscimg.oschina.net/oscnet/715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif)
Kylin 环境
- 连接 openGauss 数据库
![c6d3a5de-dc8a-4e61-8817-ddc7e79c2738.png](https://oscimg.oschina.net/oscnet/c6d3a5de-dc8a-4e61-8817-ddc7e79c2738.png)
- 创建数据表
![1e8a3233-4829-4405-9519-5ceb5aadb9e0.png](https://oscimg.oschina.net/oscnet/1e8a3233-4829-4405-9519-5ceb5aadb9e0.png)
- 插入数据
![120bc8d3-d70f-46c9-929e-e9928f33e0ac.png](https://oscimg.oschina.net/oscnet/120bc8d3-d70f-46c9-929e-e9928f33e0ac.png)
- 插入数据遍历记录
![630c938b-1e2b-4845-b6d4-3d56c9e45d00.png](https://oscimg.oschina.net/oscnet/630c938b-1e2b-4845-b6d4-3d56c9e45d00.png)
- 更新数据
![96310647-4edb-4842-bf19-9e53976b3e7d.png](https://oscimg.oschina.net/oscnet/96310647-4edb-4842-bf19-9e53976b3e7d.png)
- 更新数据后遍历记录
![31522cfc-d069-426f-b5a0-784dc7adcb50.png](https://oscimg.oschina.net/oscnet/31522cfc-d069-426f-b5a0-784dc7adcb50.png)
- 删除数据
![14083b2c-3d4e-45bf-bcef-2391f24fac1d.png](https://oscimg.oschina.net/oscnet/14083b2c-3d4e-45bf-bcef-2391f24fac1d.png)
- 删除数据后遍历记录
![a048861b-4fce-414f-9a73-fb4666d489f8.png](https://oscimg.oschina.net/oscnet/a048861b-4fce-414f-9a73-fb4666d489f8.png)
![715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif](https://oscimg.oschina.net/oscnet/715ba4e5-ced2-42cb-85cb-9c6c9a581549.gif)
UOS 环境
- 连接 openGauss 数据库
![13e040d0-f7d8-4217-a045-efb39071bacf.png](https://oscimg.oschina.net/oscnet/13e040d0-f7d8-4217-a045-efb39071bacf.png)
- 创建数据表
![570c2332-7711-4e0a-97bd-76113eb655e4.png](https://oscimg.oschina.net/oscnet/570c2332-7711-4e0a-97bd-76113eb655e4.png)
- 插入数据
![e88bc96a-00c2-4867-9a12-4cd43b0ed977.png](https://oscimg.oschina.net/oscnet/e88bc96a-00c2-4867-9a12-4cd43b0ed977.png)
- 插入数据后遍历记录
![b3240e64-377c-491f-8175-259601a6626f.png](https://oscimg.oschina.net/oscnet/b3240e64-377c-491f-8175-259601a6626f.png)
- 更新数据
![27fe1fa9-ecbd-4655-b29e-72d0c1a8f91e.png](https://oscimg.oschina.net/oscnet/27fe1fa9-ecbd-4655-b29e-72d0c1a8f91e.png)
- 更新数据后遍历记录
![c1c77aa9-f4b5-4a6c-95ed-ca096e7777e2.png](https://oscimg.oschina.net/oscnet/c1c77aa9-f4b5-4a6c-95ed-ca096e7777e2.png)
- 删除数据
![d3571724-890a-43fc-a5b3-63aa2d90d951.png](https://oscimg.oschina.net/oscnet/d3571724-890a-43fc-a5b3-63aa2d90d951.png)
- 删除数据后遍历记录
![0c1e2d28-80e7-4231-8721-90a5e30a27e9.png](https://oscimg.oschina.net/oscnet/0c1e2d28-80e7-4231-8721-90a5e30a27e9.png)
总结
通过合理的设计和开发,结合openGauss 数据库的高性能特性,可以创建出一个高效、稳定且易于使用的应用程序,满足用户的需求并提升工作效率,使得应用能够触及更广泛的用户基础,可以有效支持企业级的应用需求。点击阅读全文可跳转至原文
本文分享自微信公众号 - openGauss(openGauss)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“ OSC源创计划 ”,欢迎正在阅读的你也加入,一起分享。