Votre base de données
comme vous ne l'avez jamais vue
Loïc Knuchel
30 juin 2022
@loicknuchel
#SunnyTech
Qui travaille régulièrement avec une base de données?
Quelle est la taille de son schéma?
> 10 tables?
> 30 tables?
> 100 tables?
> 300 tables?
> 1000 tables?
Qui a déjà entendu
parler des ERD?
Qui utilise un ERD?
Choc culturel
case class User(id: User.Id,
status: User.Status,
firstName: String,
lastName: String,
email: EmailAddress,
emailValidated: Option[Instant],
bio: Option[Markdown],
website: Option[Url],
createdAt: Instant,
updatedAt: Instant)
object User {
case class Id(value: String) extends AnyVal
case class Status(value: String) extends AnyVal
}
case class EmailAddress(value: String) extends AnyVal
case class Markdown(value: String) extends AnyVal
# app/models/user.rb
class User < ApplicationRecord
validates :first_name, presence: true
end
# db/schema.rb
create_table "users", force: :cascade do |t|
t.string "first_name"
t.string "last_name"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
Cas concret
Gitlab milestone
class Milestone < ApplicationRecord
include Sortable
include Timebox
include Milestoneish
include FromUnion
include Importable
prepend_mod_with('Milestone') # rubocop: disable Cop/InjectEnterpriseEditionModule
class Predefined
ALL = [::Timebox::None, ::Timebox::Any, ::Timebox::Started, ::Timebox::Upcoming].freeze
end
has_many :milestone_releases
has_many :releases, through: :milestone_releases
has_internal_id :iid, scope: :project, track_if: -> { !importing? }
has_internal_id :iid, scope: :group, track_if: -> { !importing? }
has_many :events, as: :target, dependent: :delete_all # rubocop:disable Cop/ActiveRecordDependent
scope :active, -> { with_state(:active) }
scope :started, -> { active.where('milestones.start_date <= CURRENT_DATE') }
scope :not_started, -> { active.where('milestones.start_date > CURRENT_DATE') }
scope :not_upcoming, -> do
active
.where('milestones.due_date <= CURRENT_DATE')
.order(:project_id, :group_id, :due_date)
end
scope :order_by_name_asc, -> { order(Arel::Nodes::Ascending.new(arel_table[:title].lower)) }
scope :reorder_by_due_date_asc, -> { reorder(arel_table[:due_date].asc.nulls_last) }
scope :with_api_entity_associations, -> { preload(project: [:project_feature, :route, namespace: :route]) }
scope :order_by_dates_and_title, -> { order(due_date: :asc, start_date: :asc, title: :asc) }
validates :title, presence: true
validates_associated :milestone_releases, message: -> (_, obj) { obj[:value].map(&:errors).map(&:full_messages).join(",") }
validate :uniqueness_of_title, if: :title_changed?
state_machine :state, initial: :active do
event :close do
transition active: :closed
end
event :activate do
transition closed: :active
end
state :closed
state :active
end
# Searches for timeboxes with a matching title.
#
# This method uses ILIKE on PostgreSQL
#
# query - The search query as a String
#
# Returns an ActiveRecord::Relation.
def self.search_title(query)
fuzzy_search(query, [:title])
end
def self.min_chars_for_partial_matching
2
end
def self.reference_prefix
'%'
end
def self.reference_pattern
# NOTE: The iid pattern only matches when all characters on the expression
# are digits, so it will match %2 but not %2.1 because that's probably a
# milestone name and we want it to be matched as such.
@reference_pattern ||= %r{
(#{Project.reference_pattern})?
#{Regexp.escape(reference_prefix)}
(?:
(?<milestone_iid>
\d+(?!\S\w)\b # Integer-based milestone iid, or
) |
(?<milestone_name>
[^"\s\<]+\b | # String-based single-word milestone title, or
"[^"]+" # String-based multi-word milestone surrounded in quotes
)
)
}x
end
def self.link_reference_pattern
@link_reference_pattern ||= super("milestones", /(?<milestone>\d+)/)
end
def self.upcoming_ids(projects, groups)
unscoped
.for_projects_and_groups(projects, groups)
.active.where('milestones.due_date > CURRENT_DATE')
.order(:project_id, :group_id, :due_date).select('DISTINCT ON (project_id, group_id) id')
end
def self.with_web_entity_associations
preload(:group, project: [:project_feature, group: [:parent], namespace: :route])
end
def participants
User.joins(assigned_issues: :milestone).where(milestones: { id: id }).distinct
end
def self.sort_by_attribute(method)
sorted =
case method.to_s
when 'due_date_asc'
reorder_by_due_date_asc
when 'due_date_desc'
reorder(arel_table[:due_date].desc.nulls_last)
when 'name_asc'
reorder(Arel::Nodes::Ascending.new(arel_table[:title].lower))
when 'name_desc'
reorder(Arel::Nodes::Descending.new(arel_table[:title].lower))
when 'start_date_asc'
reorder(arel_table[:start_date].asc.nulls_last)
when 'start_date_desc'
reorder(arel_table[:start_date].desc.nulls_last)
else
order_by(method)
end
sorted.with_order_id_desc
end
def self.sort_with_expired_last(method)
# NOTE: this is a custom ordering of milestones
# to prioritize displaying non-expired milestones and milestones without due dates
sorted = reorder(Arel.sql("(CASE WHEN due_date IS NULL THEN 1 WHEN due_date >= CURRENT_DATE THEN 0 ELSE 2 END) ASC"))
sorted = if method.to_s == 'expired_last_due_date_desc'
sorted.order(due_date: :desc)
else
sorted.order(due_date: :asc)
end
sorted.with_order_id_desc
end
def self.states_count(projects, groups = nil)
return STATE_COUNT_HASH unless projects || groups
counts = Milestone
.for_projects_and_groups(projects, groups)
.reorder(nil)
.group(:state)
.count
{
opened: counts['active'] || 0,
closed: counts['closed'] || 0,
all: counts.values.sum
}
end
def for_display
self
end
def can_be_closed?
active? && issues.opened.count == 0
end
def author_id
nil
end
# TODO: remove after all code paths use `timebox_id`
# https://gitlab.com/gitlab-org/gitlab/-/issues/215688
alias_method :milestoneish_id, :timebox_id
# TODO: remove after all code paths use (group|project)_timebox?
# https://gitlab.com/gitlab-org/gitlab/-/issues/215690
alias_method :group_milestone?, :group_timebox?
alias_method :project_milestone?, :project_timebox?
def parent
if group_milestone?
group
else
project
end
end
def subgroup_milestone?
group_milestone? && parent.subgroup?
end
private
def issues_finder_params
{ project_id: project_id, group_id: group_id, include_subgroups: group_id.present? }.compact
end
# milestone titles must be unique across project and group milestones
def uniqueness_of_title
if project
relation = self.class.for_projects_and_groups([project_id], [project.group&.id])
elsif group
relation = self.class.for_projects_and_groups(group.projects.select(:id), [group.id])
end
title_exists = relation.find_by_title(title)
errors.add(:title, _("already being used for another group or project %{timebox_name}.") % { timebox_name: timebox_name }) if title_exists
end
end
class Milestone < ApplicationRecord
include Sortable
include Timebox
include Milestoneish
include FromUnion
include Importable
has_many :milestone_releases
has_many :releases, through: :milestone_releases
has_internal_id :iid, scope: :project, track_if: -> { !importing? }
has_internal_id :iid, scope: :group, track_if: -> { !importing? }
has_many :events, as: :target, dependent: :delete_all
scope :active, -> { with_state(:active) }
scope :started, -> { active.where('milestones.start_date <= CURRENT_DATE') }
scope :not_started, -> { active.where('milestones.start_date > CURRENT_DATE') }
scope :not_upcoming, -> do
active
.where('milestones.due_date <= CURRENT_DATE')
.order(:project_id, :group_id, :due_date)
end
scope :order_by_name_asc, -> { order(Arel::Nodes::Ascending.new(arel_table[:title].lower)) }
scope :reorder_by_due_date_asc, -> { reorder(arel_table[:due_date].asc.nulls_last) }
scope :with_api_entity_associations, -> { preload(project: [:project_feature, :route]) }
scope :order_by_dates_and_title, -> { order(due_date: :asc, start_date: :asc, title: :asc) }
validates :title, presence: true
validates_associated :milestone_releases, message: -> (_, obj) { ... }
validate :uniqueness_of_title, if: :title_changed?
state_machine :state, initial: :active do
event :close do
transition active: :closed
end
event :activate do
transition closed: :active
end
state :closed
state :active
end
class Milestone < ApplicationRecord
include Sortable
include Timebox
include Milestoneish
include FromUnion
include Importable
has_many :milestone_releases
has_many :releases, through: :milestone_releases
has_internal_id :iid, scope: :project, track_if: -> { !importing? }
has_internal_id :iid, scope: :group, track_if: -> { !importing? }
has_many :events, as: :target, dependent: :delete_all
scope :active, -> { with_state(:active) }
scope :started, -> { active.where('milestones.start_date <= CURRENT_DATE') }
scope :not_started, -> { active.where('milestones.start_date > CURRENT_DATE') }
scope :not_upcoming, -> do
active
.where('milestones.due_date <= CURRENT_DATE')
.order(:project_id, :group_id, :due_date)
end
scope :order_by_name_asc, -> { order(Arel::Nodes::Ascending.new(arel_table[:title].lower)) }
scope :reorder_by_due_date_asc, -> { reorder(arel_table[:due_date].asc.nulls_last) }
scope :with_api_entity_associations, -> { preload(project: [:project_feature, :route]) }
scope :order_by_dates_and_title, -> { order(due_date: :asc, start_date: :asc, title: :asc) }
validates :title, presence: true
validates_associated :milestone_releases, message: -> (_, obj) { ... }
validate :uniqueness_of_title, if: :title_changed?
state_machine :state, initial: :active do
event :close do
transition active: :closed
end
event :activate do
transition closed: :active
end
state :closed
state :active
end
CREATE TABLE milestones (
id integer NOT NULL,
title character varying NOT NULL,
project_id integer,
description text,
due_date date,
created_at timestamp without time zone,
updated_at timestamp without time zone,
state character varying,
iid integer,
title_html text,
description_html text,
start_date date,
cached_markdown_version integer,
group_id integer
);
Cas concret
33850 lignes
758 tables
😱😱😱
Quelques exemples…
614 tables
6.174 colonnes
~10 col/table, max: 71
26 tables
352 colonnes
~14 col/table, max: 31
21 tables
202 colonnes
~10 col/table, max: 22
148 tables
2.288 colonnes
~15 col/table, max: 109
4911 tables
150.158 colonnes
~31 col/table, max: 268
764 tables
14.695 colonnes
~19 col/table, max: 414
In real life…
442 tables!
Data catalog
Entity-Relationship Diagram
Bilan
Je retiens: dbdiagram.io - drawsql.app
erd
erd
erd
🎁 à venir…
100% Free
100% Privacy