Re: [sqlite] [EXTERNAL] Common index for multiple databases

[Jim Callahan]

Back off from the index semantics for a second.

If Gunter Hick has captured at the application level of what you are trying
to do (remote databases),

I think the name of the concept we are looking for is: "Eventual
Consistency".
 https://en.wikipedia.org/wiki/Eventual_consistency

SQL databases (as opposed to Xbase) are built around the concept of ACID
transactions which
implies a centralized database where everything can be reconciled
immediately. The delayed processing
of "Eventual Consistency" is implemented at the application level; for
example there are applications written
for PostgreSQL to make complete replica databases "eventually consistent".
That would be overkill in
your case, but the concept that "eventual consistency" has to be
implemented in the application space
above the core SQL level.

So, at the application level, what you want to do is create subset tables
for a particular purpose.
Good news! SQL is fantastic at creating of subsets of rows and columns of
data.

In a new SQLite database ATTACH the main database and create a query that
describes the subset
of data you need and then wrap that query in a "CREATE TABLE AS query;"
statement. That with create a subset of the data (without indexes). Rebuild
the indexes you
need in the local table (do not attempt to copy indexes!).

The application logic needs to use the subset database to build a
time-stamped transaction
to run against the main database.

Your application needs a module that accepts all the remote time stamped
transactions
and queue them up to feed into the main database. You have to decide how
your
application should handle conflicting transactions (see the "eventual
consistency"
article).
https://en.wikipedia.org/wiki/Eventual_consistency

Then run your consistent application level transaction log against the main
database.

Don't worry about "copying" indexes.  As Dr. Hipp suggests, copying indexes
is a non-starter in the SQL world.
Just copy the data and rebuild your indexes on the subset data.  If you
want to assure you don't
create a duplicate customer number; copy the column of customer numbers to
a separate table;
reindex it and join it to your subset table.

So, in short, you can't copy indexes, but you can copy any subset of data
and re-index that subset.
"Eventual consistency" has to be handled at the application level above the
SQL core (which only
handles "ACID consistency").

HTH

Jim Callahan
Callahan Data Science LLC
Orlando, FL




On Fri, Aug 3, 2018 at 5:41 AM, Hick Gunter  wrote:

> This is what I think you are asking:
>
> - You have a "main office" computer that holds the current information on
> "everything"
> - You have several different categories of users (technicians, accountant,
> ...) that require different subsets of the data
> - Each user has his own computer, that may be disconnected from the "main
> office", e.g. for "field work"
> - When a user's computer is "attached" to the "main office", it needs to
> be "synchronized".
>
> If this is correct, then you require either a "distributed" DBMS that
> handles synchronization by itself, or you need to do some programming both
> inside and outside  of SQLite.
>
> This may be appropriate for you:
>
> - As already stated, SQLite has just 1 file to hold all tables and indexes
> of the schema. Make this identical for all users. You can always leave the
> tables empty with just minimal overhead.
> - Downloading from "office" to "user" is accomplished by using ATTACH to
> make the "user" and "office" databases accessible. Just run the appropriate
> INSERT ... INTO statements. Check the authorizer callback to allow
> different users to access only the tables/fields that they are allowed to
> see. Limiting the rows requires an appropriate WHERE clause.
> - "Work" done by the user while offline needs to be saved in a worklog
> table.
> - Uploading the "work" of a user would copy the new worklog records into
> the "office" worklog table, just another INSERT ... INTO, to be processed
> by a dedicated sync application.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von John R. Sowden
> Gesendet: Donnerstag, 02. August 2018 19:12
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] [sqlite] Common index for multiple databases
>
> I have been reviewing sqlite for a couple of years, but still use foxpro.
> I have a question regarding an index issue.
>
> Currently I have several types of databases (in foxpro, one per file) that
> all point to an index of a common field, a customer account number.  The
>

Re: [sqlite] This is driving me nuts

[Jim Callahan]

> why, when you've got 16 GB ram, does a 6.4 GB vector cause any problems?

Architecturally, 4 GB is like the sound barrier -- you may get turbulence
or a sonic boom.

Because 4 GB was the limit for 32 bit word size any residual 32 bit code or
32 bit assumptions anywhere in the stack (or CPU) could create issues.
The operating system attempts to present a unified linear address space,
but under the hood all sorts of kludges may exist.

Windows AWE exists because of 4 GB.
https://en.wikipedia.org/wiki/Address_Windowing_Extensions

Thus, from an architectural standpoint, I would try to avoid allocating 4
GB of RAM  to one object under ANY operating system, but if I had to do it
I would try Linux because that is what the supercomputers use
(supercomputers have big memory in addition to fast speed and tens of
thousands of cpus).
https://www.zdnet.com/article/linux-totally-dominates-supercomputers/

Twenty-five years ago developers were more concerned about Y2K, than beyond
4 GB.
To the extent anyone in the Windows NT world thought about 4 GB of RAM, it
was allocating memory
between the operating system and applications WITHIN 4 GB (ie. do we split
4 GB of RAM between opsys:apps 2:2 or 1:3).

Jim Callahan
Orlando, FL




On Mon, May 28, 2018 at 5:35 AM, Rowan Worth <row...@dug.com> wrote:

> On 28 May 2018 at 17:29, x <tam118...@hotmail.com> wrote:
>
> > I’ve just discovered the thread in the original app decreases the
> > available memory by around 4 GB. Are they really that expensive?
>
>
> A thread itself is not expensive in terms of memory.
>
>
> > It has very little data of its own
>
>
> Either this statement is wrong, or you've misattributed the 4 GB of memory.
> -Rowan
> ___
> 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] 3.24 draft - upsert

[Jim Callahan]

Per Keith Medcalf's comment on the ancient master file merge,
I found this inscription concerning batch processing in the elephant's
graveyard:

https://www.ibm.com/support/knowledgecenter/zosbasics/com.ibm.zos.zconcepts/zconc_batchscen2.htm

Jim Callahan

On Wed, May 9, 2018 at 10:06 AM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> This is a baby implementation of the master file merge from the early part
> of the last century (after the stone knives but somewhat before
> bearskins).
>
> Take two tables, one mounted on tape drive A, with output to tape drive B,
> updated from a transaction file on tape drive C.  Start Friday night.  Come
> Monday morning your master is now on drive B and up-to-date.
>
> --
> 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 [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
> >Sent: Wednesday, 9 May, 2018 03:48
> >To: SQLite mailing list
> >Subject: Re: [sqlite] 3.24 draft - upsert
> >
> >On 5/9/18, Olivier Mascia <o...@integral.be> wrote:
> >> About:
> >>
> >> "Column names in the expressions of a DO UPDATE refer to the
> >original
> >> unchanged value of the column, before the attempted INSERT. To use
> >the value
> >> that would have been inserted had the constraint not failed, add
> >the special
> >> "excluded." table qualifier to the column name."
> >>
> >> Why using 'excluded' wording for this?
> >
> >Because that is what PostgreSQL does.  I also thought that "new"
> >would
> >have been a better choice, but they didn't consult me.  :-)
> >
> >--
> >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] How to get the entry from sqlite db without primary key or rowid order?

[Jim Callahan]

If the prefered ORDER BY clause is awkward;
How large is your table?
 and is it on a Solid State Disk (SSD) with low seek time?

If the table is small (less than 100,000 rows) and you are querying by an
indexed field (such as the Primary Key)
you could just do three (or N) SELECT statements to guarantee the order.

SELECT * FROM NEWFOLDER WHERE ID = 3;
SELECT * FROM NEWFOLDER WHERE ID = 1;
SELECT * FROM NEWFOLDER WHERE ID = 2;

This is NOT efficient, but it is what transaction processing systems do all
day with randomly arriving known customers.

If you need the results combined in one data structure (for all the values
of ID) you could make this more elaborate with a UNION query
or you could assemble the data in the language that you are calling SQL
from (assuming you are not using the command line interface). If you are
using the command line interface you could redirect to a file and append
(">" and ">>").

Jim Callahan
Callahan Data Science LLC
Orlando, FL

<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail_term=icon>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail_term=link>
<#m_-7888910753152976491_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Fri, Mar 9, 2018 at 8:14 AM, Hegde, Deepakakumar (D.) <
deep...@allgosystems.com> wrote:

> Hi All,
>
>
> We have a problem as below:
>
>
> we have created a table as below:
>
> CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;
>
> We have inserted 5 entry to this table, and ID will be from 1 to 5 as below
>
> ID   NAME
> 1 ABC
>
> 2 AAA
>
> 3 CBA
>
> 4 BAC
>
> 5 BBB
>
>
> We execute following select statetment:
>
>
> SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);
>
>
> output for above is:
>
>
> ID   NAME
>
> 1 ABC
>
> 2 AAA
>
> 3 CBA
>
>
> It seems by default sqlite is getting the entry in the order of primary
> key or rowid.
>
>
> So for us expected output is:
>
> ID   NAME
>
> 3 CBA
>
> 1 ABC
>
> 2 AAA
>
>
> Is there anyway to do this without adding a new column? with the same
> table?
>
> we need a way where by we can get the entry as we given in "where" "in"
> clause
>
>
> Thanks and Regards
>
> Deepak
>
>
> ___
> 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 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

[Jim Callahan]

What locale?
The locale setting may influence character to numeric conversions at the C
language library level.

sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
> 9.22337203685478e+18
> sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC);
> 9223372036854775807
> Notice the trailing white space which makes the difference.


Although U.S. dollar users are used to the currency symbol to the left of
the digits; in some countries
the currency symbol is to the right of the digits and sometimes there is a
space between the digits and the currency symbol.

Currencies are often represented as decimals (except when accounting
systems use integer pennies) and so a trailing space
in some or all locales may trigger an assumption of either a monetary or
floating point value.

The locale could impact something as low level as an atoi() or atol() C
conversion functions.
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rtref/itol.htm#itol

"These members are 1 if the currency_symbol or int_curr_symbol strings
should precede the value of a monetary amount, or 0 if the strings should
follow the value. The p_cs_precedes and int_p_cs_precedes members apply to
positive amounts (or zero), and the n_cs_precedes and int_n_cs_precedes members
apply to negative amounts."
...
"These members are 1 if a space should appear between the currency_symbol
 or int_curr_symbol strings and the amount, or 0 if no space should appear.
The p_sep_by_space and int_p_sep_by_space members apply to positive amounts
(or zero), and the n_sep_by_space and int_n_sep_by_space members apply to
negative amounts."
https://www.gnu.org/savannah-checkouts/gnu/libc/manual/html_node/Currency-Symbol.html#Currency-Symbol

"In many European countries such as France, Germany, Greece, Scandinavian
countries, the symbol is usually placed after the amount (e.g., 20,50 €)."
Note space between amount and Euro symbol.
https://en.wikipedia.org/wiki/Currency_symbol

Scientific Linux is used at CERN and ETHZ and other European facilities?

So, my guess would be that the space after the number, plus certain locales
would reproduce the issue.


Jim Callahan
Callahan Data Science LLC
Orlando, FL



On Tue, Jan 23, 2018 at 10:22 AM, Ralf Junker <ralfjun...@gmx.de> wrote:

> On 23.01.2018 15:31, Richard Hipp wrote:
>
> I'm still unable to reproduce this problem.
>>
>
> sqlite3.exe from this ZIP:
>
>   https://www.sqlite.org/2018/sqlite-tools-win32-x86-322.zip
>
> Running on Windows 7:
>
> SQLite version 3.22.0 2018-01-22 18:45:57
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
> 9.22337203685478e+18
> sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC);
> 9223372036854775807
>
> Notice the trailing white space which makes the difference.
>
> Ralf
>
> ___
> 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 - Interrogate Date/Time field Statement question

[Jim Callahan]

This code:

SELECT ( substr('02/13/2016',7,4) || '-'
  || substr('02/13/2016',1,2) || '-'
  || substr('02/13/2016',4,2) ) ;

yields

2016-02-13

The above code, is dependent on fixed length strings (the leading zero) in
other words '02/13/2016' and not '2/13/2016'.

If you do not have fixed length date strings,  you would probably have to
use
globs or regular expressions.

*glob(X,Y)*

The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note
that the X and Y arguments are reversed in the glob() function relative to
the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator.

https://sqlite.org/lang_corefunc.html#glob


The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP
operator will normally result in an error message. If an application-defined
SQL function <https://sqlite.org/c3ref/create_function.html> named "regexp"
is added at run-time, then the "*X* REGEXP *Y*" operator will be
implemented as a call to "regexp(*Y*,*X*)".

https://sqlite.org/lang_expr.html


Type of regular expression needed:
https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2-4e2c-8389-1266f496e4b2/regular-expression-to-get-date-format-from-string?forum=csharplanguage

​Jim Callahan

On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <rbar...@njdevils.net> wrote:

> Hi Jim,
>
> I could alter the program that populates the Date/Time Column to the
> format you specify.  I'm trying real hard not to as that program has been
> in use for many years and it would be a significant undertaking to convert
> the program then convert the existing data.  Not saying I won't do it as
> I'm at that point, just wondering if it's possible to avoid that route.
>
> If I converted the date/time field, would it be easier to create counts?
>
> If you could, would you be able to offer a sample Select statement I can
> alter to fit my needs?
>
> Thank you very much for the reply!
>
> Side note, I'll be visiting Disney in July!
>
> Regards,
>
> -Ron
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 9:15 PM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
> Can you convert the dates to ISO 8601 date time format?
> https://en.wikipedia.org/wiki/ISO_8601
>
> -MM-DD hh:mm:ss
>
> ISO date strings (when zero filled) are sortable which necessarily
> includes comparable (Java speak).
> By "zero filled" I mean for March you have "03" and not just "3".
>
> Then if you could generate/populate the boundary values in ISO format; the
> comparisons would be straightforward and you could avoid the julian date
> conversion.
>
> Another disadvantage of Julian dates are the different base years used by
> applications including Unix, MS Access, MS Excel for Windows and MS Excel
> for MacIntosh. Each application is internally consistent, but the minute
> you exchange data between applications...
> https://support.microsoft.com/en-us/help/214330/differences-
> between-the-1900-and-the-1904-date-system-in-excel
>
> Your specification actually requires day counts; so you may need Julian
> dates after all.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <rbar...@njdevils.net> wrote:
>
> > Hello all,
> >
> > To everyone who helped me before - thank you very much!
> >
> > I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
> >
> > I have to count a Date/Time field and the problem is, this field
> > contains data in a format I'm not sure can be counted.
> >
> > I need to count all the dates in the field but the dates are a
> > combined Date and time in the format examples below.
> > My goal is to use the current Date/time ('NOW') and calculate the time
> > difference in days, from my DB Sourced field.
> >
> > I need to capture...
> > Less than 1 month old
> > 1 month old
> > 2 months old
> > 1 year old.
> > all the way to greater than 10 years old.
> >
> > Is this even possible in SQLite and if so, how would I go about doing it?
> >
> > I have been googling a few queries and come up blank.
> >
> > I try this code and differing combinations of it but it always returns
> > NULL.
> >
> > SELECT CAST
> > ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date))
> > As
> > Integer)
> > FROM Volume_Information
> 

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Jim Callahan]

Can you convert the dates to ISO 8601 date time format?
https://en.wikipedia.org/wiki/ISO_8601

-MM-DD hh:mm:ss

ISO date strings (when zero filled) are sortable which necessarily includes
comparable (Java speak).
By "zero filled" I mean for March you have "03" and not just "3".

Then if you could generate/populate the boundary values in ISO format; the
comparisons would be straightforward and you could avoid the julian date
conversion.

Another disadvantage of Julian dates are the different base years used by
applications including Unix, MS Access, MS Excel for Windows and MS Excel
for MacIntosh. Each application is internally consistent, but the minute
you exchange data between applications...
https://support.microsoft.com/en-us/help/214330/differences-
between-the-1900-and-the-1904-date-system-in-excel

Your specification actually requires day counts; so you may need Julian
dates after all.

Jim Callahan
Orlando, FL



On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <rbar...@njdevils.net> wrote:

> Hello all,
>
> To everyone who helped me before - thank you very much!
>
> I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
>
> I have to count a Date/Time field and the problem is, this field contains
> data in a format I'm not sure can be counted.
>
> I need to count all the dates in the field but the dates are a combined
> Date and time in the format examples below.
> My goal is to use the current Date/time ('NOW') and calculate the time
> difference in days, from my DB Sourced field.
>
> I need to capture...
> Less than 1 month old
> 1 month old
> 2 months old
> 1 year old.
> all the way to greater than 10 years old.
>
> Is this even possible in SQLite and if so, how would I go about doing it?
>
> I have been googling a few queries and come up blank.
>
> I try this code and differing combinations of it but it always returns
> NULL.
>
> SELECT CAST
> ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date)) As
> Integer)
> FROM Volume_Information
>
> Here is what I have to work with.
>
> Table Name:
> Volume_Information
>
> Column name:
> VI_Creation_Date
>
> Date Format:
> MM/DD/CCYY HH:MM:SS AM/PM
>
> Examples:
>
> 10/30/2015 2:28:30 AM
> 2/13/2016 7:51:04 AM
> 5/15/2016 12:06:24 PM
> 10/7/2016 1:27:13 PM
>
> Any Help would be greatly appreciated,
>
> Thanks,
>
> -Ron
>
> ___
> 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] extension to run bash

[Jim Callahan]

> How much doing all that is worth is a different question, since the calls
made through this
> proposed system() SQLite function would also likely be non-portable.  In
this very example,
> there is no wc on Windows.

I would suggest renaming the proposed system() function bash() since now
and in the future there may be different command line shells.

The (now) new Windows 10 Anniversary Edition has the option of installing a
shell that nearly runs Canonical Ubuntu Linux's BASH shell. In a few years
it will likely be a routine part of Windows.

See:
https://msdn.microsoft.com/en-us/commandline/wsl/about

Thus, at some point, Linux, OS/X and Windows will all support Bash scripts.

For now, there are non-native emulators MinGW/MSys and Cygwin to provide
Bash on Windows.

MinGW/MSys
http://www.mingw.org/wiki/msys

Cygwin
http://www.mingw.org/node/21

Jim Callahan
Orlando, FL



On Wed, Jan 11, 2017 at 5:21 PM, Warren Young <war...@etr-usa.com> wrote:

> On Jan 11, 2017, at 3:11 PM, Richard Hipp <d...@sqlite.org> wrote:
> >
> > On 1/11/17, Scott Hess <sh...@google.com> wrote:
> >>  UPDATE result SET nRows = system('wc', '-l', fileNames);
> >>
> >> ...
> >> [Though, yes, this means you'll have to use fork() and execlp() and
> >> waitpid() to implement, rather than popen().
> >
> > Which further means that the code would not be portable to Windows.
>
> There is a way to do popen() in Windows, but it’s reeealy ugly:
>
>https://msdn.microsoft.com/en-us/library/ms682499%28VS.85%29.aspx
>
> How much doing all that is worth is a different question, since the calls
> made through this proposed system() SQLite function would also likely be
> non-portable.  In this very example, there is no wc on Windows.
>
> This is essentially the same problem that leads to autoconf, autosetup,
> CMake, etc: you can’t write a portable Makefile, if by “portable” you
> include non-POSIX OSes.
> ___
> 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] extension to run bash

[Jim Callahan]

> may need ability to run bash commands and assign result to a column. For
example:
​> ​
UPDATE  result SET nRows =` wc -l fileNames` ;

​Might be easier to run Bash commands in Bash; write the results to a file​
and then redirect the file into SQLite.

See for example, this Nabble thread.

http://sqlite.1065341.n5.nabble.com/How-accept-sqlite3-commands-from-stdin-td38710.html

Jim Callahan
Orlando, FL




On Wed, Jan 11, 2017 at 4:23 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLites,
>
> I am using exclusively sqlite3 shell for all the processing and may need
> ability to run bash commands and assign result to a column. For example:
>
> UPDATE  result SET nRows =` wc -l fileNames` ;
>
> Here I used `` as would be in bash for command substitution. This would
> run wc command (word count), count number of lines in each file listed in
> column fileNames and update the row correspondingly.
>
> As far as I understand I should be able to write loadable extension to
> accomplish this. My questions are:
>
> 1. Given that I use sqlite3 shell exclusively, does this path makes sense?
> If path should be different, what is it?
> 2. If loadable extension is good way to go, is there an example that I
> could use given that I have zero knowledge of sqlite's internals?
>
> 3. Maybe mixing SQL and shell commands (different syntaxes) is
> linguistically inappropriate and thus difficult?
>
> Thank you,
>
> Roman
>
>
> ___
> 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] Advice on using dates and hours for bin counts, time-zone agnostic

[Jim Callahan]

I agree with Dave Raymond, I would use "-MM-DD HH:MM:SS" format because
it is both sortable and comparable as well as an ISO standard (ISO 8601).
In addition, you don't have to know the base date the number of days were
calculated since.  The base date (epoch) for MS Excel Windows, MS Excel Mac
and MS Access are all different. Besides it is human readable and you can
eyeball if the string seams reasonable.

Are you using just one datetime for each interval?
or are you using start and end times?

Are the intervals GUARANTEED to be 15 minutes? Really?

Has everyone agreed whether the timestamp is the start or end time?

What reporting applications (besides SQLite) will be used?
(for example, is anyone going to produce charts?)
How do these applications represent time?

How and when are the times synchronized (is it an automatic process?
does it require human intervention? is it performed manually?)

National Institute of Standards and Technology (NIST) Time
http://www.time.gov/

Ideally, you would like to store the location, timezone and a delta (hours)
from GMT.

Time Zone database
http://www.iana.org/time-zones

Time Zone details
http://www.iana.org/time-zones/repository/tz-link.html

Some best practices
https://www.w3.org/TR/timezone/


But, as Einstein said, it's all relative.

Jim Callahan
Orlando, FL










On Tue, Jan 10, 2017 at 10:01 AM, Jeffrey Mattox <j...@mac.com> wrote:

> My application will be counting events and saving the totals for every
> 15-minute time period over many days -- that table will have an eventCount
> and a date/time for each bin.  I'll be displaying the data as various
> histograms: the number of events per day over the year (365 values), the
> number of events for every day of the week (7 values), the number of events
> for each quarter hour of every day (24*7 values), etc.
>
> Pseudo SQL:
>UPDATE Events  SET count = count + 1  WHERE eventTime =
> integer(now_minutes/15)
>SELECT TOTAL(count)  FROM Events  WHERE eventTime is Jan 3 of every year
>SELECT TOTAL(count)  FROM Events  WHERE eventTime is a Friday
>SELECT TOTAL(count)  FROM Events  WHERE eventTime is between 10:00 am
> and 10:15 am
>etc.
>
> How should I define "eventTime" to facilitate the storage and retrieval of
> the counts?  E.g., one field or a pair of fields (the date and the
> 15-minute-period)?  I'm unsure whether to use date and time, or datetime,
> or julianday.
>
> One snake pit I want to avoid is timezone hassle.  Everything should be
> based on the local clock time, regardless of where the computer is located
> later.  If I store a value for "1/1/17 at noon" when I'm in the eastern TZ
> and later ask for the value at "1/1/17 at noon" when I'm in any other time
> zone, I want the same result regardless of the time zone -- as if it's
> always the same date and time everywhere.  (I've handled this in the past
> by converting the local time to a string, and saving that without any TZ
> indicator.)
>
> How should I define the columns for date and time?
>
> ___
> 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] Connecting DataBases files into one

[Jim Callahan]

​​
Agree with Stephen and Dr. Hipp
1. Make copies of the SQLite data files when the SIO program is not in use
and without using a network -- use a USB stick or removable disk (prevent
corruption, always good to have a backup)
2. Using the SQLite command line interface (CLI)
 https://sqlite.org/cli.html
 use the "ATTACH DATABASE" SQL statement
 https://sqlite.org/lang_attach.html
3. Again at the SQLite command line use the ".databases"  "dot-command" to
verify the connection

The ".databases" command shows a list of all databases open in the current
connection. There will always be at least 2. The first one is "main", the
original database opened. The second is "temp", the database used for
temporary tables. There may be additional databases listed for databases
attached using the ATTACH statement. The first output column is the name
the database is attached with, and the second column is the filename of the
external file.

sqlite> *.databases
*


https://sqlite.org/cli.html

4. Check the schemas of the two databases with the .tables, .schema and
.indexes command

5. If the tables have EXACTLY the SAME FIELDS you can merge tables using
the procedure described in this StackOverflow answer.

Pay attention, however, the difference between UNION and UNION ALL
http://stackoverflow.com/questions/30292367/sqlite-append-two-tables-from-two-databases-that-have-the-exact-same-schema


Jim Callahan
Orlando, FL

On Tue, Jan 10, 2017 at 7:08 AM, rmroz80 <rmro...@o2.pl> wrote:

> Good morningI have one question with sqlite database files. In my
> company (school) there is an application called SIO (System Informacji
> Oświatowej - Educational Information System). This program is running on 2
> independent computers. Each program has own sqlite file called SIO2.sqlite
> and data are written separately on each machine. Few days ago my boss, ask
> me is this possible to join data from two systems and create one file
> containing data from both computers. Data files containing various
> information about our school like: names, surnames, adressess and
> equipment. Some data are on comp1, some on comp2, and now it is work for me
> to create 3 comp containing data from 2 and 1. Sorry for my weak English,
> because my native language is Polish.Thanks in advance   Rafał Mroziński
> ___
> 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] problem with sqlite 4

[Jim Callahan]

David Empson wrote:

SQLite Expert Personal is a third party product which uses the SQLite
> database engine. It is not using “SQLite 4” (which is in early development
> stages and not been released), but will be using some version of SQLite 3.
> This mailing list is not an appropriate place to get support for products
> which use SQLite, but this looks like odd behaviour with SQLite itself,
> which may be worth investigating further.


Moreover if one submits this bug report to the "SQLite Expert" mailing list
using the email address:

supp...@sqliteexpert.com

one may qualify for a $50 credit towards one's next purchase:

"Submit a bug report and get $50 discount when purchasing SQLite Expert
Professional!

If you submit one or more bug reports in either SQLite Expert Personal or
Professional, you will receive a promotional code by email which you can
use when purchasing SQLite Expert Professional for $50 discount (over 50%
of the original price).
Conditions:

   -  You must submit at least one bug report to qualify for this offer.
   -  Feature requests do not count as bug reports.
   -  The reported bug must be reproducible with the latest version of
   SQLite Expert.
   -  Multiple bug reports do not qualify for cumulative discount.
   -  If you already purchased SQLite Expert Professional, you are not
   entitled to a partial refund if you submit a bug report. However, you
   qualify for a discount if you wish to purchase an additional license."

http://www.sqliteexpert.com/support.html

I am not affiliated with Coral Creek Software and the only information I
could find out about the company as opposed to the product (in less than 2
minutes of Google searching) is:

https://www.bizapedia.com/fl/coral-creek-software.html

Jim Callahan
Orlando, FL

On Mon, Jan 2, 2017 at 5:15 PM, David Empson <demp...@emptech.co.nz> wrote:

>
> > On 3/01/2017, at 4:48 AM, claude.del-vi...@laposte.net wrote:
> >
> > Hi,
> >
> > The problem described here occurs both with the x32 and x64 versions of
> the expert personal 4 (Windows 10). Hereafter, a little database to show
> the bug.
> >
> > The table "sample" is used to store words occurring in texts. Texts are
> identified by an id number.
> >
> > CREATE TABLE IF NOT EXISTS sample (
> > textid INT,
> > word VARCHAR(100),
> > UNIQUE (textid,word)
> > );
> >
> > CREATE INDEX [word index] ON [sample] ([word]);
> >
> > INSERT INTO sample VALUES
> > (1,"hello"),
> > (1,"world"),
> > (1,"apple"),
> > (1,"fruit"),
> > (2,"fruit"),
> > (2,"banana"),
> > (3,"database")
> > ;
> >
> > Now, one wants to list all the tuples corresponding to the texts
> containing the word "fruit". In the table above, only the texts 1 and 2
> contains the word "fruit". Therefore, the expected result must be :
> >
> > RecNo textid word
> > - -- --
> > 1 1 apple
> > 2 1 fruit
> > 3 1 hello
> > 4 1 world
> > 5 2 banana
> > 6 2 fruit
> >
> > The following SQL request should achieve the goal :
> >
> > SELECT l2.textid, l2.[word]
> > FROM sample AS l1, sample AS l2
> > WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid])
> > ;
> >
> > But il does not since it delivers the wrong answer :
> >
> > RecNo textid word
> > - -- -
> > 1 1 fruit
> > 2 1 fruit
> > 3 1 fruit
> > 4 1 fruit
> > 5 2 fruit
> > 6 2 fruit
> >
> > However, by adjoining in the SELECT part of the above request either a
> constant string or the command DISTINCT , then the result becomes correct !
> >
> > SELECT "happy new year", l2.textid, l2.[word]
> > FROM sample AS l1, sample AS l2
> > WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid])
> > ;
> >
> > RecNo 'happy new year' textid word
> > -  -- --
> > 1 happy new year 1 apple
> > 2 happy new year 1 fruit
> > 3 happy new year 1 hello
> > 4 happy new year 1 world
> > 5 happy new year 2 banana
> > 6 happy new year 2 fruit
> >
> > SELECT DISTINCT l2.textid, l2.[word]
> > FROM sample AS l1, sample AS l2
> > WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid])
> > ;
> >
> > RecNo textid word
> > - -- --
> > 1 1 apple
> > 2 1 fruit
> > 3 1 hello
> > 4 1 world
> > 5 2 banana
> > 6 2 fruit
> >
> > Thank you for your reading. Please, notice that this "strange" behavior
> does not occur with the version 3 of Sqlite exp

[sqlite] problem with sqlite 4

[Jim Callahan]

In reply to
> On 3/01/2017, at 4:48 AM, claude.del-vi...@laposte.net

David Empson wrote:

SQLite Expert Personal is a third party product which uses the SQLite
> database engine. It is not using “SQLite 4” (which is in early development
> stages and not been released), but will be using some version of SQLite 3.
> This mailing list is not an appropriate place to get support for products
> which use SQLite, but this looks like odd behaviour with SQLite itself,
> which may be worth investigating further.


Moreover if one submits this bug report to the "SQLite Expert" mailing list
using the email address:

supp...@sqliteexpert.com

one may qualify for a $50 credit towards one's next purchase:

"Submit a bug report and get $50 discount when purchasing SQLite Expert
Professional!

If you submit one or more bug reports in either SQLite Expert Personal or
Professional, you will receive a promotional code by email which you can
use when purchasing SQLite Expert Professional for $50 discount (over 50%
of the original price).
Conditions:

   -  You must submit at least one bug report to qualify for this offer.
   -  Feature requests do not count as bug reports.
   -  The reported bug must be reproducible with the latest version of
   SQLite Expert.
   -  Multiple bug reports do not qualify for cumulative discount.
   -  If you already purchased SQLite Expert Professional, you are not
   entitled to a partial refund if you submit a bug report. However, you
   qualify for a discount if you wish to purchase an additional license."

http://www.sqliteexpert.com/support.html

I am not affiliated with Coral Creek Software and the only information I
could find out about the company as opposed to the product (in less than 2
minutes of Google searching) is:

https://www.bizapedia.com/fl/coral-creek-software.html

Jim Callahan
Orlando, FL
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database problem with later Android Studio emulator

[Jim Callahan]

Wild guess: Could there possibly be a difference in where the different
implementations
of Android look for SQLite files? If you are using a default directory and
the default directory
is different on different machines; then Android might not be looking in
the correct directory to find your files; resulting in a "file not found"
error.  Also consider file permission issues.

You might find these StackOverflow discussions useful:

December 15, 2010
http://stackoverflow.com/questions/4452538/location-of-sqlite-database-on-the-device

March 10, 2013
http://stackoverflow.com/questions/15326455/what-is-the-default-database-location-of-an-android-app-for-an-unrooted-device

September 9, 2013
http://stackoverflow.com/questions/18905804/where-is-my-sqlite-database-stored-in-android

<http://stackoverflow.com/questions/18905804/where-is-my-sqlite-database-stored-in-android>

Jim Callahan
Orlando, FL






On Thu, Sep 1, 2016 at 3:13 AM, Charles L. Sykes <char...@sykesclan.com>
wrote:

> Dear SQLite Experts:
>
>
>
> I recently made the move from Windows 7 to Windows 10, and from Eclipse to
> Android Studio.
>
>
>
> I have a set of Oscar quiz apps with a SQLite database, which ran fine when
> I converted them over, testing under the Nexus Emulator (API 19). When I
> later obtained a Samsung Galaxy S7 and copied the apk over to the physical
> device, I got the "Unfortunately, APP NAME has stopped". I forgot about it
> (since it was running under the Nexus emulator), until I uploaded them to
> Amazon for sale, and they failed on all devices except an older one,
> possibly the Nexus.
>
>
>
> I installed a Galaxy emulator and figured out how to get a detailed debug
> log, and now I am perplexed in getting a "table not found" error.
>
>
>
> I know this doesn't occur with the Nexus emulator because I put in Toast
> statements to display table counts as part of my standard debugging when a
> new version of the database is loaded.
>
>
>
> In testing, I'm only switching between the two emulators. There are no code
> changes.
>
>
>
> (Since the app only does queries, and no updates, the table is initially
> created outside and pushed onto the phone. Basically I use SQL Workbench to
> load the table from text files. I didn't see a reason to have hundreds of
> INSERT statements in the code for a one-time use. The canned database is
> then used in both the Android and iPhone versions [never got the Windows
> version to work].)
>
>
>
> I originally created the apps a couple of years ago (version 3.??). Do I
> need to possibly download a later version of SQLite and recreate the
> database with it?
>
>
>
> Thank you for any insight you can offer,
>
>
>
> cls
>
> ___
> 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] 64-bit SQLite3.exe

[Jim Callahan]

The issue is not simply recompiling the CLI for 64-bit; I assume you want
the tested reliability of SQLite.

"The [SQLite] project has 787 times as much test code and test scripts" as
it does source code for the executable.
https://www.sqlite.org/testing.html


Running the exact same tests would not be enough, because you (and I) would
want special tests for larger than 4 GB
RAM. So, additional tests would have to be developed.

So, I see three alternate solutions:

1. Find a corporate sponsor to fund development of 64-bit SQLite CLI

2. Remove some tables from memory (though it sounds like your difficulties
are caused
by recursion rather than the size of the raw data)

3. Use SQLite for persistent storage but move the in memory (tree
navigation) operations to another (open source)  language
such as C, Java, Python or R (or the new Julia language that is approaching
version 1.0)  that has interfaces for SQLite
and a 64-bit build for Windows.  You will probably need another language to
display the output anyway why not take
advantage of Python, R or Julia?

Even if you move to another language, you may find that your problem is
recursion.
In my experience, computer science textbooks give elegant examples using
recursion,
but then say the solution is not scale-able and give a less elegant
solution using iterative techniques.

Jim Callahan
Data Scientist
Orlando, FL


On Tue, Aug 9, 2016 at 10:31 AM, Rousselot, Richard A <
richard.a.rousse...@centurylink.com> wrote:

> I would like to request a SQLite official 64-bit SQLite3.exe CLI (not DLL)
> be created.
>
> I have reviewed the prior discussions regarding 64-bit SQLite3 and the
> reasoning for which why creating a 64-bit version is denied are "it does
> not make a real difference", "you can just use ram disks", etc., etc.
>
> Here is my plea...  I am using a set of complicated CTEs to crawl through
> a network (tree) to aggregate and calculate formulas.  I don't have
> exceptionally large datasets but my CTEs result in a ton of memory usage.
> The process works well from disk, in Windows, but using a smaller test
> sample I get about a 30% to 40% increase in processing time if I set the
> PRAGMA to temp_store = 2.  If I use a normal dataset, not a small test, I
> hit an approximate 2G limit and get a "out of memory" message, which I
> understand is due to SQLite3.exe being 32-bit.  I have found some 3rd party
> 64-bit builds for SQLite3 (best found is 3.8.5) but they are out of date
> and don't allow all functionality that I am using.  So, I do have a use
> case that requires 64-bit and I would see a significant increase in speed.
>
> As to RAM disks, I work in a corporate environment that locks down user
> rights which precludes me from distributing a tool that requires the
> creation of a tool that needs administrator rights.  I also, would like to
> avoid having to compile it myself; I am not a software engineer.
>
> Thanks for your consideration.
>
> Richard
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the sender
> by reply e-mail and destroy all copies of the communication and any
> attachments.
> ___
> 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] newbie has waited days for a DB build to complete. what's up with this.

[Jim Callahan]

Temp Files
Have you checked how much storage is available to the temporary file
locations?
The temporary file locations are different depending on the OS, build, VFS
and PRAGMA settings.
See the last section "5.0 Temporary File Storage Locations" of:
https://www.sqlite.org/tempfiles.html


The database was growing for about 1-1/2 days.  Then its journal
> disappeared, the file size dropped to zero, but sqlite3 is still running
> 100% CPU time, now for a total of 3800+ minutes (63+ hours).  The database
> is still locked, but I have no idea what sqlite3 is doing, or if it will
> ever stop.  All partitions still have lots of space left (most of this is
> running in a RAID partition of 11 TiB).  Here's what I gave to sqlite3 on
> my Linux system:


You might have a huge storage allocation for the main file and log, but
some other temp file might be being dumped
to a more constrained storage location.

RAM
Since you are using RAID disk controller; I assume you have 64 bit CPU and
more than 8 GB of RAM?
If you have 8 GB or more of RAM would it help to use an in memory database?

Transactions
Are you using explicit or implicit transactions?
https://www.sqlite.org/lang_transaction.html


Steps
Agree with Darren Duncan and Dr. Hipp you may want to have at least 3
separate steps
(each step should be a separate transaction):

1. Simple load
2. Create additional column
3. Create index

Have you pre-defined the table you are loading data into? (step 0 CREATE
TABLE)

If "Step 1 Simple Load" does not complete; then may want to load a fixed
number of rows into separate tables (per Darren Duncan)  and then combine
using an APPEND
or a UNION query (doing so before steps 2 and 3).

HTH

Jim Callahan
Data Scientist
Orlando, FL




On Wed, Aug 3, 2016 at 11:00 PM, Kevin O'Gorman <kevinogorm...@gmail.com>
wrote:

> I'm working on a hobby project, but the data has gotten a bit out of hand.
> I thought I'd put it in a real database rather than flat ASCII files.
>
> I've got a problem set of about 1 billion game positions and 187GB to work
> on (no, I won't have to solve them all) that took about 4 hours for a
> generator program just to write.  I wrote code to turn them into something
> SQLite could import.  Actually, it's import, build a non-primary index, and
> alter table to add a column, all in sqlite3.
>
> The database was growing for about 1-1/2 days.  Then its journal
> disappeared, the file size dropped to zero, but sqlite3 is still running
> 100% CPU time, now for a total of 3800+ minutes (63+ hours).  The database
> is still locked, but I have no idea what sqlite3 is doing, or if it will
> ever stop.  All partitions still have lots of space left (most of this is
> running in a RAID partition of 11 TiB).  Here's what I gave to sqlite3 on
> my Linux system:
>
> time sqlite3 qubic.db < BEGIN EXCLUSIVE TRANSACTION;
> DROP TABLE IF EXISTS qmoves;
> CREATE TABLE qmoves (
>   qfrom CHAR(64),
>   qmove INT,
>   qto   CHAR(64),
>   qweight INT,
>   PRIMARY KEY (qfrom, qmove) ON CONFLICT ROLLBACK
> );
> CREATE INDEX IF NOT EXISTS qmoves_by_dest ON qmoves (
>   qto,
>   qweight
> );
> CREATE TABLE IF NOT EXISTS qposn (
>   qposn CHAR(64) PRIMARY KEY ON CONFLICT ROLLBACK,
>   qmaxval INT,
>   qmove INT,
>   qminval INT,
>   qstatus INT
> );
> .separator " "
> .import am.all qmoves
> ALTER TABLE qmoves ADD COLUMN qstatus INT DEFAULT NULL;
> .schema
> COMMIT TRANSACTION;
>
> EOF
>
> Any clues, hints, or advice?
>
>
> --
> #define QUESTION ((bb) || (!bb)) /* Shakespeare */
> ___
> 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] sqlite fixed data loading extension

[Jim Callahan]

>
> ??I have a need for something that can parse and load into sqlite tables
> fixed length data.


?All the applications I have had experience with in the past (Sqlite Expert,
> Navicat, and some others) require the user to run an application and setup
> the functionality each and every time you used it. They do not give the
> ability to save the import setup into a meaningful parameters file that can
> be edited an rerun from the command line. So these types of applications
> are ruled out. I would think I could do this in some fashion with
> Informatica or like professional product, but at the expense of $$$ and
> m
> ??
> ore $.?


The open source statistical language R can read fixed width files and write
out SQLite tables.

https://www.r-project.org/

Read a table of *f*ixed *w*idth *f*ormatted data into a data.frame
<https://stat.ethz.ch/R-manual/R-devel/library/base/html/data.frame.html>.
https://stat.ethz.ch/R-manual/R-devel/library/utils/html/read.fwf.html

Write a data frame to a SQLite table
http://www.rdocumentation.org/packages/RSQLite/functions/dbWriteTable
<http://rpackages.ianhowson.com/cran/RSQLite/man/dbWriteTable.html>

This blog post gives an overview of RSQLite
http://sandymuspratt.blogspot.com/2012/11/r-and-sqlite-part-1.html

Full documentation for the RSQLite package
https://cran.r-project.org/web/packages/RSQLite/RSQLite.pdf

BTW, since you are familiar with the Microsoft language C#;
Microsoft has purchased Revolution Analytics and is now
supporting a version of R.
https://mran.revolutionanalytics.com/open/

Jim Callahan
Orlando, FL

On Fri, Mar 25, 2016 at 7:49 AM, Don V Nielsen 
wrote:

> I have a need for something that can parse and load into sqlite tables
> fixed length data. I know sqlite has a csv import, so I would like to
> duplicate that kind of functionality handling fixed columnar data. I
> thought an extension would be perfect so I could specify something as
> simple as "sqlite3 -fx parameter.dat" on the command line and it would
> import the data.
>
> Has anyone written a data loading extension already that would be willing
> to share the source code with me? I have not written an sqlite extension
> before, I know C# not C/C++, and leveraging someone else's effort would
> help me a lot in learning the language and the extension. I've already
> written this type of sqlite data loading logic into a specific application
> using C#, but I would like it to create something more generic and
> generally usable by anyone.
>
> I've seen a lot of traffic on the mailing list about sqlite's csv import
> abilities and wondered if someone has improved them with their own
> extension. (I guess this begets the question..."Is sqlite's csv import an
> extension?")
>
> All the applications I have had experience with in the past (Sqlite Expert,
> Navicat, and some others) require the user to run an application and setup
> the functionality each and every time you used it. They do not give the
> ability to save the import setup into a meaningful parameters file that can
> be edited an rerun from the command line. So these types of applications
> are ruled out. I would think I could do this in some fashion with
> Informatica or like professional product, but at the expense of $$$ and
> more $.
>
> Any observations, comment, or suggestions? Is there a different mail list I
> should hit up?
>
> Thanks for your time and consideration
> dvn
>
>
> "My dad said to me as a child, 'Why be taught what you can learn on your
> own. That's why he would just throw me in the lake...so he could learn
> CPR." - Anthony Jeselnik.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Article about pointer abuse in SQLite

[Jim Callahan]

I have painful memories from programming 101 in the twilight of the punch
card era
that initializing variables was a big deal in FORTRAN and COBOL. After
declaring a long list of variables for a convoluted assignment one would
have to go back and set equal to zero in FORTRAN and "MOVE SPACES TO" in
COBOL. There was no "cut and paste" this was the punch card era (we also
had to walk seven miles uphill barefoot in the snow to school -- joke).
Assuming one did not get a cryptic compiler message (and in those days all
compiler messages were cryptic, "Probable user error:") one would get a
bizarre run time error at a time when one did not clearly understand the
difference between "compile time" and "run time" (the batch job was
initialized with cryptic JCL cards and returned one long printout on green
bar paper so it took careful reading to understand what the computer was
doing).  The UNIX shells (or even the MS-DOS command line) were such an
improvement over IBM mainframe JCL hell.

In a shared mainframe or minicomputer environment there was constant
clutter in memory. This was long before Internet era security concerns.

Wikipedia has an article on "Uninitialized Variable" with a C example and
references
a C standard:
"ISO/IEC 9899:TC3 (Current C standard)"
<http://www.open-std.org/JTC1/SC22/WG14/www/docs/n1256.pdf> (PDF).
2007-09-07. p. 126. Retrieved 2008-09-26. Section 6.7.8, paragraph 10.

If uninitialized variables are allowed and memory is not automatically set
to zero the program will have non-deterministic run-time behavior because
of the random clutter in memory.

Jim Callahan







<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail_term=oa-2115-v2-a>
This
email has been sent from a virus-free computer protected by Avast.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail_term=oa-2115-v2-a>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Wed, Mar 23, 2016 at 1:23 PM, Doug Nebeker  wrote:

> > For obvious security reasons all allocations from the Operating System
> are pre-initialized to 0x00.
>
> Time to bash Windows, but according to the docs for HeapAlloc, memory is
> not automatically initialized to 0
>
>
> https://msdn.microsoft.com/en-us/library/windows/desktop/aa366597(v=vs.85).aspx
>
> This fits with my experience as well.
>
> Doug
>
>
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf
> Sent: Tuesday, March 22, 2016 8:41 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Article about pointer abuse in SQLite
>
>
> > This discussion on the nature of undefined behaviour code is
> > interesting.  I don't know the reasoning, but it seems that VS6 often
> > initialized things to 0xcd in debug mode and (usually) had memory
> > uninitialized to 0x00 when complied in Release (perhaps 0x00 just
> > happens to be what was on the stack or heap).  I presume this wasn't
> > just to make people suffer  when things don't work the same in debug
> > vs release mode.
>
> The initialization of memory to non-0x00 is a compiler function.
>
> For obvious security reasons all allocations from the Operating System are
> pre-initialized to 0x00.  This is so that your program cannot request a big
> hunk of virtual memory which is full of a predecessor process data and then
> proceed to search it for nifty things like previously used private keys,
> userids, passwords, and so forth.  Such behaviour is required for any
> Operating Systems to obtain any security certification level whatsoever.
>
>
>
>
> ___
> 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] Changing the default page_size in 3.12.0

[Jim Callahan]

??

> From: Domingo Alvarez Duarte
> I have a question, is it enough to vacuum a database to update to the new
> page size ?
>

Apparently all you need to is "pragma page_size=4096; vacuum;" using the
> appropriate page size.
> This makes very easy to convert any(all) database(s) with a single
> ?t?
> command from the command-line, like so (Win7 example):
> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"


?I am confused.
The "page" is an in-memory structure; i?t is how large a chunk the program
reads from the file (analogous to how large a scoop or shovel), So, unless
one is using an index, how would the on disk structure be impacted? How
does SQLite handle the last block (does it expect the file to be an even
multiple of the block size, or does it accept that the last read might be
less than a full block?).

For example, if one encountered an old file, would it be enough to rebuild
the indices?
?
Or is it simply a matter of closing the file? (close the file with the old
version and open the file with the new?).

I haven't read the source code so I don't know what assertions, checks or
assumptions SQLite
uses.

Jim?



On Sat, Mar 5, 2016 at 11:04 AM,  wrote:

> From: Domingo Alvarez Duarte
>> I have a question, is it enough to vacuum a database to update to the new
>> page size ?
>>
>
> Apparently all you need to is "pragma page_size=4096; vacuum;" using the
> appropriate page size.
> This makes very easy to convert any(all) database(s) with a single command
> from the command-line, like so (Win7 example):
>
> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Changing the default page_size in 3.12.0

[Jim Callahan]

Is 4096 bytes a large enough page size?

Apparently the disk drive industry has shifted from 512 byte sectors to
4096 byte sectors.
http://tilt.lib.tsinghua.edu.cn/docs/tech/tp613_transition_to_4k_sectors.pdf

Should SQLite maintain a 1:1 ratio between page size and sector size?
or should the page size be a multiple of the sector size? Say 2:1 (8096 or
8K)? or 4:1 (16K).

What sizes do other databases use? (SQL Server and Postgres both use 8096
default)

For years, virtual machines (VM) have used 4K pages (I think this started
with IBM VM/370);
while disk drives had 512 byte sectors (an 8:1 ratio).

With a 2:1 ratio, in terms of seek time, one gets the second page for free.

Would 8096 bytes (8K) be too much for a multi-tasking embedded device (such
as a smart phone?).

Are there any benchmarks?

Jim







On Fri, Mar 4, 2016 at 10:48 AM, Richard Hipp  wrote:

> The tip of trunk (3.12.0 alpha) changes the default page size for new
> database file from 1024 to 4096 bytes.
>
> https://www.sqlite.org/draft/releaselog/3_12_0.html
> https://www.sqlite.org/draft/pgszchng2016.html
>
> This seems like a potentially disruptive change, so I want to give
> you, the user community, plenty of time to consider the consequences
> and potentially talk me out of it.
>
> The "Pre-release Snapshot" on the download page
> (https://www.sqlite.org/download.html) contains this change, if you
> want to actually evaluate the latest changes in your application.
>
> We hope to release 3.12.0 in early April, or maybe even sooner, so if
> you want to provide feedback, you should do so without unnecessary
> delay.
> --
> 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] Database is locked (wal) - purely from read-only connections/queries

[Jim Callahan]

Are you using any SQL VIEWs?
"You cannot DELETE, INSERT, or UPDATE a view.
Views are read-only in SQLite."
http://sqlite.org/lang_createview.html

Jim

On Mon, Feb 29, 2016 at 10:31 PM, Vince Scafaria  wrote:

> Richard, I can confirm that having a writable connection open first, prior
> to trying any reads, does avoid the "database is locked" error.  However, I
> still do get "SQLite error (17): statement aborts" errors.  What are the
> rules I must follow to avoid getting these SQLITE_SCHEMA errors?  I am not
> running any SQL that I would generally consider to be altering the schema.
> I'm simply doing multiple concurrent reads on read-only connections and
> running INSERT/UPDATE (not CREATE/DROP) statements on the writable
> connection.  Thank you.
>
> Vince Scafaria
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Encrypt the SQL query

[Jim Callahan]

>
> queries are just stored as normal string constants in C++ so it is easy to
> be decoded via reverse engineering method.


Is the normal "reverse engineering method":
1. a hexdump of your executable?
2. debugging your executable?

A trivial cipher could be used to encode the strings prior to storing
in C++ and then decoded on the fly. That would protect against
the strings be obvious in a casual hexdump, but would provide
no protection against a determined adversary who could guess the
likely cipher (from reverse engineering your Google searches or
likely reference books).

"Show me your flowcharts and conceal your tables, and I shall continue to
be mystified. Show me your tables, and I won?t usually need your
flowcharts; they?ll be obvious."
Fredrick Brooks, Mythical Man-Machine Month, page p. 102-3
https://en.wikiquote.org/wiki/Fred_Brooks

Jim Callahan
Orlando, FL






On Thu, Feb 25, 2016 at 1:01 AM,  wrote:

> Hi,
>
> In my C++ program, I will invoke SQLite to execute SQL queries. But these
> queries are just stored as normal string constants in C++ so it is easy to
> be decoded via reverse engineering method. Does SQLite provide a good way
> to
> encrypt the SQL query strings while does not affect the performance when
> executing the queries?
>
> Thanks
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Performance comparison between SQLite and SQL Server?

[Jim Callahan]

SQLite would be most comparable to *SQL Server Express LocalDB* edition
which is introduced in this July 2011 blog post
https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/

More uptodate information about *SQL Server Express LocalDB* edition
is in this 2016 Microsoft Developer's Network (MSDN) article
https://msdn.microsoft.com/en-us/library/hh510202.aspx

This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH
"*Situations Where SQLite Works Well*"

and

"*Situations Where A Client/Server RDBMS May Work Better*"
http://sqlite.org/whentouse.html


Opening lines of whentouse.html:

"SQLite is not directly comparable to client/server SQL database engines
such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to
solve a different problem.   Client/server SQL database engines strive to
implement a shared repository of enterprise data. ...SQLite strives to
provide local data storage for individual applications and devices."

Even Microsoft has adopted SQLite for some limited tasks (such as storing
state) within every shipping copy of Windows 10.
"SQLite is a unique case: it is an open source, externally developed
software that is used by core system components, and our flagship apps like
Cortana and Skype.  ...After shipping SQLite as a system component in July,
we wanted to include it in our SDK for November. With more than 20,000
Windows Apps and more than half of our top apps using SQLite, it made sense
to just make expose the system SQLite to app developers."
http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/


There is a historical and unfair (specially compiled version of SQLite
against default settings of PostgreSQL) benchmark
available on this page, but now that you understand the use cases, this
particular benchmark is not that useful in addition
to being out of date and unfair.
https://www.sqlite.org/speed.html

Jim Callahan
Data Scientist
https://www.linkedin.com/in/jamesbcallahan
Orlando, FL

On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin  wrote:

>
> On 15 Feb 2016, at 9:41pm, James K. Lowden 
> wrote:
>
> > SQL Server has none of those restrictions, and probably keeps pace with
> > SQLite even on its home turf.  But the administration of SQL Server is
> > nontrivial.  For that reason alone, I would never use it in situations
> > where SQLite would do.
>
> That's the fella.  Major advantage of SQLite: zero admin.  Not even a
> background task.
>
> Second advantage: you know exactly where you data is.  Better still, it's
> simple: one database == one file, and the file has the same name as the
> database.  I remember trying to reconstruct a MySQL database from a dead
> server.  One folder with a confusing mass of files in.  Your database is
> part of some of those files, but the files may be huge even if the one
> database you care about is tiny.  That was not a fun time.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Efficient relational SELECT

[Jim Callahan]

Might want to split  bookings into a transaction table and a reservation
table.

The reservation table would have one column for each room and one row for
each calendar day (assuming this is a respectable joint with no hourly
reservations!).

Reservation table has primary key of date and room entries start out as NA.
As rooms are booked NAs are replaced with IDs from booking-transaction
table.

Jim
 This email has been sent from a
virus-free computer protected by Avast.
www.avast.com 
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Thu, Feb 4, 2016 at 1:32 PM, Simon Slavin  wrote:

> Simplified explanation.  Here's the setup:
>
> Two tables:
>
> rooms: each room has an id (think the standard SQLite rowid/id) and some
> other columns
> bookings: includes room id, date, time, and some other stuff
>
> Date/time is encoded as a long COLLATEable string.  In other words sorting
> a column by my date/time format will sort correctly into date order.
>
> Bookings may be made in reverse order.  In other words, someone may book a
> room for next month, and after that someone may book the same room for
> tomorrow.
>
> System contains about 50 rooms and 5000 bookings.  I can create whatever
> indexes you want.
>
> Requirement:
>
> I want to show an HTML table which lists some rooms (using WHERE and ORDER
> BY) and the latest time each of those rooms is booked for.  At the moment
> my code uses one SELECT to get room details and one SELECT for each room to
> find the most recent booking.
>
> The obvious thing is to combine the two using JOIN.  But the best JOIN I
> can think of has to find the most recent booking using a clunky test for
> the biggest date.  And I can't figure out a good phrasing for a sub-select.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Jim Callahan]

I am not interested in a complete ORM; what I am interested is when the
object-oriented language supports a SQL-R-like object. In R, the object is
called a data.frame and the package "Pandas" supplies a similar data frame
object to Python.
https://pypi.python.org/pypi/pandas/0.10.0/

R as I have mentioned has fantastic interfaces to SQL databases that allow
one to pass a query and have the result populate a data frame. The data
frame in R or Python can be fed to a machine learning algorithm (scikit
learn on Python) or to a plotting package such as ggplot or bokeh.
https://pypi.python.org/pypi/ggplot/0.6.8
http://bokeh.pydata.org/en/latest/
http://scikit-learn.org/stable/

What I want to do is to demonstrate short scripts in R and Python to
accomplish the same task.  I don't want the Python scripts to be longer and
more complicated because Python has an lower level interface to SQLite.
When I tried to do this two years ago, the SQLAlchemy package (for
example) did not understand SQLite VIEWS and one had to write an explicit
loop to build the data frame (which would make the Python code longer and
more complicated than the R code).

I can't ask for enhancements to the SQL CLI because that is a standard that
was written for communication with non-object oriented languages such as C
and COBOL which do not have standardized SQL-table-like objects (one could
of course write a library for C to implement a data frame -- R itself  is
written in C and so are most implementations of Python), but S (the
proprietary ancestor of R) was written so Bell Labs scientists
did not have to learn C or FORTRAN.

Rather than invent a whole new spec out of whole cloth, I thought the JDBC
spec might be a good start at a high level interface and would have the
added benefit of being cross platform. There seem to be a lot of SQL GUIs
written in Java that use JDBC; so JDBC must be doing something right?

There also appears to be a Python interface to ODBC. Microsoft should have
a strong interest in having a good ODBC driver for SQLite (having adopted
SQLite in MS Windows) because Microsoft products such as C#, MS Access and
MS Excel all could consume SQLite data via ODBC. Perhaps Microsoft
could issue a consulting contract (to Dr. Richard Hipp) for an high quality
ODBC driver for SQLite?
But, is ODBC cross-platform?

I agree that error messages are an issue -- is this a problem with the
JDBC/ODBC specs, the implementations or is the entire technical approach
beyond redemption?

Jim Callahan
Orlando, FL























<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
This
email has been sent from a virus-free computer protected by Avast.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Jan 30, 2016 at 6:03 PM, Simon Slavin  wrote:

>
> On 30 Jan 2016, at 8:13pm, Yannick Duch?ne 
> wrote:
>
> > In my opinion (which some others share), OO is a bag of miscellaneous
> things which are better tools and better understood when accosted
> individually. Just trying to define what OO is, shows it: is this about
> late binding? (if it is, then there sub?program references, first?class
> functions, or even static polymorphism and signature overloading) About
> encapsulation? (if it is, then there is already modularity and scopes)
> About grouping logically related entities? (if it is, there is already
> modularity, and sometime physically grouping is a bad physical design).
>
> There are a number of problems in using a relational database for
> object-oriented purposes.  One is that to provide access to stored objects
> you need to access the database in very inefficient ways which are slow and
> are not helped by caching.  You can read about some of the problems here:
>
> <https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch>
>
> There are databases designed from the ground up as OO databases.  Or
> rather as ways to provide persistent storage for objects.  They tend to be
> non-relational databases, optimised for efficiency in making changes to
> objects rather than searching and sorting.
>
> You can use SQLite like this.  In fact implementing persistent object
> storage in SQLite would be a wonderful exercise for a programming class.
> But it might result in a solution too slow to be useful for real programs.
> The biggest pointer for this is that it hasn't been done.  There's no
> library to implement persistent object storage that everyone knows about.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Best way to store only date

[Jim Callahan]

> the century? I prefer the oldfashoned yymmdd.

> The advantage of the four-digit year is that it can be used for sorting
> over a wide range.

Let's not create a Y2100 problem; right after fixing Y2K!   ;)



This
email has been sent from a virus-free computer protected by Avast.
www.avast.com

<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Jan 30, 2016 at 8:48 AM, Gerry Snyder 
wrote:

> On Jan 30, 2016 6:18 AM, "E.Pasma"  wrote:
> >
> > The diagram got broken in my email and here is another try:
> >
> >  Needs to be light | Needs to be| Needs to do  |
> >  (small footprint) | Human-Readable | calculations |
> >  - | ---|  |
> >  YES   | YES| NO   | Integer as
> >||  | Igor's suggestion
> >||  |
> >  YES   | NO | YES  | Float/Int
> >||  | Julianday
> >||  |
> >  NO| YES| YES  | Datetime/Numeric
> >||  | ISO Standard
> >
> > With respect to Igor's suggestion, mmdd (as integer), why not leave
> out
> > the century? I prefer the oldfashoned yymmdd.
>
> The advantage of the four-digit year is that it can be used for sorting
> over a wide range.
>
> Gerry
> >
> > Thanks, E. Pasma
> > 30-01-2016 00:31, R Smith:
> >
> > >
> > > On 2016/01/29 5:23 PM, Igor Tandetnik wrote:
> > >>
> > >> Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other
> > >> words, storing calendar dates as integers like 20160129.
> > >
> > > The main advantage of this format is that it is of course
> > > human-readable, even as an integer.
> > > The important disadvantage is that you cannot do date calculations
> > > without first casting and translating - something the Julian day or
> more
> > > expensive 19-char ISO format (-MM-DD HH:NN:SS which is
> > > human-readable AND in most systems calculatable) is better at.
> > >
> > > My point being: when I decide which date format to use, I first try to
> > > establish whether I will use it for calculations or simply record/log
> > > purposes, and if readability (from data source) would be needed/helpful
> > > or not. The decision matrix ends up something like this:
> > >
> > >
> > > Needs to be light (small footprint)| Needs to be Human-Readable
> > > | Needs to do calculations   |
> > > -- | --
> > > | -- |
> --
> > > YES  | YES |
> > > NO | Integer (as Igor's suggestion)
> > > YES  |NO |
> > > YES|  Float/Int Julianday
> > > NO   | YES |
> > > YES| Datetime/Numeric ISO Standard
> > > -- | --
> > > | -- |
> --
> > >
> > > If you can say "No" to two of these criteria, go for the most
> efficient.
> > >
> > > If you can say "No" to all three criteria, perhaps reconsider whether
> > > you really need that column in your table.
> > >
> > >
> > > Cheers,
> > > Ryan
> > >
> > > ___
> > > 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] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Jim Callahan]

Today, I found a Python package, "JayDeBeApi" that accesses SQLite via JDBC
rather than directly through the C language Call Level Interface (CLI).
https://github.com/baztian/jaydebeapi/blob/master/README.rst

This might provide the object oriented interface I have been looking for!
Has anyone tried it?

The SQLite3 CLI matches the specifications of an interface that was
intended for the C programming language. The CLI specs were intended for C
and COBOL not designed with an object oriented language in mind.
"Developed in the early 1990s, the API was defined only for the programming
languages C <https://en.wikipedia.org/wiki/C_(programming_language)> and
COBOL <https://en.wikipedia.org/wiki/COBOL>."
https://en.wikipedia.org/wiki/Call_Level_Interface

By  contrast, JDBC was designed for Java (an object oriented language) as a
substitute for Microsoft's ODBC.

Now that a Python developer is using JDBC instead of CLI we may get a more
object oriented interface:

"JDBC connections support creating and executing statements.  ...Query
statements return a JDBC row result set. The row result set is used to walk
over the result set <https://en.wikipedia.org/wiki/Result_set>. Individual
columns <https://en.wikipedia.org/wiki/Column_(database)> in a row are
retrieved either by name or by column number. There may be any number of
rows in the result set. The row result set has metadata that describes the
names of the columns and their types."
https://en.wikipedia.org/wiki/Java_Database_Connectivity

As I recall there are at least three JDBC drivers for SQLite3, one by
Werner
http://www.ch-werner.de/javasqlite/

one by Saito (Xerial)
https://github.com/xerial/sqlite-jdbc/blob/master/README.md

and Saito's was forked (with attribution from Zentus)
https://github.com/crawshaw/sqlitejdbc

I am hopeful this new JDBC based interface will provide as satisfactory
high level channel between SQLite3 and Python.

Jim Callahan

On Thu, Jan 7, 2016 at 7:55 PM, Warren Young  wrote:

> On Jan 7, 2016, at 5:22 PM, Jim Callahan 
> wrote:
> >
> > I believe R has remarkably good interface packages for SQLite
>
> That?s the appropriate level: the high-level language's DB access layer
> should map the low-level C record-at-a-time API to an appropriate
> language-level abstraction.
>
> R almost forces you to do this because of things like data.frame.  But,
> that?s no argument for other HLL DBMS API writers not to provide similar
> affordances.
>
> I?ve been involved with two different C++ DBMS wrapper libraries, and both
> of them provide a way to get a std::vector<> as a result set instead of
> iterate over individual rows.  As with R?s SQLite wrapper, I felt it was my
> C++ code?s responsibility to do this repackaging, not the underlying C DBMS
> access API.
>
> That?s not to say that the SQLite C API has no warts:
>
> 1. sqlite3_column_*() uses 0-based indices but sqlite3_bind_*() uses
> 1-based indices.  I can cope with either base, but please pick one!  (And
> make it the correct base for programming, 0.  (Yes, I know I just praised R
> above.  R?s use of 1-based arrays is WRONG.))
>
> 2. There is no ?preview? mechanism.  That is, you can?t bind some
> parameters to a prepared query string and then get the resulting SQL
> because SQLite substitutes the values into the query at a layer below the
> SQL parser.  This means that if you have an error in your SQL syntax or
> your parameters cause a constraint violation, your debug logging layer can
> only log the prepared query string, not the parameters that went into it,
> which makes it unnecessarily difficult to determine which code path caused
> you to get the error when looking at logs of a running system.
>
> 3. The query finalization code could be less picky.  If I prepare a new
> query without finalizing the previous one, I?d rather that SQLite didn?t
> punish me by throwing errors unless I put it into a ?lint? mode.  Just toss
> the half-finished prior query and move on, please.
>
> 4. There are several signs of backwards compatible extensions which make
> the API more complex than if it were designed with the features from the
> start.  (e.g. _v2() APIs, the various ways to get error codes, etc.)
> Hopefully those doing the SQLite4 effort will feel free to break the API,
> jettisoning this historical baggage.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
This
email has been sent from a virus-free computer protected by Avast.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Jim Callahan]

At the command line interface (CLI) in SQLite
(and most SQL implementations) is an interpreted
set at a time language with implicit loops.

Efficient low level languages (such as C) process data
a record at a time and the existing API is appropriate
for them.

Object Oriented Interactive Languages (OOIL ?) can receive a Table, a View
or a Query all at once as a data set.
I would count among the OOIL languages: R, Python, Julia Scala,
MatLab/Octave and APL. In a slightly different category would be Java and
C# which are object oriented and arguably interpreted, but are not intended
to be used interactively at a command line with a Read-Evaluate-Print-Loop
(REPL).

The intent of the higher level API is to improve the reliability of the
interfaces. The existing SQLite APIs are correct, but hard to use in the
sense that creating an interface from an OOIL language is more involved
than just "wrapping" one by one a set of functions. What I am proposing is
a second set of APIs that when trivially wrapped for use in an OOIL
language would result in a function that makes sense to an OOIL programmer
and interprets the SQL statements in a manner consistent with the SQLite
CLI (perhaps it could even borrow code from the CLI).

I believe R has remarkably good interface packages for SQLite, but that is
not necessarily the norm across the other OOIL languages.

I am assuming that the higher level API would be hard to use in C because
its up to the programmer to write the low level code while maintaining a
complex abstraction in their head (because C is better suited for creating
abstractions than using them). Header files (.h) would help some but they
would inflate the size of the code and still be hard for the C programmer
to keep track of. So, that's why I see the need for a second higher API
that might be written in C, but would certainly not be used in C!

I am undecided as to whether the higher level API would be useful in Java
or C#.  Java and C# programmers might not be used to implicit loops and
find them not worth the trouble;
whereas R, Python or Julia programer would expect to get an entire table,
view or query all at once.

The higher level API would have to be optional, since it would not be
desirable for a programmer or organization that needs SQLite to run with
the smallest possible footprint on a phone, tablet or Internet of things
(IOT) device.

Just a wishlist idea. No rush for me because I am happy in R and will
probably be moving from SQLite to client server SQL database before I move
from R to Python, Julia or Java.

Jim Callahan
Orlando, FL


<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
This
email has been sent from a virus-free computer protected by Avast.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[sqlite] Exporting

[Jim Callahan]

Copying the file to another computer would likely work; except if the
application encrypted the data.

Jim

On Sun, Dec 6, 2015 at 5:22 PM, Simon Slavin  wrote:

> If you cannot run any program apart from the ones already installed then
> your only option is to copy the database file to another computer.
>
> -
> Simon
>
> > On 6 Dec 2015, at 8:35 p.m., Thomas Morris  wrote:
> >
> > Unfortunately, the application is required.  I am working on a
> government network and I cannot install sqlite, I can just run it through
> the application.  I actually do not even access to use the command prompt
> to initialize the sqlite shell.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Simple Math Question

[Jim Callahan]

SQL is a storage and retrieval engine with limited calculation abilities in
support of storage, retrieval and reporting.

You can store numbers as integers, text or floating point and the calling
language can use whatever subroutines to translate and manipulate the
numbers.

If the calling language has subroutine library that supports binary coded
decimal (BCD)
https://en.wikipedia.org/wiki/Binary-coded_decimal
you can store the inputs and outputs in SQL while performing calculations
in the calling language.

The Intel chip family (and I assume ARM RISC chips) has only rudimentary
support for BCD, so the calculations have to be implemented in a higher
level language.
https://en.wikipedia.org/wiki/Intel_BCD_opcode

Besides SQLite3 is implemented in C; not assembler.

TI has an assembly language library and this enthusiast has found a library
for building an onscreen calculator (but he is really excited by ferrite
core magnetic memory! -- hasn't been used in 30 years!).
http://www.eetimes.com/author.asp?section_id=14_id=1282755

On Fri, Oct 23, 2015 at 11:53 AM, Scott Hess  wrote:

> In one case, you asked "When I add these imprecise values together, do they
> equal this other precise value?"  In the other case you asked "When I add
> these imprecise values together, what is the decimal expansion?" and then
> you noticed that the decimal expansion did not equal that precise value.
>
> My point is that what is going on internally is all there is.  It's not
> reporting something different from the result it sees, it is very literally
> reporting what it has.  In the language metaphor, you're asking the
> questions in English (base-10 in this case), and the computer only knows
> how to think in Japanese (base-2 in this case), so you can't avoid the
> translation back and forth, and when you give it little bits and pieces
> then ask it to put them together, it can't understand your intention from
> the bits and pieces.
>
> In your example, the computer didn't at some point think "I had a 23, here,
> but I'm going to report 22.9 just for the heck of it".  What probably
> happened on the other case is that it had a near-25 value which was closer
> to 25 than to 24.99, so it printed 25, whereas on the near-23 case
> it was closer to 22.9 than 23, so it went with that.  When you have a
> bunch of base-2 representations of base-10 fractional numbers, sometimes
> they're slightly too small, sometimes slightly too large.  When you add
> them together, sometimes you're lucky and the errors cancel out and you
> happen to get what you hoped for, but sometimes the errors go against you
> and you end up slightly too small or slightly too large.
>
> -scott
>
>
> On Fri, Oct 23, 2015 at 8:34 AM, Rousselot, Richard A <
> Richard.A.Rousselot at centurylink.com> wrote:
>
> > Scott,
> >
> > I agree with everything you said but...  To me if a program/CPU evaluates
> > something internally, then when it reports the result it should be the
> > result as it sees it.  It shouldn't report something different.
> >
> > So using your analogy, I ask a English speaking person a two interrelated
> > questions, they translate the questions to Japanese in their head, then
> > answers one question in Japanese and another in English.  I say pick a
> > language and stick with it.  Either answer my question all in English or
> > all in Japanese don't mix it.
> >
> > I think we are getting to hung up on the details of what is going on
> > internally.  The real question is why don't the two results, which are
> > coming from the same program, agree?  (i.e. return 22.99 not
> > 23.0)
> >
> > Richard
> >
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott Hess
> > Sent: Friday, October 23, 2015 10:05 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Simple Math Question
> >
> > On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne  >
> > wrote:
> >
> > > On Fri, Oct 23, 2015 at 4:16 PM, Rousselot, Richard A <
> > > Richard.A.Rousselot at centurylink.com> wrote:
> > > > So I decided to output 1000 digits, because why not?  So now I am
> > > > more perplexed with all these digits showing it is working the
> > > > opposite of
> > > how I
> > > > expected it.  Why is the second set of equations evaluating to a
> "yes"
> > > when
> > > > it is the only one that is obviously NOT equal to the expression???
> > >
> > > Indeed, that's puzzling :)
> >
> >
> > Just to be clear, though, how floating-point numbers work is breaking
> your
> > expectations because your expectations are wrong when applied to
> > floating-point numbers.  Internally, they are base-2 scientific notation,
> > so asking for more significant digits in the base-10 representation won't
> > help - base-10 fractional numbers cannot always be represented precisely
> in
> > base-2, ALSO base-2 fractional numbers cannot always be 

[sqlite] Simple Math Question

[Jim Callahan]

Pocket calculators and COBOL used binary coded decimal (bcd) numbers to
avoid the representation/round off issues. But this meant another entire
number type (supported with addition, subtraction and having to be type
checked in functions) in addition to integer and floating point; most found
it easier to use integers to keep track on pennies...

On Fri, Oct 23, 2015 at 11:05 AM, Scott Hess  wrote:

> On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne 
> wrote:
>
> > On Fri, Oct 23, 2015 at 4:16 PM, Rousselot, Richard A <
> > Richard.A.Rousselot at centurylink.com> wrote:
> > > So I decided to output 1000 digits, because why not?  So now I am more
> > > perplexed with all these digits showing it is working the opposite of
> > how I
> > > expected it.  Why is the second set of equations evaluating to a "yes"
> > when
> > > it is the only one that is obviously NOT equal to the expression???
> >
> > Indeed, that's puzzling :)
>
>
> Just to be clear, though, how floating-point numbers work is breaking your
> expectations because your expectations are wrong when applied to
> floating-point numbers.  Internally, they are base-2 scientific notation,
> so asking for more significant digits in the base-10 representation won't
> help - base-10 fractional numbers cannot always be represented precisely in
> base-2, ALSO base-2 fractional numbers cannot always be represented
> precisely in base-10, so it's like a game of telephone where you can end up
> slightly removed from where you started out, even though it seems like it's
> a simple round trip.  Since each individual digit cannot be represented
> perfectly, it doesn't matter how many digits of precision you ask for,
> you'll always be able to find cases where it doesn't line up like you
> expect.
>
> Think of it this way: Find an English sentence, and find an English to
> Japanese translator.  Translate each individual word of the sentence from
> English to Japanese, then concatenate the results together.  Then translate
> the entire original sentence to Japanese.  The results will almost never be
> the same.  Then do the same process translating the Japanese back to
> English.  Again, the two routes will provide different results, _and_ both
> of those results will almost certainly not match the original English
> sentence.  This isn't a reflection of the translator's abilities at all.
>
> I'm not saying the computer is always right, just that the computer is
> following a very strict recipe with reproducible results.  I don't mean
> reproducible like your three examples make logical sense to you, the user,
> I mean reproducible like my Intel box gives the same results as my AMD box
> as my ARM box.  If you want to be able to deal with fractional decimal
> values with high fidelity, you either need to arrange for base-10
> representation (slow, because computers have to simulate it), or you have
> to do your math in shifted fashion (fast, but can be error prone).
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite3 file as database

[Jim Callahan]

sanam
If renaming the file does not work; then use whatever package that can read
the file to read  it in and write it back out as a comma or tab delimited
file.

In the SQLite command line interface (CLI, implemented as SQLite3.exe) one
can:
"Use the ".import" command to import CSV (comma separated value) data into
an SQLite table.
... Note that it is important to set the "mode" to "csv" before running the
".import" command. This is necessary to prevent the command-line shell from
trying to interpret the input file text as some other format.

sqlite> *.mode csv*
sqlite> *.import C:/work/somedata.csv tab1*

https://www.sqlite.org/cli.html

Jim

On Sat, Sep 12, 2015 at 8:19 AM,  wrote:

> Hello dears
> I have a database file as database.db3 but I have to use database.sqlite3 .
> How can I convert the db3 file to sqlite3 file?
> Thanks for your help
> sanam
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] CSV excel import

[Jim Callahan]

Another option is to save the Excel file as tab separated values. Using
tabs as delimiters avoids some of the comma issues.
Jim
On Jul 30, 2015 6:07 PM, "R.Smith"  wrote:

>
>
> On 2015-07-30 11:12 PM, Sylvain Pointeau wrote:
>
>> Le jeudi 30 juillet 2015, Simon Slavin  a ?crit :
>>
>>  Yes, as I wrote, this bad behaviour (which you could reasonably call a
>>> bug) is documented.  That's how Microsoft wrote Excel and that's the way
>>> they want it to work, and that's how it will continue to work.
>>>
>>> Simon.
>>>
>>>  There is a workaround for the leading 0, but you cannot have multi-line
>> when doing this. It is one or the other, too bad! I though generating xml
>> for excel instead of csv, but I didn't have time to try yet.
>>
>
> Something I like to point out to clients everywhere:
>
> Excel is intended (much like Calc etc.) to be a financial spreadsheet
> system, NOT a data-manipulation tool. Its Row/Column/Cell backbone simply
> lends well to the latter and so people press it into service - but that
> wasn't the design goal.
>
> On that note, if you output things to Excel in CSV (or TSV formats) and
> you have columns suffering leading zero text, you can simply Prepend an
> Equals sign.
>
> i.e. if this is your CSV:
>
> ID, Name, Age
> "00017", John, 14
> "10044", Joan, 17
> "00038", James, 16
>
> Which will import wrong losing leading zeroes, then change it to this:
>
> ID, Name, Age
> ="00017", John, 14
> ="10044", Joan, 17
> ="00038", James, 16
>
> and Excel will behave perfectly well without any added weird characters or
> the like.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Comparing same data in two tables in two separate databases

[Jim Callahan]

yes, ATTACH and then join using a SELECT statement.

After you do the ATTACH, this video shows how to do the join using the
SQLite command line interface. Notice how you can keep track of identically
named fields in the two tables.
https://www.youtube.com/watch?v=NcrZoHselPk

Jim


On Tue, Jul 21, 2015 at 9:09 PM, Igor Tandetnik  wrote:

> On 7/21/2015 8:54 PM, Hayden Livingston wrote:
>
>> I would like to compare two tables (of the same name) in two separate
>> database files.
>>
>
> Are you looking for ATTACH DATABASE (
> http://www.sqlite.org/lang_attach.html ) ?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Doc page revision request

[Jim Callahan]

I Simon's point about idiotic web filters is valid.

"Cnt" is innocuous in formal documentation where by context it clearly
means "count", but think of how people type text messages. If an online
chat board in html had text like messages then a machine learning algorithm
(for a web filter) would tend to associate "cnt" with sexually explicit and
racially offensive language that would not be appropriate for an elementary
school aged child.

By middle school the student and their friends are probably experimenting
with the language

Web  filters are sometimes used in corporations, government agencies and
public facilities, so I can see why it might be an issue, even though "cnt"
is completely innocuous in formal documentation in a way it would not be in
a "how many ... does it take to change light bulb" joke or in a string of
offensive expletives.

It is a matter of context. And to a crudely programmed machine learning
algorithm it is all html text with no context.

Jim


On Tue, Jul 21, 2015 at 10:52 AM, Bernardo Sulzbach <
mafagafogigante at gmail.com> wrote:

> About using "cnt", it is by far not just this page. There are tons of
> documentation and programming pages out there that use "cnt" instead
> of "count".
>
> The last part of your message seems more valid, though.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Jim Callahan]

"Unlike most SQL databases, SQLite does not restrict the type of data that
may be inserted into a column based on the columns declared type. Instead,
SQLite uses dynamic typing <https://www.sqlite.org/datatype3.html>. The
declared type of a column is used to determine the affinity
<https://www.sqlite.org/datatype3.html#affinity> of the column only."
https://www.sqlite.org/lang_createtable.html

Each column in an SQLite 3 database is assigned one of the following type
affinities:

   - TEXT
   - NUMERIC
   - INTEGER
   - REAL
   - NONE

...The affinity of a column is determined by the declared type of the
column, according to the following rules in the order shown:

   1.

   If the declared type contains the string "INT" then it is assigned
   INTEGER affinity.
   2.

   If the declared type of the column contains any of the strings "CHAR",
   "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type
   VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
   3.

   If the declared type for a column contains the string "BLOB" or if no
   type is specified then the column has affinity NONE.
   4.

   If the declared type for a column contains any of the strings "REAL",
   "FLOA", or "DOUB" then the column has REAL affinity.
   5.

   Otherwise, the affinity is NUMERIC.

Note that the order of the rules for determining column affinity is
important. A column whose declared type is "CHARINT" will match both rules
1 and 2 but the first rule takes precedence and so the column affinity will
be INTEGER.
https://www.sqlite.org/datatype3.html#affinity

Thus, in the affinity rules, "Notice that the type VARCHAR contains the
string "CHAR" and is thus assigned TEXT affinity."

These rules allow  complex SQL CREATE TABLE statements to be run (for
compatibility) and mapped to the five storage affinities (which keep SQLite
small and fast) used by SQLite.

Jim Callahan
Orlando, FL

On Thu, Jul 16, 2015 at 6:09 PM, Simon Slavin  wrote:

>
> On 16 Jul 2015, at 10:46pm, Hayden Livingston 
> wrote:
>
> > It seems that
> >
> > CREATE TABLE A( something varchar(255) )
> >
> > it just
> >
> > CREATE TABLE A( something text )
> >
> > Why have it at all?
>
> Lots of people take SQL code originally intended for other SQL engines,
> start up SQLite and want it to work first time.  So it does.  It won't
> perfectly copy the behaviour of engines which respect the '255' but it's
> good enough to assure the programmer that SQLite is worth using.
>
> Simon.
> ___
> 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

[Jim Callahan]

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] VFS for an MCU internal flash

[Jim Callahan]

Here is a link to a more recent (2013) discussion with a more recent
(3.x.xx vs 2.x.xx) version of SQLite:

http://comments.gmane.org/gmane.comp.db.sqlite.general/83038

Jim

On Mon, Jul 13, 2015 at 11:22 AM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> SQLite expects a file system.
> "SQLite reads and writes directly to ordinary disk files."
> https://www.sqlite.org/about.html
>
> You need a minimal file system.
>
> A file system does not have to be large.
> CP/M ran on an 8 bit Z-80 with less than 64k of RAM  (but the original
> CP/M was written in Z-80 assembly language -- not C).
>
> Here is an article on a stripped down Linux for M3/M4: uCLinux
>
> http://electronicdesign.com/embedded/practical-advice-running-uclinux-cortex-m3m4
>
> And here is website for SQLite for uClinux.
> http://www.menie.org/georges/uClinux/sqlite.html
>
>
> The other option would be to emulate the Linux system file calls.
> You could start from scratch or download the Linux source code (assuming
> you have installed and know how to use git source code management system):
>
> # the Linux kernel (approx. 640MB download):
> $ git clone git://git.kernel.org/pub/scm/linux/kernel/git/torvalds/linux.git
>
>
>
> https://www.kernel.org/pub/software/scm/git/docs/user-manual.html#how-to-get-a-git-repository
>
> Hope this helps.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Mon, Jul 13, 2015 at 5:52 AM, Shuhrat Rahimov 
> wrote:
>
>> Hello,
>> I was trying to run SQLite on ARM Cortex M4 CPU. What I need is a minimal
>> SQLite without the most features. I have no file system, so I wanted to
>> save a single file db on the internal MCU flash. I have successfully cross
>> compiled SQLite for ARM Cortex M4. I have set among others compile time
>> options SQLITE_OS_OTHER=1 and SQLITE_THREADSAFE=0.  Now I wanted to
>> implement a VFS. I want to save db tables directly in internal flash
>> without a file system. I have difficulties to understand test_onefile.c
>> implementation provided by SQLite. How could I edit that file in order to
>> be able to write file content on the internal MCU flash? Please, could
>> someone explain it to me or give some links where it is explained?
>> Kind Regards
>> ___
>> 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

[Jim Callahan]

SQLite expects a file system.
"SQLite reads and writes directly to ordinary disk files."
https://www.sqlite.org/about.html

You need a minimal file system.

A file system does not have to be large.
CP/M ran on an 8 bit Z-80 with less than 64k of RAM  (but the original CP/M
was written in Z-80 assembly language -- not C).

Here is an article on a stripped down Linux for M3/M4: uCLinux
http://electronicdesign.com/embedded/practical-advice-running-uclinux-cortex-m3m4

And here is website for SQLite for uClinux.
http://www.menie.org/georges/uClinux/sqlite.html


The other option would be to emulate the Linux system file calls.
You could start from scratch or download the Linux source code (assuming
you have installed and know how to use git source code management system):

# the Linux kernel (approx. 640MB download):
$ git clone git://git.kernel.org/pub/scm/linux/kernel/git/torvalds/linux.git


https://www.kernel.org/pub/software/scm/git/docs/user-manual.html#how-to-get-a-git-repository

Hope this helps.

Jim Callahan
Orlando, FL



On Mon, Jul 13, 2015 at 5:52 AM, Shuhrat Rahimov 
wrote:

> Hello,
> I was trying to run SQLite on ARM Cortex M4 CPU. What I need is a minimal
> SQLite without the most features. I have no file system, so I wanted to
> save a single file db on the internal MCU flash. I have successfully cross
> compiled SQLite for ARM Cortex M4. I have set among others compile time
> options SQLITE_OS_OTHER=1 and SQLITE_THREADSAFE=0.  Now I wanted to
> implement a VFS. I want to save db tables directly in internal flash
> without a file system. I have difficulties to understand test_onefile.c
> implementation provided by SQLite. How could I edit that file in order to
> be able to write file content on the internal MCU flash? Please, could
> someone explain it to me or give some links where it is explained?
> Kind Regards
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] convert a PostgreSQL to sqlite

[Jim Callahan]

Are you using an PostgreSQL add-ins such as "PostGIS" or an object
relational mapper? In that case I would recommend an intermediate program
or language.

For example, if you are using the PostGIS add-in I would recommend going
through a GIS program such as ESRI's ArcGIS or the open source QGIS. There
is a GIS addin for SQLite (Spatialite), but I would not trust a SQL to SQL
transfer (PostgreSQL/PostGIS to SQLite/Spatialite) to transfer GIS
information, since there is so much going on in the GIS above the level of
SQL and there might be implementation differences (of equivalent GIS
concepts) at the SQL level.

Jim Callahan
Orlando, FL

On Sun, Jul 5, 2015 at 4:46 PM, John McKown 
wrote:

> On Sun, Jul 5, 2015 at 7:44 AM,  wrote:
>
> > Is there a way (free, open source, Ubuntu) to convert a
> > PostgreSQL-database with data in it to a sqlite database?
> >
>
> If you're looking for something already done, I am not aware of anything
> that will do that "out of the box"
>
> convertPGtoSQLite pgdatabase SQLite.db
>
> ?===?
>
> ?One way is to use pg_dump something like:
>
> pg_dump -C -c -a -f database-information.txt -O --column-inserts
> --quote-all-identifiers --dbname=theDataBase
>
> You'd then need to _somehow_ (your choice), manipulate this file so that
> you can feed it into the sqlite3 command. Or not. Just "for fun" (no
> profit), I did the commands:
>
>  $ pg_dump -C -c -n racf -f racf.info -O --column-inserts
> --quote-all-identifiers joarmc
> $  sqlite racf.db2   $sqlite3  Error: near line 5: near "SET": syntax error
> Error: near line 6: near "SET": syntax error
> Error: near line 7: near "SET": syntax error
> Error: near line 8: near "SET": syntax error
> Error: near line 9: near "SET": syntax error
> Error: near line 10: near "SET": syntax error
> Error: near line 16: near "SCHEMA": syntax error
> Error: near line 23: near "COMMENT": syntax error
> Error: near line 26: near "SET": syntax error
> Error: near line 32: near "TYPE": syntax error
> Error: near line 42: near "TYPE": syntax error
> Error: near line 52: near "TYPE": syntax error
> Error: near line 62: near "FUNCTION": syntax error
> Error: near line 67: near "if": syntax error
> Error: near line 68: near "if": syntax error
> Error: near line 69: near "if": syntax error
> Error: near line 70: near "if": syntax error
> Error: near line 71: near "if": syntax error
> Error: near line 72: near "return": syntax error
> Error: near line 73: cannot commit - no transaction is active
> Error: near line 74: near "$$": syntax error
> Error: near line 81: near "FUNCTION": syntax error
> Error: near line 86: near "if": syntax error
> Error: near line 87: near "if": syntax error
> Error: near line 88: near "if": syntax error
> Error: near line 89: near "if": syntax error
> Error: near line 90: near "if": syntax error
> Error: near line 91: near "return": syntax error
> Error: near line 92: cannot commit - no transaction is active
> Error: near line 93: near "$$": syntax error
> Error: near line 96: near "SET": syntax error
> Error: near line 98: near "SET": syntax error
> Error: near line 91437: near "USING": syntax error
> ...
>
> Hum, not too shabby.
>
>
> ===
>
> A different approach would be to use some language, such as Perl (or
> Python, Java, R, ...) with the appropriate PostgreSQL and SQLite drivers to
> read the PostgreSQL data base and write it to ?SQLite.
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] convert a PostgreSQL to sqlite

[Jim Callahan]

Welcome to SQLite3!

Make sure SQLite3 is appropriate for your use; for some use cases a
client-server database like PostgreSQL is recommended:

"Situations Where A Client/Server RDBMS May Work Better
...If there are many client programs sending SQL to the same database over
a network, then use a client/server database engine [such as PostgreSQL]
instead of SQLite. SQLite will work over a network filesystem, but because
of the latency associated with most network filesystems, performance will
not be great.   ...A good rule of thumb is to avoid using SQLite in
situations where the same database will be accessed directly (without an
intervening application server) and simultaneously from many computers over
a network."
https://www.sqlite.org/whentouse.html

Assuming SQLite is appropriate/use scenario to your application; I agree
with Simon to use the SQL dump to transfer.

One area where SQL itself is not standardized is the syntax for testing for
NOT NULL.

According two the PostgreSQL documentation there are two ways to test for
NULL; SQLite will only accept the second method "NOTNULL"; the "IS NOT
NULL" syntax does not work.

PostgreSQL documentation:
"To check whether a value is or is not null, use the constructs:

expression IS NULLexpression IS NOT NULL

or the equivalent, but nonstandard, constructs:

expression ISNULLexpression NOTNULL

Do not write expression = NULL because NULL is not "equal to" NULL."
http://www.postgresql.org/docs/9.1/static/functions-comparison.html


I had used Microsoft Access, so I was used to the "IS NOT NULL" syntax and
had to learn "NOTNULL".

It  is not the end of the world, but it could be a gotcha.

Other than that one exception, whenever I have gotten the SQL correct
SQLite3 has worked for me.

HTH,
Jim Callahan
Orlando, FL

On Sun, Jul 5, 2015 at 4:11 PM, Simon Slavin  wrote:

>
> On 5 Jul 2015, at 1:44pm, c.buhtz at posteo.jp wrote:
>
> > Is there a way (free, open source, Ubuntu) to convert a
> > PostgreSQL-database with data in it to a sqlite database?
>
> Dump the Postgres database to a text file of SQL commands.  Then use the
> SQLite command-line tool to .read that text file.
>
> <http://www.postgresql.org/docs/9.4/static/backup-dump.html>
> <https://www.sqlite.org/download.html>
> <https://www.sqlite.org/cli.html>
>
> There may be some minor differences in the two versions of SQL and you may
> have to deal with them before SQLite will .read your file.  If you're not
> sure of equivalents post here and we'll try to help.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

SQLite in GIS
I had known about Spatialite, but I hadn't realized that QGIS was using
SQLite (both indpendently and through Spatialite).

"While PostGIS is generally used on a server to provide spatial database
capabilities to multiple users at the same time, QGIS also supports the use
of a file format called *spatialite* that is a lightweight, portable way to
store an entire spatial database in a single file. ...Using the [Database]
Browser panel, we can create a new *spatialite database* and get it setup
for use in QGIS"
http://docs.qgis.org/2.0/en/docs/training_manual/databases/spatialite.html

A YouTube video
https://www.youtube.com/watch?v=3nG-oY5p9O0

"QGIS has support for approximately 2,700 known CRSs [Coordinate Reference
Systems]. Definitions for each CRS are stored in a *SQLite database* that
is installed with QGIS."
https://docs.qgis.org/2.2/en/docs/user_manual/working_with_projections/working_with_projections.html

For more information on the Spatialite family of projects:
http://www.gaia-gis.it/gaia-sins/

Jim Callahan
Orlando, FL

On Wed, Feb 18, 2015 at 9:34 AM, Richard Hipp  wrote:

> In a feeble effort to do "marketing", I have revised the "Appropriate
> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> Things" and "Edge of the Network" above the break.  See:
>
> https://www.sqlite.org/whentouse.html
>
> Please be my "focus group", and provide feedback, comments,
> suggestions, and/or criticism about the revised document.   Send your
> remarks back to this mailing list, or directly to me at the email in
> the signature.
>
> Thank you for your help.
>
> --
> 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] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Jim Callahan]

?Readers on GitHub might also be interested in ODBC and JDBC drivers for
SQLite:

ODBC
http://www.ch-werner.de/sqliteodbc/

JDBC
http://www.ch-werner.de/javasqlite/index.html?

Also, a few weeks ago there was a discussion on this list of how to call
SQLite from C# and an add-in for Microsoft Visual Studio.

Also there is a (very high quality!)  RSQLite interface from the R
statistical language to SQLite.

RSQLite (on CRAN)
http://cran.r-project.org/web/packages/RSQLite/index.html
as well as

sqldf (on CRAN)
http://cran.r-project.org/web/packages/sqldf/sqldf.pdf

sqldf (on GitHub)
https://github.com/ggrothendieck/sqldf


As I understand there are interfaces from a variety of other languages
including Python and PHP.

Jim Callahan
Orlando, FL


On Mon, May 4, 2015 at 8:55 AM, Gerald Bauer  wrote:

> Hello,
>   Thanks I've updated the awesome-sqlite list [1] and now includes a
> new section on SpatiaLite and more admin tools. 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] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Jim Callahan]

This link lists five SQLite "management tools":
1. SQLite Expert
2. SQLite Administrator
3. SQLite Database Browser
4. SQLite Spy
5. SQLite Manager (Firefox plug-in)
http://www.ginktage.com/2013/08/5-popular-and-free-sqlite-management-tools/

Also there is the Spatialite project which builds a spatial database (ie.
for GIS) on top of SQLite and it has its own GUI:

Spatialite GUI
https://www.gaia-gis.it/fossil/spatialite_gui/index

There are probably many, many more, but this is a start.

Jim Callahan
Orlando, FL

On Sun, May 3, 2015 at 11:28 AM, Gerald Bauer 
wrote:

> Hello,
>
>   I've started yet another awesome collection about  - surprise,
> surprise - SQLite - inspired by the awesome- trend on
> GitHub that collects links on a single page - in Markdown e.g.
> README.md - in a git repo.
>
>What?s news? The awesome-sqlite repo collects SQLite goodies,
> tools, scripts, books, etc. Still early and rough. Anything missing?
> Contributions welcome. 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] SQLite using internally by Windows 10

[Jim Callahan]

Any details on SQLite in Windows 10?

Don't remember anything in MS Build video cast last week.
Closest, I could find were these two old posts:

1. USING SQLITE IN WINDOWS 10 UNIVERSAL APPS
http://igrali.com/2015/05/01/using-sqlite-in-windows-10-universal-apps/

2. SQLite.Net-PCL  (Portable Code Library fork)
https://github.com/oysteinkrog/SQLite.Net-PCL

Jim Callahan

On Thu, Apr 30, 2015 at 11:24 PM, Richard Hipp  wrote:

> https://twitter.com/john_lam/status/593837681945092096
>
> --
> 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] SQLite and Scientific Computing: Arrays and Complex Numbers

[Jim Callahan]

Arrays of data are used are used in almost every modeling application; a
much smaller subset of those applications use complex numbers.

As Peter Baumann has argued:
"Science and engineering data often come as sensor, image, simulation, and
statistics data digitized/sampled from some natural phenomenon or generated
from some simulation. These naturally resemble raster data ("arrays" in
programming) of some particular dimensionality. In the Earth Sciences, for
example, we find 1-D sensor time series, 2-D satellite images, 3-D x/y/t
image time series and x/y/z geophysical cubes, and 4-D x/y/z/t atmosphere
and ocean data. Array data application domains span Earth, Space, Life, and
Social sciences, plus many more. Array Databases are being standardized by
ISO (nickname: "Science SQL")."
https://rd-alliance.org/groups/big-data-analytics-ig/wiki/big-data-use-case-massive-multi-dimensional-arrays.html

For example, in the electric utility industry, looks at "load curves" at
the amount of power used in each hour of the year (24*7*52 = 8,760).
http://www.eia.gov/todayinenergy/detail.cfm?id=4190

The CSV file of the sample load shape can be downloaded here (there are
zeros for the last 2 months):
http://www.eia.gov/todayinenergy/chartdata/AnnualLoadCurve.csv

With "smart metering" utilities have curves like this for every single
customer (except that smart meters  measure every 15 minutes (4*8,760)
instead of once per hour).  That is a lot different than having a billing
system record for each customer with a name, address and two or three items
per month.

COMPLEX NUMBERS
Calculations involving sine waves such as AC circuits, radio engineering or
seismology typically use complex numbers:
http://en.wikipedia.org/wiki/Mathematics_of_radio_engineering

Since, the advent of SPICE at Berkeley in the 1970s
http://www.ecircuitcenter.com/AboutSPICE.htm

there are a lot of electric circuit simulation models:
http://www.semiwiki.com/forum/showwiki.php?title=Semi+Wiki:EDA+Open+Source+Tools+Wiki

These are all potential users of SQLite3 for an application file format.

So, the application of arrays and complex numbers go far beyond the one
question posed to this list with arrays of integers and reals being far
more common than arrays of complex numbers.

Complex numbers are included as types in C, C#, FORTRAN and Python and are
being added to server databases (Oracle and Microsoft SQL Server) as user
defined types, so programmers and server database administrators may
develop an expectation that complex types or user defined types will be
supported SQLite3.

But, even so, I would expect that users of complex numbers would be an
extremely small subset of data base users (less than 5%? or even less than
1%?).

Jim Callahan
Orlando, FL




On Mon, Apr 27, 2015 at 12:48 PM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:

> All,
>
> I've been enjoying this discussion and have learned a thing or two from
> all the suggestions.
>
> My particular problem is indeed solved. Adding a simple blob to my
> original table is the best solution in this case. If you think about the
> trace data as simply a screenshot of the analyzer display then I think it
> is easier to understand why I don't want to bother with more complex
> solutions. Even though they may be technically more correct, they are
> overkill for what I'm trying to accomplish. I'm just saving a screenshot,
> minus the graticule and all the other decorations on the display. There's
> no need to make it any more complicated than that. I don't even have to
> save the X-axis elements as those are derived from other parameters that
> are already known. Someone asked if the trace data was time stamped. It is
> not. Date and time of the test are stored along with all the other relevant
> information.
>
> If I ever have to deal with complex numbers as my core data I have some
> pretty good ideas on how to handle them now. BTW, the languages I use most,
> VEE & C#, both support complex data types. Given the amount of use database
> programs see in the scientific world I'm surprised there is no native
> complex data type (I can appreciate the difficulty in implementing such a
> thing). Array support for each type would be nice too. We have this in VEE
> and it is so easy to use. But for now every programmer working in science
> or engineering has to re-invent the wheel.
>
> For the curious here are the analyzers I'm working with:
>
> http://www.keysight.com/en/pc-101745%3Aepsg%3Apgr/pna-network-analyzers-300-khz-to-11-thz?cc=US=eng
>
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org 

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

[Jim Callahan]

Oops, left out two links, the Rasdaman 10.0 quote is from the Rasdaman
roadmap.
http://rasdaman.org/roadmap

and the quote about the ISO designation is from the Array SQL Rasdaman page:
http://rasdaman.com/ArraySQL/

But, the conclusion remains the same:

So, Multi-dimensional arrays may be coming to SQL via the ISO Standard 9075
and to SQLite3 via Rasdaman, but only if, Rasdaman decides to use SQLite3
as their default backend in their 10.0.0 release.


Jim Callahan
Orlando, FL

On Mon, Apr 27, 2015 at 12:33 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> Two more thoughts (and I have to get back to work!):
> 1. Rasdaman
> 2. ISO SQL/MDA (multi-dimensional arrays)
>
> In June 2014, British tech website "The Register" reported, "the ISO SQL
> working group agreed to start work on SQL/MDA (multi-dimensional array)
> specs.   ...A separate effort, called Rasdaman <http://rasdaman.org/> (a
> scalable multi-dimensional array analytics server) has been working for
> some time to apply an SQL-like query language to array databases.
> Rasdaman's backer, Peter Baumann of Jacobs University Bremen in Germany,
> put forward the proposal now adopted by the ISO."
>
> http://www.theregister.co.uk/2014/06/26/sql_to_worlddog_we_doing_big_data_too/
>
> Apparently, Rasdaman runs with PostgreSQL, but is considering having
> SQLite as a default:
>
> "Suggested *big* changes for 10.0
> Improve UX by removing big external dependencies (by default)
>
>- rasserver: use *SQLite*/filesystem backend by default instead of
>PostgreSQL
>- petascope: use embedded ASQLDB instead of PostgreSQL
>- petascope: local deployment with an embedded jetty in $RMANHOME/bin
>for example, rather than in an external Tomcat. This would be managed by
>similarly start/stop_petascope.sh for example
>? "
>
> Rasdaman lists the official ISO designation:
> "ISO Array-SQL:
> ...
> It's ISO: In June 2014, ISO/IEC JTC1 SC32 WG3 has decided to establish ISO
> 9075 Part 15: SQL/MDA as ISO's latest SQL extension, specifically for Big
> Science Data."
>
> The only semi-official (non-Rasdaman) ISO document I could find (with one
> or two Google searches) was this presentation:
>
> "WG3 is incorporating two interesting new capabilities into
> the next version of 9075:
> ? Support for JSON ... [detail omitted]
> ? Support for Multi Dimensional Arrays ... [detail omitted]"
> http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf
>
> So, Multi-dimensional arrays may be coming to SQL via the ISO Standard
> 9075 and to SQLite3 via Rasdaman, but only if, Rasdaman decides to use
> SQLite3 as their default backend in their 10.0.0 release.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Mon, Apr 27, 2015 at 11:23 AM, Jim Callahan <
> jim.callahan.orlando at gmail.com> wrote:
>
>> Two more thoughts (and you'll stay with SQLite):
>> 1. SciQL -> MonetDB -> sockets -> embedding R in MonetDB (ala PostgresQL)
>> http://en.wikipedia.org/wiki/MonetDB
>>
>> "there was one issue that we could not solve: Processing data from the
>> database with R required transferring the relevant data over the socket
>> first. This is fine and "fast enough" for  up to ? say ? several thousand
>> rows, but not for much more. We have had a lot of demand for transferring
>> larger amounts of data from users. Hence, we chose to go in a different
>> direction.
>> Starting with the Oct2014 release, MonetDB will ship with a feature we
>> call *R-Integration*. R has some support for running embedded
>> <http://developer.r-project.org/embedded.html> in another application, a
>> fact we leverage heavily. What it does is make R scripts a first class
>> citizen of the SQL world in MonetDB."
>> https://www.monetdb.org/content/embedded-r-monetdb
>>
>> So, instead of having an embedded database (like SQLite3) embedded in a
>> language, (like Python or R), they windup having the database swallow the
>> language!!! (embeding the language in the database -- instead of the other
>> way arround).
>>
>> 2. ROLAP -> Mondrian -> TomCat
>> As opposed the the scientific community, the business community thinks in
>> terms of "cubes" rather than arrays which gets one to Online Analytic
>> Processing (OLAP) or when implemented over a relational database: ROLAP. Or
>> the whole thing gets rebranded by marketing as "Business Intelligence" or
>> "Analysis Services" or "Pivot Tables".
>> The open source ROLAP tool is "Mondrian".
>>
>> Mondrian can run over SQLit

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

[Jim Callahan]

Two more thoughts (and I have to get back to work!):
1. Rasdaman
2. ISO SQL/MDA (multi-dimensional arrays)

In June 2014, British tech website "The Register" reported, "the ISO SQL
working group agreed to start work on SQL/MDA (multi-dimensional array)
specs.   ...A separate effort, called Rasdaman <http://rasdaman.org/> (a
scalable multi-dimensional array analytics server) has been working for
some time to apply an SQL-like query language to array databases.
Rasdaman's backer, Peter Baumann of Jacobs University Bremen in Germany,
put forward the proposal now adopted by the ISO."
http://www.theregister.co.uk/2014/06/26/sql_to_worlddog_we_doing_big_data_too/

Apparently, Rasdaman runs with PostgreSQL, but is considering having SQLite
as a default:

"Suggested *big* changes for 10.0
Improve UX by removing big external dependencies (by default)

   - rasserver: use *SQLite*/filesystem backend by default instead of
   PostgreSQL
   - petascope: use embedded ASQLDB instead of PostgreSQL
   - petascope: local deployment with an embedded jetty in $RMANHOME/bin
   for example, rather than in an external Tomcat. This would be managed by
   similarly start/stop_petascope.sh for example
   ? "

Rasdaman lists the official ISO designation:
"ISO Array-SQL:
...
It's ISO: In June 2014, ISO/IEC JTC1 SC32 WG3 has decided to establish ISO
9075 Part 15: SQL/MDA as ISO's latest SQL extension, specifically for Big
Science Data."

The only semi-official (non-Rasdaman) ISO document I could find (with one
or two Google searches) was this presentation:

"WG3 is incorporating two interesting new capabilities into
the next version of 9075:
? Support for JSON ... [detail omitted]
? Support for Multi Dimensional Arrays ... [detail omitted]"
http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf

So, Multi-dimensional arrays may be coming to SQL via the ISO Standard 9075
and to SQLite3 via Rasdaman, but only if, Rasdaman decides to use SQLite3
as their default backend in their 10.0.0 release.

Jim Callahan
Orlando, FL



On Mon, Apr 27, 2015 at 11:23 AM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> Two more thoughts (and you'll stay with SQLite):
> 1. SciQL -> MonetDB -> sockets -> embedding R in MonetDB (ala PostgresQL)
> http://en.wikipedia.org/wiki/MonetDB
>
> "there was one issue that we could not solve: Processing data from the
> database with R required transferring the relevant data over the socket
> first. This is fine and "fast enough" for  up to ? say ? several thousand
> rows, but not for much more. We have had a lot of demand for transferring
> larger amounts of data from users. Hence, we chose to go in a different
> direction.
> Starting with the Oct2014 release, MonetDB will ship with a feature we call
>  *R-Integration*. R has some support for running embedded
> <http://developer.r-project.org/embedded.html> in another application, a
> fact we leverage heavily. What it does is make R scripts a first class
> citizen of the SQL world in MonetDB."
> https://www.monetdb.org/content/embedded-r-monetdb
>
> So, instead of having an embedded database (like SQLite3) embedded in a
> language, (like Python or R), they windup having the database swallow the
> language!!! (embeding the language in the database -- instead of the other
> way arround).
>
> 2. ROLAP -> Mondrian -> TomCat
> As opposed the the scientific community, the business community thinks in
> terms of "cubes" rather than arrays which gets one to Online Analytic
> Processing (OLAP) or when implemented over a relational database: ROLAP. Or
> the whole thing gets rebranded by marketing as "Business Intelligence" or
> "Analysis Services" or "Pivot Tables".
> The open source ROLAP tool is "Mondrian".
>
> Mondrian can run over SQLite, but seems to run more often with a Java
> application server such as TomCat or JBoss.
>
> Mondrian Cube with SQLite
> This entry was written by haoccheng, posted on October 7, 2012
> "Mondrian is an in-memory OLAP cube (cache) built on top of relational
> database. Cube is a multi-dimensional data structure. A cell holds numeric
> measurements of a business; aggregation can be executed efficiently along
> dimensions. MDX is the query language of Mondrian and a query can be broken
> down to lookup at the cube (cache hit) and direct SQL query against the
> underneath relational database.
> I play with the Mondrian and SQLite.
> #1: Create a sqlite database (I borrow the data model from the Mondrian
> tutorial by Slawomir, architect of Pentaho)."
> https://haoccheng.wordpress.com/2012/10/07/mondrian-cube-with-sqlite/
>
> Creating a basic Mondrian OLAP Cube
> BY SLAWOMIR CHODNICKI, ON JULY 10TH, 2010
>

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

[Jim Callahan]

Two more thoughts (and you'll stay with SQLite):
1. SciQL -> MonetDB -> sockets -> embedding R in MonetDB (ala PostgresQL)
http://en.wikipedia.org/wiki/MonetDB

"there was one issue that we could not solve: Processing data from the
database with R required transferring the relevant data over the socket
first. This is fine and "fast enough" for  up to ? say ? several thousand
rows, but not for much more. We have had a lot of demand for transferring
larger amounts of data from users. Hence, we chose to go in a different
direction.
Starting with the Oct2014 release, MonetDB will ship with a feature we call
*R-Integration*. R has some support for running embedded
<http://developer.r-project.org/embedded.html> in another application, a
fact we leverage heavily. What it does is make R scripts a first class
citizen of the SQL world in MonetDB."
https://www.monetdb.org/content/embedded-r-monetdb

So, instead of having an embedded database (like SQLite3) embedded in a
language, (like Python or R), they windup having the database swallow the
language!!! (embeding the language in the database -- instead of the other
way arround).

2. ROLAP -> Mondrian -> TomCat
As opposed the the scientific community, the business community thinks in
terms of "cubes" rather than arrays which gets one to Online Analytic
Processing (OLAP) or when implemented over a relational database: ROLAP. Or
the whole thing gets rebranded by marketing as "Business Intelligence" or
"Analysis Services" or "Pivot Tables".
The open source ROLAP tool is "Mondrian".

Mondrian can run over SQLite, but seems to run more often with a Java
application server such as TomCat or JBoss.

Mondrian Cube with SQLite
This entry was written by haoccheng, posted on October 7, 2012
"Mondrian is an in-memory OLAP cube (cache) built on top of relational
database. Cube is a multi-dimensional data structure. A cell holds numeric
measurements of a business; aggregation can be executed efficiently along
dimensions. MDX is the query language of Mondrian and a query can be broken
down to lookup at the cube (cache hit) and direct SQL query against the
underneath relational database.
I play with the Mondrian and SQLite.
#1: Create a sqlite database (I borrow the data model from the Mondrian
tutorial by Slawomir, architect of Pentaho)."
https://haoccheng.wordpress.com/2012/10/07/mondrian-cube-with-sqlite/

Creating a basic Mondrian OLAP Cube
BY SLAWOMIR CHODNICKI, ON JULY 10TH, 2010
"If you?d like to follow the examples you will need access to a database, a
copy of Pentaho Kettle and a Mondrian installation. I will be using MySQL
as RDBMS and JasperServer 3.7. CE
<http://jasperforge.org/projects/jasperserver> for the Mondrian
installation. Other possibilities include Pentaho BI-Server
<http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/>
and
a bare bones Mondrian/JPivot
<http://sourceforge.net/projects/mondrian/files/> and PAT
<http://code.google.com/p/pentahoanalysistool/> installation."
http://type-exit.org/adventures-with-open-source-bi/2010/07/creating-a-basic-mondrian-olap-cube/

So, taking any of the paths (MonetDB or Mondrian) gets you to complex
server setups pretty quickly, the one exception is haoccheng makes a brake
attempt to setup Mondrian over SQLite (but even his attempt requires a
TomCat Java application server):

"#3: Install the Tomcat; verify the http://localhost:8080 up and running.
#4: Download Mondrian; unzip mondrian-version.war to tomcat_home/webapps/
folder.
#5: Get the sqlite jdbc driver: sqlite-jdbc-version.jar; deploy to
tomcat_home/webapps/mondrian/WEB-INF/lib
#6: Change the connection string in Mondrian web.xml
(tomcat_home/webapps/mondrian/WEB-INF) to point to the sqlite database."


Almost makes one pine for MS Excel PivotTables.

Jim Callahan
Orlando, FL

On Sun, Apr 26, 2015 at 9:55 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> The original thread asking about an array of complex numbers has been
> marked as "solved." The requester has decided to serialize the complex
> numbers and store them in a blob. Earlier, Keith had suggested storing
> complex numbers as a pair of real numbers and a separate box table. I
> extended Keith's suggestion with two or three tables, elements, arrays and
> optionally coordinates.
>
> There is some literature on storing arrays in SQL databases. In addition
> complex numbers seem to be the orphan stepchild of programming languages
> (let alone databases). Although FORTRAN IV had complex numbers they were
> not added to the C standard until C99.
>
> Language / Standard / Library
> 
> C / C99/ complex.h
> http://en.wikibooks.org/wiki/C_Programming/C_Reference/complex.h
> C# / 4.0 / System.Numerics.Complex
>
> https://m

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

[Jim Callahan]

The original thread asking about an array of complex numbers has been
marked as "solved." The requester has decided to serialize the complex
numbers and store them in a blob. Earlier, Keith had suggested storing
complex numbers as a pair of real numbers and a separate box table. I
extended Keith's suggestion with two or three tables, elements, arrays and
optionally coordinates.

There is some literature on storing arrays in SQL databases. In addition
complex numbers seem to be the orphan stepchild of programming languages
(let alone databases). Although FORTRAN IV had complex numbers they were
not added to the C standard until C99.

Language / Standard / Library

C / C99/ complex.h
http://en.wikibooks.org/wiki/C_Programming/C_Reference/complex.h
C# / 4.0 / System.Numerics.Complex
https://msdn.microsoft.com/en-us/library/system.numerics.complex(v=vs.110).aspx

Java /?/ Apache Commons
Python/2.6.5/ cmath
https://docs.python.org/2/library/cmath.html

http://en.wikipedia.org/wiki/Complex_data_type

So, I suppose if a company wanted to sponsor it, complex numbers could be
supported through an addin library similar to FTS3 and FTS4 for full text
searches.
http://sqlite.org/fts3.html

Here for example, is a discussion on IBM DeveloperWorks concerning the
Informix database.
https://www.ibm.com/developerworks/community/blogs/gbowerman/entry/sql_and_the_complex_plane?lang=en

Some databases have Abstract Defined Type (Oracle) or User Defined Types
(Microsoft SQL Server) that could be used for complex numbers.

Ironically, the scientific data format NetCDF did not have provision for
complex numbers (it was designed for weather data).
https://www.unidata.ucar.edu/mailing_lists/archives/netcdfgroup/2011/msg00027.html

There are some discusssions of scientific versions of SQL (such as SciQL):

"A Query Language for Multidimensional Arrays:
Design, Implementation, and Optimization Techniques"
http://homepages.inf.ed.ac.uk/libkin/papers/sigmod96a.pdf

"Requirements for Science Data Bases and SciDB"
http://www-db.cs.wisc.edu/cidr/cidr2009/Paper_26.pdf

"SciQL, A Query Language for Science Applications"
http://homepages.cwi.nl/~zhang/papers/arraydb11.pdf

Jim

[sqlite] Thoughts on storing arrays of complex numbers (Solved)

[Jim Callahan]

Does the data from the network analyzer have a datetime stamp?
On Apr 25, 2015 3:45 PM, "Drago, William @ CSG - NARDA-MITEQ" <
William.Drago at l-3com.com> wrote:

> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > users-bounces at mailinglists.sqlite.org] On Behalf Of Scott Hess
> > Sent: Friday, April 24, 2015 3:19 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Thoughts on storing arrays of complex numbers
> > (Solved)
> >
> > On Fri, Apr 24, 2015 at 12:01 PM, Drago, William @ CSG - NARDA-MITEQ
> >  wrote:
> > > Since the data is received from the analyzer as an array of
> > > real/imaginary pairs (R,I,R,I,R,I,R,I...), 3202 elements total,
> > that's
> > > how I will blob and store it. This is the simplest way to add it to
> > > the database. It's just one more field along with all the other data.
> > > If I ever need to operate on that trace data again it's a simple
> > > matter of pulling out of the database and un-blobbing it.
> >
> > In a case like this, I don't think I've ever come to regret suggesting
> > the use of a serialization library, like protobuf (or cap'n proto or
> > third or avro or ...).  When you make your ad-hoc serialization
> > strategy, it works swell for six months, then a new requirement comes
> > downstream and you have to figure out a new format plus how to convert
> > all the old data.  If that happens two or three times, you start to get
> > a combinatoric problem which makes it hard to reason about how a change
> > is going to affect existing installs.  Most such requirements are for
> > an additional field per array index, which many serialization libraries
> > can support pretty transparently.
>
> So, serialize the complex array data then store it in SQLite as a blob?
> I'm working in C# which has built-in support for serialization, do I still
> need a third party library? Other than writing some objects to disk in Java
> quite a few years ago, I have little experience with serialization, sorry
> for the ignorance.
>
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / 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
>

[sqlite] Thoughts on storing arrays of complex numbers

[Jim Callahan]

Keith is close.

I would suggest two tables: Elements and Arrays.

Elements Table
Since arrays are usually referenced by names, I would include an
"arrayname" field in the Elements table to indicate membership in a
particular array. Also, I would not trust the recordID for the internal
ordering of the array, so I would suggest a SeqNo (sequence number or
unidimensional index) for the elements of the array.

CREATE TABLE ComplexElements
(
   ComplexElementID INTEGER PRIMARY KEY,
   ComplexArrayName  CHARACTER NOT NULL DEFAULT 'ComplexArray1',
   SeqNo INTEGER NOT NULL DEFAULT 0,-- zero based
   RealPart REAL NOT NULL DEFAULT 0,
   ImagPart REAL NOT NULL DEFAULT 0
);

The dimensions of the array are an array property and not an element
property, therefore the number of elements in each dimensions are stored in
the ComplexArrays table. SQLite knows nothing about the dimensions (they
are just data) the higher level language calling SQLite coerces the
dimensions on the unidimensional list of array element pairs provided by
SQLite.

I have allowed for up to five dimensions (dim1 through dim5).

Do you want zero or one based arrays?

Do you want row-major or column-major interpretation of element vector?

?
CREATE TABLE  ComplexArrays
(
  ComplexArrayID INTEGER PRIMARY KEY,
  ComplexArrayName  CHARACTER NOT NULL DEFAULT 'ComplexArray1',
  dim1 INTEGER NOT NULL DEFAULT 0,  -- min element 0 in 1 dim
  dim2 DEFAULT NULL ALIAS y,-- dim is maxsize of dim, not coord
  dim3 DEFAULT NULL ALIAS z,
  dim4 DEFAULT NULL,
  dim5 DEFAULT NULL,
);

Coordinates, you would probably want to generate the indices for the arrays
"on the fly" in the higher level language, but if you wanted to reference
the array indices in SQL you might have a third table "Coordinates" which
would store the ComplexArrayName, SeqNo, Coord1 ALIAS ,X Coord2 ALIAS Y,
Coord3 ALIAS Z, Coord4, Coord5.

Then one might create a SQL VIEW  "ComplexData" which would allow one to
query the contents of an array (a view is a stored query that acts as a
virtual table -- you can use the view name as if it were a table in a
query):

SELECT ArrayName, X, Y, Z, RealPart, ImagPart FROM ComplexData
WHERE ArrayName = 'Array1';

or, to select a single element, specify the coordinates:

SELECT ArrayName, X, Y, Z, RealPart, ImagPart FROM ComplexData
WHERE ArrayName = 'Array1' AND X = 0 AND Y = 0 AND Z = 0; -- 3D, zero based
array

Thanks to Keith, he was on the right track.

Warning, my capitalization and names may be inconsistent and my SQL might
be pseudocode, but the intent is create the structures to support the final
two queries.

Hope this helps.

Jim Callahan
Orlando, FL

On Fri, Apr 24, 2015 at 5:52 PM, Keith Medcalf  wrote:

>
> Create table ComplexNumbers
> (
>id integer primary key,
>real real not null default 0,
>imag real not null default 0
> );
>
> Then, where ever you need to use a complex number you store it in the
> complex number table and store the id of that number instead.  For example:
>
> ??
> create table  Boxes
> (
>id integer primary key,
>length integer references ComplexNumbers,
>width integer references COmplexNumbers
> );
>
> Or if you need a list then something lije:
>
> create table ListHeader
> (
>List integer primary key,
>Name text collate nocase not null unique,
> );
>
> create table ListEntries
> (
>List integer not null references ListHeader,
>member integer not null references ComplexNumber
> );
>
> This is called a Relational Data Model because, well, you relate things to
> each other.
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Drago, William @ CSG -
> > NARDA-MITEQ
> > Sent: Friday, 24 April, 2015 09:38
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Thoughts on storing arrays of complex numbers
> >
> > 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://ww

[sqlite] Thoughts on storing arrays of complex numbers

[Jim Callahan]

>I could have two blob fields in my table. One for the real parts > and one
for the imaginary. (I don't like this.)

Could you be more specific about what is not to like?
A. Does it limit your ability to use SQL?
B. Does it cause problems for the language interface?
C. Is it harder to do data validation?

It is not unusual to use data bases to store pairs of numbers, for example
GIS programs use databases to store latitude and longitude pairs in
separate columns.

So, what specific problems would be created by a REAL column and an
IMAGINARY column?

Jim Callahan
Orlando, FL

On Fri, Apr 24, 2015 at 9:37 AM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> 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
>

[sqlite] Destroy all evidence of a database

[Jim Callahan]

Can you turn off logging and overwrite the database with unencrypted zeros
or nulls;
just before deleting it?

Encrypting the overwrite character(s) would give the encryption attacker a
cleartext -- a bad move right out of the "Imitation Game".

Jim

On Wed, Apr 22, 2015 at 10:34 AM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 3:23pm, John McKown 
> wrote:
>
> > If it is
> > a POSIX compliant, perhaps what you could do is create a "temporary"
> > (mktemp) file of "appropriate" size.
>
> I had never considered that idea.  Thank you very much.  Unfortunately it
> won't work in this situation because the people in control of the system
> would either say "No virtual file systems" or leap at the idea and insist
> that everyone uses virtual encrypted file systems for all data files at all
> times.  I'm not sure which would be worse.
>
>
> On 22 Apr 2015, at 3:14pm, Richard Hipp  wrote:
>
> > Can you add the SQLITE_OPEN_MEMORY option to the sqlite3_open() call,
> > forcing SQLite to keep all content exclusively in memory and never
> > writing to disk?
>
> Sorry, the data can potentially get too big to keep it in memory (even
> virtual memory).  But a memory database would be a great solution if I
> could rely on it being small.  Thank for that too.
>
> I'm guessing that since you didn't point out any persistent SQLite
> temporary file that I'd missed there are no obvious problems with the
> procedure I included in my post.  That's good enough for the SQLite-related
> part of this problem.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Best way to temporarily store data before processing

[Jim Callahan]

?There are too many unknowns to give a definitive answer to your question.
Any solution you chose is going to create some stress somewhere (in your
"Prepared Tables" database, RAM, the file system and so forth.

Just from my own limited experience. It is very easy while hacking to
CREATE TABLE and DELETE FROM in your main database; the price one pays is
that running .VACUUM to clean up the mess becomes time consuming.

RAM would be fastest, but also the riskiest; any memory leak at all in your
programs handling "gigabytes" of data and you run the risk of having to
reboot the system. RAM files would also be at risk from power
interruptions. If you were sure your data would "only" be 100 megabytes
then you would have a substantial safety margin in RAM.

Using RAM from the outset might fall in the category of "premature
optimization". Usually one wants something that is robust and has the
option of leaving an audit trail -- if that solution is too slow then look
at putting parts of it in RAM. One typically needs an option of audit
trails because there is always screwy data out there -- that sooner or
later shows up and break one's system.

A separate database using ATTACH makes sense, but the question would be
whether to have a template database or to create the files. This is
somewhat a matter of personal preference. I found it was not that hard to
.DUMP the template databases and create an "all code" solution. From my
perspective, I found an "all code" solution to be easier to maintain
because I did not have to rely on what may or may not be in (a version of)
a  template database (and if necessary, I could change the template
databases in the same project code I was working on), but your
circumstances and preferences may be different. This also depends on the
expressiveness of your scripting language. I was using Windows batch
scripts which did not loop very well; so I had to use some ingenuity to do
repetitive batch operations. Python is a more expressive language.

My recollection is that SQLite has a "temp" or "tmp" namespace available
for intermediate tables -- it was on my todo list, but I never got around
to exploring that option.

Jim Callahan
Orlando, FL








On Tue, Apr 14, 2015 at 5:40 AM, Jonathan Moules  wrote:

> Hi List,
> I'm wondering if anyone can offer me a "best practice" way of doing this.
>
> I'm doing some log analysis using Python/SQLite. Python parses a log file
> and splits the raw data from each line in the log into one of about 40
> tables in an SQLite database (I'll call them Raw Tables).
>
> Once a log file has been processed like this, I run some SQL which takes
> the data from the Raw Tables and aggregates/processes it into about 10
> different "Prepared Tables" which are read with a bunch of Views. The
> aggregation/processing doesn't take long, and the SQL for it is simple.
>
> I'd like to update the Prepared Tables after each log file is read because
> there are thousands of files and I don't want to have to rely on having GB
> of disk space sitting around for temporary Raw Tables.
>
> Once the Prepared Tables have been created, there's no real need to keep
> the data in the Raw Tables.
>
> The Prepared Tables don't have to be in the same database as the Raw
> Tables. I'm happy to use ATTACH.
>
> So my question:
> What's the best way to do this with the minimum overhead?
>
> Options that have come to mind (probably missed a lot):
> - Some sort of empty template database for the Raw Tables
> which is copied/cloned/overwritten for each file processed.
> - And/Or use "DELETE FROM Raw_Tables" to truncate it after
> each file (there are no indexes).
> - And/Or place it into :memory:.
> - And/Or just CREATE the Raw Tables for each file?
> - And/Or do it within the Prepared Tables database and use
> "DELETE FROM Raw_Tables". (That file you wouldn't want in :memory: of
> course).
>
>
> Thoughts welcome, thanks for your time,
> Jonathan
>
> 
>
> HR Wallingford and its subsidiaries uses faxes and emails for confidential
> and legally privileged business communications. They do not of themselves
> create legal commitments. Disclosure to parties other than addressees
> requires our specific consent. We are not liable for unauthorised
> disclosures nor reliance upon them.
> If you have received this message in error please advise us immediately
> and destroy all copies of it.
>
> HR Wallingford Limited
> Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
> Registered in England No. 02562099
>
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Transpose selected rows into columns

[Jim Callahan]

You are welcome.

The crosstab followed by a calculation is a common pattern in statistics,
political science and accounting (if you added a percent change column
after the diff you would have the classic "accounting variance" report).

Using an intermediate TABLE or VIEW is an "information hiding" tactic
analogous to putting a complex calculation in a subroutine.

Jim Callahan
Orlando, FL



On Wed, Apr 8, 2015 at 1:36 AM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:

> Jim,
>
> This works quite well. Thank you.
>
> And thanks to all others who replied.
>
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Morris
> > Sent: Tuesday, April 07, 2015 10:07 AM
> > To: sqlite-users at mailinglists.sqlite.org
> > Subject: Re: [sqlite] Transpose selected rows into columns
> >
> > You might try
> >
> > select SerialNumber, V0, V5, V5-V0
> > from
> > (select SerialNumber,
> > max(case Stim when 'V0' then Resp else null end) V0,
> > max(case Stim when 'V5' then Resp else null end) V5 from MyTable
> > group by SerialNumber)
> >
> >
> >
> > On 4/7/2015 6:58 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> > > Igor,
> > >
> > > Your solution works well. What I can't figure out is how to
> > efficiently create a column representing V5-V0.
> > >
> > > SerialNumber |  V0   |  V5  | Vdiff
> > > -|---|--|---
> > > 123  |  0.2  |  0.6 |  0.4
> > >
> > >
> > > This is what I'm using, but it takes twice as long:
> > >
> > > select SerialNumber,
> > >
> > > max(case Stim when 'V0' then Resp else null end) V0,
> > > max(case Stim when 'V5' then Resp else null end) V5,
> > >
> > > (max(case Stim when 'V0' then Resp else null end) -
> > > max(case Stim when 'V5' then Resp else null end)) Vdiff
> > >
> > > from MyTable group by SerialNumber;
> > >
> > >
> > > There must be a more efficient way. (I tried V5-V0 and assigning
> > > intermediate values to variables but got nothing but errors.)
> > >
> > > Thanks,
> > > --
> > > Bill Drago
> > > Senior Engineer
> > > L3 Narda-MITEQ
> > > 435 Moreland Road
> > > Hauppauge, NY 11788
> > > 631-272-5947 / William.Drago at L-3COM.com
> > >
> > >
> > >
> > >> -Original Message-
> > >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > >> users-bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> > >> Sent: Friday, March 27, 2015 3:20 PM
> > >> To: sqlite-users at mailinglists.sqlite.org
> > >> Subject: Re: [sqlite] Transpose selected rows into columns
> > >>
> > >> On 3/27/2015 11:48 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> > >>> I want the rows containing V0 and V5 to become columns like this:
> > >>>
> > >>> SerialNumber |  V0   |  V5
> > >>> -|---|---
> > >>> 123  | 0.136 | 0.599
> > >>> 124  | 0.126 | 0.587
> > >>> 125  | 0.119 | 0.602
> > >> select SerialNumber,
> > >> max(case Stim when 'V0' then Resp else null end) V0,
> > >> max(case Stim when 'V5' then Resp else null end) V5 from MyTable
> > >> group by SerialNumber;
> > >>
> > >> --
> > >> Igor Tandetnik
> > >>
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users at mailinglists.sqlite.org
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > > 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 con

[sqlite] Transpose selected rows into columns

[Jim Callahan]

?Why not CREATE an intermediate SQL VIEW or TABLE with V0 and V5 and then
use the resulting VIEW or TABLE as input to a second query that computes
the diff?
Or use R?

First query:
CREATE TABLE newtable
AS SELECT
?
   max(case Stim when 'V0' then Resp else null end) V0,
   max(case Stim when 'V5' then Resp else null end) V5;
or
CREATE VIEW newview AS SELECT... ;

Second query:
SELECT SerialNumber, V5, V0, (V5-V0) AS Vdiff FROM newtable;

Hope this helps.

Alternatively, R has transpose function, crosstabs and a good package for
going back and forth from tables to R dataframes.

Hadley Wickham's RSQLite 1.0.0
http://blog.rstudio.org/2014/10/25/rsqlite-1-0-0/

R and SQLite: Part 1
http://sandymuspratt.blogspot.com/2012/11/r-and-sqlite-part-1.html

Note crosstab is commonly used slang, formal statistical name is
"contingency table".

Converting between data frames and contingency tables
http://www.cookbook-r.com/Manipulating_data/Converting_between_data_frames_and_contingency_tables/

https://stat.ethz.ch/R-manual/R-patched/library/base/html/table.html

Transpose -- t()
http://www.r-statistics.com/tag/transpose/

http://www.statmethods.net/management/reshape.html


Hope this helps,

Jim Callahan
Orlando, FL

On Tue, Apr 7, 2015 at 9:58 AM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:

> Igor,
>
> Your solution works well. What I can't figure out is how to efficiently
> create a column representing V5-V0.
>
> SerialNumber |  V0   |  V5  | Vdiff
> -|---|--|---
> 123  |  0.2  |  0.6 |  0.4
>
>
> This is what I'm using, but it takes twice as long:
>
> select SerialNumber,
>
>
> ??
> max(case Stim when 'V0' then Resp else null end) V0,
>max(case Stim when 'V5' then Resp else null end) V5,
>
>(max(case Stim when 'V0' then Resp else null end) -
>max(case Stim when 'V5' then Resp else null end)) Vdiff
>
> from MyTable group by SerialNumber;
>
>
> There must be a more efficient way. (I tried V5-V0 and assigning
> intermediate values to variables but got nothing but errors.)
>
> Thanks,
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> > users-bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> > Sent: Friday, March 27, 2015 3:20 PM
> > To: sqlite-users at mailinglists.sqlite.org
> > Subject: Re: [sqlite] Transpose selected rows into columns
> >
> > On 3/27/2015 11:48 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> > > I want the rows containing V0 and V5 to become columns like this:
> > >
> > > SerialNumber |  V0   |  V5
> > > -|---|---
> > > 123  | 0.136 | 0.599
> > > 124  | 0.126 | 0.587
> > > 125  | 0.119 | 0.602
> >
> > select SerialNumber,
> >max(case Stim when 'V0' then Resp else null end) V0,
> >max(case Stim when 'V5' then Resp else null end) V5 from MyTable
> > group by SerialNumber;
> >
> > --
> > Igor Tandetnik
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 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
>

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Jim Callahan]

You may or may not find this Adobe Lightroom thread helpful
(it winds up discussing critical sections and thread proliferation
-- which are not a SQLite issues):
https://forums.adobe.com/thread/1229203?tstart=0

Jim Callahan

On Wed, Apr 1, 2015 at 3:21 PM, Simon Slavin  wrote:

> We can only answer your original question with a 'No'.  We're not aware of
> any dramatic SQLite slow-down with any recent Windows update, let alone W7
> specifically.  As Alex wrote, it's probably better if you ask questions on
> a forum about Lightroom.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] FAQ typo

[Jim Callahan]

Not personally familiar with Russian translation issue, but thanks for the
English language spell check should use "You" instead of "Your" in cited
sentences.

Jim Callahan
Orlando, FL

On Mon, Mar 16, 2015 at 5:19 PM, O.Zolotov  wrote:

> Dear All,
> the FAQ's item 21 ( http://www.sqlite.org/faq.html ) has a typo. The
> sentences
>
> " Your can use PRAGMA integrity_check
> Your can use PRAGMA quick_check ..."
>
> are more likely to be
> " You can use PRAGMA integrity_check
> You can use PRAGMA quick_check ..."
>
> PS
> By the way, do I have a right to translate the FAQ into Russian?
>
> Best Regards,
> Oleg V. Zolotov
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] restructuring databases (was Re: Can I copy one column of data to another table?)

[Jim Callahan]

The appropriate structure of the database depends on whether you need the
tables spread out into multiple tables for consistency ("one fact in one
location") -- a process called "normalization or whether you want all the
data in one table for ease of querying ("denormalization").

Transactional systems are usually designed to be normalized; while decision
support tables may be more de-normalized (for ease of querying).

If you want to combine your tables along the primary key, the way to do it
in SQL is to create SELECT ...JOIN queries and then when you have a working
join query that has the struture you want you have a choice of using the
SELECT...JOIN query as if it was table or converting the query to a table
with a CREATE TABLE FROM SELECT ...JOIN query.

Jim Callahan
Orlando, FL

On Sat, Mar 7, 2015 at 8:18 PM, Darren Duncan 
wrote:

> On 2015-03-07 9:59 AM, Simon Slavin wrote:
>
>> On 7 Mar 2015, at 4:42pm, Dave  wrote:
>>
>>  I am fairly new at this although I have wanted to learn and tried again
>>> and again...But I have a problem. I created a database and probably did it
>>> wrong and I am trying to fix it. I made a database with 7 tables in it all
>>> with a primary key and a record ID that matches the primary key. Now when
>>> trying to use the database I see that I should have made 1 table with all
>>> the related data (I think) and am trying to copy one column of data at a
>>> time to the "main" table. Can that be done and if so how?
>>>
>>
>> Without going into your situation in detail, I have a suggestion which
>> may help you approach the problem another way.  The SQLite shell tool has a
>> '.dump' command which turns a database into SQL commands, and a '.read'
>> command which uses the commands to create schema and data in a new database.
>>
>> So dump the database into a text file.  Then you can use editing tools
>> (usually global find-and-replace) mess with the text file so that all the
>> inserting is done to the same table.  Then you can create your new database
>> by reading the altered text file.
>>
>
> Frankly the idea (proposed by Simon here) of solving this by dumping
> everything to a text file and manipulating it there with editing tools
> sounds abysmal to me.
>
> The only time one might consider that reasonable is if the total number of
> records is just a handful and you're essentially just re-entering them from
> scratch.
>
> Once you've already got your data in SQLite, the best general solution by
> far is to use SQL to manipulate it; if you can't, you've already lost.
>
> What you want to do is create new table(s) with the new format you want,
> and then do INSERT INTO  SELECT FROM  such that the SELECT easily
> and reliably does all the hard work of collecting up all the data from the
> old tables and rearranging it into the new format.  Depending on the
> complexity of the task, you may also create temporary tables for
> intermediate stages of the processing.
>
> Solving the problem with the likes of SQL UPDATE is hard, but using SELECT
> is easy.
>
> By a similar token, I believe SQL is often the best place to clean up data
> from external sources.  Create temporary tables that are very lax in format
> and constraints that take the external data as pristine as possible, load
> into those, and then use SELECTs/etc to derive cleaner versions from those
> into the final tables (or other intermediaries), and you can use the SQL
> powers to filter or compensate for dirty data etc.  Especially useful for
> dealing with duplicate data in the source, find or handle with SELECT GROUP
> BY etc rather than trying conditional INSERT logic or what have you.
>
> -- Darren Duncan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] PhD student

[Jim Callahan]

Books that discuss BOTH R and SQL are a very small subset and assume some
knowledge of both.
R INTRODUCTORY BOOKS
1. Peter Dalgaard, "Introductory Statistics with R", 2002.
"The book is based upon a set of notes developed for the course in Basic
Statistics for Health Researchers at the Faculty of Health Sciences of the
University of Copenhagen. This course had as its primary target.. students
for the Ph.D. degree in medicine." Intro page viii.
body mass index (BMI) and age of menarche.
2. Jared Lander, "R for Everyone", 2014.
More modern, but less focused on health and a little more scattershot.

R AUTHORITATIVE REFERENCE
1. Brian Ripley and William Venables, "Modern Applied Statistics with S",
2002.

Anything by John Chambers, Robert Gentleman or Brian Ripley or any member
of the "R Core Development Team" can be considered authoritative (the stuff
you can footnote without frowns) on R.

Also, if you are going to use the R mailing list read all of the PDFs that
come with the base installation of R. Its better now, but the R mailing
list used to have a very strong "RTFM" attitude and did not want to explain
anything that was clearly covered in the manuals. Especially read the "R
Import/Export Manual" PDF.

ADVANCED R (with SQL)
Depends on what you are doing.
If you working with health surveys,
Thomas Lumley's "Complex Surveys" is invaluable  One of Lumley's
examples is the CDC's BRFSS, "The Behavioral Risk Factor Surveillance System
 (BRFSS) is the world's largest, on-going telephone health survey system."
(from CDC website). Which in Lumley's example is:

   - The BRFSS 2007 data as a HUGE (245Mb) SQLite database
   .
   ?"?

1. Thomas Lumley, "Complex Surveys: A Guide to Health Analysis Using R",
http://r-survey.r-forge.r-project.org/svybook/index.html

On the other hand, if you are dealing with biological data such as trying
to match results from GeneChips with existing reference sources you might
prefer Robert Gentleman's "R Programming for Bioinformatics" especially,
Chapter 8 "Data Technologies".

1. Robert Gentleman's "R Programming for Bioinformatics", 2009.
"We begin our discussion by describing a range of tools that have been
implemented in R and that can be used to process and transform data. Next
we discuss the different interfaces to databases that are available, but
focus our discussion on SQLite as it is used extensively within the
Bioconductor Project." page 229
The databases discussion resumes on page 238, Section 8.4, discusses SQLite
on page 241 including  a specific example:
"In the code below we load the SQLite package, initialize a driver and open
a dataase that has been supplied with the RBionf [R] package that
accompanies this volume. The database contains a number of tables that map
between identifers on the Affymetrix HG-U95v2 GeneChip and different
quantities of interest such as GO categories or PubMed IDs (that map
published papers that discuss the corresponding genes). We then list the
tables in that database."

Sometimes we get tired of reading dry tomes and we prefer something more
chatty and amusing.

For R and other tools I enjoy reading:

Cathy O'Neil's and Rachel Schutt's "Doing Data Science: Straight Talk from
the Frontline", 2013. It's an O'Reilly book.

For SQLite, I enjoy
Michael Owen's, "The Definitive Guide to SQLite", 2006. -- maybe not the
whole book, but the Chapter 4 example page 75 "Foods mentioned in episodes
of the Seinfield sitcom" is a hoot (and turned out to help me solve an real
world problem).

If you are doing anything beyond Stats 101 classical statistics it helps to
understand the Bayesian bogeyman.

A fascinating, non-technical, historical account is provided by Sharon
Bertsch McGrayne, in her book "The Theory that would not Die...".

BAYESIAN STATISTICS (HISTORY)
Sharon Bertsch McGrayne,
"The Theory That Would Not Die
How Bayes' Rule Cracked the Enigma Code, Hunted Down Russian Submarines,
and Emerged Triumphant from Two Centuries of Controversy"
?, 2011.
http://yalepress.yale.edu/book.asp?isbn=9780300169690

"For the student who is being exposed to Bayesian statistics for the first
time, McGrayne?s book provides a wealth of illustrations to whet his or her
appetite for more. It will broaden and deepen the field of reference of the
more experienced statistician, and the general reader will find an
understandable, well-written, and fascinating account of a scientific field
of great importance today. "
http://www.ams.org/notices/201205/rtx120500657p.pdf
All the more timely with the release of the movie "The Imitation Game",
because Turing & Co. cracked the German Enigma code using Bayesian
statistics.?
There few specific "Bayesian" packages in R (an interface to BUGS); but it
lurks in the background of many of them  -- any use of the word "prior".

Hope this helps.
Jim

On Wed, Feb 25, 2015 at 11:28 AM, VASILEIOU Eleftheria  wrote:

>  Hi,
>
> I would need to use R for my analysis for my 

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

This might give an impression of the scale of what the BioConductor people
are doing.

"The Gene Expression Omnibus (GEO) at the National Center for Biotechnology
Information (NCBI) is the largest fully public repository [as of 2005] for
high-throughput molecular abundance data, primarily gene expression data."
http://www.ncbi.nlm.nih.gov/pubmed/15608262

"The NCBI Gene Expression Omnibus (GEO) represents the largest public
repository of microarray data. However, finding data in GEO can be
challenging. We have developed GEOmetadb in an attempt to make querying the
GEO metadata both easier and more powerful. All GEO metadata records as
well as the relationships between them are parsed and stored in a local
MySQL database. ... In addition, a Bioconductor package, GEOmetadb that
utilizes a SQLite export of the entire GEOmetadb database is also
available, rendering the entire GEO database accessible with full power of
SQL-based queries from within R."
http://www.ncbi.nlm.nih.gov/pubmed/18842599

Annotation Database Interface

Bioconductor version: Release (3.0)

Provides user interface and database connection code for annotation data
packages using SQLite data storage.

Author: Herve Pages, Marc Carlson, Seth Falcon, Nianhua Li

Maintainer: Bioconductor Package Maintainer 

Citation (from within R, enter citation("AnnotationDbi")):

Pages H, Carlson M, Falcon S and Li N. *AnnotationDbi: Annotation Database
Interface*. R package version 1.28.1.

http://master.bioconductor.org/packages/release/bioc/html/AnnotationDbi.html

To really understand the enormity of what they attempting, you need a
picture like the one "Figure 1: Annotation Packages: the big picture" on
the first page of this document:
http://master.bioconductor.org/packages/release/bioc/vignettes/AnnotationDbi/inst/doc/IntroToAnnotationPackages.pdf

Just to grasp the scale and complexity of what they are doing; one of the
databases mentioned GO.db stores a gigantic directed acyclic graph (DAG).

"GOBPANCESTOR Annotation of GO Identifiers to their Biological Process
Ancestors Description This data set describes associations between GO
Biological Process (BP) terms and their ancestor BP terms, based on the
directed acyclic graph (DAG) defined by the Gene Ontology Consortium. The
format is an R object mapping the GO BP terms to all ancestor terms, where
an ancestor term is a more general GO term that precedes the given GO term
in the DAG (in other words, the parents, and all their parents, etc.)."

I get the idea that they are storing a DAG in a SQLite database for use in
R, explaining "associations between GO Biological Process (BP) terms and
their ancestor BP terms, based on the directed acyclic graph (DAG) defined
by the Gene Ontology Consortium."

DAG, SQLite, R, Biological Processes and Gene Ontology in one paragraph;
oh, my head hurts, I think I'll stick to simpler stuff.

Jim





On Wed, Feb 25, 2015 at 3:13 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> I first learned about SQLite in the Bioconductor branch of R. I figured if
> they could handle massive genetic databases in SQLite, SQLite ought to be
> able to handle a million (or even 12 million) voters in a voter file.
>
> Here is a brief article from 2006, "How to Use SQLite with R" by Seth
> Falcon.
>
> http://master.bioconductor.org/help/course-materials/2006/rforbioinformatics/labs/thurs/SQLite-R-howto.pdf
> Jim
>
> On Thu, Feb 19, 2015 at 2:08 PM, Jim Callahan <
> jim.callahan.orlando at gmail.com> wrote:
>
>> Strongly agree with using the R package Sqldf.
>> I used both RSQLite and Sqldf, both worked extremely well (and I am both
>> a lazy and picky end user). Sqldf had the advantage that it took you all
>> the way to your destination the workhorse R object the data frame (R can
>> define new objects, but the data frame as an in memory table is the
>> default).
>> The SQLITE3 command line interface and the R command line had a nice
>> synergy; SQL was great for getting a subset of rows and columns or building
>> a complex view from multiple tables. Both RSqlite and Sqldf could
>> understand the query/view as a table and all looping in both SQL and R took
>> place behind the scenes in compiled code.
>>
>> Smart phone users say "there is an app for that". R users would say
>> "there is a package for that" and CRAN is the equivalent of the Apple app
>> store or Google Play.
>>
>> R has packages for graphics, classical statistics, Bayesian statistics
>> and machine learning. R also has packages for spacial statistics (including
>> reading ESRI shapefiles), for graph theory and for building decision trees.
>> There is another whole app store for biological applications "bioconductor".
>>
>> T

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

I first learned about SQLite in the Bioconductor branch of R. I figured if
they could handle massive genetic databases in SQLite, SQLite ought to be
able to handle a million (or even 12 million) voters in a voter file.

Here is a brief article from 2006, "How to Use SQLite with R" by Seth
Falcon.
http://master.bioconductor.org/help/course-materials/2006/rforbioinformatics/labs/thurs/SQLite-R-howto.pdf
Jim

On Thu, Feb 19, 2015 at 2:08 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> Strongly agree with using the R package Sqldf.
> I used both RSQLite and Sqldf, both worked extremely well (and I am both a
> lazy and picky end user). Sqldf had the advantage that it took you all the
> way to your destination the workhorse R object the data frame (R can define
> new objects, but the data frame as an in memory table is the default).
> The SQLITE3 command line interface and the R command line had a nice
> synergy; SQL was great for getting a subset of rows and columns or building
> a complex view from multiple tables. Both RSqlite and Sqldf could
> understand the query/view as a table and all looping in both SQL and R took
> place behind the scenes in compiled code.
>
> Smart phone users say "there is an app for that". R users would say "there
> is a package for that" and CRAN is the equivalent of the Apple app store or
> Google Play.
>
> R has packages for graphics, classical statistics, Bayesian statistics and
> machine learning. R also has packages for spacial statistics (including
> reading ESRI shapefiles), for graph theory and for building decision trees.
> There is another whole app store for biological applications "bioconductor".
>
> The CRAN website has "views" (pages or blogs) showing how packages solve
> common problems in a variety of academic disciplines or application areas.
>
> Jim Callahan
>  On Feb 19, 2015 11:38 AM, "Gabor Grothendieck" 
> wrote:
>
>> On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp  wrote:
>> > On 2/18/15, Jim Callahan  wrote:
>> >> I would mention the open source statistical language R in the "data
>> >> analysis" section.
>> >
>> > I've heard of R but never tried to use it myself.  Is an SQLite
>> > interface built into R, sure enough?  Or is that something that has to
>> > be added in separately?
>> >
>>
>> RSQLite is an add-on package to R; however, for data analysis (as
>> opposed to specific database manipulation) I would think most R users
>> would use my sqldf R add-on package (which uses RSQLite by default and
>> also can use driver packages of certain other databases) rather than
>> RSQLite directly if they were going to use SQL for that.
>>
>> In R a data.frame is like an SQL table but in memory and sqldf lets
>> you apply SQL statements to them as if they were all one big SQLite
>> database.  A common misconception is it must be slow but in fact its
>> sufficiently fast that some people use it to get a speed advantage
>> over plain R.  Others use it to learn SQL or to ease the transition to
>> R and others use it allow them to manipulate R data frames without
>> knowing much about R provided they know SQL.
>>
>> If you have not tried R this takes you through installing R and
>> running sqldf in about 5 minutes:
>> https://sqldf.googlecode.com/#For_Those_New_to_R
>>
>> The rest of that page gives many other examples.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

Strongly agree with using the R package Sqldf.
I used both RSQLite and Sqldf, both worked extremely well (and I am both a
lazy and picky end user). Sqldf had the advantage that it took you all the
way to your destination the workhorse R object the data frame (R can define
new objects, but the data frame as an in memory table is the default).
The SQLITE3 command line interface and the R command line had a nice
synergy; SQL was great for getting a subset of rows and columns or building
a complex view from multiple tables. Both RSqlite and Sqldf could
understand the query/view as a table and all looping in both SQL and R took
place behind the scenes in compiled code.

Smart phone users say "there is an app for that". R users would say "there
is a package for that" and CRAN is the equivalent of the Apple app store or
Google Play.

R has packages for graphics, classical statistics, Bayesian statistics and
machine learning. R also has packages for spacial statistics (including
reading ESRI shapefiles), for graph theory and for building decision trees.
There is another whole app store for biological applications "bioconductor".

The CRAN website has "views" (pages or blogs) showing how packages solve
common problems in a variety of academic disciplines or application areas.

Jim Callahan
 On Feb 19, 2015 11:38 AM, "Gabor Grothendieck" 
wrote:

> On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp  wrote:
> > On 2/18/15, Jim Callahan  wrote:
> >> I would mention the open source statistical language R in the "data
> >> analysis" section.
> >
> > I've heard of R but never tried to use it myself.  Is an SQLite
> > interface built into R, sure enough?  Or is that something that has to
> > be added in separately?
> >
>
> RSQLite is an add-on package to R; however, for data analysis (as
> opposed to specific database manipulation) I would think most R users
> would use my sqldf R add-on package (which uses RSQLite by default and
> also can use driver packages of certain other databases) rather than
> RSQLite directly if they were going to use SQL for that.
>
> In R a data.frame is like an SQL table but in memory and sqldf lets
> you apply SQL statements to them as if they were all one big SQLite
> database.  A common misconception is it must be slow but in fact its
> sufficiently fast that some people use it to get a speed advantage
> over plain R.  Others use it to learn SQL or to ease the transition to
> R and others use it allow them to manipulate R data frames without
> knowing much about R provided they know SQL.
>
> If you have not tried R this takes you through installing R and
> running sqldf in about 5 minutes:
> https://sqldf.googlecode.com/#For_Those_New_to_R
>
> The rest of that page gives many other examples.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

I would mention the open source statistical language R in the "data
analysis" section. The interface in the RSqlite package is much better and
faster than any of the Python interfaces in that the interface fully
understands queries as tables and that the looping for the return of rows
is done in compiled code rather than at an interpreted command line.
On Feb 18, 2015 9:34 AM, "Richard Hipp"  wrote:

> In a feeble effort to do "marketing", I have revised the "Appropriate
> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> Things" and "Edge of the Network" above the break.  See:
>
> https://www.sqlite.org/whentouse.html
>
> Please be my "focus group", and provide feedback, comments,
> suggestions, and/or criticism about the revised document.   Send your
> remarks back to this mailing list, or directly to me at the email in
> the signature.
>
> Thank you for your help.
>
> --
> 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
>

Re: [sqlite] Feature Request - RowCount

[Jim Callahan]

So, if I understand the discussion the fastest way to get a count from the
command line interface (CLI) is to count the rows in the primary key,
assuming you have a primary key and that it is not a composite key.

SELECT COUNT(primarykey) FROM table1

The "primarykey" in the above example is a stand in  for the actual name of
the field designated as the primary key.

I am also relying on the answers to FAQ #1 and #26.

#26 The unique columns have non-null values (the answer says a lot more,
but that is the essence of what I am relying on).

#1 If you have an integer primary key (which by definition in SQLITE3
autoincrements) one might be able to get an approximate row count faster
using the:

sqlite3_last_insert_rowid()

function.

Jim Callahan
Orlando, FL

On Dec 13, 2014 10:17 AM, "Simon Slavin" <slav...@bigfraud.org> wrote:

>
> On 13 Dec 2014, at 12:38pm, Richard Hipp <d...@sqlite.org> wrote:
>
> > Also, if there are indices available, SQLite attempts to count the
> smallest
> > index (it has to guess at which is the smallest by looking at the number
> > and declared datatypes of the columns) and counting the smallest index
> > instead, under the theory that a smaller index will involve less I/O.
>
> Would it not be faster to just count the number of pages each index takes
> up ?  Uh ... no.
> Wow.  You really don't like storing counts or sizes, do you ?
>
> > To do better than this requires, as far as I know, an incompatible file
> > format change and/or a performance hit for applications that do not use
> the
> > feature.
>
> Can you tell us whether the problem exists in SQLite4 ?  I know it uses a
> different format for indexes.  I tried checking the documentation but
> didn't see an answer that didn't involve more work than I felt like doing.
>
> 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] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[Jim Callahan]

The question is ambiguous does he want all the Fred Smiths or just a
specific Fred Smith?

For example, do we have a case of mistaken identity? How many Fred Smiths
are out there?

Or does he want All the records for a specific person named Fred Smith?

If it is a specific person, is there a (unique) ID number for the person (a
primary key)? If there is an ID number you can query for that specific
number.

On the other hand if they want all the "Fred Smiths"; do they want
Frederick Smith or Fredrica Smith or some named Theodore Fredrick Smith,
but goes by Fred?

Hope this helps,

Jim Callahan
 On Dec 7, 2014 9:24 PM, "Dwight Harvey" <
dharv...@students.northweststate.edu> wrote:

> I am a student with no Tech or IT background what so ever.
>
> I am new to Databases and IT in general.
>
> I am taking an accelerated class in database basics and within the last
> three weeks I just learned what databases were.
>
> I know very little and Databases are complex and intimidating.
>
> I figured out how to run queries but I don't know if they are
> correct/accurate, as in what I requested from the 'RUN' results?
>
> How do you 'VERIFY' your query results?
>
>
> My instructor wants me to explain how do I KNOW that the records are
> accurate. Here is an example of what is expected in the assignment...
>
>  *VERIFICATION:  *What is verification?  Each time you retrieve data, you
> should ask yourself, "How do I know I selected the correct data?".   For
> example, if you were asked to pull all records written by an author named
> Fred Smith, your query might be based on last name equal to Smith.
> However, if you might get records for someone with the first name of Fred,
> Mary and Ginger.   What would you do to insure you are pulling only Fred?
> The person who has requested the data will always want assurance from you
> that you are 100% positive you pulled the correct records.  Look at the
> records returned and always as yourself, did I pull the correct records?
> How would I verify it?
>
> "Capture each query, number of records returned and *explain your
> validation of the query.*" Example:
>
> /** First query 1. List all employees **/
> SELECT dbo.Firstname, dbo.Lastname
> FROM dbo.employees
> --records returned: 24
> *--Validation:  I did a quick listing of top 200 records and 4 were
> returned*.
> ___
> 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] GUI INfo

[Jim Callahan]

>From a MS Access point of view, think of SQLite as your backend, similar to
a desktop version of MS SQL Server.

What language you use really depends on what you are trying to do. If all
you want is forms and reports, you don't have to give up  MS Access, just
use an ODBC driver for SQLite (Google for it), set up an ODBC data source
in MS Windows and connect to it as an external database from MS Access.

If the idea is to set up an application completely outside of MS Access and
you need a reporter writer  -- do you want a commercial report writer like
Crystal Reports or do you want an open source report writer?  Open source
report writer projects tend to be associated with larger business
intelligence (bi)/data warehousing projects such as Talend or Pentaho. The
open source bi projects (Talend and Pentaho) are mostly written in Java.

On the other hand if you are developing phone apps, SQLite is probably
already there in Android, ios or mono.

If you are developing scientific applications you might use the R
statistical language, Python or the new language Julia.

It really depends on what you are tying to do.

Jim Callahan


On Wed, Sep 10, 2014 at 4:56 PM, Maxine Nietz <nevada...@sbcglobal.net>
wrote:

> I am an experienced Access VBA programmer. I know about the SQLite commands
> to create and manipulate data in tables and queries.
>
>
>
> What I want to know is where do I find info on creating a graphical user
> interface such as menus, forms and reports. What additional programs are
> required to do this?
>
>
>
> Thanks in advance,
>
> Max
>
>
>
> ___
> 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] Improve query performance using a join

[Jim Callahan]

A few observations
1. My condolences with those dimensions you are heading for "big
data"/hadoop land.

2. Worry about the number of rows; that's what feeds into the big-oh: O(n).
Assuming your 150 columns translate into a 1.5k to 2k record length that
means your 300 gigabyte file must have 150 (300 gig/2k) to 200 (300
gig/1.5k) million records. That's a lot of n for O(n).

3. SSDs are faster than spinning disks because they have potentially zero
track to track seek times. On the other hand, once the read-write head is
above the correct disk sector disks have very highly evolved I/O for
consecutive sectors (not fragmented). By contrast, the controllers that
provide the interface to SSDs are not nearly as evolved so SSDs may still
have lower bandwidth (for example a "Class 10" SD card designed for HD
Video still (unless otherwise marked) only has 10 Mbyte per second speed).
http://en.wikipedia.org/wiki/Secure_Digital

However, a device speed of 10 Mbyte per second would put a SCSI device near
the bottom of the speed hierarchy.
http://en.wikipedia.org/wiki/SCSI

Serial ATA (SATA) is an even faster interface.
http://en.wikipedia.org/wiki/Serial_ATA

So, I would recommend double checking the specs of your SSD and not
necessarily making the reasonable, but not yet true, assumption that
solid-state has to be faster than mechanical.

One strategy that might work is to have an entirely separate (not joined) 8
column table; develop queries (targets) on that database and then write out
the primary key of the rows you are interested in to a separate table
(CREATE TABLE AS SELECT primarykey FROM 8columntable WHERE yourquery;) and
then JOIN the row reduced table to the main table. If your rowreduced table
has millions of fewer records (primary keys) that's millions of rows (in
the main 150 column table) where the precompiled SQL query doesn't have to
be executed.

HTH,

Jim Callahan
Orlando, FL














On Wed, Aug 6, 2014 at 11:51 PM, Paul Dillon <paul.dil...@gmail.com> wrote:

> Hello,
>
> I would like to know if splitting a big table into two smaller ones, and
> then using a join in my queries would speed up performance.
>
> My table is 100-300GB in size and has about 150 columns.  There are 8
> fields that I frequently use in my queries, which require full table scans
> to complete.  I usually query each field once per data load, so the time to
> index them is not worth it.  (FYI I am data mining).
>
> So my questions are:
>
> 1. Will moving these 8 query fields to a smaller table improve query
> performance when joined to the larger table?  My logic is that this small
> table would only be about 5% the size of the full table, so the full table
> scan to service the query might be faster.
>
> 2. Would it be worthwhile having that smaller table in a separate .db file,
> so that the data is closer together on my SSD drive?
>
> 3. Would the data loading performance be heavily impacted if I had to
> insert into two tables instead of one?  I use "INSERT OR REPLACE" for my
> loading, with a single index.
>
> Many Thanks,
>
> Paul
> ___
> 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] In-memory DB slower than disk-based?

[Jim Callahan]

What is the system memory utilization?
If the in-memory database overloads the real memory
the frantic os virtual memory paging (thrashing) could slow
everything down.

>From your use scenario it sounds like your database shouldn't be that large,
but how large is the database? and how large is the system ram?
What percent of the ram is being used?

Jim Callahan


On Wed, Aug 6, 2014 at 3:39 PM, Fabian Giesen <fabi...@radgametools.com>
wrote:

> On 8/6/2014 11:51 AM, Jensen, Vern wrote:
>
>> *bump*
>>
>> Anyone?
>>
>
> Not sure if this is your problem, but this kind of load puts a lot of
> stress on SQLite's page cache, which might well perform worse than your
> OS'es page cache (used for "real" disk IO) does, especially under high
> contention.
>
> Also, SQLite's defaults wrt. threading are set up to be safe, which means
> that when in doubt about what your code might be doing, SQLite will
> serialize.
>
> I did a bit of SQLite perf tuning recently and here's two things you might
> want to try:
>
> 1. What's your value for "flags"? SQLite by default (pessimistically)
> assumes that you might be sharing a DB connection between multiple threads,
> and thus wraps essentially all API functions in locks to make sure this is
> safe. In a heavily multi-threaded scenario, I would recommend that you
> manually ensure that each connection is only used by one thread at a time,
> and then add SQLITE_OPEN_NOMUTEX to your flags. This reduces lock traffic
> substantially. (If your connection was per-thread already, doing so will
> not reduce contention or have any impact on your asymptotic perf or
> scalability, but locks aren't free.)
>
> 2. Before sqlite3_initialize, try
>
>   "sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);"
>
> This disables some API functions that provide access to memory stats, but
> avoids a global lock on every memory allocation/free, and SQLite does a lot
> of them. If you don't need these memory statistics, turning this off can
> make a noticeable difference in scalability.
>
> I'm not sure if this will affect page cache throughput specifically, but
> these two things are both fairly easy to try.
>
> -Fabian
>
> ___
> 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] Views as Virtual Tables -- Command line vs. Called Interface

[Jim Callahan]

Roger,
Sorry for unintended slights.
My haste and terseness may have confused matters.

Another long story (below), but if you are in a hurry, my question is:

TERSE QUESTION
Is the sqlite3_table_column_metadata() SQLite C API function also wrapped
by the APSW Python Library?
http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata

Or is there another way to get the primary key without scraping the SQL
(string) field in the SQLite3 master table?

LONG STORY
I like Jessica Hamrick's Python dbtools library for  the final conversion
to the Python pandas library data structures. I have posted the necessary
changes to her blog:

I like it, but I need to import a VIEW from SQLite to Pandas (I want to
shield my users from scary SQL JOINS and just present them one flat table).
Underlying Python/SQLite libraries support SQL VIEWS (see SQLite mailing
list "Views as Virtual Tables -- Command line vs. Called Interface". So, in
your code, need to change cmd = "SELECT name FROM sqlite_master WHERE
type='table'"

to
cmd = "SELECT name FROM sqlite_master WHERE type IN ('table','view')"

SQLite VIEWs are read only (not update-able)
so also need error msg-s if type='view' in insert, update and delete.
I would do myself, but I just downloaded Git yesterday and am not yet
familiar with version control."
http://www.jesshamrick.com/2013/04/13/on-collecting-data/

I also noticed that Jessica Hamrick's dbtools was scraping the SQL (string)
column in the SQLite master table with regular expressions to obtain the
column names, column types and the primary key (to use in creating pandas
object). I pointed out that the Python APSW library has wrapped the SQLite3
C API functions (sqlite3_column_name & sqlite3_column_decltype) for the
column name and column type. But, I don't see how to get the primary key in
APSW. Is the sqlite3_table_column_metadata() SQLite C API function
http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata
also wrapped by the APSW Python Library? I posted:

Also, you don't have to scrape the colnames and types with regular
expressions; there is an api for that.
In the APSW SQLite Python library, there is a cursor.getdescription()
method that:
"returns information about each column is a tuple of (column_name,
declared_column_type). The type is what was declared in the CREATE TABLE
statement - the value returned in the row will be whatever type you put in
for that row and column. (This is known as manifest typing which is also
the way that Python works. The variable a could contain an integer, and
then you could put a string in it. Other static languages such as C or
other SQL databases only let you put one type in - eg a could only contain
an integer or a string, but never both.)" The APW calls the SQLite C API
functions:
sqlite3_column_name
sqlite3_column_decltype

So, [with APSW] you [would be] are relying on SQLite3's native parsing and
not someone else's random regex or homebrew parser.
http://rogerbinns.github.io/apsw/cursor.html#cursor-class

BTW, open source, Notepad++, has nice syntax highlighting for Python.

I have an afternoon and evening full of meetings, but I will attempt this
fix myself tomorrow (Tuesday) morning.

Cheers.

Jim Callahan
Orlando, FL






On Sun, Aug 3, 2014 at 11:31 AM, Roger Binns <rog...@rogerbinns.com> wrote:

> Disclosure:  I am the apsw author
>
>
> On 08/02/2014 10:19 AM, Jim Callahan wrote:
>
>> I got apsw to work, but it had a curious side-effect
>> -- it clobbered my IPython prompt (replaced prompt with smiley faces).
>>
>
> APSW certainly didn't do that.  It doesn't do anything - you have to make
> calls and get responses.
>
> If you use the APSW shell then it will use ANSI escape sequences to colour
> the output.  However this is only done if the output is a terminal, and can
> be turned off.  (That is the case for Linux & Mac. For Windows you also
> need to install colorama.)
>
>
>  For those who are interested.
>> 1. downloaded apsw -- does not work with Python's package manager pip
>> http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
>>
>
> APSW moved from googlecode a while back.  It is at:
>
>   https://github.com/rogerbinns/apsw
>
> This explains why:
>
>   http://www.rogerbinns.com/blog/moving-to-github.html
>
> APSW is actually now on pypi.  Someone else put it up there and it has no
> connection to me.  It is also extremely unlikely to install because it
> doesn't handle the SQLite dependency, nor have Windows binaries.
>
>
>  3. commented out "import apswrow" from suggested script (not found, not
>> needed)
>>
>
> That has no connection to APSW either.  It is written by someone else to
> turn rows returned from a tuple into also having the column names.
>
> Roger
>
> __

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

[Jim Callahan]

Keith,
I got apsw to work, but it had a curious side-effect
-- it clobbered my IPython prompt (replaced prompt with smiley faces).

For those who are interested.
1. downloaded apsw -- does not work with Python's package manager pip
http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
2. Ran downloaded installation program (forgot to close IPython window)
3. commented out "import apswrow" from suggested script (not found, not
needed)
4. changed file name to my file "VotersLAF.db"

# have to download apsw directly without using Python's pip
# http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
# downloaded Windows Python 2.7 64 bit version
# apsw-3.8.5-r1.win-amd64-py2.7.exe

import apsw
# import apswrow (apswrow not found and not needed)
cn=apsw.Connection('VotersLAF.db')
for row in cn.cursor().execute('select * from activevoters limit 3'): print
row

It worked, it read the correct data from the SQLite3 SQL VIEW. Thanks!

Jim Callahan
Orlando, FL


On Sat, Aug 2, 2014 at 5:35 AM, Jim Callahan <jim.callahan.orla...@gmail.com
> wrote:

> THANK YOU!!!
>
> On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
>> Works just fine.  The SQL adaption layer in your chosen Python -> SQLite
>> interface must be doing something wacky.
>
>
> Thank you for demonstrating that alternative packages do work.
>
> apsw looks good and a search of GitHub suggests that the dbtools package
> may implement the simple SQLite bridge to pandas that I need.
> "This module handles simple interfacing with a SQLite database. Inspired
> by ipython-sql <https://pypi.python.org/pypi/ipython-sql>, dbtools
> returns pandas DataFrame
> <http://pandas.pydata.org/pandas-docs/stable/dsintro.html#dataframe> objects
> from SELECT queries, and can handle basic forms of other SQL statements (
> CREATE, INSERT, UPDATE, DELETE, and DROP).
> The goal is *not* to replicate the full functionality of SQLAlchemy
> <http://www.sqlalchemy.org/> or really to be used for object-relational
> mapping at all. This is meant to be used more for scientific data
> collection (e.g., behavioral experiments) as convenient access to a robust
> form of storage."
> https://github.com/jhamrick/dbtools
>
> I will test both apsw and dbtools after a breakfast meeting this morning.
>
> Background for group -- my short range goal is to fix page 175 of O'Reilly
> book "Python for Data Analysis by Wes McKinney. I have already contributed
> to errata using (a now dubious) SQLAlchemy solution. Both apsw and dbtools
> have potential to be much better solutions.
>
> This fix to the SQLite to Python/Pandas bridge is crucial for my next
> short term goal of developing simple code examples for this SQLite dataset
> for creating crosstabs and thematic maps in both Python and R. This in turn
> is part of a longer term project to implement the statistical methods
> vaguely described in Sasha Issenberg's book, "The Victory Lab" and any new
> statistical applications that may be used in the future.
>
> I greatly appreciate the help of this group at this critical juncture. I
> was about to abandon the entire Python branch of the project.
>
> Jim Callahan
> Orlando, FL
>
>
>
>
> On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
>>
>> >When I try to query a view (created at the SQLite command line) from
>> >IPython (I will provide specifics, but I want to ask a more general
>> >question first); Python complains about one of the joins inside the view.
>> >So, the called language interface is not passing to Python the view as a
>> >virtual table/resultset, but instead Python is parsing the view and and
>> >trying (and failing) to execute it.
>>
>> >If necessary, I can send the whole Lafayette County, FL database (public
>> >record extract) via private email. Lafayette County is one of the
>> smallest
>> >counties in Florida with only 4,556 voters which makes it ideal for
>> >developing convoluted SQL before moving the SQL to the big counties like
>> >Orange, Broward or Miami-Dade.
>>
>> >Unfortunately, the Anaconda Python environment is a 250 megabyte
>> >(compressed) download.
>>
>> >I am trying to understand enough so that I can write an intelligent
>> >question to the Python/SQLAlchemy/SQLite list.
>>
>> >Why does Python get to see the innards of a View; when the query just
>> >involves columns (in a view) that flow straight through from the base
>> table
>> >(as opposed to being joined from some other table)?
>>
>> > Why does Python get to see the innards o

Re: [sqlite] Handling Timezones

[Jim Callahan]

I assume you know about the timezone (tz) database maintained by the
Internet Assigned Numbers Authority (IANA) -- it is sometimes referred as
the Eggert/Olson database -- after its code and data maintainers.

http://www.iana.org/time-zones

"The tz database is published as a set of text files
<http://en.wikipedia.org/wiki/Text_file> which list the rules and zone
transitions in a human-readable format. For use, these text files are
compiled <http://en.wikipedia.org/wiki/Compiler> into a set of
platform-independent binary files
<http://en.wikipedia.org/wiki/Binary_file>—one
per time zone. The reference source code includes such a compiler called
*zic* (zone information compiler), as well as code to read those files and
use them in standard APIs
<http://en.wikipedia.org/wiki/Application_programming_interface> such as
localtime() and mktime()....The Olson timezone IDs are also used by the
Unicode Common Locale Data Repository
<http://en.wikipedia.org/wiki/Common_Locale_Data_Repository> (CLDR)
and International
Components for Unicode
<http://en.wikipedia.org/wiki/International_Components_for_Unicode> (ICU).
For example, the CLDR Windows–Tzid table maps Microsoft Windows time zone
IDs to the standard Olson names."
http://en.wikipedia.org/wiki/Tz_database

I assume the tz database could be imported into SQLite assuming that's not
what they are using already.

As long as time and date strings have leading zeros ("01") and not just
("1") it is easy to use SQLite string functions to pull them apart and use
in calculations, but using this method you have to keep track of the
modulus (2 AM - 5 hours is?) yourself.

I learned about the "Olson database" from Wes McKinney's book "Python for
Data Analysis." CAUTION: check the book's errata website
http://oreil.ly/python_for_data_analysis
before following any instructions in the book -- the field is changing very
rapidly and in just over a year -- several key instructions (including
which Python distribution to use) are already out of date. Wes has two
sections on time zones:
pages 18-26 where he downloads a usgov_bitly dataset and analyzes the time
zone
and pages 303-306 where he analyzes date stamps and mentions the ptz AND
pandas Python libraries and the "Olson database"

Jim Callahan
Orlando, FL







On Tue, Jul 29, 2014 at 9:41 AM, Will Fong <w...@digitaldev.com> wrote:

> Hi,
>
> How are timezones best handled? Since dates are stored in GMT, when I
> go to display them, I need to add/subtract the timezone. That's not
> too hard when I can just store the timezone as "-5" for EST. When I'm
> providing a date to query on, I would have to apply the reverse of the
> timezone, "+5", to normalize it to GMT.
>
> That kinda sucks :(
>
> I come from PostgreSQL, so I normally set at the connection level the
> timezone and PG handles all the conversions. Does SQLite have a
> similar feature?
>
> Is there a standard way to handle this?
>
> Thanks,
> -will
> ___
> 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] Views as Virtual Tables -- Command line vs. Called Interface

[Jim Callahan]

THANK YOU!!!

On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

> Works just fine.  The SQL adaption layer in your chosen Python -> SQLite
> interface must be doing something wacky.


Thank you for demonstrating that alternative packages do work.

apsw looks good and a search of GitHub suggests that the dbtools package
may implement the simple SQLite bridge to pandas that I need.
"This module handles simple interfacing with a SQLite database. Inspired by
ipython-sql <https://pypi.python.org/pypi/ipython-sql>, dbtools returns pandas
DataFrame
<http://pandas.pydata.org/pandas-docs/stable/dsintro.html#dataframe> objects
from SELECT queries, and can handle basic forms of other SQL statements (
CREATE, INSERT, UPDATE, DELETE, and DROP).
The goal is *not* to replicate the full functionality of SQLAlchemy
<http://www.sqlalchemy.org/> or really to be used for object-relational
mapping at all. This is meant to be used more for scientific data
collection (e.g., behavioral experiments) as convenient access to a robust
form of storage."
https://github.com/jhamrick/dbtools

I will test both apsw and dbtools after a breakfast meeting this morning.

Background for group -- my short range goal is to fix page 175 of O'Reilly
book "Python for Data Analysis by Wes McKinney. I have already contributed
to errata using (a now dubious) SQLAlchemy solution. Both apsw and dbtools
have potential to be much better solutions.

This fix to the SQLite to Python/Pandas bridge is crucial for my next short
term goal of developing simple code examples for this SQLite dataset for
creating crosstabs and thematic maps in both Python and R. This in turn is
part of a longer term project to implement the statistical methods vaguely
described in Sasha Issenberg's book, "The Victory Lab" and any new
statistical applications that may be used in the future.

I greatly appreciate the help of this group at this critical juncture. I
was about to abandon the entire Python branch of the project.

Jim Callahan
Orlando, FL



On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> >When I try to query a view (created at the SQLite command line) from
> >IPython (I will provide specifics, but I want to ask a more general
> >question first); Python complains about one of the joins inside the view.
> >So, the called language interface is not passing to Python the view as a
> >virtual table/resultset, but instead Python is parsing the view and and
> >trying (and failing) to execute it.
>
> >If necessary, I can send the whole Lafayette County, FL database (public
> >record extract) via private email. Lafayette County is one of the smallest
> >counties in Florida with only 4,556 voters which makes it ideal for
> >developing convoluted SQL before moving the SQL to the big counties like
> >Orange, Broward or Miami-Dade.
>
> >Unfortunately, the Anaconda Python environment is a 250 megabyte
> >(compressed) download.
>
> >I am trying to understand enough so that I can write an intelligent
> >question to the Python/SQLAlchemy/SQLite list.
>
> >Why does Python get to see the innards of a View; when the query just
> >involves columns (in a view) that flow straight through from the base
> table
> >(as opposed to being joined from some other table)?
>
> > Why does Python get to see the innards of a View; when the query just
> > involves columns (in a view) that flow straight through from the base
> > table
> > (as opposed to being joined from some other table)?
>
> None of the "normal" Python wrappers or interfaces do the things you
> attribute to them.
>
> 2014-08-01 16:13:39 [D:\Temp]
> >sqlite test.db
> SQLite version 3.8.6 2014-08-01 01:40:33
> Enter ".help" for usage hints.
> sqlite> create table Voters ( VoterID integer primary key, firstname text,
> lastname text, GenderID integer not null);
> sqlite> create table Gender ( GenderID integer primary key, GenderName
> text not null);
> sqlite> create view ActiveVoters as select * from Voters join Gender using
> (GenderID);
> sqlite> insert into voters values (null, 'Freddie', 'Kruger', 1);
> sqlite> insert into voters values (null, 'Marko', 'Pinhead', 1);
> sqlite> insert into voters values (null, 'Lizzy', 'Borden', 2);
> sqlite> insert into gender values (1, 'Male');
> sqlite> insert into gender values (2, 'Female');
> sqlite> select * from activevoters limit 3;
> 1|Freddie|Kruger|1|Male
> 2|Marko|Pinhead|1|Male
> 3|Lizzy|Borden|2|Female
> sqlite> .quit
>
> 2014-08-01 16:13:44 [D:\Temp]
> >python
> Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)]
> on win32
> Type "help", &q

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

[Jim Callahan]

On Fri, Aug 1, 2014 at 3:41 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> > On 1 Aug 2014, at 8:11pm, Jim Callahan <jim.callahan.orla...@gmail.com>
> wrote:
> >
> > Why does Python get to see the innards of a View; when the query just
> > involves columns (in a view) that flow straight through from the base
> table
> > (as opposed to being joined from some other table)?
>
> A VIEW is just a way of saving a SELECT statement for execution later.
>

Correct a view is a saved SELECT statement. My question is the division of
labor.
IMHO, from a DBA virtual table perspective shouldn't the engine closest to
the data (SQLite in this case) parse and run the SELECT statement specified
by the view?  and just return a resultset to the calling program?

Why is Python parsing the AS SELECT clause of the CREATE VIEW statement?
Shouldn't Python just pass 'SELECT FirstName LastName FROM  ActiveVoters
LIMIT 3' to SQLite and SQLite parse, recognize that ActiveVoters is a VIEW,
 run the SQL and substitute it like a macro-preprocessor before anyone
(especially the calling program) is the wiser?

I can't ask the Python list if I can't specify the correct behavior of a
correct implementation  of the  call level interface.

I have many time used a view in place of a table in MS Access and indeed, I
connected MS Access via ODBC to an ancestor of this database and MS Access
saw an ancestor of this view as a table. I expect something similar from
Python, R or
 Java.

A view is not just supposed to be a convenience from the command line
interface and unusable from other programs; is it?

Jim


>> If you execute the SELECT statement from the VIEW definition as if it
was a
>> separate SELECT statement, do you get an error message of some kind ?

Works OK at command line and does not give any error messages. Almost
impossible to do from Python at my current level of ignorance.

>
> 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] Views as Virtual Tables -- Command line vs. Called Interface

[Jim Callahan]

On Fri, Aug 1, 2014 at 11:58 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> > On 1 Aug 2014, at 4:45pm, Jim Callahan <jim.callahan.orla...@gmail.com>
> wrote:
>
> > column is not present in both tables
>
> This is usually the result of using the syntax "JOIN table USING column"
> because SQL requires columns of that name to be present in both tables.
>  Instead of that phrasing see if you can use this one:
>
> JOIN thattable ON thattable.thatcolumn = thistable.thiscolumn
>

I changed the syntax from:

LEFT JOIN Gender USING (GenderID)

to

INNER JOIN Gender ON Gender.GenderID = Voters.GenderID

Again it worked on the SQLite command line, but not when called from
Python.

>
> If that doesn't help ...
>
> > SQLite Version 3.8.0.1
>
> Is that the version your IPython interface is using ?  Can you give us the
> output of
>
> SELECT sqlite_version()
>
> when done through the iPython interface ?


pd.read_sql_query('SELECT sqlite_version()', engine)
0 sqlite_version()
3.6.21

> And I'm afraid we may also need to see the view definition, so can you
> tell us whatever you used for your CREATE VIEW command ?
>

CREATE VIEW ActiveVoters2
AS SELECT
Voters.CountyID,
Voters.VoterID,
LastName, Suffix, FirstName,MidName,
Supress,
ResAddress1,
ResAddress2,
ResCity, ResST, ResZip9,
MailAddress1,
MailAddress2,
MailAddress3
MailCity, MailST, MailZip9, MailCountry,
Voters.GenderID,
Voters.RaceID,
BirthDate,
RegDate,
Voters.PartyID,
Precinct, PGroup, PSplit, PSuffix,
Voters.StatusID,
CD, HD, SD, CC, SB,
AreaCode, PhoneNumber, PhoneExt, -- Added PhoneExt -- Thursday July 24,
2014 -- FVRS
Email,   -- Added Email-- Thursday July 24,
2014 -- FVRS
County.CountyName,
Gender.GenderName,
Race.RaceName,
Party.PartyName,
Status.StatusName,
VoterHistoryCol.ENov2012,
VoterHistoryCol.EAug2012,
VoterHistoryCol.EPPP2012,
VoterHistoryCol.ENov2010,
VoterHistoryCol.EAug2010,
VoterHistoryCol.ENov2008,
VoterHistoryCol.EAug2008,
VoterHistoryCol.EPPP2008,
(CASE WHEN substr(BirthDate,6,5) <= "11-06" -- Election Day 2012:
Nov 6, 2012
  THEN   2012 - substr(BirthDate,1,4)   -- Had birthday
  ELSE   2012 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
  END) AS AgeENov2012,  -- Age as of Nov 6,
2012
(CASE WHEN substr(BirthDate,6,5) <= "08-26" -- Election Day 2014:
Aug 26, 2014
  THEN   2014 - substr(BirthDate,1,4)   -- Had birthday
  ELSE   2014 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
  END) AS AgeEAug2014,  -- Age as of Aug 26,
2014
(CASE WHEN substr(BirthDate,6,5) <= "11-04" -- Election Day 2014:
Nov 4, 2014
  THEN   2014 - substr(BirthDate,1,4)   -- Had birthday
  ELSE   2014 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
  END) AS AgeENov2014   -- Age as of Nov 4, 2014
FROM Voters
INNER JOIN County ON County.CountyID = Voters.CountyID
INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
INNER JOIN Race   ON Race.RaceID = Voters.RaceID
INNER JOIN Party  ON Party.PartyID   = Voters.PartyID
INNER JOIN Status ON Status.StatusID = Voters.StatusID
INNER JOIN VoterHistoryCol ON VoterHistoryCol.VoterID = Voters.VoterID;

If necessary, I can send the whole Lafayette County, FL database (public
record extract) via private email. Lafayette County is one of the smallest
counties in Florida with only 4,556 voters which makes it ideal for
developing convoluted SQL before moving the SQL to the big counties like
Orange, Broward or Miami-Dade.

Unfortunately, the Anaconda Python environment is a 250 megabyte
(compressed) download.

I am trying to understand enough so that I can write an intelligent
question to the Python/SQLAlchemy/SQLite list.

Why does Python get to see the innards of a View; when the query just
involves columns (in a view) that flow straight through from the base table
(as opposed to being joined from some other table)?

Jim

>
> 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

[sqlite] Views as Virtual Tables -- Command line vs. Called Interface

[Jim Callahan]

At the SQLite3 command line I can't tell the difference between a view and
a table without looking at the schema (that's a good thing).

When I try to query a view (created at the SQLite command line) from
IPython (I will provide specifics, but I want to ask a more general
question first); Python complains about one of the joins inside the view.
So, the called language interface is not passing to Python the view as a
virtual table/resultset, but instead Python is parsing the view and and
trying (and failing) to execute it.

My question is: why is the view processing being handled by the calling
language instead of by SQLite? Shouldn't the Call Level Interface recognize
when a table is actually a view, process the view, but return a resultset
without the calling program being any the wiser?

SPECIFICS
I created a database, a table and a view using the SQLite Command interface.

SQLite Version 3.8.0.1
database: VotersLAF
table:Voters
view: ActiveVoters

Simple query (table version)
SELECT FirstName, LastName FROM Voters LIMIT 3;

Simple query (view version)
SELECT FirstName, LastName FROM ActiveVoters LIMIT 3;

Both queries work from the SQLite 3 command line.

>From Python, the first query (using the table) works, but the second query
(using the view) fails, with a message referring to a join that is internal
to view and not visible from the query:

OperationalError: (Operational Error) cannot join column using GenderID -
column is not present in both tables 'SELECT FirstName, LastName FROM
ActiveVoters LIMIT 3'

The view ActiveVoters limits the number of rows and adds some additional
information via JOINS. In this case the error message is referring GenderID
which is a simple look up from 'M' and 'F' to 'Male' and 'Female' which
works at the SQLite Command Line Interface and is irrelevant to the query
at hand, but for the fact that it is included in the view definition.

Table: Voters
VoterID
FirstName
LastName
GenderID

Table: Gender
GenderID
GenderName

View: ActiveVoters
Voters.VoterID
FirstName
LastName
Voters.GenderID
Gender.GenderName

I used some "--" comments in the view definition.

This is not the list for a Python question, but if it helps or if anyone is
curious, I just downloaded the Anaconda distribution this week and am
running Python 2.7 on Windows 7.

Windows 7 Service Pack 1
Python 2.7.7
Anaconda 2.0.1 (64 bit) (default June 11, 2014 10:40:02)
[MSC v.1500 AMD 64 bit (AMD64) ]
IPython 2.1.0

The interactive shell is IPython with the pylab option.
IPython --pylab

import numpy as np
import matplotlib as pt
import sqlite3
import pandas as pd

from sqlalchemy import create_engine

# OBSOLETE: con = sqlite3.connect('VotersLAF.db')
# SQLite database via the pysqlite driver.
# Note that pysqlite is the same driver as the
# sqlite3 module included with the Python distrib.
# Connect String: sqlite+pysqlite:///file_path
# http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html
#module-sqlalchemy.dialects.sqlite.pysqlite

engine = create_engine('sqlite+pysqlite:///VotersLAF.db')

# TABLE Version works
pd.read_sql_query('SELECT FirstName, LastName FROM Voters LIMIT 3', engine)

# VIEW Version does not work
pd.read_sql_query('SELECT FirstName, LastName FROM ActiveVoters LIMIT 3',
engine)

OperationalError: (Operational Error) cannot join column using GenderID -
column is not present in both tables 'SELECT FirstName, LastName FROM
ActiveVoters LIMIT 3'

Why does the Python program know anything about the view? Why isn't table
handling transparent like at the SQLite3 command line?

Thanks,

Jim Callahan
Orlando, FL
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inefficient query plan in queries with multiple joins

[Jim Callahan]

Would the rtree be useful in a composite primary key and/or ad-hoc
composite query context?
http://www.sqlite.org/rtree.html

If so, how would SQLite know to create and/or use an rtree without explicit
user intervention?

Jim Callahan

> But this does point out a possible enhancement to SQLite:
> Suppose the query is something like this:
> SELECT a,b,c,d,e,f FROM ... WHERE ... ORDER BY a,b,c,d LIMIT 25;
> If SQLite is unable to find a combination of indices that makes things
> naturally come out in a,b,c,d order, then it scans the entire result set
> looking for the 25 entries with the least values for a,b,c,d.  Even if the
> indices guarantee that "a" is coming out in order, because b,c,d are not in
> order, it still scans the entire result set.
> Suppose "a" is in order but b,c,d are not.  Then if the TEMP B-TREE is
> filled up with 25 entries and if the current "a" is greater than the
> largest "a" in the TEMP B-TREE, the scan can stop at that point, no?  But
> SQLite keeps on scanning until the end, even though none of the extra rows
> scanned will ever be in the top 25.
> Version 3.8.2 is in its quiet period right now.  But maybe we can do
> something about this for version 3.8.3.



On Sat, Nov 30, 2013 at 7:26 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Sat, Nov 30, 2013 at 7:08 AM, Simon Slavin <slav...@bigfraud.org>
> wrote:
>
> >
> > On 30 Nov 2013, at 10:24am, George <pinkisntw...@gmail.com> wrote:
> >
> > > I have noticed that when I order using just one column then the query
> is
> > > very fast, because no TEMP B-TREE is used. When I add the other columns
> > > then TEMP B-TREE is used and the query is very slow.
> >
> > Do you have indexes on those four tables ideally suited to the query
> > you're executing ?  Think about how you, as a human, would need to look
> > things up on each table to satisfy your JOIN and ORDER BY clauses.
> >
> >
> For joins, it isn't sufficient just to have indices on the order-by
> columns.  There are lots of other conditions as well.  Generally speaking,
> the indices need to be UNIQUE and the columns indexed need to be NOT NULL.
> The OP's query might not be amenable to that.
>
> But this does point out a possible enhancement to SQLite:
>
> Suppose the query is something like this:
>
> SELECT a,b,c,d,e,f FROM ... WHERE ... ORDER BY a,b,c,d LIMIT 25;
>
> If SQLite is unable to find a combination of indices that makes things
> naturally come out in a,b,c,d order, then it scans the entire result set
> looking for the 25 entries with the least values for a,b,c,d.  Even if the
> indices guarantee that "a" is coming out in order, because b,c,d are not in
> order, it still scans the entire result set.
>
> Suppose "a" is in order but b,c,d are not.  Then if the TEMP B-TREE is
> filled up with 25 entries and if the current "a" is greater than the
> largest "a" in the TEMP B-TREE, the scan can stop at that point, no?  But
> SQLite keeps on scanning until the end, even though none of the extra rows
> scanned will ever be in the top 25.
>
> Version 3.8.2 is in its quiet period right now.  But maybe we can do
> something about this for version 3.8.3.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY

[Jim Callahan]

How to query and/or group complex SQL?

Add the language to create a SQL VIEW before your SELECT statement:

CREATE VIEW viewname AS SELECT [your SQL]
http://www.sqlite.org/lang_createview.html

Then use the viewname in a second SELECT statement as you would a table.

SELECT order_id, issued,  company,  dep_id,  qty, dispatch,  surplus
FROM viewname
WHERE order_id = 1;

or, if necessary

WHERE order_id = '1';

How to query and/or group:
...complex SQL...

order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> "3""13-11-2013"  "Siemens"  "8" "3""3""0"
> How I can group by order_id? In the example I'would return on order_id

=1:


I assume your SQL is already producing the output in your example.
Hope I haven't missed your point and this helps.

Jim

On Thu, Nov 14, 2013 at 2:48 AM, Giuseppe Costanzi <
giuseppecosta...@gmail.com> wrote:

> hi to everybody,
> you excuse for the preceding mails but I have had problems with this and I
> have had to change provider.
> However I propose my question.
> I have this query, that you also see in attachment file.
> SELECT
> orders.order_id AS order_id,
> strftime('%d-%m-%Y', orders.issued) AS issued,
> suppliers.company AS company,
> departments.department_id AS dep_id,
> order_details.quantity AS qty,
>
> SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
> dispatch,
>
> order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
> THEN 1  ELSE 0 END) AS surplus
>
> FROM orders
> INNER JOIN departments ON (departments.department_id =
> orders.department_id)
>
> INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id)
> INNER JOIN order_details ON (orders.order_id = order_details.order_id)
> INNER JOIN transactions ON order_details.order_detail_id =
> transactions.order_detail_id
>
> WHERE  orders.state = 0 AND orders.enable =1
> GROUP BY order_details.order_detail_id
>
> that return such as
>
> order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> "3""13-11-2013"  "Siemens"  "8" "3""3""0"
>
> How I can group by order_id? In the example I'would return on order_id
> =1:
>
> order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "8""6""2"
>
> any suggestions?
>
> regards beppe
>
> ___
> 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] Update and GROUP BY

[Jim Callahan]

Good question Gert. Good solution, Igor and I like Keith's formatting.

I thought the list might be interested in some of the statistical issues
involved in determining if this method of replacing null values is an
appropriate method for your data analysis and alternatives that are
available.

The statistical term for replacing "missing values" (a type of null value)
with a computed or selected value is  "imputation."

This problem/solution presented on this list is an implementation of a type
of mean imputation. The statistical language R, has an entire package
devoted to imputation (although ironically, it doesn't have this exact
method -- it calculates the mean of an entire column without grouping  or
performs a more complex analysis. Although that may be because R experts
know a way to add the grouping.).
http://cran.r-project.org/web/packages/imputation/imputation.pdf

The Wikipedia article, "Imputation (statistics)"
http://en.wikipedia.org/wiki/Imputation_(statistics)
points out some of the tradeoffs involved:

"Another imputation technique involves replacing any missing value with the
mean of that variable for all other cases [records], which has the benefit
of not changing the sample mean for that variable. However, mean imputation
attenuates any correlations involving the variable(s) that are imputed.
This is because, in cases with imputation, there is guaranteed to be no
relationship between the imputed variable and any other measured variables.
Thus, mean imputation has some attractive properties for univariate
analysis but becomes problematic for multivariate analysis.

Regression imputation has the opposite problem of mean imputation. A
regression model is estimated to predict observed values of a variable
based on other variables, and that model is then used to impute values in
cases where that variable is missing. In other words, available information
for complete and incomplete cases is used to predict whether a value on a
specific variable is missing or not. Fitted values from the regression
model are then used to impute the missing values. The problem is that the
imputed data do not have an error term included in their estimation, thus
the estimates fit perfectly along the regression line without any residual
variance. This causes relationships to be over identified and suggest
greater precision in the imputed values than is warranted. The regression
model predicts the most likely value of missing data but does not supply
uncertainty about that value."

There is a lot more in the Wikipedia article, but this seemed like the most
relevant section.

HTH,
Jim Callahan


On Sat, Nov 2, 2013 at 2:23 PM, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 11/2/2013 1:06 PM, Gert Van Assche wrote:
>
>> All, I have this table:
>>
>> DROP TABLE T;
>> CREATE TABLE T (N, V, G);
>> INSERT INTO T VALUES('a', 1, 'x');
>> INSERT INTO T VALUES('b', 3, 'x');
>> INSERT INTO T VALUES('c', null, 'x');
>> INSERT INTO T VALUES('d', 80, 'y');
>> INSERT INTO T VALUES('e', null, 'y');
>> INSERT INTO T VALUES('f', 60, 'y');
>> INSERT INTO T VALUES('g', null, 'y');
>> INSERT INTO T VALUES('h', null, 'z');
>> INSERT INTO T VALUES('i', 111, 'z');
>>
>> I would like to see where N='c', V as the average for the group (G) were
>> this record belongs to (so 'x').
>>
>> Thus where N='c' I would get 2, and where N='e' or 'g', it would be 70,
>> and
>> where N=h it would be 111.
>>
>
> I'm not sure I quite follow, but something like this perhaps:
>
> update T set V = (select avg(V) from T t2 where T.G = t2.G)
> where V is null;
>
> --
> Igor Tandetnik
>
>
> ___
> 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