SQLite Table ID

[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.

[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.

[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!

[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]

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

[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...

[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

[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.

[Chris Holbrook]

Shhh - they'll all want one...

[Michael Mattias]

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

[Chris Holbrook]

MCM, look at pragma table_info

[Michael Mattias]

On the FAQ page I did not see any function which returns "columns of a named table"

If there is no other catalog function for this you should be able to get a list of columns and their datatypes by executing a SELECT...

select * from target_table

To avoid creating a result set, qualfy the query to return NO rows...

select * from target_table [B][COLOR="Red"]where ROWID < 1[/COLOR] [/B]

If there is a separate catalog function to return column info... never mind.

MCM

[Chris Holbrook]

The CurveID column is "connected" to the RowID in the Curves table. So far this is working quite well.

If you are using the SQLte RowID - a pseudocolumn which as built in to every row created - then beware - Rowids are not necessarily allocated in sequence! For example, if you delete a few rows, ISTR those rowids are re-used, which could be amusing.

You could alternatively consider declaring another column using AUTOINCREMENT values:

CREATE TABLE mytable( mycolumn INTEGER PRIMARY KEY AUTOINCREMENT );

Also, consider that the implementation of Rowid is not the same in all other SQL databases, so in using it you are creating non-portable code (which doesn't always matter!).

[Mark Smit]

Hello again,

Sorry I disappeared for a few days...

After reading all your suggestions I ended up doing the following.

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

The CurveID column is "connected" to the RowID in the Curves table. So far this is working quite well.


Thanks for all your ideas!

[matt humphreys]

I'm struggling with the need for a separate table for each set of curve data...perhaps mark could confirm that requirement for us?

If indeed there is then ...
one could always parse/pattern match the sql field returned from

select sql from sqlite_master where type='table'

for the correct table structure...
eg sql field returns

Create table CurveName1 (Depth not null,Value not null);

then parse out the 'CurveName1' and pattern match the rest


OR...to examine each table one could select the table name

select tbl_Name from sqlite_master where type='table'

and, as Mike posted, then use

 PRAGMA Table_info (tbl_Name)

but we are then looping thru two tables.


OR...perhaps when creating the table just prefix each table name with say 'Curve_' and parse it off when returning the list of names to the user.

select * from sqlite_master where type='table' and tbl_name like 'curve_%'

[Michael Mattias]

SOoooo.... the question is, IF we wanted to add a "description" field, COULD it be done, and if so, HOW?

Create table table_desc (table_name varchar(24) not null, user_description varchar (60));
Create unique index tablename_idx on table_desc (table_name);
Commit;
Insert into table_desc  (table_name, user_description) (Select tableName, 'description of '  || tablename from sqlLite_master); 
commit;

Something like that anyway.

MCM

[John Montenigro]

Yup! Good find, Michael! I didn't realize in Matt's post above that he was referring to the same table I was talking about; I didn't know it's name.

In the FAQ I noticed that the "sqlite_master" table does not contain a "table_description" field, AND that it is a read-only table (except through such commands as "CREATE", "UPDATE", etc.)

This is the right place for such an identifier, and would handle the initial problem. Too bad it isn't already there.

SOoooo.... the question is, IF we wanted to add a "description" field, COULD it be done, and if so, HOW?

-JohnM

[Michael Mattias]

Aren't there meta-data tables ("system tables")? There should be a "system table" that is named "Tables" that contains the names of the user-tables.....

John, any good database will have "catalog" functions to return the names of all tables, and for any table the names and data types of all columns. There's usually a lot more 'catalog' information available but this sounds like it would be enough for this application.

But I don't know SQLLite .. but let me check their web site real quick...

http://www.sqlite.org/faq.html

See FAQ # 7

MCM

[John Montenigro]

I don't know SQLite, but I've used SQL databases in the past.

Aren't there meta-data tables ("system tables")? There should be a "system table" that is named "Tables" that contains the names of the user-tables. It is possible that this table contains a comment or description field for each user-table.

Additionally, there should also be a "system table" named "Fields", which lists the name, type, and length of every field in the user-tables. You might be able to recognize your Curve tables from those descriptors...

-John

[Michael Mattias]

>How do I know it's really a "Curve" table I'm reading?

I have two applications where I let the user "open" or "select" a database but I have to know that it is a 'valid' database for this application.

What I do is check for the existence of the required tables, then if they exist, check that all the required columns exist in the correct order, then (recent addition) check there are no extra columns the user has decided to add. (That last one drove me nuts until the user mentioned almost in passing that he had "only " added two columns).

I suppose you could also check that said columns are the correct datatypes, precision and scale, but in my applications I figured if I got this far I'd pay off if it was NOT a 'correct' database.