Mismatched number of keys

[criegod]

I wrote a ruby application using DataMapper quite a few years ago but a recent O/S upgrade (viz to Ruby 3.2, most notably) broke it... seemingly irremediably (data_objects module not updated for 6+ years?) so... i'm porting to Sequel. I got one of my modules ported, and working... but without certain things, e.g. my working schema is:

`class Album < Sequel::Model
end

class Artist < Sequel::Model
end
`
But this:

`
class Album < Sequel::Model
many_to_one :artists
end

class Artist < Sequel::Model
one_to_many :albums
end
'
Results in the following output:

listAlbums /home/craig/.local/share/gem/ruby/3.2.0/gems/sequel-5.84.0/lib/sequel/model/associations.rb:2355:indef_one_to_many': mismatched number of keys: [:artist_id] vs [:id, :name] (Sequel::Error)
from /home/craig/.local/share/gem/ruby/3.2.0/gems/sequel-5.84.0/lib/sequel/model/associations.rb:2034:in def_association' from /home/craig/.local/share/gem/ruby/3.2.0/gems/sequel-5.84.0/lib/sequel/model/associations.rb:1917:in associate'
from /home/craig/.local/share/gem/ruby/3.2.0/gems/sequel-5.84.0/lib/sequel/model/associations.rb:1978:in one_to_many' from /home/craig/src/ruby/sequel-schema.rb:23:in class:Artist'
from /home/craig/src/ruby/sequel-schema.rb:21:in <top (required)>' from /usr/local/bin/listAlbums:3:in require_relative'
from /usr/local/bin/listAlbums:3:in `

'
'
I'm suspecting the problem is in my postgres db table definitions:

`
music=> \d albums
Table "public.albums"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('albums_id_seq'::regclass)
artist_id | integer | | not null |
name | character varying(255) | | not null |
path | character varying(255) | | |
bdate | timestamp without time zone | | |
year | integer | | |
artist_name | character varying(255) | | not null |
Indexes:
"albums_pkey" PRIMARY KEY, btree (id, artist_id, name, artist_name)
"index_albums_artist" btree (artist_name)
"unique_albums_key" UNIQUE, btree (id, artist_id, name)

music=> \d artists
Table "public.artists"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('artists_id_seq'::regclass)
name | character varying(255) | | not null |
Indexes:
"artists_pkey" PRIMARY KEY, btree (id, name)
'

I didn't (exactly) create those definitions... dataMapper did. I always thought there was something "wrong" with the way it did it... creating an artist_name column in the 'albums' table, when the join i wanted was through 'artist_id'. I'm no db expert, though, so i just used what it gave me. Now, though, i think that may be the source of the problem. But, given one day's familiarity with Sequel, the fact that i wrote this code years ago, and that i myself didn't even create that (irregular?) table definition... i'm over my head as to how to fix the 'mismatched number of keys' error... but... i think the problem is probably quite simply resolved... if only i knew what i was doing. Can anyone enlighten me as to how to proceed. I was not at all familiar with db migrations, but started reading about it. However, for me, i think all i might need to do is go into psql and change a table definition (drop the 'artist_name' column?). I'd like a little more insight, though, before really messing things up. Thanks for any help.

[jeremyevans]

You need to specify one_to_many :albums, primary_key: :id This is because the primary key for your artists table is composite. I cannot think of a reason to use a composite primary key that includes in auto incrementing key. I recommend rethinking your schema.

[criegod]

Added 'primary_key', as you said, and it now works with the 'one_to_many' and 'many_to_one' methods (?) included in the classes. I should have said that my DataMapper class definitions, for the Album class, includes:

belongs_to :artist, :key => true

and the Artist class includes:

has n, :albums

I believe, from those, it created the schema i have. The composite primary key you refer to must be the 'unique_albums_key', which, i believe, along with the 'artist_name' field, DataMapper created all by itself. If i'm wrong about that... as i said, it's been many years. Anyway, DataMapper seemed to need this extra junk (that i don't really understand) but i'm thinking/hoping (and from what you said), Sequel doesn't. So... should i be able to do what i want with a postgres db definition that eliminates the 'artist_name' field and the 'index_albums_artist' and 'unique_albums_key'?

I really don't understand this latter, as just the album id is unique (no?) The key which uses 'id', and 'artist_id' is unique, but unnecessarily, redundantly, so (it seems to me). And then to add 'name' as a 3rd part of the key...??? I don't understand what i have, but i don't need to (DataMapper is dead afaic) if i know that Sequel operates the way i originally thought about this: i only need an id key for an album, an id key for an artist, and an artist_id key contained within an album record. And, also, the "magic" (one_to_many, etc...) that makes it work properly. This mess came about (i believe) because of DataMapper limitations that didn't support the "right" way to do things.
Sorry i'm so clueless about much of this but, the help is greatly appreciated.

[jeremyevans]

I recommend you drop the existing primary key constraints, and add new primary key constraints on just the id columns. Then, if you want other column(s) to be unique, add appropriate unique constraints.

Your schema does not appear normalized, at least the artist_name column in the albums table part. You should probably read up on normalized database design, as it will make things easier in the long term. If you have questions about Sequel itself, please let me know.

[criegod]

Thank you for that. As i said, the artist_name column (which DataMapper) created, didn't seem quite right to me. I believe you're pointing out the same thing, in specific terms, when you say this is not normalized. Right! If i'd remembered the terminology , i might have said that myself. I have Sequel questions now, but they're basic. Thanks for the higher-level help. Although i wasn't asking specifically about Sequel (with this latter), your help (confirming i'm on the right track) is greatly appreciated.