Adding Tables/Queries - proper technique?

[Coder1]

I'm working on a plug-in that will require a new table, and associated queries.

I want my table/queries to participate fully in the vBulletin datastore/caching system.

What I'm trying to do is avoid doing $db->query in my plugins... my table should be cached, the information should be fetched and stored in the proper $vbulletin arrray, and so on.

Let me give a totally contrived, ridiculous example:

I add the user_foo table to the database, with two fields, userid and foo.

I have a query that gets the "foo" value for particular userid (same userid as in the user table, this would be a "primary key" relationship, though I would maintain that relationship in code... I don't like adding fields/keys, altering core tables).

I'd like to store the value of "foo" for each user in $vbulletin->userinfo['foo'] and have it be available in templates as $bbuserinfo[foo].

[Noppid]

Rather then adding a table, add the foo field to the the vbulletin user table and you're done.

[Peggy]

foo field?? makes me wanna run out for chinese food... ;)

[Coder1]

My example is just that, an example. Also, I think in general, data normalization not withstanding, that it's a bad idea to alter existing/core tables, Rather, creating a separate table related by a key field is, in my opinion, the proper way to go.

Even if you created a new field in an existing table, you would have to modify an existing query, and also modify how the results were processed in order to package the results into the proper $vBulletin array.

So I'm back to the original question: new table, new query, cached, with the results properly "dressed" in their $vbulletin clothes. Any examples?

[Noppid]

My example is just that, an example. Also, I think in general, data normalization not withstanding, that it's a bad idea to alter existing/core tables, Rather, creating a separate table related by a key field is, in my opinion, the proper way to go.

Even if you created a new field in an existing table, you would have to modify an existing query, and also modify how the results were processed in order to package the results into the proper $vBulletin array.

So I'm back to the original question: new table, new query, cached, with the results properly "dressed" in their $vbulletin clothes. Any examples?

You are incorrect. Carry on.

[Coder1]

About altering tables? Are about needing to alter the queries? I might be incorrect, but I'm willing to be corrected so, please, elaborate.

What I don't like about altering core tables is: what happens when a new version arrives, which might alter the table structure? Is the patch going to account for your new fields? How does your installer handle all the existing records? You have to not only alter the table, you have to provide a value for the new fields in all existing records. What if that value cannot be null? What if it's calculated? Isolating all of these changes into a new table seems, to me, to be the safer method. It also makes modifying/expanding/updating your plugin easier, as your data is isolated from the core data.

[Noppid]

None of the contigencies are unique to adding a table versus adding a field to an existing table. Either way, blank fields have to be dealt with and populated. That is a given.

But, when you use the user table, the fields, if already populated with the proper data, the programmers job on install of the mod, will show up in the user array and not affect vBulletin operation or upgrades so long as you make sure your field name is truely unique.

Some stuff requires a new table, but not if the intention is to get it in the vBulletin object in the userinfo. Of course you want to be judicious. If you are addding 30 variables, there may need to be a different approach.

There's more then one way to skin a cat. But I don't see the question bringing up anything so complex that this approach would break anything while providing the means to write the least code.

[Coder1]

I really regret using "user" for my question. My current project has nothing to do with userinfo or user data, so I apologize for that. I think it's throwing us off the track.

In fact, for this project I'll be able to just add a value to an existing array that is already serialized into a single value in an existing table.

But I'm keenly interested in the THEORY.

Imagine an elaborate plugin with loads of new data in a new table or two.

I think I understand how to get the data, and put it into the datastore. I'm NOT sure how the raw data is synchronized with the datastore, however.

I know how to retrieve data from the datastore, and de-serialize it, I think... I'm fuzzy on what role $specialtemplates plays in this...

That side of things is becoming clearer to me, how to get data out, into the datastore, and into a $vbulletin object/variable.

What about inserting new data? Does it go into the datastore, and then magically back into the raw table, somehow? Or do you do an insert or update query directly?

[Noppid]

OK, let me think about that. You are correct, the user array had me focused on that one aspect in particular. But I'm certain you can do what ya want, I never had the need however. Of course all the preiously discussed over head on the front end of the application of the data remains. You just want it cached and available anytime any where?

[Coder1]

Do I? I guess that's the core question, for me. What I think I need is a breakdown on when things happen in vbulletin.

WHEN does data get put into the datastore table? WHEN does the datastore data become cached? WHEN does data from the datastore cache become de-serialized into a variable or array?

HOW does NEW data, work its way back into the database? Just an insert query?

So in our hypothetical plugin, yeah, let's assume I want a particular variable available anywhere.

Would I

1) Do a $db select query at global_start? Then add the results to the datastore? How to get the data into the cache?

[Noppid]

I don't know yet. I know there is a way to do such things, I have never used it though.

Have you read this forum? http://www.vbulletin.org/forum/forumdisplay.php?f=110

[Coder1]

Yeah, slowly wading through. The signal-to-noise ratio makes .org a bit of a chore.