jercfd Posted December 26, 2009 Share Posted December 26, 2009 (edited) Just a simple song database manager using SQLite. Features:Able to save Name, Artist, Album, Year, Album Cover Image, and Lyrics.Menu to select artists and albums already entered into database.View all entries on single tab.Based of Gerifield's Exampleexpandcollapse popup#include <ButtonConstants.au3> #include <ComboConstants.au3> #include <DateTimeConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <ListViewConstants.au3> #include <StaticConstants.au3> #include <TabConstants.au3> #include <WindowsConstants.au3> #include <SQLite.au3> #include <File.au3> #include <GuiListView.au3> Opt("GUIOnEventMode", 1) _SQLite_Startup(@ScriptDir & "\SQLite3.dll") If FileExists(@ScriptDir & "\Songs.db") Then $hDatabaseFile = _SQLite_Open(@ScriptDir & "\Songs.db") Else $hDatabaseFile = _SQLite_Open(@ScriptDir & "\Songs.db") _SQLite_Exec($hDatabaseFile, "CREATE TABLE SongDB (ID,Name,Artist,Album,Year,Album_Cover,Lyrics);") EndIf $hGUI = GUICreate("Song Database Manager", 641, 481, -1, -1, BitOR($WS_MAXIMIZEBOX, $WS_MINIMIZEBOX, $WS_SIZEBOX, $WS_THICKFRAME, $WS_SYSMENU, $WS_CAPTION, $WS_OVERLAPPEDWINDOW, $WS_TILEDWINDOW, $WS_POPUP, $WS_POPUPWINDOW, $WS_GROUP, $WS_TABSTOP, $WS_BORDER, $WS_CLIPSIBLINGS)) GUISetOnEvent($GUI_EVENT_CLOSE, "_hGUIEvents", $hGUI) $hFile = GUICtrlCreateMenu("&File") $hFile_Exit = GUICtrlCreateMenuItem("Exit" & @TAB & "Alt+F4", $hFile) GUICtrlSetOnEvent(-1, "_hMenuEvents") $hEdit = GUICtrlCreateMenu("&Edit") $hEdit_Previous = GUICtrlCreateMenuItem("Previous Entry", $hEdit) GUICtrlSetOnEvent(-1, "_hMenuEvents") $hEdit_Next = GUICtrlCreateMenuItem("Next Entry", $hEdit) GUICtrlSetOnEvent(-1, "_hMenuEvents") GUICtrlCreateMenuItem("", $hEdit) $hEdit_Save = GUICtrlCreateMenuItem("Save Current Entry", $hEdit) GUICtrlSetOnEvent(-1, "_hMenuEvents") $hEdit_Delete = GUICtrlCreateMenuItem("Delete Current Entry", $hEdit) GUICtrlSetOnEvent(-1, "_hMenuEvents") $hTab = GUICtrlCreateTab(10, 10, 620, 440) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKBOTTOM) $hEditTab = GUICtrlCreateTabItem("Edit Database") $hSongEntrySave = GUICtrlCreateButton("Save Entry", 19, 40, 375, 45, $BS_DEFPUSHBUTTON) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKHEIGHT) GUICtrlSetOnEvent(-1, "_hEditDatabaseEvents") $hSongEntryDelete = GUICtrlCreateButton("Delete Entry", 399, 65, 75, 20) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlSetOnEvent(-1, "_hEditDatabaseEvents") $hMultiLeft = GUICtrlCreateButton("<<<", 399, 40, 40, 20) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlSetOnEvent(-1, "_hEditDatabaseEvents") $hLeft = GUICtrlCreateButton("<", 444, 40, 40, 20) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlSetOnEvent(-1, "_hEditDatabaseEvents") $iEntryID = 1 $hEntryNumber = GUICtrlCreateLabel($iEntryID, 489, 42, 40, 14, $SS_CENTER) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $hRight = GUICtrlCreateButton(">", 534, 40, 40, 20) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlSetOnEvent(-1, "_hEditDatabaseEvents") $hMultiRight = GUICtrlCreateButton(">>>", 579, 40, 40, 20) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlSetOnEvent(-1, "_hEditDatabaseEvents") $hGotoEntry = GUICtrlCreateButton("Goto:", 524, 65, 45, 20) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlSetOnEvent(-1, "_hEditDatabaseEvents") $hGotoEntryNumber = GUICtrlCreateInput($iEntryID, 574, 65, 45, 21, BitOR($ES_CENTER, $ES_AUTOHSCROLL, $ES_NUMBER)) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlCreateLabel("Song Name:", 33, 98, 63, 14) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $hSongName = GUICtrlCreateInput("", 99, 95, 220, 21) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlCreateLabel("Artist:", 66, 123, 30, 14) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $sAllArtists = "" _GetAllArtists() $hSongArtist = GUICtrlCreateCombo($sAllArtists, 99, 120, 220, 25) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlCreateLabel("Album:", 60, 148, 36, 14) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $sAllAlbums = "" _GetAllAlbums() $hSongAlbum = GUICtrlCreateCombo($sAllAlbums, 99, 145, 220, 25) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlCreateLabel("Year Released:", 19, 173, 77, 14) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $hSongYear = GUICtrlCreateDate(@YEAR, 99, 170, 220, 21, $DTS_UPDOWN) GUICtrlSendMsg(-1, 0x1032, 0, "yyyy") GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlCreateLabel("Album Cover:", 29, 198, 67, 14) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $sSongCoverBinary = "" $hSongCover = GUICtrlCreatePic("", 99, 195, 220, 220, 0) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $hSongCoverSelect = GUICtrlCreateButton("Select Image", 244, 395, 75, 20) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlSetOnEvent(-1, "_hEditDatabaseEvents") GUICtrlCreateLabel("Lyrics:", 334, 98, 34, 14) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $hSongLyrics = GUICtrlCreateEdit("", 334, 115, 285, 325) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKBOTTOM) $hBrowseTab = GUICtrlCreateTabItem("Browse Database") Local $iDBSuccess, $asSongDB, $iRows, $iColumns, $sDBHeader, $ia $iDBSuccess = _SQLite_GetTable2d($hDatabaseFile, "SELECT * FROM SongDB ORDER BY ID;", $asSongDB, $iRows, $iColumns) If $iDBSuccess = $SQLITE_OK Then $sDBHeader = "#|" For $ia = 0 To UBound($asSongDB, 2) - 1 If $asSongDB[0][$ia] <> "Album_Cover" Then $sDBHeader &= $asSongDB[0][$ia] & "|" EndIf Next EndIf $hBrowseDatabase = GUICtrlCreateListView($sDBHeader, 19, 40, 600, 375) GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKBOTTOM) _GUICtrlListView_SetColumnWidth($hBrowseDatabase, 0, $LVSCW_AUTOSIZE_USEHEADER) _SongBrowseDatabase() $hRefreshDB = GUICtrlCreateButton("Refresh", 19, 420, 75, 20) GUICtrlSetOnEvent(-1, "_hBrowseDatabaseEvents") $hGotoDBEntry = GUICtrlCreateButton("Goto:", 524, 420, 45, 20) GUICtrlSetOnEvent(-1, "_hBrowseDatabaseEvents") GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) $hGotoDBEntryNumber = GUICtrlCreateInput("1", 574, 420, 45, 21, BitOR($ES_CENTER, $ES_AUTOHSCROLL, $ES_NUMBER)) GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) GUICtrlCreateTabItem("") _SongEntryOpen() GUISetState(@SW_SHOW, $hGUI) While 1 Sleep(100) WEnd Func _hGUIEvents() Switch @GUI_CtrlId Case $GUI_EVENT_CLOSE _Exit() EndSwitch EndFunc ;==>_hGUIEvents Func _hMenuEvents() Switch @GUI_CtrlId Case $hFile_Exit _Exit() Case $hEdit_Previous $iEntryID -= 1 If $iEntryID < 1 Then $iEntryID = 1 GUICtrlSetData($hEntryNumber, $iEntryID) GUICtrlSetData($hGotoEntryNumber, $iEntryID) _SongEntryOpen() Case $hEdit_Next $iEntryID += 1 If $iEntryID > 65000 Then $iEntryID = 65000 GUICtrlSetData($hEntryNumber, $iEntryID) GUICtrlSetData($hGotoEntryNumber, $iEntryID) _SongEntryOpen() Case $hEdit_Save If StringIsSpace(GUICtrlRead($hSongName)) Or GUICtrlRead($hSongName) == "" Then MsgBox(16, "Error", "Song Name is empty.") Else _SongEntrySave() EndIf Case $hEdit_Delete Local $iDeleteQuestion $iDeleteQuestion = MsgBox(36, "Delete Current Entry", "Are you sure you want to delete the current entry?") If $iDeleteQuestion = 6 Then _SongEntryDelete() EndSwitch EndFunc ;==>_hMenuEvents Func _hEditDatabaseEvents() Switch @GUI_CtrlId Case $hMultiLeft $iEntryID -= 3 If $iEntryID < 1 Then $iEntryID = 1 GUICtrlSetData($hEntryNumber, $iEntryID) GUICtrlSetData($hGotoEntryNumber, $iEntryID) _SongEntryOpen() Case $hLeft $iEntryID -= 1 If $iEntryID < 1 Then $iEntryID = 1 GUICtrlSetData($hEntryNumber, $iEntryID) GUICtrlSetData($hGotoEntryNumber, $iEntryID) _SongEntryOpen() Case $hRight $iEntryID += 1 If $iEntryID > 65000 Then $iEntryID = 65000 GUICtrlSetData($hEntryNumber, $iEntryID) GUICtrlSetData($hGotoEntryNumber, $iEntryID) _SongEntryOpen() Case $hMultiRight $iEntryID += 3 If $iEntryID > 65000 Then $iEntryID = 65000 GUICtrlSetData($hEntryNumber, $iEntryID) GUICtrlSetData($hGotoEntryNumber, $iEntryID) _SongEntryOpen() Case $hGotoEntry $iEntryID = Round(Number(GUICtrlRead($hGotoEntryNumber))) If $iEntryID < 1 Then $iEntryID = 1 If $iEntryID > 65000 Then $iEntryID = 65000 GUICtrlSetData($hEntryNumber, $iEntryID) GUICtrlSetData($hGotoEntryNumber, $iEntryID) _SongEntryOpen() Case $hSongCoverSelect Local $sSongCoverLocation $sSongCoverLocation = FileOpenDialog("Select Album Art", @DesktopDir, "Images (*.BMP; *.JPG; *.JPEG; *.GIF)", 1, "", $hGUI) If Not @error And FileExists($sSongCoverLocation) Then GUICtrlSetImage($hSongCover, $sSongCoverLocation) $sSongAlbumCoverFile = FileOpen($sSongCoverLocation, 16) $sSongAlbumCover = FileRead($sSongAlbumCoverFile) FileClose($sSongAlbumCoverFile) $sSongCoverBinary = $sSongAlbumCover EndIf Case $hSongEntrySave If StringIsSpace(GUICtrlRead($hSongName)) Or GUICtrlRead($hSongName) == "" Then MsgBox(16, "Error", "Song Name is empty.") Else _SongEntrySave() EndIf Case $hSongEntryDelete Local $iDeleteQuestion $iDeleteQuestion = MsgBox(36, "Delete Current Entry", "Are you sure you want to delete the current entry?") If $iDeleteQuestion = 6 Then _SongEntryDelete() EndSwitch EndFunc ;==>_hEditDatabaseEvents Func _SongEntryOpen() Local $iSongID, $asDatabaseLine, $sTempFile $iSongID = $iEntryID GUICtrlSetData($hSongName, "") GUICtrlSetData($hSongArtist, "") GUICtrlSetData($hSongAlbum, "") GUICtrlSetData($hSongYear, @YEAR) GUICtrlSetImage($hSongCover, "") $sSongCoverBinary = 0 GUICtrlSetData($hSongLyrics, "") If _SQLite_QuerySingleRow($hDatabaseFile, "SELECT * FROM SongDB WHERE ID='" & $iSongID & "';", $asDatabaseLine) == $SQLITE_OK Then If $asDatabaseLine[0] == "" Then GUICtrlSetData($hSongArtist, $sAllArtists) GUICtrlSetData($hSongAlbum, $sAllAlbums) ;MsgBox(0,"ERROR","Query error!") Else GUICtrlSetData($hSongName, $asDatabaseLine[1]) GUICtrlSetData($hSongArtist, $sAllArtists, $asDatabaseLine[2]) GUICtrlSetData($hSongAlbum, $sAllAlbums, $asDatabaseLine[3]) GUICtrlSetData($hSongYear, $asDatabaseLine[4]) $sTempFile = _TempFile() FileWrite($sTempFile, Binary($asDatabaseLine[5])) GUICtrlSetImage($hSongCover, $sTempFile) FileDelete($sTempFile) $sSongCoverBinary = $asDatabaseLine[5] GUICtrlSetData($hSongLyrics, $asDatabaseLine[6]) EndIf EndIf EndFunc ;==>_SongEntryOpen Func _SongEntrySave() Local $iSongID, $sSongName, $sSongArtist, $sSongAlbum, $sSongYear, $sSongAlbumCoverFile, $sSongAlbumCover, $sSongLyrics, $asDatabaseLine $iSongID = $iEntryID $sSongName = GUICtrlRead($hSongName) $sSongArtist = GUICtrlRead($hSongArtist) $sSongAlbum = GUICtrlRead($hSongAlbum) $sSongYear = GUICtrlRead($hSongYear) $sSongAlbumCover = $sSongCoverBinary $sSongLyrics = GUICtrlRead($hSongLyrics) _SQLite_QuerySingleRow($hDatabaseFile, "SELECT ID FROM SongDB WHERE ID='" & $iSongID & "';", $asDatabaseLine) If $asDatabaseLine[0] <> "" Then _SQLite_Exec($hDatabaseFile, "UPDATE SongDB SET Name='" & $sSongName & "',Artist='" & $sSongArtist & "',Album='" & $sSongAlbum & "',Year='" & $sSongYear & "',Album_Cover='" & $sSongAlbumCover & "',Lyrics='" & $sSongLyrics & "' WHERE ID='" & $iSongID & "';") Else _SQLite_Exec($hDatabaseFile, "INSERT INTO SongDB (ID,Name,Artist,Album,Year,Album_Cover,Lyrics) VALUES ('" & $iSongID & "','" & $sSongName & "','" & $sSongArtist & "','" & $sSongAlbum & "','" & $sSongYear & "','" & $sSongAlbumCover & "','" & $sSongLyrics & "');") EndIf EndFunc ;==>_SongEntrySave Func _SongEntryDelete() Local $iSongID, $asDatabaseLine $iSongID = $iEntryID GUICtrlSetData($hSongName, "") GUICtrlSetData($hSongArtist, $sAllArtists, "") GUICtrlSetData($hSongAlbum, $sAllAlbums, "") GUICtrlSetData($hSongYear, @YEAR) GUICtrlSetImage($hSongCover, "") GUICtrlSetData($hSongLyrics, "") _SQLite_QuerySingleRow($hDatabaseFile, "SELECT ID FROM SongDB WHERE ID='" & $iSongID & "';", $asDatabaseLine) If $asDatabaseLine[0] <> "" Then _SQLite_Exec($hDatabaseFile, "DELETE FROM SongDB WHERE ID='" & $iSongID & "';") EndIf EndFunc ;==>_SongEntryDelete Func _GetAllArtists() Local $iDBSuccess, $asSongDB, $iRows, $iColumns $iDBSuccess = _SQLite_GetTable2d($hDatabaseFile, "SELECT * FROM SongDB ORDER BY Artist;", $asSongDB, $iRows, $iColumns) If $iDBSuccess = $SQLITE_OK Then For $id = 1 To UBound($asSongDB) - 1 If $asSongDB[$id][2] <> "" And $asSongDB[$id][2] <> $asSongDB[$id - 1][2] Then $sAllArtists &= $asSongDB[$id][2] & "|" EndIf Next EndIf EndFunc ;==>_GetAllArtists Func _GetAllAlbums() Local $iDBSuccess, $asSongDB, $iRows, $iColumns $iDBSuccess = _SQLite_GetTable2d($hDatabaseFile, "SELECT * FROM SongDB ORDER BY Artist;", $asSongDB, $iRows, $iColumns) If $iDBSuccess = $SQLITE_OK Then For $id = 1 To UBound($asSongDB) - 1 If $asSongDB[$id][3] <> "" And $asSongDB[$id][3] <> $asSongDB[$id - 1][3] Then $sAllAlbums &= $asSongDB[$id][3] & "|" EndIf Next EndIf EndFunc ;==>_GetAllAlbums Func _hBrowseDatabaseEvents() Switch @GUI_CtrlId Case $hRefreshDB _SongBrowseDatabase() Case $hGotoDBEntry Local $iGotoDBEntry $iGotoDBEntry = Round(Number(GUICtrlRead($hGotoDBEntryNumber))) If $iGotoDBEntry < 1 Then $iGotoDBEntry = 1 If $iGotoDBEntry > 65000 Then $iGotoDBEntry = 65000 GUICtrlSetData($hGotoDBEntryNumber, $iGotoDBEntry) _GUICtrlListView_ClickItem($hBrowseDatabase, $iGotoDBEntry - 1) EndSwitch EndFunc ;==>_hBrowseDatabaseEvents Func _SongBrowseDatabase() Local $iDBSuccess, $asSongDB, $iRows, $iColumns, $sDBItemText, $ib, $ic _GUICtrlListView_DeleteAllItems($hBrowseDatabase) $iDBSuccess = _SQLite_GetTable2d($hDatabaseFile, "SELECT * FROM SongDB ORDER BY ID;", $asSongDB, $iRows, $iColumns) If $iDBSuccess = $SQLITE_OK Then For $ib = 1 To UBound($asSongDB) - 1 $sDBItemText = $ib & "|" For $ic = 0 To UBound($asSongDB, 2) - 1 If $ic <> UBound($asSongDB, 2) - 2 Then $sDBItemText &= $asSongDB[$ib][$ic] & "|" EndIf Next GUICtrlCreateListViewItem($sDBItemText, $hBrowseDatabase) Next EndIf EndFunc ;==>_SongBrowseDatabase Func _Exit() _SQLite_Close() _SQLite_Shutdown() Exit EndFunc ;==>_Exit Edited December 27, 2009 by jercfd Link to comment Share on other sites More sharing options...
TheSaint Posted December 26, 2009 Share Posted December 26, 2009 Thanks for sharing! Make sure brain is in gear before opening mouth! Remember, what is not said, can be just as important as what is said. Spoiler What is the Secret Key? Life is like a Donut If I put effort into communication, I expect you to read properly & fully, or just not comment. Ignoring those who try to divert conversation with irrelevancies. If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it. I'm only big and bad, to those who have an over-active imagination. I may have the Artistic Liesense to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage) Link to comment Share on other sites More sharing options...
slayerz Posted December 27, 2009 Share Posted December 27, 2009 Thanks, i can learn more bout sqlite thru ur example script AUTOIT[sup] I'm lovin' it![/sup] Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now