Getting the size of an sqlite database

December 23rd, 2010 | No Comments »

Sometimes you need a way (probably a multi-platform way) to get the size of an sqlite database and inside the cubesql server we really need to know that information a lot of times.
In C we use a function like:

sqlite3_int64 file_size (const char *path)
{
	#ifdef WIN32
	WIN32_FILE_ATTRIBUTE_DATA   fileInfo;
	if (GetFileAttributesExA(path, GetFileExInfoStandard, (void*)&fileInfo) == 0) return 0;
	// combine two 32bit values fileInfo.nFileSizeHigh and fileInfo.nFileSizeLow to one 64 bit value
	return (((__int64)fileInfo.nFileSizeHigh) << 32 ) + fileInfo.nFileSizeLow;
	#else
	struct stat sb;
	if (stat(path, &sb) == -1) return 0;
	return sb.st_size;
	#endif
}

and it works pretty well.

In REAL Studio you can simply use the FolderItem class to get that value (using the Length property).

But there is another powerful way that’s is multi-platform and language independent, you just need the sqlite SQL language itself.

sqlite3_int64 cubesql_dbsize (sqlite3 *db)
{
	sqlite3_int64 n1, n2;
	
	n1 = cubesql_singlecolumn_select_int64 (db, "PRAGMA page_count;", 0, 0);
	n2 = cubesql_singlecolumn_select_int64 (db, "PRAGMA page_size;", 0, 0);
	
	// PRAGMA page_count * PRAGMA page_size;
	return n1 * n2;
}

Obviously the values returned by PRAGMA page_count; and PRAGMA page_size; can be used inside every application that have access to an sqlite3 database and multiplying these values represents a very easy way to get the database file without the needs to use the underline OS system calls.

SQLite and unique rowid … Something you really need to know.

December 21st, 2010 | 8 Comments »

I posted this article in my old blog on Sunday, January 21, 2007 but I think it’s so important that I decided to re-post it again here.

If you don’t want to read the whole post then just do this: Every time you create a table with sqlite make sure to have an INTEGER PRIMARY KEY AUTOINCREMENT column (the rowid column will be an alias to this one).

If you have some time then read on…

A lot of people don’t realize that a rowid can change. As always a simple example worths more than 1000 words. You can test this example with SQLiteManager.

Create a table without a primary key:
CREATE TABLE test (name TEXT);

Insert some data into the table:
INSERT INTO test (name) VALUES (‘marco’);
INSERT INTO test (name) VALUES (‘giuly’);
INSERT INTO test (name) VALUES (‘gregory’);
INSERT INTO test (name) VALUES (‘house’);

Perform a SELECT rowid,* FROM test;
Here you go the result:
1 marco
2 giuly
3 gregory
4 house

Everything seems OK (until now…)
Now delete a couple of rows:
DELETE FROM test WHERE name=’marco';
DELETE FROM test WHERE name=’gregory';

Now perform again: SELECT rowid,* FROM test;
Here it is the result:
2 giuly
4 house

Everything is fine, no?
That’s cool…

Now, perform a VACUUM on the database and run again the query:
SELECT rowid,* FROM test;
Here it is the result:
1 giuly
2 house

Rowids are changed!!!! So please take extra care when you define a table and need to reference records using rowids.
From the official documentation: “Rowids can change at any time and without notice. If you need to depend on your rowid, make it an INTEGER PRIMARY KEY, then it is guaranteed not to change”. And I add also AUTOINCREMENT so you are sure that the same rowid(s) are not reused when rows are deleted.

Testing the new server, memory leaks and code coverage with XCode

December 17th, 2010 | 2 Comments »

As you probably know SQLabs is busy developing the next generation of our sqlite based server (to be released in late January).
We want it to be rock solid and among with some unique and powerful features we are investing a lot of energies into an efficient testing process and this blog post is about some it.

The first thing I personally developed is a MacOS X 10.6 only feature (it will be used only internally for testing purpose) and it’s a debug module that helps us to track down all the memory related issues. I know that MacOS X already comes with some powerful memory debugging options but we need something unique. A server must be a rock solid product, it must have zero crashes plus it cannot leaks memory so I decided to developed a custom C module just to keep track of all memory allocations, deallocations, double frees and leaks and more important it gives us a way to track down the exact cause of the memory issues.

Standard tools are able to report some cryptic information like “you are leaking 800 bytes with a pointer 0x84332089″. But we need to know exactly where that pointer has been allocated. Our module has been developed to reply exactly to that question. When a block of memory is originally allocated we save (among other information) the exact program stack trace, so when we need to find out more information about it we can easily read the saved stack trace.

As a real world example, I tried a pre-beta version of the new server, I connected some clients, I performed some sql operations and the I closed the server. Here you go a debug output:

As you can see it seems that this release it leaks 7680 bytes and both are allocated inside the client_init function. One is 3584 bytes long and the other is 4096 bytes long. A quick view at that function:

and we was able to fix the memory leak (automatically reported) in a couple of minutes (we forgot to free client->inbuffer and client itself when it disconnects).
It’s a real powerful tool and it uses some powerful features that MacOS X gives to the developers.

Memory related issues are very important to fix but equally important are the crash that can occurs during the program lifecycle. Some crashes are due to logical issues and sometimes are difficult to discover but there are other crash categories that can be easily fixed and automatically discovered. This is where XCode and Code Coverage comes in our help.

I created a new XCode target (I duplicated my Debug target) and I named it CodeCoverage. Then I set Base SDK to MacOS X 10.6, in the Other Linker Flags I added -lgcov then I checked the Generate Test Coverage Files and the Instrument Program Flow checkboxes.

At this point when I run the server some special files are written inside the Objects-normal folder full of information about which code is executed, how many times and so on. In order to better understand and manage all this useful data I developed a custom REALbasic application that is able to automatically execute the gcov command line utility on that folder and is able display in a nice GUI the overall collected information. Final result looks like:

It’s a screenshot from an early pre-beta release but you can see that with our test we was already able to touch more than 50% of the server’s code without a crash. This does not prevents from logical errors but for simple crashes it’s a very powerful tool. With my app when you double click on a file name inside the listbox you can examine that file in more detail:

For every line inside the file you can see how many times it has been executed and in red is reported a line of code never executed. You can then use that information in order to modify the test to try to increase code coverage.

Someday I could also release my app.
Hope that the information posted in this article can be of some help to someone.

SQLiteManager 3.6.0

December 5th, 2010 | 1 Comment »

We are happy to announce the new SQLiteManager 3.6.0 version.
This update includes some important features:

  • Updated SQLite to version 3.7.3 with WAL support
  • Added support for full path in the Recent menu
  • Added some sanity checks
  • Fixed an import from mySQL sql bug
  • Fixed all Foreign Keys related bugs in the Table editor
  • Fixed some issues related to the Manage panel
  • A lot of other small fixes and optimizations

You can download you copy from http://www.sqlabs.com/sqlitemanager.php