Tadpole - for SQLite

Add-Ons
42

Norman, you are talking about a situation similar to this?

Create Table dave_pk ( 
   pk01 integer,
   key1 text,
   key2 text,
primary Key ( pk01 autoincrement));

Create Table dave_fk ( 
   pk02 integer,
   key1 text,
   key2 text,
primary Key ( pk02 autoincrement) ,
foreign Key ( key1,key2) references dave_pk (key1,key2));

Tadpole (and most other similar programs I have played with) only allow you to create tables like this by manually entering the SQL code. The Create Table Wizard won’t create composite keys (perhaps a future version will)

That being said… When Tadpole parses the DDL it seems to ignore composite keys, (either PK or FK)… it DOES use them in SQL queries… just not for the extended “DESCRIBE TABLE” or “SCHEMA DIAGRAM”… at least not yet

43

I’ve used a few that do let you, right in the ERD, define a new table, add columns, etc an even establish relationships graphically

For some folks who are visually oriented its very useful

44

Tadpole will provide access to the CREATE TABLE wizard (which includes single FK support) and add those tables directly into the diagram… but it will not support the full feature suite some of the more powerful ERD applications do. For Tadpole it is more of a schema visualizer, than visual schema builder…

At this rate, scope creep is gonna get me :smiley:

45

Put that one on the feature request list :stuck_out_tongue:

46

Dave, just wanted to let you know that Tadpole crashes after opening one of my DB’s when I click in the expand arrow of the listbox (containing the table list)…

I mean, the file opens… but when I click in one of the tables… usually the second or third click… it crashes…

Would you like to check that out ?

47

[quote=337863:@Roman Varas]Dave, just wanted to let you know that Tadpole crashes after opening one of my DB’s when I click in the expand arrow of the listbox (containing the table list)…

I mean, the file opens… but when I click in one of the tables… usually the second or third click… it crashes…

Would you like to check that out ?[/quote]
Yes I would… I was informed of an issue this morning that would crash Tadpole if you double click on a VIEW where one or more tables required by the view had been deleted… This has been fixed, but not yet released…

48

FYI… Sample diagram has been updated with automatic notations (some tweaking still required : )

SEE ABOVE

49

Dave, track.trackid : is 1-1 should be M-1 link ?

50

This is related directly to what Norm was talking about above :frowning:

PLAYLISTRACK has a COMPOUND Primary Key, but independant FK defintions

  • [PK=PlaylistID,TrackID]
  • [FK=PlaylistID]
  • [FK=TrackID]

TRACK

  • [PK=TrackID]
  • [FK=AlbumID]
  • [FK=GenreID]
  • [FK=MediaType]

So Tadpole is generating the Link based on the FK of PlaylistTrack.TrackID to PK of Track.TrackID
Since TadPole is INCORRECTLY splitting the PK of PlayIist (not thinking its a Compound Key), it sees a PK to PK type link which would in fact be 1:1.

Had it “ignored” the PK in Playlistrack the link type would have been M:1, but the PK icons would not have been there either …
So yes, there is something I need to figure out…

51

a general problem because FK’s and REFERENCES do not indicate cardinality

52

Actually I think they do… once I get the compound/composite key “fixed”
at least to the level of

  • 1:1 - Source is also a PK to Destination is also a PK
  • 1:M - Source is also a PK to Destination not also a PK
  • M:1 - Source Not a PK to Destination is also a PK
  • M:M - Source FK not PK to Destination not a PK

In all above cases Source must be an FK as well…

Basically it “assume” a MANY, unless the Source/Destination field is also a PK (since “there can be only one”)
and I think I can refine it even more by comparing Source(is PK/is FK) to Destination(is PK/is FK) instead of just
Source(is PK) to Destinatin(is PK)

This is how the above diagram was produced… with the known current error of improperly catching compound keys

53

another erd for chinook : you can see playlisttrack to track is M-1

also this one :

54

I am AGREEING with you… Tadpole is not 100% on this diagram (yet)…
And FYI… I am using that exact (Orange) diagram as my bench mark… :slight_smile:

55

except there’s nothing in the raw sql in sqlite_master to tell you a relationship is 1:1, 1:m, n:m etc
Directly from that diagrams sqlite sample db (I literally just downloaded it)

CREATE TABLE "albums"
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId]) 
		ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE "artists"
(
    [ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] NVARCHAR(120)
)

the best you can derive automatically from the sql is "there IS a relation ship because of the FK definition’ but cardinality cant be determined with out running a query. And if the db is empty you cant even do that.

56

Windows 6/19 dated exe is raising an error on trying to delete a row from a sqlite db.

UnhandledException: OutOfBoundsException
Current date: 2017-06-28 16:19:05

Executable Name: tadpole.exe
Executable Path: C:\Users\…\Desktop\tadpole.exe_ERRORLOG.txt
Executable Size: 5303630
Executable ModificationDate: 2017-06-19 17:01:22
Executable CreationDate: 2017-06-19 17:01:22
Call Stack:
Stack:

57

Yeah… my bad… changed some table references and didn’t recheck that function… it is fixed for next release…

58

A new evaluation copy of Tadpole is available. This version contains the “alpha” release of the Schema Diagram function… note the word “ALPHA”… there is still more work to go… Right now it “guesses” at the cardinality of the relations, it does not autolayout the tables on a new diagram (yet), and the load and save is to a single file on your desktop… All of that will evolve.

For the most part I believe I have addressed all the issues that people have sent me… and I appreciate the previous , current and hopefully future feedback to help me make this a better product.

28Jun2017
- fixed broken issue with DELETE ROW
- adjusted Schema Diagram to recoginize composite keys
- added preliminary load/save for Schema Diagram (limited to Desktop/sample.erd for now)

26Jun2017
- fixed problem where Navigator would "reset" if vertical slider was moved
- fixed problem with crashes on malformed views from Navigator

23Jun2017
- implemented FOREIGN KEY on CREATE TABLE Wizard and DESCRIBE TABLE

macOS http://www.rdS.com/tadpole/tadpole4macos.zip
Win http://www.rdS.com/tadpole/tadpole4win.zip

59

Dave,
a quick question. why is the amount for item over million show as 4.500000e+6??

60

Dave:

tadpole crashed on quit (two times)
Delete only One row (Demo mode ?)
[refuse to delete a second Row]

Why Field names (RecordSet Fields as displayed as Listbox headers) are TitleCased ?
Do not report you open a locked .sqlite file.

Edit:
Boolean colors: Green for True is the choice; however using Blue for False ? I was awaiting Red for False (green inverted color).
This is your choice.

Edit 2: macOS El Capitan 10.12.5

61

will look into this… your image did not display

Emile… no offense… but I am having trouble determining exactly what you are attempting to raise as an issue, And what are things you simply think should be “different”. RED for False… Sorry no… RED is for ERROR situations, and “False” is not an error… So if by “This is your choice”, you mean “That the choice is mine as I am the developer”… or do you mean “The choice should be the user?”

Why are field names Titled cased? Because they are (in this case) TITLES, the same as if it were a report… and for that asthetics (to me) are important.

Locked SQLite Database… Would still be “readable”… and would report an error if you attempt to WRITE… If file permissions are set to a level that the user is not even allowed to acces the database, if will not open it, and will report an error. If you find otherwise, please provide a reproduciable situation.

EDIT : I just locked a DB… and Tadpole indicated it was locked, when I attempted to connect to it

As to crash… I have not been able to crash Tadpole… but have not yet run with Sierra, so that “might” be contributing.

As to the DELETE… .yes there is an issue… for some reason it is not catching a specific error, and “thinks” it did in fact delete the row

EDIT : Delete issue is resolved… :slight_smile:
EDIT : I just locked a DB… and Tadpole indicated it was locked, when I attempted to connect to it