mirror of
https://github.com/johndoe6345789/metabuilder.git
synced 2026-04-26 14:54:55 +00:00
459 lines
11 KiB
Markdown
459 lines
11 KiB
Markdown
# Entity Relationship Schema Specification
|
|
**Version**: 2.0
|
|
**Date**: 2026-02-04
|
|
|
|
---
|
|
|
|
## Relationship Types
|
|
|
|
MetaBuilder entity schemas support all standard relationship patterns using explicit `relations` declarations.
|
|
|
|
### 1. One-to-One (1:1)
|
|
|
|
**Pattern**: User has one Profile, Profile belongs to one User
|
|
|
|
```yaml
|
|
entity: User
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
username: {type: string, required: true}
|
|
relations:
|
|
profile:
|
|
type: one-to-one
|
|
entity: Profile
|
|
foreign_key: userId # Field in Profile that points to User
|
|
|
|
entity: Profile
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
userId: {type: uuid, required: true, unique: true}
|
|
bio: {type: text}
|
|
relations:
|
|
user:
|
|
type: belongs-to
|
|
entity: User
|
|
foreign_key: userId # This field
|
|
```
|
|
|
|
### 2. One-to-Many (1:N)
|
|
|
|
**Pattern**: User has many Posts, Post belongs to one User
|
|
|
|
```yaml
|
|
entity: User
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
username: {type: string}
|
|
relations:
|
|
posts:
|
|
type: has-many
|
|
entity: Post
|
|
foreign_key: authorId # Field in Post
|
|
|
|
entity: Post
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
title: {type: string}
|
|
authorId: {type: uuid, required: true}
|
|
relations:
|
|
author:
|
|
type: belongs-to
|
|
entity: User
|
|
foreign_key: authorId # This field
|
|
```
|
|
|
|
### 3. Many-to-Many (M:N)
|
|
|
|
**Pattern**: User has many Roles, Role has many Users (through UserRole join table)
|
|
|
|
```yaml
|
|
entity: User
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
username: {type: string}
|
|
relations:
|
|
roles:
|
|
type: many-to-many
|
|
entity: Role
|
|
through: UserRole # Join table name
|
|
foreign_key: userId # FK in UserRole
|
|
target_key: roleId # Other FK in UserRole
|
|
|
|
entity: Role
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
name: {type: string}
|
|
relations:
|
|
users:
|
|
type: many-to-many
|
|
entity: User
|
|
through: UserRole
|
|
foreign_key: roleId
|
|
target_key: userId
|
|
|
|
entity: UserRole
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
userId: {type: uuid, required: true}
|
|
roleId: {type: uuid, required: true}
|
|
assignedAt: {type: bigint}
|
|
indexes:
|
|
- fields: [userId, roleId]
|
|
unique: true
|
|
relations:
|
|
user:
|
|
type: belongs-to
|
|
entity: User
|
|
foreign_key: userId
|
|
role:
|
|
type: belongs-to
|
|
entity: Role
|
|
foreign_key: roleId
|
|
```
|
|
|
|
### 4. Self-Referential (Tree/Graph)
|
|
|
|
**Pattern**: Category has parent Category (recursive hierarchy)
|
|
|
|
```yaml
|
|
entity: Category
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
name: {type: string}
|
|
parentId: {type: uuid, nullable: true}
|
|
relations:
|
|
parent:
|
|
type: belongs-to
|
|
entity: Category # Self-reference
|
|
foreign_key: parentId
|
|
nullable: true
|
|
children:
|
|
type: has-many
|
|
entity: Category # Self-reference
|
|
foreign_key: parentId
|
|
```
|
|
|
|
### 5. Polymorphic (Generic Relations)
|
|
|
|
**Pattern**: Comment can belong to Post OR Video OR Product
|
|
|
|
```yaml
|
|
entity: Comment
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
content: {type: text}
|
|
commentableId: {type: uuid, required: true}
|
|
commentableType: {type: string, required: true} # "Post", "Video", "Product"
|
|
indexes:
|
|
- fields: [commentableId, commentableType]
|
|
relations:
|
|
commentable:
|
|
type: polymorphic
|
|
foreign_key: commentableId
|
|
type_key: commentableType
|
|
entities:
|
|
- Post
|
|
- Video
|
|
- Product
|
|
|
|
entity: Post
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
title: {type: string}
|
|
relations:
|
|
comments:
|
|
type: has-many
|
|
entity: Comment
|
|
polymorphic: true
|
|
as: commentable # Matches relation name in Comment
|
|
```
|
|
|
|
### 6. Through Relationships (Has-Many-Through)
|
|
|
|
**Pattern**: Country has many Users through Cities
|
|
|
|
```yaml
|
|
entity: Country
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
name: {type: string}
|
|
relations:
|
|
cities:
|
|
type: has-many
|
|
entity: City
|
|
foreign_key: countryId
|
|
users:
|
|
type: has-many-through
|
|
entity: User
|
|
through: City # Intermediate entity
|
|
source_key: countryId # FK in City → Country
|
|
target_key: cityId # FK in User → City
|
|
|
|
entity: City
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
name: {type: string}
|
|
countryId: {type: uuid, required: true}
|
|
relations:
|
|
country:
|
|
type: belongs-to
|
|
entity: Country
|
|
foreign_key: countryId
|
|
users:
|
|
type: has-many
|
|
entity: User
|
|
foreign_key: cityId
|
|
|
|
entity: User
|
|
fields:
|
|
id: {type: uuid, primary: true}
|
|
name: {type: string}
|
|
cityId: {type: uuid, required: true}
|
|
relations:
|
|
city:
|
|
type: belongs-to
|
|
entity: City
|
|
foreign_key: cityId
|
|
country:
|
|
type: has-one-through
|
|
entity: Country
|
|
through: City
|
|
source_key: cityId # User.cityId
|
|
target_key: countryId # City.countryId
|
|
```
|
|
|
|
---
|
|
|
|
## Relation Field Specification
|
|
|
|
### Required Fields
|
|
|
|
```yaml
|
|
relations:
|
|
<relation_name>:
|
|
type: <relationship_type> # Required
|
|
entity: <target_entity> # Required
|
|
foreign_key: <field_name> # Required (except polymorphic)
|
|
```
|
|
|
|
### Optional Fields
|
|
|
|
```yaml
|
|
relations:
|
|
<relation_name>:
|
|
# Optional configuration
|
|
nullable: true|false # Can relation be null? (default: false)
|
|
cascade_delete: true|false # Delete related records? (default: false)
|
|
cascade_update: true|false # Update related FKs? (default: false)
|
|
on_delete: cascade|set_null|restrict|no_action # FK constraint
|
|
on_update: cascade|set_null|restrict|no_action # FK constraint
|
|
eager_load: true|false # Auto-load in queries? (default: false)
|
|
inverse_of: <relation_name> # Name of inverse relation
|
|
```
|
|
|
|
### Polymorphic-Specific Fields
|
|
|
|
```yaml
|
|
relations:
|
|
commentable:
|
|
type: polymorphic
|
|
foreign_key: commentableId
|
|
type_key: commentableType # Required - field storing entity type
|
|
entities: [Post, Video] # Required - allowed entity types
|
|
```
|
|
|
|
### Many-to-Many Specific Fields
|
|
|
|
```yaml
|
|
relations:
|
|
roles:
|
|
type: many-to-many
|
|
entity: Role
|
|
through: UserRole # Required - join table
|
|
foreign_key: userId # Required - FK in join table
|
|
target_key: roleId # Required - other FK in join table
|
|
attributes: [assignedAt] # Optional - extra fields from join table
|
|
```
|
|
|
|
---
|
|
|
|
## Relationship Types Summary
|
|
|
|
| Type | Description | Example |
|
|
|------|-------------|---------|
|
|
| `belongs-to` | N:1 relationship | Post → User (many posts belong to one user) |
|
|
| `has-one` | 1:1 relationship | User → Profile (one user has one profile) |
|
|
| `has-many` | 1:N relationship | User → Posts (one user has many posts) |
|
|
| `many-to-many` | M:N via join table | User ↔ Roles (via UserRole) |
|
|
| `one-to-one` | 1:1 bidirectional | User ↔ Profile |
|
|
| `polymorphic` | N:1 to multiple types | Comment → Post\|Video\|Product |
|
|
| `has-many-through` | 1:N via intermediate | Country → Users (through Cities) |
|
|
| `has-one-through` | 1:1 via intermediate | User → Country (through City) |
|
|
|
|
---
|
|
|
|
## FK Constraints
|
|
|
|
### on_delete Behavior
|
|
|
|
| Value | Behavior |
|
|
|-------|----------|
|
|
| `cascade` | Delete related records when parent deleted |
|
|
| `set_null` | Set FK to NULL when parent deleted |
|
|
| `restrict` | Prevent deletion if related records exist |
|
|
| `no_action` | Database decides (usually same as restrict) |
|
|
|
|
### on_update Behavior
|
|
|
|
| Value | Behavior |
|
|
|-------|----------|
|
|
| `cascade` | Update FK when parent ID changes |
|
|
| `set_null` | Set FK to NULL when parent ID changes |
|
|
| `restrict` | Prevent update if related records exist |
|
|
| `no_action` | Database decides |
|
|
|
|
---
|
|
|
|
## Example: Full Forum Schema with Relations
|
|
|
|
```yaml
|
|
entity: ForumCategory
|
|
fields:
|
|
id: {type: cuid, primary: true}
|
|
name: {type: string, required: true}
|
|
parentId: {type: cuid, nullable: true}
|
|
relations:
|
|
parent:
|
|
type: belongs-to
|
|
entity: ForumCategory
|
|
foreign_key: parentId
|
|
nullable: true
|
|
on_delete: set_null
|
|
children:
|
|
type: has-many
|
|
entity: ForumCategory
|
|
foreign_key: parentId
|
|
cascade_delete: false
|
|
threads:
|
|
type: has-many
|
|
entity: ForumThread
|
|
foreign_key: categoryId
|
|
cascade_delete: true
|
|
|
|
---
|
|
|
|
entity: ForumThread
|
|
fields:
|
|
id: {type: cuid, primary: true}
|
|
categoryId: {type: cuid, required: true}
|
|
authorId: {type: uuid, required: true}
|
|
title: {type: string, required: true}
|
|
relations:
|
|
category:
|
|
type: belongs-to
|
|
entity: ForumCategory
|
|
foreign_key: categoryId
|
|
on_delete: cascade
|
|
author:
|
|
type: belongs-to
|
|
entity: User
|
|
foreign_key: authorId
|
|
on_delete: restrict # Can't delete user with threads
|
|
posts:
|
|
type: has-many
|
|
entity: ForumPost
|
|
foreign_key: threadId
|
|
cascade_delete: true # Delete posts when thread deleted
|
|
|
|
---
|
|
|
|
entity: ForumPost
|
|
fields:
|
|
id: {type: cuid, primary: true}
|
|
threadId: {type: cuid, required: true}
|
|
authorId: {type: uuid, required: true}
|
|
content: {type: text, required: true}
|
|
relations:
|
|
thread:
|
|
type: belongs-to
|
|
entity: ForumThread
|
|
foreign_key: threadId
|
|
on_delete: cascade
|
|
author:
|
|
type: belongs-to
|
|
entity: User
|
|
foreign_key: authorId
|
|
on_delete: restrict
|
|
```
|
|
|
|
---
|
|
|
|
## Implementation in C++ DBAL
|
|
|
|
### EntitySchemaLoader Enhancement
|
|
|
|
Add `RelationDef` struct:
|
|
|
|
```cpp
|
|
struct RelationDef {
|
|
std::string name;
|
|
std::string type; // belongs-to, has-one, has-many, etc.
|
|
std::string entity; // Target entity name
|
|
std::string foreignKey; // FK field name
|
|
std::optional<std::string> targetKey; // For M:N
|
|
std::optional<std::string> through; // Join table for M:N
|
|
std::optional<std::string> typeKey; // For polymorphic
|
|
std::vector<std::string> entities; // For polymorphic
|
|
bool nullable = false;
|
|
bool cascadeDelete = false;
|
|
bool cascadeUpdate = false;
|
|
std::string onDelete = "restrict";
|
|
std::string onUpdate = "no_action";
|
|
bool eagerLoad = false;
|
|
};
|
|
|
|
struct EntitySchema {
|
|
std::string name;
|
|
std::vector<EntityField> fields;
|
|
std::vector<EntityIndex> indexes;
|
|
std::vector<RelationDef> relations; // ← NEW
|
|
ACLDefinition acl;
|
|
};
|
|
```
|
|
|
|
### Usage in Generic Operations
|
|
|
|
```cpp
|
|
// Eager loading example
|
|
auto result = adapter.read("users", userId);
|
|
if (result.isOk()) {
|
|
auto user = result.getValue();
|
|
|
|
// Auto-load relations marked eager_load: true
|
|
auto schema = adapter.getEntitySchema("users");
|
|
for (const auto& rel : schema.relations) {
|
|
if (rel.eagerLoad) {
|
|
if (rel.type == "belongs-to") {
|
|
auto fkValue = user[rel.foreignKey];
|
|
auto related = adapter.read(rel.entity, fkValue.get<std::string>());
|
|
user[rel.name] = related.getValue();
|
|
}
|
|
else if (rel.type == "has-many") {
|
|
Json filter = {{rel.foreignKey, user["id"]}};
|
|
auto related = adapter.list(rel.entity, {.filter = filter});
|
|
user[rel.name] = related.getValue().items;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
```
|
|
|
|
---
|
|
|
|
## Next Steps
|
|
|
|
1. **Update existing entity YAML files** to include relations
|
|
2. **Update EntitySchemaLoader** to parse relations
|
|
3. **Add relationship helpers** to generic adapters (e.g., `loadRelation()`, `saveWithRelations()`)
|
|
4. **Create Prisma schema generator** from YAML schemas (see next task)
|