I migrated my geeklog's mysql database to mssql(2000) db server and the incompatibilities are the following:

1-)Mysql's function
-unixtimestamp()
-now()
-TO_DAYS()
-date_sub()
-curdate()
-date_format()
-dayofmonth()
-MID()
-LENGTH()
-INSTR()

2-)Clauses
-LIMIT
-Fulltext

The major problem is the 'REPLACE INTO', that was replaced to a delete/insert query with more little things.

Other problem is the 'GROUP BY' clause, with hidden fields, but according to the mysql's doc, there is no major problem if used in the right way like the following orientation:

"Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You will get unpredictable results."

diego

Hi Diego,

Did you end up getting geeklog to work sucessfully with MS SQL?

Did you have to write a DB Class replacement ?

Hi,
Geeklog is working fine in MSSQL, but I'm still in a hard test level with the 'GROUP BY' clause used in stats plugin.

In DB Class, I had to do some drastic changes in dbsave() and dbcopy(). The debug function, I'm working on it yet and other places I changed the mysql's function to mssql.

I finished de debug function, now it's working fine with MSSQL.

ooo I don't suppose you documented as you went? If so please please post it or if you didn't document please give a brief run down on the steps you took from that you can remember, I plan on moving from Mysql to MS SQL.

There are corresponding MS SQL fuction for each of these, and for those there arn't a quick VB script or similar will emulate those functions into a format useable by MS SQL.

-unixtimestamp()
-now()
-TO_DAYS()
-date_sub()
-curdate()
-date_format()
-dayofmonth()
-MID()
-LENGTH()
-INSTR()

Hi, I don't have time to document it yet. So, a brief sumary of similar, but not equal functions:

-unixtimestamp(): datediff()
-now(): getdate()
-TO_DAYS(): datediff()
-date_sub(): dateadd()
-curdate(): convert()
-date_format(): datepart() or convert()
-dayofmonth(): datepart()
-MID(): substring()
-LENGTH(): len()
-INSTR(): charindex()

Now you only have to adapt it on the right way for each function.

Hi,

I am on my way to migrate to another dbms too. Can somebody give me direction please ? How can we write replacement for mysql statement:

REPLACE INTO $table ($fields) VALUES ($values)

1. A Series of delete and insert:
DELETE from $table WHERE ...
INSERT INTO $table ($fields) values ($values) WHERE ...

2. Or doing the hard way with UPDATE:

UPDATE $table
SET field1 = $value1, field2 = $value2, field3=$value3
WHERE primarykey_field = $primarykeyvalue

In both alternatives I found that me must know the primary key field and it's value. How can we possibly do it inside dbSave($table,$fields,$values) function ?

TIA

Which database do you want to migrate to?

Actually, I would propose that we roll the changes to the core geeklog codebase if they are ready. This would need to be coordinated with Dirk as he is the CVS maintainer for that version. A SQL compliant 1.3.x has a lot of value and make it easy for others to take the mysql.class.php and port it to the DBMS of their choice. Please talk to Dirk about this...

Oracle. I have two options: 1. modify the dbSave functions, assuming the first field is the primary key, or 2. Rewrite query statements in all module for dbSave with a generic sql statement: update table set field1= ... where primary key= ....

It seems that the the second choice is safer since we must handle other issue like select limit statement.