Re: [sqlite] Embedded Database Functions

L/SQL, it might be both easier and better to retarget PostgreSQL's PL/pgSQL for SQLite opcodes, rather than starting from scratch with your own Oracle PL/SQL style language. (And PL/pgSQL's BSD license is compatible with SQLite's.) -- Andrew Piskorski <[EMAIL PROTECTED]> htt

[sqlite] deductive relational databases Re: [sqlite] selecting rows of the view via its position

her than the more usual row-store, like SQLite, Oracle, etc.) databases. MonetDB is one such (and OpenSource) column-store database. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] selecting rows of the view via its position

"Re: [sqlite] windowing functions != recursive functions", Thu, 13 Oct 2005 http://www.mail-archive.com/sqlite-users@sqlite.org/msg10855.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg10854.html -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] sqlite3_free()

tions rather than equivalent thread local storage versions, although I've never been sure just what it is. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

g like this in SQLite, I'm just curious in general... -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Memory DB: Load from file

: http://www.sqlite.org/google-talk-slides/page-024.html http://www.sqlite.org/google-talk-slides/page-025.html -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

[sqlite] SQLite as R data store

at they behave exactly like ordinary data frames to the users. It is likely that the project will result in the first instance in an R package (which may in due course become part of the tarball). Congratulations, Miguel! - End forwarded message - -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] updating SQLite to implement The Third Manifesto

a table column that may possibly be unknown can be > split into a separate related table, that only has records when the > value is known. > > 3.5 All variables default to a reasonable valid value for their type > if not explicitly set, such as the number zero or the empty string.

Re: [sqlite] performance statistics

Am I mistaken? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Managing trees in the database

"Hierarchical data in RDBMSs", which I just stumbled across: http://troels.arvin.dk/db/rdbms/links/#hierarchical -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Managing trees in the database

ccording to Dan Wickstrom in 2001, who I'm pretty sure knew what he was talking about): "This method provides the flexiblity of the nested set model, and it doesn't suffer from the same performance problems on insert/update operations. In addition, the implementation is simpler." -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Possible feature request 'Raw Row' representation

er to actually finish forward porting it to a more recent PostgreSQL version. But it seemed interesting. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] :memory: and sessions with PHP

e a bizarre hack. (But then I don't know what your sessions really are, nor do I really understand your application requirements, so perhaps I am missing something.) -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Simple ATTACH/memory database question

. Shouldn't SQLite's built-in cache for disk-backed databases already accomplished that for reads? Have you tested the actual performance? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] R: [sqlite] Snapshot database creation performance

ization of this sort of stuff lately, e.g.: http://db.csail.mit.edu/projects/cstore/ -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Snapshot database creation performance

On Tue, Feb 07, 2006 at 07:43:44AM -0500, Andrew Piskorski wrote: > On Tue, Feb 07, 2006 at 12:52:08PM +0100, Clinco, Michele wrote: > > My program is written in .Net and the compression routines I'm using are > > stream based, so I need to create a memory stream from the inter

Re: [sqlite] Snapshot database creation performance

ession routines I'm using are > stream based, so I need to create a memory stream from the internal > buffers that can be used by the compression routine... Do you mean you wish to directly access SQLite's in-memory data structures, rather than using a SQL query to get the data? Why?

Re: [sqlite] More benchmarks

n the manner expected of a competent but non-expert user of that tool. Naturally this various for different databases. If you find the process of properly installing and configuring the database software overly complicated or poorly documented, then that's a perfectly legitimate complaint, but it h

Re: [sqlite] proposal for improving concurrency in SQLite

l 2004 ideas for achieving MVCC semantics with table level locking for writes, by integrating shadow paging into the BTree layer: http://www.mail-archive.com/sqlite-users@sqlite.org/msg01935.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg01982.html -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] uSQLiteServer Source code available

un the database server on a very resource constrained embedded system, rather than a general purpose server box? Or? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] CHECK constraints

ot;not null" constraint as well. Nulls are always allowed unless you have a not null constraint. I'm not certain, but I believe this is standard behavior in all SQL RDBMSs that support constraints, not just Oracle. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

[sqlite] type confusion

es basically does NOT care about their "type". You can assign any value to any variable, etc. Thus it makes sense to describe Tcl as "type agnostic". That seems to have certain parallels to SQLite's manifest typing. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

ularly wasting time with suggestions like yours is unlikely to achieve victory in anything. I therefore suggest that you change your name from "Jay" to something less misleading. Just a thought. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Convert SQLite to Java

s the attraction of "pure Java"? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] windowing functions != recursive functions

m_1 ,( (1 - :coeff) * -- Of course ema is not defined here, so this fails with -- 'ORA-00904: invalid column name': nvl((lag(ema ,1) over (partition by symbol order by day)) ,val) ) as ema_term_2 from atp_ema ) v order by symbol ,day ; -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

[sqlite] SQL Window/OLAP functions

lent example of a use where windowing functions can be hugely helpful. Unfortunately, I've never had a compelling need to use SQLite for that, otherwise I'd probably take a shot at adding support for the SQL:2003 Window/OLAP stuff. :) -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] CROSS keyword disables certain join optimizations

o provide an actual syntax or language for giving SQLite such hints, probably by embedding them into specially formatted SQL comments (which is Oracle's approach). -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] v3 number handling and relational design help...

oin" when performance is required? Can an 8 bytes Did you measure a serious performance problem when joining on two separate columns? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Survey: NULLs and GROUP BY

INTO t1 VALUES(NULL,NULL,8); >INSERT INTO t1 SELECT * FROM t1; >SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3; -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Multi-threading.

sound engineering reasons for debating the relative merits of the two designs, sometimes. But "Gnome vs. KDE" really boils down to a bunch of more specific questions about things each due, AND even the sum total of each of those things are probably pretty trivial compared to, "Go

Re: [sqlite] Multi-threading.

to talk to were probably all running with well under 100 threads per process, and only 2 or 3 such processes at most. Presumably even the earlier lousy process scheduler could handle that ok. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] client/server

Oracle shine. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] philosophy behind public domain?

d release as either GPL or BSD, not both. Dual-licensing can make sense sometimes (aka, give useful options that otherwise do not exist), but I don't think it does in the case of GPL + BSD. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] List of latest changes in CVS

On Fri, May 06, 2005 at 08:59:28AM -0400, Thomas Briggs wrote: > >Is there a way to see a list of the latest changes made in CVS? I Use this: http://www.red-bean.com/cvs2cl/ -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] High throughput and durability

solution would be to add MVCC support to SQLite, as has been discussed on the list in the past. That would be cool. :) -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] beat 120,000 inserts/sec

hat a 10k or 15k rpm SCSI disk is genuinely substantially slower for writes than a 7200 rpm IDE disk. Most likely, the SCSI disks had write-through cache turned off, and the IDE disks had it turned on. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] idea: sqlite3_begin() and sqlite3_end() ?

ke SQLite doesn't have them at all. Heck, I don't think even PostgreSQL had them until version 8.0. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] atomic db replacement

On Thu, Mar 17, 2005 at 08:33:03PM -0700, Ara.T.Howard wrote: > On Sat, 12 Mar 2005, Andrew Piskorski wrote: > > >On Sat, Mar 12, 2005 at 10:03:25AM -0700, Ara.T.Howard wrote: > > > >>does anyone have a strategy for doing massive updates to a db and atomicly > >

Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround

n those annoyingly manual dba knobs some more. PostgreSQL is probably better in that respect, as its "rollback" space is effectively in the table itself, which will just keep getting bigger and bigger as necessary. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround

sort of priority, even if it could be done without making the code much more complicated. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] atomic db replacement

those? What scenario are you working with here? Do you have a large number of readers that MUST not block while a massive/slow update is done tot he database, or something like that? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] thoughts on a web-based front end to sqlite3 db?

e, or something else? People I trust are of the opinion that Python is a Good Thing, but I know nothing about one of its db APIs vs. another. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] ticket 1147

resultset. Is there some standard precisely specifying what this metadata resultset should look like? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] ticket 1147

changes values and then submits them, does ADO.NET somehow correctly check that another transaction has not modified those same rows in the meantime? And what does it do then, throw a "Someone else has changed your data in the db" exception? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] ticket 1147

DDR_DEV SQL> select u.username as "u.username" from user_users u; u.username -- DDR_DEV SQL> select u.username as u.username from user_users u; select u.username as u.username from user_users u * ER

Re: [sqlite] ticket 1147

ving the user application correctly tack on an "as my_col_name" to the approriate columns in the query is pretty trivial, so why don't these user applications correctly do that? Is it genuinely infeasible for some reason? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Solaris 2.5 compilation

default Solaris install, and last time I used Solaris, the provided nawk and awk definitely WERE different. At least a few years back, I believe Solaris awk was old awk, while many (most?) other awks are new awk, and thus equivalent (or at least more similar) to Solaris nawk. -- Andrew Piskorski &

Re: [sqlite] SQLite Advocacy

you're quite welcome. I'm always happy to engage in intelligent discussion on interesting topics, and while I can occasionally be obnoxious, or even wrong, I do strive both to convey a true and accurate picture of the facts, as I best understand them, and to better understand them myself. -- And

Re: [sqlite] excessive malloc() calls

s is usually not a good idea. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] New to SQLite, couple of questions

ses could be a huge win. I have considered using in-memory SQLite (haven't actually done it yet) rather than lots of ugly hash tables for exactly that reason. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] sqlite3_open() exclusive?

either. > because it's a single library reliant on pthreads and libc, and thus > lightweight and potentially ok for embedded systems; worst case, I write > a RDBMS shell around SQLite especially for the package manager. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Any way of using an implicit BTree ordering in SQLite?

I strongly suspect I could find plenty of high-powered database backed website developers who would argue the exact opposite. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Any way of using an implicit BTree ordering in SQLite?

mpletely different than Oracle's "connect by", but used for much the same reasons. They considered Celko's nested sets (and in fact implemented them for testing, I believe), but ultimately chose the Tree Sortkey implementation instead. They've been using it since 2000 or so. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] SQLite 3.08 Deadlock in a Linux Multithreaded Multi-Process Application

f special Apache wackiness? (I am not familar with Apache.) > I'm NOT using transactions. To be a bit pedantic, yes you are... You may not be explicitly starting a transaction, but as with any RDBMS worthy of the name, you are using them. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] SQLite for large bulletin board systems?

h many different applications, all integrated. In the latter case, well, if you really want that software to be used by and scale to a Fortune 500 company, that audience is also going to want a whole lot of features that your local stamp collecting club would never care about. -- Andrew Piskorski &

Re: [sqlite] SQLite for large bulletin board systems?

er of writers can't starve your thousands of readers. That should be doable, one way or another. E.g., one simple (not necessarily the best) way might be to simply cache the highest-hit pages in memory, and only update the cache at most once every 4 seconds or so. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] bind variables

On Tue, Aug 24, 2004 at 03:15:30PM -0400, Andrew Piskorski wrote: > Btw, I've used these database APIs and know that they all use ':' to > indicate a named bind variable which then maps to a Tcl variable, in > very much the same scheme you've explained above: > > - AOL

[sqlite] bind variables

me you've explained above: - AOLserver's Oracle and PostgreSQL drivers. (Which is then used and further extended by the OpenACS db api.) - nstcl (which wraps Oratcl, Pgtcl, etc.) I don't know what other db APIs do or don't do with bind variables. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] locking via windows nfs client

on one machine, and have all 30 clients talk to it over the network? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

Re: [sqlite] Views and performances...

so it probably shares these sorts of problems. But views are still good. Generally, I'd only remove use of a handy view once you see a real performance problem (a slow query with a bad query plan). -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/

[sqlite] Cloudscape?

Anyone know much of anything about IBM's Cloudscape database? Advantages or disadvantages vs. SQLite? http://www-306.ibm.com/software/data/cloudscape/ http://www.zdnet.com.au/news/software/0,261733,39155170,00.htm I hadn't heard of it before, so I'm curious. -- Andrew Piskorski <[EM

Re: [sqlite] any webhost that supports PHP and SQLite?

Lite. So all else being equal, you might be a bit better off with an otherwise similar hosting provider who is more focussed on the toolset that you want to use. That really doesn't matter much though, because with a virtual server arrangement, you should be able to do anything you want. -

Re: [sqlite] unsolved RDBMS problems Re: [sqlite] vers 3.0 concurrency issues

On Thu, May 06, 2004 at 03:20:13PM -0400, Andrew Piskorski wrote: > - User defined types, aka good "object" support (Date's "Third > Manifesto"). > > - Native bi-temporal support, or even just good support for one of > valid-time or transaction-time (Snodgrass)

[sqlite] unsolved RDBMS problems Re: [sqlite] vers 3.0 concurrency issues

his seems to currently either be not feasible at all, or so complicated and with such poor scalability that hardly anyone even tries it. See also: http://openacs.org/forums/message-view?message_id=128060 There are probably many other examples of significant unsolved RDBMS problems, too. -- Andre

Re: [sqlite] vers 3.0 concurrency issues

to quickly lose all ability to create your data model from scratch... Unfortunately there seems to be no way to have the best of both worlds. Either you put the extra effort into maintaining SEPARATE create scripts and upgrade scripts, or you lose the ability to do one or the other at all. -- And

Re: [sqlite] vers 3.0 concurrency issues

re neither as powerful as PostgreSQL nor as small and simple as SQLite, and that none of them are Open Source. Therefore, they are not particularly interesting - not to me anyway. -- Andrew Piskorski <[EMAIL PROTECTED]&

Re: [sqlite] vers 3.0 concurrency issues

r. Hipp plus a few other experts on this list could probably come up with a reasonably good idea... -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Updating with a Cursor

rapper API (e.g., in order to help support multiple databases), or is this just an extra Nice to Have feature for you? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ - To unsubscribe, e-mail: [EMA

Re: [sqlite] XML translation inquiry

ent storage API intended for that, rather than repeatedly converting between the RDBMS and XML? If you tell me you're sure there is some good reason I may be prepared to believe you, but offhand I can't think of one myself. -- Andr

Re: [sqlite] Effectiveness of PRAGMA integrity_check;

software running to react to signals from the UPS, you get that sort of protection for free, and you certainly want the system UPS anyway. But that's also much more complicated and vulnerable to failures due to misconfigured software, so it'd sure be nice to have the hard-drive-UPS as well. -

Re: [sqlite] Concurrency, MVCC

rrency limitations mean there isn't much point to doing that. Simplicity however, is of course an important concern. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ - To unsubscribe, e-mail: [EMA

[sqlite] Concurrency, MVCC

nately, it sounds as if the HiBASE project is dead and the code was never finished. It also appears to be strictly a main-memory database, so I'm not sure how applicable any of that work would be so a database like SQLite (or Mnesia for that matter) which may be used either in-memory or on-disk. Anyo