Updated 2016-07-05 13:12:04 by Googie

Description of the software edit

What: SQLiteStudio
Where: http://sqlitestudio.pl/ (only viewable in browsers with Javascript enabled)
Description: cross-platform sqlite database manager, formerly written in Tcl. Starting with version 3.0.0 it's developed in C++/Qt (although it still supports Tcl as an embedded scripting language). Supports SQLite 3.x, SQLite 2.x. Currently tested on Linux, Solaris, MacOS X and Windows. Open source (GPL licence).
Currently at version 3.1.0.
Updated: 6/2016
Contact: http://sqlitestudio.pl/?act=contact
Forum: http://forum.sqlitestudio.pl/
Wiki: http://wiki.sqlitestudio.pl/
Bugs and ideas: http://bugs.sqlitestudio.pl/
ChangeLog: http://sqlitestudio.pl/?act=changelog

SQLiteStudio and Tcl relationship edit

Although SQLiteStudio is no longer developed in Tcl, it still supports Tcl as an implementation language for custom SQL functions, custom collations and for data populating engine, with more scriptable areas to come in future.

More details: http://wiki.sqlitestudio.pl/index.php/ScriptingTcl

A collection of Tcl scripts for SQLiteStudio: http://wiki.sqlitestudio.pl/index.php/Scripts_repository#Tcl_scripts

Quick links edit

To report bug or feature request: http://bugs.sqlitestudio.pl

To discuss on forum: http://forum.sqlitestudio.pl

Beta versions edit

Googie 2012 Sep 07: Beta 1 of 2.1.0 is available, if anyone's interested. Details at [1].

Comments and Questions edit

Problem with SQLiteStudio 1.1.1, ActiveTcl 8.5.4, and Solaris 9

LV 2009 Feb 13

While I am not generally superstitious, I hope this isn't a Friday, the 13th type problem...

I recently tried the 1.1.1 code using ActiveTcl 8.5.4, and the results were rather disappointing:
$ /vol/tclsrcsol/ActiveTcl/bin/tclsh8.5 main.tcl
Bus Error(coredump)
$ dbx /vol/tclsrcsol/ActiveTcl/bin/tclsh8.5 core
skip the reading of the shared libraries
detected a multithreaded program
[email protected] ([email protected]) terminated by signal BUS (invalid address alignment)
0xfead827c: exprAnalyze+0x00f0:         bad opcode
(dbx 1) where                                                         
current thread: [email protected]
=>[1] exprAnalyze(0x793a60, 0x47, 0x0, 0x59ffb4, 0xffbfc27c, 0x5a006c), at 0xfead827c
  [2] exprAnalyzeAll(0x793a60, 0x59ffb4, 0x0, 0xfeab50b0, 0x0, 0x1), at 0xfead79fc
  [3] sqlite3WhereBegin(0xffbfc27c, 0x793a60, 0x7938c0, 0xffbfc030, 0x1, 0x0), at 0xfeadb66c
  [4] sqlite3Select(0xffbfc27c, 0x793788, 0xffbfc0dc, 0x793858, 0x793a60, 0x742188), at 0xfead1cfc
and so on for another 17 stack levels

Unfortunately, ActiveTcl wasn't distributed with debugging symbols, so it is tough to figure out what is going wrong.

Googie - 6 of April 2009 - It looks like some problem with Sqlite3 library from your ActiveTcl installation. Maybe you should try binary distribution for Solaris? It's available on the homepage of SQLiteStudio.

Favorable comments edit

AET 26jul07

Just tried it on XP. Very nice indeed. Nice, uncluttered interface, and intuitive to use. Compliments on your web site, too.

EKB 29Jan10

I'm using this heavily in current development (for a web site with an SQLite back-end) and it is just great that it is available. It has its quirks, but the functionality is great. I can do anything I need to do, and in most cases, when I think, "It should be able to do this...", I look where I think it ought to be in the interface, and there it is. Nice design.

Discussion: add database or new database edit

There doesn't seem to be an option to open an existing database, or am I missing something? When I first started the only option under the File menu was "New Database".

JAG - I agree that it's a bit confusing, but go ahead and select "New Database". In the dialog that opens, provide a name for your database (just to represent it in the tree), then select the "Choose existing database" button to browse for your existing file.

Googie 1 Sep 2007 "New Database" is going to be changed to "Add database" in next release. I think it's less confusing.

Bryan Oakley for the sake of discussion... "Add database" is still confusing to me. I don't want to add my database to some other database (?), I simply want to open it and browse around. Does "Add" mean "Add to the GUI"? That's a bit confusing. Maybe it's because I've not used similar products in the past. Also, if I open a database and click on a table name on the left, nothing seems to happen. It's not clear what purpose there is in single-clicking on a table on the left.

Those quibbles aside, it's a pretty nice GUI. I like your attention to detail -- the "Did you know that..." and the license dialogs are very professional looking, as is the toolbar.

As a final comment -- it's not clear if changes to a database are immediate or not. I'm afraid to try something like "edit table" because it's not clear whether or not that's a reversible action. Do I get to play around with an in-memory version of the database without actually altering the live database, or do actions directly affect the underlying database? Put more succinctly, do I have to "commit" or "save" changes for them to take effect?

Googie 21 Oct 2008 Regarding "final comment" by Bryan Oakley - This is pretty hard to do without nested transactions in SQLite, but I'll think about it.

[Charlie] 29 Oct 2008 Bryan, if you want to browse around a database you created, don't click on Add database. You have to click on the database you created and in the working area you'll see the structure. Click on Data to see the data. Make sure you click on Commit new row when you enter data; else your data won't show, it won't be saved. You'll notice that when you click on Commit new row, the new row will be numbered; this means that it has been saved. So make sure to click on Commit new row after each entry and look for the row number.

About the edit table question, I have the same problem: fields that have been created don't show up on the edit table. It seems the whole database has to be recreated for each change to the database. I wonder if the data is gone then. I am afraid to try. See the question below.

A direction for this Studio: a database manager for the general public edit

[Charlie], Sunday October 19th 2008. If Pawel's goal is to write yet another piece of software for programmers, to please programmers, one piece of sofware among zillions, then it has absolutely no interest (at least as far as I'm concerned). But if the purpose is to write a piece of software for the general public, a public domain equivalent of Access then it is a very worthwhile initiative. Unfortunately, the way the program presents itself is not at all general user friendly as of now (version 1.0.0). In the first place, the average user thinks in terms of Database/Fields and not in terms of tables.

The simple steps to create a database are:

1) Create a database (or a file)

2) Add Fields

3) Add data

4) Save

The view most people are comfortable with is table view (vertical lines) and not one at a time record view (horizontal lines).

A lot of care has to be given to the interface if Pawel ever dreams of providing a public domain answer to Access. But if he wants to cater to programmers as most programmers on internet do, then his Studio will be limited to programming circles, it will never move to the main stream, it will be stuck in one place, it won't evolve and Pawel will lose interest. Too bad. Most programmers using SQL Lite are comfortable with its syntax. The general public is not. There is a gap to fill and Pawel might manage to fill this gap.

Googie - 20 Oct 2008 - It is always nice to read criticism, truly. Usually I take it seriously, but this time I have to gainsay:

1) SQLite is not like the microsoft Access database, therefore SQLiteStudio is not Access-like manager.

2) Charlie, please notice, that nobody forces users to learn SQL when they want to create SQLite database with SQLiteStudio, on the contrary - whole database schema and tables data can be created with clicking buttons and entering names. Why would it be hard for public users?

More than that - I've read that SQLiteStudio is quite popular among Open Tibia server users - they use it to edit the server parameters stored in SQLite database and I don't think they are programmers.

Anyway, interface is going to be raised very much to be more user friendly. General tests for user friendly usage are planned, also creating database schema by drawing diagrams is planned as well, so I hope it will satisfy your expectations, at least some parts :)

[Charlie] Monday October 21st 2008, 11:33 am- Thanks for your quick reply, Pavel. The best thing to do would be to grab a very simple database manager from the past, say from the DOS days and see how simple it was and why it was so simple. It'll give you ideas. As I said earlier, the general public is familiar with the notion of fields and records but not with the notion of tables.

The interface could have the following elements: Database, Table, Index, Trigger, View, Tools, Help

I asked around in the college; very few people know about tables, indexes, trigers, views, etc. Even some programmers don't have a clue about this whole thing. They all know abut Facebook and sex nevertheless. Colleges, it figures.

The best thing to do would be to do a step by step tutorial giving an example and to put it on your site: here is something that could be of interest. The purpose is to create a database with the student addresses; another database would be connected to it: a database with the student's marks for each course; another database for tuition fees paid.

If you install a wiki on your site, I could write the tutorial myself with some help from you.

Basically I was able to do the following:

1) I created a database: Stud-ID (Student ID; where is the file located?

2) I clicked on Connect and I got 4 lines under the name of my Stud-ID database: Tables, Indexes, Triggers, Views

3) I would like to create 8 fields: Family Name, First Name, Address, City, State, Country, Phone number, E-mail address and enter data in each field.

Given the fact that you're open to dialogue, that you're working hard on your project, that you program excellent stuff, we can only congratulate you on your work, your attitude and your dedication.

EKB Well, as a programmer, I was delighted to be able to access views, triggers, etc. So, it's true, this is a great support for a programmer. However, I see the point that Charlie is trying to make. Basically, he seems to be saying that if you have any interest in it, there appears to be a demand for an easy-to-use DB interface that has all the simplicity of SQLite with sharing databases (it's just a file! no more system-level DB registration!). It sounds like it isn't interesting enough for you to do for free. (Fair enough!) But you might consider doing it for money. You can keep the free version that you've got now. (Thank you!) but then release a version for the general user that you charge for. If it still isn't worth it to you, even if you got paid for it, then don't do it. What you're doing is very useful already. (And if you do decide to charge for it, check around on the web for advice for small commercial software shops -- there's a lot of good information there.)

Googie 29 Dec 2008 - EKB, so what do you expect from application for general user? Could you describe a little more how do you "see" it?

EKB Well, I'm not sure what Charlie had in mind other than what he wrote. Off of the top of my head (and without looking at Access), here's what I would think... There is a very very small audience that thinks in terms of database structure and will refactor a database into 3rd normal form. However, there is a large audience that needs a database of some sort and (although they don't know it) needs it to be factored into 3NF. So they might pay for something that would do that for them. I think the way to go about designing an application is to think about a typical problem that someone would want database software to solve, and create software to help them accomplish that goal. Some possible (very brief) scenarios:

  • A teacher wants to keep a list of students with parents' phone numbers, student birthdays, and then link that to another list that keeps track of grades.
  • An office assistant needs to keep track of office supply inventory. He also wants to keep a record of where to order different supplies, with contact information and price, and would like to monitor which department is making most use of which materials.
  • A student has an extensive collection of boyfriends that is too large to manage efficiently without software. She would like to organize them by category and keep track of when they are available. (Heh heh).

So, the people are thinking in terms of related lists and collections, rather than a "database". Keys never come into it. Ideally, the software would help them by a) automatically doing some tasks without asking, and b) asking them for input using language that is natural for the user. Does that make sense?

Googie 30 Dec 2008 - EKB, these are goals for specialized applications, not for database manager. Maybe some extensions to SQLiteStudio would provide such functionality, but for this moment I'm not interested in writing such extension(s), even if paid. Currently I focus on key features listed in TODO. When I'm finished with them I'll think about extensions for more specialized needs.

EKB That sounds good to me. For my own purposes, what you've done and what you have on your TODO are what are interesting to me. And I completely sympathize. I can understand where Charlie is coming from, but there are a lot of commercial software ventures that I'm just not interested in enough to do, even if I were paid for it. I guess that's why everything I've done on my own so far has been open source.

[William] I agree almost entirely with EKB and [Charlie].

I'd like to mention something very few people are aware of: When you write software, you have to forget about it for a good 2-3 months and when you'll get back to it, you'll see your program the same way a user sees it. THEN you'll know if it's too complex or not.

Mind you some programmers are easily able to put themselves in the place of a new user and they can tell right away if their program is intuitive or not.

If you can't do it, ask your mother to test the program, ask your father, ask a friend. Ask anyone who is not a programmer. Watch their reaction.

If they can't understand your program within the first 3 minutes, they won't be interested.

IMHO, whoever will write a user friendly SQL data entry program will make a lot of money.

I like the idea of always working on the same program and improving it constantly so I'm with you on that one, Googie. It seems to me however that a lot of people start a lot of programs but they never complete a single ONE. They seem to have problems bringing their software to fruition, I believe.

So keep it up Googie! Never get discouraged.

[Gerry W] I never was able to create a single database with SQLStudio. I think the best thing would be to provide already made databases as models. It seems to me a lot of attention has been given to the interface and the graphics (to the look) but not to the engine itself. After all a database is nothing but a series of columns that can be sorted. What's so hard about it! I wish this baby could only work! Maybe by providing a database I'll finally see a database in SQL Studio.

Googie - 23 October 2011 - Gerry, did you even bother to look into manual (especially paragraph 2.2 and later)? It's here: [2]. I'm sorry you could not create a single database, but why didn't you ask for help? I'm always willing to help. You can ask on SQLiteStudio forum here: [3]. For the record - it's SQLiteStudio, not SQLStudio. Just to not confuse anyone.

[Gerry W] The computer where I installed SQLiteStudio (and not SQLStudio, sorry!) is not connected to Internet therefore I couldn'r read your manual. Besides do I have to read a manual just to create three columns, namely field # 1, field # 2 and field # 3? Why don't you provide a database with -say- 5 fields and 10 records, a database called Address book for example?

Or better provide a database called To do list with 3 fields: * Field # 1: To do item * Field # 2: Project * Field # 3: Due date

Simply put a key to sort to To dos by project.

You can also provide a list of instructions about how to set up a database. Show step # 1, step # 2.

Just show how to set up a new To do database step by step. Write: Click on this, click on that etc.

I might -no doubt- be more stupid than everyone else but my take is this: If I wasn't able to create a database, not many people will. I have to add that I have been using databases in one form of another since 25 years. My father taught me the art of databasing. Perhaps providing solutions would be a great asset. I don't like to hear reactions like: My software is free so don't complain. True it is free but I certainly would be ready and willing to pay a hundred bucks for a database engine that works.

Googie - 25 October 2011 - I would really like to move this discussion to SQLiteStudio forum (as mentioned above), cause this is not the place. Please ask another questions there if you could.

Anyway, you need to understand nature SQLite database. The SQLite database is made of tables, indexes, triggers and views. Data is kept in tables, so if you want to put some data into database, you need to create table. So what you need to do is create database (you have button for that on toolbar or entry in the menubar), then open it (double click on it), then create table in that database (there's toolbar button for that, or right-click on database and there's menu entry). When creating table you can specify what exactly columns do you want to have. It's not that hard, isn't it?

I already noted in my TODO to write such "step-by-step" instruction in manual. Additionally the manual will be available offline starting with version 2.1.0.

I won't include "example database", because if user wants to create SQLite database with SQLiteStudio, he needs to know at least some basics (really basics) about SQLite database itself. As an example: I personally have never used Microsoft Access and if I would need to create database in Access, I won't know how to do it. Should I blame Access or my lack of knowledge? Also, have you tried other SQLite management software? I think none of them will provide "example database", because there's no sense doing so.

Finally, I'm not saying "it's free, don't complain". I take every opinion seriously, as you may noticed.

[Gerry W] If putting an example makes things simpler for some people, why say no? The wise man says: A little flexibility never hurt anyone.

Googie - 28 Oct 2011 - There will be an example in User Manual on "how to create first database, table and put a data into it". I won't provide existing database as an example with SQLiteStudio distribution, because I would break one of major SQLiteStudio adventages, the "Single binary file distribution". I think that proper chapter in User Manual will do the thing. I admit your suggestions made me to enhance the manual.

[Gerry W] I have a feeling your software does not work on Windows Vista. Anyone else has this problem?

Googie - 1 Nov 2011 - And what do you mean by that? Doesn't start? Some function does not work? I test it on Windows XP and 7. Should work on Vista just fine.

Features discussion edit

Wednesday 29 October 2008 18:23 Any hope that we could be able to sort fields A-Z or Z-A with SQLite Studio or is this sorting principle against the SQL standards. Thanks.

+ is it possible that when we click on Edit database, we have to re-type in all the fields again for the fields are not shown then. Is this more like a Recreate database function rather than an Edit Table function.

Finally in some places in the program the word Apply is used while in other places it is the word Change and in other places the word Commit is used. Wouldn't implement be a better term for all functions?

Googie - 13 Nov 2008 - Sorting by columns is done in 1.1 (still in development, as for 13 Nov 2008), broken Edit database is fixed as well. I need to take a closer look at Apply, Change and Commit use cases. I'll try to standardize them.

Open source discussion edit

Larry Smith I regret putting it so harshly, but the above statement "Open Source BUT..." means that it is NOT Open Source. "Open Source" has a precise meaning, and the use of the term should be governed by the Open Source Initiative [4], and should not be used willy-nilly for what amounts to a source-available proprietary product. True open source means commercial usage is okay, modification is okay and redistribution is okay. The above statement denies all of these and is therefore NOT "Open Source." You should read [5] before you use the term again.

Kevin Walzer For better or worse, the OSI does not have a trademark on the phrase "Open Source." So there is nothing to prevent anyone from using the term as they see fit.

LV I know that I always bristle when I see people create their own meanings for terms like freeware or public domain . And when OSI created a definition of open source , I wasn't really certain that it was a good idea - seemed to me that it would have been better to come up with a new term and leave the old, vague, term to continue to have the wide variety of meanings that it had grown to have.

Googie In my understanding "open source" is just a description of releasing model. It means that sources are open to read and eventually to modify, but nothing more. I don't interpret it and don't use it as filled with whole ideology. Anyway, I agree that writing it using uppercased-words makes it somehow related to "Open Source" in OSI understanding. I didn't write it like this in here, on this page (some else did), so I just fixed it at this moment.

Googie ...and one more thing. I've read some interview with Richard Stallman not so long ago. He pointed out difference between Free Software [6] and Open Source. There is a big difference. So:
 (...) commercial usage is okay, modification is okay and redistribution is okay.

is much closer to Free Software, than an Open Source. That's just a meaningless riff, because I don't force my application to be OSI Open Source compatible.

KJN It appears that the product is available with a source code license (as UNIX was in days gone by). That does not imply that the license is open-source.

[Charlie] Sunday October 19th 2008, 5h 26 pm Excellent comment by Larry Smith. There is no such thing as Open source but. It's either open source or it's not open source. There are no buts or ifs. Apparently the problem has been solved and the software is now open source without a but.

Googie 21 Oct 2008 Well, as I've already wrote above - I've never wrote Open Source. I've wrote open source. I never wrote it's Open Source in OSI understanding. I wrote its sources are open to read and I wrote a license that describes agreements related to usage of sources.

Anyway this subject has expired, since SQLiteStudio is now published on GPLv2 licence.

[Charlie] Wednesday 22 Oct 2008 OK. So it was: the source was open for reading but it was not Open Source per se. It did not comply with the Open Source criteria as we all know them. Not capitalizing O and S made all the difference. I understand the GPLv2 license suits you better so I guess we have all come to an understanding about your license. Thanks for making it all clear for everyone.

MHo 2011-12-07: I'm getting ./sqlitestudio: Invalid argument on Solaris.

Googie 2011-12-07: Which binary exactly are you using? Is your Solaris ix86 or Sparc?

LVwikignome - 2011-12-07 13:26:41

MHo - is this executing as a tclkit, or using tclsh? I've seen peculiar things if I tried to run a packaged application file against a version of tclkit significantly different (and what that means I am not certain) version of the interpreter used when creating the packaged application.

Googie 2011-12-07: Assuming that MHo uses binary distribution of SQLiteStudio from its homepage build for Solaris, than he is running starkit with a tclkit boundled within.

MHo 2012-11-28: What command line parameters are available for sqlitestudio? On Windows, the program should accept backslashes as well as forwardslashes in folder- or filenames.

Googie 2013-01-20: Sorry for late response. I just noticed this question. SQLiteStudio does support both forwardslash and backslash in db path. About command line options - I just checked that on Windows you cannot see anything because stdout is being redirected. I will fix that in 2.1.1. As for version 2.1.0 (and 2.1.1) supported options are:
| SQLiteStudio (v2.1.1) [http://sqlitestudio.one.pl]|
| Command line syntax:
| ./main.tcl options [database file to open] ...
| Supported options:
| --create, -c <file> - Creates new SQLite3 database and opens it.
| --debug, -d         - Enables debug messages on STDOUT.
| --localbugs <file>  - Bug reports goes to specified local file.
| --help, -h          - Prints this help.
| --tcllibs <paths>   - Adds specified directories to Tcl library path.
|                       The <paths> is in format: path1:path2:path3:...
| --plugins <dir>     - Specifies additional directory to look for plugins in it.

LAM - 2014-10-26 14:47:49

Leaving TCL/TK ?

Would be interesting to know why the author dropped TCL in favor of C++/Qt ...

Googie: There is a forum post about this: http://forum.sqlitestudio.pl/viewtopic.php?f=16&t=334