LinkML 与 OWL 实践：从 PostgreSQL 整理业务领域模型

SELECT p.name AS project, e.name AS owner
FROM projects AS p
JOIN customers AS c ON c.id = p.customer_id
JOIN employees AS e ON e.id = p.owner_id
WHERE c.name = '远山制造';

SELECT p.name AS project, e.name AS participant,
       m.role_code, m.allocation_pct, m.joined_on, m.left_on
FROM projects AS p
JOIN customers AS c ON c.id = p.customer_id
JOIN project_memberships AS m ON m.project_id = p.id
JOIN employees AS e ON e.id = m.employee_id
WHERE c.name = '远山制造';

id: https://example.org/work/schema
name: work_domain
prefixes:
  linkml: https://w3id.org/linkml/
  ex: https://example.org/work/
imports:
  - linkml:types
default_prefix: ex
default_range: string

mkdir linkml-tutorial
cd linkml-tutorial
python3 -m venv venv
source venv/bin/activate
pip install linkml
linkml-convert --help

id: https://w3id.org/linkml/examples/personinfo
name: personinfo
prefixes:
  linkml: https://w3id.org/linkml/
  personinfo: https://w3id.org/linkml/examples/personinfo
imports:
  - linkml:types
default_range: string
default_prefix: personinfo

classes:
  Person:
    attributes:
      id:
      full_name:
      aliases:
      phone:
      age:

id: ORCID:1234
full_name: Clark Kent
age: "32"
phone: 555-555-5555

gen-json-schema personinfo.yaml
linkml-validate -s personinfo.yaml data.yaml
linkml-convert -s personinfo.yaml data.yaml -t ttl

"age": {
  "type": ["string", "null"]
}

made_up_field: hello

@prefix personinfo: <https://w3id.org/linkml/examples/personinfo> .

_:c14n0 a personinfo:Person ;
    personinfo:age "32" ;
    personinfo:full_name "Clark Kent" ;
    personinfo:id "ORCID:1234" ;
    personinfo:phone "555-555-5555" .

@prefix ex: <https://example.org/work/> .

<https://example.org/work/project/101>
    ex:owner <https://example.org/work/employee/1> .

@prefix ex: <https://example.org/work/> .
@prefix owl: <http://www.w3.org/2002/07/owl#> .

ex:Project a owl:Class .

gen-owl my_schema.yaml

CREATE TABLE projects (
 id integer PRIMARY KEY,
 name text NOT NULL,
 customer_id integer NOT NULL REFERENCES customers(id),
 owner_id integer NOT NULL REFERENCES employees(id),
 status_code text NOT NULL REFERENCES project_statuses(code),
 budget numeric(12,2) NOT NULL CHECK (budget >= 0),
 start_date date NOT NULL,
 end_date date,
 CHECK (end_date IS NULL OR end_date >= start_date)
);

CREATE TABLE project_memberships (
 id integer PRIMARY KEY,
 project_id integer NOT NULL REFERENCES projects(id),
 employee_id integer NOT NULL REFERENCES employees(id),
 role_code text NOT NULL CHECK (role_code IN ('lead','engineer','analyst')),
 allocation_pct integer NOT NULL CHECK (allocation_pct BETWEEN 1 AND 100),
 joined_on date NOT NULL,
 left_on date,
 UNIQUE (project_id, employee_id, joined_on),
 CHECK (left_on IS NULL OR left_on >= joined_on)
);

CREATE TABLE tasks (
 id integer PRIMARY KEY,
 project_id integer NOT NULL REFERENCES projects(id),
 assignee_id integer REFERENCES employees(id),
 title text NOT NULL,
 status_code text NOT NULL CHECK (status_code IN ('todo','doing','done')),
 estimated_hours numeric(6,2) NOT NULL CHECK (estimated_hours >= 0),
 due_date date NOT NULL
);

建模目标
为项目管理数据整理一个可审阅的LinkML模型，随后生成OWL。
模型要区分负责人、参与者和任务执行人。

结构材料
完整DDL、主外键、NOT NULL、CHECK、唯一约束及表列注释。

事实材料
员工、项目、参与和任务样本；包含未指派任务、结束参与记录。

业务说明
字段单位、关系含义、有效期约定、已确认规则及待确认问题。

from schema_automator.importers.sql_import_engine import SqlImportEngine
from linkml_runtime.dumpers import yaml_dumper

engine = SqlImportEngine()
try:
    schema = engine.convert(connection_url)
finally:
    if engine.engine is not None:
        engine.engine.dispose()

yaml_dumper.dump(schema, to_file="database-import.yaml")

你协助我整理项目管理领域的LinkML模型。模型随后用于生成OWL，
也会作为人工审阅业务概念与关系的材料。

输入材料：
1. 上文七张表的完整DDL与注释；
2. 员工、项目、参与记录和任务样本；
3. 下列业务说明及待确认问题。

已确认说明：
- Project.owner表示项目负责人。
- Participation.employee表示某次参与的员工。
- Task.assignee表示具体任务执行人，允许为空。
- Participation需要保存角色、投入比例、加入与退出日期。
- allocation_pct单位为%，范围1至100，仅约束单条参与记录。
- budget_cny单位为人民币元；estimated_hours单位为小时。
- 部门关系表示直接上级，不默认定义传递推理。
- 允许缺省标量范围继承顶层name slot。Task.title可映射为Task.name，但不得同时保留两个独立字段。
- ProjectStatus、ParticipationRole、TaskStatus的枚举建模是本案例候选方案，不代表DDL本身已证明它们在业务上不可扩展；请将此点列为建模选择。
- NamedEntity与Dataset是模型组织选择，不是数据库表；请逐项检查继承后的属性是否适用于各子类。

请分五部分输出：
A. 业务类、关系与术语候选，每项注明DDL、注释或业务说明依据。
B. 表和字段到模型元素的映射表，包括类型、必填性及重命名。
C. 歧义和待确认问题；材料不足时明确写未知。
D. 一份完整domain.yaml，包含前缀、linkml:types、枚举、类和slot。
E. 可用schema表达的声明，以及需数据库或应用代码检查的规则。

模型组织要求：
- 将Department、Employee、Customer、Project、Participation、Task作为业务类候选；逐项说明为何将project_memberships抽象为Participation。
- NamedEntity为可选的抽象复用方案。特别检查继承的name是否会错误要求Participation提供名称；如会，给出最小调整，不要为了沿用草稿而牺牲语义。
- Dataset仅作为实例集合容器候选，六类业务记录分别为列表；说明它不是DDL中的表，也不是OWL业务概念。
- 对象标识使用ex:employee/2等稳定CURIE，说明数据库ID到CURIE的映射方式。
- 区分项目状态、参与角色、任务状态三个候选枚举，并指出使用枚举是本案例设计选择，是否适合需由业务维护方式确认。

不要根据样本增加全局投入≤100%的规则。
不要假定owner与lead角色必然相同。
不要把外键都重命名为“负责”，不要删除关系上的有效期和比例。
不要执行数据库操作。先输出候选，不声称已经验证或生成OWL。

请审查上面的domain.yaml，不要直接重写全文。

逐项对照DDL、样本和业务说明，输出：
1. 遗漏的字段、错误类型、错误必填性及关系角色混淆；
2. 没有依据却新增的规则；
3. 标识、前缀、引用和枚举是否自洽；
4. NamedEntity与Dataset是否被误认为业务表；
5. 哪些约束无法仅靠当前YAML完成实例检查；
6. 与已有RDF实例的class_uri/slot_uri及枚举表示可能出现的差异。

每个问题给出位置、依据、影响与最小修改建议。
无法由输入确定的内容列为待确认，不自行决定。
不要把“可以解析”写成“业务正确”，不要声称运行了任何工具。

id: https://example.org/work/schema
name: work_domain
prefixes:
  linkml: https://w3id.org/linkml/
  ex: https://example.org/work/
  schema: https://schema.org/
default_prefix: ex
default_range: string
imports:
  - linkml:types

enums:
  ProjectStatus:
    permissible_values:
      planned: {description: 计划中}
      active: {description: 进行中}
      completed: {description: 已完成}
  ParticipationRole:
    permissible_values:
      lead: {description: 项目负责人角色}
      engineer: {description: 工程师}
      analyst: {description: 分析师}
  TaskStatus:
    permissible_values:
      todo: {description: 待处理}
      doing: {description: 进行中}
      done: {description: 已完成}

slots:
  id:
    identifier: true
    required: true
    range: uriorcurie
  name:
    required: true
    slot_uri: schema:name

classes:
  NamedEntity:
    abstract: true
    slots: [id, name]
  Department:
    is_a: NamedEntity
    description: 内部组织单元；parent_department 表示直接上级，不预设传递推理。
    attributes:
      parent_department: {range: Department}
  Employee:
    is_a: NamedEntity
    class_uri: schema:Person
    attributes:
      email: {required: true, pattern: '^.+@.+\..+$'}
      department: {range: Department, required: true}
      manager: {range: Employee}
      hired_on: {range: date, required: true}
  Customer:
    is_a: NamedEntity
    class_uri: schema:Organization
    attributes:
      industry: {required: true}
  Project:
    is_a: NamedEntity
    attributes:
      customer: {range: Customer, required: true}
      owner: {range: Employee, required: true}
      status: {range: ProjectStatus, required: true}
      budget_cny: {range: decimal, minimum_value: 0, required: true}
      start_date: {range: date, required: true}
      end_date: {range: date}
  Participation:
    description: 带角色、投入比例及有效期的员工参与项目关系，须保留为独立实体。
    slots: [id]
    attributes:
      project: {range: Project, required: true}
      employee: {range: Employee, required: true}
      role: {range: ParticipationRole, required: true}
      allocation_pct: {range: integer, minimum_value: 1, maximum_value: 100, required: true}
      joined_on: {range: date, required: true}
      left_on: {range: date}
  Task:
    is_a: NamedEntity
    attributes:
      project: {range: Project, required: true}
      assignee: {range: Employee}
      task_status: {range: TaskStatus, required: true}
      estimated_hours: {range: decimal, minimum_value: 0, required: true}
      due_date: {range: date, required: true}
  Dataset:
    tree_root: true
    attributes:
      departments: {range: Department, multivalued: true, inlined_as_list: true}
      employees: {range: Employee, multivalued: true, inlined_as_list: true}
      customers: {range: Customer, multivalued: true, inlined_as_list: true}
      projects: {range: Project, multivalued: true, inlined_as_list: true}
      participations: {range: Participation, multivalued: true, inlined_as_list: true}
      tasks: {range: Task, multivalued: true, inlined_as_list: true}

{
  "employees": [
    {
      "id": "ex:employee/2",
      "name": "林晓",
      "email": "lin@example.test",
      "department": "ex:department/4",
      "manager": "ex:employee/1",
      "hired_on": "2021-06-15"
    }
  ],
  "participations": [
    {
      "id": "ex:participation/1002",
      "project": "ex:project/101",
      "employee": "ex:employee/2",
      "role": "engineer",
      "allocation_pct": 60,
      "joined_on": "2026-01-01"
    }
  ]
}

cd demos/postgres-ontology
python3 -m venv .venv
.venv/bin/python -m pip install -r requirements.txt
# 连接既有linkml_demo，读取数据并写本地结果文件
.venv/bin/python run.py

python3 -m venv .venv-tools
.venv-tools/bin/python -m pip install -r requirements-tools.txt
.venv-tools/bin/gen-owl --help

.venv-tools/bin/gen-owl \
  --no-metaclasses \
  --no-type-objects \
  --use-native-uris \
  --default-permissible-value-type owl:Class \
  --no-add-ols-annotations \
  --skip-vacuous-local-range-axioms \
  --skip-vacuous-min-zero-cardinality-axioms \
  --consolidate-cardinality-axioms \
  --skip-abstract-class-as-unionof-subclasses \
  -f ttl domain.yaml \
  > domain.generated.owl.ttl 2> domain.generated.owl.log

@prefix ex: <https://example.org/work/> .
@prefix owl: <http://www.w3.org/2002/07/owl#> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .

ex:Employee a owl:Class ;
    rdfs:subClassOf ex:NamedEntity .

ex:Participation a owl:Class ;
    rdfs:subClassOf
        [ a owl:Restriction ;
          owl:allValuesFrom ex:Employee ;
          owl:onProperty ex:employee ],
        [ a owl:Restriction ;
          owl:cardinality 1 ;
          owl:onProperty ex:employee ] .

ex:ProjectStatus a owl:Class ;
    owl:unionOf (
        <https://example.org/work/ProjectStatus#planned>
        <https://example.org/work/ProjectStatus#active>
        <https://example.org/work/ProjectStatus#completed>
    ) .

<https://example.org/work/ProjectStatus#active> a owl:Class ;
    rdfs:subClassOf ex:ProjectStatus .

<https://example.org/work/project/101>
    ex:status <https://example.org/work/project/101/value/status> .

<https://example.org/work/project/101/value/status>
    a <https://example.org/work/ProjectStatus#active> .

ex:Participation rdfs:label "项目参与记录"@zh,
                            "Participation"@en .

from pathlib import Path
from rdflib import Graph
from rdflib.namespace import RDF, OWL

root = Path(".")  # 在案例目录运行
for filename in (
    "database-import.owl.ttl",
    "domain.owl.ttl",
    "instances.ttl",
    "instances-aligned.ttl",
):
    graph = Graph().parse(root / filename, format="turtle")
    restrictions = set(graph.subjects(RDF.type, OWL.Restriction))
    print(filename, len(graph), len(restrictions))

owl:allValuesFrom [
    a rdfs:Datatype ;
    owl:intersectionOf (
        xsd:decimal
        [ a rdfs:Datatype ;
          owl:onDatatype xsd:integer ;
          owl:withRestrictions ( [ xsd:minInclusive 0 ] ) ]
    )
] .