0.7 => 0.8 upgrade with postgres

[David]

After following the upgrade steps -

When I attempt to log in i get:

Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[42703]: Undefined column: 7 ERROR: column "ip" of relation
"habari__sessions" does not exist
LINE 1: INSERT INTO habari__sessions ( data, expires, ip, token,
ua ...
^ in system/
classes/databaseconnection.php line 273

Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer: "70.98.171.2" in {path}/habari/system/classes/
databaseconnection.php on line 273

Warning: PDO::lastInsertId() [function.PDO-lastInsertId]:
SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval of
sequence "habari__log_pkey_seq" is not yet defined in this session in
{path}/habari/system/classes/databaseconnection.php on line 680

Followed by:
Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer: "70.98.171.2" in system/classes/
databaseconnection.php line 273

Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer: "70.98.171.2" in {path}/habari/system/classes/
databaseconnection.php on line 273

Warning: PDO::lastInsertId() [function.PDO-lastInsertId]:
SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval of
sequence "habari__log_pkey_seq" is not yet defined in this session in
{path}/habari/system/classes/databaseconnection.php on line 680

Warning: PDO::lastInsertId() [function.PDO-lastInsertId]:
SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval
of sequence "habari__log_pkey_seq" is not yet defined in this session
in system/classes/databaseconnection.php line 680

Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer: "70.98.171.2" in {path}/habari/system/classes/
databaseconnection.php on line 273

Warning: PDO::lastInsertId() [function.PDO-lastInsertId]:
SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval of
sequence "habari__log_pkey_seq" is not yet defined in this session in
{path}/habari/system/classes/databaseconnection.php on line 680

[Andy C]

Hmm - on the face of it, it looks like there should be a new PG schema update file created in

system/classes/pgsql/upgrades/post/XXXXX

This file, as a minimum, needs to include

ALTER TABLE {$prefix}sessions ADD COLUMN ip VARCHAR(45);

However, this column is defined as mandatory (NOT NULL) so I don't know what dummy value 
we could place there for pre-existing data.

This sequence also needs to be created in the schema upgrade script...

CREATE SEQUENCE {$prefix}log_pkey_seq;

..and possibly this table definition needs changing as a consequence.

CREATE TABLE {$prefix}log (
  id BIGINT NOT NULL DEFAULT nextval('{$prefix}log_pkey_seq'),
  user_id INTEGER NULL DEFAULT NULL,
  type_id INTEGER NOT NULL,
  severity_id SMALLINT NOT NULL,
  message VARCHAR(255) NOT NULL,
  data BYTEA NULL,
  timestamp INT NOT NULL,
  ip VARCHAR(45) NOT NULL,
  PRIMARY KEY (id)
);

Hopefully, someone with more detailed knowledge of PG can make these changes which hopefully should let to progress further.

However, as the last PG schema upgrade script is dated April 2009, it is possible there are a few other schema changes that still need to be added.
--
Andy

[David]

I've created a schema.sql and upgrade.sql script to move from Habari
0.7 to 0.8. for postgres

I don't know how to name the script so that it runs via schema/pgsql/
upgrades/post/XXXX.sql

Also, i can't seem to attach files here i'll cut'n'paste and hope that
the formatting doesn't get messed up.

====schema.sql=====
CREATE SEQUENCE {$prefix}posts_pkey_seq;
CREATE TABLE {$prefix}posts (
id BIGINT NOT NULL DEFAULT nextval('{$prefix}posts_pkey_seq'),
slug VARCHAR(255) NOT NULL,
content_type INTEGER NOT NULL,
title VARCHAR(255) NOT NULL,
guid VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
cached_content TEXT NOT NULL,
user_id INTEGER NOT NULL,
status INTEGER NOT NULL,
pubdate INTEGER NOT NULL,
updated INTEGER NOT NULL,
modified INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (slug)
);

CREATE TABLE {$prefix}postinfo (
post_id BIGINT NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT,
PRIMARY KEY (post_id,name)
);

CREATE SEQUENCE {$prefix}posttype_pkey_seq;
CREATE TABLE {$prefix}posttype (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}posttype_pkey_seq'),
name VARCHAR(255) NOT NULL,
active SMALLINT DEFAULT 1,
PRIMARY KEY (id)
);

CREATE SEQUENCE {$prefix}poststatus_pkey_seq;
CREATE TABLE {$prefix}poststatus (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}poststatus_pkey_seq'),
name VARCHAR(255) NOT NULL,
internal SMALLINT,
PRIMARY KEY (id)
);

CREATE TABLE {$prefix}options (
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT,
PRIMARY KEY (name)
);

CREATE SEQUENCE {$prefix}users_pkey_seq;
CREATE TABLE {$prefix}users (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}users_pkey_seq'),
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE (username)
);

CREATE TABLE {$prefix}userinfo (
user_id SMALLINT NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT,
PRIMARY KEY (user_id,name)
);

CREATE SEQUENCE {$prefix}comments_pkey_seq;
CREATE TABLE {$prefix}comments (
id BIGINT NOT NULL DEFAULT nextval('{$prefix}comments_pkey_seq'),
post_id BIGINT NOT NULL,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
url VARCHAR(255) NULL,
ip VARCHAR(45) NOT NULL,
content TEXT,
status INTEGER NOT NULL,
date INT NOT NULL,
type INTEGER NOT NULL,
PRIMARY KEY (id)
);

CREATE INDEX {$prefix}comments_post_id_key ON {$prefix}comments (
post_id
);


CREATE SEQUENCE {$prefix}commenttype_pkey_seq;
CREATE TABLE {$prefix}commenttype (
id BIGINT NOT NuLL DEFAULT nextval('{$prefix}commenttype_pkey_seq'),
name VARCHAR(255) NOT NULL,
active INTEGER DEFAULT 1,
PRIMARY KEY (id)
);

CREATE SEQUENCE {$prefix}commentstatus_pkey_seq;
CREATE TABLE {$prefix}commentstatus (

id BIGINT NOT NULL DEFAULT nextval('{$prefix}

commentstatus_pkey_seq'),
name VARCHAR(255) NOT NULL,
internal INTEGER DEFAULT 1,
PRIMARY KEY (ID)
);


CREATE TABLE {$prefix}commentinfo (
comment_id BIGINT NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT NULL,
PRIMARY KEY (comment_id,name)
);

CREATE SEQUENCE {$prefix}rewrite_rules_pkey_seq;
CREATE TABLE {$prefix}rewrite_rules (
rule_id INTEGER NOT NULL DEFAULT nextval('{$prefix}
rewrite_rules_pkey_seq'),
name VARCHAR(255) NOT NULL,
parse_regex VARCHAR(255) NOT NULL,
build_str VARCHAR(255) NOT NULL,
handler VARCHAR(255) NOT NULL,
action VARCHAR(255) NOT NULL,
priority INTEGER NOT NULL,
is_active INTEGER NOT NULL DEFAULT 0,
rule_class INTEGER NOT NULL DEFAULT 0,
description TEXT NULL,
parameters TEXT NULL,
PRIMARY KEY (rule_id)
);

CREATE SEQUENCE {$prefix}crontab_pkey_seq;
CREATE TABLE {$prefix}crontab (
cron_id INTEGER NOT NULL DEFAULT nextval('{$prefix}
crontab_pkey_seq'),
name VARCHAR(255) NOT NULL,
callback VARCHAR(255) NOT NULL,
last_run INTEGER,
next_run INTEGER NOT NULL,
increment INTEGER NOT NULL,
start_time INTEGER NOT NULL,
end_time INTEGER,
result VARCHAR(255) NOT NULL,
notify VARCHAR(255) NOT NULL,
cron_class SMALLINT NOT NULL DEFAULT 0,
description TEXT NULL,
PRIMARY KEY (cron_id)
);

CREATE SEQUENCE {$prefix}log_pkey_seq;

CREATE TABLE {$prefix}log (
id BIGINT NOT NULL DEFAULT nextval('{$prefix}log_pkey_seq'),
user_id INTEGER NULL DEFAULT NULL,
type_id INTEGER NOT NULL,
severity_id SMALLINT NOT NULL,
message VARCHAR(255) NOT NULL,
data BYTEA NULL,
timestamp INT NOT NULL,
ip VARCHAR(45) NOT NULL,
PRIMARY KEY (id)
);

CREATE SEQUENCE {$prefix}log_types_pkey_seq;
CREATE TABLE {$prefix}log_types (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}log_types_pkey_seq'),
module VARCHAR(100) NOT NULL,
type VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE (module,type)
);

CREATE SEQUENCE {$prefix}groups_pkey_seq;
CREATE TABLE {$prefix}groups (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}groups_pkey_seq'),
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
);

CREATE SEQUENCE {$prefix}users_groups_pkey_seq;
CREATE TABLE {$prefix}users_groups (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}
users_groups_pkey_seq'),
user_id INTEGER NOT NULL,
group_id INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (user_id,group_id)
);

CREATE TABLE {$prefix}sessions (
token varchar(255) NOT NULL,
ip VARCHAR(45) NOT NULL,
expires BIGINT NOT NULL DEFAULT 0,
ua VARCHAR(255) NOT NULL,
data TEXT,
user_id INTEGER,
PRIMARY KEY (token)
);

CREATE SEQUENCE {$prefix}terms_pkey_seq;
CREATE TABLE {$prefix}terms (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}terms_pkey_seq'),
term VARCHAR(255) NOT NULL,
term_display VARCHAR(255) NOT NULL,
vocabulary_id INTEGER NOT NULL,
mptt_left INTEGER NOT NULL,
mptt_right INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (vocabulary_id,mptt_right,mptt_left),
UNIQUE (vocabulary_id,term)
);

CREATE TABLE {$prefix}terminfo (
term_id BIGINT NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT NULL,
PRIMARY KEY (term_id,name)
);

CREATE SEQUENCE {$prefix}vocabularies_pkey_seq;
CREATE TABLE {$prefix}vocabularies (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}
vocabularies_pkey_seq'),
name VARCHAR(255) NOT NULL,
description TEXT,
features TEXT,
PRIMARY KEY (id)
);

CREATE TABLE {$prefix}object_terms (
object_id INTEGER NOT NULL,
term_id INTEGER NOT NULL,
object_type_id INTEGER NOT NULL,
PRIMARY KEY (object_id, term_id)
);

CREATE SEQUENCE {$prefix}object_types_pkey_seq;
CREATE TABLE {$prefix}object_types (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}
object_types_pkey_seq'),
name VARCHAR(50),
PRIMARY KEY (id)
);

INSERT INTO {$prefix}object_types (name) VALUES ('post');

CREATE SEQUENCE {$prefix}tokens_pkey_seq;
CREATE TABLE {$prefix}tokens (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}tokens_pkey_seq'),
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NULL,
token_type INT NOT NULL DEFAULT 0,
token_group VARCHAR(255) NULL,
PRIMARY KEY (id),
UNIQUE (name)
);

CREATE TABLE {$prefix}post_tokens (
post_id INTEGER NOT NULL,
token_id INTEGER NOT NULL,
PRIMARY KEY (post_id, token_id)
);

CREATE TABLE {$prefix}group_token_permissions (
group_id INTEGER NOT NULL,
token_id INTEGER NOT NULL,
access_mask SMALLINT NOT NULL,
PRIMARY KEY (group_id, token_id)
);

CREATE TABLE {$prefix}user_token_permissions (
user_id INTEGER NOT NULL,
token_id INTEGER NOT NULL,
access_mask SMALLINT NOT NULL,
PRIMARY KEY (user_id, token_id)
);

CREATE SEQUENCE {$prefix}scopes_pkey_seq;
CREATE TABLE {$prefix}scopes (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}scopes_pkey_seq'),
name VARCHAR(255) NOT NULL,
criteria TEXT NOT NULL,
description TEXT NULL,
priority SMALLINT NOT NULL,
PRIMARY KEY (id)
);

CREATE SEQUENCE {$prefix}blocks_pkey_seq;
CREATE TABLE {$prefix}blocks (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}blocks_pkey_seq'),
title VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL,
data TEXT NULL,
PRIMARY KEY (id)
);

CREATE SEQUENCE {$prefix}blocks_areas_pkey_seq;
CREATE TABLE {$prefix}blocks_areas (
id INTEGER NOT NULL DEFAULT nextval('{$prefix}
blocks_areas_pkey_seq'),
block_id INTEGER NOT NULL,
area VARCHAR(255) NOT NULL,
scope_id INTEGER NOT NULL,
PRIMARY KEY (id),
display_order INTEGER NOT NULL DEFAULT 0
);
====


====upgrade.sql====
BEGIN; /* Transactional DDL FTW take that MySQL */
DROP SEQUENCE IF EXISTS {$prefix}tags_pkey_seq;

CREATE SEQUENCE {$prefix}commenttype_pkey_seq;
CREATE TABLE {$prefix}commenttype (
id BIGINT NOT NuLL DEFAULT nextval('{$prefix}commenttype_pkey_seq'),
name VARCHAR(255) NOT NULL,
active INTEGER DEFAULT 1,
PRIMARY KEY (id)
);

CREATE SEQUENCE {$prefix}commentstatus_pkey_seq;
CREATE TABLE {$prefix}commentstatus (

id BIGINT NOT NULL DEFAULT nextval('{$prefix}

commentstatus_pkey_seq'),
name VARCHAR(255) NOT NULL,
internal INTEGER DEFAULT 1,
PRIMARY KEY (id)
);

CREATE TABLE {$prefix}terminfo (
term_id bigint NOT NULL,
name character varying(255) NOT NULL,
type integer DEFAULT 0 NOT NULL,
"value" text,
PRIMARY KEY (term_id,name)
);

ALTER TABLE {$prefix}comments
ALTER COLUMN ip TYPE character varying(45) /* TYPE change - table:
comments original: bigint new: character varying(45) */;

ALTER TABLE {$prefix}log
ALTER COLUMN ip TYPE character varying(45) /* TYPE change - table:
log original: bigint new: character varying(45) */,
ALTER COLUMN ip DROP DEFAULT;

ALTER TABLE {$prefix}sessions
DROP COLUMN subnet,
ADD COLUMN ip character varying(45) NOT NULL defaut '';
COMMIT;
====

[David]

One more problem.

I had a permission problem with the cache directory, and got an error
file_put_contents(/path/to/my/site/habari/user/cache/
c8942f7085f506bcfb69b4a3bd64363d.
1137ee5f5dab2280f8646db1c09c4580.cache) [<a href='function.file-put-
contents'>function.file-put-contents</a>]: failed to open stream:
Permission denied in system/classes/filecache.php:152

This also gave:
value too long for type character varying(255)
STATEMENT: INSERT INTO habari__log ( data, ip, message, severity_id,
timestamp, type_id, user_id ) VALUES ( $1, $2, $3, $4, $5, $6, $7 )

I'd suggest changing the type of "message" to "text". for this table.
ALTER TABLE {$prefix}log
ALTER COLUMN message TYPE text;