New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Recognize generated columns in data table #462

Open
JuergenNiesner opened this Issue Jan 3, 2019 · 6 comments

Comments

Projects
None yet
2 participants
@JuergenNiesner
Copy link

JuergenNiesner commented Jan 3, 2019

Create database table with generated column and try to edit the data or insert new data in the datatab view

Example for my table:
CREATE TABLE `t1` (
    `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `full_name` VARCHAR(255) AS (concat(`first_name`,_utf8mb3' ',`last_name`)) VIRTUAL,
    `first_name` VARCHAR(10) NULL DEFAULT NULL,
    `last_name` VARCHAR(10) NULL DEFAULT NULL,
    PRIMARY KEY (`ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Current behavior

dataview table columns viewing data from generated columns can be edited, but not saved to DB

dataview table columns viewing data from generated columns should be readonly, no edit enabled

  • HeidiSQL version: 9.5.0.5433
  • Database system and version: MySQL 8.0.13
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

ansgarbecker commented Jan 3, 2019

Confirmed with the latest build. On MariaDB 10.0, I even get an SQL error when trying to save rows with a manually entered value for a virtual column:

SQL Error (1906): The value specified for computed column 'idv' in table 'test2' ignored

@JuergenNiesner

This comment has been minimized.

Copy link

JuergenNiesner commented Jan 4, 2019

In addition to the read only columns for generated db table columns, AUTOINCREMENT columns and CURRENT_TIMESTAMP columns should also be read only, because normally this columns are filled from automatic retrieved data from the db system. Maybe there is an option if those columns should be editable or not.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

ansgarbecker commented Jan 4, 2019

I don't agree here, as MySQL/MariaDB has no problems with updating autoincrement and current_timestamp columns. Would be an artificial limitation.

@JuergenNiesner

This comment has been minimized.

Copy link

JuergenNiesner commented Jan 4, 2019

I agree that this is not necessary for MySQL or MariaDB, but it would agree to the intent of the database designer.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

ansgarbecker commented Jan 4, 2019

Yes, probably. I would myself also agree for most cases, but not in all. Other users may also want to edit such columns.

@JuergenNiesner

This comment has been minimized.

Copy link

JuergenNiesner commented Jan 4, 2019

Suggestion: Would light gray background color or so an idea for indication of TIMESTAMP and AUTOINCREMENT columns?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment