[MDEV-4587] TABLE METADATA - Add a table history, about STRUCTURE and queries that was done to change the structure (like GIT log) Created: 2013-05-26  Updated: 2013-06-18

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Trivial
Reporter: roberto spadim Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

hi guys, should be nice a tool or sql commands to see .frm history
what i'm talking about?
1)CREATE TABLE A (a int);
2)ALTER TABLE A add b int;
3)ALTER TABLE A drop a;

that information that could be nice to have
and maybe after this we could some tool like diff

diff table a.1 a.current
and it will create a query like:
)alter table a add b int, drop a;

something similar to git, but instead of checkout, just reproduce the diff query (queries) to update the current table from some point in time

second part is replication or export/import and merge
it's like merge in git, and push/pull

well it's a bit complicated but with this tool and git, i could have a full history of table inside database, instead inside some other file, and since it's inside database, i will never lost historical information

i don't know if this could be done with binlog or something similar, but could be nice this tool be a per table log, instead a "daemon" binlog

for first version just history could be nice, and at a second version the diff tool

some fields that i thik that are usefull
unique hash (like git), user, datetime, ddl command

well that's all thanks guys!

 Comments   
Comment by Sergei Golubchik [ 2013-05-26 ]

you can do that with an audit plugin. this plugin can be notified when a table is created or altered, and it can keep track of all statements that did that.

Comment by roberto spadim [ 2013-05-26 ]

some ALTER command needs a UPDATE, in other words, maybe some UPDATE queries should be changed to include a option, like:
1) CREATE TABLE A (a int, primary key(a));
2) ALTER TABLE A add b int;
3) UPDATE INCLUDE_IN_HISTORY A SET b=a;
4) ALTER TABLE DROP PRIMARY KEY, ADD PRIMARY KEY (b);

the INCLUDE_IN_HISTORY could add the query to table definition log
well for now that's all

Comment by roberto spadim [ 2013-05-26 ]

hi sergey, i'm a source code begineer, could you give me more information about audit? maybe a example?
i will need information about how to write inside files too, example, how to write to file "table_name.hist" in the same folder of "table_name.frm"?
and the other question is, what file format should i use? a flat file? a "sqlite" file? any idea?

Comment by Sergei Golubchik [ 2013-05-26 ]

Yes. see plugin/audit_null example in the MariaDB 5.5 source tree.
It also shows how to create files and write there.
Still, for basic C/C++ questions, please use appropriate forums or mailing lists.

Comment by roberto spadim [ 2013-05-26 ]

nice, what about make it a 'default' feature of mariadb?
should i first create plugin, submit it and wait for a stable version?
after that mariadb could include it by default in binary/source code packages?

Comment by Sergei Golubchik [ 2013-05-26 ]

yes, if you create such a plugin, and we accept it into the mainline, then it can be included by default in our releases.

Comment by roberto spadim [ 2013-06-16 ]

maybe this MDEV-4259 could help here

Generated at Thu Feb 08 06:57:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.