Welcome to Geeklog Thursday, December 12 2019 @ 10:14 pm EST

Geeklog Forums

MSSQL Migration


Status: offline

diego

Forum User
Newbie
Registered: 15/04/04
Posts: 7
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


[]'s diego
 Quote

Status: offline

Blaine

Forum User
Full Member
Registered: 16/07/02
Posts: 1233
Location:Canada
Hi Diego,

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

Did you have to write a DB Class replacement ?
Geeklog components by PortalParts -- www.portalparts.com
 Quote

Status: offline

diego

Forum User
Newbie
Registered: 15/04/04
Posts: 7
caffeinated
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.
[]'s diego
 Quote

Status: offline

diego

Forum User
Newbie
Registered: 15/04/04
Posts: 7
caffeinated
I finished de debug function, now it's working fine with MSSQL.
[]'s diego
 Quote

Status: offline

mthomas

Forum User
Full Member
Registered: 18/04/04
Posts: 148
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()
 Quote

Status: offline

diego

Forum User
Newbie
Registered: 15/04/04
Posts: 7
caffeinated
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.
[]'s diego
 Quote

rdsianto

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
 Quote

Status: offline

diego

Forum User
Newbie
Registered: 15/04/04
Posts: 7
caffeinated
Which database do you want to migrate to?
[]'s diego
 Quote

Status: offline

Tony

Site Admin
Admin
Registered: 17/12/01
Posts: 405
Location:Urbandale, Iowa
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...
The reason people blame things on previous generations is that there's only one other choice.
 Quote

rdsianto

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.

 Quote

All times are EST. The time is now 10:14 pm.

  • Normal Topic
  • Sticky Topic
  • Locked Topic
  • New Post
  • Sticky Topic W/ New Post
  • Locked Topic W/ New Post
  •  View Anonymous Posts
  •  Able to post
  •  Filtered HTML Allowed
  •  Censored Content