Confirm
574
edits
Changes
Created page with "= Goals = * Ref integ of email addresses * More customizable scoping = Use cases = # Notify anytime any Document changes. # Notify anytime a Document in a certain locale changes..."
= Goals =
* Ref integ of email addresses
* More customizable scoping
= Use cases =
# Notify anytime any Document changes.
# Notify anytime a Document in a certain locale changes.
# Notify when a particular Document changes.
# Delete an object and make its watches go away.
# New articles or changes to Documents that are untranslated into de and that are localizable and that are approved.
# Notify when an approval happens in German.
# Major edits to Documents in English
# Major edits to Documents
# Any activity tagged with "blue"
# Anything Fred does
# Tell what the user is subscribed to
= Evolution of table structure =
== Iteration 1: Event type determines the format of the path column(s). ==
path path2* type** email/whatever
15 DocumentEvent whatever@whatever.com
15.en-US DocumentEvent
15.de.2837 DocumentEvent
15.en-US.30 DocumentSignificanceEvent
TagEvent
* optional. Choose the split per event type based on where orthogonality happens.
** An event type, which can span, frex, use cases 1-3 below. Determines the use of the spec fields.
== Iteration 2: Get ref-integ on content type. ==
content_type object_id path event_type email/? (usecase)
15 en-US DocumentLocaleEvent 2
15 DefaultEvent 1
15 2837 DefaultEvent 3
15 en-US.30 DocumentLocaleSignifEvent 7
15 de.L.A UntranslatedEvent 5
Don't make path col unicode: save space.
== Iteration 3: More normalization, yielding better queryability: orthogonality of path elements (not just hierarchy) is possible without ridiculously inefficient substring queries. I don't think the joins would be too bad, but benches would be nice. ==
watches
id content_type object_id event_type user/email/whatever
1 15 2345 UntranslatedEvent
watch_elements:
watch_id name value (int for size/speed/ad hoc joins)
1 lang CRC32(de) # Postgres doesn't have a CRC32 function, so ad hoc joins on string keys would be a pain.
1 localizable 1
1 approved 1
on UntranslatedEvent for object_id=2345 and content_type=15:
select distinct email from watches
left join watch_elements lang on watches.id=lang.watch_id and name='LANG' and (value IS NULL or value='de')
left join watch_elements localizable on watches.id=localizable.watch_id and name='LOCA' and (value IS NULL or value='1')
left join watch_elements approved on watches.id=approved.watch_id and name='APPR' and (value IS NULL or value='1')
where content_type=15 and (object_id=2345 or object_id IS NULL) and event_type=UntranslatedEvent
# It's an (n+1)-table join where n is the max length of the path (excluding content type and object ID). n of the joins, however, would be selecting among the same n rows: not a big deal.
== Iteration 4: Move content_type and object_id into watch_elements? See what the benches say. ==
watches:
id event_type user/email/whatever
1 UntranslatedEvent
watch_elements:
watch_id name value (int for size/speed/ad hoc joins)
1 lang CRC32(de) # Postgres doesn't support CRC32 in the DB, so ad hoc joins on string keys would be a drag.
1 localizable 1
1 approved 1
1 content_type 15
1 object_id 2345
This will bring it up to an (n+1)-table join, n being the max length of a certain event type's path *including* content type and object ID.
= Features =
* Maybe blocking an addy someday till a mail is confirmed (to prevent spamming)?
* Daily digests, supported by 2 tables:
digests:
user event_type
digest_elements (values plugged into the template specified by event_type):
name value
id 8634
hat_color red
* Ref integ of email addresses
* More customizable scoping
= Use cases =
# Notify anytime any Document changes.
# Notify anytime a Document in a certain locale changes.
# Notify when a particular Document changes.
# Delete an object and make its watches go away.
# New articles or changes to Documents that are untranslated into de and that are localizable and that are approved.
# Notify when an approval happens in German.
# Major edits to Documents in English
# Major edits to Documents
# Any activity tagged with "blue"
# Anything Fred does
# Tell what the user is subscribed to
= Evolution of table structure =
== Iteration 1: Event type determines the format of the path column(s). ==
path path2* type** email/whatever
15 DocumentEvent whatever@whatever.com
15.en-US DocumentEvent
15.de.2837 DocumentEvent
15.en-US.30 DocumentSignificanceEvent
TagEvent
* optional. Choose the split per event type based on where orthogonality happens.
** An event type, which can span, frex, use cases 1-3 below. Determines the use of the spec fields.
== Iteration 2: Get ref-integ on content type. ==
content_type object_id path event_type email/? (usecase)
15 en-US DocumentLocaleEvent 2
15 DefaultEvent 1
15 2837 DefaultEvent 3
15 en-US.30 DocumentLocaleSignifEvent 7
15 de.L.A UntranslatedEvent 5
Don't make path col unicode: save space.
== Iteration 3: More normalization, yielding better queryability: orthogonality of path elements (not just hierarchy) is possible without ridiculously inefficient substring queries. I don't think the joins would be too bad, but benches would be nice. ==
watches
id content_type object_id event_type user/email/whatever
1 15 2345 UntranslatedEvent
watch_elements:
watch_id name value (int for size/speed/ad hoc joins)
1 lang CRC32(de) # Postgres doesn't have a CRC32 function, so ad hoc joins on string keys would be a pain.
1 localizable 1
1 approved 1
on UntranslatedEvent for object_id=2345 and content_type=15:
select distinct email from watches
left join watch_elements lang on watches.id=lang.watch_id and name='LANG' and (value IS NULL or value='de')
left join watch_elements localizable on watches.id=localizable.watch_id and name='LOCA' and (value IS NULL or value='1')
left join watch_elements approved on watches.id=approved.watch_id and name='APPR' and (value IS NULL or value='1')
where content_type=15 and (object_id=2345 or object_id IS NULL) and event_type=UntranslatedEvent
# It's an (n+1)-table join where n is the max length of the path (excluding content type and object ID). n of the joins, however, would be selecting among the same n rows: not a big deal.
== Iteration 4: Move content_type and object_id into watch_elements? See what the benches say. ==
watches:
id event_type user/email/whatever
1 UntranslatedEvent
watch_elements:
watch_id name value (int for size/speed/ad hoc joins)
1 lang CRC32(de) # Postgres doesn't support CRC32 in the DB, so ad hoc joins on string keys would be a drag.
1 localizable 1
1 approved 1
1 content_type 15
1 object_id 2345
This will bring it up to an (n+1)-table join, n being the max length of a certain event type's path *including* content type and object ID.
= Features =
* Maybe blocking an addy someday till a mail is confirmed (to prevent spamming)?
* Daily digests, supported by 2 tables:
digests:
user event_type
digest_elements (values plugged into the template specified by event_type):
name value
id 8634
hat_color red
