def get_sequences(self, model, num_children, shard_range):
output = []
our_epoch = int(time.mktime(datetime(2012, 11, 1).timetuple()) * 1000)
proc = """CREATE OR REPLACE FUNCTION next_sharded_id(varchar, int, OUT result bigint) AS $$
DECLARE
sequence_name ALIAS FOR $1;
shard_id ALIAS FOR $2;
seq_id bigint;
now_millis bigint;
BEGIN
SELECT nextval(sequence_name::regclass) % 1024 INTO seq_id;
SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
result := (now_millis - {our_epoch}) << 23;
result := result | (shard_id << 10);
result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;""".format(
our_epoch=our_epoch
)
output.append(self.style.SQL_KEYWORD(proc))
for i in shard_range:
child = generate_child_partition(model, i)
output.append(
self.style.SQL_KEYWORD("CREATE SEQUENCE ")
+ self.style.SQL_TABLE(get_sharded_id_sequence_name(child))
+ ";"
)
return output
def get_children_table_sql(self, model, known_models, num_children,
shard_range):
output = []
opts = model._meta
def get_child_table_sql(child_num):
child = generate_child_partition(model, child_num)
output, references = self.connection.creation.sql_create_model(
child, self.style, [model, child])
return output
for i in shard_range:
output.extend(get_child_table_sql(i))
# Generate indexes for tables.
original_db_table = opts.db_table
for i in shard_range:
# TODO: suffix
opts.db_table = '%s_%s' % (original_db_table, i)
output.extend(
self.connection.creation.sql_indexes_for_model(
model, self.style))
opts.db_table = original_db_table
# ALTERs for check constraint on children table.
migrations = []
for i in shard_range:
child = generate_child_partition(model, i)
if isinstance(child._shards.key, basestring):
shard_key_repr = child._shards.key
shard_key_expr = '"%s"' % shard_key_repr
else:
shard_key_repr = '_'.join(child._shards.key)
# TODO: This sums the keys for the expression right now.
# This needs to match the logic in MasterShardOptions.get_key_from_kwargs.
shard_key_expr = '("' + '" + "'.join(child._shards.key) + '")'
constraint_name = "%s_%s_check_modulo" % (child._meta.db_table,
shard_key_repr)
# output.append(self.style.SQL_KEYWORD('ALTER TABLE ') +
# self.style.SQL_TABLE('"' + child._meta.db_table + '"') +
# self.style.SQL_KEYWORD(' ADD CONSTRAINT ') +
# self.style.SQL_FIELD('"'+shard_key_repr+'"')+
# #self.style.SQL_FIELD('"' + constraint_name + '"') +
# #self.style.SQL_KEYWORD(' CHECK ') +
# self.style.SQL_KEYWORD(' REFERENCES ') +
# '(%s);' % (model.parent._meta.db_table))
#'(%s %% %d = %d);' % (shard_key_expr, num_children, i))
# Temporary ALTER TABLEs to use new sequences until we've fully
# transitioned all old tables.
#migrations.append('ALTER TABLE "{0}" ALTER COLUMN id SET DEFAULT next_sharded_id(\'{0}_id_seq\', {1});'.format(child._meta.db_table, i))
return output + migrations
def get_children_table_sql(self, model, known_models, num_children, shard_range):
output = []
opts = model._meta
def get_child_table_sql(child_num):
child = generate_child_partition(model, child_num)
output, references = self.connection.creation.sql_create_model(child, self.style, [model, child])
return output
for i in shard_range:
output.extend(get_child_table_sql(i))
# Generate indexes for tables.
original_db_table = opts.db_table
for i in shard_range:
# TODO: suffix
opts.db_table = "%s_%s" % (original_db_table, i)
output.extend(self.connection.creation.sql_indexes_for_model(model, self.style))
opts.db_table = original_db_table
# ALTERs for check constraint on children table.
migrations = []
for i in shard_range:
child = generate_child_partition(model, i)
if isinstance(child._shards.key, basestring):
shard_key_repr = child._shards.key
shard_key_expr = '"%s"' % shard_key_repr
else:
shard_key_repr = "_".join(child._shards.key)
# TODO: This sums the keys for the expression right now.
# This needs to match the logic in MasterShardOptions.get_key_from_kwargs.
shard_key_expr = '("' + '" + "'.join(child._shards.key) + '")'
constraint_name = "%s_%s_check_modulo" % (child._meta.db_table, shard_key_repr)
output.append(
self.style.SQL_KEYWORD("ALTER TABLE ")
+ self.style.SQL_TABLE('"' + child._meta.db_table + '"')
+ self.style.SQL_KEYWORD(" ADD CONSTRAINT ")
+ self.style.SQL_FIELD('"' + constraint_name + '"')
+ self.style.SQL_KEYWORD(" CHECK ")
+ "(%s %% %d = %d);" % (shard_key_expr, num_children, i)
)
# Temporary ALTER TABLEs to use new sequences until we've fully
# transitioned all old tables.
migrations.append(
"ALTER TABLE \"{0}\" ALTER COLUMN id SET DEFAULT next_sharded_id('{0}_id_seq', {1});".format(
child._meta.db_table, i
)
)
return output + migrations
def get_sequences(self, model, num_children, shard_range):
output = []
our_epoch = int(time.mktime(datetime(2012, 11, 1).timetuple()) * 1000)
for i in shard_range:
proc = """
delimiter //
drop table if exists shard_seq_tbl //
create table shard_seq_tbl ( nextval bigint not null primary key auto_increment ) engine = MyISAM //
alter table shard_seq_tbl AUTO_INCREMENT = 10000 //
drop function if exists shard_nextval //
create function shard_nextval()
returns bigint
begin
insert into shard_seq_tbl values (NULL) ;
set @R_ObjectId_val=LAST_INSERT_ID() ;
delete from shard_seq_tbl ;
return @R_ObjectId_val ;
end//
drop function if exists now_msec//
CREATE FUNCTION now_msec RETURNS STRING SONAME "now_msec.so"//
drop function if exists next_sharded_id //
CREATE function next_sharded_id ()
RETURNS bigint
BEGIN
DECLARE our_epoch bigint DEFAULT 1325419260000;
DECLARE seq_id bigint;
DECLARE now_millis bigint;
DECLARE shard_id int DEFAULT {0};
DECLARE result bigint UNSIGNED;
SELECT MOD(shard_nextval(),1024) INTO seq_id;
SELECT now_msec() INTO now_millis;
set result := (now_millis - our_epoch) << 23;
set result := result | (seq_id << 10);
set result := result | (shard_id);
RETURN result;
END//
delimiter ;
"""
output.append(self.style.SQL_KEYWORD(proc.format(i)))
child = generate_child_partition(model, i)
#output.append(self.style.SQL_KEYWORD("CREATE SEQUENCE ") +
# self.style.SQL_TABLE(get_sharded_id_sequence_name(child)) + ";")
return output
def get_sequences(self, model, num_children, shard_range):
output = []
our_epoch = int(time.mktime(datetime(2012, 11, 1).timetuple()) * 1000)
for i in shard_range:
proc = """
delimiter //
drop table if exists shard_seq_tbl //
create table shard_seq_tbl ( nextval bigint not null primary key auto_increment ) engine = MyISAM //
alter table shard_seq_tbl AUTO_INCREMENT = 10000 //
drop function if exists shard_nextval //
create function shard_nextval()
returns bigint
begin
insert into shard_seq_tbl values (NULL) ;
set @R_ObjectId_val=LAST_INSERT_ID() ;
delete from shard_seq_tbl ;
return @R_ObjectId_val ;
end//
drop function if exists now_msec//
CREATE FUNCTION now_msec RETURNS STRING SONAME "now_msec.so"//
drop function if exists next_sharded_id //
CREATE function next_sharded_id ()
RETURNS bigint
BEGIN
DECLARE our_epoch bigint DEFAULT 1325419260000;
DECLARE seq_id bigint;
DECLARE now_millis bigint;
DECLARE shard_id int DEFAULT {0};
DECLARE result bigint UNSIGNED;
SELECT MOD(shard_nextval(),1024) INTO seq_id;
SELECT now_msec() INTO now_millis;
set result := (now_millis - our_epoch) << 23;
set result := result | (seq_id << 10);
set result := result | (shard_id);
RETURN result;
END//
delimiter ;
"""
output.append(self.style.SQL_KEYWORD(proc.format(i)))
child = generate_child_partition(model, i)
#output.append(self.style.SQL_KEYWORD("CREATE SEQUENCE ") +
# self.style.SQL_TABLE(get_sharded_id_sequence_name(child)) + ";")
return output
def get_child_table_sql(child_num):
child = generate_child_partition(model, child_num)
output, references = self.connection.creation.sql_create_model(
child, self.style, [model, child])
return output
def get_child_table_sql(child_num):
child = generate_child_partition(model, child_num)
output, references = self.connection.creation.sql_create_model(child, self.style, [model, child])
return output
