Cannot deploy my Development environment

Support
1

Hello all!

When I try to deploy my Development environement, I have this message (CrashLoopBackOff)

image
image644×179 10.9 KB

then the application does not run: 503 Service Temporarily Unvailable.
image
image618×138 4.7 KB

In my Database, I must say that I created a View with the SQL command in the Data/Queries section.
“CREATE VIEW myview AS …”

If I delete the view, (DROP VIEW myview;) then now I succeed to deploy may app. Once deployed, I must re-create my view, and then the view can be used by my application.

The deployment does not seem to support the Views, am I right? Is it something that can be fixed?

Thanks!
Jean

2

Hi Jean,

Yes, you are correct, currently MySQL VIEW is not supported, however, I will note this down for discussion for our next product review meeting.

Kind regards,
Jo

3

Hi Jo, ok thank you. Yes I hope views will be supported soon, I think they are essential for handling complex queries.
For now, view support would only be necessary during application deployment, as I’ve found they are well supported at runtime.
I have also noticed that it is curently possible to manage views in the Query/SQL screen using SQL commands such as CREATE VIEW, SELECT table_name FROM information_schema.view, SELECT VIEW_DEFINITION, DROP VIEWS, etc.

In the meantime, could you suggest other ways to handle complex queries besides SQL views?

Thank, Jean.

4

Hi Jean,

Could you please give me a bit more information on what you are trying to achieve and how you want to work with the data? Have you used data views with a complex query? If not, I can send you some links on data views.

Kind regards,
Jo

5

This is an example where I would like to use SQL Views. I have two tables: Timesheet and Invoice.

Timesheet
ConsultantKey
CustometKey
Date
StartTime
EndTime

Invoice
ConsultantKey
CustometKey
InvoiceNo
InvoiceDate
StartDate
EndDate
Rate
Taxe1
Taxe2

An invoice is created by selecting all TimeSheet that the date are between Invoice.StartDate and Invoice.EndDate, for a given ConsultantKey and CustometKey.

First I created a view for the timesheet that calculates the time difference for display and for more calculations.
CREATE VIEW AS TimesheetView
SELECT
ConsultantKey
CustometKey
Date
SEC_TO_TIME(TIME_TO_SEC(EndTime) - TIME_TO_SEC(StartTime)) AS DiffHHMM,
ROUND((TIME_TO_SEC(EndTime) - TIME_TO_SEC(StartTime)) / 3600,2) AS DiffDecimalHour,
FROM Timesheet

Then I use this VIEW to create a second VIEW for applying to an Invoice report detail.

CREATE VIEW AS InvoiceTimesheetView
SELECT
I.ConsultantKey,
I.CustometKey,
I.InvoiceNo,
I.InvoiceDate,
I.StartDate,
I.EndDate,
TV.Date,
TV.DiffHHMM,
TV.DiffDecimalHour
FROM
Invoice I
JOIN
TimesheetView TV ON TV.Date >= I.StartDate AND TV.Date <= I.EndDate
AND TV.ConsultantKey = I.ConsultantKey
AND TV.CustometKey = I.CustometKey

and the Invoice report summary

CREATE VIEW AS InvoiceSumView
SELECT
I.ConsultantKey,
I.CustometKey,
I.InvoiceNo,
I.InvoiceDate,
I.StartDate,
I.EndDate,
I.Rate,
I.Taxe1,
I.Taxe2,
SUM(FT.DiffDecimalHour) AS TotalHours,
ROUND(SUM(DiffDecimalHour) * I.Rate,2) AS TotalAmount,
ROUND(SUM(DiffDecimalHour) * I.Rate * (I.Taxe1/100),2) AS Taxe1Amount,
ROUND(SUM(DiffDecimalHour) * I.Rate * (I.Taxe2/100),2) As Taxe2Amount ,
FROM
Invoice I
JOIN
TimesheetView TV ON TV.Date >= I.StartDate AND TV.Date <= I.EndDate
AND TV.ConsultantKey = I.ConsultantKey
AND TV.CustometKey = I.CustometKey

Finally I re-use these Views filtered like this: SELECT * FROM InvoiceTimesheetView WHERE I.ConsultantKey = ? AND I.CustometKey ?

The last view will be re-used for many other forms or reports, such a statistics, amount of total taxe received in a year, list of customers who have not pay yet, total amount earn in a year per customer, etc…
So for each of these requests, i don’t want to re-create huge SQL queries difficult to read and error prones.

I wonder how I can handle easily these complex queries without using SQL VIEWS.

Regards,
Jean

6

Hi Jean,

We are looking into this for you, and I will get back to you shortly!

Kind regards,
Jo

1 Like
7

Hi Jean,

We are looking at implementing VIEW as a data source in the future, thanks for the feedback! In the meantime, could you write your query and then give the query a primary key (this can be added on the Indices page on the Query form). Once the query has a primary key it can then be used as a read-only data source for forms, reports etc. Once the primary key is added to the query Five will know what record you are selecting in the associated list. I hope this is a solution for you for now.

Kind regards,
Jo

8

Looking forward for this new feature, thank you!