Re: [sqlite] SQLite openning a database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: > Christian Smith wrote: > >>> /tmp/ is rwx by anyone >>> root stats /tmp/tmpdb.root.1072 (pid==1072) >>> Nonexistant >>> User ln -s /etc/fstab /tmp/tmpdb.root.1072 >>> root creates /tmp/tmpdb.root.1072 >>> fstab erased. >>> >> >> >> SQLite wouldn't work like that. It would read the file (which is actually >> /etc/fstab) and determine that it is not a valid SQLite db. SQLite >> doesn't >> just trample over files without verifying that they are in fact valid >> SQLite databases. >> > > I don't *think* this is a problem. But version 3.1 will contain > extra security features just to make sure. In particular, it will > initialize the random number generated used to create temp file > names from /dev/urandom if available. If you want to create temporary file names, use mkstemp(3) to create and open a file, then close it and open it with sqlite3_open(). > I'll carefully audit the > code to make sure files that are not valid databases are never > overwritten. And I might put in code to make sure opened databases > do not have more than 1 link. (Having an SQLite database that > is aliased by links (hard or soft) is dangerous in other ways, > since it no longer has a unique rollback journal filename, and > hence a hot journal might be missed after a power failure - > resulting in database corruption.) > wow. :) - -- All content of all messages exchanged herein are left in the Public Domain, unless otherwise explicitly stated. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB525yhDd4aOud5P8RAjH4AJ9qGQktUxO6XH91ffgFwsWah/ctuwCdEv6f MOFdXf9HgjauWFMxxe1xsfk= =iBc7 -END PGP SIGNATURE-
Re: [sqlite] sql tables. . .?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks. I was considering creating a table for each package identified as category/grp/class/package/version and storing dependencies and files in there; but I decided not to cerate thousands of tables :) I'm still examining the idea, it may be useful. Interesting, dependencies(/conflicts) in their own table. Christian Smith wrote: > On Sat, 8 Jan 2005, John Richard Moser wrote: > > >>-BEGIN PGP SIGNED MESSAGE- >>Hash: SHA1 >> >>What can be in an SQL table? >> >>Can tables contain tables? I want to be able to search for a given >>package and find what files are in it, search for a file and find what >>packages supply it. . . what's the best way to do this? > > > > We did a package management tool using SQLite. We used a schema similar to > the following: > > create table packages ( > package text primary key, > version text > ); > > create table files ( > file text primary key, > package text, > md5 text > ); > > create table dependencies ( > package text, > depends text, > details text -- Optional minimum version > ); > > > This schema is sufficient to implement packages with files and > dependencies. The set of files in all installed packages must be disjoint, > a desirable property. > > You can then get the owner of a file using: > SELECT file, package FROM files > WHERE file = ''; > > Christian > - -- All content of all messages exchanged herein are left in the Public Domain, unless otherwise explicitly stated. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB52xdhDd4aOud5P8RArewAJ9uGPSBSoL1QDq4rfAC178YTnkFxACgingP Lwu1pLig3j374n6oTeOigvY= =eSdM -END PGP SIGNATURE-
[sqlite] regression test failure with 2.8.15
hi, i was updating sqlite 2.8.14 -> 2.8.15 and now I get this single regression failure: tcl-2.2... Error: can't read "result(*)": variable isn't array Is this an actual problem or is this a bug in the test itself?
Re: [sqlite] Re: How To Select Mulit Threaded DLL in Visual Studi]
Thanks, but you must have misunderstood my post. I know how to configure projects under both Visual Studio 6 and 2003 (I've been working with VS for quite a few years now). The reason why I asked the OP to exactly specify which option he means is that I found his mail not very precise. To make sure that I don't misunderstand his question, I asked for details. Anyway, I really appreciate your efforts to helping out :-) Rolf Steve Frierdich wrote: Steve Frierdich wrote: Rolf Attached is a Power Point presentation that shows how to select a Multi Threaded DLL or a Debug Multi Threaded DLL for the user run-time library for a Visaul Studio Project. In short below are the steps to take. In the Power Point are visual representations of the steps show below. First Select Project / Settings from the menu bar to bring up the Projects Settings Dialog Box <> Next choose the "C/C"++ Tab on the Projects Settings Dialog Box Then choose "Code Generation" from the "Category" drop down combo box. Finally select "MultiThreaded DLL" from the "Use run-time library" drop down combo box. Hope this helps. Steve
[sqlite] Re: How To Select Mulit Threaded DLL in Visual Studi
Steve Frierdich wrote: Rolf Attached is a Power Point presentation that shows how to select a Multi Threaded DLL or a Debug Multi Threaded DLL for the user run-time library for a Visaul Studio Project. In short below are the steps to take. In the Power Point are visual representations of the steps show below. First Select Project / Settings from the menu bar to bring up the Projects Settings Dialog Box <> Next choose the "C/C"++ Tab on the Projects Settings Dialog Box Then choose "Code Generation" from the "Category" drop down combo box. Finally select "MultiThreaded DLL" from the "Use run-time library" drop down combo box. Hope this helps. Steve
RE: [sqlite] Good db XML datastructure?
I touched on a similar topic in my blog: http://www.nedbatchelder.com/blog/200411.html#e20041117T084310 The comments there got distracted onto the question of how to use XML data in a relational database, and pointed off to http://www.sqlxml.org/ and http://www.sqlx.org/ There was a suggestion to use XMLSpy, as it will connect to a database and generate XML Schema for your tables. I haven't tried it though. For my purposes, I ended up using a simple ad-hoc XML file. --Ned. -Original Message- From: Keith Herold [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 3:25 PM To: sqlite-users@sqlite.org Subject: [sqlite] Good db XML datastructure? Hi all; I have been trying to find a good XML schema for describing databases, so that I can, among other things, produce documentation for my database format. Is there a good (free) one already out there, that is genarally accepted/used? I'm not looking to export data, just the description of the database (i.e., schema, comments, etc.) -- Keith ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] OT: Compiling under Windows
I was guessing that the OP means the option for runtime library. But the question wasn't very precise. If it's really about the choice of runtime library, then you don't have to use "multithreaded dll". You can any version of the runtime. The only thing you should do is to select the same version for all modules (the EXE or DLL project plus all static libraries linked into it); if you're linking with another DLL, it doesn't matter which version that DLL is using. However, it is advisable to always use either "multithreaded" or "multithreaded dll" when creating DLL projects. In an application it's your choice whether you use single threading or multithreading, but if you're writing a DLL that may be used by others, you should make sure that it uses the multithreading safe version of the C runtime, because you don't know whether the applications using your DLL are single- or multi-threaded. Rolf Keith Herold wrote: In VS 6, we generally find that you need to compile everything with Multithreaded-dll, even the libraries, or you get very strange error messages as the compiler tries to link with conflicting run-time libraries. --Keith On Thu, 13 Jan 2005 20:54:13 +0100, Rolf Schaeuble <[EMAIL PROTECTED]> wrote: What do you mean with "Multithreaded" or "Mulithreaded-DLL" project? Can you explain exactly where you can select between these two options, and whether you are using Visual Studio 6 or 2002/2003? Rolf Schäuble Michael Knigge wrote: Hello, this question is slightly OT here but. I want to compile SQLite as a library (not as a DLL) and add this library to my DLL-Project. Why? Because the C-Compiler from MS produces so many warnings and I want to compile all my own projects without even a single warning So I wonder what is correct: To compile/link the resulting sqlite.lib as a "Multithreaded" project or as a "Multithreaded-DLL" project? Remember, I want to add this sqlite.lib to my DLL Bye & Thanks, Michael
Re: [sqlite] OT: Compiling under Windows
In VS 6, we generally find that you need to compile everything with Multithreaded-dll, even the libraries, or you get very strange error messages as the compiler tries to link with conflicting run-time libraries. --Keith On Thu, 13 Jan 2005 20:54:13 +0100, Rolf Schaeuble <[EMAIL PROTECTED]> wrote: > What do you mean with "Multithreaded" or "Mulithreaded-DLL" project? Can > you explain exactly where you can select between these two options, and > whether you are using Visual Studio 6 or 2002/2003? > > Rolf Schäuble > > Michael Knigge wrote: > > > Hello, > > > > this question is slightly OT here but. > > > > I want to compile SQLite as a library (not as a DLL) and add this > > library to my DLL-Project. Why? Because the C-Compiler from MS > > produces so many warnings and I want to compile all my own projects > > without even a single warning > > > > So I wonder what is correct: To compile/link the resulting sqlite.lib > > as a "Multithreaded" project or as a "Multithreaded-DLL" project? > > Remember, I want to add this sqlite.lib to my DLL > > > > > > Bye & Thanks, > > Michael > > > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] Good db XML datastructure?
Hi all; I have been trying to find a good XML schema for describing databases, so that I can, among other things, produce documentation for my database format. Is there a good (free) one already out there, that is genarally accepted/used? I'm not looking to export data, just the description of the database (i.e., schema, comments, etc.) -- Keith ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] OT: Compiling under Windows
What do you mean with "Multithreaded" or "Mulithreaded-DLL" project? Can you explain exactly where you can select between these two options, and whether you are using Visual Studio 6 or 2002/2003? Rolf Schäuble Michael Knigge wrote: Hello, this question is slightly OT here but. I want to compile SQLite as a library (not as a DLL) and add this library to my DLL-Project. Why? Because the C-Compiler from MS produces so many warnings and I want to compile all my own projects without even a single warning So I wonder what is correct: To compile/link the resulting sqlite.lib as a "Multithreaded" project or as a "Multithreaded-DLL" project? Remember, I want to add this sqlite.lib to my DLL Bye & Thanks, Michael
Re: [sqlite] How efficiently will SQLite work as a queue?
Andrew Shakinovsky wrote: I am looking at the PRAGMA auto_vacuum command. How efficient is this (as far as time is concerned. I am not concerned about space)? Autovacuum makes the database a little larger (by a few percent) and it makes DELETE operations a little slower (by about 20%, IIRC). More details are forthcoming. Autovacuum is a new feature for version 3.1 which has not yet been released. -- D. Richard Hipp -- [EMAIL PROTECTED] -- http://www.hwaci.com/drh/
Re: [sqlite] sql tables. . .?
On Sat, 8 Jan 2005, John Richard Moser wrote: >-BEGIN PGP SIGNED MESSAGE- >Hash: SHA1 > >What can be in an SQL table? > >Can tables contain tables? I want to be able to search for a given >package and find what files are in it, search for a file and find what >packages supply it. . . what's the best way to do this? We did a package management tool using SQLite. We used a schema similar to the following: create table packages ( package text primary key, version text ); create table files ( file text primary key, package text, md5 text ); create table dependencies ( package text, depends text, details text -- Optional minimum version ); This schema is sufficient to implement packages with files and dependencies. The set of files in all installed packages must be disjoint, a desirable property. You can then get the owner of a file using: SELECT file, package FROM files WHERE file = ''; Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] How efficiently will SQLite work as a queue?
I am contemplating using SQLite as a message queueing mechanism, and was wondering whether this would be efficient. In particular, I will be adding new records to a table, while another process removes those records when it is ready to handle that "message". My question relates to possible fragmentation that may occur within the database file as records are deleted. Will SQLite efficiently reclaim the space used by the deleted records? I am looking at the PRAGMA auto_vacuum command. How efficient is this (as far as time is concerned. I am not concerned about space)?
Re: [sqlite] SQLite openning a database
Christian Smith wrote: /tmp/ is rwx by anyone root stats /tmp/tmpdb.root.1072 (pid==1072) Nonexistant User ln -s /etc/fstab /tmp/tmpdb.root.1072 root creates /tmp/tmpdb.root.1072 fstab erased. SQLite wouldn't work like that. It would read the file (which is actually /etc/fstab) and determine that it is not a valid SQLite db. SQLite doesn't just trample over files without verifying that they are in fact valid SQLite databases. I don't *think* this is a problem. But version 3.1 will contain extra security features just to make sure. In particular, it will initialize the random number generated used to create temp file names from /dev/urandom if available. I'll carefully audit the code to make sure files that are not valid databases are never overwritten. And I might put in code to make sure opened databases do not have more than 1 link. (Having an SQLite database that is aliased by links (hard or soft) is dangerous in other ways, since it no longer has a unique rollback journal filename, and hence a hot journal might be missed after a power failure - resulting in database corruption.) -- D. Richard Hipp -- [EMAIL PROTECTED] -- http://www.hwaci.com/drh/
Re: [sqlite] SQLite openning a database
On Wed, 12 Jan 2005, John Richard Moser wrote: > >Christian Smith wrote: >> On Tue, 11 Jan 2005, John Richard Moser wrote: >> >>>http://rafb.net/paste/results/jRFmZD25.html >>> >>>While most of this isn't of general interest, I'd like to point out that >>>I create the database by opening it with open() using O_EXCL and >>>O_CREAT, and then close the file. After that I call SQLite's >>>sqlite3_open() to open the database. >>> >>>The reason I do this is so that the database can't be raced to create >>>arbitrary writes. For example, I don't want to call sqlite3_open(), >>>have it find the database doesn't exist, then have a normal user insert >>>a symlink to /etc/fstab, and have sqlite3 "create" the database. >> >> But /etc/fstab is protected from modification by the operating system >> security system. Under your scheme, someone can simply rm the file after >> your open but before the sqlite3_open, and you'll still have the same >> none problem. > >/tmp/ is rwx by anyone >root stats /tmp/tmpdb.root.1072 (pid==1072) >Nonexistant >User ln -s /etc/fstab /tmp/tmpdb.root.1072 >root creates /tmp/tmpdb.root.1072 >fstab erased. SQLite wouldn't work like that. It would read the file (which is actually /etc/fstab) and determine that it is not a valid SQLite db. SQLite doesn't just trample over files without verifying that they are in fact valid SQLite databases. >> >>>It should be reviewed whether this is a general concern (I haven't >>>audited SQLite, so I don't know), and if so, whether a function such as >>>sqlite3_sopen() to "open or securely create" a database should be made. >>>~ This may just be my paranoia. >> >> No, this is of no concern. You're protected against malicious use by the >> operating system. The only person who can open, say, /etc/fstab is root. >> If the user is root, they can do whatever damage they want already, they >> don't need SQLite to do it for them. > >Do you know what a temp file race condition is? it's roughly 20% of >securuty vulnerabilites based on Ubuntu Linux Security Notices. Thanks, that hadn't occured to me. > >https://www.ubuntulinux.org/wiki/USNAnalysis > >The type of logic I'm thinking of is the type indicated in USN 3-1, 5-1, >6-1, 4-1, 13-1, 15-1, 16-1, 24-1, 43-1, 49-1, 51-1 > >http://www.ubuntulinux.org/support/documentation/usn/ > >Sometimes SQLite may be used by a root owned process. SQLite 3 already uses the O_EXCL when creating temporary files, such as the temporary database. Non-temporary database files should not be stored in world writeable locations. If you're writing a package management tool, then the package database should be in /var/db or some such place, out of reach of the common user. Thus, such symlink attacks are protected against by the OS security. If you're creating a database for temporary (per-process) use, then simply use a memory only database (filename ":memory:"). Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] multiple thread concurrency problem with exclusive transaction locks
2. disable asserts. I don't know how this is done in C, but I assume that in a release build, asserts are off. Disable asserts() using -DNDEBUG=1. This more than doubles the speed of SQLite. Asserts() are turned off in release builds. We build sqlite3.08 as follows: ./configure --disable-shared --enable-threadsafe I removed and rebuilt sqlite3, and rebuilt the test application just to be sure. With a cpu-bound process maxing out each virtual CPU on a hyperthreaded system, the test app ran 10 times in succession without failing. As soon as I killed off 1 or both of the processes, the test runs started to fail in the manner described in my original post. (hardly scientific, but at least highly suspicious) I also recompiled to turn off NDEBUG and enable assertions (This required editing Makefile.in; using the configure setting --disable-releasemode didn't seem to do anything) and achieved the exact same results. Mike C asked whether we were using multi-cpu kernels. The answer is, I don't know. All of our kernels are stock, but I was under the impression that at least as far as hyperthreading is concerned, it is the kernel that causes it to appear as though there are two CPUs, so by virtue of the fact that two are visible, multi-cpu support must be present. Nonetheless, I will check into possibly recompiling the kernel on one or more of our test machines. -Eli
Re: [sqlite] multiple thread concurrency problem with exclusive transaction locks
mike cariotoglou wrote: 2. disable asserts. I don't know how this is done in C, but I assume that in a release build, asserts are off. Disable asserts() using -DNDEBUG=1. This more than doubles the speed of SQLite. Asserts() are turned off in release builds. -- D. Richard Hipp -- [EMAIL PROTECTED] -- http://www.hwaci.com/drh/
Re: Re: [sqlite] V2.8 or V3.0 for embedded devices?
I use eMbedded Visual C++ 4.0 with de 3.0.8 Wrapper created by Rob Groves that you will find in http://www.codeproject.com/database/CppSQLite.asp and with de WinCE port http://sqlite-wince.sourceforge.net/ created by Nuno Lucas. You will find a manual steb-by-step of install this WinCE port in my home page: http://www.aaronplanell.com/WinCESQLite.pdf >Markus Oliver Junginger wrote: >> D. Richard Hipp wrote: >> >>> Not true. V2.8 stores the first 240 or so bytes of data for each >>> row on a single disk page and the rest on overflow pages. ... >> >> >> Thanks for the clarification. So, it seems V3.x is the right choice >> for embedded software, too, and we will migrate to V3.x in the long term. >> >> Regards, >> Markus >> >> >Please, what kind of application developent do you use me to develop in >pocketpc and sqlite 3.0?? >Thanks in advance. >
Re: [sqlite] V2.8 or V3.0 for embedded devices?
Miquel Matas wrote: Please, what kind of application developent do you use me to develop in pocketpc and sqlite 3.0?? Are you asking about the IDE or the application itself? IDE: Embedded Visual Studio 4 and Visual Studio 2003 (it runs on pdas and on desktops). application: it's actually a platform for writing offline applications that synchonize against any end system, which is usually SAP. If you are interested about some details you are welcome to contact me directly: m Markus. j Junginger RR @ m MobileX xag . IdeA (remove spaces and upper case letters) Regards, Markus
Re: [sqlite] V2.8 or V3.0 for embedded devices?
Markus Oliver Junginger wrote: D. Richard Hipp wrote: Not true. V2.8 stores the first 240 or so bytes of data for each row on a single disk page and the rest on overflow pages. ... Thanks for the clarification. So, it seems V3.x is the right choice for embedded software, too, and we will migrate to V3.x in the long term. Regards, Markus Please, what kind of application developent do you use me to develop in pocketpc and sqlite 3.0?? Thanks in advance.
Re: [sqlite] V2.8 or V3.0 for embedded devices?
D. Richard Hipp wrote: Not true. V2.8 stores the first 240 or so bytes of data for each row on a single disk page and the rest on overflow pages. ... Thanks for the clarification. So, it seems V3.x is the right choice for embedded software, too, and we will migrate to V3.x in the long term. Regards, Markus