[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] Changing WAL mode for a transaction

[Navaneeth K N]

Hello,

My application runs the following right after opening the connection to the 
database. 

  pragma journal_mode=wal;
  pragma page_size=4096

When the application wants to perform a bulk data load (loading around 21Gb of 
data), it runs the following before starting the transaction. 

pragma journal_mode=delete

This is done because I am under the assumption that WAL journal mode is not 
suited for long running, big transactions (please correct me if I am wrong). 

But the journal mode change seems to be not affecting. Documentation for WAL 
states the following:

"Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. If a 
process sets WAL mode, then closes and reopens the database, the database will 
come back in WAL mode. In contrast, if a process sets (for example) PRAGMA 
journal_mode=TRUNCATE and then closes and reopens the database will come back 
up in the default rollback mode of DELETE rather than the previous TRUNCATE 
setting."

Does this mean, in my case the journal mode change won't have any effect? When 
I tested, I still see -wal & -shm files present. Will it use "DELETE" journal 
mode for my huge transaction?

Any help would be great!
?
Navaneeth

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

[Joe Mucchiello]

I'm surprised no one suggested a virtual table interface to the OP's blob. 
Store the blob of complex numbers as a blob in raw form (he said it was a 
single platform so endian concerns don't matter) and then create a virtual 
table that provides the array index into the complex numbers. Add a few helper 
functions for accessing the complex numbers directly and you could read or 
write the blob using SQL. The virtual table would have a Dataset_ID, Row, 
Real_part, Im_part as it's fields and the virtual interface would claim there's 
a unique index on (dataset_id, Row).

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Thanks Bill

That worked

BUT

In my form when I go to "DataBindings" I get a Pop-up to "Add Project
Datasource" then the 2 screens to choose Datasource Type( I select
Database) then I choose DataSet. This is where it breaks down."Choose Data
Connection there are 4 choices "Access" and 3 server types, then there is
 which I select. And that leaves me with a single "Data provider"
Microsoft OLE DB simple provider  This connection works, but is it correct?
The connection is MSDAOSP

Then I get to the screen "Choose your database objects" and an ERROR

"Selecting Objects of type 'Table' is not supported.

Then we get to the next part of the problem. I am in my late 70's and have
not done any SQL for over 15 years ( I was pretty good at one time.) Where
do I go from here.

Jay

On Sat, Apr 25, 2015 at 5:12 PM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:

> I don't know what error you're getting so I can't offer specific help.
>
> This is what works for us:
>
> Open your project in Visual Studio.
>
> Click Tools > NuGet Package Manager > Manage NuGet Packages for Solution...
>
> Select Online > nugget.org
>
> In the search box enter system.data.sqlite
>
> A list of packages should be displayed after a short wait.
>
> Select System.Data.SQLite Core (x86/x64) or, if you need LINQ and Entity
> Framework support, select System.Data.SQLite (x86/x64), then click Install
> (we use Core here)
>
> After installation you should see a little green checkmark next to the
> package you selected.
>
> Click Close and you're done. Check on line for VB.NET code samples using
> SQLITE.
>
> Good luck,
>
> --
> 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 Jay Smith
> > Sent: Saturday, April 25, 2015 3:50 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> >
> > Hi Bill
> >
> > I have been trying for 2 days and I am totally lost.
> >
> > I have NuGet Package Manager. I don't know how to use it.
> >
> > From the  PM>   System.Data.SQLiteI get an error.
> >
> > Can you be a little more specific.
> >
> > Jay
> >
> > On Thu, Apr 23, 2015 at 4:08 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 Jay Smith
> > > > Sent: Thursday, April 23, 2015 3:47 PM
> > > > To: General Discussion of SQLite Database
> > > > Subject: Re: [sqlite] building SQLite DLL with Visual C++
> > > >
> > > > Thank you Adam for responding to my post. I have the windows
> > > > binaries downloaded.
> > > >
> > > > At this point I am just following the instructions in the book. And
> > > > I really am not sure what I need the dll for.
> > > >
> > > > Here's the scenario. I have created a program in vb2012. The
> > program
> > > > stores less than 20 fields of data. I am currently saving the data
> > > > in a html format. I just recently discovered SQLite. I am now in
> > the
> > > > process of changing over to a database to store data. I studied SQL
> > > > and Oracle
> > > > 10 years ago. I have almost completed the database for the project.
> > > > My problem is how to integrate the SQL db into my VB program.
> > >
> > > Your subject line says C++, but the line above says VB, so is your
> > > program written in C++ or VB?
> > > If it's VB just get SQLite with NuGet (Tools > NuGet Package Manager,
> > > then search for System.Data.SQLite Core).
> > >
> > > -Bill
> > >
> > >
> > > 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] Changing WAL mode for a transaction

[Simon Slavin]

On 26 Apr 2015, at 1:01pm, Navaneeth K N  wrote:

>  pragma page_size=4096

Just to remove this from your question, that PRAGMA has effect only before the 
first item is created in the database.  Once the database file has something in 
it, it already has pages, so the page size is fixed.  Further changes have no 
effect.

Simon.