294 lines
11 KiB
TypeScript
294 lines
11 KiB
TypeScript
|
"use client"
|
||
|
|
|
||
|
|
import { useState, useEffect } from "react"
|
||
|
|
import { Card, CardContent, CardDescription, CardHeader, CardTitle } from "@/components/ui/card"
|
||
|
|
import { Table, TableBody, TableCell, TableHead, TableHeader, TableRow } from "@/components/ui/table"
|
||
|
|
import { Button } from "@/components/ui/button"
|
||
|
|
import { Badge } from "@/components/ui/badge"
|
||
|
|
import { Accordion, AccordionContent, AccordionItem, AccordionTrigger } from "@/components/ui/accordion"
|
||
|
|
import { Database, TableIcon, Key, Download, RefreshCw } from "lucide-react"
|
||
|
|
import { Skeleton } from "@/components/ui/skeleton"
|
||
|
|
|
||
|
|
interface TableField {
|
||
|
|
Field: string
|
||
|
|
Type: string
|
||
|
|
Null: string
|
||
|
|
Key: string
|
||
|
|
Default: string | null
|
||
|
|
Extra: string
|
||
|
|
}
|
||
|
|
|
||
|
|
interface DatabaseStructure {
|
||
|
|
[tableName: string]: TableField[]
|
||
|
|
}
|
||
|
|
|
||
|
|
export function DatabaseStructureViewer() {
|
||
|
|
const [databases, setDatabases] = useState<string[]>([])
|
||
|
|
const [selectedDatabase, setSelectedDatabase] = useState<string>("")
|
||
|
|
const [structure, setStructure] = useState<DatabaseStructure | null>(null)
|
||
|
|
const [loading, setLoading] = useState<boolean>(false)
|
||
|
|
const [error, setError] = useState<string | null>(null)
|
||
|
|
|
||
|
|
// 获取数据库列表
|
||
|
|
useEffect(() => {
|
||
|
|
const fetchDatabases = async () => {
|
||
|
|
try {
|
||
|
|
setLoading(true)
|
||
|
|
const response = await fetch("/api/database-structure")
|
||
|
|
const result = await response.json()
|
||
|
|
|
||
|
|
if (result.success) {
|
||
|
|
const dbList = result.data.map((item: any) => Object.values(item)[0] as string)
|
||
|
|
setDatabases(dbList)
|
||
|
|
|
||
|
|
// 默认选择第一个非系统数据库
|
||
|
|
const nonSystemDbs = dbList.filter(
|
||
|
|
(db: string) => !["information_schema", "mysql", "performance_schema", "sys"].includes(db),
|
||
|
|
)
|
||
|
|
if (nonSystemDbs.length > 0) {
|
||
|
|
setSelectedDatabase(nonSystemDbs[0])
|
||
|
|
}
|
||
|
|
} else {
|
||
|
|
setError(result.message || "获取数据库列表失败")
|
||
|
|
}
|
||
|
|
} catch (err) {
|
||
|
|
setError("获取数据库列表失败")
|
||
|
|
console.error(err)
|
||
|
|
} finally {
|
||
|
|
setLoading(false)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
fetchDatabases()
|
||
|
|
}, [])
|
||
|
|
|
||
|
|
// 获取数据库结构
|
||
|
|
useEffect(() => {
|
||
|
|
if (!selectedDatabase) return
|
||
|
|
|
||
|
|
const fetchDatabaseStructure = async () => {
|
||
|
|
try {
|
||
|
|
setLoading(true)
|
||
|
|
setStructure(null)
|
||
|
|
|
||
|
|
const response = await fetch(`/api/database-structure?database=${selectedDatabase}`)
|
||
|
|
const result = await response.json()
|
||
|
|
|
||
|
|
if (result.success) {
|
||
|
|
setStructure(result.data)
|
||
|
|
setError(null)
|
||
|
|
} else {
|
||
|
|
setError(result.message || "获取数据库结构失败")
|
||
|
|
}
|
||
|
|
} catch (err) {
|
||
|
|
setError("获取数据库结构失败")
|
||
|
|
console.error(err)
|
||
|
|
} finally {
|
||
|
|
setLoading(false)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
fetchDatabaseStructure()
|
||
|
|
}, [selectedDatabase])
|
||
|
|
|
||
|
|
// 获取字段类型的简化描述
|
||
|
|
const getSimplifiedType = (type: string) => {
|
||
|
|
if (type.includes("int")) return "Integer"
|
||
|
|
if (type.includes("varchar") || type.includes("text") || type.includes("char")) return "String"
|
||
|
|
if (type.includes("datetime") || type.includes("timestamp")) return "DateTime"
|
||
|
|
if (type.includes("date")) return "Date"
|
||
|
|
if (type.includes("decimal") || type.includes("float") || type.includes("double")) return "Decimal"
|
||
|
|
if (type.includes("bool")) return "Boolean"
|
||
|
|
if (type.includes("json")) return "JSON"
|
||
|
|
if (type.includes("blob")) return "Binary"
|
||
|
|
return type
|
||
|
|
}
|
||
|
|
|
||
|
|
// 获取字段类型的标签颜色
|
||
|
|
const getTypeColor = (type: string) => {
|
||
|
|
if (type.includes("int")) return "bg-blue-100 text-blue-800"
|
||
|
|
if (type.includes("varchar") || type.includes("text") || type.includes("char")) return "bg-green-100 text-green-800"
|
||
|
|
if (type.includes("datetime") || type.includes("timestamp") || type.includes("date"))
|
||
|
|
return "bg-purple-100 text-purple-800"
|
||
|
|
if (type.includes("decimal") || type.includes("float") || type.includes("double"))
|
||
|
|
return "bg-yellow-100 text-yellow-800"
|
||
|
|
if (type.includes("bool")) return "bg-orange-100 text-orange-800"
|
||
|
|
if (type.includes("json")) return "bg-indigo-100 text-indigo-800"
|
||
|
|
if (type.includes("blob")) return "bg-red-100 text-red-800"
|
||
|
|
return "bg-gray-100 text-gray-800"
|
||
|
|
}
|
||
|
|
|
||
|
|
// 导出数据库结构为JSON
|
||
|
|
const exportDatabaseStructure = () => {
|
||
|
|
if (!structure) return
|
||
|
|
|
||
|
|
const dataStr = JSON.stringify(structure, null, 2)
|
||
|
|
const dataUri = `data:application/json;charset=utf-8,${encodeURIComponent(dataStr)}`
|
||
|
|
|
||
|
|
const exportFileDefaultName = `${selectedDatabase}-structure.json`
|
||
|
|
|
||
|
|
const linkElement = document.createElement("a")
|
||
|
|
linkElement.setAttribute("href", dataUri)
|
||
|
|
linkElement.setAttribute("download", exportFileDefaultName)
|
||
|
|
linkElement.click()
|
||
|
|
}
|
||
|
|
|
||
|
|
// 刷新数据库结构
|
||
|
|
const refreshDatabaseStructure = async () => {
|
||
|
|
if (!selectedDatabase) return
|
||
|
|
|
||
|
|
try {
|
||
|
|
setLoading(true)
|
||
|
|
setStructure(null)
|
||
|
|
|
||
|
|
const response = await fetch(`/api/database-structure?database=${selectedDatabase}`)
|
||
|
|
const result = await response.json()
|
||
|
|
|
||
|
|
if (result.success) {
|
||
|
|
setStructure(result.data)
|
||
|
|
setError(null)
|
||
|
|
} else {
|
||
|
|
setError(result.message || "刷新数据库结构失败")
|
||
|
|
}
|
||
|
|
} catch (err) {
|
||
|
|
setError("刷新数据库结构失败")
|
||
|
|
console.error(err)
|
||
|
|
} finally {
|
||
|
|
setLoading(false)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return (
|
||
|
|
<Card className="w-full">
|
||
|
|
<CardHeader>
|
||
|
|
<div className="flex justify-between items-center">
|
||
|
|
<div>
|
||
|
|
<CardTitle>数据库结构查看器</CardTitle>
|
||
|
|
<CardDescription>查看和分析数据库表结构</CardDescription>
|
||
|
|
</div>
|
||
|
|
<div className="flex space-x-2">
|
||
|
|
<Button
|
||
|
|
variant="outline"
|
||
|
|
size="sm"
|
||
|
|
onClick={refreshDatabaseStructure}
|
||
|
|
disabled={loading || !selectedDatabase}
|
||
|
|
>
|
||
|
|
<RefreshCw className="h-4 w-4 mr-2" />
|
||
|
|
刷新
|
||
|
|
</Button>
|
||
|
|
<Button variant="outline" size="sm" onClick={exportDatabaseStructure} disabled={!structure}>
|
||
|
|
<Download className="h-4 w-4 mr-2" />
|
||
|
|
导出JSON
|
||
|
|
</Button>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</CardHeader>
|
||
|
|
<CardContent>
|
||
|
|
{loading && (
|
||
|
|
<div className="space-y-4">
|
||
|
|
<Skeleton className="h-8 w-full" />
|
||
|
|
<Skeleton className="h-64 w-full" />
|
||
|
|
</div>
|
||
|
|
)}
|
||
|
|
|
||
|
|
{error && (
|
||
|
|
<div className="p-4 bg-red-50 text-red-800 rounded-md">
|
||
|
|
<p>{error}</p>
|
||
|
|
</div>
|
||
|
|
)}
|
||
|
|
|
||
|
|
{!loading && !error && (
|
||
|
|
<div className="space-y-4">
|
||
|
|
<div className="flex space-x-2 overflow-x-auto pb-2">
|
||
|
|
{databases.map((db) => (
|
||
|
|
<Button
|
||
|
|
key={db}
|
||
|
|
variant={selectedDatabase === db ? "default" : "outline"}
|
||
|
|
size="sm"
|
||
|
|
onClick={() => setSelectedDatabase(db)}
|
||
|
|
>
|
||
|
|
<Database className="h-4 w-4 mr-2" />
|
||
|
|
{db}
|
||
|
|
</Button>
|
||
|
|
))}
|
||
|
|
</div>
|
||
|
|
|
||
|
|
{structure && (
|
||
|
|
<div>
|
||
|
|
<h3 className="text-lg font-medium mb-2">数据库: {selectedDatabase}</h3>
|
||
|
|
<p className="text-sm text-muted-foreground mb-4">共 {Object.keys(structure).length} 张表</p>
|
||
|
|
|
||
|
|
<Accordion type="multiple" className="w-full">
|
||
|
|
{Object.entries(structure).map(([tableName, fields]) => (
|
||
|
|
<AccordionItem key={tableName} value={tableName}>
|
||
|
|
<AccordionTrigger className="hover:bg-muted/50 px-4">
|
||
|
|
<div className="flex items-center">
|
||
|
|
<TableIcon className="h-4 w-4 mr-2" />
|
||
|
|
<span>{tableName}</span>
|
||
|
|
<Badge className="ml-2 bg-gray-100 text-gray-800">{fields.length} 字段</Badge>
|
||
|
|
</div>
|
||
|
|
</AccordionTrigger>
|
||
|
|
<AccordionContent>
|
||
|
|
<div className="rounded-md border overflow-hidden">
|
||
|
|
<Table>
|
||
|
|
<TableHeader>
|
||
|
|
<TableRow>
|
||
|
|
<TableHead className="w-[200px]">字段名</TableHead>
|
||
|
|
<TableHead>类型</TableHead>
|
||
|
|
<TableHead>允许空值</TableHead>
|
||
|
|
<TableHead>键</TableHead>
|
||
|
|
<TableHead>默认值</TableHead>
|
||
|
|
<TableHead>额外</TableHead>
|
||
|
|
</TableRow>
|
||
|
|
</TableHeader>
|
||
|
|
<TableBody>
|
||
|
|
{fields.map((field, index) => (
|
||
|
|
<TableRow key={index}>
|
||
|
|
<TableCell className="font-medium">
|
||
|
|
{field.Field}
|
||
|
|
{field.Key === "PRI" && <Key className="h-3 w-3 ml-1 inline text-amber-500" />}
|
||
|
|
</TableCell>
|
||
|
|
<TableCell>
|
||
|
|
<Badge className={getTypeColor(field.Type)}>{getSimplifiedType(field.Type)}</Badge>
|
||
|
|
<span className="text-xs text-muted-foreground ml-2">{field.Type}</span>
|
||
|
|
</TableCell>
|
||
|
|
<TableCell>
|
||
|
|
{field.Null === "YES" ? (
|
||
|
|
<Badge variant="outline">可空</Badge>
|
||
|
|
) : (
|
||
|
|
<Badge className="bg-red-100 text-red-800">非空</Badge>
|
||
|
|
)}
|
||
|
|
</TableCell>
|
||
|
|
<TableCell>
|
||
|
|
{field.Key === "PRI" && <Badge>主键</Badge>}
|
||
|
|
{field.Key === "UNI" && <Badge className="bg-blue-100 text-blue-800">唯一</Badge>}
|
||
|
|
{field.Key === "MUL" && (
|
||
|
|
<Badge className="bg-purple-100 text-purple-800">索引</Badge>
|
||
|
|
)}
|
||
|
|
</TableCell>
|
||
|
|
<TableCell>
|
||
|
|
{field.Default !== null ? (
|
||
|
|
field.Default
|
||
|
|
) : (
|
||
|
|
<span className="text-muted-foreground">NULL</span>
|
||
|
|
)}
|
||
|
|
</TableCell>
|
||
|
|
<TableCell>{field.Extra}</TableCell>
|
||
|
|
</TableRow>
|
||
|
|
))}
|
||
|
|
</TableBody>
|
||
|
|
</Table>
|
||
|
|
</div>
|
||
|
|
</AccordionContent>
|
||
|
|
</AccordionItem>
|
||
|
|
))}
|
||
|
|
</Accordion>
|
||
|
|
</div>
|
||
|
|
)}
|
||
|
|
</div>
|
||
|
|
)}
|
||
|
|
</CardContent>
|
||
|
|
</Card>
|
||
|
|
)
|
||
|
|
}
|