Toggle Submenu
  1. Forums
  2. Recent
  3. Categories
  4. Tags
  5. Users
Add a reply View Replies (7)

Resolved DuckDB user defined types, etc.

  1. Vladimir Esipov
    Vladimir Esipov
  2. Valentina Studio
  3. Wednesday, September 18 2024, 06:40 AM
  4.  Subscribe via email
A full database dump is performed successfully, but a dump of a single table with the selected "Structure and records" option ends with an error. By the way, by default, for DuckDB, the row group size is something like 122800, perhaps it should be specified in the dump creation wizard. Inserting 256 records into a transaction for DDB is inefficient.

At the same time, the Dump does not contain scripts for creating user types that are in the DB, but there are comments on objects,
and vice versa, for Backup, in the schema.sql file, user types are present, but there are no comments on objects.

The Studio schema editor also does not show user data types (ENUM and other composite types), perhaps it is worth adding.
Also, in the table creation dialog, it is advisable to display types in the user data type selection list, if they are in the schema (as is done for VDB).

Yes, ENUMs are not editable yet, and when they are used in a table creation script, they are inlined as if they belong to a specific table, like in MySQL, and not to the schema as a whole, i.e. they become anonymous in the table.
It turns out that user data types in the schema are by themselves, and they are in the table - by themselves - the connection is lost.
Why this is done is a great mystery.:)

Если вышеизложенное для вас не новость и есть в дорожной карте — проигнорируйте, в противном случае готов открыть соответствующие тикеты в MANTIS.
  1. Beta
Comment
There are no comments made yet.
Responses (7)
Sergey Pashkov
Sergey Pashkov Accepted Answer
Hello Vladimir,

It seems that the custom types feature is not fully completed yet, so we have decided to postpone supporting it. Additionally, there's another oddity (at least in 1.0): the table is not entirely independent of the type. For example, we can't delete a type if a table contains a column created with that type.
Comment
There are no comments made yet.
  1. 2 weeks ago
  2. Valentina Studio
  3. # 1
Vladimir Esipov
Vladimir Esipov Accepted Answer
Hello all!
For example, we can't delete a type if a table contains a column created with that type.

Isn't this normal behavior? - you can't delete a parent object.

In turn, it seems inconvenient to me that duckdb_tables() returns SQL, where ENUMs are stored in expanded form, as anonymous (all with the same data_type_id = 104).
To find out the name, which specific ENUM participated in the creation of which table, you have to separately query duckdb_dependencies();
But, perhaps, this is correct.

In general, respected duck hunters have been messing with ENUM types for a long time, and since version 0.6 they promised to make them editable - but still no! Yesterday, problems with them were opened again. So - we will wait.

In the release for 14.5 it is written that in 14.6 there will be access to reports from DukDB on the server. That is, if I understand correctly - over time, the ability to work with DukDB via REST will be implemented?

By the way, regarding the crash of server 14.5 on MAC - under Win_11 x64 everything is fine for now.
Comment
There are no comments made yet.
  1. 2 weeks ago
  2. Valentina Studio
  3. # 2
Vladimir Esipov
Vladimir Esipov Accepted Answer
Oh, I forgot to congratulate you on the first release of DukeDB!
Happy hunting!:D
Comment
There are no comments made yet.
  1. 2 weeks ago
  2. Valentina Studio
  3. # 3
Sergey Pashkov
Sergey Pashkov Accepted Answer
Yes, it’s expected, just the table must point to the custom type, not to the base type.
Comment
There are no comments made yet.
  1. 2 weeks ago
  2. Valentina Studio
  3. # 4
Vladimir Esipov
Vladimir Esipov Accepted Answer
For example, we can't delete a type if a table contains a column created with that type.

By default, for the DROP statement is set to the RESTRICT rule.
To delete a type with all dependencies, you must explicitly specify CASCADE:

DROP TYPE CASCADE;

A pointer to a custom type can be pulled from duckdb_dependencies().
All dependencies are tracked there, except for View
Comment
There are no comments made yet.
  1. 2 weeks ago
  2. Valentina Studio
  3. # 5
Sergey Pashkov
Sergey Pashkov Accepted Answer
Yes, we can get table-type dependency.
But is there a way to find column-type dependency?

For example:


create type my_type as enum ('a','b','c');

create table t1( f1 integer, f2 my_type );


Here we can see only IDs of table and type


SELECT * FROM duckdb_dependencies()


but what column is using this type?
Comment
There are no comments made yet.
  1. 2 weeks ago
  2. Valentina Studio
  3. # 6
Vladimir Esipov
Vladimir Esipov Accepted Answer
...but what column is using this type?


Из duckdb_types() получаем type_oid и labels
FROM duckdb_types() SELECT type_oid, labels WHERE type_name='my_type' AND NOT internal;

-- type_oid = 2157 -> уникален, соответствует objid в duckdb_dependencies()
-- labels = [a, b, c]

-- Из duckdb_columns() получаем table_oid и data_type
FROM duckdb_columns() SELECT table_oid, data_type_id, data_type WHERE table_name='t1' AND column_name ='f2';

-- table_oid = 2163 -> уникален, соответствует refobjid в duckdb_dependencies()
-- data_type_id = 104 -> одинаков для всех анонимных ENUM
-- data_type = ENUM('a', 'b', 'c')

-- but what column is using this type?

Ну да, я же говорю - неудобно! Т.к., data_type_id одинаков для всех анонимных ENUM, сопоставить можно только опосредованно, опросив 3 табличные функции и сравнив значения в
"labels" и "data_type", т.е., в вашеи случае [a, b, c] и ENUM('a', 'b', 'c')
В принципе, можно обернуть в макрос, но я надеюсь что DuckDB предоставят более прямое и элегантное решение.
Comment
There are no comments made yet.
  1. 2 weeks ago
  2. Valentina Studio
  3. # 7
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories