i'll post it on issue tracker

ok, cool

https://github.com/ponyorm/pony/issues/386

here you are, sir!

Thanks for reporting, I hope I will be able to fix it today

It was really nice when you fixed the bug I reported the last time! Thanks!

@metaprogrammer wow, that was a quick job of that bug!

It's already fixed? Wow!

yes

I now feel excused then to ask another of my nitpicking questions: is it possible to use startswith on a list of options somehow, or would it need patching it in Pony? The following doesn't work: subclasses = tuple(c.__name__ for c in A.__subclasses__()) select(l for l in Log if l.representation.startswith(subclasses))According to Python's documentation, startswith accepts tuples, but it doesn't work in Pony. I tried an alternative way, but it doesn't work either: subclasses = tuple(c.__name__ for c in A.__subclasses__()) select(l for l in Log if any(l.representation.startswith(c) for c in subclasses))This one has a pretty obvious reason, as I try to iterate over c, which is not a database entity, inside a query.

By the way, you may notice some similarities to the matter I raised here: the isinstance function. But here, in this case, I really need to use repr and eval, because I try to make a Log object, which accepts any valid db.Entity as one of its values. So I'll ask two more questions about this: 1. Can I count on repr to always return strings that are reversible to entity in eval? 2. Would it be possible to add the option of having Set(db.Entity) (or Required(db.Entity) )? You may also remember the issue about primary key I mentioned here; since it's not yet fixed, I need to use repr, which works correctly.

# If you prefer, I can move that to GitHub as a question, so it would stay there for future reference for others.

Hi Krzysztof! Why do you need to use startswith instead of equality check, like log.classname in subclasses?

In principle it is possible to add support of startswith with a list of prefixes, but the resulted SQL will not be efficient

I didn't think of that, I suppose I could add classname field to Log instead of just representation. However, if I would need to store multiple representations, I could always separate them by semicolons or something, but in this case I would need to create a separate Entity class with classname and representation as attributes, and add Set(Entity) to Log. I have a feeling that would be inefficient, but that's just a hunch – what do you think?

The best solution, I think, would be to add Set(db.Entity) possibility, which could work with strings and representation under the hood, just like I'm thinking of doing right now.

Or maybe you have something even better in mind?

> Can I count on repr to always return strings that are reversible to entity in eval? I think yes, if you don't redeclare __repr__ in specific entity class. But it looks like a hack > Would it be possible to add the option of having Set(db.Entity) or Required(db.Entity)? At this moment generic relationships are not supported. It is possible to add them, but it is a non-trivial task. If all your entity classes use simple id value as a primary key, and each log record linked to a single object of arbitrary type, then you can just define two attributes, like obj_class and obj_id, and then get object using db.entities[log.obj_class][obj_id]

It is not entirely clear what goal your logging system should achieve, so it is hard to give specific advice. Maybe you can use the following: class LogRecord(db.Entity): id = PrimaryKey(int, auto=True) dt = Required(datetime) description = Required(str) objects = Set("ObjectRef") class ObjectRef(db.Entity): obj_class = Required(str) obj_id = Required(int) log_records = Set("LogRecord") PrimaryKey(obj_class, obj_id) And then you create an instance of AnyObject for each object of another type p1 = Person(name='John', age=22) p1_ref = ObjectRef(obj_class=p1.__class__.__name__, obj_id=p1.id) log_record = LogRecord(description="Person created", objects=[p1_ref]) And then you can find ObjectRef instances related to specific log records, and found real objects corresponding to ObjectRef instances

Well, not all my objects use plain id for primary keys, so I can't use that, I would really need to stick to repr, because then I can have things like A[B[1],C[2]]. So I think I'll have to use another (meta-)entity for that, which would look pretty much like what you did above.

Then maybe class ObjectRef(db.Entity): repr = PrimaryKey(str) classname = Required(str) log_records = Set("LogRecord")

Yeah, that's the way I'm going to do that, I suppose. So I think that matter is resolved, I should really have thought that through before asking. Lesson learned.

But, since we're already here, what do you think about adding something like OrderedSet as a feature? Right now, if we want to have a set of objects with their order, we have to do it like this: class Track(db.Entity): id = PrimaryKey(int, auto = True) title = Required(str) artist = Required('Artist') playlist_appearances = Set('TrackAppearance') class TrackAppearance(db.Entity): playlist = Required('Playlist') track = Required(Track) position = Required(int) PrimaryKey(playlist, track) class Playlist(db.Entity): id = PrimaryKey(int, auto = True) name = Required(str) tracks = Set(TrackAppearance)Is it really that inefficient as it looks, or maybe it's not that bad? I have several relationships like that in my database, and I don't think I can really change them.

It is a good idea, and I wanted to add a List attribute type from the beginning, which internally uses an intermediate table like in your example. Maybe OrderedSet is a better name for it. I definitely want to add it, but it is a big task, so probably not in the near future

Right now you need to implement OrderedSet manually using intermediate entity like in your example

Note that when using intermediate entity like TrackAppearance you can use attribute lifting to get all Playlists related to specific Track if order is not important: track.playlist_appearances.playlist gives a set of Playlist objects. And in reverse direction, playlist.tracks.track returns a set of all Track objects related to specific playlist

It simplifies some queries. But manualy working with position value is still tedious, so I hope OrderedSet will be implemented eventualy

Alright, thank you very much! I feel more confident in that solution now. :-) Looking forward for the OrderedSet or List in the future, but no rush, it's not that big deal. :-)

I would like to offer a new feature. raw data types. operators to which can be choosen from the available list

This give a possibility to add db specific types

Could you give an example of such type?

Pistgre arrays

But that would break the independence of Pony-generated scheme with other databases, wouldn't it?

I'm no expert, I'm just curious.

By the way, using raw SQL queries wouldn't help you?

I want to transparently store and load from db sets of digits without json

Arrays give subsets and simularity search

I have a feeling that it won't be suitable for Pony as it is an engine-dependent feature. But you'll have to wait for @metaprogrammer to answer this.

By the way, I believe I found a very minor bug – or at least an incorrect example in the documentation.

This is a part of the example from the documentation, here: https://docs.ponyorm.com/entities.html#hybrid-methods-and-properties def cars_by_color(self, color): return select(car for car in self.cars if car.color == color)Here it takes a color as an argument, so it obviously can't have @property decorator.

But let's try to make the following scheme: class Person(db.Entity): cars = Set('Car') @property def first_car(self): return select(car for car in self.cars).order_by(lambda c: c.purchase_year).first() class Car(db.Entity): make = Required(str) owner = Required(Person) purchase_year = Required(int)And let's add some objects: john = Person() ford = Car(make='Ford', owner=john, purchase_year=2014) toyota = Car(make='Toyota', owner=john, purchase_year=2016) commit()Then, the following doesn't work: print(john.first_car)It raises TypeError: Query can only iterate over entity or another query (not a list of objects).

We could change our property a bit and change self.cars to self.cars.select(), like this: class Person(db.Entity): cars = Set('Car') @property def first_car(self): return select(car for car in self.cars.select()).order_by(lambda c: c.purchase_year).first()Then it works as expected, but is inconsistent with the docs.

At this moment, if you have some specific object retrieved from the database: p1 = Person[1] you cannot start a query by iterating over collection: select(c for c in p1.cars if something) You need to iterate over entity: select(c foor c in Car if c.owner == p and something) This is because in Python the source of generator evaluated before generator itself, so when a query is written as: select(c for c in p1.cars if something) Then Python at first evals iter(p1.cars) and then pass it as an argument to generator object. So, iter(p1.cars) is not lazy, and retrieves list of cars before generator can be analyzed On the other side, for second and the following for-loops inside generator Python does not evaluated iterator beforehand, so Pony can inspect the source of for loop and translate it correctly. So the following is possible: select(p for p in Person for c in p.cars if something) Here p is not some already retrieved Person instance, but just abstract Person object inside a generator, so p.cars is just abstract collection and not some specific list of objects. So, if you want to apply first_car property to an object already retrieved from the database, you need to rewrite it a bit: @property def first_car(self): return select(car for car in Car if car.owner == self).order_by(lambda c: c.purchase_year).first() It is possible that in the future we can make iter(john.cars) a lazy object, and then your current form will also work both inside and outside of a query. Sorry, I need to go offline until Monday. Stsouko, thanks for the suggestion, I'll respond you later

Alright, I get it, it's just the documentation makes the same mistake I did – it would be nice to have this fixed so it doesn't confuse anyone else. Also, if I'm correct, having just self.cars.select() works the same and it doesn't break anything. Just consider it when you'll have a moment. And have some rest on the weekend! :-)

Ah, ok, I understand now what you mean. I agree, we need to fix it in the documentation #todo

Yes, self.cars.select() should work too

Thanks, have a good weekend!

I would like to offer a new feature. raw data types. operators to which can be choosen from the available list

In my experience, it is a non-trivial task to add a new data type. It requires to implement a converters between Python and DBAPI values, and also write a complex code for query translator. For mutable types (like arrays) the correct code is even more complex, because array value should be represented as a proxy object who tracks changes in situations like Person[1].phone_array.append(new_phone) and notifies parent object that it needs to be saved. I doubt we can provide an universal "raw data type" support. But implementation of PostgreSQL arrays support should be similar to JSON, so maybe we can just add PostgreSQL arrays. Usually we add types which can be implemented in all databases, but here we can make an exception. We even already have a corresponding issue: https://github.com/ponyorm/pony/issues/243

I will be glad if you implement an array support in postgres. perhaps in oracle also presented such data type

arrays are often used in search tasks. for example in image databases

Alright. Let's consider a following scheme: class A(db.Entity): b = Required('B') c = Required('C') xs = Set('X') PrimaryKey(b, c) class B(db.Entity): id = PrimaryKey(int, auto=True) asses = Set(A) class C(db.Entity): id = PrimaryKey(int, auto=True) asses = Set(A) class X(db.Entity): a = PrimaryKey(A) something = Optional(str)

> It's not correct. Since the primary key of object X is an A object, the primary key of x1 should be a1, or rather primary keys of a1. So the expected value would be ((1, 1),) Krzysztof, the problem is, "primary key" meaning may be ambiguous. Consider the following models: db = Database('sqlite', ':memory:') class A(db.Entity): b = Required("B") c = Required("C") PrimaryKey(b, c) class B(db.Entity): id = PrimaryKey(int) a_set = Set(A) class C(db.Entity): x = Required("X") y = Required("Y") a_set = Set(A) PrimaryKey(x, y) class X(db.Entity): id = PrimaryKey(int) c_set = Set(C) class Y(db.Entity): id = PrimaryKey(int) c_set = Set(C) db.generate_mapping(create_tables=True) with db_session(): x1 = X(id=456) y1 = Y(id=789) b1 = B(id=123) c1 = C(x=x1, y=y1) a1 = A(b=b1, c=c1) flush() print(a1) # A[B[123], C[X[456], Y[789]]] A primary key of A instance would mean the following things: 1) A tuple consisting of another entities (B[123], C[X[456], Y[789]]) - this is a "logical" (high-level) primary key, which PonyORM uses internally in db session cache 2) A flat tuple consisting of raw column values (123, 456, 789) - this is a "physical" (low-level) primary key which corresponds to values of foreign key columns. PonyORM uses it when constructing SQL queries 3) A potentially nested tuple of raw column values (123, (456, 789)) - this is what you want You can get (1) using a1._pkval_ and (2) using a1.get_pk(). In order to get (3) you can get a1._pkval_ and process it recursively. Pony does not provide a built-in way to obtain (3) because there was no popular use-case where (3) is necessary

I know, but it just feels wrong when in some cases Entity[e.get_pk()] works, and sometimes it fails. If Pony uses (2) when returning primary key, it should accept it when someone tries to access an entity using that key.

In the case I described, Pony accepted tuple ((1, 1),) and returned a correct object, even though it itself returned another primary key. So it accepts (3), but returns (2), which just *feels* incorrect.

> If Pony uses (2) when returning primary key, it should accept it when someone tries to access an entity using that key Sounds logical. I'll think how can we implement it...

If I may, the (3) actually would be better, because it would prevent ambiguities. I feel that they may arise, but I would need some time to find an example.

And Pony accepts (3) already, so making it return (3) as well would be more logical, I think. What do you think?

(2) is useful, because can be directly used in SQL. Composite foreign key in SQL is a plain sequence of columns without any nesting. > If I may, the (3) actually would be better, because it would prevent ambiguities. I feel that they may arise, but I would need some time to find an example. Actually I don't think there are any ambiguites in (2). In the end, SQL foreign key is flat for any primary key > And Pony accepts (3) already, so making it return (3) as well would be more logical, I think. What do you think? (3) looks as not as useful comparing to (2), as it requires some additional processing when working with SQL. Maybe I can (a) add nested=True keyword argument to .get_pk() to force it return (3) and (b) improve Entity.__getitem__ to support (2), but I don't understand in details how to do it yet

Hi @jake23247 !

@metaprogrammer, I'm sorry to keep you bothered with this, but in the face of new thoughts, I feel I should respond to you anyway.

I still believe the nested key would be the better option. I know that the flat ones can't be ambiguous, so it's no longer about that. But I feel that some *cognitive* ambiguities may still arise. If we have (123, 456, 789) in the code (let's forget for a minute that having it hard-coded would be a bad idea), one may begin to wonder: does that object really has 3 primary keys? Maybe only two, and one of them is an object with two integers as a primary key? Or maybe there is only one object in the primary key, but that deeper object has three primary keys? And so on. I know that developer should know what it's all about, but sometimes it may be confusing. “In the case of ambiguity, refuse the temptation to guess” – but the temptation could be strong here.

About the temptation to guess: the second reason is that having __getitem__ accept flat keys would make the Pony „guess” what developer had in mind. Let's suppose developer passed (123, 456, 789) as an argument. Now that could mean, for example, ((123, 456), 789) (a) or (123, (456, 789)) (b) in the terms of nested keys. What if (a) would be the correct structure, but developer had (b) in mind (because he was mistaken about which element has nested keys, first or second)? Now that will silently pass and return some item if it exists. But this item can't be what the developer (erroneously) asked for. In this case, one advantage of the nested keys would be that Pony could complain about having two elements of primary keys where only one can exist. That could be spotted in tests, but in case of flat keys, it would most probably arise at runtime.

The third reason would be a really nice correlation with the representation of an object. In a repr result, we can clearly see that the keys are nested. Having the same from get_pk would be always obvious. Also, “explicit is better than implicit” – here I would see explicit as explicitly pointing out that the keys are nested, without having to pass additional parameter to a function.

Fourth reason: unclear rules of flat representation. If we have 123, an integer, what would the primary key be? The obvious solution would be the same integer, 123. But let's go further with this. Since the nested key (123, (456,)) would be represented as (123, 456) in the flat form, the (123,) tuple (primary key is the object which has single integer as its key) could be also flattened to the single integer, so just 123. But then we lose the possibility of comparing the type of a returned key, which can be convenient sometimes. But what if (123,) tuple won't be flattened at all? Well, then we would have an exception. I don't feel this would be good.

Your point about flattened keys being more practical with SQL doesn't convince me at all. Pony is already a pretty big layer of abstraction, why should we make exceptions from this in small things? I agree, it may be more convenient to get flat key when further passing it to raw SQL. But Pony already lacks many features that would make converting to SQL easier, and I don't think it's a bad thing.

I think that's it. But one more thing, probably the most important of this all.

I don't write all this because I disagree that strongly with you. I won't stop using Pony if you decide to use flat keys in the end, and I will still reach out to you in case of any future doubts (provided it won't be too much for you).

The reason why I decided to elaborate so much on this, and why I try to notify you whenever I spot something odd, is that Pony really puts a smile on my face, and that is not an exaggeration. Working with Pony is a true joy that makes me want to experiment with different things. In SQL, I experimented because I had to, because it was always a struggle for me. So Pony is a really great thing for me, and I feel that one way I can give back is to provide you with feedback whenever I feel something is wrong, so you could make Pony an even greater tool.

So, whatever you decide will be fine, and Pony will continue to be great no matter what.

I may be a bit wrong in some of the above, or completely wrong – I would appreciate if you would take some time to consider what I wrote and give me some feedback. But you don't have to rush it.

We already did fix for that moment. So you can use a = A.select().first() A[a.get_pk()]

Alexander, we didn't publish the fix yet

Yes, but he should know that we already solve the problem)

Hi Krzystsof! Thank you for the full and detailed answer! I will answer a little later, maybe tomorrow

And he will be able to use it soon

Actually, our fix is a bit different

Hi @matthewrobertbell

Hi! There is a fast way to copy or clone a row/object changing only the id? Just a simple (and wrong) graphical explanation what I want: customer = Customer[1] customer.id = 2, Customer(customer), that's because i have to save new entities to the db but im busy to write all the required atts for different tables, because im only need different entities, the values doesn't matter. I have this idea just for curiosity, the time i spend writing and searching i would have written all the code that i needed xD

There is no ready clone method for entities If you have a dict with values, you can use it several times: obj1 = MyEntity(**kwargs) obj2 = MyEntity(**kwargs)

There is no ready clone method for entities If you have a dict with values, you can use it several times: obj1 = MyEntity(**kwargs) obj2 = MyEntity(**kwargs)

hmm okey xD, i have to write the values manually at lease once

xDD

Dont forget to pop primary key

Dont forget to pop primary key

yep!

and this : obj2 = MyEntity(**obj1.to_dict(exclude=['id']))

and this : obj2 = MyEntity(**obj1.to_dict(exclude=['id']))

damn to_dict xDDDDD

In my experience, it is a non-trivial task to add a new data type. It requires to implement a converters between Python and DBAPI values, and also write a complex code for query translator. For mutable types (like arrays) the correct code is even more complex, because array value should be represented as a proxy object who tracks changes in situations like Person[1].phone_array.append(new_phone) and notifies parent object that it needs to be saved. I doubt we can provide an universal "raw data type" support. But implementation of PostgreSQL arrays support should be similar to JSON, so maybe we can just add PostgreSQL arrays. Usually we add types which can be implemented in all databases, but here we can make an exception. We even already have a corresponding issue: https://github.com/ponyorm/pony/issues/243

+1 on that

@metaprogrammer , could you please look into https://github.com/ponyorm/pony/issues/390 ? It is really important for our project.