vhost extension site scoping

[craayzie]

I now have two sites running on the same Radiant instance using the
vhost extension. When I try to add a snippet to the 2nd site,
ActiveRecord complains that I'm violating the unique constraint on the
snippet name. I thought the "apply_site_scoping" rake task of the
vhost extension would have limited any unique constraints to be within
the context of the site?

What's the proper way to have ActiveRecord apply this constraint only
within the site "namespace"?

2011-02-13T20:25:45-08:00 app[web.1]: ActiveRecord::StatementInvalid
(PGError: ERROR: duplicate key value violates unique constraint
"name"
2011-02-13T20:25:45-08:00 app[web.1]: : INSERT INTO
"snippets" ("name", "updated_by_id", "created_at", "filter_id",
"updated_at", "created_by_id", "lock_version", "content", "site_id")
VALUES('google_analytics', NULL, '2011-02-14 04:25:45.024735', '',
'2011-02-14 04:25:45.024735', 1, 0, 'fake-content', 2) RETURNING "id"):

[craayzie]

So this appears to be simple issue of the Snippets model validation
not being updated. I know the proper way to do this is to create an
ActiveRecord migration but just to see it work I hand-edited vendor/
radiant/app/models/snippet.rb

- validates_uniqueness_of :name, :scope
+ validates_uniqueness_of :name, :scope => :site_id

I then ran 'rake db:migrate' but nothing changed? Did I miss a step?

[craayzie]

It looks like it's a DB-level constraint that's throwing the error as
opposed to something at the model level .. still figuring out how to
correct this ..

SQLite3::ConstraintException: column name is not unique: INSERT INTO

"snippets" ("name", "updated_by_id", "created_at", "filter_id",
"updated_at", "created_by_id", "lock_version", "content", "site_id")

VALUES('google_analytics', NULL, '2011-02-14 05:59:51', '',
'2011-02-14 05:59:51', 1, 0, 'blam', 2)

[craayzie]

So I need to write a migration that will update the DB constraint to
allow for unique pairs of 'name' and 'site_id' records. Only example I
can find of how to do this requires creating and index:

add_index :snippets, [:name, :site_id], :unique=>true

Is this the best option? Or is there a way to create the constraint
without generating an index?

[William Ross]

On 14 Feb 2011, at 05:46, craayzie wrote:

So this appears to be simple issue of the Snippets model validation
not being updated. I know the proper way to do this is to create an
ActiveRecord migration but just to see it work I hand-edited vendor/
radiant/app/models/snippet.rb

- validates_uniqueness_of :name, :scope
+ validates_uniqueness_of :name, :scope => :site_id

I then ran 'rake db:migrate' but nothing changed? Did I miss a step?

Slight muddle there: migrations change the database columns. Validations are applied in the model before the object is saved to the database.

In the case of Snippets you have both kinds: there's a uniqueness constraint in the database which is supported by a validates_uniqueness call in the model class so that we can return advisory messages rather than just throwing errors.

I don't know what the current situation is with the vhost extension but your fix to the validates_uniqueness call is right and you can remove the uniqueness constraint from the database with this migration:

remove_index :snippets, :name

You'll find similar validations on Layout and User but perhaps those won't matter.

best,

will

[craayzie]

So weird. I run the migration but it does nothing to db/schema.rb:

add_index "snippets", ["name", "site_id"], :name =>
"index_snippets_on_name_and_site_id", :unique => true
add_index "snippets", ["name"], :name => "name", :unique => true

Here's the exact migration:

$ cat db/migrate/004_migration4.rb
class AddSiteIdIndex < ActiveRecord::Migration
def self.up
remove_index :snippets, :name
end

def self.down
add_index "snippets", ["name"], :name => "name", :unique => true
end
end

Any ideas?

[William Ross]

On 14 Feb 2011, at 08:14, craayzie wrote:

> So weird. I run the migration but it does nothing to db/schema.rb:
>
> add_index "snippets", ["name", "site_id"], :name =>
> "index_snippets_on_name_and_site_id", :unique => true
> add_index "snippets", ["name"], :name => "name", :unique => true
>
> Here's the exact migration:
>
> $ cat db/migrate/004_migration4.rb
> class AddSiteIdIndex < ActiveRecord::Migration
> def self.up
> remove_index :snippets, :name
> end

It's probably the named index, and me being too hasty. Try

remove_index :snippets, :name => 'name'

will

[Haselwanter Edmund]

Snippet:
name:
message:
'Name wurde schon verwendet'
scope:
site_id

in config/vhost.yml

should do the trick ...

--
DI Edmund Haselwanter, edm...@haselwanter.com, http://edmund.haselwanter.com/
http://www.iteh.at | http://facebook.com/iTeh.solutions | http://at.linkedin.com/in/haselwanteredmund

[craayzie]

What is it about the config/vhost.yml file that would make this unique
index on the "name" attribute go away?

On a related note it looks like sqlite requires that all indexed
columns be unique!
SQLite3::ConstraintException: indexed columns are not unique: CREATE
UNIQUE INDEX "name" ON "snippets" ("name")

Has anyone run into this before?


On Feb 14, 12:50 am, Haselwanter Edmund <edm...@haselwanter.com>
wrote:

> DI Edmund Haselwanter, edm...@haselwanter.com,http://edmund.haselwanter.com/http://www.iteh.at|http://facebook.com/iTeh.solutions|http://at.linkedin.com/in/haselwanteredmund

[craayzie]

The migration to remove the unique constraint works great (below) but
when I try to roll it back I get the following from sqlite:

$ rake db:rollback
(in /Users/username/Sites/heroku)
== CleanUpSnippetConstraints1: reverting
=====================================
-- remove_index(:snippets, {:name=>"index_snippets_on_name"})
-> 0.0010s
-- add_index("snippets", ["name"], {:unique=>true, :name=>"name"})
rake aborted!
An error has occurred, this and all later migrations canceled:

SQLite3::ConstraintException: indexed columns are not unique: CREATE
UNIQUE INDEX "name" ON "snippets" ("name")

$ cat db/migrate/20091003095746_clean_up_snippet_constraints1.rb
class CleanUpSnippetConstraints1 < ActiveRecord::Migration
def self.up
remove_index :snippets, :name => "name"
add_index :snippets, ["name"], :name =>
"index_snippets_on_name", :unique => false
end

def self.down
remove_index :snippets, :name => "index_snippets_on_name"

add_index "snippets", ["name"], :name => "name", :unique => true
end
end

$ rake db:migrate
(in /Users/username/Sites/heroku)
== CleanUpSnippetConstraints1: migrating
=====================================
-- remove_index(:snippets, {:name=>"name"})
-> 0.0004s
-- add_index(:snippets, ["name"],
{:unique=>false, :name=>"index_snippets_on_name"})
-> 0.0753s
== CleanUpSnippetConstraints1: migrated (0.0760s)
============================

[craayzie]

Below is the migration that finally did the trick! There wasn't really
a need to have an index on the name attribute anyways since it'll
always require the site_id.

I'm still interested in Haselwanter's note about how the config/
vhost.yml file alone should have taken care of this. Am I missing
something obvious here?

class CleanUpSnippetConstraints < ActiveRecord::Migration

def self.up
remove_index :snippets, :name => "name"

end

def self.down

add_index "snippets", ["name"], :name => "name", :unique => true
end
end

[Haselwanter Edmund]

On 15.02.2011, at 01:37, craayzie wrote:

> Below is the migration that finally did the trick! There wasn't really
> a need to have an index on the name attribute anyways since it'll
> always require the site_id.

The uniqueness of a field in rails is a programatic constrain in ruby, not a constrain on the database layer.
The vhost.yml file takes care of this and evaluates this.

https://github.com/saturnflyer/radiant-vhost-extension/blob/master/lib/vhost/site_scoped_model_extensions.rb#L12

>
> I'm still interested in Haselwanter's note about how the config/
> vhost.yml file alone should have taken care of this. Am I missing
> something obvious here?

yes:

There is some code in the rake radiant:extensions:vhost:apply_site_scoping which alters special index
behaviour. At least for snippets it should just work

https://github.com/saturnflyer/radiant-vhost-extension/blob/master/lib/tasks/add_site_columns.rb#L45

--

DI Edmund Haselwanter, edm...@haselwanter.com, http://edmund.haselwanter.com/

[craayzie]

Oh wow. Interesting. I installed the radiant-vhost-extension by
installing the gem. The add_site_columns.rb that comes with the gem
looks very different than the one on github that you linked to.
http://pastie.org/1566676

Seems like a bug w/ the radiant-vhost-extension gem?

On Feb 15, 1:16 am, Haselwanter Edmund <edm...@haselwanter.com> wrote:
> On 15.02.2011, at 01:37, craayzie wrote:
>
> > Below is the migration that finally did the trick! There wasn't really
> > a need to have an index on the name attribute anyways since it'll
> > always require the site_id.
>
> The uniqueness of a field in rails is a programatic constrain in ruby, not a constrain on the database layer.
> The vhost.yml file takes care of this and evaluates this.
>

> https://github.com/saturnflyer/radiant-vhost-extension/blob/master/li...

>
>
>
> > I'm still interested in Haselwanter's note about how the config/
> > vhost.yml file alone should have taken care of this. Am I missing
> > something obvious here?
>
> yes:
>
> There is some code in the rake radiant:extensions:vhost:apply_site_scoping which alters special index
> behaviour. At least for snippets it should just work
>

> https://github.com/saturnflyer/radiant-vhost-extension/blob/master/li...

> DI Edmund Haselwanter, edm...@haselwanter.com,http://edmund.haselwanter.com/http://www.iteh.at|http://facebook.com/iTeh.solutions|http://at.linkedin.com/in/haselwanteredmund