postgresql / sqlite

Permalink 1 user found helpful
Is anyone working on support for postgresql or sqlite? I want to get c5 running on those databases.

If not, I guess I will give it a go. Probably starting with sqlite, because that will be the easier one.

View Replies: View Best Answer
Shiny replied on at Permalink Reply
i started on a port to postgres on Sunday
I've got the schema installed - but need to go back and make sure it still works in mysql

You keen to collaborate?
wltr replied on at Permalink Reply 1 Attachment
I started by running the c5 installer and worked from there. The install script can be run with some changes, but it fails to complete because of database differences.

In the install script I skipped, for now, queries than only work in MySQL. These are the "show tables", "set names" and "set character set" queries.

I was abled to get some tables to be created.

- There is no data dictionary in adodb for sqlite. I created one, but needs to be tested further.
- In db.xml <DEFTIMESTAMP/> was replace with <DEFAULT value="NOW"/>
- Indexes must have a unique name for the entire database. This is also true for postgres. I solved this by prefixing the index name with the table name. e.g. akHandle > FileAttributeKeys_akHandle
- Tables are created, but some inserts fail. Still investigating.

For postgres I had to replace the following settings in db.xml:
- Attribute size="1" had to be remove where type="I1" was used
- Attribute type="I2" had to be replaced with type="I"
- <DEFAULT value="0000-00-00 00:00:00"/> had to be removed because it is not a valid date
- Column name ctID is not allowed, because it is reserved for a system column. System colums in postgres are oid, tableoid, xmin, cmin, xmax, cmax, ctid. These cannot be used for column names.

The ctID column name seems like a major issue for postgres support. Did you solve this one?

Because if not, it would mean that the core code needs to be modified.
Shiny replied on at Permalink Reply
Do you have this in a git repo somewhere?
or patch file
wltr replied on at Permalink Reply
zotsf replied on at Permalink Reply
Hey guys. It looks like you are making some progress and I'm very encouraged!

I'm looking forward to a robust solution if you can swing it!
Remo replied on at Permalink Reply
what was mentioned about ctID is a big core change..

Unless someone from the core team replies nothing is going to happen.

The same with oracle, for both databases we would have to change lots of php.
lenninpadilla replied on at Permalink Reply
So do we have a patch for postgresql databases?
wltr replied on at Permalink Best Answer Reply
I haven't found a way to support postgresql without changing the database layout. This means a significant change to the core that affects everything.

The C5 team would have to decide if it's worth the effort.
lenninpadilla replied on at Permalink Reply
Thank you for your answer!