Re: [sqlite] multiuser DB on network share
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jay Sprenkle wrote: > This sounds exactly like what > causes the trashed shared MS Access databases I've seen and network locking > issues I see warnings about here. No it isn't. > How is this supposed to work correctly without the client being notified? Err, the client OS is notified with an oplock break. > If I write an application that requires serialized access to a file, and > I rely > on operating system locking, if the operating system drops my locks > without notifying me my design breaks. You are totally confused. The application talks to the client OS (kernel). The abstraction is a kernel interface and file handles. The kernel then has a provider for the relevant filesystem. That provider then talks to a block layer or over the network. Oplocks etc are happening in that second part. They are not application visible. (1)Application <= (2)kernel => (3)redirector <= (4) network server Oplocks are something available in the SMB protocol (and recent NFS versions) between 3 and 4. They allow the redirector to optimize in the case when it is the only client with the file open. For example if oplocks are not available, then every read/write/lock request from the application (1) gets to the redirector (3) which has to contact the server (4) in order to satisfy the request. If the server (4) grants an oplock to the redirector (3) then that redirector knows it is the only client of the server with the file open. Therefore file contents cannot change so it can cache file content information. Similarly it can also do all byte range locking internally since no other client has the file open. If another client requests to open the file, then the server (4) sends the oplock break to the first redirector which will discard cached file contents and apply byte range locks it was handling internally, before acknowledging the oplock break. From that point on it has to send all read/write/lock requests to the server since the other client(s) could be modifying the file. At no point in all of this is the application(1) aware of oplocks, nor does it have any control over them. Heck even the kernel (2) doesn't know. It just hands all requests to the redirector (3) which can use oplocks to optimize performance. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFpdWdmOOfHg372QQRArwkAKC+NX8C8KTBtL5DVKNZbefZ0W/VTACgi7Kr JBlumHql+rsB0AbjG/5NNpM= =YNJF -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Length of time taken by analyze
--- RB Smissaert <[EMAIL PROTECTED]> wrote: > Could somebody explain to me why a particular table analyze is taking so > long? This is a large table, but still it seems the time to analyze this > table is out of proportion. ... > The table with the long analyze is the table ENTRY and this has indeed a few > million rows, but the table AUTHORISATION has a bit less than 1 million rows > and the analyze there is proportionally much faster. > Are these times just normal and is this just how it is or is there something > that I should be aware of? > Thanks for any advice. If I'm not mistaken, I recall the SQLite documentation saying that ANALYZE only scans the indexes, not the table rows. Perhaps you have more indexes on that particular table. Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiuser DB on network share
> How is the first client 'contacted' and asked to respond? > I can't see how this is anything but useless. I can't imagine very many > programs honor this kind of request since I've never even heard of this > before last week. If the first client doesn't respond to the request > it would have to degenerate to a standard lock. Is this an OS hack > designed in for a specific microsoft application? I said client OS, not program. It is part of the SMB protocol and the client operating system includes an SMB implementation. Under Windows it is called the SMB redirector. The specific protocol request is named oplock break. Any SMB implementation that uses oplocks (they have to be specifically requested at time of open by the SMB implementation) also has to implement the oplock breaks. How is this supposed to work correctly without the client being notified? If I write an application that requires serialized access to a file, and I rely on operating system locking, if the operating system drops my locks without notifying me my design breaks. This sounds exactly like what causes the trashed shared MS Access databases I've seen and network locking issues I see warnings about here. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiuser DB on network share
On 1/10/07, Nuno Lucas <[EMAIL PROTECTED]> wrote: > How is the first client 'contacted' and asked to respond? > I can't see how this is anything but useless. I can't imagine very many > programs honor this kind of request since I've never even heard of this > before last week. If the first client doesn't respond to the request > it would have to degenerate to a standard lock. Is this an OS hack > designed in for a specific microsoft application? The client is the SMB/CIFS file system driver, not the application. It is all transparent to the programmer, and that is the problem, because if the operating system doesn't handle this well (in other words, is bugged) the programmer has no idea it's corrupting it's own file. So it sounds like turning them off ( they mentioned a windows registry change in one web page ) would be a good idea if you wanted to ensure database integrity on a shared directory. The whole idea is actually quite clever, but the problem is that it was idealised before people understood everything about networked file systems (the security aspect was completely overlooked at the beginning). The current versions are quite good, but as they have to be compatible with older clients (Win9X), a lot of hacks need to be done (not forgetting it was done in a time Microsoft didn't believe in the future of TCP/IP). For better or worse, is still the major network file system for small networks (and I don't see any future change on this). Thanks for the info. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite update question
On 1/10/07, Jim Crafton <[EMAIL PROTECTED]> wrote: I seem to be having a problem with updates to a table. I am using SQLITE_VERSION "3.3.8", statically compiled on Win32 with VC++ 6. I have two sqlite3_stmt* instances in the same process. The first is allocated by a call to sqlite3_prepare() with a SELECT statement. The second is allocated with the intention of updating a column in the table by a call to sqlite3_prepare() using an UPDATE statement and the "?" syntax for the actual values. I bind the update values on the second sqlite3_stmt by a call to sqlite3_bind_XXX. I then call sqlite3_step on the second sqlite3_stmt, and then sqlite3_finalize. All of this succeeds. I then reset the first sqlite3_stmt (the SELECT) by calling sqlite3_finalize() with the first sqlite3_stmt and then reopen it sqlite3_prepare() (using the same SQL SELECT statement). However, when I examine the results I do *not* see the changed column! Instead it has the old value in it. I am at a loss for what I am doing wrong, and while I'm sure it's something obvious and stupid, I'm not seeing it so far. Did you use a transaction that's not committed by any chance? Are you preparing one after the other completes or are you preparing them at startup? -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite update question
I seem to be having a problem with updates to a table. I am using SQLITE_VERSION "3.3.8", statically compiled on Win32 with VC++ 6. I have two sqlite3_stmt* instances in the same process. The first is allocated by a call to sqlite3_prepare() with a SELECT statement. The second is allocated with the intention of updating a column in the table by a call to sqlite3_prepare() using an UPDATE statement and the "?" syntax for the actual values. I bind the update values on the second sqlite3_stmt by a call to sqlite3_bind_XXX. I then call sqlite3_step on the second sqlite3_stmt, and then sqlite3_finalize. All of this succeeds. I then reset the first sqlite3_stmt (the SELECT) by calling sqlite3_finalize() with the first sqlite3_stmt and then reopen it sqlite3_prepare() (using the same SQL SELECT statement). However, when I examine the results I do *not* see the changed column! Instead it has the old value in it. I am at a loss for what I am doing wrong, and while I'm sure it's something obvious and stupid, I'm not seeing it so far. Any help would be most appreciated! Thanks Jim - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite Preprocessor
Does a preprocessor exist for sqlite and if so where ? If not that might be a really nice project to be able to support syntax as follows: SQLITE_EXEC at :loginhndllogin "dbname.db"; SQLITE_EXEC at :loginhndl declar cursor c1; SQLITE_EXEC at :loginhndl prepare cursor c1 using sqlStr; SQLITE_EXEC at :loginhndl bind x (not sure about this one) SQLITE_EXEC at :loginhndl fetch c1 into :hostvars ; SQLITE_EXEC at :loginhndl close cursor c1; SQLITE_EXEC at :loginhndl close database . The list would go on and on, but you get the idea. Regards, Ken Dan Kennedy <[EMAIL PROTECTED]> wrote: I don't think so. The master journal file is created (and also deleted, unless a crash occurs) during the final commit processing (i.e. during sqlite3_step() of COMMIT). Up until that point there's no special processing for multi-database transactions. I'm wondering if attaching a database in the middle of a transaction was disallowed purely to save having to test it... Dan. On Wed, 2007-01-10 at 08:37 -0500, Tom Briggs wrote: >Would attaching a database mid-transaction, then making changes in > the newly-attached database, make it impossible to create the correct > master journal file for the overall transaction? Just a curious shot in > the dark. > >-Tom > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Monday, January 08, 2007 10:43 AM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] attach in transaction > > > > [EMAIL PROTECTED] wrote: > > > > > > Can someone tell me why attach cannot be called within transaction? > > > > I do not recall. > > > > Clearly a DETACH will not work inside a transaction if the > > table being detached has been accessed or modified within that > > transaction. But ATTACH could work, I would think. > > > > Perhaps we disallowed ATTACH so that people would not expect > > an automatic DETACH if the transaction rolls back? > > > > The prohibition against running ATTACH within transaction > > first appeared in version 3.0.1. Presumably in version 3.0.0 > > you could ATTACH within a transaction. > > > > If you want to experiement, you can comment out the check > > for begin within a transaction in the attach.c source file, > > recompile, try doing various attaches within transactions, > > and see what bad things happen. This might give us a clue > > as to why it is restricted. Usually we do not prohibit > > things without good reason, I just do not recall what that > > reason is. Perhaps the original reason no longer applies > > > > -- > > D. Richard Hipp > > > > > > -- > > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > -- > > --- > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiuser DB on network share
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jay Sprenkle wrote: > How is the first client 'contacted' and asked to respond? > I can't see how this is anything but useless. I can't imagine very many > programs honor this kind of request since I've never even heard of this > before last week. If the first client doesn't respond to the request > it would have to degenerate to a standard lock. Is this an OS hack > designed in for a specific microsoft application? I said client OS, not program. It is part of the SMB protocol and the client operating system includes an SMB implementation. Under Windows it is called the SMB redirector. The specific protocol request is named oplock break. Any SMB implementation that uses oplocks (they have to be specifically requested at time of open by the SMB implementation) also has to implement the oplock breaks. Note that if a client refuses to respond to an oplock break, the server will eventually timeout and break the whole TCP connection (ie forcibly disconnect the client). > Degenerate cases? This sounds like something only Microsoft could dream > up, so I guess degenerate applies... ;) The degenerate cases are due to poor design of the signing implementation. SMB signing as a security solution has so many loopholes that I don't know why people both. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFpRijmOOfHg372QQRAmW7AJoCjr5wGUTfX7GSn1mrAFAkmO5exwCfTa0w NXz27LvxqL3Xu0hkgq+wwVw= =scgT -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiuser DB on network share
John Stanton wrote: There are definite locking issues with some implementations of NFS. Every time I see this NFS locking issue mentioned I wonder if there is a tool which can determine whether the issue actually exists on a particular system. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiuser DB on network share
There are definite locking issues with some implementations of NFS. You can avoid all the sharing problems by running a server, just like all the larger scale DBMS implementations. Daniel Önnerby wrote: I thought I read somewhere in the docs that this was not reliable (maybe I dreamed it)??? This is great if this works, although I might still make the socketserver for notifying when updates has been made. Thank you for your replies. John Stanton wrote: Why not just use the SMB file locks if you are using the SMB networking? Daniel Önnerby wrote: Well.. I do not mean that I will use the socketserver to run queries against it. What I mean is that the database is opened by the applications from a windows share. The socketserver is only used to ask permission to write to the database and notifying the other applications that an update has been made. My thought was that this will not require much implementation on the application. Just adding a call to the socketserver before every INSERT or UPDATE, and that call will wait until the permission has been granted by the socketserver. Once the INSERT/UPDATE has been made, another call is made to the socketserver to unlock the database. The socketserver will then notify the other clients that an update has been made. Best regards Daniel John Stanton wrote: That should work quite well. We use such a strategy to implement remote, multi user access to Sqlite databases. the user is unconcerned about locking or contentions. In our case we made the server run on port 80 (HTTP) and use regular HTTP protocol so that it easily penetrates firewalls. The server in our case can either be a CGI process on a regular WWW server or use a purpose developed multi-threaded daemon which gives better performance. We make the data transport format XML for uniformity. For example if the usage requirement were to become too intensive for sqlite we can switch the shared database to being PostgreSQL without affecting the clients. Daniel Önnerby wrote: Hi all! At the company I work we have a windows application that use sqlite for the document format and this works great. We are now thinking about if it would be possible to have multiple users to access the db simultaneously from different computers (like a enterprise edition :) ). I have read everything about the multithreading issues and I know that sqlite is not designed to work like this. But I have an idea on how I might solve this in our case and would like to ask the community if you think this is a god idea (or if it would work at all): My idea is to create a small socketserver on the local network that the application holds an open connection to. When someone wants to write (lock) to the DB you always need to ask the socketserver if this is ok. The server will not keep any track of the database itself. The only purpose of the server is so that no one tries to write simultaneously. The server will also notify the applications when a modification has been made (on unlock). So.. could this work??? Best regards and thanks for the best (and smallest) SQL database ever made. Daniel - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] [ANN] Dynaset Book Worm Application
Hi,
(I hope it is not off topic)
I released a program written with wxLua using sqlite
by wxSQLite3 interface via my lua binding.
You may get it from:
http://www.dynaset.org/dogusanh/download/dsbw-1.4.zip (2815Kb)
readme.txt is as follows:
DynaSet Book Worm (dsbw)
- About:
A simple program for managing your books.
- OS: Windows
- Installation:
Unzip somewhere, run dsbw.exe.
- ToDo:
It would be good to get book details from Amazon,etc.
- Implementation:
Most of the code taken from wxLua samples.
dsbw.exe Loader. You can use lua5.1.exe instead of it.
ds280u.dll wxlua module, slightly modified and renamed
to prevent conflicts. Compiled with static wx,
and with my lua-wxsqlite3 module.
dsbw.lua Bootstrap file (language is set here!)
db/* Sqlite3 database created here.
src/*dsbw.exe sources and Code::Blocks project file
for wxlua module.
app/*Application sources.
app/application.lua Application code.
app/translations.lua Translations. Technique taken from WebBook.
You can translate the program modifying this
file. Ex:
tt["WebBook - Version 1.0"] = {
tr = "WebBook - Sürüm 1.0",
pt = "WebBook - Versão 1.0",
}
app/images.lua Some xpm images.
app/help Files for about box.
- Used software:
lua-5.1.1
wx-2.8.0-unicode
wxlua-2.8.0-cvs20061226
sqlite-3.3.7
wxsqlite3-1.6
- License:
wxWidgets (http://www.opensource.org/licenses/wxwindows.php)
- Thanks to:
Lua http://www.lua.org
wxWidgets http://www.wxwidgets.org
wxLua http://wxlua.sourceforge.net
SQLitehttp://www.sqlite.org
wxSQLite3 http://wxcode.sourceforge.net/components/wxsqlite3
Upx http://upx.sourceforge.net
- Comments and suggestions are welcome.
--
Regards,
Hakki Dogusan
http://www.dynaset.org/dogusanh
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Length of time taken by analyze
Could somebody explain to me why a particular table analyze is taking so long? This is a large table, but still it seems the time to analyze this table is out of proportion. These are the statement and the times in seconds they take: CREATE TABLE [ADDRESS] ([ADDRESS_ID] INTEGER PRIMARY KEY, [ADDRESS_LINE_1] TEXT, [POSTCODE] TEXT, [UPDATED_DATE] INTEGER, [ADDRESS_LINE_2] TEXT, [ADDRESS_LINE_3] TEXT, [ADDRESS_LINE_4] TEXT, [ADDRESS_LINE_5] TEXT) 0.95INSERT INTO ADDRESS(ADDRESS_ID, ADDRESS_LINE_1, POSTCODE, UPDATED_DATE, ADDRESS_LINE_2, ADDRESS_LINE_3, ADDRESS_LINE_4, ADDRESS_LINE_5) values(:ADDRESS_ID, :ADDRESS_LINE_1, :POSTCODE, :UPDATED_DATE, :ADDRESS_LINE_2, :ADDRESS_LINE_3, :ADDRESS_LINE_4, :ADDRESS_LINE_5) SELECT ADDRESS_ID, ADDRESS_LINE_1, POSTCODE, UPDATED_DATE, ADDRESS_LINE_2, ADDRESS_LINE_3, ADDRESS_LINE_4, ADDRESS_LINE_5 FROM ADDRESS 0.12CREATE INDEX IDX1_ADDRESS_ADDRESS_LINE_1 ON ADDRESS(ADDRESS_LINE_1) 0.14CREATE INDEX IDX2_ADDRESS_POSTCODE ON ADDRESS(POSTCODE) 0.11CREATE INDEX IDX3_ADDRESS_UPDATED_DATE ON ADDRESS(UPDATED_DATE) 0.14CREATE INDEX IDX4_ADDRESS_ADDRESS_LINE_2 ON ADDRESS(ADDRESS_LINE_2) 0.12CREATE INDEX IDX5_ADDRESS_ADDRESS_LINE_3 ON ADDRESS(ADDRESS_LINE_3) 0.12CREATE INDEX IDX6_ADDRESS_ADDRESS_LINE_4 ON ADDRESS(ADDRESS_LINE_4) 0.11CREATE INDEX IDX7_ADDRESS_ADDRESS_LINE_5 ON ADDRESS(ADDRESS_LINE_5) 0.05analyze ADDRESS CREATE TABLE [ADDRESSLINK] ([ADDRESS_LINK_ID] INTEGER PRIMARY KEY, [ADDRESS_ID] INTEGER, [FOREIGN_ID] INTEGER, [ADDRESS_TYPE] INTEGER, [ADDRESS_LINK_TYPE] INTEGER) 0.74INSERT INTO ADDRESSLINK(ADDRESS_LINK_ID, ADDRESS_ID, FOREIGN_ID, ADDRESS_TYPE, ADDRESS_LINK_TYPE) values(:ADDRESS_LINK_ID, :ADDRESS_ID, :FOREIGN_ID, :ADDRESS_TYPE, :ADDRESS_LINK_TYPE) SELECT ADDRESS_LINK_ID, ADDRESS_ID, FOREIGN_ID, ADDRESS_TYPE, ADDRESS_LINK_TYPE FROM ADDRESSLINK 0.12CREATE INDEX IDX1_ADDRESSLINK_ADDRESS_ID ON ADDRESSLINK(ADDRESS_ID) 0.12CREATE INDEX IDX2_ADDRESSLINK_FOREIGN_ID ON ADDRESSLINK(FOREIGN_ID) 0.03analyze ADDRESSLINK CREATE TABLE [PHONE] ([PHONE_ID] INTEGER PRIMARY KEY, [PHONE_NUMBER] TEXT, [UPDATED_DATE] INTEGER) 0.61INSERT INTO PHONE(PHONE_ID, PHONE_NUMBER, UPDATED_DATE) values(:PHONE_ID, :PHONE_NUMBER, :UPDATED_DATE) SELECT PHONE_ID, PHONE_NUMBER, UPDATED_DATE FROM PHONE 0.12CREATE INDEX IDX1_PHONE_PHONE_NUMBER ON PHONE(PHONE_NUMBER) 0.12CREATE INDEX IDX2_PHONE_UPDATED_DATE ON PHONE(UPDATED_DATE) 0.02analyze PHONE CREATE TABLE [PHONELINK] ([PHONE_LINK_ID] INTEGER PRIMARY KEY, [PHONE_ID] TEXT, [FOREIGN_ID] INTEGER, [PHONE_LINK_TYPE] TEXT, [PHONE_TYPE_ID] TEXT) 0.8 INSERT INTO PHONELINK(PHONE_LINK_ID, PHONE_ID, FOREIGN_ID, PHONE_LINK_TYPE, PHONE_TYPE_ID) values(:PHONE_LINK_ID, :PHONE_ID, :FOREIGN_ID, :PHONE_LINK_TYPE, :PHONE_TYPE_ID) SELECT PHONE_LINK_ID, PHONE_ID, FOREIGN_ID, PHONE_LINK_TYPE, PHONE_TYPE_ID FROM PHONELINK 0.2 CREATE INDEX IDX1_PHONELINK_PHONE_ID ON PHONELINK(PHONE_ID) 0.12CREATE INDEX IDX2_PHONELINK_FOREIGN_ID ON PHONELINK(FOREIGN_ID) 0.14CREATE INDEX IDX3_PHONELINK_PHONE_LINK_TYPE ON PHONELINK(PHONE_LINK_TYPE) 0.16CREATE INDEX IDX4_PHONELINK_PHONE_TYPE_ID ON PHONELINK(PHONE_TYPE_ID) 0.05analyze PHONELINK CREATE TABLE [PHONETYPE] ([PHONE_TYPE_ID] TEXT, [PHONE_TYPE_DESCRIPTION] TEXT, [DORMANT_FLAG] INTEGER) 0.05INSERT INTO PHONETYPE(PHONE_TYPE_ID, PHONE_TYPE_DESCRIPTION, DORMANT_FLAG) values(:PHONE_TYPE_ID, :PHONE_TYPE_DESCRIPTION, :DORMANT_FLAG) SELECT PHONE_TYPE_ID, PHONE_TYPE_DESCRIPTION, DORMANT_FLAG FROM PHONETYPE CREATE TABLE [ENTRY] ([ENTRY_ID] INTEGER PRIMARY KEY, [ADDED_BY] INTEGER, [UPDATED_BY] INTEGER, [READ_CODE] TEXT, [TERM_ID] TEXT, [ENCOUNTER_ID] INTEGER, [SECURE_TYPE] INTEGER, [ADDED_DATE] INTEGER, [UPDATED_DATE] INTEGER, [FREE_TEXT] TEXT, [DORMANT_FLAG] INTEGER, [ENTRY_TYPE] INTEGER, [START_DATE] INTEGER, [END_DATE] INTEGER, [PROBLEM_ID] INTEGER, [ENTRY_FLAGS] TEXT, [PATIENT_ID] INTEGER, [SUBJECT_TYPE] TEXT, [TERM_TEXT] TEXT) 294.94 INSERT INTO ENTRY(ENTRY_ID, ADDED_BY, UPDATED_BY, READ_CODE, TERM_ID, ENCOUNTER_ID, SECURE_TYPE, ADDED_DATE, UPDATED_DATE, FREE_TEXT, DORMANT_FLAG, ENTRY_TYPE, START_DATE, END_DATE, PROBLEM_ID, ENTRY_FLAGS, PATIENT_ID, SUBJECT_TYPE, TERM_TEXT) values(:ENTRY_ID, :ADDED_BY, :UPDATED_BY, :READ_CODE, :TERM_ID, :ENCOUNTER_ID, :SECURE_TYPE, :ADDED_DATE, :UPDATED_DATE, :FREE_TEXT, :DORMANT_FLAG, :ENTRY_TYPE, :START_DATE, :END_DATE, :PROBLEM_ID, :ENTRY_FLAGS, :PATIENT_ID, :SUBJECT_TYPE, :TERM_TEXT) SELECT ENTRY_ID, ADDED_BY, UPDATED_BY, READ_CODE, TERM_ID, ENCOUNTER_ID, SECURE_TYPE, ADDED_DATE, UPDATED_DATE, FREE_TEXT, DORMANT_FLAG, ENTRY_TYPE, START_DATE, END_DATE, PROBLEM_ID, ENTRY_FLAGS, PATIENT_ID, SUBJECT_TYPE, TERM_TEXT FROM ENTRY 51.02 CREATE INDEX IDX1_ENTRY_ADDED_BY ON ENTRY(ADDED_BY) 39.77 CREATE INDEX IDX2_ENTRY_UPDATED_BY ON ENTRY(UPDATED_BY) 48.12 CREATE INDEX IDX3_ENTRY_READ_CODE ON ENTRY(READ_CODE) 27.59 CREATE INDEX IDX4_ENTRY_ENCOUNTER_ID ON ENTRY(ENCOUNTER_ID
Re: [sqlite] MSWindows SQLite Compilation
On Wednesday, January 10, 2007 Leonardo Mateo wrote: > I'm trying to get an implib from sqlite3.dll to link with. The easiest way to do this with MinGW/MSYS is to use the configure and make tools with the source distribution, e.g., sqlite-3.3.10.tar.gz The instructions on how to do this are in the first 20 lines of the README file in that archive. > The generated implib is suspiciously small (790 b) > When I try to compile I got an undefined reference to sqlite3_open error When you make an implib from a DLL, ld makes an implib that assumes your code will use the DLL. There is hardly ever a reason to do this since gcc will link against the DLL directly. e -- Doug Currie Londonderry, NH, USA - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] MSWindows SQLite Compilation
Hi guys, I'm trying to get an implib from sqlite3.dll to link with. I'm developing a dll which will use SQLite API, but I'm trying to avoid shipping sqlite3.dll for simplicity. The fact is, I've downloaded sqlite-source-3_3_9.zip and sqlitedll-3_3_9.zip from SQLite web site and, what I'm trying to do is: Include sqlite3.h from within my code, Generate an implib file with ld (from MinGW) Link with this implib. The problems are: The generated implib is suspiciously small (790 b) When I try to compile I got an undefined reference to sqlite3_open error The commands I used were: D:\Development\plugins-test>ld -shared --export-all-symbols --out-implib libsqli te3-1.lib C:\Windows\sqlite3.dll Output: Creating library file: libsqlite3-1.lib D:\Development\plugins-test>gcc -shared -Wl,-L./,-llibsqlite3-1 dllfunc.o Output: dllfunc.o(.text+0x15):dllfunc.c: undefined reference to `sqlite3_open' dllfunc.o(.text+0x24):dllfunc.c: undefined reference to `sqlite3_errmsg' collect2: ld returned 1 exit status The problem is with the implib file, does any one know how I have to do this? Thanx a lot in advance -- Leonardo Mateo. There's no place like ~ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] database image malformed
Dan Kennedy wrote: On Tue, 2007-01-09 at 22:28 -0500, Mark Richards wrote: Using sqlite in our embedded device has offered tremendous capabilities and very conveniently, and I thank the developers and enthusiasts who continue to further this excellent project. I've had one issue that I cannot explain and would ask for some input. sqlite 3.1.3 linux kernel 2.6.12 cris-axis-linux-gnu One field system began to issue "database disk image is malformed" for reasons that I cannot yet explain. I ran a PRAGMA integrity_check, which told me: sqlite> PRAGMA integrity_check; *** in database main *** Main freelist: 1 of 1 pages missing from overflow list starting at 0 Page 46 is never used rowid 1355980 missing from index timestamp rowid 1356049 missing from index timestamp ... rowid 1356108 missing from index timestamp wrong # of entries in index timestamp wrong # of entries in index dataid sqlite> VACUUM failed with the same "database disk image is malformed". Attempts at deleting all records from the broken table failed. In the end, I was able to repair the database by bringing it down to my workstation and loading it in the SQLite Administrator windows GUI and executing Database: cleanup. My database is built using the following PRAGMA statements: pragmaPRAGMA auto_vacuum = 1; pragmaPRAGMA count_changes = 1; pragmaPRAGMA empty_result_callbacks = 1; pragmaPRAGMA legacy_file_format = OFF; pragmaPRAGMA synchronous = OFF; With pragma synchronous set to "OFF", if the device lost power or the operating system crashed in the middle of a transaction database corruption can occur. That may well be it. The box reboots every 24 hours but when it does it uses a stable copy of the database and all the "operating" data is replaced. However the stable copy is updated every hour by purging records older than a certain timeframe, performing a VACUUM, and then overwriting the stable copy. Looking at the cron scheduling I see that both events have an opportunity to collide and may well have done so particularly since the table that issued errors is the same one that is updated during this process. I wish there were a way to change these PRAGMA settings on a built table. Apparently one has to start from scratch. 1) are there any tools available in sqlite3 that will help me find the cause of this type of issue? 2) does anyone know what did SQLite Administrator do that VACUUM didn't? The integrity check shows problems with index structures only - so maybe SQLite Administrator issued queries that never used an index. Although that doesn't explain why the VACUUM failed, I would of thought the same reasoning would apply. Maybe it ran out of space in the file-system or something? No, there's plenty of room on the partition. I'll ask the author of SQLite Administrator about it. 3) since auto_vacuum is ON, I still need to do a VACUUM every so often. Any ideas why? A vacuum recreates an entire database, more or less ensuring that records are packed into database pages with very little wasted space. By contrast, auto-vacuum mode automatically shrinks the file whenever one or more pages are completely empty. So in an auto-vacuum database file there are never empty pages but the packing of records may be sub-optimal. Hence it is possible (even likely) that a VACUUM operation will reduce the size of an auto-vacuum database a bit. The btree layer tries to keep every page at least 2/3 full during regular balancing, so I would guess a VACUUM could shrink an auto-vacuum database by at most 33%. Almost certainly less. Does this match up with what you're seeing? Ran one this morning against the database. Prior to VACUUM: 233472 bytes After VACUUM: 168960 bytes That's about 28% difference. Matches your spec. Thank you! /m - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] attach in transaction
I don't think so. The master journal file is created (and also deleted, unless a crash occurs) during the final commit processing (i.e. during sqlite3_step() of COMMIT). Up until that point there's no special processing for multi-database transactions. I'm wondering if attaching a database in the middle of a transaction was disallowed purely to save having to test it... Dan. On Wed, 2007-01-10 at 08:37 -0500, Tom Briggs wrote: >Would attaching a database mid-transaction, then making changes in > the newly-attached database, make it impossible to create the correct > master journal file for the overall transaction? Just a curious shot in > the dark. > >-Tom > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Monday, January 08, 2007 10:43 AM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] attach in transaction > > > > [EMAIL PROTECTED] wrote: > > > > > > Can someone tell me why attach cannot be called within transaction? > > > > I do not recall. > > > > Clearly a DETACH will not work inside a transaction if the > > table being detached has been accessed or modified within that > > transaction. But ATTACH could work, I would think. > > > > Perhaps we disallowed ATTACH so that people would not expect > > an automatic DETACH if the transaction rolls back? > > > > The prohibition against running ATTACH within transaction > > first appeared in version 3.0.1. Presumably in version 3.0.0 > > you could ATTACH within a transaction. > > > > If you want to experiement, you can comment out the check > > for begin within a transaction in the attach.c source file, > > recompile, try doing various attaches within transactions, > > and see what bad things happen. This might give us a clue > > as to why it is restricted. Usually we do not prohibit > > things without good reason, I just do not recall what that > > reason is. Perhaps the original reason no longer applies > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > > > -- > > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > -- > > --- > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] attach in transaction
Would attaching a database mid-transaction, then making changes in the newly-attached database, make it impossible to create the correct master journal file for the overall transaction? Just a curious shot in the dark. -Tom > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, January 08, 2007 10:43 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] attach in transaction > > [EMAIL PROTECTED] wrote: > > > > Can someone tell me why attach cannot be called within transaction? > > I do not recall. > > Clearly a DETACH will not work inside a transaction if the > table being detached has been accessed or modified within that > transaction. But ATTACH could work, I would think. > > Perhaps we disallowed ATTACH so that people would not expect > an automatic DETACH if the transaction rolls back? > > The prohibition against running ATTACH within transaction > first appeared in version 3.0.1. Presumably in version 3.0.0 > you could ATTACH within a transaction. > > If you want to experiement, you can comment out the check > for begin within a transaction in the attach.c source file, > recompile, try doing various attaches within transactions, > and see what bad things happen. This might give us a clue > as to why it is restricted. Usually we do not prohibit > things without good reason, I just do not recall what that > reason is. Perhaps the original reason no longer applies > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Version 3.3.10
SQLite version 3.3.10 is now available on the SQLite website http://www.sqlite.org/ This version fixes several problems that were introduced a week ago by version 3.3.9. Upgrading is recommended for everyone. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiuser DB on network share
> Oplocks do not break things. Oplocks will guarantee consistency. They > are granted when only one client OS has a file open letting that client > OS perform locking and caching operations internally without consulting > the server each time. If another client wants to open the file, then > that second open request is held up by the server, the first client > contacted and asked to flush any outstanding data, acquire locks, and > drop the oplock. Once that has happened then the second client gets the > answer to its open request. How is the first client 'contacted' and asked to respond? I can't see how this is anything but useless. I can't imagine very many programs honor this kind of request since I've never even heard of this before last week. If the first client doesn't respond to the request it would have to degenerate to a standard lock. Is this an OS hack designed in for a specific microsoft application? The client is the SMB/CIFS file system driver, not the application. It is all transparent to the programmer, and that is the problem, because if the operating system doesn't handle this well (in other words, is bugged) the programmer has no idea it's corrupting it's own file. One advantage of using a samba server for this is that you can configure each share and even fake oplocks (meaning just ignoring) on read-only media (like a shared CD/DVD drive), meaning substantial performance improvements (the data can be all cached on the user OS). > There are other forms of oplocks that allow for opening and closing the > file multiple times without consulting the server as well as some forms > of limiting sharing (dropped when clients start using byte range locking). > > There have been some problems with Windows when smb signing is in use as > the design of smb signing assumes request response pairs whereas oplock > break notifications are asynchronous. Other than degenerate cases, > current Windows versions have been patched. Degenerate cases? This sounds like something only Microsoft could dream up, so I guess degenerate applies... ;) The whole idea is actually quite clever, but the problem is that it was idealised before people understood everything about networked file systems (the security aspect was completely overlooked at the beginning). The current versions are quite good, but as they have to be compatible with older clients (Win9X), a lot of hacks need to be done (not forgetting it was done in a time Microsoft didn't believe in the future of TCP/IP). For better or worse, is still the major network file system for small networks (and I don't see any future change on this). Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiuser DB on network share
Oplocks seems interesting. I will need to do some reading/testing. Thanks for all the comments and hints. /Daniel Jay Sprenkle wrote: I've heard this too. Windows networking has some issues with locking. You might research 'oplocks' or 'opportunistic locking' (or opportunistic caching) if you're interested in understanding what it's doing. I was reading it the other day and thought it might be the key to making it work correctly if you could turn oplocks off in windows. On 1/9/07, Daniel Önnerby <[EMAIL PROTECTED]> wrote: I thought I read somewhere in the docs that this was not reliable (maybe I dreamed it)??? This is great if this works, although I might still make the socketserver for notifying when updates has been made. Thank you for your replies. John Stanton wrote: > Why not just use the SMB file locks if you are using the SMB networking? -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
