[sqlite] Fwd: Re: SQLite and Thunderbird
Dear sqlite-users list, I'd like to try and get some pointers on the following issue as documented on the following included BZ issues. Please CC me as I'm not subscribed to this list. Forwarded Message Subject: Re: SQLite and Thunderbird Date: Fri, 26 Feb 2016 10:06:25 +0100 From: Jan Stan?k Organization: Red Hat To: Steven Haigh CC: nils at redhat.com, stransky at redhat.com Hi, I presume thet this is general thunderbird issue, not Fedora specific one. If so, I would suggest asking at sqlite-users at mailinglists.sqlite.org, they are usually quite helpful. Regards, Jan Dne 26.2.2016 v 07:10 Steven Haigh napsal(a): > Re: > https://bugzilla.redhat.com/show_bug.cgi?id=1310864 > https://bugzilla.redhat.com/show_bug.cgi?id=1311032 > > Hi all, > > Just trying to open a channel of communication regarding these bugs. > > While I believe thunderbird uses a format of call that is depreciated in > the newer SQLite packages, it is not ideal to statically compile > thunderbird against sqlite to make it work (which I believe is the > current fix). > > Any suggestions on a long-term fix? > -- Jan Stanek - Red Hat Associate Developer Engineer - Databases Team -- Steven Haigh Email: netwiz at crc.id.au Web: https://www.crc.id.au Phone: (03) 9001 6090 - 0412 935 897 -- next part -- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 819 bytes Desc: OpenPGP digital signature URL: <http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160226/234451af/attachment.pgp>
[sqlite] Database is locked (wal) - purely from read-only connections/queries
I am using System.Data.SQLite in .NET and encountering "database is locked"
with wal using multiple threads from the same process running simple select
statements with (separate) read-only connections. Please see the link below and
note the Visual Studio output window when it runs.
https://drive.google.com/open?id=0By9M2uwoQgnKUnN6Z2NoWDZLS2s
Here is the bulk of the code in case more helpful than the download:
class Program
{
///
/// Configure for x64 and hit F5 in Visual Studio
/// Check the output window and note "database is locked"
///
///
static void Main(string[] args)
{
// the db file was copied to bin
// note: the db was last opened with wal
var dbPath = new
FileInfo(Assembly.GetExecutingAssembly().Location).Directory.FullName;
var dbFile = Path.Combine(dbPath, "Simple.db3");
var csb = new SQLiteConnectionStringBuilder();
csb.DataSource = dbFile;
csb.ReadOnly = true;
var connStr = csb.ConnectionString;
RunTest(connStr);
}
private static void RunTest(string connStr)
{
var f1 = Task.Factory.StartNew(() => DoReadTest(connStr),
TaskCreationOptions.LongRunning);
var f2 = Task.Factory.StartNew(() => DoReadTest(connStr),
TaskCreationOptions.LongRunning);
var f3 = Task.Factory.StartNew(() => DoReadTest(connStr),
TaskCreationOptions.LongRunning);
var f4 = Task.Factory.StartNew(() => DoReadTest(connStr),
TaskCreationOptions.LongRunning);
Task.WaitAll(new List { f1, f2, f3, f4 }.ToArray());
}
private static void DoReadTest(string connStr)
{
var untilTime = DateTime.UtcNow.AddSeconds(10);
int numRuns = 0;
long totalMS = 0;
// hammer the db with reads
do
{
var sw = new Stopwatch();
sw.Start();
ReadTestWorker(connStr);
numRuns++;
totalMS += sw.ElapsedMilliseconds;
} while (DateTime.UtcNow < untilTime);
Console.WriteLine($"Thread {Thread.CurrentThread.ManagedThreadId}
avg ms: { totalMS / numRuns }, Total runs {numRuns }");
}
private static void ReadTestWorker(string connStr)
{
const string sql = "SELECT MAX(Id) AS MaxId FROM TestTable;";
using (var connection = new SQLiteConnection(connStr))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var msg = $"Thread
{Thread.CurrentThread.ManagedThreadId} says max id is : {reader[0]}";
//Console.WriteLine(msg);
}
}
}
}
}
}
Thank you,
Vince
[sqlite] Possible bug in the SQL parser
I'm using SQLite 3.8.10.2 and the following query illustrates the problem: WITH tA(id, name) AS ( SELECT 1, "a" UNION ALL SELECT 2, "b" ), tB(name) AS ( SELECT "a" UNION ALL SELECT "b" ) SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name); There is no _id_ column in the tB table, yet the statement doesn't produce any error and in fact will return the ids of table tA. This doesn't seem correct to me. -- *Jo?o Ramos*
[sqlite] ASK SQLite algoritm to chose Index
On 26 Feb 2016, at 5:45pm, Christoforus Surjoputro wrote: > I've problem with sqlite choosing index that I think I have better index to > use. I've ask here but still didn't get why this happen? Did sqlite choose > the best index to use or choose the last entered index? SQLite doesn't know which index was entered last. It chose the best index based on what it knows. Can you post your TABLE definition and the INDEXes you defined on the table, and the result of EXPLAIN QUERY PLAN on your SELECT ? Please run ANALYZE, then do EXPLAIN QUERY PLAN on your SELECT again and tell us if anything changed. Simon.
[sqlite] ASK SQLite algoritm to chose Index
I've problem with sqlite choosing index that I think I have better index to
use. I've ask?here but still didn't get why this happen? Did sqlite choose the
best index to use or choose the last entered index?
| ? |
| ? | | ? | ? | ? | ? | ? |
| SQLite use autoindex instead my own indexI've problem with SQLite autoindex
in UNIQUE table. I've create table like below. c.execute('''CREATE TABLE user(
id INTEGER PRIMARY KEY, email TEXT NOT... |
| |
| View on stackoverflow.com | Preview by Yahoo |
| |
| ? |
Thank you.
[sqlite] Why is a separate journal file needed ?
On 24 February 2016 at 23:46, Igor Tandetnik wrote: > On 2/24/2016 10:08 AM, Stephen Chrzanowski wrote: > >> IMO, all that, plus the fact that you have an easy roll back mechanism. >> Anything that needs to be put in the database is external to the pristine >> database. Lock the database with a transaction, fill up the journal, the >> power goes out, your pristine database isn't touched. >> > > Rollback journal works the other way round. New data is written to > database file; previous content of overwritten pages is copied to journal > file. In principle this is correct, but actually the database *file* is not immediately modified in rollback mode. Instead when a page is modified the original contents are saved to the rollback journal, and the page is updated *in memory*. > Committing a transaction is fast - simply delete the journal. So this is not quite true. It's only[1] at commit time that the pages modified in memory are paged back to disk, and this concentration of I/O makes commit relatively expensive. But there is a reason for this - it improves concurrency. As you point out, the database file is not "pristine" after the first modified page is written to disk, until the journal file is deleted (which marks the end of the commit phase and finalises the transaction). Clearly other processes must be locked out of the database for this phase. By deferring modification on disk as long as possible, sqlite maximises the amount of time other processes can continue to read the database. [1] or if its memory cache is exceeded, sqlite will obtain the EXCLUSIVE lock and modify the database before commit time. See pragmas cache_size and cache_spill. -Rowan
[sqlite] Why is a separate journal file needed ?
On 24 February 2016 at 21:49, Richard Hipp wrote: > On 2/24/16, Simon Slavin wrote: > > Why can't the information which SQLite > > stores in a journal file be put in the database file ? > > Doing so would double the size of the database file. Every database > file would contain extra space (normally unused) set aside for the > journal. > Surely this extra space would be proportional to the size of the largest [recent] transaction, rather than the size of the database itself? To be specific I'm thinking of rollback journals, I don't have experience with WAL mode. -Rowan
[sqlite] ROWID schema surgery
On Fri, 26 Feb 2016 at 16:42 Simon Slavin wrote: > > I thought I knew where it was documented but I can't find it at the > moment. The idea is that if you have given the column an explicit name (in > your case 'storeID') then you might be referring to it in your code, so > SQLite shouldn't change the values. > > That should work correctly in the current and all future versions of > SQLite3. It's an assumption lots of programmers make. > My question is whether modifying "sqlite_master" after the fact to make the schema like this has any possible pitfalls/caveats, as opposed to creating the table this way from the start. It's not really clear to me how to tell what schema modifications are sound and what are not (eg. if you add the column to the beginning of the schema instead of the end, the table gets corrupted because now all of the columns are offset 1 from how they're actually stored).
[sqlite] Fwd: Re: SQLite and Thunderbird
Richard Hipp wrote:
> I suppose that Thunderbird was making use of the fts3_tokenizer()
> interface, which has be removed from standard builds due to security
> concerns, as of version 3.11.0. You can reenable that feature at
> compile-time by building with -DSQLITE_ENABLE_FTS3_TOKENIZER. See the
> last bullet (the only bullet under the "Backwards Compability"
> heading) of the release notes at
> https://www.sqlite.org/releaselog/3_11_0.html for links to further
> information.
>
> At this time, you basically have two options:
>
> (1) Compile your system sqlite3.so library using
> SQLITE_ENABLE_FTS3_TOKENIZER and hope that none of the applications
> that link against this library use it in such a way that the
> fts3_tokenizer() could present a security vulnerability.
>
> (2) Statically link against a version of SQLite that you compile
> yourself. SQlite is a single file of C code ("sqlite3.c") so making
> it a part of the project source tree is not a big deal.
>
> Option (2) seems like the best choice to me since that guarantees that
> Thunderbird will continue to operate regardless of what historical
> version of sqlite3.so happens to be installed (or not installed) on
> the system and regardless of the compile-time options used to create
> that sqlite3.so. (For example, what if somebody installs a new
> sqlite3.so that omits full-text search?) Static linking removes a
> dependency and makes Thunderbird more robust.
Thunderbird has *always* used its own statically built sqlite, just like all
other Mozilla software.
In fact, it has more than one copy:
https://hg.mozilla.org/mozilla-central/file/918df3a0bc1c/db/sqlite3/src
https://hg.mozilla.org/mozilla-central/file/918df3a0bc1c/security/nss/lib/sqlite
>
> On 2/26/16, Steven Haigh wrote:
>> Dear sqlite-users list,
>>
>> I'd like to try and get some pointers on the following issue as
>> documented on the following included BZ issues.
>>
>> Please CC me as I'm not subscribed to this list.
>>
>>
>> Forwarded Message
>> Subject: Re: SQLite and Thunderbird
>> Date: Fri, 26 Feb 2016 10:06:25 +0100
>> From: Jan Stan?k
>> Organization: Red Hat
>> To: Steven Haigh
>> CC: nils at redhat.com, stransky at redhat.com
>>
>> Hi,
>> I presume thet this is general thunderbird issue, not Fedora specific
>> one. If so, I would suggest asking at
>> sqlite-users at mailinglists.sqlite.org, they are usually quite helpful.
>>
>> Regards,
>> Jan
>>
>> Dne 26.2.2016 v 07:10 Steven Haigh napsal(a):
>>> Re:
>>> https://bugzilla.redhat.com/show_bug.cgi?id=1310864
>>> https://bugzilla.redhat.com/show_bug.cgi?id=1311032
>>>
>>> Hi all,
>>>
>>> Just trying to open a channel of communication regarding these bugs.
>>>
>>> While I believe thunderbird uses a format of call that is depreciated in
>>> the newer SQLite packages, it is not ideal to statically compile
>>> thunderbird against sqlite to make it work (which I believe is the
>>> current fix).
>>>
>>> Any suggestions on a long-term fix?
>>>
>>
>>
>> --
>> Jan Stanek - Red Hat Associate Developer Engineer - Databases Team
>>
>>
>>
>> --
>> Steven Haigh
>>
>> Email: netwiz at crc.id.au
>> Web: https://www.crc.id.au
>> Phone: (03) 9001 6090 - 0412 935 897
>>
>>
>>
>>
>
>
--
-- Howard Chu
CTO, Symas Corp. http://www.symas.com
Director, Highland Sun http://highlandsun.com/hyc/
Chief Architect, OpenLDAP http://www.openldap.org/project/
[sqlite] ROWID schema surgery
On 26 Feb 2016, at 2:22pm, Tristan Seligmann wrote: > PRAGMA writable_schema=on; > UPDATE sqlite_master SET sql='CREATE TABLE some_table_name (..., storeID > INTEGER PRIMARY KEY)' WHERE tbl_name='some_table_name'; I thought I knew where it was documented but I can't find it at the moment. The idea is that if you have given the column an explicit name (in your case 'storeID') then you might be referring to it in your code, so SQLite shouldn't change the values. That should work correctly in the current and all future versions of SQLite3. It's an assumption lots of programmers make. Simon.
[sqlite] Encrypt the SQL query
Hi, To encrypt the SQLite database, I can only find the following extension: https://www.sqlite.org/see/doc/trunk/www/readme.wiki So I must recompile and enable the extension to encrypt the database, is that correct? Thanks > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin > Sent: Thursday, February 25, 2016 4:39 PM > To: SQLite mailing list > Subject: Re: [sqlite] Encrypt the SQL query > > > On 25 Feb 2016, at 6:01am, > wrote: > > > Does SQLite provide a good way to > > encrypt the SQL query strings while does not affect the performance > > when executing the queries? > > The source code for SQLite is available. There's no way to prevent a hacker > reverse-engineering whatever calls you make and adding source code of > their own to log your command before executing it. > > You can encrypt your database stored on disk, but not your interaction with > the SQLite API. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ROWID schema surgery
Axiom is an ORM'ish layer on top of SQLite in Python. Due to unfortunate historic reasons, Axiom relies on the implicit ROWID (actually "oid") column present in every table in SQLite, without declaring an explicit PRIMARY KEY; this, of course, means that VACUUMing an Axiom table will corrupt it as some or all of the ROWIDs will change. Over on the Axiom bug tracker[1], we're trying to figure out a way to transition away from this unfortunate situation to using an explicit PRIMARY KEY. Unfortunately, ALTER TABLE may not use a PRIMARY KEY constraint, so it doesn't work for this purpose. However, the following evil trick seems to work: PRAGMA writable_schema=on; UPDATE sqlite_master SET sql='CREATE TABLE some_table_name (..., storeID INTEGER PRIMARY KEY)' WHERE tbl_name='some_table_name'; The following notebook session seems to demonstrate that it works: http://nbviewer.jupyter.org/url/bucket.mithrandi.net/sqlite-vacuum-working.ipynb and for completeness, here's a similar session demonstrating how VACUUM changes the values without the schema trickery: http://nbviewer.jupyter.org/url/bucket.mithrandi.net/sqlite-vacuum-broken.ipynb However, the question is whether this is something we can and should be relying on to function correctly. Are there any likely problems with this trick? [1] https://github.com/twisted/axiom/issues/35
[sqlite] Encrypt the SQL query
On Thu, 25 Feb 2016 14:01:31 +0800 wrote: > Does SQLite provide a good way to encrypt the SQL query strings while > does not affect the performance when executing the queries? If you're worried about the user examining your program image statically, you could encrypt your SQL by whatever means, and decrypt it "just in time": sqlite3_prepare(db, decrypt(sql, key), ... ); Of course, if the key is also present in the image, you're only discouraging the uninterested. (Something I suppose Tim Cook knows a thing or two about...) If you're worried about the user examining the running program -- for example, with ltrace(1) -- then your question is moot, because at some point the encrypted SQL wiill have to be decrypted before SQLite interprets it. --jkl
[sqlite] SQLite and Thunderbird
On 26 Feb 2016, at 9:23am, Steven Haigh wrote: > Please CC me as I'm not subscribed to this list. I sent this person the 'if you don't read a list don't post' message. Simon.
[sqlite] Can SQLite be used from DLL
On 2/26/2016 12:07 PM, Igor Korot wrote: > Full code is in here: > http://stackoverflow.com/questions/35345258/crash-when-disconnecting-from-sqlite-database Off the top, the example never calls Connect, which leaves SQLiteDatabase::m_db contain uninitialized garbage (you don't initialize it in constructor either), and then sqlite3_close(garbage) call in destructor is having a bad time. You don't even expose Connect() on Database, so it's unclear how the main program is supposed to use the class. Also, passing std::vector across module boundaries is asking for trouble. There is a very narrow band of build configuration parameters in which this would work. -- Igor Tandetnik
[sqlite] Patch to fix buffer overflow in icu extension.
Summary: Certain Unicode code points expand to more than two code points
when run through u_strToUpper().
SQLite's src/ext/icu/icu.c contains icuCaseFunc16() which implements custom
upper() and lower() functions. It allocates a buffer of twice the input
size because some code points take more space when uppercased (or
lowercased) than the input. Code points such as U+FB04 (ffl ligature)
uppercase to _three_ code points, so this can lead to a buffer overflow
because the result is not nul-terminated.
The following patch catches the U_BUFFER_OVERFLOW_ERROR result and
re-allocates to the actual size needed.
CL patching Chromium is:
https://codereview.chromium.org/1704103002/
-scott
---
third_party/sqlite/src/ext/icu/icu.c | 31 +--
third_party/sqlite/src/test/icu.test | 7 +++
2 files changed, 32 insertions(+), 6 deletions(-)
diff --git a/third_party/sqlite/src/ext/icu/icu.c
b/third_party/sqlite/src/ext/icu/icu.c
index 7e2b800..d384f71 100644
--- a/third_party/sqlite/src/ext/icu/icu.c
+++ b/third_party/sqlite/src/ext/icu/icu.c
@@ -341,26 +341,45 @@ static void icuCaseFunc16(sqlite3_context *p, int
nArg, sqlite3_value **apArg){
if( !zInput ){
return;
}
- nInput = sqlite3_value_bytes16(apArg[0]);
+ nOutput = nInput = sqlite3_value_bytes16(apArg[0]);
- nOutput = nInput * 2 + 2;
zOutput = sqlite3_malloc(nOutput);
if( !zOutput ){
return;
}
if( sqlite3_user_data(p) ){
-u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status);
+nOutput = u_strToUpper(
+zOutput, nOutput/2, zInput, nInput/2, zLocale, &status) * 2;
}else{
-u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status);
+nOutput = u_strToLower(
+zOutput, nOutput/2, zInput, nInput/2, zLocale, &status) * 2;
}
- if( !U_SUCCESS(status) ){
+ if ( status == U_BUFFER_OVERFLOW_ERROR ) {
+UChar* newOutput = sqlite3_realloc(zOutput, nOutput);
+if( !newOutput ){
+ sqlite3_free(zOutput);
+ return;
+}
+zOutput = newOutput;
+status = U_ZERO_ERROR;
+if( sqlite3_user_data(p) ){
+ nOutput = u_strToUpper(
+ zOutput, nOutput/2, zInput, nInput/2, zLocale, &status) * 2;
+}else{
+ nOutput = u_strToLower(
+ zOutput, nOutput/2, zInput, nInput/2, zLocale, &status) * 2;
+}
+ }
+
+ if( U_FAILURE(status) ){
icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
+sqlite3_free(zOutput);
return;
}
- sqlite3_result_text16(p, zOutput, -1, xFree);
+ sqlite3_result_text16(p, zOutput, nOutput, xFree);
}
/*
diff --git a/third_party/sqlite/src/test/icu.test
b/third_party/sqlite/src/test/icu.test
index 73cb9b9..22948aa 100644
--- a/third_party/sqlite/src/test/icu.test
+++ b/third_party/sqlite/src/test/icu.test
@@ -56,6 +56,10 @@ set ::ograve "\xF2"
#
set ::szlig "\xDF"
+# U+FB03 (ffi ligature) and U+FB04 (ffl ligature). They're uppercased
+# to 'FFI' and 'FFL'.
+set ::ffi_ffl "\ufb03\ufb04"
+
# Tests of the upper()/lower() functions.
#
test_expr icu-2.1 {i1='HellO WorlD'} {upper(i1)} {HELLO WORLD}
@@ -72,6 +76,9 @@ test_expr icu-2.6 {i1=$::OGRAVE} {upper(i1)} $::OGRAVE
test_expr icu-2.7 {i1=$::szlig} {upper(i1)} "SS"
test_expr icu-2.8 {i1='SS'} {lower(i1)} "ss"
+test_expr icu-2.9 {i1=$::ffi_ffl} {upper(i1)} "FFIFFL"
+test_expr icu-2.10 {i1=$::ffi_ffl} {lower(i1)} $::ffi_ffl
+
# In turkish (locale="tr_TR"), the lower case version of I
# is "small dotless i" (code point 0x131 (decimal 305)).
#
--
2.7.0
[sqlite] Can SQLite be used from DLL
Hello, Teg,
On Fri, Feb 26, 2016 at 11:41 AM, Teg wrote:
> Hello Igor,
>
> I use Sqlite through a DLL interface. I mean I have another DLL that
> includes Sqlite static linked inside it. All opening, closing and
> access to Sqlite takes place inside the context of the DLL. The
> application never makes Sqlite calls directly. Instead it uses higher
> level functions inside the DLL which talk to the Database through
> Sqlite.
My structure is just like yours:
DLL1
class __declspec(dllexport) Database
{
public:
Database();
virtual ~Database();
virtual int Connect(const char *dbName);
};
DLL2:
class __declspec(dllexport) SQLiteDatabase : public Database
{
public:
SQLiteDatabase();
virtual ~SQLiteDatabase();
virtual int Connect(const char *dbName);
private:
sqlite3 *m_db;
};
DLL3:
extern "C" __declspec(dllexport) Database *ConnectToDB(Database *db)
{
db = new SQLiteDatabase;
return db;
}
main application:
class A
{
public:
A(Database *db)
{
m_db = NULL;
HINSTANCE hInst = LoadLibrary( TEXT( "dialogs.dll" ) );
MYFUNC func = (MYFUNC) GetProcAddress( hInst, "DatabaseProfile" );
m_db = func( db );
}
~A()
{
}
Database *GetDatabase()
{
return m_db;
}
private: Database *m_db;
};
int _tmain(int argc, _TCHAR* argv[])
{
Database *db = NULL, *m_db;
A *a = new A( db );
m_db = a->GetDatabase();
delete m_db;
delete a;
return 0;
}
Full code is in here:
http://stackoverflow.com/questions/35345258/crash-when-disconnecting-from-sqlite-database
Is CL suggestion makes sense?
Thank you.
> I've not attempted to do what you're doing exactly. I don't like
> sharing memory across the DLL boundary because I mostly use static
> linkage. It causes issues. I'll use allocated memory as handles to a
> class inside the DLL but to the caller it's just a black box. The
> caller doesn't know what the handle contains (like a file handle).
>
>
> Thursday, February 25, 2016, 10:31:49 AM, you wrote:
>
> IK> Hi,
> IK> I'm trying to design/make a program where I will use SQLite from the DLL.
>
> IK> What I mean is I will establish a connection inside a DLL but disconnect
> from
> IK> the database in my main application.
>
> IK> It turns out that doing so I am getting the crash when I try to
> disconnect from
> IK> the database file.
>
> IK> Connecting to the DB and issuing the query works OK and the data is
> retrieved.
>
> IK> I put up some small demo if you need a code to look at.
>
> IK> Thank you.
> IK> ___
> IK> sqlite-users mailing list
> IK> sqlite-users at mailinglists.sqlite.org
> IK> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Tegmailto:Teg at djii.com
>
[sqlite] Can SQLite be used from DLL
Hello Igor, I use Sqlite through a DLL interface. I mean I have another DLL that includes Sqlite static linked inside it. All opening, closing and access to Sqlite takes place inside the context of the DLL. The application never makes Sqlite calls directly. Instead it uses higher level functions inside the DLL which talk to the Database through Sqlite. I've not attempted to do what you're doing exactly. I don't like sharing memory across the DLL boundary because I mostly use static linkage. It causes issues. I'll use allocated memory as handles to a class inside the DLL but to the caller it's just a black box. The caller doesn't know what the handle contains (like a file handle). Thursday, February 25, 2016, 10:31:49 AM, you wrote: IK> Hi, IK> I'm trying to design/make a program where I will use SQLite from the DLL. IK> What I mean is I will establish a connection inside a DLL but disconnect from IK> the database in my main application. IK> It turns out that doing so I am getting the crash when I try to disconnect from IK> the database file. IK> Connecting to the DB and issuing the query works OK and the data is retrieved. IK> I put up some small demo if you need a code to look at. IK> Thank you. IK> ___ IK> sqlite-users mailing list IK> sqlite-users at mailinglists.sqlite.org IK> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] Can SQLite be used from DLL
On 2/25/16 10:31 AM, Igor Korot wrote: > Hi, > I'm trying to design/make a program where I will use SQLite from the DLL. > > What I mean is I will establish a connection inside a DLL but disconnect from > the database in my main application. > > It turns out that doing so I am getting the crash when I try to disconnect > from > the database file. > > Connecting to the DB and issuing the query works OK and the data is retrieved. > > I put up some small demo if you need a code to look at. > > Thank you. > This sounds like the classic multi-run time problem. If the DLL and the mainline are using different copies of the run time this can happen (your giving free a block of memory its malloc didn't allocate). The general fix is that if you are using DLL's then EVERYTHING in the program needs to use DLLs for anything used in any DLL to use the same copy. -- Richard Damon
[sqlite] Why is a separate journal file needed ?
On 2/26/2016 4:01 AM, Rowan Worth wrote: > On 24 February 2016 at 23:46, Igor Tandetnik wrote: > >> On 2/24/2016 10:08 AM, Stephen Chrzanowski wrote: >> >>> IMO, all that, plus the fact that you have an easy roll back mechanism. >>> Anything that needs to be put in the database is external to the pristine >>> database. Lock the database with a transaction, fill up the journal, the >>> power goes out, your pristine database isn't touched. >>> >> >> Rollback journal works the other way round. New data is written to >> database file; previous content of overwritten pages is copied to journal >> file. > > > In principle this is correct, but actually the database *file* is not > immediately modified in rollback mode. Instead when a page is modified the > original contents are saved to the rollback journal, and the page is > updated *in memory*. ... until such time as the cache needs to be spilled - then it's updated in the database file. In fact, I'm pretty sure the rollback journal is not created as long as all the changes are entirely in RAM. I simplified to make the main point stand out: it is not true that the database file remains "pristine" while the transaction is in progress, and changes are written only to the journal file. Instead, to the first approximation, the opposite is true. >> Committing a transaction is fast - simply delete the journal. > > > So this is not quite true. It's only[1] at commit time that the pages > modified in memory are paged back to disk, and this concentration of I/O > makes commit relatively expensive. Yes, I again simplified so as not to distract from the main point. Essentially, commit forces spilling the cache. -- Igor Tandetnik
[sqlite] Fwd: Re: SQLite and Thunderbird
On 2/26/16, Richard Hipp wrote: > > At this time, you basically have two options: > Beginning with version 3.12.0, there will be a third option. You can use the sqlite3_db_config() interface with the new SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER argument to enable the two-argument version of fts3_tokenizer() for a specific database connection at run-time. See the draft change log (https://www.sqlite.org/draft/releaselog/3_12_0.html) for links to further information. -- D. Richard Hipp drh at sqlite.org
[sqlite] ROWID schema surgery
On 2/26/16, Tristan Seligmann wrote: > > PRAGMA writable_schema=on; > UPDATE sqlite_master SET sql='CREATE TABLE some_table_name (..., storeID > INTEGER PRIMARY KEY)' WHERE tbl_name='some_table_name'; > I don't think that will work. I believe you are going to need to copy the table content. Like this: BEGIN; ALTER TABLE some_table_name RENAME TO temp_name; CREATE TABLE some_table_name(oid INTEGER PRIMARY KEY, ); INSERT INTO some_table_name SELECT oid, * FROM temp_name; DROP TABLE temp_name; COMMIT; -- D. Richard Hipp drh at sqlite.org
[sqlite] Encrypt the SQL query
On Fri, 26 Feb 2016 14:39:50 +0800, wrote: > To encrypt the SQLite database, I can only find the following extension: > > https://www.sqlite.org/see/doc/trunk/www/readme.wiki > > So I must recompile and enable the extension to encrypt > the database, is that correct? That's almost correct, you also have to buy a perpetual license for the proprietary SEE extension: http://www.sqlite.org/support.html http://www.hwaci.com/sw/sqlite/see.html -- Regards, Kees Nuyt
[sqlite] Why is a separate journal file needed ?
I have just checked a twitter database from a library of test DBs - the DB is 88Kb and the associated WAL is 4012Kb similarly I have a Safari history.DB that is 294Kb and associated WAL that is 3974Kb. these are the bigger ones in my test library but they are real world databases Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 26 February 2016 at 09:32, Paul Sanderson wrote: > WAL files can be many times bigger than a database - the default WAL > checkpoint size is when the WAL grows to > 1000 pages. You can get a > DB (for example) with 100 pages and a WAL of 1000 (with multiple > different copies of the same page). > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit > -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > > > On 26 February 2016 at 08:46, Rowan Worth wrote: >> On 24 February 2016 at 21:49, Richard Hipp wrote: >> >>> On 2/24/16, Simon Slavin wrote: >>> > Why can't the information which SQLite >>> > stores in a journal file be put in the database file ? >>> >>> Doing so would double the size of the database file. Every database >>> file would contain extra space (normally unused) set aside for the >>> journal. >>> >> >> Surely this extra space would be proportional to the size of the largest >> [recent] transaction, rather than the size of the database itself? To be >> specific I'm thinking of rollback journals, I don't have experience with >> WAL mode. >> >> -Rowan >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why is a separate journal file needed ?
WAL files can be many times bigger than a database - the default WAL checkpoint size is when the WAL grows to > 1000 pages. You can get a DB (for example) with 100 pages and a WAL of 1000 (with multiple different copies of the same page). Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 26 February 2016 at 08:46, Rowan Worth wrote: > On 24 February 2016 at 21:49, Richard Hipp wrote: > >> On 2/24/16, Simon Slavin wrote: >> > Why can't the information which SQLite >> > stores in a journal file be put in the database file ? >> >> Doing so would double the size of the database file. Every database >> file would contain extra space (normally unused) set aside for the >> journal. >> > > Surely this extra space would be proportional to the size of the largest > [recent] transaction, rather than the size of the database itself? To be > specific I'm thinking of rollback journals, I don't have experience with > WAL mode. > > -Rowan > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Re: SQLite and Thunderbird
I suppose that Thunderbird was making use of the fts3_tokenizer()
interface, which has be removed from standard builds due to security
concerns, as of version 3.11.0. You can reenable that feature at
compile-time by building with -DSQLITE_ENABLE_FTS3_TOKENIZER. See the
last bullet (the only bullet under the "Backwards Compability"
heading) of the release notes at
https://www.sqlite.org/releaselog/3_11_0.html for links to further
information.
At this time, you basically have two options:
(1) Compile your system sqlite3.so library using
SQLITE_ENABLE_FTS3_TOKENIZER and hope that none of the applications
that link against this library use it in such a way that the
fts3_tokenizer() could present a security vulnerability.
(2) Statically link against a version of SQLite that you compile
yourself. SQlite is a single file of C code ("sqlite3.c") so making
it a part of the project source tree is not a big deal.
Option (2) seems like the best choice to me since that guarantees that
Thunderbird will continue to operate regardless of what historical
version of sqlite3.so happens to be installed (or not installed) on
the system and regardless of the compile-time options used to create
that sqlite3.so. (For example, what if somebody installs a new
sqlite3.so that omits full-text search?) Static linking removes a
dependency and makes Thunderbird more robust.
On 2/26/16, Steven Haigh wrote:
> Dear sqlite-users list,
>
> I'd like to try and get some pointers on the following issue as
> documented on the following included BZ issues.
>
> Please CC me as I'm not subscribed to this list.
>
>
> Forwarded Message
> Subject: Re: SQLite and Thunderbird
> Date: Fri, 26 Feb 2016 10:06:25 +0100
> From: Jan Stan?k
> Organization: Red Hat
> To: Steven Haigh
> CC: nils at redhat.com, stransky at redhat.com
>
> Hi,
> I presume thet this is general thunderbird issue, not Fedora specific
> one. If so, I would suggest asking at
> sqlite-users at mailinglists.sqlite.org, they are usually quite helpful.
>
> Regards,
> Jan
>
> Dne 26.2.2016 v 07:10 Steven Haigh napsal(a):
>> Re:
>> https://bugzilla.redhat.com/show_bug.cgi?id=1310864
>> https://bugzilla.redhat.com/show_bug.cgi?id=1311032
>>
>> Hi all,
>>
>> Just trying to open a channel of communication regarding these bugs.
>>
>> While I believe thunderbird uses a format of call that is depreciated in
>> the newer SQLite packages, it is not ideal to statically compile
>> thunderbird against sqlite to make it work (which I believe is the
>> current fix).
>>
>> Any suggestions on a long-term fix?
>>
>
>
> --
> Jan Stanek - Red Hat Associate Developer Engineer - Databases Team
>
>
>
> --
> Steven Haigh
>
> Email: netwiz at crc.id.au
> Web: https://www.crc.id.au
> Phone: (03) 9001 6090 - 0412 935 897
>
>
>
>
--
D. Richard Hipp
drh at sqlite.org
[sqlite] Very minor documentation bug
On 2/25/16, Luke Amery wrote: > https://www.sqlite.org/c3ref/vfs.html > > New fields may be appended in figure versions > Thank you. Fixed at https://www.sqlite.org/src/info/ff3d7f845e1875d6 -- D. Richard Hipp drh at sqlite.org
[sqlite] bubble-generator.tcl usage question
> Message: 31 > Date: Thu, 25 Feb 2016 07:53:06 -0500 > From: Richard Hipp > To: SQLite mailing list > Subject: Re: [sqlite] bubble-generator.tcl usage question > Message-ID: > > Content-Type: text/plain; charset=UTF-8 > > On 2/25/16, btiffin wrote: >> >> I can't quite figure out if the bubble diagram generator, >> bubble-generator.tcl is available for modification and redistribution. > > I am happy that you found the bubble-generator.tcl script useful. > Feel free to reuse it for whatever you want. No attribution required. Great, Richard, many thanks. I thought that was likely the case, but needed to be sure, and there will be an attribution. I'll try and not make it too over the top, but I find it a absolute joy and privilege to be able to generate these diagrams with such a beautiful little utility. > >> >> I've created some COBOL syntax diagrams for GnuCOBOL with the tool, >> and >> I'd like to dedicate the images, along with bubble-cobol-data.tcl and >> a >> slightly modified bubble-generator.tcl to the public domain but I'm >> not >> sure if the files in art/syntax are part of the core SQLite dedication >> or not. >> >> Asking for a little clarification before releasing something to the >> public commons that is not within my rights or privilege to do so. >> >> Awesome outputs by the way, and a nicely concise domain specific >> language. >> >> Samples at http://open-cobol.sourceforge.net/diagrams/all.html and an >> entry in the GnuCOBOL FAQ at >> http://open-cobol.sourceforge.net/faq/index.html#bubble-cobol-tcl >> >> but not yet dedicated, until I'm sure that that would be allowed and >> appreciated. >> > > Let me know what the permanent links are, please, since I want to add > them to http://wiki.tcl.tk/21708 wiki page. Cool, and will. Should have the dedication in place this evening, and I'll set up the permanent home for the all.html page shortly, very likely the same one used for the preview. http://open-cobol.sourceforge.net/diagrams/all.html And I'll make a promise, that if the overview finds a new home, that link will be redirected. The practical use of the diagrams will be as part of the GnuCOBOL documentation, in the reserved words section. http://open-cobol.sourceforge.net/faq/index.html#reserved-words That document has grown too large, and will soon be split up and delivered as a Fossil based documentation set, but the above will be kept in place as permanent links, for as long as SourceForge is around. > > -- > D. Richard Hipp > drh at sqlite.org Have good, Richard, for now and for always. Cheers, Brian btiffin at gnu.org
