将 MySQL 模式转换为 Github Wiki?
我正在使用 GitHub Wiki 页面记录一个项目，现在我想将 MySQL 数据库架构导出为 Markdown 格式.有没有办法将 CLI 输出转换为 GitHub Wiki md?理想的表格.
我的输出如下所示:
解决方案这段代码比较长.我道歉.它由两个存储过程组成.仅运行第一个您可能会感到满意.第二个使用第一个的输出(表中第一个左侧的数据).您可能还希望将代码合二为一.但我把它们分开了.第二个存储过程产生类似于 describe myTable 的输出.但它对数据库中的ALL 表执行此操作，您需要这样的输出.
您可以通过传递一个参数(字符串)供数据库报告来使用它.
我创建了一个单独的数据库，代码按名称显式引用了该数据库中的表.因此，如果您对该存储过程具有 EXECUTE 权限，则可以从任何当前数据库运行它.因此，作为一个简单的测试，不要将报告数据库设置为您当前的数据库，而只是按名称(以报告数据库名称限定)调用存储过程.这一切都显示在下面的测试块中.
两个存储过程
CREATE SCHEMA Reporting101a;-- 见**注1**如果存在`Reporting101a`，则删除程序.`describeTables_v2a`;分隔符 $$CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`describeTables_v2a`(IN dbName varchar(100), -- 报告表结构的dbnameOUT theSession int, -- 为会话#分配的OUT参数IN deleteSessionRows BOOL, -- 在此会话的主报告表中删除行时为真#IN callTheSecondStoredProc BOOL -- TRUE = 输出来自第二个存储过程中的漂亮输出.FALSE= 不太漂亮的输出)开始声明此表 CHAR(100);如果存在报告101a.t输出，则删除临时表；创建临时表报告101a.t输出( id int auto_increment 主键，tblName varchar(100) 不为空，ordVal int 不为空，cField varchar(100) 不为空，cType varchar(100) 不为空，cNull varchar(100) 不为空，cKey varchar(100) 不为空，cDefault varchar(100) null,cExtra varchar(100) null);如果存在报告101a.tOutput2，则删除临时表；创建临时表报告101a.tOutput2( tblName varchar(100) 主键，colCount int 不为空，cFieldMaxLen int 不为空，cTypeMaxLen int 不为空，cNullMaxLen int 不为空，cKeyMaxLen int 不为空，cDefaultMaxLen int 不为空，cExtraMaxLen int 不为空);INSERT Reporting101a.tOutput(tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra)SELECT TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME AS 字段, COLUMN_TYPE AS TYPE, RPAD(IS_NULLABLE,4,' ') AS 'Null',RPAD(COLUMN_KEY,3,' ') AS 'Key',RPAD(COLUMN_DEFAULT,7,' ') AS 'DEFAULT',EXTRA AS ExtraFROM information_schema.columns WHERE table_schema = dbName ORDER BY table_name,ordinal_position;-- select * from information_schema.columns WHERE table_schema = '57security' order by table_name,ordinal_position;更新报告101a.t输出设置 cExtra=' 'WHERE cExtra='';更新报告101a.t输出SET cField=RPAD(cField,5,'')其中 LENGTH(cField)<5;INSERT Reporting101a.tOutput2(tblName,colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen)SELECT tblName,COUNT(*),0,0,0,0,0,0从报告101a.t输出按 tblName 分组；更新 tOutput2 t2加入( SELECT tblName,MAX(LENGTH(cField)) AS mField,MAX(LENGTH(cType)) AS mType,MAX(LENGTH(cNull)) AS mNull,IFNULL(MAX(LENGTH(cKey)),0) AS mKey,IFNULL(MAX(LENGTH(cDefault)),0) AS mDefault,IFNULL(MAX(LENGTH(cExtra)),0) AS mExtra从报告101a.t输出按 tblName 分组) XON x.tblName=t2.tblName设置 t2.cFieldMaxLen=x.mField,t2.cTypeMaxLen=x.mType,cNullMaxLen=x.mNull,cKeyMaxLen=x.mKey,cDefaultMaxLen=x.mDefault,cExtraMaxLen=x.mExtra；-- 删除表 Reporting101a.reportDataDefsSession;-- 用于快速更改表结构- 注意，保持上面的掉线电话提醒！只需使用它来快速调整结构如果不存在则创建表 Reporting101a.reportDataDefsSession( -- 仅出于安全会话 auto_inc 使用的目的-- 除非您希望会话体验异常行为，否则请不要删除sessionId INT AUTO_INCREMENT PRIMARY KEY，虚拟字符(1)非空，creationDT 日期时间不为空);如果不存在则创建表 Reporting101a.reportDataDefs( sessionId INT NOT NULL,tblName VARCHAR(100) NOT NULL, -- 表名ordVal INT NOT NULL, -- 列的位置编号"cField VARCHAR(100) NOT NULL, -- 列cType VARCHAR(100) NOT NULL, -- 数据类型cNull VARCHAR(100) NOT NULL, -- 可空性cKey VARCHAR(100) NOT NULL, -- 密钥信息cDefault VARCHAR(100) NULL, -- 默认值cExtra VARCHAR(100) NULL, -- 额外输出colCount INT NOT NULL, -- 这里和下面的列是非规范化数据cFieldMaxLen INT 不为空，cTypeMaxLen INT 非空，cNullMaxLen INT 不为空，cKeyMaxLen INT 不为空，cDefaultMaxLen INT 不为空，cExtraMaxLen INT 非空);-- 由于缺乏更好的概念，我们将调用称为会话".程序员调用-- First Stored Proc，在我们获得唯一的下一个递增数字后，我们称其为会话.-- 这个数字是会话号.将所有输出作为列值放置.这使我们能够-- 在存储过程之间移动，有安全输出，有历史快照，并保留数据-- 通过会话 # 供以后使用，无论使用什么.INSERT Reporting101a.reportDataDefsSession(dummy,creationDT) VALUES ('X',now());SET @mySession=LAST_INSERT_ID();-- 就是这样，我们的会话#(阅读上面的段落)INSERT Reporting101a.reportDataDefs(sessionId,tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen)选择@mySession,t1.tblName,t1.ordVal,t1.cField,t1.cType,t1.cNull,t1.cKey,t1.cDefault,t1.cExtra,t2.colCount,t2.cFieldMaxLen,t2.cTypeMaxLen,t2.cNullMaxLen,t2.cKeyMaxLen,t2.cDefaultMaxLen,t2.cExtraMaxLen从报告101a.t输出t1加入报告101a.tOutput2 t2ON t2.tblName=t1.tblNameORDER BY t1.tblName,t1.id;删除临时表报告101a.t输出；删除临时表报告101a.tOutput2;SET theSession=@mySession;-- 作为参数传入的 OUT var-- ********************************************************************************-- ********************************************************************************-- 标签Some_Sort_of_Output":如果 callTheSecondStoredProc=TRUE THEN-- 调用者说要调用第二个存储过程(用于漂亮打印)-- 这将生成类似于DESCRIBE myTable"的输出-- 但请记住，它会对引用数据库中的每个表执行此操作CALL Reporting101a.`Print_Tables_Like_Describe`(@mySession);-- 上面的调用只是给了你输出.别的-- 调用者选择不自动调用 Pretty Printing 第二个存储过程.-- 注意，调用者可以在使用 OUT 参数后立即轻松调用它.-- 所以我们的输出将是这个会话的输出 reportDataDefs 表的结果集#选择 *来自 Reporting101a.reportDataDefsWHERE sessionId=@mySessionORDER BY tblName,ordVal;万一;-- ********************************************************************************-- ********************************************************************************如果 deleteSessionRows=TRUE 那么-- 调用者说此时不需要输出行.删除它们.-- 注意，如果这个布尔值为 TRUE，你就不能调用 Pretty Printing-- 会话中的第二个存储过程 # 因为数据不见了.——-- 不管怎样，你从上面的Some_Sort_of_Output"得到了一些东西.从 Reporting101a.reportDataDefs 中删除WHERE sessionId=@mySession;万一;完$$分隔符；如果存在`Reporting101a`，则删除程序.`Print_Tables_Like_Describe`;分隔符 $$CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`Print_Tables_Like_Describe`(pSessionId INT)开始DECLARE done INT DEFAULT FALSE；声明 curTable VARCHAR(100) DEFAULT '';声明 bFirst BOOL DEFAULT TRUE;声明 lv_tblName,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra VARCHAR(100);声明 lv_ordVal,lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,lv_cExtraMaxLen INT;DECLARE cur1 CURSOR FOR SELECT tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen来自 Reporting101a.reportDataDefsWHERE sessionId=pSessionId按 tblName,ordVal 排序；DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 请注意在上面，CURSOR 的东西必须放在最后，否则错误 1337:变量或条件声明 aft curs"如果不存在则创建表 Reporting101a.reportOutput( lineNum INT AUTO_INCREMENT PRIMARY KEY,sessionId INT 不为空，lineOut varchar(100) 非空);-- 插入 Reporting101a.reportOutput(sessionId,lineOut)- 选择-- 设置curTable='';从 Reporting101a.reportOutput 中删除WHERE sessionId=pSessionId;打开cur1;read_loop: 循环FETCH cur1 INTO lv_tblName,lv_ordVal,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra,lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,lv_cExtraMaxLen;如果完成 THEN离开读循环；万一;如果 lv_tblName<>curTable THEN如果 bFirst=FALSE 那么INSERT Reporting101a.reportOutput(sessionId,lineOut)选择 pSessionId,'';别的SET bFirst=FALSE;万一;INSERT Reporting101a.reportOutput(sessionId,lineOut)选择 pSessionId,lv_tblName;INSERT Reporting101a.reportOutput(sessionId,lineOut)SELECT pSessionId,CONCAT('+-',重复('-',GREATEST(5,lv_cFieldMaxLen)), '-+-',重复('-',GREATEST(4,lv_cTypeMaxLen)), '-+-',重复('-',GREATEST(4,lv_cNullMaxLen)), '-+-',重复('-',GREATEST(3,lv_cKeyMaxLen)), '-+-',重复('-',GREATEST(7,lv_cDefaultMaxLen)), '-+-',REPEAT('-',GREATEST(5,lv_cExtraMaxLen)), '-+');SET @dashLineNumRow=LAST_INSERT_ID();INSERT Reporting101a.reportOutput(sessionId,lineOut)SELECT pSessionId,CONCAT('| ','场地'，重复(' ',GREATEST(0,lv_cFieldMaxLen-5)), ' |','类型'，重复(' ',GREATEST(0,lv_cTypeMaxLen-4)), ' |','空值'，重复(' ',GREATEST(0,lv_cNullMaxLen-4)), ' |','钥匙'，重复(' ',GREATEST(0,lv_cKeyMaxLen-3)), ' |','默认'，重复(' ',GREATEST(0,lv_cDefaultMaxLen-7)), ' |','额外的'，重复(' ',GREATEST(0,lv_cExtraMaxLen-5)), '|');INSERT Reporting101a.reportOutput(sessionId,lineOut)选择 pSessionId,lineOut来自 Reporting101a.reportOutputWHERE lineNum=@dashLineNumRow;-- SELECT * FROM Reporting101a.reportDataDefs WHERE sessionId=24;SET curTable=lv_tblName;万一;INSERT Reporting101a.reportOutput(sessionId,lineOut)选择 pSessionId，CONCAT('|',合并(lv_cField，'')，COALESCE(REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-LENGTH(lv_cField))),''),' |',合并(lv_cType，'')，COALESCE(REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-LENGTH(lv_cType))),''),' |',合并(lv_cNull，'')，COALESCE(REPEAT(' ',GREATEST(0,lv_cNullMaxLen-LENGTH(lv_cNull))),''),' |',COALESCE(lv_cKey,' '),COALESCE(REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-LENGTH(lv_cKey))),''),' |',合并(lv_cDefault，'')，COALESCE(REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-LENGTH(lv_cDefault))),''),' |',合并(lv_cExtra，'')，COALESCE(REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-LENGTH(lv_cExtra))),''),'|');INSERT Reporting101a.reportOutput(sessionId,lineOut)选择 pSessionId,lineOut来自 Reporting101a.reportOutputWHERE lineNum=@dashLineNumRow;结束循环；关闭 cur1;select lineOut as '' from Reporting101a.reportOutput where sessionId=pSessionId order by lineNum;完$$分隔符；测试
测试:
-- 见**注2**设置@theOutVar =-1;-- 一个变量用作下面的 OUT 变量-- 见**注3**-- 注意:以 `TRUE` 作为第 4 个参数，这是一个一次性交易.意思是，你已经完成了.调用 Reporting101a.describeTables_v2a('stackoverflow',@theOutVar,false,true);-- 见**注4**-- 主要用于上面的第 4 个参数为 false 时调用 Reporting101a.Print_Tables_Like_Describe(@theOutVar);-- 以块格式加载数据以获得更漂亮的结果.输出
+--------------------------------------------------------------------------------------------+||+----------------------------------------------------------------------------------------------+|课程||+------------+--------------+------+-----+---------+----------------+ |||领域 |类型 |空 |钥匙 |默认 |额外 |||+------------+--------------+------+-----+---------+----------------+ |||课程编号 |整数(11) |否 |PRI ||自动增量|||+------------+--------------+------+-----+---------+----------------+ |||部门编号 |整数(11) |否 |多 |||||+------------+--------------+------+-----+---------+----------------+ |||课程名称 |varchar(100) |否 ||||||+------------+--------------+------+-----+---------+----------------+ ||||部门 ||+------------+--------------+------+-----+---------+----------------+ |||领域 |类型 |空 |钥匙 |默认 |额外 |||+------------+--------------+------+-----+---------+----------------+ |||部门编号 |整数(11) |否 |PRI ||自动增量|||+------------+--------------+------+-----+---------+----------------+ |||部门名称 |varchar(100) |否 ||||||+------------+--------------+------+-----+---------+----------------+ ||||连接 ||+------------+---------+------+-----+---------+----------------+ |||领域 |类型 |空 |钥匙 |默认 |额外 |||+------------+---------+------+-----+---------+----------------+ |||身份证 |整数(11) |否 |PRI ||自动增量|||+------------+---------+------+-----+---------+----------------+ |||学生ID |整数(11) |否 |多 |||||+------------+---------+------+-----+---------+----------------+ |||课程编号 |整数(11) |否 |多 |||||+------------+---------+------+-----+---------+----------------+ |||期限 |整数(11) |否 ||||||+------------+---------+------+-----+---------+----------------+ |||出席 |整数(11) |否 ||||||+------------+---------+------+-----+---------+----------------+ |||等级 |整数(11) |否 ||||||+------------+---------+------+-----+---------+----------------+ ||||学生 ||+-----------+--------------+------+-----+---------+----------------+ |||领域 |类型 |空 |钥匙 |默认 |额外 |||+-----------+--------------+------+-----+---------+----------------+ |||学生ID |整数(11) |否 |PRI ||自动增量|||+-----------+--------------+------+-----+---------+----------------+ |||全名 |varchar(100) |否 ||||||+-----------+--------------+------+-----+---------+----------------+ ||||测试表||+-----------------------------------------+---------------+------+-----+---------+-------+ |||领域 |类型 |空 |钥匙 |默认 |额外 |||+-----------------------------------------+---------------+------+-----+---------+-------+ |||noPKhere |整数(11) |否 ||||||+-----------------------------------------+---------------+------+-----+---------+-------+ |||veryLongColumnName_And_Then.Some_%_More |十进制(12,2)|是 ||||||+-----------------------------------------+---------------+------+-----+---------+-------+ |||限制|整数(11) |否 ||||||+-----------------------------------------+---------------+------+-----+---------+-------+ ||||测试表2 ||+-------+---------+------+-------+-------+-------+|||领域 |类型 |空 |钥匙 |默认 |额外 |||+-------+---------+------+-------+-------+-------+|||身份证 |整数(11) |否 |PRI |||||+-------+---------+------+-------+-------+-------+|+----------------------------------------------------------------------------------------------+注意1:创建了一个名为Reporting101a 的数据库来容纳两个存储过程和一些支持表.这些例程首先调用一个存储过程，该存储过程通过使用一个字符串来引用要报告的数据库.
生成输出的数据通过特殊的INFORMATION_SCHEMA 数据库以只读安全方式访问.因此，不会触及所报告的数据库.
此数据库中保存了三个非临时表.
reportDataDefsSession- 用于获取会话的简单表#reportDataDefs- 从INFORMATION_SCHEMA返回的数据并稍作调整.它是基于会话的.reportOutput- 用于打印打印的表，如 MySQL 的DESCRIBE.它只是一个将输出放在一起的表格.它是基于会话的.
Note2:此 INT 变量作为 OUT 参数目标包含在内，写入并允许您在之后插入其他代码第一个存储过程准备数据.它代表一个会话#，它隔离输出以供以后报告.
某些环境(例如 PHP)具有某些技巧，这使其成为某些程序员的表演障碍.因此，如果您需要自己组合这两个存储过程，请这样做(如果您感到困惑，请单独询问我).
无论如何，它展示了数据或工作如何将存储过程调用链接在一起.
老实说，我将 session # 作为 OUT 参数的主要原因之一是我知道我必须创建一个 CURSOR 才能获得漂亮的输出.这需要一个
Note4:用于您想要不同的输出但想要使用会话 # 的情况.您通常不需要这个.
I am documenting a project using GitHub Wiki pages, and now I would like to export the MySQL database schema into markdown format. Is there a way to convert the CLI output to GitHub Wiki md? Ideally as tables.
My output looks like this:
解决方案This code is rather long. I apologize. It consists of two stored procedures. You may be satisfied running just the first one. The second uses the output from the first (the data the first left in tables). You may also wish to combine the code into one. But I kept them separate. The second stored proc produces the output to resemble describe myTable. But it performs it for ALL tables in the database you desire such output.
You use this by passing a parameter (string) for the database to report on.
I create a separate database, and the code explicitly references tables in that database by name. So if you have EXECUTE privileges to this stored procedure, you can run it from any current database. So, as a simple test, do not set the reporting database as your current database, and merely call the stored procedure by name (qualified with reporting db name). This is all shown in the Test chunk below.
Two Stored Procedures
CREATE SCHEMA Reporting101a; -- See **Note1**
DROP PROCEDURE IF EXISTS `Reporting101a`.`describeTables_v2a`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`describeTables_v2a`(
IN dbName varchar(100), -- the dbname to report table structures
OUT theSession int, -- OUT parameter for session# assigned
IN deleteSessionRows BOOL, -- true for delete rows when done from main reporting table for this session#
IN callTheSecondStoredProc BOOL -- TRUE = output is from Pretty output in Second Stored Proc. FALSE= not so pretty output
)
BEGIN
DECLARE thisTable CHAR(100);
DROP TEMPORARY TABLE IF EXISTS Reporting101a.tOutput;
CREATE TEMPORARY TABLE Reporting101a.tOutput
( id int auto_increment primary key,
tblName varchar(100) not null,
ordVal int not null,
cField varchar(100) not null,
cType varchar(100) not null,
cNull varchar(100) not null,
cKey varchar(100) not null,
cDefault varchar(100) null,
cExtra varchar(100) null
);
DROP TEMPORARY TABLE IF EXISTS Reporting101a.tOutput2;
CREATE TEMPORARY TABLE Reporting101a.tOutput2
( tblName varchar(100) primary key,
colCount int not null,
cFieldMaxLen int not null,
cTypeMaxLen int not null,
cNullMaxLen int not null,
cKeyMaxLen int not null,
cDefaultMaxLen int not null,
cExtraMaxLen int not null
);
INSERT Reporting101a.tOutput(tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra)
SELECT TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME AS Field, COLUMN_TYPE AS TYPE, RPAD(IS_NULLABLE,4,' ') AS 'Null',
RPAD(COLUMN_KEY,3,' ') AS 'Key',RPAD(COLUMN_DEFAULT,7,' ') AS 'DEFAULT',EXTRA AS Extra
FROM information_schema.columns WHERE table_schema = dbName ORDER BY table_name,ordinal_position;
-- select * from information_schema.columns WHERE table_schema = '57security' order by table_name,ordinal_position;
UPDATE Reporting101a.tOutput
SET cExtra=' '
WHERE cExtra='';
UPDATE Reporting101a.tOutput
SET cField=RPAD(cField,5,' ')
WHERE LENGTH(cField)<5;
INSERT Reporting101a.tOutput2(tblName,colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen)
SELECT tblName,COUNT(*),0,0,0,0,0,0
FROM Reporting101a.tOutput
GROUP BY tblName;
UPDATE tOutput2 t2
JOIN
( SELECT tblName,MAX(LENGTH(cField)) AS mField,MAX(LENGTH(cType)) AS mType,MAX(LENGTH(cNull)) AS mNull,
IFNULL(MAX(LENGTH(cKey)),0) AS mKey,IFNULL(MAX(LENGTH(cDefault)),0) AS mDefault,IFNULL(MAX(LENGTH(cExtra)),0) AS mExtra
FROM Reporting101a.tOutput
GROUP BY tblName
) x
ON x.tblName=t2.tblName
SET t2.cFieldMaxLen=x.mField,t2.cTypeMaxLen=x.mType,cNullMaxLen=x.mNull,
cKeyMaxLen=x.mKey,cDefaultMaxLen=x.mDefault,cExtraMaxLen=x.mExtra;
-- DROP TABLE Reporting101a.reportDataDefsSession; -- useful for quick change of structure of table
-- note, keep above drop call remmed out ! Just use it for quick tweaks to structure
CREATE TABLE IF NOT EXISTS Reporting101a.reportDataDefsSession
( -- for the sole purpose of safe session auto_inc usage
-- Please don't delete unless you want the sessions to experience aberant behavior
sessionId INT AUTO_INCREMENT PRIMARY KEY,
dummy CHAR(1) NOT NULL,
creationDT datetime not null
);
CREATE TABLE IF NOT EXISTS Reporting101a.reportDataDefs
( sessionId INT NOT NULL,
tblName VARCHAR(100) NOT NULL, -- Tablename
ordVal INT NOT NULL, -- the "position number" of the Column
cField VARCHAR(100) NOT NULL, -- The Column
cType VARCHAR(100) NOT NULL, -- Datatype
cNull VARCHAR(100) NOT NULL, -- Nullability
cKey VARCHAR(100) NOT NULL, -- Key info
cDefault VARCHAR(100) NULL, -- Default value
cExtra VARCHAR(100) NULL, -- Extra output
colCount INT NOT NULL, -- the columns here and below are de-normalize data
cFieldMaxLen INT NOT NULL,
cTypeMaxLen INT NOT NULL,
cNullMaxLen INT NOT NULL,
cKeyMaxLen INT NOT NULL,
cDefaultMaxLen INT NOT NULL,
cExtraMaxLen INT NOT NULL
);
-- For lack of a better notion, we are calling calls "sessions". The programmer calls the
-- First Stored Proc, and we call that a session after we get a unique next incrementing number.
-- That number is the session #. House all output with that as a column value. This allows us to
-- move between stored procs, have safe output, have historical snapshots, and retain the data
-- via a session # for later use, whatever use.
INSERT Reporting101a.reportDataDefsSession(dummy,creationDT) VALUES ('X',now());
SET @mySession=LAST_INSERT_ID(); -- there it is, our session # (read the above paragraph)
INSERT Reporting101a.reportDataDefs(sessionId,tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,
colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen)
SELECT @mySession,t1.tblName,t1.ordVal,t1.cField,t1.cType,t1.cNull,t1.cKey,t1.cDefault,t1.cExtra,
t2.colCount,t2.cFieldMaxLen,t2.cTypeMaxLen,t2.cNullMaxLen,t2.cKeyMaxLen,t2.cDefaultMaxLen,t2.cExtraMaxLen
FROM Reporting101a.tOutput t1
JOIN Reporting101a.tOutput2 t2
ON t2.tblName=t1.tblName
ORDER BY t1.tblName,t1.id;
DROP TEMPORARY TABLE Reporting101a.tOutput;
DROP TEMPORARY TABLE Reporting101a.tOutput2;
SET theSession=@mySession; -- the OUT var that came in as a parameter
-- ***************************************************************************
-- ***************************************************************************
-- Label "Some_Sort_of_Output":
IF callTheSecondStoredProc=TRUE THEN
-- The caller says to call the second stored proc (for Pretty Printing)
-- This will generate output similar to `DESCRIBE myTable`
-- But remember, it will do it for EVERY table in referenced database
CALL Reporting101a.`Print_Tables_Like_Describe`(@mySession);
-- The above call just gave you output.
ELSE
-- The caller chose to not auto call the Pretty Printing second stored procedure.
-- Note, the caller can easily call it right after using the OUT parameter.
-- So our output will be a resultset of out reportDataDefs table for this session #
SELECT *
FROM Reporting101a.reportDataDefs
WHERE sessionId=@mySession
ORDER BY tblName,ordVal;
END IF;
-- ***************************************************************************
-- ***************************************************************************
IF deleteSessionRows=TRUE THEN
-- The caller says output rows are NOT needed at this point. Delete them.
-- Note, if this boolean comes in TRUE, you can't call Pretty Printing
-- second stored procedure with the session # because the data is gone.
--
-- Regardless, you are getting something back from "Some_Sort_of_Output" above.
DELETE FROM Reporting101a.reportDataDefs
WHERE sessionId=@mySession;
END IF;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `Reporting101a`.`Print_Tables_Like_Describe`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`Print_Tables_Like_Describe`(
pSessionId INT
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE curTable VARCHAR(100) DEFAULT '';
DECLARE bFirst BOOL DEFAULT TRUE;
DECLARE lv_tblName,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra VARCHAR(100);
DECLARE lv_ordVal,lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,lv_cExtraMaxLen INT;
DECLARE cur1 CURSOR FOR SELECT tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,
colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen
FROM Reporting101a.reportDataDefs
WHERE sessionId=pSessionId
ORDER BY tblName,ordVal;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Please note in the above, CURSOR stuff must come last else "Error 1337: Variable or condition decl aft curs"
CREATE TABLE IF NOT EXISTS Reporting101a.reportOutput
( lineNum INT AUTO_INCREMENT PRIMARY KEY,
sessionId INT NOT NULL,
lineOut varchar(100) NOT NULL
);
-- INSERT Reporting101a.reportOutput(sessionId,lineOut)
-- SELECT
-- SET curTable='';
DELETE FROM Reporting101a.reportOutput
WHERE sessionId=pSessionId;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO lv_tblName,lv_ordVal,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra,
lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,lv_cExtraMaxLen ;
IF done THEN
LEAVE read_loop;
END IF;
IF lv_tblName<>curTable THEN
IF bFirst=FALSE THEN
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,'';
ELSE
SET bFirst=FALSE;
END IF;
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,lv_tblName;
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,CONCAT('+-',
REPEAT('-',GREATEST(5,lv_cFieldMaxLen)), '-+-',
REPEAT('-',GREATEST(4,lv_cTypeMaxLen)), '-+-',
REPEAT('-',GREATEST(4,lv_cNullMaxLen)), '-+-',
REPEAT('-',GREATEST(3,lv_cKeyMaxLen)), '-+-',
REPEAT('-',GREATEST(7,lv_cDefaultMaxLen)), '-+-',
REPEAT('-',GREATEST(5,lv_cExtraMaxLen)), '-+');
SET @dashLineNumRow=LAST_INSERT_ID();
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,CONCAT('| ',
'Field',
REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-5)), ' | ',
'Type',
REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-4)), ' | ',
'Null',
REPEAT(' ',GREATEST(0,lv_cNullMaxLen-4)), ' | ',
'Key',
REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-3)), ' | ',
'Default',
REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-7)), ' | ',
'Extra',
REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-5)), ' |');
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,lineOut
FROM Reporting101a.reportOutput
WHERE lineNum=@dashLineNumRow;
-- SELECT * FROM Reporting101a.reportDataDefs WHERE sessionId=24;
SET curTable=lv_tblName;
END IF;
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,
CONCAT('| ',
COALESCE(lv_cField,''),
COALESCE(REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-LENGTH(lv_cField))),''),' | ',
COALESCE(lv_cType,''),
COALESCE(REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-LENGTH(lv_cType))),''),' | ',
COALESCE(lv_cNull,''),
COALESCE(REPEAT(' ',GREATEST(0,lv_cNullMaxLen-LENGTH(lv_cNull))),''),' | ',
COALESCE(lv_cKey,' '),
COALESCE(REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-LENGTH(lv_cKey))),''),' | ',
COALESCE(lv_cDefault,' '),
COALESCE(REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-LENGTH(lv_cDefault))),''),' | ',
COALESCE(lv_cExtra,' '),
COALESCE(REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-LENGTH(lv_cExtra))),''),' |');
INSERT Reporting101a.reportOutput(sessionId,lineOut)
SELECT pSessionId,lineOut
FROM Reporting101a.reportOutput
WHERE lineNum=@dashLineNumRow;
END LOOP;
CLOSE cur1;
select lineOut as '' from Reporting101a.reportOutput where sessionId=pSessionId order by lineNum;
END$$
DELIMITER ;
Test
Test:
-- See **Note2**
SET @theOutVar =-1; -- A variable used as the OUT variable below
-- See **Note3**
-- Note: with `TRUE` as the 4th parameter, this is a one call deal. Meaning, you are done.
call Reporting101a.describeTables_v2a('stackoverflow',@theOutVar,false,true);
-- See **Note4**
-- Primarily used if the 4th parameter above is false
call Reporting101a.Print_Tables_Like_Describe(@theOutVar); -- loads data for prettier results in chunk format.
Output
+--------------------------------------------------------------------------------------------+
| |
+--------------------------------------------------------------------------------------------+
| course |
| +------------+--------------+------+-----+---------+----------------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +------------+--------------+------+-----+---------+----------------+ |
| | courseId | int(11) | NO | PRI | | auto_increment | |
| +------------+--------------+------+-----+---------+----------------+ |
| | deptId | int(11) | NO | MUL | | | |
| +------------+--------------+------+-----+---------+----------------+ |
| | courseName | varchar(100) | NO | | | | |
| +------------+--------------+------+-----+---------+----------------+ |
| |
| dept |
| +----------+--------------+------+-----+---------+----------------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +----------+--------------+------+-----+---------+----------------+ |
| | deptId | int(11) | NO | PRI | | auto_increment | |
| +----------+--------------+------+-----+---------+----------------+ |
| | deptName | varchar(100) | NO | | | | |
| +----------+--------------+------+-----+---------+----------------+ |
| |
| scjunction |
| +------------+---------+------+-----+---------+----------------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +------------+---------+------+-----+---------+----------------+ |
| | id | int(11) | NO | PRI | | auto_increment | |
| +------------+---------+------+-----+---------+----------------+ |
| | studentId | int(11) | NO | MUL | | | |
| +------------+---------+------+-----+---------+----------------+ |
| | courseId | int(11) | NO | MUL | | | |
| +------------+---------+------+-----+---------+----------------+ |
| | term | int(11) | NO | | | | |
| +------------+---------+------+-----+---------+----------------+ |
| | attendance | int(11) | NO | | | | |
| +------------+---------+------+-----+---------+----------------+ |
| | grade | int(11) | NO | | | | |
| +------------+---------+------+-----+---------+----------------+ |
| |
| student |
| +-----------+--------------+------+-----+---------+----------------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +-----------+--------------+------+-----+---------+----------------+ |
| | studentId | int(11) | NO | PRI | | auto_increment | |
| +-----------+--------------+------+-----+---------+----------------+ |
| | fullName | varchar(100) | NO | | | | |
| +-----------+--------------+------+-----+---------+----------------+ |
| |
| testtable |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | noPKhere | int(11) | NO | | | | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | veryLongColumnName_And_Then.Some_%_More | decimal(12,2) | YES | | | | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | limit | int(11) | NO | | | | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| |
| testtable2 |
| +-------+---------+------+-----+---------+-------+ |
| | Field | Type | Null | Key | Default | Extra | |
| +-------+---------+------+-----+---------+-------+ |
| | id | int(11) | NO | PRI | | | |
| +-------+---------+------+-----+---------+-------+ |
+--------------------------------------------------------------------------------------------+
Note1: A Database called Reporting101a is created to house two stored procedures and some support tables. These routines start by a call a stored procedure referring to the database to be reported on by use of a string.
The data to produce the output is accessed thru the special INFORMATION_SCHEMA database in a READ-only safe manner. As so, the database being reported on is not touched.
There are three non-temporary tables kept in this database.
reportDataDefsSession- A simple table used for getting a session#reportDataDefs- data returne fromINFORMATION_SCHEMAand massaged a bit. It is session-based.reportOutput- A table for print printing like MySQL'sDESCRIBE. It is just a table for putting the output together. It is session-based.
Note2: This INT variable is included as an OUT parameter target, written to, and allows you to wedge your other code in after the First stored procedure prepares the data. It represents a session # that isolates the output for later reporting.
Some environments such as PHP have certain tricks that make this a show-stopper for some programmers. So if you need to combine both stored procedures on your own, then do so (or ask me to separately if you are confused).
In any case, it shows how data or efforts can Chain Together stored procedure calls.
Honestly one of the main reasons I come out with a session # as an OUT parameter is that I know I have to create a CURSOR to get the pretty output. And that requires a Cursor DECLARE at the top of a second stored procedure. And DECLAREs must occur at the top of a stored procedure. So with hands tied, I went this route.
Note3: This is the call to the First stored procedure. It is highly likely that you will be done after this call having TRUE as your 4th parameter. The Stored Procedure is pretty well documented inside of it. The 3rd parameter is for whether or not you want the data deleted from the reporting table for the session #. The delete occurs after any output as a resultset. So, this depends on your choice.
Parameters:
- the database name to describe all tables like
describe myTable - the
INTOUTparameter to hold the session # - boolean: do you want the data deleted from the reporting table at the end
- boolean: should we auto-call the pretty printing stored procedure that generates
describe-like output. If you decide to pass parameter 4 asFALSE, then your output may resemble this:
Note4: Used in cases where you want different output but want the session # to work with. You typically don't need this.
相关文章