Re: [sqlite] call PRAGMA page_size twice?
it is a test case for the this PRAGMA... in reality, yes, just one call is good enough. On Tue, Apr 21, 2009 at 12:37 PM, John Machin wrote: > On 21/04/2009 2:07 PM, julian qian wrote: >> HI, >> call PRAGMA page_size =xxx twice immediately, only first time it has >> effect, immediately call it second times, the value can't be changed. >> is this predefined? http://www.sqlite.org/pragma.html#pragma_page_size >> only say "The page_size pragma will only cause an immediate change in >> the page size if it is issued while the database is still empty, prior >> to the first CREATE TABLE statement" > > Do you have any good reason for wanting to call the pragma twice in a > row with two different values? Can't you just call it once with the > value that you use currently on the second call? > > ___ > 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] How to secure standalone SQLite db (Ken)
On Tue, Apr 21, 2009 at 1:43 PM, Ravi Thapliyal wrote: > Thanks Ken for replying, but I will appreciate if you brief me the process > of encryption. Check out the SQLite Encryption Extension http://www.hwaci.com/sw/sqlite/see.html Regards, Eugene Wee ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to secure standalone SQLite db (Ken)
Thanks Ken for replying, but I will appreciate if you brief me the process of encryption. Regards Ravi Message: 11 Date: Mon, 20 Apr 2009 01:26:23 -0700 (PDT) From: Ken Subject: Re: [sqlite] How to secure standalone SQLite db To: General Discussion of SQLite Database Message-ID: <514410.36231...@web81001.mail.mud.yahoo.com> Content-Type: text/plain; charset=us-ascii encryption ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] call PRAGMA page_size twice?
On 21/04/2009 2:07 PM, julian qian wrote: > HI, > call PRAGMA page_size =xxx twice immediately, only first time it has > effect, immediately call it second times, the value can't be changed. > is this predefined? http://www.sqlite.org/pragma.html#pragma_page_size > only say "The page_size pragma will only cause an immediate change in > the page size if it is issued while the database is still empty, prior > to the first CREATE TABLE statement" Do you have any good reason for wanting to call the pragma twice in a row with two different values? Can't you just call it once with the value that you use currently on the second call? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] call PRAGMA page_size twice?
HI, call PRAGMA page_size =xxx twice immediately, only first time it has effect, immediately call it second times, the value can't be changed. is this predefined? http://www.sqlite.org/pragma.html#pragma_page_size only say "The page_size pragma will only cause an immediate change in the page size if it is issued while the database is still empty, prior to the first CREATE TABLE statement" thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select raise with expression
Hi all, I've been using the raise() function in select statements in triggers to check data entry. I give it text to return a meaningful error to the user, but would like to include some specific info about the data being entered. Is this possible? The SQLite syntax suggests tat only a static string can be returned, and not an expression. For instance, I have a trigger that checks that an entered Account Code exists: create trigger "Entries update Account Code" before update of "Account Code" on "Entries" begin select raise(rollback, 'Account Code does not exist') where not exists ( select 1 from Accounts where Code = new."Account Code" ) ; end ; But I'd like to change the raise function line to return more detail about the problem: select raise(rollback, 'Failed to set Account Code = ' || new."Account Code" || ' where ID = ' || new.ID || ' because this Account Code does not exist in the Accounts table.') but SQLite won't accept it, giving an error: SQL error near line 6: near "||": syntax error Is there another way? Or can the raise() function be enhanced to allow it? Thanks, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite crashing on iPhone (or so says Apple)
>>> I have written an application for the iPhone called Grocery iQ that >>> uses SQLite. I don't link to or use the built-in SQLite library on >>> the iPhone. Instead, I compile the SQLite amalgamation into the >>> executable. The SQLite version currently being used in our app is >>> 3.6.7. >> >> I sent instructions to Brian Killen on how you can download the latest >> version of SQLite+CEROD. Perhaps recompiling will help. Are there any particular bug fixes or changes that you know of that might address my problem? I'm all for upgrading the SQLite version, it's just that we will have to do several days of testing to verify it works well, resubmit to Apple, then wait 5+ days to hear from them if it works or not. Although given their tech support response times, we may have all of that done before I ever hear back from them. >>> * before opening the database, the only other SQLite API calls are: >>> sqlite3_config(SQLITE_CONFIG_HEAP, &mSqliteMemory[0], 3145728, >>> 512); // mSqliteMemory is declared as: unsigned char >>> mSqliteMemory[3145728]; >> >> You will probably do better to allocate most of that 3MB to page cache >> using sqlite3_config(SQLITE_CONFIG_PAGECHACHE, ...). The assign 100K >> or so to each database connection's lookaside memory allocator using >> sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...) immediately after it >> is opened. With the above, usually a 100K or so is enough heap, >> though more might be required if you are holding many prepared >> statements or if you are using unusually big prepared statements. >> >> Oops. I'm late for meeting. More to follow later tonight. > > > As I was saying > > Use sqlite3_status() to actually measure your memory usage. Make > adjustments once you know how the memory is being used. Don't guess; > measure. Also remember that later versions of SQLite use less memory > for storing prepared statements, so you might want to upgrade if > memory is an issue. Limit your cache sizes using the cache_size > pragma. Make use of sqlite3_soft_heap_limit() if you need to. Or > right a custom pcache implementation that limits the amount of memory > used for the page cache. Thank you for the tips on tuning the memory usage. I will definitely use this advice when working on Grocery iQ 2.0. The way I have it working now though, I shouldn't be experiencing any problems like Apple has reported, right? If SQLite fails any allocations, it should return an error and fail gracefully, correct? -Jason ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what is the right cache_size for sqllite version 3.5.9
Thanks! I will do so. JP From: P Kishor To: General Discussion of SQLite Database Sent: Monday, April 20, 2009 5:18:27 PM Subject: Re: [sqlite] what is the right cache_size for sqllite version 3.5.9 On Mon, Apr 20, 2009 at 7:13 PM, Joanne Pham wrote: > I haven't test with different sizes at all? In other words, what Roger is gently trying to tell you is to test yourself before asking. You are the best judge of your conditions, your machines, your application. Testing is the surest way to find out the most appropriate answer for your situation. Change the cache_size then test. Then change again and test. Soon you will know the answer. You can then come back and ask the list, providing results of your test, and then folks might be able to guide to a better solution. > JP > > > > > > From: Roger Binns > To: General Discussion of SQLite Database > Sent: Monday, April 20, 2009 5:11:48 PM > Subject: Re: [sqlite] what is the right cache_size for sqllite version 3.5.9 > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Joanne Pham wrote: >> It it the right size for the cache_size? My applications have a lot of >> writes operations and can be up to millions rows per minutes. > > What results did you get when you did your testing with different sizes? > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkntDz0ACgkQmOOfHg372QShOgCghMA55nGhZJTj4EvjFNZ4sf7c > XKQAnjL5MLb6W4rkNfBJu3mS6nEdCAtP > =Vfdz > -END PGP SIGNATURE- > ___ > 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] SQLite crashing on iPhone (or so says Apple)
On Apr 20, 2009, at 6:09 PM, D. Richard Hipp wrote: > > On Apr 20, 2009, at 5:32 PM, Jason Boehle wrote: > >> I have written an application for the iPhone called Grocery iQ that >> uses SQLite. I don't link to or use the built-in SQLite library on >> the iPhone. Instead, I compile the SQLite amalgamation into the >> executable. The SQLite version currently being used in our app is >> 3.6.7. > > I sent instructions to Brian Killen on how you can download the latest > version of SQLite+CEROD. Perhaps recompiling will help. > >> * before opening the database, the only other SQLite API calls are: >> sqlite3_config(SQLITE_CONFIG_HEAP, &mSqliteMemory[0], 3145728, >> 512); // mSqliteMemory is declared as: unsigned char >> mSqliteMemory[3145728]; > > You will probably do better to allocate most of that 3MB to page cache > using sqlite3_config(SQLITE_CONFIG_PAGECHACHE, ...). The assign 100K > or so to each database connection's lookaside memory allocator using > sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...) immediately after it > is opened. With the above, usually a 100K or so is enough heap, > though more might be required if you are holding many prepared > statements or if you are using unusually big prepared statements. > > Oops. I'm late for meeting. More to follow later tonight. As I was saying Use sqlite3_status() to actually measure your memory usage. Make adjustments once you know how the memory is being used. Don't guess; measure. Also remember that later versions of SQLite use less memory for storing prepared statements, so you might want to upgrade if memory is an issue. Limit your cache sizes using the cache_size pragma. Make use of sqlite3_soft_heap_limit() if you need to. Or right a custom pcache implementation that limits the amount of memory used for the page cache. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what is the right cache_size for sqllite version 3.5.9
On Mon, Apr 20, 2009 at 7:13 PM, Joanne Pham wrote: > I haven't test with different sizes at all? In other words, what Roger is gently trying to tell you is to test yourself before asking. You are the best judge of your conditions, your machines, your application. Testing is the surest way to find out the most appropriate answer for your situation. Change the cache_size then test. Then change again and test. Soon you will know the answer. You can then come back and ask the list, providing results of your test, and then folks might be able to guide to a better solution. > JP > > > > > > From: Roger Binns > To: General Discussion of SQLite Database > Sent: Monday, April 20, 2009 5:11:48 PM > Subject: Re: [sqlite] what is the right cache_size for sqllite version 3.5.9 > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Joanne Pham wrote: >> It it the right size for the cache_size? My applications have a lot of >> writes operations and can be up to millions rows per minutes. > > What results did you get when you did your testing with different sizes? > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkntDz0ACgkQmOOfHg372QShOgCghMA55nGhZJTj4EvjFNZ4sf7c > XKQAnjL5MLb6W4rkNfBJu3mS6nEdCAtP > =Vfdz > -END PGP SIGNATURE- > ___ > 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] what is the right cache_size for sqllite version 3.5.9
I haven't test with different sizes at all? JP From: Roger Binns To: General Discussion of SQLite Database Sent: Monday, April 20, 2009 5:11:48 PM Subject: Re: [sqlite] what is the right cache_size for sqllite version 3.5.9 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joanne Pham wrote: > It it the right size for the cache_size? My applications have a lot of writes > operations and can be up to millions rows per minutes. What results did you get when you did your testing with different sizes? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkntDz0ACgkQmOOfHg372QShOgCghMA55nGhZJTj4EvjFNZ4sf7c XKQAnjL5MLb6W4rkNfBJu3mS6nEdCAtP =Vfdz -END PGP SIGNATURE- ___ 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] what is the right cache_size for sqllite version 3.5.9
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joanne Pham wrote: > It it the right size for the cache_size? My applications have a lot of writes > operations and can be up to millions rows per minutes. What results did you get when you did your testing with different sizes? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkntDz0ACgkQmOOfHg372QShOgCghMA55nGhZJTj4EvjFNZ4sf7c XKQAnjL5MLb6W4rkNfBJu3mS6nEdCAtP =Vfdz -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] what is the right cache_size for sqllite version 3.5.9
Hi All, I am currently using sqlite 3.5.9 and I have set the cache_size as below: sqlSt = sqlite3_exec(pDb, "PRAGMA cache_size = 2000 ", NULL, 0, &errMsg); It it the right size for the cache_size? My applications have a lot of writes operations and can be up to millions rows per minutes. Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ResultSetMetaData with javasqlite
Hi all,
I have ran into an issue while using the javasqlite jdbc wrapper.The
issue can be illustrated with the following code:
import java.sql.*;
public class Main {
public static void main( String[] args ) throws Exception {
Class.forName( "SQLite.JDBCDriver");
Connection cx = DriverManager.getConnection("jdbc:sqlite:/foo.db");
Statement st = cx.createStatement();
st.execute( "DROP TABLE IF EXISTS t1");
st.execute( "DROP TABLE IF EXISTS t2");
st.execute( "CREATE TABLE t1 (a int, b varchar)");
st.execute( "CREATE TABLE t2 (a int, b varchar)");
st.execute( "INSERT INTO t1 VALUES (1,'one')");
ResultSet rs = st.executeQuery("SELECT * FROM t1");
ResultSetMetaData md = rs.getMetaData();
System.out.println("Number of columns t1 = " + md.getColumnCount());
rs.close();
rs = st.executeQuery("SELECT * FROM t2");
md = rs.getMetaData();
System.out.println("Number of columns t2 = " + md.getColumnCount());
rs.close();
st.close();
}
}
Basically it seems that the result set metdata is not properly
initialized when a query returns no rows.
I guess my question is is this a bug or is it intended behavior?
Implementing a similar program with the C api I am able to get the
proper count of columns back so I would assume a bug.
I should note that I am running javasqlite 20090409 with sqlite 3.6.12
on mac os x leopard.
Thanks,
-Justin
--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite crashing on iPhone (or so says Apple)
On Apr 20, 2009, at 5:32 PM, Jason Boehle wrote: > I have written an application for the iPhone called Grocery iQ that > uses SQLite. I don't link to or use the built-in SQLite library on > the iPhone. Instead, I compile the SQLite amalgamation into the > executable. The SQLite version currently being used in our app is > 3.6.7. I sent instructions to Brian Killen on how you can download the latest version of SQLite+CEROD. Perhaps recompiling will help. > * before opening the database, the only other SQLite API calls are: >sqlite3_config(SQLITE_CONFIG_HEAP, &mSqliteMemory[0], 3145728, > 512); // mSqliteMemory is declared as: unsigned char > mSqliteMemory[3145728]; You will probably do better to allocate most of that 3MB to page cache using sqlite3_config(SQLITE_CONFIG_PAGECHACHE, ...). The assign 100K or so to each database connection's lookaside memory allocator using sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...) immediately after it is opened. With the above, usually a 100K or so is enough heap, though more might be required if you are holding many prepared statements or if you are using unusually big prepared statements. Oops. I'm late for meeting. More to follow later tonight. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite crashing on iPhone (or so says Apple)
I have written an application for the iPhone called Grocery iQ that uses SQLite. I don't link to or use the built-in SQLite library on the iPhone. Instead, I compile the SQLite amalgamation into the executable. The SQLite version currently being used in our app is 3.6.7. We have been working hard on a new update for our application, which we first submitted to Apple about 3 weeks ago. They rejected our update because they said it crashes on startup. However, in our testing (100+ hours of QA on 15+ different devices), we have never once seen the app crash like they are claiming. Hoping that maybe our first submission was just an incorrectly signed executable or a corrupt upload, I rebuilt and resubmitted the app. It got rejected again for the same reason. Apple sent us crash logs, which show it crashing deep in SQLite code. However, the crash is at an odd place. We still cannot reproduce this crash locally, so I'm reaching out for some help on this issue. The crash logs are all the same, here is the stack trace: Exception Type: EXC_BAD_ACCESS (SIGBUS) Exception Codes: KERN_PROTECTION_FAILURE at 0x000d Crashed Thread: 0 Thread 0 Crashed: 0 Grocery iQ 0x0002d006 sqlite3ExprCodeTarget (sqlite3.c:57600) 1 Grocery iQ 0x0002d648 sqlite3ExprCodeTemp (sqlite3.c:57879) 2 Grocery iQ 0x0002cde4 sqlite3ExprCodeTarget (sqlite3.c:57493) 3 Grocery iQ 0x0002d648 sqlite3ExprCodeTemp (sqlite3.c:57879) 4 Grocery iQ 0x0002cde4 sqlite3ExprCodeTarget (sqlite3.c:57493) 5 Grocery iQ 0x0002d648 sqlite3ExprCodeTemp (sqlite3.c:57879) 6 Grocery iQ 0x0002cde4 sqlite3ExprCodeTarget (sqlite3.c:57493) 7 Grocery iQ 0x0002dade sqlite3ExprCode (sqlite3.c:57894) 8 Grocery iQ 0x00042ef0 sqlite3Update (sqlite3.c:77390) 9 Grocery iQ 0x000447de sqlite3Parser (sqlite3.c:84117) 10 Grocery iQ 0x00045f7a sqlite3RunParser (sqlite3.c:85471) 11 Grocery iQ 0x000464fa sqlite3NestedParse (sqlite3.c:60739) 12 Grocery iQ 0x00048370 sqlite3AlterFinishAddColumn (sqlite3.c:59173) 13 Grocery iQ 0x00045b16 sqlite3Parser (sqlite3.c:84603) 14 Grocery iQ 0x00045f7a sqlite3RunParser (sqlite3.c:85471) 15 Grocery iQ 0x000487ee sqlite3LockAndPrepare (sqlite3.c:71412) 16 Grocery iQ 0x0003c41e sqlite3_exec (sqlite3.c:71549) 17 Grocery iQ 0xce44 -[SQLDatabase execQuery:] (SQLDatabase.m:122) 18 Grocery iQ 0x2c42 -[GroceryIQAppDelegate upgradeSchema2To3] (GroceryIQAppDelegate.m:281) 19 Grocery iQ 0x3218 -[GroceryIQAppDelegate upgradeSchemaToLatestVersion] (GroceryIQAppDelegate.m:377) 20 Grocery iQ 0x3600 -[GroceryIQAppDelegate ensureDatabases] (GroceryIQAppDelegate.m:442) 21 Grocery iQ 0x2250 -[GroceryIQAppDelegate initApp] (GroceryIQAppDelegate.m:68) 22 Grocery iQ 0x26b8 -[GroceryIQAppDelegate applicationDidFinishLaunching:] (GroceryIQAppDelegate.m:143) 23 UIKit 0x30a4ef24 -[UIApplication performInitializationWithURL:asPanel:] + 160 24 UIKit 0x30a57dec -[UIApplication _runWithURL:] + 644 25 Foundation 0x306945a2 __NSFireDelayedPerform + 326 26 CoreFoundation 0x30269d88 CFRunLoopRunSpecific + 2642 27 CoreFoundation 0x30269320 CFRunLoopRunInMode + 44 28 GraphicsServices 0x31567e58 GSEventRunModal + 268 29 UIKit 0x30a4fa6c -[UIApplication _run] + 520 30 UIKit 0x30a591d0 UIApplicationMain + 1132 31 Grocery iQ 0x2090 main (main.m:6) 32 Grocery iQ 0x202c start + 44 The SQL statement it is crashing on is an ALTER TABLE command: ALTER TABLE listitems ADD COLUMN 'storeid' INTEGER DEFAULT NULL Before this statement, the app has already successfully executed several SQL statements that create and populate other tables. The listitems table already exists at the time of the ALTER TABLE execution. It is the first ALTER TABLE statement executed against the database. Other important details about our application: * we have licensed the CEROD extension for SQLite and it, too, is compiled into our app. It's capabilities are not used for this database, though. * the database is created with the following call: sqlite3_open_v2([mPath fileSystemRepresentation], &mDatabase, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil); * there is only one connection open to the database * before opening the database, the only other SQLite API calls are:
Re: [sqlite] Tree structure in SQLite DB
On Mon, 20 Apr 2009 12:02:36 -0700 (PDT), Mächi wrote: >Hello everybody, > >I'm trying to figure out how to make a tree structure in a SQLite DB. Can >anybody help on that point? Do I need to specify the parentkey attribut >specialy? how can I query this DB? Roger Binns is right. Just another hint: Search for "adjacency model" and "nested set", which are the two most common models. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tree structure in SQLite DB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mächi wrote: > I'm trying to figure out how to make a tree structure in a SQLite DB. Can > anybody help on that point? Do I need to specify the parentkey attribut > specialy? how can I query this DB? There are many matches on Google queries for doing this using generic SQL. To my knowledge there is nothing in SQLite that makes tree structures any easier or harder than with other SQL databases. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkns0vgACgkQmOOfHg372QSEKQCg43eK1UQU0wOy1KWVnNMfNyL6 Q0wAoI6moJnT9ZWdVgx/QIReSTcQzn45 =sy43 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tree structure in SQLite DB
Hello everybody, I'm trying to figure out how to make a tree structure in a SQLite DB. Can anybody help on that point? Do I need to specify the parentkey attribut specialy? how can I query this DB? Thanks, Mächi -- View this message in context: http://www.nabble.com/Tree-structure-in-SQLite-DB-tp23142869p23142869.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to synchronize the SQLite db - SQLite db
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ravi Thapliyal wrote: > 3)Has anyone does this kind of syncing? Yes, but in a different context. The only way you can do it sensibly is by keeping a journal of changes with each source. Ideally the change journal should have very low granularity (ie give which column changed rather than the whole record). To do the synchronization you send the part of the change journal the other parties haven't seen to them and they replay it. Further refinements depend on how changes are made. For example you can record timestamps for the changes (providing the clocks are accurate) to automatically pick the most recent change, if appropriate. You can record old as well as new column values which makes change conflicts easy to detect and in some cases also automatically resolvable. As a bonus change journals also provide the underlying data for undo and redo. If you have snapshots of the whole database then you can generate the change journal on demand (eg if the other party last saw you at particular id/time then grab a snapshot from then and from now and do some sort of internal diff operation which will result in a cruder change journal). If this is still all a pain for you then there are open source projects such as OpenSync although it is aimed at PIM style data (address books, calendar etc). I would recommend you start by working on your test suite with test cases like two different sources changing the same record to different values, to the same values, a record being changed on one source while being deleted on another, mismatched clocks etc. As you then start to work on your algorithm and code it will become clear very quickly if you are on the right path. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAknsu+YACgkQmOOfHg372QRa/wCeOTI5OVv1Zh+WLjHiE7z+QZDH DlgAnir/Hy+OJxG2ko6L4PF2XUrKsQJe =Qkeb -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange SAVEPOINT behaviour with locking_mode=exclusive
At 15:37 20.04.2009, D. Richard Hipp wrote: >Ticket #3811 has been addressed by enhancing the documentation to >explain that journal_mode changes are only guaranteed to work if they >occur prior to the first transaction. Whether or not a journal_mode >change works after the start of the first transaction is undefined. Thanks! >Even as I type this message, it occurs to me that we should probably >lock down the journal_mode at the start of the first transaction. >Otherwise, there are a bazillion cases of journal_mode changes at >strange times (such as in the middle of a nested transaction) that >could cause problems (segfaults) even if their behavior is undefined. In this case, it would make sense IMO that setting the journal_mode should generally (and especially after the 1st transaction) report the active mode so applications can check if the journal_mode change was in fact effective. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?
On Mon, 20 Apr 2009 08:59:02 -0400, Jean-Denis Muys wrote: >> It's a shame: I far preferred the BNF: more compact, not to mention you >> could copy and paste as well as search the text of the syntax itself. > > "shame"? Are you sure that's the word you wanted to use? Quite sure, yes. > - compactness: very weak argument, maybe even a strawman. Merely poorly articulated; my apologies. > - copy & paste: the last time I wanted to copy & paste the formal syntax > of > any language was... about right around when dinosaurs went extinct. I've found it useful in helping (remote) colleagues and acquaintances less familiar with SQLite. > - search: I don't think there is any valid or interesting search of BNF > except for the keywords of the language, which *are* searchable with the > syntax diagrams. I've grown very used to using inline searching with my Web browser: it was very useful for me to highlight and isolate specific keywords in a given syntax definition, as they appear in context. > In all these "read-only" situations, you want the grammar to be very > fast to read and understand. > > This is where syntax diagrams excel: the cognitive load to understand > them is far less than BNF (for the majority of people). I must be unusual, then, because I find that a compact representation allows me to see the whole very quickly, while also being able to focus on a single part. Perhaps what trips me up about the current syntax diagrams is their two-dimentional nature: thereas the BNF was read linearly, options in the syntax diagrams are arranged perpendicular to the flow of the syntax, and once one runs out of horizontal space for the diagram (which seems to happen faster with such images and cannot be alleviated by increasing one's line length) the directions of the lines and arrows can get fairly difficult to decypher. That the BNF was expressed in colour-coded text helped considerably also: could this not be done with the syntax diagrams (assuming it can be done in such a way that they don't end up looking gaudy)? Obviously this is all a matter of personal preference, and my aim was not to start an argument by expressing mine, Jean-Denis. If I am among the minority, so be it. Fortunately I am by now sufficiently familiar with SQLite's syntax that the diagrams suffice when I need a refresher. :) -- J. King ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange SAVEPOINT behaviour with locking_mode=exclusive
On Apr 20, 2009, at 6:59 AM, Ralf Junker wrote: > Hello Ken, > >> I think the problem is not in the locking mode but rather: >> >> PRAGMA journal_mode = off; >> >> I'm not sure if rollbacks actually function with the journalling >> turned off. > > According to the documentation, journal_mode=off disables rollback. > >> Can you try it without the above line? > > I had already looked at the issue again and now blieve that it is an > inconsistency between journal_mode=off and locking_mode. > > I filed it as a bug report at http://www.sqlite.org/cvstrac/tktview?tn=3811 > but it has not yet been addressed by the SQLite developers. > >> The logic implies that the rows in question should not exist since >> they are rolledback. > > No. With journal_mode=off, rows in question SHOULD exist, but > miraculously do when in exclusive mode. Ticket #3811 has been addressed by enhancing the documentation to explain that journal_mode changes are only guaranteed to work if they occur prior to the first transaction. Whether or not a journal_mode change works after the start of the first transaction is undefined. Even as I type this message, it occurs to me that we should probably lock down the journal_mode at the start of the first transaction. Otherwise, there are a bazillion cases of journal_mode changes at strange times (such as in the middle of a nested transaction) that could cause problems (segfaults) even if their behavior is undefined. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?
J. King schrieb: > On Mon, 20 Apr 2009 06:53:37 -0400, D. Richard Hipp wrote: > >> http://wiki.tcl.tk/21708 > > It's a shame: I far preferred the BNF: more compact, not to mention you > could copy and paste as well as search the text of the syntax itself. > I guess it wouldn't be too hard to write a different output plugin for that Tcl code to write BNFs instead of the syntax diagrams, the info must be in the input data for that anyway. So if you want BNFs back, plugin a different backend to that code. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?
On 4/20/09 2:35 PM, "J. King" wrote: > On Mon, 20 Apr 2009 06:53:37 -0400, D. Richard Hipp wrote: > >> http://wiki.tcl.tk/21708 > > It's a shame: I far preferred the BNF: more compact, not to mention you > could copy and paste as well as search the text of the syntax itself. "shame"? Are you sure that's the word you wanted to use? I personally think those syntax diagrams are *better* than BNF (though no shame either way). - compactness: very weak argument, maybe even a strawman. - copy & paste: the last time I wanted to copy & paste the formal syntax of any language was... about right around when dinosaurs went extinct. - search: I don't think there is any valid or interesting search of BNF except for the keywords of the language, which *are* searchable with the syntax diagrams. Now *what are* the actual real-life uses of a formal specification of SQLite's grammar? Realistically, there are few: - look up the features of the language, typically to get a quick grasp of the features, or to compare with another dialect of SQL - check whether some [more or less obscure] construct is supported in the grammar - understand why some statement is rejected by SQLite (debugging). In all these "read-only" situations, you want the grammar to be very fast to read and understand. This is where syntax diagrams excel: the cognitive load to understand them is far less than BNF (for the majority of people). I personally am rather experienced with formal grammars and BNF, but a relative newbie with SQL and SQLite. I welcome the syntax diagrams. Regards, Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?
On Mon, 20 Apr 2009 06:53:37 -0400, D. Richard Hipp wrote: > http://wiki.tcl.tk/21708 It's a shame: I far preferred the BNF: more compact, not to mention you could copy and paste as well as search the text of the syntax itself. -- J. King ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange SAVEPOINT behaviour with locking_mode=exclusive
Hello Ken, >I think the problem is not in the locking mode but rather: > > PRAGMA journal_mode = off; > >I'm not sure if rollbacks actually function with the journalling turned off. According to the documentation, journal_mode=off disables rollback. >Can you try it without the above line? I had already looked at the issue again and now blieve that it is an inconsistency between journal_mode=off and locking_mode. I filed it as a bug report at http://www.sqlite.org/cvstrac/tktview?tn=3811 but it has not yet been addressed by the SQLite developers. >The logic implies that the rows in question should not exist since they are >rolledback. No. With journal_mode=off, rows in question SHOULD exist, but miraculously do when in exclusive mode. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?
On Apr 20, 2009, at 12:38 AM, ntr wrote: > Hi all, > > Is any one knows what tool do they use to generate the SYNTAX > (railroad) > diagrams > that describing the SQL Syntax ?? > > (on http://www.sqlite.org/syntaxdiagrams.html) http://wiki.tcl.tk/21708 D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] which tool do they use to generate the SQL Syntax diagrams?
Hi all, Is any one knows what tool do they use to generate the SYNTAX (railroad) diagrams that describing the SQL Syntax ?? (on http://www.sqlite.org/syntaxdiagrams.html) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] index optimization
On Apr 20, 2009, at 4:20 PM, galea...@korg.it wrote: > Hi, > if I've got a lot of queries as follows: > SELECT id,title FROM Song WHERE title >= 'last_title' AND > (title>'last_title' OR id>last_id) ORDER BY title ASC, id ASC > what's the best index should be created? (id is the key); > I red that I can only use a multicolumn index if the left condition is > equal (=), is it true? CREATE INDEX i1 ON Song(title, id); Or leave the "id" bit out if "id" is actually an integer primary key. > > ___ > 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] index optimization
Hi, if I've got a lot of queries as follows: SELECT id,title FROM Song WHERE title >= 'last_title' AND (title>'last_title' OR id>last_id) ORDER BY title ASC, id ASC what's the best index should be created? (id is the key); I red that I can only use a multicolumn index if the left condition is equal (=), is it true? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to synchronize the SQLite db - SQLite db
A simple thing to prevent data collisions is to design a unique name for each client into the tables. That way you know where the data comes from. --- On Mon, 4/20/09, Ravi Thapliyal wrote: > From: Ravi Thapliyal > Subject: [sqlite] How to synchronize the SQLite db - SQLite db > To: sqlite-users@sqlite.org > Date: Monday, April 20, 2009, 2:34 AM > I am looking at a design that will > require synchronizing a disconnected > SQLite DB file on client's machines to a central server. > The version of the DB on the server will also be modified > periodically, so > there is a chance that new records will be created in > either and also > updated. > Conflicts therefore are an issue. What I'm worried about is > just the > logistics of either > 1) Importing all user's data to a single > DB somehow > 2) Managing several DB files from clients > automatically. > 3) Has anyone does this kind of syncing? > I realize I'm somewhat light > on details, but I'm not really even sure exactly what this > system will need > to do: it's more of a framework really. > At any rate, anyone have experience synchronizing SQLite > DB files? > Suggestions appreciated. > > -Inline Attachment Follows- > > ___ > 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] How to secure standalone SQLite db
also ACL's might help --- On Mon, 4/20/09, Ravi Thapliyal wrote: > From: Ravi Thapliyal > Subject: [sqlite] How to secure standalone SQLite db > To: sqlite-users@sqlite.org > Date: Monday, April 20, 2009, 2:36 AM > I have a windows standalone > application with SQLite as a database, so what > is the procedure to secure this SQLite database, so that > the application > users cannot access the database directly, it should only > be accessed by > application. > Thanks > > > -Inline Attachment Follows- > > ___ > 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] How to secure standalone SQLite db
encryption --- On Mon, 4/20/09, Ravi Thapliyal wrote: > From: Ravi Thapliyal > Subject: [sqlite] How to secure standalone SQLite db > To: sqlite-users@sqlite.org > Date: Monday, April 20, 2009, 2:36 AM > I have a windows standalone > application with SQLite as a database, so what > is the procedure to secure this SQLite database, so that > the application > users cannot access the database directly, it should only > be accessed by > application. > Thanks > > > -Inline Attachment Follows- > > ___ > 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] How to secure standalone SQLite db
I have a windows standalone application with SQLite as a database, so what is the procedure to secure this SQLite database, so that the application users cannot access the database directly, it should only be accessed by application. Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to synchronize the SQLite db - SQLite db
I am looking at a design that will require synchronizing a disconnected SQLite DB file on client's machines to a central server. The version of the DB on the server will also be modified periodically, so there is a chance that new records will be created in either and also updated. Conflicts therefore are an issue. What I'm worried about is just the logistics of either 1) Importing all user's data to a single DB somehow 2) Managing several DB files from clients automatically. 3) Has anyone does this kind of syncing? I realize I'm somewhat light on details, but I'm not really even sure exactly what this system will need to do: it's more of a framework really. At any rate, anyone have experience synchronizing SQLite DB files? Suggestions appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
