Support for JDBI style folder

[reubenfirmin]

JDBI is one of the saner database access libraries in the Java world. To solve the N+1 project (e.g. user with permissions) it allows the developer to specify a function that can fold rows into a hierarchy.

For example, say we have:

select u.*, p.user_perm from users u inner join permissions p on (p.user_id) where u.id=X

and let's say that the result is:

1             foo@b   CAN_DO_X
1             foo@b   CAN_DO_Y
1             foo@b   CAN_DO_Z

A folder would be a function that takes a row at a time, which also is passed in an accumulator (a dict of id -> typed object, in this case id->user).

For each row passed in, it would check the accumulator to see if the base object (user) existed. If not, it would make the base user object from the row and add it to the accumulator. It would then unpack the permission from the row, and add it to the list of permissions in the user object.

The output result of the user_folder would be a list of unique users, which might only be one user in this case, each of whom would have a list of permissions attached to them.

The beauty of this for you is that it doesn't require many changes, but it gives records support for N+1 queries very efficiently. You could add an option "folder" param to the query function; if supplied, call it once per row, and then return the values from the dict.

[vlcinsky]

Personally I have difficulties to understand the proposal. As there is clearly no visible feedback showing interest of other users, I propose closing it.

[vlcinsky]

As records serve as wrapper around sqlalchemy and tablib, both behaving very "tabular", proposed tree-like structures do not fit into it much.

[reubenfirmin]

This is not a tree like structure.

[vlcinsky]

Sorry for my misunderstanding of "folder" concept. Could you provide sort of README snippet demonstrating proposed behaviour?

[dsmatharu]

An analogy is something like aggregate function on a non-numerical column that collects / folds the string values as a list (instead of a function that gives single result with numerical values).

To extend the above example data:

1 foo@b CAN_DO_X 1 foo@b CAN_DO_Y 1 foo@b CAN_DO_Z 2 bar@b CAN_DO_L 2 bar@b CAN_DO_M

should give: 1 foo@b CAN_DO_X, CAN_DO_Y, CAN_DO_Z 2 bar@b CAN_DO_L, CAN_DO_M

The list separator needs to be a parameter(possibly with a default ",") here.