Skip to content

Commit e87a35d

Browse files
goldenbullchenjn
goldenbull
and
chenjn
authored
feat: add postgresql backend (#872)
Co-authored-by: chenjn <chenjn@vm>
1 parent 1323012 commit e87a35d

File tree

9 files changed

+571
-27
lines changed

9 files changed

+571
-27
lines changed

‎Ui/Service/DataSource/DAO/Dapper/DapperDataBase.cs‎

Lines changed: 43 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@
66
using _1RM.Model.Protocol.Base;
77
using System.Data.SQLite;
88
using MySql.Data.MySqlClient;
9+
using Npgsql;
910
using NUlid;
1011
using Shawn.Utils;
1112
using _1RM.Utils;
@@ -20,6 +21,21 @@ public DapperDatabase(string databaseName, DatabaseType databaseType) : base(dat
2021
{
2122
}
2223

24+
/// <summary>
25+
/// Quote character sqlite MySQL Postgres
26+
/// Double quote (") Identifier String literal Identifier
27+
/// Single quote (') String literal String literal String literal
28+
/// Backtick (`) Identifier Identifier N/A
29+
///
30+
/// So we write SQL in sqlite/MySQL style, using backtick for identifiers
31+
/// and single quote for string literals. When backend is postgres, replace
32+
/// the backtick with double quote
33+
/// </summary>
34+
string NormalizedSql(string sql)
35+
{
36+
return DatabaseType == DatabaseType.PostgreSQL ? sql.Replace('`', '"') : sql;
37+
}
38+
2339
protected IDbConnection? _dbConnection;
2440
public IDbConnection? Connection => _dbConnection;
2541

@@ -78,7 +94,7 @@ protected virtual Result OpenConnection(string actionInfo = "")
7894
DatabaseType.MySql => new MySqlConnection(_connectionString),
7995
DatabaseType.Sqlite => new SQLiteConnection(_connectionString),
8096
DatabaseType.SqlServer => throw new NotImplementedException(DatabaseType.ToString() + " not supported!"),
81-
DatabaseType.PostgreSQL => throw new NotImplementedException(DatabaseType.ToString() + " not supported!"),
97+
DatabaseType.PostgreSQL => new NpgsqlConnection(_connectionString),
8298
DatabaseType.Oracle => throw new NotImplementedException(DatabaseType.ToString() + " not supported!"),
8399
_ => throw new NotImplementedException(DatabaseType.ToString() + " not supported!")
84100
};
@@ -108,6 +124,17 @@ protected virtual Result OpenConnection(string actionInfo = "")
108124
error = mse.Message;
109125
_lastException = mse;
110126
}
127+
catch (NpgsqlException pgse)
128+
{
129+
if (_lastException?.Message != pgse.Message)
130+
{
131+
SimpleLogHelper.Error(pgse);
132+
MsAppCenterHelper.Error(pgse);
133+
}
134+
135+
error = pgse.Message;
136+
_lastException = pgse;
137+
}
111138
catch (TimeoutException te)
112139
{
113140
if (_lastException?.Message != te.Message)
@@ -152,17 +179,16 @@ public override Result InitTables()
152179

153180
try
154181
{
155-
_dbConnection?.Execute(@$"
182+
_dbConnection?.Execute(NormalizedSql(@$"
156183
CREATE TABLE IF NOT EXISTS `{Config.TABLE_NAME}` (
157184
`{nameof(Config.Key)}` VARCHAR (64) PRIMARY KEY
158185
NOT NULL
159186
UNIQUE,
160187
`{nameof(Config.Value)}` TEXT NOT NULL
161188
);
162-
");
163-
189+
"));
164190

165-
_dbConnection?.Execute(@$"
191+
_dbConnection?.Execute(NormalizedSql(@$"
166192
CREATE TABLE IF NOT EXISTS `{Server.TABLE_NAME}` (
167193
`{nameof(Server.Id)}` VARCHAR (64) PRIMARY KEY
168194
NOT NULL
@@ -171,7 +197,7 @@ NOT NULL
171197
`{nameof(Server.ClassVersion)}` VARCHAR (32) NOT NULL,
172198
`{nameof(Server.Json)}` TEXT NOT NULL
173199
);
174-
");
200+
"));
175201
SetEncryptionTest();
176202
}
177203
catch (Exception e)
@@ -213,7 +239,7 @@ public override ResultSelects GetServers()
213239
if (!result.IsSuccess) return ResultSelects.Fail(result.ErrorInfo);
214240
try
215241
{
216-
var ps = _dbConnection.Query<Server>($"SELECT * FROM `{Server.TABLE_NAME}`")
242+
var ps = _dbConnection.Query<Server>(NormalizedSql($"SELECT * FROM `{Server.TABLE_NAME}`"))
217243
.Select(x => x?.ToProtocolServerBase())
218244
.Where(x => x != null).ToList();
219245
return ResultSelects.Success((ps as List<ProtocolBase>)!);
@@ -225,10 +251,11 @@ public override ResultSelects GetServers()
225251
}
226252
}
227253

228-
private const string SqlInsert = $@"INSERT INTO `{Server.TABLE_NAME}`
254+
private string SqlInsert => NormalizedSql($@"INSERT INTO `{Server.TABLE_NAME}`
229255
(`{nameof(Server.Id)}`,`{nameof(Server.Protocol)}`, `{nameof(Server.ClassVersion)}`, `{nameof(Server.Json)}`)
230256
VALUES
231-
(@{nameof(Server.Id)}, @{nameof(Server.Protocol)}, @{nameof(Server.ClassVersion)}, @{nameof(Server.Json)});";
257+
(@{nameof(Server.Id)}, @{nameof(Server.Protocol)}, @{nameof(Server.ClassVersion)}, @{nameof(Server.Json)});");
258+
232259
/// <summary>
233260
/// 插入成功后会更新 protocolBase.Id
234261
/// </summary>
@@ -288,11 +315,11 @@ public override Result AddServer(IEnumerable<ProtocolBase> protocolBases)
288315
}
289316
}
290317

291-
static readonly string SqlUpdate = $@"UPDATE `{Server.TABLE_NAME}` SET
318+
private string SqlUpdate => NormalizedSql($@"UPDATE `{Server.TABLE_NAME}` SET
292319
`{nameof(Server.Protocol)}` = @{nameof(Server.Protocol)},
293320
`{nameof(Server.ClassVersion)}` = @{nameof(Server.ClassVersion)},
294321
`{nameof(Server.Json)}` = @{nameof(Server.Json)}
295-
WHERE `{nameof(Server.Id)}`= @{nameof(Server.Id)};";
322+
WHERE `{nameof(Server.Id)}`= @{nameof(Server.Id)};");
296323
public override Result UpdateServer(ProtocolBase server)
297324
{
298325
string info = IoC.Translate("We can not update on database:");
@@ -357,7 +384,7 @@ public override Result DeleteServer(IEnumerable<string> ids)
357384
if (!result.IsSuccess) return result;
358385
try
359386
{
360-
var ret = _dbConnection?.Execute($@"DELETE FROM `{Server.TABLE_NAME}` WHERE `{nameof(Server.Id)}` IN @{nameof(Server.Id)};", new { Id = ids }) > 0;
387+
var ret = _dbConnection?.Execute(NormalizedSql($@"DELETE FROM `{Server.TABLE_NAME}` WHERE `{nameof(Server.Id)}` IN @{nameof(Server.Id)};"), new { Id = ids }) > 0;
361388
if (ret)
362389
SetDataUpdateTimestamp();
363390
return Result.Success();
@@ -383,7 +410,7 @@ protected ResultString GetConfigPrivate(string key)
383410
if (!result.IsSuccess) return ResultString.Fail(result.ErrorInfo);
384411
try
385412
{
386-
var config = _dbConnection?.QueryFirstOrDefault<Config>($"SELECT * FROM `{Config.TABLE_NAME}` WHERE `{nameof(Config.Key)}` = @{nameof(Config.Key)}",
413+
var config = _dbConnection?.QueryFirstOrDefault<Config>(NormalizedSql($"SELECT * FROM `{Config.TABLE_NAME}` WHERE `{nameof(Config.Key)}` = @{nameof(Config.Key)}"),
387414
new { Key = key, });
388415
return ResultString.Success(config?.Value);
389416
}
@@ -394,9 +421,9 @@ protected ResultString GetConfigPrivate(string key)
394421
}
395422
}
396423

397-
private static readonly string SqlInsertConfig = $@"INSERT INTO `{Config.TABLE_NAME}` (`{nameof(Config.Key)}`, `{nameof(Config.Value)}`) VALUES (@{nameof(Config.Key)}, @{nameof(Config.Value)})";
398-
private static readonly string SqlUpdateConfig = $@"UPDATE `{Config.TABLE_NAME}` SET `{nameof(Config.Value)}` = @{nameof(Config.Value)} WHERE `{nameof(Config.Key)}` = @{nameof(Config.Key)}";
399-
private static readonly string SqlDeleteConfig = $@"Delete FROM `{Config.TABLE_NAME}` WHERE `{nameof(Config.Key)}` = @{nameof(Config.Key)}";
424+
private string SqlInsertConfig => NormalizedSql($@"INSERT INTO `{Config.TABLE_NAME}` (`{nameof(Config.Key)}`, `{nameof(Config.Value)}`) VALUES (@{nameof(Config.Key)}, @{nameof(Config.Value)})");
425+
private string SqlUpdateConfig => NormalizedSql($@"UPDATE `{Config.TABLE_NAME}` SET `{nameof(Config.Value)}` = @{nameof(Config.Value)} WHERE `{nameof(Config.Key)}` = @{nameof(Config.Key)}");
426+
private string SqlDeleteConfig => NormalizedSql($@"Delete FROM `{Config.TABLE_NAME}` WHERE `{nameof(Config.Key)}` = @{nameof(Config.Key)}");
400427

401428
public override Result SetConfig(string key, string? value)
402429
{

‎Ui/Service/DataSource/DAO/IDataBase.cs‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -202,12 +202,16 @@ public static string GetSqliteConnectionString(string dbPath)
202202
{
203203
return $"Data Source={dbPath}; Pooling=true;Min Pool Size=1";
204204
}
205+
205206
public static string GetMysqlConnectionString(string host, int port, string dbName, string user, string password, int connectTimeOutSeconds)
206207
{
207208
return $"server={host};port={port};database={dbName};Character Set=utf8;Uid={user};password={password};Connect Timeout={connectTimeOutSeconds};";
208209
}
209210

210-
211+
public static string GetPgsqlConnectionString(string host, int port, string dbName, string user, string password, int connectTimeOutSeconds)
212+
{
213+
return $"Host={host};Port={port};Database=\"{dbName}\";Username={user};Password={password};Timeout={connectTimeOutSeconds};";
214+
}
211215

212216
//private static string? TryGetConfig(this IDatabase iDatabase, string key)
213217
//{

‎Ui/Service/DataSource/Model/DataSourceBase.Config.cs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@ namespace _1RM.Service.DataSource.Model
1919
[JsonConverter(typeof(JsonKnownTypesConverter<DataSourceBase>))] // json serialize/deserialize derived types https://stackoverflow.com/a/60296886/8629624
2020
[JsonKnownType(typeof(MysqlSource), nameof(MysqlSource))]
2121
[JsonKnownType(typeof(SqliteSource), nameof(SqliteSource))]
22+
[JsonKnownType(typeof(PgsqlSource), nameof(PgsqlSource))]
2223
public abstract partial class DataSourceBase : NotifyPropertyChangedBase
2324
{
2425
protected DataSourceBase()

‎Ui/Service/DataSource/Model/PgsqlSource.cs‎

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,103 @@
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Linq;
4+
using System.Text;
5+
using System.Threading.Tasks;
6+
using _1RM.Service.DataSource.DAO.Dapper;
7+
using _1RM.Service.DataSource.DAO;
8+
using _1RM.Utils;
9+
using Newtonsoft.Json;
10+
11+
namespace _1RM.Service.DataSource.Model
12+
{
13+
public sealed class PgsqlSource : DataSourceBase
14+
{
15+
16+
private string _host = "127.0.0.1";
17+
public string Host
18+
{
19+
get => _host;
20+
set => SetAndNotifyIfChanged(ref _host, value);
21+
}
22+
23+
private int _port = 5432;
24+
public int Port
25+
{
26+
get => _port;
27+
set => SetAndNotifyIfChanged(ref _port, value);
28+
}
29+
30+
private string _databaseName = "1Remote";
31+
public string DatabaseName
32+
{
33+
get => _databaseName;
34+
set => SetAndNotifyIfChanged(ref _databaseName, value);
35+
}
36+
37+
private string _userName = "";
38+
public string UserName
39+
{
40+
get => _userName;
41+
set => SetAndNotifyIfChanged(ref _userName, value);
42+
}
43+
44+
[JsonProperty(nameof(Password))]
45+
private string EncryptPassword { get; set; } = "";
46+
47+
[JsonIgnore]
48+
public string Password
49+
{
50+
get
51+
{
52+
if (string.IsNullOrEmpty(EncryptPassword))
53+
return "";
54+
var t = UnSafeStringEncipher.SimpleDecrypt(EncryptPassword);
55+
if (string.IsNullOrEmpty(t))
56+
return EncryptPassword;
57+
return t ?? "";
58+
}
59+
set
60+
{
61+
EncryptPassword = string.IsNullOrEmpty(value) ? "" : UnSafeStringEncipher.SimpleEncrypt(value);
62+
var t = UnSafeStringEncipher.SimpleDecrypt(EncryptPassword);
63+
RaisePropertyChanged();
64+
}
65+
}
66+
67+
public PgsqlSource() : base()
68+
{
69+
}
70+
71+
public override string GetConnectionString(int connectTimeOutSeconds = 5)
72+
{
73+
return DbExtensions.GetPgsqlConnectionString(_host, _port, _databaseName, _userName, Password, connectTimeOutSeconds);
74+
}
75+
76+
[JsonIgnore]
77+
public override DatabaseType DatabaseType => DatabaseType.PostgreSQL;
78+
79+
[JsonIgnore] public override string Description => $"Host={Host};Port={Port};Database={DatabaseName};Username={UserName};...";
80+
81+
private IDatabase? _database = null;
82+
public override IDatabase GetDataBase()
83+
{
84+
_database ??= new DapperDatabase(DatabaseName, DatabaseType.PostgreSQL);
85+
_database.DatabaseName = DatabaseName;
86+
return _database;
87+
}
88+
89+
public static bool TestConnection(PgsqlSource config)
90+
{
91+
return TestConnection(config.Host, config.Port, config.DatabaseName, config.UserName, config.Password);
92+
}
93+
94+
public static bool TestConnection(string host, int port, string dbName, string userName, string password)
95+
{
96+
var str = DbExtensions.GetPgsqlConnectionString(host, port, dbName, userName, password, 2);
97+
var db = new DapperDatabase(dbName, DatabaseType.PostgreSQL);
98+
var ret = db.OpenNewConnection(str);
99+
db.CloseConnection();
100+
return ret.IsSuccess;
101+
}
102+
}
103+
}

‎Ui/Ui.csproj‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -177,6 +177,7 @@
177177
<PackageReference Include="Microsoft.AppCenter.Analytics" Version="5.0.1" />
178178
<PackageReference Include="Microsoft.AppCenter.Crashes" Version="5.0.1" />
179179
<PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
180+
<PackageReference Include="Npgsql" Version="9.0.2" />
180181
<PackageReference Include="NUlid" Version="1.7.1" />
181182
<PackageReference Include="Sentry" Version="4.13.0" />
182183
<PackageReference Include="SSH.NET" Version="2023.0.0" />

‎Ui/View/Settings/DataSource/DataSourceView.xaml‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -230,6 +230,25 @@
230230
<Path Height="14" UseLayoutRounding="True" Data="M1001.632 793.792c-7.84-13.856-26.016-37.536-93.12-83.2a1096.224 1096.224 0 0 0-125.152-74.144c-30.592-82.784-89.824-190.112-176.256-319.36-93.056-139.168-201.12-197.792-321.888-174.56a756.608 756.608 0 0 0-40.928-37.696C213.824 78.688 139.2 56.48 96.32 60.736c-19.424 1.952-34.016 9.056-43.36 21.088-21.664 27.904-14.432 68.064 85.504 198.912 19.008 55.616 23.072 84.672 23.072 99.296 0 30.912 15.968 66.368 49.984 110.752l-32 109.504c-28.544 97.792 23.328 224.288 71.616 268.384 25.76 23.552 47.456 20.032 58.176 15.84 21.504-8.448 38.848-29.472 50.048-89.504 5.728 14.112 11.808 29.312 18.208 45.6 34.56 87.744 68.352 136.288 106.336 152.736a32.032 32.032 0 0 0 25.44-58.688c-9.408-4.096-35.328-23.712-72.288-117.504-31.168-79.136-53.856-132.064-69.376-161.856a32.224 32.224 0 0 0-35.328-16.48 32.032 32.032 0 0 0-25.024 29.92c-3.872 91.04-13.056 130.4-19.2 147.008-26.496-30.464-68.128-125.984-47.232-197.536 20.768-71.232 32.992-112.928 36.64-125.248a31.936 31.936 0 0 0-5.888-29.28c-41.664-51.168-46.176-75.584-46.176-83.712 0-29.472-9.248-70.4-28.288-125.152a31.104 31.104 0 0 0-4.768-8.896c-53.824-70.112-73.6-105.216-80.832-121.888 25.632 1.216 74.336 15.04 91.008 29.376a660.8 660.8 0 0 1 49.024 46.304c8 8.448 19.968 11.872 31.232 8.928 100.192-25.92 188.928 21.152 271.072 144 87.808 131.328 146.144 238.048 173.408 317.216a32 32 0 0 0 16.384 18.432 1004.544 1004.544 0 0 1 128.8 75.264c7.392 5.024 14.048 9.696 20.064 14.016h-98.848a32.032 32.032 0 0 0-24.352 52.736 3098.752 3098.752 0 0 0 97.856 110.464 32 32 0 1 0 46.56-43.872 2237.6 2237.6 0 0 1-50.08-55.328h110.08a32.032 32.032 0 0 0 27.84-47.776z" Fill="{DynamicResource PrimaryTextBrush}" Stretch="Uniform"/>
231231
</MenuItem.Icon>
232232
</MenuItem>
233+
234+
<MenuItem Command="{Binding CmdAdd}" CommandParameter="PostgreSQL">
235+
<MenuItem.Header>
236+
<StackPanel Orientation="Horizontal">
237+
<TextBlock Text="PostgreSQL"></TextBlock>
238+
<TextBlock VerticalAlignment="Center" Margin="2 0">
239+
<Hyperlink NavigateUri="https://1remote.org/usage/database/use-online-database/" TextDecorations="{x:Null}"
240+
controls:HyperlinkHelper.IsOpenExternal="True">
241+
(?)
242+
</Hyperlink>
243+
</TextBlock>
244+
</StackPanel>
245+
</MenuItem.Header>
246+
<MenuItem.Icon>
247+
<Path Height="14" UseLayoutRounding="True" Data="M33,34c0-0.205,0.012-0.376,0.018-0.565C33.008,33.184,33,33,33,33s0.012-0.009,0.032-0.022c0.149-2.673,0.886-3.703,1.675-4.29c-0.11-0.153-0.237-0.318-0.356-0.475c-0.333-0.437-0.748-0.979-1.192-1.674l-0.082-0.158c-0.067-0.164-0.229-0.447-0.435-0.819c-1.183-2.14-3.645-6.592-1.96-9.404c0.738-1.232,2.122-1.942,4.121-2.117C33.986,11.718,30.925,6.115,23.985,6c-0.002,0-0.004,0-0.006,0c-6.041-0.098-8.026,5.392-8.672,8.672c0.89-0.377,1.906-0.606,2.836-0.606c0.014,0,0.029,0,0.043,0c2.29,0.017,3.865,1.239,4.323,3.354c0.335,1.552,0.496,2.91,0.492,4.153c-0.01,2.719-0.558,4.149-1.042,5.411l-0.154,0.408c-0.124,0.334-0.255,0.645-0.379,0.937c-0.126,0.298-0.237,0.563-0.318,0.802c0.484,0.11,0.864,0.265,1.125,0.38l0.151,0.066c0.047,0.02,0.094,0.043,0.137,0.069c0.848,0.516,1.376,1.309,1.489,2.233c0.061,0.498,0.051,3.893,0.03,6.855c0.087,1.285,0.305,2.364,0.593,3.146c0.409,1.114,1.431,3.241,3.394,3.119c1.37-0.085,2.687-0.919,3.561-2.019c0.938-1.181,1.284-2.487,1.414-3.958V34z M15.114 28.917c-1.613-1.683-2.399-3.947-2.104-6.056.285-2.035.124-4.027.037-5.098-.029-.357-.048-.623-.047-.77 0-.008.002-.015.003-.023 0-.004-.002-.007-.002-.011.121-3.021 1.286-7.787 4.493-10.62C15.932 5.724 13.388 4.913 11 5 7.258 5.136 3.636 7.724 4 15c.137 2.73 3.222 19.103 7.44 19 .603-.015 1.229-.402 1.872-1.176 1.017-1.223 2.005-2.332 2.708-3.104C15.705 29.481 15.401 29.217 15.114 28.917zM37.023 14.731c.015.154.002.286-.022.408.031.92-.068 1.813-.169 2.677-.074.636-.15 1.293-.171 1.952-.021.645.07 1.282.166 1.956.225 1.578.459 3.359-.765 5.437.225.296.423.571.581.837 4.61-7.475 6.468-16.361 4.695-18.626C38.655 5.944 34.941 4.952 31.999 5c-.921.015-1.758.139-2.473.294C34.602 7.754 36.863 13.026 37.023 14.731zM41 30.071c-2.665.55-3.947.257-4.569-.126-.1.072-.2.133-.293.19-.372.225-.961.583-1.105 2.782.083.016.156.025.246.044L35.714 33c1.32.06 3.049-.31 4.063-.781C41.962 31.205 43.153 29.627 41 30.071zM22.023 32.119c-.037-.298-.198-.539-.492-.732l-.108-.047C21.062 31.181 20.653 31 20 31h-.004c-.127.01-.253.019-.38.019-.052 0-.103-.007-.155-.009-.474.365-1.148.647-2.816.99-2.98.759-1.221 1.655-.078 1.794 1.106.277 3.735.614 5.481-.809C22.043 32.537 22.035 32.229 22.023 32.119z M20.681 18.501c-.292.302-.753.566-1.262.484-.828-.134-1.463-1.133-1.417-1.508h0c.044-.374.751-.569 1.578-.435.287.047.548.128.768.228-.32-.688-.899-1.085-1.782-1.182-1.565-.174-3.226.644-3.56 1.097.007.11.02.251.033.417.093 1.147.265 3.284-.05 5.537-.208 1.485.393 3.169 1.567 4.395.757.79 1.641 1.29 2.513 1.438.111-.478.309-.944.513-1.425.113-.265.233-.547.346-.852l.162-.427c.443-1.155.9-2.35.909-4.703C21.003 20.66 20.892 19.627 20.681 18.501zM34.847 22.007c-.104-.729-.211-1.484-.185-2.303.023-.742.105-1.442.184-2.119.062-.533.11-1.045.138-1.55-1.289.107-2.145.479-2.551 1.108.168-.057.358-.102.568-.129.892-.116 1.543.141 1.618.637.055.363-.253.705-.388.836-.277.269-.626.442-.981.488-.064.008-.129.012-.192.012-.353 0-.69-.121-.949-.3.112 1.973 1.567 4.612 2.283 5.907.153.277.271.498.369.688C35.154 24.163 35.009 23.143 34.847 22.007z"
248+
Fill="{DynamicResource PrimaryTextBrush}" Stretch="Uniform"/>
249+
</MenuItem.Icon>
250+
</MenuItem>
251+
233252
</MenuItem>
234253
</Menu>
235254

0 commit comments

Comments
 (0)