How a bundle can provide "default data" i.e. pre-filled tables in Symfony 2?

17
votes

I think I've a good understanding of Symfony and how bundle works.

However I've never found how to solve a simple problem: make a reusable bundle that provides data like tables/Doctrine entities pre-filled with (i.e.) all country names in the world, all provinces of Italy, tax rates history in England and so on.

Of course the purpose is to provide forms, services and controllers relying on this data source, without the need to copy and paste tables and entities across projects.

How would you do that?

Data fixtures IMHO are not an option because an obvious reason: you are going to purge your database while it's running.

A custom command reading from a static data-source (json, YAML) and performing inserts/updates?

4
symfonydoctrine-ormbundle
If you are not like doctrine fixtures only becuase of purging a database, you can use the option --append. As doc says: use this flag to append data instead of deleting data before loading it (deleting first is the default behavior). – Serge Kvashnin
That's really a good question, we have the same problem on a large project of ours. (We simply ship an install script, but that's really ugly). The ugliest part is that we need to hard code IDs into our code to reference the prefilled entities. Really looking forward to answers to this question. – lxg
@lxg -- are you familiar with Magento install/upgrade scripts? They provide a pretty versatile solution (read as: "implies significant work"). In a Magento install/upgrade script you can execute raw SQL or use the DBAL API to build tables, add data and fetch the resulting autoincrement IDs. Our convention was to insert these new autoincremented IDs into the Magento config table so we could later reference the config-key in our code (not the ids themselves). It's obviously not a perfect situation but unless your data has other unique identifiers (name, slug, uuid) how else could it be done. – Mihai Stancu
Here's a relevant example for Magento install/update script. The thing is the install/update scripts aren't that smart by themselves (they do have versioning control and they get run incrementally based on the module's declared version and its recorded installed version). But other than that the flexibility and configuration-keys and everything else are a byproduct of the Magento framework. – Mihai Stancu
If you are working with SQL/ORM, maybe Doctrine Migrations might help with updating the database. – Jasper N. Brouwer

4 Answers

10
votes

First step is declaring a Doctrine entity in your Bundle. I think you should create DataFixtures to populate your datas into db.

You maybe should consider to use Seeds instead of Fixtures.

Fixtures are fake datas, used to test your application

Seeds are the minimal datas required for your application to work.

Technically, these are exactly the same thing, you declare it under the "DataFixtures/" folder and you import them with the "doctrine:fixtures:load" command.

You can create a folder "Fixtures/", and a folder "Seeds/" under the folder "DataFixtures", then load your seeds with the command 

php app/console doctrine:fixtures:load --fixtures=/path/to/seeds/folder --append

It was suggested in the comments that it may be safer, especially in production environment, to create a custom Symfony2 command to force the "--append" mode. Without this mode, your database will be purged, and you could loose your production data.

2
votes

This answer assumes you're using composer to install your bundles (and you really exclude fixtures as an option).

What you can do, is make an SQL export of the data you want, and make sure it uses INSERT IGNORE INTO, and get the correct unique constraints.

Then you save that file somewhere in your bundle, in a "data" or "fixtures" folder. so your path to that file will be like: "vendor/company/epicbundle/data/countries.sql"

What you then can do, is add post-insert and post-update commands in your composer.json, that looks like this:

"post-install-cmd": [
    "php app/console doctrine:query:sql \"$(cat vendor/company/epicbundle/data/countries.sql)\""
]

If you only want it to run on install, you only add it there, if you sometimes update the sql file, you also add it to the post-update-cmd.

Please note that this solution only works if people don't temper with the table names, otherwise the queries will fail.

If you want a more save/stable solution, you can write your own post-install script in Symfony that uses the entity manager, and there you can use, for example, a csv file, and insert/update it row by row.

1
votes

Basically, anything you could implement would surely rely on persistence mechanisms used in your ORM/ODM/whatever. So, you'll end up implementing a typical fixture loading mechanism, at least partially: you'd execute code that saves some provided data; if it's serialized you'd do XML/JSON/YAML parsing (but this is just a technicality) and persist the results into the database.

Thus, it's not bad to stick with Doctrine Fixtures. They are programmable and extensible (you can even fetch your data from the web upon loading).

As stated in @paul-andrieux's answer, if you are worried about data loss (e.g. your bundle's seeds are loaded when the end user's DB is already up), you should use doctrine:fixtures:load --append and let the constraints do their job (like, in a country names table you'd have a unique constraint on country name or even a 'slug') so that inserting duplicate rows silently fails inserting a single entity, in case if your bundle has updated the country list, and the end user had a previous version.

If you really worry about your end users' data you could write a wrapper for the doctrine:fixtures:load command that would have the --append flag always on and register it as a separate command. (You could run needed migrations there, too)

@lxg's hard-coded IDs problem is solvable, too. Try using natural keys where applicable (e.g. the countries table would have a slug primary key that would be great-britain for Grean Britain). This way your searches would be pretty easy: $em->find('\MyBundle\Country', 'great-britain');. If you cannot come up with a natural key, then maybe the entity is not really needed for the end user.

UPD. Here's an article that could be useful: http://www.craftitonline.com/2014/09/doctrine-migrations-with-schema-api-without-symfony-symfony-cmf-seobundle-sylius-example/

0
votes

Generally speaking, the bundle embedded the entities that will be loaded via the ORM/ODM using their built-in commands (like doctrine:schema:update, doctrine:migration:diff, ...) and provides a custom command that load the required fixtures using the ODM/ORM

This command can read the fixtures in multiple way (parsing yaml, xml, raw sql, dql, ...), it is just a matter of taste. Tones of bundles, parser, ... exist for those tasks.

In your documentation, you just have to state in a clear way that the developer must run this command after your bundle installation and schema update.