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 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)
stickerYes=0; No=1int(4)
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)
hygiene_requirementunsigned int(4)

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_idint(11)Nullable
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 ​