Details
diff --git a/account/src/main/resources/com/ning/billing/account/ddl.sql b/account/src/main/resources/com/ning/billing/account/ddl.sql
index da8ba7d..67b3560 100644
--- a/account/src/main/resources/com/ning/billing/account/ddl.sql
+++ b/account/src/main/resources/com/ning/billing/account/ddl.sql
@@ -30,7 +30,7 @@ CREATE TABLE accounts (
updated_by varchar(50) DEFAULT NULL,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX accounts_id ON accounts(id);
CREATE UNIQUE INDEX accounts_external_key ON accounts(external_key);
CREATE INDEX accounts_tenant_record_id ON accounts(tenant_record_id);
@@ -67,7 +67,7 @@ CREATE TABLE account_history (
updated_date datetime NOT NULL,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
- );
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX account_history_target_record_id ON account_history(target_record_id);
CREATE INDEX account_history_tenant_record_id ON account_history(tenant_record_id);
@@ -85,7 +85,7 @@ CREATE TABLE account_emails (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX account_email_id ON account_emails(id);
CREATE INDEX account_email_account_id_email ON account_emails(account_id, email);
CREATE INDEX account_emails_tenant_account_record_id ON account_emails(tenant_record_id, account_record_id);
@@ -106,6 +106,6 @@ CREATE TABLE account_email_history (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX account_email_target_record_id ON account_email_history(target_record_id);
CREATE INDEX account_email_history_tenant_account_record_id ON account_email_history(tenant_record_id, account_record_id);
diff --git a/beatrix/src/main/resources/com/ning/billing/beatrix/ddl.sql b/beatrix/src/main/resources/com/ning/billing/beatrix/ddl.sql
index ff53553..b334e44 100644
--- a/beatrix/src/main/resources/com/ning/billing/beatrix/ddl.sql
+++ b/beatrix/src/main/resources/com/ning/billing/beatrix/ddl.sql
@@ -14,7 +14,7 @@ CREATE TABLE bus_ext_events (
search_key1 int(11) unsigned default null,
search_key2 int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX `idx_bus_ext_where` ON bus_ext_events (`processing_state`,`processing_owner`,`processing_available_date`);
CREATE INDEX bus_ext_events_tenant_account_record_id ON bus_ext_events(search_key2, search_key1);
@@ -32,5 +32,4 @@ CREATE TABLE bus_ext_events_history (
search_key1 int(11) unsigned default null,
search_key2 int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
-
+) CHARACTER SET utf8 COLLATE utf8_bin;
diff --git a/entitlement/src/main/resources/com/ning/billing/entitlement/ddl.sql b/entitlement/src/main/resources/com/ning/billing/entitlement/ddl.sql
index a4061e9..8a20159 100644
--- a/entitlement/src/main/resources/com/ning/billing/entitlement/ddl.sql
+++ b/entitlement/src/main/resources/com/ning/billing/entitlement/ddl.sql
@@ -17,6 +17,6 @@ CREATE TABLE blocking_states (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX blocking_states_id ON blocking_states(blockable_id);
CREATE INDEX blocking_states_tenant_account_record_id ON blocking_states(tenant_record_id, account_record_id);
diff --git a/invoice/src/main/resources/com/ning/billing/invoice/ddl.sql b/invoice/src/main/resources/com/ning/billing/invoice/ddl.sql
index de1c031..10e4eec 100644
--- a/invoice/src/main/resources/com/ning/billing/invoice/ddl.sql
+++ b/invoice/src/main/resources/com/ning/billing/invoice/ddl.sql
@@ -22,7 +22,7 @@ CREATE TABLE invoice_items (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX invoice_items_id ON invoice_items(id);
CREATE INDEX invoice_items_subscription_id ON invoice_items(subscription_id ASC);
CREATE INDEX invoice_items_invoice_id ON invoice_items(invoice_id ASC);
@@ -43,7 +43,7 @@ CREATE TABLE invoices (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX invoices_id ON invoices(id);
CREATE INDEX invoices_account_target ON invoices(account_id ASC, target_date);
CREATE INDEX invoices_tenant_account_record_id ON invoices(tenant_record_id, account_record_id);
@@ -65,7 +65,7 @@ CREATE TABLE invoice_payments (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX invoice_payments_id ON invoice_payments(id);
CREATE INDEX invoice_payments ON invoice_payments(payment_id);
CREATE INDEX invoice_payments_invoice_id ON invoice_payments(invoice_id);
diff --git a/payment/src/main/resources/com/ning/billing/payment/ddl.sql b/payment/src/main/resources/com/ning/billing/payment/ddl.sql
index 6dfc718..5da9aa0 100644
--- a/payment/src/main/resources/com/ning/billing/payment/ddl.sql
+++ b/payment/src/main/resources/com/ning/billing/payment/ddl.sql
@@ -18,7 +18,7 @@ CREATE TABLE payments (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY (record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX payments_id ON payments(id);
CREATE INDEX payments_inv ON payments(invoice_id);
CREATE INDEX payments_accnt ON payments(account_id);
@@ -46,7 +46,7 @@ CREATE TABLE payment_history (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX payment_history_target_record_id ON payment_history(target_record_id);
CREATE INDEX payment_history_tenant_account_record_id ON payment_history(tenant_record_id, account_record_id);
@@ -67,7 +67,7 @@ CREATE TABLE payment_attempts (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY (record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX payment_attempts_id ON payment_attempts(id);
CREATE INDEX payment_attempts_payment ON payment_attempts(payment_id);
CREATE INDEX payment_attempts_tenant_account_record_id ON payment_attempts(tenant_record_id, account_record_id);
@@ -91,7 +91,7 @@ CREATE TABLE payment_attempt_history (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX payment_attempt_history_target_record_id ON payment_attempt_history(target_record_id);
CREATE INDEX payment_attempt_history_tenant_account_record_id ON payment_attempt_history(tenant_record_id, account_record_id);
@@ -109,7 +109,7 @@ CREATE TABLE payment_methods (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY (record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX payment_methods_id ON payment_methods(id);
CREATE INDEX payment_methods_active_accnt ON payment_methods(is_active, account_id);
CREATE INDEX payment_methods_tenant_account_record_id ON payment_methods(tenant_record_id, account_record_id);
@@ -130,7 +130,7 @@ CREATE TABLE payment_method_history (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX payment_method_history_target_record_id ON payment_method_history(target_record_id);
CREATE INDEX payment_method_history_tenant_account_record_id ON payment_method_history(tenant_record_id, account_record_id);
@@ -151,7 +151,7 @@ CREATE TABLE refunds (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY (record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX refunds_id ON refunds(id);
CREATE INDEX refunds_pay ON refunds(payment_id);
CREATE INDEX refunds_accnt ON refunds(account_id);
@@ -176,7 +176,7 @@ CREATE TABLE refund_history (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX refund_history_target_record_id ON refund_history(target_record_id);
CREATE INDEX refund_history_tenant_account_record_id ON refund_history(tenant_record_id, account_record_id);
diff --git a/subscription/src/main/resources/com/ning/billing/subscription/ddl.sql b/subscription/src/main/resources/com/ning/billing/subscription/ddl.sql
index 0dafa01..314fae5 100644
--- a/subscription/src/main/resources/com/ning/billing/subscription/ddl.sql
+++ b/subscription/src/main/resources/com/ning/billing/subscription/ddl.sql
@@ -21,7 +21,7 @@ CREATE TABLE subscription_events (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX subscription_events_id ON subscription_events(id);
CREATE INDEX idx_ent_1 ON subscription_events(subscription_id, is_active, effective_date);
CREATE INDEX idx_ent_2 ON subscription_events(subscription_id, effective_date, created_date, requested_date,id);
@@ -45,7 +45,7 @@ CREATE TABLE subscriptions (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX subscriptions_id ON subscriptions(id);
CREATE INDEX subscriptions_bundle_id ON subscriptions(bundle_id);
CREATE INDEX subscriptions_tenant_account_record_id ON subscriptions(tenant_record_id, account_record_id);
@@ -65,7 +65,7 @@ CREATE TABLE bundles (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX bundles_id ON bundles(id);
CREATE INDEX bundles_key ON bundles(external_key);
CREATE INDEX bundles_account ON bundles(account_id);
diff --git a/tenant/src/main/resources/com/ning/billing/tenant/ddl.sql b/tenant/src/main/resources/com/ning/billing/tenant/ddl.sql
index 82b705d..807620d 100644
--- a/tenant/src/main/resources/com/ning/billing/tenant/ddl.sql
+++ b/tenant/src/main/resources/com/ning/billing/tenant/ddl.sql
@@ -13,7 +13,7 @@ CREATE TABLE tenants (
updated_date datetime DEFAULT NULL,
updated_by varchar(50) DEFAULT NULL,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX tenants_id ON tenants(id);
CREATE UNIQUE INDEX tenants_api_key ON tenants(api_key);
@@ -31,5 +31,5 @@ CREATE TABLE tenant_kvs (
updated_date datetime DEFAULT NULL,
updated_by varchar(50) DEFAULT NULL,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX tenant_kvs_key ON tenant_kvs(tenant_key);
diff --git a/usage/src/main/resources/com/ning/billing/usage/ddl.sql b/usage/src/main/resources/com/ning/billing/usage/ddl.sql
index 9422d50..c9c9011 100644
--- a/usage/src/main/resources/com/ning/billing/usage/ddl.sql
+++ b/usage/src/main/resources/com/ning/billing/usage/ddl.sql
@@ -14,7 +14,7 @@ CREATE TABLE rolled_up_usage (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX rolled_up_usage_id ON rolled_up_usage(id);
CREATE INDEX rolled_up_usage_subscription_id ON rolled_up_usage(subscription_id ASC);
CREATE INDEX rolled_up_usage_tenant_account_record_id ON rolled_up_usage(tenant_record_id, account_record_id);
diff --git a/util/src/main/java/com/ning/billing/util/audit/dao/DefaultAuditDao.java b/util/src/main/java/com/ning/billing/util/audit/dao/DefaultAuditDao.java
index a1887fe..1aa55e6 100644
--- a/util/src/main/java/com/ning/billing/util/audit/dao/DefaultAuditDao.java
+++ b/util/src/main/java/com/ning/billing/util/audit/dao/DefaultAuditDao.java
@@ -78,7 +78,7 @@ public class DefaultAuditDao implements AuditDao {
final List<AuditLog> allAuditLogs = transactionalSqlDao.execute(new EntitySqlDaoTransactionWrapper<List<AuditLog>>() {
@Override
public List<AuditLog> inTransaction(final EntitySqlDaoWrapperFactory<EntitySqlDao> entitySqlDaoWrapperFactory) throws Exception {
- return entitySqlDaoWrapperFactory.become(EntitySqlDao.class).getAuditLogsViaHistoryForTargetRecordId(historyTableName,
+ return entitySqlDaoWrapperFactory.become(EntitySqlDao.class).getAuditLogsViaHistoryForTargetRecordId(historyTableName.name(),
historyTableName.getTableName().toLowerCase(),
targetRecordId,
context);
@@ -91,7 +91,7 @@ public class DefaultAuditDao implements AuditDao {
final List<AuditLog> allAuditLogs = transactionalSqlDao.execute(new EntitySqlDaoTransactionWrapper<List<AuditLog>>() {
@Override
public List<AuditLog> inTransaction(final EntitySqlDaoWrapperFactory<EntitySqlDao> entitySqlDaoWrapperFactory) throws Exception {
- return entitySqlDaoWrapperFactory.become(EntitySqlDao.class).getAuditLogsForTargetRecordId(tableName,
+ return entitySqlDaoWrapperFactory.become(EntitySqlDao.class).getAuditLogsForTargetRecordId(tableName.name(),
targetRecordId,
context);
}
diff --git a/util/src/main/java/com/ning/billing/util/cache/AuditLogCacheLoader.java b/util/src/main/java/com/ning/billing/util/cache/AuditLogCacheLoader.java
index 90f8ad9..9075686 100644
--- a/util/src/main/java/com/ning/billing/util/cache/AuditLogCacheLoader.java
+++ b/util/src/main/java/com/ning/billing/util/cache/AuditLogCacheLoader.java
@@ -21,11 +21,10 @@ import javax.inject.Singleton;
import org.skife.jdbi.v2.IDBI;
-import com.ning.billing.util.cache.Cachable.CacheType;
import com.ning.billing.callcontext.InternalTenantContext;
+import com.ning.billing.util.cache.Cachable.CacheType;
import com.ning.billing.util.dao.AuditSqlDao;
import com.ning.billing.util.dao.NonEntityDao;
-import com.ning.billing.util.dao.TableName;
import net.sf.ehcache.loader.CacheLoader;
@@ -57,7 +56,7 @@ public class AuditLogCacheLoader extends BaseCacheLoader implements CacheLoader
}
final Object[] args = ((CacheLoaderArgument) argument).getArgs();
- final TableName tableName = (TableName) args[0];
+ final String tableName = (String) args[0];
final Long targetRecordId = (Long) args[1];
final InternalTenantContext internalTenantContext = (InternalTenantContext) args[2];
diff --git a/util/src/main/java/com/ning/billing/util/cache/AuditLogViaHistoryCacheLoader.java b/util/src/main/java/com/ning/billing/util/cache/AuditLogViaHistoryCacheLoader.java
index 1e6c759..4be9f90 100644
--- a/util/src/main/java/com/ning/billing/util/cache/AuditLogViaHistoryCacheLoader.java
+++ b/util/src/main/java/com/ning/billing/util/cache/AuditLogViaHistoryCacheLoader.java
@@ -21,11 +21,10 @@ import javax.inject.Singleton;
import org.skife.jdbi.v2.IDBI;
-import com.ning.billing.util.cache.Cachable.CacheType;
import com.ning.billing.callcontext.InternalTenantContext;
+import com.ning.billing.util.cache.Cachable.CacheType;
import com.ning.billing.util.dao.AuditSqlDao;
import com.ning.billing.util.dao.NonEntityDao;
-import com.ning.billing.util.dao.TableName;
import net.sf.ehcache.loader.CacheLoader;
@@ -57,7 +56,7 @@ public class AuditLogViaHistoryCacheLoader extends BaseCacheLoader implements Ca
}
final Object[] args = ((CacheLoaderArgument) argument).getArgs();
- final TableName tableName = (TableName) args[0];
+ final String tableName = (String) args[0];
final String historyTableName = (String) args[1];
final Long targetRecordId = (Long) args[2];
final InternalTenantContext internalTenantContext = (InternalTenantContext) args[3];
diff --git a/util/src/main/java/com/ning/billing/util/dao/AuditSqlDao.java b/util/src/main/java/com/ning/billing/util/dao/AuditSqlDao.java
index 8dbd553..d9f9b83 100644
--- a/util/src/main/java/com/ning/billing/util/dao/AuditSqlDao.java
+++ b/util/src/main/java/com/ning/billing/util/dao/AuditSqlDao.java
@@ -25,20 +25,22 @@ import org.skife.jdbi.v2.sqlobject.SqlUpdate;
import org.skife.jdbi.v2.sqlobject.customizers.Define;
import org.skife.jdbi.v2.sqlobject.customizers.RegisterMapper;
+import com.ning.billing.callcontext.InternalCallContext;
+import com.ning.billing.callcontext.InternalTenantContext;
import com.ning.billing.util.audit.AuditLog;
import com.ning.billing.util.cache.Cachable;
import com.ning.billing.util.cache.Cachable.CacheType;
import com.ning.billing.util.cache.CachableKey;
-import com.ning.billing.callcontext.InternalCallContext;
-import com.ning.billing.callcontext.InternalTenantContext;
import com.ning.billing.util.entity.dao.EntitySqlDaoStringTemplate;
/**
- * Note: cache invalidation has to happen for audit logs (which is tricky in the multi-nodes scenario).
+ * Note 1: cache invalidation has to happen for audit logs (which is tricky in the multi-nodes scenario).
* For now, we're using a time-based eviction strategy (see timeToIdleSeconds and timeToLiveSeconds in ehcache.xml)
* which is good enough: the cache will always get at least the initial CREATION audit log entry, which is the one
* we really care about (both for Analytics and for Kaui's endpoints). Besides, we do cache invalidation properly
* on our own node (see EntitySqlDaoWrapperInvocationHandler).
+ *
+ * Note 2: in the queries below, tableName always refers to the TableName enum, not the actual table name (TableName.getTableName()).
*/
@EntitySqlDaoStringTemplate("/com/ning/billing/util/entity/dao/EntitySqlDao.sql.stg")
@RegisterMapper(AuditLogMapper.class)
@@ -50,13 +52,13 @@ public interface AuditSqlDao {
@SqlQuery
@Cachable(CacheType.AUDIT_LOG)
- public List<AuditLog> getAuditLogsForTargetRecordId(@CachableKey(1) @BindBean final TableName tableName,
+ public List<AuditLog> getAuditLogsForTargetRecordId(@CachableKey(1) @Bind("tableName") final String tableName,
@CachableKey(2) @Bind("targetRecordId") final long targetRecordId,
@BindBean final InternalTenantContext context);
@SqlQuery
@Cachable(CacheType.AUDIT_LOG_VIA_HISTORY)
- public List<AuditLog> getAuditLogsViaHistoryForTargetRecordId(@CachableKey(1) @BindBean final TableName historyTableName, /* Uppercased - used to find entries in audit_log table */
+ public List<AuditLog> getAuditLogsViaHistoryForTargetRecordId(@CachableKey(1) @Bind("tableName") final String historyTableName, /* Uppercased - used to find entries in audit_log table */
@CachableKey(2) @Define("historyTableName") final String actualHistoryTableName, /* Actual table name, used in the inner join query */
@CachableKey(3) @Bind("targetRecordId") final long targetRecordId,
@BindBean final InternalTenantContext context);
diff --git a/util/src/main/resources/com/ning/billing/util/ddl.sql b/util/src/main/resources/com/ning/billing/util/ddl.sql
index b586ec2..84e9491 100644
--- a/util/src/main/resources/com/ning/billing/util/ddl.sql
+++ b/util/src/main/resources/com/ning/billing/util/ddl.sql
@@ -15,7 +15,7 @@ CREATE TABLE custom_fields (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX custom_fields_id ON custom_fields(id);
CREATE INDEX custom_fields_object_id_object_type ON custom_fields(object_id, object_type);
CREATE UNIQUE INDEX custom_fields_unique ON custom_fields(object_id, object_type, field_name);
@@ -38,7 +38,7 @@ CREATE TABLE custom_field_history (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX custom_field_history_target_record_id ON custom_field_history(target_record_id);
CREATE INDEX custom_field_history_object_id_object_type ON custom_fields(object_id, object_type);
CREATE INDEX custom_field_history_tenant_account_record_id ON custom_field_history(tenant_record_id, account_record_id);
@@ -56,7 +56,7 @@ CREATE TABLE tag_definitions (
updated_date datetime NOT NULL,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX tag_definitions_id ON tag_definitions(id);
CREATE INDEX tag_definitions_tenant_record_id ON tag_definitions(tenant_record_id);
@@ -76,7 +76,7 @@ CREATE TABLE tag_definition_history (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX tag_definition_history_id ON tag_definition_history(id);
CREATE INDEX tag_definition_history_target_record_id ON tag_definition_history(target_record_id);
CREATE INDEX tag_definition_history_name ON tag_definition_history(name);
@@ -97,7 +97,7 @@ CREATE TABLE tags (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE UNIQUE INDEX tags_id ON tags(id);
CREATE INDEX tags_by_object ON tags(object_id);
CREATE INDEX tags_tenant_account_record_id ON tags(tenant_record_id, account_record_id);
@@ -119,14 +119,12 @@ CREATE TABLE tag_history (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX tag_history_target_record_id ON tag_history(target_record_id);
CREATE INDEX tag_history_by_object ON tags(object_id);
CREATE INDEX tag_history_tenant_account_record_id ON tag_history(tenant_record_id, account_record_id);
DROP TABLE IF EXISTS audit_log;
-/*! SET storage_engine=INNODB */;
-
CREATE TABLE audit_log (
record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
id char(36) NOT NULL,
@@ -141,7 +139,7 @@ CREATE TABLE audit_log (
account_record_id int(11) unsigned default null,
tenant_record_id int(11) unsigned default null,
PRIMARY KEY(record_id)
-);
+) CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX audit_log_fetch_target_record_id ON audit_log(table_name, target_record_id);
CREATE INDEX audit_log_user_name ON audit_log(created_by);
CREATE INDEX audit_log_tenant_account_record_id ON audit_log(tenant_record_id, account_record_id);
@@ -150,81 +148,82 @@ CREATE INDEX audit_log_via_history ON audit_log(target_record_id, table_name, te
DROP TABLE IF EXISTS notifications;
- CREATE TABLE notifications (
- record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
- class_name varchar(256) NOT NULL,
- event_json varchar(2048) NOT NULL,
- user_token char(36),
- created_date datetime NOT NULL,
- creating_owner char(50) NOT NULL,
- processing_owner char(50) DEFAULT NULL,
- processing_available_date datetime DEFAULT NULL,
- processing_state varchar(14) DEFAULT 'AVAILABLE',
- search_key1 int(11) unsigned default null,
- search_key2 int(11) unsigned default null,
- queue_name char(64) NOT NULL,
- effective_date datetime NOT NULL,
- future_user_token char(36),
- PRIMARY KEY(record_id)
- );
- CREATE INDEX `idx_comp_where` ON notifications (`effective_date`, `processing_state`,`processing_owner`,`processing_available_date`);
- CREATE INDEX `idx_update` ON notifications (`processing_state`,`processing_owner`,`processing_available_date`);
- CREATE INDEX `idx_get_ready` ON notifications (`effective_date`,`created_date`);
- CREATE INDEX notifications_tenant_account_record_id ON notifications(search_key2, search_key1);
+CREATE TABLE notifications (
+ record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
+ class_name varchar(256) NOT NULL,
+ event_json varchar(2048) NOT NULL,
+ user_token char(36),
+ created_date datetime NOT NULL,
+ creating_owner char(50) NOT NULL,
+ processing_owner char(50) DEFAULT NULL,
+ processing_available_date datetime DEFAULT NULL,
+ processing_state varchar(14) DEFAULT 'AVAILABLE',
+ search_key1 int(11) unsigned default null,
+ search_key2 int(11) unsigned default null,
+ queue_name char(64) NOT NULL,
+ effective_date datetime NOT NULL,
+ future_user_token char(36),
+ PRIMARY KEY(record_id)
+) CHARACTER SET utf8 COLLATE utf8_bin;
+CREATE INDEX `idx_comp_where` ON notifications (`effective_date`, `processing_state`,`processing_owner`,`processing_available_date`);
+CREATE INDEX `idx_update` ON notifications (`processing_state`,`processing_owner`,`processing_available_date`);
+CREATE INDEX `idx_get_ready` ON notifications (`effective_date`,`created_date`);
+CREATE INDEX notifications_tenant_account_record_id ON notifications(search_key2, search_key1);
- DROP TABLE IF EXISTS notifications_history;
- CREATE TABLE notifications_history (
- record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
- class_name varchar(256) NOT NULL,
- event_json varchar(2048) NOT NULL,
- user_token char(36),
- created_date datetime NOT NULL,
- creating_owner char(50) NOT NULL,
- processing_owner char(50) DEFAULT NULL,
- processing_available_date datetime DEFAULT NULL,
- processing_state varchar(14) DEFAULT 'AVAILABLE',
- search_key1 int(11) unsigned default null,
- search_key2 int(11) unsigned default null,
- queue_name char(64) NOT NULL,
- effective_date datetime NOT NULL,
- future_user_token char(36),
- PRIMARY KEY(record_id)
- );
+DROP TABLE IF EXISTS notifications_history;
+CREATE TABLE notifications_history (
+ record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
+ class_name varchar(256) NOT NULL,
+ event_json varchar(2048) NOT NULL,
+ user_token char(36),
+ created_date datetime NOT NULL,
+ creating_owner char(50) NOT NULL,
+ processing_owner char(50) DEFAULT NULL,
+ processing_available_date datetime DEFAULT NULL,
+ processing_state varchar(14) DEFAULT 'AVAILABLE',
+ search_key1 int(11) unsigned default null,
+ search_key2 int(11) unsigned default null,
+ queue_name char(64) NOT NULL,
+ effective_date datetime NOT NULL,
+ future_user_token char(36),
+ PRIMARY KEY(record_id)
+) CHARACTER SET utf8 COLLATE utf8_bin;
- DROP TABLE IF EXISTS bus_events;
- CREATE TABLE bus_events (
- record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
- class_name varchar(128) NOT NULL,
- event_json varchar(2048) NOT NULL,
- user_token char(36),
- created_date datetime NOT NULL,
- creating_owner char(50) NOT NULL,
- processing_owner char(50) DEFAULT NULL,
- processing_available_date datetime DEFAULT NULL,
- processing_state varchar(14) DEFAULT 'AVAILABLE',
- search_key1 int(11) unsigned default null,
- search_key2 int(11) unsigned default null,
- PRIMARY KEY(record_id)
- );
- CREATE INDEX `idx_bus_where` ON bus_events (`processing_state`,`processing_owner`,`processing_available_date`);
- CREATE INDEX bus_events_tenant_account_record_id ON bus_events(search_key2, search_key1);
+DROP TABLE IF EXISTS bus_events;
+CREATE TABLE bus_events (
+ record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
+ class_name varchar(128) NOT NULL,
+ event_json varchar(2048) NOT NULL,
+ user_token char(36),
+ created_date datetime NOT NULL,
+ creating_owner char(50) NOT NULL,
+ processing_owner char(50) DEFAULT NULL,
+ processing_available_date datetime DEFAULT NULL,
+ processing_state varchar(14) DEFAULT 'AVAILABLE',
+ search_key1 int(11) unsigned default null,
+ search_key2 int(11) unsigned default null,
+ PRIMARY KEY(record_id)
+) CHARACTER SET utf8 COLLATE utf8_bin;
+CREATE INDEX `idx_bus_where` ON bus_events (`processing_state`,`processing_owner`,`processing_available_date`);
+CREATE INDEX bus_events_tenant_account_record_id ON bus_events(search_key2, search_key1);
- DROP TABLE IF EXISTS bus_events_history;
- CREATE TABLE bus_events_history (
- record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
- class_name varchar(128) NOT NULL,
- event_json varchar(2048) NOT NULL,
- user_token char(36),
- created_date datetime NOT NULL,
- creating_owner char(50) NOT NULL,
- processing_owner char(50) DEFAULT NULL,
- processing_available_date datetime DEFAULT NULL,
- processing_state varchar(14) DEFAULT 'AVAILABLE',
- search_key1 int(11) unsigned default null,
- search_key2 int(11) unsigned default null,
- PRIMARY KEY(record_id)
- );
+DROP TABLE IF EXISTS bus_events_history;
+CREATE TABLE bus_events_history (
+ record_id int(11) unsigned NOT NULL AUTO_INCREMENT,
+ class_name varchar(128) NOT NULL,
+ event_json varchar(2048) NOT NULL,
+ user_token char(36),
+ created_date datetime NOT NULL,
+ creating_owner char(50) NOT NULL,
+ processing_owner char(50) DEFAULT NULL,
+ processing_available_date datetime DEFAULT NULL,
+ processing_state varchar(14) DEFAULT 'AVAILABLE',
+ search_key1 int(11) unsigned default null,
+ search_key2 int(11) unsigned default null,
+ PRIMARY KEY(record_id)
+) CHARACTER SET utf8 COLLATE utf8_bin;
+drop table if exists sessions;
create table sessions (
record_id int(11) unsigned not null auto_increment
, start_timestamp datetime not null
@@ -233,4 +232,4 @@ create table sessions (
, host varchar(100) default null
, session_data mediumblob default null
, primary key(record_id)
-);
\ No newline at end of file
+) character set utf8 collate utf8_bin;
\ No newline at end of file
