1#include "duckdb/catalog/catalog_search_path.hpp"
2#include "duckdb/common/constants.hpp"
3#include "duckdb/common/file_system.hpp"
4#include "duckdb/common/string_util.hpp"
5#include "duckdb/function/pragma/pragma_functions.hpp"
6#include "duckdb/main/config.hpp"
7#include "duckdb/main/database_manager.hpp"
8#include "duckdb/main/client_data.hpp"
9#include "duckdb/parser/parser.hpp"
10#include "duckdb/parser/qualified_name.hpp"
11#include "duckdb/parser/statement/copy_statement.hpp"
12#include "duckdb/parser/statement/export_statement.hpp"
13
14namespace duckdb {
15
16string PragmaTableInfo(ClientContext &context, const FunctionParameters &parameters) {
17 return StringUtil::Format(fmt_str: "SELECT * FROM pragma_table_info('%s');", params: parameters.values[0].ToString());
18}
19
20string PragmaShowTables(ClientContext &context, const FunctionParameters &parameters) {
21 // clang-format off
22 return R"EOF(
23 with "tables" as
24 (
25 SELECT table_name as "name"
26 FROM duckdb_tables
27 where in_search_path(database_name, schema_name)
28 ), "views" as
29 (
30 SELECT view_name as "name"
31 FROM duckdb_views
32 where in_search_path(database_name, schema_name)
33 ), db_objects as
34 (
35 SELECT "name" FROM "tables"
36 UNION ALL
37 SELECT "name" FROM "views"
38 )
39 SELECT "name"
40 FROM db_objects
41 ORDER BY "name";)EOF";
42 // clang-format on
43}
44
45string PragmaShowTablesExpanded(ClientContext &context, const FunctionParameters &parameters) {
46 return R"(
47 SELECT
48 t.database_name AS database,
49 t.schema_name AS schema,
50 t.table_name AS name,
51 LIST(c.column_name order by c.column_index) AS column_names,
52 LIST(c.data_type order by c.column_index) AS column_types,
53 FIRST(t.temporary) AS temporary,
54 FROM duckdb_tables t
55 JOIN duckdb_columns c
56 USING (table_oid)
57 GROUP BY database, schema, name
58
59 UNION ALL
60
61 SELECT
62 v.database_name AS database,
63 v.schema_name AS schema,
64 v.view_name AS name,
65 LIST(c.column_name order by c.column_index) AS column_names,
66 LIST(c.data_type order by c.column_index) AS column_types,
67 FIRST(v.temporary) AS temporary,
68 FROM duckdb_views v
69 JOIN duckdb_columns c
70 ON (v.view_oid=c.table_oid)
71 GROUP BY database, schema, name
72
73 ORDER BY database, schema, name
74 )";
75}
76
77string PragmaShowDatabases(ClientContext &context, const FunctionParameters &parameters) {
78 return "SELECT database_name FROM duckdb_databases() WHERE NOT internal ORDER BY database_name;";
79}
80
81string PragmaAllProfiling(ClientContext &context, const FunctionParameters &parameters) {
82 return "SELECT * FROM pragma_last_profiling_output() JOIN pragma_detailed_profiling_output() ON "
83 "(pragma_last_profiling_output.operator_id);";
84}
85
86string PragmaDatabaseList(ClientContext &context, const FunctionParameters &parameters) {
87 return "SELECT * FROM pragma_database_list;";
88}
89
90string PragmaCollations(ClientContext &context, const FunctionParameters &parameters) {
91 return "SELECT * FROM pragma_collations() ORDER BY 1;";
92}
93
94string PragmaFunctionsQuery(ClientContext &context, const FunctionParameters &parameters) {
95 return "SELECT function_name AS name, upper(function_type) AS type, parameter_types AS parameters, varargs, "
96 "return_type, has_side_effects AS side_effects"
97 " FROM duckdb_functions()"
98 " WHERE function_type IN ('scalar', 'aggregate')"
99 " ORDER BY 1;";
100}
101
102string PragmaShow(ClientContext &context, const FunctionParameters &parameters) {
103 // PRAGMA table_info but with some aliases
104 auto table = QualifiedName::Parse(input: parameters.values[0].ToString());
105
106 // clang-format off
107 string sql = R"(
108 SELECT
109 name AS "column_name",
110 type as "column_type",
111 CASE WHEN "notnull" THEN 'NO' ELSE 'YES' END AS "null",
112 (SELECT
113 MIN(CASE
114 WHEN constraint_type='PRIMARY KEY' THEN 'PRI'
115 WHEN constraint_type='UNIQUE' THEN 'UNI'
116 ELSE NULL END)
117 FROM duckdb_constraints() c
118 WHERE c.table_oid=cols.table_oid
119 AND list_contains(constraint_column_names, cols.column_name)) AS "key",
120 dflt_value AS "default",
121 NULL AS "extra"
122 FROM pragma_table_info('%func_param_table%')
123 LEFT JOIN duckdb_columns cols
124 ON cols.column_name = pragma_table_info.name
125 AND cols.table_name='%table_name%'
126 AND cols.schema_name='%table_schema%';)";
127 // clang-format on
128
129 sql = StringUtil::Replace(source: sql, from: "%func_param_table%", to: parameters.values[0].ToString());
130 sql = StringUtil::Replace(source: sql, from: "%table_name%", to: table.name);
131 sql = StringUtil::Replace(source: sql, from: "%table_schema%", to: table.schema.empty() ? DEFAULT_SCHEMA : table.schema);
132 return sql;
133}
134
135string PragmaVersion(ClientContext &context, const FunctionParameters &parameters) {
136 return "SELECT * FROM pragma_version();";
137}
138
139string PragmaImportDatabase(ClientContext &context, const FunctionParameters &parameters) {
140 auto &config = DBConfig::GetConfig(context);
141 if (!config.options.enable_external_access) {
142 throw PermissionException("Import is disabled through configuration");
143 }
144 auto &fs = FileSystem::GetFileSystem(context);
145
146 string final_query;
147 // read the "shema.sql" and "load.sql" files
148 vector<string> files = {"schema.sql", "load.sql"};
149 for (auto &file : files) {
150 auto file_path = fs.JoinPath(a: parameters.values[0].ToString(), path: file);
151 auto handle = fs.OpenFile(path: file_path, flags: FileFlags::FILE_FLAGS_READ, lock: FileSystem::DEFAULT_LOCK,
152 compression: FileSystem::DEFAULT_COMPRESSION);
153 auto fsize = fs.GetFileSize(handle&: *handle);
154 auto buffer = make_unsafe_uniq_array<char>(n: fsize);
155 fs.Read(handle&: *handle, buffer: buffer.get(), nr_bytes: fsize);
156 auto query = string(buffer.get(), fsize);
157 // Replace the placeholder with the path provided to IMPORT
158 if (file == "load.sql") {
159 Parser parser;
160 parser.ParseQuery(query);
161 auto copy_statements = std::move(parser.statements);
162 query.clear();
163 for (auto &statement_p : copy_statements) {
164 D_ASSERT(statement_p->type == StatementType::COPY_STATEMENT);
165 auto &statement = statement_p->Cast<CopyStatement>();
166 auto &info = *statement.info;
167 auto file_name = fs.ExtractName(path: info.file_path);
168 info.file_path = fs.JoinPath(a: parameters.values[0].ToString(), path: file_name);
169 query += statement.ToString() + ";";
170 }
171 }
172 final_query += query;
173 }
174 return final_query;
175}
176
177string PragmaDatabaseSize(ClientContext &context, const FunctionParameters &parameters) {
178 return "SELECT * FROM pragma_database_size();";
179}
180
181string PragmaStorageInfo(ClientContext &context, const FunctionParameters &parameters) {
182 return StringUtil::Format(fmt_str: "SELECT * FROM pragma_storage_info('%s');", params: parameters.values[0].ToString());
183}
184
185void PragmaQueries::RegisterFunction(BuiltinFunctions &set) {
186 set.AddFunction(function: PragmaFunction::PragmaCall(name: "table_info", query: PragmaTableInfo, arguments: {LogicalType::VARCHAR}));
187 set.AddFunction(function: PragmaFunction::PragmaCall(name: "storage_info", query: PragmaStorageInfo, arguments: {LogicalType::VARCHAR}));
188 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "show_tables", query: PragmaShowTables));
189 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "show_tables_expanded", query: PragmaShowTablesExpanded));
190 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "show_databases", query: PragmaShowDatabases));
191 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "database_list", query: PragmaDatabaseList));
192 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "collations", query: PragmaCollations));
193 set.AddFunction(function: PragmaFunction::PragmaCall(name: "show", query: PragmaShow, arguments: {LogicalType::VARCHAR}));
194 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "version", query: PragmaVersion));
195 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "database_size", query: PragmaDatabaseSize));
196 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "functions", query: PragmaFunctionsQuery));
197 set.AddFunction(function: PragmaFunction::PragmaCall(name: "import_database", query: PragmaImportDatabase, arguments: {LogicalType::VARCHAR}));
198 set.AddFunction(function: PragmaFunction::PragmaStatement(name: "all_profiling_output", query: PragmaAllProfiling));
199}
200
201} // namespace duckdb
202