Open edhemphill opened 7 months ago
Further research indicates to me that Clickhouse does not even support constraints with foreign keys. It does not seem to be supported in their SQL spec.
See: https://clickhouse.com/docs/en/sql-reference/statements/alter/constraint https://stackoverflow.com/questions/66354832/how-can-i-use-functionality-of-primary-key-and-foreign-key-in-clickhouse
Take for instance this table:
type User struct {
HModel
Email string `gorm:"index"`
Roles []*Role `gorm:"many2many:user_roles;"`
FirstName string
LastName string
Role string
}
...
type HModel struct {
ID string `gorm:"primarykey"`
CreatedAt time.Time
UpdatedAt time.Time
// DeletedAt DeletedAt `gorm:"index"`
}
When doing a err = db.AutoMigrate(&User{}, &Role{})
the driver will attempt this:
ALTER TABLE `user_roles` ADD CONSTRAINT `fk_user_roles_role`
FOREIGN KEY (`role_id`,`role_type`) REFERENCES `roles`(`id`,`type`)
But, at least based on my very limited (a week worth of messing with Clickhouse) this SQL statement is just wrong.
For instance running it in clickhouse-client results in:
Syntax error: failed at position 63 ('FOREIGN'):
ALTER TABLE `user_roles` ADD CONSTRAINT `fk_user_roles_role` FOREIGN KEY (`role_id`,`role_type`) REFERENCES `roles`(`id`,`type`)
Expected one of: CHECK, ASSUME
Maybe someone could shed light on this.
But - as far as i can tell - this driver is just basically broken.
I think the only reasonable way to do many-to-many in clickhouse would be to simply use the ReplacingMergeTree
engine using TUPLE()
as the ORDER BY
Anyone? thoughts?
Or please tell me I am missing something big here.
Not sure this is a bug or my own misunderstanding of gorm:
These models:
an implementation of adding an alias for a visitor like this:
Fails like such:
However, an implementation calling my own SQL works fine:
Results:
Verified everything using the clickhouse client.
My understanding is that the error:
code: 101, message: Unexpected packet Query received from client
Is likely when the same client is asked to start a new transaction while waiting on another to complete. My cursory glance at the source confirms this.
Notice that the clickhouse-go
end of stream
event never occurs in the first implementation. Is the gorm clickhouse driver not waiting for the first action to complete, or am i doing something wrong here?related: https://github.com/ClickHouse/clickhouse-go/issues/322 https://github.com/ClickHouse/clickhouse-go/issues/485