Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[sky5walk]

I recently tried using Coalesce() to check existence of a column name.
SELECT DISTINCT COALESCE((SELECT 'AColumnThatDoesNotExist' FROM TD), -999)
FROM TD;
But, the SQL error code dominates? "no such column: AColumnThatDoesNotExist"
Had to resort to:
SELECT name FROM pragma_table_info('TD') WHERE name =
'AColumnThatDoesNotExist';
And oddly, pragma_table_info() fails below if the Table TD is unadorned?
SELECT name FROM pragma_table_info(TD) WHERE name =
'AColumnThatDoesNotExist';

On Wed, Mar 11, 2020 at 2:20 PM Richard Hipp  wrote:

> On 3/11/20, Hick Gunter  wrote:
> > While ORACLE does state that COALESCE will short circuit,
>
> A similar problem was reported on a ticket here:
> https://www.sqlite.org/src/tktview?name=3c9eadd2a6  (The problem
> reported on that ticket might not seem to be the same at first glance,
> but deep down they are both the same issue.)
>
> I have a patch to fix the problem on a branch
> (https://www.sqlite.org/src/timeline?r=do-not-factor-functions) which
> you can experiment with. More changes and analysis are needed prior to
> landing on trunk.  I cannot guarantee that such a landing will in fact
> occur, though it seems more likely than not at this point.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Cannot export 'sqlite3' file to CSV

[sky5walk]

Easier still: Install https://nightlies.sqlitebrowser.org/latest/
Then open your sqlite database(s) directly within the browser.
There you can run queries and/or export to csv.

On Thu, Feb 27, 2020, 4:54 AM Shawn Wagner  wrote:

> Use the standard sqlite3 shell program:
> https://www.sqlite.org/cli.html#csv_export
>
> On Thu, Feb 27, 2020, 1:48 AM Rachael Courts 
> wrote:
>
> > Hi All,
> >
> > I'm a couple of months into my PhD, studying bioacoustics. I am using
> > SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV
> files
> > to import into MATLAB for further analyses.
> >
> > Currently I have attempted the data export function, selecting which
> > database and table I would like to export, however no option for 'CSV'
> > exportation is available. The only options that appear are 'JSON, SQL,
> > HTML, and PDF' which can't be read into excel.
> >
> > I have also attempted the queries tab in SQLiteStudio, in an attempt to
> > write code for exportation. This did not work.
> >
> > I also attempted opening the command window on my computer, to change the
> > '.sqlite3' file to 'CSV' but it seems that the '.sqlite3' file doesn't
> have
> > a '.db' attached, and therefore will not open in the command window for
> > editing.
> >
> > I'm seeking ANY advice on how to export these files into CSV, so they can
> > be read in excel, and easily imported into MATLAB.
> >
> > I've attached an example file, if this helps.
> >
> > I hope to hear from someone soon,
> > Rachael
> >
> >
> > Sent from Outlook
> > ___
> > 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] DRH interview on why/how SQLite succeeded

[sky5walk]

In the ever expanding bloat of tooling, DRH is my hero.

On Wed, Feb 19, 2020 at 2:56 PM Stephen Chrzanowski 
wrote:

> I just finished listening to this.  Really cool.
>
> Thanks for ALL of your hard work SQLite team.  I appreciate it sincerely.
>
>
> On Wed, Feb 19, 2020 at 12:39 PM Simon Slavin 
> wrote:
>
> > 
> >
> > Podcast / transcription of DRH interview:
> >
> > " We talked to Richard about the history of SQLite, where it came from,
> > why it succeeded as a database, how its development is sustainably funded
> > and also how it’s the most widely deployed database engine in the world.
> "
> >
> > Not a formal treatment, but you can piece together what's said and get a
> > lot of information on how SQLite developed into what it is today, who is
> in
> > the development team, and how it is funded through the SQLite Consortium.
> > Plus random anecdotes about computing over the last 40 years.
> > ___
> > 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] WITHOUT ROWID tables

[sky5walk]

'rowid' is a common name and could be in anyone's schema regardless of
table type.

On Mon, Feb 17, 2020 at 2:35 AM x  wrote:

> Re my earlier post (which didn’t go out to mailing list)
>
>
> sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK
> returns false if table tblName is a without rowid table
>
> seems to work for me.
>
> Sent from Mail for
> Windows 10
>
> 
> From: sqlite-users  on
> behalf of Simon Slavin 
> Sent: Sunday, February 16, 2020 6:11:02 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] WITHOUT ROWID tables
>
> On 16 Feb 2020, at 6:00pm, Bernardo Ramos  wrote:
>
> > I've got no rows for both with and without rowid tables:
>
> 
>
> " as of SQLite version 3.30.0 on 2019-10-04 "
> ___
> 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] WITHOUT ROWID tables

[sky5walk]

Ok, not ideal. Still confusing, but I see the difference.
For my code, I know the schemas. I guess a SQL builder could offer up query
options to the user browsing new databases.

On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin  wrote:

> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
>
> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> table.
> >>
> > Confused...What if I made an index on a ROWID table?
> > CREATE INDEX "Z" ON "DOC" ("n_id");
>
> The parameter in index_info() is normally the name of an index.  So if you
> create an index "Z" and ask for index_info("Z") you will get information on
> that index.
>
> If you create a WITHOUT ROWID table with name 'Y", and ask for
> index_info("Y") you will get information on the primary key of that table.
>
> If both exist, you get information about the index.
>
> 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

Re: [sqlite] WITHOUT ROWID tables

[sky5walk]

>
> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> table.
>
Confused...What if I made an index on a ROWID table?
CREATE INDEX "Z" ON "DOC" ("n_id");
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[sky5walk]

SQLite is your everywhere database, except on servers ;)

On Mon, Jan 27, 2020 at 9:12 PM sub sk79  wrote:

> How about Seamless, Integrated or Baked-in?
>
> -Neal
>
> On Monday, January 27, 2020, Warren Young  wrote:
>
> > On Jan 27, 2020, at 3:18 PM, Richard Hipp  wrote:
> > >
> > > "serverless" has become a popular buzz-word that
> > > means "managed by my hosting provider rather than by me.”
> >
> > “Serverless” it a screwy buzzword anyway, because of course there’s still
> > a server under its new meaning.
> >
> > My vote?  Keep using the term.  We were here first.
> >
> > This is an ancient problem.  It is why is any serious dictionary the
> count
> > of definitions considerably exceeds the count of headwords.  These new
> > kids?  “serverless, sense 2.”
> > ___
> > 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] New word to replace "serverless"

[sky5walk]

Client (only) db
Sequential db

On Mon, Jan 27, 2020, 5:27 PM Peter da Silva  wrote:

> Local?
>
> On Mon, 27 Jan 2020, 16:19 Richard Hipp,  wrote:
>
> > For many years I have described SQLite as being "serverless", as a way
> > to distinguish it from the more traditional client/server design of
> > RDBMSes.  "Serverless" seemed like the natural term to use, as it
> > seems to mean "without a server".
> >
> > But more recently, "serverless" has become a popular buzz-word that
> > means "managed by my hosting provider rather than by me."  Many
> > readers have internalized this new marketing-driven meaning for
> > "serverless" and are hence confused when they see my claim that
> > "SQLite is serverless".
> >
> > How can I fix this?  What alternative word can I use in place of
> > "serverless" to mean "without a server"?
> >
> > Note that "in-process" and "embedded" are not adequate substitutes for
> > "serverless".  An RDBMS might be in-process or embedded but still be
> > running a server in a separate thread. In fact, that is how most
> > embedded RDBMSes other than SQLite work, if I am not much mistaken.
> >
> > When I say "serverless" I mean that the application invokes a
> > function, that function performs some task on behalf of the
> > application, then the function returns, *and that is all*.  No threads
> > are left over, running in the background to do housekeeping.  The
> > function does send messages to some other thread or process.  The
> > function does not have an event loop.  The function does not have its
> > own stack. The function (with its subfunctions) does all the work
> > itself, using the callers stack, then returns control to the caller.
> >
> > So what do I call this, if I can no longer use the word "serverless"
> > without confusing people?
> >
> > "no-server"?
> > "sans-server"?
> > "stackless"?
> > "non-client/server"?
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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] List of innocuous functions?

[sky5walk]

Doh! I'm on 3.30.0.
Gotcha, thanks.

On Sat, Jan 25, 2020 at 8:22 PM Richard Hipp  wrote:

> On 1/25/20, sky5w...@gmail.com  wrote:
> > SELECT DISTINCT name FROM pragma_function_list
> > --WHERE (flags & 0x20)!=0 -- no such column: flags
> > ORDER BY name;
> >
> > Works if I drop the WHERE.
> > Is there a special compile flag that must be used?
>
> You need to be using SQLite 3.31.0 or later.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] List of innocuous functions?

[sky5walk]

SELECT DISTINCT name FROM pragma_function_list
--WHERE (flags & 0x20)!=0 -- no such column: flags
ORDER BY name;

Works if I drop the WHERE.
Is there a special compile flag that must be used?

On Fri, Jan 24, 2020 at 5:42 PM Brian Curley  wrote:

> separate but somewhat related question, based on the response:
>
>Has any thought been given to updating the documentation to cover those
> pragmas that have been upgraded to selectable entities?
>
> I've only been able to find a handful of references along the way, such as
> pragma_table_info, pragma_index_info, and the aforementioned
> pragma_function_list, at least that I recall. Any sort of listing of what's
> available like these?
>
> I'd think that it would be helpful in the peanut gallery. Thanks!
>
> Regards.
>
> Brian P Curley
>
>
>
> On Fri, Jan 24, 2020 at 4:12 PM Richard Hipp  wrote:
>
> > SELECT DISTINCT name
> >FROM pragma_function_list
> >  WHERE (flags & 0x20)!=0
> >  ORDER BY name;
> >
> > On 1/24/20, Peter Kolbus  wrote:
> > > Is there any documentation showing, or an easy way to generate, the
> exact
> > > list of SQLite-provided functions that are innocuous?
> > >
> > > I’d like to turn on the new SQLITE_TRUSTED_SCHEMA but support a variety
> > of
> > > applications and am hoping for something to guide analysis.
> > >
> > > Thanks
> > > -Peter
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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] Questions about your "Performance Matters" talk re SQLite

[sky5walk]

haha, that is a mangled way of saying I wrapped my db functions in a dll
for multiple app use. I did not expose this config setting as I never knew
its impact. To be honest, I still don't. ;)

On Fri, Jan 3, 2020 at 5:18 PM Tim Streater  wrote:

> On 03 Jan 2020, at 22:08, sky5walk  wrote:
>
> > Querying the config state is helpful for a dll wrapped database, ...
>
> What's one of them?
>
>
> --
> Cheers  --  Tim
> ___
> 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] FW: Questions about your "Performance Matters" talk re SQLite

[sky5walk]

Well, I told you I'm getting SQLITE_MISUSE so that kinda answers your side
question?
I am only interested in this topic for the performance gain so quoted.
I need to create a personal test case(my use model) to verify these
statements.
Querying the config state is helpful for a dll wrapped database, but I can
adapt without the '_get()' function.

On Fri, Jan 3, 2020 at 2:02 PM Keith Medcalf  wrote:

>
> On Friday, 3 January, 2020 11:32, sky5w...@gmail.com wrote:
>
> > Is there a query function for these and other config settings?
> > I see no sqlite3_config_get() in sqlite3.h.
>
> No.  There are config options to get specific config data where that might
> be useful.
> Otherwise, you simply set the configuration to match your requirements.
>
> That is, if you need to have configuration X, Y, and Z then just set it
> the way you want it.  Either the configuration is set that way or you get
> an error return (you do check return codes right, that is what they are
> for).  There is not really much point in doing "if memstatus is on turn it
> off" when you can just turn it off.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] FW: Questions about your "Performance Matters" talk re SQLite

[sky5walk]

Is there a query function for these and other config settings?
I see no sqlite3_config_get() in sqlite3.h.

On Fri, Jan 3, 2020 at 11:36 AM Keith Medcalf  wrote:

> On Friday, 3 January, 2020 09:30, sky5w...@gmail.com wrote:
>
> >I get SQLITE_MISUSE when attempting
> >sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
> >immediately after opening a db connection?
> >My connection has THREADSAFE = 1.
>
> That is correct.  You must configure the library before it is initialized,
> not after.
>
> https://sqlite.org/c3ref/config.html
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> ___
> 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] FW: Questions about your "Performance Matters" talk re SQLite

[sky5walk]

I get SQLITE_MISUSE when attempting
sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
immediately after opening a db connection?
My connection has THREADSAFE = 1.

On Thu, Jan 2, 2020 at 7:32 PM Keith Medcalf  wrote:

>
> Indeed turning off memstatus leads to a 500% (from ~3s to ~0.5s)
> performance increase.
> Changing the threading mode or the indirection level of the mutexes calls
> seems to have no significant effect.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users  On
> >Behalf Of Richard Hipp
> >Sent: Thursday, 2 January, 2020 16:00
> >To: SQLite mailing list 
> >Subject: Re: [sqlite] FW: Questions about your "Performance Matters" talk
> >re SQLite
> >
> >On 1/2/20, Barry Smith  wrote:
> >> One thing that really stands is “creates 64 threads that operate on
> >> independent tables in the sqlite database, performing operations that
> >should
> >> be almost entirely independent.”
> >>
> >
> >Looking at the main.c file
> >(https://github.com/plasma-
> >umass/coz/blob/master/benchmarks/sqlite/main.c)
> >it appears that the test creates 64 separate database connections,
> >each with a separate in-memory database.
> >
> >There are two sources of contention here:
> >
> >(1) SQLite keeps track of the total amount of memory it is using on
> >all threads.  So for each malloc() and free() it has to take a mutex
> >to increase or decrease the counters.  This is probably the primary
> >source of contention.  It can be disabled by running:
> >
> >sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
> >
> >early in main(), before any other SQLite interface calls.  Make that
> >one change and I suspect that most of the thread contention will go
> >away.
> >
> >(2) SQLite has a single PRNG used by all threads.  And so there is a
> >mutex that has to be taken whenever a new random number is generated.
> >But the workload does not appear to be using any random numbers, so I
> >doubt that this is an actual problem in this case.
> >
> >> I’d encourage you *not* to use cpu cycles as a proxy for runtime.
> >Dynamic frequency
> >> scaling can mess up these measurements, especially if the clock
> >frequency is dropped
> >> in response to the program’s behavior.
> >
> >The task requires X number of CPU cycles *regardless* of the clock
> >frequency.  If the clock slows down, then it takes more elapse time to
> >run those X cycles, but it does not increase or decrease the number of
> >cycles required.  So in that sense, counting the number of CPU cycles
> >is an excellent measure of effort required to complete the
> >computation.
> >
> >Furthermore, the idea that thread contention will cause the CPU clock
> >to slow down seems silly.  Technically, I suppose such a think might
> >actually happen - IF you do all of your work as multiple threads
> >within the same process and they all blocked on the same resource.
> >The point is, you shouldn't do that.  Instead of one process with 64
> >threads, how about 64 processes with one thread each.  Since they are
> >all doing different things (serving independent HTTP requests, for
> >example) they might as well each have their own address space.
> >Keeping each job in a separate process provides isolation for added
> >security.  And it completely eliminates the need for mutexes and the
> >accompanying thread contention.
> >
> >If SQLite runs faster for you when you make direct calls to
> >pthread_mutex_lock() rather than indirect calls, how much faster would
> >it run if you completely eliminated all calls to pthread_mutex_lock()
> >by putting each task in a separate process?
> >
> >
> >--
> >D. Richard Hipp
> >d...@sqlite.org
> >___
> >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] When not using threads: should I switch to single-thread mode

[sky5walk]

Another point being, fully normalized data can be a bear to extract.
I see penalities in my humble database reports, so I leave 3rd and 4th
normalization for managers wish lists. ;)

On Fri, Dec 27, 2019, 6:37 PM Simon Slavin  wrote:

> On 27 Dec 2019, at 9:57pm, Keith Medcalf  wrote:
>
> > Setting "SINGLETHREAD" does indeed disable the multithreaded sorters.
> When in one of the multithreaded modes, that query utilizes an average of
> 60% CPU, compared to 12% when running singlethreaded.
>
> So if I understand this right, SQLite in multithread mode can itself use
> multiple threads at once, which means it can use many cores at once, which
> means it might be faster, most likely for complicated queries which involve
> lots of different things to be done.
>
> I'm slightly stunned.  That had never occurred to me.  Thank you.
> ___
> 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] Causal profiling

[sky5walk]

Thanks for sharing!
Did his suggested optimization make it to a commit?

On Wed, Dec 25, 2019 at 10:46 AM Craig H Maynard  wrote:

> All,
>
> Just watched an interesting lecture by UMass professor Emery Berger on
> improving software performance:
>
> https://www.youtube.com/watch?v=r-TLSBdHe1A
>
> SQLite is discussed in the section on causal profiling, which begins at
> 34:12.
>
> Craig
>
> --
> Craig H Maynard
> Rhode Island, USA
> 401.413.2376
>
> ___
> 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] Things you shouldn't assume when you store names

[sky5walk]

Ok, I'll bite.
The 'current consensus' in any system is tenuous and not an arbiter of its
effectiveness.
In this case, data modelers hoping to save a column. arrggg.
It flies in the face of data normalization and pushes the problem down the
line.
Forgive my simple linear thinking on the immensely complex topic of 'your
name here and here and here'.
Sincerely,
alias  ;)

On Sat, Nov 9, 2019 at 2:26 PM Simon Slavin  wrote:

> Since I don't see many posts yet this weekend, please excuse one of mine
> which isn't exactly on charter.  Feel free to argue me out of posting in
> personal (offlist) email.
>
> In a previous job I got to see databases made up by all sorts of other
> people and organisations.  Every time I saw a field called 'firstname' or
> 'second name' or 'surname' or 'familyname' I groaned.  So I was nodding
> along as I read this:
>
> <
> https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
> >
>
> I think this one is unusually well-written.
>
> In case you want to know how best to handle personal names, the current
> consensus seems to be to use a single field containing the whole name,
> which can be searched by substring.  Computer systems for places with
> non-Roman character sets sometimes use two fields: name in local characters
> (Chinese, Devanagari, etc.) and name in Roman characters.
>
> Also note that current privacy legislation in the US and EU means you are
> not allowed to ask for anything like 'full legal name' unless you cannot
> run your business without it.  Ask them for their name, and store what they
> tell you, with the words in the order they gave them.  If you need to sort
> people in name order (think very hard about why, first), create a field
> called 'sort order' and populate it yourself.  Sorting is your problem, not
> that of the people you're sorting.
>
> Part of a continuing series including falsehoods about dates, times,
> places, street addresses, gender, relations, phone numbers, taxes, and
> amounts of money.
>
> Good luck, and watch your back.
> ___
> 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] Opposite of SQLite

[sky5walk]

etiLQS or SQLead or SQLdark
Haha

On Thu, Oct 10, 2019, 3:07 PM David Raymond 
wrote:

> SQLephantine
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Ned Fleming
> Sent: Thursday, October 10, 2019 2:55 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Opposite of SQLite
>
>
> > Someone asked:
> >
> >>> What the opposite of "Lite”?
> >
>
> SQLessLite
>
> --
> Ned
>
> ___
> 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] Import XLS file?

[sky5walk]

If you are uncomfortable with exporting to csv and importing csv from the
command line,
you could install DB Browser for SQLite,
https://github.com/sqlitebrowser/sqlitebrowser.
Then manually build your SQLite tables and cut and paste into them from the
spreadsheet.

On Tue, Oct 1, 2019 at 8:58 PM Jake Thaw  wrote:

> You might like to consider the xlsx virtual table I wrote last year:
>
> https://github.com/jakethaw/xlsx_vtab
>
> Note that this only works for xlsx files, and may have unexpected
> results in some circumstances (e.g. merged cells).
>
> On Wed, Oct 2, 2019 at 4:39 AM Winfried  wrote:
> >
> > Hello,
> >
> > I need to import an XLS file into SQLite.
> >
> > The  web site    doesn't seem to show
> how, and
> > searching the archives  here 
>  didn't
> > help.
> >
> > FWIW, the data are in the file's first sheet, while other sheets only
> > contain text infos about the data.
> >
> > Thank you.
> >
> >
> >
> > --
> > Sent from: http://sqlite.1065341.n5.nabble.com/
> > ___
> > 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] [EXTERNAL] Comparison of incompatible types

[sky5walk]

Good catch! I will check if I have similar errors, but printf() returns
TEXT, round() returns FLOAT. So, do your numerical comparisons BEFORE
formatting statements.

On Mon, Sep 16, 2019 at 2:45 AM Hick Gunter  wrote:

> This is well documented in https://sqlite.org/datatypes.html and
> https://sqlite.org/datatype3.html
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von kapil
> Gesendet: Samstag, 14. September 2019 11:15
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] [sqlite] Comparison of incompatible types
>
> Hi all,
>
> I was wondering whether sqlite does type checking for fields which are
> referenced in WHERE clause. Because when i tried to compare a field
> generated by printf function and tried to compare it with a float value,
> the comparison didn't work
>
> Eg.
> SELECT count.theme as theme, printf("%.2f",
> (count.num_sets*100.00/sum.total)) as percentage
>
> FROM count,sum
>
> WHERE percentage >= 5.00;
>
> It was not giving expected results.
> When i replaced printf with ROUND function, it worked.
> So i got to think whether this was because printf returns string value.
>
> If so then shouldn't there be type check on fields in comparisons so that
> user can get a meaningful error message or in this case, any error message
> at all
>
> Regards
> Kapil Garg
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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] round function inconsistent

[sky5walk]

Yes, I fear users are attempting simplistic banking apps with floating
point rounds. :(
May explain where millions of my pennies went in my last android stock sale!

On Fri, May 24, 2019 at 1:55 PM Warren Young  wrote:

> On May 24, 2019, at 7:10 AM, Jose Isaias Cabrera 
> wrote:
> >
> > Dr. Richard Hipp, on Friday, May 24, 2019 08:44 AM, wrote...
> >>> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> >>> exist?  Thanks.
> >>
> >> Consider these two queries:
> >>
> >>  SELECT round(3.255,2);
> >>  SELECT round(3.2548,2);
> >>
> >> Do you expect them to give different answers?
> >
> > 3.26
> > 3.25
>
> Go read this: https://www.floating-point-gui.de/
>
> Then install this extension:
>
> https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index
>
> Having done both, you’ll know why SQLite does what it does, and will have
> a solution for the problem where it matters.
>
> And it doesn’t always matter!  Anything dealing in real-world
> measurements, for example, generally doesn’t need more than 9 decimal
> digits of precision, depending on the measuring instrument, which is more
> than covered by an IEEE-754 double.  Measurement instruments are not
> absolutely precise: they have inherent inaccuracies, and electronic
> instruments have drift and other characteristics that can prevent them from
> giving the same answer given the same task.
>
> Since the measurement itself cannot be more precise than an IEEE-754
> double, any “error” in an answer computed from it is down in the noise
> floor of the measurement.  That is to say, two measurements taken
> back-to-back might give the same or greater error.
>
> This is no accident.  IEEE-754 was designed by people working in the
> physical sciences, and they knew all of this when they designed it.  It is
> what it was designed *for*.
>
> What IEEE-754 was *not* designed for was discrete math, such as monetary
> matters on scales smaller than a national economy.
> ___
> 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] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

[sky5walk]

Thank you for bringing topic back to original intent.
Clearly much is missing from the latest SQL standard. Debates aside, the
URL is equally lacking.

Yes, "own" extensions solve much of my needs.

On Thu, May 23, 2019, 7:26 AM J. King  wrote:

> On May 23, 2019 6:46:52 a.m. EDT, R Smith  wrote:
>
> >This is SQLite. Perhaps some of us could collaborate on a fork called
> >SQLbloat and put out standard libs/code/precompileds for versions of
> >sqlite with everything - bbq sauce and all, for when you don't need
> >Lite
> >- then you can specify that the queries are for SQLbloat and they will
> >run out the box directly and correctly.  Linux distro wars coming to an
>
> I find this a little condescending. There's a lot of reasons to like
> SQLite, and the aspect that sways me more than others is not "lite", but
> "zeroconf".
>
> I mainly use SQLite in PHP and Tcl, so using my own SQLite library is
> often not practical, and in the case of PHP, loadable extensions are
> disabled by default. I as a programmer am at the mercy of what
> distributions package---which is often SQLite in its default configuration,
> so a less-lite-but-still-zeroconf SQLessLite as the default configuration
> would be extremely valuable to me. At the same time, those working in tiny
> systems still have tons of compile-time options to keep things lean.
>
> In short, I'm skeptical that the choices Hwaci have made about what to
> exclude are necessarily beyond reproach or discussion. Derogatory
> references to  "SQLbloat" really don't further the cause of honest
> discussion.
>
>
> --
> J. King
> ___
> 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] SQL Features That SQLite Does Not Implement

[sky5walk]

Yes, I expected some pushback. However, my post was to save the next person
having to search for answers. I did not expect to requote the top line of
the page I listed?
"SQLite implements most of the common features of SQL. Rather than try to
list all the features of SQL that SQLite does support, *it is much easier
to list those that it does not.* Unsupported features of SQL are shown
below."

I am asking for a followup to the missing features list.
Similar to this: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

Yes, I am aware of the extension capabilities and will augment them with my
own.
Expect more questions in that effort. ;)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL Features That SQLite Does Not Implement

[sky5walk]

I'm often scrambling to decide whether to do complicated queries in SQL or
my own code?
...Getting oh so close to a working query, only to fail at function not
defined?!!

Please add a note to the omitted page that many basic math functions are
NOT supported. (sqrt,mod,power,stdev,etc.)
https://www.sqlite.org/omitted.html
I am curious why these are not available when there are switches for much
larger search functions like FTS345?

...now reading how to build my own extensions...
Thanks for SQLite!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Options used for precompiled dll for Windows?

[sky5walk]

Ok, "making" some headway. :)

; COMPILE SOURCE AS DLL:
;   Use MS Visual Studio Command Prompt with the appropriate target: x86 or
x64.
;   Ex. shortcut: x64 Native Tools Command Prompt for VS 2017
;   %comspec% /k "C:\Program Files (x86)\Microsoft Visual
Studio\2017\Community\VC\Auxiliary\Build\vcvars64.bat
;
;   Then create and execute a bat file with the following: (enable/disable
desired options)
;   cl sqlite3.c -DSQLITE_THREADSAFE -DSQLITE_API=__declspec(dllexport)
-link -dll -out:sqlite3.dll

SQLite Version= 3.28.0
SQLite Lib= 2019-04-16 19:49:53
884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
SQLite ThreadSafe = 1
SQLite Options:
COMPILER=msvc-1915
THREADSAFE=1
This approach is super fast and light, but it does not populate the version
fields within the dll? I need that for traceability.

So, needs more work or try compiling with nmake cmd and the edited
makefile.msc(which is huge). Maybe someone has a clue?

Thanks for SQLite!

On Fri, Apr 19, 2019 at 7:26 PM  wrote:

> I was afraid you'd say that.
> I wanted to edit the Windows x64 make file to reduce a lot of the options.
>
> SQLite Version= 3.28.0
> SQLite Lib= 2019-04-16 19:49:53
> 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
>
> Anyway, I iterated this sql query:
> "SELECT sqlite_compileoption_get(0-n until empty return); "
> SQLite Options Returned:
> COMPILER=msvc-1500
> ENABLE_COLUMN_METADATA
> ENABLE_DBSTAT_VTAB
> ENABLE_FTS3
> ENABLE_FTS4
> ENABLE_FTS5
> ENABLE_GEOPOLY
> ENABLE_JSON1
> ENABLE_RTREE
> ENABLE_STMTVTAB
> MAX_TRIGGER_DEPTH=100
> TEMP_STORE=1
> THREADSAFE=1
>
> Any chance to publish the make file used for the releases?
> I found this thousand liner in the amalgamated sqlite-autoconf-328
> folder.
> ~\sqlite-autoconf-328\Makefile.msc
> But it makes no mention of ENABLE_FTS5?
> Most of the other options are in there.
>
> Any examples to drop an option?
> Ex. OPT_FEATURE_FLAGS = $(OPT_FEATURE_FLAGS) -DSQLITE_ENABLE_RTREE=0 <--0|1
>
> Also, I read the mail list that some additional flag is required to export
> the functions in the dll?
>
> Thanks for SQLite!
>
> On Fri, Apr 19, 2019 at 9:36 AM Warren Young  wrote:
>
>> On Apr 18, 2019, at 2:37 PM, sky5w...@gmail.com wrote:
>> >
>> > Searched a while...what was the makefile or the options used to create
>> the
>> > latest precompiled sqlite3.dll?
>> > https://www.sqlite.org/download.html
>> > --> sqlite-dll-win64-x64-328.zip
>>
>> Execute “PRAGMA compile_options;” and it’ll tell you.
>> ___
>> 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] Options used for precompiled dll for Windows?

[sky5walk]

I was afraid you'd say that.
I wanted to edit the Windows x64 make file to reduce a lot of the options.

SQLite Version= 3.28.0
SQLite Lib= 2019-04-16 19:49:53
884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50

Anyway, I iterated this sql query:
"SELECT sqlite_compileoption_get(0-n until empty return); "
SQLite Options Returned:
COMPILER=msvc-1500
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
MAX_TRIGGER_DEPTH=100
TEMP_STORE=1
THREADSAFE=1

Any chance to publish the make file used for the releases?
I found this thousand liner in the amalgamated sqlite-autoconf-328
folder.
~\sqlite-autoconf-328\Makefile.msc
But it makes no mention of ENABLE_FTS5?
Most of the other options are in there.

Any examples to drop an option?
Ex. OPT_FEATURE_FLAGS = $(OPT_FEATURE_FLAGS) -DSQLITE_ENABLE_RTREE=0 <--0|1

Also, I read the mail list that some additional flag is required to export
the functions in the dll?

Thanks for SQLite!

On Fri, Apr 19, 2019 at 9:36 AM Warren Young  wrote:

> On Apr 18, 2019, at 2:37 PM, sky5w...@gmail.com wrote:
> >
> > Searched a while...what was the makefile or the options used to create
> the
> > latest precompiled sqlite3.dll?
> > https://www.sqlite.org/download.html
> > --> sqlite-dll-win64-x64-328.zip
>
> Execute “PRAGMA compile_options;” and it’ll tell you.
> ___
> 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] Options used for precompiled dll for Windows?

[sky5walk]

Hi,
Searched a while...what was the makefile or the options used to create the
latest precompiled sqlite3.dll?
https://www.sqlite.org/download.html
--> sqlite-dll-win64-x64-328.zip

Thanks for SQLite!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users