unit libman;
//{$DEFINE DEBUG}
{
2011-09-14:#0が含まれる文字列を与えるとエラーが出るので対処。
}


interface
uses
	myTag;

procedure SlimDB;
function  GetTag(var ATag : TMyTag; sFileName : WideString) : Boolean;
procedure PutTag(ATag : TMyTag);


implementation
uses
{$IFDEF DEBUG}
	myDebug,
{$ENDIF}
	Windows,
	Forms,
	SysUtils,
	SQLiteTable3,
	myFile,
	myMessageBox,
	myNum,
	myString,
	myWStrings,
	main;


const
	lcsDB_FILENAME = '.library.sqlite3';
	lcsDB_SQLDLL   = 'sqlite3.dll';

const
	lcsDBCOL_FILE_NAME       = 'FILE_NAME';
	lcsDBCOL_FILE_SIZE       = 'FILE_SIZE';
	lcsDBCOL_FILE_WRITETIME  = 'FILE_WRITETIME';
	lcsDBCOL_FILE_CREATETIME = 'FILE_CREATETIME';
	lcsDBCOL_MEDIA_LENGTH    = 'MEDIA_LENGTH';
	lcsDBCOL_MEDIA_DURATION  = 'MEDIA_DURATION';
	lcsDBCOL_VIDEO_WIDTH     = 'VIDEO_WIDTH';
	lcsDBCOL_VIDEO_HEIGHT    = 'VIDEO_HEIGHT';
	lcsDBCOL_VIDEO_FRAMERATE = 'VIDEO_FRAMERATE';

	lcsDBCOL_TAG_ARTIST      = 'TAG_ARTIST';
	lcsDBCOL_TAG_ALBUM       = 'TAG_ALBUM';
	lcsDBCOL_TAG_ALBUMARTIST = 'TAG_ALBUMARTIST';
	lcsDBCOL_TAG_COMPOSER    = 'TAG_COMPOSER';
	lcsDBCOL_TAG_CONDUCTOR   = 'TAG_CONDUCTOR';
	lcsDBCOL_TAG_GENRE       = 'TAG_GENRE';
	lcsDBCOL_TAG_TITLE       = 'TAG_TITLE';
	lcsDBCOL_TAG_TRACK       = 'TAG_TRACK';
	lcsDBCOL_TAG_WRITER      = 'TAG_WRITER';

var
	//データベース
	F_SQLiteDB : TSQLiteDatabase;


function lfnsDBTextConvert(sText : WideString) : WideString;
begin
	//2011-09-14:#0が含まれる文字列を与えるとエラーが出るので対処。
	Result := WideString('''') + gfnsStrReplace(WideString(PWideChar(sText)), '''', '''''') + WideString('''');
end;
function lfnsFindConvert(sText : WideString) : WideString;
begin
	//2011-09-14:#0が含まれる文字列を与えるとエラーが出るので対処。
	Result := lfnsDBTextConvert(WideUpperCase(gfnsStrToHalf(gfnsStrToHiragana(WideString(PWideChar(sText))))));
end;

function lfnsDBTextConvertA(sText : AnsiString) : AnsiString;
begin
	//2011-09-14:#0が含まれる文字列を与えるとエラーが出るので対処。
	Result := '''' + StringReplace(AnsiString(PAnsiChar(sText)), '''', '''''', [rfReplaceAll]) + '''';
end;


procedure F_Create;
var
	ls_SQL     : WideString;
	li_ErrMode : UINT;
begin
	if not(gfnbFileExists(gfnsExePathGet + lcsDB_SQLDLL)) then begin
Exit;
	end;

	li_ErrMode := SetErrorMode(SEM_FAILCRITICALERRORS);
	try
		F_SQLiteDB := TSQLiteDatabase.Create(gfnsWideToUTF8(gfnsFileExtChange(gfnsExeNameGet, '') + lcsDB_FILENAME));
//		F_SQLiteDB := TSQLiteDatabase.Create('h:\test.sq3');
		//データベース作成
		if not(F_SQLiteDB.TableExists('TAG_TABLE')) then begin
			ls_SQL :=
					'CREATE TABLE TAG_TABLE ('
				+       lcsDBCOL_FILE_NAME        + ' VARCHAR PRIMARY KEY'
				+ ',' + lcsDBCOL_FILE_SIZE        + ' INTEGER'
				+ ',' + lcsDBCOL_FILE_WRITETIME   + ' INTEGER'
				+ ',' + lcsDBCOL_FILE_CREATETIME  + ' INTEGER'
				+ ',' + lcsDBCOL_MEDIA_LENGTH     + ' INT'
				+ ',' + lcsDBCOL_MEDIA_DURATION   + ' DOUBLE'
				+ ',' + lcsDBCOL_VIDEO_WIDTH      + ' INT'
				+ ',' + lcsDBCOL_VIDEO_HEIGHT     + ' INT'
				+ ',' + lcsDBCOL_VIDEO_FRAMERATE  + ' DOUBLE'

				+ ',' + lcsDBCOL_TAG_ARTIST       + ' VARCHAR'
				+ ',' + lcsDBCOL_TAG_ALBUM        + ' VARCHAR'
				+ ',' + lcsDBCOL_TAG_ALBUMARTIST  + ' VARCHAR'
				+ ',' + lcsDBCOL_TAG_COMPOSER     + ' VARCHAR'
				+ ',' + lcsDBCOL_TAG_CONDUCTOR    + ' VARCHAR'
				+ ',' + lcsDBCOL_TAG_GENRE        + ' VARCHAR'
				+ ',' + lcsDBCOL_TAG_TITLE        + ' VARCHAR'
				+ ',' + lcsDBCOL_TAG_TRACK        + ' VARCHAR'
				+ ',' + lcsDBCOL_TAG_WRITER       + ' VARCHAR'
				+ ');';
			F_SQLiteDB.ExecSQL(gfnsWideToUtf8(ls_SQL));
		end;
	except
		F_SQLiteDB.Free;
//		gpcShowMessage('ライブラリデータベースが作成できません');
	end;
	SetErrorMode(li_ErrMode);
end;

procedure F_Destroy;
begin
	if (F_SQLiteDB <> nil) then begin
		F_SQLiteDB.Free;
	end;
end;

procedure SlimDB;
//存在しないファイルのレコードとデータベースの無駄な領域を削除する。
var
	l_SQLTable : TSQLiteTable;
	ls_SQL     : AnsiString;
	ls_File    : WideString;
begin
	if (F_SQLiteDB = nil)
	or not(gfnbFileExists(gfnsExePathGet + lcsDB_SQLDLL)) then begin
Exit;
	end;

	ls_SQL := 'SELECT ' + lcsDBCOL_FILE_NAME + ', ROWID FROM TAG_TABLE';

	l_SQLTable := F_SQLiteDB.GetTable(ls_SQL);
	try
		l_SQLTable.MoveFirst;
		F_SQLiteDB.BeginTransaction;
		try
			while not(l_SQLTable.EOF) do begin
				ls_File := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_FILE_NAME]);
				if not(gfnbFileExists(ls_File)) then begin
					//ファイルが存在しないのでレコード削除
					ls_SQL := 'DELETE FROM TAG_TABLE WHERE ROWID = ' + l_SQLTable.FieldByName['ROWID'] + ';';
					F_SQLiteDB.ExecSQL(ls_SQL);
				end;
				l_SQLTable.Next;
			end;
		finally
			F_SQLiteDB.Commit;
		end;
		F_SQLiteDB.ExecSQL('vacuum;');
	finally
		l_SQLTable.Free;
	end;

	if not(Application.Terminated) then begin
		gpcShowMessage('データベースの最適化が終了しました');
	end;
end;

function GetTag(var ATag : TMyTag; sFileName : WideString) : Boolean;
var
	l_SQLTable : TSQLiteTable;
	ls_SQL     : WideString;
	l_TagValue : TMyTagValue;
	l_Tag      : TMyTag;
begin
	Result := False;

	if (F_SQLiteDB = nil)
	or not(gfnbFileExists(gfnsExePathGet + lcsDB_SQLDLL)) then begin
Exit;
	end;

	ls_SQL := 'SELECT * FROM TAG_TABLE WHERE ' + lcsDBCOL_FILE_NAME + ' = ' + lfnsDBTextConvert(sFileName);

	l_SQLTable := F_SQLiteDB.GetTable(gfnsWideToUtf8(ls_SQL));
	try
		if (l_SQLTable.Count > 0) then begin
			//ライブラリにあった
			Result := True;

			FillChar(l_TagValue, SizeOf(l_TagValue), 0);

			l_TagValue.FileName          := sFileName;
			l_TagValue.FileSize          := gfniStrToInt(l_SQLTable.FieldByName[lcsDBCOL_FILE_SIZE]);
			l_TagValue.FileWriteTimeInt  := gfniStrToInt(l_SQLTable.FieldByName[lcsDBCOL_FILE_WRITETIME]);
			l_TagValue.FileCreateTimeInt := gfniStrToInt(l_SQLTable.FieldByName[lcsDBCOL_FILE_CREATETIME]);

//			l_TagValue.MediaLength    := StrToIntDef  (l_SQLTable.FieldByName[lcsDBCOL_MEDIA_LENGTH],    0);
			l_TagValue.MediaDuration  := StrToFloatDef(l_SQLTable.FieldByName[lcsDBCOL_MEDIA_DURATION],  0);
			l_TagValue.VideoWidth     := StrToIntDef  (l_SQLTable.FieldByName[lcsDBCOL_VIDEO_WIDTH],     0);
			l_TagValue.VideoHeight    := StrToIntDef  (l_SQLTable.FieldByName[lcsDBCOL_VIDEO_HEIGHT],    0);
			l_TagValue.VideoFrameRate := StrToFloatDef(l_SQLTable.FieldByName[lcsDBCOL_VIDEO_FRAMERATE], 0);

			l_TagValue.Album          := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_ALBUM]);
			l_TagValue.AlbumArtist    := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_ALBUMARTIST]);
			l_TagValue.Artist         := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_ARTIST]);
			l_TagValue.Composer       := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_COMPOSER]);
			l_TagValue.Conductor      := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_CONDUCTOR]);
			l_TagValue.Genre          := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_GENRE]);
			l_TagValue.Title          := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_TITLE]);
			l_TagValue.Track          := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_TRACK]);
			l_TagValue.Writer         := gfnsUtf8ToWide(l_SQLTable.FieldByName[lcsDBCOL_TAG_WRITER]);

			if (ATag = nil) then begin
				ATag := TMyTag.Create(l_TagValue);
			end else begin
				l_Tag := TMyTag.Create(l_TagValue);
				try
					ATag.Assign(l_Tag);
				finally
					l_Tag.Free;
				end;
			end;
		end;
	finally
		l_SQLTable.Free;
	end;
end;

procedure PutTag(ATag : TMyTag);
var
	ls_SQL : WideString;
begin
	if (F_SQLiteDB = nil)
	or not(gfnbFileExists(gfnsExePathGet + lcsDB_SQLDLL)) then
	begin
Exit;
	end;

	if (gfnbFileExists(ATag.FileName)) then
	begin
		ls_SQL := 'INSERT OR REPLACE INTO TAG_TABLE ('
			+       lcsDBCOL_FILE_NAME
			+ ',' + lcsDBCOL_FILE_SIZE
			+ ',' + lcsDBCOL_FILE_WRITETIME
			+ ',' + lcsDBCOL_FILE_CREATETIME
			+ ',' + lcsDBCOL_MEDIA_LENGTH
			+ ',' + lcsDBCOL_MEDIA_DURATION
			+ ',' + lcsDBCOL_VIDEO_WIDTH
			+ ',' + lcsDBCOL_VIDEO_HEIGHT
			+ ',' + lcsDBCOL_VIDEO_FRAMERATE

			+ ',' + lcsDBCOL_TAG_ARTIST
			+ ',' + lcsDBCOL_TAG_ALBUM
			+ ',' + lcsDBCOL_TAG_ALBUMARTIST
			+ ',' + lcsDBCOL_TAG_COMPOSER
			+ ',' + lcsDBCOL_TAG_CONDUCTOR
			+ ',' + lcsDBCOL_TAG_GENRE
			+ ',' + lcsDBCOL_TAG_TITLE
			+ ',' + lcsDBCOL_TAG_TRACK
			+ ',' + lcsDBCOL_TAG_WRITER
			+ ') VALUES ('
			+       lfnsDBTextConvert(ATag.FileName)
			+ ',' + IntToStr(ATag.FileSize)
			+ ',' + IntToStr(ATag.FileWriteTimeInt)
			+ ',' + IntToStr(ATag.FileCreateTimeInt)
			+ ',' + IntToStr(ATag.MediaLength)
			+ ',' + FloatToStr(ATag.MediaDuration)
			+ ',' + IntToStr(ATag.VideoWidth)
			+ ',' + IntToStr(ATag.VideoHeight)
			+ ',' + FloatToStr(ATag.VideoFrameRate)

			+ ',' + lfnsDBTextConvert(ATag.Artist)
			+ ',' + lfnsDBTextConvert(ATag.Album)
			+ ',' + lfnsDBTextConvert(ATag.AlbumArtist)
			+ ',' + lfnsDBTextConvert(ATag.Composer)
			+ ',' + lfnsDBTextConvert(ATag.Conductor)
			+ ',' + lfnsDBTextConvert(ATag.Genre)
			+ ',' + lfnsDBTextConvert(ATag.Title)
			+ ',' + lfnsDBTextConvert(ATag.Track)
			+ ',' + lfnsDBTextConvert(ATag.Writer)
			+ ');'
		;

		F_SQLiteDB.BeginTransaction;
		try
			F_SQLiteDB.ExecSQL(gfnsWideToUtf8(ls_SQL));
		finally
			F_SQLiteDB.Commit;
		end;
	end;
end;


//==============================================================================
initialization
	F_Create;

finalization
	F_Destroy;


end.