Skip to content

Commit 40e86fb

Browse files
CorrectRoadHCorrectRoadH
authored
fix: too many connect error (sqlchat#71)
* add fetch all struct * add other database implement * rename method name * adjust the order of functions * eslint * eslint * eslint * eslint * eslint * fix error end when fetch schema batch
1 parent 498bf15 commit 40e86fb

File tree

5 files changed

+138
-8
lines changed

5 files changed

+138
-8
lines changed

‎src/lib/connectors/index.ts

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,11 @@ export interface Connector {
1616
tableName: string,
1717
structureFetched: (tableName: string, structure: string) => void
1818
) => Promise<void>;
19+
getTableStructureBatch: (
20+
databaseName: string,
21+
tableNameList: string[],
22+
structureFetched: (tableName: string, structure: string) => void
23+
) => Promise<void>;
1924
}
2025

2126
export const newConnector = (connection: Connection): Connector => {

‎src/lib/connectors/mssql/index.ts

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -117,6 +117,39 @@ const getTableStructure = async (
117117
);
118118
};
119119

120+
const getTableStructureBatch = async (
121+
connection: Connection,
122+
databaseName: string,
123+
tableNameList: string[],
124+
structureFetched: (tableName: string, structure: string) => void
125+
): Promise<void> => {
126+
const pool = await getMSSQLConnection(connection);
127+
const request = pool.request();
128+
129+
await Promise.all(
130+
tableNameList.map(async (tableName) => {
131+
const { recordset } = await request.query(
132+
`SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM ${databaseName}.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='${tableName}';`
133+
);
134+
const columnList = [];
135+
// Transform to standard schema string.
136+
for (const row of recordset) {
137+
columnList.push(
138+
`${row["COLUMN_NAME"]} ${row["DATA_TYPE"].toUpperCase()} ${
139+
String(row["IS_NULLABLE"]).toUpperCase() === "NO" ? "NOT NULL" : ""
140+
}`
141+
);
142+
}
143+
structureFetched(
144+
tableName,
145+
`CREATE TABLE [${tableName}] (
146+
${columnList.join(",\n")}
147+
);`
148+
);
149+
})
150+
);
151+
};
152+
120153
const newConnector = (connection: Connection): Connector => {
121154
return {
122155
testConnection: () => testConnection(connection),
@@ -130,6 +163,17 @@ const newConnector = (connection: Connection): Connector => {
130163
structureFetched: (tableName: string, structure: string) => void
131164
) =>
132165
getTableStructure(connection, databaseName, tableName, structureFetched),
166+
getTableStructureBatch: (
167+
databaseName: string,
168+
tableNameList: string[],
169+
structureFetched: (tableName: string, structure: string) => void
170+
) =>
171+
getTableStructureBatch(
172+
connection,
173+
databaseName,
174+
tableNameList,
175+
structureFetched
176+
),
133177
};
134178
};
135179

‎src/lib/connectors/mysql/index.ts

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,29 @@ const getTableStructure = async (
111111
structureFetched(tableName, rows[0]["Create Table"] || "");
112112
};
113113

114+
const getTableStructureBatch = async (
115+
connection: Connection,
116+
databaseName: string,
117+
tableNameList: string[],
118+
structureFetched: (tableName: string, structure: string) => void
119+
): Promise<void> => {
120+
const conn = await getMySQLConnection(connection);
121+
122+
await Promise.all(
123+
tableNameList.map(async (tableName) => {
124+
const [rows] = await conn.query<RowDataPacket[]>(
125+
`SHOW CREATE TABLE \`${databaseName}\`.\`${tableName}\`;`
126+
);
127+
if (rows.length !== 1) {
128+
throw new Error("Unexpected number of rows.");
129+
}
130+
structureFetched(tableName, rows[0]["Create Table"] || "");
131+
})
132+
).finally(() => {
133+
conn.destroy();
134+
});
135+
};
136+
114137
const newConnector = (connection: Connection): Connector => {
115138
return {
116139
testConnection: () => testConnection(connection),
@@ -124,6 +147,17 @@ const newConnector = (connection: Connection): Connector => {
124147
structureFetched: (tableName: string, structure: string) => void
125148
) =>
126149
getTableStructure(connection, databaseName, tableName, structureFetched),
150+
getTableStructureBatch: (
151+
databaseName: string,
152+
tableNameList: string[],
153+
structureFetched: (tableName: string, structure: string) => void
154+
) =>
155+
getTableStructureBatch(
156+
connection,
157+
databaseName,
158+
tableNameList,
159+
structureFetched
160+
),
127161
};
128162
};
129163

‎src/lib/connectors/postgres/index.ts

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,42 @@ const getTableStructure = async (
121121
);
122122
};
123123

124+
const getTableStructureBatch = async (
125+
connection: Connection,
126+
databaseName: string,
127+
tableNameList: string[],
128+
structureFetched: (tableName: string, structure: string) => void
129+
): Promise<void> => {
130+
connection.database = databaseName;
131+
const client = newPostgresClient(connection);
132+
await client.connect();
133+
await Promise.all(
134+
tableNameList.map(async (tableName) => {
135+
const { rows } = await client.query(
136+
`SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema='public' AND table_name=$1;`,
137+
[tableName]
138+
);
139+
const columnList = [];
140+
// TODO(steven): transform it to standard schema string.
141+
for (const row of rows) {
142+
columnList.push(
143+
`${row["column_name"]} ${row["data_type"].toUpperCase()} ${
144+
String(row["is_nullable"]).toUpperCase() === "NO" ? "NOT NULL" : ""
145+
}`
146+
);
147+
}
148+
structureFetched(
149+
tableName,
150+
`CREATE TABLE \`${tableName}\` (
151+
${columnList.join(",\n")}
152+
);`
153+
);
154+
})
155+
).finally(async () => {
156+
await client.end();
157+
});
158+
};
159+
124160
const newConnector = (connection: Connection): Connector => {
125161
return {
126162
testConnection: () => testConnection(connection),
@@ -134,6 +170,17 @@ const newConnector = (connection: Connection): Connector => {
134170
structureFetched: (tableName: string, structure: string) => void
135171
) =>
136172
getTableStructure(connection, databaseName, tableName, structureFetched),
173+
getTableStructureBatch: (
174+
databaseName: string,
175+
tableNameList: string[],
176+
structureFetched: (tableName: string, structure: string) => void
177+
) =>
178+
getTableStructureBatch(
179+
connection,
180+
databaseName,
181+
tableNameList,
182+
structureFetched
183+
),
137184
};
138185
};
139186

‎src/pages/api/connection/db_schema.ts

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -22,14 +22,14 @@ const handler = async (req: NextApiRequest, res: NextApiResponse) => {
2222
structure,
2323
});
2424
};
25-
Promise.all(
26-
rawTableNameList.map(async (tableName) =>
27-
connector.getTableStructure(db, tableName, structureFetched)
28-
)
29-
).then(() => {
30-
res.status(200).json({
31-
data: tableStructures,
32-
});
25+
await connector.getTableStructureBatch(
26+
db,
27+
rawTableNameList,
28+
structureFetched
29+
);
30+
31+
res.status(200).json({
32+
data: tableStructures,
3333
});
3434
} catch (error: any) {
3535
res.status(400).json({

0 commit comments

Comments
 (0)