SQL and non-refreshing views?

[Rasori]

I'm just kind of playing around with programming, I've gotten a relatively solid grasp on Java and eventually decided what I actually wanted to do with it. Then I realized that, for my program I need a database.

 

SQL has been the best option so far, but I need to find out if I can make a view that doesn't refresh when its referenced tables are updated. Alternatively, if I could make a table that is based off of the values of another table (only at the time of creation), that would also be acceptable.

 

The basic concept is to create a view that allows a user to see selected pieces of a table they don't normally have access to. Preferably, I'd like a specific procedure to be the only way to refresh the view's values, though it's acceptable if I'd have to outright drop the view and create a new one every time I want to update it.

 

I can easily see why this may not have been implemented in SQL, but nonetheless I was hoping there was some way I could do it. If you've got any ideas, please let me know!

 

(I'm using MySQL and InnoDB but all I've gotten so far is a rough outline so it's still early enough for me to change (foreign keys are a must for the database, as a note, and preferably I'd like something free/open-source)).

[bascule]

SQL has been the best option so far, but I need to find out if I can make a view that doesn't refresh when its referenced tables are updated. Alternatively, if I could make a table that is based off of the values of another table (only at the time of creation), that would also be acceptable.

 

Try using a temporary table.

[Rasori]

Hmm...

 

A useful tool but I don't think it's going to work for what I'm looking at (it is, however, entirely possible that what I'm looking for isn't going to have a nice solution). I'm thinking more of something that can be initiated, say, on Saturday, and then will have the same values through to Friday night, where it will be updated to new values. [Edit: this would be through potentially multiple iterations of a client logging in and out, hence why the temporary table doesn't seem to fit my needs]

 

In honesty, because of the scale of what I'm looking at (read: very small), it may be better to just manually make the tables I need each week.

Edited December 5, 2009 by Rasori

[doG]

Cron a script that runs once a week to drop your temporary table and create a new one with the new data. A stored view, by it's very nature, is always going to show current data.

[Rasori]

The temporary table won't be deleted on the client logging out? That was the understanding the link gave me. If not, sweet and thank you, but if it does I may need to fall back on manual labor (or some jury-rigged scripting, which is really what programming is about to me anyway ).

 

It does certainly seem that a view is not going to work. I'll do some more research on temporary tables.

[bascule]

The temporary table won't be deleted on the client logging out?

 

The temporary table will be destroyed the instant you (or your framework) disconnect from MySQL

[doG]

A useful tool but I don't think it's going to work for what I'm looking at (it is, however, entirely possible that what I'm looking for isn't going to have a nice solution). I'm thinking more of something that can be initiated, say, on Saturday, and then will have the same values through to Friday night, where it will be updated to new values. [Edit: this would be through potentially multiple iterations of a client logging in and out, hence why the temporary table doesn't seem to fit my needs]

 

The temporary table won't be deleted on the client logging out?

 

Yes a temporary table created with the CREATE TEMPORARY TABLE statement will expire on logout but you can create a dummy table that you intend to use temporarily with a SELECT INTO TABLE... statement that will persist until you drop it instead of creating a TEMPORARY table that only lasts for a user's session; just don't name the table TEMPORARY. Put the script in a stored procedure and execute it weekly with the event scheduler.

Edited December 6, 2009 by doG

[Rasori]

Aha. That, my friends, takes the cake. Simple yet perfect (and blatantly missed by me). Thank you very much!