SQLite Table ID

[Michael Mattias]

Wow, lots of catalog functions available thru pragmas..http://www.sqlite.org/pragma.html

[Chris Holbrook]

Shhh - they'll all want one...

[Michael Mattias]

Right now I have to get catalog functions working thru ADO.

Anyone who wants to send me starter code is welcome to do so...

[email protected]

Specifically, I'm looking for equivalent methods to get the info returned by the ODBC "SqlColumns" and SqlTables" functions.

[Michael Mattias]

>1. Created a master Curve table with two columns (RowID, Name)
>2. Created a second table for CurveData using (RowID, CurveID, Depth, Value

Why not ...

CREATE TABLE Curve_data 
    (CurveID VARCHAR(24), 
      Description  VARCHAR(6),  
      Depth whatever, 
      value whatever)

You could use a value like "MASTER" or "00000" for the Curve ID master (changes to same prohibited by the application) , and user-supplied values to add additional curve_data rows.

You wouldn't even need a second table.

Add a "user_id" column and you could store everything in there... when user asks for a list of his current curve_data, you select only rows for his user_id.

I must be missing something, but if I were designing a system where the user can store his own variation(s) of some 'master' configuration this is how I would do it. One table is certainly easier to manage than two.

MCM

[Chris Holbrook]

Why not ... One table is certainly easier to manage than two. MCM

It would be interesting and pretty easy (for whoever has the data) to compare the efficiencies of the two solutions which you mention. On the face of it, there is a lot of redundancy here, but there is also a lot of redundancy in storing a boolean value as a long, yet it works well...

[John Montenigro]

Thinking strictly in terms of database design, the "curve-table" indentifier describes the table, so it belongs with the other fields that describe the table. In the master table, (conceptually) there is one and only one row/record that describes a specific table. THAT's where the additional field is needed. It may require ADMIN rights to modify this master table, but the case is made easily when comparing the retrieval of one field from one master record, versus the many SELECTs and processing to obtain the same information from multiple user-level tables via this workaround.

If I'm understanding this correctly, this is a problem that pertains to the identification of a table, so it's a false economy to attempt that outside of the master table. (It's metadata, ABOUT the user-table. It does not belong IN a user-table.)

Now, I could be totally misunderstanding how these "user tables" are being created, and I may be assuming incorrectly that your program has control over what those tables are named, and if I am thus off-base, my comment above may not apply.

However, if your app has control...

[Michael Mattias]

Well, the programming challenge with multiple tables is pretty simple: What do you do when "something bad" happens after you've already updated one of the tables and the INSERT or UPDATE fails on a later table?

If your DBMS 'does transactions' then it's pretty straightforward.. you BEGIN TRANSACTION before INSERT or UPDATE of the FIRST table, and END TRANSACTION with COMMIT rather than ROLLBACK only when all tables were successfully updated.

BUt now try this with a DBMS which does not support transactions. I do this by DELETING any rows added in prior tables... but in my application there are no UPDATEs, there are only INSERTs, so it's nowhere near as complicated as when UPDATEs or DELETEs - which it certainly appears this application must support - are involved.


MCM

[John Montenigro]

Hey Guys,

Just wondering if anyone knows how to assign a "Type ID" to a SQLite Table.

What I need is a way to identify the type of table in the database but I can't use the tables name as that will be user defined at runtime.

Here's the ideas I have so far...

1. Create a secondary table that lists the Table Types and Names.
2. When reading a table, read the column names to see if they match a known pattern.

Any other suggestions would be great!

Thanks

Michael,

You're referring to the classic problem of "cascading deletes". If your DBMS handles "transactions", then it has a suitable strategy already designed into it's executables. If not, then you have to develop your own. But Mark's original statement is about adding (presumably, to the master table) identification information about a user table. Just in the purest sense of the data model, that information belongs in the master table, and not in a user table.

On several fronts, I just don't see any sense in creating a user-table to contain metadata.

As you imply, such a solution will be more complicated and would be more vulnerable to improper completion (and potential repair) if the cascade strategy is weak or interruptable.

Not to mention the additional overhead for a function that the DBMS could easily handle with one additonal field in the master table record for the user-table!

[Chris Holbrook]

John, I'm not sure that I agree with your proposal re changing SQLITE_MASTER, for two reasons:

1. Relational databases are all about storing data and relationships, like a foreign key, which is what we have here. You are right, it is metadata, but the relational database design has a place for it. *

2. I don't know how practical it would be to change the structure of SQLITE_MASTER, though it will only cost a message to the sqlite users forum to find out!

For my money, Matt Humphrey's analysis was closer to the mark (see also my fine picture in post #10)

*The fact that SQLite does not allow the definition of a foreign key per se - you have to use a database trigger to implement it - is possibly a bit of a red herring here.

[Michael Mattias]

Just going back to the original problem...

Thanks for the tips so far but I think I need to clear things up...

1. Add user named tables (example: Gamma, Temp, Dog, etc...)
2. These tables are "Curve" tables consisting of a Depth and Value column
3. The user can add as many unique curves as they like

My problem is knowing which table is what. When I open the database to read the tables. How do I know it's really a "Curve" table I'm reading?

... if all the ""information describing the curve" have the same data - Depth and value (point #2 above).. then there's no need for a separate table for each. (Point #1)

That is, this discussion - while interesting - is based on a less-than-optimal starting point.

And we see again the putting the "How" (use a separate table for each curve) before the "What" (store and retrieve multiple distinguishable curves by name/ID) is also non-optimal.