Re: [sqlite] How to restore from backup or alter db in multi-user envionment?

2012-12-01 Thread Simon Slavin 

On 1 Dec 2012, at 3:22pm, dochsm  wrote:

> My database is shared by many users on a network. As part of auto-updating my
> client applications via the web it might be necessary to alter some table
> structures / view sql or put extra data into tables inside the shared sqlite
> database. Problem is that the db might be in use at the time. 
> 
> Qn1) Is it safe simply to wrap up everthing I want to do in a transaction
> and then commit the transaction?

As far as SQLite is concerned, yes.  SQLite will lock the schema against 
changes while SELECT UPDATE INSERT commands are executing, and will lock the 
database against those commands while the database schema is being changed.  
The effect is that locking is used in the same way it would be if two users 
tried to do UPDATE commands at the same time.

> Q2) If I back up the database first using the online backup to safely back
> it up, how do I safely restore it again when the db might be in use at the
> time?

I assume you're talking about the online backup API:



Under those circumstances I think you would use the same backup API in the 
other direction to restore the database again.  It will have a lock on that 
destination database the whole time it's working, of course.  So if you have 
implemented a timeout correctly your users will just get a long pause or a 
"database busy" message during access.

If you have been writing your code correctly you will have used transactions to 
group all related accesses /including related SELECTs/ together.  So any 
particular piece of code in your app should notice only consistency.

> I'm using DISQLite and the online api in delphi, not the command line thing.

I have seen some bad drivers which assume that underlying schema will not be 
changed by another user while they have a database connection open.  I have no 
reason to believe that this is one of them, but it might be worth reading the 
documentation.

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

Re: [sqlite] Database locked after read

2012-12-01 Thread Pavel Ivanov 
> I have found that when using SQLite under Windows if you execute an update
> statement and then a select statement, the database is locked until the
> select statement is finished.  How can I stop this from happening?

Note that you don't have to execute UPDATE before the SELECT to
reproduce that. SELECT places shared lock on the database. UPDATE
cannot proceed while any shared locks are active on the database. To
avoid such situation you can switch your database to the WAL journal
mode (http://www.sqlite.org/wal.html).


Pavel


On Sat, Dec 1, 2012 at 2:02 PM, Scott Ferrett
 wrote:
> I have found that when using SQLite under Windows if you execute an update
> statement and then a select statement, the database is locked until the
> select statement is finished.  How can I stop this from happening?
>
> Here is a simple test program that simulates this situation.  If you run the
> program you will get the message "Update executed without problems".
> Without closing this message you run the program again you will get "Table
> is locked??".
>
> void DisplayMessage(_TCHAR* message)
> {
>   MessageBox(NULL, message, "Test Lock", 0);
> }
>
> void Check(int status, char* message)
> {
>   if (status != 0 && status != SQLITE_ROW && status != SQLITE_DONE)
> DisplayMessage(message);
> }
> int _tmain(int argc, _TCHAR* argv[])
> {
>   sqlite3* connection;
>   bool needCreate = sqlite3_open_v2("test.sqlite", ,
> SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK;
>   if (!needCreate || sqlite3_open_v2("test.sqlite", ,
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK)
>   {
> if (needCreate)
> {
>   Check(sqlite3_exec(connection, "CREATE TABLE TESTFILE (id LONG, data
> CHAR(30))", NULL, NULL, NULL), "Create Table");
>   Check(sqlite3_exec(connection, "CREATE TABLE TESTFILE2 (id LONG, data
> CHAR(30))", NULL, NULL, NULL), "Create Table 2");
>   Check(sqlite3_exec(connection, "INSERT INTO TESTFILE VALUES (1, 'A')",
> NULL, NULL, NULL), "Insert 1");
>   Check(sqlite3_exec(connection, "INSERT INTO TESTFILE VALUES (2, 'B')",
> NULL, NULL, NULL), "Insert 2");
>   Check(sqlite3_exec(connection, "INSERT INTO TESTFILE2 VALUES (1,
> 'A')", NULL, NULL, NULL), "Insert 1");
>   Check(sqlite3_exec(connection, "INSERT INTO TESTFILE2 VALUES (2,
> 'B')", NULL, NULL, NULL), "Insert 2");
> }
> sqlite3_stmt* statement1;
> Check(sqlite3_prepare_v2(connection, "UPDATE TESTFILE SET data='A' WHERE
> id=1", -1, , NULL), "Update");
> int status = sqlite3_step(statement1);
> sqlite3_finalize(statement1);
> sqlite3_stmt* statement2;
> Check(sqlite3_prepare_v2(connection, "SELECT ID,data FROM TESTFILE2",
> -1, , NULL), "SELECT");
> Check(sqlite3_step(statement2), "stepping Select");
> if (status == SQLITE_DONE)
>   DisplayMessage("Update executed without problems"); // This simulates
> a select statement that takes a long time to execute
> else if (status == SQLITE_BUSY)
>   DisplayMessage("Table is locked??");
> else
>   DisplayMessage("There was a problem");
> sqlite3_finalize(statement2);
> sqlite3_close(connection);
>   }
>   return 0;
> }
>
>
> ___
> 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] Database locked after read

2012-12-01 Thread Scott Ferrett 
I have found that when using SQLite under Windows if you execute an 
update statement and then a select statement, the database is locked 
until the select statement is finished.  How can I stop this from happening?


Here is a simple test program that simulates this situation.  If you run 
the program you will get the message "Update executed without 
problems".  Without closing this message you run the program again you 
will get "Table is locked??".


void DisplayMessage(_TCHAR* message)
{
  MessageBox(NULL, message, "Test Lock", 0);
}

void Check(int status, char* message)
{
  if (status != 0 && status != SQLITE_ROW && status != SQLITE_DONE)
DisplayMessage(message);
}
int _tmain(int argc, _TCHAR* argv[])
{
  sqlite3* connection;
  bool needCreate = sqlite3_open_v2("test.sqlite", , 
SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK;
  if (!needCreate || sqlite3_open_v2("test.sqlite", , 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK)

  {
if (needCreate)
{
  Check(sqlite3_exec(connection, "CREATE TABLE TESTFILE (id LONG, 
data CHAR(30))", NULL, NULL, NULL), "Create Table");
  Check(sqlite3_exec(connection, "CREATE TABLE TESTFILE2 (id LONG, 
data CHAR(30))", NULL, NULL, NULL), "Create Table 2");
  Check(sqlite3_exec(connection, "INSERT INTO TESTFILE VALUES (1, 
'A')", NULL, NULL, NULL), "Insert 1");
  Check(sqlite3_exec(connection, "INSERT INTO TESTFILE VALUES (2, 
'B')", NULL, NULL, NULL), "Insert 2");
  Check(sqlite3_exec(connection, "INSERT INTO TESTFILE2 VALUES (1, 
'A')", NULL, NULL, NULL), "Insert 1");
  Check(sqlite3_exec(connection, "INSERT INTO TESTFILE2 VALUES (2, 
'B')", NULL, NULL, NULL), "Insert 2");

}
sqlite3_stmt* statement1;
Check(sqlite3_prepare_v2(connection, "UPDATE TESTFILE SET data='A' 
WHERE id=1", -1, , NULL), "Update");

int status = sqlite3_step(statement1);
sqlite3_finalize(statement1);
sqlite3_stmt* statement2;
Check(sqlite3_prepare_v2(connection, "SELECT ID,data FROM 
TESTFILE2", -1, , NULL), "SELECT");

Check(sqlite3_step(statement2), "stepping Select");
if (status == SQLITE_DONE)
  DisplayMessage("Update executed without problems"); // This 
simulates a select statement that takes a long time to execute

else if (status == SQLITE_BUSY)
  DisplayMessage("Table is locked??");
else
  DisplayMessage("There was a problem");
sqlite3_finalize(statement2);
sqlite3_close(connection);
  }
  return 0;
}


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

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

2012-12-01 Thread Bert Huijben 


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of David de Regt
> Sent: vrijdag 30 november 2012 18:41
> To: General Discussion of SQLite Database
> Subject: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance
> 
> Hey all.  I've been struggling with a basic perf issue running the same
code on
> Windows vs. iOS and OSX.
> 
> Basic query set:
> CREATE TABLE test (col1 int, col2 text);
> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')
> 
> I'm coding this using the default C amalgamation release and using
> prepare/etc. on all platforms in the exact same way (same very simple DB-
> access class I made).  I realize that using a transaction around this
would
> vastly improve perf, but given the atomic nature of the app that this test
is
> simulating, it won't work to wrap it into transactions, so my goal is to
improve
> the atomic performance.  These are all being run on the same Macbook Pro,
> with an SSD, running Windows via boot camp, OSX natively, and iOS via the
> iOS simulator:
> 
> With defaults (pragma sync = on, default journal_mode):
> Windows: 2500ms
> iOS: 300ms
> OSX: 280ms

On Windows Sqlite by default uses the real/full fsyncs to the hardware as
provided by the OS, while on at least OS/X, by default it doesn't.
See http://sqlite.org/pragma.html#pragma_fullfsync
(I don't know about iOS, but I wouldn't be surprised if it doesn't have the
same level of syncs)

But that doesn't answer why the performance is so different when the syncs
are off in your followup question.
> 
> With pragma sync = off, journal_mode = memory:
> Windows: 62ms
> iOS: 25ms
> OSX: 25ms
> 
> Turning off sync doesn't make me feel warm and fuzzy about our lost-power
> scenario, so with sync on, it seems like something must be fishy for it to
be

Bert

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

[sqlite] How to restore from backup or alter db in multi-user envionment?

2012-12-01 Thread dochsm 
My database is shared by many users on a network. As part of auto-updating my
client applications via the web it might be necessary to alter some table
structures / view sql or put extra data into tables inside the shared sqlite
database. Problem is that the db might be in use at the time. 

Qn1) Is it safe simply to wrap up everthing I want to do in a transaction
and then commit the transaction?

Q2) If I back up the database first using the online backup to safely back
it up, how do I safely restore it again when the db might be in use at the
time?

I'm using DISQLite and the online api in delphi, not the command line thing.
Howard



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-restore-from-backup-or-alter-db-in-multi-user-envionment-tp65812.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory using: is it normal

2012-12-01 Thread Jay A. Kreibich 
On Sat, Dec 01, 2012 at 06:00:59AM +0400, Уточкин Александр scratched on the 
wall:

> Could you tell me please if it is normal that memory used by application
> goes like this:
> 
> SQLite3_Prepare_v2 - Memory used changes slightly
> Binding values - Memory used changes slightly
> Loop with sq3lite_step - Memory used growths by 16Mb
> sq3lite_finalize - Memory used doesn't reduce

  The patterns sounds right.  SQLite will allocate memory for the page
  cache as needed, but it does not release it.

  On most systems the default max cache size is 2000 pages.  On most
  systems the default page size is 1K, but it can be 4K on some Windows
  systems.  That makes the 16MB look a shade big, but it might be about
  right if you're running on a Windows system, or if you've adjusted
  the default page size and/or cache size.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory using: is it normal

2012-12-01 Thread Igor Tandetnik 
Уточкин Александр  wrote:
> Could you tell me please if it is normal that memory used by application
> goes like this:
> 
> SQLite3_Prepare_v2 - Memory used changes slightly
> 
> Binding values - Memory used changes slightly
> 
> Loop with sq3lite_step - Memory used growths by 16Mb
> 
> sq3lite_finalize - Memory used doesn't reduce

Yes, this is normal. You are looking at the page cache. See also

http://sqlite.org/pragma.html#pragma_cache_size

-- 
Igor Tandetnik

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

[sqlite] Memory using: is it normal

2012-12-01 Thread Уточкин Александр 
Good day everybody!

 

Could you tell me please if it is normal that memory used by application
goes like this:

SQLite3_Prepare_v2 - Memory used changes slightly

Binding values - Memory used changes slightly

Loop with sq3lite_step - Memory used growths by 16Mb

sq3lite_finalize - Memory used doesn't reduce

 

SQL: SELECT * FROM tb WHERE datetime>":.." and datetime <":.."

SQL result: aprox. 71000 records

 

Table CREATE Syntax:

CREATE TABLE tb

(DataSource STRING, datetime TEXT, Open REAL, High REAL, Low REAL, Close
REAL, Volume Integer, FileName STRING, QUIK_DT TEXT)

 

Thank you!

 

 

Best regards,

Alexander.

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

[sqlite] A minor bug in the SQLite shell program.

2012-12-01 Thread Peter Aronson 
I have found a minor bug in the sqlite3 shell program. If you use .read to run 
a 
file with exactly two errors in it, it will exit from sqlite3 to the operating 
system.
To demonstrate, I first create a file with two lines each of which causes an 
(called e2.sql)
 
select nonexistentfunction();
select nonexistentfunction();
 
Then I use .read on this file:
 
sqlite3 -header -column label.esrigdb
SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions 
Enter 
SQL statements terminated with a ";"
sqlite> .q
/rococo3/peter/sqliteexe101 > sqlite3 x.db SQLite version 3.7.14.1 2012-10-04 
19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a 
";"
sqlite> .read e2.sql
Error: near line 1: no such function: nonexistentfunction
Error: near line 2: no such function: nonexistentfunction
/rococo3/peter/sqliteexe101 > 
 
And I'm back at the command prompt! This works this way on both Solaris and 
Windows. 

A quick examination of shell.c shows me that in do_meta_command(), quit sets rc 
to 2, and that read sets rc to the output from process_input (which is errCnt) :
.
.
.
  if( c=='q' && strncmp(azArg[0], "quit", n)==0 && nArg==1 ){
    rc = 2;
  }else
.
.
.
  if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 && nArg==2 ){
    FILE *alt = fopen(azArg[1], "rb");
    if( alt==0 ){
  fprintf(stderr,"Error: cannot open \"%s\"\n", azArg[1]);
  rc = 1;
    }else{
  rc = process_input(p, alt);
  fclose(alt);
    }
  }else
.
.
.
  return rc;
}
 
And this in process_input() (note the return of errCnt):
 
.
.
.
  while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){ .
.
.
.
  if( zLine && zLine[0]=='.' && nSql==0 ){
    if( p->echoOn ) printf("%s\n", zLine);
    rc = do_meta_command(zLine, p);
    if( rc==2 ){ /* exit requested */
  break;
  }else if( rc ){
    errCnt++;
  }
  continue;
    }
.
.
.
  }
  free(zLine);
  return errCnt;
}
 
And when process_input() returns after being called from main(),sqlite3 exits.
 
Adding the following line (the one with the comment) to do_meta_command() seems 
to fix the problem:
 
.
.
.
    if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 && nArg==2 ){
  FILE *alt = fopen(azArg[1], "rb");
  if( alt==0 ){
    fprintf(stderr,"Error: cannot open \"%s\"\n", azArg[1]);
    rc = 1;
    }else{
  rc = process_input(p, alt);
  fclose(alt);
  if (rc>1) rc = 1; /* rc == errCnt, but when rc == 2, shell exits. */
    }
  }else
.
.
.
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

2012-12-01 Thread Dimiter 'malkia' Stanev 
You said that other people tested it, and verified it, so it's unlikely 
that an Antivirus (or some other software - Inventory, or something 
similar) might be problematic.


Another thing you can do is to fire up procmon (SysInternals) and watch 
writes to your db. - they would happen from your process and from the 
System one (so don't filter out only your process).


Windows XP monitors certain file extensions for backup purposes - you 
might have to check on that one too if your file matches one of the 
listed extensions there (about ~1000)

http://msdn.microsoft.com/en-us/library/aa378870(VS.85).aspx

Check this file too - %windir%\system32\restore\Filelist.xml.

As to Alex - I work in gamedev studio, and we use sqlite for some core 
things, and as much I like that we used OSX/Linux or FreeBSD we are 
stuck with Windows - at least Windows 7 for now (which I like), and no 
plans to upgrade to 8.


On 11/30/2012 9:41 AM, David de Regt wrote:

Hey all.  I've been struggling with a basic perf issue running the same code on 
Windows vs. iOS and OSX.

Basic query set:
CREATE TABLE test (col1 int, col2 text);
[loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I'm coding this using the default C amalgamation release and using prepare/etc. 
on all platforms in the exact same way (same very simple DB-access class I 
made).  I realize that using a transaction around this would vastly improve 
perf, but given the atomic nature of the app that this test is simulating, it 
won't work to wrap it into transactions, so my goal is to improve the atomic 
performance.  These are all being run on the same Macbook Pro, with an SSD, 
running Windows via boot camp, OSX natively, and iOS via the iOS simulator:

With defaults (pragma sync = on, default journal_mode):
Windows: 2500ms
iOS: 300ms
OSX: 280ms

With pragma sync = off, journal_mode = memory:
Windows: 62ms
iOS: 25ms
OSX: 25ms

Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
scenario, so with sync on, it seems like something must be fishy for it to be 
~8-9x slower than the other platforms.  Is there something ridiculous about the 
windows file system performance that hoses sqlite's open/read/write/close 
transaction cycle?  Is there anything I can do, or just accept it and move on?  
With how that scales up, we may need to move to something like using embedded 
MySQL or LocalDB on Windows to get the same performance as we see with SQLite 
on other platforms, which seems quite ridiculous.

Thanks!
-David


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