From 59ea832c8ec4c462fa13323ab1310eb47ffce590 Mon Sep 17 00:00:00 2001
From: garvinhicking <garvinhicking>
Date: Sun, 14 Jan 2007 14:47:52 +0000
Subject: [PATCH] Prepare DB structure upgrade

---
 sql/db.sql                                    |  4 ++-
 sql/db_update_1.2-alpha2_1.1-alpha3_mysql.sql |  2 ++
 ..._update_1.2-alpha2_1.1-alpha3_postgres.sql |  3 ++
 ...db_update_1.2-alpha2_1.1-alpha3_sqlite.sql | 36 +++++++++++++++++++
 4 files changed, 44 insertions(+), 1 deletion(-)
 create mode 100644 sql/db_update_1.2-alpha2_1.1-alpha3_mysql.sql
 create mode 100644 sql/db_update_1.2-alpha2_1.1-alpha3_postgres.sql
 create mode 100644 sql/db_update_1.2-alpha2_1.1-alpha3_sqlite.sql

diff --git a/sql/db.sql b/sql/db.sql
index 5fc651a..219e466 100644
--- a/sql/db.sql
+++ b/sql/db.sql
@@ -217,12 +217,14 @@ CREATE TABLE {PREFIX}category (
   authorid int(11) default NULL,
   category_left int(11) default '0',
   category_right int(11) default '0',
-  parentid int(11) DEFAULT '0' NOT NULL
+  parentid int(11) DEFAULT '0' NOT NULL,
+  sort_order int(11)
 ) {UTF_8};
 
 CREATE INDEX categorya_idx ON {PREFIX}category (authorid);
 CREATE INDEX categoryp_idx ON {PREFIX}category (parentid);
 CREATE INDEX categorylr_idx ON {PREFIX}category (category_left, category_right);
+CREATE INDEX categoryso_idx ON {PREFIX}category (sort_order);
 
 CREATE TABLE {PREFIX}images (
   id {AUTOINCREMENT} {PRIMARY},
diff --git a/sql/db_update_1.2-alpha2_1.1-alpha3_mysql.sql b/sql/db_update_1.2-alpha2_1.1-alpha3_mysql.sql
new file mode 100644
index 0000000..bee1af4
--- /dev/null
+++ b/sql/db_update_1.2-alpha2_1.1-alpha3_mysql.sql
@@ -0,0 +1,2 @@
+ALTER TABLE {PREFIX}category ADD COLUMN sort_order int(11);
+CREATE INDEX categoryso_idx ON {PREFIX}category (sort_order);
diff --git a/sql/db_update_1.2-alpha2_1.1-alpha3_postgres.sql b/sql/db_update_1.2-alpha2_1.1-alpha3_postgres.sql
new file mode 100644
index 0000000..4a1fc9a
--- /dev/null
+++ b/sql/db_update_1.2-alpha2_1.1-alpha3_postgres.sql
@@ -0,0 +1,3 @@
+ALTER TABLE {PREFIX}category ADD COLUMN sort_order int(11);
+CREATE INDEX categoryso_idx ON {PREFIX}category (sort_order);
+
diff --git a/sql/db_update_1.2-alpha2_1.1-alpha3_sqlite.sql b/sql/db_update_1.2-alpha2_1.1-alpha3_sqlite.sql
new file mode 100644
index 0000000..0dcb935
--- /dev/null
+++ b/sql/db_update_1.2-alpha2_1.1-alpha3_sqlite.sql
@@ -0,0 +1,36 @@
+CREATE TABLE {PREFIX}tempcategory (
+  categoryid {AUTOINCREMENT} {PRIMARY},
+  category_name varchar(255) default NULL,
+  category_icon varchar(255) default NULL,
+  category_description text,
+  authorid int(11) default NULL,
+  category_left int(11) default '0',
+  category_right int(11) default '0',
+  parentid int(11) DEFAULT '0' NOT NULL,
+  sort_order int(11)
+) {UTF_8};
+
+INSERT INTO {PREFIX}tempcategory (categoryid, category_name, category_icon, category_description, authorid, category_left, category_right, parentid) SELECT categoryid, category_name, category_icon, category_description, authorid, category_left, category_right, parentid FROM {PREFIX}category;
+DROP TABLE {PREFIX}category;
+
+CREATE TABLE {PREFIX}category (
+  categoryid {AUTOINCREMENT} {PRIMARY},
+  category_name varchar(255) default NULL,
+  category_icon varchar(255) default NULL,
+  category_description text,
+  authorid int(11) default NULL,
+  category_left int(11) default '0',
+  category_right int(11) default '0',
+  parentid int(11) DEFAULT '0' NOT NULL,
+  sort_order int(11)
+) {UTF_8};
+
+CREATE INDEX categorya_idx ON {PREFIX}category (authorid);
+CREATE INDEX categoryp_idx ON {PREFIX}category (parentid);
+CREATE INDEX categorylr_idx ON {PREFIX}category (category_left, category_right);
+CREATE INDEX categoryso_idx ON {PREFIX}category (sort_order);
+
+
+INSERT INTO {PREFIX}category (categoryid, category_name, category_icon, category_description, authorid, category_left, category_right, parentid) SELECT categoryid, category_name, category_icon, category_description, authorid, category_left, category_right, parentid FROM {PREFIX}tempcategory;
+DROP TABLE {PREFIX}tempcategory;
+
-- 
2.39.5