[Ryan Day]

I know... but I have to use it for work and in order to push anything through my office it must use Postgres. So are there plans to expand database support? The reason I post this in here is because I've started working on PgsqlDataSourceProvider.class.php.

So far it passes all the unit tests that MysqlDataSourceProvider passes, but once you branch much beyond that it doesn't work. The problem(as far as I know) is that the SqlBuilder is designed specifically for MySQL (and I suppose sqlite, I really don't know much about sqlite). So when the queries are being put together, there are methods, such as escapeWithTicks(), that break postgres syntax. I have created a hacky factory'ish workaround; I made SqlBuilder an interface, my PgSqlBuilder MySqlBuilder implement it. All DataSourceProviders must respond to the getBuilder() method, which returns a string telling you the proper SqlBuilder (either mysql or pgsql). So in PdoDataSet.class.php it calls $this->source->getBuilder() which gives us the proper class. The reason I use a string is that the SqlBuilder.class also defines the Criterion and Join classes, and I can't Library::import() multiple SqlBuilders without an error(I'm tryin to touch and move as little of the code as possible).

Anyways, this works to the point where I can create an app, create a model, and generate scaffolding for that model using a Postgres database. But the SqlBuilder change would have to be made correctly and implemented throughout the orm/ and any other classes that use it. So I wanted to just throw this out there before I go any further.

So has this already been discussed an decided against? Or is someone already working on it?

[KrisJordan]

Great questions and sounds like you're off to a solid start.

This has not been discussed, decided against, nor worked on. MySQL/SQLite were the initially chosen support platforms because they're most common (and all anyone has needed so far). I figured there might come a time when we would need specialized SQLBuilders and it looks like that time has come.

There are some other things that have been discussed and need to be implemented with SqlBuilder -- primarily (timely as this just came up on another forum post) complex boolean queries. $post->or(Criteria::equal('title', 'foo'),Criteria::equal('title','bar'))->first()

Do not be afraid of refactoring so long as the tests are passing. Your strategy sounds reasonable and well guided. Let me know if there are any branches/commits to be reviewed.

[Ryan Day]

Sounds good, I'll start putting it together. I've created a few new tests and I'm putting the classes together. Once I have something you can look at I'll let you know!

Oh one question, I'm getting a Call to a member function beginTransaction() on a non-object in SqlitePdoDataSourceProviderTest.php, is that a known issue, or perhaps I just don't have sqlite cocnfigured correctly?

[KrisJordan]

Great -- I strongly suggest forking the repository on Github which makes bringing in changes from repositories around the community really straightforward. Not sure what your comfort level is with git but the github folks have put together some great guides like:

On forking a project: http://help.github.com/forking/

Other general topics: http://github.com/guides/home

If you run into any walls/questions - be encouraged to use this thread for further discussion.

[Ryan Day]

Not too familiar with github, but it seems pretty straightforward. I've pushed some changes, more as a test then anything else. I'm pretty sure I've only touched my fork, so let me know if I've overwritten anything.

So far no big problems, the real issues are auto increments <=> sequences, and putting quotes around everything to make sure proper capitalization is followed. So I'm trying to make sure I quote tables & columns at the correct place in the code. Postgres is also really annoying about types. I've had to pass along type information in some assign() calls to make sure that setting an Integer to '' makes it NULL instead of an empty string.

But anyways, all the unit tests pass for everything but there are still some issues with timestamp types. Also I seem to be dumping core when running the Pgsql tests. Still looking into what exactly is causing that(all the php bugs re: pgsql pdo coredump seem to be from a couple years ago, not sure what my problem is).

I'll keep you posted with the updates and when I think its ready for a pull request!

[KrisJordan]

This is great, thanks Ryan!

It would be a huge help if you could also make a script in the 'setup' dir that properly installs postgres on the Sandbox. If the recommended way is as straightforward as using Aptitude (ala MySQL) you can just let me know the package(s) and I can add it to the mix.

[Dodi]

Love it. I am a postgreSQL user. Can't wait for postgreSQL support.

[Ryan Day]

Postgres certainly isn't official yet, but if you are adventurous you can grab git://github.com/rday/recess.git for my current code. I have two sites (NOT production yet) running postgres and so far things are ok. If you would like to clone it that would be great. Some things to note, I'm trying to stick with the standard Recess Types so far. So if you create a DateTime field from the Recess Tools Generator, you are creating a Timestamp Without Time Zone database field. Date and Time fields become date and time postgres fields, String becomes varchar(255), text is text. I haven't experiment very far with blobs. I've added the Postgres Point and Box datatypes as well (although they are not accessible from the Tools generator) and a "contains" method. That is mainly because I store GPS in one of my apps and I wanted to put that there, but I haven't added any additional geometric support.

One thing to note, I've removed the config files from my GIT repo, so you will need to grab the recess-conf.php from the main repo and use "pgsql:host=localhost;dbname=lalala;sameasmysql", to the RecessConf::$defaultDatabase array. So if you are able to test, awsome! If not no problems, I am no stranger to management running from new things

[Heitzso]

Ryan, I'm working on a postgresql/postgis project and likely needing a REST framework in PHP (alternative is jumping to XMPP). Looking at RECESS. App needs to demo (demo grade only) mid November. Is your postgresql branch functional enough to get started? I could help debug and possibly contribute some code, but need the basic pieces to work now. Thanks.

[Ryan Day]

I haven't put in any postGIS code yet. I don't see an issue with putting in basic functionality though. Some of postGIS requires advanced sql syntax which Recess doesn't support( I don't think, I'll ask around). I'm thinking of something like SELECT queries in the FROM section, etc.

What specific functionality do you need from postGIS immediately, and what postgresql functionality do you need now? If you currently just need to create a database, and populate fields you are good to go. If you want to use postGIS geometric data types, I'll have to test that out as I'm using the postgresql geometric datatypes and I don't think the two are compatible. I'll check though. Right now, however, you can store GPS locations as Point types and Box types. Obviously a polygon isn't much more complex to store then a box, but I haven't built the Input class for a polygon yet(because that is much more complex then the Input for a point or box).

I have one pending bug that I am aware of in the code. If you have a primary key index that isn't auto-incrementing(and therefore no sequence) you will get a crash when the database tries to give you the last inserted ID. I haven't pushed that fix yet, but will when I get home today.

So I guess I'm side stepping your question. Is it functional enough to get started? I would say yes, I use it and even use Point datatypes. However if your base functionality includes retrieving data where ST_Distance(a, < whatever, that isn't ready yet. I will install postgis on the sandbox tonight and give it some tests. Let me know what you think, thanks!

[Heitzso]

I'm considering putting everything into postgresql functions, then using straight PDO (w/o any RECESS abstractions) to call the functions. That lets me leverage RECESS mapping of GET/PUT/POST/DELETE into methods and translation of output. (I believe, have not tried to code this up). I'll let you know if I try to work through your tree.