Structure de la base de données : Différence entre versions

Aller à : navigation, rechercher
(→‎Champs cachés)
(→‎Structure)
 
(19 révisions intermédiaires par 2 utilisateurs non affichées)
Ligne 1 : Ligne 1 :
 +
=[[Media:OpenFlyers_database_schema.png|Schéma de la structure de la base de données]]=
 +
 
=Structure=
 
=Structure=
  
Ligne 24 : Ligne 26 :
 
     on account (owner_id);
 
     on account (owner_id);
  
−
create table account_entry
+
CREATE TABLE `account_entry` (
−
(
+
  `id` int(11) NOT NULL AUTO_INCREMENT,
−
    id                 int auto_increment
+
  `flow_id` int(11) DEFAULT NULL,
−
        primary key,
+
  `account_date` datetime DEFAULT NULL,
−
    flow_id             int                           null,
+
  `account_id` int(11) DEFAULT NULL,
−
    account_date       datetime                       null,
+
  `credit` decimal(15,5) DEFAULT '0.00000',
−
    account_id         int                           null,
+
  `debit` decimal(15,5) DEFAULT '0.00000',
−
    credit             decimal(15, 5) default 0.00000 null,
+
  `payment_type` int(10) unsigned DEFAULT NULL,
−
    debit               decimal(15, 5) default 0.00000 null,
+
  `payment_description` text,
−
    payment_type       int unsigned                   null,
+
  `person_delivery_id` int(10) unsigned DEFAULT NULL,
−
    payment_description text                           null,
+
  `comments` text,
−
    person_delivery_id int unsigned                   null,
+
  `validated` int(1) DEFAULT '0',
−
    comments           text                           null,
+
  `exported` int(1) DEFAULT '0',
−
    validated           int(1)         default 0       null,
+
   `registration_date` datetime DEFAULT NULL,
−
    exported           int(1)         default 0       null,
+
  `budget_id` int(10) unsigned DEFAULT NULL,
−
    registration_date   datetime                       null,
+
  `product_id` int(10) unsigned DEFAULT NULL,
−
    budget_id           int unsigned                   null,
+
  `signature` varchar(56) DEFAULT NULL,
−
    product_id         int unsigned                   null,
+
  `signature_date` bigint(13) unsigned DEFAULT NULL,
−
    signature           varchar(56)                   null,
+
  `lettering` int(11) DEFAULT NULL,
−
    signature_date     bigint(13) unsigned           null,
+
  `lettering_date` datetime DEFAULT NULL,
−
    lettering           int                           null,
+
  `flow_reversal_id` int(10) unsigned DEFAULT NULL,
−
    lettering_date     datetime                       null
+
  `transaction_id` text,
−
)
+
  PRIMARY KEY (`id`),
−
    comment 'List of account entries';
+
  KEY `idx_flow_id` (`flow_id`),
−
 
+
  KEY `idx_account_date` (`account_date`),
−
create index idx_account_date
+
  KEY `idx_signature_date` (`signature_date`),
−
    on account_entry (account_date);
+
  KEY `flow_reversal_id_idx` (`flow_reversal_id`)
−
 
+
);
−
create index idx_flow_id
+
−
    on account_entry (flow_id);
+
−
 
+
−
create index idx_signature_date
+
−
    on account_entry (signature_date);
+
  
 
create table account_link
 
create table account_link
Ligne 101 : Ligne 98 :
 
     profile_id    bigint unsigned default 0 not null,
 
     profile_id    bigint unsigned default 0 not null,
 
     primary key (accounting_id, profile_id)
 
     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)
 
 
);
 
);
  
Ligne 267 : Ligne 250 :
 
     resource_id int unsigned default 0 not null,
 
     resource_id int unsigned default 0 not null,
 
     primary key (booking_id, resource_id)
 
     primary key (booking_id, resource_id)
 +
);
 +
 +
CREATE TABLE `booking_training_program` (
 +
  `booking_group_id` int(10) unsigned NOT NULL,
 +
  `training_program_id` int(10) unsigned NOT NULL,
 +
  PRIMARY KEY (`booking_group_id`,`training_program_id`),
 +
  CONSTRAINT `booking_training_program_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`)
 
);
 
);
  
Ligne 287 : Ligne 277 :
 
);
 
);
  
−
create table business_field
+
CREATE TABLE `business_field` (
−
(
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
−
    id                       int unsigned auto_increment
+
  `variable` varchar(255) DEFAULT NULL,
−
        primary key,
+
  `label` varchar(255) NOT NULL,
−
    variable                 varchar(255)                   null,
+
  `value_type` varchar(40) NOT NULL,
−
    label                   varchar(255)                   not null,
+
  `category` varchar(255) NOT NULL,
−
    value_type               varchar(40)                   not null,
+
  `order_num` int(11) NOT NULL,
−
    category                 varchar(255)                   not null,
+
  `dsn` varchar(20) DEFAULT 'customer',
−
    order_num               int                           not null,
+
  `compulsory_fill` tinyint(1) DEFAULT '0',
−
    dsn                     varchar(20) default 'customer' null,
+
  `linked_category` varchar(255) DEFAULT NULL,
−
    compulsory_fill         tinyint(1) default 0         null,
+
  `linked_business_field_id` int(10) unsigned DEFAULT NULL,
−
    linked_category         varchar(255)                   null,
+
  `linked_field_name` varchar(255) DEFAULT NULL,
−
    linked_business_field_id int unsigned                   null,
+
  `max_display` int(10) DEFAULT '-1',
−
    linked_field_name       varchar(255)                   null,
+
  `formula` text,
−
    max_display             int(10)     default -1         null,
+
  `default_value` text,
−
    formula                 text                           null,
+
  `placeholder` text,
−
    default_value           text                           null,
+
  `access_level_required` tinyint(4) NOT NULL DEFAULT '0',
−
    placeholder             text                           null,
+
  `user_access_mode` tinyint(1) NOT NULL DEFAULT '0',
−
    access_level_required   tinyint     default 0         not null,
+
  PRIMARY KEY (`id`),
−
    constraint variable
+
  UNIQUE KEY `variable` (`variable`),
−
        unique (variable)
+
  KEY `business_field_linked_business_field_id_fk` (`linked_business_field_id`),
−
)
+
  CONSTRAINT `business_field_linked_business_field_id_fk` FOREIGN KEY (`linked_business_field_id`) REFERENCES `business_field` (`id`)
−
    comment 'List of extra form field';
+
) COMMENT='List of extra form field';
  
 
create table business_field_activity_type
 
create table business_field_activity_type
Ligne 648 : Ligne 638 :
 
);
 
);
  
−
create table flight_track
+
CREATE TABLE `flight_track` (
−
(
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
−
    id       int unsigned auto_increment
+
  `flight_id` int(10) NOT NULL,
−
        primary key,
+
  `track` mediumblob NOT NULL,
−
    flight_id int(10)   not null,
+
  `file_type` varchar(255) DEFAULT 'kml',
−
    track     mediumblob not null
+
  PRIMARY KEY (`id`)
 
);
 
);
  
Ligne 1 006 : Ligne 996 :
 
     primary key (code, key_id)
 
     primary key (code, key_id)
 
);
 
);
−
 
−
create table payment_distribution
 
−
(
 
−
    payment_id        int unsigned        default 0 not null
 
−
        primary key,
 
−
    account_club_id    int unsigned                  null,
 
−
    person_delivery    tinyint(1) unsigned default 0 null,
 
−
    member_budget_id  int unsigned                  null,
 
−
    treasury_budget_id int unsigned                  null
 
−
)
 
−
    comment 'payment distribution description';
 
  
 
create table payment_summary_file
 
create table payment_summary_file
Ligne 1 030 : Ligne 1 009 :
 
);
 
);
  
−
create table payment_type
+
CREATE TABLE `payment_type` (
−
(
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
−
    id               int unsigned auto_increment
+
  `name` text,
−
        primary key,
+
  `text_field_label` text,
−
    name             text                         null,
+
  `only_admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
−
    text_field_label text                         null,
+
  `pos_key_id` int(10) unsigned DEFAULT NULL,
−
    only_admin       tinyint(1) unsigned default 0 not null,
+
  `treasury_account_id` int(10) unsigned DEFAULT NULL,
−
    order_num        int                           null,
+
  `person_delivery` tinyint(1) unsigned DEFAULT '0',
−
    pos_key_id      int unsigned                 null
+
  `person_budget_id` int(10) unsigned DEFAULT NULL,
−
)
+
  `treasury_budget_id` int(10) unsigned DEFAULT NULL,
−
    comment 'type of payment description';
+
  `minimum_amount` decimal(10,0) DEFAULT NULL,
 +
  `maximum_amount` decimal(10,0) DEFAULT NULL,
 +
  PRIMARY KEY (`id`)
 +
) COMMENT='type of payment description';
  
 
create table person
 
create table person
Ligne 1 587 : Ligne 1 569 :
 
         primary key,
 
         primary key,
 
     name varchar(255) not null
 
     name varchar(255) not null
 +
);
 +
 +
CREATE TABLE `training` (
 +
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 +
  `name` varchar(255) NOT NULL,
 +
  PRIMARY KEY (`id`)
 +
);
 +
 +
CREATE TABLE `training_activity` (
 +
  `activity_id` int(10) unsigned NOT NULL,
 +
  `training_item_id` int(10) unsigned NOT NULL DEFAULT '0',
 +
  `training_program_id` int(10) unsigned NOT NULL DEFAULT '1',
 +
  `level_reached` int(10) unsigned DEFAULT NULL,
 +
  `comment` varchar(255) DEFAULT NULL,
 +
  `validated` tinyint(1) NOT NULL DEFAULT '0',
 +
  PRIMARY KEY (`activity_id`,`training_item_id`,`training_program_id`),
 +
  KEY `training_program_id_idx` (`training_program_id`),
 +
  KEY `training_program_item_id_idx` (`training_program_id`,`training_item_id`),
 +
  CONSTRAINT `training_activity_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 +
  CONSTRAINT `training_activity_training_program_item_id_fk` FOREIGN KEY (`training_program_id`, `training_item_id`) REFERENCES `training_program_item` (`training_program_id`, `training_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 +
);
 +
 +
CREATE TABLE `training_activity_next_item` (
 +
  `student_id` int(10) unsigned NOT NULL,
 +
  `training_program_id` int(10) unsigned NOT NULL,
 +
  `training_item_id` int(10) unsigned NOT NULL,
 +
  `reporting_offset` tinyint(10) unsigned NOT NULL DEFAULT '0',
 +
  `item_preselected` tinyint(1) unsigned NOT NULL DEFAULT '0',
 +
  PRIMARY KEY (`student_id`,`training_program_id`,`training_item_id`),
 +
  KEY `training_item_id_idx` (`training_item_id`),
 +
  KEY `training_program_id_idx` (`training_program_id`),
 +
  KEY `training_activity_next_item_training_program_item_id_fk` (`training_program_id`,`training_item_id`),
 +
  CONSTRAINT `training_activity_next_item_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 +
  CONSTRAINT `training_activity_next_item_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 +
  CONSTRAINT `training_activity_next_item_training_program_item_id_fk` FOREIGN KEY (`training_program_id`, `training_item_id`) REFERENCES `training_program_item` (`training_program_id`, `training_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 +
);
 +
 +
CREATE TABLE `training_activity_person` (
 +
  `activity_id` int(10) unsigned NOT NULL,
 +
  `person_id` int(10) unsigned NOT NULL,
 +
  `num` int(10) unsigned NOT NULL DEFAULT '0',
 +
  `training_checking_date` datetime DEFAULT NULL,
 +
  `training_checking_sentence` varchar(255) DEFAULT NULL,
 +
  PRIMARY KEY (`activity_id`,`person_id`,`num`),
 +
  KEY `person_id_idx` (`person_id`),
 +
  CONSTRAINT `training_activity_person_activity_id_fk` FOREIGN KEY (`activity_id`) REFERENCES `flight` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 +
  CONSTRAINT `training_activity_person_person_id_fk` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 +
);
 +
 +
CREATE TABLE `training_phase` (
 +
  `training_program_item_order_num` int(11) unsigned NOT NULL,
 +
  `training_program_id` int(10) unsigned NOT NULL,
 +
  `name` varchar(255) DEFAULT NULL,
 +
  PRIMARY KEY (`training_program_item_order_num`,`training_program_id`),
 +
  KEY `training_program_id_idx` (`training_program_id`),
 +
  CONSTRAINT `training_phase_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 +
);
 +
 +
CREATE TABLE `training_program` (
 +
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 +
`training_id` int(10) unsigned DEFAULT NULL,
 +
`name` varchar(255) NOT NULL,
 +
PRIMARY KEY (`id`),
 +
KEY `training_id_idx` (`training_id`),
 +
CONSTRAINT `training_program_training_id_fk` FOREIGN KEY (`training_id`) REFERENCES `training` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 +
);
 +
 +
CREATE TABLE `training_program_activity` (
 +
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 +
  `training_program_id` int(10) unsigned NOT NULL,
 +
  `activity_type_id` bigint(20) unsigned NOT NULL,
 +
  `duration` int(11) unsigned DEFAULT NULL,
 +
  `breaktime` int(11) unsigned DEFAULT NULL,
 +
  `order_num` int(11) unsigned NOT NULL,
 +
  PRIMARY KEY (`id`),
 +
  KEY `training_program_id_idx` (`training_program_id`),
 +
  KEY `activity_type_id_idx` (`activity_type_id`),
 +
  CONSTRAINT `training_program_activity_activity_type_id_fk` FOREIGN KEY (`activity_type_id`) REFERENCES `activity_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 +
  CONSTRAINT `training_program_activity_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 +
);
 +
 +
CREATE TABLE `training_item` (
 +
  `training_id` int(10) unsigned NOT NULL,
 +
  `training_item_label_id` int(10) unsigned NOT NULL,
 +
  `number_of_sessions` int(11) NOT NULL,
 +
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
 +
  PRIMARY KEY (`training_id`,`training_item_label_id`),
 +
  KEY `order_num_idx` (`order_num` ASC),
 +
  CONSTRAINT `training_item_training_id_fk` FOREIGN KEY (`training_id`) REFERENCES `training` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 
);
 
);
  
Ligne 2 044 : Ligne 2 115 :
  
 
==Champs cachés==
 
==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é :
−
Les champs suivants ne peuvent être exportés et ne doivent pas être inclus dans une requête SELECT. De plus, un "SELECT *" n'est possible 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 :
 
*Table structure :
−
**name
+
**address
 +
**admin_num
 +
**city
 +
**country
 +
**default_notification
 +
**default_slot_range
 +
**default_timezone
 +
**default_view_type
 +
**email
 +
**fax
 +
**first_hour_displayed
 
**info_cell
 
**info_cell
 +
**lang
 +
**last_hour_displayed
 
**logo
 
**logo
 
**logo_name
 
**logo_name
 
**logo_ext
 
**logo_ext
 
**logo_size
 
**logo_size
−
**first_hour_displayed
+
**mail_from_address
−
**last_hour_displayed
+
−
**usual_profiles
+
−
**default_slot_range
+
−
**min_slot_range
+
−
**twilight_range
+
 
**mailing_list_name
 
**mailing_list_name
 
**mailing_list_type
 
**mailing_list_type
−
**default_timezone
+
**min_slot_range
−
**lang
+
**name
−
**admin_num
+
**phone
−
**mail_from_address
+
−
**default_view_type
+
−
**address
+
−
**zipcode
+
−
**city
+
 
**state
 
**state
−
**country
+
**twilight_range
−
**phone
+
**usual_profiles
−
**fax
+
−
**email
+
−
**default_notification
+
 
**welcome_cell
 
**welcome_cell
−
 
+
**zipcode
−
*Table journal: Tous les champs
+
*Table oauth_client : Tous les champs
−
*Table log: Tous les champs
+
*Table oauth_access_token : Tous les champs
−
*Table parameter: Tous les champs
+
*Table oauth_auth_code : Tous les champs
−
 
+
*Table oauth_refresh_token : Tous les champs
−
*Table person:
+
−
**hash_password
+
−
 
+
  
 
Exemple de requêtes non autorisées :
 
Exemple de requêtes non autorisées :

Version actuelle en date du 10 janvier 2024 à 16:25

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

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