Author Topic: SQLite3 DB/Table Naming  (Read 2122 times)

Offline Alexi

  • Acquaintance
  • *
  • Posts: 15
    • View Profile
SQLite3 DB/Table Naming
« on: May 01, 2009, 01:17:00 AM »
Just a tip - insure that all your tables have unique 'names' apart from your database file names.  You will encounter difficulties. For example:  if you create a database file named foo.db -- do not have a table inside that is also called 'foo' (grossly simplified example)

My real world eample:  names.db  with a variety of naming tables...one of which was a 'names' table which oddly was freakishly failing lookups.

(assuming tables are <wibble> INTEGER PRIMARY KEY AUTOINCREMENT, <womble> TEXT)
select name from keynames where nameid=1    Success
select name from nodenames where nameid=1  Success
... <rinse and repeat n+1 successes>
select name from names where nameid=1         Fail  <-- Collision with DB "names.db"

FIX?

If you find you are in a mess with a mature (poorly named heritage) db and table structure, you can temporarily rename your file(s), repoint your db_connect()s at the new filename(s) and the existing table(s) you are trying to work with will dereference properly.  But it will still be a goat rope adventure depending on the complexity of your legacy inheritance.

IMHO, a more healthy fix would be to detangle the pasta outside the mud and add fresh sauce.

I don't recall MySQL being quite so anal (but, then again, I've also not tried to foo-foo MySQL's namespace in 2.16).

I just thought it might be something to be mindful of if you are thinking of localizing your storage, enhancing your portability options, or compiling to support more than one database for your own world domination strategies.

Cheers,
Alexi

Offline quixadhal

  • BFF
  • ***
  • Posts: 642
    • View Profile
    • WileyMUD
Re: SQLite3 DB/Table Naming
« Reply #1 on: May 01, 2009, 08:51:29 AM »
That's absurd.  I believe you, but it's still absurd.

The "filename" of the backend storage of any database system shouldn't have ANYTHING to do with any entities inside the RDB itself.  I find it hard to believe that's an error in SQLite itself, but suspect the driver's API layer is doing something dumb.

Heck, I use PostgreSQL, and it scatters data all over the place, but the files are generally named after an internal object-ID, transaction-ID, or something else that has only marginal relation to the data kept there.