automated users to rusers migration without old.publiclab.org

[btbonval]

Running old.publiclab.org is a security vulnerability.

However, there is a process that engages between old and new public labs which migrate a user in the users table into the rusers table. So far, in order to migrate old users, we've had to turn on the old site.

As far as I can tell, the migration simply copies some data between two tables.

This was the idea I had back in late August:

1. sort through the users table to find all usernames which are not already in rusers (this is very easy SQL)
2. create a new entry in rusers with username, email address, and openid_identifier based on data from users; the rest can be left as defaults; also assign a random password hash.

There might have been another ticket or another email thread with the easy SQL from step 1, but I can't find it. Should be easy to recreate.

[btbonval]

I have attempted to manually migrate someone from the old table to the new table.

This isn't legit SQL. OLD is going to represent a variable holding the user's record from the users table.

INSERT INTO rusers (username, email, created_at, updated_at, openid_identifier, role, reset_key) VALUES ({OLD.name}, {OLD.mail}, now(), now(), 'http://old.publiclab.org/user/{OLD.uid}/identity', 'basic', 'somebiglongresetpasswordlink');

If this works, we can automate the process.

[jywarren]

Could we do this on a per-person basis when they next sign in? To avoid creating rusers we don't need? Just a thought, i don't know if that's better.

On Sun, Dec 15, 2013 at 11:04 PM, Bryan Bonvallet notifications@github.comwrote:

I have attempted to manually migrate someone from the old table to the new table.

This isn't legit SQL. OLD is going to represent a variable holding the user's record from the users table.

INSERT INTO rusers (username, email, created_at, updated_at, openid_identifier, role, reset_key) VALUES ({OLD.name}, {OLD.mail}, now(), now(), 'http://old.publiclab.org/user/{OLD.uid}/identity', 'basic', 'somebiglongresetpasswordlink');

If this works, we can automate the process.

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-30632594 .

[btbonval]

Not a bad thought.

What we'd want to do in that case is something like this:

1. if a user login fails, we need to check if the user is defined in users but not rusers
2. if a user is found in users but not rusers, perform the migration and assign a random reset key
3. email the user with the reset key info
4. write a flash message indicating that the user should check his/her email, contact web@ for support.

There isn't a way to verify a person against users because we can't hash the password correctly, assuming they even have one (some folks like me were using openid). We have to resort to password reset. But this could be a good way to trigger that process. -Bryan

On Mon, Dec 16, 2013 at 10:44 AM, Jeffrey Warren notifications@github.comwrote:

Could we do this on a per-person basis when they next sign in? To avoid creating rusers we don't need? Just a thought, i don't know if that's better.

On Sun, Dec 15, 2013 at 11:04 PM, Bryan Bonvallet notifications@github.comwrote:

I have attempted to manually migrate someone from the old table to the new table.

This isn't legit SQL. OLD is going to represent a variable holding the user's record from the users table.

INSERT INTO rusers (username, email, created_at, updated_at, openid_identifier, role, reset_key) VALUES ({OLD.name}, {OLD.mail}, now(), now(), 'http://old.publiclab.org/user/{OLD.uid}/identity', 'basic', 'somebiglongresetpasswordlink');

If this works, we can automate the process.

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30632594> .

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-30670436 .

[btbonval]

User confirmed the little trick I performed above worked. We are clear to automate this process using something like what Jeff described.

[jywarren]

Great; the filter that 'catches' non-migrated users is here:

https://github.com/jywarren/plots2/blob/master/app/controllers/user_sessions_controller.rb#L49

redirects here, if I am reading this right; we'll have to check where the openid redirect actually happens: https://github.com/jywarren/plots2/blob/master/app/controllers/users_controller.rb#L19

On Wed, Dec 18, 2013 at 1:00 PM, Bryan Bonvallet notifications@github.comwrote:

User confirmed the little trick I performed above worked. We are clear to automate this process using something like what Jeff described.

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-30864737 .

[btbonval]

I recall reading that code a long time ago and being confused by it (the users controller bit).

However, the user sessions bit is pretty clear methinks. We want to rewrite that code to migrate the user rather than forwarding to the users controller.

On Wed, Dec 18, 2013 at 1:07 PM, Jeffrey Warren notifications@github.comwrote:

Great; the filter that 'catches' non-migrated users is here:

https://github.com/jywarren/plots2/blob/master/app/controllers/user_sessions_controller.rb#L49

redirects here, if I am reading this right; we'll have to check where the openid redirect actually happens:

https://github.com/jywarren/plots2/blob/master/app/controllers/users_controller.rb#L19

On Wed, Dec 18, 2013 at 1:00 PM, Bryan Bonvallet notifications@github.comwrote:

User confirmed the little trick I performed above worked. We are clear to automate this process using something like what Jeff described.

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30864737> .

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-30865337 .

[jywarren]

The weird thing is that the user_sessions controller function is run twice -- once on first arrival, and again after the external site redirects you back with extra parameters. It's pretty arcane :-(

But yeah, we should try to catch it before it sends you to the external site.

On Wed, Dec 18, 2013 at 1:11 PM, Bryan Bonvallet notifications@github.comwrote:

I recall reading that code a long time ago and being confused by it (the users controller bit).

However, the user sessions bit is pretty clear methinks. We want to rewrite that code to migrate the user rather than forwarding to the users controller.

On Wed, Dec 18, 2013 at 1:07 PM, Jeffrey Warren notifications@github.comwrote:

Great; the filter that 'catches' non-migrated users is here:

https://github.com/jywarren/plots2/blob/master/app/controllers/user_sessions_controller.rb#L49

redirects here, if I am reading this right; we'll have to check where the openid redirect actually happens:

https://github.com/jywarren/plots2/blob/master/app/controllers/users_controller.rb#L19

On Wed, Dec 18, 2013 at 1:00 PM, Bryan Bonvallet notifications@github.comwrote:

User confirmed the little trick I performed above worked. We are clear to automate this process using something like what Jeff described.

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30864737> .

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30865337> .

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-30865616 .

[btbonval]

We can probably write something into the user controller to perform migration. It seems appropriate there. The user sessions controller can identify the old table usage and then call the user's migration function.

The biggest problem I see here is that we won't be able to test this out very well. I suppose we can create a new user, delete the contents of ruser, and try to login?

On Wed, Dec 18, 2013 at 1:15 PM, Jeffrey Warren notifications@github.comwrote:

The weird thing is that the user_sessions controller function is run twice -- once on first arrival, and again after the external site redirects you back with extra parameters. It's pretty arcane :-(

But yeah, we should try to catch it before it sends you to the external site.

On Wed, Dec 18, 2013 at 1:11 PM, Bryan Bonvallet notifications@github.comwrote:

I recall reading that code a long time ago and being confused by it (the users controller bit).

However, the user sessions bit is pretty clear methinks. We want to rewrite that code to migrate the user rather than forwarding to the users controller.

On Wed, Dec 18, 2013 at 1:07 PM, Jeffrey Warren < notifications@github.com>wrote:

Great; the filter that 'catches' non-migrated users is here:

https://github.com/jywarren/plots2/blob/master/app/controllers/user_sessions_controller.rb#L49

redirects here, if I am reading this right; we'll have to check where the openid redirect actually happens:

https://github.com/jywarren/plots2/blob/master/app/controllers/users_controller.rb#L19

On Wed, Dec 18, 2013 at 1:00 PM, Bryan Bonvallet notifications@github.comwrote:

User confirmed the little trick I performed above worked. We are clear to automate this process using something like what Jeff described.

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30864737> .

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30865337> .

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30865616> .

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-30865938 .

[jywarren]

That sounds reasonable; but we'll have to be sure the other appropriate dependent records also get deleted. Although if it's a new user, maybe there won't be any other stray bits yet.

On Wed, Dec 18, 2013 at 1:18 PM, Bryan Bonvallet notifications@github.comwrote:

We can probably write something into the user controller to perform migration. It seems appropriate there. The user sessions controller can identify the old table usage and then call the user's migration function.

The biggest problem I see here is that we won't be able to test this out very well. I suppose we can create a new user, delete the contents of ruser, and try to login?

On Wed, Dec 18, 2013 at 1:15 PM, Jeffrey Warren notifications@github.comwrote:

The weird thing is that the user_sessions controller function is run twice -- once on first arrival, and again after the external site redirects you back with extra parameters. It's pretty arcane :-(

But yeah, we should try to catch it before it sends you to the external site.

On Wed, Dec 18, 2013 at 1:11 PM, Bryan Bonvallet notifications@github.comwrote:

I recall reading that code a long time ago and being confused by it (the users controller bit).

However, the user sessions bit is pretty clear methinks. We want to rewrite that code to migrate the user rather than forwarding to the users controller.

On Wed, Dec 18, 2013 at 1:07 PM, Jeffrey Warren < notifications@github.com>wrote:

Great; the filter that 'catches' non-migrated users is here:

https://github.com/jywarren/plots2/blob/master/app/controllers/user_sessions_controller.rb#L49

redirects here, if I am reading this right; we'll have to check where the openid redirect actually happens:

https://github.com/jywarren/plots2/blob/master/app/controllers/users_controller.rb#L19

On Wed, Dec 18, 2013 at 1:00 PM, Bryan Bonvallet notifications@github.comwrote:

User confirmed the little trick I performed above worked. We are clear to automate this process using something like what Jeff described.

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30864737>

.

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30865337> .

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30865616> .

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-30865938> .

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-30866234 .

[jywarren]

Let's make a button to "migrate in" a user, so we can test this working in Ruby code before doing anything automated.

def migrate
  du = DrupalUsers.find(uid)
  u = User.new({
    :username => du.name,
    :email => du.mail,
    :openid_identifier => "http://old.publiclab.org/user/"+uid.to_s+"/identity",
    ...etc...
  }).save({})
  u.reset_key
end

Also, as shown on the last line, let's move the reset key function into the User model, so we can call it DRYly: https://github.com/jywarren/plots2/blob/master/app/controllers/users_controller.rb#L169

Sanity check?

[btbonval]

I hadn't thought of a button on the user's profile page, but it is a good idea. Would it be available to moderators or just admin? We should make sure it only shows up for old-style users.

I think it makes sense to move the password reset into the model. It'll be accessible from the controller and it makes sense in the model as it modifies a low-level user model attribute as opposed to performing any sort of calculations for rendering to a web page.

I think very specifically these are the lines we want in User.reset_key: https://github.com/jywarren/plots2/blob/e39f331fe3da68967f216623329ece24254315db/app/controllers/users_controller.rb#L167-L173

I don't know if we necessarily always want to email the user, maybe there is a parameter of :email => true sends notification, :email => false does not. In that case, we'd take this whole bit and pass the email parameter as desired: https://github.com/jywarren/plots2/blob/e39f331fe3da68967f216623329ece24254315db/app/controllers/users_controller.rb#L167-L175

[jywarren]

OK, added a button etc in 62d8aa9; check it out. I tested it locally only so far. I esp. want to check that it actually sends the email for pwd reset.

[jywarren]

oops i meant 07c288f

[jywarren]

I guess i don't know how to use hashes. I REALLY mean this: https://github.com/jywarren/plots2/commit/d8c4b96679f9f740473365b8067ebd9383d3f7d0

[btbonval]

Out of curiousity I ran the reverse of what we want:

SELECT username FROM rusers WHERE username NOT IN (SELECT name FROM users);

There are 4 users in User but not in DrupalUser. Might be worth looking into those.

This is a bad idea and I need to find a better query, but in theory this would return all users whom need to be migrated:

SELECT name FROM users WHERE name NOT IN (SELECT username FROM rusers);

[btbonval]

Ohhh btw I tried those things in Rails ActiveRecord instead of jumping straight to SQL. Rails 4 has a really handy thing that could be

DrupalUsers.select(:name).where.not(name: User.select(:username))

That would turn into exactly the second SQL posted above.

Unfortunately we're running Rails 3. There was no good way to setup such a query, and I tried for a little while. This should work, but doesn't return anything meaningful:

DrupalUsers.select(:name).where('name NOT IN (?)', User.select(:username))

In all cases of my testing (both SQL and ActiveRecord) I called the aggregate COUNT()/.count to prevent returning a million billion strings which would bog down RAM resources.

I should try a RIGHT OUTER JOIN of User on DrupalUsers and then look for NULLs in User.name or User.id in the where clause. Might run better than a subquery. No idea how to do that in ActiveRecord :/

[jywarren]

There is manual join syntax... :joins => :users but I'm not sure about the outerness. Outieness. On Apr 4, 2014 11:52 PM, "Bryan Bonvallet" notifications@github.com wrote:

Ohhh btw I tried those things in Rails ActiveRecord instead of jumping straight to SQL. Rails 4 has a really handy thing that could be

DrupalUsers.select(:name).where.not(name: User.select(:username))

That would turn into exactly the second SQL posted above.

Unfortunately we're running Rails 3. There was no good way to setup such a query, and I tried for a little while. This should work, but doesn't return anything meaningful:

DrupalUsers.select(:name).where('name NOT IN (?)', User.select(:username)

In all cases of my testing (both SQL and ActiveRecord) I called the aggregate COUNT()/.count to prevent returning a million billion strings which would bog down RAM resources.

I should try a RIGHT OUTER JOIN of User on DrupalUsers and then look for NULLs in User.name or User.id in the where clause. Might run better than a subquery. No idea how to do that in ActiveRecord :/

Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-39628143 .

[btbonval]

I don't remember seeing that syntax, but I'll look again. I think that operator is called a rocket? In case I need to do lexographic search on the webs. On Apr 5, 2014 9:08 AM, "Jeffrey Warren" notifications@github.com wrote:

There is manual join syntax... :joins => :users but I'm not sure about the outerness. Outieness. On Apr 4, 2014 11:52 PM, "Bryan Bonvallet" notifications@github.com wrote:

Ohhh btw I tried those things in Rails ActiveRecord instead of jumping straight to SQL. Rails 4 has a really handy thing that could be

DrupalUsers.select(:name).where.not(name: User.select(:username))

That would turn into exactly the second SQL posted above.

Unfortunately we're running Rails 3. There was no good way to setup such a query, and I tried for a little while. This should work, but doesn't return anything meaningful:

DrupalUsers.select(:name).where('name NOT IN (?)', User.select(:username)

In all cases of my testing (both SQL and ActiveRecord) I called the aggregate COUNT()/.count to prevent returning a million billion strings which would bog down RAM resources.

I should try a RIGHT OUTER JOIN of User on DrupalUsers and then look for NULLs in User.name or User.id in the where clause. Might run better than a subquery. No idea how to do that in ActiveRecord :/

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39628143> .

Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-39637221 .

[jywarren]

It should be a parameter in the find() method of activerecord. It used to be :includes => if I recall. Here's a left outer join: http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3#3246059 On Apr 5, 2014 9:10 AM, "Bryan Bonvallet" notifications@github.com wrote:

I don't remember seeing that syntax, but I'll look again. I think that operator is called a rocket? In case I need to do lexographic search on the webs. On Apr 5, 2014 9:08 AM, "Jeffrey Warren" notifications@github.com wrote:

There is manual join syntax... :joins => :users but I'm not sure about the outerness. Outieness. On Apr 4, 2014 11:52 PM, "Bryan Bonvallet" notifications@github.com wrote:

Ohhh btw I tried those things in Rails ActiveRecord instead of jumping straight to SQL. Rails 4 has a really handy thing that could be

DrupalUsers.select(:name).where.not(name: User.select(:username))

That would turn into exactly the second SQL posted above.

Unfortunately we're running Rails 3. There was no good way to setup such a query, and I tried for a little while. This should work, but doesn't return anything meaningful:

DrupalUsers.select(:name).where('name NOT IN (?)', User.select(:username)

In all cases of my testing (both SQL and ActiveRecord) I called the aggregate COUNT()/.count to prevent returning a million billion strings which would bog down RAM resources.

I should try a RIGHT OUTER JOIN of User on DrupalUsers and then look for NULLs in User.name or User.id in the where clause. Might run better than a subquery. No idea how to do that in ActiveRecord :/

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39628143> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637221> .

Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-39637273 .

[btbonval]

Sweet. This weekend I'm hacking on a rails project for FinalsClub work. Diving into plots2 was a helpful refresher of rails. Given I'm back to rails, I'll see what I can do with plots in the coming weeks/months. Always helpful to be using the same architecture across projects. On Apr 5, 2014 9:13 AM, "Jeffrey Warren" notifications@github.com wrote:

It should be a parameter in the find() method of activerecord. It used to be :includes => if I recall. Here's a left outer join:

http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3#3246059 On Apr 5, 2014 9:10 AM, "Bryan Bonvallet" notifications@github.com wrote:

I don't remember seeing that syntax, but I'll look again. I think that operator is called a rocket? In case I need to do lexographic search on the webs. On Apr 5, 2014 9:08 AM, "Jeffrey Warren" notifications@github.com wrote:

There is manual join syntax... :joins => :users but I'm not sure about the outerness. Outieness. On Apr 4, 2014 11:52 PM, "Bryan Bonvallet" notifications@github.com wrote:

Ohhh btw I tried those things in Rails ActiveRecord instead of jumping straight to SQL. Rails 4 has a really handy thing that could be

DrupalUsers.select(:name).where.not(name: User.select(:username))

That would turn into exactly the second SQL posted above.

Unfortunately we're running Rails 3. There was no good way to setup such a query, and I tried for a little while. This should work, but doesn't return anything meaningful:

DrupalUsers.select(:name).where('name NOT IN (?)', User.select(:username)

In all cases of my testing (both SQL and ActiveRecord) I called the aggregate COUNT()/.count to prevent returning a million billion strings which would bog down RAM resources.

I should try a RIGHT OUTER JOIN of User on DrupalUsers and then look for NULLs in User.name or User.id in the where clause. Might run better than a subquery. No idea how to do that in ActiveRecord :/

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39628143> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637221> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637273> .

Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-39637337 .

[jywarren]

One thing I worry about is whether wed be bringing a whole lot of spam users back in by migrating them. Thoughts? On Apr 5, 2014 9:16 AM, "Bryan Bonvallet" notifications@github.com wrote:

Sweet. This weekend I'm hacking on a rails project for FinalsClub work. Diving into plots2 was a helpful refresher of rails. Given I'm back to rails, I'll see what I can do with plots in the coming weeks/months. Always helpful to be using the same architecture across projects. On Apr 5, 2014 9:13 AM, "Jeffrey Warren" notifications@github.com wrote:

It should be a parameter in the find() method of activerecord. It used to be :includes => if I recall. Here's a left outer join:

http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3#3246059 On Apr 5, 2014 9:10 AM, "Bryan Bonvallet" notifications@github.com wrote:

I don't remember seeing that syntax, but I'll look again. I think that operator is called a rocket? In case I need to do lexographic search on the webs. On Apr 5, 2014 9:08 AM, "Jeffrey Warren" notifications@github.com wrote:

There is manual join syntax... :joins => :users but I'm not sure about the outerness. Outieness. On Apr 4, 2014 11:52 PM, "Bryan Bonvallet" <notifications@github.com

wrote:

Ohhh btw I tried those things in Rails ActiveRecord instead of jumping straight to SQL. Rails 4 has a really handy thing that could be

DrupalUsers.select(:name).where.not(name: User.select(:username))

That would turn into exactly the second SQL posted above.

Unfortunately we're running Rails 3. There was no good way to setup such a query, and I tried for a little while. This should work, but doesn't return anything meaningful:

DrupalUsers.select(:name).where('name NOT IN (?)', User.select(:username)

In all cases of my testing (both SQL and ActiveRecord) I called the aggregate COUNT()/.count to prevent returning a million billion strings which would bog down RAM resources.

I should try a RIGHT OUTER JOIN of User on DrupalUsers and then look for NULLs in User.name or User.id in the where clause. Might run better than a subquery. No idea how to do that in ActiveRecord :/

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39628143> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637221> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637273> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637337> .

Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-39637402 .

[btbonval]

That's why I think we should attach migration to password reset.

I'm only running this query for test subjects for the button. Spammers are the best test subjects if we screw up, and if we succeed, we take 'em out back and delete them like old yeller.

Lemons, lemonade. On Apr 5, 2014 9:20 AM, "Jeffrey Warren" notifications@github.com wrote:

One thing I worry about is whether wed be bringing a whole lot of spam users back in by migrating them. Thoughts? On Apr 5, 2014 9:16 AM, "Bryan Bonvallet" notifications@github.com wrote:

Sweet. This weekend I'm hacking on a rails project for FinalsClub work. Diving into plots2 was a helpful refresher of rails. Given I'm back to rails, I'll see what I can do with plots in the coming weeks/months. Always helpful to be using the same architecture across projects. On Apr 5, 2014 9:13 AM, "Jeffrey Warren" notifications@github.com wrote:

It should be a parameter in the find() method of activerecord. It used to be :includes => if I recall. Here's a left outer join:

http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3#3246059

On Apr 5, 2014 9:10 AM, "Bryan Bonvallet" notifications@github.com wrote:

I don't remember seeing that syntax, but I'll look again. I think that operator is called a rocket? In case I need to do lexographic search on the webs. On Apr 5, 2014 9:08 AM, "Jeffrey Warren" notifications@github.com wrote:

There is manual join syntax... :joins => :users but I'm not sure about the outerness. Outieness. On Apr 4, 2014 11:52 PM, "Bryan Bonvallet" < notifications@github.com

wrote:

Ohhh btw I tried those things in Rails ActiveRecord instead of jumping straight to SQL. Rails 4 has a really handy thing that could be

DrupalUsers.select(:name).where.not(name: User.select(:username))

That would turn into exactly the second SQL posted above.

Unfortunately we're running Rails 3. There was no good way to setup such a query, and I tried for a little while. This should work, but doesn't return anything meaningful:

DrupalUsers.select(:name).where('name NOT IN (?)', User.select(:username)

In all cases of my testing (both SQL and ActiveRecord) I called the aggregate COUNT()/.count to prevent returning a million billion strings which would bog down RAM resources.

I should try a RIGHT OUTER JOIN of User on DrupalUsers and then look for NULLs in User.name or User.id in the where clause. Might run better than a subquery. No idea how to do that in ActiveRecord :/

Reply to this email directly or view it on GitHub<

https://github.com/jywarren/plots2/issues/213#issuecomment-39628143>

.

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637221> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637273> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637337> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637402> .

Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-39637460 .

[jywarren]

Rad. On Apr 5, 2014 9:22 AM, "Bryan Bonvallet" notifications@github.com wrote:

That's why I think we should attach migration to password reset.

I'm only running this query for test subjects for the button. Spammers are the best test subjects if we screw up, and if we succeed, we take 'em out back and delete them like old yeller.

Lemons, lemonade. On Apr 5, 2014 9:20 AM, "Jeffrey Warren" notifications@github.com wrote:

One thing I worry about is whether wed be bringing a whole lot of spam users back in by migrating them. Thoughts? On Apr 5, 2014 9:16 AM, "Bryan Bonvallet" notifications@github.com wrote:

Sweet. This weekend I'm hacking on a rails project for FinalsClub work. Diving into plots2 was a helpful refresher of rails. Given I'm back to rails, I'll see what I can do with plots in the coming weeks/months. Always helpful to be using the same architecture across projects. On Apr 5, 2014 9:13 AM, "Jeffrey Warren" notifications@github.com wrote:

It should be a parameter in the find() method of activerecord. It used to be :includes => if I recall. Here's a left outer join:

http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3#3246059

On Apr 5, 2014 9:10 AM, "Bryan Bonvallet" notifications@github.com wrote:

I don't remember seeing that syntax, but I'll look again. I think that operator is called a rocket? In case I need to do lexographic search on the webs. On Apr 5, 2014 9:08 AM, "Jeffrey Warren" <notifications@github.com

wrote:

There is manual join syntax... :joins => :users but I'm not sure about the outerness. Outieness. On Apr 4, 2014 11:52 PM, "Bryan Bonvallet" < notifications@github.com

wrote:

Ohhh btw I tried those things in Rails ActiveRecord instead of jumping straight to SQL. Rails 4 has a really handy thing that could be

DrupalUsers.select(:name).where.not(name: User.select(:username))

That would turn into exactly the second SQL posted above.

Unfortunately we're running Rails 3. There was no good way to setup such a query, and I tried for a little while. This should work, but doesn't return anything meaningful:

DrupalUsers.select(:name).where('name NOT IN (?)', User.select(:username)

In all cases of my testing (both SQL and ActiveRecord) I called the aggregate COUNT()/.count to prevent returning a million billion strings which would bog down RAM resources.

I should try a RIGHT OUTER JOIN of User on DrupalUsers and then look for NULLs in User.name or User.id in the where clause. Might run better than a subquery. No idea how to do that in ActiveRecord :/

Reply to this email directly or view it on GitHub<

https://github.com/jywarren/plots2/issues/213#issuecomment-39628143>

.

Reply to this email directly or view it on GitHub<

https://github.com/jywarren/plots2/issues/213#issuecomment-39637221>

.

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637273> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637337> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637402> .

Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/213#issuecomment-39637460> .

Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/213#issuecomment-39637507 .

[jywarren]

Are we good to close this since we have the button for migrating people now, and it works? Reopen if you're still interested in the batch script.