Version du 21 novembre 2020 à 13:34

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(1) unsigned default 1 not null,
    category         tinyint(1) unsigned default 0 null,
    account_type     tinyint(1) 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 null,
    debit               decimal(15, 5) default 0.00000 null,
    payment_type        int unsigned                   null,
    payment_description text                           null,
    person_delivery_id  int unsigned                   null,
    comments            text                           null,
    validated           int(1)         default 0       null,
    exported            int(1)         default 0       null,
    registration_date   datetime                       null,
    budget_id           int unsigned                   null,
    product_id          int unsigned                   null,
    signature           varchar(56)                    null,
    signature_date      bigint(13) unsigned            null,
    lettering           int                            null,
    lettering_date      datetime                       null
)
    comment 'List of account entries';
 
create index idx_account_date
    on account_entry (account_date);
 
create index idx_flow_id
    on account_entry (flow_id);
 
create index idx_signature_date
    on account_entry (signature_date);
 
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(1) 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    default 0 not null,
    profile_id      bigint unsigned default 0 not null,
    primary key (account_type_id, profile_id)
)
    comment 'Linked account type 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(1)             null,
    decimal_precision int unsigned default 2 null
);
 
create table accounting_profile
(
    accounting_id int unsigned    default 0 not null,
    profile_id    bigint unsigned default 0 not null,
    primary key (accounting_id, profile_id)
);
 
create table activity_training
(
    activity_id       int unsigned default 0 not null,
    theme_id          int unsigned default 0 not null,
    student_id        int(10)                null,
    level_reached     int unsigned           null,
    comment           varchar(255)           null,
    validated         tinyint(1)   default 0 not null,
    checker_person_id int(10)                null,
    checking_date     datetime               null,
    checking_sentence varchar(255)           null,
    primary key (activity_id, theme_id)
);
 
create table activity_type
(
    id        bigint unsigned default 0 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
)
    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,
    inspection_date datetime                       null,
    inspection_time int                            null,
    tolerance_time  int(11) unsigned default 6000  null,
    last_counter    int                            null,
    interval_visit  int(11) unsigned default 30000 null,
    time_alert1     int              default 6000  null,
    time_alert2     int              default 0     null,
    time_alert3     int              default -3000 null,
    day_alert1      smallint(3)      default 15    null,
    day_alert2      smallint(3)      default 0     null,
    day_alert3      smallint(3)      default -8    null
);
 
create table aircraft_type
(
    id                   int unsigned auto_increment
        primary key,
    flight_time_formula  varchar(255)        default '%DURATION' null,
    counter_state        tinyint(2)          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(1) unsigned default 4           not null
)
    comment 'Types of aircraft';
 
create table aircraft_type_allowed_status
(
    aircraft_type_id int unsigned null,
    place_num        int unsigned null,
    status_id        int unsigned null
)
    comment 'List of allowed functions for each aircraft type';
 
create table aircraft_type_mandatory_flight_type
(
    aircraft_type_id int unsigned    null,
    activity_type_id bigint unsigned null
)
    comment 'List of compulsory flight type for each aircraft type';
 
create table aircraft_type_uncomp_flight_type
(
    aircraft_type_id int unsigned    null,
    activity_type_id bigint unsigned null
)
    comment 'List of uncompatible flight type for each aircraft type';
 
create table aircraft_type_validity_type
(
    aircraft_type_id int unsigned default 0 not null,
    validity_type_id int unsigned default 0 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(10)              not null,
    keyword    varchar(255)         null,
    order_num  int(10)              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 null,
    credit          decimal(15, 5) default 0.00000 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
);
 
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 default 0 not null,
    activity_type_id int unsigned default 0 not null,
    primary key (booking_id, activity_type_id)
);
 
create table booking_changelog
(
    id             int unsigned auto_increment
        primary key,
    booking_id     int unsigned not null,
    person_id      int unsigned not null,
    changelog_date datetime     not null,
    booking_state  text         null
);
 
create table booking_person
(
    booking_id int unsigned default 0 not null,
    person_id  int unsigned default 0 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 budget
(
    id          int unsigned auto_increment
        primary key,
    group_id    int unsigned default 0 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(10)     default -1         null,
    formula                  text                           null,
    default_value            text                           null,
    placeholder              text                           null,
    access_level_required    tinyint     default 0          not null,
    constraint variable
        unique (variable)
)
    comment 'List of extra form field';
 
create table business_field_activity_type
(
    business_field_id       int unsigned    default 0 not null,
    activity_type_id        bigint unsigned default 0 not null,
    business_field_group_id int unsigned    default 0 not null,
    visibility_type         tinyint(1) unsigned       null,
    primary key (business_field_id, activity_type_id)
);
 
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 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, 2) default 0.00    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
)
    comment 'List of customer bill stored file';
 
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 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(