DbSchema for SQLite Database

DbSchema is a powerful database management and design tool for SQLite. It offers features such as visual schema design, team collaboration with GIT, schema deployment and HTML schema documentation.

How to Connect to SQLite Database

1. Access the Connection Dialog

   Choose "Connect to the database" or "New Model Connected to the Database" will let you select your database and open the Connection Dialog.

   DbSchema automatically downloads the JDBC driver to connect to your database.

2. Configure the Connection

In the Connection Dialog window, select the JDBC URL you want to connect with. If your database is running in the cloud, choose Edit Manually and insert the JDBC URL from the cloud console.

Enter the host name, the authentication details, select your database as described in the Connection Dialog page.

3. Troubleshoot Firewall Issues
If you encounter connection issues, it may be due to firewall settings. Read how to enable Firewall Connections.

• JDBC Driver
• How to Create Foreign Keys

JDBC Driver

Sqlite is working with database files placed on the same computer as the DbSchema runs ( only local mode ). Connecting to Sqlite would require only the path to the database file. JDBC drivers can be found on www.sqlite.org and on http://www.zentus.com/sqlitejdbc/.

Create new database by executing :

sqlite3.exe test.db

General Information for Android Developers

Android is an operating system based on Java. DbSchema as well as JDBC drivers are also implemented in Java. This is a software on top on Java providing Android libraries. Search the net for Android SDK, download and install it.

Android File System

Connecting the phone you may see the files on the phone SDCard as easy as you would connect a photo camera to your computer. Android is running on a Linux core. The main folders, as well as the folders where installed application resides are read and write protected, so by default you cannot access them. ROOTING the phone is a common procedure to get the highest right access and see all the files. This procedure may still have disadvantages, like you may not be able to get automatic updates on your phone. Below I will present you the tools you can use to read files without rooting your phone.

Enable Debugging on Android Phone

On the phone you have an Settings option. Look for Developer option and enable the USB-Debugging checkbox. This will allow to access the Android files with limited permission rights. Connect the phone to your computer using USB cable.

Fore accessing the Android files two tools from Android SDK are important for you:

• Adb Adb is a console-oriented command prompt. Using it you can access the File System inside Android. By default, connecting the phone to your computer you will see only the files placed on the phone SDCard. Using Adb you may read or write files on the internal memory. Adb is placed in AndroidSdk/platform-tools folder. Open a command prompt on windows, go in the indicated folder and execute adb.exe.
• ddms This is graphical interface with the phone. Using it you can also read or write files. The tool is more useful for developers testing their applications. For manipulating files is not as powerful as adb.

Android Applications

Android software packages can be opened using a zip util ( like 7-zip ). The software is placed in structure of folders. For example, look in the Android Sdk sample folder, there is a notepad application. Its package is com.example.android.notepad. You can find this in the application manifest file, as well as a structure of folders to the main class. Before getting to your application database you should know which is the application package path.

Download the Sqlite Database from Android

From command prompt start the adb utility. First change the current user using the command below. You should use your application package instead of com.example.android.notepad. The application should be already installed on the phone.

    run-as com.example.android.notepad

Now change in the application directory. You should try this command 'blind'. This because you cannot do ls or dir - you will not have permission for this.

    cd /data/data/com.example.android.notepad/databases

Next step is to copy the file to SDCard. I couldn't manage to get it down in one single step, I had to first copy it to SDCard and than pull it down. Do this inside the adb session.

    cat note-pad.db > /sdcard/note_pad.db
    

After this step I still was not able to view the file in Windows Explorer, maybe the permissions were still not fine. I succeed to get the file in Windows using the command below. First exist the previous adb session.

    adb pull /sdcard/note_pad.db .

How to Create Foreign Keys in Sqlite

Foreign Keys can be created in Sqlite only at in the same time with the table they belongs. When creating a table, go in the Table Dialog on the Foreign Keys tab and add the foreign key. DbSchema will generate a command like :

        CREATE TABLE books (
	bookid               INTEGER( 11 ) NOT NULL,
	authorid             INTEGER( 11 ),
	CONSTRAINT pk_books PRIMARY KEY ( bookid ),
	FOREIGN KEY ( authorid ) REFERENCES authors( authorid ) ON DELETE CASCADE ON UPDATE CASCADE
 );
    

This will work. Adding a foreing key later ( with ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY... ) is not supported in Sqlite. Write to Sqlite about this, with more requests they will consider to implement it.