 |
Rainchild
Wizard
Joined: 10 Oct 2000
Posts: 1551
Location: Australia
|
 Posted: Wed Oct 06, 2004 2:59 am
[OT] MySQL help
|
Meh, I'm a Microsoft SQL boy at heart, but I'm needing to use MySQL for a linux-based app (ok ok, for the MUD) .... now, I'm a bad programmer in that I like things to be foolproof for distribution even if I don't ever plan to distribute the program.
So... I need pointers in the right direction to write a SQL script that will create and upgrade a database automatically to the latest version every time it is run.
In Microsoft SQL I typically do the following:
| Code: |
-- check to see if table exists and create if necessary
IF NOT EXISTS( SELECT * FROM sysobjects WHERE name='mytable' AND xtype='U' )
BEGIN
CREATE TABLE mytable (
mycolumn1 INTEGER,
mycolumn2 VARCHAR( 30 )
)
-- insert version number into my table that keeps track of versions
INSERT INTO versions( tablename, version ) VALUES( 'mytable', 1 )
END
-- upgrade table to version 2 if necessary
IF ( SELECT version FROM versions WHERE tablename='mytable' ) < 2
BEGIN
ALTER TABLE mytable
ADD mycolumn3 DATETIME
UPDATE versions SET version=2 WHERE tablename='mytable'
END
-- upgrade table to version 3 if necessary
IF ( SELECT version FROM versions WHERE tablename='mytable' ) < 3
BEGIN
ALTER TABLE mytable
ADD mycolumn4 INTEGER, mycolumn5 VARCHAR( 200 )
UPDATE versions SET version=3 WHERE tablename='mytable'
END
|
... but I can't figure out any way to do that in MySQL, anyone familiar enough with MySQL to give me some pointers in the right direction?
Tah,
-- Rainchild |
|
|
|
|
Rehcra
Novice
Joined: 10 Oct 2000
Posts: 43
|
Posted: Wed Oct 06, 2004 3:38 pm
|
-- check to see if table exists and create if necessary
Try CREATE TABLE IF NOT EXISTS foo (...);
-- upgrade table to version 2 if necessary
You can use SHOW CREATE TABLE foo; to get the definition of a table, then search the definition to see if it needs the ALTER table command
I don't use MSSQL, do I'm not sure if the above is actual script form MSSQL. But unless they've added something to 4.0, MySQL doesn't have a scripting langauge that will do what you want above.
I work in VB, but in C you would have to do something close. With error handleing, etc removed.
| Code: |
Dim WithEvents adoConnection As ADODB.Connection
Public Sub OpenConnection()
Set adoConnection = New ADODB.Connection
With adoConnection
.ConnectionString = "..."
.Mode = adModeReadWrite
.Open
End With
End Sub
Public Function CmdExecute(PASSCMD As String) As Recordset
Dim CMD As New ADODB.Command
With CMD
.CommandText = PASSCMD
.ActiveConnection = adoConnection
.CommandType = adCmdText
Set CmdExecute = .Execute
End With
End Function
Public Sub Main()
Dim RS as Recordset
CmdExecute("CREATE TABLE IF NOT EXISTS foo;")
SET RS = CmdExecute("SHOW CREATE TABLE foo;")
IF RS.EOF = false THEN
IF INSTR(RS(1),"mycol ") > 0 THEN
CmdExecute("ALTER TABLE foo ADD COLUMN mycol ... ;"
END IF
END IF
SET RS = CmdExecute("SELECT version FROM versions WHERE ... ;")
IF RS.EOF = false
IF RS("version") < 3 THEN
...
END IF
END IF
etc...
End Sub
|
Correct Syntax can be found at http://dev.mysql.com/doc/mysql/en/SQL_Syntax.html along with everything else you might ever want to know.
You can email me if you want to discuss it more.
<EDIT> Actually it looks like stored procs have been been added in 5.0, docs are at http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html, I'm stuck with 3.5*, so I won't be able to help you with this so much.
Rehcra
steve@oec-sysNOSPAM.com |
|
|
|
|
Rainchild
Wizard
Joined: 10 Oct 2000
Posts: 1551
Location: Australia
|
Posted: Wed Oct 06, 2004 10:43 pm
|
Aye, I've started working programatically to do it... its kinda annoying, I guess I'm just spoilt by the MS interface. I saw that 5 had started to add the stored procs, but I don't really want to be running an alpha version for the MUDs future storage... I mean people get a bit twitchy about their pfiles as it stands, we don't need to add an alpha state to them too ;)
Looks like MySQL is pretty limited, I'm using InnoDB tables so I can at least maintain foreign keys and stuff, but I dunno... I guess it is hard to write a databasing program but I woulda thought they'd have some of those basic necessities done ;)
The 'future' 5.1 / 5.2 version sounds like it'll be really good when it comes out, its just a matter of getting by until then, hehe.
As a side note, I got sent a link: www.firebirdsql.com which looks really interesting ... but I couldn't find any benchmarks on it to see how it stacked up against the competition. |
|
|
|
|
Silencer
Newbie
Joined: 23 Aug 2004
Posts: 8
Location: Tamworth, Australia
|
Posted: Sat Dec 18, 2004 9:53 am
|
Limited?!? That's a nice way to describe it, you weren't spoiled by MSSQL you were brainwashed. If you need direct access to MySQL server, you might as well put PHPmyadmin on your website if you have PHP support. Seriously MySQL is a lot more stable and functional once you're used to it. Heck one look at MSSQL made me gag, what a mess...
|
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
