That's sort of backwards of how the postcards table works, but the idea is right.
The postcards table is really a database of "sent" cards. It's all the information to create a "postcard" but not a "postcard" itself.
The Links database is where all the postcards (images & keywords, etc) are stored, in the current version. That may change, depending on if it's easier to add a table, and join it, or just increase the size of the database record.
This is really expanding into a "database" as I've started to track/link all sorts of data on the sending/getting of cards. And now, I have to track the uploading as well -- before only the system admins were uploading.
To track the number of uploads, you'd look again at Links database, and check the "owner" field. If that gets pulled out, it will still be the same idea... but in order to have a functioning postcards.cgi service, the "postcards" database (table) is only tracking cards sent (and the statistics surrounding them). (Minimal changes to the functioning of links.)
To build something with these complex relations, you've got to define your objects, and what attributes are attached to those objects.
For example:
A "postcard" is not an image, it's a collection of data that includes an image -- such as to, from, names, emails, text, colors, etc.
An "image" can be incorporated into a postcard, but it can also be incorporated into the gallery. An image has to have an 'owner' - which is the uploader, or in the case of the admin upload, whomever they set it to. It has other information such as copyright, keywords, descriptions, perhaps the # of times it was sent or viewed, etc. The owner links it to the users table.
The user table has all the user information, but doesn't need any of the data that can be linked to via the Username field.
For speed, the user record should contain the users statistics, but those can be re-generated from the log (for deleted files) or from the current database for current standings. With careful programming, this 'rebuild' ability shouldn't really even be needed in disaster recovery. In essence, that means it's data-redundant, but it allows for a significant speed boost to show user stats, already calculated rather than calcuated on the fly each time.
right now I've got multiple uploads working. I'm starting to integrate it all into the LinkSQL system now.
As I do that, I'll probably find more data relations that need some sort of rules applied to them.
Right now, the data relations are by Username and LinkID.
A file is attached to a LinkID, which is in turn a "record" in the database, and that is linked to the User-data via the "owner" field.
This allows a Linkowner to find all their links, a link to find the link owner, and a link to find the image, and an image to find a link and thus the owner.
I need to build in some sort of 'expunge' system to get rid of aborted or cancelled uploads. This is somewhat trickier that it would seem, since if you want a well-rounded data system, the uploaded files should be able to be linked back to the uploading user, even if the user is disconnected before finishing. They should be prompted to "finsh adding" their files on the next log-in.
This means the temporary/upload filename has to point to the user in some way. This is important too, because the LinkID is not assigned until the record is inserted in to the Links database.
Even though this should happen quickly, on loaded systems, or with a interupt called, this can be a several seconds process.
upload -> validate file name -> validate file -> put info into hash -> enter hash into database -> rename file to ID -> move file to 'preview'.
You don't want to upload directly to your preview area, since you'd like to fairly safely be able to delete your 'temp' directory without affecting successfully entered files.
If the record is succesfully entered into the "validate" or "pending" database, and the file doesn't get moved (due to an error, crash, or whatever) you have a 'broken' record, but the data _should_ be available to correct it. That means storing the temporary filename in the "pending" record, until the record is approved.
Lot's of little things :)
As I pointed out -- file upload is about 4 or 5 lines of code. Right now I've got multiple file uploads working, but all it does is take the files from the local machine, and upload them to the 'temp' directory. All the data manipulation and SQL insertions have yet to be coded.
Fortunately, that should be straight forward ... the GT modules make it easy :) But it's a lot of code and processes to keep an eye on.
PUGDOGŪ PUGDOGŪ Enterprises, Inc.
FAQ: http://pugdog.com/FAQ
