SQLite Table ID

[Mark Smit]

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

[Chris Holbrook]

My advice is - work out the relational design before writing code. You could use an entity-relationship diagram to do this. There are a couple of different styles, the "crows-foot" notation favoured by Oracle is the one I find easiest.

[Mark Smit]

Thanks Chris, I'll start reading up on that.

In the mean time please keep the ideas coming guys

[Joe Byrne]

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

Well, tables don't have 'type', so I assume you are referring the the data type of an individual row/field/cell?

If so, you can do a query like this:

PRAGMA table_info(table-name);

From the SQLite Docs page:

For each column in the named table, invoke the callback function once with information about that column, including the column name, data type, whether or not the column can be NULL, and the default value for the column.

The "issue" with this however, is that SQLite does not enforce data-types. It really doesn't care how you define a field type, it will basically treat the data as text. Most type-casting checks need to be done in your code and not via the database engine.

[Michael Mattias]

>Well, tables don't have 'type',

I'm not an SQLLite user so I did not know if perhaps that product might not utilize a "type" to distinquish, say, a TABLE from a VIEW.

Not that TABLE/VIEW makes a whole lot of difference at the application level unless you want to update thru the VIEW... which you may or may not be allowed to do depending on how that VIEW is defined.


MCM

[matt humphreys]

I'm not exactly sure what you're after ...this may help...
Select * from sqlite_master
will give all the info including the DDL to create
or perhaps something similar to ...
Select * from sqlite_master where type='table' or type='view' order by type

[Mark Smit]

Hey Guys,

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?

Would be nice if SQLite had an additional ID variable for each table...

In the mean time I think I'll have to resort to a master curve table listing all the user named curves.

[Chris Holbrook]

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?

how about a single table with columns NAME, DEPTH and VALUE. Either that or table to contain a directory of tables, which is more work.

So you select the set of values for a given curve name with:

 select depth, value from curves where name = 'XXXX' order by depth

[matt humphreys]

or perhaps a compromise to limit db bloat.
have, as chris says, one table for all curve data except store the NAME_ID as say a byte (which gives you 255 Curve Names, or Integer or Long if storing more than 255 curves..you get the picture...) and as you suggest Mark, have a master table of all the Curve Names.

tblCurveNames = NAME_ID, NAME
tblCurveData = NAME_ID, DEPTH and VALUE

query is still similar
select depth, value from curves where name_id = 'X' order by depth
but we are not storing all that name string data each time

2 tables, super simple

[Chris Holbrook]

2 tables, super simple

like:

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

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

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]

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]

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

[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_%'

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

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

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

MCM, look at pragma table_info