Skip to content

Database structure

This page is automatically generated and can be manually generated with database scripts.

Introduction

This documentation is automatically generated by information from MySQL and the PHP code.

The documentation inform you as developer about the database structure and column meaning. Additional for the development of new API or backend parts, the module table usage (extracted from PHP codebase) below provides helpful information. The provided information may help to find already existing queries, gateways, and/or the best location for a new query/function.

List of tables

Structure of tables

Table fs_abholer

Description - fs_abholer

Stores filled pickup slots, describes by who fetches when, where and if confirmed, needed to generate statistics (count, but not weight).

Open todos from old documentation - fs_abholer

  • Have deleted entries from fs_foodsaver and fs_betrieb reappear.
  • Hide deleted fs_betrieb on existing map
  • Don't care about that in existing code. Used to show pickup history, but broken old entries seem fine.
  • Document: Add to database as comment (1: Confirmed, 0: Not confirmed)
  • Clarify: Why is this an integer and not a boolean?

Table columns - fs_abholer

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Weak-foreign key (fs_foodsaver:id)
betrieb_idunsigned int(10)Weak-foreign key (fs_betrieb:id)
datedatetime
confirmed1: Confirmed, 0: Not confirmedunsigned int(4)
idunsigned int(10)Primary Key, Auto-Increment

Table fs_abholzeiten

Description - fs_abholzeiten

Contains information about regurlary reoccuring pickup slots.

Open todos from old documentation - fs_abholzeiten

  • Document: Add to database as column (Dow) comment: Day of week (1=Monday, 0=Sunday)
  • Document: Add to database as column (Time) comment: When on the day the pickup is
  • Document: Add to database as column (Fetcher) comment: Number of slots (>= 0, >= 1 enforced by frontend)
  • Clarify: Where are additional single pickup slots are stored?
  • Remove all non-existent references to fs_betrieb, add ON DELETE CASCADE.
  • Add foreign key relationship to fs_betrieb Reasoning: The data only affects future pickups that will not occur in case a store is removed.

Table columns - fs_abholzeiten

ColumnDescriptionTypeproperties
betrieb_idunsigned int(10)Primary Key, Weak-foreign key (fs_betrieb:id)
dowDay of week (1=Monday, 0=Sunday)unsigned int(4)Primary Key
timeTime when on the day the pickup istime=00:00:00Primary Key
fetcherNumber of slots (> 0, limited by frontend currently 8)unsigned int(4)=4
descriptionoptional description for this pickup timevarchar(100)Nullable

Table fs_achievement

Description - fs_achievement

Table columns - fs_achievement

ColumnDescriptionTypeproperties
idunsigned int(11)Primary Key, Auto-Increment
region_idregion defining the scope in which this achievement is relevantunsigned int(10)foreign key (fs_bezirk:id)
namevarchar(255)Nullable
descriptionvarchar(255)Nullable
validity_in_days_after_assignmentint(11)Nullable
is_requestable_by_foodsaverint(1)Nullable
created_attimestamp=current_timestamp()
updated_attimestampNullable
iconthe icon to display this achievement withvarchar(255)Nullable

Table fs_answer

Description - fs_answer

Stores answers to quiz.

Open todos from old documentation - fs_answer

  • Add foreign key relationship to fs_question as well as ON DELETE CASCADE. Reasoning: Answers to removed questions are not needed, existing quiz sessions get a copy in fs_quiz_session.quiz_result.

Table columns - fs_answer

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
question_idunsigned int(10)foreign key (fs_question:id)
texttext(16777215)Nullable
explanationtext(16777215)
rightunsigned int(4)Nullable

Table fs_apitoken

Description - fs_apitoken

User tokens for ICS/ICAL/WebCal access to calendar of future events/pickups.

Open todos from old documentation - fs_apitoken

  • Remove tokens for not existing users
  • add foreign key relationship to fs_foodsaver as well as ON DELETE CASCADE.

Table columns - fs_apitoken

ColumnDescriptionTypeproperties
foodsaver_idOwner of the token and identifier for the calendarunsigned int(10)foreign key (fs_foodsaver:id)
tokenAccess token, hex-number from openssl.varchar(255)

Table fs_basket

Description - fs_basket

Lists all foodbaskets.

Open todos from old documentation - fs_basket

  • Check code to never display foodbaskets from removed users except to admins.
  • Clarify: Meaning of status == Basket::Status
  • Clarify: Meaning of fs_id
  • Clarify: Meaning of appost
  • Clarify: Meaning of contact_type
  • Clarify: Meaning of location_type
  • Clarify: Meaning of weight unit
  • Clarify: Meaning of picture
  • Clarify: Meaning of time
  • Clarify: Meaning of update
  • Clarify: Meaning of until
  • Clarify: Meaning of fetchtime
  • Clarify: Owner of tel
  • Clarify: Owner of handy

Table columns - fs_basket

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
foodsaver_idunsigned int(10)Weak-foreign key (fs_foodsaver:id)
status@Basket::Statusunsigned int(4)Nullable
timeCreation datetimedatetimeNullable
updatedatetimeNullable
untilBasket is present until datetime expiresdatetime
fetchtimedatetimeNullable
descriptiontext(16777215)Nullable
picturetext(65535)Nullable
telvarchar(50)
handyvarchar(50)
contact_typevarchar(20)=1
location_typeFix set to 0unsigned int(4)Nullable
weightSmaller <0kg = 3kgfloatNullable
latBasket location latitudefloat
lonBasket location longitudefloat
bezirk_idunsigned int(10)Weak-foreign key (fs_bezirk:id)
appostint(4)

Table fs_basket_anfrage

Description - fs_basket_anfrage

Lists foodbasket requests.

Open todos from old documentation - fs_basket_anfrage

  • Remove entries for non-existing users
  • add ON DELETE CASCADE to fs_foodsaver.
  • Remove entries for non-existing baskets
  • add ON DELETE CASCADE to fs_basket.
  • Reasoning: Requests for non existant users do not need to be kept, basket FK is purely defensive.

Table columns - fs_basket_anfrage

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
basket_idunsigned int(10)Primary Key, foreign key (fs_basket:id)
statusunsigned int(4)Nullable
timedatetime
appostint(4)

Table fs_basket_has_art

Description - fs_basket_has_art

Combines foodbaskets with different types of food. Unused: Has to be entered but is never evaluated.

Open todos from old documentation - fs_basket_has_art

  • remove in code (in future), leave database as is (for now)

Table columns - fs_basket_has_art

ColumnDescriptionTypeproperties
basket_idunsigned int(10)Primary Key
art_idunsigned int(10)Primary Key

Table fs_basket_has_types

Description - fs_basket_has_types

See fs_basket_has_art

Table columns - fs_basket_has_types

ColumnDescriptionTypeproperties
basket_idunsigned int(10)Primary Key
types_idunsigned int(10)Primary Key

Table fs_basket_has_wallpost_abandoned

Description - fs_basket_has_wallpost_abandoned

Table columns - fs_basket_has_wallpost_abandoned

ColumnDescriptionTypeproperties
basket_idunsigned int(10)Primary Key, foreign key (fs_basket:id)
wallpost_idunsigned int(10)Primary Key, foreign key (fs_wallpost:id)

Table fs_bell

Description - fs_bell

Stores arbitrary notifications

Table columns - fs_bell

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
namevarchar(50)Nullable
bodyvarchar(50)Nullable
varstext(16777215)Nullable
attrvarchar(500)Nullable
iconvarchar(150)Nullable
identifiervarchar(40)Nullable
timedatetime
closeableunsigned int(4)=1
expirationdateNullable

Table fs_betrieb

Description - fs_betrieb

Stores stores.

Open todos from old documentation - fs_betrieb

  • Conversation 108242 and 108243 missing (store 1, 2) (fixed, inserted)
  • Link bezirk_id to fs_bezirk
  • Care about kette_id, betrieb_kategorie_id (fixed with issue #862)

Table columns - fs_betrieb

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
betrieb_status_id@Status::CooperationStatusunsigned int(10)
bezirk_idunsigned int(10)
addeddate
plzvarchar(5)
stadtvarchar(50)
latvarchar(20)Nullable
lonvarchar(20)Nullable
kette_idunsigned int(10)Weak-foreign key (fs_kette:id), Nullable
betrieb_kategorie_idunsigned int(10)foreign key (fs_betrieb_kategorie:id), Nullable
namevarchar(120)Nullable
strvarchar(120)Nullable
status_datedateNullable
statusunsigned int(4)Nullable
ansprechpartnervarchar(60)Nullable
telefonvarchar(50)Nullable
faxvarchar(50)Nullable
emailvarchar(60)Nullable
begindateNullable
besonderheitentext(16777215)Nullable
public_infovarchar(535)Nullable
public_time@Store::PublicTimesint(4)
ueberzeugungsarbeit@Store::ConvinceStatusint(4)
presseYes=0; No=1int(4)Nullable
stickerYes=0; No=1int(4)Nullable
abholmengeSee @WeightHelperint(4)
team_status0 = Team Voll; 1 = Es werden noch Helfer gesucht; 2 = Es werden dringend Helfer gesuchtint(4)=1
prefetchtimeFrequence of expected regular pickups in secondsunsigned int(10)=1209600
team_conversation_idunsigned int(10)Nullable
springer_conversation_idunsigned int(10)Nullable
deleted_atdatetimeNullable
use_region_pickup_rule[@StoreSettings::USE](https://gitlab.com/foodsharing-dev/foodsharing/-/tree/master/src/Modules/Core/DBConstants/StoreSettings/USE.php)_PICKUP_RULE_YES = Store follows region pickup rule. @StoreSettings::USE_PICKUP_RULE_NO = Store does not follow region pickup rule.unsigned int(1)

Table fs_betrieb_has_lebensmittel

Description - fs_betrieb_has_lebensmittel

Table columns - fs_betrieb_has_lebensmittel

ColumnDescriptionTypeproperties
betrieb_idunsigned int(10)Primary Key
lebensmittel_idunsigned int(10)Primary Key

Table fs_betrieb_kategorie

Description - fs_betrieb_kategorie

Table columns - fs_betrieb_kategorie

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
namevarchar(50)Nullable

Table fs_betrieb_notiz

Description - fs_betrieb_notiz

Stores wallposts on stores (independent of wallpost system)

Open todos from old documentation - fs_betrieb_notiz

  • Have deleted entries from fs_foodsaver and fs_betrieb reappear.

Table columns - fs_betrieb_notiz

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
foodsaver_idunsigned int(10)
betrieb_idunsigned int(10)
milestoneunsigned int(4)
texttext(16777215)Nullable
zeitdatetimeNullable
lastint(4)

Table fs_store_log

Description - fs_store_log

Table columns - fs_store_log

ColumnDescriptionTypeproperties
idunsigned int(11)Primary Key, Auto-Increment
store_idID of Storeint(10)
date_activitywhen did the action take placedatetime=current_timestamp()
actionaction type that was performedint(4)
fs_id_afoodsaver_id who is doing the actionint(10)
fs_id_pto which foodsaver_id is it done toint(10)Nullable
date_referencedate referenced (slot or wallpost entry)datetimeNullable
contentText from the store-wall-entryvarchar(255)Nullable
reasonWhy a negativ action was donetext(16777215)Nullable

Table fs_theme

Description - fs_theme

Threads in the forum.

Open todos from old documentation - fs_theme

  • nothing as threads from deleted users should be kept

Table columns - fs_theme

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
foodsaver_idunsigned int(10)
last_post_idunsigned int(10)
namevarchar(260)Nullable
timedatetimeNullable
activeunsigned int(4)=1
stickyint(1)
statusstatus of the thread (open or closed)unsigned int(10)

Table fs_theme_follower

Description - fs_theme_follower

stores who follows themes

Open todos from old documentation - fs_theme_follower

  • remove broken data (as follower information is irrelevant for broken XY)
  • Add FK

Table columns - fs_theme_follower

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
theme_idunsigned int(10)Primary Key, foreign key (fs_theme:id)
infotypeint(1)
bell_notificationint(1)=1

Table fs_theme_post

Description - fs_theme_post

Stores posts in themes

Open todos from old documentation - fs_theme_post

  • Recreate users
  • Remove broken data (for themes, not users)
  • Add FK to themes

Table columns - fs_theme_post

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
theme_idunsigned int(10)foreign key (fs_theme:id)
foodsaver_idunsigned int(10)
reply_postunsigned int(10)
bodytext(16777215)Nullable
timedatetimeNullable
hidden_timedatetimeNullable
hidden_byunsigned int(10)foreign key (fs_foodsaver:id), Nullable
hidden_reasonvarchar(255)Nullable

Table fs_usernotes_has_wallpost

Description - fs_usernotes_has_wallpost

connects organotes on users with wallposts

Open todos from old documentation - fs_usernotes_has_wallpost

  • recreate missing users
  • remove broken entries (for wallposts)

Table columns - fs_usernotes_has_wallpost

ColumnDescriptionTypeproperties
usernotes_idunsigned int(10)Primary Key
wallpost_idunsigned int(10)Primary Key, foreign key (fs_wallpost:id)
usercommentint(4)

Table fs_verify_history

Description - fs_verify_history

Table columns - fs_verify_history

ColumnDescriptionTypeproperties
fs_idunsigned int(10)Nullable
datedatetime
bot_idunsigned int(10)Nullable
change_statusint(1)Nullable

Table fs_wallpost

Description - fs_wallpost

Table columns - fs_wallpost

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
foodsaver_idunsigned int(10)
bodytext(16777215)Nullable
timedatetimeNullable
attachtext(16777215)Nullable

Table phinxlog

Description - phinxlog

Table columns - phinxlog

ColumnDescriptionTypeproperties
versionint(20)Primary Key
migration_namevarchar(100)Nullable
start_timetimestampNullable
end_timetimestampNullable
breakpointint(1)

Table uploads

Description - uploads

Table columns - uploads

ColumnDescriptionTypeproperties
uuidchar(36)Primary Key
user_idunsigned int(10)
sha256hashchar(64)
mimeTypevarchar(255)
uploaded_atdatetime
lastaccess_atdatetime
filesizeunsigned int(10)
used_inIndicates in which module this uploaded file is being used (profile photo, wall post, ...). A value of null indicates that the file is not being used (yet).unsigned int(4)Nullable
usage_idId of the entity that uses this uploaded file, e.g. id of the profile or the wall post. A null value indicates that the file is not being used (yet).char(10)Nullable

Usage of table in PHP Modules

Achievement

Activity

Application

Banana

Basket

Bell

Blog

Buddy

BusinessCard

Command

Commands

Content

DTO

Dev

Event

FoodSharePoint

Foodsaver

Group

Info

Login

Mailbox

Mails

Maintenance

Map

Message

PassportGenerator

Profile

PushNotification

Querys

Quiz

Region

Report

Settings

Statistics

Stats

Store

StoreCategories

StoreChain

Unclassified

Unit

Voting

WallPost

WorkGroup