Datenbank : Teil 1 erstellen einer DB

CREATE TABLE IF NOT EXISTS Bookings(
	id INTEGER PRIMARY KEY,
	account INTEGER NOT NULL,
	time INTEGER DEFAULT (unixepoch()),
	change INTEGER NOT NULL,
	short_desc TEXT DEFAULT NULL,
	purpose TEXT DEFAULT NULL,
	FOREIGN KEY (account) REFERENCES Accounts (id) ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS Accounts(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL
);

BEGIN TRANSACTION;
INSERT INTO Bookings (account, time, change, short_desc) VALUES (1, unixepoch("2025-04-25 12:34:56"), 120000, "Einnahmen Lohn1");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, 25000, "Einnahmen Lohn2");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, 15000, "Einnahmen WGP");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, 6000, "Einnahmen LMP VM");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, 4000, "Einnahmen Extra");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, -50000, "Ausgaben Miete");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, -10000, "Ausgaben Strom");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, -1005, "Ausgaben Bank");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, -2000, "Ausgaben RuBe");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, -30000, "Ausgaben LMP");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, -10000, "Ausgaben DuS");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, -15000, "Ausgaben frei1");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, 0, "Ausgaben frei2");
INSERT INTO Bookings (account, change, short_desc) VALUES (1, 0, "Ausgaben Extra");
COMMIT;

BEGIN TRANSACTION;
INSERT INTO Bookings (account, change, short_desc) VALUES (2, 10000, "Eingang Sparbuch");
INSERT INTO Bookings (account, change, short_desc, purpose) VALUES (2, -7500, "Ausgang Sparbuch", "X, Y, Z");
COMMIT;

BEGIN TRANSACTION;
INSERT INTO Accounts (name) VALUES ("Haushaltsbuch"), ("Sparbuch");
COMMIT;

#include <SQLite.au3>

Global $sDataFolder = "Data\", $sSQLiteDLL = $sDataFolder&"sqlite3_"&(@AutoItX64?"64":"86")&".dll", $sDatabase = $sDataFolder&"database.sqlite"
Global $sSQLCreateTableBooking = "" & _
	"CREATE TABLE IF NOT EXISTS Bookings(" & _
	  "id INTEGER PRIMARY KEY," & _
	  "account INTEGER NOT NULL," & _
	  "time INTEGER DEFAULT (unixepoch())," & _
	  "change INTEGER NOT NULL," & _
	  "short_desc TEXT DEFAULT NULL," & _
	  "purpose TEXT DEFAULT NULL," & _
	  "FOREIGN KEY (account) REFERENCES Accounts (id) ON DELETE CASCADE ON UPDATE NO ACTION" & _
	");"
Global $sSQLCreateTableAccounts = "" & _
	"CREATE TABLE IF NOT EXISTS Accounts(" & _
	  "id INTEGER PRIMARY KEY," & _
	  "name TEXT NOT NULL" & _
	");"
Global $sSQLReset = "DROP TABLE IF EXISTS Bookings; DROP TABLE IF EXISTS Accounts;"
Global $sSQLAllData = "SELECT " & _
							"b.account, " & _
							"a.name, " & _
							"b.time, " & _
							"datetime(b.time, 'unixepoch') as stime, " & _
							"strftime('%Y', datetime(b.time, 'unixepoch')) as year, " & _
							"strftime('%m', datetime(b.time, 'unixepoch')) as month, " & _
							"strftime('%d', datetime(b.time, 'unixepoch')) as day, " & _
							"strftime('%w', datetime(b.time, 'unixepoch')) as weekday, " & _
							"b.change, " & _
							"b.short_desc, " & _
							"b.purpose " & _
						"FROM Bookings b JOIN Accounts a ON b.account=a.id"

_SQLite_Startup($sSQLiteDLL)
If @error Then MsgBox(16, "Error", 'SQLite could not be loaded. Check, if the file "'&$sSQLiteDLL&'" exists.')

Global $hDB = _SQLite_Open($sDatabase)

; For Debugging to remove old data
_SQLite_ExecCatchErr($hDB, $sSQLReset, False, True)

_SQLite_ExecCatchErr($hDB, $sSQLCreateTableBooking, False, True)
_SQLite_ExecCatchErr($hDB, $sSQLCreateTableAccounts, False, True)

_fillWithExampleData($hDB)

Local $arColumns, $arData

; Output Table Accounts
$arData = _SQLite_GetAllCatchErr($hDB, "SELECT * From Accounts", $arColumns)
_printData($arData, $arColumns, "Table Accounts")

; Output Table Bookings
$arData = _SQLite_GetAllCatchErr($hDB, "SELECT * From Bookings", $arColumns)
_printData($arData, $arColumns, "Table Bookings")

; Output everything with extra data
; $arData = _SQLite_GetAllCatchErr($hDB, $sSQLAllData, $arColumns)
; _printData($arData, $arColumns, "All data with extra infos for further processing")


; output change by month
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, month, cast(SUM(change) as float)/100 as change FROM ("&$sSQLAllData&") GROUP BY name, month, year ORDER BY name, year, month", $arColumns)
; _printData($arData, $arColumns, "Change by Month-Year")

; output change by year
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, cast(SUM(change) as float)/100 as change FROM ("&$sSQLAllData&") GROUP BY name, year ORDER BY name, year", $arColumns)
; _printData($arData, $arColumns, "Change by Year")

; output change by half year
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, month/6+1 as halfyear, cast(SUM(change) as float)/100 as change FROM ("&$sSQLAllData&") GROUP BY name, year, (month-1)/6 ORDER BY name, year, halfyear", $arColumns)
; _printData($arData, $arColumns, "Change by Half Year")

; output change by quarter
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, month/3+1 as quarter, cast(SUM(change) as float)/100 as change FROM ("&$sSQLAllData&") GROUP BY name, year, (month-1)/3 ORDER BY name, year, quarter", $arColumns)
; _printData($arData, $arColumns, "Change by Quarter")

; output change by third
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, month/4+1 as third, cast(SUM(change) as float)/100 as change FROM ("&$sSQLAllData&") GROUP BY name, year, (month-1)/4 ORDER BY name, year, third", $arColumns)
; _printData($arData, $arColumns, "Change by Year/3")

; output change by day
Global $sSQLChangeByDay = "SELECT name, year, month, day, cast(SUM(change) as float)/100 as change FROM ("&$sSQLAllData&") GROUP BY name, year, month, day ORDER BY name, year, month, day"
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, month, day, cast(SUM(change) as float)/100 as change FROM ("&$sSQLAllData&") GROUP BY name, year, month, day ORDER BY name, year, month, day", $arColumns)
; _printData($arData, $arColumns, "Change by Day")

; output spending avarage by day
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, AVG(change) as average FROM ("&$sSQLChangeByDay&") GROUP BY name, year ORDER BY name, year", $arColumns)
; _printData($arData, $arColumns, "Average change by day")

; output spending avarage by week
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, AVG(change)*7 as average FROM ("&$sSQLChangeByDay&") GROUP BY name, year ORDER BY name, year", $arColumns)
; _printData($arData, $arColumns, "Average change by week")

; output spending avarage by weekday
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, weekday, cast(AVG(change) as float)/100 as average FROM ("&$sSQLAllData&") GROUP BY name, year, weekday ORDER BY name, year, weekday", $arColumns)
; _printData($arData, $arColumns, "Average change by day")

; output saldo
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT a.name, cast(SUM(b.change) as float)/100 as saldo FROM Bookings b JOIN Accounts a ON b.account=a.id GROUP BY a.name", $arColumns)
; _printData($arData, $arColumns, "Saldo")

; output saldo until specific datetime
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT a.name, cast(SUM(b.change) as float)/100 as saldo FROM Bookings b JOIN Accounts a ON b.account=a.id WHERE b.time<unixepoch('2025-01-01 00:00:00') GROUP BY a.name", $arColumns)
; _printData($arData, $arColumns, "Saldo at 2025-01-01 00-00-00")

; output change by quarter for year 2025
; $arData = _SQLite_GetAllCatchErr($hDB, "SELECT name, year, month/3+1 as quarter, cast(SUM(change) as float)/100 as change FROM ("&$sSQLAllData&") WHERE year='2025' GROUP BY name, year, (month-1)/3 ORDER BY name, year, quarter", $arColumns)
; _printData($arData, $arColumns, "Change by Quarter (2025)")

Func _exit()
	_SQLite_Close($hDB)
	_SQLite_Shutdown()
	Exit
EndFunc

Func _SQLite_ExecCatchErr($hDB, $sSql, $bTransaction = True, $bFatal = False)
	_SQLite_Exec($hDB, 'BEGIN TRANSACTION;'&$sSql&'COMMIT;')
	If @error Then
		_SQLite_Exec($hDB, "ROLLBACK;")
		MsgBox(16, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg() & "Executing """&$sSql&"""")
		If $bFatal Then _exit()
	EndIf
EndFunc

Func _SQLite_GetDataCatchErr($hDB, $sSql)
	Local $arColumns
	Return _SQLite_GetAllCatchErr($hDB, $sSql, $arColumns)
EndFunc

Func _SQLite_GetAllCatchErr($hDB, $sSql, ByRef $arColumns)
	Local $arResult, $iRows
	_SQLite_GetTableData2D($hDB, $sSql, $arResult, $iRows, $arColumns)
	If @error Then
		MsgBox(16, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg() & "Executing """&$sSql&"""")
		Local $arResult[0][0]
		Return SetError(1, 0, $arResult)
	EndIf
	Return $arResult
EndFunc

Func _printData(ByRef $arData, ByRef $arColumns, $sTitle = Default)
	If Not IsArray($arData) Or Not IsArray($arColumns) Then Return -1
	If UBound($arData,0)<>2 Then Return -1
	Local $arColWidth[UBound($arData, 2)]
	For $i=0 To UBound($arColumns)-1 Step 1
		If StringLen($arColumns[$i])>$arColWidth[$i] Then $arColWidth[$i] = StringLen($arColumns[$i])
	Next
	For $j=0 To UBound($arData, 2)-1 Step 1
		For $i=0 To UBound($arData, 1)-1 Step 1
			Local $arMatch = StringRegExp($arData[$i][$j], "(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})", 1)
			If Not @error Then
				$arData[$i][$j] = $arMatch[0]&"-"&$arMatch[1]&"-"&$arMatch[2]&" "&$arMatch[3]&"-"&$arMatch[4]&"-"&$arMatch[5]
			EndIf
			If StringLen($arData[$i][$j])>$arColWidth[$j] Then $arColWidth[$j] = StringLen($arData[$i][$j])
		Next
	Next
	Local $arSeps[UBound($arColWidth)], $iMaxLength = 0
	For $i=0 To UBound($arColWidth)-1 Step 1
		$iMaxLength+=$arColWidth[$i]+3
	Next
	Local $iAdd = 0
	If $iMaxLength<StringLen($sTitle) Then $iAdd = Int((StringLen($sTitle)-$iMaxLength+4)/UBound($arColWidth))+(Mod((StringLen($sTitle)-$iMaxLength)+4, UBound($arColWidth))>0?1:0)
	For $i=0 To UBound($arColWidth)-1 Step 1
		$arColWidth[$i] += $iAdd
	Next
	For $i=0 To UBound($arColWidth)-1 Step 1
		For $j=0 To $arColWidth[$i]+1 Step 1
			$arSeps[$i] &= "-"
		Next
	Next
	If $sTitle<>Default Then
		ConsoleWrite("+")
		For $i=0 To UBound($arSeps)-1 Step 1
			ConsoleWrite($arSeps[$i])
			If $i<UBound($arSeps)-1 Then ConsoleWrite("-")
		Next
		ConsoleWrite("+"&@crlf)
		Local $iTitleSpace = Int(($iMaxLength-3-StringLen($sTitle))/2)
		If $iTitleSpace<0 Then $iTitleSpace=0
		Local $iExtra = 0
		If Mod(($iMaxLength-3-StringLen($sTitle))/2, 2)<>0 Then $iExtra = 1
		ConsoleWrite(StringFormat("| %"&$iTitleSpace&"s%s%"&$iTitleSpace+$iExtra&"s |", "", $sTitle, "")&@crlf)
	EndIf
	For $i=0 To UBound($arSeps)-1 Step 1
		ConsoleWrite("+"&$arSeps[$i])
	Next
	ConsoleWrite("+"&@crlf)
	For $i=0 To UBound($arColumns)-1 Step 1
		ConsoleWrite(StringFormat("| %-"&$arColWidth[$i]&"s ", StringUpper($arColumns[$i])))
	Next
	ConsoleWrite("| "&@crlf)
	ConsoleWrite("+")
	For $i=0 To UBound($arSeps)-1 Step 1
		ConsoleWrite($arSeps[$i])
		If $i<UBound($arSeps)-1 Then ConsoleWrite("-")
	Next
	ConsoleWrite("+"&@crlf)
	For $i=0 To UBound($arData, 1)-1 Step 1
		For $j=0 To UBound($arData, 2)-1 Step 1
			ConsoleWrite(StringFormat("| %-"&$arColWidth[$j]&"s ", $arData[$i][$j]))
		Next
		ConsoleWrite("| "&@crlf)
	Next
	For $i=0 To UBound($arSeps)-1 Step 1
		ConsoleWrite("+"&$arSeps[$i])
	Next
	ConsoleWrite("+"&@crlf)
EndFunc

Func _fillWithExampleData($hDB)
	_SQLite_ExecCatchErr($hDB, 'INSERT INTO Accounts (name) VALUES ("Haushaltsbuch"), ("Sparbuch");')
	#cs
	_SQLite_ExecCatchErr($hDB, '' & _
			'INSERT INTO Bookings (account, time, change, short_desc) VALUES (1, unixepoch("2025-04-25 12:34:56"), 120000, "Einnahmen Lohn1");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, 25000, "Einnahmen Lohn2");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, 15000, "Einnahmen WGP");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, 6000, "Einnahmen LMP VM");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, 4000, "Einnahmen Extra");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, -50000, "Ausgaben Miete");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, -10000, "Ausgaben Strom");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, -1005, "Ausgaben Bank");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, -2000, "Ausgaben RuBe");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, -30000, "Ausgaben LMP");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, -10000, "Ausgaben DuS");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, -15000, "Ausgaben frei1");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, 0, "Ausgaben frei2");' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (1, 0, "Ausgaben Extra");' & _
		'')
	_SQLite_ExecCatchErr($hDB, '' & _
			'INSERT INTO Bookings (account, change, short_desc) VALUES (2, 10000, "Eingang Sparbuch");' & _
			'INSERT INTO Bookings (account, change, short_desc, purpose) VALUES (2, -7500, "Ausgang Sparbuch", "X, Y, Z");' & _
		'')
	#ce
	; generate some example data for 2024+2025 (complete years)
	Local $arData = [ _
		[1, 120000, "Einnahmn Lohn1", ""], _
		[1, 25000, "Einnahmen Lohn2", ""], _
		[1, 15000, "Einnahmen WGP", ""], _
		[1, 6000, "Einnahmen LMP VM", ""], _
		[1, 4000, "Einnahmen Extra", ""], _
		[1, -50000, "Ausgaben Miete", ""], _
		[1, -10000, "Ausgaben Strom", ""], _
		[1, -1005, "Ausgaben Bank", ""], _
		[1, -2000, "Ausgaben RuBe", ""], _
		[1, -30000, "Ausgaben LMP", ""], _
		[1, -10000, "Ausgaben DuS", ""], _
		[1, -15000, "Ausgaben frei1", ""], _
		[1, 0, "Ausgaben frei2", ""], _
		[1, 0, "Ausgaben Extra", ""], _
		[2, 10000, "Eingang Sparbuch", ""], _
		[2, -7500, "Ausgang Sparbuch", "X, Y, Z"] _
	]
	For $iYear=2024 To 2025 Step 1
		For $iMonth=1 To 12 Step 1
			Local $sSql = ""
			For $i=0 To UBound($arData)-1 Step 1
				Local $iDay = Random(1, 28, 1)
				Local $sDate = StringFormat("%04d-%02d-%02d %02d:%02d:%02d", $iYear, $iMonth, $iDay, 12, 30, 00)
				Local $iMultiplier = Random(0.6, 1)
				If $arData[$i][3]="" Then
					$sSql &= StringFormat('INSERT INTO Bookings (account, time, change, short_desc) VALUES (%d, unixepoch("%s"), %d, "%s");', $arData[$i][0], $sDate, $arData[$i][1]*$iMultiplier, $arData[$i][2])
				Else
					$sSql &= StringFormat('INSERT INTO Bookings (account, time, change, short_desc, purpose) VALUES (%d, unixepoch("%s"), %d, "%s", "%s");', $arData[$i][0], $sDate, $arData[$i][1]*$iMultiplier, $arData[$i][2], $arData[$i][3])
				EndIf
			Next
			_SQLite_ExecCatchErr($hDB, $sSql)
		Next
	Next
EndFunc

static void unixepochFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    computeJD(&x);
    if( x.useSubsec ){
      sqlite3_result_double(context, (x.iJD - 21086676*(i64)10000000)/1000.0);
    }else{
      sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000);
    }
  }
}