Subject: MSSQL Migration

Posted on: 15/04/04 10:41am
By: diego

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


MSSQL Migration

Posted on: 15/04/04 12:02pm
By: Blaine

Hi Diego,

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

Did you have to write a DB Class replacement ?

MSSQL Migration

Posted on: 15/04/04 02:00pm
By: diego

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.

MSSQL Migration

Posted on: 15/04/04 06:05pm
By: diego

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

MSSQL Migration

Posted on: 18/04/04 05:42pm
By: mthomas

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()

MSSQL Migration

Posted on: 19/04/04 05:55pm
By: diego

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.

MSSQL Migration

Posted on: 01/05/04 01:46am
By: Anonymous

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

MSSQL Migration

Posted on: 04/05/04 03:31pm
By: diego

Which database do you want to migrate to?

MSSQL Migration

Posted on: 04/05/04 05:28pm
By: Tony

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...

MSSQL Migration

Posted on: 04/05/04 09:10pm
By: Anonymous

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.


Geeklog - Forum
https://www.geeklog.net/forum/viewtopic.php?showtopic=33585