[sqlite] SQLite Database File Size
Hi, I have started to use SQLite recently. I have an interesting situation of deciding the database schematic for my solution. In one of the tables I need to store contents of size 2000 * 20. If I could create 4 rows containing only one or two columns, my schema is quite convenient and extendable at a later time. If I restrict that data to be of 2000 rows with 20 columns, my solution is not extendable at a later time because in future I may need to extend to 25 columns. I learn that to add a column, I need to create some temp table and do copy operations (I got this details from sqlite FAQ). Here is my clarification. If I have 4 rows with two columns in each row, I find the db file size to be around 1.2 MB. But if I store the same data in 2000 rows with 21 columns, I have the DB File size of around 300KB. Why is this huge difference in file size. I am also quite new to Databases. If any one of you could clarify whether I am doing something wrong or is it a feature of Sqlite file structure? Please advise. With regards, Sankara Narayanan B
Re: [sqlite] SQLite embedded installation
Hello John, On 28 jul 2004, at 23:42, John Mistler wrote: I created an application with XCode for OS X that interfaces with a MySQL database. I would like to switch it over to SQLite. Could someone shed some light on how I go about incorporating the SQLite library into my application? For instance, what do I copy into my bundle from the tar.gz download that came from sqlite.org and where should it go? QuickLite may help you: http://www.webbotech.com At that point, does it just run as an embedded database engine when my app is launched Yes. or do I need to configure it in some way so that it will launch alongside my app? No config needed if you use QuickLite. Will the sqlite library then install automatically when a user copies the app to his hard drive? Just take a look at the examples provided. Will all of my SQL language transfer over? Refer to this document: http://www.sqlite.org/lang.html Any help would be hugely appreciated. Best regards, -- Tito smime.p7s Description: S/MIME cryptographic signature
RE: [sqlite] Storing unicode
Cleared this problem up - it was actually a conversion problem on my part (never rely on MFC to function correctly - always check!), SQLite works fine Dennis // Software for animal shelters http://www.smartpethealth.com // -Original Message- From: Dennis Volodomanov Sent: Wednesday, July 28, 2004 4:08 PM To: [EMAIL PROTECTED] Subject: [sqlite] Storing unicode Hello all, Can you please tell me how to store UNICODE in SQLite v3? I'm using the sqlite3_mprintf() functions with %q, but it doesn't handle UNICODE correctly - I pass in a CString with a UNICODE value, but on the output of this function I get an incorrect string. The program is of course compiled with all the necessary stuff for UNICODE to work and it does with other functions, it's only on the sqlite3_mprintf() generated string that there's no UNICODE stuff.
[sqlite] sqlite_compile - bad return code
Hello, I am using SQLITE 2.8.13. Until now I had no major problems. My problem is a call to sqlite_compile() with this query: "SELECT * FROM RV WHERE VH=115863566" sqlite_compile returns SQLITE_OK, but *ppVm is NULL and pzErrmsg is "out of memory". I think there are two issues here: 1. Why sqlite_compile() returns SQLITE_OK if the call failed. I found this a little unintuitive, so it's probably a (rare) bug. 2. Why out of memory on my desktop 512 RAM / Win2K / VC7 dll. However this may have to do with platform specific reasons ... Regards, Ionut Filip
Re: Re: [sqlite] C callback that returns numeric data without conversion from text?
On Tue, Jul 27, 2004 at 10:56:09PM -0700, Al Danial wrote: > Great, that fits the bill perfectly, thanks! > > I know you mentioned the code is incomplete but the usage instructions > don't match the executable's behavior: the third command line argument > (the SQL command, or argv[2]) isn't used by the code. The interactive mode > works great though, and demonstrates the prepare/step/finalize method > very clearly. > > It would be cool if sqlite came with an examples/ subdirectory that > contained this program (or ones like it). Examples can often explain > things so much more easily than text descriptions of functions. >-- Al > > Yes, I was going to use argv[2] for a binary file name, to be used with a sqlite3_bind_blob example. Mike Chirico
[sqlite] collation sequence
would it be possible to add a COLLATE keyword at the TABLE level, also, so that, if the following is executed : CREATE TABLE bla(...) COLLATE mySequence then this sequence is the default for this table, when no specific sequence is defined ? or, if this is not possible, is it possible to override the collate sequence for the whole database ? this would be useful so that we don't have to define the collate keyword for each column.
Re: [sqlite] re: ANN: SQLiteDB, a COM wrapper around sqlite has been released
Ed Porter >> The SQLite code is in the DLL (you do not need to install the SQLite3.02 dll). This wrapper is excellent! As a third party reviewer, I find it to be the best one to date! Thx for the feedback. I'll give it a shot. Fred.
Re: [sqlite] One or many db and vacuum (Improving speed)
On Wed, 28 Jul 2004, Rubens Jr. wrote: >Hi ! > >Suppose one database with 4 tables : S1, B1, B2 and B3, where S1 (small >1) has < 100 records, and B1, B2, and B3 (big tables) has > 100,000 >records each. Them entire database has +- 400 MegaBytes. These records >are inserted in ramdom order : S1, B1, B1, B1, B3, B2, B1, B1 ... etc, so >I think the database will become 'fragmented'. The tables will be fragmented. >The SQL : SELECT * FROM S1 will read all 400 megabytes to find the 100 >records ? (and became slow on a older computer) Or the Sqlite is smart >enough to seek only the sectors of database and find all records as quik >as if where only these table on database ? SQLite will only search the pages used by S1 in this case. SQLite uses page based storage, and one page can hold data for only one table or index at a time. The pages are located using a btree index, one per table and another per index. The btree for S1 will reference only S1 pages. >Is better to use another database to S1 only ? No. >I Want to find these records as fast as possible ... >The VACUUM 'desfragment' the database ? (make a copy of all tables in >sequence) ? Don't think so. VACUUM will compact the database, reclaiming unused pages and making the database smaller. If your tables are indexed, then you can make them faster by exporting the data in index order (sort by index field[s]), dropping and recreating the table, then re-importing in index order. If you mainly access tables in index order, this could be a big performance gain as the page cache will be used in a more optimal manner. If you only access single records at a time, or in random order, it won't gain you much. >If so, After a VACUUM the speed of SELECT * FROM S1 will be faster than >before the vacuum ? (since all records of S1 will be one right after >another and sqlite has not to scan all 400 Megabytes) No. > >Obs: using version 3.03 > >Thanks > >Rubens Jr. > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] HI SQLITE HELP IN GTK
Hi,
I am using sqlite embedded in gtk program, the code is giving a segmetation fault
and i am not able to figure out the error.
The func where it is giving the segmetation fault is as given below :
func()
{
int j,Crows=0,Ccols=0;
txt = gtk_editable_get_chars( GTK_EDITABLE(C_Card_Entry), 0, -1 );
printf("\nThe Card number entered is
%s",gtk_entry_get_text(GTK_ENTRY(C_Card_Entry))) ;
printf("\nXXX%s",gtk_editable_get_chars(
GTK_EDITABLE(C_Card_Entry), 0, -1 ));
/* j=sqlite_get_table_printf(p_db,"SELECT C_SmartCardNo FROM Cust where
C_SmartCardNo = '%s'",_Result, , ,
,gtk_entry_get_text(GTK_ENTRY(C_Card_Entry)));*/
// j=sqlite_get_table(p_db,"SELECT C_SmartCardNo FROM Cust where ",_Result,
, , );
//printf("\nThe ouput %s",C_Result[1]);
if(Crows !=0){
/*sqlite_get_table_printf(p_db, "UPDATE Cust set C_Status = %s WHERE
C_SmartCardNo
='%s';",NULL,NULL,NULL,NULL,flag,gtk_entry_get_text(GTK_ENTRY//(C_Card_Entry)));
*/
quick_message("Card present ");}
else
quick_message("Card Number Not Present");
gtk_entry_set_text(GTK_ENTRY(C_Card_Entry),"");
return 0;
}
Best Regards,
Bibin Bhanu
[sqlite] One or many db and vacuum (Improving speed)
Hi ! Suppose one database with 4 tables : S1, B1, B2 and B3, where S1 (small 1) has < 100 records, and B1, B2, and B3 (big tables) has > 100,000 records each. Them entire database has +- 400 MegaBytes. These records are inserted in ramdom order : S1, B1, B1, B1, B3, B2, B1, B1 ... etc, so I think the database will become 'fragmented'. The SQL : SELECT * FROM S1 will read all 400 megabytes to find the 100 records ? (and became slow on a older computer) Or the Sqlite is smart enough to seek only the sectors of database and find all records as quik as if where only these table on database ? Is better to use another database to S1 only ? I Want to find these records as fast as possible ... The VACUUM 'desfragment' the database ? (make a copy of all tables in sequence) ? If so, After a VACUUM the speed of SELECT * FROM S1 will be faster than before the vacuum ? (since all records of S1 will be one right after another and sqlite has not to scan all 400 Megabytes) Obs: using version 3.03 Thanks Rubens Jr.
[sqlite] Storing unicode
Hello all, Can you please tell me how to store UNICODE in SQLite v3? I'm using the sqlite3_mprintf() functions with %q, but it doesn't handle UNICODE correctly - I pass in a CString with a UNICODE value, but on the output of this function I get an incorrect string. The program is of course compiled with all the necessary stuff for UNICODE to work and it does with other functions, it's only on the sqlite3_mprintf() generated string that there's no UNICODE stuff.
