(ãµã ãã€ã«ã¯DALLE-3ã«ãã£ãŠçæ) ããã«ã¡ã¯ãAI Shiftã®åæŸã§ãã ãã®èšäºã¯ AI Shift Advent Calendar ã®4æ¥ç®ã®èšäºã§ãã ä»åã¯Anthropic瀟ããçºè¡šãããMCP(Model Context Protocol)ã«ã€ããŠåãäžããããšæããŸãã MCP ã¯LLMãšå€éšããŒã¿ãœãŒã¹ãããŒã«ã®ã·ãŒã ã¬ã¹ãªé£æºãè¡ãããã®ãããã³ã«ã§ããLLMã¯äººéã§èšãã°è³ã¿ãã«åœããéšåã§ãããã®èœåãåŒãåºãããã«ã¯å€éšã®ããŒã¿ãšã®é£æºãäžå¯æ¬ ã§ãã人éãé ã§èããã ãã§ã¯ãªããå¿
èŠãªæ
å ±ã«ã¢ã¯ã»ã¹ããªããããããåé¡è§£æ±ºãè¡ã£ãŠããŸãã ãããŸã§ãLLMãšå€éšããŒã¿ãçµã¿åãããåãçµã¿ã¯ãããŠããŠããã Function Calling ãšåŒã°ããæ©æ§ãçšããããšã§ããŒã«ã®éžæãå€éšæ
å ±ã«åãåãããããã®æ
å ±æœåºãªã©ãå®çŸ©ããŠå®è¡ããããšãã§ããŸãããäžæ¹ã§Function Callingã§ã¯éœåºŠå€éšæ
å ±ã«ã¢ã¯ã»ã¹ããããã«é¢æ°ã®å®çŸ©ã詳现ã«ããå¿
èŠããããŸããã MCPã§ã¯ãåæ
å ±ãœãŒã¹ã«å¯ŸããŠå®è¡å¯èœãªåŠçã"ããŒã«"ãšãã圢ã§å®çŸ©ãããã®ããŒã«ãã©ã®ããã«äœ¿ãããšããã®ã¯LLMã«å€æãããŠå®è¡ãé²è¡ããŸãã "AIãšãŒãžã§ã³ã"ãæ§ç¯ããã«ããã£ãŠãèªåèªèº«ã§å€æããè¡åéžæãããèªãå€éšã®æ
å ±ã«ã¢ã¯ã»ã¹ããæ¬¡ã®æææ±ºå®ã«ã€ãªããŠããããšãã£ãäžé£ã®ã¢ãããŒããMCPãçšããããšã§å¯èœã«ãªããŸãã å
·äœçãªã»ããã¢ããã«ã€ããŠã¯ä»ã®èšäºã§ãæ¢ã«åèã«ãªããã®ã倿°åºãŠããŠãããããããã«é¢ããŠã¯ç°¡æœã«ãŸãšããŸãã æ¬èšäºã§ã¯MCPã®åäœãçè§£ããããã«SQLiteãšã®æ¥ç¶ãè¡ããäºçŽå¯Ÿè©±ãè¡ãAIãšãŒãžã§ã³ãã®æ§ç¯ãšãã¬ãŒã¹ãè¡ããŸãã ä»åã®èšäºã§ã¯ä»¥äžã®å
容ã«è§ŠããŸãã MCPãçšããŠããŒã«ã«ã«æ§ç¯ããSQLiteã«ã¢ã¯ã»ã¹ äºçŽç®¡çãè¡ãããã®æå°éã®ããŒã¿ããŒã¹ã®æ§ç¯ äžèšã«ããäºçŽåŠçãèªåŸçã«å®è¡ãããããªAIãšãŒãžã§ã³ãã®æ§ç¯ MCPãå
·äœçã«ã©ããªåŠçãè¡ã£ãŠãããããã¬ãŒã¹ 1. æºå MCPã® Quickstart ã«æºæ ããŸãã å·®åãšããŠã¯ãäºçŽå¯Ÿè©±ã·ã¹ãã ãæ§ç¯ããããã«SQLiteã§äºçŽç®¡çDBã®æ§ç¯ãè¡ããŸãã Macã«ããã»ããã¢ãããããŠããŸãããWindowsã§ããå Žåã¯å
Œξ
å ±ãåèã«ãã€ã€ãäºçŽDBã®æ§ç¯éšåã ãæ¬èšäºãåç
§ãã ããã 1.1 ç°å¢ MacBook Pro 16ã€ã³ã, 2021 Apple M1 Pro MacOS Ventura(13.6.8) 1.2 åææ¡ä»¶ Claude Desktop ã®ææ°çã®ã€ã³ã¹ããŒã« uv 0.4.18ä»¥äž Git SQLite ( to check) $ brew install uv git sqlite3 1.3 äºçŽDBã®æ§ç¯ äºçŽç®¡çãè¡ãããã®DBãèšå®ããŸããDBèšèšã«é¢ããŠã¯LLMãšã®ããåãã«ãã£ãŠäœæããŸããã 1.3.1 DBãžã®ã¢ã¯ã»ã¹ sqlite3 ~/test.db <<EOF 1.3.2 äºçŽæ ããŒãã« äºçŽæ ãåãæ±ãããŒãã«ã§ããâ¯æâ¯æ¥(date)ã®äœæ(start_time)ããäœæ(end_time)ãŸã§ã®äºçŽæ ãäœæ (capacity)ããããšããæ
å ±ã管çããŸãã CREATE TABLE reservation_frames ( id INTEGER PRIMARY KEY AUTOINCREMENT, date DATE NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, capacity INTEGER NOT NULL ); 1.3.3 äºçŽããŒãã« äºçŽæ
å ±ãæ±ããŸãã ã©ã®äºçŽæ (reservation_frame_id)ã«å¯ŸããŠèª°(customer_name, customer_contact)ãäºçŽãããŠãããããããŠäºçŽã®ã¹ããŒã¿ã¹(confirmed, canceled, pending)ãæ±ããŸãã CREATE TABLE reservations ( id INTEGER PRIMARY KEY AUTOINCREMENT, reservation_frame_id INTEGER NOT NULL, customer_name TEXT NOT NULL, customer_contact TEXT NOT NULL, status TEXT NOT NULL CHECK(status IN ('confirmed', 'cancelled', 'pending')), FOREIGN KEY (reservation_frame_id) REFERENCES reservation_frames(id) ); 1.3.4 äºçŽæ æ
å ±ã®è¿œå 12æ5æ¥ãã12æ11æ¥ã®10:00(start_time)ãã16:00(end_time)ãŸã§1æéæ¯ã®æ ãããããç©ºãæ (capacity)1ã§è¿œå ããŸãã INSERT INTO reservation_frames (date, start_time, end_time, capacity) VALUES ('2024-12-05', '10:00:00', '11:00:00', 1), ('2024-12-05', '11:00:00', '12:00:00', 1), ('2024-12-05', '12:00:00', '13:00:00', 1), ('2024-12-05', '13:00:00', '14:00:00', 1), ('2024-12-05', '14:00:00', '15:00:00', 1), ('2024-12-05', '15:00:00', '16:00:00', 1), ('2024-12-05', '16:00:00', '17:00:00', 1), ('2024-12-06', '10:00:00', '11:00:00', 1), ('2024-12-06', '11:00:00', '12:00:00', 1), ('2024-12-06', '12:00:00', '13:00:00', 1), ('2024-12-06', '13:00:00', '14:00:00', 1), ('2024-12-06', '14:00:00', '15:00:00', 1), ('2024-12-06', '15:00:00', '16:00:00', 1), ('2024-12-06', '16:00:00', '17:00:00', 1), ('2024-12-07', '10:00:00', '11:00:00', 1), ('2024-12-07', '11:00:00', '12:00:00', 1), ('2024-12-07', '12:00:00', '13:00:00', 1), ('2024-12-07', '13:00:00', '14:00:00', 1), ('2024-12-07', '14:00:00', '15:00:00', 1), ('2024-12-07', '15:00:00', '16:00:00', 1), ('2024-12-07', '16:00:00', '17:00:00', 1), ('2024-12-08', '10:00:00', '11:00:00', 1), ('2024-12-08', '11:00:00', '12:00:00', 1), ('2024-12-08', '12:00:00', '13:00:00', 1), ('2024-12-08', '13:00:00', '14:00:00', 1), ('2024-12-08', '14:00:00', '15:00:00', 1), ('2024-12-08', '15:00:00', '16:00:00', 1), ('2024-12-08', '16:00:00', '17:00:00', 1), ('2024-12-09', '10:00:00', '11:00:00', 1), ('2024-12-09', '11:00:00', '12:00:00', 1), ('2024-12-09', '12:00:00', '13:00:00', 1), ('2024-12-09', '13:00:00', '14:00:00', 1), ('2024-12-09', '14:00:00', '15:00:00', 1), ('2024-12-09', '15:00:00', '16:00:00', 1), ('2024-12-09', '16:00:00', '17:00:00', 1), ('2024-12-10', '10:00:00', '11:00:00', 1), ('2024-12-10', '11:00:00', '12:00:00', 1), ('2024-12-10', '12:00:00', '13:00:00', 1), ('2024-12-10', '13:00:00', '14:00:00', 1), ('2024-12-10', '14:00:00', '15:00:00', 1), ('2024-12-10', '15:00:00', '16:00:00', 1), ('2024-12-10', '16:00:00', '17:00:00', 1), ('2024-12-11', '10:00:00', '11:00:00', 1), ('2024-12-11', '11:00:00', '12:00:00', 1), ('2024-12-11', '12:00:00', '13:00:00', 1), ('2024-12-11', '13:00:00', '14:00:00', 1), ('2024-12-11', '14:00:00', '15:00:00', 1), ('2024-12-11', '15:00:00', '16:00:00', 1), ('2024-12-11', '16:00:00', '17:00:00', 1); 1.3.5 äºçŽæ
å ±ã®è¿œå æ¢ã«å
šäœã®7å²ãåãŸã£ãŠããç¶æ³ãäœãããã«äºçŽæ æ
å ±ãã©ã³ãã ã§LLMã«äœã£ãŠããããŸããã INSERT INTO reservations (id, reservation_frame_id, customer_name, customer_contact, status) VALUES (1, 1, 'Customer_1', 'customer_1@example.com', 'confirmed'), (2, 3, 'Customer_2', 'customer_2@example.com', 'confirmed'), (3, 4, 'Customer_3', 'customer_3@example.com', 'confirmed'), (4, 5, 'Customer_4', 'customer_4@example.com', 'confirmed'), (5, 6, 'Customer_5', 'customer_5@example.com', 'confirmed'), (6, 8, 'Customer_6', 'customer_6@example.com', 'confirmed'), (7, 10, 'Customer_7', 'customer_7@example.com', 'confirmed'), (8, 11, 'Customer_8', 'customer_8@example.com', 'confirmed'), (9, 12, 'Customer_9', 'customer_9@example.com', 'confirmed'), (10, 14, 'Customer_10', 'customer_10@example.com', 'confirmed'), (11, 15, 'Customer_11', 'customer_11@example.com', 'confirmed'), (12, 17, 'Customer_12', 'customer_12@example.com', 'confirmed'), (13, 18, 'Customer_13', 'customer_13@example.com', 'confirmed'), (14, 19, 'Customer_14', 'customer_14@example.com', 'confirmed'), (15, 21, 'Customer_15', 'customer_15@example.com', 'confirmed'), (16, 22, 'Customer_16', 'customer_16@example.com', 'confirmed'), (17, 23, 'Customer_17', 'customer_17@example.com', 'confirmed'), (18, 25, 'Customer_18', 'customer_18@example.com', 'confirmed'), (19, 26, 'Customer_19', 'customer_19@example.com', 'confirmed'), (20, 27, 'Customer_20', 'customer_20@example.com', 'confirmed'), (21, 29, 'Customer_21', 'customer_21@example.com', 'confirmed'), (22, 30, 'Customer_22', 'customer_22@example.com', 'confirmed'), (23, 31, 'Customer_23', 'customer_23@example.com', 'confirmed'), (24, 33, 'Customer_24', 'customer_24@example.com', 'confirmed'), (25, 34, 'Customer_25', 'customer_25@example.com', 'confirmed'), (26, 35, 'Customer_26', 'customer_26@example.com', 'confirmed'), (27, 37, 'Customer_27', 'customer_27@example.com', 'confirmed'), (28, 38, 'Customer_28', 'customer_28@example.com', 'confirmed'), (29, 39, 'Customer_29', 'customer_29@example.com', 'confirmed'), (30, 41, 'Customer_30', 'customer_30@example.com', 'confirmed'), (31, 42, 'Customer_31', 'customer_31@example.com', 'confirmed'), (32, 43, 'Customer_32', 'customer_32@example.com', 'confirmed'), (33, 45, 'Customer_33', 'customer_33@example.com', 'confirmed'), (34, 46, 'Customer_34', 'customer_34@example.com', 'confirmed'), (35, 47, 'Customer_35', 'customer_35@example.com', 'confirmed'), (36, 49, 'Customer_36', 'customer_36@example.com', 'confirmed'); 1.4 configãã¡ã€ã«ã®ç¢ºç« ~/Library/Application Support/Claude/claude_desktop_config.json ãç·šéã { "mcpServers": { "sqlite": { "command": "uvx", "args": ["mcp-server-sqlite", "--db-path", "/Users/<YOUR_USERNAME>/test.db"] } } } 1.5 MCPãµãŒããŒã®èµ·å $ uvx mcp-server-sqlite MCPã®èšå®ãå®äºããClaudeã® Start new chat ãã¯ãªãã¯ãããšãã£ããå
¥åæ¬ã®å³äžã«éæ§ã®ããŒã¯ãš6ãšããæ°åã衚瀺ãããŠããŸããã¯ãªãã¯ãããšMCPãæäœå¯èœãªããŒã«ã®äžèЧãåºãŠããŸããä»åã¯SQLiteã«é¢ããèšå®ãè¡ã£ãã®ã§ã以äžã®æäœãèš±å¯ãããŠããŸãã詳ãã㯠ãã¡ã ã埡芧ãã ãã Query Tools read-query: SELECTã¯ãšãªãå®è¡ããŠããŒã¿ããŒã¹ããããŒã¿ãèªã¿åããŸã write-query: INSERTãUPDATEããŸãã¯DELETEã¯ãšãªãå®è¡ãã create-table: ããŒã¿ããŒã¹ã«æ°ããããŒãã«ãäœæãã Schema Tools list-table: ããŒã¿ããŒã¹å
ã®ãã¹ãŠã®ããŒãã«ã®ãªã¹ããååŸãã describe-table: ç¹å®ã®ããŒãã«ã®ã¹ããŒãæ
å ±ã衚瀺ãã Analysis Tools append-insight: ã¡ã¢ãªãœãŒã¹ã«æ°ããããžãã¹ã€ã³ãµã€ãã远å ãã 2. äºçŽå¯Ÿè©±ã®å®è¡ ãŸãã¯æ§ç¯ããäºçŽå¯Ÿè©±AIãšãŒãžã§ã³ããšã®ããåããåç»ã§åŸ¡èЧãã ããããã®ããšããããã®ããåãã«é¢ããŠçްãããã¬ãŒã¹ãè¡ããŸãã 3. åäœã®ãã¬ãŒã¹ ãããã®ããŒã«ããŠãŒã¶ãŒããã®Queryã«åãããŠToolã䜿ãåããã¿ã¹ã¯ã®å®è¡ãè¡ããŸããå®éã«åç»ã®äžããäžéšã®ããåããåãåºããªããMCPã«ããå
éšåäœã®ãã¬ãŒã¹ãè¡ã£ãŠãããŸãã â ãSQLiteã®ããŒãã«ãåç
§ããŠäºçŽãåãããã§ããã list-table 質åã«å¯ŸããŠããŒãã«ã®äžèЧãåç
§ããŠããŸããããã§ã¯3ã€ã®ããŒãã«( reservations , sqlite_sequence , reservation_frames )ãããããšãããããŸãã Request {} Response [{'name': 'reservations'}, {'name': 'sqlite_sequence'}, {'name': 'reservation_frames'}] describe-data ããŒãã«ã®äžèЧã«å¯ŸããŠãé¢é£ããããªreservationsããŒãã«ãšreservation_framesããŒãã«ã®ã¹ããŒãå®çŸ©ãèŠã«è¡ã£ãŠããŸãã reservationsããŒãã«ã«å¯ŸããRequest { `table_name`: `reservations` } reservationsããŒãã«ã«å¯ŸããResponse [{'cid': 0, 'name': 'id', 'type': 'INTEGER', 'notnull': 0, 'dflt_value': None, 'pk': 1}, {'cid': 1, 'name': 'reservation_frame_id', 'type': 'INTEGER', 'notnull': 1, 'dflt_value': None, 'pk': 0}, {'cid': 2, 'name': 'customer_name', 'type': 'TEXT', 'notnull': 1, 'dflt_value': None, 'pk': 0}, {'cid': 3, 'name': 'customer_contact', 'type': 'TEXT', 'notnull': 1, 'dflt_value': None, 'pk': 0}, {'cid': 4, 'name': 'status', 'type': 'TEXT', 'notnull': 1, 'dflt_value': None, 'pk': 0}] reservation_framesã«å¯ŸããRequest { `table_name`: `reservation_frames` } reservation_framesã«å¯ŸããResponse [{'cid': 0, 'name': 'id', 'type': 'INTEGER', 'notnull': 0, 'dflt_value': None, 'pk': 1}, {'cid': 1, 'name': 'date', 'type': 'DATE', 'notnull': 1, 'dflt_value': None, 'pk': 0}, {'cid': 2, 'name': 'start_time', 'type': 'TIME', 'notnull': 1, 'dflt_value': None, 'pk': 0}, {'cid': 3, 'name': 'end_time', 'type': 'TIME', 'notnull': 1, 'dflt_value': None, 'pk': 0}, {'cid': 4, 'name': 'capacity', 'type': 'INTEGER', 'notnull': 1, 'dflt_value': None, 'pk': 0}] äžèšã®ããŒã«æäœã®çµæãããšã«2ã€ã®ããŒãã«ãããããšãšãã®ããŒãã«å®çŸ©ã«ã€ããŠã®åçããŸãreservationsããŒãã«ã«customer_nameãšcustomer_contactãšãããã£ãŒã«ããããããšããäºçŽã«ã¯ååãšé£çµ¡å
ãå¿
èŠã§ããããšãçè§£ããŠãæ
å ±æäŸããŠãŒã¶ãŒã«ãªã¯ãšã¹ãããŠããŸãã â¡ çŽè¿ã§äºçŽå¯èœãªå Žæã4ã€æããŠã read-query: äºçŽã®ç©ºãæ æ
å ±ãååŸããããã«ã¯ãšãªãçºè¡ããŠããŸãã Request { `query`: ` WITH reservation_counts AS ( SELECT reservation_frame_id, COUNT(*) as reserved_count FROM reservations WHERE status != 'cancelled' GROUP BY reservation_frame_id ) SELECT rf.id, rf.date, rf.start_time, rf.end_time, rf.capacity, COALESCE(rc.reserved_count, 0) as current_reservations, (rf.capacity - COALESCE(rc.reserved_count, 0)) as available_seats FROM reservation_frames rf LEFT JOIN reservation_counts rc ON rf.id = rc.reservation_frame_id WHERE rf.date >= DATE('now') AND (rf.capacity - COALESCE(rc.reserved_count, 0)) > 0 ORDER BY rf.date, rf.start_time LIMIT 4; ` } Response Error: Only SELECT queries are allowed for read-query read-query: â ã§çºè¡ããSQLãå®è¡ãããšãšã©ãŒãçºçããããšãããããšã©ãŒå
容ãããšã«SQLãä¿®æ£ããŠåãã©ã€ããŠããŸãã Request { `query`: ` SELECT rf.id, rf.date, rf.start_time, rf.end_time, rf.capacity, (SELECT COUNT(*) FROM reservations r WHERE r.reservation_frame_id = rf.id AND r.status != 'cancelled') as reserved_count FROM reservation_frames rf WHERE rf.date >= DATE('now') ORDER BY rf.date, rf.start_time LIMIT 4; ` } Response [{'id': 1, 'date': '2024-12-05', 'start_time': '10:00:00', 'end_time': '11:00:00', 'capacity': 1, 'reserved_count': 1}, {'id': 2, 'date': '2024-12-05', 'start_time': '11:00:00', 'end_time': '12:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 3, 'date': '2024-12-05', 'start_time': '12:00:00', 'end_time': '13:00:00', 'capacity': 1, 'reserved_count': 1}, {'id': 4, 'date': '2024-12-05', 'start_time': '13:00:00', 'end_time': '14:00:00', 'capacity': 1, 'reserved_count': 1}] åãã©ã€ã®çµæãäºçŽæ ã®æ
å ±ãšäºçŽã®ç©ºãã®æç¡ãããããããªããŒã¿ãè¿ã£ãŠããŠããŸãããã®å
容ãããšã«ãŠãŒã¶ãŒã«äºçŽæ æ
å ±ããŠãŒã¶ãŒã«äŒããŠããŸãã(ãã ããããã§ã¯ã空ããç¡ãäºçŽæ ãè¿ããŠããã®ã§ãSQLãäžå®å
šã§ããããšãããããŸãã) ⢠空ãããã®ãã®ã4ã€æã㊠read-query: ãŠãŒã¶ãŒã®èŠä»¶ã«åãããŠãä»¶æ°ã4ä»¶ã«ããããWhereå¥ã§statusãèæ
®ããŠããããšãããããŸãã(å
çšã®ã¯ãšãªã§statusãèæ
®ãããŠããªãã£ãããšãLLMåŽã§ä¿®æ£ããŠããŸãã) Request { `query`: ` SELECT rf.id, rf.date, rf.start_time, rf.end_time, rf.capacity, (SELECT COUNT(*) FROM reservations r WHERE r.reservation_frame_id = rf.id AND r.status != 'cancelled') as reserved_count FROM reservation_frames rf WHERE rf.date >= DATE('now') AND (SELECT COUNT(*) FROM reservations r WHERE r.reservation_frame_id = rf.id AND r.status != 'cancelled') < rf.capacity ORDER BY rf.date, rf.start_time LIMIT 4; ` } Response [{'id': 2, 'date': '2024-12-05', 'start_time': '11:00:00', 'end_time': '12:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 7, 'date': '2024-12-05', 'start_time': '16:00:00', 'end_time': '17:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 9, 'date': '2024-12-06', 'start_time': '11:00:00', 'end_time': '12:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 13, 'date': '2024-12-06', 'start_time': '15:00:00', 'end_time': '16:00:00', 'capacity': 1, 'reserved_count': 0}] äžèšã®æ
å ±ãããšã«ãŠãŒã¶ãŒã«äºçŽæ ã®æç€ºãšè¿œå ã®æ
å ±ãæ±ãããããªå¿çãããŠããŸãã ⣠12æ6æ¥ã®11æããäºçŽãããã§ã read-sql: æå®ããæéã«å¯ŸããŠæ¹ããŠãŸã äºçŽæ ã®ç©ºãããããããã§ãã¯ããŠããŸãã Request { `query`: ` SELECT rf.id, rf.date, rf.start_time, rf.end_time, rf.capacity, (SELECT COUNT(*) FROM reservations r WHERE r.reservation_frame_id = rf.id AND r.status != 'cancelled') as reserved_count FROM reservation_frames rf WHERE rf.id = 9; ` } Response [{'id': 9, 'date': '2024-12-06', 'start_time': '11:00:00', 'end_time': '12:00:00', 'capacity': 1, 'reserved_count': 0}] äºçŽæ ã®ç©ºãã確èªã§ãããããå®éã®äºçŽå®è¡åŠçã«é²ã¿ãŸãã †<åå>ã§ã ãŠãŒã¶ãŒãååã話ããéãããã§ã¯ããŒã«ã®éžæã¯è¡ãããŸããã§ãããæ¬¡ã«äºçŽãå®è¡ããããã«ã¯é£çµ¡å
ãèãå¿
èŠããããšå€æããããã§ã¯è¿œå ã®ãã¢ãªã³ã°ã«ãšã©ãŸã£ãŠããŸãã ⥠é£çµ¡å
ã¯<ã¡ãŒã«ã¢ãã¬ã¹>ã§ã äºçŽã«å¿
èŠãªæ
å ±ããã¹ãŠæã£ãã®ã§ãäºçŽæ
å ±ãreservationsããŒãã«ã«æžã蟌ã¿ãè¡ããŸãã write-query Request { `query`: ` INSERT INTO reservations (reservation_frame_id, customer_name, customer_contact, status) VALUES (9, 'åæŸç¥å€ª', 'tomomatsu@example.com', 'confirmed'); ` } Response [{'affected_rows': 1}] DBããã®ã¬ã¹ãã³ã¹ãæ£åžžã ã£ãã®ã§ãäºçŽãå®äºããããšãšãäºçŽã«äœ¿çšããæ
å ±ããŠãŒã¶ãŒã«äŒããŠããŸãããŸãäžéãäºçŽå®è¡å
šäœã®ã¿ã¹ã¯ãå®äºããã®ã§ã¯ããŒãžã³ã°ã®ã¡ãã»ãŒãžãäŒããŠããŸãã ãã®ãšãã®reservationsã®ããŒãã«ãèŠãŠã¿ããšãæåŸã®è¡ã«äºçŽã远å ãããããšãããããŸãã ⊠ãã£ã±ãäºçŽå€æŽãããã§ãã read-query äºçŽã®å€æŽãå¿
èŠãšããããšã§äºçŽæ ã®æç€ºãå¿
èŠãšå€æããäºçŽæ æ€çŽ¢çšã®Queryãçºè¡ããŸãã Request { `query`: ` SELECT rf.id, rf.date, rf.start_time, rf.end_time, rf.capacity, (SELECT COUNT(*) FROM reservations r WHERE r.reservation_frame_id = rf.id AND r.status != 'cancelled') as reserved_count FROM reservation_frames rf WHERE rf.date >= DATE('now') AND (SELECT COUNT(*) FROM reservations r WHERE r.reservation_frame_id = rf.id AND r.status != 'cancelled') < rf.capacity AND rf.id != 9 ORDER BY rf.date, rf.start_time LIMIT 4; ` } Response [{'id': 2, 'date': '2024-12-05', 'start_time': '11:00:00', 'end_time': '12:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 7, 'date': '2024-12-05', 'start_time': '16:00:00', 'end_time': '17:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 13, 'date': '2024-12-06', 'start_time': '15:00:00', 'end_time': '16:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 16, 'date': '2024-12-07', 'start_time': '11:00:00', 'end_time': '12:00:00', 'capacity': 1, 'reserved_count': 0}] Responseã®äºçŽæ æ
å ±ãããšã«ããŠãŒã¶ãŒã«æç€ºããŠããŸããå
çšäºçŽå®è¡ãã12æ6æ¥ã®11:00ããã®æ ã¯ç©ºãæ ãç¡ããªã£ããããæç€ºãããŠããªãããšãããããŸãããŸãããããŸã§ã®ããåããã4件衚瀺ããã®ãé©åãšå€æãqueryã«ãåæ ãããŠããŸãã â§ 12æ8æ¥ä»¥éã¯ïŒ read-query å
ã»ã©ã®æ¡ä»¶ã ãšãŠãŒã¶ãŒåŽã®æ¡ä»¶ã«åããªãã£ããããæ¡ä»¶ã®å€æŽãæ±ããŠããŸããQueryã®Whereå¥ã®æ¡ä»¶ã«2024-12-08以éãšããæ¡ä»¶ãä»äžãããŠããããšãããããŸãã Request { `query`: ` SELECT rf.id, rf.date, rf.start_time, rf.end_time, rf.capacity, (SELECT COUNT(*) FROM reservations r WHERE r.reservation_frame_id = rf.id AND r.status != 'cancelled') as reserved_count FROM reservation_frames rf WHERE rf.date >= '2024-12-08' AND (SELECT COUNT(*) FROM reservations r WHERE r.reservation_frame_id = rf.id AND r.status != 'cancelled') < rf.capacity ORDER BY rf.date, rf.start_time LIMIT 4; ` } Response [{'id': 24, 'date': '2024-12-08', 'start_time': '12:00:00', 'end_time': '13:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 28, 'date': '2024-12-08', 'start_time': '16:00:00', 'end_time': '17:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 32, 'date': '2024-12-09', 'start_time': '13:00:00', 'end_time': '14:00:00', 'capacity': 1, 'reserved_count': 0}, {'id': 36, 'date': '2024-12-10', 'start_time': '10:00:00', 'end_time': '11:00:00', 'capacity': 1, 'reserved_count': 0}] Responseã®äºçŽæ æ
å ±ãããšã«ããŠãŒã¶ãŒã«æç€ºããŠããŸãã âš 12æ9æ¥ã®ãã€ã§ãé¡ãããŸã write-queryâ ãŸãã¯ãæ¢ã«å
¥ã£ãŠããäºçŽã«å¯ŸããŠãã£ã³ã»ã«åŠçããããŸãã Request { `query`: ` UPDATE reservations SET status = 'cancelled' WHERE reservation_frame_id = 9 AND customer_name = 'åæŸç¥å€ª' AND customer_contact = 'tomomatsu@example.com' AND status = 'confirmed'; ` } Response [{'affected_rows': 1}] write-queryâ¡ ç¶ããŠäºçŽã®å®è¡åŠçãè¡ããŸãããŠãŒã¶ãŒã¯12æ9æ¥ã®ãã€ãšããææ§ãªè¡šçŸãããŠããŸãããæç€ºããåè£ã¯äžã€ãããªãã®ã§ã12æ9æ¥ã®13:00-14:00ãšããããšãçè§£ããŠããŸãããŸãããã§ã«ååãé£çµ¡å
ã®æ
å ±ã¯èããŠããããããã¢ãªã³ã°ãã¹ããããããŠreservationsããŒãã«ãžã®æžã蟌ã¿ãå®è¡ããŸãã Request { `query`: ` INSERT INTO reservations (reservation_frame_id, customer_name, customer_contact, status) VALUES (32, 'åæŸç¥å€ª', 'tomomatsu@example.com', 'confirmed'); ` } Response [{'affected_rows': 1}] äºçŽãæåããã®ã§ãæ°ããäºçŽæ
å ±ããŠãŒã¶ãŒã«è¿åŽããŠããŸãããã ããããã§ã®äžé£ã®ãã£ã³ã»ã«ãšäºçŽåŠçã¯ãäºçŽãæç«ããªãã£ãå Žåã«äºçŽã®åé€ã ããè¡ãããŠããŸãããããã©ã³ã¶ã¯ã·ã§ã³ç®¡çã¯äžéšèª²é¡ãããããã§ãã äžé£ã®åŠçã«ãã£ãŠãDBã®æ
å ±ãæŽæ°ãããŠããŸããid=39ã®äºçŽã¯cancelledã«ãªããæ°ãã«id=40ãconfirmedã§äºçŽããããŠããããšãããããŸãã â© ããããšãããããŸã ããã§ã¯ç¹ã«ããŒã«éžæã®å¿
èŠæ§ããªããããã¯ããŒãžã³ã°ã®åçãè¿ãã®ã¿ãšãªã£ãŠããŸãã ⪠äºçŽç
§äŒãããŠãã ããã以åã«ãã£ã³ã»ã«ããæ¥ä»ãåãããŠæããŠäžããã read-sql äºçŽç
§äŒãããããã«ãreservationsããŒãã«ã®ç
§äŒãè¡ããŸããæç€ºéããäºçŽãã£ã³ã»ã«ãããã®ã察象ã«ãªã£ãŠããŸãã Request { `query`: ` SELECT r.id, rf.date, rf.start_time, rf.end_time, r.status, r.customer_name, r.customer_contact FROM reservations r JOIN reservation_frames rf ON r.reservation_frame_id = rf.id WHERE r.customer_name = 'åæŸç¥å€ª' AND r.customer_contact = 'tomomatsu@example.com' ORDER BY rf.date, rf.start_time; ` } Response [{'id': 39, 'date': '2024-12-06', 'start_time': '11:00:00', 'end_time': '12:00:00', 'status': 'cancelled', 'customer_name': 'åæŸç¥å€ª', 'customer_contact': 'tomomatsu@example.com'}, {'id': 40, 'date': '2024-12-09', 'start_time': '13:00:00', 'end_time': '14:00:00', 'status': 'confirmed', 'customer_name': 'åæŸç¥å€ª', 'customer_contact': 'tomomatsu@example.com'}] ã¬ã¹ãã³ã¹å
容ãããšã«ãŠãŒã¶ãŒã«ãã£ã³ã»ã«æžã¿ãçŸåšã®äºçŽãããããè¿åŽããŠããŸãã 4. ãŸãšã MCPã䜿ãããšã§ãããªã«ãç°¡åã«äºçŽå¯Ÿè©±ãšãŒãžã§ã³ããäœãããšãã§ããŸããããŸããLLMåŽãäºåã«ç¥ã£ãŠããå
容ã¯SQLiteãšããDBãããããã®DBã«å¯ŸããŠ6ã€ã®æäœãã§ãããšããèšå®ããç¥ããŸãããLLMã¯ãŠãŒã¶ãŒãšã®å¯Ÿè©±ãå§ãŸããšèªã倿ããããŒã¿ããŒã¹ã®ããŒãã«ã®äžèЧãååŸããããããŒãã«ã®ã¹ããŒãå®çŸ©ãååŸãããããã®ããŒãã«æ§é ãçè§£ããããã§ãããŒãã«ã«å¯Ÿããèªã¿èŸŒã¿åŠçãæžã蟌ã¿åŠçãæè»ã«å®æœããããšãã§ãããšããããšã®äžé£ããã¬ãŒã¹ããããšãã§ããŸããã å€éšæ
å ±ãžã®ã¢ã¯ã»ã¹ã¯SQLiteã ãã§ãªãæ¢ã«GitãããŒã«ã«ãã¡ã€ã«ãPostgreSQLãªã©å®è£
ããã£ãããèªåã§ãæ§ç¯ããããšãå¯èœã§ããããããçµã¿åãããããšã«ãã£ãŠããé«åºŠãªã¿ã¹ã¯ãå®è¡ãããšãŒãžã§ã³ãã®æ§ç¯ãå¯èœã«ãªããŸãã ä»åŸã®AIãšãŒãžã§ã³ãã®çºå±ã«MCPã倧ããé¢ãã£ãŠããããšã¯ããããééããªããšæã£ãŠããŸããAI Shift瀟ãšããŠãåšèŸºæè¡ãåŒãç¶ã調æ»ã»æ€èšŒãé²ããŠãããããšæããŸãã ææ¥ã¯ãã³ã³ãã³ãããŒã ã®äŒè€ããã®èšäºãäžããäºå®ã§ãã ã粟èªããã ãããããšãããããŸããã æçš¿ ãAI Shift Advent Calendar 2024ãMCP(Model Context Protocol)ãçšããäºçŽå¯Ÿè©±AIãšãŒãžã§ã³ãã®æ§ç¯ãšåäœã®ãã¬ãŒã¹ ã¯ æ ªåŒäŒç€ŸAI Shift ã«æåã«è¡šç€ºãããŸããã