Dinamicaly Generate Model Without Gii Tool

Yii 1.1 General Discussion
1

I am trying to create a social network application with Yii and I stumbled upon a really bad issue :D

First thing first: I have a system DB table that contains all members of the application named ‘sys_accounts’ and for each user I create a friends table that’s named: ‘13_connections’ where 13 is the user id.

My problem is this: I want to build relations between tables so I can easily access information from sys_accounts using foreign keys from 13_connections, but given the fact that 13_connections is dynamically generated, I do not have a model for this DB table. I’m thinking that if the application has a big bunch of users, the join will slow it down quite allot.

So thinking that I have the tables:

1_connections

2_connections

3_connections

neither with models and sys_accounts with model file I would like to build relations for every USERID_connections - sys_account table.

How can I generate a model file for the table 13_connections without using Gii tool… How can I do it using php code or Yii classes. Does Yii offer support for this? And if it does, how does the code look like?

2

If your application needs to create tables dynamically, you’re doing it wrong :) According to database normalization, this sounds like a many-to-many relation, which looks like this:

sys_accounts table:




user_id | username

------------------

1       | Ares

2       | Someone

connections table:




user_id | friend_id

-------------------

1       | 2

Ares is friends with Someone.

Relation:


'connections' => array(self::MANY_MANY, 'SysAccount', 'connections(user_id, friend_id)'),
3

Think about it this way… I want to have 30+ tables associated with a user, because I can easily create statistics for example, and i will have 1 or several rows for each user, insted of having millions of rows in one table.

Simple math: 100.000 user, each having 1.000 friends it adds to 100.000.000 rows in the connections table opposite to 1.000 rows in userid_connections table. This is way I choosed to go with the dynamic creation… And in this case it’s a 1 to many relation… so it really improves the application speed…

Should i guess from your answer that this is not possible?

4

Well, it is possible but not advisable. Better follow the way Tsunami proposed. Keeping tables opened is not for free resourcewise. And creating and/or modifying tables on the fly is actually a sign of poor application design.

5

Databases are made to have millions of rows, with the right indexes it’s not slow. Database normalization has been around for 40 years, and it’s still taught in schools, so I suggest you look into it ;)

6

I do have a cluster of servers to sustain my application, so i really don’t care if the application is big.

Second of all… I am taking every know method of protection against SQL injections and so on… I’m really not afraid of attacks either…

If it IS possible, how?

7

Security by paranoia is not a sane approach.

Your previous statement is suggesting something different.

I am still in heavy opposition to this. But since you insist …

You can create and populate your connection tables by implementing the necessary logic inside your model’s afterSave() method. The table name is returned by the tableName() method. But since you cannot safely predict the table name from within a connection table’s AR model, this won’t buy you anything. In fact, you’ll have to bypass the rules() method entirely. There are two ways now to get the content of those tables loaded. One would be to overwrite the afterFind() method. However, this way you will lose the “loaded when needed” aspect. If you wish to maintain that, you will need to implement setConnections() and getConnections() methods and turn User.connections into a virtual attribute.

Coming to think of it, you could perhaps implement your own relation type. That would be a bit harder to do, but I think it would lead to an almost clean solution.

Well, that’s so much I can tell you for know. May [font=“Courier New”]{$diety_of_your_choice}[/font] be with you on the rocky road ahead.