Re: [sqlite] Re: SQLite3 Concurrency
How about the case of a simple BEGIN which sets a deferred lock so
that the busy will occur when that lock is promoted later in the the
transaction?
As I understand it the deferred lock capability is conducive to
better concurrency, but does have other effects requiring that
provision be made to intercept a BUSY in the body of the transaction.
Ok. But this situation are more preferable for me that "BEGIN
EXCLUSIVE".
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement:
" << errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
Your BEGIN tries to set a RESERVED lock, but only one RESERVED lock is
permitted so your BEGIN may fail.
Not true.
BTW:
"The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is
optional) is used to take SQLite out of autocommit mode. Note that the
BEGIN command does not acquire any locks on the database. After a BEGIN
command, a SHARED lock will be acquired when the first SELECT statement
is executed. A RESERVED lock will be acquired when the first INSERT,
UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired
until either the memory cache fills up and must be spilled to disk or
until the transaction commits. In this way, the system delays blocking
read access to the file file until the last possible moment."
BTW, I suggest that you do not use sqlite3_exec on new programs and
use sqlite3_prepare and sqlite3_step. It is the preferred interface.
Sqlite3_exec is deprecated and exists for legacy applications.
sqlite3_exec not deprecated but preferable to use. But I don't see any
advantages in PREPARE/STEP when SQL statement so simple and will not
used anymore with another variabled params...
--
Regards,
Igor Mironchick,
Intervale ©
#ICQ 492-597-570
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Re: SQLite3 Concurrency
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
What's the point of running a SELECT statement without actually
reading the returned rows?
It's simple example, not real code. Only show strategy.
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement:
" << errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
Why do you want to keep running the same statement over and over?
Since I guarante that there is no errors in SQL syntax something when
ret != SQLITE_OK mean that there is concurrency problem and I need to
wait unloking database. For that reason I keep the same statement over
and over.
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
I'm not sure I understand what your point is here. You have a reader
that hogs the database forever in a long-running transaction. Of
course writers are locked out.
No. Reader will not hog database because if in this example ret !=
SQLITE_OK then writer have already lock database for write and we need
to wait for unlocking. And when I wrote: "More dangerous when "COMMIT"
after "INSERT" return SQLITE_BUSY" I mean that if this wouldn't be
checked then probably we lost ours data.
P.S. There is no reasons for two threads lock the same database and will
run while cycle at the same time.
--
Regards,
Igor Mironchick,
Intervale ©
#ICQ 492-597-570
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite3 Concurrency
How about the case of a simple BEGIN which sets a deferred lock so
that the busy will occur when that lock is promoted later in the the
transaction?
As I understand it the deferred lock capability is conducive to better
concurrency, but does have other effects requiring that provision be
made to intercept a BUSY in the body of the transaction.
Ok. But this situation are more preferable for me that "BEGIN EXCLUSIVE".
What about this:
char * errors;
// I guarante that here no errors in SQL syntaxis.
char * sql = "SELECT * FROM data";
sqlite3_exec( db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( db, sql, 0, 0, );
while( ret != SQLITE_OK )
{
std::cerr << "There is some errors while executing SQL statement: "
<< errors << std::endl;
sqlite3_free( errors );
ret = sqlite3_exec( db, sql, 0, 0, );
}
sqlite3_exec( db, "END", 0, 0, 0 );
More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!!
--
Regards,
Igor Mironchick,
Intervale ©
#ICQ 492-597-570
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] SQLite3 Concurrency
Hi, guys. I have some questions about multithreading in SQLite: a) If one thread "BEGIN" and after that another thread try to "BEGIN". What will with the second thread? How I understand there is nothing wrong. Is it right? b) The same one only when second thread "BEGIN" after first thread "BEGIN" and "INSERT" but before "COMMIT"? Here, how I understand, second thread will get "SQLITE_BUSY" and have nothing to read?!?! P.S. First thread is a writer and a reader and the second one is a reader only. -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] INDEXES and PRIMARY KEY
Hi, guys. Can anybody explain me for what PRIMARY KEY needed? For example, is there some pluses using PRIMARY KEY insted of a simple INTEGER column (when I connect two tables by values of this column in SELECT queries)? And is PRIMARY KEY auto increment his value when inserting new value in a table? I mean can I use INTEGER PRIMARY KEY and set him by himself? And for what a INDEX? P.S. Sorry for so newbies questions :) -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ATTACH/DETACH DATABASE
Hi, guys. If I attach database "db_attached" into "db" and then close "db". After opening "db" does I need to attach "db_attached" again or this attachment was stored??? -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
Hi, guys. Dennis Cote wrote: Stephen, You should look into using the newer prepare/step API functions instead of the callback mechanism. It will make your code clearer, and will probably execute faster as well. I don't think so. Prepare/step mechanism can invoke productivity of "INSERT" or "UPDATE", or something like this, when you have much rows to insert/update. But when you need to do "SELECT" sqlite3_exec - the best way in my opinion. Look at the code that you represent - this is the answer on all questions. Want you or don't, but you must allocate memory for result and call function in C/C++ is very cheaply operation. You sad: "It will make your code clearer". I don't agree. Look... char * sql = sqlite3_mprintf( "SELECT * FROM %s", table_name ); int ret = sqlite3_exec( db, sql, callback, NULL, NULL ); sqlite3_free( sql ); What can be clearer than it??? But when you store many information in DB than transactions/prepare/step are more preferable for productivity. But it does not make code clearer, inside out... The new API is used to implement the current version of sqlite3_exec that uses the callback mechanism so you can look at that code to see how the new API is used. The following excerpt is from the file legacy.c in the sqlite source. It shows how sqlite uses the new API functions to build the arrays of strings it passes to the callback function. By using the new API functions directly you can avoid the overhead of converting all the database fields into string and building these arrays, only to have your callback function iterate over the string arrays and convert the values back into other types (for non string fields anyway) and then stuff them into vectors. You can extract the fields and store them directly into the vectors you want. And here I see this magic word "callback" :) -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
I think problem is in use char * as parameter in push function. For that
reason I use std::string always.
Look, in callback2 SQLite pass char ** - array of char*. But when your
code leave callback2 SQLite might errase that array and then all char*
will garbage. But when in push method you will pass const std::string &
-- char* will transform into std::string and you will not lose any data.
Or event you can obviosly call ctor of std::string like
q->push( atoi(argv[0] ),atoi(argv[1] ),atoi(argv[2] ), std::string(
argv[3] ), std::string( argv[4] ) );
and push method then will looks like this:
void QuestionDB::push( int b, int c, int v, const std::string & q, const
std::string & a )
{
...
}
P.S. Try don't use char* in C++ code. Use std::string instead. And when
you need char* you can transform std::string into char* with c_str()
method of std::string.
Stephen Sutherland wrote:
Igor I tried your solution and can't get it working .
Here is my code.
The STRANGE problem that I am having is that when it adds to the vector at position 1, the vector contains the object.
But when it adds to vector at position 2 using push_back - for some reason the contents at vector 1 is garbage.
then when it adds to vector at position 3 using push_back - for some reason position 3 is garabage ?
the errors in this situation doesn't seem to follow much logic ? but it's consistent.
for example if i run this code and it uses push 4 times, position 1 and 3 might have garbase while position 2 will have the same contents as position 4 ? ?
this is some weird stuff i haven't seen before.
I'm wondering if the problem is due to the scope of either my QuestionDBStruct or my vector .
=
static int callback2(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for(i=0; i<argc; i++)
{
printf("%i. %s = %s\n", i, azColName[i], argv[i] ? argv[i] : "NULL");
}
QuestionDB* q = (QuestionDB*)NotUsed;
q->push(c );
return 0;
};
QuestionDB::QuestionDB()
{
};
void QuestionDB::push(int b, int c, int v, char* q, char* a)
{
QuestionDBStruct qbs;
qbs.bible_book =1;
qbs.bible_chapter =2;
qbs.bible_verse=3;
qbs.bible_answer ="test";
qbs.bible_question =q;
printf("\n** push called \n");
vecQuestions.push_back(qbs);
for(int x = 0 ; x < vecQuestions.size(); x++)
printf("\nvecQuestion[%i] = %s \n", x,
((QuestionDBStruct)vecQuestions[x]).bible_question );
printf("\n***\n");
}; ========
Igor Mironchick <[EMAIL PROTECTED]> wrote:
If I understand you right then try it:
static int add_value( void *st, int, char **value, char ** )
{
storage_t * storage = (storage_t*) st;
st->push( value[ 0 ] );
return SQLITE_OK;
};
class storage_t {
public:
storage_t()
: m_db( 0 )
{
sqlite3_open( "your_database.db", _db );
};
virtual ~storage_t()
{
sqlite3_close( m_db );
};
void push( const std::string & v )
{
m_buff.push_back( v );
}
void read_table()
{
sqlite3_exec( m_db, "SELECT * FROM some_table",
add_value, this, NULL );
}
private:
sqlite3 * m_db;
std::vector< std::string > m_buff;
};
This is very simple example, but it can help you I think.
Stephen Sutherland wrote:
Hi
I am using the 'quick start' C-styled code for sqlite3
http://www.sqlite.org/quickstart.html
I think I'm running into a problem trying to put it in classes to make it somewhat object oriented. So I'm asking for help about how to make it object-oriented - or to confirm whether what I'm doing is object oriented.
Here is the code:
[code]
//callback function
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
};
// this callback is referenced here.
void MyClass::executeSQLStatement()
{
rc = sqlite3_exec(db, "select * from table1" , callback, 0, );
};
[/code]
However I am trying to add a vector in the callback function to store the
results. When I put the vector in it seems I am forced to do something like
this:
[code]
vector vecX;
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for(i=0; i> printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
vecX.push_back(argv[3]);
printf("\n");
return 0;
};
[/code]
Now this doesn't seem object oriented ?
Nor do I understand how I woul
Re: [sqlite] Object Oriented Question About Adding a vector inside the callback function
If I understand you right then try it:
static int add_value( void *st, int, char **value, char ** )
{
storage_t * storage = (storage_t*) st;
st->push( value[ 0 ] );
return SQLITE_OK;
};
class storage_t {
public:
storage_t()
:m_db( 0 )
{
sqlite3_open( "your_database.db", _db );
};
virtual ~storage_t()
{
sqlite3_close( m_db );
};
void push( const std::string & v )
{
m_buff.push_back( v );
}
void read_table()
{
sqlite3_exec( m_db, "SELECT * FROM some_table",
add_value, this, NULL );
}
private:
sqlite3 * m_db;
std::vector< std::string > m_buff;
};
This is very simple example, but it can help you I think.
Stephen Sutherland wrote:
Hi
I am using the 'quick start' C-styled code for sqlite3 http://www.sqlite.org/quickstart.html
I think I'm running into a problem trying to put it in classes to make it somewhat object oriented. So I'm asking for help about how to make it object-oriented - or to confirm whether what I'm doing is object oriented.
Here is the code:
[code]
//callback function
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
};
// this callback is referenced here.
void MyClass::executeSQLStatement()
{
rc = sqlite3_exec(db, "select * from table1" , callback, 0, );
};
[/code]
However I am trying to add a vector in the callback function to store the results. When I put the vector in it seems I am forced to do something like this:
[code]
vector vecX;
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
vecX.push_back(argv[3]);
printf("\n");
return 0;
};
[/code]
Now this doesn't seem object oriented ?
Nor do I understand how I would access this vector from other classes ?
And I don't know how this vector which I created can be considered part of the class ? it seems to me to only have page scope.
Any advice on how to make my vector object oriented or accessible by other classes ?
Thanks in Advance
Stephen
-----
Pinpoint customers who are looking for what you sell.
--
Regards,
Igor Mironchick,
Intervale ©
#ICQ 492-597-570
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Read Only DB
Hi. How can I open DB in read-only mode? -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [SQLite improve productivity]
Thx, very helpfull reply. One more question: is it need to do "END" after "BEGIN" or enought "COMMIT"? By the way, some edition of sql query improve productivity a little bit too: // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( '%q', '%q', '%q', '%q' )", m_sources_map[ it->m_source ].m_sid.c_str(), sec.c_str(), usec.c_str(), it->m_value.c_str() ); was changed with: // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( %d, %d, %d, '%s' )", m_sources_map[ it->m_source ].m_sid, it->m_time.sec(), it->m_time.usec(), it->m_value.c_str() ); where m_sources_map[ it->m_source ].m_sid is long now. A common issue of high latency transactions. SQLite has a high per-transaction overhead, which can be amortized across multiple INSERTs or UPDATEs to improve the average INSERT rate. You are doing a single INSERT per transaction, so wrap multiple INSERTs inside a single "BEGIN" ... "END" transaction. See: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations Christian -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [SQLite improve productivity]
Thx, very helpfull reply. One more question: is it need to do "END" after "BEGIN" or enought "COMMIT"? By the way, some edition of sql query improve productivity a little bit too: // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( '%q', '%q', '%q', '%q' )", m_sources_map[ it->m_source ].m_sid.c_str(), sec.c_str(), usec.c_str(), it->m_value.c_str() ); was changed with: // Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER, TEXT) char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( %d, %d, %d, '%s' )", m_sources_map[ it->m_source ].m_sid, it->m_time.sec(), it->m_time.usec(), it->m_value.c_str() ); A common issue of high latency transactions. SQLite has a high per-transaction overhead, which can be amortized across multiple INSERTs or UPDATEs to improve the average INSERT rate. You are doing a single INSERT per transaction, so wrap multiple INSERTs inside a single "BEGIN" ... "END" transaction. See: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations Christian -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] [SQLite improve productivity]
Hi.
How can I improve productivity of this code:
a_messenger_t::data_buff_t --> std::deque< some_struct >
//
char * errors = 0;
for( a_messenger_t::data_buff_t::const_iterator it = msg.m_buff.begin(),
last = msg.m_buff.end(); it != last; ++it )
{
// Converting long to std::string...
std::string sec( itos( it->m_time.sec() ) );
std::string usec( itos( it->m_time.usec() ) );
// Columns in 'data' table defined like (INTEGER, INTEGER,
INTEGER, TEXT)
char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( '%q',
'%q', '%q', '%q' )",
m_sources_map[ it->m_source ].m_sid.c_str(),
sec.c_str(), usec.c_str(), it->m_value.c_str() );
// m_db --> sqlite3*
int ret = sqlite3_exec( m_db, sql, 0, 0, );
if( ret != SQLITE_OK )
{
ACE_DEBUG( ( LM_INFO, ACE_TEXT( "[%D] %M -- %C\n" ), errors ) );
sqlite3_free( errors );
}
sqlite3_free( sql );
}
Any idea? This method are so slow - about 1 kB per second new data in my DB.
--
Regards,
Igor Mironchick,
Intervale ©
#ICQ 492-597-570
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
