Database structure

This page is automatic generated.

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

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_application_has_wallpost

Description - fs_application_has_wallpost

Link table between applications (for groups) and the wallposts. Design broken: Links foodsaver ids to the wallposts, e.g. all applications a foodsaver does to somewhere

Open todos from old documentation - fs_application_has_wallpost

  • Remove entries for non-existing users
  • add ON DELETE CASCADE to fs_foodsaver.
  • Remove entries for non-existing wallposts
  • add ON DELETE CASCADE to fs_wallpost
  • Reasoning: Applications texts for deleted users are not useful anymore

Table columns - fs_application_has_wallpost

ColumnDescriptionTypeproperties
application_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
wallpost_idunsigned int(10)Primary Key, foreign key (fs_wallpost:id)

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
picturevarchar(150)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)
fs_idint(10)
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_idunsigned int(10)
bezirk_idunsigned int(10)
addeddate
plzvarchar(5)
stadtvarchar(50)
latvarchar(20)Nullable
lonvarchar(20)Nullable
kette_idunsigned int(10)Nullable
betrieb_kategorie_idint(11)Nullable
namevarchar(120)Nullable
strvarchar(120)Nullable
hsnrvarchar(20)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(200)Nullable
public_timeint(4)
ueberzeugungsarbeitint(4)
presseint(4)
stickerint(4)
abholmengeint(4)
team_status0 = Team Voll; 1 = Es werden noch Helfer gesucht; 2 = Es werden dringend Helfer gesuchtint(4)=1
prefetchtimeunsigned int(10)=1209600
team_conversation_idunsigned int(10)Nullable
springer_conversation_idunsigned int(10)Nullable
deleted_atdatetimeNullable

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_betrieb_team

Description - fs_betrieb_team

Defines foodsaver team embers with pickup store provided food and statistic.

Open todos from old documentation - fs_betrieb_team

  • Have deleted entries from fs_foodsaver and fs_betrieb reappear.
  • Clarify: Describe the meaning of APPLIED_FOR_TEAM and jumber
  • Clarify: Could a managing/verantworktlicher can pick up from a store?
  • Clarify: What are Biebs? StoreGateway::getBiebsForStore()
  • Clarify: Why does the table contains a id, is the foodsaver_id and betrieb_id not unique enought
  • Clarify: Why are stats directly part table?
  • Clarify: Is it possible to be black listed for a store or a team?

Table columns - fs_betrieb_team

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Weak-foreign key (fs_foodsaver:id)
betrieb_idunsigned int(10)Weak-foreign key (fs_betrieb:id)
verantwortlich@StoreTeam::Responsibleunsigned int(4)Nullable
active@StoreTeam::MembershipStatusint(11)
stat_last_updatedatetimeNullable
stat_fetchcountunsigned int(10)
stat_first_fetchdateNullable
stat_last_fetchdateNullable
stat_add_datedateNullable
idunsigned int(10)Primary Key, Auto-Increment

Table fs_bezirk

Description - fs_bezirk

Stores districts as well as workinggroups (differentiated by type column) (referred to as 'group' or 'district' in this document)

Open todos from old documentation - fs_bezirk

  • Remove unused columns email_pass, conversation_id
  • Clarify: Field description
  • Clarify: Extract typs from src\Modules\Core\DBConstants\Region\Type

Table columns - fs_bezirk

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
parent_idunsigned int(11)foreign key (fs_bezirk:id), Nullable
has_childrenint(4)
typeint(4)=1
teasertext(16777215)
desctext(16777215)
photovarchar(200)
masterunsigned int(10)
mailbox_idunsigned int(10)
namevarchar(50)Nullable
emailvarchar(120)
email_passvarchar(50)
email_namevarchar(100)
apply_typeint(4)=2
banana_countint(4)
fetch_countint(4)
week_numint(4)
report_numint(4)
stat_last_updatedatetime=current_timestamp()
stat_fetchweightunsigned decimal
stat_fetchcountunsigned int(10)
stat_postcountunsigned int(10)
stat_betriebcountunsigned int(7)
stat_korpcountunsigned int(7)
stat_botcountunsigned int(7)
stat_fscountunsigned int(7)
stat_fairteilercountunsigned int(7)
conversation_idunsigned int(10)
moderatedint(4)

Table fs_bezirk_closure

Description - fs_bezirk_closure

Table columns - fs_bezirk_closure

ColumnDescriptionTypeproperties
bezirk_idunsigned int(10)foreign key (fs_bezirk:id)
ancestor_idunsigned int(10)foreign key (fs_bezirk:id)
depthunsigned int(10)

Table fs_bezirk_has_theme

Description - fs_bezirk_has_theme

Forum: Relates threads to groups.

Open todos from old documentation - fs_bezirk_has_theme

  • remove broken entries
  • add foreign key relationsips on fs_theme and fs_bezirk with ON DELETE CASCADE trigger.

Table columns - fs_bezirk_has_theme

ColumnDescriptionTypeproperties
theme_idunsigned int(10)Primary Key, foreign key (fs_theme:id)
bezirk_idunsigned int(10)Primary Key, foreign key (fs_bezirk:id)
bot_themeunsigned int(4)

Table fs_bezirk_has_wallpost

Description - fs_bezirk_has_wallpost

Open todos from old documentation - fs_bezirk_has_wallpost

  • remove broken entries
  • add foreign key relationships on fs_bezirk and fs_wallpost with ON DELETE CASCADE trigger.

Table columns - fs_bezirk_has_wallpost

ColumnDescriptionTypeproperties
bezirk_idunsigned int(10)Primary Key, foreign key (fs_bezirk:id)
wallpost_idunsigned int(10)Primary Key, foreign key (fs_wallpost:id)

Table fs_blog_entry

Description - fs_blog_entry

Stores blog entries (there is a /news URL)

Table columns - fs_blog_entry

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
bezirk_idunsigned int(10)
foodsaver_idunsigned int(10)
activeunsigned int(4)
namevarchar(100)Nullable
teaservarchar(500)Nullable
bodytext(16777215)Nullable
timedatetimeNullable
picturevarchar(150)

Table fs_botschafter

Description - fs_botschafter

Notes the ambassador/admin attribute on a group membership.

Open todos from old documentation - fs_botschafter

  • remove broken entries
  • add foreign key relationships on fs_bezirk and fs_foodsaver

Table columns - fs_botschafter

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
bezirk_idunsigned int(10)Primary Key, foreign key (fs_bezirk:id)

Table fs_buddy

Description - fs_buddy

'I know XY' connection/friendship relation.

Open todos from old documentation - fs_buddy

  • investigate, why there are lots of '0' entries remove broken entries
  • add foreign key relationships on fs_foodsaver with ON DELETE CASCADE trigger

Table columns - fs_buddy

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
buddy_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
confirmedunsigned int(4)Nullable

Table fs_contact

Description - fs_contact

Stores email addresses gathered from incoming/outgoing emails that will be used for autocompletion.

Table columns - fs_contact

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

Table fs_content

Description - fs_content

'Mini-CMS' content table that is used to generate some pages as well as sections on some pages.

Table columns - fs_content

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
namevarchar(20)Nullable
titlevarchar(120)Nullable
bodytext(16777215)Nullable
last_moddatetimeNullable

Table fs_conversation

Description - fs_conversation

Conversation table

Open todos from old documentation - fs_conversation

  • get rid of API module
  • switch over to foodsharing-API backend to get rid of denormalized columns.
  • By that, also stop htmlentities encoding. On the decoding side, implement time aware message body decoders.
  • Denormalized fields usage: listConversations (member, last)
  • remove last_foodsaver_id (unused, easy)
  • remove heartbeat code (that is not used since we have socket.io)

Table columns - fs_conversation

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
lockedint(1)
namevarchar(40)Nullable
lastdatetimeNullable
last_foodsaver_idunsigned int(10)Nullable
last_message_idunsigned int(10)Nullable
last_messagetext(16777215)Nullable
last_message_is_htmlentity_encodedint(1)=1

Table fs_email_blacklist

Description - fs_email_blacklist

Contains email addresses that are not allowed to signup (needs database admin to maintain)

Table columns - fs_email_blacklist

ColumnDescriptionTypeproperties
emailvarchar(255)
sincetimestamp=current_timestamp()
reasontext(16777215)

Table fs_email_bounces

Description - fs_email_bounces

Table columns - fs_email_bounces

ColumnDescriptionTypeproperties
emailvarchar(255)
bounced_atdatetime
bounce_categoryvarchar(255)

Table fs_email_status

Description - fs_email_status

Mass mailer status table (per-recipient status)

Open todos from old documentation - fs_email_status

  • remove broken entries
  • add foreign key relationships to fs_foodsaver and fs_email with ON DELETE CASCADE

Table columns - fs_email_status

ColumnDescriptionTypeproperties
email_idunsigned int(10)Primary Key, foreign key (fs_send_email:id)
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
statusunsigned int(4)Nullable

Table fs_event

Description - fs_event

Contains all events

Open todos from old documentation - fs_event

  • Regain missing users
  • Check code fetching events to properly handle deleted users
  • remove broken entries for fs_bezirk by setting them NULL
  • add foreign key relationship to fs_bezirk (on delete set NULL)
  • add foreign key relationship to fs_location (nullable)

Table columns - fs_event

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
foodsaver_idunsigned int(10)
bezirk_idunsigned int(10)foreign key (fs_bezirk:id), Nullable
location_idunsigned int(10)foreign key (fs_location:id), Nullable
publicint(1)
namevarchar(200)Nullable
startdatetime
enddatetime
descriptiontext(16777215)Nullable
botunsigned int(4)Nullable
onlineunsigned int(4)Nullable

Table fs_event_has_wallpost

Description - fs_event_has_wallpost

Links wallposts on event (only communication channel for an event)

Open todos from old documentation - fs_event_has_wallpost

  • Remove broken entries
  • add foreign key relationships to fs_event and fs_wallpost, ON DELETE CASCADE

Table columns - fs_event_has_wallpost

ColumnDescriptionTypeproperties
event_idunsigned int(10)Primary Key, foreign key (fs_event:id)
wallpost_idunsigned int(10)Primary Key, foreign key (fs_wallpost:id)

Table fs_fairteiler

Description - fs_fairteiler

Lists fair share points

Open todos from old documentation - fs_fairteiler

  • Null broken entries (should still be listable in map)
  • add foreign key relationship to fs_bezirk

Table columns - fs_fairteiler

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
bezirk_idunsigned int(10)foreign key (fs_bezirk:id), Nullable
namevarchar(260)Nullable
picturevarchar(100)
statusunsigned int(4)Nullable
desctext(16777215)Nullable
anschriftvarchar(260)Nullable
plzvarchar(5)Nullable
ortvarchar(100)Nullable
latvarchar(100)Nullable
lonvarchar(100)Nullable
add_datedateNullable
add_foodsaverunsigned int(10)Nullable

Table fs_fairteiler_follower

Description - fs_fairteiler_follower

Links follower/responsible to food share points

Open todos from old documentation - fs_fairteiler_follower

  • Remove broken entries
  • add foreign key relationships to fs_foodsaver and fs_fairteiler with ON DELETE CASCADE

Table columns - fs_fairteiler_follower

ColumnDescriptionTypeproperties
fairteiler_idunsigned int(10)Primary Key, foreign key (fs_fairteiler:id)
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
typeunsigned int(4)=1
infotypeunsigned int(4)=1

Table fs_fairteiler_has_wallpost

Description - fs_fairteiler_has_wallpost

Links wallposts to fair share points

Open todos from old documentation - fs_fairteiler_has_wallpost

  • Remove broken entries
  • add foreign key relationship to fs_fairteiler, fs_wallpost with ON DELETE CASCADE
  • check code that handles wallpost display to see how NULL foodsavers are handled.
  • Reasoning: Keep wallposts from deleted users.

Table columns - fs_fairteiler_has_wallpost

ColumnDescriptionTypeproperties
fairteiler_idunsigned int(10)Primary Key, foreign key (fs_fairteiler:id)
wallpost_idunsigned int(10)Primary Key, foreign key (fs_wallpost:id)

Table fs_fetchdate

Description - fs_fetchdate

Stores non-recurring pickup slots (just the slot, no fetcher information)

Open todos from old documentation - fs_fetchdate

  • reinsert missing stores

Table columns - fs_fetchdate

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
betrieb_idunsigned int(10)
timedatetimeNullable
fetchercountunsigned int(4)Nullable

Table fs_fetchweight

Description - fs_fetchweight

Table columns - fs_fetchweight

ColumnDescriptionTypeproperties
idint(11)Primary Key
weightdecimal

Table fs_foodsaver

Description - fs_foodsaver

User

Open todos from old documentation - fs_foodsaver

  • add deleted_at column
  • bezirk_id ('home district') is 0 for a lot of users. Should be NULL. As we don't need this soon in new backend, care later.
  • Document: Add to database as column (deleted_at) comment: deleted_at: deletion day of account, if NULL, account is active

Table columns - fs_foodsaver

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
bezirk_idunsigned int(10)
positionvarchar(255)
verifiedunsigned int(4)
last_passdatetimeNullable
new_bezirkvarchar(120)
want_newint(4)
mailbox_idunsigned int(10)Nullable
rolleint(4)
typeint(4)Nullable
plzvarchar(10)Nullable
stadtvarchar(100)Nullable
latvarchar(20)Nullable
lonvarchar(20)Nullable
photovarchar(50)Nullable
emailvarchar(120)Nullable
passwordvarchar(100)Nullable
namevarchar(120)Nullable
adminunsigned int(4)Nullable
nachnamevarchar(120)Nullable
anschriftvarchar(120)Nullable
telefonvarchar(30)Nullable
homepagevarchar(255)Nullable
handyvarchar(50)Nullable
geschlechtunsigned int(4)
geb_datumdateNullable
anmeldedatumdatetimeNullable
privacy_notice_accepted_datedatetimeNullable
privacy_policy_accepted_datedatetimeNullable
orgateamunsigned int(4)Nullable
activeunsigned int(4)
datatext(16777215)
about_me_publictext(16777215)
newsletterint(1)
tokenvarchar(100)
infomail_messageint(1)Nullable
last_logindatetimeNullable
stat_fetchweightunsigned decimal
stat_fetchcountunsigned int(10)
stat_ratecountunsigned int(10)
stat_ratingunsigned decimal
stat_postcountint(11)
stat_buddycountunsigned int(7)
stat_bananacountunsigned int(7)
stat_fetchratedecimal=100
sleep_statusunsigned int(4)
sleep_fromdateNullable
sleep_untildateNullable
sleep_msgtext(16777215)Nullable
optiontext(16777215)
betaint(1)
quiz_rolleunsigned int(4)
contact_publicint(4)
deleted_atdatetimeNullable
about_me_interntext(65535)Nullable
deleted_byid of the user who deleted this profileunsigned int(10)Nullable
deleted_reasonoptional explanation why this profile was deletedvarchar(200)Nullable

Table fs_foodsaver_archive

Description - fs_foodsaver_archive

Archive table to hold removed users for admin purposes

Open todos from old documentation - fs_foodsaver_archive

  • Move to fs_foodsaver_archive3
  • Recreate with same structure as fs_foodsaver

Table columns - fs_foodsaver_archive

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
bezirk_idunsigned int(10)
positionvarchar(255)
verifiedunsigned int(4)
last_passdatetimeNullable
new_bezirkvarchar(120)
want_newint(4)
mailbox_idunsigned int(10)Nullable
rolleint(4)
typeint(4)Nullable
plzvarchar(10)Nullable
stadtvarchar(100)Nullable
latvarchar(20)Nullable
lonvarchar(20)Nullable
photovarchar(50)Nullable
emailvarchar(120)Nullable
passwordvarchar(100)Nullable
namevarchar(120)Nullable
adminunsigned int(4)Nullable
nachnamevarchar(120)Nullable
anschriftvarchar(120)Nullable
telefonvarchar(30)Nullable
homepagevarchar(255)Nullable
handyvarchar(50)Nullable
geschlechtunsigned int(4)
geb_datumdateNullable
anmeldedatumdatetimeNullable
privacy_notice_accepted_datedatetimeNullable
privacy_policy_accepted_datedatetimeNullable
orgateamunsigned int(4)Nullable
activeunsigned int(4)
datatext(16777215)
about_me_publictext(16777215)
newsletterint(1)
tokenvarchar(25)
infomail_messageint(1)Nullable
last_logindatetimeNullable
stat_fetchweightunsigned decimal
stat_fetchcountunsigned int(10)
stat_ratecountunsigned int(10)
stat_ratingunsigned decimal
stat_postcountint(11)
stat_buddycountunsigned int(7)
stat_bananacountunsigned int(7)
stat_fetchratedecimal=100
sleep_statusunsigned int(4)
sleep_fromdateNullable
sleep_untildateNullable
sleep_msgtext(16777215)Nullable
optiontext(16777215)
betaint(1)
quiz_rolleunsigned int(4)
contact_publicint(4)
deleted_atdatetimeNullable
about_me_interntext(65535)Nullable
deleted_byid of the user who deleted this profileunsigned int(10)Nullable
deleted_reasonoptional explanation why this profile was deletedvarchar(200)Nullable

Table fs_foodsaver_change_history

Description - fs_foodsaver_change_history

Logs all changes to personal data in foodsaver table

Open todos from old documentation - fs_foodsaver_change_history

  • Do not use indices as this should persist deletions (as one reason for it was to be able to detect abuse)

Table columns - fs_foodsaver_change_history

ColumnDescriptionTypeproperties
datetimestamp=current_timestamp()
fs_idint(11)
changer_idint(11)
object_nametext(16777215)
old_valuetext(16777215)Nullable
new_valuetext(16777215)Nullable

Table fs_foodsaver_has_bell

Description - fs_foodsaver_has_bell

Stores bell <-> user relationship

Open todos from old documentation - fs_foodsaver_has_bell

  • Remove broken data
  • Add foreign key relationships to fs_foodsaver and fs_bell

Table columns - fs_foodsaver_has_bell

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
bell_idunsigned int(10)Primary Key, foreign key (fs_bell:id)
seenunsigned int(4)Nullable

Table fs_foodsaver_has_bezirk

Description - fs_foodsaver_has_bezirk

Describes which foodsaver is in which group/district/workgroups and regions membership

Open todos from old documentation - fs_foodsaver_has_bezirk

  • Remove broken data
  • Add foreign key relationships to fs_foodsaver and fs_bezirk

Table columns - fs_foodsaver_has_bezirk

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
bezirk_idunsigned int(10)Primary Key, foreign key (fs_bezirk:id)
active0=beworben,1=aktiv,10=vielleichtunsigned int(10)Nullable
addeddatetime=current_timestamp()
applicationtext(16777215)

Table fs_foodsaver_has_contact

Description - fs_foodsaver_has_contact

Relates contacts (email addresses, see above) to users

Open todos from old documentation - fs_foodsaver_has_contact

  • Remove broken data
  • Add FK to fs_foodsaver, fs_contact

Table columns - fs_foodsaver_has_contact

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
contact_idunsigned int(10)Primary Key, foreign key (fs_contact:id)

Table fs_foodsaver_has_conversation

Description - fs_foodsaver_has_conversation

Relates conversations to foodsavers. Care: It is also used to look up conversations by user

Open todos from old documentation - fs_foodsaver_has_conversation

  • Reinsert missing foodsaver, add FK to fs_conversation (not to user)
  • Fix code to handle deleted users correctly

Table columns - fs_foodsaver_has_conversation

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)foreign key (fs_foodsaver:id)
conversation_idunsigned int(10)foreign key (fs_conversation:id)
unreadunsigned int(4)=1Nullable
idunsigned int(10)Primary Key, Auto-Increment

Table fs_foodsaver_has_event

Description - fs_foodsaver_has_event

Relates users to events.

Open todos from old documentation - fs_foodsaver_has_event

  • Remove broken data
  • FK on fs_foodsaver, fs_event, ON DELETE CASCADE

Table columns - fs_foodsaver_has_event

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
event_idunsigned int(10)Primary Key, foreign key (fs_event:id)
statusunsigned int(4)

Table fs_foodsaver_has_options

Description - fs_foodsaver_has_options

Table columns - fs_foodsaver_has_options

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
option_typecategory of the optionunsigned int(10)Primary Key
option_valuevalue of the optionvarchar(255)

Table fs_foodsaver_has_poll

Description - fs_foodsaver_has_poll

Table columns - fs_foodsaver_has_poll

ColumnDescriptionTypeproperties
foodsaver_idid of the voterunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
poll_idid of the pollunsigned int(10)Primary Key, foreign key (fs_poll:id)
timetime at which the voter has voted, null if not voted yetdatetimeNullable

Table fs_foodsaver_has_wallpost

Description - fs_foodsaver_has_wallpost

Wallposts on user profile

Open todos from old documentation - fs_foodsaver_has_wallpost

  • Remove broken data
  • FK on fs_foodsaver, fs_wallpost, ON DELETE CASCADE

Table columns - fs_foodsaver_has_wallpost

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

Table fs_ipblock

Description - fs_ipblock

Table columns - fs_ipblock

ColumnDescriptionTypeproperties
ipvarchar(20)Primary Key
contextvarchar(10)Primary Key
startdatetimeNullable
durationunsigned int(10)Nullable

Table fs_kette

Description - fs_kette

Store chains

Table columns - fs_kette

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
namevarchar(60)Nullable
logovarchar(30)

Table fs_lebensmittel

Description - fs_lebensmittel

Store different kinds of food to be linked with individual stores. Only ever implemented as setter, don't care for now.

Table columns - fs_lebensmittel

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

Table fs_location

Description - fs_location

Table columns - fs_location

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
namevarchar(200)Nullable
latdecimalNullable
londecimalNullable
zipvarchar(10)Nullable
cityvarchar(100)Nullable
streetvarchar(200)Nullable

Table fs_mailbox

Description - fs_mailbox

Stores mailbox names (for email mailboxes)

Table columns - fs_mailbox

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
namevarchar(50)Nullable
memberint(4)
last_accessdatetime=current_timestamp()

Table fs_mailbox_member

Description - fs_mailbox_member

Maps additional mailbox access for users (e.g. granting custom mailboxes or group ones)

Open todos from old documentation - fs_mailbox_member

  • Remove brokendata
  • FK on fs_mailbox, fs_mailbox_member, ON DELETE CASCADE

Table columns - fs_mailbox_member

ColumnDescriptionTypeproperties
mailbox_idunsigned int(10)Primary Key, foreign key (fs_mailbox:id)
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
email_namevarchar(120)

Table fs_mailbox_message

Description - fs_mailbox_message

emails

Open todos from old documentation - fs_mailbox_message

  • remove brokendata
  • FK on fs_mailbox, ON DELETE CASCADE

Table columns - fs_mailbox_message

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
mailbox_idunsigned int(10)foreign key (fs_mailbox:id)
folderunsigned int(4)=1Nullable
sendertext(65535)Nullable
totext(16777215)
subjecttext(65535)Nullable
bodytext(16777215)Nullable
body_htmltext(16777215)
timedatetimeNullable
attachtext(16777215)Nullable
readunsigned int(4)Nullable
answerunsigned int(4)Nullable

Table fs_mailchange

Description - fs_mailchange

Requests to change the emailaddress

Open todos from old documentation - fs_mailchange

  • Remove broken / old data
  • FK on fs_foodsaver, ON DELETE CASCADE
  • There is a recent entry with foodsaver_id = 0. How did that get here?

Table columns - fs_mailchange

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
newmailvarchar(200)Nullable
timedatetimeNullable
tokenvarchar(300)Nullable

Table fs_msg

Description - fs_msg

Conversation messages

Open todos from old documentation - fs_msg

  • Remove broken data (conversations), readd missing users
  • FK on fs_conversation, ON DELETE CASCADE

Table columns - fs_msg

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
conversation_idunsigned int(10)foreign key (fs_conversation:id)
foodsaver_idunsigned int(10)
bodytext(16777215)Nullable
timedatetimeNullable
is_htmlentity_encodedint(1)=1

Table fs_pass_gen

Description - fs_pass_gen

Logs which ID cards have been generated

Open todos from old documentation - fs_pass_gen

  • remove broken data (foodsaver_id), readd missing (bot_id)
  • FK for foodsaver_id, ON DELETE CASCADE
  • handle emtpy users correctly (bot_id)

Table columns - fs_pass_gen

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
datedatetimePrimary Key
bot_idunsigned int(10)foreign key (fs_foodsaver:id), Nullable

Table fs_pass_request

Description - fs_pass_request

Password change/forgot requestsRelates questions <-> quiz

Open todos from old documentation - fs_pass_request

  • remove broken/old data
  • FK for foodsaver_id, ON DELETE CASCADE

Table columns - fs_pass_request

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
namevarchar(50)Nullable
timedatetimeNullable

Table fs_poll

Description - fs_poll

Table columns - fs_poll

ColumnDescriptionTypeproperties
idunique id of the pollunsigned int(10)Primary Key, Auto-Increment
region_idregion with which the poll is associatedunsigned int(10)foreign key (fs_bezirk:id)
nametitle of the pollvarchar(200)Nullable
descriptiondescription of the polltext(16777215)Nullable
scopedetermines who will be invited to voteunsigned int(2)
typedetermines how a vote is cast and which values are possible for each optionunsigned int(2)
startstart timestamp for the polldatetime
endend timestamp for the polldatetime
authorid of the user who created the pollunsigned int(10)
creation_timestampdatetime
votesnumber of users who have votedunsigned int(10)
cancelled_byid of the user who cancelled the pollunsigned int(10)Nullable
eligible_votes_countnumber of users who are eligible to voteunsigned int(10)
shuffle_optionsint(4)=1

Table fs_poll_has_options

Description - fs_poll_has_options

Table columns - fs_poll_has_options

ColumnDescriptionTypeproperties
poll_idthe poll to which this option belongsunsigned int(10)Primary Key, foreign key (fs_poll:id)
optionindex of the optionunsigned int(2)Primary Key
option_textdescription text of the optionvarchar(200)Nullable

Table fs_poll_option_has_value

Description - fs_poll_option_has_value

Table columns - fs_poll_option_has_value

ColumnDescriptionTypeproperties
poll_idthe poll to which the option belongsunsigned int(10)Primary Key, foreign key (fs_poll:id)
optionindex of the optionunsigned int(2)Primary Key
valuevalue for the optionint(2)Primary Key
votesnumber of current votes for the valueunsigned int(10)

Table fs_post_reaction

Description - fs_post_reaction

Table columns - fs_post_reaction

ColumnDescriptionTypeproperties
post_idunsigned int(10)foreign key (fs_theme_post:id)
timedatetime
foodsaver_idint(11)
keyvarchar(63)Nullable

Table fs_push_notification_subscription

Description - fs_push_notification_subscription

Table columns - fs_push_notification_subscription

ColumnDescriptionTypeproperties
idint(11)Primary Key, Auto-Increment
foodsaver_idint(11)
datatext(65535)Nullable
typevarchar(24)Nullable

Table fs_question

Description - fs_question

Questions (for quiz)

Table columns - fs_question

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
texttext(16777215)Nullable
durationunsigned int(3)
wikilinkvarchar(250)

Table fs_question_has_quiz

Description - fs_question_has_quiz

Relates questions <-> quiz

Open todos from old documentation - fs_question_has_quiz

  • remove broken data
  • FK to fs_quiz, fs_question, ON DELETE CASCADE

Table columns - fs_question_has_quiz

ColumnDescriptionTypeproperties
question_idunsigned int(10)Primary Key, foreign key (fs_question:id)
quiz_idunsigned int(10)Primary Key, foreign key (fs_quiz:id)
fpunsigned int(4)Nullable

Table fs_question_has_wallpost

Description - fs_question_has_wallpost

Relates wallposts to questions

Open todos from old documentation - fs_question_has_wallpost

  • remove broken data
  • FK to fs_question, fs_wallpost, ON DELETE CASCADE

Table columns - fs_question_has_wallpost

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

Table fs_quiz

Description - fs_quiz

Table columns - fs_quiz

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
namevarchar(200)Nullable
desctext(16777215)Nullable
maxfpunsigned int(6)
questcountunsigned int(6)

Table fs_quiz_session

Description - fs_quiz_session

Each try (by users) of a quiz (session)

Open todos from old documentation - fs_quiz_session

  • remove broken data

Table columns - fs_quiz_session

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
foodsaver_idunsigned int(10)foreign key (fs_foodsaver:id)
quiz_idunsigned int(10)
statusunsigned int(4)Nullable
quiz_indexunsigned int(4)Nullable
quiz_questionstext(16777215)Nullable
quiz_resulttext(16777215)Nullable
time_startdatetimeNullable
time_enddatetimeNullable
fpdecimalNullable
maxfpunsigned int(4)Nullable
quest_countunsigned int(4)Nullable
easymodeint(4)

Table fs_rating

Description - fs_rating

Stores trust bananas

Open todos from old documentation - fs_rating

  • remove broken data (as we don't want to have trust bananas from/for deleted users)
  • Add FK

Table columns - fs_rating

ColumnDescriptionTypeproperties
foodsaver_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
rater_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
msgtext(16777215)
timedatetime

Table fs_region_function

Description - fs_region_function

Table columns - fs_region_function

ColumnDescriptionTypeproperties
idint(11)Primary Key, Auto-Increment
region_idunsigned int(11)foreign key (fs_bezirk:id)
function_idunsigned int(11)
target_idint(11)Nullable

Table fs_region_options

Description - fs_region_options

Table columns - fs_region_options

ColumnDescriptionTypeproperties
region_idunsigned int(10)Primary Key, foreign key (fs_bezirk:id)
option_typecategory of the optionunsigned int(10)Primary Key
option_valuevalue of the optionvarchar(255)

Table fs_region_pin

Description - fs_region_pin

Table columns - fs_region_pin

ColumnDescriptionTypeproperties
region_idregion idunsigned int(10)Primary Key, foreign key (fs_bezirk:id)
latlatitudevarchar(20)
lonlongitudevarchar(20)
descdescriptiontext(16777215)
statusstate of the pinunsigned int(4)

Table fs_report

Description - fs_report

Table columns - fs_report

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
foodsaver_idunsigned int(10)
reporter_idunsigned int(10)Nullable
reporttypeunsigned int(4)Nullable
betrieb_idunsigned int(10)Nullable
timedatetimeNullable
committedunsigned int(4)Nullable
msgtext(16777215)Nullable
tvaluevarchar(300)Nullable

Table fs_report_has_wallpost

Description - fs_report_has_wallpost

Table columns - fs_report_has_wallpost

ColumnDescriptionTypeproperties
fsreport_idunsigned int(10)Primary Key, foreign key (fs_foodsaver:id)
wallpost_idunsigned int(10)Primary Key, foreign key (fs_wallpost:id)

Table fs_send_email

Description - fs_send_email

Table columns - fs_send_email

ColumnDescriptionTypeproperties
idunsigned int(10)Primary Key, Auto-Increment
foodsaver_idunsigned int(10)
mailbox_idunsigned int(10)
modeint(4)=1
completeint(4)
namevarchar(200)Nullable
messagetext(16777215)Nullable
zeitdatetimeNullable
reciptext(16777215)Nullable
attachvarchar(500)

Table fs_stat_abholmengen

Description - fs_stat_abholmengen

Stores statistics per store (maybe broken implementation?)

Open todos from old documentation - fs_stat_abholmengen

  • add FK to store

Table columns - fs_stat_abholmengen

ColumnDescriptionTypeproperties
betrieb_idunsigned int(10)Primary Key
datedatetimePrimary Key
abholmengedecimal

Table fs_store_log

Description - fs_store_log

Table columns - fs_store_log

ColumnDescriptionTypeproperties
idint(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 donevarchar(255)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

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)

Usage of table in PHP Modules

Activity

Application

Basket

Bell

Blog

Buddy

BusinessCard

Content

Dev

Email

Event

FoodSharePoint

Foodsaver

Group

Info

Login

Lookup

Mailbox

Mails

Maintenance

Map

Message

Migrate

PassportGenerator

Profile

PushNotification

Quiz

Region

Report

Settings

Statistics

Stats

Store

Team

Unclassified

Voting

WallPost

WorkGroup

Xhr