Create a gist now

Instantly share code, notes, and snippets.

@wagerlabs /example.sql Secret
Created Mar 30, 2012

Mix in action
CREATE TABLE Publisher
(
id text AUTO, -- auto-generated, uuid
stacks set of text REFERENCES Stacks, -- primary key ref
sub_count int, -- Subscription.stack.publisher.id
PRIMARY KEY (id)
);
CREATE TABLE Stack
(
id text AUTO,
publisher text NOT NULL REFERENCES Publisher,
subscriptions set of text REFERENCES Subscription,
PRIMARY KEY (id)
);
CREATE TABLE Subscription
(
id text AUTO,
user text NOT NULL REFERENCES User,
stack text NOT NULL REFERENCES Stack,
notify_freq text NOT NULL,
language text NOT NULL,
last_notified int,
primary key (id)
);
CREATE TABLE NewDocs
(
stack text NOT NULL REFERENCES Stack,
add_date int NOT NULL,
doc_id text NOT NULL,
PRIMARY KEY (stack),
SECONDARY KEY (add_date)
);
CREATE TABLE User
(
id text NOT NULL,
subscriptions set of text REFERENCES Subscription,
PRIMARY KEY (id)
);
CREATE TABLE UnconfirmedEmails
(
id text AUTO,
email text NOT NULL,
PRIMARY KEY (id)
);
-- CREATE TRIGGER reminder2
-- ON Sales.Customer
-- AFTER INSERT, UPDATE, DELETE
-- AS
-module(publisher).
-export([
setup/2,
teardown/0,
describe/0,
create/1,
delete/1,
fetch/1,
store/2,
add_to_stacks/2,
remove_from_stacks/2,
bump_sub_count/2
]).
setup(RPS, WPS) ->
ddb:create_table(<<"Publisher">>, RPS, WPS).
teardown() ->
ddb:remove_table(<<"Publisher">>).
describe() ->
ddb:describe_table(<<"Publisher">>).
create(Values) ->
Values1 = mix:auto_gen(Values, [ {<<"id">>, 'string'} ]),
Args = mix:prep_ddb_args(Values1, [
{<<"id">>, 'string'},
{<<"sub_count">>, 'number'},
{<<"stacks">>, 'string_set'}
]),
ddb:put(<<"Publisher">>, Args).
delete(Id) ->
ddb:delete(<<"Publisher">>, ddb:key_value(Id, 'string')).
fetch(Id) ->
ddb:get(<<"Publisher">>, ddb:key_value(Id, 'string')).
store(Id, Values) ->
Args = mix:prep_ddb_args(Values, 'put', [
{<<"sub_count">>, 'number'},
{<<"stacks">>, 'string_set'}
]),
ddb:update(<<"Publisher">>, ddb:key_value(Id, 'string'), Args).
add_to_stacks(Id, Values) ->
ddb:update(<<"Publisher">>, ddb:key_value(Id, 'string'), [{<<"stacks">>, Values, 'string_set', 'add'}]).
remove_from_stacks(Id, Values) ->
ddb:update(<<"Publisher">>, ddb:key_value(Id, 'string'), [{<<"stacks">>, Values, 'string_set', 'delete'}]).
bump_sub_count(Id, Value) ->
ddb:update(<<"Publisher">>, ddb:key_value(Id, 'string'), [{<<"sub_count">>, Value, 'number', 'add'}]).
-module(stack).
-export([
setup/2,
teardown/0,
describe/0,
create/1,
delete/1,
fetch/1,
store/2,
add_to_subscriptions/2,
remove_from_subscriptions/2
]).
setup(RPS, WPS) ->
ddb:create_table(<<"Stack">>, RPS, WPS).
teardown() ->
ddb:remove_table(<<"Stack">>).
describe() ->
ddb:describe_table(<<"Stack">>).
create(Values) ->
Values1 = mix:auto_gen(Values, [ {<<"id">>, 'string'} ]),
Args = mix:prep_ddb_args(Values1, [
{<<"id">>, 'string'},
{<<"subscriptions">>, 'string_set'},
{<<"publisher">>, 'string'}
]),
ddb:put(<<"Stack">>, Args),
PrimaryKey = proplists:get_value(<<"id">>, Values1),
publisher:add_to_stacks(Id, [PrimaryKey]).
delete(Id) ->
ddb:delete(<<"Stack">>, ddb:key_value(Id, 'string')),
PrimaryKey = proplists:get_value(<<"id">>, Values1),
publisher:remove_from_stacks(Id, [PrimaryKey]).
fetch(Id) ->
ddb:get(<<"Stack">>, ddb:key_value(Id, 'string')).
store(Id, Values) ->
Args = mix:prep_ddb_args(Values, 'put', [
{<<"subscriptions">>, 'string_set'},
{<<"publisher">>, 'string'}
]),
ddb:update(<<"Stack">>, ddb:key_value(Id, 'string'), Args).
add_to_subscriptions(Id, Values) ->
ddb:update(<<"Stack">>, ddb:key_value(Id, 'string'), [{<<"subscriptions">>, Values, 'string_set', 'add'}]).
remove_from_subscriptions(Id, Values) ->
ddb:update(<<"Stack">>, ddb:key_value(Id, 'string'), [{<<"subscriptions">>, Values, 'string_set', 'delete'}]).
-module(subscription).
-export([
setup/2,
teardown/0,
describe/0,
create/1,
delete/1,
fetch/1,
store/2,
bump_last_notified/2
]).
setup(RPS, WPS) ->
ddb:create_table(<<"Subscription">>, RPS, WPS).
teardown() ->
ddb:remove_table(<<"Subscription">>).
describe() ->
ddb:describe_table(<<"Subscription">>).
create(Values) ->
Values1 = mix:auto_gen(Values, [ {<<"id">>, 'string'} ]),
Args = mix:prep_ddb_args(Values1, [
{<<"id">>, 'string'},
{<<"last_notified">>, 'number'},
{<<"language">>, 'string'},
{<<"notify_freq">>, 'string'},
{<<"stack">>, 'string'},
{<<"user">>, 'string'}
]),
ddb:put(<<"Subscription">>, Args),
PrimaryKey = proplists:get_value(<<"id">>, Values1),
user:add_to_subscriptions(Id, [PrimaryKey]),
stack:add_to_subscriptions(Id, [PrimaryKey]).
delete(Id) ->
ddb:delete(<<"Subscription">>, ddb:key_value(Id, 'string')),
PrimaryKey = proplists:get_value(<<"id">>, Values1),
user:remove_from_subscriptions(Id, [PrimaryKey]),
stack:remove_from_subscriptions(Id, [PrimaryKey]).
fetch(Id) ->
ddb:get(<<"Subscription">>, ddb:key_value(Id, 'string')).
store(Id, Values) ->
Args = mix:prep_ddb_args(Values, 'put', [
{<<"last_notified">>, 'number'},
{<<"language">>, 'string'},
{<<"notify_freq">>, 'string'},
{<<"stack">>, 'string'},
{<<"user">>, 'string'}
]),
ddb:update(<<"Subscription">>, ddb:key_value(Id, 'string'), Args).
bump_last_notified(Id, Value) ->
ddb:update(<<"Subscription">>, ddb:key_value(Id, 'string'), [{<<"last_notified">>, Value, 'number', 'add'}]).
@wagerlabs
Owner

Note that Amazon DynamoDB has no built-in referential integrity so I generate the code for it. For example, the code in subscription:create updates both User and Publisher "tables".

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment