using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLitePCL; //SQLite
namespace Photos_Videos
{
public static class clsDB
{
public static SQLiteConnection dbConnection = new SQLiteConnection("Photo_Explorer.db");
public static void Execute(string
sSQL)
{
//------------<
btnCreateDB_Click() >------------
//SQLiteConnection
dbConnection = new SQLiteConnection("Folders.db");
ISQLiteStatement cnStatement = dbConnection.Prepare(sSQL);
cnStatement.Step();
//------------</
btnCreateDB_Click() >------------
}
public static void Create_Database()
{
//------------<
Create_Database() >------------
//--<
Tables >--
string sSQL = @"CREATE TABLE IF NOT
EXISTS Folders
(IDFolder Integer Primary Key Autoincrement NOT
NULL
, Foldername VARCHAR(255)
, Path VARCHAR(255)
, [dtRead] datetime NULL
);";
clsDB.Execute(sSQL);
sSQL = @"CREATE
TABLE IF NOT EXISTS [Files] (
[IDFile] INTEGER NOT NULL
, [IDFolder] INTEGER NOT NULL
, [Filename] nvarchar(100) NOT NULL
, [Path] nvarchar(255) NOT NULL
, [dtRead] datetime NULL
, [SizeByte] int NOT NULL
, [IsImage] bit NOT NULL
, [Extension] nchar(5) NOT NULL
, CONSTRAINT[PK_Files]
PRIMARY KEY([IDFile])
);";
clsDB.Execute(sSQL);
sSQL = @"CREATE
TABLE IF NOT EXISTS [Words] (
[IDWord] INTEGER NOT
NULL
, [Word] nvarchar(100)
NOT NULL
, CONSTRAINT [PK_Words]
PRIMARY KEY ([IDWord])
);";
clsDB.Execute(sSQL);
sSQL = @"CREATE
TABLE IF NOT EXISTS [Word_Index] (
[IDWord_Index] INTEGER
NOT NULL
, [IDWord] INTEGER NOT NULL
, [IDTable] INTEGER NOT NULL
, [IDRecord] INTEGER NOT NULL
, CONSTRAINT [PK_Word_Index] PRIMARY KEY
([IDWord_Index])
);";
clsDB.Execute(sSQL);
//--</
Tables >--
//--<
Indizes >--
sSQL = @"CREATE
INDEX IF NOT EXISTS [Folders_Path] ON [Folders] ([Path]);";
clsDB.Execute(sSQL);
sSQL = @"CREATE
INDEX IF NOT EXISTS [Foldername] ON
[Folders] ([Foldername] ASC);";
clsDB.Execute(sSQL);
sSQL = @"CREATE INDEX
IF NOT EXISTS [Files_Path] ON [Files] ([Path] );";
clsDB.Execute(sSQL);
sSQL = @"CREATE INDEX
IF NOT EXISTS [Filename] ON [Files] ([Filename] );";
clsDB.Execute(sSQL);
sSQL = @"CREATE INDEX
IF NOT EXISTS [Word] ON [Words]([Word] );";
clsDB.Execute(sSQL);
sSQL = @"CREATE INDEX
IF NOT EXISTS [IDWord_IDTable_IDRecord] ON [Word_Index]
([IDWord] ,[IDTable]
,[IDRecord] );";
clsDB.Execute(sSQL);
//--</ Indizes >--
//------------</
Create_Database() >------------
}
}
}
|