Topic: How to model multiple tables with same schema

I'm trying to convert an old PHP project to rails.

I have 7 database servers, I replicate the data from these to one beefy server for feeding the website.  I'm trying to figure out how I can do the modeling with Rails without having to make/remove classes if I add/remove a server and have use of all the builtin Rails relationships.  Here's a brief layout:

Servers table:
id, name, ip, etc

Then I have tables for each server:
0_device
1_device
...
6_device

There are 15 other tables that relate to the device with the same format, <server_id>_<tablename>.  I can't merge them without having the ID's overlap, and it would make huge tables.

I have a table called Devices, with the DeviceID and ServerID.  In PHP, I query it with DeviceID to find the ServerID and then use:

 $serverid."_device" 

wherever I need a specific table.

Any idea's how I can do that in Rails?

Thanks,

-Drognan

Last edited by Drognan (2012-02-27 17:08:42)

Re: How to model multiple tables with same schema

I'm not sure that you have provided enough info for me to fully understand your requirement
It seems like your setup is overly complex, I would have thought that you should have a servers table with a record for each device?

I'm also not clear on where your models are supposed to be looking for theirt data, on the one main db or on each individual server?

I'd be happy to help if you can supply more clarity?

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: How to model multiple tables with same schema

Thanks for the reply.  The individual servers are old(Mysql4) and altering them directly voids the support contract from the vendor.  I have some perl scripts that copy the data off these servers to another server that I use for the website that displays the data.  There are 16 tables I copy with the format <serverid>_<tablename>, if I can get one working I can get them all working so lets pretend there's one table copied from each server, 0_device, 1_device... 6_device.

I have worked with an MVC framework on another project so all the tables that I've created within this database will work with Rails(once I set the primary key), besides the tables imported from the other servers.    In the Servers table, I have id, ip, name, the perl scripts I use to load the main database from the remote server loop thru this to get the data from each server.  Then I have a Devices tables that has: serverid, deviceid, so I know what server's tables to look for the device in.

Here's some sample PHP code:

To get a list of all devices:
$resultservers = mysql_query("select server_id from Servers");
while(list($server_id) = mysql_fetch_array($resultservers))
{
   $resultdevices = mysql_query("select device_id, name from ".$server_id."_device");
   while(list ($device_id, $name) = mysql_fetch_array($resultdevices))
   {
      print $device_id." ".$name."<br>\n";
   }
}

To get one device:
$resultdevice = mysql_query("select server_id from Devices where device_id = '33321241'");
list($server_id) = mysql_fetch_array($resultdevice);

$resultdevice2 = mysql_querY("select name from ".$server_id."_device where device_id = '33321241'");
list($name) = mysql_fetch_array($resultdevice2);
print $name;

Let me know if you need more information.

Re: How to model multiple tables with same schema

I'm still not clear on where your problem lies, the php code you have shown is easily replicable in rails with the following code, although I have never cut a single line of php.

controller

  def index
    @servers = Server.all
  end

and the corresponding view template
index.html.erb

<% @servers.each do |server| %>
  <%= "#{server.device_id} #{server.name}" <br />
<%end%>

For that to work you would need to have a server model that connects to whatever table you need

 class Server < ActiveRecord::Base
 end

That's it assuming you have a table named servers

I suspect I haven't addressed your issue because that's just basic rails stuff so if you can tell me where, in the above code, your difficulty lies then I'll try to clarify further

Actually, I think something has just clicked
It's the relationships between the servers and the devices tables you are trying to figure out?
Can you give me a simple use case or can we expand on the code I have given you to get this working rather than php code?

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: How to model multiple tables with same schema

I'm thinking you need in your model

 class Server < ActiveRecord::Base
  has_many :device_0s
  has_many device_is
  #....
 end

 class Device_1 < ActiveRecord::Base
  belongs_to :server
 end

 class Device_2 < ActiveRecord::Base
  belongs_to :server
 end
etc...

So you can then use
a Rails console example

  >> s = Server.first
...
  >> s.device_1s.each do |d|
            puts d.name
            puts d.server.ip
       end
  >>

Does that make sense?

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: How to model multiple tables with same schema

You could create a class that handles all devices, hooked up to a mysql view. It seems like a very strange db design to duplicate fields and columns in multiple tables. not normalised at all and rails works much better with a normalised database but that;'s your call and I'm sure whatever you choose it can work

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: How to model multiple tables with same schema

Thanks, the portion I get from the vendor's database servers isn't normalized, their database design isn't good.   I think I'll have to Device_1, etc, we don't add servers very often.

I found something like this on the internet, but it didn't work:

@servers.each do |server|
  eval <<-END_EVAL
    class #{server.id}_DeviceController < ActiveRecord::Base
      def index
        @#{server.id}.devices.all
        
        respond_to do |format|
          format.html # index.html.erb
        end
      end
  END_EVAL      
end

Re: How to model multiple tables with same schema

ok, but can you say in Engilsh what you are trying to achieve? I still lack clarity on your spec.
Give me a 'use case'

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: How to model multiple tables with same schema

I want to be able to use the #_tables and have the relationships work but not have to add new classes if I add an additional server's tables to the list(7_table).

Re: How to model multiple tables with same schema

Well, that's dead easy to do but the relationship rules might take a bit of figuring out/
You can set the table_name property at run time in your model.
So you only need 1 device class.
you could do this either as a class method or an instance method (There might be chaching issues so you will have to experiment a bit

class Device < Activerecord::Base
  def set_table_name(device_number) #This might be a reserved method name so maybe pick a different name for this method
       set_table_name "device_#{device_number}s"
  end
end

When you call this method and how you figure out what number to pass into the method is where the real work will be but as a starting point you could try this by just hard coding the parameter to the method and checking that it works as expected

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)