PostgreSQL Database Support

Questions related to installing Zuluru 1: Locked now, all new installations should use Zuluru 3
Locked
phancox
Posts: 6
Joined: Mon Aug 24, 2015 7:01 am

PostgreSQL Database Support

Post by phancox »

Is it possible to install Zuluru with PostgreSQL instead of MySQL?

Documentation seems to imply that PostgreSQL is supported but untested. I have successfully installed with MySQL but the PostgreSQL install has many errors when creating the database schema. Am I doing something wrong or is it just that this has never worked?

THANKS
Peter
GregS
Site Admin
Posts: 240
Joined: Thu Jan 06, 2011 4:58 pm

Re: PostgreSQL Database Support

Post by GregS »

Zuluru has never been tested with anything but MySQL. The CakePHP framework ostensibly supports it, but I don't know to what level. Having worked with CakePHP and MS-SQL together on unrelated projects, I know that it's not nearly as well supported as MySQL is in the schema management area, so Postgres may well be in a similar boat. My expectation is that CakePHP 3.x is probably much better in this area than 1.3 is, but the version of Zuluru that works with CakePHP 3 is a couple months away yet.

If you send me some of the errors, I might be able to tell whether they're easily resolved or indicative of more fundamental problems. If the latter, and it's mostly schema management stuff, it might not be too hard to build the initial database manually and then see how Zuluru runs regular tasks on it. I do expect there are a few MySQL-specific things in the code, but they should mostly be commented as such, and it really should be just a handful.
phancox
Posts: 6
Joined: Mon Aug 24, 2015 7:01 am

Re: PostgreSQL Database Support

Post by phancox »

First error seemed to be that "FULL" is a reserved word which I think you are using for the name of many indexes. Assuming you never directly reference the index name in the code (probably not a safe assumption) I changed index name "full" to "fullx".

That seemed to help, getting me to the point of multiple "already defined" messages when building the database. Didn't look that much into this but I suspect PostgreSQL requires index names to be unique and perhaps MySQL does not. Could try prefixing the index name with the name of the table it indexes.

Didn't try this as I wanted to review the functionality before spending too much time making it work for another database; everything else we run on PostgreSQL.

As far as evaluating the software, the first issue I had was with the admin password. Couldn't get "admin" and "password" to work and not sure if this is the right one anyway. Looked at the tables and noticed that the stored value is different to what appears to be in the initial schema load. Took the easy out and changed the default email address and used "forgotten password".

Also interested in how to remove the Canadian specifics (we're in Australia). Haven't looked yet but is it possible to change the format checks on phone numbers and postcode or do I need to make code changes?

THANKS
Peter
GregS
Site Admin
Posts: 240
Joined: Thu Jan 06, 2011 4:58 pm

Re: PostgreSQL Database Support

Post by GregS »

The index names are never used anywhere in the code, so they can be changed to anything that works. They have the names they have for internal consistency, and because they have to have *some* name.

The admin password should be hashed in the database using the randomly-generated salt written into the config/install.php file. If you ran the install more than once, one might have been updated without the other. There are other possible reasons, but it's not too important now as you were able to work around it.

Some of the Canadian details are in the provinces and countries table, both initialized from the relevant files under config/schema/data. Validation of things like phone numbers and postal codes is in the code, though. I have added a few options in there as demand has come up, and can do that for you too if you want. Or you can just use Canadian addresses and numbers for your initial testing; postal codes are of the format A1A 1A1 (and that's a perfectly valid one to use), and a simple valid phone number would be 212-222-2222. The upcoming CakePHP 3.x version of Zuluru will probably make use of a third-party library that knows the format for these things around the world, so this will be less of an issue at that time.

As for that functionality review, may be worth pointing out that the new version will maintain most things mostly the same, but improve user-friendliness in a number of areas which drive the most questions, as well as putting a more mobile-friendly face on it. After which time, I'll get back to adding new features again.
phancox
Posts: 6
Joined: Mon Aug 24, 2015 7:01 am

Re: PostgreSQL Database Support

Post by phancox »

Thanks Greg,

I did customise countries and provinces before building the database; and looked up some phone numbers and postcodes from the demo site :-)

The move to CakePHP 3.x sounds promising for modification of the application. When is the next version due for release? Is it possible to get a pre-release version, I notice github has only master branch? I haven't looked at the application yet myself (will do that in the next day or so) but my friend who is looking for something to use for a couple of sports (baseball and basketball initially) started looking today (I only completed the install late last night). His initial feedback was that it was very Frisbee orientated and a little user-unfriendly. You mentioned that the upcoming version improves user friendlyness so, as we're just starting, it would be nice to look at what's coming.

If it does look suitable for us, then I'll likely attempt to get it running on PostgreSQL.

Do you know of any sites using Zuluru for baseball or basketball?

REGARDS
Peter
GregS
Site Admin
Posts: 240
Joined: Thu Jan 06, 2011 4:58 pm

Re: PostgreSQL Database Support

Post by GregS »

Being open source, I work on the new version when I find the time, but the schedule depends somewhat on what other projects I take on to pay the bills. I am hoping for an initial release in October, but November is probably more realistic. I think I'm about half way done right now, but there's still so very much broken in it that I don't feel comfortable making it available. When it's added to Github, it will be a new repo; it can't really be done cleanly and easily as a branch, because every single file gets renamed.

Zuluru did start out being used for ultimate, and that's still the bulk of the user base, but it's being used by a number of organizations for other sports. http://fbaregistration.com/ is an example that's using it for baseball, softball, basketball and soccer (though I don't think those are all active at the moment). Be sure to go through the various Configuration -> Settings pages to customize the feature set for your needs; ultimate-specific things like spirit scoring can be turned off altogether in there.

If your friend wants to make some notes on the area where user-friendliness could be improved, I can let you know whether those are things that are already being addressed in the new version, and if they aren't I can definitely consider them.
Locked