[sqlite] ANNOUNCE: SHSQL - SQL for LINUX/UNIX Shell scripts
Hi I have just released SHSQL under the GPL, it is a method how SQL can be easily intergrated into shell scripts. The web site is at http://www.edlsystems.com/shsql and can be downloaded from ftp://ftp.edlsystems.com/shsql (Needless to say a SQLite3 version is included) Enjoy Eddy -- Edward A. Macnaghten http://www.edlsystems.com
Re: [sqlite] Get a table's primary key?
rowid is alsways an alias for the primary key whether an "integer primary key" field was specified or not On Tue, 2004-10-05 at 18:57, Kirk Haines wrote: > Does the SQLite API (for either 2.x or 3.x versions of SQLite) offer > any way to determing which field, if any, in a table was created as an > INTEGER PRIMARY KEY? > > I've looked through the API docs, but either I am overlooking it, or > this capability doesn't exist. > > > Thanks, > > Kirk Haines -- Edward A. Macnaghten http://www.edlsystems.com
Re: [sqlite] Schema of an attached database
Wow - that was fast!
Any plans for a 3.0.8?
Eddy
On Tue, 2004-10-05 at 16:44, D. Richard Hipp wrote:
> Eddy Macnaghten wrote:
> > This does not seem to work for 3.0.7
> >
> >
> >>Marc Pitoniak wrote:
> >>
> >>>To get the schema of a table T from A I've tried "PRAGMA table_info('A.T') "
> >>>among other things without any success.
> >>>
> >>
> >>PRAGMA A.table_info(T);
> >
>
> Fixed with check-in [2001]
> http://www.sqlite.org/cvstrac/chngview?cn=2001
--
Edward A. Macnaghten
http://www.edlsystems.com
Re: [sqlite] Schema of an attached database
This does not seem to work for 3.0.7
If you have a table named "t" in the main database, and you attach a
database that has a table named t in it as attachdb, then
pragma attachdb.table_info(t);
still seems to give the info for the table t in the main database.
Is this a bug or have I got it wrong?
On Tue, 2004-10-05 at 14:08, D. Richard Hipp wrote:
> Marc Pitoniak wrote:
> >
> > To get the schema of a table T from A I've tried "PRAGMA table_info('A.T') "
> > among other things without any success.
> >
>
> PRAGMA A.table_info(T);
--
Edward A. Macnaghten
http://www.edlsystems.com
Re: [sqlite] why remove sqlite_encode() ?
If you use BLOBs I do not think you need sqlite_encode_binary and sqlite_decode_binary On Tue, 2004-10-05 at 02:30, [EMAIL PROTECTED] wrote: > Hello, > > I am new to sqlite. So far I've been very impressed by it. We > are using it as the backend of an open-source MAPI message store. > Still struggling with locking issues, but looking alright. > > Question, why as the encode and decode funtions been removed? > Did they perform badly, are there bugs? Caveats? > > We have incorporated the old source into our project, though I > hate to rely on dead code. Should we switch to base64? One > very attractive feature of sqlite_encode was that it did minimal > encoding, hence it was more effecient that base64. > > Thanks, > Kervin -- Edward A. Macnaghten http://www.edlsystems.com
RE: [sqlite] SQL help
Replying to my own post, sorry :-) Being pedantic here, when I said the "correct" way of doing what is required was... > select * from t > where (a, b) in (select max(a), b from t group by b); that would work for ORACLE, but is not ANSI as such. The ANSI method would be... select * from t x where a in (select max(a) from t y where x.b = y.b); However - that does not work under SQLite either :-( You are stuck with the "hashing" method described in my answer. If the tables are exceptionally large, and you would need indexing to kick in to perform the query you may think of adding an extra column containing the hashed value and populate it prior to performing the query update t set ab = (b * 10) + a; select * from t where ab in (select (b * 10) + max(a) from t); That would use an index on ab in a lot of SQLs, I do not know about SQLite. On Tue, 2004-10-05 at 01:55, Eddy Macnaghten wrote: > > SELECT MAX(A) AS A, B, C > > FROM T > > GROUP BY B > > > > This is an invalid SQL statement (SQLite should generate an error here). > > The correct(tm) way to do this is with subqueries. > > ORACLE SQL (and others), using subqueries, you would use... > > select * from t > where (a, b) in (select max(a), b from t group by b); > > However - SQLite does not support that (two elements in an "in" clause) :-( > > Can you hash the two columns to give one as such (or similar)? > > > > > select * from t > where ((a * 10) + b) in (select (a * 10) + max(b) > from t group by b); > > > > which should give you what you want if you can. > > > Eddy > -- Edward A. Macnaghten http://www.edlsystems.com
RE: [sqlite] SQL help
> SELECT MAX(A) AS A, B, C > FROM T > GROUP BY B > This is an invalid SQL statement (SQLite should generate an error here). The correct(tm) way to do this is with subqueries. ORACLE SQL (and others), using subqueries, you would use... select * from t where (a, b) in (select max(a), b from t group by b); However - SQLite does not support that (two elements in an "in" clause) :-( Can you hash the two columns to give one as such (or similar)? > select * from t where ((a * 10) + b) in (select (a * 10) + max(b) from t group by b); which should give you what you want if you can. Eddy
Re: [sqlite] "library routine called out of space"
Are you sure you do not mean "out of memory" ? That means a malloc failed. What are you calling sqlite3_prepare with? Especially what is the third argument? On Fri, 2004-10-01 at 01:06, Cory Nelson wrote: > Anyone know why I'd get that from an sqlite3_prepare()? -- Edward A. Macnaghten http://www.edlsystems.com
[sqlite] Database enumeration
Hi Is there a way of either... (a) getting a list of databases attached (using attached command), or (b) Finding the database name used for any given table name name) For instance: If I have a statement "select * from fred", is there a way I can find out what database "fred" is on, or at least finding the appropriate sqlite_master record. I could do that if I could do (a) above, because then all I need to do is to look through all the appropriate sqlite_master tables (after the sql_temp_master of course) until I found it. At the moment I am copying the sqlite3 structure into my own code from the sqlite/src and creating a routine to do (a) above (enumerating databases), I feel this is wrong, not least because the sqlite3 structure may change, I suppose, at any point in later versions, which of course would cause problems. Are there any better solutions anyone is aware of? -- Edward A. Macnaghten http://www.edlsystems.com
Re: [sqlite] OOo/SQLite, searching for a developer
Have you looked at http://www.ch-werner.de/sqliteodbc/dba-sqlite-sdbc.html Eddy On Tue, 2004-09-28 at 22:25, M. Fioretti wrote: > On Fri, Aug 20, 2004 11:40:16 AM +0100, Sophie Gautier > ([EMAIL PROTECTED]) wrote: > > Hi all, > > > > This is my first mail here, so I'll introduce myself shortly : I'm > > Sophie Gautier, leader of the french-speaking community in the > > OpenOffice.org project. Louis Suarez-Potts, our community manager, > > is in cc of this mail. > > > > The purpose of this mail it to try to find a developer who would be > > interested in writing an embedding SDBC driver for SQLite. Why, because > > we really love SQLite > > Sophie, > > you might want to repost an updated announce, if still needed, as a > comment to this article: > > http://www.linuxjournal.com/article.php?sid=7800 > > HTH, > Marco Fioretti -- Edward A. Macnaghten http://www.edlsystems.com
Re: [sqlite] Begin transaction at RESERVED lock level?
I would suggest a dummy update or insert just after the BEGIN TRANSACTION that does not do anything meaningful, just creates the RESERVED lock. On Sat, 2004-09-25 at 00:30, b.bum wrote: > Is there a way to do a 'begin transaction' directly at the RESERVED > locking level? > > A typical usage pattern-- correct me if there is a better way-- is to: > > - start a transaction (NO LOCK TAKEN) > - do a series of selects to gather or verify state (SHARED) > - do a series of inserts/updates (with interspersed selects) to write > new state (RESERVED) > ... repeat selects/inserts/updates (RESERVED) > - end/commit transaction (UNLOCK) > > It would appear that the lock level during a transaction can transition > from SHARED to RESERVED, but will never fall from RESERVED back to > SHARED until the transaction is completed, at which point in time the > lock will fall back to UNLOCK. > > If I have deduced the above correctly, then it would appear to be an > advantage to be able to immediately push the lock to RESERVED upon > entry into a transaction. If it is impossible to gain a RESERVED lock, > then the initial selects are a waste of time and control can return > immediately. > > Obviously, I don't want such behavior to happen all the time. There > are a lot of advantages to having SHARED locks during transactions > until the point in time the RESERVED lock is really needed. I'm just > looking for a way to mark the lock as RESERVED at the beginning of the > transaction in certain special cases. > > b.bum -- Edward A. Macnaghten http://www.edlsystems.com
Re: [sqlite] Lock files....
On Fri, 2004-09-24 at 16:50, Ara.T.Howard wrote: > On Fri, 24 Sep 2004, Eddy Macnaghten wrote: > > > There is no way I can guarantee the "nfs" to be good. Also, a mixture of > > Linux and Windows clients need to be allowed for, I do not know how SAMBA > > supports the fcntl functionality, or how well it interfaces with the Windows > > Server or Client locking mechanisms. > > why not a simple lock daemon then? An attraction of SQLite is that it is all client based - making almost practically zero cost administration. Although this is not an issue on implementations that have an IT department, it makes a BIG difference in supporting the penny-ally implementations that do not (all THEY have to do is to install the app on the client and pint it to the database file...). If I was going to run a daemon, I would use PostgreSQL or something. > > > > I will post the source on my web site soon if you are interested. > > it's interesting even if it contains many race conditions due to inode caching > - i'd like to see it though. I am getting some tests together now. As soon as I have got it ready enough I will put it on my site and inform the list... -- Edward A. Macnaghten http://www.edlsystems.com
Re: [sqlite] Lock files....
On Fri, 2004-09-24 at 14:51, Ara.T.Howard wrote: > On Fri, 24 Sep 2004, Eddy Macnaghten wrote: > > > However, reading the documentation it seems that SQLite is not hot on > > concurrent access through networks, or across platforms, due to the funnies > > of fcntl, or incompatibilities between Windows and Linux and so on. > > it works o.k. if your nfs impl is good (netapp, linux - not sun) but code > needs to be prepared to handle BUSY errors because sqlite uses byte ranges > locks and therefore you may have a lock only to find out you need some 'more' > lock. i do think this type of use is frowned upon though - i certainly find > it unnerving. There is no way I can guarantee the "nfs" to be good. Also, a mixture of Linux and Windows clients need to be allowed for, I do not know how SAMBA supports the fcntl functionality, or how well it interfaces with the Windows Server or Client locking mechanisms. > i've done the same thing. what's your locking algorithim? there are only a > few operations which are atomic on nfs and they are not open(O_EXCL) or mkdir > which people typically use. i've written a robust nfs safe lockfile api based > on the atomicity of link(2) and command line tool that is highly configurable > and that supports automatic recovery from stale locks left over from dead > processes/machine reboots, you can find it here > >http://raa.ruby-lang.org/project/lockfile/ > Hmm - interesting. However, I cannot really use your method as link(2) is not really available under Windows (as far as I can tell). The mechanism I use is not brilliant, that is that it does not guarantee 100% that locking is successful! - Though I have taken care to make sure it is as close to 99.999 (as many 9s as possible) % sure that it is. The more clients there is the more likely it is to fail, which is why this is only good for a small number of clients (approx 5) in non mission-critical applications. I do not know how easy it is to recover a corrupt sqlite3 database file, if there is not a utility to do that then I will probably work on one (it would open the file exclusively and create a dump that can be re-imported into a new database). My lock file has a 16 byte header, then a number of 4 byte records each representing a lock aquired by someone. Each client has a unique number. When a lock is to be aquired the cliet first checks to see if the file is free (the first byte is zero), if it is not it waits 1/100 of a second and tries again. If it is free it sets the first byte to -1, and the appropriate header field to the client number. It then reads the header (with the first 512 bytes) again and checks that it still "owns" the file (the appropriate header field set to the client number) - if not starts the process again - if it does it then performs searches and updates on the file accordingly, unowning the file (setting the first bu=yte to zero) when finished. I will post the source on my web site soon if you are interested. The mechanism, as I said, is not 100% guaranteed, but should work in practice for a small number of clients on a LAN - even with mixed (old) architectures - on a non mission critical scenario - so long as database corruption does not happen TOO often and that corrupt databases can be mostly recovered. Where more reliable scaleability and stability is required then PostgreSQL, or one of that ilk, is reccomended anyway! > > Ideally, I would like an indicator in the SQLite database file header record > > to determine if this file locking is required, and set accordingly when the > > database is created, and possibly be switched on and off with a PRAGMA > > command or similar, or maybe it's own utility. > > this could be o.k. - but better IMHO would simply be a switch that allowed the > entire database to be locked read/write in a blocking fashion rather than > using byte range locks. on the other hand this is an easy task for the > application domain... you are suggesting a pragma that would indicate some > sort of external lock was required to access the database? This is essential in my case IMHO. I suppose in theory this could be an atrribute of the connection API call, but it does not belong there. If some clients use one kind of locking mechanism, and others use another, database corruption and problems are guaranteed! -- Edward A. Macnaghten http://www.edlsystems.com
[sqlite] Lock files....
Hi there I am new to this list, so please excuse me if this has been covered before, or is not the right place for this. I am writing a development language/environment that has a SQL back end. I am (planning to) put in a number of SQL engine connectivities, and SQLite is ideal for the low end type implementations - that is a small database (less than 1,000,000 records in any table) small number of users (5 or less). However, reading the documentation it seems that SQLite is not hot on concurrent access through networks, or across platforms, due to the funnies of fcntl, or incompatibilities between Windows and Linux and so on. Now SQLite would be ideal for the low end implementation (being a client oritented database there is hardly any administration and so on), however, I will need it to be multi user - not much multi - 5 users or so - but still multi user, and to be so across platforms. To achieve this I have written a mechanism that creates a "lock" file, (in the same directory as the database, with the same name with ".lock" after it). This is used by SQLite clients to register, and deregister locks (what fcntl would do under the current Linux clients). I have designed it so it does not use fcntl, but could be used to perform the locking SQLite requires to guarantee concurrency and integrity. Of course, when this locking mechanism is in use there is a performance hit, as extra network processing and read/writes are required each time a lock is required, so therefore this is not right for all scenarios, though the performance hit would probably not be significant for what I want to use it for. Ideally, I would like an indicator in the SQLite database file header record to determine if this file locking is required, and set accordingly when the database is created, and possibly be switched on and off with a PRAGMA command or similar, or maybe it's own utility. What are people's views on this? -- Edward A. Macnaghten http://www.edlsystems.com
