RE: [sqlite] Restricting integer primary key values
Hi Brett, I dont think that there is a way within Sqlite to automagically do what you are looking for. I know that within Oracle they have the ability for you to define a sequence which allows lower and upper bounds to be defined as well as if the sequence can loop, which sounds like what you could be looking for. However typically even within Oracle you then use the value obtained from the sequence value into your insert statement. This however is not automatic and would still require you to check the bounds conditions, since a declared sequence can be used for anything you really like and is not tied to a specific primary key within a specific table. In the case of sqlite you would probably need to create a C function to do that your self. Dr H, I was wondering if it would be worth considering exposing the ability for a user to define a function that could override the auto increment abilities of the primary key, or would that open up a can of worms ? Wayne Brett Keating <[EMAIL PROTECTED]> wrote: Hi, This does indeed work, but only to prevent primary keys from being inserted into the database if they fall out of range. What I was hoping was that the with this command, the sqlite3 would always automatically choose primary keys that fell within this range. However with this command, the sqlite3 will eventually automatically choose a primary key outside of this range (I tested with a range of 100 to 1000), and fail to insert the new item based on the check clause. I tried to repeat the statement hoping that a new primary key would be generated and eventually the insertion would succeed, but this ended up causing an infinite loop (I think the key once chosen by the automated algorithm remains the same for repeated attempts). Are there any alternatives? I want automatic key generation that is restricted to a range, and I don't want failures unless all possible values are taken. I will continue trying other things in the meantime. Thanks, Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, January 14, 2007 6:09 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Restricting integer primary key values "Brett Keating" wrote: > Hi, > > I don't want to spam the list but basically, if "id INTEGER PRIMARY > KEY CHECK (id > 0 and id < 0x)" or something along those lines > will work, please let me know. It's not clear if sqlite3 supports > checks on primary keys from what I can tell. > SQLite does not support hexadecimal constants. Use 4294967295 instead of 0x and it should work. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite vs MS Access
Mark, If you looking for accessibility to your SQLITE DB from within other windows applications (That support ODBC) then there is an ODBC driver for SQLITE which is probably the most common manner to access databases. Take a look at the following link. http://www.ch-werner.de/sqliteodbc/ Cheers Wayne P Kishor <[EMAIL PROTECTED]> wrote: On 9/6/06, Allan, Mark wrote: > Hi, > > After successfully using SQLite on an embedded device, we are now thinking of > using SQLite in a PC application. > > This would be used in place of an MS Access database on a local/network > disk. I believe that SQLite should be quicker for both transactions and > queries than Access. The one draw back that comes to mind maybe portability > (i.e. accessing data outside of the application), although the data would be > portable across machines (PC, Mac, Unix, etc) should we ever need it to be > in the future. > well, you could write your application in a platform-neutral language, and it will be portable everywhere. You could choose any of the popular ones (Perl, Python, Tcl). If you desire a gui, you could write a web application, and your gui framework -- the browser -- would be automatically pre-installed on every personal (and not so personal) computer on the planet. Or, you could cleanly decouple your backend from the gui, and rewrite only the gui part in the OS-specific framework (beautiful Cocoa with its SQLite-based core-data is already present on every Mac OS X 10.4.x onward, and whatever it is that is used on PCs and non-Mac Unix). -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Order of columns within a CREATE TABLE statement
Hi all, Just had a quick question with regards to the order of the columns within a create table statement I have a few tables that use the BLOB type for storing various lengths of binary data and I was wondering if its better (more efficient) to always declare columns of this type last within the create table statement or not. I'm not searching on these columns but they do have the chance of being updated with more or less binary data than was originally in them, so wondered if there is any penalty difference for having them at the end or in the middle of of a list of other columns within the table. Or does it not really matter given that any column can handle any data type ? Thanks Wayne
Re: [sqlite] Running App state in db?
Elrond. If you dont have any luck with the database way check out ACE Adaptive Communications Environment. That has wrapped all of the code for the likes of mutexes etc. So could save you a bunch of time if you need to go to option 1 especially across multiple OS's http://www.cs.wustl.edu/~schmidt/ACE-overview.html Elrond <[EMAIL PROTECTED]> wrote: On Wed, Mar 01, 2006 at 01:32:31PM -0600, Jim C. Nasby wrote: > BTW, if you're running everything on a single machine there's lots of > other ways you can do locking that don't involve the database. [...] Well, my locking data isn't as simple as "locked, not locked". The resource has ranges that can be locked, and it can be locked for reading (shared) and writing (exclusive). It's not really fun. That said, I want the whole fun to work on Unix and Windows. So my options are: 1) Write native api code for the job, using shared memory/mutexs, or whatnot for the relevant OS. 2) Find a suitable storage for the structured data. I'm currently trying (2). Elrond
Re: [sqlite] Running App state in db?
Unfortunately I think that this would lock the whole database within SQLITE as there is no row level locking, so probably not the best way to go forward, unless all of the other applications are only performing reads ? Some othe ideas that might help. Have a field in one of your tables (May be a process table as Jim descriobed) that stores the last update time when your main app performed a refresh of the data. Your other applications could then infer that if that value is greater than some threshold that the data within is old and should not be trusted. So your other applications could infer from that that your app has crashed. In this case you might not need to clean the DB as the data is effectively implied as being bad given that the last_refresh time is outside of your accepted aging window. This assumes that you are periodically refreshing the data in there which sounds like that is the case On recovery (restart ) of your application I think the only thing you probably dont want to do is go thru the recreation of the tables as that would invalidate any prepares that your other applications have done. So may be delete the old data and refresh it (or simply overwrite it). In doing so your other applications would then see a new time stamp within the accepted threshold range and so could now trust that data again. Wayne "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: On Wed, Mar 01, 2006 at 07:38:58PM +0100, Elrond wrote: > > Hi, > > I'm considering to put the state of a running app into an > sqlite db. I want it in a db, so external tools can query > it and know, what the app is doing currently. > > Any hints on how to clean up the db, when the app crashes? > > (I have external resources, that I need to "lock", so the > idea is to put the locks in the db, so more than one > instance of the app can run and they don't kill the > external resource.) > > Any hints? Depending on your needs, you might be able to just lock a row for updates and hold that lock. IE, open a seperate connection to the database and do: BEGIN; UPDATE process SET start_time = now() WHERE process_id = ?; And then 'sit' on that connection until you're done. When you're finished, just issue a COMMIT. Note that some databases won't like you leaving that transaction open a real long time, so it depends on what you're doing if this will work. I also don't know if SQLite cares about such things. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
[sqlite] Possible feature request 'Raw Row' representation
Hi, I was wondering if this could be a useful addition to the SQLITE API set. I'm not sure if this possible to create so figured I'd post it here for further discussion rather than blindly putting it in as an enhancment request. Just for some background I have an application that runs on multiple machines (and different platforms) and periodicially each one sends various statistics about its health to the other applications. The stats are computed and stored in a single table for historical purposes, but what gets sent out to the other machines is the latest row from that table. At the moment when I pull back the row. I package it up in to a network byte order representation and send it on its merry way. The recv's of course unpack the message and then refresh their local information with the information sent, again in a SQLITE DB having the same table structure as what it originated from. So I was thinking (Dangerous with out too much coffee!) if the DB format is platform neutral is there a way to obtain a raw image of that row from SQLITE rather than performing the sqlite3_column_XXX for each field returned. If a raw row representation was available say via the likes of an API const void *sqlite3_get_rawrow(sqlite3_stmt*) I could then skip the sqlite3_column_xxx operations as well as some of the network byte order conversion routines and send that to the various receivers. (The assumption here is of course that the byte array returned is in a platform neutral representation) On the receiving side there could be a similar set of API's to insert the raw row into the corresponding table int sqlite3_insert_rawrow(sqlite3*, const char* tablename, void *rawrow); Or alternatively if the row was just to be discarded after reading there could be something like the following to allow you to still perform the sqlite3_column_XXX requests on it sqlite3_stmt* sqlite3_set_rawrow_result(sqlite3*, void *rawrow) While I have an application that could benefit from this, I guess it could be employed in other situations as well such as forming some form of replication deamon between two DB's, or creating a binary table dump that could be more compact than a text export Well thats about it, let the responses begin Wayne
