You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@isis.apache.org by Smitha N <sm...@gmail.com> on 2018/02/14 15:26:49 UTC
Using database Views
Hi,
In our application, there is a need to use database views and I was
wondering what is the best way to implement it using the framework.
How do we map the database view fields to the Object? Can we use ViewModels
with persistanceCapability and refer to the view?
If above scenario is valid, how do we make such scenarios work for
integration testing?
If not please advise on how to use database views.
Regards
Smitha
Re: Using database Views
Posted by Stephen Cameron <st...@gmail.com>.
The SQL is not liked by HSQLDB, works fine with MySQL as you say, what
happens if you just remove 'AS'?
I've not used mine against HSQLDB .
On Thu, Feb 15, 2018 at 10:49 PM, Smitha N <sm...@gmail.com> wrote:
> Hello Steve,
>
> Thanks for the input. I tried creating viewModel in similar fashion.
>
> But I face issues when I try to run the integration tests for this class.
>
> I get issues creating the view with the below mentioned errors:
>
>
> Error thrown executing CREATE VIEW `usersforportal` AS SELECT `users`.id AS
> id,`users`.helenaam AS helenaam,`users`.email AS email FROM `users` WHERE
> `users`.actief = 1 AND `users`.bedrijf_id = 1 AND `users`.email is not null
> : unexpected token: required: AS
> java.sql.SQLSyntaxErrorException: unexpected token: required: AS
> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
> at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
> at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
>
>
> When I try the same syntax on MYSQL workbench , it works fine and there are
> no errors.
>
> Somehow these issues occur while running the integration tests with
> in-memory database.
>
> Any help is much appreciated.
>
> Regards
> Smitha
>
> On Wed, Feb 14, 2018 at 10:17 PM, Stephen Cameron <
> steve.cameron.62@gmail.com> wrote:
>
> > Hi,
> >
> > Using a DataNucleus capability rather than an Apache Isis one.
> >
> > http://www.datanucleus.org/products/accessplatform_3_1/rdbms/views.html
> >
> > You can use annotations in your class instead of a jdo config file, I
> > copied examples from Estatio to get started.
> >
> > But here is one I've created:
> > https://github.com/Stephen-Cameron-Data-Services/isis-
> > chats/blob/master/reports/src/main/java/au/com/scds/chats/
> dom/report/view/
> > ActivityAttendanceSummary.java
> >
> > The class can be seen by making your view class a ViewModel.
> >
> > I don't think you can use persistable views, may be wrong on that.
> >
> > I've had a problem with views, that DN tries to create them before the
> > underlying tables are created, never resolved that but I should.
> >
> > If I were to start afresh I'd not use them unless absolutely necessary
> for
> > performance, instead use queries on my domain objects and write code,
> then
> > you have easier refactoring.
> >
> > Steve
> >
> >
> >
> > On Thu, Feb 15, 2018 at 2:26 AM, Smitha N <sm...@gmail.com>
> wrote:
> >
> > > Hi,
> > >
> > > In our application, there is a need to use database views and I was
> > > wondering what is the best way to implement it using the framework.
> > >
> > > How do we map the database view fields to the Object? Can we use
> > ViewModels
> > > with persistanceCapability and refer to the view?
> > >
> > > If above scenario is valid, how do we make such scenarios work for
> > > integration testing?
> > > If not please advise on how to use database views.
> > >
> > > Regards
> > > Smitha
> > >
> >
>
Re: Using database Views
Posted by Smitha N <sm...@gmail.com>.
Hello Steve,
Thanks for the input. I tried creating viewModel in similar fashion.
But I face issues when I try to run the integration tests for this class.
I get issues creating the view with the below mentioned errors:
Error thrown executing CREATE VIEW `usersforportal` AS SELECT `users`.id AS
id,`users`.helenaam AS helenaam,`users`.email AS email FROM `users` WHERE
`users`.actief = 1 AND `users`.bedrijf_id = 1 AND `users`.email is not null
: unexpected token: required: AS
java.sql.SQLSyntaxErrorException: unexpected token: required: AS
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
When I try the same syntax on MYSQL workbench , it works fine and there are
no errors.
Somehow these issues occur while running the integration tests with
in-memory database.
Any help is much appreciated.
Regards
Smitha
On Wed, Feb 14, 2018 at 10:17 PM, Stephen Cameron <
steve.cameron.62@gmail.com> wrote:
> Hi,
>
> Using a DataNucleus capability rather than an Apache Isis one.
>
> http://www.datanucleus.org/products/accessplatform_3_1/rdbms/views.html
>
> You can use annotations in your class instead of a jdo config file, I
> copied examples from Estatio to get started.
>
> But here is one I've created:
> https://github.com/Stephen-Cameron-Data-Services/isis-
> chats/blob/master/reports/src/main/java/au/com/scds/chats/dom/report/view/
> ActivityAttendanceSummary.java
>
> The class can be seen by making your view class a ViewModel.
>
> I don't think you can use persistable views, may be wrong on that.
>
> I've had a problem with views, that DN tries to create them before the
> underlying tables are created, never resolved that but I should.
>
> If I were to start afresh I'd not use them unless absolutely necessary for
> performance, instead use queries on my domain objects and write code, then
> you have easier refactoring.
>
> Steve
>
>
>
> On Thu, Feb 15, 2018 at 2:26 AM, Smitha N <sm...@gmail.com> wrote:
>
> > Hi,
> >
> > In our application, there is a need to use database views and I was
> > wondering what is the best way to implement it using the framework.
> >
> > How do we map the database view fields to the Object? Can we use
> ViewModels
> > with persistanceCapability and refer to the view?
> >
> > If above scenario is valid, how do we make such scenarios work for
> > integration testing?
> > If not please advise on how to use database views.
> >
> > Regards
> > Smitha
> >
>
Re: Using database Views
Posted by Stephen Cameron <st...@gmail.com>.
Hi,
Using a DataNucleus capability rather than an Apache Isis one.
http://www.datanucleus.org/products/accessplatform_3_1/rdbms/views.html
You can use annotations in your class instead of a jdo config file, I
copied examples from Estatio to get started.
But here is one I've created:
https://github.com/Stephen-Cameron-Data-Services/isis-chats/blob/master/reports/src/main/java/au/com/scds/chats/dom/report/view/ActivityAttendanceSummary.java
The class can be seen by making your view class a ViewModel.
I don't think you can use persistable views, may be wrong on that.
I've had a problem with views, that DN tries to create them before the
underlying tables are created, never resolved that but I should.
If I were to start afresh I'd not use them unless absolutely necessary for
performance, instead use queries on my domain objects and write code, then
you have easier refactoring.
Steve
On Thu, Feb 15, 2018 at 2:26 AM, Smitha N <sm...@gmail.com> wrote:
> Hi,
>
> In our application, there is a need to use database views and I was
> wondering what is the best way to implement it using the framework.
>
> How do we map the database view fields to the Object? Can we use ViewModels
> with persistanceCapability and refer to the view?
>
> If above scenario is valid, how do we make such scenarios work for
> integration testing?
> If not please advise on how to use database views.
>
> Regards
> Smitha
>