Describe Table (Sqlite)

Databases
1

I know about db.FieldSchema, and “pragma table_info” and “.schema”
but those only return

  • field name
  • field type
  • Null Allowed
  • Default Value
  • Primary Key

How to easily get other attributes?

  • AutoIncrement?
  • Unique
  • Collate NoCase
  • etc.

I would prefer NOT to have to parse the data in SQLITE_MASTER as there could be many different “coding styles” etc.

2

I did it parsing sqlite master, or differently with postgres
it’s “only” some regex, but it’s the only way I found to have these informations.

3

yeah, after hours searching the 'Net, I’ve come to the same conclusion

thanks… Luckily I’m really good at parsing data structures :slight_smile:

4

[quote=322957:@Jean-Yves Pochez]I did it parsing sqlite master, or differently with postgres
it’s “only” some regex, but it’s the only way I found to have these informations.

[/quote]
This looks pretty cool. Is this something you created?

5

@Bob Keeney - did you check this with Carol ? :slight_smile:

6

Ok… I have relegated myself to having to parse Sqlite_master, however using db.Fieldschema is required if sqlite_master indicates the table was created using “CREATE table XYZ AS (select)”, since there is really nothing to parse metadata wise.
The problem is … it seems Db.FieldSchema cannot reference a table in an ATTACHED database???

NEVERMIND… it seems that SQLITE does not insert the ACTUAL SQL used to create the table, but the SQL required to re-create the structure… which is actually kinda nice

So if you say “Create Table XYZ as Select * from ABC” , SQLite Master has “Create Table XYZ (field type,field type,field type)” instead

7

yes … it has evolved since this picture, here is a new one.
bold fields are indexed, bold types are “not null”, yellow fields are for PK, small “I” at the top of table to show indexes, small “T” for triggers, some methods to make direct windows in xojo …

double clic on a table lets you see a list and edit it - works for sqlite and postgres (but extendable to other db)

8

is it Mac only? or is it cross platform?
And it looks amazing.

9

I programmed it to be cross platform, cross database, and cross language but did not compile for win or linux for a long time
anyway it should compile right away…

10

well I need some help for windows …
I have no error when I compile, I checked the MDI property, compiled for win7-64
and I have no window, no menu on my app under windows !
my app on the mac side is a multiple document app.
any clue ? thanks.
do I need to make one background window under winos ?

11

might be better to start a new thread…

and MDI is an antiquated GUI design… I’d re-engineer it to be the same as the Mac Version

12

yes - sorry.