Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Querying and parsing MySQL data types

by LanX (Archbishop)
on Nov 30, 2018 at 21:58 UTC ( #1226557=perlquestion: print w/replies, xml ) Need Help??

LanX has asked for the wisdom of the Perl Monks concerning the following question:

Hi

I'm most probably reinventing the wheel

I had to write a parser to parse mysql types like

  • smallint(6) unsigned zerofill
  • enum('export','import')
  • enum('Y','N')
Is there any way to get this type info in a more atomic and standardized way?

I tried DBI::column_info and querying information_schema.`COLUMNS` but couldn't get anything which didn't required writing a parser.

background

My job involves writing many HTML/JS GUIs for our data models and my colleagues are quite "agile" in changing data-models on the fly. Our schemas are replicated (but not synchronized) on different servers.

Now I'm trying to automate type-checking and form generation on the GUI level.

Such that enum('Y','N') results in a [ ] check box and enum('export','import','view') in a select menu.

update

actually using DBI::column_info I'm getting quite detailed informations for enum

mysql_type_name => "enum('Y','N')", mysql_values => ["Y", "N"], TYPE_NAME => "ENUM",

but informations like unsigned and zerofill seem to get lost.

Cheers Rolf
(addicted to the Perl Programming Language :)
Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

update

fixed format issue, thanks stevieb++

Replies are listed 'Best First'.
Re: Querying and parsing MySQL data types
by kschwab (Vicar) on Nov 30, 2018 at 22:19 UTC
    You mentioned "COLUMNS from information_schema", but you can get the same info with a little simpler syntax:
    mysql> SHOW COLUMNS FROM student LIKE 's%';
    +------------+------------------+------+-----+---------+----------------+
    | Field      | Type             | Null | Key | Default | Extra          |
    +------------+------------------+------+-----+---------+----------------+
    | sex        | enum('F','M')    | NO   |     | NULL    |                |
    | student_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    +------------+------------------+------+-----+---------+----------------+
    
    I suppose that doesn't help much, because you still have to parse out all the stuff in 'Type' and 'Extra'.
      Thanks, TIMTOWTDI and I tried most of them:

      DESCRIBE t_tm1_export_import_attributes;

      Field Type Null Key Default Extra
      f_node_id int(10) unsigned NO PRI
      f_transfertype enum('export','import') NO export
      f_cube varchar(100) NO cubename
      f_active enum('Y','N') NO Y
      f_checkposliste enum('0','1') NO 0
      f_kumulativ enum('0','1') NO 0
      f_bstdimnr int(11) NO 0
      f_file varchar(300) NO
      f_info varchar(300) NO

      Cheers Rolf
      (addicted to the Perl Programming Language :)
      Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1226557]
Front-paged by stevieb
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (6)
As of 2019-08-20 01:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If you were the first to set foot on the Moon, what would be your epigram?






    Results (142 votes). Check out past polls.

    Notices?