Welcome to Geeklog, Anonymous Tuesday, October 15 2024 @ 11:54 am EDT
Geeklog Forums
MSSQL Migration
Status: offline
diego
Forum User
Newbie
Registered: 04/15/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
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
16
18
Quote
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
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
32
20
Quote
Status: offline
mthomas
Forum User
Full Member
Registered: 04/18/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()
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()
18
19
Quote
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
-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
18
17
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
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
15
17
Quote
Status: offline
Tony
Site Admin
Admin
Registered: 12/17/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.
The reason people blame things on previous generations is that there's only one other choice.
17
15
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.
It seems that the the second choice is safer since we must handle other issue like select limit statement.
15
12
Quote
All times are EDT. The time is now 11:54 am.
- 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