openGauss数据驾驭:跨平台开发实战

07/02 18:00
阅读数 11

需求

  • 开发一套跨平台的连接openGauss数据库的可视化的桌面版应用系统;
  • 在应用系统与openGauss之间建立数据交互逻辑,实现数据的增、删、改、查;

环境准备

  • 服务器
    • 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环境
Windwos环境
Kylin环境
UOS环境

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.00Processes: 216Memory used: 6.2%Swap used: 0%Usage On: 8%IP address: 192.168.192.129Users online: 2To run a command as administrator(user "root"),use "sudo <command>".[omm@localhost root]$
  • 查看数据库启动状态
[omm@localhost root]$ gs_om -t status --detail[   Cluster State   ]
cluster_state : Unavailableredistributing : Nocurrent_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]$
  • 启动openGauss
[omm@localhost root]$ gs_om -t startStarting cluster.=========================================[SUCCESS] localhost.localdomain2024-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 sockets2024-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 sockets2024-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]$

修改pg_hba.conf文件

目录:/opt/huawei/install/data/dn
[omm@localhost dn]$ pwd/opt/huawei/install/data/dn[omm@localhost dn]$[omm@localhost dn]$ lltotal 5052drwx------ 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.statedrwx------ 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.confdrwx------ 2 omm dbgrp    4096 May 31 23:40 pg_clogdrwx------ 2 omm dbgrp    4096 May 31 23:40 pg_csnlog-rw------- 1 omm dbgrp       0 Jun  2 11:54 pg_ctl.lockdrwx------ 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.confdrwx------ 4 omm dbgrp    4096 May 31 23:40 pg_llogdrwx------ 2 omm dbgrp    4096 May 31 23:40 pg_locationdrwx------ 2 omm dbgrp    4096 Jun  2 11:55 pg_logicaldrwx------ 4 omm dbgrp    4096 May 31 23:40 pg_multixactdrwx------ 2 omm dbgrp    4096 Jun  2 11:54 pg_notifydrwx------ 2 omm dbgrp    4096 May 31 23:40 pg_replslotdrwx------ 2 omm dbgrp    4096 May 31 23:40 pg_serialdrwx------ 2 omm dbgrp    4096 May 31 23:40 pg_snapshotsdrwx------ 2 omm dbgrp    4096 Jun  2 11:55 pg_stat_tmpdrwx------ 2 omm dbgrp    4096 May 31 23:40 pg_tblspcdrwx------ 2 omm dbgrp    4096 May 31 23:40 pg_twophase-rw------- 1 omm dbgrp       4 May 31 23:40 PG_VERSIONdrwx------ 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.randdrwx------ 5 omm dbgrp    4096 May 31 23:40 undo[omm@localhost dn]$
  • 增加需要访问计算机的IP地址
  • 对所有IP地址进行开放:0.0.0.0/0
  • 修改trust替换成md5加密方式
[omm@localhost dn]$ vi pg_hba.conf
# PostgreSQL Client Authentication Configuration File
....................
# IPv4 local connections:host all all 127.0.0.1/32 trusthost all all 192.168.192.129/32 md5host 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

修改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
....................

重启服务

[omm@localhost dn]$ gs_om -t stopStopping cluster.=========================================Successfully stopped cluster.=========================================End stop cluster.[omm@localhost dn]$ gs_om -t startStarting cluster.=========================================[SUCCESS] localhost.localdomain2024-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 sockets2024-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 sockets2024-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]$

创建数据库、远程连接角色

[omm@localhost dn]$ gsql -d postgres -p 15400gsql ((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 ROLEopenGauss=#openGauss=# create database testdb owner testuser;CREATE DATABASEopenGauss=#openGauss=# GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;GRANTopenGauss=#openGauss=# GRANT ALL PRIVILEGES TO testuser;ALTER ROLEopenGauss=#

应用程序开发

文章中的开发代码仅做测试使用,实际开发中,需要根据实际情况进行严谨的逻辑分析。

界面布局

在窗体中标签、文本框、按钮等控件,以输入数据库的相关连接信息、进行数据库连接的测试;

创建公共函数

//实现数据表的创建,数据的新增、删除、更新等操作
function FunExecSql(vSqlStr: string): Boolean;var tmpQuery:TZQuery;//uses ZDatasetbegin 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;  //服务器IPConnection.Database := edtDatabase.Text;  //数据库名称Connection.User := edtUserName.Text;      //用户名Connection.Password := edtPassword.Text;  //密码Connection.Port:=15400;                   //端口号

创建数据表遍历过程

//数据表记录的遍历procedure FunGetOpenQuery(vSqlStr: string);var  tmpQuery:TZQuery;//uses ZDatasetbegin  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等;

执行结果

Windows 环境

  • 连接 openGauss 数据库
  • 创建数据表
  • 插入数据
  • 插入数据后遍历记录
  • 更新数据
  • 更新数据后遍历记录
  • 删除数据
  • 删除数据后遍历记录

Kylin 环境

  • 连接 openGauss 数据库
  • 创建数据表
  • 插入数据
  • 插入数据遍历记录
  • 更新数据
  • 更新数据后遍历记录
  • 删除数据
  • 删除数据后遍历记录

UOS 环境

  • 连接 openGauss 数据库
  • 创建数据表
  • 插入数据
  • 插入数据后遍历记录
  • 更新数据
  • 更新数据后遍历记录
  • 删除数据
  • 删除数据后遍历记录

总结

通过合理的设计和开发,结合openGauss 数据库的高性能特性,可以创建出一个高效、稳定且易于使用的应用程序,满足用户的需求并提升工作效率,使得应用能够触及更广泛的用户基础,可以有效支持企业级的应用需求。

点击阅读全文可跳转至原文

本文分享自微信公众号 - openGauss(openGauss)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部