SQLite and Array values?

Databases
1

Hi Folks,

I need to create a database with 2 tables.

The first table contains 2 Text columns:
TapeSetName that is the Key
TapeID which is a list of tape IDs that are members of that set

The second contains 4 columns:
TapeID that is the key
Archives that is a list of archive IDs
Created is a list of SQL dates that track with the entries of the Archives column
ArchiveOffset is a list of numeric values that track with the entries of the Archives column
Label is a list of text labels that also track with the Archives entries

From the digging that I’ve done, it doesn’t appear that there is such an element as an array in SQLite.

Does anyone have a solution that will allow the tracking of the elements in the columns agains the Archives column without requiring 4 separate additional tables?

2

I think the “right” way to do it is to create 4 additional tables, but from the looks of it, you could just store it as tab-delimited text, no?

3

Thanks, Kem. My concern about concatenated text is that something could happen to cause the values to get out of sync. To better define the situation since you may have a better suggestion than where I’m heading -

The application environment allows a user to create tape collections, or sets. In each set there can be any number of physical tapes. On each tape, there can be one or more archive entities. Each archive entity has a unique identifier, a creation date, a human readable label and a block offset value to define where on a given tape the archive entity starts.

Currently, I’m tracking these through text files in a folder hierarchy, but tracking 1,000’s of tapes in this manner is now taking too long during startup and scanning for customer satisfaction.

4

Separate tables are the way to do this with a relational database.

Tab-delimited text in a column is a bad idea and violates one of the normalization rules. Plus it would make it hard to query.

5

See Paul’s response. ^

6

Looking at this outside of the app makes it look like I need to start at the bottom and work up and maybe it’s only one table.

Archives table:
ArchiveID As VarChar
VolumeCount As Number
Offset As Number
Label As Text
Created As SQLDate
OnTape As VarChar
InTapeSet As Text

Since the Archive entity is the most important component, I can then gather the tapes and tape sets from the archives and I don’t need to track the tapes and tape sets separately.

Or, am I still too buried in my own confusion?

7

Maybe I misunderstand something but ….

There’s a “TapeSet” that has a name & tapeSetID (maybe a number of tapes that make up the set although this can be derived)
Then a list of Tapes that make up that TapeSet ?
And each tape in the set has a label, created date, tape ID, etc ?

8

tape
intID – counter
strName – varChar
dteCreated – date
strNotes – varChar

tapeSet
intID – counter
intTapeID – what tape this set belongs to
strName – varChar
dteCreated – date
strNotes – varChar

Maybe misunderstand you, but I always make such text based design of the various databases as I create.
Criteria:

  1. Shall be possible for any person to understand. (With the same knowledge as I have.)
  2. Shall not be site / project / customer specific. As open as possible.
  3. Possible to use in any future project with no or as little modification as possible.
  4. Easy and open code, no costumer name is written in the code or in the documentation.
  5. Copy/paste friendly.

If you write simple code, you need none or little documentation as the code is fairly clear as it is.

“SELECT intID, strName FROM [tape] ORDER BY strName;”
“SELECT t.intID, t.strName FROM [tape] t ORDER BY t.strName DESC;”
“SELECT t.intID, t.strName, ts.intID, ts.strName FROM tape t, tapeSet ts WHERE t.intID = ts.intTapeID ORDER BY ts.dteCreated;”

The above lines works with little or no modification in very many languages and for many kind of different databases.

I hope this helps!!

9

[quote=61264:@Norman Palardy]Maybe I misunderstand something but ….

There’s a “TapeSet” that has a name & tapeSetID (maybe a number of tapes that make up the set although this can be derived)
Then a list of Tapes that make up that TapeSet ?
And each tape in the set has a label, created date, tape ID, etc ?[/quote]
And each tape can also contain one of more archive entities which also have a unique ID, label, creation date, and other elements.

From the available data, I need to be able to determine:

  • What are the tape set names
  • What tapes are in a tape set
  • What archives are on those tape(s)

At the top level information display
. we show a list of tape sets
When the user selects a tape set
. we change the list to show the archives on the tape(s) in that tape set
What we don’t show is that we then create an array of all of the tapes in a tape set containing arrays of the archives on the tapes.

That’s close to the manner in which I write everything. Where I’m getting hung up is that a single data table entry could have multiple sub entries - i.e. a Tape Set could have multiple tapes which, in turn, could have multiple archives. But, each tape and archive can only belong to one tape set.

10

No idea what n “archive entity” might be but that’s not important

Model it just like we talked about

Table TapeSet
name & tape set ID

Table tape
tape set ID, label, created date, tape ID

Table Archive Entities
tape ID, unique ID, label, creation date

Note - ID’s are unique and NOT for “user consumption” they are for software purposes only
If the user needs some other “ID” then store that but it is NOT used internally to identify items

IE/ from my old days we used to have tape sets and they might be composed of “tape 1” “tape 2” etc for a set made on a specific date
Those “user labels” could be reused as much as you wanted ■■■■ internally the software can (& needs to be able to) distinguish between “tape 1 in archive set 1” and “tape 1 in archive set 300”

11

An archive entity is a single container of files (like a ZIP file or a tar ball). My issue is that I need to be able to track multiple tapes per tape set and then multiple archives per tape. This is where I started with not being able to add an array of tape IDs to the Tape Sets table.

For example:

Tape Set “Daily 2nd Unit - Camera 2 - Beach - 11-23-2013” could contain tapes:

  • 5E3400E1334A:0
  • 5E340103223E:0
  • 5E340466EEA0:0
  • 5E340466EEA0:1

Each tape gets it’s ID from the first archive entity’s ID. In the event an archive entity spans more than 2 tapes, the tape ID could be replicated, so we use a colon folioed by the vol number to further identify the tapes in such a spanned set.

Tape ID 5E3400E1334A:0 Could then contain archive entities:

  • 5E3400E1334A:0
  • 5E3400E14221:1131144
  • 5E3400E1A7EE:1799100
  • 5E340103223E:20032212

(the number after the colon in this case is the block offset of the archive entity on the tape)
This last archive entity then continues onto the next tape 5E340103223E:0

  • 5E340103223E:0
  • 5E340466EEA0:46623222

Which continues onto tape 5E340466EEA0:0

  • 5E340466EEA0:0
  • (you can’t have a single bulleted line)

Which continues onto tape 5E340466EEA0:1

  • 5E340466EEA0:0
  • 5E3404811E02:1211782

I also need to be able to track each archive’s ASCII label as well as the date it was created and I may want to extend it to contain the size of the specific archive entity.

That’s why I back tracked to the archives table as the total answer since the archive entity is the lowest common denominator. To repeat:

  • ArchiveID As VarChar
  • VolumeCount As Number
  • Offset As Number
  • Label As Text
  • Created As SQLDate
  • Size As Number
  • OnTape As VarChar
  • InTapeSet As Text

This would allow me to deal with a single table while still being able to generate the list of tape sets much more chicly that parsing the catalog folders when there are 1,000’s of tape sets in a system’s records.

12

A single table & parsing will make for more work than necessary

A handful of tables (as given) will allow you to track an unlimited number of tape sets, unlimited number of tapes per set & unlimited number of archive entities per tape

Its all nice simple relations and you can put whatever nice friendly identifiers on things your users want (or that you want)

For instance
An invoice is nice & simple
It has a header + an “array” of details
Well to model that you have

Table Invoice
invoice id
other “ONE TIME” data (date time, buyer etc)

Table InvoiceDetails
invoice id (so we know what invoice these details belong to)
line item details (sku, product name, purchase price, qty, etc )

You have an array with an array with an array
So extend this simple modelling technique (like I suggested) so

So lets say the invoice is not simple products but each line item is a project with subtasks)
IE

Biil To Norman Palardy Project 1 $500 - implement XYZ $100 - implement XYZ $100 - implement XYZ $100 - implement XYZ $100 - implement XYZ $100 Project 2 $500 - implement abc $100 - implement 123 $100 - implement def $100 - implement ghi $100 - implement jkl $100

Table Invoice
invoice id
other “ONE TIME” data (date time, buyer, bill to, etc)

Table InvoiceDetails
invoice id (so we know what invoice these details belong to)
invoice detail ID (so we can see all the subtasks they made this line up)
total price (total of all subtasks)

Table InvoiceSubDetails
invoice detail id (so we know what invoice detail these belong to)
units, sku, extended price and whatever else

Now I have an array of arrays (an invoice has an array of invoice details that each have an array of sub details)

13

Actually, that did work out better after I’d had a night not thinking about it. As I said, my own confusion had me … well, confused.

The archives table is linked to the tapes table which is linked to the tapesets table.