Re: [sqlite] SEE encryption password
Here is a suggestion. You can select between decimal and hexadecimal output. https://nousrandom.net/randominteger/index.html - Scott Doctor sc...@scottdoctor.com - On 5/6/2019 13:20, Jens Alfke wrote: On May 5, 2019, at 11:04 PM, Birajendu Sahu wrote: I would like to generate a device specific key upon installation of the application. Same time I don't want to save the key in device memory too. For AES256, just generate 32 bytes (256 bits) of securely random data and use that as the key. (“Securely random” means do not use `rand` or `random`, rather a random number generator provided by the OS or a crypto library. This varies by platform. On macOS see SecRandom.h.) (Don’t try to do anything fancy to generate the key. There is nothing you can do that will give you a more-random, less-guessable key than your OS’s secure RNG.) The key should be stored in OS-provided secure storage. On macOS that’s the Keychain. I’m sure Windows and Android have some equivalent of that. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE
Try creating a fresh project, call it sqliteshell.exe The amalgamation zip has a file called shell.c Add the files shell.c, sqlite3.c, and sqlite3.h to the new project compile. See if you still get any errors. The shell.c program is the command line utility. See if you still get any errors. - Scott Doctor sc...@scottdoctor.com - On 12/21/2018 13:02, Larry Brasfield wrote: Zydeholic wrote: ➢ I compile and get one error: Severity Code Description Project File Line Suppression State Error LNK2001 unresolved external symbol _sqlite3_version sqlite_try_3 C:\Users\DSNoS\source\repos\sqlite_try_3\sqlite_try_3\sqlite3.obj 1 That symbol is declared, and a definition for the object so named is coded, in the sqlite3.c amalgamation without the possibility of omission by the preprocessor. So I find it exceedingly strange that your link operation is complaining of an unresolvable reference to that symbol in sqlite3.obj. For a C compilation, which you certainly should be using for that C source, the name should be undecorated, except for the leading underscore, just as it appears in the above-quoted error message. This leads me to believe you are doing something too strange for anybody to guess with the information provided so far. You may notice that this thread is misnamed for this latest difficulty, since the code does actually compile. If I had to name it accurately, it would be called: [off topic] Need build instructions for my project which uses SQLite in a development environment differing from the one actually supported by the SQLite team. Some questions to ask yourself as you attempt to sort this out: 1. Am I compiling the .c sources as C language? 2. Have I modified the sources everybody assumes are as released by the SQLite team? 3. What does insight does dumpbin.exe, (the VC command line tool for showing compiled image content), provide into my link errors? 4. How does my sqlite3.obj differ from the one I get following step 19 at https://www.sqlite.org/cli.html , and why? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE
Click the link. https://sqlite.org/download.html Download the amalgamation zip file. Unzip to your files directory. Should be two files. sqlite.c and sqlite.h Add #include "sqlite.h" to your file. Compile. ----- Scott Doctor sc...@scottdoctor.com - On 12/20/2018 15:34, Igor Korot wrote: Hi, Unfortunately you didn't tell what did you try to compile, where did you get the files you tried to compile from and you didn't even supply the error message you received from the compilation. Please follow-up and provide this and hopefully someone here will be able to help. Thank you. On Thu, Dec 20, 2018 at 5:17 PM Donald Shepherd wrote: You'll need to provide more information, speaking as someone who compiles the amalgamation off the SQLite website with VC++ 2017 on Windows 10 with no issues. SQLite is C code, not C++ code, but VC++ detects that based off the file extension and compiles it as such. Trying to use whatever "CPPSqlite3.cpp" is and adding C++ headers is not likely to get you anywhere unless you follow up with whoever created those files for assistance. Regards, Donald Shepherd. On Fri, 21 Dec 2018 at 09:41, zydeholic wrote: Hello folks, I looked through the last few months of posts in the archive, and no subject lines seemed to cover this. I've tried a couple of CPPSqlite3.cpp and .h from github.com.I've tried code from a couple of websites. NOTHING seems to compile all the way through. I'm using Visual Studio 2017 C++. I'm on a WIndows 10 machine. 64bit, but compiling 32 bit. I tried Code::Blocks and got different, but equally incomplete compiles. I've included in the includes. I've downloaded sqlite3.c and .h. Nothing seems to work. Is there ANYWHERE that offers a step-by-step solution to this that WILL COMPILE all the way through. I'm sure this has been covered before, but I did not see a way to search the entire archives. Any help appreciated. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite in C# without System.Data.SQLite
Why not just add the amalgmation to your source then do C function calss. I do not get why you would use a dll when you can just link in the amalgamtion into your program and have full access to the latest version. - Scott Doctor sc...@scottdoctor.com - ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange Corruption Issue
SSD's have a limited number of write cycles. You may have a failing SSD. Those are still, IMO, another 5-10 years before they solve the write lifetime reliabilty issue. - Scott Doctor sc...@scottdoctor.com - On 6/18/2018 20:15, Patrick Herbst wrote: I'm using sqlite in an embedded application, running on SSD. journal_mode=persist so that it is more resilient to loss of power. I'm seeing corruption. I'm using sqlite to log events on the system, and the corruption is well in the middle of a power session; not at the tail end of log when a power loss might occur. What i'm seeing is just a few pages corrupted with random bits being flipped. looking in a hex editor I can see the corrupted data, and where I can tell what values it SHOULD be, I see that they're wrong, but only by a single bit flip in random bytes here and there. for example a "A" is "a", or a "E" is "A". These are all changes of a single bit. there are far more examples... but in pretty much every case (even when RowID's are wrong) its just off by a bit. I'm using sqlite 3.7 (i know, old, but this this system is old). Has anyone else seen random bit flips? Any idea what could be causing it? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
Just out of curiosity, is the sqlite website using nginx or apache as the server? - Scott Doctor sc...@scottdoctor.com - ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Move to Github!!?
What fossil needs is for the UI to perform ALL normal common functions (new, commit, clone,...) WITHOUT having to open a command line window. That is imo the main limitation. - Scott Doctor sc...@scottdoctor.com - ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] random value get re-generated too often in SQLite
Is it possible that the first call to random is cached and the cached value is being returned in subsequent calls? - Scott Doctor sc...@scottdoctor.com - On 12/8/2017 12:09, John McKown wrote: On Fri, Dec 8, 2017 at 12:54 PM, John Mount <jmo...@win-vector.com> wrote: I am seeing an issue where a random value in a sub-query gets re-generated each time the value is used in later queries. Below is an example query: SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT * from ( VALUES(1),(2) ) ) a ) b One would expect r1 == r2. --- John Mount Interesting. I duplicated your example using PostgreSQL and it does what you said you expected: R1==R2. In SQLite3, the .eqp full shows: sqlite> .eqp full sqlite> SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT * from ( VALUES(1),(2) ) ) a ) b; --EQP-- 0,0,0,SCAN SUBQUERY 1 addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 0 000 Start at 0 1 InitCoroutine 1 7 200 sqlite_sq_5619D81F9BF8 2 Integer1 2 000 r[2]=1 3 Yield 1 0 000 4 Integer2 2 000 r[2]=2 5 Yield 1 0 000 6 EndCoroutine 1 0 000 7 InitCoroutine 1 0 200 8 Yield 1 13000 next row of "sqlite_sq_5619D81F9BF8" 9 Function0 0 0 4 random(0) 00 r[4]=func(r[0]) 10 Function0 0 0 5 random(0) 00 r[5]=func(r[0]) 11 ResultRow 4 2 000 output=r[4..5] 12Goto 0 8 000 13Halt 0 0 000 4548137244590923354 8821858240296964415 761559492082550893 2723588653195689097 I think this is being done due to the "subquery flattening" as described on http://sqlite.org/optoverview.html, which says: To overcome this problem, SQLite attempts to flatten subqueries in the FROM clause of a SELECT. This involves inserting the FROM clause of the subquery into the FROM clause of the outer query and rewriting expressions in the outer query that refer to the result set of the subquery. For example: So your "SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT * from ( VALUES(1),(2) ) ) a ) b;" becomes "SELECT random() AS r1, random() AS r2 FROM ( SELECT * from (values(1),(2)) a) b;" I think the above is born out if you put the word DISTINCT in front of the random() in the original example. This forces SQLite to _not_ use subquery flattening. Example: sqlite> SELECT r AS r1, r AS r2 FROM ( SELECT distinct random() AS r FROM ( SELECT * from ( VALUES(1),(2) ) c ) a ) b; --EQP-- 1,0,0,SCAN SUBQUERY 2 AS c --EQP-- 1,0,0,USE TEMP B-TREE FOR DISTINCT --EQP-- 0,0,0,SCAN SUBQUERY 1 AS b addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 0 000 Start at 0 1 InitCoroutine 1 18200 sqlite_sq_55E270A58EA8 2 InitCoroutine 2 8 300 sqlite_sq_55E270A58AA8 3 Integer1 3 000 r[3]=1 4 Yield 2 0 000 5 Integer2 3 000 r[3]=2 6 Yield 2 0 000 7 EndCoroutine 2 0 000 8 OpenEphemeral 3 0 0 k(1,B) 08 nColumn=0 9 InitCoroutine 2 0 300 10 Yield 2 17000 next row of "sqlite_sq_55E270A58AA8" 11 Function0 0 0 5 random(0) 00 r[5]=func(r[0]) 12 Found 3 165 1 00 key=r[5] 13 MakeRecord 5 1 600 r[6]=mkrec(r[5]) 14 IdxInsert 3 6 000 key=r[6] 15 Yield 1 0 000 16Goto 0 10000 17EndCoroutine 1 0 000 18InitCoroutine 1 0 200 19 Yield 1 24000 next row of "sqlite_sq_55E270A58EA8" 20 Copy 5 7 000 r[7]=r[5]; sqlite_sq_55E270A58EA8.r 21 Copy 7 8 000 r[8]=r[7] 22 ResultRow 7 2 000 output=r[7..8] 23Goto 0 190
Re: [sqlite] Problem with mailing list
Check your spam folder. Some messages get trapped from the list in there occassionally On September 6, 2017 1:28:21 AM PDT, Bart Smissaert <bart.smissa...@gmail.com> wrote: >For some reason it seems postings I send sometimes don't get through or >maybe they do get through but I can't see them. >I posted something yesterday at 12:55 pm (Problem on Windows 10 >machines) and I still can't see that posting on the list. >I mail from my normal GMail account. >Is there anything that I might be doing wrong? > >RBS >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users - - - - Scott Doctor sc...@scottdoctor.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert mysql to sqlite
The database evolved over the past ten years with many modifications over time. I decided to just do it the hard way. Created the schema from scratch in sqlite, exported each table as csv from mysql (22 of them). Mysql workbench only exports to csv at the table level, not at the database level. Then I imported the csv into the new sqlite database. Everything seems to have transferred properly that way and all the garbage from years gone by is scrubbed. - Scott Doctor sc...@scottdoctor.com - On 9/10/2016 14:39, Simon Slavin wrote: On 10 Sep 2016, at 10:24pm, Scott Doctor <sc...@scottdoctor.com> wrote: Anyone know of a utility to directly convert from MySQL to sqlite? What syntax does the SQL dump use that the SQLite command-line shell doesn't like ? I generally do this stuff using find/replace in a text processor (TextEdit on the Mac, NOTEPAD on Windows). Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Convert mysql to sqlite
I have a database with a few tables and about 140MB of data in it that exists as a MySQL database. Using MySQL workbench I can export the data, but it dumps as a SQL dump with its flavor of sql. I want to convert this database into a sqlite database. Nothing fancy in it. No triggers, procedures, or foreign keys. Tables are normal structure with a pk field and some text and integer fields in each table. Anyone know of a utility to directly convert from MySQL to sqlite? -- - Scott Doctor sc...@scottdoctor.com - ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Responsive" website revamp at www.sqlite.org
For my current website, I tested various techniques so that my website renders properly on a phone and a large desktop monitor. Most smart phones have a landscape resolution of at least 1024 pixels, most newer phones are much higher. I found that 950 pixel fixed centered width for a website page works on everything. A phone turned landscape can read 12 point common fonts at full width zoom. I find that using a fixed width instead of an adaptive width is better for phones. Especially when viewing the same website on a large desktop monitor. With the context centered, the website is not too wide which can be hard to read on a large screen, but fits onto a phone screen nicely. Another thing I found about phones with using fixed pages and fixed width text boxes versus dynamic width pages. Tapping the text zooms to the width of a text box. Tapping on a dynamic width box had mixed results as the phone operating system has to guess a zoom factor, which often resulted in over zooming requiring horizontal scrolling to read. I split the screen into vertical fourths. Navigation, notes, and such in the left quadrant, text boxes and other content in the other three quadrants. Following more along the lines of the current style of the sqlite website, the right most quadrant column for stuff such as the common links box with the page content in the first 3 quadrant columns. I think a fixed width instead of dynamic width works better with phones and other small devices. Newspapers use 4-6 columns on a page instead of stretching a line all the way across the page. It is easier for the eyes to track from line to line. I find wide width of the sqlite pages hard to read on my desktop monitors. Although I can shrink the width of the browser, I am usually working on other things when opening the page which need wider widths of the browser. People who mostly use phones for browsing the web are not using devices with a paltry 320x480 screen. scaling for a 950 width would work better. Fixed width content boxes seem to work better for phones as it gives better clues to the phone how to zoom. Tapping any text box will zoom to the width of that text box. Dynamic width boxes do not have those kind of clues. I laid out my entire website using the four quadrant vertical columns and fixed width text boxes, which seems to work well on various phones I tried, along with large desktop monitors. You can see how it works here> https://nousrandom.net/index.html ----- Scott Doctor sc...@scottdoctor.com - On 9/5/2016 13:55, Richard Hipp wrote: Most of the world views the internet on their phone now, I am told, and websites are suppose to be "responsive", meaning that they reformat themselves to be attractive and useful for the majority who view them through a 320x480 pixel soda-straw. In an effort to conform to this trend, I have made some changes to the *draft* SQLite website (http://sqlite.org/draft) Your feedback on these changes is appreciated. Please be sure to try out the new design both on a narrow-screen phone and on a traditional desktop browser. The goal is to provide a more mobile-friendly website without reducing the information content available to desktop users. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] json arrays
Regarding json format, assume a list of numbers as a json array.
Should numbers in a json array have quotes around the numbers?
For example:
{"result":["1.23","2.34","3.45"]}
or
{"result":[1.23,2.34,3.45]}
--
Scott Doctor
sc...@scottdoctor.com
--
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT ... FOR UPDATE
My current project has me bouncing between sqlite and mysql. In mysql, to lock a row of data at the start of a process to hold off concurrent operations, the recommended technique with the innodb engine is to issue SELECT field FROM table FOR UPDATE; This holds off any other process from accessing that row until it is released. This is according to the mysql manual. My question is, looking through the sqlite documentation, it is not clear how sqlite will handle that statement. Since I am porting sql between several databases, I am trying to figure out if I need to search and destroy any of those statements from mysql when converting it to sqlite. Scott Doctor sc...@scottdoctor.com -- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Article about pointer abuse in SQLite
It is uninitialized. you are setting an initial value within an if
statement. For the compiler, the code has NOT actually executed. so it
does not use the value of the variable arbitrary_true_false. If it was a
#define then it would use the value but still give an error because it
is not a compiler directive #if but a code if.
The logic is that the first instance of assignment is within a
conditional. That is a particularly nasty kind of bug and should be
reported as an error. because if later you decide to change
arbitrary_true_false to false, then s.x would not be initialized before
use. the compiler is correct to issue the warning. Give s.x a value
after/at initialization, but before the if statement to give it a
desired initial value then recompile, that should fix the error.
Compilers only set the code to initialize the variable at declaration,
not actually use the values during compile. If it was declared as a
constant using a compiler directive such as #define, then the compiler
would use the value in the logic and still give an error, but a
different one because the conditional would always evaluate true (or
false depending on what it was set to)
On 03/21/2016 21:31, J Decker wrote:
> On Mon, Mar 21, 2016 at 8:40 PM, Scott Doctor
> wrote:
>> you are missing
>>
>> using System;
> whatever. It still fails because it says the variable is
> uninitilalized. THe only thing that doesn't is actually running it.
>
> That same pattern not matter what the language triggers warning/error checkers
>>
>> Scott Doctor
>> scott at scottdoctor.com
>> --
>>
>>
>> On 3/21/2016 5:21 PM, J Decker wrote:
>>> So far I just see analysis tools fail for the same sorts of valid code...
>>>
>>> this is a bit of C# but the same idea causes the same warnings and
>>> there's nothign tecniclally wrong with this.
>>>
>>>
>>>
>>> class test
>>> {
>>> struct large_struct { public int x; }
>>> bool arbitrary_true_false = true;
>>> void method()
>>> {
>>> bool initialized = false;
>>> large_struct s;
>>> if( arbitrary_true_false )
>>> {
>>>initialized = true;
>>>s.x = 1;
>>> }
>>> if( initialized )
>>> {
>>>Console.WriteLine( "this fails(during compile) as
>>> uninitialized: {0}", s.x );
>>> }
>>> }
>>> }
>>>
>>> On Mon, Mar 21, 2016 at 4:35 PM, James K. Lowden
>>> wrote:
>>>> On Mon, 21 Mar 2016 13:48:06 -0700
>>>> Scott Perry wrote:
>>>>
>>>>> Compilers allow you to choose your standard; --std=c11 means
>>>>> something very specific (and unchanging)
>>>> They do. And that covers what the standard covers. The standard also
>>>> has limits. It includes constructs that are syntactically permitted
>>>> but whose behavior is left undefined, known by the scarred as "UB" for
>>>> "undefined behavior". An example from Clang's discussion is
>>>>
>>>> int i = 10 << 31;
>>>>
>>>> The standard says << is a shift operator. It places no limit on the
>>>> number of bits to be shifted. If that number is so large that the
>>>> product cannot be represented by the assigned variable, that is *not*
>>>> an error. The standard allows the compiler to do anything or nothing
>>>> with it. As you may imagine, the varieties of anything and nothing are
>>>> many.
>>>>
>>>> Compiler writers are well aware that "nothing" is faster done than
>>>> "something". Over time, they have gotten more aggressive in simply
>>>> deleting UB code. As a consequence, programmers who thought they wrote
>>>> standards-conforming code get burned when they upgrade/change
>>>> compilers. Mysterious and sometimes subtle errors are introduced by
>>>> the compiler for the user's benefit.
>>>>
>>>> Your googlefu will turn up lots of discussion. One I liked that wasn't
>>>> on Page 1:
>>>>
>>>>
>>>> http://blog.frama-c.com/index.php?post/2013/10/09/Overflow-float-integer
>>>>
>>>> --jkl
>>>> _______
>>>> sqlite-users mailing list
>>>> sqlite-users at mailinglists.sqlite.org
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
-
Scott Doctor
scott at scottdoctor.com
[sqlite] Article about pointer abuse in SQLite
you are missing
using System;
Scott Doctor
scott at scottdoctor.com
--
On 3/21/2016 5:21 PM, J Decker wrote:
> So far I just see analysis tools fail for the same sorts of valid code...
>
> this is a bit of C# but the same idea causes the same warnings and
> there's nothign tecniclally wrong with this.
>
>
>
> class test
> {
> struct large_struct { public int x; }
> bool arbitrary_true_false = true;
> void method()
> {
>bool initialized = false;
>large_struct s;
>if( arbitrary_true_false )
>{
> initialized = true;
> s.x = 1;
>}
>if( initialized )
>{
> Console.WriteLine( "this fails(during compile) as
> uninitialized: {0}", s.x );
>}
> }
> }
>
> On Mon, Mar 21, 2016 at 4:35 PM, James K. Lowden
> wrote:
>> On Mon, 21 Mar 2016 13:48:06 -0700
>> Scott Perry wrote:
>>
>>> Compilers allow you to choose your standard; --std=c11 means
>>> something very specific (and unchanging)
>> They do. And that covers what the standard covers. The standard also
>> has limits. It includes constructs that are syntactically permitted
>> but whose behavior is left undefined, known by the scarred as "UB" for
>> "undefined behavior". An example from Clang's discussion is
>>
>> int i = 10 << 31;
>>
>> The standard says << is a shift operator. It places no limit on the
>> number of bits to be shifted. If that number is so large that the
>> product cannot be represented by the assigned variable, that is *not*
>> an error. The standard allows the compiler to do anything or nothing
>> with it. As you may imagine, the varieties of anything and nothing are
>> many.
>>
>> Compiler writers are well aware that "nothing" is faster done than
>> "something". Over time, they have gotten more aggressive in simply
>> deleting UB code. As a consequence, programmers who thought they wrote
>> standards-conforming code get burned when they upgrade/change
>> compilers. Mysterious and sometimes subtle errors are introduced by
>> the compiler for the user's benefit.
>>
>> Your googlefu will turn up lots of discussion. One I liked that wasn't
>> on Page 1:
>>
>>
>> http://blog.frama-c.com/index.php?post/2013/10/09/Overflow-float-integer
>>
>> --jkl
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
[sqlite] hard links and SQLite
On 01/11/2016 18:06, Rowan Worth wrote: > On 12 January 2016 at 03:00, Felipe Gasper wrote: > >> On 11 Jan 2016 1:45 PM, Scott Hess wrote: >> >>> As far as preventing the other process from using it before the schema >>> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, >>> the schema does not exist. If you create the schema as a transaction, >>> that >>> will be atomic. >>> >> But in order for that SELECT to avert TOCTTOU errors, we?d have to do >> BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. >> > No, only the connection which is creating the schema needs BEGIN EXCLUSIVE. > The other connections can determine the schema state based on a normal > "SELECT count(*) FROM sqlite_master": > > * if it returns SQLITE_OK and at least one row, the schema has been created > and it can proceed > * if it returns SQLITE_OK and zero rows, the schema hasn't been created yet > * if it returns SQLITE_BUSY, the schema is in the process of being created > (or there's some other EXCLUSIVE transaction in progress, or a transaction > is being committed at this very moment, or an in-progress write transaction > has spilled sqlite's memory cache) > > > As Scott also hinted at, hard linking DB files is dangerous because > connections against each of the links will use different -journal files. In > the event that your schema creation process (or the machine its running on) > crashes halfway through COMMIT, connecting to the permanent database will > cause corruption to be observed (because it's not aware of the other > journal file and thus can't rollback the partial transaction). > > This may also be possible if another process simply connects to the > permanent DB at just the wrong time (ie. halfway through the schema > creation COMMIT)? Or maybe not, in my experience POSIX locks are maintained > across hardlinks but I haven't checked if this is specified by the standard > or file-system dependent. > > > Do your different connections actually run different code? Or are you > potentially in the situation where two threads are trying to create the > same DB at the same time (each via their own .tmp file)? > > -Rowan > _______ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users TOCTTOU? What is that? -- - Scott Doctor scott at scottdoctor.com
[sqlite] website documentation wording
Simply "Intel decided". using 'have', 'has', 'has been', 'have been' can almost always be dropped entirely or replaced with 'is', 'was', 'were' depending on tense. -------- Scott Doctor scott at scottdoctor.com -- On 12/4/2015 9:13 AM, Simon Slavin wrote: > On 4 Dec 2015, at 3:59pm, Jay Kreibich wrote: > >> It is actually in the ISO standard that the proper pronunciation is ?ess cue >> ell?. It became ?sequel? in some circles, mostly thanks to Microsoft. > Unfortunately I work as a contractor and if I can pick up the client's > pronunciation and copy it, the client likes me more. So I flip back and > forth between 'an ess cue ell' and 'a sequel'. My opinion on that, and lots > of other computer-related terms, depends on who's paying me that month. > > The worst one is the collective corporation. Is it "Intel has decided" or > "Intel have decided" ? Whichever one I write for whichever side of the > Atlantic, I get told off for getting it wrong. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Simple Math Question
17 -> 0x10001 mantissa -1 -> 0xf exponent ( or however many bits the exponent is represented by exact ---- Scott Doctor scott at scottdoctor.com On 10/23/2015 12:46 AM, Keith Medcalf wrote: > You are thinking (and typing) in base 10. Computers use this new-fangled > thing called binary -- base 2. > The "floating point" type is IEEE 754 double precision binary (base 2) > floating point. > > SQLite does not use decimal floating point (IEEE 854-1987) or General Decimal > Arithmetic > http://speleotrove.com/decimal/decarith.html > as specified by IBM and implemented in many non-lite SQL Engines. > > >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Scott Doctor >> Sent: Friday, 23 October, 2015 01:31 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Simple Math Question >> >> Something sounds wrong with this floating point issue. His example >> consists of two digit numbers with simple addition. The number 1.7 would >> be represented by the value 17 and an exponent of -1, which is an exact >> number, same with his other numbers. His math operations should give >> exact results since the operations are simple addition. Since he is not >> doing any multiplication or division, there should not be any resolution >> issues. His results using his simple equations should give exact >> answers. So something is not handling the numbers properly if he is not >> getting exact results. If the claims about floating point results in >> sqlite are as stated in this thread of messages then floating point >> should be entirely avoided and eliminated from sqlite as this implies >> that floating point operations are improperly implemented rendering >> useless and incorrect results. >> >> >> Scott Doctor >> scott at scottdoctor.com >> >> On 10/23/2015 12:14 AM, Dominique Devienne wrote: >>> On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik >> wrote: >>>> On 10/22/2015 4:25 PM, Rousselot, Richard A wrote: >>>> >>>>> FWIW, MySQL and Oracle both return all yes for that query. >>>>> >>>> In MySQL, 9.2 is a literal of DECIMAL type, which is in fact >> represented >>>> as a finite decimal fraction. SQLite doesn't have such a type. You >> would >>>> likely observe similar results in MySQL if you write your constants >> like >>>> this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type). >>>> >>> And oracle's NUMBER can be up to 20 bytes (21 unsigned), stores number >> is >>> base-100, i.e. each byte represents 2 base-10 digits. >>> http://www.orafaq.com/wiki/Number >>> >>> SQLite only uses IEEE double, which often cannot represent accurately >> even >>> small (as in text) numbers with a decimal point. You can use Oracle's >>> BINARY_DOUBLE to force it using an IEEE double. FWIW. --DD >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Simple Math Question
Something sounds wrong with this floating point issue. His example consists of two digit numbers with simple addition. The number 1.7 would be represented by the value 17 and an exponent of -1, which is an exact number, same with his other numbers. His math operations should give exact results since the operations are simple addition. Since he is not doing any multiplication or division, there should not be any resolution issues. His results using his simple equations should give exact answers. So something is not handling the numbers properly if he is not getting exact results. If the claims about floating point results in sqlite are as stated in this thread of messages then floating point should be entirely avoided and eliminated from sqlite as this implies that floating point operations are improperly implemented rendering useless and incorrect results. Scott Doctor scott at scottdoctor.com On 10/23/2015 12:14 AM, Dominique Devienne wrote: > On Thu, Oct 22, 2015 at 11:55 PM, Igor Tandetnik > wrote: > >> On 10/22/2015 4:25 PM, Rousselot, Richard A wrote: >> >>> FWIW, MySQL and Oracle both return all yes for that query. >>> >> In MySQL, 9.2 is a literal of DECIMAL type, which is in fact represented >> as a finite decimal fraction. SQLite doesn't have such a type. You would >> likely observe similar results in MySQL if you write your constants like >> this: 9.2e0 (scientific notation makes them have FLOAT or DOUBLE type). >> > And oracle's NUMBER can be up to 20 bytes (21 unsigned), stores number is > base-100, i.e. each byte represents 2 base-10 digits. > http://www.orafaq.com/wiki/Number > > SQLite only uses IEEE double, which often cannot represent accurately even > small (as in text) numbers with a decimal point. You can use Oracle's > BINARY_DOUBLE to force it using an IEEE double. FWIW. --DD > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
I am using Embarcadero's development studio and compiling in the sqlite amalgamation. You can just turn off those two warnings. To turn it off for whole project: Select the Projects menu Options Under the C++ Compiler options select the warnings Open the options for selected warnings. scroll down to those two warnings and disable them. To turn off the warnings just for sqlite, just right click on the sqlite.c file name, select the local options. the same project options menu opens, but will apply only to that file. If you try to compile the SQLite amalgamation with all warnings on, you will get over 600 of them. Most warnings are for unused variables, and variables that are assigned a value but never used I examined those particular ones. It is due to assigning a variable within an 'if'. Although it has double parenthesis around it, which is supposed to indicate that the assignment is intended, it still gives the warning for some reason on some of them. That is an important warning as that is usually a nasty bug where a single equals is used instead of a double equals for an equivalence comparison. Although that style makes for more compact code by doing the assignment at the same time as the logic test, I find that it is a safer practice to do the assignment just before the 'if' then test the variable. From a debugging point of view it is much easier to set a break point on the if statement and see the actual result being checked. I did a few compile tests. with the optimizer, the resulting compiled code was identical either way.. Scott Doctor scott at scottdoctor.com On 10/22/2015 2:01 AM, Marco Turco wrote: > Hi all, > > I'm trying to generate the sqlite3 library but there is no way with > Embercadero C++ 7.00. > > I always receive some warnings and the first two related to the > _endthreadex' and '_beginthreadex' cannot permit to link me the library. > > > > See below. > > > > k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e -tW > -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF > -D__HARBOUR__ -DSQLITE_HAS_CODEC=1 -Ik:\BCC70\Include;k:\XHARBOUR\Include > -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c > > > > Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero > Technologies, Inc. > > K:\sqlite_see_2013\sqlite3.c: > > Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function > '_endthreadex' with no prototype in function sqlite3ThreadProc > > Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function > '_beginthreadex' with no prototype in function sqlite3ThreadCreate > > Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is never > used in function winUnfetch > > Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect > assignment in function vdbeSorterCompareInt > > Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect > assignment in function vdbeSorterSetupMerge > > Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is never > used in function sqlite3CodecGetKey > > k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512 > @K:\sqlite_see_2013\sqlite_see_2013.bcl , > K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst > > > > TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc. > > +K:\sqlite_see_2013\Obj\sqlite3.Obj > > > > Any help ? Thank you in advance > > > > Marco > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Form-Feed (0x0C) is not a space character in JSON
http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf
http://json.org/
Form feeds are allowed in strings. Form feeds are traditionally
treated the same as a space. Often inserted for pagination and
ignored by compilers or treated the same as a space character.
Scott Doctor
scott at scottdoctor.com
--
On 10/16/2015 7:46 AM, Graham Holden wrote:
> I've not seen the RFC but you say "JSON only has 5 whitespace characters" and
> then list only 4, and your patched array only has four 1's. Have you missed
> one, or is the 5 wrong (or am I missing something)?
>
> Graham
>
> Sent from Samsung Mobile
>
> Original message
> From: Jan Nijtmans
> Date: 16/10/2015 14:33 (GMT+00:00)
> To: General Discussion of SQLite Database mailinglists.sqlite.org>
> Subject: [sqlite] Form-Feed (0x0C) is not a space character in JSON
>
> Hi all,
>
> Just noted in the jsonIsSpace[] array: According to RFC 7159, JSON
> only has 5 whitespace characters, FF (0x0C) is not among them:
>
>ws = *(
> %x20 / ; Space
> %x09 / ; Horizontal tab
> %x0A / ; Line feed or New line
> %x0D; Carriage return
> )
>
> Suggested patch below.
>
> Regards,
> Jan Nijtmans
> $ fossil diff
> Index: ext/misc/json1.c
> ==
> --- ext/misc/json1.c
> +++ ext/misc/json1.c
> @@ -50,11 +50,11 @@
> ** Growing our own isspace() routine this way is twice as fast as
> ** the library isspace() function, resulting in a 7% overall performance
> ** increase for the parser. (Ubuntu14.10 gcc 4.8.4 x64 with -Os).
> */
> static const char jsonIsSpace[] = {
> - 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 1, 1, 0, 0,
> + 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What's the status of SQLite4? Where can I find its source code?
I simply noticed others inquiring about sqlite4. As I am doing a new project, if it was going to be available in the near future then I would start playing with it and use its features. But if it will be years, and is mostly a play ground, then it is not an issue to ponder for now.. Scott Doctor scott at scottdoctor.com On 10/5/2015 3:30 AM, Richard Hipp wrote: > Jerry & Scott: What are you hoping to achieve with SQLite4 that you > cannot do with SQLite3? > > On 10/5/15, Stephen Chrzanowski wrote: >> There was a thread sometime last year about this. Basically, SQLite4 isn't >> planned for a release at this time, and from what I understood then, >> chances are there isn't going to be a thought towards release for at few >> more years. It basically is a playground to test theories, it is buggy, >> and such. It shouldn't be considered for use in a production environment. >> >> On Mon, Oct 5, 2015 at 1:18 AM, Scott Doctor >> wrote: >> >>> Is there a release date set for sqlite4? >>> >>> >>> Scott Doctor >>> scott at scottdoctor.com >>> -- >>> >>> >>> On 10/4/2015 9:14 PM, Stephen Chrzanowski wrote: >>> >>>> https://sqlite.org/src4/tree?ci=trunk >>>> >>>> On Mon, Oct 5, 2015 at 12:02 AM, Jerry wrote: >>>> >>>> Seems I could not find its source code ... >>>>> https://sqlite.org/src4/doc/trunk/www/index.wiki >>>>> >>>>> Thanks. >>>>> ___ >>>>> sqlite-users mailing list >>>>> sqlite-users at mailinglists.sqlite.org >>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>>> >>>>> ___ >>>> sqlite-users mailing list >>>> sqlite-users at mailinglists.sqlite.org >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >
[sqlite] What's the status of SQLite4? Where can I find its source code?
Is there a release date set for sqlite4? Scott Doctor scott at scottdoctor.com -- On 10/4/2015 9:14 PM, Stephen Chrzanowski wrote: > https://sqlite.org/src4/tree?ci=trunk > > On Mon, Oct 5, 2015 at 12:02 AM, Jerry wrote: > >> Seems I could not find its source code ... >> https://sqlite.org/src4/doc/trunk/www/index.wiki >> >> Thanks. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Using collation instead a virtual table
Best practice when dealing with floating point is to normalize and Chop. The best practice for dealing with floating point operations is to normalize your data sets before proceeding. All numbers should be -1.0<=x<=1.0. Done properly, after calculations are complete, the data set is easily returned to its original range and domain by reversing the normalization process. Chop is basically a rounding to a specified number of digits. Often 4 to 8 digits is adequate but each application is different. When dealing with matrix convolutions, after each row operation, chop each number in the matrix. Mathematically it can be shown that the final result is more accurate than allowing the floating point resolution error to propagate. Numbers such as 2.9 should round to 3. before doing the next round of calculations. This is especially important for results near zero. Typically numbers less than, for instance 0.1 should be set to zero. This is especially important in matrix operations. All floating point math libraries have round and/or chop functions. In fact, not chopping when doing large data sets will ultimately result in significantly wrong results due to propagating floating point resolution errors. Chopping corrects for these errors. If you remember your High School chemistry or biology class, one of the first lessons is about significant digits. How to determine the proper number of significant digits depends on your application and field of study. By using proper number of significant digits throughout your calculations, the result will be more correct than not doing so. ---- Scott Doctor scott at scottdoctor.com -- On 9/9/2015 11:47 AM, R.Smith wrote: > > > On 2015-09-09 05:19 PM, Constantine Yannakopoulos wrote: >> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik >> wrote: >> >>> A comparison like this would not generally be a proper >>> collation. The >>> equivalence relation it induces is not transitive - it's >>> possible to have A >>> == B and B == C but A != C (when A is "close enough" to B >>> and B is "close >>> enough" to C, but A and C are just far enough from each other). >>> >> ?Out of curiosity, doesn't this also apply also to numeric >> (real number) >> comparisons since SQLite3 uses IEEE floating point arithmetic?? > > IEEE Float comparisons do not work this way - you are more > likely to find the opposite: two numbers that seem to be near > perfectly equal might fail an equality test. > > Such confusion might be caused by statements such as: > ...WHERE (5.6 - 3.1) = 2.5 > ...WHERE (14 * 0.4) = 5.6 > > Which might return false if two or more of the constants > cannot be precisely represented. (The second one is a known > problem value). > > Nothing however would "seem" equal to the processor if they > are not exactly equal in binary form - no "almost" matching > happens. > > BTW: In strict Math it can be shown that 0.999... (repeating) > is exactly equal to 1 but in IEEE floats they are not, but > that is just because an 8-byte (64b) float lacks the capacity > to render the repeating nines to sufficiently wide a > representation to find the one-ness of it. > > https://en.wikipedia.org/wiki/0.999... > > IEEE fun in C#: > > Testing 1/3: > f = 0.333 > d = 0.333 > m = 0. > f*3 = 1 > d*3 = 1 > m*3 = 0. > (double)f*3 = 1.0002980232 > (decimal)f*3 = 0.999 > (decimal)d*3 = 0.999 > (double)((float)i/3)*3 = 1 > Testing 2/3: > f = 0.667 > d = 0.667 > m = 0.6667 > f*3 = 2 > d*3 = 2 > m*3 = 2.0001 > (double)f*3 = 2.0005960464 > (decimal)f*3 = 2.001 > (decimal)d*3 = 2.001 > (double)((float)i/3)*3 = 2 > > Cheers, > Ryan > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Version Control
Slightly off topic, but I am looking at version control systems. SQLite looks like it is using Fossil. How does Fossil compare to using Git, Mercurial, or Subversion? - Scott Doctor scott at scottdoctor.com -
[sqlite] FTS5
Is FTS5 fully tested and part of the current version, or is it still experimental? Scott Doctor scott at scottdoctor.com --
[sqlite] Proper way to abort
Sheesh, how did I miss that. Guess I need new glasses. Thats exactly what I was looking for. Scott Doctor scott at scottdoctor.com -- On 8/24/2015 9:39 AM, Jean-Christophe Deschamps wrote: > >> >> I have some queries that may take 5-15 seconds to complete. >> Sometimes the situation changes shortly after starting the >> query where my program does not need those results anymore >> and the program wants to abort and begin a different query >> instead. >> >> My question is: What is the proper way to abort a query, or >> other operation during execution that will not cause any >> issues? By issues I mean files not being closed, or memory >> not being free'd and such because the operation was aborted >> and did not have finish normall, but I can continue normally >> after the abort.. > > Maybe http://www.sqlite.org/c3ref/interrupt.html > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >
[sqlite] Proper way to abort
I have some queries that may take 5-15 seconds to complete. Sometimes the situation changes shortly after starting the query where my program does not need those results anymore and the program wants to abort and begin a different query instead. My question is: What is the proper way to abort a query, or other operation during execution that will not cause any issues? By issues I mean files not being closed, or memory not being free'd and such because the operation was aborted and did not have finish normall, but I can continue normally after the abort.. Scott Doctor scott at scottdoctor.com --
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
(oops glitched my send) Try using a database modeling program such as TMS Software's Data Modeler or SQL Maestro Group's SQLite Maestro These handle the changes nicely. You use the programs for designing your databases, queries, views and such. It spits out SQL to run on your system that handles setting up and changes. Or you can connect directly to your SQLite database and it will handle making all the changes for you. Scott Doctor scott at scottdoctor.com -- On 8/21/2015 11:54 AM, Scott Doctor wrote: > > ---- > Scott Doctor > scott at scottdoctor.com > -- > > On 8/21/2015 10:37 AM, sqlite-mail wrote: >> Thanks for your attention! >> >> Only to remark on this I tested on postgresql and somehow it >> knows how deal >> with it ! "so few (none?)" >> >> Cheers ! >> >>> Fri Aug 21 2015 19:08:58 CEST from "J Decker" >>> Subject: >>> Re: [sqlite] Is this a bug ? How to rename a table and all >>> dependencies ? >>> >>> On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail >>> >>> wrote: >>>> Then do you think this is a bug ? >>>>> Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" >>>>> >>>>> Subject: Re: [sqlite] Is this a bug ? How to rename a >>>>> table and all >>>>> dependencies ? >>> while it may be considered a nice thing; it's not common >>> practice to >>> rename tables, so few (none?) sql implementations >>> automatically update >>> references to tables when they are renamed. Not a bug; more >>> like out >>> of scope. >>> >>> It wouldn't know if you were moving a source table for archival >>> purposes and going to replace it with another empty one or >>> moving >>> because you didn't like your original schema. >>> >>>>> On 21 Aug 2015, at 12:20pm, sqlite-mail >>>>> wrote: >>>>> >>>>> >>>>>> Does anybody knows how to rename a table and all it's >>>>>> dependencies in one >>>>>> go >>>>>> ? >>>>>> >>>>>> >>>>> Can't be done. Sorry. >>>>> >>>>> Simon. >>>>> ___ >>>>> sqlite-users mailing list >>>>> sqlite-users at mailinglists.sqlite.org >>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> ___ >>>> sqlite-users mailing list >>>> sqlite-users at mailinglists.sqlite.org >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Scott Doctor scott at scottdoctor.com -- On 8/21/2015 10:37 AM, sqlite-mail wrote: > Thanks for your attention! > > Only to remark on this I tested on postgresql and somehow it knows how deal > with it ! "so few (none?)" > > Cheers ! > > >> Fri Aug 21 2015 19:08:58 CEST from "J Decker" >> Subject: >> Re: [sqlite] Is this a bug ? How to rename a table and all dependencies ? >> >> On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail > dev.dadbiz.es> >> wrote: >> >>> Then do you think this is a bug ? >>> >>>> Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" >>>> Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >>>> dependencies ? >>> >> while it may be considered a nice thing; it's not common practice to >> rename tables, so few (none?) sql implementations automatically update >> references to tables when they are renamed. Not a bug; more like out >> of scope. >> >> It wouldn't know if you were moving a source table for archival >> purposes and going to replace it with another empty one or moving >> because you didn't like your original schema. >> >> >>> >>>> On 21 Aug 2015, at 12:20pm, sqlite-mail >>>> wrote: >>>> >>>> >>>> >>>>> Does anybody knows how to rename a table and all it's dependencies in one >>>>> go >>>>> ? >>>>> >>>>> >>>> Can't be done. Sorry. >>>> >>>> Simon. >>>> ___ >>>> sqlite-users mailing list >>>> sqlite-users at mailinglists.sqlite.org >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compile warnings
My opinion is to keep it portable. The warning is there for a reason.
you are worried about performance yet are calling a function that does
nothing, which will take more memory cycles than a simple check for zero.
Trying to memset a zero length is a bug, not the warning. Add an if
statement around it. If the variable is local, it will probably be
optimized as a register variable and a zero check of a register is a
single op-code.
the problem with disabling warnings is that even if this instance is not
an error, some other part of the code may end up with the same situation
but is an error in the coding. I would prefer code that can be compiled
with all warnings turned on that gives no warnings than have a potential
problem because of a glitch in the code.
Scott Doctor
scott at scottdoctor.com
On 8/20/2015 9:32 AM, Scott Hess wrote:
> Yeah, we saw this with Chromium, too. The patch we use is below.
>
> I'm with Dr Hipp that this is really more of a GCC issue. If it was
> literally a 0 constant, it would make sense to warn so that the code can be
> removed. But it's only a 0 if you optimize a certain way.
>
> -scott
>
>
> diff --git a/third_party/sqlite/src/src/expr.c
> b/third_party/sqlite/src/src/expr.c
> index 4012f6c..65f211e 100644
> --- a/third_party/sqlite/src/src/expr.c
> +++ b/third_party/sqlite/src/src/expr.c
> @@ -856,7 +856,9 @@ static Expr *exprDup(sqlite3 *db, Expr *p, int flags,
> u8 **pzBuffer){
> }else{
> int nSize = exprStructSize(p);
> memcpy(zAlloc, p, nSize);
> -memset([nSize], 0, EXPR_FULLSIZE-nSize);
> +if( EXPR_FULLSIZE>nSize ){
> + memset([nSize], 0, EXPR_FULLSIZE-nSize);
> +}
> }
>
> /* Set the EP_Reduced, EP_TokenOnly, and EP_Static flags
> appropriately. */
>
>
> On Thu, Aug 20, 2015 at 3:13 AM, Bernhard Schommer <
> bernhardschommer at gmail.com> wrote:
>
>> Hi,
>>
>> the warning which is mentioned in the ticket
>> f51d9501800de5a0fb69d5048ce6662981b461ec still occurs also with newer gcc
>> versions. The ticket was closed after a gcc bug report was opened. The gcc
>> bug was closed due to a missing testcase.
>> I had a quick look at the problem and it seems that the warning is right
>> since in certain context and with optimizations enabled gcc can prove
>> that EXPR_FULLSIZE
>> - nSize == 0 and thus the triggered warning for this is correct.
>> Replacing
>> memset([nSize], 0, EXPR_FULLSIZE-nSize);
>> By
>> if(EXPR_FULLSIZE-nSize > 0)
>> memset([nSize], 0, EXPR_FULLSIZE-nSize);
>> would remove the warning,
>>
>> Cheers,
>> -Bernhard
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
[sqlite] pragma temp_store_directory is deprecated, what is the alternative?
Another issue to consider is security. Some programs, such as mine, needs to carefully control temporary files. Unless the user selects a specific directory for temporary files, the files are put in a subdirectory of the program directory, created at run-time, then is security erased when done. Letting the operating system handle where to put the temporary files is a security issue for certain types of programs. My opinion is it is best to simply ask the user at install where to put temporary files and save that in a config file or in a table field, or simply create a temporary directory on the programs directory. Scott Doctor scott at scottdoctor.com --
[sqlite] CSV excel import
A trick that works great most of the time with ODS is when exporting to CSV select the option to quote all fields. One problem with CSV is that many exports quote strings but not numbers. If everything is quoted then it is much simpler to process. But would need at least several options on the import: 1) what is the separator token (i.e. is it a comma, or a period, or a semicolon,...) 2) what is the decimal token (i.e. is it a period, comma, other,...) 3) Should quoted strings keep the quotes or strip the quote characters during processing 4) What is the escape sequence for embedding a quote character within a quoted string 5) using single or double quote character as the quote token. As you can see the number of permutations grows very fast to accommodate the wide variety of ways common programs handle CSV exports. On 7/30/2015 11:28 AM, Bernardo Sulzbach wrote: > I can remember two times when my life would have been easier if I > could throw big .ods into sqlite3 dbs. So I would also like such a > project. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] VFS for an MCU internal flash
Below is a link to files for the Atmel SAM4S processor that implements a simple FAT file system. The Atmel part is an ARM M4 core which I used in a recent design. the link takes you to the C files. YOu can also easily navigate to the various documentation for the code. http://asf.atmel.com/docs/latest/common.services.fs.fatfs.access_example.sam4s_xplained/html/files.html Scott Doctor scott at scottdoctor.com -- On 7/13/2015 12:29 PM, Jim Callahan wrote: > At a minimum SQLite needs a C complier (or cross-compiler) for the CPU. > > The storage device manufacturer usually publishes some specs (and sample > code -- such as assembly language routines callable from C) if a third > party has a primitive file system (a term I prefer to VFS which could refer > to virtualizing a network file system) the device manufacturer should know > about it. > > The problem consists of closing the gap, by building or buying a software > layer (primitive file system?) between the routines or sample code provided > by the storage device manufacturer and the calls made by SQLite. > > Virtual usually refers to a layer up the stack (more abstraction) he is > trying to go a layer down the stack (closer to the hardware) which is more > primitive without all the fancy stuff of the Unix civilization. > > Here is an article (SIGMOD 2013) on trying to get MS SQL Server to run on > an SSD with an ARM chip. > http://pages.cs.wisc.edu/~jignesh/publ/SmartSSD.pdf > > > Here is a discussion of solid state drive (SSD) firmware. > http://www.openssd-project.org/wiki/The_OpenSSD_Project > > As the controller chip on the SDD drive becomes a more powerful ARM chip, > it may be feasible to have SQLite in the SDD itself. > > Here is a discussion from 2008 > http://sqlite.1065341.n5.nabble.com/Porting-into-a-microcontroller-minimum-requirements-td37469.html > > Many handheld consumer devices follow the mobile phone tablet model and > have a Unix or Linux derived operating system iOS (based BSD Unix), Android > (based on Linux) or Windows (based on Windows) or Ubuntu (a Linux > distribution) and most of these already have SQLite. > > Jim > > > > On Mon, Jul 13, 2015 at 11:52 AM, Richard Hipp wrote: > >> On 7/13/15, Jim Callahan wrote: >>> SQLite expects a file system. >>> >> Not necessarily. Out-of-the-box SQLite does need a filesystem, but >> embedded system designers can substitute an alternative VFS >> implementation that writes directly to hardware. This has been done >> before. There are consumer gadgets that you can buy off-the-shelf >> today (in blister packs) that contain an instance of SQLite that talks >> directly to flash memory - essentially using SQLite as the filesystem. >> >> I think Shuhrat is just trying to do this again. >> >> -- >> D. Richard Hipp >> drh at sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Tables and Columns of Database of Whatsapp
If you are attempting to teach SQL, why not just use a simple example, such as an address book. Probably more informative to work through the process of creating a simple address table, then adding a second table of people to discuss foreign keys and such. Two people may link to the same address. Forinstance, they are from the same family, or work at thesame company. Reverse engineering an applications databaseprobably introduces much more complexity than should beintroduced if the students do not already understand SQL. Scott Doctor scott at scottdoctor.com --
[sqlite] RemovableStorage in UWP for SQLite database - sqlite3_open_v2 issue
On 6/27/2015 9:37 AM, Juan Pablo Garc??a Coello wrote: > path to the removable storage path like ??E:\db.dat?? and it always > tells CannotOpen. Your email is using charse gb2312 which is a Chinese character set. four of the characters are not mapping to UTF-8 properly. A problem may be that you are passing invalid UTF-8 to SQLite which may be causing the inability to open the file. the invalid characters are 0x3f ox3f which is not a valid UTF-8 sequence. ---- Scott Doctor scott at scottdoctor.com --
[sqlite] sqlite error code 1034
An fsync error with nvram may be caused by a timeout during the write cycle. write cycles in nvram can be many times longer than a read cycle in this type of memory. Trying to write a large buffer of data may take multiple seconds especially if adresses are non-linear depending on size and technology of the nvram. Try more frequent fsyncs, or extending system timeouts as a test. Errors happen for a reason and should not ever be ignored without knowing the cause. Another problem with nvram is limited number of write cycles. if a particular memory location is constantly in read/write cycles it is possible that the memory is near the end of its life and needs to be replaced. Scott Doctor scott at scottdoctor.com On 6/23/2015 1:09 AM, Simon Slavin wrote: > On 23 Jun 2015, at 6:01am, Mayank Kumar (mayankum) > wrote: > >> Do you think if sqlite3_step or sqlite3_open fail with this error, we should >> treat this as transaction committed and ignore the fsync error and it will >> be retried with the next commit or read and eventually will sync unless I >> see this error continuously in which case we can treat this as catastrophic. >> >> Also I wanted to know if fsync requires space on the filesystem since in our >> case nvram has little space(200mb), do you think fsync could fail because of >> that as well? > You can't ignore fsync() since it indicates that future changes to the file > may not be valid, with no errors reporting the problems (because an error has > already been reported). So yes your program should crash. > > But I've never seen fsync() in real life except when it indicated a hardware > problem of some kind -- either a badly connected cable or a bug in the mass > storage driver. Software problems which might cause fsync() to fail are > beyond my competence. Sorry. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Mozilla wiki 'avoid SQLite'
On 6/14/2015 11:28 PM, david at andl.org wrote: > I won't abuse the patience of our hosts by prolonging this debate, but I > disagree strongly with this theme. So you disagree with a disagreement? > I have almost certainly written more C/C++ code than you or most of the > people on this list, I doubt it. > and I never choose it first. I am personally at least 3 > times as productive in C# as I am in C (slightly narrower margin in C++), > and computers are far cheaper than brains. C# is just a bastardized version of C++ which is a bastardized version of C. If you only program in C# then I guess you have only written code for PC's. A whole other world exists beyond PC's and Microsoft. C# is in no way portable, neither is C++. Only C is truly portable. Both C++ and C# require committing to a specific compiler product which is the antithesis of C. I am often forced to use C++ as the cross compiler platforms (such as C# or Embarcadero (Borland) compiler) force such to use the system GUI, but all of "My" code, as compared to the GUI code, that does the real work is written in C. C++ is merely a wrapper around the C language. C and C++ co-mingle very nicely. > This theme is strongly reminiscent of arguments over moving from assembly > language, and it's basically wrong. Well a bunch of very experienced programmers, with very diverse backgrounds seem to disagree. > The best tool is the one that gets the > required job done with maximal speed at minimal cost. Which is more important: How fast you can crank out code with minimal effort (which means you are letting others write the canned code portion of your code), or creating something new where you do the hard work so the end user has a well designed efficient product? > And just for the record, C# does not compile into byte code. I suggest you > check your facts. Quite believable. Which is why Microsoft software is so efficient, fast, small, and lacking of bugs. Scott Doctor scott at scottdoctor.com --
[sqlite] Mozilla wiki 'avoid SQLite'
On 6/14/2015 3:00 PM, Simon Slavin wrote: > The result is that that higher level the language you write in, the better. I disagree. The use of languages higher than C result in slow bloated code. A language that calls a language that calls a language. Simple programs become multi-megabyte resource hogs. I agree that C compilers are able to optimize assembler code to a level that hand-coded assembler probably could not achieve. The problem is that higher level languages are not compiling to assembler, but compiling to the language below it. ---- Scott Doctor scott at scottdoctor.com --
[sqlite] User-defined types -- in Andl
Any properly written documentation on any subject always begins with an executive summary (no more than a few pages), an overview (usually a dozen more pages), then gets into the nitty gritty. Consider if I want you to write a paragraph in Egyptian Hieroglyphics. So I provide you with a few "sample" sentences already written in Egyptian Hieroglyphics. Would you be able to both understand my examples, and write a proper paragraph in Egyptian Hieroglyphics? Regarding a formal definition. That should be the first thing you write when creating something new. That is where the details start to show collisions, issues, problems. To the contrary, when I start using something new, I do not want to sift through 22k pages of text just to get the concept. Very few manuals are written well. You need to be able to explain the entire language in "A Few" pages. A summary of the hieroglyphics. (operators. This is what, a dozen or so symbols) A one or two sentence description of each key word. (e.g. JOIN, SELECT, INSERT,... especially anything new) A one or two sentence explanation for each key word (or symbol) how it relates to the equivalent SQL. Any documentation on any topic should be structured as such. The need for a formal definition is obvious, but is usually used in the same fashion as a dictionary (the printed on paper kind). A few people will read the entire book. But most will just turn to the entry of interest skipping everything else. The trick is being able to find that one word quickly and getting "All" the needed information in a concise deliberate fashion. - Scott Doctor scott at scottdoctor.com - On 6/7/2015 10:28 PM, david at andl.org wrote: > Thanks for pointing it out, but I knew that the best way to show off a > language is with examples. That's why there are nine sample Andl scripts > comprising dozens of individual examples in the Samples folder. My guess is > if that you're asking me to write examples, the real lesson is that I didn't > make them easy enough to find. > > I have a formal grammar, but I don't expect anyone to read that. More and > better examples is the way to go.
[sqlite] User-defined types -- in Andl
So we are supposed to learn this new language by osmosis? Scott Doctor scott at scottdoctor.com On 6/7/2015 11:00 AM, Simon Slavin wrote: > On 7 Jun 2015, at 6:51pm, Scott Doctor wrote: > >> Do you have a PDF that explains the language? > There are plenty of blog entries which explain the language. I spent more > time looking for some examples (I understand better from examples) and > eventually found one. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] User-defined types -- in Andl
Do you have a PDF that explains the language? My opinion is that I have seen many languages come and go. Consider general programing languages. C is far superior to just about any language available. In fact the underlying code for most languages is written in C. So the question becomes, why does everyone see a need to keep creating new languages? Beyond the assembler instruction compiler for a new processor, a C compiler is usually the first compiler written for that processor. It is simple and straight forward to do, and I have done so many times. If you look at the evolution of the C language (even considering that bastard child C++) it has changed very little in 35 years, is available for all architectures, properly written code will compile for any architecture unmodified, and it has every hook needed to do any programming task. Many decades ago, an attempt was made to make a more efficient keyboard to replace the QWERTY keyboard. Some of the fledgling computer companies in the 1970's and 80's tried to get them accepted. From a straight technical perspective, they are more efficient. They all failed to be accepted. Consider what is easier. To train a new generation on the old stuff? or to re-train half a dozen generations on the new stuff? Choices are usually made on which is easier now versus the long term benefits later. What I find interesting is how many of these "New" languages are so similar to C. Java, PHP, and such, take the base constructs of C, then add "Special" additions to do what is basically just a C function. They change some syntax to make it more BASIC like, but the general technique still follows C. So why not just use C? In my current project, I debated just writing a bunch of C functions to handle my data. Once I got into it, I realized that beyond simply reading/writing some data structures, the code was getting complex very fast. SQLite lets me offload the low level details and just write a query with SQLite handling the parsing and search. Is it ideal, hardly. But the alternative is much more complicated and not worth the effort. If your data is just a few simple data structures, sure, just write some C code. But the reality is that most well developed programs quickly branch into ever increasing complexity. Regarding SQL, many companies are attempting to replace SQL with their flavor of an interface. Embarcadero (the old Borland) has in their development system a "Universal" database interface to make accessing databases "Universal". The idea being that a database designer just wants their data and does not care about the underlying mechanisms. Wait, that is the entire concept behind every programming language. If programmers cared about the underlying mechanism at every level and just wanted to write the most optimal code possible (which is a far off concept no longer desired for some reason) then all programs would be written in assembler. I used their system for a while. Now I just write the SQL directly and just link in SQLite instead of using Embarcadero's stuff. Although some of their constructs "seemed" to simplify some tasks, the program as a whole was actually more complex. My opinion why SQL has endured is that it actually hides from the programmer the internal complexity required to implement a task. While some of the syntax may be a bit quirky, so is talking to a teenager, but we adapt. If SQL did not do what is needed then people would not use it. The reality is that SQL actually is a well thought out language, even if the syntax can be a bit bulky and awkward. If you consider "Who" will be using the language, I find that many of the "Users" are not computer scientists, but people that are trying to make a database for their business purposes, and their specialty is not the intricacies and philosophies of coding architecture. Some of us are those brainiac computer geek types. But I see a large number of users as people who "Learned Enough" to do their job. Another issue is the ability for someone a decade, or two, or three, later, who is handed the database code without any documentation, to be able to decipher the program. (remember the Y2K bug). As you develop your language, consider if someone without having read the manual, but has a background in programming, could decipher a program written in your language. Scott Doctor scott at scottdoctor.com On 6/7/2015 2:17 AM, david at andl.org wrote: > I've been reading this thread with great interest. It parallels the project > I've been working on: Andl. > > Andl is A New Database Language. > > Andl does what SQL does, but it is not SQL. Andl has been developed as a > fully featured database programming language following the principles set > out by Date and Darwen in The Third Manifesto. It includes a full > implementation of
[sqlite] User-defined types
Just write your program in C. Use the C syntax to do whatever you want and you have full control over the minutiae. Scott Doctor scott at scottdoctor.com -- > On 6/4/15, Darko Volaric wrote: >> What is motivating this for me is that I generate many unique queries in my >> code for almost any operation. Converting those to SQL is error prone and >> uses a lot of memory compared to the operation involved. The database >> engine is so fast and efficient yet I'm wasting resources making SQL! >> >
[sqlite] AUTOINC vs. UUIDs
I can see the issue. For instance, an invoice ID related to inventory, where the invoice ID is the PK number. Two salesmen on their iPads take an order where their local database assigns the same PK number. When merged you have two invoices with the same ID. Seems a classic problem with non centralized input. A second field, such as salesman ID, would need to squashed into that invoices PK number to guarantee a uniqueness to the number, assuming every salesman has a unique ID. Scott Doctor scott at scottdoctor.com On 5/20/2015 3:08 PM, Steven M. McNeese wrote: > You misunderstood. If an application running on a tablet for 2 users add rows > to a local database with an auto increment key, each would get a key based on > their database an let's say for grins they both start with a new clean > database. User 1 gets key 1 and user 2 gets key 1. Now they push their data > to a server each with key 1 but different data. That doesn't work. Both > records needs to be added to the server database - a collection of all the > data collected on the client tablets. >
[sqlite] AUTOINC vs. UUIDs
Hmmm, 9,223,372,036,854,775,807. Disregarding the negative numbers because,... well... If your computer can process one billion completed, finalized, transactions per second (I want one of those computers), the pool of numbers will deplete in a mere: 9223372036854775807 / 10 ) / 60 seconds) / 60 minutes) / 24 hours)/ 365 days) = 292 years. rounded to the nearest year, but I think your great, great, great, great, great, grand-kids will probably not care, that assumes humans are not wiped out by by Skynet and eaten by Aliens who harvest us as delicacies. Scott Doctor scott at scottdoctor.com On 5/20/2015 2:38 PM, Jean-Christophe Deschamps wrote: > At 23:24 20/05/2015, you wrote: > >> On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese >> wrote: >> > Often times people will use GUIDs as primary keys when different >> systems need to generate >> > rows and then merge together. Like an application that works with >> offline clients that push the >> > data to a server when the connect. However there are other ways of >> accomplishing the same thing. >> >> For curiosity - Is there a site/blog post somewhere >> enumerating/listing these other ways ? > > I don't know, but let's say your rowids range from > -9223372036854775807 to 9223372036854775807, that's > 18446744073709551614 possible rowids. > > Imagine that in 50 years, the total population on Earth will grow to > (say) 50 billion people (I hope it won't!). > > 18 446 744 073 709 551 614 / 50 000 000 000 = 368 934 881.474 191 032 28 > > That leaves you the possibility to assign a unique identifier to every > potential client on the planet (and a big one) in nearly 369 million > servers concurrently without the faintest risk of collision. > > At this rate, you may limit rowids to only positive integers ... and > hire a large army of telemarketers. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] AUTOINC vs. UUIDs
Given a field that is a primary key with auto-increment, does sqlite store an integer that gets incremented, or does it look at the last row and increment its value? Scott Doctor scott at scottdoctor.com On 5/20/2015 11:05 AM, Simon Slavin wrote: > Posting this not because I agree with it but because the subject has come up > here a couple of times. > > <https://www.clever-cloud.com/blog/engineering/2015/05/20/Why-Auto-Increment-Is-A-Terrible-Idea/> > > "Today, I?ll talk about why we stopped using serial integers for our primary > keys, and why we?re now extensively using Universally Unique IDs (or UUIDs) > almost everywhere." > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please confirm what I THINK I know about blobs
My design philosophy is that if I have to think about what something is, then that thought is a piece of information that should accompany the blob. Consider ten years from now when someone else is looking at the database for the first time. Will they know what is in that blob? Column names should be descriptive of its contents. If you cannot describe the contents in a word, then that row should have at least a simple text column so a description can tag the blob. Scott Doctor scott at scottdoctor.com On 5/9/2015 4:18 AM, William Drago wrote: > On 5/9/2015 6:40 AM, Eduardo Morras wrote: >> On Sat, 09 May 2015 06:09:41 -0400 >> William Drago wrote: >> >>> All, >>> >>> Say you encounter a blob in a database. There's no way to >>> tell if that blob carries bytes, floats, doubles, etc, correct? >>> >>> Assuming the above is true, then is it always prudent to >>> store some metadata along with your blobs so that they can >>> be identified in the future? >>> >>> Example table: >>> >>> ModelNo TEXT (e.g. SO-239) >>> SerialNo TEXT (e.g. 101) >>> VSWR BLOB (e.g. x'feab12c...') >>> VSWR_Type TEXT (e.g. double) >>> >>> >>> Does this make sense? >> You can use SQL comments on CREATE TABLE, those comments aren't >> deleted from SQLITE_MASTER table, you can query it as a normal table. >> >> CREATE TABLE blob_table ( >> ModelNo TEXT, -- e.g. S0-239 >> SerialNo TEXT, -- e.g. 101 >> VSWR BLOB -- double, e.g. x'feab12c' >> ); >> >> SELECT sql from sqlite_master where type='table' AND >> tbl_name='blob_table'; >> >> will return >> >> CREATE TABLE blob_table ( >> ModelNo TEXT, -- e.g. S0-239 >> SerialNo TEXT, -- e.g. 101 >> VSWR BLOB -- double, e.g. x'feab12c' >> ) > > This is a clever idea and saves the addition of a column just for blob > type. Is this a reliable feature of SQLite? Does anyone see any issues > with this as opposed to using a dedicated column? > > Thanks, > -Bill > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Multiple Prepared Statements
So if I have a loop that finds a row with some data (statement1) then based on values from that row sets fields in other rows statement2 find a row to set new data statement3 set column to something, repeat n times. then go back and do it all again x times. I can prepare the 3 statements first then loop until I am done then finalize the 3 statements. That sure saves much overhead from preparing and finalizing 3*n*x times. Scott Doctor scott at scottdoctor.com -- On 5/8/2015 3:20 PM, Richard Hipp wrote: > On 5/8/15, Scott Doctor wrote: >> Can I prepare multiple statements then implement them in >> arbitrary order (based on some logic)? >> >> Or do the statements need to be prepared, stepped, finalized >> serially? >> > They can be run in any arbitrary order. That is the usual case, actually.
[sqlite] Multiple Prepared Statements
Can I prepare multiple statements then implement them in arbitrary order (based on some logic)? Or do the statements need to be prepared, stepped, finalized serially? Scott Doctor scott at scottdoctor.com --
[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome
That will be the day I can hit print on my computer and a Roast Beef Sandwich, with lettuce, tomato, onion, cheddar, and a smear of mustard on Marble Rye comes out my printer, all while driving my flying car (it is 2015, where is my flying car?) Scott Doctor scott at scottdoctor.com -- On 5/6/2015 8:11 AM, Justin Clift wrote: > On 4 May 2015, at 18:52, Scott Doctor wrote: > >> The day I can open source my rent, groceries, car repairs, is when >> everything else can be free. > Rent and groceries... yeah, good luck there. ;) > > But car repairs might actually come along at some point (decade or two?) > if the 3D printing scene goes big. :) > > (and "... 3D printed groceries ..." hrmm ;>) > > + Justin > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Multiple instances of the same program accessing the same db file
The issue of locking a file during concurrent access seems to be a major issue, mostly due to the schizophrenic abilities of network file systems. Has the SQLite development team considered embedding its own file locking system logic, such as creating its own lock file to prevent damage from concurrent write access, that does not rely on the network file system? Seems that if the issue is due to the design of the network file system, which we have no control, then embedding some internal logic system that handles self-flagging of a lock condition seems a reasonable course to pursue. Scott Doctor scott at scottdoctor.com --
[sqlite] What software is deployed more than SQLite?
You could just use all caps and scream the headline, then you do not need to worry what should be capitalized. - Scott Doctor scott at scottdoctor.com - On 5/4/2015 10:01 AM, Tim Streater wrote: > On 04 May 2015 at 17:31, jungle Boogie wrote: > >> On 4 May 2015 at 07:58, Warren Young wrote: >>> On May 3, 2015, at 6:50 PM, jungle Boogie >>> wrote: >>>> On 3 May 2015 at 11:18, Richard Hipp wrote: >>>>> Any input you can provide is appreciated! >>>> Congratulations to you and your team on SQLite's achievement and I >>>> wish you continued success. >>>> >>>> "Most Widely Deployed And Used Database Engine" >>>> >>>> I don't think the A in and needs capitalization. >>> Both are correct. The only incorrect thing to do is to mix styles on titles >>> within a single work. >>> >>> http://www.quickanddirtytips.com/education/grammar/title-capitalization-rules >> This says small words don't need the capitalization... >> http://grammar.yourdictionary.com/capitalization/rules-for-capitalization-in-t >> itles.html > In fact none of the words need capitalisation except the first. Otherwise it > looks like a 1930's newspaper headline. > > -- > Cheers -- Tim > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome
Nothing wrong with a commercial tool. I use, and paid for, many of them. After trying everything I could find for a particular problem, open source, freeware, trial versions, I chose a commercial program as it was more detailed and did want I needed it to do. Their program solved an issue for me that the open source, free stuff did not. The day I can open source my rent, groceries, car repairs, is when everything else can be free. - Scott Doctor scott at scottdoctor.com - On 5/4/2015 9:15 AM, Gerald Bauer wrote: > Hello, >Thanks for the additions to the awesome-sqlite [1] list. I added > the ODBC and JDBC drivers to a new middleware section, and the R > driver to a new language binding section, and the R data frame package > to a misc section. > >Note: For now commercial only tools (e.g. SQLite Analyzer) will NOT > get include - sorry. If you want to get it included, buy a sponsored > link or a beer for everyone on the mailing list! Just kidding ;-) > >Cheers. > > [1] https://github.com/planetopendata/awesome-sqlite > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Multiple instances of the same program accessing the same db file
I googled file locking issues. Sheesh, The horror stories of programs like Quickbooks corrupting data files. Files getting locked when nobody is using them (A windows 7 bug that supposedly was fixed) and endless information about what not to do, or what does not work. Very little about what does work. My application is oriented toward research where data is added, but rarely deleted. Doing a cleanup or purge operation is rare, but would be done by a single user with a backup made of the original (I automate backups since people always forget to do it). So I just instruct the user to make sure they are the only one accessing that database when doing so. Users always follow directions, um What I am considering is if two people are adding to the same project file at the same time, make a local database that has the new information, then do a merge later into the main project file later. This is a real situation anyways as data may be collected offsite on something like an iPad or laptop then merged into the main database later. Isolated operation with no internet access. Would work similar to the rules of accounting. Make a transaction log then close the books at the end of the day. Seems to work for the past few hundred years for the bean counters. If person X needs person Y's new data, they just run the merge operation. Hmm, I can think of multiple problems with this technique, or the aggravation the users may encounter. Need to think about it some more, Scott Doctor scott at scottdoctor.com
[sqlite] What software is deployed more than SQLite?
OK, here is merciless. The grammar police. Starting a sentence with 'There' is not proper. I suggest the following Rewrite: /There are likely more copies of SQLite in use than all other database engines combined./ As: More copies of SQLite are in use than all other databases combined. Rewrite: /There are billions and billions of copies of SQLite in the wild./ As: Billions and billions of SQLite copies are in the wild. Rewrite: There may be more instance of libc in use than SQLite. As: More instances of libc may be in use than SQLite. hmm, not sure I agree with that statement. The point of the following should be first followed by the explanation. Rewrite: But libc is not a single software component, but rather several competing implementations (ex: BSD vs. GNU) with similar interfaces. No one implementation has nearly the reach of SQLite. As: However, No implementation of libc has the reach of SQLite. Several competing implementations of libc exist with similar interfaces (e.g. BSD vs. GNU), SQLite has one. Rewrite: There are probably far more deployments of SQLite than there are of Linux. SQLite is used on every Android device, which accounts for the bulk of Linux uses. And SQLite is probably on most Linux desktops since it is part of Chrome and Firefox. Many Linux servers have a copy of SQLite by virtue of running PHP or Python. Some fraction of Linux servers may omit SQLite, but that fraction is far smaller than the number of iPhones, and so SQLite still comes out numerically superior. As: SQLite has more deployments than Linux. Every Android device uses SQLite. Since SQLite is part of Chrome and Firefox, SQLite is probably on most Linux Desktops. Many Linux servers, By virtue of running PHP or Python use SQLite. I suggest dropping the last sentence about a fraction of linux servers. Rewrite: There are more deployments of SQLite than there are of Apple products since SQLite is used in every Apple product and hence SQLite deployments are a superset of Apple products sold. As: SQLite has more deployments than Apple products since all Apple products use SQLite. Scott Doctor scott at scottdoctor.com On 5/3/2015 1:32 PM, Richard Hipp wrote: > On 5/3/15, Richard Hipp wrote: >> I'm trying to update the "Most Deployed Database" page >> (https://www.sqlite.org/mostdeployed.html) in the SQLite documentation >> (which has not been touched in close to a decade)... > I invite merciless criticism of the draft revision at > (https://www.sqlite.org/draft/mostdeployed.html). This is part of the > SQLite "sales pitch", so I want SQLite to look good, but I don't want > to overstate the case.
[sqlite] Multiple instances of the same program accessing the same db file
Hmm, one for doing my own locking, one against it. As this seems to be an obvious issue in any network, I wonder why the network developers have not appropriately addressed this issue. Looks like I need to research this problem more before implementing. I dislike probability games of designs that will work most of the time, but have a potential collision scenario. Such is why so many applications have the occasional crash or corruption. Scott Doctor scott at scottdoctor.com On 5/3/2015 12:54 PM, James K. Lowden wrote: > On Sat, 02 May 2015 19:24:19 -0700 > Scott Doctor wrote: > >> Each computer has its own copy of the program, but may >> share the database file which may be located on any of the computers. >> So I am wondering whether I should implement my own locking logic in >> my program. > Yes, you need to arrange within the application not to have two > processes writing to the remote database at the same time. The locking > SQLite uses depends on semantics not provided by the remote > filesystem. > > With a local filesystem, when two processes are updating a file, each > process's update is visible to the other in the unified buffer cache > supplied by the OS. In a networked filesystem, there is no unified > buffer cache: updates from process A, while manifest in the file, are > not necessarily reflected in the cache used by process B on another > machine. A subsequent update from B based on its outdated cache could > well create an incoherent file image. > > The only safe answer is arrange for each update to begin by locking the > file in the application. Then open the database, update it, close the > database, and unlock the file. By the same token, after any update > every reading process should close and re-open the database before > continuing to rely on the database > > One way to do that would be to keep an update.count file adjacent to > the database file. Lock it, read it, increment it, proceed with the > database update, and release it. Before each read, lock the file for > reading, and check the counter value. If it's changed, close and > re-open the database, execute the SELECT, and release the file. > > That's just an outline; I might have overlooked something. The > complexity and pitfalls explain why applications that need > inter-machine consistency connect to a DBMS daemon that manages its > database files locally. > > HTH. > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Multiple instances of the same program accessing the same db file
hmm, I am using sqlite as a project file that keeps track of a variety of information (usually a couple dozen megabytes in size per project). My initial post assumed a single user with a couple windows open. The file might be accessed by another user on a local area network. Usually no more than a few people at the same time for a specific project file. My program runs on windows and mac with a local area network connecting the computers. Each computer has its own copy of the program, but may share the database file which may be located on any of the computers. So I am wondering whether I should implement my own locking logic in my program. - Scott Doctor scott at scottdoctor.com - On 5/2/2015 6:10 PM, Scott Robison wrote: > Since I'm not clear on whether your two or more > processes are running on the same machine accessing a local drive or on > multiple machines or over a network, keep in mind that network file systems > are notoriously bad at the things SQLite needs (locking).
[sqlite] Multiple instances of the same program accessing the same db file
To review, after opening the database, issue the PRAGMA busy_timeout = x, with x being however long I want to wait before aborting. I can keep both database handles open at the same time, but need to make sure I finalize the operation before the timeout happens (assuming the other program is also trying to access the database at the same time). - Scott Doctor scott at scottdoctor.com -
[sqlite] Multiple instances of the same program accessing the same db file
Is the PRAGMA value the retry interval, or the timeout where it aborts and reports a failure? Scott Doctor scott at scottdoctor.com -- On 5/2/2015 5:08 PM, Simon Slavin wrote: > On 3 May 2015, at 12:55am, J Decker wrote: > >> Yes, it really requires only a little additional work on application side. >> The native open will open it in read/write share allow, and handle >> interlocking. >> >> if you get a result of SQLITE_BUSY you need to retry the operation after a >> short time. > Just to update J a little, you no longer need to handle the retry in your own > code. SQLite has its own exponential-backoff-and-retry feature. You set it > up using either C code or a PRAGMA, which have identical result: > > <https://www.sqlite.org/c3ref/busy_timeout.html> > > <https://www.sqlite.org/pragma.html#pragma_busy_timeout> > > Do this with your connection handle after you open the database. Set it to a > couple of minutes, or however long you want your program to keep retrying > before failing and reporting an error to the user. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] Multiple instances of the same program accessing the same db file
I am somewhat new to sqlite and am trying to decide an issue with the program I am writing (cross platform, written in C/C++). After reading through the sqlite documentation, I am still unsure about the issue how to implement multiple instances of the same program. Consider a program that may have more than one instance of the same program open at the same time. Both instances need to read/write the same sqlite database file. Can both instances open the same database file at the same time? Another way to word the question is whether sqlite will properly handle two independent programs accessing the same sqlite database file at the same time where both programs will be reading/writing to the database? Or do I need to implement or more complex strategy for accessing the sqlite file? -- - Scott Doctor scott at scottdoctor.com -
[sqlite] dropping a constraint
The page I was referring to in the documentation is http://www.sqlite.org/lang.html - Scott Doctor scott at scottdoctor.com - On 5/1/2015 12:10 AM, Clemens Ladisch wrote: > Scott Doctor wrote: >> I noticed that the sqlite documentation does not show the CONSTRAINT keyword > http://www.sqlite.org/syntax/column-constraint.html > http://www.sqlite.org/syntax/table-constraint.html > >> Also it appears that sqlite does not support DROP CONSTRAINT > Indeed:http://www.sqlite.org/lang.html > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] dropping a constraint
I noticed that the sqlite documentation does not show the CONSTRAINT keyword, but it looks as though sqlite accepts the keyword. Also it appears that sqlite does not support DROP CONSTRAINT, but does support DROP VIEW and other similar. Is this correct? -- Scott Doctor scott at scottdoctor.com --
[sqlite] Segfault during FTS index creation from huge data
This makes my head spin. ridiculous that an over commit even exists. any slight performance gain is totally nullified by a crashing program. - Scott Doctor scott at scottdoctor.com - On 4/30/2015 11:47 AM, Scott Robison wrote: > On Thu, Apr 30, 2015 at 11:42 AM, James K. Lowden schemamania.org> > wrote: > >> On Wed, 29 Apr 2015 20:29:07 -0600 >> Scott Robison wrote: >> >>>> That code can fail on a system configured to overcommit memory. By >>>> that standard, the pointer is invalid. >>>> >>> Accidentally sent before I was finished. In any case, by "invalid >>> pointer" I did not mean to imply "it returns a bit pattern that could >>> never represent a valid pointer". I mean "if you dereference a >>> pointer returned by malloc that is not null or some implementation >>> defined value, it should not result in an invalid memory access". >> Agreed. And I don't think that will happen with malloc. It might, and >> I have a plausible scenario, but I don't think that's what happened. >> > The Linux man page for malloc documents that the pointer returned may not > be usable in the case of optimistic memory allocations, as the eventual use > of the pointer may trigger the need to commit a page of memory to the > address space and that a page of memory may not be available at that point > in time. Thus malloc, on Linux, makes no guarantee as to the viability of > using the returned pointer. > > Perhaps you are correct and "sigsegv" is not the literal signal that is > triggered in this case. I don't care, really. The fact is that an > apparently valid pointer was returned from a memory allocation function yet > can result in an invalid access for whatever reason (out of memory, in this > case). The Linux OOM killer may kill the offending process (which is what > one would expect, but one would also expect malloc to return null, so we > already know not to expect the expected). Or it may kill some other process > which has done nothing wrong! Sure, the OS is protecting the two processes > address space from one another, but it seems to me that if one process can > kill another process, there is a problem. > > I can see the utility of a memory allocation strategy like this. It should > in no way be the *default* memory allocation strategy, especially for a > system that tries to be posix compliant, because this is in direct > violation of posix compliance (by my reading) from > http://pubs.opengroup.org/onlinepubs/009695399/functions/malloc.html > > Upon successful completion with *size* not equal to 0, *malloc*() shall >> return a pointer to the allocated space. >> > Or maybe posix just needs a better definition for "allocated space". Sure, > an address was allocated in the processes address space, but actual memory > was not allocated. > > The decades old interface contract was "if you call malloc with a non-zero > size, you can depend on malloc to return a null pointer or a pointer to the > first byte of an uninitialized allocation". Thus your application could > decide what to do if the memory was not available: abort, exit, select an > alternative code path that can get the job done with less or no memory, > ignore the return value and let sigsegv handle it later. > > Now with optimistic memory allocation, you do not have a choice. If your > malloc call results in an overcommit, your process can be killed later when > it tries to access the memory. Or some other innocent process might be > killed. > > I really wonder how many man hours have been wasted trying to debug > problems with processes just to find out that the killed process did > nothing wrong, it was some other process overcommitting memory. Or worse, > how many man hours were wasted and no good reason was ever learned. > > I came across this last night while learning more about OOM: > https://lwn.net/Articles/104179/ -- particular, the analogy, which I think > is spot on. > > I realize that there is no one right answer to how an OS should handle > memory exhaustion. There are various tradeoffs. However, C is not an > operating system, it is a language, and the standards tell you how you can > expect it to behave. In this case, the C API is broken on Linux by default, > so it becomes impossible to write fault tolerant applications in the face > of this feature. >
[sqlite] Segfault during FTS index creation from huge data
How about trying the sqlite3.exe command line utility. put your
sql for that operation in a text file, launch the program, open
the database, then read in the sql file with the .read command.
If the error occurs, then possibly sqlite3. if not then it is
probably something else.
Scott Doctor
scott at scottdoctor.com
--
On 4/28/2015 6:29 AM, Artem wrote:
> No, I'm sure that is not a problem in my software, it exactly
> error of the SQLite library. My software is very simple - it creates
> simple connection to the database with connection string like
> "Data Source={0};New=false;Journal
> Mode=Off;Synchronous=Off;FailIfMissing=True"
> and executes a query like
> INSERT INTO test_fts(test_fts) VALUES('rebuild');
> and that is all.
> I'm pretty sure because I got exactly the same error in SQLite Expert
> Professional -
> popular desktop sqlite-management software, that uses another
> sqlite driver.
>
> P.S. Source code of my function:
>
> Using conn As New SQLiteConnection(String.Format("Data
> Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True",
> "f:\Suggests\suggests.db"))
>
> conn.Open()
>
> Using command = conn.CreateCommand
> command.CommandText = "INSERT INTO suggests_fts(suggests_fts)
> VALUES('rebuild');"
> command.ExecuteNonQuery()
> command.Dispose()
> End Using
>
> conn.Close()
>
> End Using
>
> P.S. I can send the database to someone who can try, 19 GB in
> rar-archive.
>
>> Getting "NoMem" sounds very much like a memory leak somewhere, with
>> the most likely place being your own application, followed by the
>> wrapper you are using, the FTS code and lastly the SQLite core.
>> Lastly because the SQLite core is extensively tested with an
>> explicit emphasis on not leaking memory (or other resources) in the
>> first place and secondly recovering gracefully from memory allocation
>> failures.
>> Also, since you have swapped out SQLite versions and even operating
>> systems without eliminating the problem, it seems rational to look
>> into the parts that have remained the same.
>> Maybe you could run a test on Linux under the control of valgrind
>> and have its memcheck tool take a look.
>> -Urspr?ngliche Nachricht-
>> Von: Artem [mailto:devspec at yandex.ru]
>> Gesendet: Dienstag, 28. April 2015 14:36
>> An: General Discussion of SQLite Database
>> Betreff: Re: [sqlite] Segfault during FTS index creation from huge data
>>> On 04/03/2015 10:16 PM, Artem wrote:
>>>> Hi!
>>>>
>>>> The situation is like that. There?s a SQLite database with around 3
>>>> billion records. Each record consists of a certain CHAR field and several
>>>> other additional fields with different types. The file size is approx. 340
>>>> gb. The maximum content length in the doc field is 256 symbols, the
>>>> content is in Russian.
>>>>
>>>> I?m trying to create a full-text index, but it results in a Segmentation
>>>> Fault error. I?ve been trying to create it in different possible ways,
>>>> both under Windows (with SQLite Expert and my own .NET software, including
>>>> one with x64 architecture) and Linux (both Ubuntu and Centos). I?ve even
>>>> compiled sqlite from the sources, having included necessary flags for FTS3
>>>> and FTS4, but every time I get one and the same error.
>>> This does sound like a real problem, but one that might be difficult
>>> to track down.
>>> Are you able to get us a stack trace of the crash? Ideally one from a
>>> build with compiler options "-g -DSQLITE_DEBUG" set.
>>> Thanks,
>>> Dan.
>> Hi, Dan. Now I can't to do this because I haven't Linux on my PC.
>> But I tried to create FTS table again (now it was another database with
>> 1 350 000 000 rows, smaller than before). And I got the same error (out of
>> memory) on function:
>> internal override SQLiteErrorCode Reset(SQLiteStatement stmt)
>> in file SQLite3.cs
>> It returns System.Data.SQLite.SQLiteErrorCode.NoMem.
>> I home it helps.
>> P.S. It is latest version of SQLite.Net compiled in Visual Studio 2012.
>>>> I?ve tried two options:
>>>> - creating a contentless FTS4, when content is stored in a regular
>>>> table, and FTS-table contains only index (create virtual table
>>>> docs_fts using fts4(content='docs'... )
>>>> - creating a full-fledged FTS table from a regular one (insert into
>>>> do
[sqlite] Thoughts on storing arrays of complex numbers
If you are working with linear algebra type matrices, then simply make a column that represents each element. For example, a Jones matrix which is 2x2 would have four columns. Then each set of matrices are represented by a row. Best way to handle is to visualizes how you would use the numbers. If you are using recursive numerical techniques that requires algorithmic access, then a structure that supports subscript accessing is most efficient (wjicj means tables of numbers). If you are dealing with large data sets that follow a defined size, say x number of samples per test yielding y number of calculated coefficients, then the data tells you to have two tables, one for input data one for output data, with the number of columns defined by your mathematical variables. Number one rule is to keep it simple and obvious. Using a blob seems counter productive as you need to access your data by some rules, which seems to indicate a straight forward table of numbers. Write them out on a sheet of paper like you would do in a math class. The structure of the table will become self evident. Scott Doctor scott at scottdoctor.com On 4/24/2015 6:37 AM, Drago, William @ CSG - NARDA-MITEQ wrote: > All, > > I'm trying to avoid re-inventing the wheel. Is there a best or generally > accept way to store arrays of complex numbers? I'm considering the following: > > I could have two blob fields in my table. One for the real parts and one for > the imaginary. (I don't like this.) > Or, I could use a single blob field and concat the real and imaginary parts > into one long blob. (I like this.) > Or, I could store pairs in the blob > (realimaginaryrealimaginaryrealimaginaryrealimaginary). (I like this.) > > Or maybe there's a real nifty way to handle complex numbers that I haven't > thought of. > > Thanks, > -- > Bill Drago > Senior Engineer > L3 Narda-MITEQ<http://www.nardamicrowave.com/> > 435 Moreland Road > Hauppauge, NY 11788 > 631-272-5947 / William.Drago at L-3COM.com<mailto:William.Drago at L-3COM.com> > > > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any > attachments are solely for the use of the addressee and may contain > information that is privileged or confidential. Any disclosure, use or > distribution of the information contained herein is prohibited. In the event > this e-mail contains technical data within the definition of the > International Traffic in Arms Regulations or Export Administration > Regulations, it is subject to the export control laws of the U.S.Government. > The recipient should check this e-mail and any attachments for the presence > of viruses as L-3 does not accept any liability associated with the > transmission of this e-mail. If you have received this communication in > error, please notify the sender by reply e-mail and immediately delete this > message and any attachments. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
