yes, that's what I am thinking of too. This big table is in charge of store all matrix, keep track of all index changes, and rollback when needed. I will only extract a tiny part from this big table in format like a sparse matrix, and put it in C array, then the available numerical routines, such as matrix inversion, can be applied to the C array. So this big table for all matrix is a information manager or a data warehouse in my thinking. I am not sure how easy it will be to use this big table as a central warehouse for now.
On Fri, Oct 16, 2009 at 3:50 PM, Stephan Wehner <stephanweh...@gmail.com>wrote: > On Fri, Oct 16, 2009 at 1:41 PM, Michael Chen > <vancouver.mich...@gmail.com> wrote: > > rdbms is indeed not a place for store a single sparse matrix like this. > > However I have hundreds of them, and I need to break them and recombine > them > > frequently; furthermore, I need to drop a few rows or columns > successively, > > and need to be able to trace back what's a row's original index. I think > > sqlite in my C/C++ code will save lots of boring reindexing coding, and > it > > is more robust. > > Would you not store all the matrices in one table, and use a > "matrix_id" column to identify which records are for one matrix ? > > Stephan > > > > > On Fri, Oct 16, 2009 at 1:14 PM, P Kishor <punk.k...@gmail.com> wrote: > > > >> This is not the answer you are looking for, and there are SQL geniuses > >> on this list who will help you better, but really, is an rdbms really > >> a good place to store a matrix the way you are trying to do? So > >> convoluted. > >> > >> My approach, if I really was determined to store it in sqlite, would > >> be to flatten the matrix in my code, and then stuff that into a table. > >> Retrieve it and deserialize it as needed. For example, Perl's freeze, > >> thaw are tailor made for this kind of stuff. > >> > >> > >> On Fri, Oct 16, 2009 at 1:09 PM, Michael Chen > >> <vancouver.mich...@gmail.com> wrote: > >> > --this is my first version for the purpose of storing sparse numerical > >> > matrix in sql > >> > --please let me know how to fix the bug at the end of the file, and > how > >> to > >> > tune the performance > >> > --or any better reference, thanks! > >> > > >> > .explain -- return result in more > readable > >> > format > >> > .echo on -- echo the sql stmnt > >> > > >> > --I plan to store my sparse matrix (3 x 4) in table like this: > >> > -- [ 1 2 0 0 ] > >> > -- [ 0 3 9 0 ] > >> > -- [ 0 1 4 0 ] > >> > -- > >> > -- is a three-by-four matrix with six nonzero elements, so > >> > -- > >> > -- A = [ 1 2 3 9 1 4 ] // List of non-zero matrix element in order > >> > -- IA = [ 1 3 5 7 ] // IA(i) = Index of the first nonzero > element > >> of > >> > row i in A > >> > -- JA = [ 1 2 2 3 2 3 ] // JA(i) = Column position of the non zero > >> element > >> > A(i) > >> > -- > >> > -- Note: In this example, the row and line index begin by 1 and not 0. > >> > > >> > create table rowids( > >> > rows integer primary key -- there should be an easy way for a > sequence > >> > 1..M? > >> > ); > >> > > >> > insert into rowids values (1); > >> > insert into rowids values (2); > >> > insert into rowids values (3); > >> > > >> > create table colids( > >> > cols integer primary key -- there should be an easy way for a > sequence > >> > 1..N? > >> > ); > >> > > >> > insert into colids values (1); > >> > insert into colids values (2); > >> > insert into colids values (3); > >> > insert into colids values (4); > >> > > >> > > >> > create table matrix( > >> > rowid integer reference rowids, > >> > colid integer reference colids, > >> > value real non null > >> > ); > >> > > >> > insert into matrix values (1,1,1); > >> > insert into matrix values (1,2,2); > >> > insert into matrix values (2,2,3); > >> > insert into matrix values (2,3,9); > >> > insert into matrix values (3,2,1); > >> > insert into matrix values (3,3,4); > >> > insert into matrix values (2,4,0); --this entry is inserted on purpose > >> > > >> > > >> > select * from matrix; > >> > > >> > > >> > --A rowwise sparse matrix is then: > >> > > >> > create temp view rowwiseA as > >> > select value as A, colid as JA from matrix > >> > where value != 0 > >> > order by rowid, colid > >> > ; > >> > > >> > create temp view rowwiseB as > >> > select rowid, count(rowid) as ct > >> > from matrix > >> > where value != 0 > >> > group by rowid > >> > order by rowid > >> > ; > >> > > >> > create temp view rowwiseC as > >> > select a1.rowid, sum(a2.ct) +1 as JA > >> > from rowwiseB a1, rowwiseB a2 > >> > where a2.rowid < a1.rowid > >> > group by a1.rowid > >> > ; > >> > --this is not quite right yet, the first entry and last entry of JA > >> vector > >> > is not here. > >> > > >> > select * from rowwiseA; > >> > select * from rowwiseB; > >> > select * from rowwiseC; > >> > > >> > > >> > create temp view rowwiseB as > >> > select count(rowid) as ia from matrix > >> > where value != 0 > >> > group by rowid > >> > order by rowid > >> > ; > >> > > >> > > >> > > >> > --Best regards, > >> > --Michael Chen > >> > --Google Voice Phone.: 847-448-0647 > >> > _______________________________________________ > >> > sqlite-users mailing list > >> > sqlite-users@sqlite.org > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > >> > >> > >> > >> -- > >> Puneet Kishor http://www.punkish.org > >> Carbon Model http://carbonmodel.org > >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > >> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor > >> Nelson Institute, UW-Madison http://www.nelson.wisc.edu > >> ----------------------------------------------------------------------- > >> Assertions are politics; backing up assertions with evidence is science > >> ======================================================================= > >> Sent from Madison, WI, United States > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > > > -- > > Best regards, > > Michael Chen > > Google Voice Phone.: 847-448-0647 > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Stephan Wehner > > -> http://stephan.sugarmotor.org (blog and homepage) > -> http://loggingit.com > -> http://www.thrackle.org > -> http://www.buckmaster.ca > -> http://www.trafficlife.com > -> http://stephansmap.org -- http://blog.stephansmap.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Michael Chen Google Voice Phone.: 847-448-0647 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
