No subject
Sun Nov 20 20:48:46 GMT 2022
```
"Pushtaev Vadim via RT" <bug-DBIx-Class-Schema-Loader at rt.cpan.org>
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, ilmari at ilmari.org wrote:
>> Hi Vadim,
>>
>> "Pushtaev Vadim via RT" <bug-DBIx-Class-Schema-Loader at rt.cpan.org>
>> 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
```
--
Reply to this email directly or view it on GitHub:
https://github.com/dbsrgits/dbix-class-schema-loader/issues/51
You are receiving this because you are subscribed to this thread.
Message ID: <dbsrgits/dbix-class-schema-loader/issues/51 at github.com>
----==_mimepart_637a937d8d2f5_418bc6701972977
Content-Type: text/html;
charset=UTF-8
Content-Transfer-Encoding: 7bit
<p></p>
<p dir="auto">Migrated from <a href="https://rt.cpan.org/Ticket/Display.html?id=119996" rel="nofollow">rt.cpan.org#119996</a> (status was 'open')</p>
<p dir="auto">Requestors:</p>
<ul dir="auto">
<li><a href="mailto:pushtaev at cpan.org">pushtaev at cpan.org</a></li>
</ul>
<p dir="auto">From <a href="mailto:pushtaev at cpan.org">pushtaev at cpan.org</a> on 2017-01-26 13:26:44<br>
:</p>
<pre class="notranslate"><code class="notranslate">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!
</code></pre>
<p dir="auto">From <a href="mailto:ilmari at ilmari.org">ilmari at ilmari.org</a> on 2017-01-26 18:27:15<br>
:</p>
<pre class="notranslate"><code class="notranslate">Hi Vadim,
"Pushtaev Vadim via RT" <bug-DBIx-Class-Schema-Loader at rt.cpan.org>
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
</code></pre>
<p dir="auto">From <a href="mailto:pushtaev at cpan.org">pushtaev at cpan.org</a> on 2017-01-26 20:06:48<br>
:</p>
<pre class="notranslate"><code class="notranslate">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, ilmari at ilmari.org wrote:
> Hi Vadim,
>
> "Pushtaev Vadim via RT" <bug-DBIx-Class-Schema-Loader at rt.cpan.org>
> 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
>
</code></pre>
<p dir="auto">From <a href="mailto:ilmari at ilmari.org">ilmari at ilmari.org</a> on 2017-01-27 12:28:53<br>
:</p>
<pre class="notranslate"><code class="notranslate">"Pushtaev Vadim via RT" <bug-DBIx-Class-Schema-Loader at rt.cpan.org>
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, ilmari at ilmari.org wrote:
>> Hi Vadim,
>>
>> "Pushtaev Vadim via RT" <bug-DBIx-Class-Schema-Loader at rt.cpan.org>
>> 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
</code></pre>
<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />Reply to this email directly, <a href="https://github.com/dbsrgits/dbix-class-schema-loader/issues/51">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AACJ4AVB6G2PRVOL5UU7FSTWJKFP3ANCNFSM6AAAAAASGAWF4U">unsubscribe</a>.<br />You are receiving this because you are subscribed to this thread.<img src="https://github.com/notifications/beacon/AACJ4AXWCBYIJCMFV4ISF6DWJKFP3A5CNFSM6AAAAAASGAWF4WWGG33NNVSW45C7OR4XAZNFJFZXG5LFVJRW63LNMVXHIX3JMTHFNWFSYY.gif" height="1" width="1" alt="" /><span style="color: transparent; font-size: 0; display: none; visibility: hidden; overflow: hidden; opacity: 0; width: 0; height: 0; max-width: 0; max-height: 0; mso-hide: all">Message ID: <span><dbsrgits/dbix-class-schema-loader/issues/51</span><span>@</span><span>github</span><span>.</span><span>com></span></span></p>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/dbsrgits/dbix-class-schema-loader/issues/51",
"url": "https://github.com/dbsrgits/dbix-class-schema-loader/issues/51",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>
----==_mimepart_637a937d8d2f5_418bc6701972977--
More information about the DBIx-Class-Devel
mailing list