java - SQLite create dynamic table (or view?) -

-

i have 3 tables:

table.keys, table.tags, table.values

table.keys create table statement:

    createtablestatement = "create table " + tables.keys + "("             + keyscolumns._id + " integer primary key autoincrement,"             + keyscolumns.key + " text not null,"             + "unique ("             + keyscolumns.key             + ") on conflict ignore"             + ");";     execsql(sqlitedatabase, createtablestatement);

table.tags create table statement:

    createtablestatement = "create table " + tables.tags + " ("             + tagscolumns._id + " integer primary key autoincrement,"             + tagscolumns.name + " text not null,"             + "unique ("             + tagscolumns.name             + ") on conflict ignore"             + ");";     execsql(sqlitedatabase, createtablestatement);

table.value create table statement:

    createtablestatement = "create table " + tables.values + " ("             + valuescolumns._id + " integer primary key autoincrement,"             + valuescolumns.key_id + " text not null,"             + valuescolumns.tag_id + " text not null,"             + valuescolumns.value + " text not null,"             + "unique ("             + valuescolumns.key_id + ", " + valuescolumns.tag_id             + ") on conflict replace"             + ");";     execsql(sqlitedatabase, createtablestatement);

if following join:

tables.keys + " join " + tables.values + " on " + values.key_id + " = " + keys.column(keys._id) + " join " + tables.tags + " on " + values.tag_id + " = " + tags.column(tags._id);

i duplicate rows of course because result is

key | tag | value =================     |     |

what accomplish query , cursor table or view no duplicate rows following schema:

key | tag 1 | tag 2 | ... | tag n =================================     |       |       |     |

not keys must have values each tag, keys can have values.

i'm not sure how accomplish this. i'm not sure start.

in meantime have created table stores tag values know exist.

but feel inefficient because @ time have 'n' number of new tag values why able create view or table schema listed.

sqlite has no pivot functions; have in 2 steps.

first, possible tags:

select _id, name tags order name;

then, using returned data, construct query looks each possible tag each key:

select key,        (select value         values         key_id = keys._id           , tag_id = 111        ) tag_111,        (select value         values         key_id = keys._id           , tag_id = 222        ) tag_222,        ... keys;

Comments

Post a Comment

Popular posts from this blog

javascript - ScrollTo Effect (href to div) -

-
i can't seem find working simple scrollto effect. want basic, it's when click on link in nav bar, must take me selected div id scrollto effect. this code have. <nav> <ul> <li><a href="#home">home</a></li> <li><a href="#about">about</a></li> <li><a href="#contact">contact</a></li> </ul> </nav> <section id="home"> content </section> <section id="about"> content </section> <section id="contact"> content </section> js: <!----- navegación slide ---> $(document).ready(function() {   $('a[href*=#]').each(function() {     if (location.pathname.replace(/^\//,'') == this.pathname.replace(/^\//,'')     && location.hostname == this.hostname     && this.hash.replace(/#/,'') ) {       var $targetid = $(this.hash), $targetancho...
Read more

javascript - ng-class not responding to change in model in Angular? -

-
having searched extensively, got a ternary in templates , getting partially functional results, i'm 99% sure i'm doing 99% right, but...obviously not completely, i'm here. the basic html structure: <ul> <li class="week-row" ng-repeat="week in weeks" id="{{$index}}"> <ul class="tiles" ng-class="{ 'expanded': week.isopen, 'hidden': !week.isopen }"> <li class="day-tile" ng-class="{'active': (activeday == $parent.$index + '-' + $index) }" id="{{$parent.$index + '-' + $index}}" ng-repeat="day in week.days" ng-click="fn2({ week: $parent.$index, day: $index })"> <!-- moved ng-class here, , works? --> <div>some stuff in here</div> </li> </ul...
Read more

javascript - document.registerElement extending HTMLInputElement prototype while using custom tag name to avoid implicit browser style -

-
my goal two-fold: display html element keeps track of 'checked' state , triggers 'change' events without having manually implement code myself. not fight browser's implicit styling of element -webkit-appearance: none , more fussing. i need support current, stable channel of google chrome. such, not have concern compatibility non-modern browsers. i've approached problem using document.registerelement . method, register new tag, custom-checkbox , , inform browser tag inherit input. from there, need setup fact input's type checkbox. so, set type in createdcallback method ran when html element created. this code has issues: it works when not use custom tag , instead leverage is property inform browser input 'is' custom-checkbox . however, using <input> tag causes browser take on styling element. not wish have happen. when using custom tag, createdcallback method not ran. such, element not setup fact checkbox , not fire ...
Read more