The following is a list of the tables created by Coppermine during installation plus a description of the fields used. The default table prefix of cpg15x_ is used here, but this is user configurable.
Database details (mySQL hostname, database name, mySQL username, mySQL password, table prefix) are being stored in the file include/config.inc.php
When migrating to another server or if the database details should change (e.g. if the mySQL password has been changed), reflect those changes by editing the file include/config.inc.php, which does not come with the coppermine package, but is created during the install process of coppermine on the server.
To reflect the ability of coppermine to use a common table prefix for all tables created by coppermine, there is no direct reference to the tables in the Coppermine code. If you try to modify Coppermine's core code or if your try to come up with a plugin of your own, you need to understand how Coppermine refers to the table names internally. Here's a list that should explain how to refer to tables within the code: the code snippet has been taken from include/init.inc.php:
If you want to refer to the albums table code-wise, use $CONFIG['TABLE_ALBUMS'].
In the individual table information below, you will find the variable name for each table as an example.
Stores information about albums. Albums contain files and are (optionally) contained within categories.
If you want to refer to this table code-wise, use $CONFIG['TABLE_ALBUMS'].
| Field | Type | Description | References |
|---|---|---|---|
| aid | Integer | Primary key. This is the identifier of the album used throughout Coppermine. | cpg15x_pictures.aid |
| title | Text (<=255 chars) | The short title of the album. | |
| description | Text (<=65K chars) | The longer album description or caption. | |
| visibility | Integer |
This corresponds to the 'album can be viewed by' option. If the value is less than 10000 the number corresponds to the group that has permission to view the album. If the value is over 10000 then the number corresponds to 10000 plus the user_id of the user that has permission to view this album (the 'me only' option). The default value of 0 means there are no restrictions (with the exception of a possible album password); the album is publicly visible. |
cpg15x_usergroups.group_id cpg15x_users.user_id |
| uploads | YES / NO | Whether uploads are permitted to this album. Note: Group based permissions also apply. |
|
| comments | YES / NO | Whether comments are permitted on files in this album. Note: Group based permissions also apply. |
|
| votes | YES / NO | Whether voting/rating of files is permitted in this album. Note: Group based permissions also apply. Note: do not confuse this field with the votes field in the pictures table, which is a count of the number of votes on that picture. |
|
| pos | Integer | This is the position of the album within its parent category. Albums are sorted by position ascending then by aid. The default position is 0. This causes new albums to appear at the end of listings unless moved manually. | |
| category | Integer | The parent category of this album. A category of 0 indicates the album is in 'no category' and is displayed on the main page directly. If the value is more than 10000 then it means the album is a user album belonging to the user with id of category - 10000. | cpg15x_categories.cid |
| owner | Integer | cpg15x_users.user_id | |
| thumb | Integer | Indicates which picture is to be used as the thumbnail image for the album. If the value is 0 the last uploaded picture in that album is used. If the value is -1 a random picture from the album is selected. Otherwise, the value corresponds to the pid of the picture selected as thumbnail. If the album is empty a special icon is used to indicate this. | cpg15x_pictures.pid |
| keyword | Text (<=50 chars) | The album keyword. Used by the 'linked files' 'feature' to display files in multiple albums. Files that contain the album keyword as one of their picture keywords will be displayed in the album. Note: Files cannot truely be in multiple albums as a consequence of the original database design. |
|
| alb_password | MD5 hash | This is an MD5 hash of the album password. If this is blank then there is no album password. | |
| alb_password_hint | Text (<=65K chars) | An album hint which is displayed to users when they are asked to enter the album password. | |
| moderator_group | Integer | Specifies a group which has moderator permissions for this album. If set to 0 (the default) then no such group is specified. | cpg15x_usergroups.group_id |
| alb_hits | Integer | Counts the number of times this album has been viewed. This counter can be disabled in config. Note: This is not a sum of the individual file view counters. |
Contains bans placed on users, user names, email addresses, and IP addresses.
If you want to refer to this table code-wise, use $CONFIG['TABLE_BANNED'].
| Field | Type | Description | References |
|---|---|---|---|
| ban_id | Integer | Primary key. Unique identifier for a ban. | |
| user_id | Integer | The user that this ban applies to. Banned users are unable to use the gallery in any way. | cpg15x_users.user_id |
| user_name | Text (<=255 chars) | A username that this ban applies to. Registrations from a banned username are refused. | |
| Text (<=255 chars) | An email address which this ban applies to. Registrations from a banned email address are refused. | ||
| ip_addr | Text (<=255 chars) | An IP address or wildcard IP (eg. 192.168.0.%) that this ban applies to. Visitors with a matching IP address will be unable to use the gallery in any way. | |
| expiry | Datetime | The date/time that this ban is set to expire. | |
| brute_force | Integer | A counter used to track failed login attempts which may lead to an actual ban. Counts down from the config setting 'Number of failed login attempts until temporary ban' to 0. |
Contains bridge configuration settings. Used only by the bridge manager and only when bridging is enabled.
If you want to refer to this table code-wise, use $CONFIG['TABLE_BRIDGE'].
| Field | Type | Description | References |
|---|---|---|---|
| name | Text (<=40 chars) | Name of configuration setting. | |
| value | Text (<=255 chars) | Value of configuration setting. |
Contains information about categories. Categories contain albums and can be infinitely nested.
Coppermine uses both the adjacency list and nested set models to represent category heirarchy. For a description of these methods see dev.mysql.com. The ajacency method should be used for simple lookups (eg. how many subcategories does category 3 have?) and the the nested set model should be used when generating a complete or partial hierarchy (eg. for a dropdown menu) since it scales much better.
The category manager will regenerate the nested set representation from parent information if any category has lft set to 0. This is useful if you are importing categories from another system or have made changes manually.
If you want to refer to this table code-wise, use $CONFIG['TABLE_CATEGORIES'].
| Field | Type | Description | References |
|---|---|---|---|
| cid | Integer | Primary key. Unique identifier for a category. | |
| owner_id | Integer | cpg15x_users.user_id | |
| name | Text (<=255 chars) | Short name of the category. | |
| description | Text (<=65k chars) | A longer description of the category. | |
| pos | Integer | Position of the category. Categories are sorted by position ascending. The category manager assigns the position and all categories have a non-zero position. | |
| parent | Integer | The immediate parent category of this category. | cpg15x_categories.cid |
| thumb | Integer | The picture to be used as thumbnail for the category. If 0 then no thumbnail is displayed. | cpg15x_pictures.pid |
| lft | Integer | The lft and rgt fields are used to represent the category heirarchy using the nested set model. | |
| rgt | Integer | The lft and rgt fields are used to represent the category heirarchy using the nested set model. | |
| depth | Integer | Represents the depth of the category in the heirarchy. A depth of 1 indicates a top level category. |
Contains information on which groups have access to create albums in which categories.
If you want to refer to this table code-wise, use $CONFIG['TABLE_CATMAP'].
| Field | Type | Description | References |
|---|---|---|---|
| cid | Integer | The category this permission applies to. | cpg15x_categories.cid |
| group_id | Integer | The group this permission applies to. | cpg15x_usergroups.group_id |
Contains all comments made on files. This table can get quite large if you have lots of comments. It can safely be pruned to remove comments older than a certain date.
If you want to refer to this table code-wise, use $CONFIG['TABLE_COMMENTS'].
| Field | Type | Description | References |
|---|---|---|---|
| pid | Integer | The file that this comment has been made against. | cpg15x_pictures.pid |
| msg_id | Integer | Primary key. Unique identifier for a comment. | |
| msg_author | Text (<=25 chars) | The name of the user that posted the comment. This is needed mainly for anonymous commenting, however usernames of registered users are recorded here also (and must be kept up to date if a username changes). | |
| msg_body | Text (<=65k chars) | The text of the comment that was entered. | |
| msg_date | Datetime | The date and time the comment was posted. | |
| msg_raw_ip | Text (<=255 chars) | The IP of the comment poster. This IP is derived from the REMOTE_ADDR variable. If the webserver is behind a proxy this will probably be the IP of the proxy. | |
| msg_hdr_ip | Text (<=255 chars) | The IP of the comment poster. This IP is derived from the HTTP_CLIENT_IP or HTTP_X_FORWARDED_FOR variables. If the webserver is behind a proxy this should be the actual client IP instead of the proxy's IP. If neither variable is present this will be the same as the msg_raw_ip value. | |
| author_md5_id | MD5 hash | A randomly generated unique id used to 'identify' anonymous users and allow them to edit/delete their own comments (but not those of other anonymous users). It also allows enforcement of flood protection rules against individual anonymous users. This field is empty for comments by registered users. | |
| author_id | Integer | The user id of the comment poster, or 0 for anonymous comments. | cpg15x_users.user_id |
| approval | YES / NO | Whether the comment has been approved. If comment approval is not enabled, all comments are added with approval already set to YES. Comments with approval set to NO are not displayed to normal users. | |
| spam | YES / NO | Whether the comment has been determined to be a spam comment by akismet (if enabled). |
Contains main configuration settings.
Plugins can also add settings here. If so, they should prefix their settings with the short name of the plugin and clean up when uninstalled.
If you want to refer to this table code-wise, use $CONFIG['TABLE_CONFIG'].
| Field | Type | Description | References |
|---|---|---|---|
| name | Text (<=40 chars) | Name of configuration setting. | |
| value | Text (<=255 chars) | Value of configuration setting. |
A list of all keywords that have been applied to files. Using this is more efficient that loading the keywords from the pictures table every time it's needed. It can be regenerated using the keyword_create_dict.php script.
If you want to refer to this table code-wise, use $CONFIG['TABLE_DICT'].
| Field | Type | Description | References |
|---|---|---|---|
| keyId | Integer | Primary key. Used to manage the keyword dictionary. | |
| keyword | Text (<=60 chars) | Unique field. The keyword itself. |
If logging of sent ecards has been enabled, this table will contain that log. Can be used to see if the feature is being abused (or if it is being used at all).
If you want to refer to this table code-wise, use $CONFIG['TABLE_ECARDS'].
| Field | Type | Description | References |
|---|---|---|---|
| eid | Integer | Primary key. Used purely to manage the ecard log. | |
| sender_name | Text (<=50 chars) | The name specified as sender name on the sent ecard. | |
| sender_email | Text (<=65k chars) | The email address specified as sender email on the sent ecard. | |
| recipient_name | Text (<=50 chars) | The name specified as recipient name on the sent ecard. | |
| recipient_email | Text (<=65k chars) | The email address specified as recipient email on the sent ecard. | |
| link | Text (<=65k chars) | The link of the ecard. The link actually contains all details of the ecard as a base64 encoded serialized array. This is not ideal, but means ecards do not take up space in the database (unless logging is enabled) and as a consequence never expire. | |
| date | Unix timestamp | The date/time the ecard was sent. | |
| sender_ip | Text (<=255 chars) | The IP address of the ecard sender. This IP is derived from the REMOTE_ADDR variable. If the webserver is behind a proxy this will probably be the IP of the proxy. |
EXIF data for pictures. This table is used as a cache to avoid having to read the EXIF data from the file every time it is viewed. Can safely be emptied - the data will be reloaded as needed.
If you want to refer to this table code-wise, use $CONFIG['TABLE_EXIF'].
| Field | Type | Description | References |
|---|---|---|---|
| pid | Integer | Primary key. The file that this EXIF data is from. | cpg15x_pictures.pid |
| exifData | Text (<=65k chars) | The EXIF data. |
Stores the user's favorite files list.
If you want to refer to this table code-wise, use $CONFIG['TABLE_FAVPICS'].
| Field | Type | Description | References |
|---|---|---|---|
| user_id | Integer | Primary key. The user whose favorites these are. | cpg15x_users.user_id |
| user_favpics | Text (<=65k chars) | The favorites data - stored as a serialized array of pids. |
List of filetypes known to Coppermine. This tells Coppermine how to handle uploaded files based on the filename extension. Any files uploaded that are not of a known type will be rejected.
If you want to refer to this table code-wise, use $CONFIG['TABLE_FILETYPES'].
| Field | Type | Description | References |
|---|---|---|---|
| extension | Text (<=7 chars) | Primary key. The filename extension in question (lower case, without a leading dot) | |
| mime | Text (<=254 chars) | The mime type associated with this filename extension. For list of possible mime types see iana.org or /etc/mime.types on linux systems. | |
| content | audio/document/image/movie | States how Coppermine should display the file. Images are displayed using normal <img> tags, audio and video files using <object> and <embed> tags and documents are simply linked to. Only filetypes that are defined as images get automatic thumbails and resized versions created. | |
| player | DIVX/QT/RMP/SWF/WMP | States which media player should be used to play files of this type. Applies only to movie and audio files. The visitor must have an appropriate plugin to be able to view the media file.
DIVX = DivX Player, QT = QuickTime, RMP = RealPlayer, SWF = Shockwave Flash Player, WMP = Windows Media Player |
If the detailed hit stats feature is enabled, this table will contain that data. Can safely be emptied or pruned if required.
If you want to refer to this table code-wise, use $CONFIG['TABLE_HIT_STATS'].
| Field | Type | Description | References |
|---|---|---|---|
| sid | Integer | Primary key. Used to identify this hit in the interface. | |
| pid | Integer | The file that this hit was on. | cpg15x_pictures.pid |
| ip | Text (<=20 chars) | The IP address of the visitor. This IP is derived from the REMOTE_ADDR variable. If the webserver is behind a proxy this will probably be the IP of the proxy. | |
| search_phrase | Text (<=255 chars) | If the visitor arrived from a search engine, the search query they entered will be stored here. | |
| sdate | Unix timestamp | The date/time of the hit. | |
| referer | Text (<=65k chars) | If the visitor arrived from another page, the URL of that page (if available) will be stored here. | |
| browser | Text (<=255 chars) | The web browser the visitor used (if reported by the browser, and if recognised as a known browser). | |
| os | Text (<=50 chars) | The operating system the visitor used (if reported by the browser, and if recognised as a known operating system). | |
| uid | Integer | The id of the user if logged in, 0 for anonymous visitors. | cpg15x_users.user_id |
Contains the language file definitions.
If you want to refer to this table code-wise, use $CONFIG['TABLE_LANGUAGE'].
| Field | Type | Description | References |
|---|---|---|---|
| lang_id | Text (<=40 chars) | Primary key. | |
| english_name | Text (<=70 chars) | ||
| native_name | Text (<=70 chars) | ||
| custom_name | Text (<=70 chars) | ||
| flag | Text (<=15 chars) | ||
| abbr | Text (<=15 chars) | ||
| flag | Text (<=15 chars) | ||
| available | YES / NO | ||
| enabled | YES / NO | ||
| complete | YES / NO |
Contains all files that have been added to Coppermine. (Not just pictures).
If you want to refer to this table code-wise, use $CONFIG['TABLE_PICTURES'].
| Field | Type | Description | References |
|---|---|---|---|
| pid | Integer | Primary key. Used throughout Coppermine to identify this file. | |
| aid | Integer | Indicates which album this picture belongs to. | cpg15x_albums.aid |
| filepath | Text (<=255 chars) | The path on the filesystem to the directory containing the file. This is relative to Coppermine's albums directory. | |
| filename | Text (<=255 chars) | The name of the file on the filesystem. There should be no path components in this field. | |
| filesize | Integer | The size of the file in bytes. | |
| total_filesize | Integer | The combined size of the file plus any resized or thumbnail versions of the file in bytes. This is used in quota calculations. | |
| pwidth | Integer | The width of the picture in pixels. If the file is a movie this is the width it will be displayed at. | |
| pheight | Integer | The height of the picture in pixels. If the file is a movie this is the height it will be displayed at. | |
| hits | Integer | The number of times the file has been viewed. | |
| mtime | Datetime | The date/time of the most recent view of this file. Used to create the last viewed meta album. | |
| ctime | Unix timestamp | The date/time the file was added to Coppermine. Note: Coppermine does not use this field when sorting files by date. (It uses the pid field instead). |
|
| owner_id | Integer | The user who uploaded this file. 0 for anonymous users. | cpg15x_users.user_id |
| pic_rating | Integer | The value represents the current average rating of the file. This allows Coppermine to track the average rating without having to store all ratings ever made. Divide this number by 2000 to get a rating out of 5. See ratepic.php for how the value is calculated. | |
| votes | Integer | The total number of votes/ratings made on the file. | |
| title | Text (<=255 chars) | The short title of the file. | |
| caption | Text (<=65k chars) | The longer description of the file. | |
| keywords | Text (<=255 chars) | Used to tag the file with a given word or phrase. Used in searching, 'clickable keywords', and for the 'linked files' feature. The keyword separator is a configuration setting. | |
| approved | YES / NO | Whether the file has been approved by the administrator. Files that are not approved are not displayed to regular users. If approval is not enabled then the file gets a value of YES automatically. | |
| galleryicon | Integer | Indicates that this file has been selected as the thumbnail for a user gallery. | cpg15x_pictures.pid |
| user{1,2,3,4} | Text (<=255 chars) | Custom fields for image description. | |
| url_prefix | Integer | Designed as a way to allow some files to be stored in an alternate location to the albums directory, but this feature is not used by Coppermine itself. See the function get_pic_url() in include/functions.inc.php. Should normally be set to 0. | |
| pic_raw_ip | Text (<=255 chars) | The IP of the uploader. This IP is derived from the REMOTE_ADDR variable. If the webserver is behind a proxy this will probably be the IP of the proxy. | |
| pic_hdr_ip | Text (<=255 chars) | The IP of the uploader. This IP is derived from the HTTP_CLIENT_IP or HTTP_X_FORWARDED_FOR variables. If the webserver is behind a proxy this should be the actual client IP instead of the proxy's IP. If neither variable is present this will be the same as the pic_raw_ip value. | |
| lasthit_ip | Text (<=255 chars) | The IP address of the last visitor to view the file. This IP is derived from the REMOTE_ADDR variable. If the webserver is behind a proxy this will probably be the IP of the proxy. | |
| position | Integer | The position of the file within the album. This is only taken into account if the default sort order is set to position ascending or descending in config. The positions are set using the 'sort my pictures' page. |
List the currently installed plugins.
| Field | Type | Description | References |
|---|---|---|---|
| plugin_id | Integer | Primary key. Simply used to manage plugins. | |
| name | Text (<=64 chars) | The plugin name as defined by the plugin creator. | |
| path | Text (<=128 chars) | The name of the plugin's folder within the plugins directory. | |
| priority | Integer | Plugins are loaded and applied in a user specified order (by priority ascending). This can help reduce problems with plugins that don't play nice with other plugins. |
Tracks sessions for logged in and anonymous users. This is not used if Coppermine is bridged. As such, all relevant code can be found in bridge/coppermine.inc.php.
| Field | Type | Description | References |
|---|---|---|---|
| session_id | MD5 hash | Primary key. Session identifier. This is generated the first time a visitor loads a page and is retained until it expires. The user_id associated with the session is updated if the user logs in and out. | |
| user_id | Integer | The user whose session this is. 0 for anonymous sessions. | cpg15x_users.user_id |
| time | Unix timestamp | The time of the last action by the visitor. This is used to clean out expired sessions periodically. | |
| remember | Integer | Whether the user selected 'remember me' at login. This extends the session timeout from the normal 1 hour to 2 weeks. |
Used to store messages from one page to the other.
If you want to refer to this table code-wise, use $CONFIG['TABLE_TEMP_MESSAGES'].
| Field | Type | Description | References |
|---|---|---|---|
| message_id | Text (<=80 chars) | Primary key. Uniquely identifies the messages. | |
| user_id | Integer | The user the message is aimed at. | cpg15x_users.user_id |
| time | Unix timestamp | The time of the message. This is used to clean out expired messages periodically. | |
| message | Text (<=65k chars) | The text of the message. |
Contains the user groups. If bridged the table will contain imported groups from the forum. The data for the default groups are available in sql/basic.sql
If you want to refer to this table code-wise, use $CONFIG['TABLE_USERGROUPS'].
Contains all registered users. If bridged this table is not used, however the original admin account and any accounts created while unbridged will remain.
If you want to refer to this table code-wise, use $CONFIG['TABLE_USERS'].
| Field | Type | Description | References |
|---|---|---|---|
| user_id | Integer | Primary key. Uniquely identifies the user. | |
| user_group | Integer | The primary group of the user. | cpg15x_usergroups.group_id |
| user_active | YES / NO | Indicates whether the user has completed any necessary activation/approval steps after registration. | |
| user_name | Text (<=25 chars) | Unique key. The name of the user. | |
| user_password | MD5 hash | A hash of the user's password. The password cannot be determined from the hash, so the 'forgot password' feature generates a new password. | |
| user_lastvisit | Datetime | The date/time of the last login by this user. This is only updated when the user logs in. If the user has never logged in this will be 0000-00-00 00:00:00 | |
| user_regdate | Datetime | The date/time the user registered their account. | |
| user_group_list | Text (<=255 chars) | Any additional groups the user is a member of are listed here, separated with commas. | cpg15x_usergroups.group_id |
| user_email | Text (<=255 chars) | The current email address of the user. | |
| user_profile1 | Text (<=255 chars) | Custom profile field. By default this is 'Location'. | |
| user_profile2 | Text (<=255 chars) | Custom profile field. By default this is 'Interests'. | |
| user_profile3 | Text (<=255 chars) | Custom profile field. By default this is 'Website'. | |
| user_profile4 | Text (<=255 chars) | Custom profile field. By default this is 'Occupation'. | |
| user_profile5 | Text (<=255 chars) | Custom profile field. By default this is unused. | |
| user_profile6 | Text (<=65k chars) | Custom profile field. By default this is 'Biography'. This field is longer than the others. | |
| user_actkey | MD5 hash | Randomly generated key used in account activation. | |
| user_language | Text (<=40 chars) | The user's language preference. Only half implemented. |
Tracks user votes to prevent repeat ratings. The table is pruned automatically.
If you want to refer to this table code-wise, use $CONFIG['TABLE_VOTES'].
| Field | Type | Description | References |
|---|---|---|---|
| pic_id | Integer | Composite primary key. The pid of the file the vote related to/ | cpg15x_pictures.pid |
| user_md5_id | MD5 hash | Composite primary key. A randomly generated unique id used to 'identify' anonymous users. For registered users this is the hash of the user_id. | |
| vote_time | Unix timestamp | The date/time of the vote. Used when cleaning the table of old votes. |
If the detailed vote stats feature is enabled, this table will contain that data. Can safely be emptied or pruned if required.
If you want to refer to this table code-wise, use $CONFIG['TABLE_VOTE_STATS'].
| Field | Type | Description | References |
|---|---|---|---|
| sid | Integer | Primary key. Used to identify this vote in the interface. | |
| pid | Integer | The file that this vote was on. | cpg15x_pictures.pid |
| rating | Integer | The rating given. | |
| ip | Text (<=20 chars) | The IP address of the voter. This IP is derived from the REMOTE_ADDR variable. If the webserver is behind a proxy this will probably be the IP of the proxy. | |
| sdate | Unix timestamp | The date/time of the vote. | |
| referer | Text (<=65k chars) | If the voter arrived from another page, the URL of that page (if available) will be stored here. | |
| browser | Text (<=255 chars) | The web browser the voter used (if reported by the browser, and if recognised as a known browser). | |
| os | Text (<=50 chars) | The operating system the voter used (if reported by the browser, and if recognised as a known operating system). | |
| uid | Integer | The id of the user if logged in, 0 for anonymous visitors. | cpg15x_users.user_id |