From SQLiteStudio
Jump to: navigation, search

My table has huge binary files in it. Is it safe to just load its data?

Quick answer: yes, it's safe.

Long answer: When SQLiteStudio executes query, or loads data for a table, it doesn't load entire data at once. There are 2 limits applied:

  • Amount of rows is limited to 1000 per single page (or other value, defined in configuration dialog),
  • Amount of bytes in every single cell is limited to 10000, so even you have table with 30 columns and every column has values like 100MB each, SQLiteStudio will still load only 10000*30*1000 of bytes, which is about 300MB of data into memory. The calculation is made like this: 10000 of bytes per cell * 30 columns * 1000 rows per page. SQLiteStudio loads full cell data when necessary (i.e. when you're editing cell value, or when you're viewing it in the form view).

Don't worry about memory consumption of results from huge tables. In 99% of cases you're safe. You might start considering when you're querying table with 100 columns (each containing megabytes of data) and you have changed the "rows per page" in configuration to 2000 or even more (don't do that, unless you really need to).

I want to define more than one "CHECK" condition on my column. Can I do that?

Yes. In the column dialog you can switch to "advanced mode" by clicking on the bottom-left checkbox. There you can define as many column constraints of any kind, as you want.

I want single a "FOREIGN KEY" to reference multiple columns. I don't want separate "FOREIGN KEY" constraints for each column. I want one constraint for multiple columns. Is it possible?

Yes. For multiple-column "FOREIGN KEY" (and "UNIQUE" and "PRIMARY KEY") you will have to creata a Table constraint, instead of Column constraint. To do that, quit the column dialog, go back to the table window and on the bottom side of the window you have a list of Table constraints. Add new table constraint from toolbar above it.

I've entered data to a table, but it's not saved into the database. Why?

  1. Most probably you didn't commit your changes, did you? When you edit data, modified cells get the blue outline, meaning that the uncommited data is pending for commit. You have to commit it with the "Commit" button on the toolbar.
  2. Sometimes the new data you entered doesn't comply with table constraints. In that case when trying to commit data you will get the error message telling which constraint was violated. Also the cell which caused the problem will now be outlined with the red color, not blue. Fix the data and try to commit again.
  3. If you are sure you successfully commited the data (modified cells are not outlined with blue or red anymore), but the data is still not in the database, you should try to close SQLiteStudio, start it again, open the same data and see if SQLiteStudio sees it as old value, or the new = modified value. Then:
    1. If the data remained as old value, than this is a bug in SQLiteStudio and you should report it. See "SQLiteStudio crashed! What do I do?" to learn how to report a bug.
    2. If the data is the new, modified value, then you're modifying different database. In SQLiteStudio move your mouse cursor over the database you're modifying, hold it there for a moment, you will see a tooltip, which will tell you what is the full path to the database file. Then compare it with the database that you're using later on.
      1. If databases are different, then you already know - you are accessing different databases in different applications. Fix it.
      2. If databases are the same, than maybe the other application uses some kind of caching? Or the filesystem uses caching? Anyway, SQLiteStudio sees the changes even after restart, so the database file was definitely updated and the problem is somewhere else.

My query window contains several queries, but when I execute them, only one query is actually executed

By default SQLiteStudio executes only a single query - the one in which the insertion cursor is. If you prefer to execute all queries typed in the editor, you can do it in two ways:

  • Go to configuration dialog and uncheck this option:

Execute current.png

  • Leave the configuration as it was, but instead select entire contents of the editor and then execute. SQL Editor window always executes the selected contents. If no contents is selected, then the option shown on picture above decides what's executed.

I select a database on the databases list, then I execute query in SQL Editor window, but it's being executed in a different database

SQL Editor window has s "working database" selected in its toolbar (marked as number 2 on the picture):

Selected db.png

Whichever database is selected in that combo box will be used to execute the query in this editor.

The database selected in the databases list (marked as 1 on the picture) will be a default "working database" selected for a new SQL Editor window that you may open, but it doesn't decide current execution database for every SQL Editor window.

I want SQLiteStudio to make me a cup of coffe. Can it do that?

Not yet, but it's planned ;)