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

tl;dr: Are there any docs that actually show a simple example of creating a table with DBIC, whether from SQL (which I know I know how to write) or a DBIC class module (which I think I know how to write)? If not, would any kind monk please give me such an example?

This is certainly a FAQ. However, I have done a lot of reading without finding the answer. The closest I have come is https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/FAQ.pod, where the first section is ".. create a database to use?". This says "decide whether you want to have the database itself be the definitive source of information about the data layout, or your DBIx::Class schema". Actually, the answer is neither, but I know (or think I do) how to convert the information I have into a schema, so the yak shaving follows this path, "read the FAQ on setting up your classes manually, and the one on creating tables from your schema".

Setting up classes manually seems to work, but creating tables gets hairier. The FAQ says "Write a script that calls "deploy" in DBIx::Class::Schema. See there for details, or the DBIx::Class::Manual::Cookbook" The "see there" link is https://metacpan.org/pod/DBIx::Class::Schema#deploy. This says:

deploy Arguments: \%sqlt_args, $dir Attempts to deploy the schema to the current storage using SQL::Translator. See "METHODS" in SQL::Translator for a list of values for \%sqlt_args. The most common value for this would be { add_drop_table => 1 } to have the SQL produced include a DROP TABLE statement for each table created. For quoting purposes supply quote_identifiers. Additionally, the DBIx::Class parser accepts a sources parameter as a hash ref or an array ref, containing a list of source to deploy. If present, then only the sources listed will get deployed. Furthermore, you can use the add_fk_index parser parameter to prevent the parser from creating an index for each FK.

... which, frankly, isn't worth a FK. I don't want to drop a table, nor do I want to guess whether FK is supposed to mean Foreign Key. Even going to the linked docs is unhelpful, as the "Schema" method "Returns the SQL::Translator::Schema object" rather than allowing me to name a schema, as I had hoped.

The second link, https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod, is no better. There is no hint on which "recipe" to use, so, based on the "just use deploy" instruction much earlier, I tried searching for "deploy" in the document. The likeliest section I found was "Schema versioning", which started off with instructions very similar to others I'd read. The "deploy to customers" section reads:

There are several ways you could deploy your schema. These are probably beyond the scope of this recipe, but might include: 1. Require customer to apply manually using their RDBMS. 2. Package along with your app, making database dump/schema update/tests all part of your install.

... which is no help at all. Again. I haven't listed all the other dead ends I have gone down, as my frustration is showing already and repeating fruitless exercises would increase it.

TIA & Regards,

John Davies

  • Comment on Adding a database table using DBIx::Class

Replies are listed 'Best First'.
Re: Adding a database table using DBIx::Class
by Your Mother (Archbishop) on Oct 23, 2016 at 03:22 UTC

    So the short answer is no, DBIC does not support what you want to do. It’s everything or nothing. You can create every table in your schema easily with $schema->deploy. Just not a single table.

    It is easy to use straight DBI to create a single table and you have access to DBI through DBIC. The most relevant doc is probably DBIx::Class::Storage::DBI. Look for the part with $schema->storage->dbh_do(...)

    There might be some fun shortcuts to getting the table definition and “doing” it through the schema but I don’t know them just now.

Re: Adding a database table using DBIx::Class
by virtualsue (Vicar) on Sep 26, 2017 at 13:41 UTC