public void CreateDataBase()
{
if (!File.Exists(dbFileName))
{
SQLiteConnection.CreateFile(dbFileName);
}
try
{
command.CommandText = $"CREATE TABLE IF NOT EXISTS {tableName} (date TEXT, value TEXT, charcode TEXT, CONSTRAINT Cur UNIQUE (date,value,charcode))";
SQLiteFunction.RegisterFunction(typeof(string));
command.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
Console.WriteLine($"Table {tableName} didn't create");
Console.WriteLine("Error: " + ex.Message);
}
}
static void Main()
{
DbConnection cnn;
SQLiteFunction.RegisterFunction(typeof(TestFunc));
SQLiteFunction.RegisterFunction(typeof(MyCount));
SQLiteFunction.RegisterFunction(typeof(MySequence));
try
{
System.IO.File.Delete("test.db3");
}
catch
{
}
//SQLiteConnection sqlite_con = new SQLiteConnection(@"data source=""|DataDirectory|donnees.db""");
//SQLiteDataAdapter sqlite_da = new SQLiteDataAdapter();
//DataSet dataSet = new DataSet();
//sqlite_da.SelectCommand = new SQLiteCommand("select * from donnees", sqlite_con);
//sqlite_con.Open();
//sqlite_da.Fill(dataSet);
//sqlite_con.Close();
using (cnn = new SQLiteConnection())
{
TestCases tests = new TestCases();
cnn.ConnectionString = "Data Source=test.db3;Password=yVXL39etehPX";
cnn.Open();
tests.Run(cnn);
System.Windows.Forms.Application.Run(tests.frm);
}
}
/// <summary>
/// If the default data source is a file, create its directory. This function will create
/// expected database structure in the default datasource and register all custom functions.
/// </summary>
private void Initialize(string dataSource)
{
// make sure its directory exists
_fileSystem.CreateDirectory(Path.GetDirectoryName(dataSource));
using (var connection = Create(dataSource))
{
var version = GetVersion(connection);
if (version < CurrentVersion)
{
connection.Close();
connection.Dispose();
SQLiteConnection.ClearAllPools();
_fileSystem.DeleteFile(dataSource);
}
}
using (var connection = Create(dataSource))
{
SQLiteFunction.RegisterFunction(typeof(InvariantCulture));
SQLiteFunction.RegisterFunction(typeof(InvariantCultureIgnoreCase));
SQLiteFunction.RegisterFunction(typeof(GetParentPathFunction));
var initialization = Resources.Structure.Split(new [] { "----" },
StringSplitOptions.None);
using (var command = connection.CreateCommand())
{
foreach (var part in initialization)
{
command.CommandText = part;
command.ExecuteNonQuery();
}
// set current schema version
command.CommandText = "PRAGMA user_version = " + CurrentVersion;
command.ExecuteNonQuery();
}
}
}
public UnitInfoDatabase(IPreferenceSet prefs, IProteinService proteinService, ILogger logger)
{
if (proteinService == null)
{
throw new ArgumentNullException("proteinService");
}
_proteinService = proteinService;
if (logger != null)
{
_logger = logger;
}
SQLiteFunction.RegisterFunction(typeof(ToSlotType));
SQLiteFunction.RegisterFunction(typeof(GetProduction));
ForceDateTimesToUtc = true;
if (prefs != null && !String.IsNullOrEmpty(prefs.ApplicationDataFolderPath))
{
DatabaseFilePath = System.IO.Path.Combine(prefs.ApplicationDataFolderPath, Constants.SqLiteFilename);
}
}
internal static object PDO_sqliteCreateFunction(object instance, PhpStack stack)
{
string func_name = PHP.Core.Convert.ObjectToString(stack.PeekValue(1));
PhpCallback callback = PHP.Core.Convert.ObjectToCallback(stack.PeekValue(2));
object nbr = stack.PeekValueOptional(3);
stack.RemoveFrame();
int nbr_arg;
if (nbr == null)
{
nbr_arg = -1;
}
else
{
nbr_arg = PHP.Core.Convert.ObjectToInteger(nbr);
}
Delegate d = new Func <object[], object>(callback.Invoke);
SQLiteFunction.RegisterFunction(func_name, FunctionType.Scalar, nbr_arg, d);
return(null);
}
public static void AddToBindList(SQLiteFunction function)
{
FunctionList.Add(function);
}
public static void LoadExtensions()
{
SQLiteFunction.RegisterFunction(typeof(SqrtFunction));
}
static int Main(string[] args)
{
// Usage
if (args.Length < 1)
{
Console.WriteLine("usage: {0} <db file>", AppDomain.CurrentDomain.FriendlyName);
return(1);
}
// Open DB
if (!File.Exists(args[0]))
{
Console.WriteLine("Database does not exist!");
return(1);
}
SQLiteFunction.RegisterFunction(typeof(UnicodeCollation));
SQLiteConnection con = new SQLiteConnection("Data Source=" + args[0]);
con.Open();
DbDataRetriever dbRetriever = new DbDataRetriever(con);
dbRetriever.SelectDisk();
dbRetriever.LookupExtensions();
// Select folder
Console.Write(@"Enter folder name you want to search (`\` only): {0}:\", dbRetriever.DiskLetter);
DirectoryInfo folder = new DirectoryInfo(string.Format(@"{0}:\{1}", dbRetriever.DiskLetter, Console.ReadLine()));
dbRetriever.RetrieveData(folder);
Console.WriteLine("Files to find: {0}", dbRetriever.Files.Count);
// Create log file
/*StreamWriter logger = new StreamWriter("aimp-path-replacer.log", false);
* logger.AutoFlush = false;*/
// Search files and save them
DiskDataRetriever diskRetriever = new DiskDataRetriever(folder);
diskRetriever.Extensions = dbRetriever.UsedExtensions;
diskRetriever.RetrieveData();
// Save new paths
MusicDataLinker linker = new MusicDataLinker();
linker.LogicalFiles = dbRetriever.Files;
linker.PhysicalFiles = diskRetriever.Files;
linker.SetFileNotFoundFunction((AimpFile file, string output) =>
{
string errMsg = string.Format("Could not find \"{0}\" from folder \"{1}\". Insert name manually (without path) or leave empty to skip: ", file.Name, Path.GetDirectoryName(output));
Console.WriteLine(errMsg);
//logger.Write(errMsg);
string manualName = Console.ReadLine();
/*if (manualName != "")
* {
* logger.WriteLine(manualName);
* return manualName;
* }*/
//logger.WriteLine("<Skipped>");
return(manualName);
});
linker.SetDbCollisionFunction((AimpFile file, IList <string> outputs) =>
{
Console.WriteLine("There are multiple entries in database for \"{0}\".\nSelect one to work with for now or leave empty to skip", file.Name);
Console.WriteLine();
for (int i = 0; i < outputs.Count; ++i)
{
Console.WriteLine(" [{0}] {1}", i + 1, outputs[i]);
}
Console.WriteLine();
string line = Console.ReadLine();
if (line == "")
{
return(-1);
}
int entry;
if (int.TryParse(line, out entry) == false)
{
return(-1);
}
return(entry - 1);
});
linker.SetPhyCollisionFunction((AimpFile file, string fullName, IList <string> outputs) =>
{
Console.WriteLine("Select a file that is connected to \"{0}\".", fullName);
Console.WriteLine();
for (int i = 0; i < outputs.Count; ++i)
{
Console.WriteLine(" [{0}] {1}", i + 1, outputs[i]);
}
Console.WriteLine();
int entry;
if (int.TryParse(Console.ReadLine(), out entry) == false)
{
return(-1);
}
return(entry - 1);
});
linker.Run();
// Update new paths
using (SQLiteTransaction transaction = con.BeginTransaction())
{
foreach (var path in linker.Result)
{
SQLiteCommand comm = new SQLiteCommand(@"UPDATE MediaBase SET sName = ? WHERE ID = ?", con, transaction);
var sName = new SQLiteParameter(DbType.String)
{
Value = path.Value
};
var ID = new SQLiteParameter(DbType.Int64)
{
Value = path.Key
};
comm.Parameters.Add(sName);
comm.Parameters.Add(ID);
comm.ExecuteNonQuery();
comm.Dispose();
}
transaction.Commit();
}
con.Close();
con.Dispose();
Console.WriteLine("Done.");
Console.Read();
return(0);
}
static SQLiteFilterDataProvider()
{
SQLiteFunction.RegisterFunction(typeof(RegexMatchFunction));
}
static int Main(string[] args)
{
bool autoClose = false;
bool isolatedSql = false;
int exitCode = 2; /* INCOMPLETE */
Assembly assembly = Assembly.GetExecutingAssembly();
AssemblyName assemblyName = assembly.GetName();
string directory = Path.GetDirectoryName(assemblyName.CodeBase);
if (args.Length > 0)
{
try { autoClose = bool.Parse(args[0]); }
catch { }
}
if (args.Length > 1)
{
try { isolatedSql = bool.Parse(args[1]); }
catch { }
}
try { File.Delete(directory + "\\test.db"); }
catch { }
SQLiteFunction.RegisterFunction(typeof(TestFunc));
SQLiteFunction.RegisterFunction(typeof(MyCount));
SQLiteFunction.RegisterFunction(typeof(MySequence));
using (DbConnection cnn = NewConnection())
{
string connectionString = GetConnectionString(directory);
//
// NOTE: If we are unable to obtain a valid connection string
// bail out now.
//
if (connectionString != null)
{
//
// NOTE: Replace the "{DataDirectory}" token, if any, in
// the connection string with the actual directory
// this test assembly is executing from.
//
connectionString = connectionString.Replace(
"{DataDirectory}", directory);
cnn.ConnectionString = connectionString;
cnn.Open();
string sql = GetInitializationSQL(directory);
ExecuteInitializationSQL(cnn, sql, isolatedSql);
TestCases tests = new TestCases(
connectionString, cnn, sql, autoClose, isolatedSql);
tests.Run();
Application.Run(tests.frm);
if (tests.Succeeded())
{
exitCode = 0; /* SUCCESS */
}
else
{
exitCode = 1; /* FAILURE */
}
}
}
return(exitCode);
}
public SQLiteDatabase()
{
VersionUpgrade upgrade = new VersionUpgrade();
if (!upgrade.Upgrade())
{
ServiceRegistration.Get <ILogger>().Warn("SQLiteDatabase: Could not upgrade existing database");
}
try
{
_maintenanceScheduler = new ActionBlock <bool>(async _ => await PerformDatabaseMaintenanceAsync(), new ExecutionDataflowBlockOptions {
BoundedCapacity = 2
});
_messageQueue = new AsynchronousMessageQueue(this, new[] { ContentDirectoryMessaging.CHANNEL });
_messageQueue.MessageReceived += OnMessageReceived;
_messageQueue.Start();
_settings = ServiceRegistration.Get <ISettingsManager>().Load <SQLiteSettings>();
_settings.LogSettings();
LogVersionInformation();
if (_settings.EnableTraceLogging)
{
_sqliteDebugLogger = FileLogger.CreateFileLogger(ServiceRegistration.Get <IPathManager>().GetPath(@"<LOG>\SQLiteDebug.log"), LogLevel.Debug, false, true);
SQLiteLog.Initialize();
SQLiteLog.RemoveDefaultHandler();
SQLiteLog.Log += MPSQLiteLogEventHandler;
}
// We use our own collation sequence which is registered here to be able
// to sort items taking into account culture specifics
SQLiteFunction.RegisterFunction(typeof(SQLiteCultureSensitiveCollation));
var pathManager = ServiceRegistration.Get <IPathManager>();
string dataDirectory = pathManager.GetPath("<DATABASE>");
string databaseFile = Path.Combine(dataDirectory, _settings.DatabaseFileName);
// We use an URI instead of a simple database path and filename. The reason is that
// only this way we can switch on the shared cache mode of SQLite in System.Data.SQLite
// However, when using an URI, SQLite ignores the page size value specified in the connection string.
// Therefore we have to use a workaround below to create a database file with the specified page size.
string databaseFileForUri = databaseFile.Replace('\\', '/');
string databaseUri = System.Web.HttpUtility.UrlPathEncode("file:///" + databaseFileForUri + "?cache=shared");
_connectionPool = new ConnectionPool <SQLiteConnection>(CreateOpenAndInitializeConnection);
// We are using the ConnectionStringBuilder to generate the connection string
// This ensures code compatibility in case of changes to the SQLite connection string parameters
// More information on the parameters can be found here: http://www.sqlite.org/pragma.html
var connBuilder = new SQLiteConnectionStringBuilder
{
// Name of the database file including path as URI
FullUri = databaseUri,
// Use SQLite database version 3.x
Version = 3,
// Store GUIDs as binaries, not as string
// Saves some space in the database and is said to make search queries on GUIDs faster
BinaryGUID = true,
DefaultTimeout = _settings.LockTimeout,
CacheSize = _settings.CacheSizeInPages,
// Use the Write Ahead Log mode
// In this journal mode write locks do not block reads
// Needed to prevent sluggish behaviour of MP2 client when trying to read from the database (through MP2 server)
// while MP2 server writes to the database (such as when importing shares)
// More information can be found here: http://www.sqlite.org/wal.html
JournalMode = SQLiteJournalModeEnum.Wal,
// Do not use the inbuilt connection pooling of System.Data.SQLite
// We use our own connection pool which is faster.
Pooling = false,
// Sychronization Mode "Normal" enables parallel database access while at the same time preventing database
// corruption and is therefore a good compromise between "Off" (more performance) and "On"
// More information can be found here: http://www.sqlite.org/pragma.html#pragma_synchronous
SyncMode = SynchronizationModes.Normal,
// MP2's database backend uses foreign key constraints to ensure referential integrity.
// SQLite supports this, but it has to be enabled for each database connection by a PRAGMA command
// For details see http://www.sqlite.org/foreignkeys.html
ForeignKeys = true
};
if (_settings.EnableTraceLogging)
{
connBuilder.Flags = SQLiteConnectionFlags.LogAll;
}
_connectionString = connBuilder.ToString();
ServiceRegistration.Get <ILogger>().Info("SQLiteDatabase: Connection String used: '{0}'", _connectionString);
if (!File.Exists(databaseFile))
{
ServiceRegistration.Get <ILogger>().Info("SQLiteDatabase: Database file does not exists. Creating database file");
if (!Directory.Exists(dataDirectory))
{
Directory.CreateDirectory(dataDirectory);
}
// Since we use an URI in the standard connection string and system.data.sqlite therefore
// ignores the page size value in the connection string, this is a workaroung to make sure
// the page size value as specified in the settings is used. When the database file does
// not yet exists, we create a special connection string where we additionally set the
// datasource (which overrides the URI) and the page size. We then create a connection with
// that special connection string, open it and close it. That way the database file is created
// with the desired page size. The page size is permanently stored in the database file so that
// it is used when as of now we use connections with URI.
connBuilder.DataSource = databaseFile;
connBuilder.PageSize = _settings.PageSize;
using (var connection = new SQLiteConnection(connBuilder.ToString()))
{
connection.Open();
connection.Close();
}
}
// The following is necessary to avoid the creation of of a shared memory index file
// ("-shm"-file) when using exclusive locking mode. When WAL-mode is used, it is possible
// to switch between normal and exclusive locking mode at any time. However, the creation
// of a "-shm"-file can only be avoided, when exclusive locking mode is set BEFORE entering
// WAL-mode. If this is the case, it is not possible to leave exclusive locking mode
// without leaving WAL-mode before, because the "-shm"-file was not created.
// The regular connections in our connection pool use WAL-mode. Therefore we have
// to open one connection without WAL-Mode (here with JournalMode=OFF) and set locking_mode=
// EXCLUSIVE before we create the first regular connection that goes into the pool.
// To use exclusive locking mode, it is additionally necessary to set locking_mode=EXCLUSIVE
// for every connection in the pool via the InitializationCommand. If "PRAGMA locking_mode=
// EXCLUSIVE" is not in the InitializationCommand, normal locking mode is used
// although we issue "PRAGMA locking_mode=EXCLUSIVE" at this point.
// For details see here: http://sqlite.org/wal.html#noshm
// Avoiding the creation of an "-shm"-file materially improves the database performance.
if (_settings.UseExclusiveMode)
{
connBuilder.JournalMode = SQLiteJournalModeEnum.Off;
using (var connection = new SQLiteConnection(connBuilder.ToString()))
{
connection.Open();
using (var command = new SQLiteCommand(SQLiteSettings.EXCLUSIVE_MODE_COMMAND, connection))
command.ExecuteNonQuery();
connection.Close();
}
}
// Just test one "regular" connection, which is the first connection in the pool
using (var transaction = BeginTransaction())
transaction.Rollback();
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Critical("SQLiteDatabase: Error establishing database connection", e);
throw;
}
}
public static void Main(string[] args)
{
try
{
if (args.Length > 3)
{
throw new Exception("Too many arguments. Usage:\nWindowsPhotoAlbumExporter.exe [TargetPath] [AlbumName] [DatabasePath]");
}
string exportDirectory = null;
string albumName = null;
string databasePath = null;
if (args.Length > 0)
{
exportDirectory = args[0];
}
if (args.Length > 1)
{
albumName = args[1];
}
if (args.Length > 2)
{
databasePath = args[2];
}
if (databasePath == null)
{
try
{
string packagesPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Packages");
string photosPath = Directory.EnumerateDirectories(packagesPath, "Microsoft.Windows.Photos_*").First();
databasePath = Path.Combine(photosPath, "LocalState\\MediaDb.v1.sqlite");
}
catch (Exception exception)
{
throw new Exception("Cannot find photo database.", exception);
}
}
List <string> imagePaths = new List <string>(512);
SQLiteFunction.RegisterFunction(typeof(NoCaseLinguisticFunction));
string connectionString = String.Format("Data Source={0}; Read Only=True;", databasePath);
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
if (albumName == null)
{
List <string> albumNames = QueryAlbumNames(connection);
Console.WriteLine("Found albums:");
for (int i = 0; i < albumNames.Count; ++i)
{
Console.WriteLine("({0}) {1}", i + 1, albumNames[i]);
}
Console.WriteLine();
Console.WriteLine("Please enter the number or name of the album you want to export:");
string input = Console.ReadLine();
int index;
if (Int32.TryParse(input, out index))
{
--index;
if (index < 0 || index >= albumNames.Count)
{
throw new Exception("Invalid album number.");
}
albumName = albumNames[index];
}
else
{
albumName = input;
}
}
imagePaths = QueryImagePaths(connection, albumName);
connection.Close();
}
if (imagePaths.Count == 0)
{
throw new Exception(String.Format("No photos found in album \"{0}\", or album does not exist.", albumName));
}
Console.WriteLine("{0} photos found in album \"{1}\".", imagePaths.Count, albumName);
if (exportDirectory == null)
{
Console.WriteLine("Please enter the directory the photos should be exported to:");
exportDirectory = Console.ReadLine();
}
if (!Directory.Exists(exportDirectory))
{
throw new Exception("Export directory does not exist.");
}
Console.WriteLine("Start copying to \"{0}\".", exportDirectory);
try
{
int totalItems = imagePaths.Count;
for (int i = 0; i < totalItems; ++i)
{
string sourcePath = imagePaths[i];
string destinationPath = Path.Combine(exportDirectory, Path.GetFileName(sourcePath));
File.Copy(sourcePath, destinationPath);
Console.WriteLine("{0} / {1} items copied ({2}%)", i + 1, totalItems, 100 * (i + 1) / totalItems);
}
Console.WriteLine("{0} photos were copied successfully.", totalItems);
}
catch (Exception exception)
{
throw new Exception("Copying failed.", exception);
}
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
Console.WriteLine();
Console.WriteLine("Press any key to exit.");
Console.ReadKey(true);
}
/// <summary>
/// Initializes the collation UTF8CI
/// </summary>
public static void initFunction()
{
SQLiteFunction.RegisterFunction(typeof(FunctionCharindexToLike2));
}
public static void initUpper()
{
SQLiteFunction.RegisterFunction(typeof(FunctionUpper));
}
ConnectionRegistry()
{
SQLiteFunction.RegisterFunction(typeof(SQLiteCaseInsensitiveCollation));
SQLiteFunction.RegisterFunction(typeof(SQLiteLikeCI));
}
static int Main(string[] args)
{
bool autoClose = false;
int exitCode = 2; /* INCOMPLETE */
Assembly assembly = Assembly.GetExecutingAssembly();
AssemblyName assemblyName = assembly.GetName();
string directory = Path.GetDirectoryName(assemblyName.CodeBase);
if (args.Length > 0)
{
autoClose = bool.Parse(args[0]);
}
try { File.Delete(directory + "\\test.db"); } catch { }
SQLiteFunction.RegisterFunction(typeof(TestFunc));
SQLiteFunction.RegisterFunction(typeof(MyCount));
SQLiteFunction.RegisterFunction(typeof(MySequence));
using (DbConnection cnn = NewConnection())
{
string connectionString = DefaultConnectionString;
try
{
//
// NOTE: Attempt to open the configuration file associated with
// this test executable. It should contain *EXACTLY* one
// line, which will be the connection string to use for
// this test run.
//
using (StreamReader streamReader = File.OpenText(
directory + "\\test.cfg"))
{
connectionString = streamReader.ReadToEnd().Trim();
}
}
catch
{
// do nothing.
}
//
// NOTE: If we are unable to obtain a valid connection string
// bail out now.
//
if (connectionString != null)
{
//
// NOTE: Replace the "{DataDirectory}" token, if any, in the
// connection string with the actual directory this test
// assembly is executing from.
//
connectionString = connectionString.Replace(
"{DataDirectory}", directory);
cnn.ConnectionString = connectionString;
cnn.Open();
TestCases tests = new TestCases(connectionString, cnn, autoClose);
tests.Run();
Application.Run(tests.frm);
if (tests.Succeeded())
{
exitCode = 0; /* SUCCESS */
}
else
{
exitCode = 1; /* FAILURE */
}
}
}
return(exitCode);
}
public static void BindFunction(this SQLiteConnection connection, SQLiteFunction function)
{
var attr = function.GetType().GetCustomAttributes(typeof(SQLiteFunctionAttribute), false).FirstOrDefault() as SQLiteFunctionAttribute;
connection.BindFunction(attr, function);
}
public static void InitSQLiteFunctions()
{
SQLiteFunction.RegisterFunction(typeof(IUnicodeCollation));
SQLiteFunction.RegisterFunction(typeof(NumericStringCollation));
SQLiteFunction.RegisterFunction(typeof(UserLocaleCollation));
}
