Coppermine Photo Gallery v1.5.x: Documentation and Manual

Table of Contents

Database schema

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.

Configuration file

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.

Database reference within coppermine code

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.

cpg15x_albums

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.

cpg15x_banned

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.
email 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.

cpg15x_bridge

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.

cpg15x_categories

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.

cpg15x_categorymap

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

cpg15x_comments

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).

cpg15x_config

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.

cpg15x_dict

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.

cpg15x_ecards

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.

cpg15x_exif

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.

cpg15x_favpics

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.

cpg15x_filetypes

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

cpg15x_hit_stats

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

cpg15x_languages

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

cpg15x_pictures

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.

cpg15x_plugins

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.

cpg15x_sessions

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.

cpg15x_temp_messages

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.

cpg15x_usergroups

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'].


Field Type Description References
group_id Integer Primary key. Uniquely identifies the group.
group_name Text (<=255 chars) The name of the group.
group_quota Integer Maximum amount of disk space (in bytes) that users in this group are permitted to upload to their user gallery.
can_rate_pictures Integer Indicates whether or not this group has permission to vote/rate files.
can_send_ecards Integer Indicates whether or not this group has permission to send ecards files.
can_post_comments Integer Indicates whether or not this group has permission to post comments.
can_upload_pictures Integer Indicates whether or not this group has permission to upload files to public albums.
can_create_albums Integer Indicates whether or not this group has permission to upload files to their personal user gallery.
pub_upl_need_approval Integer Indicates whether or not this group must await approval for any uploads to public albums.
priv_upl_need_approval Integer Indicates whether or not this group must await approval for any uploads to their personal user gallery.
access_level Integer Indicates what level of access the group has. 0 = no access, 1 = thumbnails only, 2 = thumbnails and intermedaite files, 3 = All

cpg15x_users

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.

cpg15x_votes

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.

cpg15x_vote_stats

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