just a blog ... just a note ...

Attach database

2009-04-02T12:27:00.001+07:00

In MsSQL, a database can attach with system stored procedure sp_attach_db. The simplest way to execute this stored procedure is with this syntax

EXEC sp_attach_db ‘databaseName’, ‘path/to/database/file’

Or if you want to attach more than 1 file use this syntax

EXEC sp_attach_db ‘databaseName’, ‘path/to/database/file/1’ [,‘path/to/database/file/2’] [,‘path/to/database/file/3’]

You can attach database file up to 16 files. The ‘path/to/database/file’ should no more than 260 characters long.

This stored procedure is a shortcut for creating a database with syntax

CREATE DATABASE databaseName ON (FILENAME = ‘/path/to/database/file/1’ [,FILENAME = ‘/path/to/database/file/2’] [FILENAME = ‘/path/to/database/file/3’] […FILENAME = ‘/path/to/database/file/16’]) FOR ATTACH

FOR ATTACH required all data files, and if multiple log files exists, they must be attach to

pake clob, bukan varchar2(4000)

2008-09-16T14:51:00.000+07:00

aargrgrhhh ....
ketemu lagi kolom yg seharusnya pake clob tapi di definisikan pake
varchar2(4000), ya gak bakalan cukuplah panjang data yg mau di inputkan.

ganti!

ALTER TABLE TABLENAME MODIFY COLUMNNAME CLOB

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME

butuh create function di oracle & mssql

2008-09-16T14:35:00.000+07:00

huaaahh ...
ternyata ada yg harus di rombak di query programku, gak bisa ditulis
pake coldfusion. jadinya sekarang harus nyari cara buat create function,
kalo gak, gak bakalan bisa sama jumlah yg ditampilkan.

hm ...

install ubuntu 804 alternate di ubuntu 710

2008-09-05T09:08:00.005+07:00

setelah beberapa hari mendownload iso ubuntu dari server kambing,http://kambing.ui.edu/pub/ubuntu/ubuntu/8.04/ubuntu-8.04-alternate-i386.iso
ukutan filenya 73096396 byte.

installnya pake innotek VirtualBox lanjutan dari penasaran gak bisa install versi servernya, sekilas udah jalan OK, tp masih mungkin masih perlu tambahan modul2 lain untuk bisa sesuai keinginan.

tujuan pertama installnya sih gara2 penasaran pengen install Oracle di
ubuntu, mungkin dicoba minggu depan, senin atau selasa. install Oracle biar bisa belajar sendiri gak tergantung dari server yg disediakan kantor, berarti installnya Oracle XE.

lebih baik install sendiri trus ya bisa sesuka hati mo diapain, gak akan ada yg ngomel kalo server gagal berjalan normal :p

Loading, Login, Desktop

reset nomor identity di MS SQL

2008-09-03T08:29:00.002+07:00

cara untuk reset nomor identity di MS SQL menggunakan perintah


DBCC CHECKIDENT (namatable, RESEED, nomoridentity)

contohnya jika kita punya tableA, kita ingin ketika melakukan INSERT, nomor identity menjadi nomor 1


DBCC CHECKIDENT (tableA, RESEED, 0)

ganti filesystem di ubuntu

2008-08-29T09:56:00.002+07:00

beberapa waktu yg lalu aku baru nyadar, ternyata ada 30G di hard disk PC
yg ku pake filesystem nya ntfs (/dev/sda8 mount sbg /media/Libs). ntfs?
hehe iya, gak ngerti juga nih, apa dulu salah setting atau ada yg
terlewati :p

berhubung sekarang sudah membutuhkan filesystem reiserfs, jadi tadi pagi
ku ganti aja. ternyata caranya cukup mudah, aku pake partition editor
bawaanya ubuntu, GParted.

langkah2nya :
1. buka GParted (System - Administration - Partition Editor) atau sudo gparted
2. klik kanan partisi yg akan diubah, unmount
3. klik kanan lagi, Format to - pilih filesystem yg diinginkan
4. apply all operation. tunggu aja bentar, ntar akan di format ulang.
5. klik kanan partisi, Mount on - klik pilihan mount point
6. ubah file /etc/fstab, ganti baris
/dev/sda8 /media/Data ntfs-3g defaults,locale=en_US.UTF-8 0 0
menjadi
/dev/sda8 /media/Libs reiserfs defaults 0 2
7. tambahkan baris di /etc/mtab
/dev/sda8 /media/Libs reiserfs rw 0 0

ketika di restart, otomatis /dev/sda8 dgn mount point /media/Libs telah
tersedia

install ubuntu 8.04 hardy heron di innotek virtualbox

2008-08-06T07:56:00.005+07:00

install ubuntu di ubuntu nih :p penasaran dah beberapa hari gak berhasil2 :p rencananya sih nantinya mau dijadikan server Oracle XE

instalnya pake iso, download dari http://kambing.ui.edu/pub/ubuntu/ubuntu/hardy/ubuntu-8.04-server-i386.iso

trus setelah proses install selesai, trus reboot, selalu ada pesan

Starting up ...
This kernel requires the following features not present on the CPU:
0:6
Unable to boot - please use a kernel appropriate for your CPU

ada solusinya di https://answers.launchpad.net/ubuntu/+question/39349

gak ngerti ah :p

NOT IN bisa diganti pake JOIN dan IS NULL

2008-08-05T07:24:00.002+07:00

beberapa bulan yg lalu aku mendapatkan sebuah query yg lambat bgt, setelah googling ternyata permasalahannya ada di kondisi NOT IN, di situ dijelaskan kalo NOT IN bisa diganti pake JOIN dan IS NULL, contoh penggunaannya sbb

NOT IN


SELECT TABLE_A.COLUMN_A
FROM   TABLE_A
WHERE  TABLE_A.COLUMN_A NOT IN
       (
          SELECT TABLE_B.COLUMN_A
          FROM   TABLE_B
       )

JOIN dan IS NULL


SELECT TABLE_A.COLUMN_A
FROM   TABLE_A
       LEFT JOIN TABLE_B
           ON TABLE_A.COLUMN_A = TABLE_B.COLUMN_A
WHERE  TABLE_B.COLUMN_A IS NULL

query ini udah ku coba di MSSQL dan Oracle, hasilnya bisa dieksekusi jauh lebih cepat

MySQL-Simple-Query-Browser-0.1.10

2007-04-19T06:02:00.001+07:00

I've write a simple script to manage MySQL database, you can see it at http://docs.google.com/Doc?id=dhr2c3jq_8g3vv2t

below is from that link, but not valid for write rules

#!/usr/bin/env python

"""
for my lovely parent

MySQL Simple Query Browser
"""

__scriptname__ = "MySQL Simple Query Browser"
__author__ = "Triyadhi Surahman"
__email__ = "<triyadhi.surahman@gmail.com> <justrelay@yahoo.com>"
__date__ = "20051224 04:57:29 PM"
__version__ = "0.1.10"

import wx
import wx.grid as gridlib
import wx.lib.dialogs as dlg

import MySQLdb

import string

# Constant
# Menu File
ID_FILE_QUIT = 10001
# Menu Database
ID_DATABASE_CONNECT = 10201
ID_DATABASE_DISCONNECT = 10202
ID_DATABASE_CREATE = 10203
ID_DATABASE_DROP = 10204
ID_DATABASE_REFRESH = 10205
# Menu Table
ID_TABLE_CREATE = 10301
ID_TABLE_DROP = 10302
# Menu Query
ID_QUERY_EXECUTE = 10401
# Menu Help
ID_HELP_MANUAL = 10501
ID_HELP_ABOUT = 10502

# Misc
ID_BUTTON_ADD_TABLE_NAME_TYPE = 20001
ID_BUTTON_ADD_CREATE_TABLE = 20002
ID_COMBOBOX_DATABASE = 20003
ID_COMBOBOX_TABLE = 20004

ERROR_MESSAGE = True

# Tested MySQL command, 1st word only
has_result = ['ANALYZE', 'BACKUP', 'CHECK', 'CHECKSUM', 'DESCRIBE', 'OPTIMIZE', 'REPAIR', 'RESTORE', 'SELECT', 'SHOW']
no_result = ['ALTER', 'CREATE', 'DELETE', 'DROP', 'GRANT', 'INSERT', 'SET', 'RENAME', 'REVOKE', 'TRUNCATE', 'UPDATE', 'USE']

class DatabaseConnectDialog(wx.Dialog):
def __init__(self, parent, id, title):
wx.Dialog.__init__(self, parent, id, title)

self.boxvertical = wx.BoxSizer(wx.VERTICAL)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
statictext = wx.StaticText(self, -1, "Connect To MySQL Server")
font = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
statictext.SetFont(font)
statictext.SetSize(statictext.GetBestSize())
boxhorizontal.Add(statictext, 0, wx.ALIGN_CENTER|wx.ALL, 5)
self.boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

gridsizer = wx.GridSizer(3, 2, 5, 5)

statictexthost = wx.StaticText(self, -1, "Host")
self.textctrlhost = wx.TextCtrl(self, -1, value="localhost")

statictextusername = wx.StaticText(self, -1, "Username")

#self.textctrlusername = wx.TextCtrl(self, -1)
self.textctrlusername = wx.TextCtrl(self, -1, value="root")

self.textctrlusername.SetFocus()

statictextpassword = wx.StaticText(self, -1, "Password")
self.textctrlpassword = wx.TextCtrl(self, -1, style=wx.TE_PASSWORD)

gridsizer.AddMany([
(statictexthost, 0, wx.EXPAND),
(self.textctrlhost, 0, wx.EXPAND),
(statictextusername, 0, wx.EXPAND),
(self.textctrlusername, 0, wx.EXPAND),
(statictextpassword, 0, wx.EXPAND),
(self.textctrlpassword, 0, wx.EXPAND)
])

self.boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTER | wx.ALL, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
self.checkbox = wx.CheckBox(self, -1, "&Use Default Port (3306)")
self.checkbox.SetValue(True)
self.Bind(wx.EVT_CHECKBOX, self.CheckBoxChangePort, self.checkbox)
boxhorizontal.Add(self.checkbox, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

self.boxvertical.Add(boxhorizontal, 0, wx.ALIGN_LEFT | wx.ALL, 5)

self.gridsizerport = wx.GridSizer(1, 2, 5, 5)
statictextport = wx.StaticText(self, -1, "Port")
self.textctrlport = wx.TextCtrl(self, -1)
self.gridsizerport.AddMany([
(statictextport, 0, wx.EXPAND),
(self.textctrlport, 0, wx.EXPAND)
])
self.boxhorizontalport = wx.BoxSizer(wx.HORIZONTAL)
self.boxhorizontalport.Add(self.gridsizerport, 0, wx.ALIGN_CENTER | wx.ALL, 0)
self.boxhorizontalport.Show(self.gridsizerport, 0)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
ok = wx.Button(self, wx.ID_OK, " &OK ")
ok.SetDefault()
boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
cancel = wx.Button(self, wx.ID_CANCEL, " &Cancel ")
boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
self.boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

self.SetSizer(self.boxvertical)
# We need it if we want to change the layout automatically
# see CheckBoxChangePort
self.SetAutoLayout(True)
self.boxvertical.Fit(self)

def CheckBoxChangePort(self, event):
"""
If you want to use another port instead the default port
Alt+U or click on *Use Default Port* checkbox
"""
if event.IsChecked() == 0:
self.boxvertical.Insert(3, self.boxhorizontalport, 0, wx.ALIGN_CENTER | wx.ALL, 5)
self.boxvertical.Show(self.boxhorizontalport)
elif event.IsChecked() == 1:
self.boxvertical.Detach(self.gridsizerport)
# hidding the box
self.boxvertical.Show(self.boxhorizontalport, 0)

self.boxvertical.RecalcSizes()
self.boxvertical.Fit(self)

class DatabaseCreateDialog(wx.Dialog) :
def __init__(self, parent, id, title) :
wx.Dialog.__init__(self, parent, id, title)
boxvertical = wx.BoxSizer(wx.VERTICAL)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
statictext = wx.StaticText(self, -1, "Create Database")
font = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
statictext.SetFont(font)
statictext.SetSize(statictext.GetBestSize())
boxhorizontal.Add(statictext, 0, wx.ALIGN_CENTER|wx.ALL, 5)
boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

gridsizer = wx.GridSizer(1, 2, 5, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
statictext = wx.StaticText(self, -1, "Database Name")
self.textctrl = wx.TextCtrl(self, -1)

gridsizer.AddMany([
(statictext, 0, wx.ALIGN_LEFT),
(self.textctrl, 0, wx.ALIGN_LEFT)
])

boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
ok = wx.Button(self, wx.ID_OK, " &OK ")
ok.SetDefault()
boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
cancel = wx.Button(self, wx.ID_CANCEL, " &Cancel ")
boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

self.SetSizer(boxvertical)
# We need it if we want to change the layout automatically
# see CheckBoxChangePort
self.SetAutoLayout(True)
boxvertical.Fit(self)

class DatabaseDropDialog(wx.Dialog) :
def __init__(self, parent, id, title, databases) :
wx.Dialog.__init__(self, parent, id, title)
boxvertical = wx.BoxSizer(wx.VERTICAL)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
statictext = wx.StaticText(self, -1, "Drop Database")
font = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
statictext.SetFont(font)
statictext.SetSize(statictext.GetBestSize())
boxhorizontal.Add(statictext, 0, wx.ALIGN_CENTER|wx.ALL, 5)
boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

gridsizer = wx.GridSizer(1, 2, 5, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
statictext = wx.StaticText(self, -1, "Database Name")
self.combobox = wx.ComboBox(self, -1, style=wx.CB_READONLY)

dbnamelist = databases

for d in dbnamelist :
self.combobox.Append(d)

gridsizer.AddMany([
(statictext, 0, wx.ALIGN_LEFT),
(self.combobox, 0, wx.ALIGN_LEFT)
])

boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
ok = wx.Button(self, wx.ID_OK, " &OK ")
ok.SetDefault()
boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
cancel = wx.Button(self, wx.ID_CANCEL, " &Cancel ")
boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

self.SetSizer(boxvertical)
# We need it if we want to change the layout automatically
# see CheckBoxChangePort
self.SetAutoLayout(True)
boxvertical.Fit(self)

class TableCreateDialog(wx.Dialog) :
def __init__(self, parent, id, title, databases) :
wx.Dialog.__init__(self, parent, id, title)

boxvertical = wx.BoxSizer(wx.VERTICAL)

notebook = wx.Notebook(self, -1)
paneltablename = wx.Panel(notebook, -1)
paneladd = wx.Panel(notebook, -1)
panelsyntax = wx.Panel(notebook, -1)
notebook.AddPage(paneltablename, "Name and Type")
notebook.AddPage(paneladd, "Add Fields")
notebook.AddPage(panelsyntax, "Syntax")

# BoxSizer vertical for paneltablename
boxptv = wx.BoxSizer(wx.VERTICAL)
gridsizer = wx.GridSizer(3, 2, 5, 5)
statictextdatabases = wx.StaticText(paneltablename, -1, "Database")
statictexttablename = wx.StaticText(paneltablename, -1, "Table Name")
statictexttabletype = wx.StaticText(paneltablename, -1, "Table Type")
self.comboboxdatabases = wx.ComboBox(paneltablename, -1)
self.textctrltablename = wx.TextCtrl(paneltablename, -1, size=self.comboboxdatabases.GetSize())
self.comboboxtabletype = wx.ComboBox(paneltablename, -1, style=wx.CB_READONLY)

dbnamelist = databases

for d in dbnamelist :
self.comboboxdatabases.Append(d)

tabletypelist = ['MyISAM', 'InnoDB', 'ISAM']

for t in tabletypelist :
self.comboboxtabletype.Append(t)

self.comboboxtabletype.SetValue(tabletypelist[0])

gridsizer.AddMany([
(statictextdatabases, 0, wx.ALIGN_LEFT),
(self.comboboxdatabases, 0, wx.ALIGN_LEFT),
(statictexttablename, 0, wx.ALIGN_LEFT),
(self.textctrltablename, 0, wx.ALIGN_LEFT),
(statictexttabletype, 0, wx.ALIGN_LEFT),
(self.comboboxtabletype, 0, wx.ALIGN_LEFT)
])

boxptv.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
button = wx.Button(paneltablename, ID_BUTTON_ADD_TABLE_NAME_TYPE, " &Add ")
boxhorizontal.Add(button, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
boxptv.Add(boxhorizontal, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

# event
self.Bind(wx.EVT_BUTTON, self.AddTableNameType, id=ID_BUTTON_ADD_TABLE_NAME_TYPE)

paneltablename.SetSizer(boxptv)
paneltablename.SetAutoLayout(True)
boxptv.Fit(paneltablename)

# BoxSizer vertical for paneladd
boxpav = wx.BoxSizer(wx.VERTICAL)
gridsizer = wx.GridSizer(9, 2, 5, 5)
statictextfieldname = wx.StaticText(paneladd, -1, "Field Name")
statictextdatatype = wx.StaticText(paneladd, -1, "Data Type")
statictextlengthvalues = wx.StaticText(paneladd, -1, "Length/Values")
statictextattributes = wx.StaticText(paneladd, -1, "Attributes")
statictextnull = wx.StaticText(paneladd, -1, "NULL")
statictextdefault = wx.StaticText(paneladd, -1, "Default")
statictextextra = wx.StaticText(paneladd, -1, "Extra")
statictextcolumndefinition = wx.StaticText(paneladd, -1, "Column Definition")
statictextfulltext = wx.StaticText(paneladd, -1, "Fulltext")

self.textctrlfieldname = wx.TextCtrl(paneladd, -1)
self.textctrlfieldname.SetFocus()

self.comboboxdatatype = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)

datatypelist = [
# numeric types
'TINYINT', 'BIT', 'BOOL', 'BOOLEAN', 'SMALLINT',
'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT', 'FLOAT',
'DOUBLE', 'DOUBLE PRECISION','REAL', 'DECIMAL', 'DEC',
'NUMERIC', 'FIXED',
# date and time types
'DATE', 'DATETIME', 'TIMESTAMP', 'TIME', 'YEAR',
# string types
'CHAR', 'VARCHAR', 'BINARY', 'VARBINARY', 'TINYBLOB',
'TINYTEXT', 'BLOB', 'TEXT', 'MEDIUMBLOB', 'MEDIUMTEXT',
'LONGBLOB', 'LONGTEXT', 'ENUM', 'SET'
]
for d in datatypelist :
self.comboboxdatatype.Append(d)

self.textctrllengthvalues = wx.TextCtrl(paneladd, -1)

self.comboboxattributes = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)

attributeslist = ['', 'BINARY', 'UNSIGNED', 'UNSIGNED ZEROFILL']
for u in attributeslist :
self.comboboxattributes.Append(u)

self.comboboxnull = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)

nulllist = ['', 'NULL', 'NOT NULL']
for n in nulllist :
self.comboboxnull.Append(n)

self.textctrldefault = wx.TextCtrl(paneladd, -1)

self.comboboxextra = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)

extralist = ['', 'AUTO_INCREMENT']
for e in extralist :
self.comboboxextra.Append(e)

self.comboboxcolumndefinition = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)

columndefinitionlist = ['', 'PRIMARY KEY', 'INDEX', 'UNIQUE']
for p in columndefinitionlist :
self.comboboxcolumndefinition.Append(p)

self.checkboxfulltext = wx.CheckBox(paneladd, -1)

gridsizer.AddMany([
(statictextfieldname, 0, wx.ALIGN_LEFT),
(self.textctrlfieldname, 0, wx.ALIGN_LEFT),
(statictextdatatype, 0, wx.ALIGN_LEFT),
(self.comboboxdatatype, 0, wx.ALIGN_LEFT),
(statictextlengthvalues, 0, wx.ALIGN_LEFT),
(self.textctrllengthvalues, 0, wx.ALIGN_LEFT),
(statictextattributes, 0, wx.ALIGN_LEFT),
(self.comboboxattributes, 0, wx.ALIGN_LEFT),
(statictextnull, 0, wx.ALIGN_LEFT),
(self.comboboxnull, 0, wx.ALIGN_LEFT),
(statictextdefault, 0, wx.ALIGN_LEFT),
(self.textctrldefault, 0, wx.ALIGN_LEFT),
(statictextextra, 0, wx.ALIGN_LEFT),
(self.comboboxextra, 0, wx.ALIGN_LEFT),
(statictextcolumndefinition, 0, wx.ALIGN_LEFT),
(self.comboboxcolumndefinition, 0, wx.ALIGN_LEFT),
(statictextfulltext, 0, wx.ALIGN_LEFT),
(self.checkboxfulltext, 0, wx.ALIGN_LEFT)
])
boxpav.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
button = wx.Button(paneladd, ID_BUTTON_ADD_CREATE_TABLE, " &Add ")
#self.Bind(wx.EVT_BUTTON, self.OnAddCreateTable, id=ID_BUTTON_ADD_CREATE_TABLE)
boxhorizontal.Add(button, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
boxpav.Add(boxhorizontal, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

# event
self.Bind(wx.EVT_BUTTON, self.AddField, id=ID_BUTTON_ADD_CREATE_TABLE)

paneladd.SetSizer(boxpav)
paneladd.SetAutoLayout(True)
boxpav.Fit(paneladd)

# BoxSizer vertical for panelsyntax
boxpsv = wx.BoxSizer(wx.VERTICAL)
self.textctrlsyntax = wx.TextCtrl(panelsyntax, -1,
style=wx.TE_MULTILINE | wx.HSCROLL | wx.TE_RICH,
size=paneladd.GetSize())
boxpsv.Add(self.textctrlsyntax, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

panelsyntax.SetSizer(boxpsv)
panelsyntax.SetAutoLayout(True)
boxpsv.Fit(panelsyntax)

boxvertical.Add(notebook, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
ok = wx.Button(self, wx.ID_OK, " &OK ")
ok.SetDefault()
boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
cancel = wx.Button(self, wx.ID_CANCEL, " &Cancel ")
boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

self.SetSizer(boxvertical)
# We need it if we want to change the layout automatically
# see CheckBoxChangePort
self.SetAutoLayout(True)
boxvertical.Fit(self)

self.ResetValue()

def ResetValue(self) :
self.syntaxvalue = ""
self.tabledetails = []
self.databasename = ""
self.tablename = ""
self.tabletype = ""
self.primarykey = ""
self.unique = ""
self.index = ""
self.fulltext = ""

def AddTableNameType(self, event) :
self.databasename = self.comboboxdatabases.GetValue()
self.tablename = self.textctrltablename.GetValue()
self.tabletype = self.comboboxtabletype.GetValue()
self.PanelSyntaxValue()

def AddField(self, event) :
item = ""
if len(self.tabledetails) > 0 :
item = ",n"

item += string.strip(self.textctrlfieldname.GetValue())
item += " " + self.comboboxdatatype.GetValue()

if len(string.strip(self.textctrllengthvalues.GetValue())) > 0 :
item += "(" + self.textctrllengthvalues.GetValue() + ")"

item += " " + self.comboboxattributes.GetValue()
item += " " + self.comboboxnull.GetValue()

if len(string.strip(self.textctrldefault.GetValue())) > 0 :
item += " DEFAULT '" + self.textctrldefault.GetValue() + "'"

item += " " + self.comboboxextra.GetValue()

if self.comboboxcolumndefinition.GetValue() == 'PRIMARY KEY' :
if len(self.primarykey) == 0 :
self.primarykey = self.textctrlfieldname.GetValue()
else :
self.primarykey += ", " + self.textctrlfieldname.GetValue()
elif self.comboboxcolumndefinition.GetValue() == 'INDEX' :
if len(self.index) == 0 :
self.index = self.textctrlfieldname.GetValue()
else :
self.index += ", " + self.textctrlfieldname.GetValue()
elif self.comboboxcolumndefinition.GetValue() == 'UNIQUE' :
if len(self.unique) == 0 :
self.unique = self.textctrlfieldname.GetValue()
else :
self.unique += ", " + self.textctrlfieldname.GetValue()

if self.checkboxfulltext.IsChecked() == 1 :
if len(self.fulltext) == 0 :
self.fulltext = self.textctrlfieldname.GetValue()
else :
self.fulltext += ", " + self.textctrlfieldname.GetValue()

self.tabledetails.append(item)

self.PanelSyntaxValue()

def PanelSyntaxValue(self) :
self.syntaxvalue = "CREATE TABLE " + self.databasename + "." + self.tablename + " (n"

for i in range(len(self.tabledetails)) :
self.syntaxvalue += self.tabledetails[i]

if len(self.primarykey) > 0 :
self.syntaxvalue += ",n PRIMARY KEY (" + self.primarykey + ")"

if len(self.index) > 0 :
self.syntaxvalue += ",n INDEX (" + self.index + ")"

if len(self.unique) > 0 :
self.syntaxvalue += ",n UNIQUE (" + self.unique + ")"

if len(self.fulltext) > 0 :
self.syntaxvalue += ",n FULLTEXT (" + self.fulltext + ")"

self.syntaxvalue += "n) TYPE=" + self.tabletype

self.textctrlsyntax.SetValue(self.syntaxvalue)

class TableDropDialog(wx.Dialog) :
def __init__(self, parent, id, title, connection, databases) :
wx.Dialog.__init__(self, parent, id, title)

self.connection = connection

boxvertical = wx.BoxSizer(wx.VERTICAL)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
statictext = wx.StaticText(self, -1, "Drop Table")
font = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
statictext.SetFont(font)
statictext.SetSize(statictext.GetBestSize())
boxhorizontal.Add(statictext, 0, wx.ALIGN_CENTER|wx.ALL, 5)
boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

gridsizer = wx.GridSizer(2, 2, 5, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
statictextdb = wx.StaticText(self, -1, "Database Name")
self.comboboxdb = wx.ComboBox(self, ID_COMBOBOX_DATABASE, style=wx.CB_READONLY)
statictexttable = wx.StaticText(self, -1, "Table Name")
self.comboboxtable = wx.ComboBox(self, ID_COMBOBOX_TABLE, style=wx.CB_READONLY)

self.Bind(wx.EVT_COMBOBOX, self.DatabaseSelected, id=ID_COMBOBOX_DATABASE)
self.Bind(wx.EVT_COMBOBOX, self.TableSelected, id=ID_COMBOBOX_TABLE)

dbnamelist = databases

for d in dbnamelist :
self.comboboxdb.Append(d)

gridsizer.AddMany([
(statictextdb, 0, wx.ALIGN_LEFT),
(self.comboboxdb, 0, wx.ALIGN_LEFT),
(statictexttable, 0, wx.ALIGN_LEFT),
(self.comboboxtable, 0, wx.ALIGN_LEFT)
])

boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
ok = wx.Button(self, wx.ID_OK, " &OK ")
ok.SetDefault()
boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
cancel = wx.Button(self, wx.ID_CANCEL, " &Cancel ")
boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

self.SetSizer(boxvertical)
# We need it if we want to change the layout automatically
# see CheckBoxChangePort
self.SetAutoLayout(True)
boxvertical.Fit(self)

def DatabaseSelected(self, event) :
self.comboboxtable.Clear()
self.GetTable(self.connection, self.comboboxdb.GetValue())

def GetTable(self, connection, dbname) :
connection.query("""USE """ + dbname)
connection.query("""SHOW TABLES""")
r = connection.store_result()
f = r.fetch_row(maxrows=0)

for i in range(len(f)) :
self.comboboxtable.Append(f[i][0])

def TableSelected(self, event) :
self.dropsyntax = "DROP TABLE " + self.comboboxdb.GetValue() + "." + self.comboboxtable.GetValue()

class HelpAbout(wx.Dialog) :
def __init__(self, parent, id, title) :
wx.Dialog.__init__(self, parent, id, title)

boxvertical = wx.BoxSizer(wx.VERTICAL)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
namelabel = wx.StaticText(self, -1, __scriptname__)
font = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
namelabel.SetFont(font)
namelabel.SetSize(namelabel.GetBestSize())
namelabel.SetSize(namelabel.GetBestSize())
boxhorizontal.Add(namelabel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

gridsizer = wx.GridSizer(3, 2, 5, 5)

authorlabel = wx.StaticText(self, -1, "Author")
authorvalue = wx.StaticText(self, -1, __author__)
emaillabel = wx.StaticText(self, -1, "Email")
emailvalue = wx.StaticText(self, -1, __email__)
versionlabel = wx.StaticText(self, -1, "Version")
versionvalue = wx.StaticText(self, -1, __version__)
datelabel = wx.StaticText(self, -1, "Date")
datevalue = wx.StaticText(self, -1, __date__)

gridsizer.AddMany([
(authorlabel, 0, wx.ALIGN_LEFT),
(authorvalue, 0, wx.ALIGN_LEFT),
(emaillabel, 0, wx.ALIGN_LEFT),
(emailvalue, 0, wx.ALIGN_LEFT),
(versionlabel, 0, wx.ALIGN_LEFT),
(versionvalue, 0, wx.ALIGN_LEFT),
(datelabel, 0, wx.ALIGN_LEFT),
(datevalue, 0, wx.ALIGN_LEFT)
])

boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)

boxhorizontal = wx.BoxSizer(wx.HORIZONTAL)
ok = wx.Button(self, wx.ID_OK, " &OK ")
ok.SetDefault()
boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)

self.SetSizer(boxvertical)
# We need it if we want to change the layout automatically
# see CheckBoxChangePort
self.SetAutoLayout(True)
boxvertical.Fit(self)

class HelpManual(dlg.ScrolledMessageDialog) :
def __init__(self, parent) :
f = open("manual.txt", "r")
msg = f.read()
f.close()

dlg.ScrolledMessageDialog.__init__(self, parent, msg, caption="Manual")

def OnOK(self, event) :
self.Destroy()
event.Skip()

class ErrorMessageDialog(dlg.ScrolledMessageDialog) :
def __init__(self, parent, msg) :
dlg.ScrolledMessageDialog.__init__(self, parent, msg, caption="Query Error")

def OnOK(self, event) :
self.Destroy()
event.Skip()

class MainFrame(wx.Frame):

# Style
style = {
'frame': wx.DEFAULT_FRAME_STYLE,
'splitter': wx.SUNKEN_BORDER | wx.CLIP_CHILDREN,
'textctrl': wx.TE_MULTILINE | wx.HSCROLL | wx.TE_RICH
}

def __init__(self, parent, title):
wx.Frame.__init__(self, parent, -1, title,
size=wx.Size(750, 550),
style=self.style['frame'])

self.CentreOnScreen()

# Menu Bar
menubar = wx.MenuBar()

# Menu : File
menufile = wx.Menu()
menufile.Append(ID_FILE_QUIT, "&QuittCtrl+Q")
menubar.Append(menufile, "&File")
# Event
self.Bind(wx.EVT_MENU, self.MenuFileQuit, id=ID_FILE_QUIT)

# Menu : Database
menudatabase = wx.Menu()
menudatabase.Append(ID_DATABASE_CONNECT, "C&onnecttF11")
menudatabase.Append(ID_DATABASE_DISCONNECT, "D&isconnecttF12")
menudatabase.AppendSeparator()
menudatabase.Append(ID_DATABASE_CREATE, "&Create")
menudatabase.Append(ID_DATABASE_DROP, "&Drop")
menudatabase.AppendSeparator()
menudatabase.Append(ID_DATABASE_REFRESH, "&RefreshtCtrl+R")

menubar.Append(menudatabase, "&Database")
# Event
self.Bind(wx.EVT_MENU, self.MenuDatabaseConnect, id=ID_DATABASE_CONNECT)
self.Bind(wx.EVT_MENU, self.MenuDatabaseDisconnect, id=ID_DATABASE_DISCONNECT)
self.Bind(wx.EVT_MENU, self.MenuDatabaseCreate, id=ID_DATABASE_CREATE)
self.Bind(wx.EVT_MENU, self.MenuDatabaseDrop, id=ID_DATABASE_DROP)
self.Bind(wx.EVT_MENU, self.MenuDatabaseRefresh, id=ID_DATABASE_REFRESH)

# Menu : Table
menutable = wx.Menu()
menutable.Append(ID_TABLE_CREATE, "&Create")
menutable.Append(ID_TABLE_DROP, "&Drop")

menubar.Append(menutable, "&Table")

# Event
self.Bind(wx.EVT_MENU, self.MenuTableCreate, id=ID_TABLE_CREATE)
self.Bind(wx.EVT_MENU, self.MenuTableDrop, id=ID_TABLE_DROP)

# Menu : Query
menuquery = wx.Menu()
menuquery.Append(ID_QUERY_EXECUTE, "&ExecutetF5")

menubar.Append(menuquery, "&Query")
# Event
self.Bind(wx.EVT_MENU, self.MenuQueryExecute, id=ID_QUERY_EXECUTE)

# Menu : Help
menuhelp = wx.Menu()
menuhelp.Append(ID_HELP_MANUAL, "&ManualtF1")
menuhelp.AppendSeparator()
menuhelp.Append(ID_HELP_ABOUT, "&About")
menubar.Append(menuhelp, "&Help")
# Event
self.Bind(wx.EVT_MENU, self.MenuHelpManual, id=ID_HELP_MANUAL)
self.Bind(wx.EVT_MENU, self.MenuHelpAbout, id=ID_HELP_ABOUT)

self.SetMenuBar(menubar)

# Status Bar
self.statusbar = wx.StatusBar(self)
self.statusbar.SetFieldsCount(4)
widths = [-2, -1, 50, 50]
self.statusbar.SetStatusWidths(widths)
self.SetStatusBar(self.statusbar)

self.statusbar.SetStatusText("No Database Selected", 1)

# Split the Window
splitter1 = wx.SplitterWindow(self, style=self.style['splitter'])
splitter2 = wx.SplitterWindow(splitter1, style=self.style['splitter'])

# TextCtrl
self.textctrl = wx.TextCtrl(splitter2, -1, style=self.style['textctrl'])
self.textctrl.SetFocus()

# Grid
self.grid = gridlib.Grid(splitter2, -1)
self.grid.CreateGrid(1, 1)
self.grid.SetColLabelValue(0, "")
self.grid.SetRowLabelValue(0, "")
self.grid.EnableEditing(0)
self.grid.SetColLabelSize(20)
self.grid.SetRowLabelSize(0)
self.gridcols = 0
self.gridrows = 0

# TreeCtrl
self.treectrl = wx.TreeCtrl(splitter1, -1)

self.Bind(wx.EVT_TREE_SEL_CHANGED, self.OnTreeSelChanged, self.treectrl)

splitter2.SplitHorizontally(self.textctrl, self.grid, -275)
splitter1.SplitVertically(self.treectrl, splitter2, 150)
splitter1.SetMinimumPaneSize(20)
splitter2.SetMinimumPaneSize(20)

def MenuFileQuit(self, event) :
"""
Alt+F+Q or Ctrl+Q
"""
self.Close()

event.Skip()

def MenuDatabaseConnect(self, event) :
"""
Show the database connect dialog, Alt+D+O or F11
"""

dcd = DatabaseConnectDialog(self, -1, "Connect")
dcd.CenterOnScreen()
dcd.Show()

if dcd.ShowModal() == wx.ID_OK :
_host = dcd.textctrlhost.GetValue()
_user = dcd.textctrlusername.GetValue()
_passwd = dcd.textctrlpassword.GetValue()

if dcd.checkbox.IsChecked() == 0 :
# we need port as an integer
_port = int(dcd.textctrlport.GetValue())
self.DBConnect(_host, _user, _passwd, _port)
else :
self.DBConnect(_host, _user, _passwd)

dcd.Destroy()

event.Skip()

def MenuDatabaseDisconnect(self, event) :
"""
Alt+D+I or F11
"""

try :
self.DBClose()
except :
pass

event.Skip()

def MenuDatabaseCreate(self, event) :
"""
Show the database create dialog, Alt+D+C or F11
"""

databasecreate = DatabaseCreateDialog(self, -1, "Create")
databasecreate.CenterOnScreen()
databasecreate.Show()

if databasecreate.ShowModal() == wx.ID_OK :
self.ExecuteSQL(self.SplitSQL("CREATE DATABASE " + databasecreate.textctrl.GetValue()))

databasecreate.Destroy()

event.Skip()

def MenuDatabaseDrop(self, event) :
"""
Show the database drop dialog, Alt+D+D or F11
"""

databasedrop = DatabaseDropDialog(self, -1, "Drop", self.databases)
databasedrop.CenterOnScreen()
databasedrop.Show()

if databasedrop.ShowModal() == wx.ID_OK :
self.ExecuteSQL(self.SplitSQL("DROP DATABASE " + databasedrop.combobox.GetValue()))

databasedrop.Destroy()

event.Skip()

def MenuDatabaseRefresh(self, event) :
"""
Refresh database, get host, databases, and tables, Alt+D+R or Ctrl+R
"""

try :
rootitem = self.treectrl.GetItemText(self.treectrl.GetRootItem())
self.treectrl.DeleteAllItems()
self.DBGetDatabasesAndTables(rootitem)
except :
pass

event.Skip()

def MenuTableCreate(self, event) :
"""
Show the table create dialog, Alt+T+C
"""

tablecreatedialog = TableCreateDialog(self, -1, "Create Table", self.databases)
tablecreatedialog.CenterOnScreen()
tablecreatedialog.Show()

if tablecreatedialog.ShowModal() == wx.ID_OK :
self.ExecuteSQL(self.SplitSQL(tablecreatedialog.textctrlsyntax.GetValue()))

tablecreatedialog.ResetValue()
tablecreatedialog.Destroy()

event.Skip()

def MenuTableDrop(self, event) :
"""
Show the table drop dialog, Alt+T+D
"""

tabledrop = TableDropDialog(self, -1, "Drop Table", self.db, self.databases)
tabledrop.CenterOnScreen()
tabledrop.Show()

if tabledrop.ShowModal() == wx.ID_OK :
self.ExecuteSQL(self.SplitSQL(tabledrop.dropsyntax))

tabledrop.Destroy()

event.Skip()

def MenuQueryExecute(self, event) :
"""
Execute query, Alt+Q+E or F5
"""

self.ExecuteSQL(self.SplitSQL(self.textctrl.GetValue()))

event.Skip()

def MenuHelpManual(self, event) :
"""
Show the Manual, Alt+H+M or F1
"""
helpmanual = HelpManual(self)
helpmanual.Show()

def MenuHelpAbout(self, event) :
"""
Show the About dialog, Alt+H+A
"""

helpabout = HelpAbout(self, -1, "About")
helpabout.CenterOnScreen()
helpabout.Show()

event.Skip()

def DBConnect(self, _host, _user, _passwd, _port=3306) :
try :
self.db = MySQLdb.connect(host=_host, user=_user, passwd=_passwd, port=_port)
self.DBGetDatabasesAndTables(_host)
except MySQLdb.Error, details:
print "Error ", details

if ERROR_MESSAGE :
err = ErrorMessageDialog(self, ("Error " + str(details)))
err.Show()

def DBGetDatabasesAndTables(self, _host) :
try :
self.treeroot = self.treectrl.AddRoot(_host)
self.db.query("""SHOW DATABASES""")
r = self.db.store_result()
fr = r.fetch_row(maxrows=0)

self.databases = []
self.dbmap = {}
for i in fr :
self.databases.append(i[0])

for i in self.databases :
d = self.treectrl.AppendItem(self.treeroot, i)
self.dbmap[i] = d
self.db.query("""USE """ + i)
self.db.query("""SHOW TABLES""")
rt = self.db.store_result()
frt = rt.fetch_row(maxrows=0)

tables = []
for j in frt :
tables.append(j[0])

for j in tables :
t = self.treectrl.AppendItem(d, j)

self.db.query("""DESCRIBE """ + j)
rf = self.db.store_result()
frf = rf.fetch_row(maxrows=0)
field = []

for k in frf :
field.append(k[0])

for k in field :
fi = self.treectrl.AppendItem(t, k)
self.treectrl.Expand(self.treeroot)
except MySQLdb.Error, details :
print "Error ", details

if ERROR_MESSAGE :
err = ErrorMessageDialog(self, ("Error " + str(details)))
err.Show()

def DBClose(self) :
self.db.close()
self.treectrl.DeleteAllItems()

def SplitSQL(self, sql) :
"""
currently only supported the '#' comment
"""

sql = string.strip(sql)

tup = []
b = False

# get the # and n location and place it in 'tup' list
for i in range(len(sql)) :
if sql[i] == "#" :
b = True
index = i

while b :
if index < len(sql) :
if sql[index] == "n" :
b = False
front = i
end = index
index += 1
else :
b = False
front = i
end = index

tup.append((front, end))
index = 0

tup2 = []

# get char >= tup[i][0] and <= tup[i][1] and place it in tup2
for i in range(len(tup)) :
tup2.append("")

for j in range(tup[i][0], tup[i][1]) :
tup2[i] += sql[j]

# replace sql char with "" when it's found in tup2
for i in range(len(tup2)) :
try :
sql = string.replace(sql, tup2[i], "")
except :
pass

# replace, split, strip, and return
sql = string.replace(sql, "n", "")
ret = sql.split(";")
for i in range(len(ret)) :
ret[i] = string.strip(ret[i])

return ret

def ExecuteSQL(self, sql=[]) :
query = []
query = sql

success = False
errormessage = ""

for q in range(len(query)) :
# MySQL command
command = query[q].split(" ", 1)

if len(query[q]) > 0 :
# Set up the grid
self.grid.DeleteCols(0, self.gridcols, 1)
self.grid.DeleteRows(0, self.gridrows, 1)
self.grid.SetColLabelValue(0, "")
self.grid.SetColSize(0, self.grid.GetDefaultColSize())
self.grid.SetRowSize(0, self.grid.GetDefaultRowSize())
self.grid.ClearGrid()

try :
self.db.query(query[q])
r = self.db.store_result()
success = True

except MySQLdb.Error, detail :
success = False
self.gridcols = 0
self.gridrows = 0
print "Error", detail

errormessage += query[q] + "nError " + str(detail) + "nnn"

if success :
if command[0].upper() in has_result :
f = r.fetch_row(maxrows=0, how=1)
if len(f) >= 1 :
self.gridcols = len(f[0].keys())
self.gridrows = len(f)

self.grid.InsertCols(0, (self.gridcols - 1))
for i in range(len(f[0].keys())) :
self.grid.SetColLabelValue(i, f[0].keys()[i])

self.grid.InsertRows(1, len(f) - 1, 0)
for i in range(len(f)) :
for j in range(len(f[i].values())) :
if f[i].values()[j] == None :
self.grid.SetCellValue(i, j, "NULL")
else :
self.grid.SetCellValue(i, j, str(f[i].values()[j]))

self.grid.AutoSizeColumns(1)
self.grid.AutoSizeRows(1)
self.gridcols = j
self.gridrows = i
self.statusbar.SetStatusText(str(self.gridrows + 1), 2)

else :
self.gridcols = 0
self.gridrows = 0

elif command[0].upper() in no_result :
self.gridcols = 0
self.gridrows = 0
self.statusbar.SetStatusText("0", 2)

if command[0].upper() == 'USE' :
try :
item = self.dbmap[command[1].strip()]
self.treectrl.SelectItem(item)
self.treectrl.EnsureVisible(item)
self.treectrl.Expand(item)
except :
pass
else :
self.gridcols = 0
self.gridrows = 0
m = "Sorry, not supported or not tested yet."
print "Sorry, not supported or not tested yet."

errormessage += query[q] + "n" + m

else :
pass

if ERROR_MESSAGE :
if len(errormessage) > 0 :
err = ErrorMessageDialog(self, errormessage)
err.Show()

def OnTreeSelChanged(self, event) :
dbname = self.treectrl.GetItemText(event.GetItem())

if dbname in self.databases :
self.db.select_db(dbname)
self.statusbar.SetStatusText(str(dbname), 1)

event.Skip()

def OnCloseWindow(self, event):
try :
self.DBClose()
except :
pass

self.Destroy()

class MyApp(wx.App):
"""
We must have it
"""
def OnInit(self):
frame = MainFrame(None, __scriptname__)
frame.Show()
self.SetTopWindow(frame)
return True

def main():
app = MyApp(False)
app.MainLoop()

if __name__ == "__main__":
main()