Skip to content

Instantly share code, notes, and snippets.

@joelreymont

joelreymont/example.sql

Created April 9, 2012 22:55
Show Gist options
  • Save joelreymont/2347201 to your computer and use it in GitHub Desktop.
Save joelreymont/2347201 to your computer and use it in GitHub Desktop.
Download ZIP
Mix in action, with triggers, type specs, etc.
Raw
example.sql
CREATE TABLE Publisher
(
id string AUTO PRIMARY KEY, -- auto-generated, uuid
stacks set of string REFERENCES Stack, -- primary key ref
subscription_count int -- Subscription.stack.publisher.id
);
CREATE TABLE Stack
(
id string AUTO PRIMARY KEY,
publisher string NOT NULL REFERENCES Publisher,
subscriptions set of string REFERENCES Subscription
);
CREATE TABLE Subscription
(
id string AUTO PRIMARY KEY,
user string NOT NULL REFERENCES User,
stack string NOT NULL REFERENCES Stack,
notify_freq string NOT NULL,
language string NOT NULL,
last_notified int
);
CREATE TABLE NewDocs
(
stack string PRIMARY KEY REFERENCES Stack,
add_date int NOT NULL SECONDARY KEY,
doc_id string NOT NULL
);
CREATE TABLE User
(
id string PRIMARY KEY,
subscriptions set of string REFERENCES Subscription
);
CREATE TABLE UnconfirmedEmails
(
id string AUTO PRIMARY KEY,
email string NOT NULL
);
CREATE TRIGGER inc_subscription_count
ON Subscription
AFTER INSERT
AS
UPDATE Publisher
SET Publisher.subscription_count = Publisher.subscription_count + 1
WHERE Subscription.stack = Stack.id
AND Stack.publisher = Publisher.id;
CREATE TRIGGER dec_subscription_count
ON Subscription
AFTER DELETE
AS
UPDATE Publisher
SET Publisher.subscription_count = Publisher.subscription_count - 1
WHERE Subscription.stack = Stack.id
AND Stack.publisher = Publisher.id;
Raw
newdocs.erl
-module(newdocs).
-export([store/2,
fetch/1,
delete/1,
create/1,
describe/0,
teardown/0,
setup/2,
field_values/2,
field_value/2,
uuid/0]).
-include("newdocs.hrl").
-define(FIELDS, [{'stack', {<<"stack">>, 2, 'string'}},
{'add_date', {<<"add_date">>, 3, 'number'}},
{'doc_id', {<<"doc_id">>, 4, 'string'}}]).
-spec uuid() -> binary().
uuid() ->
New = uuid:new(self()),
V1 = uuid:get_v1(New),
Id = uuid:uuid_to_string(V1),
list_to_binary(Id).
-spec field_value(#newdocs{}, atom()) -> {binary(), binary(), binary()}.
field_value(Item, Field) ->
{Bin, Pos, Type} = proplists:get_value(Field, ?FIELDS),
{Bin, convert(element(Pos, Item)), Type}.
-spec return('_', {'ok', '_'} | {'error', '_'}) -> {'ok', '_', '_'} |
{'error', '_'}.
return(X, {'ok', Y}) ->
{'ok', X, Y};
return(_, E = {'error', _}) ->
E.
-spec convert(binary() |
integer() |
[binary() | integer()]) -> binary().
convert(Bin)
when is_binary(Bin) ->
Bin;
convert(Int)
when is_integer(Int) ->
list_to_binary(integer_to_list(Int));
convert(L)
when is_list(L) ->
lists:map(fun convert/1, L).
-spec field_values(#newdocs{}, [atom()]) -> {binary(), binary(), binary()}.
field_values(Item, Fields) ->
F1 = fun(Field) -> field_value(Item, Field)
end,
Values = lists:map(F1, Fields),
F2 = fun({_, [], _}) -> 'false'; (_) -> 'true'
end,
lists:filter(F2, Values).
-spec setup(pos_integer(), pos_integer()) -> {'ok', '_'} |
{'error', '_'}.
setup(RPS, WPS) ->
ddb:create_table(<<"NewDocs">>,
ddb:key_type(<<"stack">>,
'string',
<<"add_date">>,
'number'),
RPS,
WPS).
-spec teardown() -> {'ok', '_'} | {'error', '_'}.
teardown() ->
ddb:remove_table(<<"NewDocs">>).
-spec describe() -> {'ok', '_'} | {'error', '_'}.
describe() ->
ddb:describe_table(<<"NewDocs">>).
-spec create(#newdocs{}) -> {'ok', '_', '_'} | {'error', '_'}.
create(Item) ->
Values = field_values(Item, ['stack', 'add_date', 'doc_id']),
return(Item, ddb:put(<<"NewDocs">>, Values)).
-spec delete(#newdocs{}) -> {'ok', '_'} | {'error', '_'}.
delete(Item) ->
ddb:delete(<<"NewDocs">>,
ddb:key_value(Item#newdocs.stack,
'string',
Item#newdocs.add_date,
'number')).
-spec fetch(#newdocs{}) -> {'ok', #newdocs{}} | {'error', '_'}.
fetch(Item) ->
ddb:get(<<"NewDocs">>,
ddb:key_value(Item#newdocs.stack,
'string',
Item#newdocs.add_date,
'number')).
-spec store(#newdocs{}, proplists:proplist()) -> {'ok', '_'} |
{'error', '_'}.
store(Item, Fields) ->
F = fun({N, V, T}) -> {N, V, T, 'put'}
end,
Values1 = field_values(Item, Fields),
Values2 = lists:map(F, Values1),
ddb:update(<<"NewDocs">>,
ddb:key_value(Item#newdocs.stack,
'string',
Item#newdocs.add_date,
'number'),
Values2).
Raw
newdocs.hrl
-record(newdocs, {
stack,
add_date,
doc_id
}).
-type newdocs() :: #newdocs{}.
Raw
publisher.erl
-module(publisher).
-export([bump_subscription_count/2,
remove_from_stacks/2,
add_to_stacks/2,
store/2,
fetch/1,
delete/1,
create/1,
describe/0,
teardown/0,
setup/2,
field_values/2,
field_value/2,
uuid/0]).
-include("publisher.hrl").
-include("stack.hrl").
-define(FIELDS, [{'id', {<<"id">>, 2, 'string'}},
{'stacks', {<<"stacks">>, 3, ['string']}},
{'subscription_count', {<<"subscription_count">>, 4, 'number'}}]).
-spec uuid() -> binary().
uuid() ->
New = uuid:new(self()),
V1 = uuid:get_v1(New),
Id = uuid:uuid_to_string(V1),
list_to_binary(Id).
-spec field_value(#publisher{}, atom()) -> {binary(), binary(), binary()}.
field_value(Item, Field) ->
{Bin, Pos, Type} = proplists:get_value(Field, ?FIELDS),
{Bin, convert(element(Pos, Item)), Type}.
-spec return('_', {'ok', '_'} | {'error', '_'}) -> {'ok', '_', '_'} |
{'error', '_'}.
return(X, {'ok', Y}) ->
{'ok', X, Y};
return(_, E = {'error', _}) ->
E.
-spec convert(binary() |
integer() |
[binary() | integer()]) -> binary().
convert(Bin)
when is_binary(Bin) ->
Bin;
convert(Int)
when is_integer(Int) ->
list_to_binary(integer_to_list(Int));
convert(L)
when is_list(L) ->
lists:map(fun convert/1, L).
-spec field_values(#publisher{}, [atom()]) -> {binary(), binary(), binary()}.
field_values(Item, Fields) ->
F1 = fun(Field) -> field_value(Item, Field)
end,
Values = lists:map(F1, Fields),
F2 = fun({_, [], _}) -> 'false'; (_) -> 'true'
end,
lists:filter(F2, Values).
-spec setup(pos_integer(), pos_integer()) -> {'ok', '_'} |
{'error', '_'}.
setup(RPS, WPS) ->
ddb:create_table(<<"Publisher">>,
ddb:key_type(<<"id">>, 'string'),
RPS,
WPS).
-spec teardown() -> {'ok', '_'} | {'error', '_'}.
teardown() ->
ddb:remove_table(<<"Publisher">>).
-spec describe() -> {'ok', '_'} | {'error', '_'}.
describe() ->
ddb:describe_table(<<"Publisher">>).
-spec create(#publisher{}) -> {'ok', '_', '_'} | {'error', '_'}.
create(Item) ->
Item1 = Item#publisher{ id = uuid() },
Values = field_values(Item1,
['id', 'stacks', 'subscription_count']),
return(Item1, ddb:put(<<"Publisher">>, Values)).
-spec delete(#publisher{}) -> {'ok', '_'} | {'error', '_'}.
delete(Item) ->
ddb:delete(<<"Publisher">>,
ddb:key_value(Item#publisher.id, 'string')).
-spec fetch(#publisher{}) -> {'ok', #publisher{}} | {'error', '_'}.
fetch(Item) ->
ddb:get(<<"Publisher">>,
ddb:key_value(Item#publisher.id, 'string')).
-spec store(#publisher{}, proplists:proplist()) -> {'ok', '_'} |
{'error', '_'}.
store(Item, Fields) ->
F = fun({N, V, T}) -> {N, V, T, 'put'}
end,
Values1 = field_values(Item, Fields),
Values2 = lists:map(F, Values1),
ddb:update(<<"Publisher">>,
ddb:key_value(Item#publisher.id, 'string'),
Values2).
-spec add_to_stacks(#publisher{}, [binary()]) -> {'ok', '_'} |
{'error', '_'}.
add_to_stacks(Item, Values) ->
ddb:update(<<"Publisher">>,
ddb:key_value(Item#publisher.id, 'string'),
[{<<"stacks">>, Values, ['string'], 'add'}]).
-spec remove_from_stacks(#publisher{}, [binary()]) -> {'ok', '_'} |
{'error', '_'}.
remove_from_stacks(Item, Values) ->
ddb:update(<<"Publisher">>,
ddb:key_value(Item#publisher.id, 'string'),
[{<<"stacks">>, Values, ['string'], 'delete'}]).
-spec bump_subscription_count(#publisher{}, binary()) -> {'ok', '_'} |
{'error', '_'}.
bump_subscription_count(Item, Value) ->
ddb:update(<<"Publisher">>,
ddb:key_value(Item#publisher.id, 'string'),
[{<<"subscription_count">>, convert(Value), 'number', 'add'}]).
Raw
publisher.hrl
-record(publisher, {
id,
stacks,
subscription_count
}).
-type publisher() :: #publisher{}.
Raw
stack.erl
-module(stack).
-export([remove_from_subscriptions/2,
add_to_subscriptions/2,
store/2,
fetch/1,
delete/1,
create/1,
describe/0,
teardown/0,
setup/2,
field_values/2,
field_value/2,
uuid/0]).
-include("stack.hrl").
-include("publisher.hrl").
-include("subscription.hrl").
-define(FIELDS, [{'id', {<<"id">>, 2, 'string'}},
{'publisher', {<<"publisher">>, 3, 'string'}},
{'subscriptions', {<<"subscriptions">>, 4, ['string']}}]).
-spec uuid() -> binary().
uuid() ->
New = uuid:new(self()),
V1 = uuid:get_v1(New),
Id = uuid:uuid_to_string(V1),
list_to_binary(Id).
-spec field_value(#stack{}, atom()) -> {binary(), binary(), binary()}.
field_value(Item, Field) ->
{Bin, Pos, Type} = proplists:get_value(Field, ?FIELDS),
{Bin, convert(element(Pos, Item)), Type}.
-spec return('_', {'ok', '_'} | {'error', '_'}) -> {'ok', '_', '_'} |
{'error', '_'}.
return(X, {'ok', Y}) ->
{'ok', X, Y};
return(_, E = {'error', _}) ->
E.
-spec convert(binary() |
integer() |
[binary() | integer()]) -> binary().
convert(Bin)
when is_binary(Bin) ->
Bin;
convert(Int)
when is_integer(Int) ->
list_to_binary(integer_to_list(Int));
convert(L)
when is_list(L) ->
lists:map(fun convert/1, L).
-spec field_values(#stack{}, [atom()]) -> {binary(), binary(), binary()}.
field_values(Item, Fields) ->
F1 = fun(Field) -> field_value(Item, Field)
end,
Values = lists:map(F1, Fields),
F2 = fun({_, [], _}) -> 'false'; (_) -> 'true'
end,
lists:filter(F2, Values).
-spec setup(pos_integer(), pos_integer()) -> {'ok', '_'} |
{'error', '_'}.
setup(RPS, WPS) ->
ddb:create_table(<<"Stack">>,
ddb:key_type(<<"id">>, 'string'),
RPS,
WPS).
-spec teardown() -> {'ok', '_'} | {'error', '_'}.
teardown() ->
ddb:remove_table(<<"Stack">>).
-spec describe() -> {'ok', '_'} | {'error', '_'}.
describe() ->
ddb:describe_table(<<"Stack">>).
-spec create(#stack{}) -> {'ok', '_', '_'} | {'error', '_'}.
create(Item) ->
Item1 = Item#stack{ id = uuid() },
Values = field_values(Item1, ['id', 'publisher', 'subscriptions']),
return(Item1, ddb:put(<<"Stack">>, Values)),
Publisher = #publisher{},
Publisher1 = Publisher#publisher{ id = Item#stack.publisher },
publisher:add_to_stacks(Publisher1, [Item#stack.id]).
-spec delete(#stack{}) -> {'ok', '_'} | {'error', '_'}.
delete(Item) ->
ddb:delete(<<"Stack">>, ddb:key_value(Item#stack.id, 'string')),
Publisher = #publisher{},
Publisher1 = Publisher#publisher{ id = Item#stack.publisher },
publisher:remove_from_stacks(Publisher1, [Item#stack.id]).
-spec fetch(#stack{}) -> {'ok', #stack{}} | {'error', '_'}.
fetch(Item) ->
ddb:get(<<"Stack">>, ddb:key_value(Item#stack.id, 'string')).
-spec store(#stack{}, proplists:proplist()) -> {'ok', '_'} |
{'error', '_'}.
store(Item, Fields) ->
F = fun({N, V, T}) -> {N, V, T, 'put'}
end,
Values1 = field_values(Item, Fields),
Values2 = lists:map(F, Values1),
ddb:update(<<"Stack">>,
ddb:key_value(Item#stack.id, 'string'),
Values2).
-spec add_to_subscriptions(#stack{}, [binary()]) -> {'ok', '_'} |
{'error', '_'}.
add_to_subscriptions(Item, Values) ->
ddb:update(<<"Stack">>,
ddb:key_value(Item#stack.id, 'string'),
[{<<"subscriptions">>, Values, ['string'], 'add'}]).
-spec remove_from_subscriptions(#stack{}, [binary()]) -> {'ok', '_'} |
{'error', '_'}.
remove_from_subscriptions(Item, Values) ->
ddb:update(<<"Stack">>,
ddb:key_value(Item#stack.id, 'string'),
[{<<"subscriptions">>, Values, ['string'], 'delete'}]).
Raw
stack.hrl
-record(stack, {
id,
publisher,
subscriptions
}).
-type stack() :: #stack{}.
Raw
subscription.erl
-module(subscription).
-export([bump_last_notified/2,
store/2,
fetch/1,
delete/1,
create/1,
describe/0,
teardown/0,
setup/2,
field_values/2,
field_value/2,
uuid/0,
inc_subscription_count/1,
dec_subscription_count/1]).
-include("publisher.hrl").
-include("stack.hrl").
-include("subscription.hrl").
-include("user.hrl").
-define(FIELDS, [{'id', {<<"id">>, 2, 'string'}},
{'user', {<<"user">>, 3, 'string'}},
{'stack', {<<"stack">>, 4, 'string'}},
{'notify_freq', {<<"notify_freq">>, 5, 'string'}},
{'language', {<<"language">>, 6, 'string'}},
{'last_notified', {<<"last_notified">>, 7, 'number'}}]).
-spec uuid() -> binary().
uuid() ->
New = uuid:new(self()),
V1 = uuid:get_v1(New),
Id = uuid:uuid_to_string(V1),
list_to_binary(Id).
-spec field_value(#subscription{}, atom()) -> {binary(), binary(), binary()}.
field_value(Item, Field) ->
{Bin, Pos, Type} = proplists:get_value(Field, ?FIELDS),
{Bin, convert(element(Pos, Item)), Type}.
-spec return('_', {'ok', '_'} | {'error', '_'}) -> {'ok', '_', '_'} |
{'error', '_'}.
return(X, {'ok', Y}) ->
{'ok', X, Y};
return(_, E = {'error', _}) ->
E.
-spec convert(binary() |
integer() |
[binary() | integer()]) -> binary().
convert(Bin)
when is_binary(Bin) ->
Bin;
convert(Int)
when is_integer(Int) ->
list_to_binary(integer_to_list(Int));
convert(L)
when is_list(L) ->
lists:map(fun convert/1, L).
-spec field_values(#subscription{}, [atom()]) -> {binary(), binary(), binary()}.
field_values(Item, Fields) ->
F1 = fun(Field) -> field_value(Item, Field)
end,
Values = lists:map(F1, Fields),
F2 = fun({_, [], _}) -> 'false'; (_) -> 'true'
end,
lists:filter(F2, Values).
-spec setup(pos_integer(), pos_integer()) -> {'ok', '_'} |
{'error', '_'}.
setup(RPS, WPS) ->
ddb:create_table(<<"Subscription">>,
ddb:key_type(<<"id">>, 'string'),
RPS,
WPS).
-spec teardown() -> {'ok', '_'} | {'error', '_'}.
teardown() ->
ddb:remove_table(<<"Subscription">>).
-spec describe() -> {'ok', '_'} | {'error', '_'}.
describe() ->
ddb:describe_table(<<"Subscription">>).
-spec create(#subscription{}) -> {'ok', '_', '_'} | {'error', '_'}.
create(Item) ->
Item1 = Item#subscription{ id = uuid() },
Values = field_values(Item1,
['id',
'user',
'stack',
'notify_freq',
'language',
'last_notified']),
return(Item1, ddb:put(<<"Subscription">>, Values)),
User = #user{},
User1 = User#user{ id = Item#subscription.user },
user:add_to_subscriptions(User1, [Item#subscription.id]),
Stack = #stack{},
Stack1 = Stack#stack{ id = Item#subscription.stack },
stack:add_to_subscriptions(Stack1, [Item#subscription.id]),
inc_subscription_count(Item).
-spec delete(#subscription{}) -> {'ok', '_'} | {'error', '_'}.
delete(Item) ->
ddb:delete(<<"Subscription">>,
ddb:key_value(Item#subscription.id, 'string')),
User = #user{},
User1 = User#user{ id = Item#subscription.user },
user:remove_from_subscriptions(User1, [Item#subscription.id]),
Stack = #stack{},
Stack1 = Stack#stack{ id = Item#subscription.stack },
stack:remove_from_subscriptions(Stack1, [Item#subscription.id]),
dec_subscription_count(Item).
-spec fetch(#subscription{}) -> {'ok', #subscription{}} |
{'error', '_'}.
fetch(Item) ->
ddb:get(<<"Subscription">>,
ddb:key_value(Item#subscription.id, 'string')).
-spec store(#subscription{}, proplists:proplist()) -> {'ok', '_'} |
{'error', '_'}.
store(Item, Fields) ->
F = fun({N, V, T}) -> {N, V, T, 'put'}
end,
Values1 = field_values(Item, Fields),
Values2 = lists:map(F, Values1),
ddb:update(<<"Subscription">>,
ddb:key_value(Item#subscription.id, 'string'),
Values2).
-spec bump_last_notified(#subscription{}, binary()) -> {'ok', '_'} |
{'error', '_'}.
bump_last_notified(Item, Value) ->
ddb:update(<<"Subscription">>,
ddb:key_value(Item#subscription.id, 'string'),
[{<<"last_notified">>, convert(Value), 'number', 'add'}]).
-spec inc_subscription_count(#subscription{}) -> 'ok'.
inc_subscription_count(Subscription) ->
Stack = #stack{},
Stack1 = Stack#stack{ id = Subscription#subscription.stack },
Stack2 = stack:fetch(Stack1),
Publisher = #publisher{},
Publisher1 = Publisher#publisher{ id = Stack2#stack.publisher },
Publisher2 = publisher:fetch(Publisher1),
Publisher3 = Publisher2#publisher{ subscription_count = Publisher2#publisher.subscription_count + 1 },
publisher:store(Publisher3, ['subscription_count']).
-spec dec_subscription_count(#subscription{}) -> 'ok'.
dec_subscription_count(Subscription) ->
Stack = #stack{},
Stack1 = Stack#stack{ id = Subscription#subscription.stack },
Stack2 = stack:fetch(Stack1),
Publisher = #publisher{},
Publisher1 = Publisher#publisher{ id = Stack2#stack.publisher },
Publisher2 = publisher:fetch(Publisher1),
Publisher3 = Publisher2#publisher{ subscription_count = Publisher2#publisher.subscription_count - 1 },
publisher:store(Publisher3, ['subscription_count']).
Raw
subscription.hrl
-record(subscription, {
id,
user,
stack,
notify_freq,
language,
last_notified
}).
-type subscription() :: #subscription{}.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment