Announcement

Collapse
No announcement yet.

SQLite Table ID

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

  • SQLite Table ID

    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
    Start as you mean to go on.

  • #2
    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.

    Comment


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

      In the mean time please keep the ideas coming guys
      Start as you mean to go on.

      Comment


      • #4
        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:
        Code:
        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.
        Software makes Hardware Happen

        Comment


        • #5
          >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
          Michael Mattias
          Tal Systems (retired)
          Port Washington WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            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

            Comment


            • #7
              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.
              Start as you mean to go on.

              Comment


              • #8
                Originally posted by Mark Smit View Post
                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:

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

                Comment


                • #9
                  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

                  Comment


                  • #10
                    Originally posted by matt humphreys View Post
                    2 tables, super simple
                    like:
                    Attached Files
                    Last edited by Chris Holbrook; 10 Nov 2008, 04:19 AM.

                    Comment


                    • #11
                      >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.
                      Michael Mattias
                      Tal Systems (retired)
                      Port Washington WI USA
                      [email protected]
                      http://www.talsystems.com

                      Comment


                      • #12
                        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

                        Comment


                        • #13
                          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
                          Michael Mattias
                          Tal Systems (retired)
                          Port Washington WI USA
                          [email protected]
                          http://www.talsystems.com

                          Comment


                          • #14
                            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

                            Comment


                            • #15
                              SOoooo.... the question is, IF we wanted to add a "description" field, COULD it be done, and if so, HOW?
                              Code:
                              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
                              Michael Mattias
                              Tal Systems (retired)
                              Port Washington WI USA
                              [email protected]
                              http://www.talsystems.com

                              Comment


                              • #16
                                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
                                Code:
                                select sql from sqlite_master where type='table'
                                for the correct table structure...
                                eg sql field returns
                                Code:
                                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
                                Code:
                                select tbl_Name from sqlite_master where type='table'
                                and, as Mike posted, then use
                                Code:
                                 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.
                                Code:
                                select * from sqlite_master where type='table' and tbl_name like 'curve_%'
                                Last edited by matt humphreys; 10 Nov 2008, 10:37 PM.

                                Comment


                                • #17
                                  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!
                                  Start as you mean to go on.

                                  Comment


                                  • #18
                                    Originally posted by Mark Smit View Post
                                    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!).

                                    Comment


                                    • #19
                                      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...
                                      Code:
                                      select * from target_table
                                      To avoid creating a result set, qualfy the query to return NO rows...
                                      Code:
                                      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
                                      Michael Mattias
                                      Tal Systems (retired)
                                      Port Washington WI USA
                                      [email protected]
                                      http://www.talsystems.com

                                      Comment


                                      • #20
                                        MCM, look at pragma table_info

                                        Comment

                                        Working...
                                        X