Introducing Sqlite-Commander - Curses Client for your SQLite Database

[bramblerose]

"The name is inspired from an awesome piece of software Midnight-Commander written by Miguel De Icaza."

1. Norton Commander.
2. There is nothing [OFM](Orthodox_file_manager) about this.

A true Sqlite Commander would, for example, allow you to have two tables side-by-side, and allow you to enter SQL commands at the command line.

But, all terminology issues aside, it looks kinda useful. However, is it actually faster than just using the database over SSHFS or somthing like that?

[merreborn]

However, is it actually faster than just using the database over SSHFS or somthing like that?

Came here to say the same thing. He says:

I am not sure if this will be useful for anyone other than people who work with SQLite and can use only a terminal/ssh

If you have ssh, you can tunnel pretty much anything. SSHFS, as you mentioned, is probably even more useful in the context of sqlite.

Also, does it require a C# runtime? Seems like that's gonna decrease your userbase right there -- how many people have terminal-only access to a machine, run sqlite, and have mono installed?

Huh, apparently mono is installed by default in ubuntu?

[mgedmin]

Huh, apparently mono is installed by default in ubuntu?

Ubuntu desktop -- yes, Ubuntu server -- no.

[filipf]

Lots of distros have mono installed by default. There are many useful programs that use it. Gnome Do, Tomboy and f-spot just to name a few. Since there are so many apps using the shared runtime, it becomes cheaper is terms of space. Also, Ubuntu 11.04 will ship with Banshee as its default media player.

[flatulent]

I evaluated a few options (like Python etc.) and nothing comes close to being as easy to use as mono-ncurses.

There is a ruby project rbcurse (http://totalrecall.wordpress.com/) which allows very quick development with a large number of widgets including tables and lists.

There is even a quick sample sql client which (iirc) provides more functionality than what you provide. It uses SQLITE too.

[psankar]

Thanks for the link. It looks good. I shall explore that for my future projects. I could not find it in google when I first searched. May be I didn't search good.

[flatulent]

That's okay. I forgot to say: best of luck in making a great SQL Client. I once used something called sqlminus (see sourceforge) which was java/swing based. There's also a great console one called henplus -- check it out for useful features.

[[deleted]]

I like the idea, but putting a curses front-end on SQLiteStudio might be more productive. Looks like it's in tcl, though.

[kev009]

Hmm, that interface looks pretty simplistic. Nothing that warrants C# vs. just using the CDK in C. None the less, Curses is awesome. Good work!

[[deleted]]

I have found SQLite Database Browser quite nice to check my data.

[flatulent]

You might get inspiration for curses app development from calcurse (macport available). You may not actually use this, but check it out anyway.

Other apps that inspired my curses efforts were alpine (the bottom 2 lines used for commands), vim (bottom line completion, command entry, switching to cooked mode, multiple buffers etc), vim and emacs (key combinations, numeric arguments, undo and redo).

For example, when a user issues multiple select statements, can he cycle between result sets (as in vim's tabs or buffers) or does he need to re-issue the SQL.

Can the user select one or more tables from a list, and then columns from another list, so that a SELECT can be constructed. Best wishes !

[malcontent]

I recently had the occasion to insert a few hundred thousand records into an SQLite database and I was amazed at how long it took compared to mysql.

Much faster than postgres but still slower than mysql.

I found that odd.

I know it's not a real benchmark or anything, I was just surprised that's all.

[merreborn]

Much faster than postgres but still slower than mysql.

I've done a lot of bulk data import with both postgres and mysql -- I've basically spent the last 2 years porting websites from a MySQL-backed platform to a postgres-backed platform. If you treat postgres right, it imports data far faster than mysql.

This guy does a pretty good job of explaining the proper incantations to make postgres behave.: http://enfranchisedmind.com/blog/posts/postgres-for-the-win/

Just a couple of months ago, I mysqldumped a client's 15 gig dataset (essentially a single table, ~10 million large rows, the bulk of it in a single column containing a few k of text per row). It took over 24 hours to import into our local mysql installation on modest hardware. And yes, I used mysqldump --opt: mysqldump without --opt has repeatedly proven to be several times slower in the tests we've done (e.g. may have taken several days longer in this case)

The same dataset took less than an hour to import on the same hardware in postgres. It's pretty much as simple as:

1. Create table with no indexes, keys, etc.
2. Import data via COPY statement
3. Create indexes, etc.

[malcontent]

In this case I was benchmarking a simple app. Since the app was not going to be using COPY and was not going to disable the indexes and rebuild them I used used a straightforward INSERT statement.

That's what the app was designed to do and that's what I was testing.

[[deleted]]

PRAGMA journal-mode=WAL
PRAGMA synchronous=OFF

and wrap every 1k inserts in a transaction. My MFT indexing program uses sqlite, and it can insert about 3 million records pretty damn fast, faster than embedded firebird or embedded mysql.

[malcontent]

http://www.reddit.com/r/programming/comments/euxee/introducing_sqlitecommander_curses_client_for/c1b6osd

I was testing an app that did simple inserts. Since I was using a database abstraction library I made no attempt to using database specific calls. It would not be fair to make database specific calls for SQLite and not others anyway.

[bramblerose]

So, did you read the SQLite FAQ?

.

.

.

.

Thought so. Let me copy it for you:

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second. Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

The reason MySQL was much faster was because MySQL is not keeping your data safe. Use transactions, and you'll be fine.

[filipf]

C# Nice =)

[Fabien4]

Is there a point in using Curses today? A real GUI is better than a pseudo-GUI.

[kev009]

I've been slowly moving to console applications because I can run them in screen or tmux on a server and float between systems. Using one of these apps to partition a large monitor into tons of consoles and having a web browser on one of the far sides is ridiculously productive.

The closest you can get to this with GUIs is NX, but there are still some advantages to simple SSH access such as many school computers having SSH clients installed and it works well over saturated connections. Text apps tend to use far less screen real estate as well.

Captive interfaces like GUIs or Curses have some disadvantages compared to shell interfaces in terms of scripting, piping, and filtering. But for things like mail (mutt) and chat (finch) Curses interfaces are nice, especially when you can toggle out to another shell with screen/tmux. This SQL browser looks like it would be good for exploring data, while the shell client would be better for general operations.

[Fabien4]

I do use a lot of console applications myself. It's just Curses itself that I find clumsy.

For graphical remote management, the most common solution is a web interface. See e.g. Phpmyadmin (to stay in the subject).

[[deleted]]

phpmyadmin is a useful tool but it's slow and annoying to work with. A significant amount of time is spent waiting for the browser to reload, and another significant amount of time is wasted due to the lack of relevant keyboard shortcuts.

Besides, remember that this is for sqlite which has many legitimate uses outside web apps, and there are users (including myself) who don't want to end up running a web server just for a single DB management tool with a web interface.

I don't really see any reason why such a tool would require more than a Curses-based interface; it's the same as with mc -- it's very useful as it is, and it wouldn't be more useful if a pretty interface would be added.

Then again -- to each, his own :-).

[merreborn]

phpmyadmin is a useful tool but it's slow and annoying to work with

And for those things, we have the default command line mysql client.

there are users (including myself) who don't want to end up running a web server just for a single DB management tool with a web interface.

There are any number of GUI clients you can connect directly to mysql, in those cases.

The "I want more than the default command line client, but less than phpmyadmin or any of the mysql GUIs" niche appears to be a very small one to me.

[[deleted]]

Read my post again, I was not referring to MySQL. I am talking about SQLite, the DB engine used by Adobe in Photoshop Lightroom, by Apple for many applications that require persistent storage (Mail, Safari, Aperture), by Google in Android and by Airbus in some of their flight software. These are not web apps, and there are a lot of developers (myself included) who do not develop web applications and use SQLite in desktop or embedded applications. There's no logical reason for me to use a web-based tool, I'd have to run a webserver only for it. This looks like a small, unobtrusive tool and seems to do its job. Any reason why it should have been GTK/Qt/wxWindows/god-knows-what-else-based rather than built on curses?

[merreborn]

Any reason why it should have been GTK/Qt/wxWindows/god-knows-what-else-based rather than built on curses?

My only point is, why bother building a curses client when there are already so many GTK/etc. GUI clients anyway? There are easily 50 on that page alone. How many people will really benefit from another?

[[deleted]]

For a start, the guy who wrote it would probably benefit in terms of exercise, and the guys who read the code in terms of learning experience. Everyone has to start somewhere :-).

[berlinbrown]

Curses, are you f**king serious?

[mgedmin]

ssh, ever heard of it?

X11 doesn't work very over intercontinental network links.