How to Connect with Sqlite Database

Sqlite - related issues
Post Reply
odalipa
Posts: 3
Joined: Tue Oct 25, 2016 11:34 pm

How to Connect with Sqlite Database

Post by odalipa » Tue Oct 25, 2016 11:57 pm

Hi,

When I choose "Connect To Database" and then "Sqlite Database" , I don't get a "Connection Dialog", Host Name or IP, Port, Database User, Password, Database.

How do I connect to a Remote Sqlite Database ?

Thanks and Regards,
Mariano

harpander
Posts: 5
Joined: Fri May 22, 2015 10:36 pm

Re: How to Connect with Sqlite Database

Post by harpander » Wed Oct 26, 2016 3:07 am

SQLite database is just a file(s) on a disk, you cannot connect to it remotely unless you have remote access to the file system.

odalipa
Posts: 3
Joined: Tue Oct 25, 2016 11:34 pm

Re: How to Connect with Sqlite Database

Post by odalipa » Wed Oct 26, 2016 7:30 pm

Hi,

Thanks for the response. How do I Reverse Engineer and Document the Sqlite Database from the file ?

Thanks and Regards,
Mariano

harpander
Posts: 5
Joined: Fri May 22, 2015 10:36 pm

Re: How to Connect with Sqlite Database

Post by harpander » Wed Oct 26, 2016 8:15 pm

odalipa wrote:Hi,

Thanks for the response. How do I Reverse Engineer and Document the Sqlite Database from the file ?

Thanks and Regards,
Mariano
1. Go to Project drop down menu and select "New Project Connected to Database".
2. I don't remember if I have already changed some settings, but on my screen in Database Connection dialog I see Alias as "Sqlite" and Rdbms selected as "Sqlite".
3. Then in the Database / File field you may input the path or click Choose to select the Sqlite database file.
4. Press Connect and reverse engineering dialog pops up.
5. Select what you want to have imported to your new DbSchema project and click OK.

What I noticed when started working on a new project using SQLite, I think the post-synchronization back and forth is not perfect. For this reason I use the DbSchema for designing the schema only. IMHO, it's the clearly the best tool available for that task at the moment. After changes I always export the schema to an SQL file (F2) and use build scripts to create the actual database. If the changes are fine, I commit both the DbSchema file and the SQL-file to Git repository. The DbSchema file is XLM by the way so you can easily use diff (or e.g.Beyond Compare) to compare changes.

Also, the exported SQL file contains primary keys as constraints like this:

Code: Select all

CREATE TABLE area ( 
	areaid               integer NOT NULL  ,
	name                 text NOT NULL DEFAULT '' ,
	description          text NOT NULL DEFAULT '' ,
	CONSTRAINT Pk_area PRIMARY KEY ( areaid )
 );
But SQLite requires the primary key constraints to be in the column definition itself, so I wrote a script to convert them in build script to this:

Code: Select all

CREATE TABLE area (
	areaid               integer PRIMARY KEY AUTOINCREMENT NOT NULL  ,
	name                 text NOT NULL DEFAULT '' ,
	description          text NOT NULL DEFAULT '' 
-- MERGED IN COLUMN DEFINITION: CONSTRAINT Pk_area PRIMARY KEY ( areaid )
 );

Post Reply