SQLITE3 VACUUM, “database or disk is full”

To allow the VACUUM command to run, change the directory for temporary files to one that has enough free space. SQLite’s documentation says the temporary directory is (in order): whatever is set with the PRAGMA temp_store_directory command; or whatever is set with the SQLITE_TMPDIR environment variable; or whatever is set with the TMPDIR environment variable; … Read more

How to get the last index of a substring in SQLite?

select replace(str, rtrim(str, replace(str, “https://stackoverflow.com/”, ”)), ”) from table; Step-by-step explanation. For example, we have the string: /storage/udisk/1200 Mics/[2002] 1200 Micrograms/1200 Mics – 03 – Mescaline.mp3 The replace(str, “https://stackoverflow.com/”, ”) removes / chars from str so we will have: storageudisk1200 Mics[2002] 1200 Micrograms1200 Mics – 06 – Ecstasy.mp3 Let’s call this noslashes. Next we use … Read more

DbFunctions.TruncateTime LINQ equivalent in EF CORE

In EF6 DbFunctions.TruncateTime is used instead of DateTime.Date property because for some reason the later is not supported. In EF Core the former is not needed simply because DateTime.Date now is recognized and translated correctly. group events by events.DateTimeFrom.Date into dateGroup Unfortunately there is no documentation (yet) of what is supported, so as a general … Read more

Creating stored procedure in SQLite

SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth Source : Appropriate Uses For SQLite

Integration Testing Entity Framework code first with in-memory database

Use SQL Compact 4.0 (download both SqlCE and tools by web platform installer) – EF Code first has direct support for that. The only difference will be that your application will use connection string to big SQL Server: <add name=”MyDbContext” provider=”System.Data.SqlClient” connectionString= “Data Source=…;InitialCatalog=…;Integrated Security=SSPI” /> and your tests will use connection string to SQL … Read more

SQLite database supporting Unicode data

SQLite always stores text data as Unicode, using the Unicode encoding specified when the database was created. The database driver itself takes care to return the data as the Unicode string in the encoding used by your language/platform. If you have conversion problems, either your application tried to store an ASCII string without converting it … Read more

Unable to load DLL sqlite3 in Universal Windows App running on Mobile

Add a reference to ‘SQLite for Universal App Platform’ This will trigger the error: Payload contains two or more files with the same destination path ‘sqlite3.dll’. This error occurs because of different version of sqlite3.dll in both the locations marked by the error. Resolve this error by simply copying C:\Program Files (x86)\Microsoft SDKs\UAP\v0.8.0.0\ExtensionSDKs\SQLite.UAP.2015\\Redist\Debug\ARM\sqlite3.dll to C:\Users\%USERNAME%\.nuget\packages\SQLitePCL.raw_basic\0.7.1\build\native\sqlite3_dynamic\winrt81\arm\sqlite3.dll … Read more

sql query – how to apply limit within group by

Here’s a fairly portable query to do what you want: SELECT * FROM table1 a WHERE a.”ROWID” IN ( SELECT b.”ROWID” FROM table1 b WHERE b.”Score” >= 20 AND b.”ROWID” IS NOT NULL AND a.”CID” = b.”CID” ORDER BY b.”CID”, b.”SortKey” LIMIT 2 ) ORDER BY a.”CID”, a.”SortKey”; The query uses a correlated subquery with … Read more