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)); }