User:RichMorin/mw image

Uploaded images and other files.

Inter-table Relationships

• img_user - user ID ( user.user_id)

• img_user_text - user name ( user.user_name)

MySQL Table Description

mysql> desc mw_image;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| img_name        | varchar(255)     |      | PRI |         |       |
| img_size        | int(8) unsigned  |      | MUL | 0       |       |
| img_width       | int(5)           |      |     | 0       |       |
| img_height      | int(5)           |      |     | 0       |       |
| img_metadata    | mediumblob       |      |     |         |       |
| img_bits        | int(3)           |      |     | 0       |       |
| img_media_type  | enum(See Below)  | YES  |     | NULL    |       |
| img_major_mime  | enum(See Below)  |      |     | unknown |       |
| img_minor_mime  | varchar(32)      |      |     | unknown |       |
| img_description | tinyblob         |      |     |         |       |
| img_user        | int(5) unsigned  |      |     | 0       |       |
| img_user_text   | varchar(255)     |      |     |         |       |
| img_timestamp   | varchar(14)      |      | MUL |         |       |
+-----------------+------------------+------+-----+---------+-------+
13 rows in set

img_media_type:  'UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO',
                 'MULTIMEDIA', 'OFFICE','TEXT','EXECUTABLE','ARCHIVE'

img_major_mime:  'unknown', 'application', 'audio', 'image', 'text',
                 'video', 'message', 'model', 'multipart'

Annotated Table Creation Code

-- Uploaded images and other files.

CREATE TABLE /*$wgDBprefix*/image (

  -- Filename.
  -- This is also the title of the associated description page,
  -- which will be in namespace 6 (NS_IMAGE).

  img_name            varchar(255)   binary       NOT NULL  default '',
  
  -- File size, in bytes.

  img_size            int(8)         unsigned     NOT NULL  default '0',
  
  -- For images, size in pixels.

  img_width           int(5)                      NOT NULL  default '0',
  img_height          int(5)                      NOT NULL  default '0',
  
  -- Extracted EXIF metadata, stored as a serialized PHP array.

  img_metadata        mediumblob                  NOT NULL,
  
  -- For images, bits per pixel if known.

  img_bits            int(3)                      NOT NULL  default '0',
  
  -- Media type, as defined by the MEDIATYPE_xxx constants

  img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO",
                      "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE",
                      "ARCHIVE") default NULL,
  
  -- major part of a MIME media type, as defined by IANA
  -- see http://www.iana.org/assignments/media-types/

  img_major_mime ENUM("unknown", "application", "audio", "image", "text",
                      "video", "message", "model", "multipart") NOT NULL
                      default "unknown",
  
  -- minor part of a MIME media type, as defined by IANA
  -- The minor parts are not required to adhere to any standard,
  -- but should be consistent throughout the database.
  -- see http://www.iana.org/assignments/media-types/

  img_minor_mime      varchar(32)                 NOT NULL  default "unknown",
  
  -- Description field, as entered by the uploader.
  -- This is displayed in the image upload history and logs.

  img_description     tinyblob                    NOT NULL  default '',
  
  -- user_id and user_name of uploader.

  img_user            int(5)         unsigned     NOT NULL  default '0',
  img_user_text       varchar(255)   binary       NOT NULL  default '',
  
  -- Time of the upload.

  img_timestamp       char(14)       binary       NOT NULL  default '',
  
  PRIMARY KEY         img_name (img_name),
  
  -- Used by Special:Imagelist for sort-by-size

  INDEX               img_size (img_size),
  
  -- Used by Special:Newimages and Special:Imagelist

  INDEX               img_timestamp (img_timestamp)

) ENGINE=InnoDB;