a type column to go with variant data would probably be best... how many columns do you have that are actually self-described type&data required?
could just serialize it to a blob; include type, and the value... kinda hard to select for a value that way... at least if it's a parallel type the value can be a simple representation. variant types can either be handled with a container that contains a type indicator (VB-like) or just kept as a string, and when required to be a value, parsed to see if it can apply... why not just break out separate tables per type that link back to the data row id? For options, started with a value table that was (value_id,int,string,blob), later broke it out to 3 tables (option_id,int) (option_id,string) (option_id,blob) ... I used to create the value_id and store that back in the option map; realized I could just use the map id to get the value instead... THe other type of variable data I ran into was a bank ledger that had a 'operation' field and a value field, and a couple related account IDs.. where the procedure to do with the value was determined variably rather than the data type... If I were to do it again, I'd break out transaction types to separate tables... yes, it complicates queireis requiring joins, but for later general tool use it's easier to cope with. How many applications are really storing variable types of data? Will new applications also write new kinds of data that were previously ununderstood? Will old things still work? On Wed, Nov 26, 2014 at 9:25 AM, Darko Volaric <li...@darko.org> wrote: > You wrote: > > "From the tone of your last post (if I am reading correctly) I understand > that you have your mind set on finding a way that you have thought about a > lot lying in your bed late at night, you have rolled it around in your head > and you just "feel" this should be doable and will be so elegant a > solution. " > > That references only me, in a condescending way, without referencing the > actual issue. That's ad-hominem. You've made it clear by your posts you > don't understand the SQLite issue I'm talking about so I'm not discussing > that further, as I have already said. If you want to address anything to do > with me, then email me directly at take it off list, so as to stop wasting > other people's time. > > On Wed, Nov 26, 2014 at 8:20 AM, RSmith <rsm...@rsweb.co.za> wrote: > > > > > On 2014/11/26 15:58, Darko Volaric wrote: > > > >> I'm not looking for confirmation of ideas, on the contrary, people seem > >> to want to push their own ideas about a database should be used and how > I'm > >> not using it correctly, when that is irrlevent to the issue I'm > discussing. > >> Maybe more focus on the technical facts and less on divining what I > think > >> at night in bed and try framing your arguments based on those technical > >> facts rather than ad-hominem attacks. > >> > > > > I am sincerely sorry if you construed my reply as anything remotely ad > > hominem - it surely wasn't, and it's not pushing ideas on you, it's > giving > > advice or alternates because your ideas are short-sighted and hard to > > implement. We are however very nice on here since sharing knowledge is a > > passion, and that's why we say nice things like we understand how it > feels > > to have ideas and then offer some advice... not because we spend our days > > divining about your life. You throw everyone's advice back in their > faces > > and are arrogant about it - well, even to that we are still nice and > > willing to answer the questions - kindly accept it in that spirit. > > > > > >> But I don't intend to argue this point any further since I'm merely > >> looking for advice about how the database engine is implemented, not > about > >> how it's used or how I'm using it. > >> > > > > Ok, sticking to the facts, the database engine is implemented in a way > > that makes your original suggested options pan out like this: > > 1 - very easy for the engine, very work intensive for you. > > 2 - still easy for the engine, although it will lose most RDBMS > > querying value, and still cumbersome for you (Maybe best to use Virtual > > tables to implement this), and > > 3 - impossible without a dedicated fork, and even then very difficult. > > > > I wouldn't personally pick any of those, but if those were the only > > options in life and I had to pick one, knowing SQLite, I'd probably lean > > more towards option 2 than the others. > > > > SQLite is "loosely typed", but it is still typed, and the typing > mechanism > > is not open to the API and every one of the hundreds of core functions in > > SQLite are specifically coded to dance with those few primary types. > > Adding/Altering it must always be the very last option on any list. > > > > > > Good luck, > > Ryan > > > > _______________________________________________ > > 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
