date:20140919


>> There is no magic associated with a symbol name in C -- it is simply
>> what you chose to call it.  Just because you chose to call something 
>>"argc" does not mean that it is a count of anything (much less a count 
>>of arguments), nor even that it is a default width integer; nor does 
>>calling something an argv make it an array of pointers to character 
>>strings.

>There is something "magic" about main in standard C / C++ hosted
>environments (because it is what the standard library wants to jump to
>after running the library startup code). I dare say most developers have
>not read the standards documents and thus don't know or understand the
>exact difference between "hosted" and "free standing" environments or why
>main is used "everywhere" except (practically) for Windows GUI apps which
>need a WinMain instead of main.

>> >, and where it is understood to be defined as "char**" or equivalent,
>> >one might be forgiven for not quite understanding the difference.

>> No THE PERSON TYPING THE CODE declared it as a char**.  (Of course, the
>> actual type of the argument strings passed to the main function of a C
>> program is char*[], not char**, so you are technically using an
>> incorrect declaration and have made an incorrect assumption about the 
>> meaning of argv anyway).

>Please carefully re-read my comment: 'defined as "char**" or equivalent'.
>"char** argv" is the same as "char* argv[]" in every way except for
>spelling.

but char** is not equivalent to sqlite_value** and a scalar function
definition has a type of void (*xFunc)(sqlite3_context*,int,sqlite3_value**)

void functionName(sqlite3_context* a, int b, sqlite3_value**c)

granted the main function is

int main(int a, char**b)

but spelling "c" as "argv" does not change the type to "char**".

>Anyway, sorry for opening such a can of worms. You'll note I haven't made
>these types of comments with regard to people asking questions that seem
>obviously to be for homework problems or for deep explanations of the
>innards of exactly how VDBE works. In this case I didn't think the
>question asked warranted the type of response I read. 
>No more or less. Take care.

You too.  However, I would point out that there is a big difference between a 
symbol name and the type declared for that symbol in its instantaneous 
declaration.  Using a name often used in a different context does not change 
the declared type in the declaration to the type of the symbol name you have 
subverted.




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

Re: [sqlite] Rrepresentation of REAL type

On Fri, Sep 19, 2014 at 11:46 AM, TimeOfDay.nl 
wrote:

> I am looking at my SQLite database in two ways:
>
> 1. from PHP, in my application,  thru 'select * from table' statements and
> the such
> 2. from Adminer, as a database management tool
>
> When I look at a field that is type Real, and has the value of -41.29 as
> the entered value, the results differ.
>
> - The PHP select gives me -41.29
> - Adminer gives me -41.289147
>
> Why this difference?  Does the php library do something to 'round' numbers
> to so-many decimals?
>


See http://www.sqlite.org/faq.html#q16

The think to remember is that IEEE floating point numbers are
approximations.  They are designed to solve real world physic problems.
And in the real world it is pysically impossible to tell -41.29 and
-41.289147 apart.  So you might as well think of them as the
same number.

A 64-bit IEEE floating point number variable cannot, in fact, contain
exactly -41.29.  The closest it can get is
-41.28914734871708787977695465087890625.  SQLite doesn't even
try to get that close - promising only that floating point value are
correct to 15 significant digits.

-- 
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] Inefficient query plan with ORDER BY and UNION ALL subquery

2014-09-19 Thread Yuanzhong Xu

Hi,

In SQLite 3.8.6, suppose I have two tables in the same format:

CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);


The query plan can be quite efficient for:

EXPLAIN QUERY PLAN SELECT id,data FROM (SELECT * FROM t1 UNION ALL
SELECT * FROM t2) WHERE id=10 ORDER BY data;

1|0|0|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
2|0|0|SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

However, if you remove "data" column from the outer SELECT, it becomes
very inefficient:

EXPLAIN QUERY PLAN SELECT id FROM (SELECT * FROM t1 UNION ALL SELECT *
FROM t2) WHERE id=10 ORDER BY data;

2|0|0|SCAN TABLE t1
3|0|0|SCAN TABLE t2
1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY


Thanks,
-- 
Yuanzhong Xu
Ph.D. Student
Department of Computer Science
the University of Texas at Austin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

2014-09-19 Thread Merike

19.09.2014 05:24, Richard Hipp kirjutas:
> Thanks again for the regression report.  This problem is now fixed on
> the SQLite trunk.  See
> http://www.sqlite.org/src/info/72727b68cd0796?dc=22 for the check-in
> that fixes the problem.
>
> If all you want to know is that the problem has been fixed, you may
> stop reading now.  If you are curious about the cause of the problem
> was, you may continue reading.
>
>
That's great to hear! Looking forward to next release :)

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

[sqlite] Missing SQLite.Designer.SQLiteDataViewSupport?

2014-09-19 Thread Hans Lehmann

I am one of several developers here that use SQLite in a development
environment for mixed-platform applications.  Using Visual Studio 2010 and
2012, on Windows 7-x64.
We all have the following packages installed:

sqlite-netFx40-setup-bundle-x86-2010-1.0.84.0,

sqlite-netFx40-setup-bundle-x64-2010-1.0.84.0,

sqlite-netFx45-setup-bundle-x86-2012-1.0.84.0,

sqlite-netFx45-setup-bundle-x64-2012-1.0.84.0


with "Install the assemblies into the global assembly cache"   and
"Install the designer components for Visual Studio” both checked when
installing the x86 versions.


This has worked fine for a long time, but recently a couple of the
developers have started to get the following error from Visual Studio when
trying to connect to an existing database:


"Could not find any resources appropriate for the specified culture or the
neutral culture.  Make sure "SQLite.Designer.SQLiteDataViewSupport2012.xml"
was correctly embedded or linked into assembly "SQLite.Designer" at compile
time, or that all the satellite assemblies required are loadable and fully
signed."


We've uninstalled & re-installed, compared ProcessMonitor logs between good
and bad developer machines, etc., with no luck.


Anyone here run into something similar?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Rrepresentation of REAL type

2014-09-19 Thread TimeOfDay.nl

I am looking at my SQLite database in two ways:

1. from PHP, in my application,  thru 'select * from table' statements and
the such
2. from Adminer, as a database management tool

When I look at a field that is type Real, and has the value of -41.29 as
the entered value, the results differ.

- The PHP select gives me -41.29
- Adminer gives me -41.289147

Why this difference?  Does the php library do something to 'round' numbers
to so-many decimals?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 50% faster than 3.7.17

2014-09-19 Thread Stephen Chrzanowski

I, as well, wish to thank you for this tool Dr. Hipp.  I've never published
a public application using this engine, but, at my place of employment,
where my primary responsibility is to just monitor servers world wide, I've
coded a few tidbit web and desktop applications that have made my job SO
much easier.  Without this, my desktop apps would have to rely on MySQL,
which is massive overkill (resource wise) for some of the things I've
needed to use it for.

Thanks again!

On Fri, Sep 19, 2014 at 9:22 PM, jose isaias cabrera <
jic...@cinops.xerox.com> wrote:

> "Richard Hipp" wrote...
>
>
>
>  The latest SQLite 3.8.7 alpha version (available on the download page
>> http://www.sqlite.org/download.html) is 50% faster than the 3.7.17
>> release
>> from 16 months ago.  That is to say, it does 50% more work using the same
>> number of CPU cycles.
>>
>> This performance gain is over and above the query planner improvements
>> that
>> have also been made.  We are constantly looking for new ways to run
>> queries
>> and adding those ways into the query planner.  For example, in the
>> previous
>> release, we added a new way to evaluate IN operators with non-constant
>> right-hand-sides that was reported on this mailing list to make some
>> queries run 5 times faster.
>>
>> The 50% faster number above is not about better query plans.  This is 50%
>> faster at the low-level grunt work of moving bits on and off disk and
>> search b-trees.  We have achieved this by incorporating hundreds of
>> micro-optimizations.  Each micro-optimization might improve the
>> performance
>> by as little as 0.05%.  If we get one that improves performance by 0.25%,
>> that is considered a huge win.  Each of these optimizations is
>> unmeasurable
>> on a real-world system (we have to use cachegrind to get repeatable
>> run-times) but if you do enough of them, they add up.
>>
>> A full 10% of the performance gain has come since the previous release.
>> There have been a lot of changes.  All our tests pass, and we still have
>> 100% branch test coverage, so we are confident that we didn't break too
>> much.  But your testing is an important part of our quality process.
>> Please download a source archive or a DLL and give the latest alpha a
>> whirl, and let us know if you encounter any problems.
>>
>> P.S.:  Measurements were done using the "speedtest1 --size 5" workload on
>> Ubuntu 10.13 and gcc 4.8.1 with -Os.  YMMV.  Version 3.7.17 requires
>> 1432835574 CPU cycles and the 3.8.7 alpha requires just 953861485 CPU
>> cycles, as measured by cachegrind.
>>
>
> I don't know if folks have ever thank you, Dr. Hipp, for this wonderful
> gift to the world called SQLite.  I have become a legend in my own world
> with this tool. :-)  I do have to say that I have used it since 2006 and it
> has increased in speed every year.  Thank you!  Thank you!  And in my own
> native language, muchas gracias!
>
> josé
> ___
> 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] How preserve the string metric in an extension.

2014-09-19 Thread Scott Robison

On Fri, Sep 19, 2014 at 5:56 PM, Keith Medcalf  wrote:

>
> >While you are correct that the action is "nonsensical" based on the
> >definition of the API, surely you can concede that in an environment (C
> >programming) where "argv" is an intrinsic part of every program thanks to
> >that name's standard use as a parameter to main
>
> But that is because you (meaning the person who wrote the main function)
> have declared the symbolic names for the arguments to the function "main"
> to be those names.  The names themselves are meaningless.  Are you
> suggesting that if you ran a global search and replace of the web and all
> printed materials that replaced:
>
> int main(int argc, char *argv[]) -> int main(int urinals, char
> *urinalcake[])
>
> that unrinals and urinalcake's would thereby get magic status, and that
> people would start calling "arguments to functions" "urinalcakes" instead
> of arguments?  Does that mean that every usage of the word "urinals" refers
> to the count of the argument strings to the function main?
>

I know this. You know this. I know it can be named any valid identifier. Of
course I'm not suggesting that. I'm saying that the inexperienced
programmer might not understand that and that a kind response vs a
belittling response is probably not such a bad course of action to take.
When one is accustomed to seeing "argv" all the time in only a single
context, I think it is understandable that they might jump to the
(incorrect in this case) conclusion that argv is a char**.

> There is no magic associated with a symbol name in C -- it is simply what
> you chose to call it.  Just because you chose to call something "argc" does
> not mean that it is a count of anything (much less a count of arguments),
> nor even that it is a default width integer; nor does calling something an
> argv make it an array of pointers to character strings.
>

There is something "magic" about main in standard C / C++ hosted
environments (because it is what the standard library wants to jump to
after running the library startup code). I dare say most developers have
not read the standards documents and thus don't know or understand the
exact difference between "hosted" and "free standing" environments or why
main is used "everywhere" except (practically) for Windows GUI apps which
need a WinMain instead of main.

> >, and where it is understood to be defined as "char**" or equivalent,
> >one might be forgiven for not quite understanding the difference.
>
> No THE PERSON TYPING THE CODE declared it as a char**.  (Of course, the
> actual type of the argument strings passed to the main function of a C
> program is char*[], not char**, so you are technically using an incorrect
> declaration and have made an incorrect assumption about the meaning of argv
> anyway).
>

Please carefully re-read my comment: 'defined as "char**" or equivalent'.
"char** argv" is the same as "char* argv[]" in every way except for
spelling.

Anyway, sorry for opening such a can of worms. You'll note I haven't made
these types of comments with regard to people asking questions that seem
obviously to be for homework problems or for deep explanations of the
innards of exactly how VDBE works. In this case I didn't think the question
asked warranted the type of response I read. No more or less. Take care.

-- 
Scott Robison

On Fri, Sep 19, 2014 at 5:56 PM, Keith Medcalf  wrote:

>
> >While you are correct that the action is "nonsensical" based on the
> >definition of the API, surely you can concede that in an environment (C
> >programming) where "argv" is an intrinsic part of every program thanks to
> >that name's standard use as a parameter to main
>
> But that is because you (meaning the person who wrote the main function)
> have declared the symbolic names for the arguments to the function "main"
> to be those names.  The names themselves are meaningless.  Are you
> suggesting that if you ran a global search and replace of the web and all
> printed materials that replaced:
>
> int main(int argc, char *argv[]) -> int main(int urinals, char
> *urinalcake[])
>
> that unrinals and urinalcake's would thereby get magic status, and that
> people would start calling "arguments to functions" "urinalcakes" instead
> of arguments?  Does that mean that every usage of the word "urinals" refers
> to the count of the argument strings to the function main?
>
> There is no magic associated with a symbol name in C -- it is simply what
> you chose to call it.  Just because you chose to call something "argc" does
> not mean that it is a count of anything (much less a count of arguments),
> nor even that it is a default width integer; nor does calling something an
> argv make it an array of pointers to character strings.
>
> >, and where it is understood to be defined as "char**" or equivalent,
> >one might be forgiven for not quite understanding the difference.
>
> No THE PERSON TYPING THE CODE declared it as a char**.  (Of course, the
> actual type of the

Re: [sqlite] 50% faster than 3.7.17

2014-09-19 Thread jose isaias cabrera


"Richard Hipp" wrote...



The latest SQLite 3.8.7 alpha version (available on the download page
http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 release
from 16 months ago.  That is to say, it does 50% more work using the same
number of CPU cycles.

This performance gain is over and above the query planner improvements 
that
have also been made.  We are constantly looking for new ways to run 
queries
and adding those ways into the query planner.  For example, in the 
previous

release, we added a new way to evaluate IN operators with non-constant
right-hand-sides that was reported on this mailing list to make some
queries run 5 times faster.

The 50% faster number above is not about better query plans.  This is 50%
faster at the low-level grunt work of moving bits on and off disk and
search b-trees.  We have achieved this by incorporating hundreds of
micro-optimizations.  Each micro-optimization might improve the 
performance

by as little as 0.05%.  If we get one that improves performance by 0.25%,
that is considered a huge win.  Each of these optimizations is 
unmeasurable

on a real-world system (we have to use cachegrind to get repeatable
run-times) but if you do enough of them, they add up.

A full 10% of the performance gain has come since the previous release.
There have been a lot of changes.  All our tests pass, and we still have
100% branch test coverage, so we are confident that we didn't break too
much.  But your testing is an important part of our quality process.
Please download a source archive or a DLL and give the latest alpha a
whirl, and let us know if you encounter any problems.

P.S.:  Measurements were done using the "speedtest1 --size 5" workload on
Ubuntu 10.13 and gcc 4.8.1 with -Os.  YMMV.  Version 3.7.17 requires
1432835574 CPU cycles and the 3.8.7 alpha requires just 953861485 CPU
cycles, as measured by cachegrind.


I don't know if folks have ever thank you, Dr. Hipp, for this wonderful gift 
to the world called SQLite.  I have become a legend in my own world with 
this tool. :-)  I do have to say that I have used it since 2006 and it has 
increased in speed every year.  Thank you!  Thank you!  And in my own native 
language, muchas gracias!


josé 


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

[sqlite] 50% faster than 3.7.17

The latest SQLite 3.8.7 alpha version (available on the download page
http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 release
from 16 months ago.  That is to say, it does 50% more work using the same
number of CPU cycles.

This performance gain is over and above the query planner improvements that
have also been made.  We are constantly looking for new ways to run queries
and adding those ways into the query planner.  For example, in the previous
release, we added a new way to evaluate IN operators with non-constant
right-hand-sides that was reported on this mailing list to make some
queries run 5 times faster.

The 50% faster number above is not about better query plans.  This is 50%
faster at the low-level grunt work of moving bits on and off disk and
search b-trees.  We have achieved this by incorporating hundreds of
micro-optimizations.  Each micro-optimization might improve the performance
by as little as 0.05%.  If we get one that improves performance by 0.25%,
that is considered a huge win.  Each of these optimizations is unmeasurable
on a real-world system (we have to use cachegrind to get repeatable
run-times) but if you do enough of them, they add up.

A full 10% of the performance gain has come since the previous release.
There have been a lot of changes.  All our tests pass, and we still have
100% branch test coverage, so we are confident that we didn't break too
much.  But your testing is an important part of our quality process.
Please download a source archive or a DLL and give the latest alpha a
whirl, and let us know if you encounter any problems.

P.S.:  Measurements were done using the "speedtest1 --size 5" workload on
Ubuntu 10.13 and gcc 4.8.1 with -Os.  YMMV.  Version 3.7.17 requires
1432835574 CPU cycles and the 3.8.7 alpha requires just 953861485 CPU
cycles, as measured by cachegrind.
-- 
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] sqlite3 hangs

2014-09-19 Thread Roger Binns

On 19/09/14 17:40, Bokassa wrote:
> Hi all,
>   I see my query hanging with this stack:
> 
> dybagme->where
> #0  0x00332b00ee00 in __fsync_nocancel () from /lib64/libpthread.so.0
> #1  0x0041b418 in full_fsync (fd=6, fullSync=0, dataOnly=0) at
> sqlite3.c:27735
...
> The program itself is in state D waiting for IO. I am writing on a local
> disk. I don't use threads.
> What can be wrong?

SQLite isn't wrong.  It uses the fsync api provided by Unix to ensure
file/directory contents are written to the storage media (ie would still be
there after a power failure).

You'll need to look in your OS logs and monitoring tools to find out what is
going on.  Note that some filesystems are implemented such that asking to
fsync a file instead does a sync of the whole filesystem (it is done because
of metadata and journalling issues).  For example if you were separately
extracting lots of files they could get caught up in the fsync.

The spec for fsync is that it doesn't return until the file/directory is on
storage.

Roger

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

[sqlite] sqlite3 hangs

2014-09-19 Thread Bokassa

Hi all,
  I see my query hanging with this stack:

dybagme->where
#0  0x00332b00ee00 in __fsync_nocancel () from /lib64/libpthread.so.0
#1  0x0041b418 in full_fsync (fd=6, fullSync=0, dataOnly=0) at
sqlite3.c:27735
#2  0x0041b54c in unixSync (id=0x2315650, flags=2) at
sqlite3.c:27824
#3  0x00411c7c in sqlite3OsSync (id=0x2315650, flags=2) at
sqlite3.c:15816
#4  0x004243c8 in syncJournal (pPager=0x23153f8, newHdr=0) at
sqlite3.c:44399
#5  0x00426829 in sqlite3PagerCommitPhaseOne (pPager=0x23153f8,
zMaster=0x0, noSync=0) at sqlite3.c:46451
#6  0x0042f967 in sqlite3BtreeCommitPhaseOne (p=0x23157b8,
zMaster=0x0) at sqlite3.c:54827
#7  0x0043da85 in vdbeCommit (db=0x23150c8, p=0x234c548) at
sqlite3.c:64185
#8  0x0043e48b in sqlite3VdbeHalt (p=0x234c548) at sqlite3.c:64592
#9  0x00443f38 in sqlite3VdbeExec (p=0x234c548) at sqlite3.c:68930
#10 0x00441729 in sqlite3Step (p=0x234c548) at sqlite3.c:66694
#11 0x0044191d in sqlite3_step (pStmt=0x234c548) at sqlite3.c:66760
#12 0x0047362a in sqlite3_exec (db=0x23150c8, zSql=0x6fa820
 "update vms set pm_id=41,pm_name='s_40' where vm_id=161",
xCallback=0x0, pArg=0x0, pzErrMsg=0x6f6808 ) at sqlite3.c:96874
#13 0x0040b830 in db_update_vm_pm (pm=0x232fbf0, vm=0x234a110) at
pack.db.c:653
#14 0x0040b4fa in db_record_sched_decision (link=0x2328150) at
pack.db.c:580
#15 0x0040849b in place () at pack.place.c:182
#16 0x004051a5 in try_place () at pack.c:241
#17 0x00405022 in run () at pack.c:184
#18 0x00404d7d in main (argc=1, argv=0x7fffacdedb48) at pack.c:88

it is processing things in a loop and updating. If I do the updates in a
transaction it returns no error
but the database is not updated.

The program itself is in state D waiting for IO. I am writing on a local
disk. I don't use threads.
What can be wrong?

Thanks.

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

Re: [sqlite] How preserve the string metric in an extension.

2014-09-19 Thread Igor Tandetnik


On 9/19/2014 7:56 PM, Keith Medcalf wrote:

the actual type of the argument strings passed to the main function of a C 
program is char*[], not char**


It's valid either way.

C99 6.7.5.3/7 A declaration of a parameter as "array of type" shall be 
adjusted to "qualified pointer to type"...


And even more explicitly:

5.1.2.2.1/1 The function called at program startup is named main. It 
shall be defined with a return type of int and ... with two parameters...:

int main(int argc, char *argv[]) { /* ... */ }
or equivalent;9)...
Footnote 9: Thus, int can be replaced by a typedef name defined as int, 
or the type of argv can be written as char ** argv, and so on.


--
Igor Tandetnik

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

Re: [sqlite] How preserve the string metric in an extension.


>While you are correct that the action is "nonsensical" based on the
>definition of the API, surely you can concede that in an environment (C
>programming) where "argv" is an intrinsic part of every program thanks to
>that name's standard use as a parameter to main

But that is because you (meaning the person who wrote the main function) have 
declared the symbolic names for the arguments to the function "main" to be 
those names.  The names themselves are meaningless.  Are you suggesting that if 
you ran a global search and replace of the web and all printed materials that 
replaced:

int main(int argc, char *argv[]) -> int main(int urinals, char *urinalcake[])

that unrinals and urinalcake's would thereby get magic status, and that people 
would start calling "arguments to functions" "urinalcakes" instead of 
arguments?  Does that mean that every usage of the word "urinals" refers to the 
count of the argument strings to the function main?

There is no magic associated with a symbol name in C -- it is simply what you 
chose to call it.  Just because you chose to call something "argc" does not 
mean that it is a count of anything (much less a count of arguments), nor even 
that it is a default width integer; nor does calling something an argv make it 
an array of pointers to character strings.

>, and where it is understood to be defined as "char**" or equivalent, 
>one might be forgiven for not quite understanding the difference. 

No THE PERSON TYPING THE CODE declared it as a char**.  (Of course, the actual 
type of the argument strings passed to the main function of a C program is 
char*[], not char**, so you are technically using an incorrect declaration and 
have made an incorrect assumption about the meaning of argv anyway).




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

Re: [sqlite] How preserve the string metric in an extension.

2014-09-19 Thread Scott Robison

On Fri, Sep 19, 2014 at 3:08 PM, Igor Tandetnik  wrote:

> On 9/19/2014 4:36 PM, Andrea Peri wrote:
>
>>
>>  I don't understand why this difference
>>
>
> I don't understand why you expected there to *not* be a difference between
> a program that performs a nonsensical action, and a program that uses the
> API in accordance with the documentation.

While you are correct that the action is "nonsensical" based on the
definition of the API, surely you can concede that in an environment (C
programming) where "argv" is an intrinsic part of every program thanks to
that name's standard use as a parameter to main, and where it is understood
to be defined as "char**" or equivalent, one might be forgiven for not
quite understanding the difference. Not everyone has the same understanding
of the SQLite API or C programming. I recall asking some really newbie
questions as a less experienced programmer.

I can appreciate why people on the list will get frustrated by people
asking questions without even trying to help themselves first. This
particular question seems like an honest misunderstanding of what argv
meant, not someone trying to get free homework "assistance" or software
consulting.

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

Re: [sqlite] How preserve the string metric in an extension.

2014-09-19 Thread Igor Tandetnik


On 9/19/2014 4:36 PM, Andrea Peri wrote:

printf("%s",argv[0]);


argv[0] is sqlite_value*, not a char*. It's not a pointer to a 
NUL-terminated string, which is what %s format specifier expects.



Instead If I use this other

printf("%s",sqlite3_value_text(argv[0]));

I have correctly
"aaa"


So what seems to be the problem then?


I don't understand why this difference


I don't understand why you expected there to *not* be a difference 
between a program that performs a nonsensical action, and a program that 
uses the API in accordance with the documentation.

--
Igor Tandetnik

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

Re: [sqlite] How preserve the string metric in an extension.

2014-09-19 Thread Andrea Peri

Hi, thx for clarification.

I do no change UTF8-UTF16 (I hope).

I was guessing the sqlite3_value_text was changing because I does this
simple test:

try-ing passing "aaa" to the function and try-ing a

printf("%s",argv[0]);

inside the function I have on stdout something like:

 "P^. "

Instead If I use this other

printf("%s",sqlite3_value_text(argv[0]));

I have correctly
"aaa"

I don't understand why this difference so my only explanation was that
the sqlite was changing.

A.



2014-09-19 22:25 GMT+02:00 Richard Hipp :
>
>
> On Fri, Sep 19, 2014 at 4:22 PM, Andrea Peri  wrote:
>>
>> Hi,
>> I'm trying to implement a sqlite extension using some string metric
>> algorithms.
>>
>> My goal is to compare two strings and retrieve the metric.
>>
>> I understand that to use the argv[0] and argv[1] args inside an
>> extension function is need to apply to them the
>> sqlite3_value_text(...) function.
>>
>> Unfortunately seem that with this approach in some situations the
>> metric of the strings change.
>>
>> I see this comparing the results frominside the sqlite extension with
>> thesame algorithm in a standalone sample program.
>>
>> I don't know well why is happening this but I guess it is due to the
>> sqlite3_value_text(..)
>>
>> Is this possible ? And there is an alternative approach to avoid to
>> use the sqlite3_value_text(...) ?
>
>
> Unless you are doing UTF8<->UTF16 conversions, the output of
> sqlite3_value_text() will be exactly, bit-for-bit, what you put in.  Except
> for doing conversions between UTF8 and UTF16, SQLite never changes the text
> in any way.
>
> --
> D. Richard Hipp
> d...@sqlite.org



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How preserve the string metric in an extension.

On Fri, Sep 19, 2014 at 4:22 PM, Andrea Peri  wrote:

> Hi,
> I'm trying to implement a sqlite extension using some string metric
> algorithms.
>
> My goal is to compare two strings and retrieve the metric.
>
> I understand that to use the argv[0] and argv[1] args inside an
> extension function is need to apply to them the
> sqlite3_value_text(...) function.
>
> Unfortunately seem that with this approach in some situations the
> metric of the strings change.
>
> I see this comparing the results frominside the sqlite extension with
> thesame algorithm in a standalone sample program.
>
> I don't know well why is happening this but I guess it is due to the
> sqlite3_value_text(..)
>
> Is this possible ? And there is an alternative approach to avoid to
> use the sqlite3_value_text(...) ?
>

Unless you are doing UTF8<->UTF16 conversions, the output of
sqlite3_value_text() will be exactly, bit-for-bit, what you put in.  Except
for doing conversions between UTF8 and UTF16, SQLite never changes the text
in any way.

-- 
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] How preserve the string metric in an extension.

2014-09-19 Thread Andrea Peri

Hi,
I'm trying to implement a sqlite extension using some string metric algorithms.

My goal is to compare two strings and retrieve the metric.

I understand that to use the argv[0] and argv[1] args inside an
extension function is need to apply to them the
sqlite3_value_text(...) function.

Unfortunately seem that with this approach in some situations the
metric of the strings change.

I see this comparing the results frominside the sqlite extension with
thesame algorithm in a standalone sample program.

I don't know well why is happening this but I guess it is due to the
sqlite3_value_text(..)

Is this possible ? And there is an alternative approach to avoid to
use the sqlite3_value_text(...) ?

Thx,

-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

2014-09-19 Thread Simon Slavin

On 19 Sep 2014, at 7:42pm, Roger Binns  wrote:

> On 19/09/14 07:58, James K. Lowden wrote:
>> I wonder what "problems" you're talking about.  Do you think the IRS,
>> the Social Security Administration, the DMV, the passport agency, your
>> birth certificate, and your local bank are just doing it wrong? 
> 
> You do realise there are more people in the US than just those born in the
> country with good old fashioned roman alphabet 26 ascii letters?  And yes
> they do get it wrong:

No, no he's just working on US Pulp Magazines.  All pulp writers have 
traditional names.  He's not going to have any trouble.

Except, of course, with Daniel Keys Moran.  Who doesn't use his first name 
except when writing.  And Marion Zimmer Bradley, whose name should be sorted 
under 'Z'.  Though, of course, Edgar Rice Burroughs should be sorted under 'B'. 
 And Margaret St. Clair whose name is correctly pronounced and sorted as if it 
was Margaret Sinclair.

And Lester del Rey.  Whose name should be sorted under 'd' (his wife was 
Judy-Lynn del Rey).  And who normally claimed (falsely) that his name was Ramon 
Felipe San Juan Mario Silvio Enrico Smith Heartcourt-Brace Sierra y Alvarez del 
Rey y de los Uerdes.

Of course, the author Eando Binder was actually two people (Edward and Oscar 
Binder) but that doesn't count.

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

Re: [sqlite] An order by problem, maybe a bug?

2014-09-19 Thread Roger Binns

On 19/09/14 07:58, James K. Lowden wrote:
> I wonder what "problems" you're talking about.  Do you think the IRS,
> the Social Security Administration, the DMV, the passport agency, your
> birth certificate, and your local bank are just doing it wrong? 

You do realise there are more people in the US than just those born in the
country with good old fashioned roman alphabet 26 ascii letters?  And yes
they do get it wrong:

  http://www.usatoday.com/story/news/nation/2013/09/13/long-last-name/2810603/

Or try having the name Chloé in the US.  Heck my friend Stephane had a hard
enough time as everyone assumed he couldn't spell his own name, and
corrected it to Stephanie!  And his name is actually Stéphane, but don't
think any of those agencies you listed would acknowledge that.

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

Re: [sqlite] An order by problem, maybe a bug?

2014-09-19 Thread John McKown

On Fri, Sep 19, 2014 at 11:40 AM, Simon Slavin  wrote:
>
> On 19 Sep 2014, at 3:58pm, James K. Lowden  wrote:
>
>> On Fri, 19 Sep 2014 02:02:30 +0100
>> Simon Slavin  wrote:
>>
>>> By the way I wanted to warn you about starting any project with first
>>> name, middle name and last name fields.  This leads to problems, and
>>> I would go to some lengths to avoid it if possible.  It would be
>>> better to provide two columns:
>>>
>>> name(their name, however they want it to be shown)
>>> nameInSortOrder (their name, in whatever order you feel it should be
>>> sorted)
>>
>> I wonder what "problems" you're talking about.
>
> First, if you automatically generate letters, a letter to "Patrick Nielsen 
> Hayden" is going to start "Dear Mr/Mrs Hayden". That's incorrect.  His 
> surname is "Nielsen Hayden".  Similarly a letter to "Edward Fitz William" 
> should be addressed "Dear Mr Fitz William".
>
> Then, if you sort the names into order, you're going to find "Patrick Nielsen 
> Hayden" under H, whereas you should find him under N.
>

This is an interesting problem. Which the Unicode people addressed
with the "non-breaking space" character of U+00A0. Of course, entering
this particular space character is not as simple as just hitting the
space bar.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

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

Re: [sqlite] An order by problem, maybe a bug?

2014-09-19 Thread Simon Slavin

On 19 Sep 2014, at 3:58pm, James K. Lowden  wrote:

> On Fri, 19 Sep 2014 02:02:30 +0100
> Simon Slavin  wrote:
> 
>> By the way I wanted to warn you about starting any project with first
>> name, middle name and last name fields.  This leads to problems, and
>> I would go to some lengths to avoid it if possible.  It would be
>> better to provide two columns:
>> 
>> name(their name, however they want it to be shown)
>> nameInSortOrder (their name, in whatever order you feel it should be
>> sorted)
> 
> I wonder what "problems" you're talking about.

First, if you automatically generate letters, a letter to "Patrick Nielsen 
Hayden" is going to start "Dear Mr/Mrs Hayden". That's incorrect.  His surname 
is "Nielsen Hayden".  Similarly a letter to "Edward Fitz William" should be 
addressed "Dear Mr Fitz William".

Then, if you sort the names into order, you're going to find "Patrick Nielsen 
Hayden" under H, whereas you should find him under N.

> Problems arising from the schema you suggest:
> 
> 1.  select by last name
> 2.  select by first name
> 3.  duplicate detection[1]
> 4.  "however they want" is unknown and idiosyncratic
> 5.  "whatever order" may be more than one

For 1, why are you selecting by last name ?  The person I named above has the 
surname "Nielsen Hayden".  Which would you be selecting and why ?

For 2, match the string entered with the beginning of the 'name' field, 
probably using LIKE and a percent character.

For 3, check to see if either field is a duplicate.

For 4, type in their name however they wrote it on the form you're copying.

For 5, are you telling me you don't know how you want the name sorted ?  In 
that case, perhaps there's no need to retain a sorting field at all.

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

Re: [sqlite] An order by problem, maybe a bug?


Most systems that encompass non-western style names will use different terms:

formalname and familyname in preference to firstname and lastname.  FirstName, 
MiddleName, LastName imply ordering which does not necessarily hold.  Calling 
them FormalName, AncestorName, FamilyName more aptly describes the use of the 
various bits without implying the ordering.  Then a CommonName field is used to 
hold the display variant as the person would usually prefer to see it (which 
may be Formal Ancestor Family as in western style names, Family Formal Ancestor 
as in most Eastern style names, or Family Ancestor Formal as used in some other 
Eastern cultures).  Often a FamiliarName is provided so that the name by which 
a person wants to be addressed can be stored.  This is necessary even for 
proper handling of western names where, for example, someone might want to use:

formalname = Robert
ancestorname = Richard
familyname = Smith
familliarName = Dick
CommonName = R Dick Smith

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of James K. Lowden
>Sent: Friday, 19 September, 2014 08:58
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] An order by problem, maybe a bug?
>
>On Fri, 19 Sep 2014 02:02:30 +0100
>Simon Slavin  wrote:
>
>> By the way I wanted to warn you about starting any project with first
>> name, middle name and last name fields.  This leads to problems, and
>> I would go to some lengths to avoid it if possible.  It would be
>> better to provide two columns:
>>
>> name(their name, however they want it to be shown)
>> nameInSortOrder (their name, in whatever order you feel it should be
>> sorted)
>
>I wonder what "problems" you're talking about.  Do you think the IRS,
>the Social Security Administration, the DMV, the passport agency, your
>birth certificate, and your local bank are just doing it wrong?  I've
>filled out address forms in four languages I can think of, and every
>one had a place for my first name and last name.  I've never seen one
>that seems like it would present a problem for "Patrick McKenzie".
>
>Problems arising from the schema you suggest:
>
>1.  select by last name
>2.  select by first name
>3.  duplicate detection[1]
>4.  "however they want" is unknown and idiosyncratic
>5.  "whatever order" may be more than one
>
>If you print a list of "however they want" ordered by "whatever order",
>the result will look very strange and be hard to use, because the sort
>order will not be apparent in the listing.
>
>The issues raised in the linked page apply mostly to application
>development.  The database's job is to capture the facts.  Just get the
>person's name -- in its discrete parts -- in the database and be done
>with it.
>
>--jkl
>
>[1] Yes, I know names aren't unique.  Duplicate detection isn't only
>about rejection; it's also about noticing similarity to avoid, say,
>assigning a new identifier to the same person.
>___
>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] Atomic database structure initialization


There is also a PRAGMA user_version (see 
http://www.sqlite.org/pragma.html#pragma_schema_version) which will let you 
store a number in the database header so you can keep track of what version of 
the "user schema" you have implemented in the database.  Initially, when the 
database is created empty, the value is zero.

pragma user_version;

returns a single row with a single value which is the version, and the command,

pragma user_version=n;

lets you change it to n.  Perhaps you can use this as a flag to tell yourself 
that you are working with an uninitialized database (value is 0), that another 
process is updating the database schema (change it to -1), or that it is at 
some internal revision number known to your program (it has a value > 0) such 
that you do not need to change the schema or initialize it.

>> On 19 Sep 2014, at 8:34am, Paul  wrote:
>>
>> > if database file is missing it must be created and initialized.
>> > For that purpose I need to provide a guarantee that *on_create*
>callback will be called strictly once.
>>
>> Can you check to see whether the database file exists using file
>operations, not SQLite operations ?
>>
>> SQLite doesn't usually cope with the problem the way you described.
>It's more usual to use the form
>>
>> CREATE myTable/myIndex IF NOT EXISTS ...
>>
>> that way if the datbase file already exists and has the right
>structures in it, nothing is changed.
>>
>> Simon.
>
>I can. I think I ough to. You know, I must also provide locking to avoid
>races.
>So I was just wondering if this is a common problem among sqlite users
>and if there are tools for that.
>
>Note: I cannot wrap *on_create* in transaction because I want to allow
>user who
>specifies his  own *on_create* callback to be able to use transactions
>there.
>
>So the only solution I see not is to use sqlite3_opev_v2() with out
>SQLITE_OPEN_CREATE.
>When database is missing I create lock file *db_name*.lock, lock it for
>writing,
>check if lock file is not unlinked, perform init, unlink lock file and
>unlock.
>
>What I hate is the fact that in the middle between 'create lock file' and
>'unlink lock file'
>application may crash and leave trash behind. The worst still, It may
>leave database file
>in uninited state. Of course I can init temporary database first and then
>rename upon succes.
>But this still leaves the possibility of temporary database to hang
>around as trash.
>
>So my motivation was to check if community has some standard solutions.
>
>Thanks.
>
>___
>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] An order by problem, maybe a bug?

2014-09-19 Thread James K. Lowden

On Fri, 19 Sep 2014 02:02:30 +0100
Simon Slavin  wrote:

> By the way I wanted to warn you about starting any project with first
> name, middle name and last name fields.  This leads to problems, and
> I would go to some lengths to avoid it if possible.  It would be
> better to provide two columns:
> 
> name(their name, however they want it to be shown)
> nameInSortOrder (their name, in whatever order you feel it should be
> sorted)

I wonder what "problems" you're talking about.  Do you think the IRS,
the Social Security Administration, the DMV, the passport agency, your
birth certificate, and your local bank are just doing it wrong?  I've
filled out address forms in four languages I can think of, and every
one had a place for my first name and last name.  I've never seen one
that seems like it would present a problem for "Patrick McKenzie". 

Problems arising from the schema you suggest:

1.  select by last name
2.  select by first name
3.  duplicate detection[1]
4.  "however they want" is unknown and idiosyncratic
5.  "whatever order" may be more than one

If you print a list of "however they want" ordered by "whatever order",
the result will look very strange and be hard to use, because the sort
order will not be apparent in the listing.  

The issues raised in the linked page apply mostly to application
development.  The database's job is to capture the facts.  Just get the
person's name -- in its discrete parts -- in the database and be done
with it.  

--jkl

[1] Yes, I know names aren't unique.  Duplicate detection isn't only
about rejection; it's also about noticing similarity to avoid, say,
assigning a new identifier to the same person.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Number of registers/Mem in sqlite Vdbe

On Fri, Sep 19, 2014 at 2:04 AM, Prakash Premkumar 
wrote:

> Can you kindly tell me where in the source code is the number of registers
> for Vdbe allocated ?
>

The Parse.nMem variable keeps track of the number of registers required.
After all code is generated, the sqlite3VdbeMakeReady() routine allocates
and initializes memory to hold those registers.

>
> Thanks
> Prakash
> ___
> 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] Atomic database structure initialization

2014-09-19 Thread Clemens Ladisch

Paul wrote:
>> Paul wrote:
>>> How do you check if structure is initializad in an abstract databse?
>
> struct SqliteDatabase {
> ...
>
> /// Callback is called once database is created. Strictly one time.
> virtual bool on_create();
> ...
> };
>
> struct FooDatabase : public SqliteDatabase
> {
> bool on_create()
> {
> return EXEC_SQL("CREATE TABLE foo (id INTEGER);");
> }
>
> };

SQLiteDatabase::open(filename) // or constructor
{
sqlite3_open_v2(...filename...);
EXEC_SQL("BEGIN");
if (database_is_empty())
on_create();
EXEC_SQL("COMMIT");
// error handling omitted
}

> Note: I cannot wrap *on_create* in transaction because I want to allow user 
> who
> specifies his  own *on_create* callback to be able to use transactions there.
> ... When database is missing I create lock file ...

So you are creating *another* transaction mechanism on top of the
existing one?

The important thing is that the if() and the on_create() _must_ execute
atomically.  The easiest way to do this is to use the existing
transaction mechanism.

If you want to allow transactions in on_create(), you should implement
nested transactions, i.e., your own begin()/etc. functions issue SQL
commands only at the outermost level, and let that transaction succeed
only if all nested transactions succeeded.

> So my motivation was to check if community has some standard solutions.

As it happens, this is pretty much the mechanism used by Android's
SQLiteOpenHelper class.

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

Re: [sqlite] Atomic database structure initialization

> 
> On 19 Sep 2014, at 8:34am, Paul  wrote:
> 
> > if database file is missing it must be created and initialized. 
> > For that purpose I need to provide a guarantee that *on_create* callback 
> > will be called strictly once. 
> 
> Can you check to see whether the database file exists using file operations, 
> not SQLite operations ?
> 
> SQLite doesn't usually cope with the problem the way you described. It's more 
> usual to use the form
> 
> CREATE myTable/myIndex IF NOT EXISTS ...
> 
> that way if the datbase file already exists and has the right structures in 
> it, nothing is changed.
> 
> Simon.

I can. I think I ough to. You know, I must also provide locking to avoid races.
So I was just wondering if this is a common problem among sqlite users
and if there are tools for that.

Note: I cannot wrap *on_create* in transaction because I want to allow user who 
specifies his  own *on_create* callback to be able to use transactions there.

So the only solution I see not is to use sqlite3_opev_v2() with out 
SQLITE_OPEN_CREATE.
When database is missing I create lock file *db_name*.lock, lock it for writing,
check if lock file is not unlinked, perform init, unlink lock file and unlock.

What I hate is the fact that in the middle between 'create lock file' and 
'unlink lock file'
application may crash and leave trash behind. The worst still, It may leave 
database file
in uninited state. Of course I can init temporary database first and then 
rename upon succes.
But this still leaves the possibility of temporary database to hang around as 
trash.

So my motivation was to check if community has some standard solutions.

Thanks.

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

Re: [sqlite] Atomic database structure initialization

2014-09-19 Thread Simon Slavin

On 19 Sep 2014, at 8:34am, Paul  wrote:

> if database file is missing it must be created and initialized. 
> For that purpose I need  to provide a guarantee that *on_create* callback 
> will be called strictly once. 

Can you check to see whether the database file exists using file operations, 
not SQLite operations ?

SQLite doesn't usually cope with the problem the way you described.  It's more 
usual to use the form

CREATE myTable/myIndex IF NOT EXISTS ...

that way if the datbase file already exists and has the right structures in it, 
nothing is changed.

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

Re: [sqlite] Atomic database structure initialization



> Paul wrote:
> >> Paul wrote:
> >>> My goal is to make structure initialization of an *abstract* database 
> >>> atomic.
> >>> [...] if database file is missing it must be created and initialized.
> >>
> >> 
> >>
> >> Just do the check for the database structure and the initialization inside
> >> a transaction.
> >
> > Yeah, but you didn't read my statements carefully. I was talking about an 
> > abstract database.
> > It's wrappers responsibility to call *on_create* so it's wrappers 
> > responsibility to check.
> > How do you check if structure is initializad in an abstract databse?
> 
> By calling a non-abstract function?
> 
> I fear this description is too abstract to be useful.
> Please show some concrete (pseudo)code.

What non-abstract function do you mean? I think the fact that it's a callback 
means abstraction.

Ahyway, what I mean is (in C++):

struct SqliteDatabase {
...

/// Callback is called once database is created. Strictly one time.
virtual bool on_create();
...
};

struct FooDatabase : public SqliteDatabase
{
bool on_create()
{
return EXEC_SQL("CREATE TABLE foo (id INTEGER);");
}

};


...or something like that

> 
> >>> SQLITE_OPEN_EXCLUSIVE
> >>
> >> This flag does not mean what you think it means, and is used only
> >> internally; you are not allowed to use it.
> >
> > Please explain, why am I not allowed to use it in sqlite3_open_v2()?
> 
> That's what the documentation says ():
> | The flags parameter to sqlite3_open_v2() can take one of the following
> | three values, optionally combined with the SQLITE_OPEN_NOMUTEX,
> | SQLITE_OPEN_FULLMUTEX, SQLITE_OPEN_SHAREDCACHE, SQLITE_OPEN_PRIVATECACHE,
> | and/or SQLITE_OPEN_URI flags:
> | SQLITE_OPEN_READONLY [...]
> | SQLITE_OPEN_READWRITE [...]
> | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE [...]
> 
> ... and also what the code actually does:
> 
> /* Remove harmful bits from the flags parameter
> **
> ** The SQLITE_OPEN_NOMUTEX and SQLITE_OPEN_FULLMUTEX flags were
> ** dealt with in the previous code block. Besides these, the only
> ** valid input flags for sqlite3_open_v2() are SQLITE_OPEN_READONLY,
> ** SQLITE_OPEN_READWRITE, SQLITE_OPEN_CREATE, SQLITE_OPEN_SHAREDCACHE,
> ** SQLITE_OPEN_PRIVATECACHE, and some reserved bits. Silently mask
> ** off all other flags.
> */
> flags &= ~( SQLITE_OPEN_DELETEONCLOSE |
> SQLITE_OPEN_EXCLUSIVE |
> SQLITE_OPEN_MAIN_DB |
> SQLITE_OPEN_TEMP_DB |
> SQLITE_OPEN_TRANSIENT_DB |
> SQLITE_OPEN_MAIN_JOURNAL |
> SQLITE_OPEN_TEMP_JOURNAL |
> SQLITE_OPEN_SUBJOURNAL |
> SQLITE_OPEN_MASTER_JOURNAL |
> SQLITE_OPEN_NOMUTEX |
> SQLITE_OPEN_FULLMUTEX |
> SQLITE_OPEN_WAL
> );
> 

Hmm... I thought I can at least use this flag to know if this database 
connection is the creator of database :(

So, there are no standard means I guess?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

2014-09-19 Thread Dan Kennedy

On 09/19/2014 02:53 PM, Paul wrote:

Paul wrote:

My goal is to make structure initialization of an *abstract* database atomic.
[...] if database file is missing it must be created and initialized.

Just do the check for the database structure and the initialization inside
a transaction.

Yeah, but you didn't read my statements carefully. I was talking about an
abstract database.
It's wrappers responsibility to call *on_create* so it's wrappers
responsibility to check.
How do you check if structure is initializad in an abstract databse?

SQLITE_OPEN_EXCLUSIVE

This flag does not mean what you think it means, and is used only
internally; you are not allowed to use it.

Anyway, transactions are automatically exclusive.

I think I know what it means:

**The SQLITE_OPEN_EXCLUSIVE flag is always used in conjunction with the
SQLITE_OPEN_CREATE flag, which are both directly analogous to the O_EXCL and
O_CREAT flags of the POSIX open() API. The SQLITE_OPEN_EXCLUSIVE flag, when
paired with the SQLITE_OPEN_CREATE, is used to indicate that file should always
be created, and that it is an error if it already exists. It is not used to
indicate the file should be opened for exclusive access.**

This is from

Please explain, why am I not allowed to use it in sqlite3_open_v2()?

Because the docs for open_v2() state, somewhat ominously, that the
results will be undefined:

http://www.sqlite.org/c3ref/mark/open.html?The%20sqlite3_open_v2&If+the+3rd*undefined#mark

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

Re: [sqlite] Atomic database structure initialization

2014-09-19 Thread Clemens Ladisch

Paul wrote:
>> Paul wrote:
>>> My goal is to make structure initialization of an *abstract* database 
>>> atomic.
>>> [...] if database file is missing it must be created and initialized.
>>
>> 
>>
>> Just do the check for the database structure and the initialization inside
>> a transaction.
>
> Yeah, but you didn't read my statements carefully. I was talking about an 
> abstract database.
> It's wrappers responsibility to call *on_create* so it's wrappers 
> responsibility to check.
> How do you check if structure is initializad in an abstract databse?

By calling a non-abstract function?

I fear this description is too abstract to be useful.
Please show some concrete (pseudo)code.

>>> SQLITE_OPEN_EXCLUSIVE
>>
>> This flag does not mean what you think it means, and is used only
>> internally; you are not allowed to use it.
>
> Please explain, why am I not allowed to use it in sqlite3_open_v2()?

That's what the documentation says ():
| The flags parameter to sqlite3_open_v2() can take one of the following
| three values, optionally combined with the SQLITE_OPEN_NOMUTEX,
| SQLITE_OPEN_FULLMUTEX, SQLITE_OPEN_SHAREDCACHE, SQLITE_OPEN_PRIVATECACHE,
| and/or SQLITE_OPEN_URI flags:
| SQLITE_OPEN_READONLY [...]
| SQLITE_OPEN_READWRITE [...]
| SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE [...]

... and also what the code actually does:

  /* Remove harmful bits from the flags parameter
  **
  ** The SQLITE_OPEN_NOMUTEX and SQLITE_OPEN_FULLMUTEX flags were
  ** dealt with in the previous code block.  Besides these, the only
  ** valid input flags for sqlite3_open_v2() are SQLITE_OPEN_READONLY,
  ** SQLITE_OPEN_READWRITE, SQLITE_OPEN_CREATE, SQLITE_OPEN_SHAREDCACHE,
  ** SQLITE_OPEN_PRIVATECACHE, and some reserved bits.  Silently mask
  ** off all other flags.
  */
  flags &=  ~( SQLITE_OPEN_DELETEONCLOSE |
   SQLITE_OPEN_EXCLUSIVE |
   SQLITE_OPEN_MAIN_DB |
   SQLITE_OPEN_TEMP_DB |
   SQLITE_OPEN_TRANSIENT_DB |
   SQLITE_OPEN_MAIN_JOURNAL |
   SQLITE_OPEN_TEMP_JOURNAL |
   SQLITE_OPEN_SUBJOURNAL |
   SQLITE_OPEN_MASTER_JOURNAL |
   SQLITE_OPEN_NOMUTEX |
   SQLITE_OPEN_FULLMUTEX |
   SQLITE_OPEN_WAL
 );

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

Re: [sqlite] Atomic database structure initialization

> Paul wrote:
> > My goal is to make structure initialization of an *abstract* database 
> > atomic.
> > [...] if database file is missing it must be created and initialized.
> 
> 
> 
> Just do the check for the database structure and the initialization inside
> a transaction.

Yeah, but you didn't read my statements carefully. I was talking about an 
abstract database.
It's wrappers responsibility to call *on_create* so it's wrappers 
responsibility to check.
How do you check if structure is initializad in an abstract databse?

> 
> > SQLITE_OPEN_EXCLUSIVE
> 
> This flag does not mean what you think it means, and is used only
> internally; you are not allowed to use it.
> 
> Anyway, transactions are automatically exclusive.
> 

I think I know what it means:

**The SQLITE_OPEN_EXCLUSIVE flag is always used in conjunction with the 
SQLITE_OPEN_CREATE flag, which are both directly analogous to the O_EXCL and 
O_CREAT flags of the POSIX open() API. The SQLITE_OPEN_EXCLUSIVE flag, when 
paired with the SQLITE_OPEN_CREATE, is used to indicate that file should always 
be created, and that it is an error if it already exists. It is not used to 
indicate the file should be opened for exclusive access.**

This is from 

Please explain, why am I not allowed to use it in sqlite3_open_v2()?

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

Re: [sqlite] Atomic database structure initialization

2014-09-19 Thread Clemens Ladisch

Paul wrote:
> My goal is to make structure initialization of an *abstract* database atomic.
> [...] if database file is missing it must be created and initialized.

Just do the check for the database structure and the initialization inside
a transaction.

> SQLITE_OPEN_EXCLUSIVE

This flag does not mean what you think it means, and is used only
internally; you are not allowed to use it.

Anyway, transactions are automatically exclusive.

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

[sqlite] Atomic database structure initialization