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.
If not, I guess I will give it a go. Probably starting with sqlite, because that will be the easier one.
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.
SQLite
- 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.
Postgres
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.
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.
SQLite
- 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.
Postgres
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.
Do you have this in a git repo somewhere?
or patch file
or patch file
pushed changes to github:
http://github.com/manafta/concrete5/tree/master...
http://github.com/manafta/concrete5/tree/master...
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!
I'm looking forward to a robust solution if you can swing it!
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.
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.
So do we have a patch for postgresql databases?
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.
The C5 team would have to decide if it's worth the effort.
Thank you for your answer!
I've got the schema installed - but need to go back and make sure it still works in mysql
You keen to collaborate?