Having problems writing tests for models with *_to_many relationships

Question

I have these models:

Sequel::Model.plugin(:schema)

DB = ENV['RUBY_ENV'].eql?('test') ? Sequel.sqlite('test.db') : Sequel.sqlite('database.db')
DB.loggers << Logger.new($stdout)

unless DB.table_exists?(:channel)
  DB.create_table :channel do
    string      :id, :unique => true, :primary_key => true
    string      :title, :null => false
    text        :description
    integer     :likeCount, :default => 0
  end
end

class Channel < Sequel::Model(:channel)
  unrestrict_primary_key
  one_to_many :videos
end

unless DB.table_exists? :video
  DB.create_table :video do
    string      :id, :primary_key => true
    string      :title, :null => false
    foreign_key :channel_id, :channel
    text        :description
    DateTime    :publishedAt
    DateTime    :updatedAt
    string      :category
    integer     :viewCount
    integer     :likeCount
    integer     :favoriteCount
    integer     :dislikeCount
  end
end

class Video < Sequel::Model(:video)
  unrestrict_primary_key
  many_to_one :channel
  many_to_many :comments
end

unless DB.table_exists? (:user)
  DB.create_table :user do
    primary_key :id
    string      :username
  end
end

class User < Sequel::Model(:user)
  unrestrict_primary_key
  one_to_many :comments
end

unless DB.table_exists? :comment
  DB.create_table :comment do
    primary_key :id
    foreign_key :user_id, :user
    foreign_key :video_id, :videos
    integer     :likeCount, :default => 0
  end
end

class Comment < Sequel::Model(:comment)
  many_to_one :user
  many_to_many :videos
end

And specs that cover them:

class TestChannel < SequelTestCase

  def test_that_id_can_be_provided
    Channel.create(id: 'testId1', title: 'testTitle')
    Channel.where(title: 'testTitle').count.must_equal 1
  end

  def test_title_cant_be_null
    -> {Channel.create(id:'testId2')}.must_raise Sequel::NotNullConstraintViolation
  end

  def test_default_likeCount_and_descrition
    Channel.create(id:'testId1', title:'testTitle', description: 'test')
    rec = Channel.first(title:'testTitle')
    rec.likeCount.must_equal 0
    rec.description.must_equal 'test'
  end
end

class TestVideo < SequelTestCase
  def test_provide_id

    Video.create(id:'testId', title:'testTitle')
  end
end

class TestUser < SequelTestCase
  def test_username
    User.create(username:'testName')
    User.first.username.must_equal 'testName'
  end
end

class TestComments < SequelTestCase
  def test_comments
    Comment.create
    Comment.first.likeCount.must_equal 0
  end
end

I'm rolling out all transactions:

class SequelTestCase < MiniTest::Test
  def run(*args, &block)
    Sequel::Model.db.transaction(:rollback=>:always){super}
    self
  end
end

For models that don't have any *_to_many relations, everything goes flawlessly. But for any *_to_many relationship, I'm running in to this error:

TestComments#test_comments:
Sequel::DatabaseError: SQLite3::SQLException: no such table: main.videos

Here is a log from SQLite (I don't understand why it's referring to main.videos table; it should be just videos table):

I, [2014-06-14T02:51:44.856546 #79819]  INFO -- : (0.000055s) BEGIN
E, [2014-06-14T02:51:44.857538 #79819] ERROR -- : SQLite3::SQLException: no such table: main.channels: INSERT INTO `video` (`id`, `title`) VALUES ('testId', 'testTitle')
I, [2014-06-14T02:51:44.857870 #79819]  INFO -- : (0.000028s) ROLLBACK
I, [2014-06-14T02:51:44.858899 #79819]  INFO -- : (0.000023s) BEGIN
I, [2014-06-14T02:51:44.859825 #79819]  INFO -- : (0.000320s) INSERT INTO `channel` (`id`, `title`) VALUES ('testId1', 'testTitle')
I, [2014-06-14T02:51:44.860286 #79819]  INFO -- : (0.000152s) SELECT * FROM `channel` WHERE (`id` = 'testId1') LIMIT 1
I, [2014-06-14T02:51:44.860760 #79819]  INFO -- : (0.000073s) SELECT count(*) AS 'count' FROM `channel` WHERE (`title` = 'testTitle') LIMIT 1
I, [2014-06-14T02:51:45.168971 #79819]  INFO -- : (0.308073s) ROLLBACK
I, [2014-06-14T02:51:45.169546 #79819]  INFO -- : (0.000062s) BEGIN
I, [2014-06-14T02:51:45.170778 #79819]  INFO -- : (0.000524s) INSERT INTO `channel` (`id`, `title`, `description`) VALUES ('testId1', 'testTitle', 'test')
I, [2014-06-14T02:51:45.172969 #79819]  INFO -- : (0.000633s) SELECT * FROM `channel` WHERE (`id` = 'testId1') LIMIT 1
I, [2014-06-14T02:51:45.173788 #79819]  INFO -- : (0.000191s) SELECT * FROM `channel` WHERE (`title` = 'testTitle') LIMIT 1
I, [2014-06-14T02:51:45.267479 #79819]  INFO -- : (0.093502s) ROLLBACK
I, [2014-06-14T02:51:45.267736 #79819]  INFO -- : (0.000057s) BEGIN
E, [2014-06-14T02:51:45.268541 #79819] ERROR -- : SQLite3::ConstraintException: channel.title may not be NULL: INSERT INTO `channel` (`id`) VALUES ('testId2')
I, [2014-06-14T02:51:45.269063 #79819]  INFO -- : (0.000062s) ROLLBACK
I, [2014-06-14T02:51:48.328115 #79819]  INFO -- : (0.000048s) BEGIN
E, [2014-06-14T02:51:48.328812 #79819] ERROR -- : SQLite3::SQLException: no such table: main.videos: INSERT INTO `comment` DEFAULT VALUES
I, [2014-06-14T02:51:48.329058 #79819]  INFO -- : (0.000027s) ROLLBACK
I, [2014-06-14T02:51:48.329548 #79819]  INFO -- : (0.000022s) BEGIN
I, [2014-06-14T02:51:48.330584 #79819]  INFO -- : (0.000373s) INSERT INTO `user` (`username`) VALUES ('testName')
I, [2014-06-14T02:51:48.330885 #79819]  INFO -- : (0.000095s) SELECT * FROM `user` WHERE (`id` = 1) LIMIT 1
I, [2014-06-14T02:51:48.331049 #79819]  INFO -- : (0.000064s) SELECT * FROM `user` LIMIT 1
I, [2014-06-14T02:51:48.331875 #79819]  INFO -- : (0.000716s) ROLLBACK

How can I solve this?

Yes I do - SO is very mindful about link rot - http://meta.stackexchange.com/questions/140772/link-rot-encourage-author-to-cache-snippets-of-relevant-text-to-prepare-for-thi — Uri Agassi, Jun 13 '14 at 20:18

score 0 · Accepted Answer · answered Jun 13 '14 at 20:16

0

You should mind your plurals:

many_to_one should point to a singular form of your table, as should foreign_key:

class Video < Sequel::Model(:video)
  unrestrict_primary_key
  many_to_one :channel # <--
  many_to_many :comments
end

unless DB.table_exists? :comment
  DB.create_table :comment do
    primary_key :id
    foreign_key :user_id, :user # <--
    foreign_key :video_id, :video # <--
    integer     :likeCount, :default => 0
  end
end

class Comment < Sequel::Model(:comment)
  many_to_one :user # <--
  many_to_many :videos
end

answered Jun 13 '14 at 20:16

Uri Agassi

36,848
14
76
93

Thank you for pointing that out. But it didn't help in my situation, problems are with *_to_many relations :\ – skatkov Jun 13 '14 at 20:26
@skat - are you sure the errors did not change? `main.videos` does not exist, since the table name is `video` not `videos`. When you used plurals instead of singulars (in the comment table) - that caused this specific problem – Uri Agassi Jun 13 '14 at 20:30
@skat - first off - don't change the code in the original question, since it invalidates the answers. Second - after your change you still have a `foreign_key` pointed at `:videos` - which is a table that doesn't exist. I'm not sure how you do it in sequel, but for a many_to_many relationship you don't simply make a foreign key - you need a join table. – Uri Agassi Jun 13 '14 at 20:37
Ok, thanks. I had 3 errors here, two of them you correctly pointed out: - singular form names for *_to_one relations - many_to_many relation should be dropped (it was erroneous) - somehow, sequel doesn't play well with tables that have primary key as string. I even tried to pass :type => string to foreign_key - but it didn't help. Thanks, I'll mark your answer as correct one. And I'll take a look at this foreign_key issue closely, maybe i spotted a bug here. Sequel maintainer is actively solving them, so I should report all my findings regarding that :) – skatkov Jun 13 '14 at 21:19

Having problems writing tests for models with *_to_many relationships

1 Answers1