Hello guy I would like to discuss a diagram for the web creator. This is about relations and that relations always have fields on both tables involved. I would suggest something like this as an option (see image)

Guys* :)

Pony genernates you Fields in both classes, so you can reach one from another

that's correct - having the relationship attributes in both entities allows traversing these attributes, so you can reach one object from another. Colin, let me understand, why you'd like to omit the relationship attribute in the User entity? How does it help?

Actually, you are not the first person asking about this. My understanding is that this way it is more SQL table oriented, because in the database there is no column for this relationship in the 'User' table

probably we could have two views in the editor 'object view' and 'table view'. Another option is to have the relation attributes separated from the regular attributes - say data attributes at the top and relationship attributes under a line at the bottom in each entity

are both relational fields created by pony actually created in the database or is this just in the code for representation?

the code only for one-to-many relationship the field is created in one table only

you can see that if you open one of the specific database with SQL tab (SQLite, MySQL, etc)

okey so in this case it only helps me in the overview of the online tool

the "object" and "table" view would be very nice

what technology are you using for working with the database, once the diagram is created?

for now i using django models

and for sure postgres as database

btw, we plan to add Django tab, where you can get Django models for your diagram

? thanks :)

what about having the attributes which will not be created as fields in the database of different color on the diagram? say gray?

would be helpful as well

the problem i have is that is gets very fast unclear

Another thing which struck me, was that you can't Zoom.

yes me 2

have you ever used enterprise architect?

currently you can zoom using the browser zoom

never used the enterprise architect. what we can pick from it?

Is there planned to be able to import pony code?

one cool thing is to create modules with specific models in each of them from that you have a view in each module to see only the tables related and the relations to other module-tables

Is there planned to be able to import pony code?

how that could be heplful? what workflow do you see in this case?

one cool thing is to create modules with specific models in each of them from that you have a view in each module to see only the tables related and the relations to other module-tables

yes, this needs to be done. We use the Editor ourselves and the diagram becomes too overwhelmed too fast. But what if you need to see all entities at a glance? Anyways all of them are interconnected

one cool thing is to create modules with specific models in each of them from that you have a view in each module to see only the tables related and the relations to other module-tables

Can you share an image of how this can be done?

sure

and the diagram?

these are the modules and models inside

is just an example

ok, I see, thanks!

Sorry guys, I need to step out for an hour But let's continue this conversation, it is very cool that you can give us your feedback

oke bye :)

how that could be heplful? what workflow do you see in this case?

2 cases: - apply changes in code easily or be able to create them in code first, and later visualize them - there is currently no import at all, It would be handy if I could enter a database i created hundreds of commits earlier, to have a look at the data at that time. Having really big databases/many tables, entering then manually can be a hassle. Maybe there could be an import module for other models too. That part maybe open source, so users can contribute other mappers, e.g. Importing Django Models into Pony

yes would be very nice

But I often change models in Source code, and manual doing that in the editor later sometimes I tend to forget something.

But I often change models in Source code, and manual doing that in the editor later sometimes I tend to forget something.

Currently I have the code generated from pony in one file, to track the changes I made online in the diagram, while using a different file where I wont overwrite modifications I didn't "draw" yet, where I merge it together once in a while

But I often change models in Source code, and manual doing that in the editor later sometimes I tend to forget something.

ok, I got you, thanks

The line db_user.last_used = orm.raw_sql("NOW()") Fails with TypeError: Attribute WebSession.last_used: expected type is 'datetime'. Got: <pony.orm.ormtypes.RawSQL object at 0x7eff3eaf3b38> The schema looks like this: class WebSession(db.Entity): id = PrimaryKey(UUID) bot_token = Optional(str, nullable=True) user = Optional(User) web_token = Optional(str) created = Required(datetime, sql_default='NOW()') last_used = Required(datetime, sql_default='NOW()')

RawSQL is just a text inside an SQL query. It evaluates on the database side. If you perform change in Python, assign Python value: from datetime import datetime ... db_user.last_user = datetime.now()

I was hoping I could have used NOW() to be independent of the script time(zone).

Hmm, so you mean that that Raw SQL value just used when UPDATE command will be sent to the database later? Maybe we can add this in the future. Until that, you can add a trigger which will update the column automatically: http://stackoverflow.com/questions/9556474/how-do-i-automatically-update-a-timestamp-in-postgresql/26284695#26284695

Hmm, so you mean that that Raw SQL value just used when UPDATE command will be sent to the database later? Maybe we can add this in the future. Until that, you can add a trigger which will update the column automatically: http://stackoverflow.com/questions/9556474/how-do-i-automatically-update-a-timestamp-in-postgresql/26284695#26284695

Yep, that would be great!

Having my boring old Sticker search query again: pairs = orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage for t in st.sticker.tags if (query_text.lower() in t.string.lower() or query_text == st.sticker.emoji) ).order_by(orm.desc(2), 1).limit(50, offset=offset) How would I best exclude StickerMessages where any of the Tags t.string.lower() == "blocked tag"?

and not exists(t for t in st.sticker.tags if t.string.lower() == "blocked tag")

I already have for t in st.sticker.tags, this can't be integrated?

no, the one of the tags exists and the other is not

Right. Is that where correct? Or should that be if?

yes, it should be if

:D Thanks.

Awesome, works!

Awesome, works!

@luckydonald how do you find the resulting query witten with Pony? does it look simple and understandable for you?

@luckydonald how do you find the resulting query witten with Pony? does it look simple and understandable for you?

It is a bit long. Yeah. But I think that comes with the complexity of the task.

I just think the two fors are strange, and not instantly to understand

it would be interesting to compare this good real-life example of a long query with one written with other mappers

It is not necessary to use two `for`s here. The query can be rewritten as: orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if (query_text == st.sticker.emoji or orm.exists(t for t in st.sticker.tags if query_text.lower() in t.string.lower())) and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") ).order_by(orm.desc(2), 1).limit(50, offset=offset) Also, I don't think it is good to calculate t.string.lower() inside query, it is better to pre-calculate it in a separate column. If it is possible to check this string for exact equality instead of substring test, the query can be expressed a bit simpler: orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if (query_text == st.sticker.emoji or query_string.lower() in st.sticker.tags.lowerercase) and "nsfw" not in st.sticker.tags.lowercase) ).order_by(orm.desc(2), 1).limit(50, offset=offset)

How can I specify a contraint on multible colums to be unique? I want Tag.string, .user, .message_id to be together unique.

It is not necessary to use two `for`s here. The query can be rewritten as: orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if (query_text == st.sticker.emoji or orm.exists(t for t in st.sticker.tags if query_text.lower() in t.string.lower())) and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") ).order_by(orm.desc(2), 1).limit(50, offset=offset) Also, I don't think it is good to calculate t.string.lower() inside query, it is better to pre-calculate it in a separate column. If it is possible to check this string for exact equality instead of substring test, the query can be expressed a bit simpler: orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if (query_text == st.sticker.emoji or query_string.lower() in st.sticker.tags.lowerercase) and "nsfw" not in st.sticker.tags.lowercase) ).order_by(orm.desc(2), 1).limit(50, offset=offset)

I'll have a look, thank you!

This is composite_key(string, user, message_id), right?

yes

Thanks

Will it be applied, when the table already exists?

I think it should. I go offline, will answer other questions tomorrow. Bye!

Thanks for your time! Have a good night!

I can't get COUNT to work... :/ orm.select((t.string, orm.count(t.string)) for t in Tag).order_by(orm.desc(2), 1) This returns ["MLP", 1] But the database definitly contains more than one

If instead of t.string I use t, there are indeeed multible results

When a specific attribute is specified inside count, pony counts distinct values of that attribute. In order to count total number of rows just write count() without any arguments, it translates to COUNT(*)

I want to count all equal strings

And generate a list with [string, COUNT]

E.g. 12x "MLP", 12x "Pony", 5x "Smiling"

then do 'select((t.string, orm.count()) for t in ...)' or 'select((t.string, orm.count(t)) for t in ...)'

The former query means "for each string count number of rows", the later one means "for each string count number of distinct Tag objects", the result should be the same in your case

Which would be more efficient?

The first one, I think. The difference should not be large

"In this case, even though the object had already been loaded to the cache, the query still had to be sent to the database because the name attribute is not a unique key. The database session cache will only be used if we lookup an object by its primary or unique key." https://docs.ponyorm.com/firststeps.html#getting-objects If object would be in the cache and someone would change this row in database would be cached object updated or not?

I think here is answer on my questions https://docs.ponyorm.com/transactions.html#optimistic-concurrency-control

If Pony read an object which was already loaded in a current db_session, then Pony checks whether an attributes still holds the same value. There are four possible situations: 1) The column value is the same as previously, Pony does nothing; 2) The column value is updated in the database, but the attribute value was no read or modified by application code. In that case Pony updates the attribute value silently. 3) The application code has already read previous value of the attribute. In that case, it is possible that current state of db_session depends on the obsolete value which was read from the attribute, and to prevent possible inconsistency Pony throws UnrepeatableRead exception. 4) The value of column in the database is changed. An application code already assign a new value to the attribute without reading the previous value. In that case the "db value" of attribute will be updated silently and later during COMMIT it will be overwritten by newly assigned value

Thank you @akozlovsky for that clear explanation. By the way, I reed that you planned to include GrapQL into new PonyORM relase. When I see GraphQL query it seems stict and clear: type Project { name: String tagline: String contributors: [User] } In Python 3.6 will be variable annotaion, and table defination code coul be like this:

class Project: name: str tagline: str contibutors: [User]

I think it could be cool thing to define db schemas

What do you think, guys?

Agreed

Type is just a part of the information about attribute. Beside type, there are many other things, like is it nullable, is it unique, what a maximul leghts of a string attribute, etc. Regarding collection attributes, currently we have Set attributes only, but in the future we plan to add List attributes as well, so the square bracket syntax is not enough to declare all necessary information

What about typing library? There we could manually add custom types

Can you give an example

https://docs.python.org/3/library/typing.html#generics

@akozlovsky with the new annotation system you can also just create custom types for annotation purposes

But what may be an example in context of ORM usage

Some simple use-case

For example it would be good for type checker

Can you give an example of code?