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

[Nathan F]

Serialization should be trivial in whatever language you happen to be
using.  Something along the lines of golang's encoding/gob is ideal for
this.  In c# there is, no doubt, very robust serialization built in and it
really amounts to little more than Serialize(object) <-->
Deserialize(object).  It is best to have a separate column whose purpose is
only to give a version number of some sort to the data.  Somebody will then
be responsible for tracking any changes to the stored data with different
version numbers and, if required, write a simple method for transforming
one set of data to another.  This does, of course, potentially affect logic
within your application, but how to deal with that is up to you.

On Sun, Apr 26, 2015 at 6:13 AM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> 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-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 (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] Thoughts on storing arrays of complex numbers (Solved)

[Drago, William @ CSG - NARDA-MITEQ]

> -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] Thoughts on storing arrays of complex numbers

[Drago, William @ CSG - NARDA-MITEQ]

This makes perfect sense. Thank you.

--
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 Callahan
> Sent: Saturday, April 25, 2015 1:37 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Thoughts on storing arrays of complex numbers
>
> 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, Wil

[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

[Florian Weimer]

* Steven M. McNeese:

> I would serialize to JSON and store as a string.

You need to be careful about the choice of JSON library, many of them
lose information when converting doubles to JSON.

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

[Drago, William @ CSG - NARDA-MITEQ]

All,

I always learn something from this group even when the answers don't directly 
solve my problem, and for that I am always thankful. Perhaps I should have 
included more information about what I'm trying to accomplish and/or phrased 
the question differently. That might have saved some time for the folks who 
replied. The problem is solved, but there's more info below if anyone is 
interested.

In this case the problem is very simple: All I have to do is store the data. I 
don't have to search it or perform any computations on it, and portability is 
not an issue as this is Windows only in-house code.

The complex data array, which represents a network analyzer trace (similar to 
the trace on an oscilloscope), is processed by my application to extract the 
pertinent information which is stored in a properly normalized database. Once 
all the useful information has been extracted from the array there is no need 
to keep the array, however, as an extra measure of thoroughness I do keep it 
just in case something was missed and we need to look at it again sometime in 
the future.

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.

Thank you for your suggestions,
--
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 James K. Lowden
> Sent: Friday, April 24, 2015 12:42 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Thoughts on storing arrays of complex numbers
>
> On Fri, 24 Apr 2015 13:37:40 +
> "Drago, William @ CSG - NARDA-MITEQ"  wrote:
>
> > I'm trying to avoid re-inventing the wheel. Is there a best or
> > generally accept way to store arrays of complex numbers?
>
> A table in First Normal Form has no repeating groups.  That means no
> row has an array of any kind.  Arrays in the relational model are
> represented in columns, one element per row.
>
> A column whose type is nonrepeating is said to be "atomic", but that's
> something of a convention.  An atom is supposed to be indivisible, but
> we can take substrings of string, parts of dates, and exponents of
> floating point numbers.  So nonrepeating datatypes aren't necessarily
> atomic, exactly.  They're just not repeating.  ;-)
>
> The question of your complex array then comes down to two apects: how
> to represent the complex number, and how to represent the array.  The
> case for the array is simple: keep one complex number per row.  The
> case for the "divisible atomic" complex number depends on a choice: how
> you want the DBMS to treat the components of the complex type.
>
> The most general solution -- and therefore probably the best one -- is
> to keep the real and complex component each in its own REAL column.
> That lets you sort and select the complex numbers using SQLite's built-
> in functions without limitation.  For example, if we call those
> components "a" and "b", you could say,
>
>   select * from T where "a" between 1.0 and 2.0
>
> Such a table would be
>
>   create table complex_array
>   ( name TEXT not null
>   , ordinal INTEGER not null
>   , real_part REAL not null
>   , imaginary REAL not null
>   , primary key( name, ordinal )
>   );
>
> That's the textbook solution on a DBMS without user-defined types.
>
> An alternative is to conceive of the complex type as a datatype, and
> represent it in SQLite as BLOB or TEXT.  That severely limits SQLite's
> ability to compare and select the values, although that limitation can
> be somewhat alleviated with user-defined functions e.g.,
>
>   select * from T where "C" = complex(1.0, -0.5)
>
> If 1) you're not interested in letting the DBMS inspect the data, and
> 2) you have some convenient C function to losslessly convert your
> complex type to a string or bit-string, then a single-column
> representation might be more convenient.
>
> --jkl
>
>
> ___
> 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 

[sqlite] Thoughts on storing arrays of complex numbers

[Keith Medcalf]

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://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] Thoughts on storing arrays of complex numbers

[Dominique Devienne]

On Fri, Apr 24, 2015 at 3:37 PM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:

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

Unfortunately, if you do this, you DB/schema is not longer portable (
endianess issues), and becomes "opaque" (must know a-priori what the blob
contains, and worse in what organization, i.e. your #1 or #2 or #3).

SQLite pundits would tell you to store your real/imag floating-points in
tables I guess. Forcing you to add an order col, and a FK back to the owner
of the complex numbers in another table. But that's much less efficient as
a storage mechanism.

Others (like Steven) would tell you to store it in some human-readable text
format (JSON, XML, YAML, TOML, whatever), but that forces to parse and adds
a dependency on some parser.

A middle ground is using some self-describing binary format and stay with
blob, like AVRO for example, or CBOR perhaps, (or HDF5 with a custom VFS)
to at least have a magic cookie in the blob to infer the format, and some
portability. But still a dependency on some serializing library.

These are all I'm afraid not very satisfactory. In Oracle you'd create a
custom Complex type, and another for a VARRAY of that type, and store that
in a "cell" (row,col). But SQLite doesn't have built-in support for arrays,
nor user-defined-types.

So you remain stuck between the proverbial rock and the hard place... There
are no good solution in SQLite for things like this. --DD

[sqlite] Thoughts on storing arrays of complex numbers

[Drago, William @ CSG - NARDA-MITEQ]

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
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] Thoughts on storing arrays of complex numbers

[James K. Lowden]

On Fri, 24 Apr 2015 13:37:40 +
"Drago, William @ CSG - NARDA-MITEQ"  wrote:

> I'm trying to avoid re-inventing the wheel. Is there a best or
> generally accept way to store arrays of complex numbers? 

A table in First Normal Form has no repeating groups.  That means no
row has an array of any kind.  Arrays in the relational model are
represented in columns, one element per row.  

A column whose type is nonrepeating is said to be "atomic", but that's
something of a convention.  An atom is supposed to be indivisible, but
we can take substrings of string, parts of dates, and exponents of
floating point numbers.  So nonrepeating datatypes aren't necessarily
atomic, exactly.  They're just not repeating.  ;-)  

The question of your complex array then comes down to two apects: how
to represent the complex number, and how to represent the array.  The
case for the array is simple: keep one complex number per row.  The
case for the "divisible atomic" complex number depends on a choice: how
you want the DBMS to treat the components of the complex type.  

The most general solution -- and therefore probably the best one -- is
to keep the real and complex component each in its own REAL column.
That lets you sort and select the complex numbers using SQLite's
built-in functions without limitation.  For example, if we call those
components "a" and "b", you could say, 

select * from T where "a" between 1.0 and 2.0

Such a table would be 

create table complex_array
( name TEXT not null
, ordinal INTEGER not null
, real_part REAL not null
, imaginary REAL not null
, primary key( name, ordinal )
);

That's the textbook solution on a DBMS without user-defined types.  

An alternative is to conceive of the complex type as a datatype, and
represent it in SQLite as BLOB or TEXT.  That severely limits
SQLite's ability to compare and select the values, although that
limitation can be somewhat alleviated with user-defined functions e.g., 

select * from T where "C" = complex(1.0, -0.5)

If 1) you're not interested in letting the DBMS inspect the data, and
2) you have some convenient C function to losslessly convert your
complex type to a string or bit-string, then a single-column
representation might be more convenient.  

--jkl

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

[Scott Hess]

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.

I don't have any specific recommendations along this front, as usually
projects already have one (or two or three) already linked in to cover
unrelated cases (reading config files, importing data from another
tool, etc).

-scott

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

[Steven M. McNeese]

I would serialize to JSON and store as a string.

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Drago,
William @ CSG - NARDA-MITEQ
Sent: Friday, April 24, 2015 8:38 AM
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://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


---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[sqlite] Thoughts on storing arrays of complex numbers

[Scott Doctor]

If you are working with linear algebra type matrices, then simply make a 
column that represents each element. For example, a Jones matrix which 
is 2x2 would have four columns. Then each set of matrices are 
represented by a row. Best way to handle is to visualizes how you would 
use the numbers. If you are using recursive numerical techniques that 
requires algorithmic access, then a structure that supports subscript 
accessing is most efficient (wjicj means tables of numbers). If you are 
dealing with large data sets that follow a defined size, say x number of 
samples per test yielding y number of calculated coefficients, then the 
data tells you to have two tables, one for input data one for output 
data, with the number of columns defined by your mathematical variables. 
Number one rule is to keep it simple and obvious.

Using a blob seems counter productive as you need to access your data by 
some rules, which seems to indicate a straight forward table of numbers. 
Write them out on a sheet of paper like you would do in a math class. 
The structure of the table will become self evident.


Scott Doctor
scott at scottdoctor.com

On 4/24/2015 6:37 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> All,
>
> I'm trying to avoid re-inventing the wheel. Is there a best or generally 
> accept way to store arrays of complex numbers? I'm considering the following:
>
> I could have two blob fields in my table. One for the real parts and one for 
> the imaginary. (I don't like this.)
> Or, I could use a single blob field and concat the real and imaginary parts 
> into one long blob. (I like this.)
> Or, I could store pairs in the blob 
> (realimaginaryrealimaginaryrealimaginaryrealimaginary). (I like this.)
>
> Or maybe there's a real nifty way to handle complex numbers that I haven't 
> thought of.
>
> Thanks,
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 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
>
>