Revolutionizing Education Assessment: From SQLite to IDE Integration

Revolutionizing Education Assessment: From SQLite to IDE Integration

1

ABSTRACT

This paper first describes the definition, basic characteristics, structure and the key

technologies of an embedded database, analyses the features, architecture and the main

interface functions of SQLite, gives a detailed porting process from SQLite to an IDE

platform. With the advent of open-source engines, SQLite provides server-less, single file

approach with integration to IDE. Eclipse is one of the platforms used in designing for

integrated design environments. The technical and architectural description is discussed in

this paper and integration with SQLite library.

Revolutionizing Education Assessment: From SQLite to IDE Integration

2

1 Introduction

Relational Database Management Systems implement the relational model, which

shapes the integrity of data by relating entities with the attributes across the tables. These

systems require clear definition of the table structure, data types, and columns. A single

database is distributed over number of tables. Here, SQLite provides robust performance with

low maintenance and minimum configuration. SQLite is a software library that implements a

self-contained, server less, zero-configuration, transactional SQL database engine (Citation:

C1). It is the combination of both the database engine and the interface within a single

library, as well as the ability to store all the data in a single file, hence also known as Flat-file

DB system. SQLite is not a DB, instead is an embedded relational DB engine which can be

statically or dynamically inked with any application based on requirement, hence not a stand-

alone. SQLite database is a single ordinary disk file that can be located anywhere in the

directory hierarchy. A complete database is stored in a single cross-platform disk file, very

useful as an application file format. It is a cross platform file. It can be used on various

operating systems, both 32 and 64 bit architectures (Citation: C2). Other SQL database

engines strive to implement a shared repository of enterprise data focusing on scalability,

concurrency, centralization, and control. SQLite, on the other hand, strives to provide local

data storage for individual applications and devices focusing on economy, efficiency,

reliability, independence, and simplicity.

With the development of information technology, embedded operating system opens

up a new era for database technology according to the requirements of mobile database

system. In recent years, conventional DB systems (Oracle, SQL Server etc) are trying hard to

Revolutionizing Education Assessment: From SQLite to IDE Integration

3

extend their market in embedded system, but high operating cost and power consumption

calls for an alternate solution. These traditional databases are very slow and exhaust too much

CPU memory in most of the real-time systems, while the embedded database like SQLite

shows promising performance... SQLite is a software library that implements a SQL engine.

It has been used with great success as on-disk file format: allows the developer to handle data

in a simple way, but also have the use of database features (such as undo, redo, etc.)

(Citation: C3).

IT industry is moving from the desktop to the Web. Online services are rapidly

replacing traditional downloadable software products. Modern, desktop-based IDEs integrate

a wide range of software engineering tools, and provide a platform for writing, maintaining,

testing, building, running, debugging, and deploying software. These increase developer

productivity by incorporating many different kinds of editor services specific to the syntax

and semantics of a language (Citation: C4). These services assist developers in understanding

and navigating through the code, they direct developers to inconsistent or incomplete areas of

code, and they even help with editing code by providing automatic indentation, bracket

insertion, and content completion. The integration of complete tool suites for software

development and the development of language-specific editor services took a tremendous

effort for the current generation of IDEs such as Eclipse and Visual Studio.

This paper presents an implementation of a DB library that uses the Java interface

to communicate with the SQLite library, via Eclipse IDE. Eclipse is a superset of various

components for IDE, as well as can be used as foundation of for building tools and

evironmnets. Eclipse Platform provides a well managed windowing system. User interface

components are defined (including entry fields, push buttons, tables, and tree views),

including window lifecycle management, docking views and editors, the ability to contribute

Revolutionizing Education Assessment: From SQLite to IDE Integration

4

menu items and tool bars, and drag and drop. There is acute problem of storing the data in

relational database systems, with volume of data are increasing day-by-day we need the

database for storing the data the database available for the open source system, which is

SQLite database but with the SQLite database there are many problems such Access to the

database, security problem, memory problem, OS support problems also come into picture.

Here, basics of SQLite DB engine is defined and way to access DB from the application and

outside the application.

The main objectives of this paper are to showcase following:

1. . User can access data very easily. The data can be access from database very easily

and fast access of the data. Maximum number of queries can be executed by using

SQL server. By using queries we can access data without remote server on local

server.

2. Development of the logic that would enable-

• The database can be accessible from the class of the application but also

outside the class of application.

• Concurrent access to the database.

• Persistency to the database is preserved. Maximum no of data can be stored

in less place good memory management.

3. Database stored is much secured hence only authenticated users can access to the

database.

Revolutionizing Education Assessment: From SQLite to IDE Integration

5

2 Description

Due to the small size of SQLite code structure that makes it memory efficient, it’s

widely used in mobile application development owing to the limitations of memory and

storage on hand held devices. It is also preferable to use SQLite in small to medium size

websites since there is no need for complex configuration and maintenance, which also

makes SQLite a replacement for an enterprise database engine for testing and early launch of

an application. SQLite is often used as a surrogate for an enterprise RDBMS for

demonstration purposes or for testing. SQLite is fast and requires no setup, which takes a lot

of the hassle out of testing and which makes demos easy to launch (Citation: C3).

SQLite uses virtual machine which provides a crisp, well-defined junction between

the front-end of SQLite and the back-end. Front-end of SQLite executes SQL statements and

generates virtual machine code, while back-end executes the virtual machine code and

computes a result. The virtual machine allows the developers to see clearly and in an easily

readable form what SQLite is trying to do with each statement it compiles, which is a

tremendous help in debugging. Depending on how it is compiled, SQLite also has the

capability of tracing the execution of the virtual machine - printing each virtual machine

instruction and its result as it executes (Citation: C5).

The architecture of SQLite can be viewed in the Fig: F1. The design of SQLite is

based on hierarchical design, modular design, to facilitate the maintenance upgrades, and is

easy to port in different platform. It divides the whole database system into several different

level modules to implement respectively. It can be divided into eight primary subsystems:

Revolutionizing Education Assessment: From SQLite to IDE Integration

6

Interface, Tokenizer, Parser, Code Generator, Virtual Machine, B-Tree, Pager and OS

Interface. [1]

The core works of Code generator is to transform the received parse tree into the

assembly language identified by SQLite, and then transfer these assembly language programs

to the virtual machine, to execute. where stack of instructions are stored. The back-end is

responsible for maintaining interface with the OS of the virtual machine. When updating the

database file, SQLite creates a copy of the page content into heap memory before modifying

the page. The changes are not supposed to be visible to other processes until after the

transaction commits. SQL complier handles the breaking the statements into tokens, parsing

the tokens into complete SQL statements and generating virtual machine code. Virtual

Machine is most important part of the internal structure of SQLite, which is an engine

designed to deal with library files. It not only performs operations related to data

manipulation but also is the interface through which information is passed between client and

storage. It also performs stack operation processing for completing this operation.

Revolutionizing Education Assessment: From SQLite to IDE Integration

7

Fig 1: Architecture of SQLite [1]

Eclipse is an open source integrated development environment (IDE). It contains a base

workspace and an extensible plug-in system for customizing the environment. With the

various plugins available in Eclipse, it is very helpful to develop applications in other

programming languages: Ada, ABAP, C, C++, COBOL etc. and packages for

Mathematica.Eclipse uses plug-ins to provide all the functionality within and on top of the

runtime system. Its runtime system is based on Equinox, an implementation of the OSGi core

framework specification. Eclipse provides plug-ins for a wide variety of features, some of

which are through third parties using both free and commercial models e.g. plug-ins include

for UML, for Sequence and other UML diagrams, a plug-in for DB Explorer, and many

others.

Revolutionizing Education Assessment: From SQLite to IDE Integration

8

Fig 2: Eclipse Architecture [2]

The architecture of Eclipse consists of a Java platform where virtual machine and API

specifications are stored. OSGi Framework often known as Equinox provides support for

OSGi services, a modular system and service platform for Java programming.

Open source projects which are implemented in Eclipse are categorized as:

1. Enterprise Development

2. Embedded and Device Development

3. Rich Client Platform

4. Rich Internet Applications

5. Application Frameworks

6. Application Lifecycle Management (ALM)

7. Service Oriented Architecture (SOA)

Revolutionizing Education Assessment: From SQLite to IDE Integration

9

,

Fig 3 Various component of Eclipse [3]

Platform runtime is an integral part of OSGi services, where plug-in runtime

dependencies are defined and builds in-memory plug registry. Workspace provides interface

with the user specified directories and files, displays different projects. It also creates a low-

level workspace history by storing last commit values to avoid any loss of files. Standard

Widget Toolkit (SWT) creates integration of platform with OS [3].

Revolutionizing Education Assessment: From SQLite to IDE Integration

10

3 Implementation

SQLite provide command line shell for manual creation of DB, schema etc and execute

SQL statements on the SQLite DB. The command line program can be downloaded from the

link provided [4] along with library file (.dll) and definition file (.def) from [5]. Command

line shell program is an executable terminal based file which allows user to create interacts

with the library.

Fig 3: SQLite terminal

This terminal is provides access to SQLite library to create, modify any DB, tables

present in the single crossed platform disk. The library is created in the local system, requires

no configuration or setup.

SQLite command list is provided in (Table: T1)

Source code to create database, tables, inserting records into tables is provided in

(Appendix: A1).

Eclipse is used as IDE to connect to SQLite. Pre-requisites for creating DB connection

between Eclipse and SQLite is JDBC Driver, which can be downloaded from [6] and place at

Revolutionizing Education Assessment: From SQLite to IDE Integration

11

the class path of the project created in Eclipse (Appendix: A2) or java code (Appendix:A3)

can be used to establishing connection. Java code will also create database in case database is

not present (Appendices: A3). Project in Eclipse depends on the type of application to which

SQLite needs to be connected.

Revolutionizing Education Assessment: From SQLite to IDE Integration

12

4 Analysis and Applications

In this paper, we have a done detailed analysis to the definition, basic characteristics,

structure and the features, the architecture and the key interface functions of SQLite, and then

SQLite has been ported to Eclipse IDE. SQLite can be used as the on-disk file format for

desktop applications such as version control systems, financial analysis tools, media

cataloguing and editing suites, CAD packages, record keeping programs. Updates happen

atomically as application content is revised so the File/Save menu option becomes

superfluous. The File/Save As menu option can be implemented using the backup API.

This project shows that SQLite is advantageous as:

1. There is no file parsing and generating code to write and debug.

2. Content can be accessed and updated using powerful SQL queries, greatly reducing

the complexity of the application code.

3. The content can be viewed using third-party tools.

4. The application file is portable across all operating systems, 32-bit and 64-bit and big

as well as small architectures.

5. Start-up time and memory consumption are reduced as it holds and loads data

required for processing instead of reading entire application.

6. Multiple processes can attach to the same application file and can read and write

without interfering with each another.

The major fields where SQLite can be used:

1. Embedded devices and applications: Minimal administration and maintenance makes

SQLite powerful contender, where manual interference is negligible

Revolutionizing Education Assessment: From SQLite to IDE Integration

13

2. Web Programs, where number of users is less. Use of an RDBMS system can be

expensive instead preferable to use open source library SQLite.

Currently, a large number of applications are reported to be using SQLite as an embedded

database transaction system that is used to store application data in a uniform and robust

manner. These include major open source projects such as the Fire-fox/Mozilla6 browser and

the Powerdns7 DNS server. The embedded nature of SQLite reduces overheads and

simplifies installation. Applications can use the layer to abstract their interactions with the

operating system. Databases are stored in single files and are cross-platform compatible.

There are a number of possible extensions that can be built on the above described library.

These are not necessarily specific to this library but can also be of relevance to similar

approaches such as the JDBC as well as ODBC [7] library of SWI.

Revolutionizing Education Assessment: From SQLite to IDE Integration

14

Appendix: A1

Overview of Creating Database, Table, inserting records into tables in SQLite:

Revolutionizing Education Assessment: From SQLite to IDE Integration

15

Appendix: A2

Steps to create project and connect to SQLite Library in Eclipse IDE

Step 1: Open Eclipse IDE. Go to File Menu. Select New -→ other. Fig: 4

Fig: 4

Revolutionizing Education Assessment: From SQLite to IDE Integration

16

Step 2: Select SQL Development -→ SQL File. Fig: 5

Fig: 5

Revolutionizing Education Assessment: From SQLite to IDE Integration

17

Step 3: Create Java Project. Fill in Project Name. Provide workspace location. Select JRE

Run environment. Click on Next. Fig: 6

Fig: 6

Revolutionizing Education Assessment: From SQLite to IDE Integration

18

Step 4: Create SQL file. Enter parent folder name and file name. Select Database Server type

as SQLITE_3.5.9. Select Connection Profile from drop-down. Click on Finish. Fig: 7

Fig: 7

Revolutionizing Education Assessment: From SQLite to IDE Integration

19

Step 5: Provide Connection Profile Name. Select SQLite. Click Finish. Fig:8

Fig: 8

End of Appendix: A2

Revolutionizing Education Assessment: From SQLite to IDE Integration

20

Table

SQLite Commands [8]

Table:T1-SQLite Commands

Command Description

.backup ?DB? FILE Backup DB (default "main") to FILE

.bail ON|OFF Stop after hitting an error. Default OFF

.databases List names and files of attached databases

.dump ?TABLE?

Dump the database in an SQL text format. If TABLE

specified, only dump tables matching LIKE pattern

TABLE.

.echo ON|OFF Turn command echo on or off

.exit Exit SQLite prompt

.explain ON|OFF

Turn output mode suitable for EXPLAIN on or off.

With no args, it turns EXPLAIN on.

.header(s) ON|OFF Turn display of headers on or off

.help Show this message

.import FILE TABLE Import data from FILE into TABLE

.indices ?TABLE? Show names of all indices. If TABLE specified, only

Revolutionizing Education Assessment: From SQLite to IDE Integration

21

show indices for tables matching LIKE pattern

TABLE.

.load FILE ?ENTRY? Load an extension library

.log FILE|off Turn logging on or off. FILE can be stderr/stdout

.mode MODE

Set output mode where MODE is one of:

• csv Comma-separated values

• column Left-aligned columns.

• html HTML <table> code

• insert SQL insert statements for TABLE

• line One value per line

• list Values delimited by .separator string

• tabs Tab-separated values

• tcl TCL list elements

.nullvalue STRING Print STRING in place of NULL values

.output FILENAME Send output to FILENAME

.output stdout Send output to the screen

.print STRING... Print literal STRING

.prompt MAIN CONTINUE Replace the standard prompts

.quit Exit SQLite prompt

.read FILENAME Execute SQL in FILENAME

Revolutionizing Education Assessment: From SQLite to IDE Integration

22

.schema ?TABLE?

Show the CREATE statements. If TABLE specified,

only show tables matching LIKE pattern TABLE.

.separator STRING Change separator used by output mode and .import

.show Show the current values for various settings

.stats ON|OFF Turn stats on or off

.tables ?PATTERN? List names of tables matching a LIKE pattern

.timeout MS Try opening locked tables for MS milliseconds

.width NUM NUM Set column widths for "column" mode

.timer ON|OFF Turn the CPU timer measurement on or off

End of Table: T1