search

stefankroes / ancestry

Organise ActiveRecord model into a tree structure
MIT License
3.72k stars 458 forks source link

Improve documentation around ancestry field and collation #607

Closed kbrock closed 1 year ago

kbrock commented 1 year ago

dependency:

postgres: binary data type is not currently working mysql: binary data type or string with binary collation is working

Had wanted to go with a binary for all database, but the test would not pass. So I backtracked a little, researched opclasses, preferring ascii where possible.

This updates the comments introduced in #601

kbrock commented 1 year ago

@kshnurov I updated the gem to use binary for postgres. The test suite puked.

Staying with the original comments, but put in more documentation to hopefully get this running correctly.

kshnurov commented 1 year ago

@kshnurov I updated the gem to use binary for postgres. The test suite puked.

Ok, after some digging it turns out postgres returns bytea in hex format and pg gem doesn't unescape it by default. Here's a quick solution for postgres, it should also solve this

ALTER DATABASE dbname SET bytea_output to 'escape';

With this one, tests are green unless we use update_strategy = :sql. regexp_replace isn't possible with binary columns. I'd suggest removing :sql update strategy completely.

Another solution is to call connection.unescape_bytea on ancestry field, but I don't wanna dig into it since I'm using mysql right now.

kbrock commented 1 year ago

@kshnurov For a binary ancestry column, what else do I need to specify to set a length? And what value should I use?

 Mysql2::Error: BLOB/TEXT column 'ancestry' used in key specification without a key length (ActiveRecord::StatementInvalid)
kshnurov commented 1 year ago

For a binary ancestry column, what else do I need to specify to set a length? And what value should I use?

IDK if you missed this comment It definitely shouldn't be a BLOB.

kbrock commented 1 year ago

update: