1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
/*
* Copyright (c) 2011-2019 Alecaddd (https://alecaddd.com)
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public
* License as published by the Free Software Foundation; either
* version 2 of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* General Public License for more details.
*
* You should have received a copy of the GNU General Public
* License along with this program; if not, write to the
* Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
* Boston, MA 02110-1301 USA
*
* Authored by: Alessandro "Alecaddd" Castellani <castellani.ale@gmail.com>
*/
public class Sequeler.Services.Types.PostgreSQL : Object, DataBaseType {
public string port { set; get; default = "5432"; }
public string host { set; get; default = "127.0.0.1"; }
public string connection_string (Gee.HashMap<string, string> data) {
var username = Gda.rfc1738_encode (data["username"]);
var password = Gda.rfc1738_encode (data["password"]);
var use_ssl = Gda.rfc1738_encode (data["use_ssl"] ?? "false");
var name = Gda.rfc1738_encode (data["name"]);
host = data["host"] != "" ? Gda.rfc1738_encode (data["host"]) : host;
if (data["has_ssh"] == "true") {
port = "9000";
} else {
port = data["port"] != "" ? data["port"] : port;
}
return "PostgreSQL://" + username + ":" + password + "@DB_NAME=" + name + ";HOST=" + host + ";PORT=" + port + ";USE_SSL=" + use_ssl;
}
public string show_schema () {
return "SELECT schema_name FROM information_schema.schemata";
}
public string show_table_list (string name) {
return "SELECT relname, reltuples FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND relkind='r' ORDER BY relname DESC;";
}
public string edit_table_name (string old_table, string new_table) {
return "ALTER TABLE \"%s\" RENAME TO \"%s\"".printf (old_table, new_table);
}
public string transfer_table (string old_database, string table, string new_database) {
return "";
}
public string show_table_structure (string table, string? sortby = null, string sort = "ASC") {
var output = "SELECT * FROM information_schema.COLUMNS WHERE table_name='%s'".printf (table);
if (sortby != null) {
output += " ORDER BY %s %s".printf (sortby, sort);
}
return output;
}
public string show_table_content (
string table, int? count = null, int? page = null,
string? sortby = null, string sort = "ASC"
) {
var output = "SELECT * FROM \"%s\"".printf (table);
if (sortby != null) {
output += " ORDER BY \"%s\" %s".printf (sortby, sort);
}
if (count != null && count > settings.limit_results) {
output += " LIMIT %i".printf (settings.limit_results);
}
if (page != null && page > 1) {
output += " OFFSET %i".printf (settings.limit_results * (page - 1));
}
return output;
}
public string show_table_relations (
string table, string? database,
string? sortby = null, string sort = "ASC"
) {
var output = "SELECT ccu.column_name as \"COLUMN_NAME\", tc.constraint_name as \"CONSTRAINT_NAME\", kcu.column_name as \"REFERENCED_COLUMN_NAME\", tc.table_name as \"REFERENCED_TABLE\" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='%s' AND ccu.table_schema = '%s'".printf (table, database);
if (sortby != null) {
output += " ORDER BY \"%s\" %s".printf (sortby, sort);
}
return output;
}
public string create_database (string name) {
// Temporary placeholder methods. No current support for database
// operations in PostgreSQL.
return "";
}
public string delete_database (string name) {
// Temporary placeholder methods. No current support for database
// operations in PostgreSQL.
return "";
}
}
|
