SQLitespeed

SQLite Database Management Utility

Introduction

SQLitespeed is a database manager for SQLite with emphasis placed on Database software development. SQlitespeed makes the general importing, reviewing and working with data-sets easier and as fast as possible - hence the name.

"SQLite" is one of the best RDBMS engines ever invented. The lightweight portable design, open source format and rigorous testing suite makes it the most widely used and best engine in the "lite" category (and among the top-ranked in any other category).

SQLitespeed is Free to use. That is: No ads, no added-functionality-upon-payment and no requirements to use. We'd like you to contribute by simply using it, reporting any bugs and sharing any ideas or requests with us.


The idea

The design philosophy in SQLitespeed is to make the power within SQLite more accessible and quicker to harness for a wide audience with a bias towards software developers and development environments.

The code completion and highlighting with quick item descriptions, fast access to SQLite help pages, and ability to view the SQL for most system functions, makes it great for learning SQL and testing queries.


Functionality

SQLitespeed sports the usual array of functions one can expect from any good DB manager, but what sets it apart?

Here's a Quick overview of the additional / extended / exceptional features:

  • It excels at most development cycle needs, such as creating and maintaining Databases, learning SQL, Visual design interfaces and testing SQL functions and functionality.

  • SQL code completion and highlighting with parameter prototypes, descriptions of keywords, functions, types, and references to the owners of objects, offers fast and efficient query development.


  • Databse Schema-checking to highlight possible quirks and pitfalls inherent to SQLite databases and SQLite engine methodology. (Especially helpful and important for people new to the SQLite development environment)



  • As part of the quest to be an educational experience (and not just a tool), we've include some well-documented SQL scripts to illustrate many of the SQL and SQLite tips, tricks, and peculiarities as discussed on the SQLite forum.

    There are of course some very advanced techniques too, for those interested, most of which contributed by the brilliant SQLite forum members. (Elect to add the example scripts during installation and find them afterward in your .../Documents/SqliteScripts folder).

  • Two Query-History recorders - one offering live step-back-step-forward-through-recent-queries navigation, the other keeps a record of successful queries for every database in a page that is editable and fully supports bookmarks and code-completion-and-highlighting.
    (Screenshot: Query History)

  • Nearly every available function that can be achieved through standard SQL has an option to display the SQL for it - always looking to accelerate the learning curve and provide a reference for doing similar thing in your own code.


  • In stead of the tabbed-results of many viewers, SQLitespeed can have many Queries open simultaneously in "popped-out" panels. You can have as many as you like, arrange them on screen as you like. Easily refer between Query results, re-run, re-use, or simply have it hang around as SQL sticky-notes.
    (Screenshot)

  • Copy your queries formatted to any programming language, and add your own formats with the Codify utility.


  • Easily import any tabular formatted files, CSV, Spreadsheets, SYLK, broken files, etc; Set your own format specification and fix files as you import them.

  • When Importing CSV or separated file:
    • Understand the size, cardinality, type and presence of import data and then edit it before the import
    • Model the table layout on the import data
    • Set your own column types or let the importer automate it for you.
    • This feature is especially helpful when importing "dirty" data
    • (Screenshot: Importing Separated data)

  • Easily import huge files directly - i.e. files too large to be viewed in an editor.
    • Simply set the import parameters based on the first chunk of data in the file.
    • Choose to import to:
      • a New Table,
      • an existing Table (Update data), or
      • a new CSV file.
    • Import, and Save your settings/layout to easily re-de the import later with a new file or updated data.
    • (Screenshot: Importing Raw data)

  • Export or copy results in favoured formats - CSV, Excel / OpenOffice Calc Spreadsheet, Structured XML, scripted SQL statements or Javascript object notation (JSON) formats. (SQLitespeed is often used to quickly and easily change files from one format to another).


  • Fast Documentation access - Press [F1] in any SQL key-word in the Query-editor and the documentation for it will open instantly. On the documentation page everything is easy to find and directly linked to the on-line SQL / SQLite documentation.



New Features

Some of the latest features are listed here - but for a more complete Release log, please visit the download page...



Features & Release Log

  • New features in version 2.1.3:

    • Support standard SQLite functionality and changes up to version 3.35.4 - Not Backwards compatible with DLL's older than 3.30.0 due to newly compiled-in pragmas (See below).
    • Re-designed the Overview section and Project listings. The current selected (and visible) Tab is remembered between sessions.
    • Removed clunky sizing controls and made the layout adapt more fluidly to size preference. To make this possible, the new minimum width is 960px (up from 700px).
    • Redesigned Database-file-Search, Table-Search and Object-Search interfaces to be more intuitive. Long-running searches execute in a separate thread and can be cancelled if needed.
    • Extensive redesign of the table-comparison (Diff) tool to have a much more clear and intuitive interface with lots of speed-setting options to make finding/applying changes easier.
    • Added the "SQL Reference" page in the main Database management view, which lists all SQL keywords, functions and extended functions with short descriptions and the ability to add them to the Query by double-clicking.
    • Pop-Up help - When clicking the mouse in the Query editor, the word at the cursor is evaluated. If it is a valid SQL reserved word or function, a pop-up directly below the Query editor will show its basic use/function.
    • Similarly, When clicking the mouse in the Query editor, the closest standard SQL function found (SELECT, INSERT, CREATE, etc.) will be pre-selected in the documentation page from where it takes one click to open the on-line help.
    • Added the following new Settings (Accessible on "Settings" Tab):
      • "Ask to Confirm when closing all open DBs" - Allows the user to choose whether they want to see the "You have open DBs, close all?" confirmation when closing SQLitespeed.
      • "Show Pop-Up keyword help in Query editor" - Enable/Disable one of the other new features (See "Pop-Up help" above).
    • Implemented new management of ATTACHED Databases; Remember them and Re-Attach in the next session; Manage, add-to or remove Databases from the Attach-list, and Automatically attach/detach all databases on the list.
    • To use the new Attached Database management functions - Start by attaching one or more databases in the current connection. More menu options will become available when the list grows to 3 or more.
    • Added new PRAGMAs on the "Pragma" Tab which, though available before, only became compiled-in as standard in the provided SQLite binaries recently. These are:
      • PRAGMA function_list and its eponymous table: pragma_function_list - providing a list of registered SQL functions known to the current connection.
      • PRAGAM module_list and its eponymous table: pragma module_list - providing a list of registered Virtual Table modules known to the current connection.
    • Due to some sensible requests, changed the script editor to have the default first query to a new empty script be: "SELECT sqlite_version(), sqlite_software_id();" - information which is more pertinent and less bulky than the previous (selecting Schemata from sqlite_master).
    • Fixed an important bug whereby the Schema parser did not recognize that a named Primary Key table constraint [CREATE TABLE t(a, b, CONSTRAINT pk_1 PRIMARY KEY(a) );] makes the column it operates on a PRIMARY KEY column (a). This caused table display discrepancies and some invalid Query suggestions.
    • Fixed many small bugs and improved some interface and usability quirks thanks to bug reports and suggestions from the SQLitespeed community.

  • New features in version 2.1.2:

    • Support standard SQLite functionality and changes up to version 3.27.0 - Not Backwards compatible with DLL's older than 3.25.1 due to support for newer Window functions and UPSERT functionality.
    • Added "Plan" and "Explain" Tabs in the top-right pane to show the Query Plan and VDBE code explanation lists for the current Query.
    • Removed old "Explain" and "Query Plan" buttons from the Query Editor section.
    • Query Plan and Explain VDBE code may be copied to Sheets or Text via the respective right-click menus.
    • The main File importer (i.e. not the CSV-specific importer) now have 3 modes of Import:
      • Import to NEW Table - You set the Table name, Column names and Types,
      • Import to EXISTING Table - You pick the existing Table and match up the Column names to import via INSERT OR REPLACE,
      • Import to CSV File - You set the new File name and Column names.
    • Import configurations can be "Remembered" by using the "Save"/"Load" buttons or automatically for the Import file's name.
    • Progress bar for opening new database files (to not appear stale or "hangy" when a very large DB file takes a few seconds to open).
    • Capability to handle newer sqlite functionality: New Pragmas, UPSERTs, Window Functions, etc.
    • New (and more sensible) Keyboard short-cuts - Hover over the question-marks above the Query editor to pop up the short-cuts list.
    • Several more GUI enhancements and bug fixes - thanks to suggestions/reports from the SQLitespeed community.

  • Other features Include:

    • Complete Visual designers for tablesprimary/unique keyscheck constraintsforeign keystriggers and  indices.
    • Create new tables using other tables as templates - including their triggers and indices.
    • Alter tables and columns with adjustment of all table objects.
    • Compare tables - highlighting differences in structure and data.
    • Easy-reading graphical interpreter of Database statistics (Via Analyzer output).
    • Query stats for Time taken through different phases and Memory usage.
    • Search files among your projects, or anywhere on your computer for Databases or even specific database objects (Tables, Triggers, Indices, etc.).
      See: File and DB object Searches
    • Quick-backup (to automatic files) and full custom backups with restores confirming data version date and time before commencing.

What it isn't...

Everything that went into SQLitespeed were chosen on a basis of: "Is it faster this way?". Whenever that answer was "no", the function got axed.


Specific items excluded (as at January 2020 - this list has shrunk significantly):

  • While SQLitespeed does offer query data exports in many SQL formats, it does not offer schema or schema-data export translation to MySQL, PostGres, Oracle, MSSQL etc. at this point.

  • Linux/Unix/WinRT/MacOS versions not available currently, but are in the pipeline. Current efforts are directed at porting from Delphi to Lazarus and FPC to allow multi-platform compatibility.

Resources

The following on-line Resources provide great education, SQL insight or alternative systems for understanding and managing SQLite databases.

ResourceDescription PlatformLink
Documentation SQL as understood by SQLite Web sqlite.org/lang.html
Tutorial SQLite Database tutorial on Guru99 Web-Interactive guru99.com/sqlite-database.html
Tutorial Basic SQL tutorials on w3schools.com Web-Interactive w3schools.com/sql
DB Manager DB Browser for SQLite Cross-Platform github.com/sqlitebrowser
DB Manager SQLite-Expert Database manager Windows sqliteexpert.com

Acknowledgements

With special thanks to:
  • Everyone working on, giving feedback / suggestions, using or testing SQLitespeed - a heartfelt thank-you to all!
  • The SQLite developers: Richard Hipp, Dan Kennedy and Joe Mistachkin; The testers, users and specifically forum community who tirelessly answer all our questions.
  • Icon pack providers "FatCow" over at www.fatcow.com for excellent iconic artwork.
  • Friends and Colleagues offering direct assistance: Valerie, Davey, Ilse, Vincent and many more.
and every scientist out there...


Copyright: Right to Copy

  • This software is published under the Mozilla Public License (V.2) which you can read here:
    https://www.mozilla.org/en-US/MPL/2.0/

  • The software is free to use and free to distribute non-commercially, but does not include source code.
    • There is a plan in place to publish the source code, at which point a different license may be offered.
    • Until then, you may use, copy and share this software at your own will and risk.
    • Restrictions: You may explicitly not claim original content, trademark or attempt to sell it commercially.

  • Included SQLite Libraries, extensions and binaries are graciously offered by the SQLite community in the Public Domain - See below.

About SQLite


SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.
SQLite Public Domain Copyright notice

Included SQLite Version: 3.35.4 - 2021-04-02


Compatibility

The software is Windows™ 32-Bit based* and should work on all compatible platforms but has only been tested on 32bit XP Pro, 32/64bit Win7, 64bit Win8/8.1, 64bit Win10. It is not compatible with Win 8 R/T 64bit, and some users reported that it runs perfectly well in Wine.
Please feel free to report back on compatibility quirks on other platforms.

*Microsoft, Windows, WinXP, Win7/8/10 and all similar references are all Registered Trademarks of Microsoft Corporation.

To download page...


Disclaimer

This software is provided free of charge and free of any known malware, adware or any other 3rd-party software, but no official claims are made in this regard - please stay safe with up-to-date security and anti-virus software.

As a security-measure, File-Hashes are provided for the executable file downloads. The SQLite tools included can be verified (or re-downloaded) from www.sqlite.org/download.html.

Right to copy 2024