space allocation for database

[joebudden]

hi guys,
       
I've written an application that allows users to login, and upload files to a mysql database.

Is there any way of having it so that each user is allocated 20meg of space on the database ???

Any help appreciated

[roopurt18]

Record the size of the files uploaded by users and prevent them from uploading when they've reached their quota?

[printf]

You can add a column to the user table and track the amount of data that has already been uploaded and base your upload limits on that value!

If you already have data in the table you can get the size of that table or group of rows, by running TABLE STATUS and adding up the rows for each user, but remember file size is different than database space used, so you will have to figure how you will enforce your upload limit, based on real file size or database storage size.

One other note, it would be better to store the files on the file system and just use the database to store the path to the file and the rest of that files information (IE: size, type, date_upload, times_downloaded, ...)


printf

[joebudden]

any tips on how this can be achieved ???

here is my sql

[code]
DROP TABLE IF EXISTS artefact; 
CREATE TABLE artefact
(
  artefactId        INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  dbEmployeeId          VARCHAR(10) NOT NULL,    -- such as 'e1' (foreign key of the owner of this artefact)
  filename    VARCHAR(255) NOT NULL,  -- such as 'asterisk.wav'. This could be the name of the file in the file store.
  type    VARCHAR(50),            -- MIME type such as audio/wav
  size   INTEGER,                -- in bytes such as 44136
  description VARCHAR(4000),          -- free text comments, use how you wish
  content  MEDIUMBLOB,            -- the actual media artefact (if the file is held in the filestore, this will be empty)
  UNIQUE employee_file_name (dbEmployeeId, filename), -- owner cannot have two files with the same name
  INDEX file_name_index (dbEmployeeId, filename),    -- speeds up retrieval of an owners files by filename
  INDEX file_type_index (dbEmployeeId, type),    -- speeds up retrieval of an owners files by filetype
  CONSTRAINT artefact_employee_FK
  FOREIGN KEY (dbEmployeeId)
    REFERENCES employee(dbEmployeeId) -- an artefact must reference the employee that 'owns' it
) ;
[/code]

[trq]

[quote]any tips on how this can be achieved[/quote]

Yeah, read the post above your last one.