Re: [sqlite] single gdbconn object with multiple threads.

[Durga D]

Thank you Simon/Igor.

I got the answer.

>>But since all the work is serialized, a single thread that does all the
reading and writing would be just as fast, if not slightly faster.

How can I achieve this with single thread?

My Req. is: I have to write a wrapper for sqlite3. This wrapper will be
called by different clients from different threads within the process.
Requests might be read or write. Wrapper will have static sqlite3*. So all
clients shares the same sqlite3*. To simulate this, I created this sample
application.

doubt: How can I achieve this single thread when multiple clients are
called.

Am I right?

Regards,

On Mon, Jul 23, 2012 at 8:19 PM, Igor Tandetnik  wrote:

> On 7/23/2012 10:30 AM, Durga D wrote:
>
>> Unless your threads do something else in parallel, you could just as well

>>> do all SQLite work on a single thread
>>
>> doubt: all are parallel threads. started at same time. one thread is
>> writing and others are reading at the same time by using same sqlite3*.
>>
>
> Not really. What really happens is, one of the threads does some work,
> while the other three are sitting waiting on a mutex.
>
>
>  In this scenario, all are parallel.
>>
>
> Again - every SQLite API call acquires a mutex associated with the
> connection. If that mutex is already taken, then the thread sits there
> waiting for it to be released. In other words, two SQLite calls on the same
> connection never execute at the same time - they are serialized on the
> mutex. Threads effectively take turns to make these calls.
>
>
>  my main doubt is: same sqlite3* is passing to 4 threads from the primary
>> thread.
>>
>> Is it correct way to implement multiple readers and single writer?
>>
>
> There are no technical problems with this, if that's what you are asking.
> It would work. But since all the work is serialized, a single thread that
> does all the reading and writing would be just as fast, if not slightly
> faster. You are adding complexity but are not gaining any performance out
> of it.
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - Creating Table

[Keith Medcalf]

And of course, finalize after close is wrong.  You finalize the statement, then 
close the db, then bail.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Robert Myers
> Sent: Monday, 23 July, 2012 21:44
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] C++ - Creating Table
> 
> On 7/23/2012 7:43 PM, Arbol One wrote:
> > Thank you Michael for your prompt response.
> > I have been able to duplicate the error message. I think this could be a
> bug
> > in SQLite3.
> > void jme::mySQLite3::createTable(const std::string& s) throw
> > (std::exception) {
> >  rc = sqlite3_prepare_v2( db, s.c_str(), -1, , NULL);
> >  if(rc != SQLITE_OK) {
> rc is a bool, not an int, you have a type coercion error here.
> 
> >  sqlite3_close(db);
> >  sqlite3_finalize(stmt);
> >  std::string error("Error prepare_v2: ");
> >  error += sqlite3_errmsg(db);
> >  std::cout << "Error: " << rc << " " << error << std::endl;
> >  }
> >  rc = sqlite3_step(stmt);
> There's a use after free error here  (you don't return / throw after
> finalizing)
> >  std::cout << "Error: " << rc << std::endl;
> >
> >  if(rc != SQLITE_DONE) {
> And another type coercion error here. rc will never be equal to SQLITE_DONE.
> 
> >  sqlite3_close(db);
> >  sqlite3_finalize(stmt);
> And another use after free here, since the step will fail, you'll close
> it again. Of course, that assumes the step isn't going to crash in the
> first place.
> >  std::string error("error sqlite3_step: ");
> >  error += sqlite3_errmsg(db);
> >  std::cout << error << std::endl;
> >  }
> >  sqlite3_finalize(stmt);
> 
> And another use after free here, same problem with the error handling.
> 
> You're committing one of the cardinal sins of C++. Let the compiler do
> all the work, don't do it yourself, you'll (generic you) screw it up.
> Wrap the DB and query in objects that handle the clean up for you.
> 
> Compile everything (except sqlite3.c itself) at the highest possible
> warning level treating warnings as errors. That would've caught the type
> error. It's one of the first things I set up with new projects.
> 
> Rob
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - Creating Table

[Robert Myers]

On 7/23/2012 7:43 PM, Arbol One wrote:

Thank you Michael for your prompt response.
I have been able to duplicate the error message. I think this could be a bug
in SQLite3.
void jme::mySQLite3::createTable(const std::string& s) throw
(std::exception) {
 rc = sqlite3_prepare_v2( db, s.c_str(), -1, , NULL);
 if(rc != SQLITE_OK) {

rc is a bool, not an int, you have a type coercion error here.


 sqlite3_close(db);
 sqlite3_finalize(stmt);
 std::string error("Error prepare_v2: ");
 error += sqlite3_errmsg(db);
 std::cout << "Error: " << rc << " " << error << std::endl;
 }
 rc = sqlite3_step(stmt);
There's a use after free error here  (you don't return / throw after 
finalizing)

 std::cout << "Error: " << rc << std::endl;

 if(rc != SQLITE_DONE) {

And another type coercion error here. rc will never be equal to SQLITE_DONE.


 sqlite3_close(db);
 sqlite3_finalize(stmt);
And another use after free here, since the step will fail, you'll close 
it again. Of course, that assumes the step isn't going to crash in the 
first place.

 std::string error("error sqlite3_step: ");
 error += sqlite3_errmsg(db);
 std::cout << error << std::endl;
 }
 sqlite3_finalize(stmt);


And another use after free here, same problem with the error handling.

You're committing one of the cardinal sins of C++. Let the compiler do 
all the work, don't do it yourself, you'll (generic you) screw it up. 
Wrap the DB and query in objects that handle the clean up for you.


Compile everything (except sqlite3.c itself) at the highest possible 
warning level treating warnings as errors. That would've caught the type 
error. It's one of the first things I set up with new projects.


Rob

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help: database/disk full in creating index

[Wang, Gao]

Thank you Simon!

>Can you explain what SQLite calls you are using to do this ?  Do you mean you 
>are using a CREATE INDEX command ?

Yes indeed. The command reads: "CREATE INDEX data_idx ON data (key ASC);"

>Also, can you execute "PRAGMA journal_mode" on your database and tell us the 
>result ?

The journal_mode is "delete".

Kindest regards,
Gao

Student in Statistical Genetics, Baylor College of Medicine
(the same Gao as from: gaow [at] bcm.edu / gaow [at] rice.edu)
>
>

On 23 Jul 2012, at 10:24pm, "Wang, Gao"  wrote:

>> I am having problems creating index for my database.

>Can you explain what SQLite calls you are using to do this ?  Do you mean you 
>are using a CREATE INDEX command ?

>Also, can you execute "PRAGMA journal_mode" on your database and tell us the 
>result ?

>Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling System.Data.SQLite with latest sqlite3.dll

[J Decker]

On Mon, Jul 23, 2012 at 3:04 AM, Bernhard Mogens Ege  wrote:
> I am trying to figure out how to compile System.Data.SQLite and in the
> process update sqlite from 3.7.12.1 to 3.7.13 as I need the shared cache
> option for memory connections.
>
>
>
> And when I write 'trying' I am having trouble figuring out where exactly
> System.Data.SQLite picks up sqlite3.dll from. The source code doesn't
> include it and the installer also doesn't include it, but it must be
> somewhere.
>

It is in there in 1.0.80.0\SQLite.Interop\src\core\sqlite3.c

You need 3 projects, the netmodule, the static sqlite library and the
system.data.sqlite project

1.0.80.0\System.Data.SQLite\System.Data.SQLite.Module.2010.csproj
1.0.80.0\SQLite.Interop\SQLite.Interop.Static.2010.vcxproj
1.0.80.0\System.Data.SQLite\System.Data.SQLite.2010.csproj

interop must depend on module
system.data.sqlite must depend on interop  (right click on a project
and use 'Project Dependencies' to add the additional dependencies)

Then you can just reference system.data.sqlite.2010 in other projects

Turn off generate XML documentation in build tab of project properties
of system.data.sqlite.module project.  (otherwise it causes it to
always rebuild, and in turn all dependants of it)

Probably similar rules for more recent versions

That is if you want to include building from source in your project;
otherwise you can just use the default solution of sqlite to build a
DLL and add that as the reference.

>
>
> Is it even safe to update System.Data.SQLite 1.0.81.0 with sqlite3.dll
> 3.7.13 and will the shared cache feature work?
>
>
>
> Also, compiling System.Data.SQLite for .NET 4.0 results in some
> warnings/errors. Are they safe to ignore?
>
>
>
> Warning1   The parent file, 'SR.resx',
> for file 'SR.Designer.cs' cannot be found in the project file.
> System.Data.SQLite.2010
>
> Warning2   Load of property 'OutputType'
> failed.System.Data.SQLite.Module.2010
>
> Warning3   The parent file, 'SR.resx',
> for file 'SR.Designer.cs' cannot be found in the project file.
> System.Data.SQLite.Module.2010
>
> Warning4   Error 2005: Mapping
> Association 'FK_InternationalOrders_OrderID_OrderID' is redundant: Its
> referential integrity constraint provides sufficient information.  You can
> safely delete the mapping information for this association.
> C:\Users\bme\Documents\Visual Studio
> 2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
> 793  800  testlinq.2010
>
> Warning5   Error 2005: Mapping
> Association 'FK_OrderDetails_OrderID_OrderID' is redundant: Its referential
> integrity constraint provides sufficient information.  You can safely delete
> the mapping information for this association.
> C:\Users\bme\Documents\Visual Studio
> 2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
> 801  809  testlinq.2010
>
> Warning6   Error 2005: Mapping
> Association 'FK_OrderDetails_ProductID_ProductID' is redundant: Its
> referential integrity constraint provides sufficient information.  You can
> safely delete the mapping information for this association.
> C:\Users\bme\Documents\Visual Studio
> 2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
> 810  818  testlinq.2010
>
> Warning7   The referenced component
> 'mscorlib' could not be found.
>
> Warning8   The referenced component
> 'mscorlib' could not be found.
>
>
>
> --
>
> Bernhard
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - Creating Table

[Arbol One]

Thank you Michael for your prompt response.
I have been able to duplicate the error message. I think this could be a bug
in SQLite3.
If it is a bug, how do I go about reporting it?
//== my_sqlite3.hpp ===
#ifndef JME_MY_SQLITE3_HPP
#define JME_MY_SQLITE3_HPP

#include 
#include"sqlite3.h"
namespace jme {
class mySQLite3 {
private:
sqlite3 *db;// Data Base
sqlite3_stmt* stmt; // SQL statement
bool rc;// SQL return code
std::string databese_name;// The name of the database
std::string sql_param_tblName;// Databese table Name parameters
std::string stmtName; // SQL statement name

std::string apstr;// All Purpose String
int apint;  // All Purpose Integer
std::string sqlite3_version;
private:
//! Initalization method, instead of using the 'tor initialize
//! all varialbles and set all necessary parameters, we use an Init
void Init();

public:
//Constructors
mySQLite3();
mySQLite3(const std::string&);
//Destructor
~mySQLite3();

//! Pass to this method the name of the database to be openned
void createDatabase(const std::string&)throw(std::exception) ;

//! Pass to this method the SQL statement for the table creation
void createTable(const std::string& )throw(std::exception) ;
};
}
#endif

=== my_sqlite3.cpp===
#ifndef JME_MY_SQLITE3_HPP
#include "my_sqlite3.hpp"
#endif

jme::mySQLite3::mySQLite3(const std::string& s) {
this->Init();
this->databese_name = s;
this->createDatabase(databese_name);
}
jme::mySQLite3::mySQLite3() {
this->Init();
}
void jme::mySQLite3::Init() {
databese_name = sql_param_tblName = stmtName = ".";
sqlite3_version = "SQLite version: ";
sqlite3_version += sqlite3_libversion();
}
void jme::mySQLite3::createTable(const std::string& s) throw
(std::exception) {
rc = sqlite3_prepare_v2( db, s.c_str(), -1, , NULL);
if(rc != SQLITE_OK) {
sqlite3_close(db);
sqlite3_finalize(stmt);
std::string error("Error prepare_v2: ");
error += sqlite3_errmsg(db);
std::cout << "Error: " << rc << " " << error << std::endl;
}
rc = sqlite3_step(stmt);
std::cout << "Error: " << rc << std::endl;

if(rc != SQLITE_DONE) {
sqlite3_close(db);
sqlite3_finalize(stmt);
std::string error("error sqlite3_step: ");
error += sqlite3_errmsg(db);
std::cout << error << std::endl;
}
sqlite3_finalize(stmt);
}
void jme::mySQLite3::createDatabase(const std::string&
s)throw(std::exception) {
rc = sqlite3_open_v2(s.c_str(),
 , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
 NULL);
if(rc != SQLITE_OK) {
sqlite3_close(db);
apstr = "Error opening database: ";
apstr += databese_name;
apstr += "\nError value: ";
apstr += sqlite3_errmsg(db);
std::cout << apstr << std::endl;
}
}
jme::mySQLite3::~mySQLite3() {
sqlite3_close(db);
}

=== main.cpp ==
//STL
#include
//JME
#include"my_sqlite3.hpp"

int main() {
std::string s1("CREATE TABLE name(n_id INTEGER PRIMARY KEY, title TEXT,
fname TEXT, mname TEXT, lname TEXT)");
std::string s2("INSERT INTO name (n_id, title, fname, mname, lname)
VALUES (?, ?, ?, ?, ?)");

jme::mySQLite3 myDB("database.db3");
myDB.createTable(s1);

std::cin.get();
return 0;
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Monday, July 23, 2012 3:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] C++ - Creating Table

Just as a sanity check your code does work OK.  I made it a standalone
program.

#include 
#include "sqlite3.h"
using namespace std;
class mySQLite3Class {
private:
  //SQLite3
  sqlite3* db; //SQLite3
  string dbName; // Database name
  string apstr; // All Purpose String
  string sql_param_tblName; // Databese table Name parameters
  string stmtName;  // SQL statement name
public:
  void createDB();
  void create_tblName();
  void createDatabase(const string& s);
  void createTable(const string& s);
  mySQLite3Class(const string& s) {
createDatabase(s);
  }
};
void mySQLite3Class::createDatabase(const string& s) {
  int rc = sqlite3_open_v2(s.c_str(),
   , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
   NULL);
  if(rc != SQLITE_OK) {
std::cout << rc << std::endl;
  }
}
void mySQLite3Class::createTable(const string& s) {
  sqlite3_stmt *stmt;
  int rc = sqlite3_prepare_v2(db, s.c_str(), s.length(), , NULL );
  if(rc != SQLITE_OK) {
std::cout << rc << std::endl;// error = 1
std::cout << sqlite3_errmsg(db)
  << std::endl; // er-msg = library routine called out of
sequence
  }
  rc = sqlite3_step(stmt);
  if(rc != SQLITE_DONE) {
std::cout << rc << endl;
  }
  sqlite3_finalize(stmt);
}
int 

Re: [sqlite] Restore SQLite DB from WAL

[gsm-ginger]

> The WAL file does not store a bunch of DELETE FROM statements. The WAL
> file stores 4k pages of the database file as they will appear after the
> delete occurs. Details at http://www.sqlite.org/fileformat2.html
> So, no, there is no way to do what you are asking

Is there, instead, a method for extracting the pages marked for ignore from the
DB/ WAL / SHM files and manually piecing back together, or issuing a directive
to alter the relevant page markers from "ignore" to their previous state?

This article implies there is:
http://digitalinvestigation.wordpress.com/2012/05/04/the-forensic-implications-of-sqlites-write-ahead-log/

That said, I'm guessing it would be a non-trivial task even for a seasoned DB 
programmer...

The only other way I can see out of it would be to create a dummy SMS DB
(e.g. with one test SMS), then use this method to create a XML template to
use with SMS Backup / restore, manually piecing the XML file togther using
data extracted with carefully-chosen* GNU strings parameters:

http://forum.xda-developers.com/showthread.php?t=1585957
http://forum.xda-developers.com/showthread.php?t=1683608

That or stump up the 250 GBP for Epilog SQLite analysis ;)

M

*At least it looked that way when running the SHM file through strings
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Restore SQLite DB from WAL

[Gilleán Ó Raghallaigh]

> I think your sms-controlling app has synced the whole sms database
> with some server. When you start your phone it shows you local data
> but then it sees that server has latest data (maybe using modification
> date on the database, maybe some synchronization token stored in the
> database) and restores everything from there.
>
> So maybe you need to look for some setting saying "allow to sync
> everything with server" and "allow to sync everything back from
> server".
>
>
>Pavel

Quite possible, although AFAICT this is not the default behaviour unless the 
SMS Backup / Restore is installed and set to sync to GMail.

This would be consistent with the fact that data was turned off for the entire
accidental delete, imaging and restore attempt.

Thanks anyway, though :)

M
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help: database/disk full in creating index

[Richard Hipp]

On Mon, Jul 23, 2012 at 5:24 PM, Wang, Gao  wrote:

> 2) is there a way to re-set such a tmp directory path?
>

export TMPDIR=/new/temporary/dir/with-lots-of-space

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help: database/disk full in creating index

[Simon Slavin]

On 23 Jul 2012, at 10:24pm, "Wang, Gao"  wrote:

> I am having problems creating index for my database.

Can you explain what SQLite calls you are using to do this ?  Do you mean you 
are using a CREATE INDEX command ?

Also, can you execute "PRAGMA journal_mode" on your database and tell us the 
result ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help: database/disk full in creating index

[Wang, Gao]

Dear sqlite experts,

I am having problems creating index for my database. The error says:
"database or disk is full", although I am sure there is sufficient space
left on the disk where the database is located. However the Linux /tmp
and /var/tmp folders are mounted to a 40GB small disk (my un-index
database is about 30GB) which I guess might be the cause of the problem.
My questions are 1) is it true that indexing a database  will require
sufficient disk space on some temporary directories in the operating
system, and 2) is there a way to re-set such a tmp directory path? It turns
out the pragma_temp_store option does not work for my case, and is
flagged obsolete in the sqlite manual.

I would appreciate any suggestions to help me out. Thank you!

Kindest regards,
Gao

Student in Statistical Genetics, Baylor College of Medicine
(the same Gao as from: gaow [at] bcm.edu / gaow [at] rice.edu)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Restore SQLite DB from WAL

[Pavel Ivanov]

> In each case the same thing happens, the messages briefly display, then
> disappear

I think your sms-controlling app has synced the whole sms database
with some server. When you start your phone it shows you local data
but then it sees that server has latest data (maybe using modification
date on the database, maybe some synchronization token stored in the
database) and restores everything from there.

So maybe you need to look for some setting saying "allow to sync
everything with server" and "allow to sync everything back from
server".


Pavel


On Mon, Jul 23, 2012 at 3:51 PM,   wrote:
> So close, yet so far! This is really getting on my wick...
>
>>> Example scenario where vast swathes of content have vanished from
>>> mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty
>>> Android SMS app:
>>>
>>> ls -al /data/data/com.android.providers.telephony/databases/mm*
>>> -rw-rw 1 root root 60416 Jul 17 20:16 mmssms.db
>>> -rw-rw 1 radio radio 32768 Jul 17 16:18 mmssms.db-shm
>>> -rw-rw 1 radio radio 628832 Jun 30 19:23 mmssms.db-wal
>> The WAL file does not contain the old data, it contains the new data. If
>> the WAL file has not been checkpointed, you should be able to simply delete
>> the WAL file and the old data will still be in the original database file.
>> A "checkpoint" operation is the act of moving the new database from the WAL
>> file back into the original database file, presumably overwriting the old
>> data (depending on your settings and other factors).
>> But, if the WAL file has been partially checkpointed, and that checkpoint
>> was interrupted by a crash or power failure, deleting the WAL file will
>> corrupt your database. So you should probably run the experiment on a
>> backup. :-)
>
> In my test, I started the phone in single-user/recovery mode, deleted
> mmssms.db-wal and restarted. The deleted messages showed very briefly,
> then vanished.
> I then restored mmssms.db to its original (just after deletion) state
> and deleted both mmssms.db-wal and mmssms.db-shm. Same result.
> Not to be deterred, I then restored the mmssms.db, deleted the
> mmssms.db-shm and created a zero-length file. In all cases the owner was
> set to radio:radio (in the initial example below mmssms.db was incorrectly
> set to root.)
> In each case the same thing happens, the messages briefly display, then
> disappear, mmssms.db, then mmssms.db-wal and mmssms.db-shm with the much
> sought-after deleted messages, so (if I'm not mistaken) at least some of
> the info is retained in mmssms.db itself, albeit with instructions to
> purge.
>
> Is there any way to merge the lost data stored in mmssms.db-wal /
> mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a
> bunch of DELETE FROM statements could they be changed to INSERT INTO?
>
> Mandy
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Restore SQLite DB from WAL

[gsm-ginger]

> The WAL file does not store a bunch of DELETE FROM statements. The WAL
> file stores 4k pages of the database file as they will appear after the
> delete occurs. Details at http://www.sqlite.org/fileformat2.html
> So, no, there is no way to do what you are asking.

Damn!

Failing that, is there a means of extracting the data from the WAL / SHM 
by some similar means to extracting from the DB?

Thanks

Mandy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Restore SQLite DB from WAL

[Richard Hipp]

On Mon, Jul 23, 2012 at 3:51 PM,  wrote:

>
> Is there any way to merge the lost data stored in mmssms.db-wal /
> mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a
> bunch of DELETE FROM statements could they be changed to INSERT INTO?
>


The WAL file does not store a bunch of DELETE FROM statements.  The WAL
file stores 4k pages of the database file as they will appear after the
delete occurs.  Details at http://www.sqlite.org/fileformat2.html

So, no, there is no way to do what you are asking.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - Creating Table

[Black, Michael (IS)]

Just as a sanity check your code does work OK.  I made it a standalone program.

#include 
#include "sqlite3.h"
using namespace std;
class mySQLite3Class {
private:
  //SQLite3
  sqlite3* db; //SQLite3
  string dbName; // Database name
  string apstr; // All Purpose String
  string sql_param_tblName; // Databese table Name parameters
  string stmtName;  // SQL statement name
public:
  void createDB();
  void create_tblName();
  void createDatabase(const string& s);
  void createTable(const string& s);
  mySQLite3Class(const string& s) {
createDatabase(s);
  }
};
void mySQLite3Class::createDatabase(const string& s) {
  int rc = sqlite3_open_v2(s.c_str(),
   , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
   NULL);
  if(rc != SQLITE_OK) {
std::cout << rc << std::endl;
  }
}
void mySQLite3Class::createTable(const string& s) {
  sqlite3_stmt *stmt;
  int rc = sqlite3_prepare_v2(db, s.c_str(), s.length(), , NULL );
  if(rc != SQLITE_OK) {
std::cout << rc << std::endl;// error = 1
std::cout << sqlite3_errmsg(db)
  << std::endl; // er-msg = library routine called out of sequence
  }
  rc = sqlite3_step(stmt);
  if(rc != SQLITE_DONE) {
std::cout << rc << endl;
  }
  sqlite3_finalize(stmt);
}
int main(int argc,char *argv[])
{
  string dbName = "001Database.sql";
  string sql_param_tblName = "CREATE TABLE name(n_id INTEGER PRIMARY KEY, title 
TEXT, fname TEXT, mname TEXT, lname TEXT)";
  mySQLite3Class *myDB = new mySQLite3Class(dbName);
  myDB->createTable(sql_param_tblName);
  return 0;
}


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Arbol One [arbol...@gmail.com]
Sent: Monday, July 23, 2012 1:54 PM
To: SqLite
Subject: EXT :[sqlite] C++ - Creating Table


Using SQLite version 3.7.8 amalgamation, under Win7 with MinGW, I compile
the bellow program, but for some strange reason I am getting a runtime error
when creating the table. I hope that one of you would be able to tell me
what I am doing wrong.



TIA

===

class mySQLite3Class {

private:

//SQLite3

sqlite3* db; //SQLite3

Glib::ustring dbName; // Database name

Glib::ustring apstr; // All Purpose String



Glib::ustring sql_param_tblName; // Databese table Name parameters

Glib::ustring stmtName;  // SQL statement name

public:

void createDB();

void create_tblName();

mySQLite3Class(const Glib::ustring& s){ createDatabase(s);}

};

void mySQLite3Class::createDatabase(const Glib::ustring& s) {

rc = sqlite3_open_v2(s.c_str(),

 , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,

 NULL);

if(rc != SQLITE_OK) {

std::cout << rc << std::endl;

}

}

void mySQLite3Class::createTable(const Glib::ustring& s){

 rc = sqlite3_prepare_v2(db, s.c_str(), s.length(), , NULL );

if(rc != SQLITE_OK) {

   std::cout << rc << std::endl;// error = 1

   std::cout << sqlite3_errmsg(db)

   << std::endl; // er-msg = library routine called out
of sequence

}

rc = sqlite3_step(stmt);

if(rc != SQLITE_DONE) {

std::cout << rc << stdl;

}

sqlite3_finalize(stmt);

}

myClass{

private:

mySQLite3Class* myDB;

Glib::ustring sql_param_tblName;

Glib::ustring dbName;

public:

myClass();

}

myClass::myClass(){

dbName = "001Database.sql";

sql_param_tblName = "CREATE TABLE name(n_id INTEGER PRIMARY KEY, title
TEXT, fname TEXT, mname TEXT, lname TEXT)";

myDB = new mySQLite3Class(dbName);

myDB->createTable(sql_param_tblName); ==> // problem

}

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Restore SQLite DB from WAL

[gsm-ginger]

So close, yet so far! This is really getting on my wick...

>> Example scenario where vast swathes of content have vanished from
>> mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty
>> Android SMS app:
>>
>> ls -al /data/data/com.android.providers.telephony/databases/mm*
>> -rw-rw 1 root root 60416 Jul 17 20:16 mmssms.db
>> -rw-rw 1 radio radio 32768 Jul 17 16:18 mmssms.db-shm
>> -rw-rw 1 radio radio 628832 Jun 30 19:23 mmssms.db-wal
> The WAL file does not contain the old data, it contains the new data. If
> the WAL file has not been checkpointed, you should be able to simply delete
> the WAL file and the old data will still be in the original database file.
> A "checkpoint" operation is the act of moving the new database from the WAL
> file back into the original database file, presumably overwriting the old
> data (depending on your settings and other factors).
> But, if the WAL file has been partially checkpointed, and that checkpoint
> was interrupted by a crash or power failure, deleting the WAL file will
> corrupt your database. So you should probably run the experiment on a
> backup. :-)

In my test, I started the phone in single-user/recovery mode, deleted 
mmssms.db-wal and restarted. The deleted messages showed very briefly, 
then vanished.
I then restored mmssms.db to its original (just after deletion) state 
and deleted both mmssms.db-wal and mmssms.db-shm. Same result.
Not to be deterred, I then restored the mmssms.db, deleted the 
mmssms.db-shm and created a zero-length file. In all cases the owner was
set to radio:radio (in the initial example below mmssms.db was incorrectly
set to root.)
In each case the same thing happens, the messages briefly display, then 
disappear, mmssms.db, then mmssms.db-wal and mmssms.db-shm with the much
sought-after deleted messages, so (if I'm not mistaken) at least some of
the info is retained in mmssms.db itself, albeit with instructions to 
purge.

Is there any way to merge the lost data stored in mmssms.db-wal / 
mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a 
bunch of DELETE FROM statements could they be changed to INSERT INTO?

Mandy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - Creating Table

[Pavel Ivanov]

As you don't have stmt defined anywhere this is apparently not the
actual program you run. Seeing the full source code would be more
helpful. If the whole source is too big try to reduce it to small
program reproducing the problem. It's possible that while attempting
to reduce program source you'll find the culprit by yourself.

My bet would be you have some multi-threading issues.


Pavel


On Mon, Jul 23, 2012 at 2:54 PM, Arbol One  wrote:
> Using SQLite version 3.7.8 amalgamation, under Win7 with MinGW, I compile
> the bellow program, but for some strange reason I am getting a runtime error
> when creating the table. I hope that one of you would be able to tell me
> what I am doing wrong.
>
>
>
> TIA
>
> ===
>
> class mySQLite3Class {
>
> private:
>
> //SQLite3
>
> sqlite3* db; //SQLite3
>
> Glib::ustring dbName; // Database name
>
> Glib::ustring apstr; // All Purpose String
>
>
>
> Glib::ustring sql_param_tblName; // Databese table Name parameters
>
> Glib::ustring stmtName;  // SQL statement name
>
> public:
>
> void createDB();
>
> void create_tblName();
>
> mySQLite3Class(const Glib::ustring& s){ createDatabase(s);}
>
> };
>
> void mySQLite3Class::createDatabase(const Glib::ustring& s) {
>
> rc = sqlite3_open_v2(s.c_str(),
>
>  , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
>
>  NULL);
>
> if(rc != SQLITE_OK) {
>
> std::cout << rc << std::endl;
>
> }
>
> }
>
> void mySQLite3Class::createTable(const Glib::ustring& s){
>
>  rc = sqlite3_prepare_v2(db, s.c_str(), s.length(), , NULL );
>
> if(rc != SQLITE_OK) {
>
>std::cout << rc << std::endl;// error = 1
>
>std::cout << sqlite3_errmsg(db)
>
><< std::endl; // er-msg = library routine called out
> of sequence
>
> }
>
> rc = sqlite3_step(stmt);
>
> if(rc != SQLITE_DONE) {
>
> std::cout << rc << stdl;
>
> }
>
> sqlite3_finalize(stmt);
>
> }
>
> myClass{
>
> private:
>
> mySQLite3Class* myDB;
>
> Glib::ustring sql_param_tblName;
>
> Glib::ustring dbName;
>
> public:
>
> myClass();
>
> }
>
> myClass::myClass(){
>
> dbName = "001Database.sql";
>
> sql_param_tblName = "CREATE TABLE name(n_id INTEGER PRIMARY KEY, title
> TEXT, fname TEXT, mname TEXT, lname TEXT)";
>
> myDB = new mySQLite3Class(dbName);
>
> myDB->createTable(sql_param_tblName); ==> // problem
>
> }
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

[Black, Michael (IS)]

Nope -- that doesn't work.  Seems to me if the import is going to assume the 
field is text it should also recognize that if it doesn't start with a quote it 
shouldn't assume that all quotes are delimiters.

Plus, it should recognize that any quotes that aren't at the beginning or 
end-of-field aren't delimiters either.  Only quotes at both ends of the fields 
may be removed.



So these should all work when quotes are NOT the separator but the pipe symbol 
is:

1|"this is a test"|1 -- quotes removed field inserted

2|'this is a test'|2 -- single quotes removed and field inserted

3|'this"is"a"test'|3 -- singled quotes removed but double quotes remain.



Or is there some standard that we ought to be following?



C:\sqlite>more data2.txt
1|TEXT LINE 1 (72)'|43721
2|TEXT LINE 2 (72)'|43721
3|TEXT LINE 3 (72)'|43721
4|TEXT LINE 4 (72)'|43721
5|TEXT LINE 5 (72)'|43721
6|TEXT LINE 6 (72)'|43721
7|TEXT LINE 7 (72)'|43721
8|TEXT LINE 8 (72)'|43721
9|TEXT LINE 9 (72)'|43721

C:\sqlite>sqlite3 data.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE CONFIGURATION
(
  RECORD_IDNUMERIC   NOT NULL,
  TEXT  TEXT,
  NUMERIC_DATA  NUMERIC
);
COMMIT;
sqlite> .import data2.txt configuration
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE CONFIGURATION
(
  RECORD_IDNUMERIC   NOT NULL,
  TEXT  TEXT,
  NUMERIC_DATA  NUMERIC
);
INSERT INTO "CONFIGURATION" VALUES(1,'TEXT LINE 1 (72)',43721);
INSERT INTO "CONFIGURATION" VALUES(2,'TEXT LINE 2 (72)',43721);
INSERT INTO "CONFIGURATION" VALUES(3,'TEXT LINE 3 (72)',43721);
INSERT INTO "CONFIGURATION" VALUES(4,'TEXT LINE 4 (72)',43721);
INSERT INTO "CONFIGURATION" VALUES(5,'TEXT LINE 5 (72)',43721);
INSERT INTO "CONFIGURATION" VALUES(6,'TEXT LINE 6 (72)',43721);
INSERT INTO "CONFIGURATION" VALUES(7,'TEXT LINE 7 (72)',43721);
INSERT INTO "CONFIGURATION" VALUES(8,'TEXT LINE 8 (72)',43721);
INSERT INTO "CONFIGURATION" VALUES(9,'TEXT LINE 9 (72)',43721);
COMMIT;



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Monday, July 23, 2012 1:40 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes 
Sometimes When Importing Data


On Mon, Jul 23, 2012 at 2:28 PM, Kevin Benson wrote:

> On Mon, Jul 23, 2012 at 12:05 PM, Richard Hipp  wrote:
>
> > On Mon, Jul 23, 2012 at 8:37 AM, Hayes, Michael - IS <
> > michael.ha...@exelisinc.com> wrote:
> >
> > >
> > > The documentation says that the separator will be honored even inside
> of
> > > quotes.   ("The SQLite shell will always split fields on the separator
> > > character, no matter what comes before or after it. Quotes or
> backslashes
> > > won't escape them.).
> >
> >
> > I'm not able to find this statement anywhere in the SQLite documentation.
> > Can you send a link?
> >
> > --
>
>
> He's quoted from the wiki:
>  http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles
>

Yeah.  That wiki is really old.  Don't believe it

The CVS import for the command-line shell treats " as a quoting
characters.  All content between "..." is considered to be part of a single
field of the CVS, even if that content includes newline characters.

I think it will work to escape your isolated " characters by replacing them
with four double-quotes in a row:  



>
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C++ - Creating Table

[Arbol One]

Using SQLite version 3.7.8 amalgamation, under Win7 with MinGW, I compile
the bellow program, but for some strange reason I am getting a runtime error
when creating the table. I hope that one of you would be able to tell me
what I am doing wrong.

 

TIA

===

class mySQLite3Class {

private:

//SQLite3

sqlite3* db; //SQLite3

Glib::ustring dbName; // Database name

Glib::ustring apstr; // All Purpose String

 

Glib::ustring sql_param_tblName; // Databese table Name parameters

Glib::ustring stmtName;  // SQL statement name

public:

void createDB();

void create_tblName();

mySQLite3Class(const Glib::ustring& s){ createDatabase(s);}

};

void mySQLite3Class::createDatabase(const Glib::ustring& s) {

rc = sqlite3_open_v2(s.c_str(),

 , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,

 NULL);

if(rc != SQLITE_OK) {

std::cout << rc << std::endl;

}

}

void mySQLite3Class::createTable(const Glib::ustring& s){

 rc = sqlite3_prepare_v2(db, s.c_str(), s.length(), , NULL );

if(rc != SQLITE_OK) {

   std::cout << rc << std::endl;// error = 1

   std::cout << sqlite3_errmsg(db) 

   << std::endl; // er-msg = library routine called out
of sequence

}

rc = sqlite3_step(stmt);

if(rc != SQLITE_DONE) {

std::cout << rc << stdl;

}

sqlite3_finalize(stmt);

}

myClass{

private:

mySQLite3Class* myDB;

Glib::ustring sql_param_tblName;

Glib::ustring dbName;

public:

myClass();

}

myClass::myClass(){

dbName = "001Database.sql";

sql_param_tblName = "CREATE TABLE name(n_id INTEGER PRIMARY KEY, title
TEXT, fname TEXT, mname TEXT, lname TEXT)";

myDB = new mySQLite3Class(dbName);

myDB->createTable(sql_param_tblName); ==> // problem

}

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

[Richard Hipp]

On Mon, Jul 23, 2012 at 2:28 PM, Kevin Benson wrote:

> On Mon, Jul 23, 2012 at 12:05 PM, Richard Hipp  wrote:
>
> > On Mon, Jul 23, 2012 at 8:37 AM, Hayes, Michael - IS <
> > michael.ha...@exelisinc.com> wrote:
> >
> > >
> > > The documentation says that the separator will be honored even inside
> of
> > > quotes.   ("The SQLite shell will always split fields on the separator
> > > character, no matter what comes before or after it. Quotes or
> backslashes
> > > won't escape them.).
> >
> >
> > I'm not able to find this statement anywhere in the SQLite documentation.
> > Can you send a link?
> >
> > --
>
>
> He's quoted from the wiki:
>  http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles
>

Yeah.  That wiki is really old.  Don't believe it

The CVS import for the command-line shell treats " as a quoting
characters.  All content between "..." is considered to be part of a single
field of the CVS, even if that content includes newline characters.

I think it will work to escape your isolated " characters by replacing them
with four double-quotes in a row:  



>
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

[Kevin Benson]

On Mon, Jul 23, 2012 at 12:05 PM, Richard Hipp  wrote:

> On Mon, Jul 23, 2012 at 8:37 AM, Hayes, Michael - IS <
> michael.ha...@exelisinc.com> wrote:
>
> >
> > The documentation says that the separator will be honored even inside of
> > quotes.   ("The SQLite shell will always split fields on the separator
> > character, no matter what comes before or after it. Quotes or backslashes
> > won't escape them.).
>
>
> I'm not able to find this statement anywhere in the SQLite documentation.
> Can you send a link?
>
> --


He's quoted from the wiki:
 http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling System.Data.SQLite with latest sqlite3.dll

[Bernhard Mogens Ege]

Well, I ended up trying with 1.0.82.0 as it is now in the repository, and
followed the build instructions (which need to be updated ) to generate new
dll's with 3.7.14 (as it turns out) included.

But I still cannot use the filename syntax (I can use, but it seems to be
ignored as I get an empty db everytime I open the connection): 

:memory:;cache=shared

Is shared memory database unsupported in System.Data.SQLite?

I am keeping a connection open to the database, but I cannot reach it from
other ":memory:;cache=shared" connections.

I realize this probably should be a developer question, but I hope someone
here has the answer before I attempt on the dev list.

-- 
Bernhard


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bernhard Mogens Ege
Sent: 23. juli 2012 12:05
To: sqlite-users@sqlite.org
Subject: [sqlite] Compiling System.Data.SQLite with latest sqlite3.dll

I am trying to figure out how to compile System.Data.SQLite and in the
process update sqlite from 3.7.12.1 to 3.7.13 as I need the shared cache
option for memory connections.

 

And when I write 'trying' I am having trouble figuring out where exactly
System.Data.SQLite picks up sqlite3.dll from. The source code doesn't
include it and the installer also doesn't include it, but it must be
somewhere.

 

Is it even safe to update System.Data.SQLite 1.0.81.0 with sqlite3.dll
3.7.13 and will the shared cache feature work?

 

Also, compiling System.Data.SQLite for .NET 4.0 results in some
warnings/errors. Are they safe to ignore?

 

Warning1   The parent file, 'SR.resx',
for file 'SR.Designer.cs' cannot be found in the project file.
System.Data.SQLite.2010

Warning2   Load of property 'OutputType'
failed.System.Data.SQLite.Module.2010

Warning3   The parent file, 'SR.resx',
for file 'SR.Designer.cs' cannot be found in the project file.
System.Data.SQLite.Module.2010

Warning4   Error 2005: Mapping
Association 'FK_InternationalOrders_OrderID_OrderID' is redundant: Its
referential integrity constraint provides sufficient information.  You can
safely delete the mapping information for this association.
C:\Users\bme\Documents\Visual Studio
2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
793  800  testlinq.2010

Warning5   Error 2005: Mapping
Association 'FK_OrderDetails_OrderID_OrderID' is redundant: Its referential
integrity constraint provides sufficient information.  You can safely delete
the mapping information for this association.
C:\Users\bme\Documents\Visual Studio
2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
801  809  testlinq.2010

Warning6   Error 2005: Mapping
Association 'FK_OrderDetails_ProductID_ProductID' is redundant: Its
referential integrity constraint provides sufficient information.  You can
safely delete the mapping information for this association.
C:\Users\bme\Documents\Visual Studio
2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
810  818  testlinq.2010

Warning7   The referenced component
'mscorlib' could not be found.

Warning8   The referenced component
'mscorlib' could not be found.

 

-- 

Bernhard

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tests fail with some glibc malloc/realloc/free-related warnings on Mageia Linux 3/Cauldron with glibc-2.16-2.mga3

[Richard Hipp]

On Mon, Jul 23, 2012 at 9:31 AM, Shlomi Fish  wrote:

>
> I first suspected svn was the culprit, so I rebuilt it, but it still
> happened.
> Then I tried build SQLite and running its tests and I got this (below). I
> should note that svn works fine after I type "unset MALLOC_CHECK_" in the
> console (don't know about the sqlite tests).
>
> What is the underlying problem and how can it be fixed?
>

Thanks for the report.

As best I can tell, this appears to be a bug in MALLOC_CHECK_ in that it
does not play well with malloc_usable_size().  There does not appear to be
anything wrong with SQLite in this respect, at least as not as far as I can
see.

We test SQLite using a variety of memory allocator that do things similar
to MALLOC_CHECK_.  (See http://www.sqlite.org/testing.html#valgrind for
example.)  In particular, SQLite tests run clean under valgrind.

If you edit the "config.h" file generated by the ./configure script and
remove the HAVE_MALLOC_USABLE_SIZE define, then the resulting SQLite will
not attempt to use malloc_usable_size() and it then appears to work fine
with MALLOC_CHECK_.  However, if you do this, then SQLite will increase the
size of every memory allocation by 8 bytes and store the allocation size in
those 8 bytes so that it can figure out the allocation size for itself when
it needs it, meaning that the code will run a little slower and use a
little more memory.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tests fail with some glibc malloc/realloc/free-related warnings on Mageia Linux 3/Cauldron with glibc-2.16-2.mga3

[Shlomi Fish]

Hi,

On Mon, 23 Jul 2012 12:09:09 -0400
Richard Hipp  wrote:

> On Mon, Jul 23, 2012 at 9:31 AM, Shlomi Fish  wrote:
> 
> >
> > I first suspected svn was the culprit, so I rebuilt it, but it still
> > happened.
> > Then I tried build SQLite and running its tests and I got this (below). I
> > should note that svn works fine after I type "unset MALLOC_CHECK_" in the
> > console (don't know about the sqlite tests).
> >
> > What is the underlying problem and how can it be fixed?
> >
> 
> Which version of SQLite are you using?  And where did you get it?
> 

I'm using sqlite-src-3071300.zip , which was contained in Mageia’s
sqlite3-3.7.13-1.mga3.src.rpm . The .zip is identical to this one:
http://www.sqlite.org/sqlite-src-3071300.zip

Regards,

Shlomi Fish

-- 
-
Shlomi Fish   http://www.shlomifish.org/
Interview with Ben Collins-Sussman - http://shlom.in/sussman

I learned a lot from my teachers, and from my friends more than my teachers,
and from my pupils the most. — Rabbi Hanina

Please reply to list if it's a mailing list post - http://shlom.in/reply .
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] single gdbconn object with multiple threads.

[Igor Tandetnik]

On 7/23/2012 10:30 AM, Durga D wrote:

Unless your threads do something else in parallel, you could just as well

do all SQLite work on a single thread

doubt: all are parallel threads. started at same time. one thread is
writing and others are reading at the same time by using same sqlite3*.


Not really. What really happens is, one of the threads does some work, 
while the other three are sitting waiting on a mutex.



In this scenario, all are parallel.


Again - every SQLite API call acquires a mutex associated with the 
connection. If that mutex is already taken, then the thread sits there 
waiting for it to be released. In other words, two SQLite calls on the 
same connection never execute at the same time - they are serialized on 
the mutex. Threads effectively take turns to make these calls.



my main doubt is: same sqlite3* is passing to 4 threads from the primary
thread.

Is it correct way to implement multiple readers and single writer?


There are no technical problems with this, if that's what you are 
asking. It would work. But since all the work is serialized, a single 
thread that does all the reading and writing would be just as fast, if 
not slightly faster. You are adding complexity but are not gaining any 
performance out of it.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

[Black, Michael (IS)]

Hmmmyour data import works just fine in 3.7.9...but you're correct that 
3.7.13 burps with that error message doing the same import.
So something changed

C:\sqlite>sqlite3 data.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> CREATE TABLE CONFIGURATION
   ...> (
   ...>   RECORD_IDNUMERIC   NOT NULL,
   ...>   TEXT  TEXT,
   ...>   NUMERIC_DATA  NUMERIC
   ...> );
sqlite>
sqlite> .import data.txt configuration
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE CONFIGURATION
(
  RECORD_IDNUMERIC   NOT NULL,
  TEXT  TEXT,
  NUMERIC_DATA  NUMERIC
);
INSERT INTO "CONFIGURATION" VALUES(1,'TEXT LINE 1 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(2,'TEXT LINE 2 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(3,'TEXT LINE 3 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(4,'TEXT LINE 4 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(5,'TEXT LINE 5 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(6,'TEXT LINE 6 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(7,'TEXT LINE 7 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(8,'TEXT LINE 8 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(9,'TEXT LINE 9 (72")','43721S');
COMMIT;

Works in 3.7.10 too.


Reuse the db file for 3.7.13
C:\sqlite>sqlite3 data.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE CONFIGURATION
(
  RECORD_IDNUMERIC   NOT NULL,
  TEXT  TEXT,
  NUMERIC_DATA  NUMERIC
);
INSERT INTO "CONFIGURATION" VALUES(1,'TEXT LINE 1 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(2,'TEXT LINE 2 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(3,'TEXT LINE 3 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(4,'TEXT LINE 4 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(5,'TEXT LINE 5 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(6,'TEXT LINE 6 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(7,'TEXT LINE 7 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(8,'TEXT LINE 8 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(9,'TEXT LINE 9 (72")','43721S');
COMMIT;
sqlite> .import data.txt configuration
Error: data.txt line 10: expected 3 columns of data but found 2
sqlite> .separator |
sqlite> .import data.txt configuration
Error: data.txt line 10: expected 3 columns of data but found 2

Even putting single quotes around field 2 & 3 gives the same error.


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Hayes, Michael - IS [michael.ha...@exelisinc.com]
Sent: Monday, July 23, 2012 7:37 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes 
When Importing Data


I've got input data that uses double quotes to mean inches.  So I have records 
with a single  double quote character in a record.  In some cases, SQLite is 
ignoring separators that are after the quotes.

The documentation says that the separator will be honored even inside of 
quotes.   ("The SQLite shell will always split fields on the separator 
character, no matter what comes before or after it. Quotes or backslashes won't 
escape them.).  However, the SQLite shell seems to be behaving differently when 
there is a single quote in the record.

I'm using "sqlite-shell-win32-x86-3071300.zip" and 
"sqlite-dll-win32-x64-3071300.zip" on Windows XP.

To reproduce, create this table and import the attached data file:

CREATE TABLE CONFIGURATION
(
  RECORD_IDNUMERIC   NOT NULL,
  TEXT  TEXT,
  NUMERIC_DATA  NUMERIC
);

If there are an odd number of quotes in the file, the error message is "Error: 
Separator_Ignored_Inside_Quotes.txt line 10: expected 3 columns of data but 
found 2".

If there are an even number of quotes in the file, every other record is 
imported and the data within the quotes is imported into the column including 
separators.  Edit the attached file to remove the last line and you'll see this 
behavior.

Thanks for looking it this bug and for SQLite.

Mike Hayes
Exelis Inc.,  Bowie, MD.




Email addresses of ITT Exelis employees have changed from itt.com to 
exelisinc.com. Please update your favorites and contact information to reflect 
these changes.

This e-mail and any files transmitted with it may be proprietary and are 
intended solely for the use of the individual or entity to whom they are 
addressed. If you have received this e-mail in 

Re: [sqlite] Restore SQLite DB from WAL

[Richard Hipp]

On Mon, Jul 23, 2012 at 12:10 PM,  wrote:

> Hi, everyone
>
> Is it possible to restore deleted rows or dropped tables provided the WAL
> and shm files are still intact and contain the data you want to restore?
>

The WAL file does not contain the old data, it contains the new data.  If
the WAL file has not been checkpointed, you should be able to simply delete
the WAL file and the old data will still be in the original database file.
A "checkpoint" operation is the act of moving the new database from the WAL
file back into the original database file, presumably overwriting the old
data (depending on your settings and other factors).

But, if the WAL file has been partially checkpointed, and that checkpoint
was interrupted by a crash or power failure, deleting the WAL file will
corrupt your database.  So you should probably run the experiment on a
backup.  :-)


>
> Example scenario where vast swathes of content have vanished from
> mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty
> Android SMS app:
>
> ls -al /data/data/com.android.providers.telephony/databases/mm*
> -rw-rw1 root root 60416 Jul 17 20:16 mmssms.db`
> -rw-rw1 radioradio32768 Jul 17 16:18 mmssms.db-shm
> -rw-rw1 radioradio   628832 Jun 30 19:23 mmssms.db-wal
>
> TIA,
>
> Mandy
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Restore SQLite DB from WAL

[gsm-ginger]

Hi, everyone

Is it possible to restore deleted rows or dropped tables provided the WAL and 
shm files are still intact and contain the data you want to restore?

Example scenario where vast swathes of content have vanished from mmssms.db 
thanks to an unintentional mass-DELETE FROM through the naughty Android SMS app:

ls -al /data/data/com.android.providers.telephony/databases/mm*
-rw-rw    1 root     root         60416 Jul 17 20:16 mmssms.db`
-rw-rw    1 radio    radio        32768 Jul 17 16:18 mmssms.db-shm
-rw-rw    1 radio    radio       628832 Jun 30 19:23 mmssms.db-wal

TIA,

Mandy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

[Richard Hipp]

On Mon, Jul 23, 2012 at 8:37 AM, Hayes, Michael - IS <
michael.ha...@exelisinc.com> wrote:

>
> The documentation says that the separator will be honored even inside of
> quotes.   ("The SQLite shell will always split fields on the separator
> character, no matter what comes before or after it. Quotes or backslashes
> won't escape them.).


I'm not able to find this statement anywhere in the SQLite documentation.
Can you send a link?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tests fail with some glibc malloc/realloc/free-related warnings on Mageia Linux 3/Cauldron with glibc-2.16-2.mga3

[Shlomi Fish]

Hi all,

I'm using svn and sqlite3 on Mageia Linux 3/Cauldron with glibc-2.16-2.mga3.

today I noticed that svn checkout gave me the following by default:

<<<
shlomif@telaviv1:~$ svn co http://rpmlint.zarb.org/svn/trunk*** glibc detected 
*** svn: free(): invalid pointer: 0x00c17820 ***
*** glibc detected *** svn: realloc(): invalid pointer: 0x00c18740 ***
*** glibc detected *** svn: free(): invalid pointer: 0x00c1e260 ***
svn: E200030: SQL logic error or missing database, executing statement 'CREATE 
TABLE REPOSITORY (   id INTEGER PRIMARY KEY AUTOINCREMENT,   root  TEXT UNIQUE 
NOT NULL,   uuid  TEXT NOT NULL   ); CREATE INDEX I_UUID ON REPOSITORY (uuid); 
CREATE INDEX I_ROOT ON REPOSITORY (root); CREATE TABLE WCROOT (   id  INTEGER 
PRIMARY KEY AUTOINCREMENT,   local_abspath  TEXT UNIQUE   ); CREATE UNIQUE 
INDEX I_LOCAL_ABSPATH ON WCROOT (local_abspath); CREATE TABLE PRISTINE (   
checksum  TEXT NOT NULL PRIMARY KEY,   compression  INTEGER,   size  INTEGER 
NOT NULL,   refcount  INTEGER NOT NULL,   md5_checksum  TEXT NOT NULL   ); 
CREATE TABLE ACTUAL_NODE (   wc_id  INTEGER NOT NULL REFERENCES WCROOT (id),   
local_relpath  TEXT NOT NULL,   parent_relpath  TEXT,   properties  BLOB,   
conflict_old  TEXT,   conflict_new  TEXT,   conflict_working  TEXT,   
prop_reject  TEXT,   changelist  TEXT,   text_mod  TEXT,   tree_conflict_data  
TEXT,   conflict_data  BLOB,   older_checksum  TEXT REFERENCES PRISTI
 NE (checksum),   left_checksum  TEXT REFERENCES PRISTINE (checksum),   
right_checksum  TEXT REFERENCES PRISTINE (checksum),   PRIMARY KEY (wc_id, 
local_relpath)   ); CREATE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, 
parent_relpath); CREATE INDEX I_ACTUAL_CHANGELIST ON ACTUAL_NODE (changelist); 
CREATE TABLE LOCK (   repos_id  INTEGER NOT NULL REFERENCES REPOSITORY (id),   
repos_relpath  TEXT NOT NULL,   lock_token  TEXT NOT NULL,   lock_owner  TEXT,  
 lock_comment  TEXT,   lock_date  INTEGER,   PRIMARY KEY (repos_id, 
repos_relpath)   ); CREATE TABLE WORK_QUEUE (   id  INTEGER PRIMARY KEY 
AUTOINCREMENT,   work  BLOB NOT NULL   ); CREATE TABLE WC_LOCK (   wc_id  
INTEGER NOT NULL  REFERENCES WCROOT (id),   local_dir_relpath  TEXT NOT NULL,   
locked_levels  INTEGER NOT NULL DEFAULT -1,   PRIMARY KEY (wc_id, 
local_dir_relpath)  ); PRAGMA user_version = 29; '
shlomif@telaviv1:~$ 
>>>

I first suspected svn was the culprit, so I rebuilt it, but it still happened.
Then I tried build SQLite and running its tests and I got this (below). I
should note that svn works fine after I type "unset MALLOC_CHECK_" in the
console (don't know about the sqlite tests).

What is the underlying problem and how can it be fixed?

Regards,

Shlomi Fish

[QUOTE]
shlomif@telaviv1:~$ cd ~/progs/Rpms/SOURCES/bld/
shlomif@telaviv1:~/progs/Rpms/SOURCES/bld$ make test
./libtool --mode=link gcc   -g -O2 -DSQLITE_OS_UNIX=1 -I. -I../sqlite/src 
-I../sqlite/ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG  
-I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 
-DSQLITE_NO_SYNC=1 -DSQLITE_TEMP_STORE=1 -DTCLSH=1 -DSQLITE_TEST=1 
-DSQLITE_CRASH_TEST=1 -DSQLITE_SERVER=1 -DSQLITE_PRIVATE="" -DSQLITE_CORE  
-DBUILD_sqlite \
-o testfixture ../sqlite/src/test1.c ../sqlite/src/test2.c 
../sqlite/src/test3.c ../sqlite/src/test4.c ../sqlite/src/test5.c 
../sqlite/src/test6.c ../sqlite/src/test7.c ../sqlite/src/test8.c 
../sqlite/src/test9.c ../sqlite/src/test_autoext.c ../sqlite/src/test_async.c 
../sqlite/src/test_backup.c ../sqlite/src/test_btree.c 
../sqlite/src/test_config.c ../sqlite/src/test_demovfs.c 
../sqlite/src/test_devsym.c ../sqlite/src/test_func.c 
../sqlite/src/test_fuzzer.c ../sqlite/src/test_hexio.c 
../sqlite/src/test_init.c ../sqlite/src/test_intarray.c 
../sqlite/src/test_journal.c ../sqlite/src/test_malloc.c 
../sqlite/src/test_multiplex.c ../sqlite/src/test_mutex.c 
../sqlite/src/test_onefile.c ../sqlite/src/test_osinst.c 
../sqlite/src/test_pcache.c ../sqlite/src/test_quota.c 
../sqlite/src/test_rtree.c ../sqlite/src/test_schema.c 
../sqlite/src/test_server.c ../sqlite/src/test_superlock.c 
../sqlite/src/test_syscall.c ../sqlite/src/test_stat.c 
../sqlite/src/test_tclvar.c ../sqlite/sr
 c/test_thread.c ../sqlite/src/test_vfs.c ../sqlite/src/test_wholenumber.c 
../sqlite/src/test_wsd.c ../sqlite/ext/fts3/fts3_term.c 
../sqlite/ext/fts3/fts3_test.c  ../sqlite/src/tclsqlite.c sqlite3.c 
-L/usr/lib64 -ltcl8.5 -ldl  -lieee -lm -lpthread 
libtool: link: gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I../sqlite/src 
-I../sqlite/ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG 
-I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 
-DSQLITE_NO_SYNC=1 -DSQLITE_TEMP_STORE=1 -DTCLSH=1 -DSQLITE_TEST=1 
-DSQLITE_CRASH_TEST=1 -DSQLITE_SERVER=1 -DSQLITE_PRIVATE= -DSQLITE_CORE 
-DBUILD_sqlite -o testfixture ../sqlite/src/test1.c ../sqlite/src/test2.c 
../sqlite/src/test3.c ../sqlite/src/test4.c ../sqlite/src/test5.c 
../sqlite/src/test6.c 

[sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

[Hayes, Michael - IS]

I've got input data that uses double quotes to mean inches.  So I have records 
with a single  double quote character in a record.  In some cases, SQLite is 
ignoring separators that are after the quotes.

The documentation says that the separator will be honored even inside of 
quotes.   ("The SQLite shell will always split fields on the separator 
character, no matter what comes before or after it. Quotes or backslashes won't 
escape them.).  However, the SQLite shell seems to be behaving differently when 
there is a single quote in the record.

I'm using "sqlite-shell-win32-x86-3071300.zip" and 
"sqlite-dll-win32-x64-3071300.zip" on Windows XP.

To reproduce, create this table and import the attached data file:

CREATE TABLE CONFIGURATION
(
  RECORD_IDNUMERIC   NOT NULL,
  TEXT  TEXT,
  NUMERIC_DATA  NUMERIC
);

If there are an odd number of quotes in the file, the error message is "Error: 
Separator_Ignored_Inside_Quotes.txt line 10: expected 3 columns of data but 
found 2".

If there are an even number of quotes in the file, every other record is 
imported and the data within the quotes is imported into the column including 
separators.  Edit the attached file to remove the last line and you'll see this 
behavior.

Thanks for looking it this bug and for SQLite.

Mike Hayes
Exelis Inc.,  Bowie, MD.




Email addresses of ITT Exelis employees have changed from itt.com to 
exelisinc.com. Please update your favorites and contact information to reflect 
these changes.

This e-mail and any files transmitted with it may be proprietary and are 
intended solely for the use of the individual or entity to whom they are 
addressed. If you have received this e-mail in error please notify the sender. 
Please note that any views or opinions presented in this e-mail are solely 
those of the author and do not necessarily represent those of Exelis Inc. The 
recipient should check this e-mail and any attachments for the presence of 
viruses. Exelis Inc. accepts no liability for any damage caused by any virus 
transmitted by this e-mail.
1|TEXT LINE 1 (72")|43721S
2|TEXT LINE 2 (72")|43721S
3|TEXT LINE 3 (72")|43721S
4|TEXT LINE 4 (72")|43721S
5|TEXT LINE 5 (72")|43721S
6|TEXT LINE 6 (72")|43721S
7|TEXT LINE 7 (72")|43721S
8|TEXT LINE 8 (72")|43721S
9|TEXT LINE 9 (72")|43721S
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Email Policy Violation - Free speech. Was: A matter of UNIQUEness

[Richard Hipp]

The irony in the automated response below is beyond words.  :-)

On Mon, Jul 23, 2012 at 11:14 AM,  wrote:

> You attempted to send a message "[sqlite] Free speech. Was: A matter of
> UNIQUEness" that contained objectionable words which violates Persistent
> System's Email Policy.
> Your email has been quarantined and you are requested to contact
> it_bes_...@persistent.co.in in case this is a genuine business email. The
> quarantined email will be deleted automatically after 7 days.
>

Presumably, the message was censored because of the elided word in the
following sentence:

"Unconstructive criticism and gratuitous references to body wastes and/or
acts of  (whether by taboo words, figures of speech, acronyms, puns, or
innuendo) are not tolerated and are grounds for banishment."

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Free speech. Was: A matter of UNIQUEness

[Richard Hipp]

On Mon, Jul 23, 2012 at 9:35 AM, Arbol One  wrote:

>
> Freedom of speech does not translate to freedom of insulting
>

The statement above is a common sophistry that should not go unchallenged:
The whole point of "freedom of speech" is so that you can say things that
are controversial and uncomfortable (read: "insulting") to and of those in
power.  Even the most oppressive tyrants are happy to give their subjects
the "freedom" to speak banal, pedestrian, and uncontroversial words.  Let
us not dumb-down freedom of speech into the freedom to chit-chat.

In the US, "freedom of speech" means the "freedom to speak the truth", with
a broad interpretation of the meaning of "truth".  Any statement of opinion
is automatically considered truth.  Willful deception that causes harm,
such as "shouting 'fire' in a crowded theater", is an offense.  Note,
though, that there is no prohibition against yelling "fire" in a crowded
theater if the theater really is on fire.  In the US, truth is an absolute
defense against libel and slander, and the burden is on the plaintiff to
prove that the defendant knowingly spoke (or wrote) an untruth.  Whether or
not the speech is "insulting" does not really come into play.

Please remember, however, that having the freedom to do something does not
imply it is always wise to act upon that freedom.  Prudence dictates that
one should choose ones words carefully.  Gentle persuasion usually works
better than insults.  Furthermore, words are often a good indicator of the
speakers soul.   If you see someone (or more importantly, if you see
yourself) uttering vile and ugly words, insults, and oaths, that is usually
a good sign that the speaker is in need of introspection and repentance.

All that said, it has been my experience that when someone in authority
tries to regulate speech so as to avoid insulting this or that interest
group, the goal is usually not about seeking truth and wisdom but rather to
shutdown debate, sniffle dissent, and impose hegemony.

Finally, please note there is no freedom of speech on this mailing list.
The standard here is that all remarks must be beautiful and edifying.
Unconstructive criticism and gratuitous references to body wastes and/or
acts of rape (whether by taboo words, figures of speech, acronyms, puns, or
innuendo) are not tolerated and are grounds for banishment.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] single gdbconn object with multiple threads.

[Simon Slavin]

On 23 Jul 2012, at 3:30pm, Durga D  wrote:

> my main doubt is: same sqlite3* is passing to 4 threads from the primary
> thread.

My understanding is that this does not happen.  There is no concept of 'server' 
and 'client' in the SQLite API, and it does no thread or process handling of 
its own.  If you make a function call in a particular thread of a particular 
process, the function runs in that thread.

A note based on your original post: the 'WAL' property of a database belongs 
with the database and is stored with the database.  You don't need to specify 
WAL mode each time you open the database, all connections with it will 
automatically know to use WAL mode until you use a PRAGMA command to change the 
mode again.  I know this doesn't help solve problem but it might help you 
understand what's going on.

I can't give you a definitive answer to your question but as far as I can see 
you're doing everything right, and should finish writing your program with 
those assumptions.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] single gdbconn object with multiple threads.

[Durga D]

Hi Igor,

Sorry.

>>Unless your threads do something else in parallel, you could just as well
do all SQLite work on a single thread

doubt: all are parallel threads. started at same time. one thread is
writing and others are reading at the same time by using same sqlite3*.

I passed same sqlite3* as an argument to 4 posix threads. Among 4, three
threads are reading (selct name from table where id < 10*index) from
database. Fourth thread is writer thread, which is inserting into database.

In this scenario, all are parallel. three threads are accessing database
for read and another one for write.

It's working fine in my sample application with WAL mode only.

my main doubt is: same sqlite3* is passing to 4 threads from the primary
thread.

Is it correct way to implement multiple readers and single writer?

please ignore intel tbb.

Regards,

On Mon, Jul 23, 2012 at 5:17 PM, Igor Tandetnik  wrote:

> Durga D  wrote:
> >   I have used same gdbconn for all the threads. In my sample application,
> > it working perfectly in wal mode.
> >
> >   Is it correct approach?
>
> Approach to what? You've never stated the problem you are trying to solve.
>
> With this setup, you don't get any concurrency out of your four threads.
> All SQLite API calls are serialized on one mutex associated with the
> connection. Unless your threads do something else in parallel, you could
> just as well do all SQLite work on a single thread.
>
> >   If yes, will tbb improves the performance for reader_threads?
>
> What's "tbb"?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A matter of UNIQUEness

[Igor Tandetnik]

Arbol One  wrote:
> When using the statement 'UNIQUE INTEGER val',  does SQLite3 automatically
> create a unique integer value for the transaction  or would it only
> ascertain the uniqueness within that column?
>
> i.e.:
> 
> Glib::ustring sql_param_tblName = "CREATE TABLE name(n_id INTEGER PRIMARY
> KEY, title TEXT, fname TEXT, mname TEXT, lname TEXT)";

Generally, UNIQUE or PRIMARY KEY constraint merely enforces uniqueness, it 
doesn't automatically generate a unique value for you.

However, in SQLite, INTEGER PRIMARY KEY column (when spelled exactly this way) 
is special:

http://sqlite.org/lang_createtable.html#rowid

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A matter of UNIQUEness

[Cory Nelson]

On Mon, Jul 23, 2012 at 8:35 AM, Arbol One  wrote:
> When using the statement 'UNIQUE INTEGER val',  does SQLite3 automatically
> create a unique integer value for the transaction  or would it only
> ascertain the uniqueness within that column?
>
>
>
> i.e.:
>
> Glib::ustring sql_param_tblName = "CREATE TABLE name(n_id INTEGER PRIMARY
> KEY, title TEXT, fname TEXT, mname TEXT, lname TEXT)";
>
> [I understand that UNIQUE  is implied in PRIMARY KEY?]
>

unique creates a unique index -- there must not be any duplicates in
those columns used in the key. it doesn't do anything to affect
transactions. primary keys are unique, yes.

-- 
Cory Nelson
http://int64.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A matter of UNIQUEness

[Arbol One]

When using the statement 'UNIQUE INTEGER val',  does SQLite3 automatically
create a unique integer value for the transaction  or would it only
ascertain the uniqueness within that column?

 

i.e.:

Glib::ustring sql_param_tblName = "CREATE TABLE name(n_id INTEGER PRIMARY
KEY, title TEXT, fname TEXT, mname TEXT, lname TEXT)";

[I understand that UNIQUE  is implied in PRIMARY KEY?]

 

Freedom of speech does not translate to freedom of insulting

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] single gdbconn object with multiple threads.

[Igor Tandetnik]

Durga D  wrote:
>   I have used same gdbconn for all the threads. In my sample application,
> it working perfectly in wal mode.
> 
>   Is it correct approach?

Approach to what? You've never stated the problem you are trying to solve.

With this setup, you don't get any concurrency out of your four threads. All 
SQLite API calls are serialized on one mutex associated with the connection. 
Unless your threads do something else in parallel, you could just as well do 
all SQLite work on a single thread.

>   If yes, will tbb improves the performance for reader_threads?

What's "tbb"?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling System.Data.SQLite with latest sqlite3.dll

[Bernhard Mogens Ege]

I am trying to figure out how to compile System.Data.SQLite and in the
process update sqlite from 3.7.12.1 to 3.7.13 as I need the shared cache
option for memory connections.

 

And when I write 'trying' I am having trouble figuring out where exactly
System.Data.SQLite picks up sqlite3.dll from. The source code doesn't
include it and the installer also doesn't include it, but it must be
somewhere.

 

Is it even safe to update System.Data.SQLite 1.0.81.0 with sqlite3.dll
3.7.13 and will the shared cache feature work?

 

Also, compiling System.Data.SQLite for .NET 4.0 results in some
warnings/errors. Are they safe to ignore?

 

Warning1   The parent file, 'SR.resx',
for file 'SR.Designer.cs' cannot be found in the project file.
System.Data.SQLite.2010

Warning2   Load of property 'OutputType'
failed.System.Data.SQLite.Module.2010

Warning3   The parent file, 'SR.resx',
for file 'SR.Designer.cs' cannot be found in the project file.
System.Data.SQLite.Module.2010

Warning4   Error 2005: Mapping
Association 'FK_InternationalOrders_OrderID_OrderID' is redundant: Its
referential integrity constraint provides sufficient information.  You can
safely delete the mapping information for this association.
C:\Users\bme\Documents\Visual Studio
2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
793  800  testlinq.2010

Warning5   Error 2005: Mapping
Association 'FK_OrderDetails_OrderID_OrderID' is redundant: Its referential
integrity constraint provides sufficient information.  You can safely delete
the mapping information for this association.
C:\Users\bme\Documents\Visual Studio
2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
801  809  testlinq.2010

Warning6   Error 2005: Mapping
Association 'FK_OrderDetails_ProductID_ProductID' is redundant: Its
referential integrity constraint provides sufficient information.  You can
safely delete the mapping information for this association.
C:\Users\bme\Documents\Visual Studio
2010\Projects\sqlite-netFx-source-1.0.81.0\testlinq\NorthwindModel2010.edmx
810  818  testlinq.2010

Warning7   The referenced component
'mscorlib' could not be found.

Warning8   The referenced component
'mscorlib' could not be found.

 

-- 

Bernhard

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite, Linq, multithread and transaction usage

[Bernhard Mogens Ege]

Hmm the documentation doesn't explain how I should proceed with an entity
object. 

Is it enough to close the connection in the entity object and let the
framework open/close the connection for me?

In constructor:
MyEntities myEntities = new MyEntities(connectionString); 

In each thread:
myEntities.Connection.Open();
Do stuff, including transactions...
myEntities.Connection.Close();

Or should I create/dispose whole entity objects instead (and the connection
with it)?

In each thread:
MyEntities myEntities = new MyEntities(connectionString);
Do stuff, including transactions...
myEntities.Dispose();

I guess the latter as it would probably get its own connection object, but I
am not quite sure here (ADO.NET might do connection sharing).

-- 
Bernhard

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: 20. juli 2012 23:00
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite, Linq, multithread and transaction
usage


The basic thread-safety rules of System.Data.SQLite are documented here:

https://system.data.sqlite.org/index.html/artifact?ci=trunk=Doc/Ext
ra/limitations.html

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users