primary join condition based on a function or CAST

[Mike Bayer]

powerdns_records_table = Table(
    'records', meta,
    Column('id', Integer, primary_key=True),
    Column('name', Unicode(80)),
    Column('type', Unicode(10)),
    Column('content', Unicode(200)),
)

dhcp_hosts_table = Table(
    'dhcp_hosts', meta,
    Column('id', Integer, primary_key=True),
    Column('ip_address', PGInet, nullable=False),
    )

assign_mapper(session_context, DhcpHost, dhcp_hosts_table)

assign_mapper(session_context, DnsRecord, powerdns_records_table,
    properties={
        'dhcphost': relation(DhcpHost,
            primaryjoin=(cast(powerdns_records_table.c.content,PGInet)==dhcp_hosts_table.c.ip_address),
            foreign_keys=[powerdns_records_table.c.content](powerdns_records_table.c.content)),
        }
    )

this one might be tricky to figure out.

[Mike Bayer]

also, this works:

mapper(DhcpHost, dhcp_hosts_table)

mapper(DnsRecord, powerdns_records_table,
    properties={
        'dhcphost': relation(DhcpHost,
            primaryjoin=(cast(powerdns_records_table.c.content,PGInet)==dhcp_hosts_table.c.ip_address),
            foreign_keys=[powerdns_records_table.c.content](powerdns_records_table.c.content),
            remote_side=[dhcp_hosts_table.c.ip_address](dhcp_hosts_table.c.ip_address),
            viewonly=True,
            ),
        }
    )

now, the "viewonly" eliminates your "syncrule" issue. remote_side currently gives the lazyloader more information on which to build a lazy clause (works for eager too), and i can make it check for "CAST" to work around this issue (though id like it to work for any SQL function too).

but since viewonly, this wont help you with a flush(). if i made "syncrule" accept a manual argument, such as a callable which you would define to say "destination.content = str(source.ip_address)", that would be a generalized way for there to be any kind of functional stuff indicated within a primaryjoin condition.

note: in visit_binary() functions within properties.py/create_lazy_clause, detect all columns within the expression by searching specifically for Cast/UnaryExpression(?)/Function...we have to narrow down what can reasonably be considered the "endpoint" of the join condition. but when multiple columns detected, the foreign_keys attribute will need to be used to map back those columns. when syncrule detects this condition, that forces the error to be raised, that manual syncrule is needed. manual property looks like syncrules=rule2, .... where "rule" is a callble def(source, dest). manual syncrule will be hosted by SyncRule, and specifically does not handle the "clearkeys" scenario. it will receive dest as a params dict in the case of many-to-many.

in the case of CAST specifically, we do have some awareness that we need to go from PGInet -> string when setting DhcpHost's "content" attribute...but i dont like to redefine DB functionality on the Python side since we cant easily generalize it.

[Mike Bayer]

some progress in f5126ab3a169b6f8a9171868fe32b2bd385f8b8f. This allows lazy loaders to work even when the bind param needs to be placed within the function. As far as writable, we would need to add some more arguments to relation() indicating how sync should happen; this is probably not that hard at this point since the syncrules have been dramatically simplified.

[Mike Bayer]

the little known _local_remote_pairs meets this use case. We need to bring the option into the forefront, document its usage for odd cases like this and we're done. example is attached.

[Mike Bayer]

working version in 0.8 is attached.

[Mike Bayer]

Removing milestone: 0.8.0b1 (automated comment)