Announcement

Collapse
No announcement yet.

SQLite Table Assistance

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLite Table Assistance

    What is the best method to store matrix data in an SQLite Db?
    The data is spread sheet type data consisting of cols and rows of primarily
    numerical data.
    I could have literally hundreds of the same type so I don't really want
    to use a single table for each type and then have to id each row.

    My first thought was Tables for each type group:
    TYPE1
    TYPE2

    Storing the data as a csv BLOB.

    Each Table will also have columns for Client, Project, and Case so I can
    search on a combination of those three.
    TABLE: TYPE1
    rowid,CLIENT,PROJECT,CASE,CSVDATA

    Am I in the ballpark??

    James

  • #2
    I'd check your DBMS for "searchability" ("WHERE..." ) of BLOB or other 'long binary' data... I know Oracle can't search it or substring it, and generally if Oracle can't do it nothing can.

    But if you don't want to search on the data values themselves...that is, you only search on client and/or case and/or project, then a 'long' type is as good as anything.

    I am having trouble grasping the data concept. Is what you have "for any unique client + project + case there is exactly one NxM 'matrix' ? if so, what is wrong with one table row for each row, column of data? eg
    Code:
    client,project,case,X,Y 
    client,project,case,X,Y 
    client,project,case,X,Y 
    ...
    MCM
    Michael Mattias
    Tal Systems Inc. (retired)
    Racine WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      No I do not need to search on the data itself.
      Each client/project may have several cases of TYPE1 data where TYPE1
      data is a col/row table of data.

      Blobs were the only way I could figure out how to store the data, but being
      an SQLite novice, I was inquiring if there might be another (better?) way.

      I am having trouble grasping the data concept. Is what you have "for any unique client + project + case there is exactly one NxM 'matrix' ? if so, what is wrong with one table row for each row, column of data? eg
      Yes but each unique data table could have hundreds(possibly thousands) of rows so instead of returning just one row on a query row with a blob you have hundreds.

      James

      Comment


      • #4
        Another method I had thought of and is basically what is done using flat files:
        Client_ID_Project_ID_CASE_ID_TYPE1_TABLE where _TYPE1_TABLE is a specific
        Filename Ext. I just felt the BLOB method was better.

        James

        Comment


        • #5
          Well, then a' long binary' sounds like a good plan.

          I would not store as comma delimited, however, Just store it as "the array data" ...

          Code:
             REDIM Sheet (rows, cols) AS SINGLE/DOUBLE/INTEGER WHATEVER 
             REDIM SHeet (0,0)          ' just so it exists 
             CALL FillSheet (Sheet())   ' will redim to correct size whilst filling
             BlobDataAsString = PEEK$ ( VARPTR(Sheet(0,0), ARRAYATTR(sheet(),4) * ARRAYATTR(Sheet(),5)
          
          TABLE: 
          client
          case
          project
          nRow 
          ncol   (not strictly needed, but will be handy .....
          Blobdata
          haing BOTH nRow and nCOl will be handy as in when you retrieve the data....
          Code:
          Sql = " Select nrow, ncol, CAST (blobdata AS VARCHAR) from mytable  " _
              &  " where client = X and project=Y and case = Z"
          Execute  SQL
          
          REDIM   Sheet (nRow-1, nCol-1) AS thedatatype AT STRPTR(blobdata)
          (Assuming you can get back the blobdata as a character string using CAST or something else).
          Michael Mattias
          Tal Systems Inc. (retired)
          Racine WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            Originally posted by jcfuller View Post
            I could have literally hundreds of the same type so I don't really want to use a single table for each type and then have to id each row.
            What do you mean by "have to id each row"?

            Originally posted by jcfuller View Post
            TABLE: TYPE1
            rowid,CLIENT,PROJECT,CASE,CSVDATA

            Am I in the ballpark??
            If you are just looking to store/retrieve the data and not do any processing with SQL, that should work.

            You might consider changing it like so though:

            TABLE: TYPE1
            rowid,CLIENT,PROJECT,CASE,CSVDATA_ID (foreign key on CSVDATA(rowid))

            TABLE: CSVDATA
            rowid, CSVDATA

            In case you are reading many rows from TYPE1, this will save you from reading all the BLOB data.
            Last edited by Bern Ertl; 4 May 2009, 12:47 PM.
            Bernard Ertl
            InterPlan Systems

            Comment

            Working...
            X