Re: [sqlite] What this function returns?

[Harold Wood]

Lol so call you igor?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John McKown
Sent: Friday, November 29, 2013 8:10 PM
To: rsm...@rsweb.co.za; General Discussion of SQLite Database
Subject: Re: [sqlite] What this function returns?

Ah, found it.
http://msdn.microsoft.com/en-us/library/s3f49ktz.aspx

__int64, unsigned __int64, long long, unsigned long long,

-- 
> This is clearly another case of too many mad scientists, and not 
> enough hunchbacks.
>
> Maranatha! <><
> John McKown
>



--
This is clearly another case of too many mad scientists, and not enough
hunchbacks.

Maranatha! <><
John McKown
___
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] What this function returns?

[John McKown]

Ah, found it.
http://msdn.microsoft.com/en-us/library/s3f49ktz.aspx

__int64, unsigned __int64, long long, unsigned long long,

-- 
> This is clearly another case of too many mad scientists, and not enough
> hunchbacks.
>
> Maranatha! <><
> John McKown
>



-- 
This is clearly another case of too many mad scientists, and not enough
hunchbacks.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What this function returns?

[John McKown]

On Fri, Nov 29, 2013 at 9:47 PM, RSmith  wrote:

>
> The fact that the app compiles is no great precursor to its success in the
> wild.  The thing you are avoiding is not a dependancy - 64 bit types should
> be easy in any platform and I know Windows (anything after XP anyway) uses
> only 64-bit internals, much like any other OS since the dawn of the 21st
> century.  The fact that you can still compile 32 bit programs and run it is
> pure backward compatibility on any system that still allow it, so if your
> compiler does not support it natively, you need to upgrade.
>
> But, that is not the case... your compiler supports it just fine - it
> isn't a dependancy, it's a native part of the compiler, it must be.  Even
> in older 32-bit compiling systems there's always a 64 bit LongLong or Int64
> or whatever flavour it went by. The reason you might not be getting the
> same 32-vs-64 bit warning might be that your compiler is sneakily using 64
> bit integers in the background anyway, while the other doesn't or at least,
> doesn't warn you about it. (This is a hypothesis, I'm not using MSVC so
> cannot say for sure).
>
> Either way, you NEED to cast that return value to a proper data-type hat
> can hold all of 64 bits in it... else you will get errors, especially when
> some user of your program runs into the first >32bit number in his/her
> database and it turns negative, or overwrite previous keys, etc. etc.
>
> You cannot ignore it, but it should be really easy to fix. Any MSVC buff
> here knows the exact Type for a 64b-int in MSVC?
>

I don't _do_ Windows, but a fast search gave me:
http://msdn.microsoft.com/en-us/library/windows/desktop/aa383710%28v=vs.85%29.aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/aa384264%28v=vs.85%29.aspx

__int64, DWORD64 (unsigned), INT64 (signed), LONG64 (signed), UINT64
(unsigned), ULONG64 (unsigned), but the MSVC documentation seems to only
list "signed long int" and "unsigned long int". As best as I can find.

Personally, I would use the sqlite3 supplied name and let Dr. Hipp's code
resolve it to the proper declaration based on the compiler being used.


-- 
This is clearly another case of too many mad scientists, and not enough
hunchbacks.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What this function returns?

[RSmith]

On 2013/11/30 05:28, Igor Korot wrote:

As you can see from http://sqlite.org/c3ref/last_insert_rowid.html, it
returns sqlite3_int64, a signed 64-bit integer type.  The C99 name and
I think the C++11 name for this is int64_t, which is probably what
you want, but I vaguely recall the Microsoft compiler requires jumping
through some kind of hoop to get it.  You could just use sqlite3_int64
directly if you don't mind taking the header dependency.
Well, than I don't want the extra dependancy.
I have a nice application with dependancies set up correctly as it is
compiled on Windows.
And I wouldn't even know about it if it's not about Mac compilation. ;-)

My biggest problem is: why MSVC compiled this code just fine?

Also I am compiling 32-bit app on both platforms.


The fact that the app compiles is no great precursor to its success in the wild.  The thing you are avoiding is not a dependancy - 
64 bit types should be easy in any platform and I know Windows (anything after XP anyway) uses only 64-bit internals, much like any 
other OS since the dawn of the 21st century.  The fact that you can still compile 32 bit programs and run it is pure backward 
compatibility on any system that still allow it, so if your compiler does not support it natively, you need to upgrade.


But, that is not the case... your compiler supports it just fine - it isn't a dependancy, it's a native part of the compiler, it 
must be.  Even in older 32-bit compiling systems there's always a 64 bit LongLong or Int64 or whatever flavour it went by. The 
reason you might not be getting the same 32-vs-64 bit warning might be that your compiler is sneakily using 64 bit integers in the 
background anyway, while the other doesn't or at least, doesn't warn you about it. (This is a hypothesis, I'm not using MSVC so 
cannot say for sure).


Either way, you NEED to cast that return value to a proper data-type hat can hold all of 64 bits in it... else you will get errors, 
especially when some user of your program runs into the first >32bit number in his/her database and it turns negative, or overwrite 
previous keys, etc. etc.


You cannot ignore it, but it should be really easy to fix. Any MSVC buff here 
knows the exact Type for a 64b-int in MSVC?



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

Re: [sqlite] What this function returns?

[Igor Korot]

Simon,

On Fri, Nov 29, 2013 at 7:33 PM, Simon Slavin  wrote:
>
> On 30 Nov 2013, at 3:28am, Igor Korot  wrote:
>
>> Also I am compiling 32-bit app on both platforms.
>
> Do you absolutely need to compile 32-bit for Macs ?  There are almost no Macs 
> which can't handle 64-bit left, and 64-bit apps are faster and mode 
> compatible with everything else used these days.
>
> I have no experience of trying to develop the same app for Windows & Mac, so 
> if that's a good reason to compile for 32-bit please excuse me.

I will eventually compile 64-bit app on both platforms, but right now
for at least testing purposes
I am doing everything 32-bit.

Thank you.

>
> Simon.
> ___
> 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] Eficiency : 1 table vs several tables

[Harold Wood]

Old school database Mapper worked along that line, you had a ton of small
tables and a lookup table that told your queries what table to look in...all
in all it was a mess.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Friday, November 29, 2013 7:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Eficiency : 1 table vs several tables


On 29 Nov 2013, at 8:27pm, Carlos Ferreira  wrote:

> Let's assume that TABLE now designates my real tables that can be 
> either SQLITE tables or sub groups of records inside one big real 
> SQLite table
> 
> 
> 
> I have to load to memory and save to DB groups of these TABLE at the 
> same time ( by saving I refer to update or save the blobs inside each
table ).
> 
> 
> 
> What if  faster? 

SQLite is optimized for searching a table for specific values.  Assuming, of
course, that you have an index ideally suited to your search.  It is not
optimized for searching a huge list of tables for one with a specific name.

My assumption is that putting 1000 tables in a database is going to slow
down every operation since every operation has to search for the correct
table and /then/ search that table for the correct row(s).  But I have no
figures to prove that.

Simon.
___
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] What this function returns?

[Simon Slavin]

On 30 Nov 2013, at 3:28am, Igor Korot  wrote:

> Also I am compiling 32-bit app on both platforms.

Do you absolutely need to compile 32-bit for Macs ?  There are almost no Macs 
which can't handle 64-bit left, and 64-bit apps are faster and mode compatible 
with everything else used these days.

I have no experience of trying to develop the same app for Windows & Mac, so if 
that's a good reason to compile for 32-bit please excuse me.

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

Re: [sqlite] Eficiency : 1 table vs several tables

[Simon Slavin]

On 29 Nov 2013, at 8:27pm, Carlos Ferreira  wrote:

> Let's assume that TABLE now designates my real tables that can be either
> SQLITE tables or sub groups of records inside one big real SQLite table
> 
> 
> 
> I have to load to memory and save to DB groups of these TABLE at the same
> time ( by saving I refer to update or save the blobs inside each table ).
> 
> 
> 
> What if  faster? 

SQLite is optimized for searching a table for specific values.  Assuming, of 
course, that you have an index ideally suited to your search.  It is not 
optimized for searching a huge list of tables for one with a specific name.

My assumption is that putting 1000 tables in a database is going to slow down 
every operation since every operation has to search for the correct table and 
/then/ search that table for the correct row(s).  But I have no figures to 
prove that.

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

Re: [sqlite] What this function returns?

[Igor Korot]

Drake,

On Fri, Nov 29, 2013 at 7:04 PM, Drake Wilson  wrote:
> Quoth Igor Korot , on 2013-11-29 18:49:05 -0800:
>> Trying to change the return type to long does not solve the warning.
>>
>> Which value should this function return?
>
> As you can see from http://sqlite.org/c3ref/last_insert_rowid.html, it
> returns sqlite3_int64, a signed 64-bit integer type.  The C99 name and
> I think the C++11 name for this is int64_t, which is probably what
> you want, but I vaguely recall the Microsoft compiler requires jumping
> through some kind of hoop to get it.  You could just use sqlite3_int64
> directly if you don't mind taking the header dependency.

Well, than I don't want the extra dependancy.
I have a nice application with dependancies set up correctly as it is
compiled on Windows.
And I wouldn't even know about it if it's not about Mac compilation. ;-)

My biggest problem is: why MSVC compiled this code just fine?

Also I am compiling 32-bit app on both platforms.

>
> The truncation is actually a potential error: e.g., a row ID of 2^32
> would be returned as 0 instead on a system with 32-bit int.  It's the
> sort of thing you might not see in production for a while until it
> breaks everything suddenly a ways down the line.

Yes, thats why I am trying to solve it.

Thank you.

>
>---> Drake Wilson
> ___
> 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] Eficiency : 1 table vs several tables

[Mohit Sindhwani]

Hi Carlos,

On 30/11/2013 4:27 AM, Carlos Ferreira wrote:

My tables have all the same number of columns ( 1 column only.. of BLOBs.)
and Simon Slavin suggested I could use only one big table where I add an
extra column to identify the table name.


I would consider searching by an integer ID rather than a string if that 
is possible.



This seems quite a nice approach, because it does seems to be more memory
efficient in terms of disk usage.

However my question is the following:

Let's assume that TABLE now designates my real tables that can be either
SQLITE tables or sub groups of records inside one big real SQLite table

I have to load to memory and save to DB groups of these TABLE at the same
time ( by saving I refer to update or save the blobs inside each table ).


From the sound of it, you don't (and can't) delete a single row from 
the table.  Is that intentional?



What if  faster?

Accessing a table in SQLite and updating deleting or adding new records
Or

Querying the records of one table in such a way that the select records have
a field = Table Name..and then adding and updating these records.


My gut feeling is that accessing a smaller table is likely to be faster 
but a lot depends on the number of records.  I don't think you would see 
much difference for a few thousand or few tens of thousands records.


That said, since you know the exact query that you want to perform, you 
may want to look into partial indexes as a way to speed up these queries 
by avoiding a full table scan.

http://www.sqlite.org/partialindex.html

Best Regards,
Mohit.



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

Re: [sqlite] What this function returns?

[Drake Wilson]

Quoth Igor Korot , on 2013-11-29 18:49:05 -0800:
> Trying to change the return type to long does not solve the warning.
> 
> Which value should this function return?

As you can see from http://sqlite.org/c3ref/last_insert_rowid.html, it
returns sqlite3_int64, a signed 64-bit integer type.  The C99 name and
I think the C++11 name for this is int64_t, which is probably what
you want, but I vaguely recall the Microsoft compiler requires jumping
through some kind of hoop to get it.  You could just use sqlite3_int64
directly if you don't mind taking the header dependency.

The truncation is actually a potential error: e.g., a row ID of 2^32
would be returned as 0 instead on a system with 32-bit int.  It's the
sort of thing you might not see in production for a while until it
breaks everything suddenly a ways down the line.

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

[sqlite] What this function returns?

[Igor Korot]

Hi, ALL,
One stupid question: what sqlite3_last_insert_rowid() function returns?

I have the following code:

int MyClass::Foo()
{
   return sqlite3_last_insert_rowid( m_handle );
}

It compiles fine on Windows (7 64 bit) with MSVC 2010 SP1 Pro 32-bit project.
But on Snow Leopard XCode 4.2 32-bit project Cocoa it produces:

"Implicit conversion shortens 64-bit value to 32-bit"

Trying to change the return type to long does not solve the warning.

Which value should this function return?

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

[sqlite] Eficiency : 1 table vs several tables

[Carlos Ferreira]

Hi All,

 

Thank you all about the help on adding tables.

 

Adding the tables between  transactions did help a lot. Speed is now ok.

 

 

I received a suggestion that in my case is quite valid:

 

My tables have all the same number of columns ( 1 column only.. of BLOBs.)
and Simon Slavin suggested I could use only one big table where I add an
extra column to identify the table name.

 

This seems quite a nice approach, because it does seems to be more memory
efficient in terms of disk usage.

 

However my question is the following:

 

Let's assume that TABLE now designates my real tables that can be either
SQLITE tables or sub groups of records inside one big real SQLite table

 

I have to load to memory and save to DB groups of these TABLE at the same
time ( by saving I refer to update or save the blobs inside each table ).

 

What if  faster? 

 

Accessing a table in SQLite and updating deleting or adding new records 

 

Or

 

Querying the records of one table in such a way that the select records have
a field = Table Name..and then adding and updating these records.

 

( by updating I mean using the direct functions for incremental BLOB access
)

 

 

Thank you All

 

Carlos

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

Re: [sqlite] Big number of tables

[Carlos Ferreira]

Thank you guys.

I am going to try it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal
Sent: sexta-feira, 29 de Novembro de 2013 14:17
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Big number of tables

On Fri, Nov 29, 2013 at 2:37 PM, Igor Tandetnik  wrote:

> On 11/29/2013 8:33 AM, Carlos Ferreira wrote:
>
>> Any of you know how to speed up the creation of empty tables in SQlite?
>>
>> If I have to create more than 1000 empty tables to initialize my 
>> application document it takes a while..
>>
>
> Make sure you run all CREATE TABLE statements within a single transaction.
> My guess is you don't, and then most of the time is spent in committing an
> implicit transaction after every statement.


Here's a simple test which shows that in action:

[stephan@host:~/tmp]$ i=0; while [ $i -lt 1000 ]; do echo "create table t$i
(a,b,c);"; i=$((i + 1)); done > foo.sql
[stephan@host:~/tmp]$ wc -l foo.sql
1000 foo.sql
[stephan@host:~/tmp]$ echo 'begin;' > bar.sql
[stephan@host:~/tmp]$ cat foo.sql >> bar.sql
[stephan@host:~/tmp]$ echo 'commit;' >> bar.sql
[stephan@host:~/tmp]$ time sqlite3 x.db < foo.sql

real 2m25.208s
user 0m0.380s
sys 0m0.468s
[stephan@host:~/tmp]$ rm x.db
[stephan@host:~/tmp]$ time sqlite3 x.db < bar.sql

real 0m0.344s
user 0m0.148s
sys 0m0.000s


BIG difference.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
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] Big number of tables

[Simon Slavin]

On 29 Nov 2013, at 1:33pm, Carlos Ferreira  wrote:

> If I have to create more than 1000 empty tables to initialize my application
> document it takes a while..
> 
> 
> 
> 
> 
> Is there any workaround?

Do these tables have different columns ?  If not, then create one table and use 
a column instead of different table names.  Not only will it be faster but 
it'll use less space in the database file.

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

Re: [sqlite] Big number of tables

[Stephen Chrzanowski]

As others have suggested, transactions, create a single file and just copy,
or throw it in memory (And as an extra thought to creating it in memory is
once created, throw it to disk via the Backup API then --if needed-- work
off that).

Another option, but probably not the best idea, is each time a table is
going to be accessed, use a "CREATE TABLE {tablename} IF NOT EXISTS".
This way the table exists when the call is made to do something with that
table.  You'd probably have to comb through your code to find out when the
tables are to be accessed.  Perhaps a function that calls the above SQL
statement, keep a list/collection variable hanging around that will keep
tabs of when a table was made during that session so you're not trying to
create the table EVERY call.  The perk is that you might not have to have
all 1000 tables so the DB would probably be a tiny bit smaller?  Ok, not a
BIG perk if you're dealing with 1000 tables.

My suggestion is a* last ditch option*.  It is a lot of work to go through
code and re-test what already should be known to work with this new method,
but I figured I'd throw out another option.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Big number of tables

[Mohit Sindhwani]

Hi Carlos,

On 29/11/2013 9:33 PM, Carlos Ferreira wrote:

Any of you know how to speed up the creation of empty tables in SQlite?

If I have to create more than 1000 empty tables to initialize my application
document it takes a while..

Is there any workaround?


Workaround:
1. I would probably create the database with all the empty tables once 
and store it as a file.  Whenever a new one is needed, I'd make a copy 
of it and use that.  The copy may be a file or a blob in memory within 
your program or a blob in a database that you're using for your program, 
etc.
This is assuming that the SQLite3 table creation is indeed slow.  I have 
never tried with 1000 empty tables, so I'm not sure that it is.
2. If it is an option, do a lazy creation of tables - create only the 
tables that are needed when they are needed.  Frankly, if you often read 
and write from the database, this is a bit painful since you have to add 
quite a few more checks


As always, some of the other things that can give you a bit more speed:
* Transactions
* The correct kind of journal mode (including moving it to memory)
etc.

Best Regards,
Mohit.


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

Re: [sqlite] Your thoughts on these observations

[RSmith]

Hi L,

You seem to be after a theory rather than an actual helpful criterion - and Richard answered questions 1, 2 and 3 all in a single 
statement as far as the criterion matters.  To illuminate the theory is not really possible and presupposes a wealth of preceding 
information that must be known - something I doubt anyone will want to re-type here.


To try my hand at summarising what I think you are asking and what I think really happens without too much detail (hoping others 
will fill in where I may err):


SQLite has no direct control to a file, it operates at the other end of a VFS, which is much like a telephone conversation to 
somebody you cannot see. It asks the VFS for a handle to a file, it suppplies that handle every time it requests a file operation. 
The operations are mostly Open, Read, Write, Close and a few less important ones.  It also stores a path to an opened DB file and 
use this same mechanism for the journal files.


Specific to your question 1 : Even if it was set in stone (which it ain't) how SQLite starts a file connection, it still does not 
mean the VFS will do it in the same way. This is not just theoretical differences, there are very existing differences on how OSX or 
Windows or Linux or Android systems do this, nevermind all the custom VFSes used in the wild. So the answer becomes: "Who knows?" - 
AND, as if that's not unspecific enough, - "It can change at a whim".


Question 2: SQLite requires the data this-side-of-the-vfs to be consistent and atomic, therefore it locks files as needed and you 
cannot read a file in the middle of a write operation (I mean, what data will you be getting back? and will it be 
half-old-half-new?). When moving/renaming files - some OS systems like Linux are very happy to keep VFS comms going to a file-handle 
because the actual bytes on disk does not move, just a file-table reference to it, which you only really need as a lookup for 
acquiring a new handle. On other systems notably Android and Windows, actual bytes might be moved in part, but regardless of whether 
it is, just for the possibility thereof (and in the name of consistency with cross-volume moves), the VFS kills access to the file 
once it is moved. SQLite has no control over this and is at the mercy of the operating system on the other side of the VFS. (but it 
contains many checks and balances to ensure data stability regardless of VFS whims).


Question 3: Answered above really, but to recap - SQLite has no control over files directly, it communicates only with the VFS, 
therefore ANYTHING that goes wrong will kill the access, whether it be a move on non-movable OS, or SQLite asking for a file in a 
path (access or create) that no longer exists, or a number of other related possible issues, it's essentially unknown to SQLite and 
cannot be known comprehensively, unless you single out an OS, and even then, it may change on a whim and can never be used as a 
programmatic measure (but I don't think you were imagining it in this way anyway).



I hope this is slightly more illumiating than before. Sorry it can't be 
definitive answers.


On 2013/11/29 17:59, L. Wood wrote:

Of course, Richard has already answered question 1 and that answer definitely 
doesn't change.

Questions 2 and 3 now remain.



From: lwoo...@live.com
To: sqlite-users@sqlite.org
Date: Fri, 29 Nov 2013 15:21:51 +
Subject: Re: [sqlite] Your thoughts on these observations

Before answering my questions:

Please get rid of "rename" entirely in my post. Replace "rename/move" with just 
"move".

Also, assume that whenever I move my main database file, the -journal file 
always moves with it too.

I apologize for the confusion. My mistake. Sorry Richard.


I have made a few experimental observations and I would appreciate your 
thoughts.

1.

_open() does indeed actually open the main database file. It is left open all 
the way until _close(), when it is actually closed. Correct?

I ask because someone mentioned before that _open() doesn't really open the 
file - that it only prepares the file for opening at a later time when open is 
actually needed.


2.

If my SQL statements do nothing but read from the database (no writes), I can 
rename/move the main database file any time I wish after _open() without any 
problems whatsoever. Correct?


3.

If my SQL statements write to the database and I rename/move the main database 
file just after _open(), the next call to _step() gives me the error 
SQLITE_IOERR.

This happens NOT because the main database file has been renamed/moved. Correct?

Rather, this happens because the old path has been saved by SQLite for use by 
the -journal files. The error happens because SQLite cannot match the -journal 
file name and the main database file name (which has now changed). But here I'm 
just guessing. Is this correct? If not, then why exactly does the error occur?

___
sqlite-users 

Re: [sqlite] Your thoughts on these observations

[Simon Slavin]

On 29 Nov 2013, at 3:59pm, L. Wood  wrote:

> Of course, Richard has already answered question 1 and that answer definitely 
> doesn't change.
> 
> Questions 2 and 3 now remain.

Answer 2 is the same as Richard posted.  Operations by other users could render 
the data you're looking at corrupt.  It's not safe to move the original 
database file.

Answer 3 is variable because it depends on what SQLite has to do to serve your 
query.  Some queries only need to read data straight out of the database file.  
Others require making up temporary indexes, which could involve making 
temporary files.  Others involve handling the journal file.  It depends on 
whether the database file has really been opened yet, the nature of your query 
and what other users are doing at the some time.

But generally speaking, no.  You shouldn't assume that it's safe to move the 
original database file or the journal files while you have a database open.

So just like every other app in existence, don't move an app's data files while 
the app is running.

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

Re: [sqlite] Your thoughts on these observations

[L. Wood]

Of course, Richard has already answered question 1 and that answer definitely 
doesn't change.

Questions 2 and 3 now remain.


> From: lwoo...@live.com
> To: sqlite-users@sqlite.org
> Date: Fri, 29 Nov 2013 15:21:51 +
> Subject: Re: [sqlite] Your thoughts on these observations
>
> Before answering my questions:
>
> Please get rid of "rename" entirely in my post. Replace "rename/move" with 
> just "move".
>
> Also, assume that whenever I move my main database file, the -journal file 
> always moves with it too.
>
> I apologize for the confusion. My mistake. Sorry Richard.
>
>> I have made a few experimental observations and I would appreciate your 
>> thoughts.
>>
>> 1.
>>
>> _open() does indeed actually open the main database file. It is left open 
>> all the way until _close(), when it is actually closed. Correct?
>>
>> I ask because someone mentioned before that _open() doesn't really open the 
>> file - that it only prepares the file for opening at a later time when open 
>> is actually needed.
>>
>>
>> 2.
>>
>> If my SQL statements do nothing but read from the database (no writes), I 
>> can rename/move the main database file any time I wish after _open() without 
>> any problems whatsoever. Correct?
>>
>>
>> 3.
>>
>> If my SQL statements write to the database and I rename/move the main 
>> database file just after _open(), the next call to _step() gives me the 
>> error SQLITE_IOERR.
>>
>> This happens NOT because the main database file has been renamed/moved. 
>> Correct?
>>
>> Rather, this happens because the old path has been saved by SQLite for use 
>> by the -journal files. The error happens because SQLite cannot match the 
>> -journal file name and the main database file name (which has now changed). 
>> But here I'm just guessing. Is this correct? If not, then why exactly does 
>> the error occur?
> ___
> 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] Your thoughts on these observations

[L. Wood]

Before answering my questions:

Please get rid of "rename" entirely in my post. Replace "rename/move" with just 
"move".

Also, assume that whenever I move my main database file, the -journal file 
always moves with it too.

I apologize for the confusion. My mistake. Sorry Richard.

> I have made a few experimental observations and I would appreciate your 
> thoughts.
>
> 1.
>
> _open() does indeed actually open the main database file. It is left open all 
> the way until _close(), when it is actually closed. Correct?
>
> I ask because someone mentioned before that _open() doesn't really open the 
> file - that it only prepares the file for opening at a later time when open 
> is actually needed.
>
>
> 2.
>
> If my SQL statements do nothing but read from the database (no writes), I can 
> rename/move the main database file any time I wish after _open() without any 
> problems whatsoever. Correct?
>
>
> 3.
>
> If my SQL statements write to the database and I rename/move the main 
> database file just after _open(), the next call to _step() gives me the error 
> SQLITE_IOERR.
>
> This happens NOT because the main database file has been renamed/moved. 
> Correct?
>
> Rather, this happens because the old path has been saved by SQLite for use by 
> the -journal files. The error happens because SQLite cannot match the 
> -journal file name and the main database file name (which has now changed). 
> But here I'm just guessing. Is this correct? If not, then why exactly does 
> the error occur? 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Big number of tables

[Stephan Beal]

On Fri, Nov 29, 2013 at 2:37 PM, Igor Tandetnik  wrote:

> On 11/29/2013 8:33 AM, Carlos Ferreira wrote:
>
>> Any of you know how to speed up the creation of empty tables in SQlite?
>>
>> If I have to create more than 1000 empty tables to initialize my
>> application
>> document it takes a while..
>>
>
> Make sure you run all CREATE TABLE statements within a single transaction.
> My guess is you don't, and then most of the time is spent in committing an
> implicit transaction after every statement.


Here's a simple test which shows that in action:

[stephan@host:~/tmp]$ i=0; while [ $i -lt 1000 ]; do echo "create table t$i
(a,b,c);"; i=$((i + 1)); done > foo.sql
[stephan@host:~/tmp]$ wc -l foo.sql
1000 foo.sql
[stephan@host:~/tmp]$ echo 'begin;' > bar.sql
[stephan@host:~/tmp]$ cat foo.sql >> bar.sql
[stephan@host:~/tmp]$ echo 'commit;' >> bar.sql
[stephan@host:~/tmp]$ time sqlite3 x.db < foo.sql

real 2m25.208s
user 0m0.380s
sys 0m0.468s
[stephan@host:~/tmp]$ rm x.db
[stephan@host:~/tmp]$ time sqlite3 x.db < bar.sql

real 0m0.344s
user 0m0.148s
sys 0m0.000s


BIG difference.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Your thoughts on these observations

[Richard Hipp]

On Fri, Nov 29, 2013 at 5:43 AM, L. Wood  wrote:

> I have made a few experimental observations and I would appreciate your
> thoughts.
>
> 1.
>
> _open() does indeed actually open the main database file. It is left open
> all the way until _close(), when it is actually closed. Correct?
>

I don't remember if it defers opening the file or defers reading the file.
Either way, we reserve the right to change that behavior at any time and
without notice.  You should not depend on it either way.


>
> I ask because someone mentioned before that _open() doesn't really open
> the file - that it only prepares the file for opening at a later time when
> open is actually needed.
>
>
> 2.
>
> If my SQL statements do nothing but read from the database (no writes), I
> can rename/move the main database file any time I wish after _open()
> without any problems whatsoever. Correct?
>

Incorrect.

If some other process starts writing to the file then crashes, your process
will not be able to recover and will end up corrupting the database.


>
>
> 3.
>
> If my SQL statements write to the database and I rename/move the main
> database file just after _open(), the next call to _step() gives me the
> error SQLITE_IOERR.
>
> This happens NOT because the main database file has been renamed/moved.
> Correct?
>
> Rather, this happens because the old path has been saved by SQLite for use
> by the -journal files. The error happens because SQLite cannot match the
> -journal file name and the main database file name (which has now changed).
> But here I'm just guessing. Is this correct? If not, then why exactly does
> the error occur?
> ___
> 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] Big number of tables

[Igor Tandetnik]

On 11/29/2013 8:33 AM, Carlos Ferreira wrote:

Any of you know how to speed up the creation of empty tables in SQlite?

If I have to create more than 1000 empty tables to initialize my application
document it takes a while..


Make sure you run all CREATE TABLE statements within a single 
transaction. My guess is you don't, and then most of the time is spent 
in committing an implicit transaction after every statement.

--
Igor Tandetnik

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

[sqlite] Big number of tables

[Carlos Ferreira]

 

Hello,

 

Any of you know how to speed up the creation of empty tables in SQlite?

 

If I have to create more than 1000 empty tables to initialize my application
document it takes a while..

 

 

Is there any workaround?

 

Thanks

 

Carlos

 

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

Re: [sqlite] SQLite doesn't work for me

[R Paul]

Hi ..I recently started to program in java and tried to work with an SQLite 
example...and everytime I ran an example it would “shutdown unexpectedly” and 
it has told me that my Toast class wasn’t in the source code library, or it 
indicated source code missing. I chalked it up to conflicting version 
sources..and have been uninstalling my whole android eclipse ADT and working to 
reinstall. If anyone has any thoughts please do tell... cause I need to get 
SQLite working if I am to pass my Android programming course.

R.Paul

Sheridan College

Oakville,On



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

[sqlite] Your thoughts on these observations

[L. Wood]

I have made a few experimental observations and I would appreciate your 
thoughts.

1.

_open() does indeed actually open the main database file. It is left open all 
the way until _close(), when it is actually closed. Correct?

I ask because someone mentioned before that _open() doesn't really open the 
file - that it only prepares the file for opening at a later time when open is 
actually needed.


2.

If my SQL statements do nothing but read from the database (no writes), I can 
rename/move the main database file any time I wish after _open() without any 
problems whatsoever. Correct?


3.

If my SQL statements write to the database and I rename/move the main database 
file just after _open(), the next call to _step() gives me the error 
SQLITE_IOERR.

This happens NOT because the main database file has been renamed/moved. Correct?

Rather, this happens because the old path has been saved by SQLite for use by 
the -journal files. The error happens because SQLite cannot match the -journal 
file name and the main database file name (which has now changed). But here I'm 
just guessing. Is this correct? If not, then why exactly does the error occur?  
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users