NTP Analyzer  0.8.2
Analyze the operation of time servers
PostgreSqlFactory.cs
Go to the documentation of this file.
1 //
2 // Copyright (c) 2013-2017 Carsten Sonne Larsen <cs@innolan.net>
3 //
4 // Permission is hereby granted, free of charge, to any person obtaining a copy
5 // of this software and associated documentation files (the "Software"), to deal
6 // in the Software without restriction, including without limitation the rights
7 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8 // copies of the Software, and to permit persons to whom the Software is
9 // furnished to do so, subject to the following conditions:
10 //
11 // The above copyright notice and this permission notice shall be included in
12 // all copies or substantial portions of the Software.
13 //
14 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
20 // THE SOFTWARE.
21 
22 using System;
23 using System.Data;
24 using System.Diagnostics.CodeAnalysis;
25 using System.Text;
26 using Npgsql;
27 
48 namespace Ntp.Data.Provider
49 {
50  public sealed class PostgreSqlFactory : SqlDatabaseFactory
51  {
52  internal PostgreSqlFactory()
53  {
54  }
55 
56  private const string CreateDatabaseSql1 =
57  "SELECT COUNT(*) FROM pg_database WHERE datname = lower('{0}');";
58 
59  private const string CreateDatabaseSql2 =
60  "CREATE DATABASE {0};";
61 
62  private const string CheckTableSql =
63  "SELECT * " +
64  "FROM information_schema.tables " +
65  "WHERE table_catalog = lower('{0}') " +
66  "AND table_name = lower('{1}');";
67 
68  public override IDbCommand CreateCommand()
69  {
70  return new NpgsqlCommand();
71  }
72 
73  public override IDbConnection CreateConnection()
74  {
75  return new NpgsqlConnection(BuildConnectionString());
76  }
77 
78  [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
79  public override void CreateDatabase()
80  {
81  var connection = CreateGenericConnection();
82  connection.Open();
83 
84  var command = Instance.CreateCommand();
85  command.Connection = connection;
86  command.CommandText = string.Format(CreateDatabaseSql1, Config.Name);
87  command.Prepare();
88  int count = Convert.ToInt32(command.ExecuteScalar());
89 
90  if (count == 0)
91  {
92  command.CommandText = string.Format(CreateDatabaseSql2, Config.Name);
93  command.Prepare();
94  command.ExecuteNonQuery();
95  }
96 
97  connection.Close();
98  }
99 
100  public override IDbConnection CreateGenericConnection()
101  {
102  return new NpgsqlConnection(BuildConnectionString(false));
103  }
104 
105  public override IDbDataParameter CreateParameter(string name, object value)
106  {
107  return new NpgsqlParameter(name, value);
108  }
109 
110  public override string DateAddMinutes(string dateColumn, string minuteColumn)
111  {
112  return $"{dateColumn} - {minuteColumn} * INTERVAL '1 minute'";
113  }
114 
115  public override string PrepareCheckTableSql(string table)
116  {
117  return PrepareSql(string.Format(CheckTableSql, Config.Name, table));
118  }
119 
120  public override string PrepareCreateTableSql(string sql)
121  {
122  string sql2 = sql.
123  Replace("UNIQUE KEY", "UNIQUE").
124  Replace("BIT(1)", "BOOL");
125 
126  return PrepareSql(string.Format(sql2, "SERIAL", string.Empty));
127  }
128 
129  public override string PrepareInsertSql(string sql)
130  {
131  return PrepareSql(string.Format(sql, "SELECT LASTVAL();"));
132  }
133 
134  public override string PrepareSql(string sql)
135  {
136  return sql.Replace('[', '\"').Replace(']', '\"');
137  }
138 
139  private static string BuildConnectionString(bool includeName = true)
140  {
141  if (Config.ConnectionString != null)
142  return Config.ConnectionString;
143 
144  var b = new StringBuilder();
145  b.Append($"Server={Config.Host};");
146 
147  if (Config.Port != null)
148  b.Append($"Port={Config.Port};");
149 
150  b.Append(includeName ? $"Database={Config.Name};" : @"Database=postgres;");
151 
152  b.Append($"User Id={Config.User};");
153  b.Append($"Password={Config.Pass};");
154 
155  if (Config.ConnectionTimeout.HasValue)
156  b.Append($"Timeout={Config.ConnectionTimeout.Value};");
157 
158  b.Append(Config.EnableSsl ? @"SslMode=Require;" : @"SslMode=Disable;");
159 
160  if (Config.Protocol.HasValue)
161  b.Append($"Protocol={Config.Protocol.Value};");
162 
163  return b.ToString();
164  }
165  }
166 }
override string PrepareSql(string sql)
override string PrepareCheckTableSql(string table)
override IDbDataParameter CreateParameter(string name, object value)
override string PrepareInsertSql(string sql)
override IDbConnection CreateGenericConnection()
override string DateAddMinutes(string dateColumn, string minuteColumn)
override string PrepareCreateTableSql(string sql)
static string BuildConnectionString(bool includeName=true)
override IDbConnection CreateConnection()
var b
Definition: bootstrap.min.js:6