Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
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 rifling through the Monastery: (3)
As of 2018-12-14 03:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How many stories does it take before you've heard them all?







    Results (63 votes). Check out past polls.

    Notices?