[sqlite] How can I get the first 10 rows in sqlite db
Hello, I’ve been adding and deleting rows from the sqlite database. Now the primary ID is non-sequential. 1. How do I get the nth entry in the database 2. AND, How do I get the first n rows from the database ? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens when PK reaches max integer
Thank you Eric and Jay. Your replies address my concerns. I mean, I'm no longer concerned :) Thanks, Kavita On 10/14/10 1:20 PM, "Jay A. Kreibich" wrote: > On Thu, Oct 14, 2010 at 12:33:11PM -0500, Kavita Raghunathan scratched on the > wall: >> Hello, >> >> My Primary Key is an integer. In the lifetime of a product, it may increase >> to the maximum possible value of an integer. Our processor will make it >> overflow into a very large negative number and so on. >> >> My specific question is, if overflow occurs, how does sqlite deal with it? >> >> 1. What if its a large negative number ? >> 2. What if there is already a PK with the rolled over value, say 1? > > http://www.sqlite.org/autoinc.html -- > > If no ROWID is specified on the insert, or if the specified ROWID > has a value of NULL, then an appropriate ROWID is created > automatically. The usual algorithm is to give the newly created row > a ROWID that is one larger than the largest ROWID in the table > prior to the insert. If the table is initially empty, then a ROWID > of 1 is used. If the largest ROWID is equal to the largest possible > integer (9223372036854775807) then the database engine starts > picking positive candidate ROWIDs at random until it finds one that > is not previously used. If no unused ROWID can be found after a > reasonable number of attempts, the insert operation fails with an > SQLITE_FULL error. If no negative ROWID values are inserted > explicitly, then automatically generated ROWID values will always > be greater than zero. > > [...] > > If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a > slightly different ROWID selection algorithm is used. The ROWID > chosen for the new row is at least one larger than the largest > ROWID that has ever before existed in that same table. If the table > has never before contained any data, then a ROWID of 1 is used. If > the table has previously held a row with the largest possible > ROWID, then new INSERTs are not allowed and any attempt to insert a > new row will fail with an SQLITE_FULL error. > >> This maybe a common problem you all may have ran into. > > No, not normally. 64-bits is a *really* big domain. > >-j ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What happens when PK reaches max integer
Hello, My Primary Key is an integer. In the lifetime of a product, it may increase to the maximum possible value of an integer. Our processor will make it overflow into a very large negative number and so on. My specific question is, if overflow occurs, how does sqlite deal with it? 1. What if its a large negative number ? 2. What if there is already a PK with the rolled over value, say 1? This maybe a common problem you all may have ran into. Hope you can help answer. Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
I see. I was not aware of this because the display results of "select * from " has always shown up in ascending order of ID. I don't recall a single instance where it was delivered out of order, and hence my assumption. Now I understand Kishore's comment better about the DB not knowing about occurance and having to use order by. Will do. Kavita On 10/14/10 12:10 PM, "Alan Chandler" wrote: > On 14/10/10 17:26, Kavita Raghunathan wrote: >> I found an example: >> Select * from order by ID where limit 1 offset n-1 >> >> Will retreive the nth row. >> In this case order by ID is probably not necessary as its already ordered >> with a few missing rows. > > It might happen to be ordered - but that is an implementation detail. > Unless you use the ORDER BY clause the database may deliver the records > in any order it wishes. It is not forced to use the ID order. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
I found an example: Select * from order by ID where limit 1 offset n-1 Will retreive the nth row. In this case order by ID is probably not necessary as its already ordered with a few missing rows. Thank you! Kavita On 10/14/10 11:15 AM, "Kavita Raghunathan" wrote: > Please see comment > > > On 10/14/10 11:02 AM, "P Kishor" wrote: > > >>> Hello, >>> I¹ve been adding and deleting rows from the sqlite database. Now the primary >>> ID is non-sequential. >>> >>> >>> 1. How do I get the nth entry in the database >>> 2. AND, How do I get the first n rows from the database ? >>> >> >> Both "nth" and "first n" depend upon the sort order. Sort the table >> the way you want, and then get the nth or the first n (use >> LIMIT/OFFSET) to get the desired rows. >> >> The db by itself has no sort order, although, if you have numeric PK, >> there is an implicit ascending sort on PK. >> > > Yes, I have numeric PK. I don't want to sort the table, the way it was added > in ascending order of PK, I want it the same way, because the entries are > historical events based on order of occurance. Now, when I added, the PK was > 1,2,3,...100. I have since say deleted 2,5,11 because the events are no > longer relavant. Now I want the first 10 entries, which will be > 1,3,4,6,7,8,9,10,12,13. You are saying I can use LIMIT/OFFSET to do that? I > do not need to order. I'm looking for the simplest and most efficient way to > do this. I know, arent we all :) > > Kavita >>> >>> Thanks, >>> Kavita >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
Please see comment On 10/14/10 11:02 AM, "P Kishor" wrote: >> Hello, >> I¹ve been adding and deleting rows from the sqlite database. Now the primary >> ID is non-sequential. >> >> >> 1. How do I get the nth entry in the database >> 2. AND, How do I get the first n rows from the database ? >> > > Both "nth" and "first n" depend upon the sort order. Sort the table > the way you want, and then get the nth or the first n (use > LIMIT/OFFSET) to get the desired rows. > > The db by itself has no sort order, although, if you have numeric PK, > there is an implicit ascending sort on PK. > Yes, I have numeric PK. I don't want to sort the table, the way it was added in ascending order of PK, I want it the same way, because the entries are historical events based on order of occurance. Now, when I added, the PK was 1,2,3,...100. I have since say deleted 2,5,11 because the events are no longer relavant. Now I want the first 10 entries, which will be 1,3,4,6,7,8,9,10,12,13. You are saying I can use LIMIT/OFFSET to do that? I do not need to order. I'm looking for the simplest and most efficient way to do this. I know, arent we all :) Kavita >> >> Thanks, >> Kavita >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
3. How can I make my primary ID remain sequential even after a delete of row. Can sqlite somehow realign the indices after a row in the middle is deleted ? On 10/14/10 10:53 AM, "Kavita Raghunathan" wrote: Hello, I’ve been adding and deleting rows from the sqlite database. Now the primary ID is non-sequential. 1. How do I get the nth entry in the database 2. AND, How do I get the first n rows from the database ? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Nth row of on sqlite DB
Hello, I’ve been adding and deleting rows from the sqlite database. Now the primary ID is non-sequential. 1. How do I get the nth entry in the database 2. AND, How do I get the first n rows from the database ? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
I'm apologise for the reminder, but I think I have become a victim of "thread takeover". Would someone please kindly answer my questions ? Kavita On 7/9/10 11:41 AM, "Kavita Raghunathan" wrote: > Thanks. Sounds like I have to use BLOBs which is not what I'm doing > currently. > > 1) I wonder about backward compatibility when I start using this BLOB > method. There are previous databases out there that don't use BLOBs. Can I > mix and match ? Or does this require the previous database to be deleted on > an upgrade ? > > 2) When you say " Use statement paramters and bind the data directly" Are > you referring to the examples in http://sqlite.org/c3ref/bind_blob.html as > pointed to by Eric Smith? > > Thanks, > Kavita > > > On 7/9/10 11:30 AM, "Jay A. Kreibich" wrote: > >> On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the >> wall: >>> Hello, >>> I?m storing encrypted passwords in the sqlite database. The encryption >>> algorithm generates ?null? character, and therefore the password >>> strings can have nulls in them. >>> >>> 1. Is this an issue for storing in database ? If strcpy is used >>> anywhere, it would be a problem >> >> They can't be stored as text values without some type of encoding >> (like base64), but they can be stored as BLOBs. >> >>> 2. I?m using sprintf to generate the SQL statement as shown below. >>> This causes a problem because sprintf stops printing when it >>> encounters ?null?. >> >> Don't do that. Use statement parameters and bind the data directly. >> >>-j > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Null character problem
Thanks. Sounds like I have to use BLOBs which is not what I'm doing currently. 1) I wonder about backward compatibility when I start using this BLOB method. There are previous databases out there that don't use BLOBs. Can I mix and match ? Or does this require the previous database to be deleted on an upgrade ? 2) When you say " Use statement paramters and bind the data directly" Are you referring to the examples in http://sqlite.org/c3ref/bind_blob.html as pointed to by Eric Smith? Thanks, Kavita On 7/9/10 11:30 AM, "Jay A. Kreibich" wrote: > On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the > wall: >> Hello, >> I?m storing encrypted passwords in the sqlite database. The encryption >> algorithm generates ?null? character, and therefore the password >> strings can have nulls in them. >> >> 1. Is this an issue for storing in database ? If strcpy is used >> anywhere, it would be a problem > > They can't be stored as text values without some type of encoding > (like base64), but they can be stored as BLOBs. > >> 2. I?m using sprintf to generate the SQL statement as shown below. >> This causes a problem because sprintf stops printing when it >> encounters ?null?. > > Don't do that. Use statement parameters and bind the data directly. > >-j ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Null character problem
Hello,
I’m storing encrypted passwords in the sqlite database. The encryption
algorithm generates “null” character, and therefore the password strings can
have nulls in them.
1. Is this an issue for storing in database ? If strcpy is used anywhere, it
would be a problem
2. I’m using sprintf to generate the SQL statement as shown below. This
causes a problem because sprintf stops printing when it encounters “null”.
Please advice.
sprintf(SqlStr, "INSERT INTO %s (AttrName, AttrEnum, AttrType, AttrValue,
ReadWrite, Entity_id) VALUES('%s', %d, %d, '%s', %d, %d);", tbl_name,
db[i]->attr_name, db[i]->attr_num, db[i]->attr_type, db[i]->attr_value,
db[i]->attr_src, entity_id);
Thanks,
Kavita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Encryption of sqlite DB
Hi, What’s the simplest way to encrypt only certain rows in an sqlite DB? If there is no way to do this (for storing passwords etc), I would like to know the best way to encrypt the whole sqlite DB. (Prefer only encrypting some rows, but if this introduces complexity, I’m willing to encrypt the whole database) kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The character "'" not liked by sqlite?
Simon and Gabriel, I'm using the C API, I'm inserting strings. One of the strings happens to have an "'" in it. I have to write extra code to parse the character and escape it, I'll do that if I have to. I have not tried the command line tool. I'll try it and get back to you. Kavita On 3/2/10 12:56 PM, "Simon Slavin" wrote: > > On 2 Mar 2010, at 6:51pm, Kavita Raghunathan wrote: > >> I notice that when I try to insert the character ³¹² as part of a string into >> the sqlite database, my updates don¹t work. Any ideas why? The same string >> without the ³¹² character works. I have not debugged to see where exactly in >> sqlite it fails. >> >> I¹m inserting a text like this: ³Rootuser¹s desktop² does not work. ³Rootuser >> desktop² works, the update to database suceeds and I¹m able to view it using >> select. > > What API or toolkit are you using ? Have you tried executing the same command > with the command-line tool ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The character "'" not liked by sqlite?
I notice that when I try to insert the character “’” as part of a string into the sqlite database, my updates don’t work. Any ideas why? The same string without the “’” character works. I have not debugged to see where exactly in sqlite it fails. I’m inserting a text like this: “Rootuser’s desktop” does not work. “Rootuser desktop” works, the update to database suceeds and I’m able to view it using select. Regards, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash inside sqlite3_step
I found that you are correct. I wasn't reading the valgrind log properly. Thank you. Kavita On 1/25/10 10:26 PM, "Dan Kennedy" wrote: > > On Jan 26, 2010, at 6:16 AM, Kavita Raghunathan wrote: > >> Following Sql query crashes in allocateCursor inside of sqlite3_step >> >> INSERT INTO EntityTbl (AttrName, AttrEnum, AttrType, AttrValue, >> ReadWrite, Entity_id) VALUES(Œimage_crc¹, 6008, 16, Œ0¹, 1, 34013184); >> >> I¹m not able to get an understanding of what I might be doing wrong. >> Please note: >> >> 1. I have successfully created EntityTbl >> 2. I have added n rows successfully into table this is row n+1 that >> causes a crash >> 3. I know previously this group asked me to use valgrind to see if >> the crash was elsewhere and I did that. I¹ve enclosed the results. > > The valgrind output seems to indicate the problem is not in SQLite, no? > Function get_default_database() has a buffer overrun in it. > >> >> Thanks, >> Kavita >> >> ==28525== Memcheck, a memory error detector >> ==28525== Copyright (C) 2002-2009, and GNU GPL'd, by Julian Seward >> et al. >> ==28525== Using Valgrind-3.5.0 and LibVEX; rerun with -h for >> copyright info >> ==28525== Command: cm >> ==28525== >> updating hw address >> sh: ifconfig: command not found >> ==28525== Syscall param write(buf) points to uninitialised byte(s) >> ==28525==at 0x62A44B: ??? (in /lib/libpthread-2.5.so) >> ==28525==by 0x80661F3: unixWrite (in /home/kraghunathan/views/ >> sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x805F1A3: sqlite3OsWrite (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x8069E19: writeJournalHdr (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x806D416: pager_open_journal (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x806D4E1: sqlite3PagerBegin (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x8071BB3: sqlite3BtreeBeginTrans (in /home/ >> kraghunathan/views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80852F5: sqlite3VdbeExec (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x808084F: sqlite3Step (in /home/kraghunathan/views/ >> sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x8080A00: sqlite3_step (in /home/kraghunathan/views/ >> sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80C2866: sf_database_mgr::set_cache_size(sqlite3*, >> int) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80C2AF4: >> sf_database_mgr::create_database(sqlite3**, char const*) (in /home/ >> kraghunathan/views/sfdev-2.0/out/usr/bin/cm) >> ==28525== Address 0x40757b9 is 9 bytes inside a block of size 2,056 >> alloc'd >> ==28525==at 0x4005903: malloc (vg_replace_malloc.c:195) >> ==28525==by 0x805F746: sqlite3MemMalloc (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x8060062: mallocWithAlarm (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x806010A: sqlite3Malloc (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80683CD: pcache1Alloc (in /home/kraghunathan/views/ >> sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x8068541: sqlite3PageMalloc (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x806BA08: sqlite3PagerSetPagesize (in /home/ >> kraghunathan/views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80713A2: sqlite3BtreeSetPageSize (in /home/ >> kraghunathan/views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80A3BF5: sqlite3Pragma (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80BDA6A: yy_reduce (in /home/kraghunathan/views/ >> sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80BE3CF: sqlite3Parser (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x80BF01E: sqlite3RunParser (in /home/kraghunathan/ >> views/sfdev-2.0/out/usr/bin/cm) >> ==28525== >> ==28525== Use of uninitialised value of size 4 >> ==28525==at 0x4007D3C: strcpy (mc_replace_strmem.c:303) >> ==28525==by 0x804FEB9: >> fls_entity::get_default_database(sf_db_tbl_t**) (in /home/ >> kraghunathan/views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x804B4E6: >> cm_entity_manager::update_entity_database(sf_entity*) (in /home/ >> kraghunathan/views/sfdev-2.0/out/usr/bin/cm) >> ==28525==by 0x804B601: >> cm_entity_manager::update_entity_database() (in
[sqlite] Crash inside sqlite3_step
Following Sql query crashes in allocateCursor inside of sqlite3_step INSERT INTO EntityTbl (AttrName, AttrEnum, AttrType, AttrValue, ReadWrite, Entity_id) VALUES(‘image_crc’, 6008, 16, ‘0’, 1, 34013184); I’m not able to get an understanding of what I might be doing wrong. Please note: 1. I have successfully created EntityTbl 2. I have added n rows successfully into table this is row n+1 that causes a crash 3. I know previously this group asked me to use valgrind to see if the crash was elsewhere and I did that. I’ve enclosed the results. Thanks, Kavita ==28525== Memcheck, a memory error detector ==28525== Copyright (C) 2002-2009, and GNU GPL'd, by Julian Seward et al. ==28525== Using Valgrind-3.5.0 and LibVEX; rerun with -h for copyright info ==28525== Command: cm ==28525== updating hw address sh: ifconfig: command not found ==28525== Syscall param write(buf) points to uninitialised byte(s) ==28525==at 0x62A44B: ??? (in /lib/libpthread-2.5.so) ==28525==by 0x80661F3: unixWrite (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x805F1A3: sqlite3OsWrite (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x8069E19: writeJournalHdr (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x806D416: pager_open_journal (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x806D4E1: sqlite3PagerBegin (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x8071BB3: sqlite3BtreeBeginTrans (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80852F5: sqlite3VdbeExec (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x808084F: sqlite3Step (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x8080A00: sqlite3_step (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80C2866: sf_database_mgr::set_cache_size(sqlite3*, int) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80C2AF4: sf_database_mgr::create_database(sqlite3**, char const*) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525== Address 0x40757b9 is 9 bytes inside a block of size 2,056 alloc'd ==28525==at 0x4005903: malloc (vg_replace_malloc.c:195) ==28525==by 0x805F746: sqlite3MemMalloc (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x8060062: mallocWithAlarm (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x806010A: sqlite3Malloc (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80683CD: pcache1Alloc (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x8068541: sqlite3PageMalloc (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x806BA08: sqlite3PagerSetPagesize (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80713A2: sqlite3BtreeSetPageSize (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80A3BF5: sqlite3Pragma (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80BDA6A: yy_reduce (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80BE3CF: sqlite3Parser (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80BF01E: sqlite3RunParser (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525== ==28525== Use of uninitialised value of size 4 ==28525==at 0x4007D3C: strcpy (mc_replace_strmem.c:303) ==28525==by 0x804FEB9: fls_entity::get_default_database(sf_db_tbl_t**) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x804B4E6: cm_entity_manager::update_entity_database(sf_entity*) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x804B601: cm_entity_manager::update_entity_database() (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x804BB59: cm_entity_manager::do_init_database(char*) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x804BCDC: cm_entity_manager::cm_entity_manager() (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80499FD: main (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525== ==28525== Invalid write of size 1 ==28525==at 0x4007D3C: strcpy (mc_replace_strmem.c:303) ==28525==by 0x804FEB9: fls_entity::get_default_database(sf_db_tbl_t**) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x804B4E6: cm_entity_manager::update_entity_database(sf_entity*) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x804B601: cm_entity_manager::update_entity_database() (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x804BB59: cm_entity_manager::do_init_database(char*) (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x804BCDC: cm_entity_manager::cm_entity_manager() (in /home/kraghunathan/views/sfdev-2.0/out/usr/bin/cm) ==28525==by 0x80499FD: main (in
Re: [sqlite] Using sqlite3: Excess memory consumed ?
Good answer. Thanks, we may have more questions for you. Kavita On 12/17/09 10:46 AM, "Simon Slavin" wrote: > > On 17 Dec 2009, at 4:38pm, Kavita Raghunathan wrote: > >> It is an option to merge tables, but the concern there is that the speed of >> lookup maybe compromised? > > The speed of lookup will be very small if you have an INDEX which matches what > the SELECT asks for. So if, for example your SELECT is > > SELECT colA, colB FROM myTable WHERE source='aaa' AND colA='' > > then the TABLE myTable should have an INDEX (perhaps the primary index) which > is on the two columns > > source,colA > > For the number of records you quote, this will make the SELECT very fast. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3: Excess memory consumed ?
Thanks. We are running the analyzer that Mr Hipp reccomended. It is an option to merge tables, but the concern there is that the speed of lookup maybe compromised? I know, we cannot have it all :-) But if there is a way we can have "close to all", please let us know. Kavita On 12/17/09 10:24 AM, "Simon Slavin" wrote: > > On 17 Dec 2009, at 3:44pm, Kavita Raghunathan wrote: > >> We have a pressing need to reduce memory consumption. Your help is >> appreciated. >> >> We have a database 672 tables, all with the same layout: 2 columns of 4 bytes >> each and 186 rows. >> Theoretically, the memory consumed should be: >> >> 672 * (2 *4 * 186) = 36 Bytes ~= 1.0 MB >> >> However, the actual memory consumed is >> >> 4.2MB >> >> Is the difference due to overhead of using Sqlite3 ? Can you recommend ways >> for us to cut down? > > There is a big overhead for each table. You have 672 tables, so you have lots > of overheads. If these are lots of tables with the same schema (same columns) > then merge them all into one table using an extra column to tell which source > each row comes from. > > Also, each table has, at least, one index: the PRIMARY INDEX. This includes a > copy of the data in any column in the primary key. So if, for example, in > each table one of your columns of 4 bytes is the primary key, then you have at > least another 0.5 MB of data in your indexes. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3: Excess memory consumed ?
Thanks for the reply. You are right we should VACUUM in cases where we are removing items. But, this question is not about removing. This question is about a fully populated database and it seems bloated to be occuping 3 times the memory. Maybe this amount of memory is considered normal since we are using SQL and sqlite. Just wanted to check if that is in fact the case. On 12/17/09 9:48 AM, "Kurt D. Knudsen" wrote: > Did you try to VACUUM the database? When items are removed from the > database, the size doesn't shrink, it just marks the pages as free. > Correct me if I'm wrong. > > Kurt > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan > Sent: Thursday, December 17, 2009 10:44 AM > To: Discussion of SQLite Database > Cc: Kelvin Xu > Subject: [sqlite] Using sqlite3: Excess memory consumed ? > > Hi, > We have a pressing need to reduce memory consumption. Your help is > appreciated. > > We have a database 672 tables, all with the same layout: 2 columns of 4 > bytes each and 186 rows. > Theoretically, the memory consumed should be: > > 672 * (2 *4 * 186) = 36 Bytes ~= 1.0 MB > > However, the actual memory consumed is > > 4.2MB > > Is the difference due to overhead of using Sqlite3 ? Can you recommend > ways for us to cut down? > > Thanks for your input, > Regards, > Kavita & Skyfiber team > ===More Info about our > database > All 672 tables look like this: Example Row below > > Index(4 bytes) Value(4 bytes) > 0x 0x > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using sqlite3: Excess memory consumed ?
Hi, We have a pressing need to reduce memory consumption. Your help is appreciated. We have a database 672 tables, all with the same layout: 2 columns of 4 bytes each and 186 rows. Theoretically, the memory consumed should be: 672 * (2 *4 * 186) = 36 Bytes ~= 1.0 MB However, the actual memory consumed is 4.2MB Is the difference due to overhead of using Sqlite3 ? Can you recommend ways for us to cut down? Thanks for your input, Regards, Kavita & Skyfiber team ===More Info about our database All 672 tables look like this: Example Row below Index(4 bytes) Value(4 bytes) 0x 0x ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Local data structures vs sqlite
Ok, sqlite group, please accept my apologies. It was thoughtless of me.
On 11/5/09 4:54 PM, "P Kishor" wrote:
> First, you shouldn't hijack a thread. If you have a new subject, start
> a new email thread instead of using an existing one.
>
>
> On Thu, Nov 5, 2009 at 4:43 PM, Kavita Raghunathan
> wrote:
>>
>> I saw a presentation on sqlite by Dr Hipp that mentioned that anytime I'm
>> storing data in structures or tables, I should be thinking about using
>> sqlite instead.
>>
>> Would it be more efficient to use the sqlite database to store a table that
>> Looks like this: where lets say I'm looking for the word "auto-align". Would
>> the query be quicker than searching through this table in a "for" or while
>> loop? Assume the table has about 200 entries. I want to know if the
>> performance will be better and if I should consider storing these constants
>> in the database.
>>
>> .
>
> Seriously, with 200 entries, you couldn't even begin to tell the
> difference in speed. You could store 200 lines in a text file, grep
> through it and find your answer just as quickly as you would any other
> way.
>
> On the other hand, if you had 200,000 or 2,000,000 entries, then
> storing the data in a table, indexing it, and searching it would
> probably be more useful in terms of speed.
>
> A long time ago I remember meeting with someone who wanted to know if
> Oracle would be suitable for storing their departmental address book.
> Apparently the address book was rather large, with several hundred
> employees. Go figure. I gently told him that yes, Oracle would be ok,
> but testing would be required to back the decision.
>
>
>
>> .
>> {"giants",e_sf_attr_pm_ethernet_giants},
>> {"last_time_cleared", e_sf_attr_pm_ethernet_last_time_cleared},
>> {"port_counters_start", e_sf_attr_pm_ethernet_port_counters_start},
>> {"port_counters_end", e_sf_attr_pm_ethernet_port_counters_end},
>> {"mac_rcv_unicast", e_sf_attr_pm_ethernet_mac_rcv_unicast},
>> {"mac_rcv_multicast", e_sf_attr_pm_ethernet_mac_rcv_multicast},
>> {"mac_rcv_broadcase", e_sf_attr_pm_ethernet_mac_rcv_broadcast},
>> {"mac_xmit_unicast", e_sf_attr_pm_ethernet_mac_xmit_unicast},
>> {"mac_xmit_multicast",e_sf_attr_pm_ethernet_mac_xmit_multicast},
>> {"mac_xmit_broadcast",e_sf_attr_pm_ethernet_mac_xmit_broadcast},
>> {"mac_rcv_octet", e_sf_attr_pm_ethernet_mac_rcv_octet},
>> {"mac_xmit_octet",e_sf_attr_pm_ethernet_mac_xmit_octet},
>> {"mac_delay_exceed", e_sf_attr_pm_ethernet_mac_delay_exceed},
>> {"mac_mtu_exceed",e_sf_attr_pm_ethernet_mac_mtu_exceed},
>> {"mac_in_discard",e_sf_attr_pm_ethernet_mac_in_discard},
>> {"mac_out_discard", e_sf_attr_pm_ethernet_mac_out_discard},
>> {"mac_last_time_cleared", e_sf_attr_pm_ethernet_mac_last_time_cleared},
>> {"manual_align", e_sf_attr_pm_manual_alig},
>> {"auto_align", e_sf_attr_pm_auto_align},
>> {"initial_align", e_sf_attr_pm_initial_align},
>> {"seconds_on_align", e_sf_attr_pm_seconds_on_align},
>> {"align_start_time", e_sf_attr_pm_last_align_start_time},
>> {"align_start_trigger",e_sf_attr_pm_last_align_start_trigger},
>> {"align_start_azimuth",e_sf_attr_pm_last_align_start_azimuth},
>> {"align_start_elevation", e_sf_attr_pm_last_align_start_elevation},
>> {"align_start_rssi", e_sf_attr_pm_last_align_start_rssi},
>> {"align_start_ber",e_sf_attr_pm_last_align_start_ber},
>> {"align_end_time", e_sf_attr_pm_last_align_end_time},
>> .
>> .
>>
>>
>> On 11/5/09 4:15 PM, "Beau Wilkinson" wrote:
>>
>>> I really think this warrants further discussion. Perhaps the correct answer
>>> (that ARMs implement a non-standard FP type which is incompatible with
>>> Sqlite)
>>> is already out there, but I think the issues I raised with that answer
>>> should
>>> at least be addressed.
>>>
>>> Assuming (and perhaps this is the rub...) that Sqlite is built around C++
>>> "float" and "double," then I fail to see how any FP system that is even
>>> plausibly useful could give the results cited by Mr Drozd. If I put (for
>>&g
[sqlite] Local data structures vs sqlite
I saw a presentation on sqlite by Dr Hipp that mentioned that anytime I'm
storing data in structures or tables, I should be thinking about using
sqlite instead.
Would it be more efficient to use the sqlite database to store a table that
Looks like this: where lets say I'm looking for the word "auto-align". Would
the query be quicker than searching through this table in a "for" or while
loop? Assume the table has about 200 entries. I want to know if the
performance will be better and if I should consider storing these constants
in the database.
.
.
{"giants",e_sf_attr_pm_ethernet_giants},
{"last_time_cleared", e_sf_attr_pm_ethernet_last_time_cleared},
{"port_counters_start", e_sf_attr_pm_ethernet_port_counters_start},
{"port_counters_end", e_sf_attr_pm_ethernet_port_counters_end},
{"mac_rcv_unicast", e_sf_attr_pm_ethernet_mac_rcv_unicast},
{"mac_rcv_multicast", e_sf_attr_pm_ethernet_mac_rcv_multicast},
{"mac_rcv_broadcase", e_sf_attr_pm_ethernet_mac_rcv_broadcast},
{"mac_xmit_unicast", e_sf_attr_pm_ethernet_mac_xmit_unicast},
{"mac_xmit_multicast",e_sf_attr_pm_ethernet_mac_xmit_multicast},
{"mac_xmit_broadcast",e_sf_attr_pm_ethernet_mac_xmit_broadcast},
{"mac_rcv_octet", e_sf_attr_pm_ethernet_mac_rcv_octet},
{"mac_xmit_octet",e_sf_attr_pm_ethernet_mac_xmit_octet},
{"mac_delay_exceed", e_sf_attr_pm_ethernet_mac_delay_exceed},
{"mac_mtu_exceed",e_sf_attr_pm_ethernet_mac_mtu_exceed},
{"mac_in_discard",e_sf_attr_pm_ethernet_mac_in_discard},
{"mac_out_discard", e_sf_attr_pm_ethernet_mac_out_discard},
{"mac_last_time_cleared", e_sf_attr_pm_ethernet_mac_last_time_cleared},
{"manual_align", e_sf_attr_pm_manual_alig},
{"auto_align", e_sf_attr_pm_auto_align},
{"initial_align", e_sf_attr_pm_initial_align},
{"seconds_on_align", e_sf_attr_pm_seconds_on_align},
{"align_start_time", e_sf_attr_pm_last_align_start_time},
{"align_start_trigger",e_sf_attr_pm_last_align_start_trigger},
{"align_start_azimuth",e_sf_attr_pm_last_align_start_azimuth},
{"align_start_elevation", e_sf_attr_pm_last_align_start_elevation},
{"align_start_rssi", e_sf_attr_pm_last_align_start_rssi},
{"align_start_ber",e_sf_attr_pm_last_align_start_ber},
{"align_end_time", e_sf_attr_pm_last_align_end_time},
.
.
On 11/5/09 4:15 PM, "Beau Wilkinson" wrote:
> I really think this warrants further discussion. Perhaps the correct answer
> (that ARMs implement a non-standard FP type which is incompatible with Sqlite)
> is already out there, but I think the issues I raised with that answer should
> at least be addressed.
>
> Assuming (and perhaps this is the rub...) that Sqlite is built around C++
> "float" and "double," then I fail to see how any FP system that is even
> plausibly useful could give the results cited by Mr Drozd. If I put (for
> example) the value 100.0 into a "double," and then transport or store/retrieve
> the binary representation somehow, and then take those bits and once more
> treat them as a "double," then I ought to get 100 (or at least something very,
> very close). These are the sorts of things that Sqlite should, to my mind at
> least, be doing with real number data, and it ought not to matter what the
> underlying representation is.
>
> And yet it has been put forth in this forum that such is not the case. Rather,
> the underlying representation must comply with the IEEE FP standard, or even
> basic operations will not work. And this is so certain, well-known, and
> reasonable that discussion amongst the plebians is not warranted.
>
> How is this possible architecturally? The only explanation I can fathom is
> that Sqlite depends on the underlying representation following the IEEE
> standard at the bit level. For example, when doing sorts, maybe Sqlite is
> assuming the mantissae and exponents are in the bit ranges specified by IEEE,
> and that they are represented in the specified format (e.g. excess vs.
> complement notation) as well.
>
> If this is indeed the case, I think this is a very misguided architecture.
> Depending on the bit-level representation is bad. It's a brittle design. Of
> course, it's easy for you all to intimidate anyone who has a problem with this
> architecture... the complainer is "not in compliance with the IEEE standard"
> and is thus clearly worthy of your speedy dismissal. Bah.
>
> Ultimately, I think this is an easy excuse for a bad design. Types like
> "float" and "double" are intended by their designers to abstract over many FP
> implementations. They are not just convenient macros from IEEE FP, nor should
> they be.
>
> I could go on to take issue with the IEEE standard itself. The allocation of
> bits to exponent-versus-mantissa is rigid, for example. IEEE makes no
> allowance (that I know of) for allowing a tradeoff between precision and
> dynamic range, which is a
Re: [sqlite] Crash on freeing mutex in sqlite3.c
Pavel, You're the man! If you are ever in Dallas let me know, I owe you lunch. Found my bug using valgrind and it had nothing to do with the mutex or sqlite. Kavita - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Sent: Tuesday, October 20, 2009 3:24:59 PM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c When I say "path ... completely irrelevant" it means path doesn't cause the error and it doesn't matter at all what path you're giving - you can pass an empty path and the error will still persist. ddd or gdb or whatever debugger you use - it doesn't show you memory access errors in your program (I heard totalview can do something like that but I didn't try that). But if you run your program under valgrind it will show you those types of errors. So go ahead and do it. Pavel On Tue, Oct 20, 2009 at 4:18 PM, Kavita Raghunathan wrote: > I'm using ddd, its the GUI version of gdb. > > Are you saying that the file pathname is wrong ? When you say > "path give to sqlite is completely irrelevant" do you mean "db_filename"? > I printed this path out using the debugger and it looks correct. > > And you are correct, it does die in sqlite3_initialize while trying to free > the mutex pointer but the allocation of the pointer succeeds. So still lost. > > - Original Message - > From: "Pavel Ivanov" > To: "General Discussion of SQLite Database" > Sent: Tuesday, October 20, 2009 3:06:01 PM GMT -06:00 US/Canada Central > Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c > > The only place I see this line in SQLite's code is > sqlite3_initialize(). So your path given to sqlite3_open_v2() is > completely irrelevant and FTR no other process "can have a mutex" so > that it will lead to crash of your process. The problem is that your > application corrupts memory. Run it under valgrind and see where the > problem is. > > Pavel > > On Tue, Oct 20, 2009 at 3:25 PM, Kavita Raghunathan > wrote: >> Although i have successfully opened the database several times, >> today I'm seeing crashes on this line when I use sqlite3_open_v2: >> >> sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex); >> >> Any ideas what I could be doing wrong to cause this crash ? >> >> Here is the exact way I'm using this C wrapper: >> sqlite3_open_v2(db_filename, &EntityDB, >> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL) >> >> >> Things I've already tried: >> 1) Killed any processes that are still running that might have the mutex >> 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db >> >> Thanks, >> Kavita >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash on freeing mutex in sqlite3.c
ok, thanks, will try that. - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Sent: Tuesday, October 20, 2009 3:24:59 PM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c When I say "path ... completely irrelevant" it means path doesn't cause the error and it doesn't matter at all what path you're giving - you can pass an empty path and the error will still persist. ddd or gdb or whatever debugger you use - it doesn't show you memory access errors in your program (I heard totalview can do something like that but I didn't try that). But if you run your program under valgrind it will show you those types of errors. So go ahead and do it. Pavel On Tue, Oct 20, 2009 at 4:18 PM, Kavita Raghunathan wrote: > I'm using ddd, its the GUI version of gdb. > > Are you saying that the file pathname is wrong ? When you say > "path give to sqlite is completely irrelevant" do you mean "db_filename"? > I printed this path out using the debugger and it looks correct. > > And you are correct, it does die in sqlite3_initialize while trying to free > the mutex pointer but the allocation of the pointer succeeds. So still lost. > > - Original Message - > From: "Pavel Ivanov" > To: "General Discussion of SQLite Database" > Sent: Tuesday, October 20, 2009 3:06:01 PM GMT -06:00 US/Canada Central > Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c > > The only place I see this line in SQLite's code is > sqlite3_initialize(). So your path given to sqlite3_open_v2() is > completely irrelevant and FTR no other process "can have a mutex" so > that it will lead to crash of your process. The problem is that your > application corrupts memory. Run it under valgrind and see where the > problem is. > > Pavel > > On Tue, Oct 20, 2009 at 3:25 PM, Kavita Raghunathan > wrote: >> Although i have successfully opened the database several times, >> today I'm seeing crashes on this line when I use sqlite3_open_v2: >> >> sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex); >> >> Any ideas what I could be doing wrong to cause this crash ? >> >> Here is the exact way I'm using this C wrapper: >> sqlite3_open_v2(db_filename, &EntityDB, >> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL) >> >> >> Things I've already tried: >> 1) Killed any processes that are still running that might have the mutex >> 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db >> >> Thanks, >> Kavita >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash on freeing mutex in sqlite3.c
I'm using ddd, its the GUI version of gdb. Are you saying that the file pathname is wrong ? When you say "path give to sqlite is completely irrelevant" do you mean "db_filename"? I printed this path out using the debugger and it looks correct. And you are correct, it does die in sqlite3_initialize while trying to free the mutex pointer but the allocation of the pointer succeeds. So still lost. - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Sent: Tuesday, October 20, 2009 3:06:01 PM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c The only place I see this line in SQLite's code is sqlite3_initialize(). So your path given to sqlite3_open_v2() is completely irrelevant and FTR no other process "can have a mutex" so that it will lead to crash of your process. The problem is that your application corrupts memory. Run it under valgrind and see where the problem is. Pavel On Tue, Oct 20, 2009 at 3:25 PM, Kavita Raghunathan wrote: > Although i have successfully opened the database several times, > today I'm seeing crashes on this line when I use sqlite3_open_v2: > > sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex); > > Any ideas what I could be doing wrong to cause this crash ? > > Here is the exact way I'm using this C wrapper: > sqlite3_open_v2(db_filename, &EntityDB, > SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL) > > > Things I've already tried: > 1) Killed any processes that are still running that might have the mutex > 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db > > Thanks, > Kavita > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Crash on freeing mutex in sqlite3.c
Although i have successfully opened the database several times, today I'm seeing crashes on this line when I use sqlite3_open_v2: sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex); Any ideas what I could be doing wrong to cause this crash ? Here is the exact way I'm using this C wrapper: sqlite3_open_v2(db_filename, &EntityDB, SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL) Things I've already tried: 1) Killed any processes that are still running that might have the mutex 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
Thanks much Pavel. No you are right I'm more of a L3 protocols person with some user interfaces and network security, somehow ended up trying to use sqlite now. I'll look through your link on SQL. Regards, Kavita - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Cc: "Kelvin Xu" Sent: Thursday, October 15, 2009 10:28:23 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Replacing a table I guess you didn't work with SQL anywhere in your developer life, right? Read some books or internet pages about it. You can start from here http://en.wikipedia.org/wiki/SQL and follow any links there. To update column in all rows of the table you need to issue the following statement: UPDATE table_name SET column_name = value It doesn't require you to loop through all rows although DBMS will iterate all of them for you. Pavel On Thu, Oct 15, 2009 at 11:22 AM, Kavita Raghunathan wrote: > Thanks Pavel and Owen. This is very useful information. > > Also how can we change a whole column at a time ? > In otherwords, the entire column needing to be changed would > involve looping through each entry and changing that value, > instead i want to substitute a whole column. > > Thanks! > Kavita > - Original Message - > From: "Owen O'Neill" > To: "General Discussion of SQLite Database" > Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central > Subject: Re: [sqlite] Replacing a table > > > Run the sql > 'delete from "tablename";' > > if the table definition is different (different column names or data > types ) then you will need to drop the table and create a new one. > 'drop table "tablename";' > > http://www.sqlite.org/lang_createtable.html > > if the table is huge you might get different performance depending on > whether your journal settings are to truncate or delete or pad etc. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan > Sent: Thursday, October 15, 2009 3:51 PM > To: General Discussion of SQLite Database > Cc: Kelvin Xu > Subject: [sqlite] Replacing a table > > Hi, > > Is there a way to use the sqlite wrappers to "replace" or delete a table > completely ? > (without looping through and deleting each row and column) > The number of columns and rows of the new table is identical to the > number > of columns and rows of the old table being replaced. Is there a quick > way > to do that? > > Thanks, > Kavita > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
Thanks Pavel and Owen. This is very useful information. Also how can we change a whole column at a time ? In otherwords, the entire column needing to be changed would involve looping through each entry and changing that value, instead i want to substitute a whole column. Thanks! Kavita - Original Message - From: "Owen O'Neill" To: "General Discussion of SQLite Database" Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Replacing a table Run the sql 'delete from "tablename";' if the table definition is different (different column names or data types ) then you will need to drop the table and create a new one. 'drop table "tablename";' http://www.sqlite.org/lang_createtable.html if the table is huge you might get different performance depending on whether your journal settings are to truncate or delete or pad etc. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Thursday, October 15, 2009 3:51 PM To: General Discussion of SQLite Database Cc: Kelvin Xu Subject: [sqlite] Replacing a table Hi, Is there a way to use the sqlite wrappers to "replace" or delete a table completely ? (without looping through and deleting each row and column) The number of columns and rows of the new table is identical to the number of columns and rows of the old table being replaced. Is there a quick way to do that? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Replacing a table
Hi, Is there a way to use the sqlite wrappers to "replace" or delete a table completely ? (without looping through and deleting each row and column) The number of columns and rows of the new table is identical to the number of columns and rows of the old table being replaced. Is there a quick way to do that? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] cannot add data to sql tables
Hi, I successfully did the following using C wrappers: 1) created a .db file 2) Added the tables 3) Try to insert data into the tables created. When I try to 3) insert data into the tables, all the routines pass (Prepare, Step, Finalize) without errors. But when I run my .db file through sqlite3 shell and select * from EntityTbl(which is the name of my table), nothing shows. Any ideas where my data went ? Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Error from sqlite
Thank you, that fixed the problem. Kavita - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Sent: Monday, September 21, 2009 11:33:35 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Prepare Error from sqlite > "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName > VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue > VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" In this sql string you're missing closing parenthesis at the end and apparently you also miss comma before AttrType. Pavel On Mon, Sep 21, 2009 at 12:22 PM, Kavita Raghunathan wrote: > > I just put in my sqlite wrappers and started testing. > > I get the following error and when I stepped into the sqlite code, its when > sqlite3VdbeFinalize is called: > "prepare error: near ",": syntax error" > > > I was trying to do a "sqlite3_prepare_v2" and was using the following > sqlstring and the following wrapper > "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName > VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue > VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" > > > Anyway I can get a more descriptive error ? > Thanks! > Kavita > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Error from sqlite
Correction: The error seems to be set in sqlite3VdbeSetSql because that was the last executed statement. - Original Message - From: "Kavita Raghunathan" To: "sqlite-users" Sent: Monday, September 21, 2009 11:22:15 AM GMT -06:00 US/Canada Central Subject: [sqlite] Prepare Error from sqlite I just put in my sqlite wrappers and started testing. I get the following error and when I stepped into the sqlite code, its when sqlite3VdbeFinalize is called: "prepare error: near ",": syntax error" I was trying to do a "sqlite3_prepare_v2" and was using the following sqlstring and the following wrapper "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" Anyway I can get a more descriptive error ? Thanks! Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prepare Error from sqlite
I just put in my sqlite wrappers and started testing. I get the following error and when I stepped into the sqlite code, its when sqlite3VdbeFinalize is called: "prepare error: near ",": syntax error" I was trying to do a "sqlite3_prepare_v2" and was using the following sqlstring and the following wrapper "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" Anyway I can get a more descriptive error ? Thanks! Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] About the "EnterCriticalSection" call in sample C code
Marcus, Question about the Sample C code you wrote on sqlite.org. Could you tell me if I need the "EnterCriticalSection" over the fprintf for this to work in multiple "linux" not windows threads ? Currently I am using your sample C code as reference for my C++ wrappers around Sqlite. We use centos 5.3. And thanks for the sample code, its made my life much easier. Regards, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema and database layout
- Original Message -
From: "P Kishor"
To: "General Discussion of SQLite Database"
Sent: Wednesday, September 16, 2009 9:00:29 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Schema and database layout
>Google for help with designing relational databases. You will get 36
>million hits.
KR: You lied; I only got 4 million hits :-)
Thanks!
Kavita
>
> CREATE TABLE TypeNumbers (
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
> table_type INTEGER,
> MainID INTEGER,
> Type INTEGER,
> CurrentNumber INTEGER);
>
> And here is you "table_type" that will have numbers 1 to 10 and you
> will be able "to pass it to SQLite" (whatever that means).
>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>
> It doesn't.
>
>> KR: Different threads are going to access different tables of the same table
>> type.
>
> Even more puzzles here. I thought your table type is just one table,
> it seems that you have several tables of the same type but why? And
> what's the difference between tables of different types? Does this
> mean that you have more than 10 tables? Do you have only 10 "table
> types"?
>
>> Maybe I need to think this through, even as I write this:-)
>
> That definitely will help. :-)
>
>
> Pavel
>
> On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
> wrote:
>> Please see clarifications below:
>> - Original Message -
>> From: "Pavel Ivanov"
>> To: "General Discussion of SQLite Database"
>> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
>> Subject: Re: [sqlite] Schema and database layout
>>
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables
>>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>>
>> Create 1 table with 1 additional column "schema" which will contain
>> numbers from 1 to 10.
>>
>> KR: The 10 tables contain different data, but instead of doing this
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the
>> entity-id.
>>
>> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>> strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>> strcat(sqlStr,"MainID INTEGER,");
>> strcat(sqlStr,"Type INTEGER,");
>> strcat(sqlStr,"CurrentNumber INTEGER);");
>>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>>
>>> 2)Also, do you reccomend a different .db file for each schema so that
>>> multiple threads can easily operate at the same time ?
>>
>> It depends on how your threads will operate, how often they will issue
>> sql statements, what type of sql statements and all other details
>> about your application functionality.
>>
>> KR: Different threads are going to access different tables of the same table
>> type.
>> Maybe I need to think this through, even as I write this:-)
>>
>> Thanks, Pavel!
>>
>>
>> On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
>> wrote:
>>> All,
>>> I have a couple of questions:
>>>
>>> I've been studying the C/C++ interface, and
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables
>>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>>
>>> 2)Also, do you reccomend a different .db file for each schema so that
>>> multiple threads can easily operate at the same time ?
>>>
>>> I apologise in advance if I missed it in the documentation.
>>>
>>> Thanks again to this group for your awesome support. I've got a lot of
>>> ideas from you all.
>>>
>>> Regards,
>>> Kavita
>>> ___
>>> sqlite-users mailing list
>>&g
Re: [sqlite] Schema and database layout
- Original Message -
From: "Pavel Ivanov"
To: "General Discussion of SQLite Database"
Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Schema and database layout
> KR: The 10 tables contain different data, but instead of doing this statement
> (example below) over
> and over, I want to just pass this table "type" because all my tables look
> exactly
> like table "TypeNumbers", but they have different data, depending on the
> entity-id.
You better stop telling puzzles and explain everything in thorough
details so that we could understand it.
"entity-id" - what is it? There's no such column in the table, you've
never spoken about any entity, so we can just guess... "this table
"type" - what table type? Give the definition please what you're
calling "table type". "I want to just pass this table "type" - pass
what, where and doing what? What are you doing that is hard and with
"passing table type" will be easier?
And continuing to look into my crystal ball I still don't understand
why my suggestion doesn't suit you. You have 10 tables with exactly
the same structure, exactly the same set of columns but with different
types of data. But type of data doesn't matter when it comes to
storage of this data in the database. The only thing that matters here
is the set of columns. So you can create table like this:
>KR:
I apologise, I did not intend to be vague; you made me realize how
vague I was. Also, I'm new to database design. I surely
appreciate your help, Pavel.
Here it is:
I have 10 tables with the exact same schema:
int, int, varchar(10), varchar(20). All the 10 tables have this same schema.
Each of the 10 tables belongs to 1 entity, uniquely identified by an
entity-id.
I was planning on having 10 tables named like so: typenumber_.
and not include entity_id into the schema.
But from reading your email, it sounds like i could just have 1 table, and
add entity id to it. So, add a 5th column like this:
int, int, varchar(10), varchar(20), int(this last one is the entity id and
it would be identical every 10 entries or so)
Would this be a better design ?
Can you point me to some database design docs that dictates that tables of
the same type should be 1 single table ?
CREATE TABLE TypeNumbers (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
table_type INTEGER,
MainID INTEGER,
Type INTEGER,
CurrentNumber INTEGER);
And here is you "table_type" that will have numbers 1 to 10 and you
will be able "to pass it to SQLite" (whatever that means).
>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want to do, where do you want to pass
> "schema" (do you mean table name here?) and data?
>
> KR: The above should answer this question.
It doesn't.
> KR: Different threads are going to access different tables of the same table
> type.
Even more puzzles here. I thought your table type is just one table,
it seems that you have several tables of the same type but why? And
what's the difference between tables of different types? Does this
mean that you have more than 10 tables? Do you have only 10 "table
types"?
> Maybe I need to think this through, even as I write this:-)
That definitely will help. :-)
Pavel
On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
wrote:
> Please see clarifications below:
> - Original Message -
> From: "Pavel Ivanov"
> To: "General Discussion of SQLite Database"
> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables
>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>
> Create 1 table with 1 additional column "schema" which will contain
> numbers from 1 to 10.
>
> KR: The 10 tables contain different data, but instead of doing this statement
> (example below) over
> and over, I want to just pass this table "type" because all my tables look
> exactly
> like table "TypeNumbers", but they have different data, depending on the
> entity-id.
>
> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
> strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
> strcat(sqlStr,"MainID INTEGER,");
> strcat(sqlStr,"Type INTEGER,");
> strcat(sqlStr,"CurrentNumber INTEGER);");
>
>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want t
Re: [sqlite] Schema and database layout
Please see clarifications below:
- Original Message -
From: "Pavel Ivanov"
To: "General Discussion of SQLite Database"
Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Schema and database layout
> 1) dont see a way to reuse schemas. In other words, I have say 10 tables with
> the same schema. How would I prevent doing 10 CREATE table commands ?
Create 1 table with 1 additional column "schema" which will contain
numbers from 1 to 10.
KR: The 10 tables contain different data, but instead of doing this statement
(example below) over
and over, I want to just pass this table "type" because all my tables look
exactly
like table "TypeNumbers", but they have different data, depending on the
entity-id.
strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
strcat(sqlStr,"MainID INTEGER,");
strcat(sqlStr,"Type INTEGER,");
strcat(sqlStr,"CurrentNumber INTEGER);");
> Would there be a way I can pass the schema as well as data into SQLite ?
Elaborate please what do you want to do, where do you want to pass
"schema" (do you mean table name here?) and data?
KR: The above should answer this question.
> 2)Also, do you reccomend a different .db file for each schema so that
> multiple threads can easily operate at the same time ?
It depends on how your threads will operate, how often they will issue
sql statements, what type of sql statements and all other details
about your application functionality.
KR: Different threads are going to access different tables of the same table
type.
Maybe I need to think this through, even as I write this:-)
Thanks, Pavel!
On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
wrote:
> All,
> I have a couple of questions:
>
> I've been studying the C/C++ interface, and
> 1) dont see a way to reuse schemas. In other words, I have say 10 tables with
> the same schema. How would I prevent doing 10 CREATE table commands ? Would
> there be a way I can pass the schema as well as data into SQLite ?
>
> 2)Also, do you reccomend a different .db file for each schema so that
> multiple threads can easily operate at the same time ?
>
> I apologise in advance if I missed it in the documentation.
>
> Thanks again to this group for your awesome support. I've got a lot of ideas
> from you all.
>
> Regards,
> Kavita
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schema and database layout
All, I have a couple of questions: I've been studying the C/C++ interface, and 1) dont see a way to reuse schemas. In other words, I have say 10 tables with the same schema. How would I prevent doing 10 CREATE table commands ? Would there be a way I can pass the schema as well as data into SQLite ? 2)Also, do you reccomend a different .db file for each schema so that multiple threads can easily operate at the same time ? I apologise in advance if I missed it in the documentation. Thanks again to this group for your awesome support. I've got a lot of ideas from you all. Regards, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on converting objects into relational db
Sounds worth looking at. I will, thanks!
- Original Message -
From: "Shaun Seckman (Firaxis)"
To: "General Discussion of SQLite Database"
Sent: Thursday, September 10, 2009 9:18:26 AM GMT -08:00 US/Canada Pacific
Subject: Re: [sqlite] Question on converting objects into relational db
You could either store the object as a blob in the database or if you
had your own custom collection of these objects it's possible that you
could create a virtual table that can be used as the glue between SQLite
and this collection.
-Shaun
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
Sent: Thursday, September 10, 2009 12:02 PM
To: kennethinbox-sql...@yahoo.com; General Discussion of SQLite Database
Subject: Re: [sqlite] Question on converting objects into relational db
The entity consists of an entity ID, and an attribute list. The
attribute list could
be thought of as an array of integers or vector of integers, and the
like.
The problem I'm encountering by my affinity towards object oriented is:
I dont want to keep 2 sets of data. One inside sqlite, and one to read
from sqlite and model it as an object.
It would be cool if I could store this object
in the database.
- Original Message -
From: "Ken"
To: "General Discussion of SQLite Database"
Sent: Thursday, September 10, 2009 8:45:11 AM GMT -08:00 US/Canada
Pacific
Subject: Re: [sqlite] Question on converting objects into relational db
table might have columns, integer ID and BLOB entity.
But it would be better if you could fully describe the "Entity" internal
types instead of just a var arg...
--- On Thu, 9/10/09, Kavita Raghunathan
wrote:
> From: Kavita Raghunathan
> Subject: [sqlite] Question on converting objects into relational db
> To: "sqlite-users"
> Date: Thursday, September 10, 2009, 10:30 AM
>
> I have a very high level question, not dealing with bits
> and bytes of sqlite:
>
> I currently have an object called entity_list, that has a
> list of all entities on the system.
> While thinking about it, it sounds like a list of entities
> is like having a database. Would
> it be possible to transfer the object entity-list into a
> sqlite database ?
>
>
> How would my schema look ?
>
>
> I have enclosed an idea of how my class looks. Any links to
> useful information is
> appreciated!
>
>
> Kavita
>
>
> ---
>
> class entity_list
> {
> public:
> entity_list();
> ~entity_list();
> entity *get_specific_entity(int entity_id);
> entity *add_entity(int entity_id, void *attr_list,...);
> remove_entity(int entity_id);
> vector m_entity_list;
>
>
> private:
> };
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on converting objects into relational db
Interesting thought. Want to know more.
Wouldnt using sqlite give me other benefits like easy
of reading and writing data ?
Any other reasons why I would need a database vs a simple file dump ?
- Original Message -
From: "Pavel Ivanov"
To: "General Discussion of SQLite Database"
Sent: Thursday, September 10, 2009 9:16:36 AM GMT -08:00 US/Canada Pacific
Subject: Re: [sqlite] Question on converting objects into relational db
So why do you think you need a database?
If you want just a persistent storage then maybe simple dump of memory
used by all entities into a file will be enough...
Pavel
On Thu, Sep 10, 2009 at 12:01 PM, Kavita Raghunathan
wrote:
> The entity consists of an entity ID, and an attribute list. The attribute
> list could
> be thought of as an array of integers or vector of integers, and the like.
>
> The problem I'm encountering by my affinity towards object oriented is:
> I dont want to keep 2 sets of data. One inside sqlite, and one to read
> from sqlite and model it as an object.
> It would be cool if I could store this object
> in the database.
>
> - Original Message -
> From: "Ken"
> To: "General Discussion of SQLite Database"
> Sent: Thursday, September 10, 2009 8:45:11 AM GMT -08:00 US/Canada Pacific
> Subject: Re: [sqlite] Question on converting objects into relational db
>
> table might have columns, integer ID and BLOB entity.
>
> But it would be better if you could fully describe the "Entity" internal
> types instead of just a var arg...
>
>
>
> --- On Thu, 9/10/09, Kavita Raghunathan
> wrote:
>
>> From: Kavita Raghunathan
>> Subject: [sqlite] Question on converting objects into relational db
>> To: "sqlite-users"
>> Date: Thursday, September 10, 2009, 10:30 AM
>>
>> I have a very high level question, not dealing with bits
>> and bytes of sqlite:
>>
>> I currently have an object called entity_list, that has a
>> list of all entities on the system.
>> While thinking about it, it sounds like a list of entities
>> is like having a database. Would
>> it be possible to transfer the object entity-list into a
>> sqlite database ?
>>
>>
>> How would my schema look ?
>>
>>
>> I have enclosed an idea of how my class looks. Any links to
>> useful information is
>> appreciated!
>>
>>
>> Kavita
>>
>>
>> ---
>>
>> class entity_list
>> {
>> public:
>> entity_list();
>> ~entity_list();
>> entity *get_specific_entity(int entity_id);
>> entity *add_entity(int entity_id, void *attr_list,...);
>> remove_entity(int entity_id);
>> vector m_entity_list;
>>
>>
>> private:
>> };
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on converting objects into relational db
The entity consists of an entity ID, and an attribute list. The attribute list
could
be thought of as an array of integers or vector of integers, and the like.
The problem I'm encountering by my affinity towards object oriented is:
I dont want to keep 2 sets of data. One inside sqlite, and one to read
from sqlite and model it as an object.
It would be cool if I could store this object
in the database.
- Original Message -
From: "Ken"
To: "General Discussion of SQLite Database"
Sent: Thursday, September 10, 2009 8:45:11 AM GMT -08:00 US/Canada Pacific
Subject: Re: [sqlite] Question on converting objects into relational db
table might have columns, integer ID and BLOB entity.
But it would be better if you could fully describe the "Entity" internal types
instead of just a var arg...
--- On Thu, 9/10/09, Kavita Raghunathan wrote:
> From: Kavita Raghunathan
> Subject: [sqlite] Question on converting objects into relational db
> To: "sqlite-users"
> Date: Thursday, September 10, 2009, 10:30 AM
>
> I have a very high level question, not dealing with bits
> and bytes of sqlite:
>
> I currently have an object called entity_list, that has a
> list of all entities on the system.
> While thinking about it, it sounds like a list of entities
> is like having a database. Would
> it be possible to transfer the object entity-list into a
> sqlite database ?
>
>
> How would my schema look ?
>
>
> I have enclosed an idea of how my class looks. Any links to
> useful information is
> appreciated!
>
>
> Kavita
>
>
> ---
>
> class entity_list
> {
> public:
> entity_list();
> ~entity_list();
> entity *get_specific_entity(int entity_id);
> entity *add_entity(int entity_id, void *attr_list,...);
> remove_entity(int entity_id);
> vector m_entity_list;
>
>
> private:
> };
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on converting objects into relational db
I have a very high level question, not dealing with bits and bytes of sqlite:
I currently have an object called entity_list, that has a list of all entities
on the system.
While thinking about it, it sounds like a list of entities is like having a
database. Would
it be possible to transfer the object entity-list into a sqlite database ?
How would my schema look ?
I have enclosed an idea of how my class looks. Any links to useful information
is
appreciated!
Kavita
---
class entity_list
{
public:
entity_list();
~entity_list();
entity *get_specific_entity(int entity_id);
entity *add_entity(int entity_id, void *attr_list,...);
remove_entity(int entity_id);
vector m_entity_list;
private:
};
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Importing data into sqlite
Hi All, I'm still investigating the most effective way to import the db into sqlite that we are using as our embedded database. Of the following options, which one do you think works best, and is most extensible and future-proof? 1) Make a tab/csv delimited file with database, and install it into SQLite using the C wrapper functions 2) Populate data into a C array-type table and then import that table into sqlite during run-time 3) read a tab or csv delimited file into shell and make a .db file using the shell interface ".dump" and read that .db into sqlite using the C wrapper interface I appreciate your input! Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing data into SQLite
FYI: Mac excel does not separate rows with \r, but inserts a ^M instead. (I dont have a windows machine with me, I wonder if this is Mac specific) Sqlite does not like this because a large file with many rows appears as 1 huge infinite line to sqlite. Kavita - Original Message - From: "Kavita Raghunathan" To: "General Discussion of SQLite Database" Sent: Tuesday, September 8, 2009 11:47:38 AM GMT -08:00 US/Canada Pacific Subject: Re: [sqlite] Importing data into SQLite Yes, this works. Must have been my original csv file. I was using mac based excel and I'll now try using the windows based excel. Thanks to all for the awesome support. Kavita SQLite version 3.3.6 Enter ".help" for instructions sqlite> create table test(name text, id integer); sqlite> .separator "," sqlite> .import data.csv test sqlite> sqlite> select * from test ...> ; "a",1 "b",2 "c",3 sqlite> Original Message - From: "Robert Citek" To: "General Discussion of SQLite Database" Sent: Monday, September 7, 2009 9:08:41 AM GMT -08:00 US/Canada Pacific Subject: Re: [sqlite] Importing data into SQLite On Sun, Sep 6, 2009 at 9:32 PM, Kavita Raghunathan wrote: > Timothy and all, > When I try to import a .csv, I get a segmentation fault: > 1) First I set .seperator to , > 2) Then I type .import > 3) I see "Segmentation fault" > > Any ideas ? Here's an example of how it worked for me. $ cat data.csv "a",1 "b",2 "c",3 $ sqlite3 sample.db .schema CREATE TABLE data (foo text, bar int); $ sqlite3 -separator , sample.db '.imp "data.csv" "data" ' $ sqlite3 -header -column sample.db 'select * from data ;' foo bar -- -- "a" 1 "b" 2 "c" 3 More details here, including caveats: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles Personally, I prefer to used tab-delimited files and then import by specifying the separator as a tab: $ sqlite3 -separator $'\t' sample.db '.imp "data.tsv" "data" ' This takes advantage of the bash shell's use of $'\t' to encode a tab. Regards, - Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing data into SQLite
Yes, this works. Must have been my original csv file. I was using mac based excel and I'll now try using the windows based excel. Thanks to all for the awesome support. Kavita SQLite version 3.3.6 Enter ".help" for instructions sqlite> create table test(name text, id integer); sqlite> .separator "," sqlite> .import data.csv test sqlite> sqlite> select * from test ...> ; "a",1 "b",2 "c",3 sqlite> Original Message - From: "Robert Citek" To: "General Discussion of SQLite Database" Sent: Monday, September 7, 2009 9:08:41 AM GMT -08:00 US/Canada Pacific Subject: Re: [sqlite] Importing data into SQLite On Sun, Sep 6, 2009 at 9:32 PM, Kavita Raghunathan wrote: > Timothy and all, > When I try to import a .csv, I get a segmentation fault: > 1) First I set .seperator to , > 2) Then I type .import > 3) I see "Segmentation fault" > > Any ideas ? Here's an example of how it worked for me. $ cat data.csv "a",1 "b",2 "c",3 $ sqlite3 sample.db .schema CREATE TABLE data (foo text, bar int); $ sqlite3 -separator , sample.db '.imp "data.csv" "data" ' $ sqlite3 -header -column sample.db 'select * from data ;' foo bar -- -- "a" 1 "b" 2 "c" 3 More details here, including caveats: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles Personally, I prefer to used tab-delimited files and then import by specifying the separator as a tab: $ sqlite3 -separator $'\t' sample.db '.imp "data.tsv" "data" ' This takes advantage of the bash shell's use of $'\t' to encode a tab. Regards, - Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing data into SQLite
Dan, I'm using the latest version downloaded last week: amalgamate 3-6-17. Robert, The difference between what you did and what I did was perhaps that I had excel make the .csv file, and maybe the excel version had something to do with it ? I'll retry with the exact same steps, and let you know the result. Thanks for your help! I was told I should get sqlmaestro or sqlexpert but I'm trying to do it without having to spend $100 and it sounds like that is possible. Regards, Kavita - Original Message - From: "Robert Citek" To: "General Discussion of SQLite Database" Sent: Monday, September 7, 2009 11:08:41 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Importing data into SQLite On Sun, Sep 6, 2009 at 9:32 PM, Kavita Raghunathan wrote: > Timothy and all, > When I try to import a .csv, I get a segmentation fault: > 1) First I set .seperator to , > 2) Then I type .import > 3) I see "Segmentation fault" > > Any ideas ? Here's an example of how it worked for me. $ cat data.csv "a",1 "b",2 "c",3 $ sqlite3 sample.db .schema CREATE TABLE data (foo text, bar int); $ sqlite3 -separator , sample.db '.imp "data.csv" "data" ' $ sqlite3 -header -column sample.db 'select * from data ;' foo bar -- -- "a" 1 "b" 2 "c" 3 More details here, including caveats: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles Personally, I prefer to used tab-delimited files and then import by specifying the separator as a tab: $ sqlite3 -separator $'\t' sample.db '.imp "data.tsv" "data" ' This takes advantage of the bash shell's use of $'\t' to encode a tab. Regards, - Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing data into SQLite
Timothy and all, When I try to import a .csv, I get a segmentation fault: 1) First I set .seperator to , 2) Then I type .import 3) I see "Segmentation fault" Any ideas ? Thank you! Kavita - Original Message - From: "Timothy A. Sawyer" To: "General Discussion of SQLite Database" Sent: Monday, August 31, 2009 9:11:32 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Importing data into SQLite You can use the sqlite binary to import data from a CSV file - if you do it that way you have to make sure that your data fields in the SQLite database match exactly in order the data in the CSV file. That's been my experience. The other way is to do it programmatically (Java, C++, etc). The advantage of that is you have control over the data import. Though the sqlite binary does an excellent job of importing data, it might be better to use the latter option if you want more control over the data. It's not a difficult job to do. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Monday, August 31, 2009 10:02 AM To: sqlite-users@sqlite.org Subject: [sqlite] Importing data into SQLite Hi, I'm evaluating SQLite to be used as a small embedded database in a linux environment for Skyfiber Inc. What is the best way to import data into it ? I have a bunch of entities and attributes in an excel spreadsheet. Could I import CSV ? What should be the columns (where can I read about this etc) Regards, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing data into SQLite
Thanks. One more question: Can multiple processes and threads (linux) use the db at the same time ? Kavita - Original Message - From: "Timothy A. Sawyer" To: "General Discussion of SQLite Database" Sent: Monday, August 31, 2009 7:11:32 AM GMT -08:00 US/Canada Pacific Subject: Re: [sqlite] Importing data into SQLite You can use the sqlite binary to import data from a CSV file - if you do it that way you have to make sure that your data fields in the SQLite database match exactly in order the data in the CSV file. That's been my experience. The other way is to do it programmatically (Java, C++, etc). The advantage of that is you have control over the data import. Though the sqlite binary does an excellent job of importing data, it might be better to use the latter option if you want more control over the data. It's not a difficult job to do. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Monday, August 31, 2009 10:02 AM To: sqlite-users@sqlite.org Subject: [sqlite] Importing data into SQLite Hi, I'm evaluating SQLite to be used as a small embedded database in a linux environment for Skyfiber Inc. What is the best way to import data into it ? I have a bunch of entities and attributes in an excel spreadsheet. Could I import CSV ? What should be the columns (where can I read about this etc) Regards, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Importing data into SQLite
Hi, I'm evaluating SQLite to be used as a small embedded database in a linux environment for Skyfiber Inc. What is the best way to import data into it ? I have a bunch of entities and attributes in an excel spreadsheet. Could I import CSV ? What should be the columns (where can I read about this etc) Regards, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
