From: Marcus Eggenberger <egs@quassel-irc.org>
Date: Sun, 2 Mar 2008 13:25:31 +0000 (+0000)
Subject: improved backlog replay performance
X-Git-Tag: 0.2.0-alpha3~31
X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=cd0868fff76dd067470881134fd736e1a1d97e71

improved backlog replay performance
---

diff --git a/src/core/SQL/SQLite/8/setup_999_version.sql b/src/core/SQL/SQLite/8/setup_999_version.sql
deleted file mode 100644
index a65213b8..00000000
--- a/src/core/SQL/SQLite/8/setup_999_version.sql
+++ /dev/null
@@ -1 +0,0 @@
-INSERT INTO coreinfo (key, value) VALUES ('schemaversion', '8')
diff --git a/src/core/SQL/SQLite/8/delete_backlog_by_uid.sql b/src/core/SQL/SQLite/9/delete_backlog_by_uid.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_backlog_by_uid.sql
rename to src/core/SQL/SQLite/9/delete_backlog_by_uid.sql
diff --git a/src/core/SQL/SQLite/8/delete_backlog_for_buffer.sql b/src/core/SQL/SQLite/9/delete_backlog_for_buffer.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_backlog_for_buffer.sql
rename to src/core/SQL/SQLite/9/delete_backlog_for_buffer.sql
diff --git a/src/core/SQL/SQLite/8/delete_backlog_for_network.sql b/src/core/SQL/SQLite/9/delete_backlog_for_network.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_backlog_for_network.sql
rename to src/core/SQL/SQLite/9/delete_backlog_for_network.sql
diff --git a/src/core/SQL/SQLite/8/delete_buffer_for_bufferid.sql b/src/core/SQL/SQLite/9/delete_buffer_for_bufferid.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_buffer_for_bufferid.sql
rename to src/core/SQL/SQLite/9/delete_buffer_for_bufferid.sql
diff --git a/src/core/SQL/SQLite/8/delete_buffers_by_uid.sql b/src/core/SQL/SQLite/9/delete_buffers_by_uid.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_buffers_by_uid.sql
rename to src/core/SQL/SQLite/9/delete_buffers_by_uid.sql
diff --git a/src/core/SQL/SQLite/8/delete_buffers_for_network.sql b/src/core/SQL/SQLite/9/delete_buffers_for_network.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_buffers_for_network.sql
rename to src/core/SQL/SQLite/9/delete_buffers_for_network.sql
diff --git a/src/core/SQL/SQLite/8/delete_ircservers_for_network.sql b/src/core/SQL/SQLite/9/delete_ircservers_for_network.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_ircservers_for_network.sql
rename to src/core/SQL/SQLite/9/delete_ircservers_for_network.sql
diff --git a/src/core/SQL/SQLite/8/delete_network.sql b/src/core/SQL/SQLite/9/delete_network.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_network.sql
rename to src/core/SQL/SQLite/9/delete_network.sql
diff --git a/src/core/SQL/SQLite/8/delete_networks_by_uid.sql b/src/core/SQL/SQLite/9/delete_networks_by_uid.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_networks_by_uid.sql
rename to src/core/SQL/SQLite/9/delete_networks_by_uid.sql
diff --git a/src/core/SQL/SQLite/8/delete_quasseluser.sql b/src/core/SQL/SQLite/9/delete_quasseluser.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/delete_quasseluser.sql
rename to src/core/SQL/SQLite/9/delete_quasseluser.sql
diff --git a/src/core/SQL/SQLite/8/insert_buffer.sql b/src/core/SQL/SQLite/9/insert_buffer.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/insert_buffer.sql
rename to src/core/SQL/SQLite/9/insert_buffer.sql
diff --git a/src/core/SQL/SQLite/8/insert_message.sql b/src/core/SQL/SQLite/9/insert_message.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/insert_message.sql
rename to src/core/SQL/SQLite/9/insert_message.sql
diff --git a/src/core/SQL/SQLite/8/insert_network.sql b/src/core/SQL/SQLite/9/insert_network.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/insert_network.sql
rename to src/core/SQL/SQLite/9/insert_network.sql
diff --git a/src/core/SQL/SQLite/8/insert_quasseluser.sql b/src/core/SQL/SQLite/9/insert_quasseluser.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/insert_quasseluser.sql
rename to src/core/SQL/SQLite/9/insert_quasseluser.sql
diff --git a/src/core/SQL/SQLite/8/insert_sender.sql b/src/core/SQL/SQLite/9/insert_sender.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/insert_sender.sql
rename to src/core/SQL/SQLite/9/insert_sender.sql
diff --git a/src/core/SQL/SQLite/8/insert_server.sql b/src/core/SQL/SQLite/9/insert_server.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/insert_server.sql
rename to src/core/SQL/SQLite/9/insert_server.sql
diff --git a/src/core/SQL/SQLite/8/select_authuser.sql b/src/core/SQL/SQLite/9/select_authuser.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_authuser.sql
rename to src/core/SQL/SQLite/9/select_authuser.sql
diff --git a/src/core/SQL/SQLite/8/select_bufferByName.sql b/src/core/SQL/SQLite/9/select_bufferByName.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_bufferByName.sql
rename to src/core/SQL/SQLite/9/select_bufferByName.sql
diff --git a/src/core/SQL/SQLite/8/select_bufferExists.sql b/src/core/SQL/SQLite/9/select_bufferExists.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_bufferExists.sql
rename to src/core/SQL/SQLite/9/select_bufferExists.sql
diff --git a/src/core/SQL/SQLite/8/select_buffer_by_id.sql b/src/core/SQL/SQLite/9/select_buffer_by_id.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_buffer_by_id.sql
rename to src/core/SQL/SQLite/9/select_buffer_by_id.sql
diff --git a/src/core/SQL/SQLite/8/select_buffer_lastseen_dates.sql b/src/core/SQL/SQLite/9/select_buffer_lastseen_dates.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_buffer_lastseen_dates.sql
rename to src/core/SQL/SQLite/9/select_buffer_lastseen_dates.sql
diff --git a/src/core/SQL/SQLite/8/select_buffers.sql b/src/core/SQL/SQLite/9/select_buffers.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_buffers.sql
rename to src/core/SQL/SQLite/9/select_buffers.sql
diff --git a/src/core/SQL/SQLite/8/select_connected_networks.sql b/src/core/SQL/SQLite/9/select_connected_networks.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_connected_networks.sql
rename to src/core/SQL/SQLite/9/select_connected_networks.sql
diff --git a/src/core/SQL/SQLite/8/select_messageRange.sql b/src/core/SQL/SQLite/9/select_messageRange.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_messageRange.sql
rename to src/core/SQL/SQLite/9/select_messageRange.sql
diff --git a/src/core/SQL/SQLite/8/select_messages.sql b/src/core/SQL/SQLite/9/select_messages.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_messages.sql
rename to src/core/SQL/SQLite/9/select_messages.sql
diff --git a/src/core/SQL/SQLite/8/select_messagesOffset.sql b/src/core/SQL/SQLite/9/select_messagesOffset.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_messagesOffset.sql
rename to src/core/SQL/SQLite/9/select_messagesOffset.sql
diff --git a/src/core/SQL/SQLite/8/select_messagesSince.sql b/src/core/SQL/SQLite/9/select_messagesSince.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_messagesSince.sql
rename to src/core/SQL/SQLite/9/select_messagesSince.sql
diff --git a/src/core/SQL/SQLite/8/select_messagesSinceOffset.sql b/src/core/SQL/SQLite/9/select_messagesSinceOffset.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_messagesSinceOffset.sql
rename to src/core/SQL/SQLite/9/select_messagesSinceOffset.sql
diff --git a/src/core/SQL/SQLite/8/select_networkExists.sql b/src/core/SQL/SQLite/9/select_networkExists.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_networkExists.sql
rename to src/core/SQL/SQLite/9/select_networkExists.sql
diff --git a/src/core/SQL/SQLite/8/select_networks_for_user.sql b/src/core/SQL/SQLite/9/select_networks_for_user.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_networks_for_user.sql
rename to src/core/SQL/SQLite/9/select_networks_for_user.sql
diff --git a/src/core/SQL/SQLite/8/select_persistent_channels.sql b/src/core/SQL/SQLite/9/select_persistent_channels.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_persistent_channels.sql
rename to src/core/SQL/SQLite/9/select_persistent_channels.sql
diff --git a/src/core/SQL/SQLite/8/select_servers_for_network.sql b/src/core/SQL/SQLite/9/select_servers_for_network.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_servers_for_network.sql
rename to src/core/SQL/SQLite/9/select_servers_for_network.sql
diff --git a/src/core/SQL/SQLite/8/select_userid.sql b/src/core/SQL/SQLite/9/select_userid.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/select_userid.sql
rename to src/core/SQL/SQLite/9/select_userid.sql
diff --git a/src/core/SQL/SQLite/8/setup_000_quasseluser.sql b/src/core/SQL/SQLite/9/setup_000_quasseluser.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_000_quasseluser.sql
rename to src/core/SQL/SQLite/9/setup_000_quasseluser.sql
diff --git a/src/core/SQL/SQLite/8/setup_010_sender.sql b/src/core/SQL/SQLite/9/setup_010_sender.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_010_sender.sql
rename to src/core/SQL/SQLite/9/setup_010_sender.sql
diff --git a/src/core/SQL/SQLite/8/setup_020_network.sql b/src/core/SQL/SQLite/9/setup_020_network.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_020_network.sql
rename to src/core/SQL/SQLite/9/setup_020_network.sql
diff --git a/src/core/SQL/SQLite/8/setup_030_buffer.sql b/src/core/SQL/SQLite/9/setup_030_buffer.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_030_buffer.sql
rename to src/core/SQL/SQLite/9/setup_030_buffer.sql
diff --git a/src/core/SQL/SQLite/8/setup_040_buffer_idx.sql b/src/core/SQL/SQLite/9/setup_040_buffer_idx.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_040_buffer_idx.sql
rename to src/core/SQL/SQLite/9/setup_040_buffer_idx.sql
diff --git a/src/core/SQL/SQLite/8/setup_050_buffer_cname_idx.sql b/src/core/SQL/SQLite/9/setup_050_buffer_cname_idx.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_050_buffer_cname_idx.sql
rename to src/core/SQL/SQLite/9/setup_050_buffer_cname_idx.sql
diff --git a/src/core/SQL/SQLite/8/setup_060_backlog.sql b/src/core/SQL/SQLite/9/setup_060_backlog.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_060_backlog.sql
rename to src/core/SQL/SQLite/9/setup_060_backlog.sql
diff --git a/src/core/SQL/SQLite/8/setup_070_coreinfo.sql b/src/core/SQL/SQLite/9/setup_070_coreinfo.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_070_coreinfo.sql
rename to src/core/SQL/SQLite/9/setup_070_coreinfo.sql
diff --git a/src/core/SQL/SQLite/8/setup_080_ircservers.sql b/src/core/SQL/SQLite/9/setup_080_ircservers.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/setup_080_ircservers.sql
rename to src/core/SQL/SQLite/9/setup_080_ircservers.sql
diff --git a/src/core/SQL/SQLite/9/setup_090_create_backlog_idx.sql b/src/core/SQL/SQLite/9/setup_090_create_backlog_idx.sql
new file mode 100644
index 00000000..a91b59a2
--- /dev/null
+++ b/src/core/SQL/SQLite/9/setup_090_create_backlog_idx.sql
@@ -0,0 +1 @@
+CREATE INDEX backlog_bufferid_idx ON backlog(bufferid)
diff --git a/src/core/SQL/SQLite/9/setup_100_create_backlog_idx2.sql b/src/core/SQL/SQLite/9/setup_100_create_backlog_idx2.sql
new file mode 100644
index 00000000..b5156241
--- /dev/null
+++ b/src/core/SQL/SQLite/9/setup_100_create_backlog_idx2.sql
@@ -0,0 +1 @@
+CREATE INDEX backlog_buffer_time_idx ON backlog (bufferid, time)
diff --git a/src/core/SQL/SQLite/9/setup_110_create_buffer_idx.sql b/src/core/SQL/SQLite/9/setup_110_create_buffer_idx.sql
new file mode 100644
index 00000000..cb55f1da
--- /dev/null
+++ b/src/core/SQL/SQLite/9/setup_110_create_buffer_idx.sql
@@ -0,0 +1 @@
+CREATE INDEX buffer_user_idx ON buffer(userid)
diff --git a/src/core/SQL/SQLite/9/setup_999_version.sql b/src/core/SQL/SQLite/9/setup_999_version.sql
new file mode 100644
index 00000000..ab6551ec
--- /dev/null
+++ b/src/core/SQL/SQLite/9/setup_999_version.sql
@@ -0,0 +1 @@
+INSERT INTO coreinfo (key, value) VALUES ('schemaversion', '9')
diff --git a/src/core/SQL/SQLite/8/update_buffer_lastseen.sql b/src/core/SQL/SQLite/9/update_buffer_lastseen.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/update_buffer_lastseen.sql
rename to src/core/SQL/SQLite/9/update_buffer_lastseen.sql
diff --git a/src/core/SQL/SQLite/8/update_buffer_name.sql b/src/core/SQL/SQLite/9/update_buffer_name.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/update_buffer_name.sql
rename to src/core/SQL/SQLite/9/update_buffer_name.sql
diff --git a/src/core/SQL/SQLite/8/update_buffer_persistent_channel.sql b/src/core/SQL/SQLite/9/update_buffer_persistent_channel.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/update_buffer_persistent_channel.sql
rename to src/core/SQL/SQLite/9/update_buffer_persistent_channel.sql
diff --git a/src/core/SQL/SQLite/8/update_buffer_set_channel_key.sql b/src/core/SQL/SQLite/9/update_buffer_set_channel_key.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/update_buffer_set_channel_key.sql
rename to src/core/SQL/SQLite/9/update_buffer_set_channel_key.sql
diff --git a/src/core/SQL/SQLite/8/update_network.sql b/src/core/SQL/SQLite/9/update_network.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/update_network.sql
rename to src/core/SQL/SQLite/9/update_network.sql
diff --git a/src/core/SQL/SQLite/8/update_network_connected.sql b/src/core/SQL/SQLite/9/update_network_connected.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/update_network_connected.sql
rename to src/core/SQL/SQLite/9/update_network_connected.sql
diff --git a/src/core/SQL/SQLite/8/update_username.sql b/src/core/SQL/SQLite/9/update_username.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/update_username.sql
rename to src/core/SQL/SQLite/9/update_username.sql
diff --git a/src/core/SQL/SQLite/8/update_userpassword.sql b/src/core/SQL/SQLite/9/update_userpassword.sql
similarity index 100%
rename from src/core/SQL/SQLite/8/update_userpassword.sql
rename to src/core/SQL/SQLite/9/update_userpassword.sql
diff --git a/src/core/SQL/SQLite/9/upgrade_000_create_backlog_idx.sql b/src/core/SQL/SQLite/9/upgrade_000_create_backlog_idx.sql
new file mode 100644
index 00000000..a91b59a2
--- /dev/null
+++ b/src/core/SQL/SQLite/9/upgrade_000_create_backlog_idx.sql
@@ -0,0 +1 @@
+CREATE INDEX backlog_bufferid_idx ON backlog(bufferid)
diff --git a/src/core/SQL/SQLite/9/upgrade_010_create_backlog_idx2.sql b/src/core/SQL/SQLite/9/upgrade_010_create_backlog_idx2.sql
new file mode 100644
index 00000000..b5156241
--- /dev/null
+++ b/src/core/SQL/SQLite/9/upgrade_010_create_backlog_idx2.sql
@@ -0,0 +1 @@
+CREATE INDEX backlog_buffer_time_idx ON backlog (bufferid, time)
diff --git a/src/core/SQL/SQLite/9/upgrade_020_create_buffer_idx.sql b/src/core/SQL/SQLite/9/upgrade_020_create_buffer_idx.sql
new file mode 100644
index 00000000..cb55f1da
--- /dev/null
+++ b/src/core/SQL/SQLite/9/upgrade_020_create_buffer_idx.sql
@@ -0,0 +1 @@
+CREATE INDEX buffer_user_idx ON buffer(userid)
diff --git a/src/core/SQL/SQLite/9/upgrade_999_version.sql b/src/core/SQL/SQLite/9/upgrade_999_version.sql
new file mode 100644
index 00000000..bdeb8fed
--- /dev/null
+++ b/src/core/SQL/SQLite/9/upgrade_999_version.sql
@@ -0,0 +1,3 @@
+UPDATE coreinfo
+SET value = '9'
+WHERE key = 'schemaversion'
diff --git a/version.inc b/version.inc
index d9fdf791..4ee7731a 100644
--- a/version.inc
+++ b/version.inc
@@ -5,7 +5,7 @@
 
   quasselVersion = "0.2.0-alpha3-pre";
   quasselDate = "2008-03-02";
-  quasselBuild = 609;
+  quasselBuild = 610;
 
   //! Minimum client build number the core needs
   clientBuildNeeded = 605;