PostgreSQL schemas

[Mauricio]

Hello everyone!

I'm impressed with RedBean.

I'm working with Doophp and Doctrine, but as far as I know RedBean is
faster then Doctrine when the subject is faster development. I mean, I
don't know about benchmark and all. But I really liked how easy it is
to create simple CRUD requests.

Anyway, I've got a road block when I tried to connect to a specific
PostgreSQL Schema...

I have:
db = postgres
schema = phonebook
table = users

How can I find all users in my phonebook to create a list?

My code:

<?php

require 'rb.php';

R::setup("pgsql:host=localhost
dbname=postgres",'postgres','mypassword');

... and here I've got stucked.

I know I can
$user = R::getAll("SELECT * FROM phonebook.users ");

But how can I do the same using the RedBean structure?

Thank in advance.

:)

[gabor de mooij]

Hi! welcome to the forum!
Unfortunately Redbean does not support postgreSQL schemas (yet).
It is designed to be DB agnostic so there are not a lot of DB specific
features at the moment. For postgreSQL examples see pgtest.php in git
repo, it contains the unit tests for postgres.

cheers
Gabor

> --
> You received this message because you are subscribed to the Google Groups "redbeanphp" group.
> To post to this group, send email to redbe...@googlegroups.com.
> To unsubscribe from this group, send email to redbeanorm+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/redbeanorm?hl=en.
>
>

[Nick]

At a glance, it seems easy to add support for this...

- change the "quite draconic filtering" in Finder to allow the dot
character
- change the 'pattern of allowed characters' in OODB to allow the dot
character
- change noKW in the abstract query writer so it replaces "dot" with
"quote-dot-quote" before quoting, just in case.

This seems to work alright so far. Will leave the changes in my copy
and see if anything weird happens.

[Mauricio]

Thank you Nick and Gabor for your prompt answers.

I have made some tests and I commented the lines:

// $type = preg_replace("/\W/","", $type);

And added a dot (.) to:
$pattern = '/
[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_.]/';

I did not make any changes the abstract query so far, but I could
access de database with their schemas properly.

I will still do some more tests, but I'm very happy with it.

Have a good one!

[Nick]

> I have made some tests and I commented the lines:
>
> // $type = preg_replace("/\W/","", $type);

You might want to allow _some_ filtering. Try something like this:

$type = preg_replace("/[^\w.]/","", $type);

> And added a dot (.) to:
> $pattern = '/
> [^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_.]/';

this should probably be shortened to
'/[^a-z0-9_.]/i'

> I did not make any changes the abstract query so far, but I could
> access de database with their schemas properly.

Finder doesn't escape the table name, but everything else does (using
noKW) so I wouldn't be surprised if Finder ends up calling noKW at
some point... I assume Gabor just hasn't gotten around to it yet ;)
Actually if you are supplying fully dot-qualified table and column
names, noKW shouldn't have to apply any quotes for any of the
supported DBs afaik. So instead of doing the quote-dot-quote thing,
you can probably just return the input to noKW if it contains dots.

> I will still do some more tests, but I'm very happy with it.

You'll probably need hook up your Fuse models by hand or figure out
some other trickery to get them working.

[gabor de mooij]

Looks good, why not fork the git repo?

Verstuurd vanaf mijn iPhone

[Nick]

> Looks good, why not fork the git repo?

I did, silly. :)

It's got some other stuff in it you may or may not want, though.

https://github.com/gitbuh/redbean

Feel free to pull anything you want back in...

[gabor de mooij]

Ah wonderful !
I will taka look during Xmas holidays ;)

Verstuurd vanaf mijn iPhone

[gabor de mooij]

I have taken a look at the pgschema branch. It seems to work but it
feels a bit like a hack. For instance, the modifications allow MySQL
users to dispense beans that refer to another database which is not
really something I recommend.

Maybe we should try to find a more elegant solution to make pgschema's
possible. Can you explain to me the benefits of postgreSQL schema's in
your use case?

cheers,
Gabor

[Nick]

> I have taken a look at the pgschema branch.

Where can I find that? Is it in someone's fork?

> For instance, the modifications allow MySQL
> users to dispense beans that refer to another database which is not
> really something I recommend.

Wasn't that the desired effect of the modifications, except for PG
instead of MySQL? Is there any conceptual difference between selecting
from another PG schema and selecting from another MySQL schema?

> Maybe we should try to find a more elegant solution to make pgschema's
> possible. Can you explain to me the benefits of postgreSQL schema's in
> your use case?

I'm curious what the benefits are too. I'd assume whatever the
benefits are, they'd apply to MySQL schema as well, and perhaps
alternate file locations for SQLite?

-- Nick