Triggers and materialised views using pub sub patterns

[joeblew99]

Would it be possible to support this in a non dialect specific way.

The reason i ask this is that i often need to produce tables of data triggered from cha he's in rows in other tables.

This can be done without specific dB system dependencies by using hooks in the golang data access layer.

For example when an i settings or delete it update occurs on structs t type X, publish an event that holds the event type and structure. Other tables can then subscribe to the event type and update their table.

This needs to allow a transaction context too.

The pattern des ribed above allows lots of interesting use cases: Cqrs style architectures. Nosql database structure tire using a SQL database. Emergent design patterns

[joeblew99]

I noticed that support for composite keys and many to many relationships is requested in the issues list.

I want to point out that materialised views can be used to replace the need for many to many relationships. This is because materialised views gives alot of flexibility without breaking classic dB relational constraints.

I would be happy to help on the code for this but i am super curious if it's supported & appreciated first.

[AlekSi]

I'm not sure I understand what you are asking for. May you please clarify what you want to achieve and what should be done?

[joeblew99]

sorry. Here goes again

This is a much better summary. http://milinda.pathirage.org/kappa-architecture.com/

So with SQL dbs you need to joins between tables to build larger views that use FK's between tables. But there is another way, and it involves building materialised views. Then you dont need to do joins. You can still use a relational DB and do queries on the Materialised views.

For example say you have: Person Table

• ID
• PersonName
• PersonsBooks

Books Table

• ID
• BookName
• BookBlah

PersonsBook Table

• PersonID (composite key)
• BooksID (composite key)

Well instead of managing the joins, you can generate triggers to do the same things. Basically you want to maintain a List of Books a Person has. Person Table

• ID
• PersonName
• PersonsBooks

Books Table

• ID
• BookName
• BookBlab

PersonsBook Table (Materialised Table View)

• PersonID
• BookID
• BookName

Why ?

• no joins, but instead streaming updates to the Material Views / Tables
• writes are more expensive, but reads are much much less.
• designed to work across many databases using a Message queue. You can have a "bookAdded" , "bookremoved" & "bookdeleted" event fire and all DB's that are interested in that event can do whatever work they need. You dont have to of course, and can just use a local queue, but the point is its designed to scale.
• database migrations are not needed because the message queue can hold all changes, and you just replay it all into the new schema.
• can be modelled this in the structs tags.
• scales horizontally very well. thats about the gist of it.