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

[Stefan Keller]

As of SQL:2003, arrays are standard SQL types.
Examples of standard syntax:
  BIGINT ARRAY
  BIGINT ARRAY[100]
  BIGINT ARRAY[100] ARRAY[200]

It's fully implemented e.g. in PostgreSQL...

-S.


2015-04-27 18:48 GMT+02:00 Drago, William @ CSG - NARDA-MITEQ
:
> 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 [mailto:sqlite-
>> users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Callahan
>> Sent: Sunday, April 26, 2015 9:56 PM
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] SQLite and Scientific Computing: Arrays and Complex
>> Numbers
>>
>> 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/ms
>> g00027.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-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> 

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

[Stefan Keller]

Hi Navin

I've compared with PostgreSQL. It's twice as as "fast" as SQLite with
100 mio. records on my old laptop - but still too slow using count().

So, as Eduardo suggested, you have to solve this problem with a
separate table and triggers, like shown below.

Yours, S.

-- Create test table
CREATE TABLE test (id INTEGER PRIMARY KEY, r REAL, t TEXT );

-- Fill test table with test data...!
INSERT INTO test (r,t)
  WITH RECURSIVE
cte(r,t) AS (
   SELECT random(), hex(random()*1000)
   UNION ALL
   SELECT random(), hex(random()*1000)
 FROM cte
 LIMIT 1000 -- 10 mio.
  )
  SELECT * FROM cte;

-- Create auxiliary table
CREATE TABLE count_statistics (
  table_name TEXT not NULL,
  row_count INTEGER
);

CREATE TRIGGER count_statistics_delete_test
BEFORE DELETE ON 'test'
FOR EACH ROW BEGIN
  UPDATE count_statistics SET row_count=row_count-1 WHERE table_name='test';
END;

CREATE TRIGGER count_statistics_insert_test
BEFORE INSERT ON 'test'
FOR EACH ROW BEGIN
  UPDATE count_statistics SET row_count=row_count+1 WHERE table_name='test';
END;

-- initialize/update/reset count_statistics
UPDATE count_statistics SET row_count=(
  SELECT count(*) FROM test
) WHERE table_name='test';


.timer on
SELECT max(id) FROM test;
SELECT count(*) FROM test; -- slow!

-- This is the count replacement:
SELECT row_count FROM count_statistics WHERE table_name='test'; -- fast!

-- Done.

2015-01-25 20:05 GMT+01:00 Navin S Parakkal <nav...@hp.com>:
>
> On Saturday 24 January 2015 03:15 PM, Stefan Keller wrote:
>>
>> I think it's time for a serious simple benchmark with sqlite and say
>> PostgreSQL.
>> PostgeSQL also had performance problems time ago but this has been
>> resolved.
>> Can you describe the hp_table1 schema (CREATE TABLE statement...) and
>> some data (INSERTs)?
>>
>>
> Yes though a avid fan of sqlite, have to reconsider it for scalability
> issues. Everything about the table is present in the archives and thread .
>
>
>
> On Sunday 25 January 2015 06:17 AM, Eduardo Morras wrote:
>>
>> You can use PostgreSQL, using part of Oracle licence cost for better
>> hardware and a dinner for the team.
>>
> Yes that is an option we have to consider seriously. I thought we were doing
> something wrong like creating the index or maybe tuning page size.
>
>
> I'm still stuck.
>
>
>
>
> I maybe completely wrong but i'm guessing your B-tree index is static.
> Something in that doesn't support dynamic updation or some bug causing it
> have linear update .
>
>
> http://pastebin.com/davqKcF8
>
>
>
> Reposting below if you have missed it.
>
>
> My process.csv is around 27G. I've gzipped it and put
> atftp://navinps:sqlit...@h2.usa.hp.com  as process.csv.gz
>
> There is only 1 file there.
> md5sum process.csv.gz
> e77a322744a26d4c8a1ad4d61a84ee72  process.csv.gz
>
>  [root@centosnavin sqlite-autoconf-3080801]# cat sqlite3commands.txt
> CREATE TABLE [hp_table1] ( InstanceId INTEGER, LogTime INTEGER, ArrivalTime
> INTEGER, CollectionTime INTEGER, [dml_PROC_TIME] TIME, [dml_PROC_INTERVAL]
> INTEGER, [dml_PROC_INTEREST] TEXT, [dml_PROC_TOP_CPU_INDEX] INTEGER,
> [dml_PROC_TOP_DISK_INDEX] INTEGER, [dml_PROC_STATE_FLAG] INTEGER,
> [dml_PROC_RUN_TIME] REAL, [dml_PROC_STOP_REASON_FLAG] INTEGER,
> [dml_PROC_INTERVAL_ALIVE] INTEGER, [dml_PROC_STOP_REASON] TEXT,
> [dml_PROC_STATE] TEXT, [dml_PROC_PRI] INTEGER, [dml_PROC_NICE_PRI] INTEGER,
> [dml_PROC_CPU_LAST_USED] INTEGER, [dml_PROC_CPU_SWITCHES] INTEGER,
> [dml_PROC_IO_BYTE] REAL, [dml_PROC_VOLUNTARY_CSWITCH] INTEGER,
> [dml_PROC_FORCED_CSWITCH] INTEGER, [dml_PROC_IO_BYTE_RATE] REAL,
> [dml_PROC_CPU_TOTAL_UTIL] REAL, [dml_PROC_CPU_TOTAL_TIME] REAL,
> [dml_PROC_CPU_SYS_MODE_UTIL] REAL, [dml_PROC_CPU_SYS_MODE_TIME] REAL,
> [dml_PROC_CPU_USER_MODE_UTIL] REAL, [dml_PROC_CPU_USER_MODE_TIME] REAL,
> [dml_PROC_THREAD_COUNT] INTEGER, [dml_PROC_CPU_ALIVE_TOTAL_UTIL] REAL ,
> [dml_PROC_CPU_ALIVE_USER_MODE_UTIL]  REAL,
> [dml_PROC_CPU_ALIVE_SYS_MODE_UTIL] REAL, [dml_PROC_CPU_SYSCALL_UTIL] REAL,
> [dml_PROC_CPU_SYSCALL_TIME] REAL, [dml_PROC_CHILD_CPU_USER_MODE_UTIL] REAL,
> [dml_PROC_CHILD_CPU_SYS_MODE_UTIL] REAL, [dml_PROC_CHILD_CPU_TOTAL_UTIL]
> REAL, [dml_PROC_DISK_PHYS_READ] INTEGER, [dml_PROC_DISK_PHYS_READ_RATE]
> REAL, [dml_PROC_DISK_PHYS_WRITE] INTEGER, [dml_PROC_DISK_PHYS_WRITE_RATE]
> REAL, [dml_PROC_DISK_PHYS_IO_RATE] REAL, [dml_PROC_MEM_RES] REAL,
> [dml_PROC_MEM_SHARED_RES] REAL, [dml_PROC_MEM_VIRT] REAL,
> [dml_PROC_MEM_DATA_VIRT] REAL, [dml_PROC_MEM_STACK_VIRT] REAL,
> [dml_PROC_PAGEFAULT] INTEGER, [dml_PROC_PAGEFAULT_RATE] REAL,
> [dml_PROC_MINOR_FAULT] INTEGER, [dml_PROC_MAJOR_FAULT] INTEGER,
> [dml_PROC_MEM_LOCKED] REAL, [dml_PROC_DISK_

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

[Stefan Keller]

Hi,

Relying on sequence will not work (and is a wrong hack) since the use
case includes deleting rows explicitly.

I think it's time for a serious simple benchmark with sqlite and say PostgreSQL.
PostgeSQL also had performance problems time ago but this has been resolved.
Can you describe the hp_table1 schema (CREATE TABLE statement...) and
some data (INSERTs)?

Yours, S.


2015-01-24 10:33 GMT+01:00 Clemens Ladisch :
> Jim Wilcoxson wrote:
>> If you have a table where rows are inserted but never deleted, and you
>> have a rowid column, you can use this:
>>
>> select seq from sqlite_sequence where name = 'tablename'
>
> This works only for an AUTOINCREMENT column.
>
>> This will return instantly, without scanning any rows or indexes, and
>> is much faster than max(rowid) for huge tables.
>
> Max(rowid) has a special optimization and looks only at the last entry
> in the index.  It is what SQLite uses internally for tables without
> AUTOINCREMENT, and is actually faster than looking up the sequence value
> in a separate table.
>
>
> Regards,
> Clemens
> ___
> 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] Whish List for 2015

[Stefan Keller]

Hi,

Adding JSON to SQLite (like in PostgreSQL) ultimately needs a
JavaScript parser - and that seems to be against the goal of SQLite
being slim.

But what about adding a data type "hash table", i.e. set of keyvalue
pairs (also known as hstore, dictionary or associative array)?
Would'nt this still serve your use case?

Hash tables are already used now internally in the SQLite parser (see hash.c).
If not as part of the core it could be first implemented as an extension.
Here's the syntax of hstore in PostgreSQL [1].

Yours, S.

[1] http://www.postgresql.org/docs/current/static/hstore.html


2015-01-18 22:16 GMT+01:00 Simon Slavin :
>
> On 18 Jan 2015, at 8:29pm, David Barrett  wrote:
>
>>SELECT createNewJSONCollationFunction( "FooCollationSequence", "foo" );
>>SELECT createNewJSONCollationFunction( "BarCollationSequence", "bar" );
>
> Those two might be okay depending on what the quoted values are meant to be.
>
>>CREATE INDEX ON testDataFoo ( "json" FooCollationSequence );
>>CREATE INDEX ON testDataBar ( "json" BarCollationSequence );
>
> Syntax wrong.
>
> CREATE INDEX indexname ON tablename (columnname COLLATE collatefunction)
>
> Indexes need a name each.  Inside the brackets goes the name of the column 
> you're indexing on, then COLLATE, then the collation function to use (which 
> can be your own).  You cannot make the  collation function take a parameter 
> at this point.
>
>> Before giving that a shot, however, I'm hoping someone here can give me a
>> sense of whether that'll work?  Specifically, if I'm generating these
>> dynamic collation sequences on the fly, how does sqlite store the index on
>> disk?
>
> SQLite uses the simplest most obvious way to do it: the values returned by 
> your COLLATE function are stored in the index when a new row is added to a 
> table.  If your COLLATE function changes after rows have been added to the 
> index, SQLite does not understand the problem and can corrupt the index as it 
> tries to change it assuming that the old values are correct.
>
> 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 query key-value-pairs similar to PostgreSQL HSTORE type?

[Stefan Keller]

Hi,

Thank you all for the answers.

Petite Abeille writ

2014-03-18 19:22 GMT+01:00 Petite Abeille <petite.abei...@gmail.com>:
> If, for some reasons, you cannot even accomplish first normal form [1], i.e. 
> one and only one value per column,
> well, then, maybe, a relational database is not the right tool for the task 
> at hand.

Some answers already suggested to normalize key-values.

While I know this EAV pattern I'm still open for implementations which
are able to process KVP as a data type.
And, actually, as you may have realized, PostgreSQL proved that even
(post-)relational databases can handle KVP efficiently.

So, I'm considering to follow-up Tiago's proposal either
1. to create function similar to PostgreSQL (with the problem that
SQLite has no types and thus can't optimize the index) -
2. or to write at least a casting function which takes the key-value
string syntax and converts it into rows (pivot)??

-S.


2014-03-18 19:22 GMT+01:00 Petite Abeille <petite.abei...@gmail.com>:
>
> On Mar 18, 2014, at 2:46 AM, Stefan Keller <sfkel...@gmail.com> wrote:
>
>> Any suggestions on how to query this most efficiently (like [select
>> value from "some_key"])?
>
> As mentioned, turn this construct into a regular relational table structure.
>
> If, for some reasons, you cannot even accomplish first normal form [1], i.e. 
> one and only one value per column, well, then, maybe, a relational database 
> is not the right tool for the task at hand.
>
> [1] http://en.wikipedia.org/wiki/First_normal_form
>
> ___
> 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] How to query key-value-pairs similar to PostgreSQL HSTORE type?

[Stefan Keller]

Hi,

I have a column which contains a string structure taken from the
PostgreSQL HSTORE key-value-pairs type. This is an example of one
column value:

"operator"=>"police","name"=>"Zurich","some_key"=>"some_value"

Any suggestions on how to query this most efficiently (like [select
value from "some_key"])?

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

Re: [sqlite] Is SQLite a DBMS?

[Stefan Keller]

Dear Keith

> I think your definition is a little off. (...)

Nice thoughts about the general term of a system - but unfortunately not
state-of-the-art. If you don't trust in G* pls. read the first chapters of
any university course or well-known or books about Database Systems, like
the book "Fundamentals..." from Elmasri

Yours, Stefan



2013/9/2 Keith Medcalf 

>
> > To me (as a university teacher :->), here exists no single common
> > definition what a (R)DBMS is! That comes close:
>
> > A database management system (DBMS) is software that controls the
> > storage, organization, and retrieval of data. A DBMS provides
> > various functions like data security, data integrity, data sharing,
> > data concurrence, data independence, data recovery etc. And an RDBMS
> > is a DBMS based on the relational model.
>
> I think your definition is a little off.  A DBMS (data base management
> system) is a "system" for the management of a collection (base) of data.
>  The terms themselves indicate no requirement for the "base" or the "data",
> nor in particular the type of "system" involved -- certainly no requirement
> for "software" running on a "computer".  DNA (and RNA) are DBMSes and are
> most assuredly Systems for the Management of a Base of Data.
>
> It is unfortunate that so many wear blinders so much of the time that they
> cannot comprehend the plain and clear meaning of words without falling
> afoul of their own prejudices.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Stefan Keller]

I understand now.

To me (as a university teacher :->), here exists no single common
definition what a (R)DBMS is! That comes close:

A database management system (DBMS) is software that controls the storage,
organization, and retrieval of data. A DBMS provides various functions like
data security, data integrity, data sharing, data concurrence, data
independence, data recovery etc. And an RDBMS is a DBMS based on the
relational model.

To me, SQLite clearly is a RDBMS - with pros and cons like any product.
There is no one-size-fits-all database btw and that's why NoSQL databases
came up.

I see no evidence, why SQLite would'nt be a RDBMS, or if ot would be less
than a RDBMS like e.g. Oracle or PostgreSQL. If someone want's to argue
based on single properties, this could be of help:
http://database-management-systems.findthebest.com/compare/24-36-43-53/Access-vs-Oracle-vs-PostgreSQL-vs-SQLite



2013/9/1 kimtiago 

> Hi people,
>
> I`m really sorry if you think i`m a noob. Maybe I am.
>
> I`m brazilian and my english its not very good.
>
> I googled SQLite and i read a lot of information before post in this forum.
>
> I`m writing by nabble yes.
>
> Now please tell me. Is this a forum only for experts? I just asked a
> question.
>
> Now let me explain why i have this doubt. I did a exam and I miss this
> question, so i`m seeking for information to counter attack my teacher and
> i`m confuse beacause the information i`m reading in the web says diferent
> things. Because thia i came here.
>
> In wikipedia says that he is DBMS, but in the official web site says
> "SQLite
> is an embedded SQL database engine."
>
> I prefer the official information. Now please, someone can explain me whats
> the difference?
>
> Thank you for undestand me
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Is-SQLite-a-DBMS-tp70868p70874.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Stefan Keller]

Hi,

Wikipedia answers with yes and why (= because it's ACID and SQL compliant)
within the first three sentences!
http://en.wikipedia.org/wiki/SQLite

Yours, S.


2013/9/1 kimtiago 

> Hi,
>
> I need to know if SQLite is a DBMS and why.
>
> Please its urgent.
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Is-SQLite-a-DBMS-tp70868.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

Hi Régis

I'd wish to give you a solution but I'm sorry to have new news about that issue.

I'd be happy if there are any SQlite devs around to give you a
solution in order to make this database more usable (an more SQL
compatible).

Yours, Stefan


2013/6/11 regish :
> Hi all,
> I'm starting to use SQLITE in GIS use cases. I'm facing this view typing
> column issue, which prevent my favourite client from interpreting correctly
> numeric data types.  I'm using QGIS, so I won't be able to map numeric
> values (they fall back as text values).
> Is there anything new since 2010?  If not, should I suggest Qgis Devs to
> hack the field type detection for views?
> Régis
>
>
>
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-tp56769p69350.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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] 'integer'

[Stefan Keller]

Again a disclaimer: I use SQlite often e.g. for continuous testing and
in Desktop Apps. - and I like it as it is except for it's homepage
declaration and (more formally) for it kind of weak typing (meaning
'weak' compared to the information schema).

Weak typing in databases assigns the house keeping of consistency to
the writer - who is often unknown in database uses cases. And views
which are'nt able to keep and disclose their domain descriptor is a
'no-no' in database technology.

Can someone give me use cases on how weak typing is used?

Simon wrote:
> Stefan, please read this:
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Thank you for the weblink.
Here are some citations from it which could be of interest to you too
(see below).
I don't think SQLite supports that.
'Nough said?

Roger wrote:
> In other news people complained about those new fangled cars not behaving
> exactly like the horses they were used to.  Eventually they got over it and
> realised it was a wonderful improvement most of the time.

The citations of the SQL standard gives evidence that it's the other
way round: *You* are riding the horses.
But that's Ok to me as long as those riders declare that :->

Yours, Stefan




Excerpts from "ISO/IEC 9075:1992, Database Language SQL - July 30,
1992 (Second Informal Review Draft)",
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt :

4.1 Data Types
"SQL () defines distinct data types named by the following s: CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC,
DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME,
TIMESTAMP, and INTERVAL."

"(...) A data type descriptor includes an identification of the data
type and all information needed to characterize an instance of that
data type."

4.7 Domains
"A domain is described by a domain descriptor. (...)"

4.8  Columns
"(...) All values of the same column are of the same data type or
domain and are values in the same table.

4.12 Catalogs
"(...) An SQL-environment contains zero or more catalogs. A catalog
contains one or more schemas, but always contains a schema named
INFORMATION_SCHEMA that contains the views and domains of the
Information Schema."




2011/4/18 Simon Slavin :
>
> On 18 Apr 2011, at 1:53am, Roger Binns wrote:
>
>> Your complaints are really that SQLite doesn't function exactly how you are
>> used to.  Note that it does do exactly what you tell it - the behaviour is
>> not random.
>
> It's probably worth Stefan reading the SQL specification.  Stefan, please 
> read this:
>
> 
>
> which is about as close to a spec as there is.  Then you can survey all the 
> well known implementations of SQL and find that none of them really get at 
> all close to it.
>
> 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] 'integer'

[Stefan Keller]

uld explain what
> they are based on.
> SQLite is in no way "in-memory", it stores all data to file. And
> please explain how "data container" differs from "database". In some
> sense all databases are "data containers" because they contain data.
>
>
> Pavel
>
>
> On Sun, Apr 17, 2011 at 8:12 AM, Stefan Keller <sfkel...@gmail.com> wrote:
>> Michael and Jay are right about the subtleties on how SQlite
>> interprets what is a data type, a primary key and a database schema
>> and it's ACID implementation in general.
>>
>> To me, the main reason - and remedy - of this FAQ is that SQlite
>> should'nt be called a "SQL database" (as e.g. touted on its homepage).
>> Instead it should clearly declare itself as an "In-memory SQL
>> Datastore" or a "Data container with SQL capabilities".
>>
>> Yours, S.
>>
>>
>> 2011/4/17 Black, Michael (IS) <michael.bla...@ngc.com>:
>>> Seems to behave OK for me on 3.7.5 on Windows.  What version are you using 
>>> on what OS with what compile flags?
>>>
>>> You also "said" it didn't work but you didnt' actually what what you did.
>>>
>>> Like this...
>>>
>>>
>>>
>>> SQLite version 3.7.5
>>> Enter ".help" for instructions
>>> Enter SQL statements terminated with a ";"
>>> sqlite> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT,
>>>   ...>         OtherColumn INTEGER);
>>> sqlite>
>>> sqlite> INSERT INTO Tg (TgConfigId) VALUES (1);
>>> sqlite> SELECT * FROM Tg WHERE TgConfigId = 1;
>>> 1|
>>> sqlite>
>>> sqlite> SELECT * FROM Tg;
>>> 1|
>>> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('1');
>>> Error: PRIMARY KEY must be unique
>>> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('2');
>>> sqlite> SELECT * FROM Tg;
>>> 1|
>>> 2|
>>> sqlite> SELECT * FROM Tg WHERE TgConfigId = 2;
>>> 2|
>>>
>>>
>>>
>>>
>>>
>>> Michael D. Black
>>>
>>> Senior Scientist
>>>
>>> NG Information Systems
>>>
>>> Advanced Analytics Directorate
>>>
>>>
>>>
>>> 
>>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
>>> behalf of Tobias Vesterlund [tobias.vesterl...@ericsson.com]
>>> Sent: Saturday, April 16, 2011 12:40 PM
>>> To: sqlite-users@sqlite.org
>>> Subject: EXT :[sqlite] 'integer'
>>>
>>> Hi,
>>>
>>> I ran into something I don't understand, maybe someone here can shed some 
>>> light on it for me.
>>>
>>> I have a table named Tg which is created (with tcl) by:
>>>
>>> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT,
>>>        OtherColumn INTEGER);
>>>
>>> If I do:
>>> INSERT INTO Tg (TgConfigId) VALUES (1);
>>>
>>> The following select works:
>>> SELECT * FROM Tg WHERE TgConfigId = 1;
>>>
>>> But if I insert '1' instead I have to select on '1', 1 no longer works. 
>>> That makes some sense, but not entirely, as what I inserted isn't an 
>>> integer any longer but a string. Why would I be allowed to insert values 
>>> with '' in a column that is specified to be an integer?
>>>
>>> Regards,
>>> Tobias
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'integer'

[Stefan Keller]

Michael and Jay are right about the subtleties on how SQlite
interprets what is a data type, a primary key and a database schema
and it's ACID implementation in general.

To me, the main reason - and remedy - of this FAQ is that SQlite
should'nt be called a "SQL database" (as e.g. touted on its homepage).
Instead it should clearly declare itself as an "In-memory SQL
Datastore" or a "Data container with SQL capabilities".

Yours, S.


2011/4/17 Black, Michael (IS) :
> Seems to behave OK for me on 3.7.5 on Windows.  What version are you using on 
> what OS with what compile flags?
>
> You also "said" it didn't work but you didnt' actually what what you did.
>
> Like this...
>
>
>
> SQLite version 3.7.5
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT,
>   ...>         OtherColumn INTEGER);
> sqlite>
> sqlite> INSERT INTO Tg (TgConfigId) VALUES (1);
> sqlite> SELECT * FROM Tg WHERE TgConfigId = 1;
> 1|
> sqlite>
> sqlite> SELECT * FROM Tg;
> 1|
> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('1');
> Error: PRIMARY KEY must be unique
> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('2');
> sqlite> SELECT * FROM Tg;
> 1|
> 2|
> sqlite> SELECT * FROM Tg WHERE TgConfigId = 2;
> 2|
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Tobias Vesterlund [tobias.vesterl...@ericsson.com]
> Sent: Saturday, April 16, 2011 12:40 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] 'integer'
>
> Hi,
>
> I ran into something I don't understand, maybe someone here can shed some 
> light on it for me.
>
> I have a table named Tg which is created (with tcl) by:
>
> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT,
>        OtherColumn INTEGER);
>
> If I do:
> INSERT INTO Tg (TgConfigId) VALUES (1);
>
> The following select works:
> SELECT * FROM Tg WHERE TgConfigId = 1;
>
> But if I insert '1' instead I have to select on '1', 1 no longer works. That 
> makes some sense, but not entirely, as what I inserted isn't an integer any 
> longer but a string. Why would I be allowed to insert values with '' in a 
> column that is specified to be an integer?
>
> Regards,
> Tobias
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

Right, I don't want to lessen scalability of my application. But I
also don't want to introcude redundancy just because some columns on
the the view lack a type.

I assume SQLite wants to adhere to the relational model which states:
The result of a select statement is another relation. And "A view is
just a relation (a table), but stores a definition, rather than a set
of tuples." (from chapter fundamentals of "Database Management
Systems" by Ramakrishnan & Gehrke, 2002).

So, for the "consumer" a view should behave like a table. This has the
following advances:
* Decoupling: Rename physical column names without breaking code which
reads views.
* Security: One can grant read permission on a view without granting
any permission to the underlying table.
* Simplicity: It's easier to write queries.
* Helps to avoud redundancy: Views can have calculated columns, like
age (from birthdate) or tax or rebate.

So its pretty clear to me that views should have the option to return
types. CAST could be a solution.
Determintation of the return type of a calculation could be another
step. That's from the basics of computer languages.

-S.

2010/5/7 Pavel Ivanov <paiva...@gmail.com>:
>> To Pavel: My application reads the column types out in order to pretty
>> print the values - as mentioned by Tom - but also to generate a dialog
>> for entering new data (of course combined with INSTEAD OF TRIGGERs).
>
> So as I see it: you have some universal code for displaying and
> inputing data. And you don't want to lessen scalability of your
> application by hard-coding the relation between column names and their
> data types. So you can create additional table that will contain this
> information. And I think this solution is better than just relying on
> declared type of columns - more straightforward and more
> understandable by somebody coming to your project in the future.
>
>
> Pavel
>
> On Thu, May 6, 2010 at 7:11 PM, Stefan Keller <sfkel...@gmail.com> wrote:
>> Thank you, Tom and Dan, for your constructive answers.
>>
>> To Pavel: My application reads the column types out in order to pretty
>> print the values - as mentioned by Tom - but also to generate a dialog
>> for entering new data (of course combined with INSTEAD OF TRIGGERs).
>>
>> I understand that it's difficult to implement result-types for
>> expressions in general. To me it would be enough if there would be at
>> least one way to assign result-types with a CAST "wrapper" as I have
>> mentioned before.
>>
>> Does anybody know whom to present this proposal in order remedy this
>> current inconsistency in SQLite? sqlite-dev?
>>
>> In the meantime I thought of a hack and to assign the result-type by
>> hand in the data dictionary after having created the VIEW.
>> Would this be a feasible work around?
>>
>> Yours, S.
>>
>> 2010/5/6 BareFeetWare <list@tandb.com.au>:
>>> On 06/05/2010, at 2:51 PM, Dan Bishop wrote:
>>>
>>>> BareFeetWare wrote:
>>>>>
>>>
>>>>> I've had the same issue. In the end I had to parse my view functions in 
>>>>> my own code and look for functions that give a particular type of result. 
>>>>> So, for instance, round() gives an integer, round(..., 2) gives a real, 
>>>>> concat() gives text. I also look for a cast(... as type) to use that 
>>>>> declared type. It's fiddly and I would have hoped SQLite would have at 
>>>>> least declared the type if a cast was present, but it seems not.
>>>>>
>>>
>>>> A CAST expression could be assigned a declared type
>>>
>>> Yes, that should be fairly easy to do in SQLite, simpler than my adding it 
>>> in my own code (which then requires my own parser - not a trivial exercise).
>>>
>>>> but it would be difficult to implement it for expressions in general.  Not 
>>>> only would you have to declare a type for every function
>>>
>>> Yes, probably half the functions don't have a consistently returned type 
>>> (eg length, concat). The ones that don't (eg coalesce) should just be left 
>>> to return a null (unknown) type. The schema developer can simply wrap those 
>>> expressions in a cast if they want a predetermined output type.
>>>
>>>> but you'd have to do it for operators as well, and what type should A * B 
>>>> be if A is "SMALLINT" and B is "UINT"?
>>>
>>> I hadn't thought of operators. As far as I know, mathematical operators (* 
>>> / + -) only work with integers and reals, Perhaps the result 

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

Thank you, Tom and Dan, for your constructive answers.

To Pavel: My application reads the column types out in order to pretty
print the values - as mentioned by Tom - but also to generate a dialog
for entering new data (of course combined with INSTEAD OF TRIGGERs).

I understand that it's difficult to implement result-types for
expressions in general. To me it would be enough if there would be at
least one way to assign result-types with a CAST "wrapper" as I have
mentioned before.

Does anybody know whom to present this proposal in order remedy this
current inconsistency in SQLite? sqlite-dev?

In the meantime I thought of a hack and to assign the result-type by
hand in the data dictionary after having created the VIEW.
Would this be a feasible work around?

Yours, S.

2010/5/6 BareFeetWare :
> On 06/05/2010, at 2:51 PM, Dan Bishop wrote:
>
>> BareFeetWare wrote:
>>>
>
>>> I've had the same issue. In the end I had to parse my view functions in my 
>>> own code and look for functions that give a particular type of result. So, 
>>> for instance, round() gives an integer, round(..., 2) gives a real, 
>>> concat() gives text. I also look for a cast(... as type) to use that 
>>> declared type. It's fiddly and I would have hoped SQLite would have at 
>>> least declared the type if a cast was present, but it seems not.
>>>
>
>> A CAST expression could be assigned a declared type
>
> Yes, that should be fairly easy to do in SQLite, simpler than my adding it in 
> my own code (which then requires my own parser - not a trivial exercise).
>
>> but it would be difficult to implement it for expressions in general.  Not 
>> only would you have to declare a type for every function
>
> Yes, probably half the functions don't have a consistently returned type (eg 
> length, concat). The ones that don't (eg coalesce) should just be left to 
> return a null (unknown) type. The schema developer can simply wrap those 
> expressions in a cast if they want a predetermined output type.
>
>> but you'd have to do it for operators as well, and what type should A * B be 
>> if A is "SMALLINT" and B is "UINT"?
>
> I hadn't thought of operators. As far as I know, mathematical operators (* / 
> + -) only work with integers and reals, Perhaps the result type could be set 
> to "numeric" or some other superclass. Or, to get real fancy, if the two 
> operands are declared integers, then the result is an integer; if one is real 
> then the result is real. AFAIK SMALLINT and UINT are both treated by SQLite 
> as integer, so the result would be an integer.
>
>> Should relational operators have a declared type of BOOLEAN?
>
> Good idea.
>
> Tom
> BareFeetWare
>
>  --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
>
> ___
> 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] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

I interpret the silence on the lis that anyone agrees that SQLite has a bug
because there seems to be no way to get VIEWS returning the column type if the
column is calculated or a function. This also breaks compatibility as
mentioned in
http://www.sqlite.org/datatype3.html ("SQL statement that work on
statically typed
databases should work the same way in SQLite.").
=> Time for a ticket?

-S.

2010/5/3 Stefan Keller <sfkel...@gmail.com>:
> Unfortunately the application which reads from this view needs that
> all columns are typed - even if the values types deviate from it - and
> I think this is a logical assumption. So, I fear I do have only one
> chance and SQLite doesn't let me do it:
>
> CREATE VIEW myview AS
>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>
> To me it seems like an inconsistency (or bug) when TABLES allow
> declaration of types and VIEWS only eventually.
>
> -S.
>
> 2010/5/3 Simon Slavin <slav...@bigfraud.org>:
>>
>> On 3 May 2010, at 6:14pm, Stefan Keller wrote:
>>
>>> But in SQLite if a view column comes from a function result or some
>>> computation, then the column type is NULL...!? It's not taking the
>>> result-type as mentioned in the manual
>>> (http://www.sqlite.org/lang_select.html) - even when I try to do a
>>> CAST in the projection clause, like this:
>>>
>>>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>>>
>>> I mean that a VIEW should behave like a (read only) TABLE in any case.
>>> => Is there a way to give such columns a type anyway?
>>
>> You get two chances to CAST, one when you define the VIEW, and another when 
>> you SELECT from the VIEW.  If one of them doesn't enforce the type of 
>> evaluation you want, the other probably will.  As you say, VIEW columns 
>> don't have types at all.
>>
>> 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] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

Unfortunately the application which reads from this view needs that
all columns are typed - even if the values types deviate from it - and
I think this is a logical assumption. So, I fear I do have only one
chance and SQLite doesn't let me do it:

CREATE VIEW myview AS
  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;

To me it seems like an inconsistency (or bug) when TABLES allow
declaration of types and VIEWS only eventually.

-S.

2010/5/3 Simon Slavin <slav...@bigfraud.org>:
>
> On 3 May 2010, at 6:14pm, Stefan Keller wrote:
>
>> But in SQLite if a view column comes from a function result or some
>> computation, then the column type is NULL...!? It's not taking the
>> result-type as mentioned in the manual
>> (http://www.sqlite.org/lang_select.html) - even when I try to do a
>> CAST in the projection clause, like this:
>>
>>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>>
>> I mean that a VIEW should behave like a (read only) TABLE in any case.
>> => Is there a way to give such columns a type anyway?
>
> You get two chances to CAST, one when you define the VIEW, and another when 
> you SELECT from the VIEW.  If one of them doesn't enforce the type of 
> evaluation you want, the other probably will.  As you say, VIEW columns don't 
> have types at all.
>
> 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] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

I have a question regarding VIEWs in SQLite:

It looks like if SQLite simply copies the column type from the
original table into the corresponding view column. And I know  SQLite
implements some 'loose column typing'. That's ok so far.

But in SQLite if a view column comes from a function result or some
computation, then the column type is NULL...!? It's not taking the
result-type as mentioned in the manual
(http://www.sqlite.org/lang_select.html) - even when I try to do a
CAST in the projection clause, like this:

  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;

I mean that a VIEW should behave like a (read only) TABLE in any case.
=> Is there a way to give such columns a type anyway?

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