Add view support

[Fabio Lenzarini]

Hi Andrew,

I'm interested to add view to Item, customizing the Jam.py core.

There is some document? 

Just to start I need to add 2 field to "sys_items" table:

"is_view" boolean and "view_statement" longtext.

If work; I share the job with the comunity.

thanks

Fabio

[Drazen D. Babic]

Hi, 

by that you mean DB Views? 

Thx

[Fabio Lenzarini]

Hi Drazen, yes. Sorry for my bad English..

[Drazen D. Babic]

It's ok, nothing to be sorry about.
Just do the Pull request on github. There is no doco for the devs as it would be a pretty big job.

[Drazen D. Babic]

Oh, I forgot, I started documenting admin.sqlite stuff as you might be aware:

https://jampyapplicationbuilder.com/Jam.py.html

I'm finding this useful since many times need to reset safe mode when locked up due to testing. So for me it is easier to hop in here and copy/paste...

D.

[Fabio Lenzarini]

ThanksDrazen

i'm try to add the new field.. "f_view_statement"

added to myApp.sqlite sys_items table sys_items

added everywhere I've found "virtual_table"..

but I got this error

some hel?

thanks

Fabio

[Fabio Lenzarini]

Hi Andrew,

I need to add some new "property" to the item.. like "virtual table" flag or "table_name".. 

I want to add:

Is_view = flag to set il the item is a view

View_statement = long text with the query

I want to try to custom the core to add the capability to manage view..

Thanks

Fabio

Il ven 4 giu 2021, 20:49 Andrew Yushev <yush...@gmail.com> ha scritto:

Hi, Fabio

Do you want to add already existing fields to the table of the view form dynamically

or do you want to add fields to the database table?

пт, 4 июн. 2021 г. в 14:40, Fabio Lenzarini <fabio.l...@gmail.com>:

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/52ed1ce7-7d1e-48ed-8da3-725d97448d1an%40googlegroups.com.

[Fabio Lenzarini]

Ok... first step:

I've added 2 field on sys_items table:

is_view: to let the framework know that it is a SQL view

view_statement: contain the command to send to the DB

On the first step, the statement is written by hand.. or query maker..

The next step is write the statement reading the item... but I think to need some new field..

[Dražen Babić]

Pls remember that Jam supports different DB's. That is the idea, to develop on one and deploy on the other.

So the SQL might not work on different DB, and this is why Jam is using sqlalchemy. If views can be accessed by sqlalchemy, than great!  

So something like this:

https://pypi.org/project/sqlalchemy-views/

I do like sql idea tho, great stuff.

Thx

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/3fe17409-9cea-4826-a203-45423c0829c2n%40googlegroups.com.

[Fabio Lenzarini]

Hi Drazen,

the idea is to "assembly" the view_statement based on the setup for the different SQL dialect.

[Andrew Yushev]

Fabio, can you explain what your final goal is in terms of app development?

Adding fields to the app builder tables is not the solution.

Maybe you want to let users customize fields that are displayed in the view?

It can be done without changing the app builder.

вс, 6 июн. 2021 г. в 16:43, Fabio Lenzarini <fabio.l...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/17d86a49-e0f9-4279-a274-bdd01d806021n%40googlegroups.com.

[Fabio Lenzarini]

Hi Andrew,

In the development of an ERP, complex views are needed on data entered in several tables.

For example, suppose I need a view to show the delayed sales orders.

To do this, I need data from sales order rows, order headers, customers and articles.

How can I do this without an "SQL view"?

I would have to create a virtual table and create a program to fill it...read sales orde, read each line, read items... it works, but it's a waste of resources when the DB can do everything for us.

I have done some testing, creating the sql views on the DB by hand and creating a table in Jam.py with the same name and fields and it seems to work.

Now, I would like to give the possibility to create the sql view directly inside the framework....

[Drazen D. Babic]

Hi, 

I concur with that Andrew. It was only a matter of time when Jam would need a DB views. Plus complete translations, not only Captions but everything. And Fabio made a huge progress with it. 

Unless, of course, there is already a super simple way to present DB views in Jam :) This, sort of, touches my question about Virtual Table for messages. We just don't know how to do it...

Cheers

D.

[Andrew Yushev]

Hi, Fabio

Do you want to add already existing fields to the table of the view form dynamically

or do you want to add fields to the database table?

пт, 4 июн. 2021 г. в 14:40, Fabio Lenzarini <fabio.l...@gmail.com>:

ThanksDrazen

--

[Andrew Yushev]

Hi,

I think this situation can be resolved by using lookup fields.

For example, see the demo, the invoices have all necessary customer information

and tracks in the invoice has information about tracks from tracks table which in turn

has information from albums which in turn has information from artists.

Regards,

Andrew Yushev

вт, 8 июн. 2021 г. в 18:10, Drazen D. Babic <bab...@gmail.com>:

Hi Fabio, 

wan't aware of this, looks great. Thx

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/ec017e68-ff9d-4d9c-9f2d-fa7fa58b1f78n%40googlegroups.com.

[Drazen D. Babic]

Hi, 

this is a different scenario. In this scenario, if not mistaking, we are updating tables. The main scenario for the views in general is viewing.

Two different scenarios and two different needs. 

D.

[Danijel Kaurin]

Hi.

When I was tested Jam.py first time, I was using MySql database and I succeeded to import view on same way as external table. In Postgres, is not possible to import view sa external table (on normal way without coding) because view is not recognized sa object for import. If that would be possible, I think than can be solution for this situation.

Regards

[Fabio Lenzarini]

Ok, I'm working on it...

I'se some problem with the core logics, I'hope to solve it soon.

[Fabio Lenzarini]

Another bick on the "SQL view".

Now I've completed the parse of the new info to build the view command to send to SQLite:

all form jam.by framework..

I need to remove "view_calc_op" because sum avg and other calculation are done from the framework..

but I need to add box to chose the "join" type (inner, outer, full..)

stay tuned

Fabio

[Andrew Yushev]

Hi, Fabio

Unfortunately, I don't understand what you are doing.

Anderew

чт, 10 июн. 2021 г. в 20:16, Fabio Lenzarini <fabio.l...@gmail.com>:

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/a8895de1-d43f-49c1-9ca0-7546c28e6f96n%40googlegroups.com.

[Fabio Lenzarini]

Hi all

I have almost finished the development of the SQL view management.

Although Andrew doesn't agree with me ... :-)

I'll post a video soon and if anyone is interested, let's see how to integrate it.

ciao Fabio

[Fabio Lenzarini]

[Fabio Lenzarini]

With SQL view you can join more table.. create statistics form without create new table.

[Fabio Lenzarini]

Voilà the video.

http://www.softup.it/download/sql%20view%202.mp4

Happy "view" :-)

[Fabio Lenzarini]

For now, it works well with SQLlite, but the differences with other SQL dialects is minimal.

this is the list of the modified files:

C:\Program Files\Python39\Lib\site-packages\jam\adm_server.py 154 KB Python File 19/06/2021 13:09:07 17/03/2021 18:21:54 20/06/2021 16:01:43 14

188 ## sem_newview.sn

191 ## sem_newview.en

272 ## sem_newview.sn

280 ## sem_newview.en

901 ## sem_newview.sn                        

909 ## sem_newview.en                        

986 ## sem_newview.sn                        

989 ## sem_newview.en                        

1462 ## sem_newview.sn                    

1470 ## sem_newview.en

2752 ## sem_newview.sn    

2760 ## sem_newview.en          

2804 ## sem_newview.sn    

2812 ## sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\js\admin.js 181 KB File JavaScript 19/06/2021 13:23:37 17/03/2021 18:21:54 20/06/2021 16:01:43 24

514 // sem_newview.sn

516 // sem_newview.en

1072 // sem_newview.sn

1074 // sem_newview.en

1081 // sem_newview.sn

1083 // sem_newview.en

1410 // sem_newview.sn

1412 // sem_newview.en

2336 // sem_newview.sn

2353 // sem_newview.en

5366 // sem_newview.sn

5372 // sem_newview.en

5391 // sem_newview.sn

5399 // sem_newview.en

5469 // sem_newview.sn

5475 // sem_newview.en

5625 // sem_newview.sn

5703 // sem_newview.en

5877 // sem_newview.sn

5919 // sem_newview.en

5987 // sem_newview.sn

5989 // sem_newview.en

5998 // sem_newview.sn

6004 // sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\builder.html 30 KB Firefox HTML Document 17/06/2021 17:34:47 17/03/2021 18:21:54 20/06/2021 16:01:43 4

441 <!-- sem_newview.sn -->

443 <!-- sem_newview.en -->

457 <!-- sem_newview.sn -->

462 <!-- sem_newview.sn -->

C:\Program Files\Python39\Lib\site-packages\jam\admin\builder.py 78 KB Python File 18/06/2021 15:35:25 17/03/2021 18:21:54 20/06/2021 16:01:43 10

30         ,'f_view_join_type': ['INNER JOIN', 'LEFT JOIN', 'CROSS JOIN', 'LEFT OUTER JOIN', 'NATURAL JOIN']     ## sem_newview.n

996 ##    print("create_system_item")     ## sem_newview

1196 ## sem_newview.sn    

1204 ## sem_newview.en

1221 ## sem_newview.sn

1224 ## sem_newview.en    

1233     ## sem_newview.sn

1236     ## sem_newview.en

1257 ## sem_newview.sn    

1265 ## sem_newview.en                           

C:\Program Files\Python39\Lib\site-packages\jam\dataset.py 78 KB Python File 19/06/2021 13:12:11 17/03/2021 18:21:54 20/06/2021 16:01:43 16

9 ## sem_newview.so

16 ## sem_newview.eo

18 ## sem_newview.sn

27 ## sem_newview.en

96 ## sem_newview.sn

107 ## sem_newview.en

964 ## sem_newview.sn

967 ## sem_newview.en

978 ## sem_newview.so

986 ## sem_newview.eo

988 ## sem_newview.sn            

997 ## sem_newview.en

1025 ## sem_newview.sn        

1033 ## sem_newview.en        

1096 ## sem_newview.sn

1099 ## sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\admin\import_metadata.py 24 KB Python File 15/06/2021 16:31:09 17/03/2021 18:21:54 20/06/2021 16:01:43 2

300 ## sem_newview.sn                    

308 ## sem_newview.en                    

C:\Program Files\Python39\Lib\site-packages\jam\js\jam.js 519 KB File JavaScript 11/06/2021 15:23:03 17/03/2021 18:21:54 20/06/2021 16:01:43 10

113 // sem_newview.sn

121 // sem_newview.en

278 // sem_newview.sn

281 // sem_newview.en

3780 // sem_newview.sn

3784 // sem_newview.en

3815 // sem_newview.sn         

3827 // sem_newview.en

4541 // sem_newview.sn

4544 // sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\server_classes.py 50 KB Python File 14/06/2021 14:25:59 17/03/2021 18:21:55 20/06/2021 16:01:43 2

166             ##print("sqls", sqls) ## sem_newview

845         ## print("command", command) ## sem_newview

C:\Program Files\Python39\Lib\site-packages\jam\sql.py 53 KB Python File 18/06/2021 18:17:32 17/03/2021 18:21:55 20/06/2021 16:01:43 10

695 ## SEM_newview.sn                            

703 ## SEM_newview.en                            

708 ## SEM_newview.sn        

716 ## SEM_newview.en  

727 ## SEM_newview.sn              

731 ## SEM_newview.sn                      

799 ## SEM_newview.sn   

809 ## SEM_newview.en   

845 ## SEM_newview.sn        

1066 ## SEM_newview.en  

C:\Program Files\Python39\Lib\site-packages\jam\db\sqlite.py 6 KB Python File 17/06/2021 19:20:15 17/03/2021 18:21:54 20/06/2021 16:01:43 2

114 ## sem_newview.sn

119 ## sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\admin\task.py 14 KB Python File 11/06/2021 15:23:58 17/03/2021 18:21:54 20/06/2021 16:01:43 3

86 ## sem_newview.en the last 7 rows                    

168 ## sem_newview.sn                

171 ## sem_newview.en   

I've tagged all the custom, so, is very simple to add the code to standard Jam.py files.

I love Jam.py

it is easy to create applications from scratch and thanks to the power of open source code it is very easy to create new features.

I think the addition of SQL views will give a bigger boost to the spread of the framework.

Sometimes representing data from already built and populated tables could be complex without SQL views. Now it is possible and easy to meet the requirements of our customers.

Thanks

Ciao 

Fabio

[Andrew Yushev]

Hi, Fabio

Thank you for the work.

Can you publish the changes you made to see the code?

You are absolutely right there is a need for the alternative presentation.

I usually create a copy of an element and write the code to specify the copy behavior, defined displayed fields and 

write event handlers.

But the feature that allows it in the app builder can simplify and enhance the copy.

I personally prefer to create a copy of an element for the table.

I think it would be much simpler and I plan to do it so the users can compare.

Regards,

Andrew Yushev

вс, 20 июн. 2021 г. в 17:36, Fabio Lenzarini <fabio.l...@gmail.com>:

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/363e9e7b-5336-459a-a012-0ecf3f048c48n%40googlegroups.com.

[Fabio Lenzarini]

Hi andrew,

this is all the files involved on the custom.

a premise, I am not a Python programmer... so I tried to put together what I knew to achieve the goal. Surely an experienced eye like yours can make many improvements to the SW... :-)

For any quetion, tell me..

We can also plan a live Teams call if you need.

all the custom are tagged with sem_newview.

sn = start new code

en = end new code

so = start old code

eo = end old code

you can download the file from:

http://www.softup.it/download/sem_newview.zip

thanks

Ciao

Fabio

[Andrew Yushev]

Hi, Fabio

I wasn't able to download the file from 

http://www.softup.it/download/sem_newview.zip

I don't understand why you need the View tab in the field dialog form.

Why not use the Lookup tab to define joins.

For lookup fields jam.py uses joins to get the value and lookup_value of the field.

Not only joins, but nested joins 

For example, for the tracks 

the app generates this query that uses nested joint:

SELECT "DEMO_TRACKS"."NAME", "DEMO_TRACKS"."ALBUM", "DEMO_TRACKS"."MEDIA_TYPE",
    "DEMO_TRACKS"."GENRE", "DEMO_TRACKS"."COMPOSER", "DEMO_TRACKS"."MILLISECONDS",
    "DEMO_TRACKS"."BYTES", "DEMO_TRACKS"."UNITPRICE", "DEMO_TRACKS"."FILE",
    "DEMO_TRACKS"."ID", "DEMO_TRACKS"."DELETED",
    (SELECT SUM("QUANTITY") FROM "DEMO_INVOICE_TABLE" WHERE "DEMO_TRACKS".ID=TRACK AND "DELETED"=0) AS SOLD,
    DEMO_ALBUMS_27."TITLE" AS ALBUM_LOOKUP, DEMO_GENRES_29."NAME" AS GENRE_LOOKUP,
    DEMO_ALBUMS_27_ARTIST."NAME" AS ARTIST_LOOKUP
FROM "DEMO_TRACKS" AS "DEMO_TRACKS" LEFT OUTER JOIN "DEMO_ALBUMS" AS DEMO_ALBUMS_27
    ON "DEMO_TRACKS"."ALBUM" = DEMO_ALBUMS_27."ID" LEFT OUTER JOIN "DEMO_GENRES" AS DEMO_GENRES_29
    ON "DEMO_TRACKS"."GENRE" = DEMO_GENRES_29."ID" LEFT OUTER JOIN "DEMO_ARTISTS" AS DEMO_ALBUMS_27_ARTIST
    ON DEMO_ALBUMS_27."ARTIST" = DEMO_ALBUMS_27_ARTIST."ID"
WHERE "DEMO_TRACKS"."DELETED" = 0 ORDER BY DEMO_ALBUMS_27_ARTIST."NAME",
    DEMO_ALBUMS_27."TITLE", "DEMO_TRACKS"."NAME" LIMIT 0, 16

And these joins are enough for all practical cases.

Also as I see you generate your own queries and this means a huge amount of work

to provide support for all databases that jam,py supports and for testing it.

I am very sorry that you spend that amount of time and effort into it.

Regards,

Andrew Yushev

[Fabio Lenzarini]

Hi Andrew

What I have done is not intended to replace the excellent lookup concept that is present in Jam.py, but something to use in addition...

Suppose you have an ERP:

sales orders

delivery journals

invoices

You want to create a form to display "overdue" orders, i.e. sales order lines with the delivery date running in the past.

In the form the customer wants to:

the customer's data (code, company name, city)

the order data (order number, delivery address, payment method...)

the data of the order line (article code, description, total quantity, quantity still to be delivered, amount)

what would you do to create the form?

I use a view to put together all the data I need...

If you think it's not needed... OK, I'll implement it in my environment.

ciao

Fabio

[Dražen Babić]

Simple,

Pump millions of rows in:

sales orders

delivery journals

invoices

Test the speed of DB View and test the speed of Jam lookups, if is possible to make a similar view.

It will only show the speed but not the usability, which is quite subjective and personal.

 

The Tracks in Demo is not that great example since it does not aggregates data. It is a pure select SQL example. But ok, Fabio does not do that either - at the moment ;)

The DB view will always win over Python speed. Only a stored procedure can be faster, if not mistaking.

And than there are materialised DB views, etc...Huge topic, much bigger than Jam.

If only Jam could use DB views...

D.

[Fabio Lenzarini]

Hi Drazen

my "view".. is a real DB View.

this is the command sent to the DB:

And I use all the power of the SQL Engine.

To perform the same result without View, I need to create a virtual table and load all the records.. I think this is very slow..

[Drazen D. Babic]

Hi, 

it is not only about the performance. It is more about the people/companies out there who spent huge amount of time in developing DB views. For whatever the reasons.

This people will probably not use Jam simply because it is not supporting views. Or any other needed feature. 

I mean, even SQLite3 supports views, so no really something irrelevant that is not needed. 

[Fabio Lenzarini]

PS if the link don't work, try to copy the path to your browser

http://www.softup.it/download/sem_newview.zip

[Danijel Kaurin]

Hi Andrew.

I think than solution may be in this: when using Jam.py with MySQL, then is possible to import view from server on same way like external tables. Then we connect lookup fields like in Jam.py tables. But when using SQLite or PostgreSQL, it is not possible to import view from server (isn't visible for import). If this would be solved, than we have solution.

Regards

[Maxwell Morais]

I don't understand why take only MySQL, instead of supporting all Databases?

Jampy internally uses SQLAlchemy, so, SQLAlchemy always will recognize views for all databases that it support and that support views

MS SQL

SQLite

MySQL and MariaDb

PostgreSQL

Oracle

and others https://docs.sqlalchemy.org/en/14/dialects/index.html

Second, if the major issue is the concern about making SQL compatible with most databases, we should stick to using SQLAlchemy to generate our SQL queries, it will guarantee compatibility to all databases that SQLAlchemy supports.

https://docs.sqlalchemy.org/en/14/orm/query.html

The support of views is a really cool feature for Jampy, and it will cut-off development time by 70% in most of the cases! I'm already doing hardcoded views on my project and I saw the possibilities of the amazing work Fabio is doing!

It's only my humble opinion, but if the problem is about resources to tame the work, let me know, how can I help!

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/e4d6cc98-0ea6-49e7-a95f-775ba158ec55n%40googlegroups.com.

--

Best Regards.

Maxwell Morais

Python Developer powered by Frappé frameworks <3

+55 11 947248832

[Drazen D. Babic]

https://github.com/jam-py/jam-py/issues/64

It is not like it wasn't discussed 3 years ago ;) 

 

What I am trying to do now is to add Jam in here:
https://wiki.python.org/moin/WebFrameworks

But this is proving as impossible task. Just like this one:

https://www.techempower.com/benchmarks/

It is all about spreading the Word and to get users traction. 

So if the DB Views will provide more users, definitely something to consider.

D.

[Andrew Yushev]

I work now on copying elements.

The video demonstrates how it works.

With this approach there is no need for any additional query generation and

supports all database types.

пт, 25 июн. 2021 г. в 07:21, Drazen D. Babic <bab...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/bbba0996-b81a-4dee-86e3-827845d3e43cn%40googlegroups.com.

[Dražen Babić]

I think what Andrew is trying to show is how easy is to make a copy of existing stuff in v6.

That is a great feature. 

On Fri, 25 Jun 2021, 17:45 Fabio Lenzarini, <fabio.l...@gmail.com> wrote:

Hi Andrew,

wath is the copy on the DB?

If I add new records on Traks, I see the same records on Traks1?

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/69e476c9-33d5-448c-ba50-b5625c864929n%40googlegroups.com.

[Fabio Lenzarini]

Hi Andrew,

what is the copy on the DB?

If I add new records on Traks, I see the same records on Traks1?

[Fabio Lenzarini]

Ok Drazen, but is not the same of a SQL View...

If I "copy" the "Sales order line" table into "Sales order line on delay" and I add missing relation, every time I add a record on "Sales order line", Jam.Py add the same record to " Sales order line on delay"?

[Fabio Lenzarini]

Update:

I've try to export from SQLite and import into PostGreSQL.

I need to write some line of code to delete old view and other issue.. but the view are right created.

[Andrew Yushev]

The jam.py item is a wrapper around the database table.

So there can be several such wrappers.

For example, there is a copy method that dynamically creates such an object

https://jam-py.com/docs/refs/server/item/m_copy.html

пт, 25 июн. 2021 г. в 17:09, Fabio Lenzarini <fabio.l...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/a4e5e23c-f0b9-458f-9ab4-ece903ac0f13n%40googlegroups.com.

[Fabio Lenzarini]

Hi Andrew,

Yes.. and is fantastic... But is not the same as SQL view..

Try to explain me how to, by design, I can show the sales order line on delay.

Thanks

Fabio

[Andrew Yushev]

Hi, Fabio

Yes it is not SQL view,

As for the sales order line, I don't understand your question.

Regards, 

Andrew Yushev

вс, 27 июн. 2021 г. в 01:16, Fabio Lenzarini <fabio.l...@gmail.com>:

[Fabio Lenzarini]

my English is poor... let me try to explain my problem again, using an example:

in an ERP system, there are hundreds of tables, with many setup tables and parameters.

Now suppose we have the tables

articles, with a "group of articles" field

sales orders

sales orders rows, with a "scheduled delivery date" field

articles movements

Now, the customer wants a view in which he can see all the sales order rows that are overdue.

I would make a SQL view with sales order data, data from rows, data from articles, where expected delivery date <= today.

With my custom, without write a line of code, I can create the SQL view and show the data.

This is just one example.

If we think that in an ERP like Microsoft Dynamics AX, I can create a kind of SQL view directly in the view form and in the edit form... this makes us understand the importance of being able to aggregate several tables together.

When we develop an application that we then deploy to customers, needs like the one in the example arise all the time and I would solve almost all the problems in this way.

without SQL views, how can I do that?

thanks

Fabio

[Andrew Yushev]

Fabio, I think there is no problem at the moment to do this. 

Could you please give me some project so that I can demonstrate.

пн, 28 июн. 2021 г. в 11:09, Fabio Lenzarini <fabio.l...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/a3a96141-6638-496a-9944-46eb045010e5n%40googlegroups.com.

[Dražen Babić]

Lets not forget, some ERPs have about the same number of views as tables. 

The biggest obsticle for Jam is to recognise views in such systems. Trust me, I have 20 years old apps with many, many Oracle forms that can be replaced with Jam.

This systems have more than 150 million records in just one table.

Fix the views, and this opens a HUGE market. The views are there for security reasons. Not because of anything else.

D.

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/CACeOMLYJv7Fjagz1sDLOeMSxifcb700w%3D2At%3D_F6gaS%2ByzYfwA%40mail.gmail.com.

[Fabio Lenzarini]

I can't share now my app because I've modified the sys table to implement the SQL View.. 

but is simple:

keep the "demo" app, based on traks and invoice.

the customer need to know "the traks genres selled by country".

[Fabio Lenzarini]

If Andrew doesn't want to integrate my changes for SQL views, a middle ground might be:

a flag on the Items; this is external view

for each item with this flag, Jam.py behaves as if the "DB manual mode" flag were on.

We can draw views directly on the DB and "copy" their structure to Jam.by.

[Drazen D. Babic]

I agree with the above example, how to see the "Tracks sold by the country" . Or city.  Or Invoices by the Country, City. 

The Customers table is not the best, since Country, City is in the same table. If that makes any difference, Country, City, should be separate table.

However, the Select should not be some loop and it should not be a Report either! 

Maybe it can contain a Filter, not sure. 

Fabio, I think the views are NOT visible in all DB's.

[Fabio Lenzarini]

SQLite, postgresql, ms SQL server and MySQL works with SQL view.

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/88ad7ca1-8fdd-4261-966c-87fb7cbb0476n%40googlegroups.com.

[Drazen D. Babic]

Hi Fabio,

you are confusing me, are you saying Jam will recognize DB view in all this databases? What about Oracle?

[Fabio Lenzarini]

Hi Drazen.

I don't see why it wouldn't work..

Jam.py opens a channel to the DB and then performs a simple select.

Whether there is an SQL view or a table underneath, Jam.py doesn't care... because the DB always returns the data in the same way.

From what I've seen, Andrew has changed a lot of the code that accesses the DB, but I suppose the logic is still the same.

I insist on having SQL views.

by the way, I have already done everything... now from Jam.py I can quickly create SQL views, integrate them with the lookups and use them in the views... it takes a few fixes and adjustments that only the core developer can do, to optimise everything.

In this way, on the DB, we can create views to external DBs and create a kind of DataWarehouse... or DataAnalitics

[Fabio Lenzarini]

Hi Andrew,

Have you think how I can perform something like a SQL view like my sample: sales order on delay?

I've try to porting my customs on V6 but is very different from V5.

Thanks

Fabio

[Tony Anytime]

I presently create many DB views in Mysql for every table, many times with no changes, so that the jam views all have a different mysql view. If my understanding of all this is correct, I will be able to create the view right in Jampy. I hope that I understand this all correctly.

On Sunday, June 20, 2021 at 2:38:35 PM UTC-4 yush...@gmail.com wrote:

Hi, Fabio

Thank you for the work.

Can you publish the changes you made to see the code?

You are absolutely right there is a need for the alternative presentation.

I usually create a copy of an element and write the code to specify the copy behavior, defined displayed fields and 

write event handlers.

But the feature that allows it in the app builder can simplify and enhance the copy.

I personally prefer to create a copy of an element for the table.

I think it would be much simpler and I plan to do it so the users can compare.

Regards,

Andrew Yushev

вс, 20 июн. 2021 г. в 17:36, Fabio Lenzarini <fabio.l...@gmail.com>:

For now, it works well with SQLlite, but the differences with other SQL dialects is minimal.

this is the list of the modified files:

C:\Program Files\Python39\Lib\site-packages\jam\adm_server.py 154 KB Python File 19/06/2021 13:09:07 17/03/2021 18:21:54 20/06/2021 16:01:43 14

188 ## sem_newview.sn

191 ## sem_newview.en

272 ## sem_newview.sn

280 ## sem_newview.en

901 ## sem_newview.sn                        

909 ## sem_newview.en                        

986 ## sem_newview.sn                        

989 ## sem_newview.en                        

1462 ## sem_newview.sn                    

1470 ## sem_newview.en

2752 ## sem_newview.sn    

2760 ## sem_newview.en          

2804 ## sem_newview.sn    

2812 ## sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\js\admin.js 181 KB File JavaScript 19/06/2021 13:23:37 17/03/2021 18:21:54 20/06/2021 16:01:43 24

514 // sem_newview.sn

516 // sem_newview.en

1072 // sem_newview.sn

1074 // sem_newview.en

1081 // sem_newview.sn

1083 // sem_newview.en

1410 // sem_newview.sn

1412 // sem_newview.en

2336 // sem_newview.sn

2353 // sem_newview.en

5366 // sem_newview.sn

5372 // sem_newview.en

5391 // sem_newview.sn

5399 // sem_newview.en

5469 // sem_newview.sn

5475 // sem_newview.en

5625 // sem_newview.sn

5703 // sem_newview.en

5877 // sem_newview.sn

5919 // sem_newview.en

5987 // sem_newview.sn

5989 // sem_newview.en

5998 // sem_newview.sn

6004 // sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\builder.html 30 KB Firefox HTML Document 17/06/2021 17:34:47 17/03/2021 18:21:54 20/06/2021 16:01:43 4

441 <!-- sem_newview.sn -->

443 <!-- sem_newview.en -->

457 <!-- sem_newview.sn -->

462 <!-- sem_newview.sn -->

C:\Program Files\Python39\Lib\site-packages\jam\admin\builder.py 78 KB Python File 18/06/2021 15:35:25 17/03/2021 18:21:54 20/06/2021 16:01:43 10

30         ,'f_view_join_type': ['INNER JOIN', 'LEFT JOIN', 'CROSS JOIN', 'LEFT OUTER JOIN', 'NATURAL JOIN']     ## sem_newview.n

996 ##    print("create_system_item")     ## sem_newview

1196 ## sem_newview.sn    

1204 ## sem_newview.en

1221 ## sem_newview.sn

1224 ## sem_newview.en    

1233     ## sem_newview.sn

1236     ## sem_newview.en

1257 ## sem_newview.sn    

1265 ## sem_newview.en                           

C:\Program Files\Python39\Lib\site-packages\jam\dataset.py 78 KB Python File 19/06/2021 13:12:11 17/03/2021 18:21:54 20/06/2021 16:01:43 16

9 ## sem_newview.so

16 ## sem_newview.eo

18 ## sem_newview.sn

27 ## sem_newview.en

96 ## sem_newview.sn

107 ## sem_newview.en

964 ## sem_newview.sn

967 ## sem_newview.en

978 ## sem_newview.so

986 ## sem_newview.eo

988 ## sem_newview.sn            

997 ## sem_newview.en

1025 ## sem_newview.sn        

1033 ## sem_newview.en        

1096 ## sem_newview.sn

1099 ## sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\admin\import_metadata.py 24 KB Python File 15/06/2021 16:31:09 17/03/2021 18:21:54 20/06/2021 16:01:43 2

300 ## sem_newview.sn                    

308 ## sem_newview.en                    

C:\Program Files\Python39\Lib\site-packages\jam\js\jam.js 519 KB File JavaScript 11/06/2021 15:23:03 17/03/2021 18:21:54 20/06/2021 16:01:43 10

113 // sem_newview.sn

121 // sem_newview.en

278 // sem_newview.sn

281 // sem_newview.en

3780 // sem_newview.sn

3784 // sem_newview.en

3815 // sem_newview.sn         

3827 // sem_newview.en

4541 // sem_newview.sn

4544 // sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\server_classes.py 50 KB Python File 14/06/2021 14:25:59 17/03/2021 18:21:55 20/06/2021 16:01:43 2

166             ##print("sqls", sqls) ## sem_newview

845         ## print("command", command) ## sem_newview

C:\Program Files\Python39\Lib\site-packages\jam\sql.py 53 KB Python File 18/06/2021 18:17:32 17/03/2021 18:21:55 20/06/2021 16:01:43 10

695 ## SEM_newview.sn                            

703 ## SEM_newview.en                            

708 ## SEM_newview.sn        

716 ## SEM_newview.en  

727 ## SEM_newview.sn              

731 ## SEM_newview.sn                      

799 ## SEM_newview.sn   

809 ## SEM_newview.en   

845 ## SEM_newview.sn        

1066 ## SEM_newview.en  

C:\Program Files\Python39\Lib\site-packages\jam\db\sqlite.py 6 KB Python File 17/06/2021 19:20:15 17/03/2021 18:21:54 20/06/2021 16:01:43 2

114 ## sem_newview.sn

119 ## sem_newview.en

C:\Program Files\Python39\Lib\site-packages\jam\admin\task.py 14 KB Python File 11/06/2021 15:23:58 17/03/2021 18:21:54 20/06/2021 16:01:43 3

86 ## sem_newview.en the last 7 rows                    

168 ## sem_newview.sn                

171 ## sem_newview.en   

I've tagged all the custom, so, is very simple to add the code to standard Jam.py files.

I love Jam.py

it is easy to create applications from scratch and thanks to the power of open source code it is very easy to create new features.

I think the addition of SQL views will give a bigger boost to the spread of the framework.

Sometimes representing data from already built and populated tables could be complex without SQL views. Now it is possible and easy to meet the requirements of our customers.

Thanks

Ciao 

Fabio

Il giorno domenica 20 giugno 2021 alle 16:29:35 UTC+2 Fabio Lenzarini ha scritto:

Voilà the video.

http://www.softup.it/download/sql%20view%202.mp4

Happy "view" :-)

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/363e9e7b-5336-459a-a012-0ecf3f048c48n%40googlegroups.com.

[Fabio Lenzarini]

Hi Tony

yes...

I have modified jam.py 5 to handle SQL views.

Through new fields on sys_items and sys_fields, modifying the core, I was able from jam.py to create the SQL views and they work fine, have you seen my video?

The only problem is that Andrew does not want to integrate this into jam.py..

I tried with V6, but it's very different and doesn't work.

[Fabio Lenzarini]

For me, SQL views are fundamental... if they will not be integrated on the framework and no ideas to do statistics and other things come out, I will have to decide whether to continue with jam.py or not.

[Tony Anytime]

i actually haven't seen the Video, can you post link again please.   I personally love Jampy... but it just needs a few things to be really great.

[Fabio Lenzarini]

http://www.softup.it/download/sql%20view%202.mp4

mee too love jam.py... but I need SQL view or similar table.. for example:

to know all the sales line on delay

statistics

create widget with gauge.. 

On the invoice for example, I've a link to the customer.. If I need to create statistics by area.. by item broup and other.. 

When I design the pplication I can't know all the posible future statistics... with the SQL view is more simple.

[Tony Anytime]

Yes, it looks very interesting. Your video was so fast I put it at half speed and it was still fast. ;)   -  I could see using this, saving the step of making views outside jampy.

[Fabio Lenzarini]

Exactly..

this is my goal. But Andrew is not of the same idea...

[Dražen Babić]

Hi,

Everything needs time. I feel like this is puting a lot of pressure on the Man.

And we all work at the day job, some have very high pressure work and deadlines.

I also think that the DB views were not on the Roadmap. Yes, it might be needed, but there are other things too.

The burnout from too much work is real. Just saying.

D.

[Fabio Lenzarini]

Actually I'm on holiday..

But I've already modified on V6 the sys tables to add the necessary fields..

Ciao

Fabio