Export erstellt am: 2026-05-24 12:21:18
Datenbank: microlernen
Version: 8.4.8


==================== OBJECT SUMMARY ====================
Tabellen : 6
Views    : 1
Funktionen: 0


==================== ROW COUNTS ====================
items                               : 44
packs                               : 2
progress                            : 2
reviews                             : 2
user_settings                       : 3
users                               : 1
v_due_items                         : 44


==================== SHOW CREATE TABLE items ====================
CREATE TABLE `items` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `pack_id` int NOT NULL,
  `item_type` enum('card','cloze','mc','note') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'card',
  `front` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `back` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `hint` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `tags` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `media_type` enum('none','image','video','audio') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'none',
  `media_ref` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `direction` enum('de2pt','pt2de') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'de2pt',
  `sense` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `example` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `pack_id` (`pack_id`),
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`pack_id`) REFERENCES `packs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


==================== SHOW CREATE TABLE packs ====================
CREATE TABLE `packs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `slug` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `src_lang` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tgt_lang` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint NOT NULL DEFAULT '1',
  `mix_pt2de_percent` tinyint NOT NULL DEFAULT '30',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


==================== SHOW CREATE TABLE progress ====================
CREATE TABLE `progress` (
  `user_id` int NOT NULL,
  `item_id` bigint NOT NULL,
  `ease` float NOT NULL DEFAULT '2.5',
  `interval_days` int NOT NULL DEFAULT '0',
  `repetitions` int NOT NULL DEFAULT '0',
  `due_at` datetime DEFAULT NULL,
  `last_result` enum('again','hard','good','easy') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_seen_at` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`,`item_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `progress_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `progress_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


==================== SHOW CREATE TABLE reviews ====================
CREATE TABLE `reviews` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `item_id` bigint NOT NULL,
  `result` enum('again','hard','good','easy') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `ms` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


==================== SHOW CREATE TABLE user_settings ====================
CREATE TABLE `user_settings` (
  `user_id` int NOT NULL,
  `setting_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `setting_value` text COLLATE utf8mb4_unicode_ci,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`setting_key`),
  CONSTRAINT `fk_user_settings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


==================== SHOW CREATE TABLE users ====================
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


==================== SHOW CREATE VIEW v_due_items ====================
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_due_items` AS select `i`.`id` AS `id`,`i`.`pack_id` AS `pack_id`,`i`.`item_type` AS `item_type`,`i`.`front` AS `front`,`i`.`back` AS `back`,`i`.`hint` AS `hint`,`i`.`tags` AS `tags`,`i`.`media_type` AS `media_type`,`i`.`media_ref` AS `media_ref`,`i`.`created_at` AS `created_at`,`p`.`due_at` AS `due_at`,`p`.`ease` AS `ease`,`p`.`interval_days` AS `interval_days`,`p`.`repetitions` AS `repetitions` from (`items` `i` left join `progress` `p` on((`p`.`item_id` = `i`.`id`))) where ((`p`.`due_at` is null) or (`p`.`due_at` <= now()));


==================== TRIGGERS ====================
Keine Trigger gefunden.


==================== FUNCTION STATUS ====================
Keine Funktionen gefunden.


==================== COLUMNS ====================
items | 1 | id | bigint | NO |  | auto_increment |  | 
items | 2 | pack_id | int | NO |  |  |  | 
items | 3 | item_type | enum('card','cloze','mc','note') | NO | card |  | utf8mb4_unicode_ci | 
items | 4 | front | text | YES |  |  | utf8mb4_unicode_ci | 
items | 5 | back | text | YES |  |  | utf8mb4_unicode_ci | 
items | 6 | hint | text | YES |  |  | utf8mb4_unicode_ci | 
items | 7 | tags | varchar(255) | YES |  |  | utf8mb4_unicode_ci | 
items | 8 | media_type | enum('none','image','video','audio') | NO | none |  | utf8mb4_unicode_ci | 
items | 9 | media_ref | varchar(512) | YES |  |  | utf8mb4_unicode_ci | 
items | 10 | created_at | datetime | NO | CURRENT_TIMESTAMP | DEFAULT_GENERATED |  | 
items | 11 | direction | enum('de2pt','pt2de') | NO | de2pt |  | utf8mb4_unicode_ci | 
items | 12 | sense | varchar(255) | YES |  |  | utf8mb4_unicode_ci | 
items | 13 | example | text | YES |  |  | utf8mb4_unicode_ci | 
packs | 1 | id | int | NO |  | auto_increment |  | 
packs | 2 | slug | varchar(64) | NO |  |  | utf8mb4_unicode_ci | 
packs | 3 | title | varchar(255) | NO |  |  | utf8mb4_unicode_ci | 
packs | 4 | src_lang | varchar(16) | NO |  |  | utf8mb4_unicode_ci | 
packs | 5 | tgt_lang | varchar(16) | NO |  |  | utf8mb4_unicode_ci | 
packs | 6 | is_active | tinyint | NO | 1 |  |  | 
packs | 7 | mix_pt2de_percent | tinyint | NO | 30 |  |  | 
packs | 8 | created_at | datetime | NO | CURRENT_TIMESTAMP | DEFAULT_GENERATED |  | 
packs | 9 | updated_at | datetime | NO | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |  | 
progress | 1 | user_id | int | NO |  |  |  | 
progress | 2 | item_id | bigint | NO |  |  |  | 
progress | 3 | ease | float | NO | 2.5 |  |  | 
progress | 4 | interval_days | int | NO | 0 |  |  | 
progress | 5 | repetitions | int | NO | 0 |  |  | 
progress | 6 | due_at | datetime | YES |  |  |  | 
progress | 7 | last_result | enum('again','hard','good','easy') | YES |  |  | utf8mb4_unicode_ci | 
progress | 8 | last_seen_at | datetime | YES |  |  |  | 
reviews | 1 | id | bigint | NO |  | auto_increment |  | 
reviews | 2 | user_id | int | NO |  |  |  | 
reviews | 3 | item_id | bigint | NO |  |  |  | 
reviews | 4 | result | enum('again','hard','good','easy') | NO |  |  | utf8mb4_unicode_ci | 
reviews | 5 | ms | int | YES |  |  |  | 
reviews | 6 | created_at | datetime | NO | CURRENT_TIMESTAMP | DEFAULT_GENERATED |  | 
user_settings | 1 | user_id | int | NO |  |  |  | 
user_settings | 2 | setting_key | varchar(100) | NO |  |  | utf8mb4_unicode_ci | 
user_settings | 3 | setting_value | text | YES |  |  | utf8mb4_unicode_ci | 
user_settings | 4 | updated_at | datetime | NO | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |  | 
users | 1 | id | int | NO |  | auto_increment |  | 
users | 2 | username | varchar(64) | NO |  |  | utf8mb4_unicode_ci | 


==================== INDEXES ====================
items | pack_id | 1 | 1 | pack_id | BTREE
items | PRIMARY | 0 | 1 | id | BTREE
packs | PRIMARY | 0 | 1 | id | BTREE
packs | slug | 0 | 1 | slug | BTREE
progress | item_id | 1 | 1 | item_id | BTREE
progress | PRIMARY | 0 | 1 | user_id | BTREE
progress | PRIMARY | 0 | 2 | item_id | BTREE
reviews | item_id | 1 | 1 | item_id | BTREE
reviews | PRIMARY | 0 | 1 | id | BTREE
reviews | user_id | 1 | 1 | user_id | BTREE
user_settings | PRIMARY | 0 | 1 | user_id | BTREE
user_settings | PRIMARY | 0 | 2 | setting_key | BTREE
users | PRIMARY | 0 | 1 | id | BTREE
users | username | 0 | 1 | username | BTREE


Datei wurde auch gespeichert unter:
/var/www/html/microlernen/db_schema_export_20260524_122118.txt