[Dbix-class] DBIC/SQL::Translator is not preserving Postgres sequence names when deploying

[Ben Tilly]

I've found that there is a lot of information that you want in a
database that simply does not survive a round trip through DBIC.  We
therefore have settled maintaining the schema with raw SQL files, then
generating the Perl code by loading them into a temporary database,
and using DBIx::Class::Schema::Loader to dump our classes.  For
deployment we dump the schema of the target, then use SQL::Translator
to come up with a diff.  For production we like having a human review
that diff before deploying it, and the diffs we get are proving to be
pretty nice.

To make this work like we wanted I had to add a lot of customizability
to DBIx::Class::Schema::Loader.  But now you can do things like
perltidy the generated Perl before it hashes things, so this works
acceptably well for us.

On Fri, Feb 17, 2012 at 1:24 AM, Alexander Hartmaier
<alexander.hartmaier at t-systems.at> wrote:
> deploy uses SQL::Translator as you can see in
> Storage::DBI::deployment_statements.
> The SQLT schema doesn't currently have sequence objects so there is no
> place to store those names.
> Also not all databases have them or use them for autoinc pks.
>
> I've removed all occurences of sequence because I don't really care
> about their names, maybe that's an option for you too.
>
> Am 2012-02-16 20:08, schrieb Robert Rothenberg:
>> I've run into a strange issue.
>>
>> We have a Postgres database where the sequences used for primary keys are
>> explicitly named, e.g. "user_sequence" instead of the implicit names, such
>> as "user_id_seq".
>>
>> We've created our DBIx::Result classes (via Catalyst's myapp_create script)
>> from that database.  The sequence names are in the classes, e.g.
>>
>> __PACKAGE__->add_columns(
>>   "id",
>>   {
>>     data_type         => "integer",
>>     is_auto_increment => 1,
>>     is_nullable       => 0,
>>     sequence          => "user_sequence",
>>   },
>>   ...
>> );
>>
>> But when we deploy a new database from those classes, the sequences aren't
>> named, which actually causes problems with some SQL scripts that directly
>> refer to those sequences.
>>
>> Is there some way to get the deployment to name sequences? Or is this a bug
>> in the deployment?
>>
>> Regards,
>> Rob
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
> Handelsgericht Wien, FN 79340b
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> Notice: This e-mail contains information that is confidential and may be privileged.
> If you are not the intended recipient, please notify the sender and then
> delete this e-mail immediately.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk