Using key/value idea to store data & creating normal table VIEW?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Using key/value idea to store data & creating normal table VIEW?

Robert M. Münch
Hi, suppose I store my data like this:

Table A: table, column, data

Now I want to create a VIEW named A.table, with columns A.column and rows A.data

Is that possible?

Background: We have one table where columns can be added/removed. We want to use the session extension to keep track of changes to our database. But the session extension can't handle schema changes.

Thanks a lot.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

J Decker
Probably need to use some CTE expressions to tackle that.

https://sqlite.org/lang_with.html

On Fri, Jun 16, 2017 at 10:59 PM, Robert M. Münch <
[hidden email]> wrote:

> Hi, suppose I store my data like this:
>
> Table A: table, column, data
>
> Now I want to create a VIEW named A.table, with columns A.column and rows
> A.data
>
> Is that possible?
>
> Background: We have one table where columns can be added/removed. We want
> to use the session extension to keep track of changes to our database. But
> the session extension can't handle schema changes.
>
> Thanks a lot.
>
> --
>
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
>
> Saphirion AG
> smarter | better | faster
>
> http://www.saphirion.com
> http://www.nlpp.ch
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

wmertens
Could you not combine the data on the app side?

On Sat, 17 Jun 2017, 9:15 AM J Decker, <[hidden email]> wrote:

> Probably need to use some CTE expressions to tackle that.
>
> https://sqlite.org/lang_with.html
>
> On Fri, Jun 16, 2017 at 10:59 PM, Robert M. Münch <
> [hidden email]> wrote:
>
> > Hi, suppose I store my data like this:
> >
> > Table A: table, column, data
> >
> > Now I want to create a VIEW named A.table, with columns A.column and rows
> > A.data
> >
> > Is that possible?
> >
> > Background: We have one table where columns can be added/removed. We want
> > to use the session extension to keep track of changes to our database.
> But
> > the session extension can't handle schema changes.
> >
> > Thanks a lot.
> >
> > --
> >
> > Robert M. Münch, CEO
> > M: +41 79 65 11 49 6
> >
> > Saphirion AG
> > smarter | better | faster
> >
> > http://www.saphirion.com
> > http://www.nlpp.ch
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

wmertens
And another option of course is to store all those extra columns as JSON,
which you can query with the JSON1 extension. You can even index on the
extracted values.

On Sat, 17 Jun 2017, 9:53 AM Wout Mertens, <[hidden email]> wrote:

> Could you not combine the data on the app side?
>
> On Sat, 17 Jun 2017, 9:15 AM J Decker, <[hidden email]> wrote:
>
>> Probably need to use some CTE expressions to tackle that.
>>
>> https://sqlite.org/lang_with.html
>>
>> On Fri, Jun 16, 2017 at 10:59 PM, Robert M. Münch <
>> [hidden email]> wrote:
>>
>> > Hi, suppose I store my data like this:
>> >
>> > Table A: table, column, data
>> >
>> > Now I want to create a VIEW named A.table, with columns A.column and
>> rows
>> > A.data
>> >
>> > Is that possible?
>> >
>> > Background: We have one table where columns can be added/removed. We
>> want
>> > to use the session extension to keep track of changes to our database.
>> But
>> > the session extension can't handle schema changes.
>> >
>> > Thanks a lot.
>> >
>> > --
>> >
>> > Robert M. Münch, CEO
>> > M: +41 79 65 11 49 6
>> >
>> > Saphirion AG
>> > smarter | better | faster
>> >
>> > http://www.saphirion.com
>> > http://www.nlpp.ch
>> >
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> >
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Igor Tandetnik-2
In reply to this post by Robert M. Münch
On 6/17/2017 1:59 AM, Robert M. Münch wrote:
> Hi, suppose I store my data like this:
>
> Table A: table, column, data
>
> Now I want to create a VIEW named A.table, with columns A.column and rows A.data
>
> Is that possible?

I don't think so. The number and names of columns in the view are determined at the time CREATE VIEW statement is executed. I don't believe you can have a view whose schema magically changes as the data in the underlying table changes.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Robert M. Münch
In reply to this post by wmertens
On 17 Jun 2017, at 9:53, Wout Mertens wrote:

> Could you not combine the data on the app side?

We are currently holding all data on the app side and want to get it into SQLite to make use of it's querying features. So, that would be a step back.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Robert M. Münch
In reply to this post by wmertens
On 17 Jun 2017, at 9:57, Wout Mertens wrote:

> And another option of course is to store all those extra columns as JSON,
> which you can query with the JSON1 extension. You can even index on the
> extracted values.

That's a very interesting idea. I'm going to check it out. So, the idea would be to just store JSON data in table A that contains column/value/...?

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Robert M. Münch
In reply to this post by Igor Tandetnik-2
On 17 Jun 2017, at 14:10, Igor Tandetnik wrote:

> I don't think so. The number and names of columns in the view are determined at the time CREATE VIEW statement is executed.

That won't be a problem as we can update the VIEWs. The question is, how does a query look like that transposes the data from a flat to column structure?

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Igor Tandetnik-2
On 6/17/2017 8:17 AM, Robert M. Münch wrote:
> On 17 Jun 2017, at 14:10, Igor Tandetnik wrote:
>
>> I don't think so. The number and names of columns in the view are determined at the time CREATE VIEW statement is executed.
>
> That won't be a problem as we can update the VIEWs. The question is, how does a query look like that transposes the data from a flat to column structure?

By the same token, I don't believe such a query exists. The schema of the resultset can be derived entirely from the syntax of the SELECT statement, without consulting underlying data. So you could call, say, sqlite3_column_count() right after preparing the statement, before it has a chance to look at the actual contents of the table.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Mike Henry
In reply to this post by Robert M. Münch
what does this error mean and how can I fix it?java.sql.SQLException: opening db: './MHDB.db': open failed: EROFS (Read-only file system)


On Sat Jun 17 2017 07:17:30 GMT-0500 (Eastern Standard Time), Robert M. Münch <[hidden email]> wrote:

On 17 Jun 2017, at 14:10, Igor Tandetnik wrote:

> I don't think so. The number and names of columns in the view are determined at the time CREATE VIEW statement is executed.

That won't be a problem as we can update the VIEWs. The question is, how does a query look like that transposes the data from a flat to column structure?

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Robert M. Münch
In reply to this post by Igor Tandetnik-2
On 17 Jun 2017, at 14:25, Igor Tandetnik wrote:

> By the same token, I don't believe such a query exists.

Hi, I think such a query exists, here we go:

Columns are: col_id, value, rec_id

col-1 1 1
col-1 2 2
col-1 3 3
col-1 4 4
col-1 5 5
col-1 6 6
col-1 7 7
col-1 8 8
col-1 9 9
col-2 1 1
col-2 2 2
col-2 3 3
col-2 4 4
col-2 5 5
col-3 1 1
col-4 1 1
col-5 1 1
col-6 1 1
col-7 1 1
col-8 1 1
col-9 1 1

Which is a classical entity-attribute-value (EAV) model, taking normalization to the extreme and no longer uses columns in the traditional way. Instead, every single value is stored in its own row. Besides the value, the row also has a column to specify which attribute the value represents and a third column to identify what entity the values belongs to. Ultimately, a three column table can hold any data without ever needing to change the table definition.

To get a traditional VIEW on this, we want to transpose the data, or create a pivot of it. Here is a pretty simple structure of such a VIEW for the first three columns:

select rec_id
, max(case when col_id = "col-1" then value end) col1
, max(case when col_id = "col-2" then value end) col2
, max(case when col_id = "col-3" then value end) col3
from products
group by rec_id

With columns: rec_id, col1, col2, col3

1 1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6
7 7
8 8
9 9

That's what I need to avoid schema changes on tables with dynamic attributes and make it compatible with the session extension.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Igor Tandetnik-2
On 6/17/2017 10:36 AM, Robert M. Münch wrote:
> To get a traditional VIEW on this, we want to transpose the data, or create a pivot of it. Here is a pretty simple structure of such a VIEW for the first three columns:

For the first three (or any fixed N) columns, yes. But I thought you wanted a view that somehow automatically becomes wider or narrower as rows are inserted or deleted in the underlying table. I don't believe such a thing is possible.

> That's what I need

So you already know the answer to your question, then? What else would you like assistance with?


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Robert M. Münch
On 17 Jun 2017, at 19:29, Igor Tandetnik wrote:

> For the first three (or any fixed N) columns, yes. But I thought you wanted a view that somehow automatically becomes wider or narrower as rows are inserted or deleted in the underlying table. I don't believe such a thing is possible.

That's right, the query has to be build fitting the structure.

> So you already know the answer to your question, then? What else would you like assistance with?

Well, the question for me is, which approach will be faster?

A table with (entity, value) and a query like I posted:

select rec_id
, max(case when col_id = "col-1" then value end) col1
, max(case when col_id = "col-2" then value end) col2
, max(case when col_id = "col-3" then value end) col3
from products where col1 = "ABC"
group by rec_id


or using the JSON1 extension and building a query like:

select rec_id
, json_extract(json_value,"$.col1") as col1 from json_products
, json_extract(json_value,"$.col2") as col2 from json_products
from products, json_each(col1)
where json_each.value = "ABC"

The JSON data might be 20.000 records each JSON object/array containing 5 - 50 fields.

Are there are any benchmarks available?

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using key/value idea to store data & creating normal table VIEW?

Robert M. Münch
On 19 Jun 2017, at 10:47, Robert M. Münch wrote:

> Well, the question for me is, which approach will be faster?
> ...

Hi, answering my own question as I tried it with 50000 records and 50 columns. The JSON approach is way faster up to factor 1000.

Viele Grüsse.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Loading...