SQL Views Dependency Issue

Dear Devs,

We have two SQLViews created in DHIS2 v2.24 where one view depends on other one(Say, A depends on B). We have also scheduled an automatic maintenance event every day at 12.00 a.m. Problem is DHIS2 tries to drop view B first during this automated procedure and as a result whole procedure fails including analytics table generation.

Is there a way to make DHIS2 scheduled event drop A before B in above case.

Thanks & Regards,

Chathura Widanage

Dear Chathura,

Why do u wanna drop the views at the first place?

Sampath

···

On Tue, Oct 4, 2016 at 2:40 PM, chathura widanage chathurawidanage@gmail.com wrote:

Dear Devs,

We have two SQLViews created in DHIS2 v2.24 where one view depends on other one(Say, A depends on B). We have also scheduled an automatic maintenance event every day at 12.00 a.m. Problem is DHIS2 tries to drop view B first during this automated procedure and as a result whole procedure fails including analytics table generation.

Is there a way to make DHIS2 scheduled event drop A before B in above case.

Thanks & Regards,

Chathura Widanage


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

A negative thinker see a difficulty in every opportunity…
A positive thinker see an opportunity in every difficulty…

He is able who thinks he is able. ~ Buddha

Hi Chathura,

currently the approach to achieve this is to rely on the alphabetical ordering of SQL view names. For the scheduled tasks, the system first drops the SQL views in reversed alphabetical order. Finally, the system regenerates the SQL views in alphabetical order.

So in your case where A depends on B, you should ensure that that the name of SQL view B comes before the name of SQL view A alphabetically. This way, the system will first drop A, then B, and when it is time to regenerate the views, first generate B, then A, and you should be good.

best regards,

Lars

···

On Tue, Oct 4, 2016 at 11:10 AM, chathura widanage chathurawidanage@gmail.com wrote:

Dear Devs,

We have two SQLViews created in DHIS2 v2.24 where one view depends on other one(Say, A depends on B). We have also scheduled an automatic maintenance event every day at 12.00 a.m. Problem is DHIS2 tries to drop view B first during this automated procedure and as a result whole procedure fails including analytics table generation.

Is there a way to make DHIS2 scheduled event drop A before B in above case.

Thanks & Regards,

Chathura Widanage


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

Hi Lars,

We have two sets of SQL Views,

···

On Tue, Oct 4, 2016 at 3:41 PM, Lars Helge Øverland lars@dhis2.org wrote:

Hi Chathura,

currently the approach to achieve this is to rely on the alphabetical ordering of SQL view names. For the scheduled tasks, the system first drops the SQL views in reversed alphabetical order. Finally, the system regenerates the SQL views in alphabetical order.

So in your case where A depends on B, you should ensure that that the name of SQL view B comes before the name of SQL view A alphabetically. This way, the system will first drop A, then B, and when it is time to regenerate the views, first generate B, then A, and you should be good.

best regards,

Lars

On Tue, Oct 4, 2016 at 11:10 AM, chathura widanage chathurawidanage@gmail.com wrote:

Dear Devs,

We have two SQLViews created in DHIS2 v2.24 where one view depends on other one(Say, A depends on B). We have also scheduled an automatic maintenance event every day at 12.00 a.m. Problem is DHIS2 tries to drop view B first during this automated procedure and as a result whole procedure fails including analytics table generation.

Is there a way to make DHIS2 scheduled event drop A before B in above case.

Thanks & Regards,

Chathura Widanage


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

Best Regards,

Chathura Widanage,

Undergraduate,

Department of Computer Science and Engineering,

University of Moratuwa,

Sri Lanka

Hi,

Any update on this?

Thanks

···

On Thu, Oct 6, 2016 at 11:27 AM, chathura widanage chathurawidanage@gmail.com wrote:

Hi Lars,

We have two sets of SQL Views,


dnms nutrition last event (A)
dnms nutrition psi pi map(B)

Here A depends on B

dnms risk last event (C)
dnms risk psi pi map (D)

Here C depends on D


Problem is, from above two sets just the first set fails during maintenance procedure. Following your directions, I tried changing the names of the views to make B and D come Alphabetically before A and C. Then second set started to fail during maintenance. Any reason for this to happen?

Thanks,

Chathura

On Tue, Oct 4, 2016 at 3:41 PM, Lars Helge Øverland lars@dhis2.org wrote:

Hi Chathura,

currently the approach to achieve this is to rely on the alphabetical ordering of SQL view names. For the scheduled tasks, the system first drops the SQL views in reversed alphabetical order. Finally, the system regenerates the SQL views in alphabetical order.

So in your case where A depends on B, you should ensure that that the name of SQL view B comes before the name of SQL view A alphabetically. This way, the system will first drop A, then B, and when it is time to regenerate the views, first generate B, then A, and you should be good.

best regards,

Lars

Best Regards,

Chathura Widanage,

Undergraduate,

Department of Computer Science and Engineering,

University of Moratuwa,

Sri Lanka

On Tue, Oct 4, 2016 at 11:10 AM, chathura widanage chathurawidanage@gmail.com wrote:

Dear Devs,

We have two SQLViews created in DHIS2 v2.24 where one view depends on other one(Say, A depends on B). We have also scheduled an automatic maintenance event every day at 12.00 a.m. Problem is DHIS2 tries to drop view B first during this automated procedure and as a result whole procedure fails including analytics table generation.

Is there a way to make DHIS2 scheduled event drop A before B in above case.

Thanks & Regards,

Chathura Widanage


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

Best Regards,

Chathura Widanage,

Undergraduate,

Department of Computer Science and Engineering,

University of Moratuwa,

Sri Lanka