[sqlite] Mozilla wiki 'avoid SQLite'

[Alex Bowden]

Oh dear.  So you think that a high level language is one that does things by 
calling a lower level language?  Stop embarrassing yourself.

A high level language is one where the language designers are free to use 
whatever structural concepts best fit the problems that the language is 
designed to address,  whereas a low level language is one where the language 
designers are restricted to using structural concepts that map directly onto an 
assumed hardware model.

The assumption that a low level language compiles to assembler is nonsense.  I 
don?t remember any of them doing this before C, and even later,  many compilers 
still compile directly to machine code.

Assuming that higher level languages compile to lower level languages is also 
nonsense.  And even for ones that do, and a classic example of a language 
designed to do this would be Ratfor, then provided that the lower level 
language is compiling to machine code, then the only thing that is being slowed 
is the build process.  Not runtime execution.   

Now 20 years ago, folk like you, but better informed, would have argued that 
well written lower level language code was more efficient than higher level 
language code.  And sometimes they would have been correct.  But note the 
qualification of well written.  And even then, this was debatable.  If a 
language provides efficient higher level data structures, or fine grain 
synchronisation of multiple threads,  then the user is more likely to use them 
than if they have to create them themselves for each platform.

However today, it is rarely true.

Good modern optimisers can achieve efficiency that few, if any, low level 
language programmers will ever achieve.

And the thing is, that those compilers can do far broader optimisation of 
higher level languages that don?t expose features like the ability to write to 
a random address.  The optimisation potential of C, beyond very local 
optimisation, is minimal.

I?ve been running the same little benchmark across every hardware and language 
platform that I?ve used, for over 30 years.  

And I confess, I was very surprised when Java passed C in about 2005.  And yes, 
that was Java compiled to byte codes with runtime JIT compilation of the byte 
codes to machine code.  But it was faster.

The trend hasn?t reversed.



> On 14 Jun 2015, at 23:42, Scott Doctor  wrote:
> 
> 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mozilla wiki 'avoid SQLite'

[Alex Bowden]

> On 15 Jun 2015, at 15:44, Scott Robison  wrote:
> 
> Too many of these technology based discussions (whether languages or
> operating systems or text editors or database engines or whatever) break
> down into almost a religious fervor of "this is the one true

You mean like your comment from this morning?

"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.?

Frame it.  Put it over your bed.  And use it to remind you to put your brain in 
gear, before opening your mouth.

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Alex Bowden]

logical?

It seems equally logical to me that one of A or B might be evaluated, and if it 
were false, then the other might not be evaluated.

And it would be logical to choose which of A or B to evaluated on a predicted 
cost and probability of an advantageous false result.

but hay.  Who said their could only be one logical approach.

Alex


On 24 Aug 2011, at 20:12, Carlos Rocha wrote:

> Don't know how SQLite should behave in this case, but seems logical to 
> me that A and B would force that A is always evaluated, and B is 
> evaluated only if A is true.
> I would change the order of the two betweens:
> 
> SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND 
> '2011-08-02' AND itemID BETWEEN 100 AND 200
> 
>> Hi all,
>> 
>> I have a table like this:
>> 
>> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
>> CREATE INDEX createIdx on t(createdAt);
>> 
>> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
>> current content.
>> 
>> When perfoming a Statement like this:
>> 
>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>> 
>> the analyzer always chooses the rowid index which results in a scan over
>> one million rows. It would have to scan only a few dozen rows if it
>> chose createIdx instead (which is also a covering index). Looking at the
>> sqlite_stat2 table shows that there is no data for the rowid index.
>> Could this be the reason for the suboptimal query plan? The choice works
>> as expected if itemID is a regular column with an index on it.
>> 
>> regards
>> gerd
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] does sqlite work with Apple iCloud and IOS 5

[Alex Bowden]

Andy

Simon's answer is totally nonsense.  I suspect that he has misunderstood 
something that he's read.

The only thing that he is right about, is that neither of us can tell you 
anything about iOS 5 until the cloud API is public.

However, what I can tell you, which isn't about iOS 5, is this.

Apple uses sqlite for pretty much all iOS based data storage.  You can use 
other storage mechanisms, but sqlite is the default.

Most code that has been developed for iOS (rather than ported to iOS) does not 
use sqlite directly.  It uses core data which is a persistent object store for 
objective C objects.  The default choice for storing these persistent objects 
is to let core data store them in sqlite.

Core data really is an excellent facility which is tightly integrated with 
Objective C, Xcode, with iOS's User Interface facilities, iOS's undo stack, 
etc..  It is unlikely that Apple would walk away from that as a primary storage 
mechanism.

If you have an iPhone app, then you are probably a registered developer, in 
which case you can watch the video's from the recent WWDC.  Search for any with 
iCloud in their name, and watch them.

Otherwise, why not spend the time from now until release, finding out what core 
data will do.

Alex


On 11 Oct 2011, at 17:34, Simon Slavin wrote:

> 
> On 10 Oct 2011, at 9:55pm, Andy Davidson wrote:
> 
>> I have an iPhone app that uses a sqlite base . Apple's iCloud is very cool
>> It automatically syncs your changes back to the cloud and out to all your
>> other devices. Does anyone know  what I need to do to get sqlite to work
>> with Apple's iCloud.
> 
> There's really no point to integrating them.  They both do similar things.  
> Just one stores data in a file an your hard disk (or networked server) and 
> the other stores data at Apple's server farm.
> 
> I'm afraid I can't give details until the iCloud APIs go public, which is not 
> yet.  The same is true of everything to do with iOS 5.  But basically yes, 
> stuff works, roughly the way you'd expect it to.
> 
> It's worth bearing in mind that your device's communications with the cloud 
> might be going through a poor quality phone connection, at phone connection 
> speeds.  You do not want to store more than a few K in the cloud.  Thirty or 
> forty important locations for your GPS device ?  Fine.  Every place you've 
> ever stopped ?  No.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

[Alex Bowden]

It's not your problem.

If the idiot user wants a slow machine,  that's his choice.

It certainly isn't your job to turn off sync in order to hide how slow Windows 
is.

On 30 Nov 2012, at 17:41, David de Regt  wrote:

> Hey all.  I've been struggling with a basic perf issue running the same code 
> on Windows vs. iOS and OSX.
> 
> Basic query set:
> CREATE TABLE test (col1 int, col2 text);
> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')
> 
> I'm coding this using the default C amalgamation release and using 
> prepare/etc. on all platforms in the exact same way (same very simple 
> DB-access class I made).  I realize that using a transaction around this 
> would vastly improve perf, but given the atomic nature of the app that this 
> test is simulating, it won't work to wrap it into transactions, so my goal is 
> to improve the atomic performance.  These are all being run on the same 
> Macbook Pro, with an SSD, running Windows via boot camp, OSX natively, and 
> iOS via the iOS simulator:
> 
> With defaults (pragma sync = on, default journal_mode):
> Windows: 2500ms
> iOS: 300ms
> OSX: 280ms
> 
> With pragma sync = off, journal_mode = memory:
> Windows: 62ms
> iOS: 25ms
> OSX: 25ms
> 
> Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
> scenario, so with sync on, it seems like something must be fishy for it to be 
> ~8-9x slower than the other platforms.  Is there something ridiculous about 
> the windows file system performance that hoses sqlite's open/read/write/close 
> transaction cycle?  Is there anything I can do, or just accept it and move 
> on?  With how that scales up, we may need to move to something like using 
> embedded MySQL or LocalDB on Windows to get the same performance as we see 
> with SQLite on other platforms, which seems quite ridiculous.
> 
> Thanks!
> -David
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Alex Bowden]

I can't wait to try

order by row_number desc


On 1 Jul 2013, at 10:33, Tony Papadimitriou  wrote:

> Is there a function (or method), e.g., row(), to return the sequence number 
> of the selected row?  This is not the same as ROWID.  row() should give a 
> sequence number always starting from 1 up the to the number of rows returned 
> by the view/select etc.
> 
> If not, then please add to the wish list.
> 
> TIA 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Alex Bowden]

> This would not be something you would sort by.  

And what if I do?

> It should be assigned a value only during final 'display' of the query after 
> all 'sorts' of operations are done with.

Oh great.  So the user is supposed to understand the implementation, in order 
to understand what the results will be.


This would be just another nail in the coffin of relationality and simplicity, 
on a minor whim.

On 1 Jul 2013, at 11:01, Tony Papadimitriou <to...@acm.org> wrote:

> 
> -Original Message----- From: Alex Bowden
> Sent: Monday, July 01, 2013 12:46 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOT therowid)
> 
> 
> I can't wait to try
> 
> order by row_number desc
> 
> 
> On 1 Jul 2013, at 10:33, Tony Papadimitriou <to...@acm.org> wrote:
> 
>> Is there a function (or method), e.g., row(), to return the sequence number 
>> of the selected row?  This is not the same as ROWID.  row() should give a 
>> sequence number always starting from 1 up the to the number of rows returned 
>> by the view/select etc.
>> 
>> If not, then please add to the wish list.
>> 
>> TIA
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Alex Bowden]

> Please!  Just because you can select something doesn't mean you have to be 
> able to sort by it.

There are a small number of exceptions,  each of which is a bodge.

But some bodges are worth the impact.

>  Can you sort by *

* is a very useful and largely harmless bodge.

There is fundamentally no reason why you shouldn't be able to to sort by *.  
But people usually care about the order of field in a sort.  

> (select * by table sort by *)?  

I'm sure there will be a SQL engine somewhere that will do it for you.

It would require no additional documentation or code.  In fact it might require 
extra, to prevent it.  It's pointless,  but benign.

> So, why make it sound like I don't know what I'm talking about?

I think you beat me to it.

> 
> -Original Message----- From: Alex Bowden
> Sent: Monday, July 01, 2013 2:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
> 
>> This would not be something you would sort by.
> 
> And what if I do?
> 
>> It should be assigned a value only during final 'display' of the query after 
>> all 'sorts' of operations are done with.
> 
> Oh great.  So the user is supposed to understand the implementation, in order 
> to understand what the results will be.
> 
> 
> This would be just another nail in the coffin of relationality and 
> simplicity, on a minor whim.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Alex Bowden]

> OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)

If you approach the government france and explain that you're not really very 
good at French,  but if they made the following list of Chinese language 
inclusions into French, then it would make it easier for you to write your 
"thank you letter" to your auntie Marie,  than I'm sure they would oblige.

And even if they didn't it wouldn't make you wrong.

You are proposing changes to a relational database management system that 
damage its relationality, on the basis that you don't understand how to use 
relational databases properly, and it would make your life easier.

In defending maintaining the relationality, I don't really need to be right.  
The onus is on you to justify the change that you propose.


On 1 Jul 2013, at 16:36, Tony Papadimitriou <to...@acm.org> wrote:

> OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)
> 
>> I'm sure there will be a SQL engine somewhere that will do it for you.
> 
> We're talking about SQLite here, aren't we?  If some other database can do 
> it, then you should also consider that it may also be able to do what this 
> 'row' function.  (e.g. MySQL).  So, your point it moot.
> 
>>> So, why make it sound like I don't know what I'm talking about?
>> I think you beat me to it.
> 
> No comment!
> 
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 6:17 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Alex Bowden]

The SQL standard has always been a moving feast, chasing the field 
implementations, perfectly capable of going back on it's earlier mistakes,  the 
main purpose of which, on a good day, is to promote standardisation of SQL 
implementations and try and keep to the Relational Theory model where practical 
considerations allow.

So, if the SQL standard has drifted toward requiring "… in the order in which 
they are defined in the table definition…"  to be meaningful,   then this is an 
oversight that would likely be corrected when somebody has an in the field SQL 
database which, correctly, enforces no such concept.

People should not be encouraged to become more dependent on the use of such 
temporary misfeatures.

In context, the particular focus of your objection to the relational approach,  
seems irrelevant.

>> "sort by *" would imply that the order of the columns returned by '*' is
>> meaningful, which it is not.  "sort by the arbitrary order produced by
>> 'select *'" isn't even deterministic.  
> 
>  In SQL column order *is* deterministic, so the sort order would also
>  be deterministic.  Likely meaningless, but still deterministic.


Sort order isn't necessarily deterministic even if we know the column order.  
So the possibility that we may not know it, makes life no worse.
 

On 2 Jul 2013, at 05:30, Jay A. Kreibich  wrote:

> On Mon, Jul 01, 2013 at 10:52:20PM -0400, James K. Lowden scratched on the 
> wall:
> 
>> "select *" is shorthand for "all columns". You'll note that what's
>> returned isn't some kind of special '*' column, but all columns.  The
>> order in which the columns are returned isn't meaningful because the
>> colums have labels -- names -- to tell you which is which.  Rearranging
>> the column order doesn't change the answer.  
> 
>  That's not quite true.
> 
>  What you say is more or less true in pure Relational Theory.  Under
>  Relational Theory, relational attributes (columns) are a proper set.
>  The columns have no defined order (just as rows have no defined
>  order), and can only be definitively reference by name.
> 
>  In SQL, columns are *not* a set.  The order of the columns in any SQL
>  query or operation is strictly defined.  Columns cannot be referenced
>  by name, because SQL allows name-less columns (SELECT 1, 1, 1;) and
>  multiple columns with the same name (SELECT 1 A, 1 A, 1 A;).  SQL
>  doesn't even strictly define the column name for a calculated column
>  (SELECT avg( 1 )) and allows the DB to make up its own names.  SQLite
>  used to have several PRAGMAs to control short and long column names.
> 
>  Rather, in SQL, a column is definitively defined by its positional
>  index in the table or result set.  This is also why so many SQL APIs
>  allow you to fetch column values by index, rather than by name (which
>  would be a totally broken and dangerous API if columns could move
>  around).  It gets pretty messy...  The SQL standard goes to some
>  length to define a specific column order for stuff like JOIN operations,
>  including edge-case details like NATURAL JOINs where the number of
>  columns is reduced and somewhat ambiguously named.
> 
>  While rearranging the column order may not functionally change the
>  answer, a database is not given that flexibility in SQL.  For
>  example, "SELECT *" *must* return the columns in the order they are
>  defined in the table definition.  It isn't that most databases just
>  happen to do this-- the column order is actually predicated by the
>  standard.
> 
>> "sort by *" would imply that the order of the columns returned by '*' is
>> meaningful, which it is not.  "sort by the arbitrary order produced by
>> 'select *'" isn't even deterministic.  
> 
>  In SQL column order *is* deterministic, so the sort order would also
>  be deterministic.  Likely meaningless, but still deterministic.
> 
> 
>   -j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Alex Bowden]

> State why you don't
> like it and move on. Don't contribute any code that might address the idea.
> The rest? It is not useful.

It is useful.  It help stops people who don't understand the concept of 
relational, screwing up the system.

You just disagree with that.


On 7 Jul 2013, at 22:24, Scott Robison  wrote:

> On Sun, Jul 7, 2013 at 11:34 AM, Keith Medcalf  wrote:
> 
>> 
>> Despite the long diatribes you have not indicated a single case in which
>> the set ordinal of the row would be of any use whatsoever.
>> 
>> For 40 years we have gotten on without it, so yes, it is only for you
>> newbies that somehow think there is a use for it, and for 40 years no one
>> has ever come upon a real need to have set ordinals generated by the
>> database engine as part of the cursor processing (for various definitions
>> of database engine).
>> 
>> Kind of makes you wonder what you might have overlooked or are assuming
>> incorrectly doesn't it?
>> 
>> 
> If you wish to posit that a row number function is not useful, that's fine.
> You don't have to like it or support its inclusion. To claim that there has
> not been a single case described where it would be of any use whatsoever
> clearly demonstrates that you have not bothered reading the messages you're
> replying to! The cases have been described, you simply disagree with them.
> There *is* utility in being able to have a row or rank number function. For
> example, lets say you are Billboard Magazine and you keep track of record
> sales. On a weekly basis you generate a list of the top 40 selling records.
> One of your customers wants that list in descending order. There are
> certainly ways you can accomplish this without a rank function, but a rank
> function can make the SQL clearer, easier to maintain.
> 
> At my last job I used Microsoft SQL Server (as that was technology selected
> before I was hired) and used a rank number capability provided in T/SQL as
> part of a query to generate a list of files in a particular order. The
> order was important in my use case (prioritization of a set of files
> comprising multiple terabytes of data that needed to be synchronized, but
> where not all data was equally important). I didn't need to send all the
> sort criteria to each remote site, I just needed to ensure the order was
> maintained. Could I have done it in a different way? Yes. But as mentioned
> on multiple occasions, the idea of the rank or row number function is not
> that there is no other way to accomplish the task, it is about convenience.
> 
> Now, you are free to dislike my approach to solving the problem. I'm sure
> there are better ways to accomplish what needed to be done. Regardless of
> that, what I did was convenient and got the job done. Had that capability
> not been available to me, I could have done it in another way. No one is
> arguing that there isn't another way to accomplish the stated task.
> 
> Regardless of *ANY OF THE ABOVE*: How about exercising some common courtesy
> and not resort to denigration of another person's knowledge or apparent
> skills or lack thereof. You don't like the idea, fine. State why you don't
> like it and move on. Don't contribute any code that might address the idea.
> The rest? It is not useful.
> 
> SDR
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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