1

I need a table to log certain actions users make in WordPress.

As of now, this is the database schema I have camp up with:

id bigint(20) NOT NULL AUTO_INCREMENT,
uid bigint(20) NOT NULL,
type VARCHAR(256) NOT NULL,
data1 TEXT NOT NULL,
data2 TEXT NOT NULL,
data3 TEXT NOT NULL,
timestamp bigint(20) NOT NULL,
UNIQUE KEY id (id)

Let me clarify:

uid: User ID of the wordpress user
type: Type of action the user made (can be 'comment', 'new_post', 'login', etc)
data1/2/3: additional data (for example, ID of comment or post made)

To display the logs, I would query the database and run through a certain filter to get the text to display for that particular log. So it works something like this:

if( $type == 'comment') {
    $comment = get_comment( $data1 );
    $user = get_user($uid);
    echo "User {$user->name} has made a <a href='{$comment->permalink}'>comment</a>";
}

Is this the most efficient way of doing things? It seems quite fine to me as I do not want to just store HTML in the logs table to be outputted.

However, the problem comes where I want to hide a particular log entry when certain conditions are met. Like, for example, if a comment no longer exists, I want to hide that entry. This would pose some problems with pagination. Any suggestions on how I can overcome this?

Thanks!

EDIT:

myplugin_transactions
id bigint(20) NOT NULL AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
type VARCHAR(256) NOT NULL,
timestamp bigint(20) NOT NULL,
UNIQUE KEY id (id)

myplugin_meta
id bigint(20) NOT NULL AUTO_INCREMENT,
txn_id bigint(20) NOT NULL,
key VARCHAR(256) NOT NULL,
data TEXT NOT NULL,
UNIQUE KEY id (id)

Lets say I want to select * from myplugin_transactions where data1 would usually have had been 'x' and data2 been 'y'. How should I do it in this case?

SELECT * FROM myplugin_transactions LEFT JOIN myplugin_meta ON myplugin_transactions.id = myplugin_meta.txn_id WHERE ( ... ? )
Christian
  • 19,605
  • 3
  • 54
  • 70
mushroom
  • 1,909
  • 3
  • 16
  • 33
  • 1
    Your data types could be better. Do you really need `BIGINT` for `user_id`? Do you expect to have more than 4 billion users? The `timestamp` column should probably be the `TIMESTAMP` type. Try to avoid the `TEXT` type; it will force any sorting to be done on disk. Unless you really need more than 255 characters, use `VARCHAR(255)`. The `type` column on the main table should probably be `VARCHAR(255)` since making it 256 adds an extra byte. These changes can significantly reduce the size of the data and indexes. – G-Nugget Dec 05 '12 at 18:55
  • @G-Nugget Thanks for your input about the difference in performance between VARCHAR(255) as opposed to VARCHAR(256). Would there be any performance improvements if I use the TIMESTAMP type? Also I chose BIGINT instead of INT as it is consistant with the WordPress database schema (http://codex.wordpress.org/Database_Description/3.3#Table:_wp_users). They probably used BIGINT for scalability. I'm a plugin developer and this is written for the general public in mind. Should I then use INT or stick with BIGINT? – mushroom Dec 05 '12 at 18:59
  • I guess it's fine to use `BIGINT` if you're being consistent. What will the timestamp value actually be? Will it be a unix timestamp or a formatted date? Also, will it always be when the row is inserted to the table or will it be some other time? You should also make you `INT` type fields `UNSIGNED`; using `BIGINT`, you probably won't notice any difference, but it's the right thing to do. – G-Nugget Dec 05 '12 at 19:04
  • @G-Nugget It will be a unix timestamp. I sometimes want to get entries that are say, one hour ago and would SELECT entries with timestamp greater than . Also, I read this (http://stackoverflow.com/a/409305/1365792). In my case, shouldn't datetime be more suitable than timestamp? I know nothing about the implications of making my INT SIGNED or UNSIGNED. – mushroom Dec 05 '12 at 19:09
  • 1
    Making an `INT` type `UNSIGNED` makes it so it can only have positive values, effectively doubling the max value. `TIMESTAMP` would probably be the best type. If you really need to manipulate the time with timezones, you can convert it. `TIMESTAMP` is smaller and basically is a unix timestamp to the server. `TIMESTAMP` also has the benefit of being able to use the current time as the default value, allowing you to simplify the queries by omitting the timestamp and having the server use the current time. – G-Nugget Dec 05 '12 at 19:16
  • Join my chat at http://chat.stackoverflow.com/rooms/20654/the-g-spot if you have any more questions. – G-Nugget Dec 05 '12 at 19:29

1 Answers1

1

This answer is going to be very generic as it doesn't provide any code, but it's also too long for a comment.

Firstly, you shouldn't be storing additional data in those data1, data2, data3 fields. You're using MySQL, so you've got the power of relational databases. Use them.

You should simply have another table, which has an ID field (the ID of the action), and a data field. That way you can store 0 to as-many-items-as-you-want pieces of metadata. I mean, wordpress already does this with metadata right?

Secondly, if a comment is deleted, do you simply want to delete the action related to it? If so, simply hook into the API. I believe there is a hook for delete_comment: http://codex.wordpress.org/Plugin_API/Action_Reference#Comment.2C_Ping.2C_and_Trackback_Actions

Otherwise if you want to keep the action, you can either add an extra field or piece of metadata called, say, deleted. When a comment is deleted, as above: hook into the delete_comment call and update the action to deleted = true. Then when you run your query on all the actions, exclude the deleted statements, eg ... WHERE deleted = NULL ... etc.

EDIT2:

To answer your select statement, something like this could work:

SELECT * FROM myplugin_transactions 
LEFT JOIN myplugin_meta AS data1 
ON ( myplugin_transactions.id = data1.txn_id AND data1.key = 'data1' )
LEFT JOIN myplugin_meta AS data2 
ON ( myplugin_transactions.id = data2.txn_id AND data2.key = 'data2' )
WHERE data1.data = 'x'
AND data2.data = 'y'

Obviously replacing the data1 and data2 keywords with meaningful descriptions.

Christian
  • 19,605
  • 3
  • 54
  • 70
  • Thanks for your thoughts about using a secondary table for indefinately many addtional datas. As for the second part, I do not want to delete the log entry but rather just hide it from the user's view when certain conditions are met, but they mess with pagination as I may end up with 9 entries on the page of 10 if one gets filtered out. – mushroom Dec 05 '12 at 18:26
  • Thanks, that is a very good suggestion. I have edited my question as to how to query for something based on the suggested schema. – mushroom Dec 05 '12 at 18:41
  • What if I want to get ALL metas into the query? Do I have to do a lot of subquery? Or lets say to display the logs table, do I need to do a foreach loop for the main table, and depending on the type, query the meta table separately? – mushroom Dec 05 '12 at 19:02
  • You only need to join the tables if you want to filter by a meta key using SQL. If you want all the meta data, do a separate query for all the metadata (`SELECT * FROM myplugin_meta WHERE txn_id = {$id}`). Right now I think you're combining a lot of questions at once, you might want to take a look at existing plugins to see how they work, try and build yours, then come back with specific questions. – Christian Dec 05 '12 at 19:11
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/20652/discussion-between-jon-and-christian-varga) – mushroom Dec 05 '12 at 19:15