List:General Discussion« Previous MessageNext Message »
From:SciBit MySQL Team Date:January 1 1970 12:00am
Subject:RE: Re: [OT] PostgreSQL / MySQL Data Dictionary
View as plain text  
> In MySQL, by parsing the output of SHOW CREATE TABLE.
> 
> It would be a boon if someone were to write a utility, in an OS-independent
> language, which does that parsing for all tables in a MySQL database and
> returns SQL output that's suitable for creating a set of system tables.
> Obviously bits of this task have already been accomplished by the folks who
> wrote the MySQL module in PHP, for example, and for all we know, much of the
> code for doing it may already exist in phpMyAdmin.
> 
> PB

Well it is not OS-independent, nor free, but the part that does the job you wish for is
actually free and can easily become OS independent.  MyCon
(http://www.scibit.com/products/mycon) comes with a commandline utility called MyRun
which it uses in the background to backup databases or single tables or groups of tables
or general scripts' data or to schedule these tasks with. It has full helpfile on the
command-line parameters and options, some of the output script options can optionally
include "drop if exists" statements, "create table" and the actual data, all neatly
parsed into SQL statements ready to recreate just your schema and/or all data as well. It
can also dump to another server live instead of to an output script file. It has been
optimized to dump an average size table's data at roughly 1000recs/sec on a 1xCPU3GHz
with MySQL running localhost using only enough client-side memory for a single record at
any given time.

In short is takes any source script (which you can setup yourself or use MyCon to auto
create it for you),ex:
---
select * from accounts;
<SNIP>...etc
select * from visits left join countries using (CountryID);
select * from servicerequests where ServDate>YEAR(CURDATE());
<SNIP>...etc
---

And then produces something like this:
-- MyRun
-- Source Server: <SNIPPED>
-- Source Script: Tables.Scheduled.Run.All.sql 
-- Target Script: Tables.Scheduled.Backup.All.sql
-- Target:        Tables.Scheduled.Backup.All.sql
-- Date:          2004-08-11 09:00:02 500

-- SCRIPT SQL: select * from accounts;

-- DROP: accounts
drop table if exists accounts;

-- CREATE: accounts
create table if not exists `accounts` (
  `AccountID` int(10) NOT NULL auto_increment,
  `AccountEmail` varchar(50) default NULL,
	<SNIP>..etc
  PRIMARY KEY  (`AccountID`),
  KEY `AccountStatusID` (`AccountStatusID`),
  KEY `AccountTypeID` (`AccountTypeID`)
) TYPE=MyISAM;

-- DATA: accounts BATCH #1
insert into accounts (AccountID,AccountEmail,<SNIP>..etc) values 
<SNIP> it then proceeds to add batches of 100 recs per INSERT statement
-- DATA: accounts BATCH #2
<SNIP>..etc

As said, the DROP, CREATE and DATA are all optional.  MyRun is ofcourse not the only
utility to do this, mysqldump has been around forever and just about every other MySQL
GUI includes this type of functionality.  That said, if there is really a demand for
something like this, I am sure we can put MyRun into both a linux and win32 CGI which can
then be prompted from a website. MyRun's source is OS independent, so this won't be an
issue.  The current version is win32 and although the scripting side of it is really
small, it got a bit bloated because it also includes code to generate live
PDF,XLS,RTF,etc reports from MySQL data, which means the report modules made it "heavy".
So a cgi would more than likely fall into the <200Kb footprint range, instead of
>1Mb.

Kind Regards
SciBit MySQL Team
http://www.scibit.com


Thread
Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLBrad Tilley10 Aug
    • Re: Difference between PostgreSQL and MySQLJosh Trutwin10 Aug
      • Re: Difference between PostgreSQL and MySQLJochem van Dieten10 Aug
        • Re: Difference between PostgreSQL and MySQLJosh Trutwin11 Aug
          • Re: Difference between PostgreSQL and MySQLJochem van Dieten11 Aug
            • Re: [OT] PostgreSQL / MySQL Data DictionaryJosh Trutwin11 Aug
              • Re: [OT] PostgreSQL / MySQL Data DictionaryJochem van Dieten11 Aug
                • MySQL Data Dictionary (INFORMATION_SCHEMA)Jim Winstead11 Aug
                  • Re: MySQL Data Dictionary (INFORMATION_SCHEMA)Jochem van Dieten11 Aug
                  • Re: MySQL Data Dictionary (INFORMATION_SCHEMA)Josh Trutwin11 Aug
          • Changing Fulltext defaults in Win32leegold11 Aug
  • Re: Difference between PostgreSQL and MySQLJochem van Dieten10 Aug
  • Re: Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLSGreen10 Aug
    • Re: Difference between PostgreSQL and MySQLMartijn Tonies10 Aug
    • Re: Difference between PostgreSQL and MySQLJochem van Dieten10 Aug
      • Re: Difference between PostgreSQL and MySQLJosh Trutwin11 Aug
  • Re: Difference between PostgreSQL and MySQLMartijn Tonies10 Aug
  • Re: Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLmos11 Aug
    • RE: Difference between PostgreSQL and MySQLLachlan Mulcahy11 Aug
      • On the licensing once againDebugasRu11 Aug
        • Re: On the licensing once againZak Greant11 Aug
        • RE: On the licensing once againLachlan Mulcahy11 Aug
          • R: On the licensing once againLeonardo Francalanci11 Aug
            • Re: R: On the licensing once againDebugasRu11 Aug
              • RE: R: On the licensing once againLachlan Mulcahy11 Aug
          • Re: On the licensing once againIssac Goldstand12 Aug
            • Re: On the licensing once againZak Greant12 Aug
  • Converting access to mysql dbEWAGW11 Aug
  • Re: Changing Fulltext defaults in Win32Cemal Dalar11 Aug
    • Re: Changing Fulltext defaults in Win32leegold11 Aug
  • Re: Changing Fulltext defaults in Win32Cemal Dalar11 Aug
  • Re: [OT] PostgreSQL / MySQL Data DictionaryPeter  Brawley11 Aug
    • RE: Re: [OT] PostgreSQL / MySQL Data DictionarySciBit MySQL Team11 Aug
      • Re: [OT] PostgreSQL / MySQL Data DictionaryEamon Daly11 Aug
        • RE: Re: [OT] PostgreSQL / MySQL Data DictionarySciBit MySQL Team12 Aug
          • Re: [OT] PostgreSQL / MySQL Data DictionaryEgor Egorov12 Aug
            • RE: [OT] PostgreSQL / MySQL Data DictionarySciBit MySQL Team12 Aug
Re: On the licensing once againZak Greant11 Aug
Re: On the licensing once againLachlan Mulcahy11 Aug
  • R: On the licensing once againLeonardo Francalanci11 Aug
    • Re: R: On the licensing once againSantino11 Aug
      • Re: R: On the licensing once againZak Greant12 Aug
    • Re: R: On the licensing once againZak Greant12 Aug
Re: On the licensing once againLeonardo Francalanci11 Aug
  • Re: On the licensing once againRich Lafferty11 Aug
Re: [OT] PostgreSQL / MySQL Data DictionarySciBit MySQL Team12 Aug