Gambas2 with Postfix / MySQL
This little program is used to create domains and add users for a custom mail server. I wrote it in Gambas2 which works very much like Microsoft VB6. It is easy to connect to any database. Mysql, PostgreSQL and others.
The image is of the actual program
Gambas2 works on Linux as well as Windows. In this case, I am running it on the latest version of Debian. Actually on a virtual desktop using XEN.
Below is the actual source code.
‘ Gambas class file
PUBLIC $Con AS NEW Connection
PUBLIC intEventNumber AS Integer
PUBLIC SUB Form_Open()
DIM $Query AS String
DIM $UserQuery AS String
DIM i AS Integer
DIM j AS Integer
DIM MyRS AS Result
DIM ChRS AS Result
Connect() ‘ Run the Procedure to connect
FMain.Visible = TRUE ‘ The main form of your program
$Query = “SELECT domain FROM domain”
MyRS = $Con.Exec($Query)
Listdomains.Clear
FOR i = 1 TO MyRS.Count
domainlist.Add(MyRS!domain)
Listdomains.Add(MyRS!domain)
delcombobox.Add(MyRS!domain)
Domains.Add(MyRS!domain, MyRS!domain)
$UserQuery = “SELECT * FROM mailbox where domain = ‘” & MyRS!domain & “‘”
ChRS = $Con.Exec($UserQuery)
FOR j = 1 TO ChRS.Count
Domains.Add(ChRS!username, ChRS!username, NULL, MyRS!domain)
ChRS.MoveNext
NEXT
MyRS.MoveNext
NEXT
‘– Bookmark: Page 165
END
PUBLIC SUB makeaccount_Click()
DIM uname AS String
DIM passwd AS String
DIM domainname AS String
DIM isMyRS AS Result
DIM ChRS AS Result
DIM $UserQuery AS String
DIM $QuerySQL AS String
SELECT Message.Question(“Add User?”, “Yes”, “No”)
CASE 1
uname = createuser.Text
passwd = createpasswd.Text
domainname = domainlist.Text
IF NOT uname OR NOT passwd OR NOT domainname THEN
Message.Warning(“Missing information!”)
RETURN
END IF
$UserQuery = “SELECT * FROM mailbox where domain = ‘” & domainname & “‘ and username = ‘” & uname & “@” & domainname & “‘ and maildir = ‘” & uname & “@” & domainname & “‘”
isMyRS = $Con.Exec($UserQuery)
IF isMyRS.Length > 0 THEN
Message.Info(“Sorry User: ” & uname & “@” & domainname & ” already exists”)
END IF
IF isMyRS.Length = 0 THEN
$QuerySQL = “insert into mailbox(username,password,maildir,domain) values(‘” & uname & “@” & domainname & “‘,’” & passwd & “‘,’” & uname & “@” & domainname & “‘,’” & domainname & “‘)”
$Con.Exec($QuerySQL)
END IF
END SELECT
refreshtree()
END
PUBLIC SUB Domains_Click()
DIM uname AS String
DIM passwd AS String
DIM domainname AS String
DIM isMyRS AS Result
DIM ChRS AS Result
DIM $UserQuery AS String
DIM $QuerySQL AS String
‘ updateuser.Text =
intEventNumber = intEventNumber + 1
IF Domains.item.Children > 1 THEN
domainlist.Text = Domains.Item.Text
ELSE IF Domains.item.Children = 0 THEN
$UserQuery = “SELECT * FROM mailbox where username = ‘” & Domains.Item.Text & “‘”
isMyRS = $Con.Exec($UserQuery)
IF isMyRS.Length > 0 THEN
updateuser.Text = Domains.Item.Text
updatepasswd.text = isMyRS!password
END IF
ELSE
domainlist.Text = Domains.Item.Text
END IF
END
PUBLIC PROCEDURE Connect()
$Con.Type = “MySQL” ‘ Type of connection
$Con.Host = “” ‘ Name of the server
$Con.Login = “” ‘ User’s name for the connection
$Con.Port = “3306″ ‘ Port to use in the connection, usually 3306
$Con.Name = “postfix” ‘ Name of the database we want to use
$Con.Password = “” ‘ User’s password
$Con.Open() ‘ Open the connection
END
PUBLIC SUB Done_Click()
$Con.Close() ‘ Close the connection
FMain.Close()
END
PUBLIC SUB updateuserbutton_Click()
DIM $UserQuery AS String
DIM uname AS String
DIM passwd AS String
DIM domainname AS String
DIM newuser AS String[]
DIM npasswd AS String
DIM actchk AS String
DIM isMyRS AS Result
actchk = activechk.Text
IF updatepasswd.text AND newpasswd.Text THEN
$UserQuery = “SELECT * FROM mailbox where username = ‘” & updateuser.Text & “‘ and password=’” & updatepasswd.text & “‘”
isMyRS = $Con.Exec($UserQuery)
IF isMyRS.Length > 0 THEN
$UserQuery = “update mailbox set password=’” & newpasswd.Text & “‘ where password=’” & updatepasswd.text & “‘”
$Con.Exec($UserQuery)
updatepasswd.text = newpasswd.Text
newpasswd.Text = “”
END IF
END IF
refreshtree()
END
PUBLIC SUB deleteuser_Click()
DIM $UserQuery AS String
DIM uname AS String
DIM passwd AS String
DIM domainname AS String
DIM newuser AS String[]
SELECT Message.Question(“Delete User?”, “Yes”, “No”)
CASE 1
IF NOT updateuser.text THEN
Message.Warning(“Missing information!”)
RETURN
END IF
newuser = Split(updateuser.Text, “@”)
$UserQuery = “delete FROM mailbox where domain = ‘” & newuser[1] & “‘ and username = ‘” & newuser[0] & “@” & newuser[1] & “‘”
‘updatepasswd.Text = $UserQuery
$Con.Exec($UserQuery)
refreshtree()
END SELECT
END
PUBLIC SUB refreshtree()
DIM $Query AS String
DIM $UserQuery AS String
DIM i AS Integer
DIM j AS Integer
DIM MyRS AS Result
DIM ChRS AS Result
$Query = “SELECT domain FROM domain”
MyRS = $Con.Exec($Query)
Domains.Clear()
domainlist.Clear()
Listdomains.Clear()
delcombobox.Clear()
FOR i = 1 TO MyRS.Count
Listdomains.Add(MyRS!domain)
domainlist.Add(MyRS!domain)
delcombobox.Add(MyRS!domain)
Domains.Add(MyRS!domain, MyRS!domain)
$UserQuery = “SELECT * FROM mailbox where domain = ‘” & MyRS!domain & “‘”
ChRS = $Con.Exec($UserQuery)
IF ChRS.Length > 0 THEN
FOR j = 1 TO ChRS.Count
Domains.Add(ChRS!username, ChRS!username, NULL, MyRS!domain)
ChRS.MoveNext
NEXT
END IF
MyRS.MoveNext
NEXT
END
PUBLIC SUB adddomain_Click()
‘Add Domain
DIM isMyRS AS Result
DIM $QuerySQL AS String
IF adddomfield.Text THEN
$QuerySQL = “SELECT domain FROM domain where domain=’” & adddomfield.Text & “‘”
isMyRS = $Con.Exec($QuerySQL)
IF isMyRS.Length = 0 THEN
$QuerySQL = “insert into domain(domain) values(‘” & adddomfield.Text & “‘)”
$Con.Exec($QuerySQL)
END IF
ELSE
Message.Error(“Name missing!”)
END IF
refreshtree()
END
PUBLIC SUB activechk_Click()
END
PUBLIC SUB Form_Resize()
‘ Resize objects
TabStrip1.Width = ME.Width – (Domains.Width + 50)
TabStrip1.Height = ME.Height – (domainlist.height + createuser.height + createpasswd.height + Label4.height + Label3.height + Label2.height + Done.Height * 3 + 50)
Domains.Height = ME.Height – 50
Done.x = ME.width – (Done.width + 10)
Done.y = ME.Height – (Done.Height + 10)
Listdomains.height = TabStrip1.height – (adddomfield.height + Label1.height + 100)
Listdomains.width = TabStrip1.width – 60
END
PUBLIC SUB Button1_Click()
DIM deletedomSQL AS String
DIM deletemailbox AS String
SELECT Message.Question(“Delete ” & delcombobox.Text & “?”, “Yes”, “No”)
CASE 1
deletedomSQL = “delete from domain where domain = ‘” & delcombobox.Text & “‘”
‘Message.Info(deletedomSQL)
$Con.Exec(deletedomSQL)
deletemailbox = “delete from mailbox where domain = ‘” & delcombobox.Text & “‘”
‘Message.Info(deletedomSQL)
$Con.Exec(deletedomSQL)
refreshtree()
RETURN
END SELECT
END
The Form: Main.Form
# Gambas Form File 2.0
{ Form Form
MoveScaled(0,0,87,61)
Text = (“VIP Mail”)
{ Label2 Label
MoveScaled(32,2,16,2)
Text = (“Choose Domain”)
}
{ Domains TreeView
MoveScaled(1,2,29,57)
}
{ domainlist ComboBox
MoveScaled(32,4,21,3)
Text = (“”)
}
{ Label3 Label
MoveScaled(32,7,13,2)
Text = (“New User Name”)
}
{ createuser TextBox
MoveScaled(32,9,21,3)
Text = (“”)
}
{ Label4 Label
MoveScaled(32,12,16,2)
Text = (“Password”)
}
{ createpasswd TextBox
MoveScaled(32,14,21,3)
Text = (“”)
}
{ makeaccount Button
MoveScaled(32,18,15,3)
Text = (“Create User”)
}
{ TabStrip1 TabStrip
MoveScaled(32,23,53,33)
Count = 5
Index = 0
Text = (“Users”)
{ activechk CheckBox
MoveScaled(31,4,13,2)
Text = (“Active”)
}
{ updatepasswd TextBox
MoveScaled(2,9,22,3)
Text = (“”)
}
{ updateuserbutton Button
MoveScaled(2,19,17,3)
Text = (“Update”)
}
{ deleteuser Button
MoveScaled(21,19,15,3)
Text = (“Delete User”)
}
{ Label5 Label
MoveScaled(2,1,30,2)
Text = (“User Name (username@domain.com)”)
}
{ Label6 Label
MoveScaled(2,7,30,2)
Text = (“Old Password”)
}
{ newpasswd TextBox
MoveScaled(2,15,22,3)
Text = (“”)
}
{ Label7 Label
MoveScaled(2,13,17,2)
Text = (“New Password”)
}
{ updateuser TextBox
MoveScaled(2,3,22,3)
Text = (“”)
}
Index = 1
Text = (“Add Domain”)
{ adddomfield TextBox
MoveScaled(2,4,29,3)
Text = (“”)
}
{ Label1 Label
MoveScaled(2,1,13,2)
Text = (“Create Domain”)
}
{ adddomain Button
MoveScaled(34,4,9,3)
Text = (“Add”)
}
{ Listdomains ListBox
MoveScaled(2,9,41,19)
}
Index = 2
Text = (“Remove Domain”)
{ delcombobox ComboBox
MoveScaled(2,2,26,3)
Text = (“”)
}
{ Button1 Button
MoveScaled(30,2,9,3)
Text = (“Remove”)
}
Index = 3
Text = (“Aliases”)
{ alias TextBox
MoveScaled(26,3,24,3)
Text = (“”)
}
{ address ComboBox
MoveScaled(2,3,23,3)
Text = (“”)
}
{ Label8 Label
MoveScaled(2,1,13,2)
Text = (“Address”)
}
{ Label9 Label
MoveScaled(26,1,5,2)
Text = (“GoTo”)
}
Index = 4
Text = (“Forwarding”)
Index = 0
}
{ Done Button
MoveScaled(73,57,12,3)
Text = (“Done”)
}
}