English

Deutsch

English

Deutsch

Appearance

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 intWeak-foreign key (fs_foodsaver:id)
betrieb_idunsigned intWeak-foreign key (fs_betrieb:id)
datedatetime
confirmed1: Confirmed, 0: Not confirmedunsigned tinyint
idunsigned intPrimary 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 intPrimary Key, Weak-foreign key (fs_betrieb:id)
dowDay of week (1=Monday, 0=Sunday)unsigned tinyintPrimary Key
timeTime when on the day the pickup istime=00:00:00Primary Key
fetcherNumber of slots (> 0, limited by frontend currently 8)unsigned tinyint=4
descriptionoptional description for this pickup timevarchar(100)Nullable

Table fs_achievement ​

Description - fs_achievement ​

Table columns - fs_achievement ​

ColumnDescriptionTypeproperties
idunsigned intPrimary Key, Auto-Increment
region_idregion defining the scope in which this achievement is relevantunsigned intforeign key (fs_bezirk:id)
namevarchar(255)Nullable
descriptionvarchar(255)Nullable
validity_in_days_after_assignmentintNullable
is_requestable_by_foodsavertinyintNullable
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 intPrimary Key, Auto-Increment
question_idunsigned intforeign key (fs_question:id)
texttext(16777215)Nullable
explanationtext(16777215)
rightunsigned tinyintNullable

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 intforeign 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 intPrimary Key, Auto-Increment
foodsaver_idunsigned intWeak-foreign key (fs_foodsaver:id)
status@Basket::Statusunsigned tinyintNullable
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 tinyintNullable
weightSmaller <0kg = 3kgfloatNullable
latBasket location latitudefloat
lonBasket location longitudefloat
bezirk_idunsigned intWeak-foreign key (fs_bezirk:id)
apposttinyint

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 intPrimary Key, foreign key (fs_foodsaver:id)
basket_idunsigned intPrimary Key, foreign key (fs_basket:id)
statusunsigned tinyintNullable
timedatetime
apposttinyint

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 intPrimary Key
art_idunsigned intPrimary 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 intPrimary Key
types_idunsigned intPrimary Key

Table fs_basket_has_wallpost_abandoned ​

Description - fs_basket_has_wallpost_abandoned ​

Table columns - fs_basket_has_wallpost_abandoned ​

ColumnDescriptionTypeproperties
basket_idunsigned intPrimary Key, foreign key (fs_basket:id)
wallpost_idunsigned intPrimary Key, foreign key (fs_wallpost:id)

Table fs_bell ​

Description - fs_bell ​

Stores arbitrary notifications

Table columns - fs_bell ​

ColumnDescriptionTypeproperties
idunsigned intPrimary Key, Auto-Increment
namevarchar(50)Nullable
bodyvarchar(50)Nullable
varstext(16777215)Nullable
attrvarchar(500)Nullable
iconvarchar(150)Nullable
identifiervarchar(40)Nullable
timedatetime
closeableunsigned tinyint=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 intPrimary Key, Auto-Increment
betrieb_status_id@Status::CooperationStatusunsigned int
bezirk_idunsigned int
addeddate
plzvarchar(5)
stadtvarchar(50)
latvarchar(20)Nullable
lonvarchar(20)Nullable
kette_idunsigned intWeak-foreign key (fs_kette:id), Nullable
betrieb_kategorie_idunsigned intforeign key (fs_betrieb_kategorie:id), Nullable
namevarchar(120)Nullable
strvarchar(120)Nullable
status_datedateNullable
statusunsigned tinyintNullable
ansprechpartnervarchar(60)Nullable
telefonvarchar(50)Nullable
faxvarchar(50)Nullable
emailvarchar(60)Nullable
begindateNullable
besonderheitentext(16777215)Nullable
public_infovarchar(535)Nullable
public_time@Store::PublicTimestinyint
ueberzeugungsarbeit@Store::ConvinceStatustinyint
presseYes=0; No=1tinyint
stickerYes=0; No=1tinyint
abholmengeSee @WeightHelpertinyint
team_status0 = Team Voll; 1 = Es werden noch Helfer gesucht; 2 = Es werden dringend Helfer gesuchttinyint=1
prefetchtimeFrequence of expected regular pickups in secondsunsigned int=1209600
team_conversation_idunsigned intNullable
springer_conversation_idunsigned intNullable
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
hygiene_requirementunsigned tinyint

Table fs_betrieb_has_lebensmittel ​

Description - fs_betrieb_has_lebensmittel ​

Table columns - fs_betrieb_has_lebensmittel ​

ColumnDescriptionTypeproperties
betrieb_idunsigned intPrimary Key
lebensmittel_idunsigned intPrimary Key

Table fs_betrieb_kategorie ​

Description - fs_betrieb_kategorie ​

Table columns - fs_betrieb_kategorie ​

ColumnDescriptionTypeproperties
idunsigned intPrimary 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 intPrimary Key, Auto-Increment
foodsaver_idunsigned int
betrieb_idunsigned int
milestoneunsigned tinyint
texttext(16777215)Nullable
zeitdatetimeNullable
lasttinyint

Table fs_store_log ​

Description - fs_store_log ​

Table columns - fs_store_log ​

ColumnDescriptionTypeproperties
idunsigned intPrimary Key, Auto-Increment
store_idID of Storeint
date_activitywhen did the action take placedatetime=current_timestamp()
actionaction type that was performedint
fs_id_afoodsaver_id who is doing the actionint
fs_id_pto which foodsaver_id is it done tointNullable
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 intPrimary Key, Auto-Increment
foodsaver_idunsigned int
last_post_idunsigned int
namevarchar(260)Nullable
timedatetimeNullable
activeunsigned tinyint=1
stickytinyint
statusstatus of the thread (open or closed)unsigned int

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 intPrimary Key, foreign key (fs_foodsaver:id)
theme_idunsigned intPrimary Key, foreign key (fs_theme:id)
infotypetinyint
bell_notificationtinyint=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 intPrimary Key, Auto-Increment
theme_idunsigned intforeign key (fs_theme:id)
foodsaver_idunsigned int
reply_postunsigned int
bodytext(16777215)Nullable
timedatetimeNullable
hidden_timedatetimeNullable
hidden_byunsigned intforeign 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 intPrimary Key
wallpost_idunsigned intPrimary Key, foreign key (fs_wallpost:id)
usercommenttinyint

Table fs_verify_history ​

Description - fs_verify_history ​

Table columns - fs_verify_history ​

ColumnDescriptionTypeproperties
fs_idunsigned intNullable
datedatetime
bot_idunsigned intNullable
change_statustinyintNullable

Table fs_wallpost ​

Description - fs_wallpost ​

Table columns - fs_wallpost ​

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

Table phinxlog ​

Description - phinxlog ​

Table columns - phinxlog ​

ColumnDescriptionTypeproperties
versionbigintPrimary Key
migration_namevarchar(100)Nullable
start_timetimestampNullable
end_timetimestampNullable
breakpointtinyint

Table uploads ​

Description - uploads ​

Table columns - uploads ​

ColumnDescriptionTypeproperties
uuidchar(36)Primary Key
user_idintNullable
sha256hashchar(64)
mimeTypevarchar(255)
uploaded_atdatetime
lastaccess_atdatetime
filesizeunsigned int
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 intNullable
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 ​