Structure-de-la-base-de-données

Schéma de la structure de la base de données

• Lien vers schéma fichier .png

Structure

create table account
(
    id               int unsigned auto_increment
        primary key,
    creation_date    date                          null,
    name             text                          null,
    export_account   varchar(255)                  null,
    activated        tinyint unsigned    default 1 not null,
    category         tinyint unsigned    default 0 null,
    account_type     tinyint unsigned    default 0 null,
    owner_id         int unsigned        default 0 not null,
    payment_allowed  tinyint(1)          default 0 null,
    budget_id        int unsigned                  null,
    order_num        int                           null,
    accounting_id    int unsigned                  null,
    deactivated_date datetime                      null,
    group_sales      tinyint(1)          default 0 not null
)
    comment 'list of account';

create index idx_owner_id
    on account (owner_id);

create table account_entry
(
    id                    int auto_increment
        primary key,
    flow_id               int                          null,
    account_date          datetime                     null,
    account_id            int                          null,
    credit                decimal(15, 5) default 0.00000 not null,
    debit                 decimal(15, 5) default 0.00000 not null,
    payment_type          int unsigned                 null,
    payment_description   text                         null,
    person_delivery_id    int unsigned                 null,
    comments              text                         null,
    validated             int            default 0     null,
    exported              int            default 0     null,
    registration_date     datetime                     null,
    budget_id             int unsigned                 null,
    product_id            int unsigned                 null,
    signature             varchar(56)                  null,
    signature_date        bigint unsigned              null,
    lettering             int                          null,
    lettering_date        datetime                     null,
    flow_reversal_id      int unsigned                 null,
    transaction_id        text                         null
)
    comment 'list of account entries';

create table account_link
(
    account_id        int unsigned default 0 not null,
    linked_account_id int unsigned default 0 not null,
    primary key (account_id, linked_account_id)
);

create table account_type
(
    id                     int unsigned auto_increment
        primary key,
    name                   varchar(255)                  null,
    category               tinyint unsigned    default 0 null,
    activated              tinyint(1)          default 1 null,
    order_num              int                           null,
    accounting_id          int unsigned                  null,
    export_account_pattern varchar(255)                  null
);

create table account_type_profile
(
    account_type_id int unsigned    not null,
    profile_id      bigint unsigned not null,
    primary key (account_type_id, profile_id)
)
    comment 'linked account type field and profile';

create table accounting
(
    id                int unsigned auto_increment
        primary key,
    name              varchar(255)           null,
    unit_name         varchar(255)           null,
    symbol            varchar(255)           null,
    format            tinyint unsigned       null,
    decimal_precision int unsigned default 2 null
);

create table accounting_profile
(
    accounting_id int unsigned    not null,
    profile_id    bigint unsigned not null,
    primary key (accounting_id, profile_id)
);

create table activity_type
(
    id                                 bigint unsigned not null
        primary key,
    name                               varchar(255)             null,
    order_num                          int                      null,
    activated                          tinyint(1)      default 1 not null,
    color                              int unsigned             null,
    training                           tinyint(1)      default 0 not null,
    previous_activity_type_id          bigint unsigned          null,
    previous_activity_default_duration int unsigned             null,
    next_activity_type_id              bigint unsigned          null,
    next_activity_default_duration     int unsigned             null
)
    comment 'list of flight type';

create table aircraft
(
    id                         int unsigned default 0 not null
        primary key,
    ref_date                   datetime               null,
    ref_hours                  int                    null,
    ref_engine_hours           int                    null,
    inspection_date            datetime               null,
    inspection_time            int                    null,
    inspection_engine_time     int                    null,
    tolerance_time             int unsigned default 6000 null,
    last_counter               int                    null,
    interval_visit_calendar    varchar(255) default '50 h' null,
    interval_visit_time        varchar(255) default '50 h' null,
    interval_visit_engine_time varchar(255) default '50 h' null,
    time_alert1                int          default 6000 null,
    time_alert2                int          default 0 null,
    time_alert3                int          default -3000 null,
    engine_time_alert1         int          default 6000 null,
    engine_time_alert2         int          default 0 null,
    engine_time_alert3         int          default 0 null,
    day_alert1                 smallint     default 15 null,
    day_alert2                 smallint     default 0 null,
    day_alert3                 smallint     default -8 null
)
    comment 'all aircrafts of all airclubs';

create table aircraft_type
(
    id                      int unsigned                 not null
        primary key,
    flight_time_formula     varchar(255) default '%duration' null,
    counter_state           tinyint      default -1      not null,
    tolerance               int          default 0       null,
    autonomy                int          default 5990    null,
    true_air_speed          int unsigned default 0       not null,
    digit_counter_number    tinyint unsigned default 4   not null,
    engine_duration_formula varchar(255)                 null
)
    comment 'types of aircraft';

create table aircraft_type_allowed_status
(
    aircraft_type_id int unsigned not null,
    place_num        int unsigned null,
    status_id        int unsigned not null
)
    comment 'list of allowed functions for each aircraft type';

create table aircraft_type_mandatory_flight_type
(
    aircraft_type_id int unsigned    not null,
    activity_type_id bigint unsigned not null
)
    comment 'list of compulsory flight type for each aircraft type';

create table aircraft_type_uncomp_flight_type
(
    aircraft_type_id int unsigned    not null,
    activity_type_id bigint unsigned not null
)
    comment 'list of uncompatible flight type for each aircraft type';

create table aircraft_type_validity_type
(
    aircraft_type_id int unsigned          not null,
    validity_type_id int unsigned          not null,
    check_num        int unsigned default 0 not null,
    primary key (aircraft_type_id, validity_type_id, check_num)
)
    comment 'types of qualif required for each aircraft type';

create table allocation_rule
(
    id         int unsigned auto_increment
        primary key,
    title      varchar(255)         not null,
    account_id int                  not null,
    keyword    varchar(255)         null,
    order_num  int                  not null,
    amount     tinyint(1)           not null,
    mandatory  tinyint(1) default 0 not null
);

create table balance
(
    account_id      int unsigned                   not null,
    balance_date_id int unsigned                   not null,
    debit           decimal(15, 5) default 0.00000 not null,
    credit          decimal(15, 5) default 0.00000 not null,
    primary key (account_id, balance_date_id)
);

create table balance_date
(
    id           int unsigned auto_increment
        primary key,
    balance_date datetime null
);

create table booking
(
    id               int unsigned auto_increment
        primary key,
    booking_group_id int unsigned null,
    start_date       datetime     null,
    end_date         datetime     null
)
    comment 'records all the slots of all aircrafts of all airclubs';

create table booking_account_entry
(
    booking_id            int unsigned default 0 not null,
    account_entry_flow_id int unsigned default 0 not null,
    primary key (booking_id, account_entry_flow_id)
);

create table booking_activity_type
(
    booking_id       int unsigned    not null,
    activity_type_id bigint unsigned not null,
    primary key (booking_id, activity_type_id)
);

create table booking_person
(
    booking_id int unsigned not null,
    person_id  int unsigned not null,
    place_num  int unsigned null,
    status_id  int unsigned null,
    primary key (booking_id, person_id)
);

create table booking_resource
(
    booking_id  int unsigned default 0 not null,
    resource_id int unsigned default 0 not null,
    primary key (booking_id, resource_id)
);

create table booking_training_program
(
    booking_group_id    int unsigned not null,
    training_program_id int unsigned not null,
    primary key (booking_group_id, training_program_id)
);

create table budget
(
    id          int unsigned auto_increment
        primary key,
    group_id    int unsigned not null,
    name        varchar(255) null,
    order_num   int          null,
    export_code varchar(255) null
);

create table budget_group
(
    id        int unsigned auto_increment
        primary key,
    name      varchar(255) null,
    order_num int          null
);

create table business_field
(
    id                       int unsigned auto_increment
        primary key,
    variable                 varchar(255)          null,
    label                    varchar(255)          not null,
    value_type               varchar(40)           not null,
    category                 varchar(255)          not null,
    order_num                int                   not null,
    dsn                      varchar(20) default 'customer' null,
    compulsory_fill          tinyint(1)  default 0 null,
    linked_category          varchar(255)          null,
    linked_business_field_id int unsigned          null,
    linked_field_name        varchar(255)          null,
    max_display              int         default -1 null,
    formula                  text                  null,
    default_value            text                  null,
    placeholder              text                  null,
    access_level_required    tinyint     default 0 not null,
    user_access_mode         tinyint(1)  default 0 not null,
    unique (variable)
)
    comment 'list of extra form field';

create table business_field_content
(
    category_id       int unsigned           not null,
    business_field_id int unsigned default 0 not null,
    person_id         int unsigned default 0 not null,
    content           text                   null,
    placeholder       text                   null,
    primary key (category_id, business_field_id, person_id)
)
    comment 'content of extra form field';

create table business_field_group
(
    id        int unsigned auto_increment
        primary key,
    label     varchar(255) null,
    order_num int          null
);

create table criteria
(
    id        int unsigned auto_increment
        primary key,
    label     varchar(255)                   not null,
    predicate text                           not null,
    order_num int                            null,
    dsn       varchar(20) default 'customer' null
);

create table criteria_profile
(
    criteria_id int unsigned    not null,
    profile_id  bigint unsigned not null,
    primary key (criteria_id, profile_id)
);

create table customer_bill_entry
(
    id                    int unsigned auto_increment
        primary key,
    flow_id               int unsigned                   null,
    account_entry_id      int unsigned                   null,
    account_entry_flow_id int unsigned                   null,
    owner_category        int unsigned                   null,
    owner_id              int unsigned                   null,
    customer_bill_id      int unsigned                   null,
    product_id            int unsigned                   null,
    qty                   decimal(15, 5) default 0.00000 null,
    unit_price            decimal(15, 5) default 0.00000 null,
    debit                 decimal(15, 5) default 0.00000 null,
    credit                decimal(15, 5) default 0.00000 null,
    bill_date             datetime                       null
);

create index idx_account_entry_flow_id
    on customer_bill_entry (account_entry_flow_id);

create index idx_account_entry_id
    on customer_bill_entry (account_entry_id);

create table customer_bill_file
(
    id        int unsigned auto_increment
        primary key,
    file_type varchar(255) null,
    content   longblob     null
);

create table customer_receipt_entry
(
    id                    int unsigned auto_increment
        primary key,
    flow_id               int unsigned                   null,
    account_entry_id      int unsigned                   null,
    account_entry_flow_id int unsigned                   null,
    owner_category        int unsigned                   null,
    owner_id              int unsigned                   null,
    payment_type_id       int unsigned                   null,
    customer_receipt_id   int unsigned                   null,
    debit                 decimal(15, 5) default 0.00000 null,
    credit                decimal(15, 5) default 0.00000 null,
    receipt_date          datetime                       null,
    owner_email           varchar(255)                   null
);

create index idx_account_entry_flow_id
    on customer_receipt_entry (account_entry_flow_id);

create index idx_account_entry_id
    on customer_receipt_entry (account_entry_id);

create table customer_receipt_file
(
    id        int unsigned auto_increment
        primary key,
    file_type varchar(255) null,
    content   longblob     null
);

create table data_retention
(
    id                    int unsigned auto_increment
        primary key,
    data_processing_title varchar(45)           not null,
    legal_basis_category  int unsigned default 0 not null,
    data_retention_period int unsigned          not null,
    controller_person_id  int unsigned          null
);

create table default_display
(
    person_id     int unsigned            not null,
    display_key   varchar(255) default '' not null,
    display_value text                    null,
    primary key (person_id, display_key)
);

create table default_multi_display
(
    resource_type char(30)     not null,
    person_id     int unsigned not null,
    resource_id   varchar(100) not null,
    display_value text         null,
    primary key (resource_type, person_id, resource_id)
);

create table email_sent
(
    id          bigint unsigned auto_increment
        primary key,
    message_id  varchar(150)     not null,
    sender      varchar(255)     not null,
    title       varchar(255)     not null,
    category    tinyint unsigned not null,
    category_id int unsigned     not null,
    unique (message_id)
);

create table email_sent_recipient
(
    id                   bigint unsigned auto_increment
        primary key,
    email_sent_id        bigint unsigned  not null,
    person_id            int unsigned     not null,
    email                varchar(255)     null,
    recipient_type       tinyint unsigned null,
    smtp_status_category varchar(30)      null,
    email_status         tinyint unsigned null,
    update_date          datetime         not null,
    smtp_additional_detail text             null
);

create index email_sent_id_idx
    on email_sent_recipient (email_sent_id);

create table exceptionnal_inst_date
(
    id         int unsigned auto_increment
        primary key,
    person_id  int unsigned        not null,
    start_date datetime            null,
    end_date   datetime            null,
    presence   tinyint unsigned    null
);

create table extra_field_profile
(
    business_field_id                  int unsigned    not null,
    booking_popup_display_4_profile_id bigint unsigned not null,
    primary key (business_field_id, booking_popup_display_4_profile_id)
);

create table facebook
(
    id          int unsigned auto_increment
        primary key,
    category    int unsigned not null,
    owner_id    int unsigned not null,
    small       blob         null,
    original    mediumblob   not null,
    label       text         null,
    description text         null
);

create table favorite_icao
(
    icao varchar(6) default '' not null
        primary key
)
    comment 'favorite airfield list';

create table favorite_report
(
    report_id int unsigned not null
        primary key
);

create table favorite_report_business_field
(
    report_id         int unsigned not null,
    business_field_id int unsigned not null,
    default_value     text         null,
    primary key (report_id, business_field_id)
);

create table favorite_report_profile
(
    report_id  int unsigned              not null,
    profile_id bigint unsigned default 0 not null,
    primary key (report_id, profile_id)
);

create table fhp_aircraft_type
(
    fhp_id           int default 0 not null,
    aircraft_type_id int default 0 not null,
    primary key (fhp_id, aircraft_type_id)
)
    comment 'flight hour pricing aircraft type list';

create table fhp_flight_type
(
    fhp_id           int                 default 0 not null,
    activity_type_id bigint unsigned     default 0 not null,
    excluded         tinyint unsigned    default 0 null,
    primary key (fhp_id, activity_type_id)
)
    comment 'flight hour pricing flight type list';

create table fhp_profile
(
    fhp_id     int unsigned               not null,
    profile_id bigint unsigned default 0  not null,
    place_num  tinyint unsigned default 0 not null,
    primary key (fhp_id, profile_id, place_num)
);

create table file
(
    id            int unsigned auto_increment
        primary key,
    name          varchar(255)        null,
    content       longblob            null,
    file_type     varchar(255)        null,
    category      tinyint unsigned    null,
    owner_id      int                 null,
    file_group_id int                 null
);

create table file_group
(
    id    int unsigned auto_increment
        primary key,
    label varchar(255) null
);

create table flight
(
    id                    int unsigned auto_increment
        primary key,
    aircraft_id           int unsigned               null,
    start_date            datetime                   null,
    duration              int                        null,
    activity_type_id      bigint unsigned            null,
    people_onboard        int unsigned               null,
    departure_location_id int unsigned               null,
    arrival_location_id   int unsigned               null,
    counter_departure     int unsigned               null,
    counter_arrival       int unsigned               null,
    landing_number        int                        null,
    airborne              tinyint unsigned           null,
    validated             tinyint unsigned default 0 null,
    departure_icao_id     varchar(6)                 null,
    arrival_icao_id       varchar(6)                 null,
    engine_duration       int                        null
)
    comment 'list of flight';

create table flight_account_entry
(
    flight_id        int unsigned default 0 not null,
    account_entry_id int unsigned default 0 not null,
    primary key (flight_id, account_entry_id)
)
    comment 'flight account entry join';

create index idx_account_entry_id
    on flight_account_entry (account_entry_id);

create table flight_hours_pricing
(
    id                 int unsigned auto_increment
        primary key,
    name               text                          null,
    price_formula      text                          null,
    left_account_id    int unsigned                  null,
    right_account_id   int unsigned                  null,
    left_account_type  tinyint unsigned    default 0 null,
    right_account_type tinyint unsigned    default 0 null,
    credit_budget_id   int unsigned                  null,
    debit_budget_id    int unsigned                  null,
    order_num          int                           null,
    product_id         int unsigned                  null,
    sale_trigger_id    int unsigned        default 0 null,
    query              text                          null,
    variable_formula   varchar(255)                  null,
    business_field_id  int unsigned                  null,
    debit_bill_num     int unsigned                  null,
    credit_bill_num    int unsigned                  null,
    qty_formula        text                          null,
    unit_price_formula text                          null
)
    comment 'list of flight hours pricing formula';

create table flight_pilot
(
    flight_id int unsigned           not null,
    pilot_id  int unsigned           not null,
    status_id int unsigned           null,
    num       int unsigned default 0 not null,
    primary key (flight_id, pilot_id, num)
)
    comment 'list of crew for each flight';

create table flight_tank_qty
(
    id           int unsigned auto_increment
        primary key,
    flight_id    int unsigned                not null,
    tank_id      int unsigned                not null,
    quantity     varchar(255) default '0.00' not null,
    after_flight tinyint(1)   default 0      not null,
    account_id   int unsigned                null,
    pay_type     tinyint unsigned            null
);

create table flight_track
(
    id        int unsigned auto_increment
        primary key,
    flight_id int                     not null,
    track     mediumblob              not null,
    file_type varchar(255) default 'kml' null
);

create table flight_type_mandatory_validity_type
(
    activity_type_id bigint unsigned           not null,
    validity_type_id int unsigned    default 0 not null,
    primary key (activity_type_id, validity_type_id)
)
    comment 'list of mandatory qualification for each flight type';

create table import
(
    id                  int unsigned auto_increment
        primary key,
    label               varchar(255)                          not null,
    order_num           int                                   null,
    import_file_type    varchar(255) default 'csv-comma-CRLF' not null,
    match_query         text                                  null,
    uptodate_test_query text                                  null,
    update_query        text                                  null,
    update_activated    tinyint(1)   default 0                not null,
    login               varchar(255)                          null,
    hash_password       varchar(255)                          null,
    sync_task_name      varchar(255)                          null
);

create table ip_stopped
(
    id          int unsigned auto_increment
        primary key,
    ip          varchar(255)        not null,
    counter     tinyint unsigned    not null,
    expire_date datetime            not null
)
    comment 'blacklisted ip';

create table journal
(
    id        int unsigned auto_increment
        primary key,
    login     varchar(255)           null,
    date_log  datetime               null,
    rights    text                   null,
    rights2   text                   null,
    action    varchar(255)           null,
    person_id int unsigned default 0 null
)
    comment 'list of logs';

create index idx_action
    on journal (action);

create index idx_date_log
    on journal (date_log);

create table key_alert
(
    id         int unsigned auto_increment
        primary key,
    alert_date datetime               null,
    status     int unsigned default 0 not null
)
    comment 'key alerts';

create table key_assignment
(
    key_id      tinyint unsigned    default 0 not null
        primary key,
    key_name    tinytext                      null,
    aircraft_id int unsigned        default 0 null,
    key_state   tinyint unsigned    default 0 null,
    key_word    bigint unsigned     default 0 null
)
    comment 'key assignment description';

create table key_host
(
    id       tinyint auto_increment
        primary key,
    timeout  tinyint             default 10          not null,
    num_key  tinyint unsigned    default 8           not null,
    ipkey    varchar(50)         default '127.0.0.1' not null,
    httpport int                 default 4080        not null
)
    comment 'key host configuration';

create table key_log
(
    id        int unsigned auto_increment
        primary key,
    log_date  datetime     null,
    action    varchar(255) null,
    message   varchar(255) null,
    key_id    int unsigned null,
    person_id int unsigned null,
    xmlrpc    int unsigned null,
    error     varchar(255) null
)
    comment 'key logs';

create table location
(
    icao_name       varchar(6)       not null
        primary key,
    name            varchar(64)      not null,
    latitude        double           null,
    longitude       double           null,
    altitude        int              null,
    weather_station int unsigned     null,
    asked_counter   bigint default 0 not null
)
    comment 'airfields coord';

create table log
(
    journal_id  int unsigned not null,
    action      varchar(255) null,
    table_name  varchar(255) null,
    field_name  varchar(255) null,
    field_value varchar(255) null
)
    comment 'part of logs';

create index idx_field_value
    on log (field_value);

create index idx_journal_id
    on log (journal_id);

create index journal_id
    on log (journal_id);

create table logger
(
    id            int unsigned auto_increment
        primary key,
    serial_number int unsigned                      null,
    color         varchar(20)         default 'red' not null,
    activated     tinyint unsigned    default 1     not null,
    order_num     int unsigned                      not null
)
    comment 'logger parameters';

create table login_stopped
(
    id          int unsigned auto_increment
        primary key,
    login       varchar(255)     not null,
    counter     tinyint unsigned not null,
    expire_date datetime         not null
)
    comment 'blacklisted login';

create table m_component
(
    id                  int unsigned auto_increment
        primary key,
    parent_id           int unsigned        default 0 not null,
    m_component_type_id int unsigned                  null,
    resource_id         int unsigned                  null,
    order_num           int                           not null,
    serial_number       varchar(255)                  null,
    brandnew_date       datetime                      null,
    activated           tinyint unsigned    default 1 not null
);

create table m_component_type
(
    id                     int unsigned auto_increment
        primary key,
    parent_id              int unsigned        default 0 not null,
    resource_type_id       int                           null,
    m_classification_id    int                           null,
    is_maintenance_check   tinyint unsigned    default 1 not null,
    order_num              int                           not null,
    label                  varchar(255)                  null,
    description            varchar(255)                  null,
    manufacturer           varchar(255)                  null,
    manufacturer_reference varchar(255)                  null,
    part_number            varchar(255)                  null,
    periodicity            int unsigned                  null,
    tolerance              int unsigned        default 0 not null,
    calendar_periodicity   int unsigned                  null,
    calendar_tolerance     int unsigned        default 0 not null,
    activated              tinyint unsigned    default 1 not null
);

create table m_component_type_parentality
(
    m_component_type_id        int unsigned not null,
    m_component_type_parent_id int unsigned not null,
    primary key (m_component_type_id, m_component_type_parent_id)
);

create table m_history
(
    id                     int unsigned auto_increment
        primary key,
    m_component_id         int unsigned                  not null,
    install_date           datetime                      null,
    remove_date            datetime                      null,
    total_hours_on_install int unsigned        default 0 not null,
    threshold_hours        int unsigned        default 0 not null,
    threshold_date         datetime                      null,
    threshold_date_locked  tinyint unsigned    default 0 not null,
    threshold_hours_locked tinyint unsigned    default 0 not null
);

create table m_operation
(
    id                  int unsigned auto_increment
        primary key,
    content             text         not null,
    m_component_type_id int          null,
    order_num           int          not null
);

create table maintenance_history
(
    id                                   int unsigned auto_increment
        primary key,
    resource_id                          int unsigned                  null,
    maintenance_program_id               int unsigned                  null,
    frame_hours_at_work_start            int                           null,
    date_work_end                        date                          null,
    min_overhaul_counter                 int                           null,
    max_overhaul_counter                 int                           null,
    min_overhaul_date                    date                          null,
    max_overhaul_date                    date                          null,
    min_next_overhaul_counter            int                           null,
    max_next_overhaul_counter            int                           null,
    min_next_overhaul_date               date                          null,
    max_next_overhaul_date               date                          null,
    first_reference_visit                tinyint unsigned default 0    not null,
    is_visit                             tinyint unsigned default 0    not null,
    action_type                          tinyint unsigned              null,
    previous_line_linked_time_action     int                           null,
    previous_line_linked_calendar_action int                           null,
    next_line_linked_time_action         int                           null,
    next_line_linked_calendar_action     int                           null,
    time_tolerance                       int                           null,
    calendar_tolerance                   int                           null,
    time_periodicity_component           int                           null,
    calendar_periodicity_component       int                           null,
    time_periodicity                     int                           null,
    calendar_periodicity                 int                           null,
    reference_overhaul_counter           int                           null,
    reference_overhaul_date              date                          null
);

create table maintenance_program
(
    id                                    int unsigned auto_increment
        primary key,
    resource_id                           int unsigned                  null,
    component_type_id                     int unsigned                  null,
    description                           varchar(255)                  null,
    time_periodicity                      int                           null,
    calendar_periodicity                  int                           null,
    time_first_reference                  int                           null,
    calendar_first_reference              date                          null,
    time_tolerance                        int                           null,
    calendar_tolerance                    int                           null,
    frame_hours_at_work_start             int                           null,
    date_work_end                         date                          null,
    time_elapsed                          int                           null,
    calendar_elapsed                      int                           null,
    time_remaining                        int                           null,
    calendar_remaining                    int                           null,
    min_next_overhaul_counter             int                           null,
    max_next_overhaul_counter             int                           null,
    min_next_overhaul_date                date                          null,
    max_next_overhaul_date                date                          null,
    effective_hours_at_work_start         int                           null,
    theoretical_overhaul_counter          int                           null,
    intelligent_overhaul_counter          int                           null,
    intelligent_min_next_overhaul_counter int                           null,
    intelligent_max_next_overhaul_counter int                           null,
    effective_date_at_work_end            date                          null,
    theoretical_overhaul_date             date                          null,
    intelligent_overhaul_date             date                          null,
    intelligent_min_next_overhaul_date    date                          null,
    intelligent_max_next_overhaul_date    date                          null,
    overlapping_group_id                  int unsigned                  null,
    is_visit_rg                           tinyint unsigned default 0    not null
);

create table maintenance_view
(
    id                                                int unsigned default 0 not null
        primary key,
    frame_total_time                                  int                    null,
    frame_time_since_rg                               int                    null,
    engine_time_since_rg                              int                    null,
    propeller_time_since_rg                           int                    null,
    potential_remaining_next_time_action              int                    null,
    potential_remaining_next_time_visit               int                    null,
    potential_remaining_next_priority_time_action     int                    null,
    potential_remaining_next_calendar_action          int                    null,
    potential_remaining_next_calendar_visit           int                    null,
    potential_remaining_next_priority_calendar_action int                    null,
    next_time_action_id                               int unsigned           null,
    next_time_visit_id                                int unsigned           null,
    next_priority_time_action_id                      int unsigned           null,
    next_calendar_action_id                           int unsigned           null,
    next_calendar_visit_id                            int unsigned           null,
    next_priority_calendar_action_id                  int unsigned           null,
    overhaul_counter_action                           int                    null,
    overhaul_counter_visit                            int                    null,
    overhaul_date_action                              date                   null,
    overhaul_date_visit                               date                   null
);

create table map_logger_monitoring
(
    record_id  int unsigned not null
        primary key,
    count_data int unsigned not null
)
    comment 'link between flight and track';

create table map_track
(
    id            int unsigned auto_increment
        primary key,
    record_id     int unsigned                  null,
    start_data    int unsigned                  null,
    number        int unsigned                  null,
    start_time    datetime                      null,
    latitude_max  double                        null,
    longitude_max double                        null,
    latitude_min  double                        null,
    longitude_min double                        null,
    visible       tinyint unsigned default 1    not null
)
    comment 'tracks split from gdr';

create table nationality
(
    code  char(2) default '' not null
        primary key,
    label varchar(255)       not null
);

create table oauth_access_token
(
    id               int unsigned auto_increment
        primary key,
    uid              varchar(255)    not null,
    expiry_date_time datetime        not null,
    scopes           varchar(2048)   not null,
    oauth_client_id  int unsigned    not null,
    person_id        int unsigned    null,
    unique (id),
    unique (uid)
);

create table oauth_auth_code
(
    id               int unsigned auto_increment
        primary key,
    uid              varchar(255)    not null,
    expiry_date_time datetime        not null,
    redirect_uri     varchar(2048)   not null,
    scopes           varchar(2048)   not null,
    oauth_client_id  int unsigned    not null,
    person_id        int unsigned    not null,
    unique (id),
    unique (uid)
);

create table oauth_client
(
    id                       int unsigned auto_increment
        primary key,
    uid                      varchar(255)    not null,
    hashed_passphrase        varchar(255)    null,
    name                     text            not null,
    authorization_grant_type varchar(255)    not null,
    redirect_uri             varchar(2048)   not null,
    owner_person_id          int unsigned    not null,
    auth_cert                text            null,
    sign_cert                text            null,
    unique (id),
    unique (uid)
);

create table oauth_dpop_token
(
    id              int unsigned auto_increment
        primary key,
    jti             varchar(255) not null,
    expiration_date datetime     not null
);

create table oauth_refresh_token
(
    id                     int unsigned auto_increment
        primary key,
    uid                    varchar(255)    not null,
    expiry_date_time       datetime        not null,
    oauth_access_token_uid varchar(255)    not null,
    scopes                 varchar(2048)   not null,
    oauth_client_id        int unsigned    not null,
    person_id              int unsigned    not null,
    dpop_jkt               varchar(128)    null,
    unique (id),
    unique (uid)
);

create table parameter
(
    code       varchar(255)                  not null,
    key_id     int unsigned     default 0    not null,
    enabled    tinyint unsigned default 0    null,
    int_value  bigint unsigned  default 0    not null,
    char_value mediumtext                    null,
    primary key (code, key_id)
)
    comment 'application parameters';

create table payment_summary_file
(
    id              int unsigned auto_increment
        primary key,
    file_type       varchar(255)            null,
    content         longblob                null,
    record_date     datetime                null,
    total_entry     int unsigned            null,
    total_amount    decimal(15, 5) unsigned null,
    payment_type_id int unsigned            null
);

create table payment_type
(
    id                  int unsigned auto_increment
        primary key,
    name                text                          null,
    text_field_label    text                          null,
    only_admin          tinyint unsigned default 0    not null,
    pos_key_id          int unsigned                  null,
    treasury_account_id int unsigned                  null,
    person_delivery     tinyint unsigned default 0    null,
    person_budget_id    int unsigned                  null,
    treasury_budget_id  int unsigned                  null,
    minimum_amount      decimal(10, 0)                null,
    maximum_amount      decimal(10, 0)                null
)
    comment 'type of payment description';

create table person
(
    id                     int unsigned auto_increment
        primary key,
    name                   varchar(255)                                      not null,
    hash_password          varchar(255)                                      not null,
    first_name             varchar(255)                                      null,
    last_name              varchar(255)                                      null,
    profile                bigint unsigned                                   null,
    view_type              int unsigned                                      null,
    view_width             tinyint unsigned    default 12                    not null,
    view_height            tinyint unsigned    default 30                    not null,
    email                  varchar(255)                                      null,
    timezone               varchar(255)                                      null,
    address                varchar(255)                                      null,
    zipcode                varchar(255)                                      null,
    city                   varchar(255)                                      null,
    state                  varchar(255)                                      null,
    country                varchar(255)                                      null,
    home_phone             varchar(255)                                      null,
    work_phone             varchar(255)                                      null,
    cell_phone             varchar(255)                                      null,
    lang                   varchar(255)                                      null,
    notification           bigint unsigned                                   null,
    activated              tinyint unsigned    default 1                     null,
    birthdate              datetime            default '0000-00-00 00:00:00' null,
    sex                    tinyint unsigned    default 0                     not null,
    nationality            char(2)                                           null,
    total_flight_time      int unsigned        default 0                     null,
    date_total_flight_time datetime                                          null,
    guid                   varchar(255)                                      null,
    activity_notification  bigint unsigned                                   null,
    unique (name)
)
    comment 'used for authentication';

create table person_awaiting_activation
(
    id              int unsigned auto_increment
        primary key,
    login           varchar(255)        not null,
    ip              varchar(255)        not null,
    code            varchar(255)        not null,
    category        varchar(255)        not null,
    expiration_date datetime            null,
    used            tinyint unsigned    null
);

create table product
(
    id                  int unsigned auto_increment
        primary key,
    label               varchar(255)                  not null,
    unit                varchar(255)                  not null,
    free_sale           tinyint(1)          default 0 not null,
    locked              tinyint unsigned    default 0 null,
    variable_id         int                           null,
    sale_type           tinyint unsigned              null,
    is_external_product tinyint(1)                    null
);

create table profile
(
    id                bigint unsigned not null
        primary key,
    name              varchar(255)              null,
    permits           int unsigned              null,
    permits2          int unsigned    default 0 not null,
    permits3          int unsigned    default 0 not null,
    pictogram         int unsigned              null,
    default_status_id int unsigned              null
);

create table profile_accounting_notification
(
    profile_id    bigint unsigned not null,
    accounting_id int unsigned    not null,
    primary key (profile_id, accounting_id)
);

create table profile_extra_field_join
(
    profile_id        bigint unsigned not null,
    business_field_id int unsigned    not null,
    primary key (profile_id, business_field_id)
);

create table profile_profile_view
(
    profile_id          bigint unsigned not null,
    viewable_profile_id bigint unsigned not null,
    primary key (profile_id, viewable_profile_id)
);

create table profile_required_account_type
(
    profile_id      bigint unsigned not null,
    account_type_id int unsigned    not null,
    primary key (profile_id, account_type_id)
);

create table profile_resource_type_place
(
    profile_id       bigint unsigned not null,
    resource_type_id int unsigned    not null,
    place_num        int unsigned    not null,
    primary key (profile_id, resource_type_id, place_num)
);

create table profile_resource_type_view
(
    profile_id                bigint unsigned not null,
    viewable_resource_type_id int unsigned    not null,
    primary key (profile_id, viewable_resource_type_id)
);

create table profile_validity_type_join
(
    profile_id        bigint unsigned           not null,
    validity_type_id  int unsigned              not null,
    manage4oneself    int             default 0 not null,
    certify           int             default 0 not null,
    optional_contract tinyint(1)      default 0 not null,
    primary key (profile_id, validity_type_id)
);

create table profile_validity_type_notification
(
    profile_id       bigint unsigned not null,
    validity_type_id int unsigned    not null,
    primary key (profile_id, validity_type_id)
);

create table psp_return
(
    id                 int unsigned auto_increment
        primary key,
    psp_transaction_id int unsigned null,
    bank_answer        text         null,
    bank_misc          text         null
);

create table psp_transaction
(
    id                int unsigned auto_increment
        primary key,
    credit_account_id int unsigned           not null,
    debit_account_id  int unsigned           not null,
    transaction_date  datetime               not null,
    amount            float                  not null,
    description       varchar(255)           null,
    payment_type_id   int unsigned           not null,
    state             int unsigned default 0 not null,
    token             text                   null
);

create table psp_transaction_account_entry
(
    psp_transaction_id    int unsigned default 0 not null,
    account_entry_flow_id int unsigned default 0 not null,
    primary key (psp_transaction_id, account_entry_flow_id)
);

create table record_changelog
(
    id             int unsigned auto_increment
        primary key,
    record_id      int unsigned not null,
    record_type    varchar(255) null,
    person_id      int unsigned null,
    changelog_date datetime     not null,
    record_state   text         null
);

create table regular_presence_inst_date
(
    id         int unsigned auto_increment
        primary key,
    person_id  int unsigned     null,
    start_day  tinyint unsigned null,
    end_day    tinyint unsigned null,
    start_hour time             null,
    end_hour   time             null
);

create table resource
(
    id               int unsigned auto_increment
        primary key,
    name             varchar(255)                  null,
    resource_type_id int unsigned                  null,
    comments         varchar(255)                  null,
    order_num        int                           null,
    activated        tinyint unsigned    default 1 not null,
    bookable         int unsigned        default 1 null,
    physical         int unsigned        default 1 null,
    color            int unsigned                  null
);

create table resource_exceptional_availability
(
    id          int unsigned auto_increment
        primary key,
    resource_id int unsigned        null,
    start_date  datetime            null,
    end_date    datetime            null,
    presence    tinyint unsigned    null
);

create table resource_maintenance_alert
(
    id             int unsigned auto_increment
        primary key,
    aircraft_id    int unsigned          not null,
    alert_category tinyint unsigned      not null,
    alert_level    tinyint unsigned      not null,
    is_enabled     tinyint unsigned default 0 not null,
    is_sent        tinyint unsigned default 0 not null,
    unique (aircraft_id, alert_category, alert_level)
);

create table resource_regular_availability
(
    id          int unsigned auto_increment
        primary key,
    resource_id int unsigned        null,
    start_day   tinyint unsigned    null,
    end_day     tinyint unsigned    null,
    start_hour  time                null,
    end_hour    time                null
);

create table resource_type
(
    id                   int unsigned auto_increment
        primary key,
    name                 varchar(255)                   null,
    category             int unsigned                   null,
    seats_available      int                 default -1 null,
    comments             varchar(255)                   null,
    activated            tinyint unsigned    default 1  not null,
    max_booking_duration int                 default -1 null,
    pictogram            int unsigned                   null
);

create table resource_type_place_tag
(
    resource_type_id int unsigned        not null,
    place_num        tinyint unsigned    not null,
    place_tag        varchar(255)        null,
    place_quantity   int      default -1 null,
    primary key (resource_type_id, place_num)
);

create table sale_2_stock
(
    id                           int unsigned auto_increment
        primary key,
    product_id                   int unsigned    not null,
    stock_id                     int unsigned    not null,
    stock_variation_qty_per_sale float default 0 not null
);

create table sale_2_validity_type
(
    id               int unsigned auto_increment
        primary key,
    validity_type_id int unsigned not null,
    new_formula      varchar(255) null,
    update_formula   varchar(255) null,
    product_id       int unsigned not null,
    unique (validity_type_id)
);

create index idx_product
    on sale_2_validity_type (product_id);

create table sale_pricing
(
    id                      int unsigned auto_increment
        primary key,
    label                   varchar(255)                  null,
    price_formula           text                          null,
    debit_account_id        int unsigned                  null,
    credit_account_id       int unsigned                  null,
    debit_account_type      tinyint unsigned    default 0 null,
    credit_account_type     tinyint unsigned    default 0 null,
    credit_budget_id        int unsigned                  null,
    debit_budget_id         int unsigned                  null,
    order_num               int                           null,
    variable_formula        varchar(255)                  null,
    debit_bill_num          int unsigned                  null,
    credit_bill_num         int unsigned                  null,
    qty_formula             text                          null,
    unit_price_formula      text                          null,
    added_product_id        int unsigned                  null,
    is_for_external_product tinyint(1)          default 1 null,
    is_for_internal_product tinyint(1)          default 1 null
);

create table sale_pricing_product
(
    sale_pricing_id int unsigned default 0 not null,
    product_id      int unsigned default 0 not null,
    primary key (sale_pricing_id, product_id)
);

create table sale_pricing_profile
(
    sale_pricing_id int unsigned              not null,
    profile_id      bigint unsigned default 0 not null,
    primary key (sale_pricing_id, profile_id)
);

create table sale_trigger
(
    id             int                 default 0 not null
        primary key,
    name           varchar(255)                  null,
    event          varchar(255)                  null,
    locked         tinyint unsigned    default 0 not null,
    query          text                          null,
    validate_entry tinyint(1)          default 0 null,
    group_sales    tinyint(1)          default 0 null
);

create table spreadsheet_parameter
(
    file_number       int          not null,
    spreadsheet_key   varchar(255) not null,
    spreadsheet_value varchar(255) not null,
    file_name         varchar(255) null,
    google_url        varchar(255) not null,
    file_type         int unsigned null,
    primary key (file_number, spreadsheet_key, spreadsheet_value)
);

create table statistic
(
    name       varchar(255)                not null,
    of_version tinyint        default 0    not null,
    value      decimal(15, 2) default 0.00 null,
    primary key (name, of_version)
);

create table status
(
    id                      int unsigned auto_increment
        primary key,
    abbrev                  varchar(255) null,
    name                    varchar(255) null,
    pictogram               int unsigned null,
    carnet_aero_function_id int unsigned null
)
    comment 'list of functions for pilot';

create table stock
(
    id            int unsigned auto_increment
        primary key,
    label         varchar(255) not null,
    stock_type_id int unsigned not null
);

create table stock_level
(
    id         int unsigned auto_increment
        primary key,
    stock_id   int unsigned    not null,
    qty        float default 0 not null,
    stock_date datetime        null
);

create table stock_type
(
    id    int unsigned auto_increment
        primary key,
    label varchar(255) not null,
    unit  varchar(255) not null
);

create table stock_variation
(
    id             int unsigned auto_increment
        primary key,
    stock_id       int unsigned               not null,
    qty            float             default 0 not null,
    variation_date datetime                   null,
    validated      tinyint unsigned  default 0 null
);

create table stock_variation_account_entry
(
    product_id            int unsigned default 0 not null,
    stock_variation_id    int unsigned           not null,
    account_entry_flow_id int unsigned           not null,
    person_id             int unsigned           not null,
    primary key (product_id, stock_variation_id, account_entry_flow_id)
);

create index idx_account_entry_flow_id
    on stock_variation_account_entry (account_entry_flow_id);

create table structure
(
    id                            int              default 0 not null
        primary key,
    name                          varchar(255)               null,
    info_cell                     text                       null,
    logo                          longblob                   null,
    logo_name                     varchar(255)               null,
    logo_ext                      varchar(25)                null,
    logo_size                     int                        null,
    first_hour_displayed          time                       null,
    last_hour_displayed           time                       null,
    usual_profiles                bigint unsigned            null,
    icao                          varchar(6)                 null,
    default_slot_range            int unsigned               null,
    min_slot_range                tinyint unsigned           null,
    twilight_range                tinyint unsigned           null,
    mailing_list_name             varchar(255)               null,
    mailing_list_type             varchar(255)               null,
    structure_site_url            varchar(255)               null,
    default_timezone              varchar(255)               not null,
    lang                          varchar(255)               not null,
    admin_num                     int unsigned               not null,
    default_view_type             int unsigned               null,
    address                       varchar(255)               null,
    zipcode                       varchar(255)               null,
    city                          varchar(255)               null,
    state                         varchar(255)               null,
    country                       varchar(255)               null,
    phone                         varchar(255)               null,
    fax                           varchar(255)               null,
    email                         varchar(255)               null,
    default_notification          int unsigned               null,
    welcome_cell                  text                       null,
    business                      text                       null,
    default_activity_notification bigint unsigned            null,
    siren                         int                        null
)
    comment 'description of all airclubs';

create table supplier_bill
(
    id                    int unsigned auto_increment
        primary key,
    supplier_bill_type_id int unsigned         not null,
    bill_date             datetime             null,
    description           varchar(255)         null,
    ordinal               int unsigned         not null,
    validated             tinyint(1) default 0 null,
    supplier_bill_file_id int                  null
);

create table supplier_bill_account_entry
(
    supplier_bill_id      int unsigned not null,
    account_entry_flow_id int unsigned not null,
    primary key (supplier_bill_id, account_entry_flow_id)
);

create index idx_account_entry_flow_id
    on supplier_bill_account_entry (account_entry_flow_id);

create table supplier_bill_email_parsed
(
    id                    int auto_increment
        primary key,
    uid                   int          null,
    email_address         varchar(255) null,
    supplier_bill_file_id int unsigned not null
);

create table supplier_bill_file
(
    id        int unsigned auto_increment
        primary key,
    file_type varchar(255) null,
    content   longblob     null,
    name      varchar(255) not null
);

create table supplier_bill_type
(
    id                  int unsigned auto_increment
        primary key,
    supplier_account_id int unsigned not null,
    name                varchar(255) null,
    account_id          int unsigned not null,
    vat_account_id      int unsigned null,
    supplier_budget_id  int unsigned null,
    account_budget_id   int unsigned null,
    vat_budget_id       int unsigned null,
    rule                text         null,
    sender_email        varchar(255) null,
    subject             varchar(255) null,
    supplier_name       varchar(255) default null,
    search_keyword      varchar(255) default null
);

create table table_trigger
(
    id              int unsigned auto_increment
        primary key,
    monitored_table varchar(255) not null,
    trigger_formula varchar(255) not null
);

create table tank
(
    id                 int unsigned auto_increment
        primary key,
    aircraft_type_id   int unsigned                 not null,
    tank_type_id       int unsigned                 not null,
    unit_id            int unsigned                 not null,
    label              varchar(255)                 not null,
    max_quantity       decimal(15, 2) default -1.00 not null,
    unlimited_quantity int unsigned   default 1     null
);

create table track_record
(
    id                     int unsigned auto_increment
        primary key,
    in_progress            tinyint unsigned                       null,
    start_date             datetime default '0000-00-00 00:00:00' null,
    activated              tinyint unsigned                       null,
    datechsys_track_id     int unsigned                           null,
    resource_cat           int unsigned                           not null,
    resource_id            int unsigned                           not null,
    additional_information int unsigned                           null
);

create table track_record_data
(
    id            int unsigned auto_increment
        primary key,
    record_id     int unsigned        not null,
    record_date   datetime            null,
    longitude     double              null,
    latitude      double              null,
    altitude      int                 null,
    speed         double              null,
    track         int unsigned        null,
    acc_x         int                 null,
    acc_y         int                 null,
    acc_z         int                 null,
    pressure      int unsigned        null,
    battery_level int unsigned        null,
    gps_fix       tinyint unsigned    null,
    receive_date  datetime            null
);

create index idx_record_id
    on track_record_data (record_id);

create table track_resource
(
    id   int unsigned auto_increment
        primary key,
    name varchar(255) not null
);

create table training
(
    id   int unsigned auto_increment
        primary key,
    name varchar(255) not null
);

create table training_activity
(
    activity_id            int unsigned             not null,
    training_item_label_id int unsigned   default 0 not null,
    training_id            int unsigned   default 1 not null,
    level_reached          int unsigned             null,
    comment                varchar(255)             null,
    validated              tinyint(1)     default 0 not null,
    primary key (activity_id, training_item_label_id, training_id)
);

create table training_activity_next_item
(
    student_id             int unsigned              not null,
    training_id            int unsigned              not null,
    training_item_label_id int unsigned              not null,
    reporting_offset       tinyint unsigned default 0 not null,
    item_preselected       tinyint unsigned default 0 not null,
    primary key (student_id, training_id, training_item_label_id)
);

create table training_activity_person
(
    activity_id                int unsigned           not null,
    person_id                  int unsigned           not null,
    num                        int unsigned default 0 not null,
    training_checking_date     datetime               null,
    training_checking_sentence varchar(255)           null,
    primary key (activity_id, person_id, num)
);

create table training_item
(
    training_id            int unsigned not null,
    training_item_label_id int unsigned not null,
    number_of_sessions     int          not null,
    order_num              int          not null,
    custom_label           varchar(255) null,
    primary key (training_id, training_item_label_id)
);

create table training_phase
(
    training_item_order_num int unsigned not null,
    training_id             int unsigned not null,
    name                    varchar(255) null,
    primary key (training_item_order_num, training_id)
);

create table training_program
(
    id          int unsigned auto_increment
        primary key,
    training_id int unsigned null,
    name        varchar(255) not null
);

create table training_program_activity
(
    id                  int unsigned auto_increment
        primary key,
    training_program_id int unsigned    not null,
    activity_type_id    bigint unsigned not null,
    duration            int unsigned    null,
    breaktime           int unsigned    null,
    order_num           int unsigned    not null
);

create table uncomp_flight_type
(
    id1 bigint unsigned not null,
    id2 bigint unsigned not null
)
    comment 'list of sales';

create table user_reauth_token
(
    id                 int unsigned auto_increment
        primary key,
    user_id            int unsigned         not null,
    verification_token varchar(255)         not null,
    ip_address         varchar(45)          not null,
    expiration_date    datetime             not null,
    activated          tinyint(1) default 1 not null
);

create table validity
(
    id                  int unsigned auto_increment
        primary key,
    person_id           int unsigned         not null,
    validity_type_id    int unsigned         not null,
    registration_date   datetime(3)          null,
    expire_date         date                 null,
    no_alert            tinyint(1) default 0 not null,
    ident_value         varchar(255)         null,
    grant_date          date                 null,
    checker_person_id   int                  null,
    checking_date       date                 not null,
    checking_sentence   varchar(255)         not null,
    is_current_validity tinyint(1) default 0 not null,
    action_ip_address   varchar(255)         null,
    action_login        varchar(255)         null,
    unique (person_id, validity_type_id, registration_date)
);

create table validity_type
(
    id                       int unsigned auto_increment
        primary key,
    name                     varchar(255)                   not null,
    time_limitation          tinyint(1)          default 0  not null,
    ident_value_enable       tinyint(1)          default 0  not null,
    grant_date_enable        tinyint(1)          default 0  not null,
    mandatory                tinyint(1)          default 0  not null,
    experience_formula       text                           null,
    alert_on_login           int                 default -2 null,
    mandatory_access_control tinyint unsigned    default 0  null,
    first_reminder_alert     int unsigned        default 0  null,
    reminder_frequency_alert int unsigned        default 0  null,
    associate_attachment     tinyint unsigned    default 0  not null,
    certification_process    tinyint(1)          default 0  not null,
    is_contract              tinyint(1)          default 0  not null,
    contract_filename        varchar(255)                   not null,
    contract_file            mediumblob                     not null,
    contract_file_extension  varchar(4)                     not null,
    is_OF_contract           tinyint(1)          default 0  not null,
    activated                tinyint unsigned    default 1  not null
)
    comment 'qualifications list';

create table validity_type_page
(
    id               int unsigned auto_increment
        primary key,
    validity_type_id int unsigned        not null,
    page_index       int unsigned        not null,
    label            varchar(40)         not null,
    is_mandatory     tinyint unsigned    not null,
    unique (validity_type_id, page_index)
);

create table validity_page
(
    id                    int unsigned auto_increment
        primary key,
    validity_type_page_id int unsigned not null,
    filename              varchar(255) not null,
    file                  mediumblob   not null,
    file_extension        varchar(4)   not null
);

create table validity_2_validity_page
(
    validity_id      int unsigned not null,
    validity_page_id int unsigned not null,
    primary key (validity_id, validity_page_id)
);

create table variable
(
    id         int auto_increment
        primary key,
    variable   varchar(255) not null,
    label      varchar(255) not null,
    category   tinyint(1)   null,
    value_type varchar(255) null,
    order_num  int          not null
);

create table variable_value
(
    id           int unsigned auto_increment
        primary key,
    variable_id  int            not null,
    assign_value decimal(15, 2) not null,
    start_date   datetime       not null
);

create table web_feed
(
    id               int unsigned auto_increment
        primary key,
    channel_id       int unsigned not null,
    publication_date datetime     not null,
    title            varchar(255) null,
    content          text         null
);

create table web_feed_channel
(
    id    int unsigned auto_increment
        primary key,
    label varchar(255) not null
);

create definer = overallCustomer@localhost view eligible_pilot_for_first_flight as
select `of40_aeroclublys`.`person`.`id` AS `person_id`
from `of40_aeroclublys`.`person`
where ((`of40_aeroclublys`.`person`.`activated` = 1) and
       ((`of40_aeroclublys`.`person`.`profile` & (select `of40_aeroclublys`.`parameter`.`int_value`
                                                  from `of40_aeroclublys`.`parameter`
                                                  where (`of40_aeroclublys`.`parameter`.`code` =
                                                         'EXTERNAL_BOOKING_PILOT_PROFILE'))) > 0) and
       `of40_aeroclublys`.`person`.`id` in (select `of40_aeroclublys`.`regular_presence_inst_date`.`person_id`
                                            from `of40_aeroclublys`.`regular_presence_inst_date`
                                            union
                                            select `of40_aeroclublys`.`exceptionnal_inst_date`.`person_id`
                                            from `of40_aeroclublys`.`exceptionnal_inst_date`
                                            where ((`of40_aeroclublys`.`exceptionnal_inst_date`.`end_date` > now()) and
                                                   (`of40_aeroclublys`.`exceptionnal_inst_date`.`presence` = 1))));

create definer = overallCustomer@localhost view eligible_resource_for_first_flight as
select `of40_aeroclublys`.`resource`.`id` AS `resource_id`
from ((`of40_aeroclublys`.`resource` join `of40_aeroclublys`.`business_field_content` on ((
        `of40_aeroclublys`.`business_field_content`.`category_id` = `of40_aeroclublys`.`resource`.`id`)))
         join `of40_aeroclublys`.`business_field` on ((`of40_aeroclublys`.`business_field`.`id` =
                                                       `of40_aeroclublys`.`business_field_content`.`business_field_id`)))
where ((`of40_aeroclublys`.`resource`.`activated` = 1) and
       (`of40_aeroclublys`.`business_field`.`variable` = 'canResourcePerformFirstFlights') and
       (`of40_aeroclublys`.`business_field_content`.`content` = '1') and
       (`of40_aeroclublys`.`resource`.`bookable` = 1) and ((select count(0)
                                                            from `of40_aeroclublys`.`resource_type_place_tag` `seat`
                                                            where (`seat`.`resource_type_id` =
                                                                   `of40_aeroclublys`.`resource`.`resource_type_id`)) >=
                                                           2) and
       `of40_aeroclublys`.`resource`.`id` in (select `of40_aeroclublys`.`resource_regular_availability`.`resource_id`
                                              from `of40_aeroclublys`.`resource_regular_availability`
                                              union
                                              select `of40_aeroclublys`.`resource_exceptional_availability`.`resource_id`
                                              from `of40_aeroclublys`.`resource_exceptional_availability`
                                              where ((`of40_aeroclublys`.`resource_exceptional_availability`.`end_date` >
                                                      now()) and
                                                     (`of40_aeroclublys`.`resource_exceptional_availability`.`presence` = 1))));

create
    definer = root@localhost function distanceBetween2Point(latitude1 double, longitude1 double, latitude2 double,
                                                            longitude2 double) returns double
BEGIN
    DECLARE rlongitude1 DOUBLE;
    DECLARE rlatitude1 DOUBLE;
    DECLARE rlongitude2 DOUBLE;
    DECLARE rlatitude2 DOUBLE;
    DECLARE dlongitude DOUBLE;
    DECLARE dlatitude DOUBLE;
    DECLARE a DOUBLE;

    SET rlongitude1 = RADIANS(longitude1);
    SET rlatitude1 = RADIANS(latitude1);
    SET rlongitude2 = RADIANS(longitude2);
    SET rlatitude2 = RADIANS(latitude2);
    SET dlongitude = (rlongitude2 - rlongitude1) / 2;
    SET dlatitude = (rlatitude2 - rlatitude1) / 2;
    SET a = SIN(dlatitude) * SIN(dlatitude) + COS(rlatitude1) * COS(rlatitude2) * SIN(dlongitude) * SIN(dlongitude);
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
END;

create
    definer = overallCustomer@localhost function formatDecimal(string varchar(255)) returns varchar(255)
BEGIN
    DECLARE replacedString VARCHAR(255);
    SET replacedString = string;
    SET @decimalSeparator = (SELECT char_value FROM parameter WHERE code = 'DECIMAL_SEPARATOR' AND key_id = 0 LIMIT 1);

    SELECT IF(replacedString <> '0.00' AND replacedString <> '0', TRIM(TRAILING '0' FROM replacedString),
              replacedString)
    INTO replacedString;
    SELECT TRIM(TRAILING '.' FROM replacedString) INTO replacedString;
    SELECT REPLACE(replacedString, '.', IFNULL(@decimalSeparator, '.')) INTO replacedString;
    RETURN replacedString;
END;

create
    definer = root@localhost function isBalancedAccountEntryFlow(flowId int) returns int
BEGIN
    DECLARE isBalanced INT;

    SELECT IF(SUM(debit) <> SUM(credit), 0, 1)
    into isBalanced
    FROM account_entry
    WHERE flow_id = flowId
    GROUP BY flow_id;

    RETURN isBalanced;
END;

create
    definer = root@localhost function nearestPoint(latRef double, longRef double, distanceMax int) returns varchar(6)
BEGIN
    DECLARE rlongitude1 DOUBLE;
    DECLARE rlatitude1 DOUBLE;
    DECLARE rlongitude2 DOUBLE;
    DECLARE rlatitude2 DOUBLE;
    DECLARE a DOUBLE;
    DECLARE icao_name VARCHAR(6);
    DECLARE lenght DOUBLE;

    SET a = 2 * 6378137 * pi() * distanceMax / (1852 * 60 * 360);
    SET rlongitude1 = longRef + a / cos(latRef);
    SET rlongitude2 = longRef - a / cos(latRef);
    SET rlatitude1 = latRef + a;
    SET rlatitude2 = latRef - a;

    SELECT loc1.icao_name,
           distanceBetween2Point(
                   (SELECT loc2.latitude FROM location AS loc2 WHERE loc2.icao_name = loc1.icao_name),
                   (SELECT loc3.longitude FROM location AS loc3 WHERE loc3.icao_name = loc1.icao_name),
                   latRef,
                   longRef) AS distance
    FROM location AS loc1
    WHERE IF(rlongitude2 < rlongitude1,
             loc1.longitude BETWEEN rlongitude2 AND rlongitude1,
             loc1.longitude BETWEEN rlongitude1 AND rlongitude2)
      AND IF(rlatitude2 < rlatitude1,
             loc1.latitude BETWEEN rlatitude2 AND rlatitude1,
             loc1.latitude BETWEEN rlatitude1 AND rlatitude2)
    HAVING distance < distanceMax
    ORDER BY distance
    LIMIT 1
    INTO icao_name, lenght;

    RETURN icao_name;
END;

create
    definer = overallCustomer@localhost function sexa2HoursHundredths(sexacentimal int) returns varchar(255)
BEGIN
    DECLARE convertedValue VARCHAR(255);
    DECLARE hours VARCHAR(255);
    DECLARE roundedHours VARCHAR(255);
    DECLARE remainingTime VARCHAR(255);
    DECLARE decimalSeparator VARCHAR(1);
    SET hours = sexacentimal / 600;
    SET roundedHours = IF(sexacentimal > 0, FLOOR(hours), CEIL(hours));
    SET remainingTime = ABS(sexacentimal - roundedHours * 600);
    SET decimalSeparator =
            (SELECT IFNULL(char_value, '.') FROM parameter WHERE code = 'DECIMAL_SEPARATOR' AND key_id = 0 LIMIT 1);

    SELECT CONCAT(
                   roundedHours, decimalSeparator, LPAD(FLOOR(remainingTime / 6), 2, '0')
               )
    INTO convertedValue;

    RETURN convertedValue;
END;

create
    definer = overallCustomer@localhost function sexa2HoursMinute(sexacentimal int) returns varchar(255)
BEGIN
    DECLARE convertedValue VARCHAR(255);
    DECLARE hours VARCHAR(255); # We separate hours computation from roundHours in order to keep the minus sign if hours is 0 but sexacentimal negative
    DECLARE roundedHours VARCHAR(255);
    DECLARE remainingTime VARCHAR(255);
    SET hours = sexacentimal / 600;
    SET roundedHours = IF(sexacentimal > 0, FLOOR(hours), CEIL(hours));
    SET remainingTime = ABS(sexacentimal - roundedHours * 600);

    SELECT CONCAT(
                   roundedHours, ':', LPAD(FLOOR(remainingTime / 10), 2, '0')
               )
    INTO convertedValue;

    RETURN convertedValue;
END;

create
    definer = root@localhost function stripChars(word varchar(255)) returns varchar(255)
BEGIN
    DECLARE stripWord VARCHAR(255);
    SET stripWord = word;
    SELECT IF(stripWord REGEXP '[-]', REPLACE(stripWord, '-', ''), stripWord) INTO stripWord;
    SELECT IF(stripWord REGEXP '[ ]', REPLACE(stripWord, ' ', ''), stripWord) INTO stripWord;
    RETURN stripWord;
END;

create
    definer = overallCustomer@localhost function sumAccountEntry(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
    DECLARE balanceDate DATETIME;
    DECLARE balanceDebit DECIMAL(15, 2);
    DECLARE balanceCredit DECIMAL(15, 2);
    DECLARE totalDebit DECIMAL(15, 2);
    DECLARE totalCredit DECIMAL(15, 2);
    DECLARE totalBalance DECIMAL(15, 2);


    SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
    INTO balanceDate, balanceDebit, balanceCredit
    FROM balance_date
             LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
    WHERE balance_date.balance_date < endDate
    ORDER BY balance_date DESC
    LIMIT 1;


    IF (SELECT FOUND_ROWS()) = 0 THEN
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
    END IF;

    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
    INTO totalDebit, totalCredit
    FROM account_entry
    WHERE account_id = accountId
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME);

    SELECT (totalCredit - totalDebit) INTO totalBalance;

    RETURN totalBalance;
END;

create
    definer = overallCustomer@localhost function sumAccountEntryCredit(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
    DECLARE balanceDate DATETIME;
    DECLARE balanceCredit DECIMAL(15, 2);
    DECLARE totalCredit DECIMAL(15, 2);


    SELECT balance_date, IFNULL(credit, 0.00)
    INTO balanceDate, balanceCredit
    FROM balance_date
             LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
    WHERE balance_date.balance_date < endDate
    ORDER BY balance_date DESC
    LIMIT 1;


    IF (SELECT FOUND_ROWS()) = 0 THEN
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceCredit;
    END IF;

    SELECT IFNULL(SUM(credit), 0.00) + balanceCredit
    INTO totalCredit
    FROM account_entry
    WHERE account_id = accountId
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME);

    RETURN totalCredit;
END;

create
    definer = overallCustomer@localhost function sumAccountEntryDebit(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
    DECLARE balanceDate DATETIME;
    DECLARE balanceDebit DECIMAL(15, 2);
    DECLARE totalDebit DECIMAL(15, 2);


    SELECT balance_date, IFNULL(debit, 0.00)
    INTO balanceDate, balanceDebit
    FROM balance_date
             LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
    WHERE balance_date.balance_date < endDate
    ORDER BY balance_date DESC
    LIMIT 1;


    IF (SELECT FOUND_ROWS()) = 0 THEN
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit;
    END IF;

    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit
    INTO totalDebit
    FROM account_entry
    WHERE account_id = accountId
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME);

    RETURN totalDebit;
END;

create
    definer = root@localhost function sumValidatedAccountEntry(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
    DECLARE balanceDate DATETIME;
    DECLARE balanceDebit DECIMAL(15, 2);
    DECLARE balanceCredit DECIMAL(15, 2);
    DECLARE totalDebit DECIMAL(15, 2);
    DECLARE totalCredit DECIMAL(15, 2);
    DECLARE totalBalance DECIMAL(15, 2);


    SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
    INTO balanceDate, balanceDebit, balanceCredit
    FROM balance_date
             LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
    WHERE balance_date.balance_date < endDate
    ORDER BY balance_date DESC
    LIMIT 1;


    IF (SELECT FOUND_ROWS()) = 0 THEN
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
    END IF;

    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
    INTO totalDebit, totalCredit
    FROM account_entry
    WHERE account_id = accountId
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME)
      AND validated = 1;

    SELECT (totalCredit - totalDebit) INTO totalBalance;

    RETURN totalBalance;
END;

create
    definer = root@localhost procedure addGapToDate(in gap int)
    modifies sql data
BEGIN
    
    DECLARE done TINYINT DEFAULT 0;
    DECLARE tmpTableName VARCHAR(255);
    DECLARE tmpColumnName VARCHAR(255);
    
    DECLARE cursor1 CURSOR FOR
        SELECT TABLE_NAME, COLUMN_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = ( SELECT DATABASE() )
          AND DATA_TYPE IN ('date', 'datetime');
    
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    OPEN cursor1;
    REPEAT
        FETCH cursor1 INTO tmpTableName, tmpColumnName;
        IF NOT done THEN
            BEGIN
                SET @dynamicQuery = CONCAT('UPDATE ', tmpTableName, ' SET ', tmpColumnName, '= DATE_ADD(', tmpColumnName, ', INTERVAL ', gap, ' DAY)');
                PREPARE pQuery FROM @dynamicQuery;
                EXECUTE pQuery;
                DEALLOCATE PREPARE pQuery;
            END;
        END IF;
    UNTIL done END REPEAT;
    CLOSE cursor1;

    TRUNCATE TABLE customer_bill_file;
END;

Description

Table booking

Le contenu du champ aircraft_id de la table booking peut être nul. Dans ce cas, c'est une ancienne réservation effectuée sur une ressource non-existante ou qui n'existe plus; aussi nommé réservation orpheline.

Table flight

• airborne :
  • 0: Pas en l'air ou fermeture de vol : Le pilote a terminé le vol.
  • 1: En l'air ou ouverture de vol : Le pilote remplit le vol avant de voler.

Champs cachés

Les champs suivants ne peuvent être exportés et ne doivent pas être inclus dans une requête SELECT. De plus, un "SELECT *" est interdit quand une des tables contient un champ caché :

• Table journal : Tous les champs
• Table log : Tous les champs
• Table parameter : Tous les champs
• Table person :
  • hash_password
• Table structure :
  • address
  • admin_num
  • city
  • country
  • default_notification
  • default_slot_range
  • default_timezone
  • default_view_type
  • email
  • fax
  • first_hour_displayed
  • info_cell
  • lang
  • last_hour_displayed
  • logo
  • logo_name
  • logo_ext
  • logo_size
  • mail_from_address
  • mailing_list_name
  • mailing_list_type
  • min_slot_range
  • name
  • phone
  • state
  • twilight_range
  • usual_profiles
  • welcome_cell
  • zipcode
• Table oauth_client : Tous les champs
• Table oauth_access_token : Tous les champs
• Table oauth_auth_code : Tous les champs
• Table oauth_refresh_token : Tous les champs

Exemple de requêtes non autorisées : <sql>SELECT * FROM structure;

SELECT hash_password FROM person;

SELECT person.id, validity.* FROM person LEFT JOIN validity ON (person.id=validity.person_id);</sql>