3

I have a MySQL database dump with just the data I would like to use as Alice Fixtures. I would like to have versioned yaml files to commit onto my branch. Does someone know of a library or a bundle that parses SQL to generate YAML fixtures from them or should I just go ahead and invent the wheel ? I mean converting something like :

CREATE TABLE `shop` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `slug` VARCHAR(120) NOT NULL,
  `parent` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_SHOP_PARENT` FOREIGN KEY (`parent`) REFERENCES `shop` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `shop` VALUES
(1, 'Item Shop', 'item-shop-1', NULL),
(2, 'Forge', 'forge-1', NULL),
(3, 'Weapon Shop', 'forge-1-weapon-shop', 2),
(4, 'Armor Shop', 'forge-1-armor-shop', 2);

(Note the Foreign Key)

into this :

Shop:
  shop_1:
    id: 1
    name: Item Shop
    slug: item-shop-1
  shop_2:
    id: 2
    name: Forge
    slug: forge-1
  shop_3:
    id: 3
    name: Weapon Shop
    slug: forge-1-weapon-shop
    parent: '@shop_2'
  shop_4
    id: 4
    name: Armor Shop
    slug: forge-1-armor-shop
    parent: '@shop_2'

(Note the reference '@shop_2')

This seems like something that people may have needed in the past and that wouldn't have been too hard to make. If anyone knows of some library or bundle that does that I'd be very thankful. If not, I'll definitely get started on it and come back to answer this question with a link to the repo.

Mouradif
  • 2,666
  • 1
  • 20
  • 37
  • I don't know of any such tool. If I had this task, I would not try to develop an SQL parser, I would load the dump file into a MySQL instance, and then use a Python or Ruby script to query the tables and format the result as YAML. – Bill Karwin Jan 09 '19 at 17:14
  • Good idea ! But wouldn't that be a lot heavier on performances ? Not to mention the additional dependency ? – Mouradif Jan 09 '19 at 17:26
  • You would rather implement a MySQL-compatible SQL parser that can read dump files? Keep in mind there are some some client commands in a dump file that are not recognized by MySQL's SQL grammar. And make sure to keep your parser updated with language updates in recent versions of MySQL. – Bill Karwin Jan 09 '19 at 17:29
  • Even with the dependency on MySQL, you could have your data conversion done within an hour, compared with the *weeks* of work it would take to code a parser-based solution. – Bill Karwin Jan 09 '19 at 17:31
  • I believe MySQL parsing is a way more common requirement than the one I mention on my question so I can trust existing libraries for that. Actually after just two Google Searches I think I might use this one written in Go : https://github.com/donatj/sqlread – Mouradif Jan 09 '19 at 17:35

1 Answers1

0

Although not Symfony related, phpMyAdmin does have a YAML export feature, you can read more about the export types it supports here: https://docs.phpmyadmin.net/nl/latest/import_export.html

Dweezahr
  • 190
  • 1
  • 9