dbsrgits / dbix-class-schema-loader Goto Github PK

Hello Support Team,

I would like to make the following proposal.

In the function _table_fk_info we should select the fields with rtrim. 
Some schema have subsequent blanks and later on key values do not exist in 
the object hashref.

=> Searching for relationship will fail.


SELECT tc.constname, sr.reftabschema, sr.reftabname,
       kcu.colname, rkcu.colname, kcu.colseq,
       sr.deleterule, sr.updaterule

to

SELECT tc.constname, rtrim(sr.reftabschema), sr.reftabname,
       kcu.colname, rkcu.colname, kcu.colseq,
       sr.deleterule, sr.updaterule


Kind regards

Frank Biermann

The classes that schema loader produces are indented with 2 spaces only.

This is fine for people who code with 2 spaces, but for the rest of us
who code with 4 this is a problem, as it forces us to continue coding
the rest of the class (methods, etc) with 2 spaces. Because our editors
like consistency.

Therefore, I propose you give us the option to choose the indentation
width (2 or 4 characters, or maybe even a tab character as well for the
more kinky developers.)

Thank you.

Need implementations of _table_comment and _column_comment for more
backends (oracle, db2, mssql...)

With extra tests covering them of course.

Oracle support added: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?
p=dbsrgits/DBIx-Class-Schema-
Loader.git;a=commitdiff;h=4cd5155bb0df2e2e59378d9e878af90285967e12

On Wed Jan 20 14:50:17 2010, rbuels wrote:
> Need implementations of _table_comment and _column_comment for more
> backends (oracle, db2, mssql...)
> 
> With extra tests covering them of course.
> 

An update on this ticket:

SineSwiper added support for the REMARKS field from $dbh->table_info and $dbh-
>column_info in _table_comment and _column_comment in ::DBI.

Someone else (I forget who) added better support for MySQL, even though MySQL's 
support for comments is very lacking.

There is also a generic mechanism available to store your comments in tables, a 
table_comments and column_comments tables (overridable via table_comments_table 
and column_comments_table attributes.)

What is left is to investigate and test comment support for other supported DBs.

for MSSQL:

select t.name [Table], prop.value [TableComment]
from sys.tables t left join sys.extended_properties prop
on t.object_id = prop.major_id and prop.minor_id = 0 and prop.name = 'MS_Description'

select t.name [Table], c.name [Column], prop.value [ColumnComment]
from sys.tables t
inner join sys.columns c on t.object_id = c.object_id
left join sys.extended_properties prop
on t.object_id = prop.major_id and c.column_id = prop.minor_id and prop.name = 'MS_Description'

Hello Team,

I am trying to build a small demo application using Mojolcious and
DBIx::Class. 
Here I am trying to run this script, which I have attached for reference.

D:\learning\perl_practice_scripts\tech_utsav>perl script\generate_schema
0.07040
Bizarre copy of UNKNOWN in scalar assignment at C:/Dwimperl/perl/site/lib/DBIx
/Class/Schema/Loader/Base.pm line 1059.

and the perl.exe gets hanged, stops working.

Please let me know your inputs or suggestions.
I have referred this link: "https://github.com/tempire/MojoExample/"

I have working schema for both sqlite3 and mysql. generate_script is something
like this:
##-----------------------------------------------------------##
use Modern::Perl;
use DBIx::Class::Schema::Loader 'make_schema_at';

my $DEBUG = @ARGV and $ARGV[0] =~ /^\-[\-]*v/;

say $DBIx::Class::Schema::Loader::VERSION ;#if $DEBUG;
my @dsn = 'dbi:SQLite:dbname=techutsav.db';

my $options = {
  debug          => $DEBUG,
  dump_directory => 'lib',
  components     => [qw/InflateColumn::DateTime /],
  generate_pod   => 0,
};
make_schema_at(Schema => $options, \@dsn);
##-----------------------------------------------------------

Regards,
Satish.

DISCLAIMER: This email message and all attachments are confidential and may
contain information that is Privileged, Confidential or exempt from disclosure
under applicable law. If you are not the intended recipient, you are notified
that any dissemination, distribution or copying of this email is strictly
prohibited.  If you have received this email in error, please notify us
immediately by return email to [email protected] and destroy the
original message.  Opinions, conclusions and other information in this message
that do not relate to the official of SPAN, shall be understood to be nether
given nor endorsed by SPAN.

It appears to me that nowadays "use parent" ist recommended over "use base". Can we have an option (or switch fully) to "use parent" in the generated schema?

Oracle materialised views not being identified correctly.

There's a partial implementation here: https://github.com/dbsrgits/dbix-class-schema-loader/pull/25

It doesn't handle the DDL creation side of things (oracle materialised views have weird scheduling syntax), and I'm not sure how to deal with test cases here, in case there's prior art I should know about.

I get this warning:

DBIx::Class::Storage::DBI::_warn_undetermined_driver(): This version of
DBIC does not yet seem to supply a driver for your particular RDBMS
and/or connection method ('MariaDB'). While we will attempt to continue
anyway, the results are likely to be underwhelming. Please upgrade DBIC,
and if this message does not go away, file a bugreport including the
following info:

and then underneath, more warnings and a fatal error messages, like:

DBIx::Class::Storage::DBI::sql_maker(): Your storage class
(DBIx::Class::Storage::DBI) does not set sql_limit_dialect and you have
not supplied an explicit limit_dialect in your connection_info. DBIC
will attempt to use the GenericSubQ dialect, which works on most
databases but can be (and often is) painfully slow. Please file an RT
ticket against 'DBIx::Class::Storage::DBI' at
/home/appuser/work/my_project/local/bin/dbicdump line 175

and

Cannot find primary keys for this driver:
DBIx::Class::Schema::Loader::DBI::try {...} (): DBI Exception:
DBD::MariaDB::db primary_key failed: Incorrect database name '' [for
Statement "SHOW KEYS FROM ``.`my_db`.`my_table`"] at
/home/appuser/work/my_project/local/bin/dbicdump line 175

MariaDB made a change about five years ago which causes it to say "current_timestamp()" rather than "current_timestamp". This prevent DBIx::Class::Schema::Loader from correctly importing such default values.

The change happened here, https://github.com/MariaDB/server/commit/a411d7f4f670c24b43b50f7d2a1129e10218f4a7

I have made a pull request which should fix this problem, https://github.com/dbsrgits/dbix-class-schema-loader/pull/24

Best regards,
Dave

Hi!

I have a pull request on github (https://github.com/dbsrgits/dbix-class-schema-loader/pull/11) which allows for filtering of the ungenerated part of classes, which is very useful for me and potentially other people. That's been quietly bitrotting over there for some time so I assume that doesn't get much attention so I thought I'd try my luck filing a ticket over here.

Any questions or criticism please let me know.

Thanks!

I'm loading a schema which contains views.

DBIx::Class::Schema::Loader::Base->_load_classess() unconditionally
whines about the lack of a primary key in tables it found.  For views,
this is pointless, as (at least in Postgres) it's not possible to mark
a column as one.

I second this [old] request. 

It's an annoying -feature- ... perhaps a quick fix would be to 1) ignore
pk definition altogether for views, or 2) look for first column of view
to be named id and if so, add it is pk for ResultSource definition.

Does this affect anybody else? Are there any workarounds, suggestions of
a fix? I would gladly write up a patch if any comments received from the
comments above...

An update on this ticket:

the warning is now gone.

The proper fix for this is to detect views separately from tables and generate proper 
DBIx::Class::ResultSource::View classes with the view SQL so that they can be 
redeployed.

Until that is done, leaving this ticket open.

Hi,

Upgraded Class::Accessor::Grouped and some of my existing code stopped 
working. The code in question is a dbix::class schema generated by 
DBIx::Class::Schema::Loader and used one of my db column names 
(1900_call_description) as an accessor name. Can you revert the sanity 
checking so accessors can start with a digit again please.


Thanks,
-Chris Mckay

I switched the queue of this bug, as it is S::L's problem, not CAG's.
You are confusing the ability to have columns with any name, with the
ability to create an arbitrary accessor name. You may not do the later
in perl (at least not normally) - every accessor must* conform to
/\A[A-Z_a-z][0-9A-Z_a-z]*\z/.

This is not only limited to numbers - S::L has a general deficiency in
constructiong accessor names. It has been argued for several years that
S::L must sanitycheck all resulting accessor names and specify 'accessor
=> undef' for anything that seems out of line. Sadly the former
maintainer was extremely uncooperative on this problem.

So two courses of action:
Long term  _default_column_accessor_name() needs a facelift to stop
asking the system to create accessors with illegal names (patches very
very very welcome).
Short term all you need to do is provide a custom column naming callback
via
https://metacpan.org/module/DBIx::Class::Schema::Loader::Base#col_accessor_map

In the meantime I will have to re-relase a CAG with the ability to
shutoff the check via an envvar. Creating broken accessor names however
will not be the default mode of operation.

Cheers

* The "must" here is a little ambiguous. One of course can always do
$obj->${\"666 random crap of a name"}, but one can never do 'sub
666_haha { ... }'. Since the job of CAG is to make generally accessible
accessors (haha), there is little argument in allowing weird naming.
Especially given the workaround availability as stated above.

On Sun Nov 11 23:54:27 2012, CMCKAY wrote:
> Can you revert the sanity checking so accessors can start with a digit
> again please.

Class::Accessor::Grouped with a builtin escape hatch uploaded to CPAN.
Sorry for the delay. Rationale can be found here[1].

The bug is still valid and critical - DBIC::SL must stop attempting to
create bogus accessors.

[1]
https://metacpan.org/module/RIBASUSHI/Class-Accessor-Grouped-0.10008/lib/Class/Accessor/Grouped.pm#Notes-on-accessor-names

Versions of DBIx::Class::Schema::Loader prior to Version 0.06 always 
included the "size" definition for mysql databases.  Version 0.06+ no 
longer includes "size" definitions for some fields such as integers.  
This breaks any script that depends on the "size" definition.  Could we 
either put the size definition back in or make it a configurable 
option?  This breaks all my validation routines which depend on the 
"size" definition.


Sincerely,

John Maslanik

Hi John,

The "size" means the size of the type when declared in DDL, for example
for VARCHAR(30) the "size" is 30.

INTEGER and such do not have a size for DDL purposes, this is why it is
omitted.

Previous versions of ::Schema::Loader just set size to the size in bytes
are reported by DBI, this was wrong and broke deploying the generated
schemas with $schema->deploy using SQL::Translator.

Current versions of ::Schema::Loader are much more SQL::Translator
friendly and have accurate type information for the purposes of
deployment and cross-deployment.

If you need the old behavior, make a subclass of
::Schema::Loader::DBI::mysql that overrides _columns_info_for and pass
that in as the "loader_class".

On Fri, Jul 23, 2010 at 02:38:10AM -0400, John Maslanik via RT wrote:
> Versions of DBIx::Class::Schema::Loader prior to Version 0.06 always 
> included the "size" definition for mysql databases.  Version 0.06+ no 
> longer includes "size" definitions for some fields such as integers.  
> This breaks any script that depends on the "size" definition.  Could we 
> either put the size definition back in or make it a configurable 
> option?  This breaks all my validation routines which depend on the 
> "size" definition.
> 
> 
> Sincerely,
> 
> John Maslanik
> 
> 

Hi,

is this still an issue for you? The lack of size in bytes for integers
etc. in the generated Result classes.

I see you are asking for some sort of configuration option, so I'm not
closing the ticket yet.

What would this option do? Just set size to size in bytes rather than
DDL size?

Actually, it's not an issue for me anymore because I have switched to 
generating my schema files from the database to generating the database 
from the schema files.  Though I don't know if it may be an issue for 
others.


On 1/21/2011 12:19 PM, Rafael Kitover via RT wrote:
> <URL: https://rt.cpan.org/Ticket/Display.html?id=59640>
>
> Hi,
>
> is this still an issue for you? The lack of size in bytes for integers
> etc. in the generated Result classes.
>
> I see you are asking for some sort of configuration option, so I'm not
> closing the ticket yet.
>
> What would this option do? Just set size to size in bytes rather than
> DDL size?

This was fixed in a gist a while ago, but was never applied. I vaguely recall it was some sort of limiter that was missing on the schema-test, but do not have the exact incantation.


To reproduce:

set DBICTEST_PG_(DSN/USER/PASS)

cpanm --reinstall DBIx::Class::ResultSource::MultipleTableInheritance
cpanm --reinstall DBIx::Class::Schema::Loader

Tested under Win32 strawberryperl 5.14.2.1 on a 64bit Windows 7 machine.

After a call to make_schema_at, the current directory '.' disappears
from @INC.

As an example consider the following method:

sub dump_schema {	
	my $attrs = {
		debug          => 0,
		dump_directory => '.',
		exclude => qr/(?^:\bBIN)/
	};
	
	my $connect_info = [ $dsn, $user, $password ];
	push @$connect_info, { on_connect_do => 'PRAGMA foreign_keys = ON' };
	make_schema_at( 'TDG::Schema', $attrs, $connect_info );

    eval {
        require TDG::Schema;
        TDG::Schema->import();
        1;
    } or do {
        my $error = $@;
        croak $error;
    };

    $dbic_schema = TDG::Schema->connect( $dsn, $user, $password, '' );

}

The error I got when executing it (using a connection to a SQLite
database) was:

Can't locate TDG/Schema.pm in @INC...

Before calling make_schema_at, we have:

  DB<2> x @INC
0  'C:/Dwimperl/perl/site/lib'
1  'C:/Dwimperl/perl/vendor/lib'
2  'C:/Dwimperl/perl/lib'
3  '.'

afterwards:

  DB<3> x @INC
0  'C:/Dwimperl/perl/site/lib'
1  'C:/Dwimperl/perl/vendor/lib'
2  'C:/Dwimperl/perl/lib'

Which explains the error.

We've been using Schema::Loader for years on a large postgresql database. Over the years 
we've had to add manual relationships to the generated schema files because S:L was missing 
them for various reasons (often because they weren't declared in the database itself yet).

Now our schema is more complete and S:L is better at finding relationships and is adding 
them to the generated schema files. However, they're being silently overridden by the manual 
relationships we added previously.  The old manually added relationships are usually 
incomplete and sometimes plain wrong.

The attached patch makes it easy to identify redefined relationships. It carps is a relationship 
is redefined while DBIC_TRACE is set.

Some other env var or control mechanism could be used, I just picked DBIC_TRACE as an easy 
option. (I'd be happy if warnings were the default and were silenced by adding an attribute 
like is_redefintion=>1 to the new relationship definition.)

Committed to topic/rel_redef_warn

On Tue, Jan 10, 2012 at 07:46:36AM -0500, Tim_Bunce via RT wrote:
> Tue Jan 10 07:46:34 2012: Request 73876 was acted upon.
> Transaction: Ticket created by TIMB
>        Queue: DBIx-Class
>      Subject: Optionally warn when a relationship is redefined [PATCH]
>    Broken in: (no value)
>     Severity: Unimportant
>        Owner: Nobody
>   Requestors: [email protected]
>       Status: new
>  Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=73876 >
> 
> 
> We've been using Schema::Loader for years on a large postgresql database. Over the years 
> we've had to add manual relationships to the generated schema files because S:L was missing 
> them for various reasons (often because they weren't declared in the database itself yet).
> 
> Now our schema is more complete and S:L is better at finding relationships and is adding 
> them to the generated schema files. However, they're being silently overridden by the manual 
> relationships we added previously.  The old manually added relationships are usually 
> incomplete and sometimes plain wrong.
> 

Newer versions of S::L are detecting such cases and issue the corresponding warnings.
If your S::L is upgraded, and you are not seeing those - this is a bug in S::L itself.

The other consideration to take is that silent overrides can happen on column accessors,
in adition to relationship accessors. S::L is supposed to detect this as well (also
it should detect static compile-time sub{}s).

As far as the patch is concerned - I am really wary of tying warn-toggle semantics
into DBIC_TRACE itself. I will be back on IRC next Monday, we can discuss options
then.

Cheers

The {extra}{list} enum values are not correct encoded. I use the same connection settings for the app itself and all data from the database are correctly encoded except this enum.


> \dT+
...
 steinhaus_main | enum_tasks_status   | enum_tasks_status   | 4     | offen         +| 
                |                     |                     |       | erledigt      +| 
                |                     |                     |       | zurückgestellt | 
...


$ grep status -C5 Tasks.pm
...
  "status",
  {
    data_type => "enum",
    default_value => "offen",
    extra => {
      custom_type_name => "enum_tasks_status",
      list => ["offen", "erledigt", "zur\xFCckgestellt"],
    },
    is_nullable => 0,
  },
...

the file is in utf8 with use utf8; in the beginning so i expected:

      list => ["offen", "erledigt", "zurückgestellt"],

On 2017-11-21 09:54:01, [email protected] wrote:
> The {extra}{list} enum values are not correct encoded. I use the same
> connection settings for the app itself and all data from the database
> are correctly encoded except this enum.
> 
> 
> > \dT+
> ...
>   steinhaus_main | enum_tasks_status   | enum_tasks_status   | 4     |
> offen         +|
>                  |                     |                     |       |
> erledigt      +|
>                  |                     |                     |       |
> zurückgestellt |
> ...
> 
> 
> $ grep status -C5 Tasks.pm
> ...
>   "status",
>   {
>     data_type => "enum",
>     default_value => "offen",
>     extra => {
>       custom_type_name => "enum_tasks_status",
>       list => ["offen", "erledigt", "zur\xFCckgestellt"],
>     },
>     is_nullable => 0,
>   },
> ...
> 
> the file is in utf8 with use utf8; in the beginning so i expected:
> 
> list => ["offen", "erledigt", "zurückgestellt"],

These representations of the string are equivalent:

    $ perl -Mutf8 -E 'say "zur\xFCckgestellt" eq "zurückgestellt"'
    1

Schema::Loader uses Data::Dump to serialise method call arguments in the generated files, and it encodes all non-ASCII (and non-printable) characters using \x notation.

For aesthetic reasons it might be desirable to output Unicode word characters literally too, but the current output is not incorrect.

- ilmari

Am Di 21. Nov 2017, 06:08:27, ilmari schrieb:
> On 2017-11-21 09:54:01, [email protected] wrote:
> > The {extra}{list} enum values are not correct encoded. I use the same
> > connection settings for the app itself and all data from the database
> > are correctly encoded except this enum.
> >
> >
> > > \dT+
> > ...
> >   steinhaus_main | enum_tasks_status   | enum_tasks_status   | 4
> > |
> > offen         +|
> >                  |                     |                     |
> > |
> > erledigt      +|
> >                  |                     |                     |
> > |
> > zurückgestellt |
> > ...
> >
> >
> > $ grep status -C5 Tasks.pm
> > ...
> >   "status",
> >   {
> >     data_type => "enum",
> >     default_value => "offen",
> >     extra => {
> >       custom_type_name => "enum_tasks_status",
> >       list => ["offen", "erledigt", "zur\xFCckgestellt"],
> >     },
> >     is_nullable => 0,
> >   },
> > ...
> >
> > the file is in utf8 with use utf8; in the beginning so i expected:
> >
> > list => ["offen", "erledigt", "zurückgestellt"],
> 
> These representations of the string are equivalent:
> 
> $ perl -Mutf8 -E 'say "zur\xFCckgestellt" eq "zurückgestellt"'
> 1
> 
> Schema::Loader uses Data::Dump to serialise method call arguments in
> the generated files, and it encodes all non-ASCII (and non-printable)
> characters using \x notation.
> 
> For aesthetic reasons it might be desirable to output Unicode word
> characters literally too, but the current output is not incorrect.
> 
> - ilmari

It is not really the same ...

In the real code i have to make a Encode::decode('ISO-8859-15', $enum) as a quickfix. 

$ cat ticket123698.pl 
use utf8;
use 5.20.0;
use Data::Dumper;
say "zur\xFCckgestellt" eq "zurückgestellt";
print Dumper("zur\xFCckgestellt","zurückgestellt");
$ perl ticket123698.pl 
1
$VAR1 = 'zur�ckgestellt';
$VAR2 = "zur\x{fc}ckgestellt";

"Felix Antonius Wilhelm Ostmann via RT"
<[email protected]> writes:

> It is not really the same ...

The _internal_ representation is not the same; the \x from will be
represented internally as one byte per code point ("downgraded"), while
the literal form will be utf-8-encoded ("upgraded"). Semantically they
are the same, as evidenced by "eq" returning true.

> In the real code i have to make a Encode::decode('ISO-8859-15', $enum) as a quickfix. 

Please show where in the real code you have to do this.  It smells like
something you're passing it to suffering from the Unicode Bug,
i.e. treating the characters in the 128..255 range differently depending
on the internal representation (see
https://metacpan.org/pod/perlunicode#The-%22Unicode-Bug%22 for details).

> $ cat ticket123698.pl 
> use utf8;
> use 5.20.0;
> use Data::Dumper;
> say "zur\xFCckgestellt" eq "zurückgestellt";
> print Dumper("zur\xFCckgestellt","zurückgestellt");
> $ perl ticket123698.pl 
> 1
> $VAR1 = 'zur�ckgestellt';
> $VAR2 = "zur\x{fc}ckgestellt";

The different outputs here are a quirk of how Data::Dumper deals with
downgraded vs. upgraded strings (which could be viewed as an instance of
the Unicode Bug, but doesn't actually affect semantics).  The first one
is showing as � because you haven't thold perl that your terminal
expects UTF-8-encoded strings.  Adding

    use open qw(:std :utf8);

to the script will make it apply a UTF-8 encoding layer to the standard
input/output/error filehandles, so non-ASCII charcters show correctly.

- ilmari
-- 
"I use RMS as a guide in the same way that a boat captain would use
 a lighthouse.  It's good to know where it is, but you generally
 don't want to find yourself in the same spot." - Tollef Fog Heen

Am Di 21. Nov 2017, 07:07:59, [email protected] schrieb:
> "Felix Antonius Wilhelm Ostmann via RT"
> <[email protected]> writes:
> 
> > It is not really the same ...
> 
> The _internal_ representation is not the same; the \x from will be
> represented internally as one byte per code point ("downgraded"),
> while
> the literal form will be utf-8-encoded ("upgraded"). Semantically they
> are the same, as evidenced by "eq" returning true.
> 
> > In the real code i have to make a Encode::decode('ISO-8859-15',
> > $enum) as a quickfix.
> 
> Please show where in the real code you have to do this.  It smells
> like
> something you're passing it to suffering from the Unicode Bug,
> i.e. treating the characters in the 128..255 range differently
> depending
> on the internal representation (see
> https://metacpan.org/pod/perlunicode#The-%22Unicode-Bug%22 for
> details).
> 
> > $ cat ticket123698.pl
> > use utf8;
> > use 5.20.0;
> > use Data::Dumper;
> > say "zur\xFCckgestellt" eq "zurückgestellt";
> > print Dumper("zur\xFCckgestellt","zurückgestellt");
> > $ perl ticket123698.pl
> > 1
> > $VAR1 = 'zur�ckgestellt';
> > $VAR2 = "zur\x{fc}ckgestellt";
> 
> The different outputs here are a quirk of how Data::Dumper deals with
> downgraded vs. upgraded strings (which could be viewed as an instance
> of
> the Unicode Bug, but doesn't actually affect semantics).  The first
> one
> is showing as � because you haven't thold perl that your terminal
> expects UTF-8-encoded strings.  Adding
> 
> use open qw(:std :utf8);
> 
> to the script will make it apply a UTF-8 encoding layer to the
> standard
> input/output/error filehandles, so non-ASCII charcters show correctly.
> 
> - ilmari


OK, here is the real world scenario with pseudo code. I am using DBIx::Class + Catalyst + Template Toolkit

ResultSet:
sub enum_status {
    my ($self) = @_;
    # FIXME see https://rt.cpan.org/Public/Bug/Update.html?id=123698
    return map { Encode::decode("ISO-8859-15", $_) } @{ $self->result_source->column_info('status')->{extra}->{list} };
    return @{ $self->result_source->column_info('status')->{extra}->{list} };
}

Catalyst-Controller:
$c->stash->{status_order} = [ $rs->enum_status ];

Template:
[% FOREACH status IN status_order %]
<a href="[% c.request.uri_with({status => status}) %]">
[% END %]

Without the FIXME the links are ISO-8859-15


After reading your reply and docs about unicode-Bug i changed the code to the following:

__PACKAGE__->column_adds(
...
  {         
    data_type => "enum",
    default_value => "offen",  
    extra => {
      custom_type_name => "enum_tasks_status",
      list => ["offen", "erledigt", "zur\xFCckgestellt"],
    },      
    is_nullable => 0,          
  },
...
);
...
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:W4KhHAXiEW35h5XWiZwhFg
utf8::upgrade($_) for @{ __PACKAGE__->column_info('status')->{extra}->{list} };



But in my option this is kind of a bug. Why are all other strings comming from the database already upgraded but not this?

Schema Loader version 0.07049
Database: postgresql/jammy 14+238
OS: Linux ubuntu-jammy 5.15.0-25-generic
Example script: See below
Table schema:  See below

Issue: repeated warnings that the perhaps I " forgot to set its 'is_auto_increment' attribute during add_columns()?"
But Schema Loader doesn't recognise uuid_generate_v4() as setting the primary key when analysing existing table

How do I get Schema Loader to recognise that there is a default value for the primary key?


Example warning:

DBIx::Class::Storage::DBI::insert(): Missing value for primary key column 'id' on POSTGRES_TABLE - perhaps you forgot to set its 'is_auto_increment' attribute during add_columns()? Treating 'id' implicitly as an autoinc, and attempting value retrieval at PERL_CODE_LINE


Postgres Schema for Table "Invoice"

                               Table "public.invoice"
  Column  |            Type             | Collation | Nullable |      Default
----------+-----------------------------+-----------+----------+--------------------
 id       | uuid                        |           | not null | uuid_generate_v4()
 created  | timestamp without time zone |           | not null | now()
 modified | timestamp without time zone |           | not null | now()
 amount   | money                       |           | not null |
 currency | character varying(3)        |           | not null |
 is_paid  | boolean                     |           | not null | false
 user_id  | integer                     |           | not null |
Indexes:
    "invoice_pkey" PRIMARY KEY, btree (id)
    "invoice_unique_id" UNIQUE CONSTRAINT, btree (id)





Calling script (functional part).

use DBIx::Class::Schema::Loader qw/ make_schema_at /;
make_schema_at(
    'Series::Schema',
    {   components     => ["InflateColumn::DateTime"],
        debug          => 0,
        dump_directory => './lib',
        moniker_map    => \&moniker,

    },
    [ $sql_db->{db_dsn}, $sql_db->{db_username}, $sql_db->{db_password}, {} ],
);

sub moniker {
    my $table = shift;
    my $result;
    if ( $table eq 'user_table' ) {
        $result = 'User';
    }
    elsif ( $table eq 'group_table' ) {
        $result = 'Group';
    }
    else {
        $result = join q{}, map {ucfirst} split /_/, $table;
    }
    return $result;
} ## end sub moniker

---------- Forwarded message ----------
From: Caleb Cushing <[email protected]>
Date: Wed, Jun 8, 2011 at 3:02 PM
Subject: dbicdump and podweaver
To: [email protected]


so I'm personally for a weaver dbic plugin an example of an addon that
one could put in weaver.ini then you add the SeeAlso to your
weaver.ini and the section will automatically be added to all the pod
in your dzil distribution. This seems to me like it'd be the right way
to add the ACCESSORS section. In which case then dbicdump wouldn't
need anything, just tell it not to generate pod, and let people do it
with weaver entirely.

http://search.cpan.org/~apocal/Pod-Weaver-Section-SeeAlso-1.002/lib/Pod/Weaver/Section/SeeAlso.pm

[14:22] <Caelum> ccushing: what doesn't podweaver like about my POD?
[14:24] *** GumbyNET5 gives channel operator privileges to Bender.
[14:25] <ccushing> Caelum: oh, I'm not sure it's entirely all
podweaver but normally when you use dzil you don't have a NAME
section, you have a # ABSTRACT and it builds out NAME and a whole
bunch of pod stuff
[14:26] <ccushing> actually if the pod was below the md5sum I could
just modify it
[14:29] <Caelum> ccushing: I'll look into adding an option to generate
podweaver-compatible POD
[14:31] <ccushing> Caelum: actually maye just need an option not to
include NAME  I think that's the only thing that needs to be
removed...
[14:31] <Caelum> ccushing: which dzil pluginbundle do you use?
[14:32] <ccushing> usually @basic
[14:33] <Caelum> ccushing: what does NAME need to be replaced by?
[14:34] <Caelum> ccushing: ABSTRACT?
[14:34] <purl> i guess ABSTRACT is the landlord of xaven, apart of the
infobot development team at http://infobot.org or
mailto:[email protected]
[14:34] <ccushing> # ABSTRACT: description
[14:35] <ccushing> Caelum: ya
[14:35] <Caelum> ccushing: ok, thanks
[14:35] <ccushing> I personally usually put my pod at the end of my
files so that the dzil build doesn't change the code lines
[14:35] <ccushing> because podweaver will otherwise throw all the pod
like below # ABSTRACT:
[14:36] <Caelum> ccushing: I'll have the podweaver mode do that then
[14:36] <ccushing> right, not sure how that affects the md5sum stuff though
[14:37] <ccushing> course for all it matters to me personally... you
could throw pod below that...
[14:37] <Caelum> ccushing: but then how do you update it
[14:38] <ccushing> yeah there's the problem... because usually you
need to put code below that md5 some so no matter what you end up with
code changing spots with pod in the end
[14:38] <ccushing> idk
[14:38] <ccushing> it's a no win situation
[14:38] <ccushing> some people won't care where the pod ends up
[14:39] <Caelum> ccushing: what if s::l would run podweaver itself?
[14:39] <Caelum> ccushing: is there a marker for podweaver to say
"already ran" or something
[14:39] <ccushing> hmm
[14:39] <ccushing> idk
[14:39] <ccushing> better question for rjbs... though maybe you could
write a module that generates all this documentation later
[14:40] <ccushing> and has a marker
[14:40] <Caelum> ccushing: ok, could I ask you to open an RT for me
please? You can just paste the irc log if you want
[14:40] <ccushing> like # DBIC_ACCESSORS
[14:40] <ccushing> and then people can add that to weaver.ini
[14:40] <ccushing> and just exclude pod, include that, and you've got
it in the final build
[14:41] <ccushing> yeah
[14:42] <Caelum> ccushing: we could also exclude POD from the md5sum
[14:43] <Caelum> ccushing: that's pretty easy to do
[14:43] <ccushing> Caelum: well the more I think about it... just
having it generated by podweaver later... without anyting added to the
files would be a great idea

--
Caleb Cushing

http://xenoterracide.com



-- 
Caleb Cushing

http://xenoterracide.com

further reflection says, if you create a weaver plugin...

for dbicdump just have it add the following for a dzil option

# ABSTRACT: class::path

below the md5sum, doing it below makes it easily changed to an actual
description if someone wants. Also it shouldn't really need auto
updating after initial generation.

-- 
Caleb Cushing

http://xenoterracide.com

Hi!

Since the last update, DBIx::Class::Schema::Loader automatically creates `view_definition` for views. In case of MySQL it uses `SELECT view_definition FROM information_schema.views ...` query and the result of this query contains the database name along with every table name: `project`.`user` instead of just `user`.

It's a big issue for our project since different users and environments use different database names. It would be great to have view definition without database name or at least disable view_definition generation at all.

Thank you!

Hi Vadim,

"Pushtaev Vadim via RT" <[email protected]>
writes:

> Hi!
>
> Since the last update, DBIx::Class::Schema::Loader automatically
> creates `view_definition` for views. In case of MySQL it uses `SELECT
> view_definition FROM information_schema.views ...` query and the
> result of this query contains the database name along with every table
> name: `project`.`user` instead of just `user`.

Schema::Loader does deliberately not do anything with the view
definition SQL, since that would require a fully-fledged SQL parser.
This is incidentally why it does not introspect the view definition for
DB2, since that only gives you the whole CREATE VIEW statement, not the
body separately.

> It's a big issue for our project since different users and
> environments use different database names. It would be great to have
> view definition without database name or at least disable
> view_definition generation at all.

For non-virtual views, the view definition is only used for ->deploy.
The fact that you're happy with not having it generated at all makes it
seem to me that you're not using deploy, so the definition is purely
informative.

If you really need it, you can add an sqlt_deploy_hook method to the
result class that modifies $view->sql as appropriate for your specific
case.

Hope this helps,

Ilmari

-- 
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law

We do use `deploy` indeed. Previously we just added view_definition manually, but we would be happy to use auto-generated one (we have already monkey-patched Schema::Loader not to generate view_definiton as a workaround).

I understand you don't want to parse SQL, but there is a way to get view definition from MySQL without database name: https://dev.mysql.com/doc/refman/5.7/en/show-create-view.html

On Thu Jan 26 13:27:15 2017, [email protected] wrote:
> Hi Vadim,
> 
> "Pushtaev Vadim via RT" <[email protected]>
> writes:
> 
> > Hi!
> >
> > Since the last update, DBIx::Class::Schema::Loader automatically
> > creates `view_definition` for views. In case of MySQL it uses `SELECT
> > view_definition FROM information_schema.views ...` query and the
> > result of this query contains the database name along with every table
> > name: `project`.`user` instead of just `user`.
> 
> Schema::Loader does deliberately not do anything with the view
> definition SQL, since that would require a fully-fledged SQL parser.
> This is incidentally why it does not introspect the view definition for
> DB2, since that only gives you the whole CREATE VIEW statement, not the
> body separately.
> 
> > It's a big issue for our project since different users and
> > environments use different database names. It would be great to have
> > view definition without database name or at least disable
> > view_definition generation at all.
> 
> For non-virtual views, the view definition is only used for ->deploy.
> The fact that you're happy with not having it generated at all makes it
> seem to me that you're not using deploy, so the definition is purely
> informative.
> 
> If you really need it, you can add an sqlt_deploy_hook method to the
> result class that modifies $view->sql as appropriate for your specific
> case.
> 
> Hope this helps,
> 
> Ilmari
> 

"Pushtaev Vadim via RT" <[email protected]>
writes:

> We do use `deploy` indeed. Previously we just added view_definition
> manually, but we would be happy to use auto-generated one (we have
> already monkey-patched Schema::Loader not to generate view_definiton
> as a workaround).


> I understand you don't want to parse SQL, but there is a way to get
> view definition from MySQL without database name:
> https://dev.mysql.com/doc/refman/5.7/en/show-create-view.html

That has the same problem as DB2: It includes the whole CREATE VIEW
statement, not just the query.  Also, whether it includes the database
name (which is really the schema, in standard SQL parlance) depends on
whether your current schema matches the one of the (tables used) in the
view.

mysql> \r test
mysql> show create view test.bar \G
*************************** 1. row ***************************
                View: bar
         Create View: CREATE ALGORITHM=UNDEFINED
                      DEFINER=`ilmari`@`%` SQL SECURITY DEFINER VIEW
                      `bar` AS select `foo`.`id` AS `id` from `foo`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> \r dbictest
mysql> show create view test.bar \G
*************************** 1. row ***************************
                View: bar
         Create View: CREATE ALGORITHM=UNDEFINED
                      DEFINER=`ilmari`@`%` SQL SECURITY DEFINER VIEW
                      `test`.`bar` AS select `test`.`foo`.`id` AS
                      `id` from `test`.`foo`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

> On Thu Jan 26 13:27:15 2017, [email protected] wrote:
>> Hi Vadim,
>> 
>> "Pushtaev Vadim via RT" <[email protected]>
>> writes:
>> 
>> > Hi!
>> >
>> > Since the last update, DBIx::Class::Schema::Loader automatically
>> > creates `view_definition` for views. In case of MySQL it uses `SELECT
>> > view_definition FROM information_schema.views ...` query and the
>> > result of this query contains the database name along with every table
>> > name: `project`.`user` instead of just `user`.
>> 
>> Schema::Loader does deliberately not do anything with the view
>> definition SQL, since that would require a fully-fledged SQL parser.
>> This is incidentally why it does not introspect the view definition for
>> DB2, since that only gives you the whole CREATE VIEW statement, not the
>> body separately.
>> 
>> > It's a big issue for our project since different users and
>> > environments use different database names. It would be great to have
>> > view definition without database name or at least disable
>> > view_definition generation at all.
>> 
>> For non-virtual views, the view definition is only used for ->deploy.
>> The fact that you're happy with not having it generated at all makes it
>> seem to me that you're not using deploy, so the definition is purely
>> informative.
>> 
>> If you really need it, you can add an sqlt_deploy_hook method to the
>> result class that modifies $view->sql as appropriate for your specific
>> case.
>> 
>> Hope this helps,
>> 
>> Ilmari
>> 
>
>
>
>

-- 
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law

I have written a patch to support the postgres inheritance feature based
on
http://dev.catalystframework.org/repos/bast/branches/DBIx-Class-Schema-Loader/current
(Revision: 4794)

The additional option 'respect_inheritance' creates schema classes that
use the same inheritance/parents as in postgres. With
'inheritance_schema_map' it is even possible to create correctly
inherited tables across multiple schema.

I would really appreciate it to see this patch in a future release.

As per your request filing it here:

The idea is to make a machine-parseable comment (Data::Dumper would work
just fine) at the top of the schema, which is part of the md5
(disallowing direct user manipulation). Then once you have a schema,
when you redump all the defaults you did not supply in the call (or on
the cli) are taken from parsing back this comment. So in essence editing
the options is facilitated by a re-dump with new options which S::L
respects.

DBIx::Class::Loader 0.21 is not compatible with Apache running in
worker(threaded) mode on Linux, at least not on my tested environment,
which was:
DBIx::Class::Schema::Loader 0.04005
DBIx::Class 0.08010 and 0.08099_04
DBI 1.601 (and a slightly earlier)
DBD::Pg 2.8.6, 2.9.0 and another fairly older one.

Note that DBIx::Class::Schema works in the same configuration when used
without -Loader.

I attach a fairly complete demonstration app. See README for
setup/installation instructions.


Also, I note that this website seems to mention the problem and a
potential couple of fixes: http://d.hatena.ne.jp/holidays-l/20070126/p1
Unfortunately I can't read Japanese, but the code itself looks OK.

Note that I've just tested against the current latest dev release, and
the problem remains.

On Wed Aug 20 02:35:50 2008, TJC wrote:
> DBIx::Class::Loader 0.21 is not compatible with Apache running in
> worker(threaded) mode on Linux, at least not on my tested environment,
> which was:
> DBIx::Class::Schema::Loader 0.04005
> DBIx::Class 0.08010 and 0.08099_04
> DBI 1.601 (and a slightly earlier)
> DBD::Pg 2.8.6, 2.9.0 and another fairly older one.
> 
> Note that DBIx::Class::Schema works in the same configuration when used
> without -Loader.
> 
> I attach a fairly complete demonstration app. See README for
> setup/installation instructions.
> 
> 
> Also, I note that this website seems to mention the problem and a
> potential couple of fixes: http://d.hatena.ne.jp/holidays-l/20070126/p1
> Unfortunately I can't read Japanese, but the code itself looks OK.

Is this still a problem?

On Thu Dec 02 07:38:28 2010, RIBASUSHI wrote:
> On Wed Aug 20 02:35:50 2008, TJC wrote:
> > DBIx::Class::Loader 0.21 is not compatible with Apache running in
> > worker(threaded) mode on Linux, at least not on my tested environment,
> > which was:
> > DBIx::Class::Schema::Loader 0.04005
> > DBIx::Class 0.08010 and 0.08099_04
> > DBI 1.601 (and a slightly earlier)
> > DBD::Pg 2.8.6, 2.9.0 and another fairly older one.
> > 
> > Note that DBIx::Class::Schema works in the same configuration when used
> > without -Loader.
> > 
> > I attach a fairly complete demonstration app. See README for
> > setup/installation instructions.
> > 
> > 
> > Also, I note that this website seems to mention the problem and a
> > potential couple of fixes: http://d.hatena.ne.jp/holidays-l/20070126/p1
> > Unfortunately I can't read Japanese, but the code itself looks OK.
> 
> Is this still a problem?

Took me a while to build a new VM and set it up with everything, since I don't run 
Apache any more, especially not the threaded version.

However - yes, I can confirm the problem still occurs on Ubuntu Maverick, with its 
versions of Apache (2.2), Perl (5.10.1), mod_perl, mpm_worker, and all the latest 
version of Perl modules installed from CPAN.


http://192.168.1.204/testdbic/loader

Caught exception in MyApp::Controller::Root->loader "DBD::Pg::db STORE failed: 
handle 2 is owned by thread 7f0ffc427bf0 not current thread 7f1002cd92c0 (handles 
can't be shared between threads and your driver may need a CLONE method added) at 
/usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.pm line 217."

BTW I opened another ticket (63558) about some warnings in the unit tests 
from DBIC about DCSL holding cached references when it shouldn't be..
I just wondered if maybe that's related to this bug, since if DCSL was 
closing the handles properly then the new threads would all re-open their 
own handles just fine.

Or maybe it's unrelated.

This table cannot be round tripped via the 

CREATE TABLE GOOD (
  sometext text NOT NULL,
  UNIQUE KEY sometext2 ( sometext(150))
) 

dbicdump -o dump_directory=./lib  -o
components='["InflateColumn::DateTime"]' DFL::Schema::TEST
"dbi:mysql:database=$1:host=dbslave2" UID PWD '{ overwrite_modifications
=> 1, quote_char => "`" }'

Produces :

__PACKAGE__->add_unique_constraint("sometext2", [ "sometext"]);

This produces bad sql when deployed:


my $schema = Test::Schema::TEST->connect("dbi:mysql:dbname=${db}",
$user, $pass);
    $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
			    '0.1',
			    './sql/'
	);

    $schema->deploy({ add_drop_table => 1});

############

CREATE TABLE BAD (
  sometext text NOT NULL,
  UNIQUE KEY sometext2 ( sometext)
) 

Adding dependency on the sqlt support ticket - without having an sqlt
infrastructure to consume index lengths, there's no point of adding this
to dbic.

I have a table called "device_criteria". Even with naming => { ALL => 'v8', force_ascii => 1 } in my config file, I end up with a source called DeviceCriteria, not DeviceCriterion.

this looks to be the root problem:

perl -MLingua::EN::Inflect::Phrase -wle'print Lingua::EN::Inflect::Phrase::to_S("device_criteria");'
-> device_criteria

I guess some splitting into words is required first?

"Karen Etheridge via RT" <[email protected]>
writes:

> I have a table called "device_criteria". Even with naming => { ALL => 'v8', force_ascii => 1 }
> in my config file, I end up with a source called DeviceCriteria, not DeviceCriterion.
>
> this looks to be the root problem:
>
> perl -MLingua::EN::Inflect::Phrase -wle'print Lingua::EN::Inflect::Phrase::to_S("device_criteria");'
> -> device_criteria
>
> I guess some splitting into words is required first?

That wouldn't help, Lingua::EN::Inflect::Phrase doesn't handle "criteria" on its own either.

$ perl -MLingua::EN::Inflect::Phrase -wle'print Lingua::EN::Inflect::Phrase::to_S("criteria");'
criteria

The underlying problem seems to be that Lingua::EN::Inflect::Number
thinks 'criteria' is already singular:

$ perl -MLingua::EN::Inflect::Number=number -E 'say number "criteria"'
s

I think this all stems from the fact hat Lingua::EN::Inflect::Number
abuses Lingua::EN::Inflect::PL() to convert to _singular_, which is
documented as "undefined (and unlikely to be correct)", the comment in
to_S() even says "I don't know why this works, but it seems to."

Lingua::EN::Inflect does know to pluralise "criterion" to "criteria",
but the usual (accidental?) behaviour of PL() singularising already-plural
words doesn't seem to apply to it (or other "ion" -> "a" words like "aphelion").

Lingua::EN::Inflect::Phrase does have a bunch of special cases, I guess this
could be added there.

-ilmari

Is this being rejected because (seemingly) Lingua::* doesn't know how to handle this word?

On 2018-07-26 03:16:47, ilmari wrote:

> > I guess some splitting into words is required first?
> 
> That wouldn't help, Lingua::EN::Inflect::Phrase doesn't handle
> "criteria" on its own either.

This works (and indeed is what I'm doing elsewhere in the codebase:

    use Lingua::EN::Inflexion 'noun';
    my $table_name = 'device_criteria';
    my @words = split('_', $table_name);
    $words[-1] = noun($words[-1])->singular;
    my $source = camelize(join('_', @words));

perl -MLingua::EN::Inflexion=noun -wle'print noun("criteria")->singular'
--> criterion

Currently, Result classes generated by Schema::Loader use the normal Perl MRO. This creates problems when usinc a custom base class that uses multiple components, see for example https://github.com/frioux/DBIx-Class-Helpers/issues/91 and the discussion at https://github.com/frioux/DBIx-Class-Helpers/pull/103

Adding an explicit C<use mro 'c3';> after the C<use base> is enough to fix the problem. I'm not completely sure how C3 works in Moose classes, though.

MSSQL 'timestamp' columns are not actually timestamps, they are 8-byte blobs which store an incrementing version value whenever the row is changed, but do not actually store any date/time information whatsoever (in fact, in later versions of MSSQL they have started calling these 'RowVersion').

Currently S::L reads these as data_type => 'timestamp' but ribasushi suggested that it might be better for these to be treated as blobs since this is closer to what they actually are. He asked me to open this ticket and paste the below IRC chat log...


** scrollback from IRC **

<--snip-->
<ribasushi> vanstyn: are you certain it is a blob and it's not DBI/S::L doing switching on you? 
<vanstyn> well, navicat shows it as a BLOB ...
<vanstyn> but... that might just be how it is exposed... yeah, this seems very much like what it is... thanks!
<ribasushi> vanstyn: I'd run S::L against it to make sure - it doesn't seem to have a special handling to do timestamp => blob (by a cursory browse of source)
<ribasushi> vanstyn: but if it *is* the timestamp thing - it is rather very common, especially among .net/asp software
<vanstyn> yeah, I did run it through S::L, and it shows data_type => 'timestamp'
<ribasushi> then that's what it is ;)
<ribasushi> navicat is DWIMing for you (because it is effectively a blob)
<ribasushi> in fact...
<vanstyn> and it also set inflate_datetime => 0, which makes me think S::L knows this
* robsco ([email protected]) has joined #dbix-class
<vanstyn> so, yeah, mystery solved...
<ribasushi> ilmari: ^^ perhaps S::L should s/timestamp/blob/ and shove an extra metadatakey of "was such and such datatype"
<ribasushi> ilmari: so that this confusion doesn't happen again
<ribasushi> ( for MSSQL obv. )
* stelf|pc ([email protected]) has joined #dbix-class
* stelf|pc_ has quit (Ping timeout: 360 seconds)
<ribasushi> vanstyn: and yes, Caelum did consider it, but added support in an ass-backwards way: https://github.com/dbsrgits/dbix-class-schema-loader/commit/81ade4d9da9b861849c51894b7e8f380e74192d4#diff-c3693ab39251ec52efb7839c3c0ca409R122
<vanstyn> ribasushi: thanks again!
<ribasushi> vanstyn: can you open a ticket against S::L with the conversation above so it doesn't get lost?