Mistral Sample Database

For anyone that needs it, I’ve created a clone of the Chinook database for SQLite, to handle some testing I was doing. The main changes are the addition of a version field for use in testing concurrency and a few changes to the naming conventions.

You’ll find it over on Github.

opens fine ! thanks.

Jean-Yves: How do you achieve that ?

Xojo self-made app to edit graphically databases (sqlite, postgres and mysql for now, but more if needed)
nice thing is to quite immediately make listboxes from the database structure, or pages to edit the data.
still there are important missing things that prevent it to be sold as a developper tool, but as I don’t use them it’s ok for me !

I use DBSchema for all my design work.

Here is the default diagram that TADPOLE makes (tables can be dragged around to fit your need)

@Dave S: which library are you using to power the ERD? Thanks.

@James Dooley : why does the track table have a primary key in dbschema, and no pk in dave’s and my app ?
I also tested in some other db tools I have and they don’t set the ID field as a primary key.

that is curios since the sql to create that says

“library”? It is all custom written Xojo code

The DDL for that table has no PK defined

CREATE TABLE track ( ID integer NOT NULL,
  name text NOT NULL,
composer text,
milliseconds integer,
       bytes integer,
  unit_price text NOT NULL,
    album_id integer NOT NULL,
media_type_id integer NOT NULL,
     genre_id integer NOT NULL,
      version integer NOT NULL DEFAULT 1,
   created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY ( album_id) REFERENCES album ( ID) ,
      FOREIGN KEY ( media_type_id) REFERENCES media_type ( ID) ,
      FOREIGN KEY ( genre_id) REFERENCES genre ( ID) )

[quote=446197:@Dave S]The DDL for that table has no PK defined
[/quote]
I saw that, so why James’ diagram shows a primarykey for the ID field ?

Because the diagram came from something else ?
Or from something that just assumed that ID is ALWAYS a primary key (I’ve known some tools that did this in the past)
Or any other reason like maybe the diagram isn’t 100% correct ?

Got it in one! I extracted it from another project… sorry about that.

I did a commit in the meantime so it should be OK now.

OK. I seem to remember a discussing on an XOJO library that could be used to draw diagrams, but can’t find a reference now.