Database Details
This article will provide you with information on how and why the Kitto database works the way it does. This database is (almost) completely in the third-normalized form, so it is optimized for write operations. The only non-normalized area is in the private message table.
I will be explaining tables in 'clusters'. A 'cluster' is a group of related tables on the ER (entity relationship) drawing to the right. These tables will be close together (hence 'cluster'). Each table in a cluster will receive a bullet-point with a short description of its purpose. If there are any noteworthy columns in that table (columns whose purpose is non-obvious or otherwise require explanation), they will receive a bullet-point under that table.
An ER diagram is available in PNG format. You should consult this diagram whilst reading the below.
User Cluster
- user holds users.
- password_hash is the md5sum the user's password plus a salt.
- password_hash_salt The salt used to generate the password hash. This is a random 32-character string that will be replaced by a new string whenever the user changes their password. The use of a salt is useful in preventing rainbow table attacks in the event that somebody gets a copy of your database.
- current_salt_hash and current_salt_expiration hold information regarding the token that is stored in the cookie. If the time in the expiration column comes to pass, the salt will be destroyed and the user will be logged out.
- avatar_id holds the user's preferred avatar ID. This field is optional, so it may be 0.
- avatar is LEFT JOIN'd to the user table (so it is optional) and provides the user's selected avatar.
- timezone and datetime_format are INNER JOIN'd to the user table. They provide the user's timezone and the date/time format that they prefer to see dates/times in. If these tables are empty, a user cannot exist.
- user_notification holds events for a specific user. These may be things like a user received an item from another user, or the user has a new private message waiting for them.
- notification_text holds the actual message that the user will be shown.
- notification_url holds a URL fragment. This fragment is the page that the user will be redirected to when they click on the event text. The fragment should be in the format /page-slug/any/additional/arguments. You can add new notifications using the User#notify() method.
- user_online is a MEMORY table that stores information about users and guests who are browsing the site.
- user_type will reflect whether or not the client is a logged-in user or just a guest.
- user_id will be populated if the user_type = user.
- client_ip will be populated if the user_type = guest.
- client_user_agent is the browser being used.
Permission Cluster
See the permission system details article for more information on how permissions work.
- staff_group is a group for users.
- show_staff_group is a yes/no flag that indicates whether or not this group should be listed on the staff page. Yes indicates that the group should be displayed, and no indicates that it should not.
- order_by is a number that is used for determining what order to show the groups on the staff page. An ORDER BY order_by ASC is done when selecting the group list.
- user_staff_group is a mapping of users to groups. A row in this table places a user in a specific group.
- staff_permission contains all of the permissions for your site.
- api_name is the string you supply when doing a User#hasPermission() call.
- staff_group_staff_permission is a mapping of groups to permissions. A row in this table indicates that a specific group is granted a specific permission.
Private Message Cluster
- user_message holds private messages. A private message row is a single message from exactly one user and received by exactly one user. The carbon-copy feature of the messaging system creates one or more rows in this table - one row for each recipient of the message.
- recipient_list is a specially-formatted string that holds a complete list of recipients. Since it is immensely annoying to store this detail in a normalized manner (hold recipients in another table for a 'message group', only delete those rows when *every* instance of the message in the message group is deleted, etc.), I elected to store the user ID / username in a text field. To make working with this ugly hack easier, the Message#setRecipientList() and Message#getRecipientList() methods parse this string and return an array.
Pet Cluster
- user_pet links together three things - a particular species of a pet, a particular color, and a user. Rows in this table are actual instances of user-owned pets.
- unixtime_next_decrement is a UNIX timestamp representing the next time the pet's hunger/happiness levels should drop.
- pet_specie rows represent each unique species of pet.
- relative_image_dir is the name of the folder which holds the pet's images. The folder should be relative to resources/pets/.
- pet_specie_color represents each individual color that may be applied to the different pet species. This table should contain one entry for red, one for blue, etc.
- base_color is a yes/no flag that indicates whether or not this is a basic color that a pet may be created in during the Adopt a Pet process. Yes means that the color is available, no means that is is not.
- pet_specie_pet_specie_color is a table that maps the different colors to a species of pet. A row in this table means that a pet species is available in a certain color.
- During the adopt process, this mapping is used to determine what basic colors the pet has. For example, one species may have images drawn for red, green, and yellow, while another may only have images for red and green. It is perfectly acceptable to only build mappings for the two colors that the second species has, and the adopt page will only show those two colors in the color dropdown menu.
- When a user tries to use a paint item, the mapping is used to determine if the species can be painted in that color. If there is no mapping for the species and the item's color, the user will be politely informed of the failure and their item will *not* be destroyed.
Item Cluster
- user_item rows map together two things - a user and an item type. Rows in this table are actual instances of items in a user's inventory.
- item_class is a table of 'types' for item_type. Entries in this table are high-level types - food, paint, toy, etc. A row in this table should also have a corresponding *_Item class.
- php_class is the name of the class for this particular type of item.
- relative_image_dir is the folder, relative to resources/items/, in which images for this item type should reside.
- verb is the text that will be used on the use item page in the sentence "which pet would you like to {$VERB} with the {$ITEM_NAME}?".
- normal_inventory_display is a yes/no flag. When set to yes, items in this class will not show up on the inventory screen; you will need to build your own screen for showing them (the Crafting screen, for example).
- item_type are the different types of items (ie, Red Apple, Green Apple, Ball) available in your application.
- happiness_bonus / hunger_bonus / pet_specie_color_id are all related to a specific item type.
- item_image is relative to the item class' folder.
- recipe_created_item_type_id / recipe_batch_quantity are related to recipe items. They define how many of a specific type of item your recipe results in.
- unique_item is a yes/no flag. When set to yes, a user will only be allowed to own one of that item type. This is useful for things like recipes, where you'd only want a user to own one copy.
- transferable_item is a yes/no flag that indicates whether or not a user may give away a type of item.
- item_recipe_material defines the ingredients for a recipe.
- item_recipe_type is a lookup table with recipe types (recipe, blueprint, etc).
Shop Cluster
- shop contains the NPC-owned shops.
- shop_inventory rows are a specific quantity of a specific item being available for a specific price in a specific shop.
- shop_restock rows indicate that a specific item should stock a specific shop every so often.
- restock_frequency_seconds is the number of seconds between attempts to perform this restock.
- unixtime_next_restock is a UNIX timestamp representing the next time a restock attempt should be made.
- min_price / max_price are the minimum and maximum prices this item should stock at. min_quantity / max_quantity are the minimum and maximum quantities of this item that should be added to the shop's stock per restock.
- store_quantity_cap is the maximum number of a specific item that the store should allow to be in stock at once. This prevents massive build-ups caused by repeated restocks of the item with nobody purchasing it.
Board Cluster
- board rows are the different forums that threads can be created in.
- board_locked is a yes/no flag indicating whether or not the creation of threads by non-admin users is restricted. Yes means that only users with a certain permission may create new threads, and no means that any user may create threads. This flag does not cause threads inside the board to be restricted.
- news_source is a yes/no flag indicating whether or not thread from this board should be considered news items and shown on the news page. Yes indicates that threads should be used as news items, and no indicates that they should not.
- board_thread rows represent all of the threads that exist.
- stickied is an integer that gets ORDERed BY when the thread list is selected. 0 will come up first, so any threads that are stickied = 0 are considered stuck. The BoardThread#getStickied() method knows about this and translates the real value into true (stuck) or false (not stuck).
- locked is a yes/no flag indicating whether or not any user, regardless of permissions, may continue to post in a thread. Yes indicates that the thread is closed and new posts may not be added, and no indicates that the thread is open and accepting new posts.
- board_thread_post contains the text of posts.
Utility Tables
- jump_page rows each correspond to a page slug. The row provides information such as permissions, page titles, and the physical file to include.
- page_title is the title that is put in between the <title> tags.
- page_html_title is the title that is displayed in the body of the page. Normally, you would want this to match the page_title, but in some cases you may want to put HTML into the title. HTML tags in the <title> tag do not work, so a separate field is needed.
- layout_type determines which layout the page should use. At this time, only the 'deep' layout is used.
- page_slug is the 'slug' for the page. In the URL for a thread, yoursite.com/boards/1, the boards section is the slug. The slug in the URL is used to look up a jump_page entry and load the appropriate PHP script.
- access_level determines what permission model the page should use. 'Public' means that there are no restrictions on who can view the page. 'User' indicates that the client must be logged in to view the page. 'Restricted' means that the permission in 'restricted_permission_api_name' should be checked, and if the user has that permission, they may view the page.
- restricted_permission_api_name is the permission that should be checked before allowing the client to access the page *only if* the access_level field is set to 'restricted'.
- php_script is the file to load, relative to the root of your Kitto install.
- include_tinymce is a yes/no flag indicating whether or not the includes for the TinyMCE javascript files should be performed. TinyMCE is a large chunk of javascript, so transferring it and having the browser parse it all every single page request does not make sense. So, for performance purposes, the includes will only be done if this flag is set to 'yes'.
- show_layout is a yes/no flag that determines if the regular header/footer layout templayes should be rendered. Setting this to no is useful for pages that will be requested/used by an AJAX callback.
- crontab is used by the Ghettocron subsystem for scheduling its jobs. See the Ghettocron article for more information.
- cron_class is the name of the class that should be instantiated when it is time to run this cronjob.
- cron_frequency_seconds is the number of seconds between each run.
- unixtime_next_run is a UNIX timestamp representing the next time at which the ghettocronjob should run.
