Other difficult things: the set datatype.

The ENUM is easier:

the ENUM
aspm_source enum('local','reported','central') collate latin1_general_cs NOT NULL default 'reported',

I can (almost) simulate with a check constraint:
aspm_source varchar(8) check (aspm_source in ('local','reported','central')) NOT NULL default 'reported',

[]'s
- Walter

Good to know that single quotes should get used. But I think it's quite difficult to port it. The problem is using direct queries, I think.

You might want to take a look ath the db_delta function, if you want to get a shock: It's totally MySQL-only and it actually would be nice to have this more abstracted from the direct queries or abstracted at all, too :p.

Do you think porting to other DBMS will work by only changing the direct queries?

Hi blueyed!

Yes, the trouble will be the direct queries and mysql-isms.

The single quotes will help a lot.

I'm just researching. ASAP, I will post the complete difficulties to put that to work and maybe we can find solutions together :)

I will try to do that on the weekend.

The db abstraction of b2evolution uses a lot of fetch_object.

In my attempt to move to pg, I found a lot of replaces like: blog_ID for blog_id. (The field blog_ID doesn't exists when I use PostgreSQL).

So, i've done a fast test with mysql, postgresql 8.1 and firebird 1.5.

In PostgreSQL:

CREATE TABLE object_lower
(
  object_id serial NOT NULL,
  object_name varchar
) ;

CREATE TABLE object_mixed
(
  object_ID serial NOT NULL,
  object_NAME varchar
) 

CREATE TABLE object_mixed_quotes
(
  "object_ID" SERIAL NOT NULL
  "object_NAME" varchar
) ;

I have inserted just one row in each table. The work:

<pre>
<?
$db_conn = pg_connect("host=localhost port=5432 dbname=fetchobject user=postgres password=password");
if (!$db_conn) {
  echo "Failed connecting to postgres database $database\n";
  exit;
}

$query1 = pg_query($db_conn, "SELECT object_ID, object_NAME FROM object_lower");
$query2 = pg_query($db_conn, "SELECT object_ID, object_NAME FROM object_mixed");
$query3 = pg_query($db_conn, 'SELECT "object_ID", "object_NAME" FROM object_mixed_quotes');
print_r($query3);

$data = pg_fetch_object($query1);
print_r($data);

$data = pg_fetch_object($query2);
print_r($data);

$data = pg_fetch_object($query3);
print_r($data);

?>
</pre>

Results:

Resource id #5stdClass Object
(
    [object_id] = > 1
    [object_name] = > object #1
)
stdClass Object
(
    [object_id] = > 1
    [object_name] = > object #1
)
stdClass Object
(
    [object_ID] = > 1
    [object_NAME] = > object #1
)

The test with MySQL:

$query1 = mysql_query("SELECT object_ID, object_NAME FROM object_lower");
$query2 = mysql_query("SELECT object_ID, object_NAME FROM object_mixed");
$query3 = mysql_query('SELECT object_id, object_name FROM object_mixed');
#print_r($query3);
print_r($query1);
$data = mysql_fetch_object($query1);
print_r($data);

$data = mysql_fetch_object($query2);
print_r($data);

$data = mysql_fetch_object($query3);
print_r($data);

And the results:

Resource id #3stdClass Object
(
    [object_ID]  = > 1
    [object_NAME] = > object #1
)
stdClass Object
(
    [object_ID] = > 1
    [object_NAME] = > object #1
)
stdClass Object
(
    [object_id] = > 1
    [object_name] = > object #1
)

In MySQL, if I put double quotes around column names, It returns the column names :)

In Firebird, it returns always in UPPER_CASE, no matter if i query object_id, object_ID or OBJECT_ID.

I think that if b2evolution will make easier to port to another database, this is a issue to be revised. Looks to me that after fetch_object, the right thing to do is to convert all the object members keys to lowercase.

Any toughts?

(I can send the scripts and databases of this test, if someone wishes).
:D
[]'s
- Walter

Walter,

Normally, all queries should quote their values with $DB->quote() or use single quotes. Feel free to modify the other ones in the CVS codebase so that they conform (contact me if you need access).

Regarding ENUMS, you can just just varchars instead. You don't need the constraint.

For lower case IDs and the rest, you will have to adapt the DB class in a way that it translates b2evo's MySQL queries to Postgres and postgres results to MySQL results that b2evo can handle. Regular expressions are your friend! :p

HI Planque. I will checkout the CVS codebase (I was working on the files of 1.9.2) and modify the quotes issues. That's simple but solves a lot of things :D .

About the ENUMS, I like the constraints :) . But I think that before effectively port b2evolution to another database, we need to check a lot of other things, and we can let the constraint to solve after that . I have made a conversor in python that generates the constraints automagically :)

Fow lower case IDS, I was just putting strtolower everywhere :) Well, that's not the most beautiful thing to do :)

There's a lot of issues with dates too. b2evolution uses functions like YEAR, MONTH instead of the standart EXTRACT (YEAR from datecolumn), etc . EXTRACT works on MySQL, PostgreSQL, Firebird and Oracle, and changing that will help a lot.

Another difficult one is the last_insert_id on other databases, but I will explain that after. (Even the code in ez_sql doesn't helps, cause in newer versions os PostgreSQL we don't use OID).

Other difficult one is the set datatype in MySQL.

I thing that there's a lot of work to do :)

[]'s
- Walter

Curious whatever happened with this? Is everybody here really happy with MySQL? (really asking, don't know enough to challenge ... it's only that some devs I know trumpet postgresql as being "better" in some ways)

If anyone is still interested in pursuing a postgres port, I have a suggestion that I'm more than a little hesitant to post because I am NOT a programmer, not even close. But here goes: the phpBB project works with numerous back ends, and their abstraction layers are readily available.

My thought is that you might be able to derive much of the info you need by examining the phpBB docs.

If that sounds like a suggestion with any value, go to http://area51.phpbb.com/docs/code/ and in the Packages combobox choose "dbal".

OTOH if this is a ridiculous suggestion, feel free to point & laugh!

<g>

kazar

Hi kazar!

Well, I have tried to do this port. I like Postgres a lot!

The port is not complete, but, I cant tell you that is easier to do it now than some time ago.

The SQL standart, is not 100% followed by the databases vendors. All the databases implements SQL, but there's a lot of syntax differences. Some time ago, I have researched the b2evolution code, searching for all MySQL dialects. Where was possible to change the MySQL dialect for more a standart dialect, without broke MySQL compability, I changed the queries (a lot of EXTRACT in dates, by example).

But, b2evolution is deeper tied to MySQL. A lot of queries dependes on last_insert_id, and the most of the databases don't have anything similar. And there's a lot of MySQLisms that are hard to change.

And, as François said me, it's allright as is now. So, is possible, as b2evolution uses a db abstraction. If you realy want, some time ago I have tried to do this port and have some code that you can study.

[]'s
- Walter

Hey Walter -- If life weren't so short I would study up enough to help out in this regard. As a business analyst/tech strategist I would say it would be a good goal for the b2e roadmap to include making it multi-DB, just so its fate is not so entwined with whatever MySQL's roadmap proves to be down the road, and also to accommodate organizations with very strict Approved Titles Only type of IT policies (such as someone who posted somewhere on these forums, whose company absolutely only allows MS SQL Server).

But I am only a wimpy p/t coder, learn enough to maintain my own phpBB forums for instance. Really don't know SQL or PHP, and have lived more than half a normal lifespan already and am still interested in too many things to include Learn Programming in the list.

That said, if you reiniate this effort I would be happy to contribute in all ways I can. I am a good tester, a PM, a writer by nature and p/t profession (i.e., could help with docs) and in general a strong thinker with dedication to the OS concept, and to having fun and allowing for different personality types, as well as to taking a solid approach to project management/programming guidelines and the like.

So just ping me any time you think I could help.

kazar
(falling in love with b2e)