Announcement

Collapse
No announcement yet.

SQLite Table Assistance

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

  • Bern Ertl
    replied
    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, 11:47 AM.

    Leave a comment:


  • Michael Mattias
    replied
    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).

    Leave a comment:


  • jcfuller
    replied
    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

    Leave a comment:


  • jcfuller
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • jcfuller
    started a topic SQLite Table Assistance

    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
Working...
X