Re: [sqlite] Multiple NATURAL JOIN precedence?
Kristoffer Danielsson wrote: > All my tables have unique column names, so I tend to use "NATURAL JOIN" for > my joins (all join-columns are indexed). When I have multiple tables, I > sometimes get incredibly slow queries (not sure if they even terminate). If all of your tables have unique column names, then a natural join would degenerate to a cartesian product, because there are no column pairs across different tables for which only rows having matching values are kept. A cartesian product would almost always be slow regardless of your JOIN syntax. I'll assume that you meant to say that all of your columns *except* the ones you are joining on, have unique column names, which makes more sense. Correct me if I'm wrong. > SELECT SUM(x) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL JOIN t4 GROUP > BY x ORDER BY x; -- takes forever, whereas: > > SELECT SUM(x) FROM t2 NATURAL JOIN t3 NATURAL JOIN t4 NATURAL JOIN t1 GROUP > BY x ORDER BY x; -- takes one second Are all of those "x" supposed to be the same column? I don't think it makes sense to do all of [SUM(x), GROUP BY x, ORDER BY x] in the same query. If the query is supposed to have exactly 1 output row, counting the number of groups resulting from the GROUP BY, then the ORDER BY is useless, and makes the query slower (unless a smart optimizer eliminates it from ever running). If the query is supposed to have an output row for each distinct value of x from the GROUP BY, then SUM(x) would presumably be the same number as x for every row. Did you mean this?: SELECT foo, SUM(bar) FROM ... GROUP BY foo ORDER BY foo -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Stored procedures" performance issue
Igor Tandetnik wrote: > Kristoffer Danielsson <kristoffer.daniels...@live.se> wrote: >> When I create my own "stored procedures" using >> sqlite3_create_function, I get horrible performance (which I >> expected) even though the column of interest is INDEXED. >> >> Consider this sample (it's stupid, but it shows my problem): >> >> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); >> >> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside >> this function, if I encounter a date greater than my birthday, then I >> want to tell SQLite to stop searching, since the date is indexed. > > I'm not sure what you mean by "a date greater than my birthday". > Presumably, your birthday comes every year, so every date is either your > birthday or falls between two birthdays (except dates before the date > you were actually born on, but those can't be greater than any of your > birthday dates). Another meaning for birthday is the the day in history where one was born, and there is just one of these per person. More often this is what people are talking about when they are dealing with dates having a year part. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feature request - field exclusion list in select list
ow often they want to "select all except this" and want to know how to say that concisely in SQL. >> And arguably it shouldn't be too terribly complicated for a >> DBMS to implement. > > Or for you to implement it in your code :-) You could have a function that > takes a table name and a list of columns to exclude and returns a string to > insert into your query of the desired columns, comma separated. (pragma > table_info will get you the column names.) Yes, anyone certainly could implement this at the application level, and so what I propose isn't reasonably a top priority for a DBMS to do it. At the same time, I don't think it would hurt to do it in the DBMS either. That is, except that you raise a very good point about once you add it you're stuck with it until SQLite 4. > Implementing something like this in your codebase (plus perhaps making the > function available as open source) would give your argument substantiation. > For example you could point to how many bugs it helped catch, developer > productivity, adoption by other projects (this points to the need) etc. I am in the process of implementing this now, mainly in the form of implementing my Muldis D language (as open source) as an alternative query/every-task-SQL-is-for language over SQLite and other DBMSs. (I'm also implementing the language as a standalone reference implementation that works natively rather than over SQL.) So sure, we can see in the future from actual uptake how much people use that feature. When this is working, I will post to the list and it can be tried out. > ["D" related stuff elided] > > Unfortunately the various names and symbols aren't conducive to Google > searches (it keeps matching smileys!). Probably Muldis D having syntax that is more C/Perl/etc like, meaning lots of use of non-alpha characters, has something to do with searchability. On the other hand, Tutorial D is much closer to SQL syntax, most alphanumerics. But I just mentioned these for reference, and part of my inspiration; my proposal for SQL should stand considered just in the context of SQL. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feature request - field exclusion list in select list
Jean-Christophe Deschamps wrote: > Darren, > > At 02:19 28/09/2009, you wrote: > ´¯¯¯ >> So my proposed "" is identical to the old "> sublist>", and my addition is the optional EXCEPT plus list of not >> derived columns. >> >> Note that I'm not stuck on the keyword EXCEPT, but it should be a word >> that >> reads similarly. > `--- > > I would love to see that proposal accepted and someday implemented. > > I'm nonetheless worried by the fact that EXCEPT is already a keyword, > and is already part of the SELECT syntax. I don't see that being a problem unless it would be ambiguous to the parser. Given that my proposed use can *not* be followed by the word SELECT it wouldn't be confused with "SELECT ... EXCEPT SELECT ...". In fact, reusing a keyword is actually advantageous as it means that no SQL which currently works on the DBMS will become illegal, which I think is very important, as we then have complete backwards compatibility. Although there might not be a problem anyway with a new word if the parser can interpret any code written to either the old SQL features or the new ones without ambiguity, and isn't strict to disallow the used word in places where it was allowed before. (As a side note, I'll mention that Muldis D doesn't really have any reserved words at all; you can name your entities anything and the parser can tell what is intended by context, same as humans, and the parser is fairly simple single-pass.) Generally speaking, I think SQL already uses the same keywords in multiple uses sometimes; for example, "WHERE foo BETWEEN 5 AND 8" is not the same "AND" as with the usual boolean conjunctions. > Maybe another verb less likely to pose problem would be easier to > manage. What would you think of: > > SELECT ALLBUT foo FROM t ... > > Just a thought. That could work as a simpler form where the result columns are simply input columns and no derived expressions, but my proposal would keep the full flexibility of the ... in "SELECT ... FROM ..." which I think is what people would expect to be able to do. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] feature request - field exclusion list in select list
Following my previous message to the sqlite-users list, I've done a bit more
research and decided to escalate my reply to a formal feature request.
I was initially going to file a ticket, but it seems that non-registered SQLite
developers can't do that anymore, and we're supposed to do it on sqlite-users
where a non-registered developer would then distill list chatter to real
tickets. So here I go ...
-
I propose that SQLite (and other DBMSs, and the SQL standard) add support for
explicitly naming a list of result fields that they do *not* want, intended for
use in situations where users do want most of the fields from a source but
don't
want a few. It would make for much more robust code if users can explicitly
encode their intentions, meaning say that they want all fields except field
foo,
and have them get exactly that (with the expectation that if non-foo fields are
added or removed to the source, they get or don't get those automatically, same
as if they did "SELECT *"). I have seen evidence from both first hand
experience and from many other developers, that this feature would be very
helpful to them. And arguably it shouldn't be too terribly complicated for a
DBMS to implement.
Now I know the SQL 2008 standard doesn't have the feature, at least in its
Foundation; I checked; see section "7.12 " of the SQL
standard, which deals with the relevant area. Similarly, the SQLite SQL
grammar
doesn't include it, as seen at
http://www.sqlite.org/syntaxdiagrams.html#select-core .
What I propose is extending the syntax of what the standard calls "". The old SQL 2008 definition is:
::=
| [ { }... ]
... and noting that the definition of "" is:
::=
|
... so my proposed redefinition is:
::=
[ EXCEPT ]
::=
| [ { }... ]
::=
[ { }... ]
So my proposed "" is identical to the old "", and my addition is the optional EXCEPT plus list of not derived
columns.
Note that I'm not stuck on the keyword EXCEPT, but it should be a word that
reads similarly.
Examples of use:
SELECT * EXCEPT col4 FROM tbl
SELECT foo.*, bar.col6 EXCEPT foo.col3 FROM tbl1 INNER JOIN tbl2 USING (id)
The semantics of the change are as if someone wrote the original "" in normal SQL with extra detail that spelled out all the fields
individually, and excluded the ones after the EXCEPT. However, the semantics
are also that this interpretation is done in the context of when the SQL
statement is executed, not just when it is written; if the underlying database
schema changes meanwhile, the result's column list would be affected.
I can also cite prior art in that Chris Date's and Hugh Darwen's Tutorial D has
the feature I propose, where it is used to modify the relational projection
operation, and it is spelled with the "ALL BUT" prefix (example "tbl { ALL BUT
col4 }". Similarly, my Muldis D language has that feature, spelled with a "!"
prefix (example "$...@{!col4}"), or as the long-hand "complementary projection"
operator separate to the "projection" operator.
Note that especially if this proposal is treated favorably by SQLite, I'll go
on
and propose it to other DBMS groups too, starting with Postgres.
Thank you in advance for the consideration.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT ALL BUT syntax (was Re: less typing possible ?)
Stef Mientki wrote: > hello, > > I often want to see most of the columns of a table / view / query, but a > few I don't want to see. > So I now create a huge list of fields, > but isn't there a more typo-frindly way, like : > > select * - field33 from table A syntax option introduced in Date and Darwen's Tutorial D language, an analogy of which I've included in my Muldis D language, is the ALL BUT modifier. Adapted into SQL with that same spelling, if you wanted all the fields except for "field33", it might be spelled like: SELECT ALL BUT field33 FROM table Or, since SQL already tends to use ALL as an implicit alternative to DISTINCT to mean "include duplicates", we could use the * instead: SELECT * BUT field33 FROM table Or if you don't like how that looks, maybe EXCEPT: SELECT * EXCEPT field33 FROM table This could be generalized so you could have any field list on the left of the BUT/EXCEPT, so then you have the full flexibility of what you have now; eg: SELECT foo.*, bar.field20 EXCEPT foo.field5 FROM foo INNER JOIN bar USING (id) Its nonstandard (unless the SQL standard has a feature like this which I'm not aware of), but I think very useful. For example, often users want to get all the result fields except for the artificial fields just used to join the tables. If SQLite's authors want to add such syntax as that, I support it, and other DBMSs could always follow suit. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Jay A. Kreibich wrote: > On Sat, Sep 19, 2009 at 01:14:56PM -0700, Darren Duncan scratched on the wall: >> 3c. I would like to have the option for SQLite to never have duplicate >> unqualified column names; for example, if one said "foo NATURAL INNER >> JOIN bar" then only a single column with the common data would be in >> the result, rather than 2; > > Umm... it does work that way. That's part of the SQL standard. > > NATURAL JOINs and JOIN ... USING( ) will only return one copy of > each column pair used in the JOIN. My memory must be rusty then, because while I believe that is what should happen, I recalled using SQL DBMSs that behaved differently (which is, NATURAL or USING just controlled what records joined with what records, and that all of the non-distinct input columns were still output); I did not check recently though. >> likewise for inner joins with explicit join conditions of "foo.a = >> bar.a" would just return a single "a" in the result. > > This goes against the SQL standard and, in this case, I think > this would be a *very* Bad Idea. You're taking a command format that > is designed to take an arbitrary expression and changing the output > format based off the particulars of that expression. That strikes me > as extremely dangerous. For example, if someone has the first line > of code and changes it to something like the second line of code, > suddenly their output changes! > > ...t1 JOIN t2 ON t1.a = t2.a... => ..., a, ... > ...t1 JOIN t2 ON toupper(t1.a) = toupper(t2.a)... => ..., t1.a, t2.a, ... > > If you only want one column because you're using a straight equality, > use NATURAL or USING. USING is particularly useful to JOIN across a > sub-set of the commonly named columns. The more general solution here to the duplicate column name problem is to be stricter than the SQL standard and treat attempts to return duplicate column names as a syntax or constraint error. For example, if you had 2 tables 'foo' and 'bar' with columns named (a, b) and (b, c), then a plain "select * from foo inner join bar on ..." should throw an exception because there would be two 'b' in the result. And so, proper NATURAL or USING behavior is one way to say "select * from foo inner join bar ..." with success, and spelling out the result column list rather than using "*" is another way. But you have to deal with it explicitly or the SQL will refuse to run, is what the DBMS should do, or the DBMS should be customizable so it can be thusly strict. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Alexey Pechnikov wrote: > Hello! > > On Sunday 20 September 2009 00:14:56 Darren Duncan wrote: >> 3b. I would like to have the option for SQLite to always operate using >> 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply >> treated >> as another value of its own singleton type that is disjoint from all other >> types >> same as Integer, Numeric, Text, Blob are disjoint. And so, one could then >> just >> use ordinary equality or not-equality tests to check for NULL, and NULL >> would >> equal NULL but not equal anything else, and boolean tests would always >> return >> true or false, not null. Once again, this would mean that behavior is more >> like >> what users actually expect and bugs can be avoided, and the query optimizer >> can >> be more efficient again, allowing more reorganization knowing at answers >> wouldn't change due to this. > > NULL is the old RDBMS problem. And SQLite Tcl interface has no equal > availability > for NULL values because we can't operate with non-defined variables. So we > can > translate NULL values into empty Tcl strings but not vice versa. Well we could also ditch NULL entirely in the database as the relational model doesn't actually require it and it is simply a convenient way to say we know we don't have normal data somewhere. Though NULL is also deficient in that way because it doesn't say *why* we don't have normal data (eg, not applicable versus applicable but unknown). I would argue for the elimination of NULL entirely and just let people design their databases to explicitly say "this point is missing for this reason", but what I proposed above was meant to be a softer intermediate approach to let NULL-depending people down easier. As for Tcl, well one solution there is to create a new singleton TCL type and use its one value to correspond to NULL, and so then empty string will continue to just mean empty string, as it should. Not being able to distinguish known-to-be-an-empty-string from unknown-or-N/A value is a *bad* thing. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > On 18 Sep 2009, at 9:43pm, Noah Hart wrote: >> Stored Procedures > > How do those differ from what can be done with triggers ? A stored procedure is an arbitrary-sized named sequence of statements to execute, which is stored in the database as data (same as table or view or trigger definitions), and which generally is explicitly invoked as a statement. A trigger is a stimulus-response rule that says when a particular event happens then a particular stored procedure is to be executed automatically. In the general case, this is like an event handler in a typical application that responds to mouse clicks or network connections or whatever. Some DBMSs support this in the more general sense of "do this when this happens" but most DBMSs that support "triggers" just handler more limited situations, such as "do this before/after a record is inserted/updated/deleted in this table". -- As for my own wishlist, well I'll name a few items. 1. If I were asked a year ago I would say top of the list is support for child transactions, but these were already delivered in 3.6.8 so my greatest wish is already met. 2. While stored procedures would be very valuable, I do not see them necessary for SQLite itself to implement, since these at least can be effectively done at the user level, especially when you consider that you would want to have parameters and variables so to either use the same user input with multiple statements in the procedure or feed results of one statement to input to another, and you'd probably want conditionals or loops etc, which host languages already provide for your use. 3. I would like to see a pragma and/or compile-time option (or several for finer graining) that subtly alters some SQL semantics or allowed syntax when in use, to deal with some SQL mis-features. 3a. I would like to have the option for SQLite to always operate using set semantics rather than bag semantics, automatically, so that for example any join or union or select-list or group or count() or aggregate etc would always just return unique rows and never treat duplicates as being distinct. Using this mode would first of all be more likely to give the results that users actually want, avoiding common bugs, and also allow for the SQLite query optimizer to be much more efficient as it could safely make more rearrangements of the query without worry that doing so would change the answer in the presence of duplicates. 3b. I would like to have the option for SQLite to always operate using 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply treated as another value of its own singleton type that is disjoint from all other types same as Integer, Numeric, Text, Blob are disjoint. And so, one could then just use ordinary equality or not-equality tests to check for NULL, and NULL would equal NULL but not equal anything else, and boolean tests would always return true or false, not null. Once again, this would mean that behavior is more like what users actually expect and bugs can be avoided, and the query optimizer can be more efficient again, allowing more reorganization knowing at answers wouldn't change due to this. 3c. I would like to have the option for SQLite to never have duplicate unqualified column names; for example, if one said "foo NATURAL INNER JOIN bar" then only a single column with the common data would be in the result, rather than 2; likewise for inner joins with explicit join conditions of "foo.a = bar.a" would just return a single "a" in the result. Such things as this, especially 3a,3b, *are* best implemented at the internal guts level of SQLite, for what is hopefully obvious reasons. Now writing SQL that targets these semantics may not be fully portable, but it would be a lot more correct and trouble-free for people just using SQLite, or other DBMSs that support those semantics I proposed. And a point is that an implementation of what I proposed would be *simpler*/*liter* than what is required to implement the standard SQL semantics that support duplicates and 3VL, and it generally does what people actually want. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Temporal SQLite database
Alberto Simões wrote: > Dear SQLite users, > > I am preparing a temporal database. Basically, entries will have a compound > key composed by the real key and some kind of time stamp. This is not > complicated to do, but I would like to know if anybody did something similar > and if: > - used the number of non-leap seconds since whatever time the system > considers to be the epoch, as time stamp > - used an auto-increment value as the time-stamp, and adding the time stamp > as an auxiliary column, > - knows of any Perl module that hides this time management. > > Thanks for your ideas. > ambs For time-stamps, the best thing to use is a simple count of TAI seconds, same as the Perl 6 core (S02) Instant type. Save representing dates as YMDHIS for user-entered artifacts. You can also convert the TAI to a YMDHIS for user display when necessary, but otherwise that extra complexity isn't needed. AFAIK, TAI can be determined in the first place simply as an offset from the Unix integer time, I think. Various Perl modules exist to help; search for TAI on CPAN. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does SQLite have an Instr function?
RB Smissaert wrote:
> Does SQLite have a string function that produces the first position of a
> string within another string?
> For example select Instr('abcd', 'c') would produce 3
> Looked in the documentation and the forum, but couldn't see it.
This also isn't a simple problem since the answer would vary depending on your
abstraction level in dealing with characters; for example, is a character a
language-dependent grapheme, a language-independent grapheme, a Unicode
codepoint, a byte, an integer, etc. If asked the length of a character string
that has an accented letter, say, the answer would vary depending on which of
the above abstractions they want the answer in, and for less abstracted
answers,
it is affected by what codepoints or bytes are used for the character, etc.
Similarly, asking "at what position does the substring match" is not simple.
In
practice, it is simpler to deal with strings than characters, and asking simply
*if* a string is a substring of another, is a much simpler question, and LIKE
does that already. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asking for SQL statement help
Dennis Volodomanov wrote: > SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT JOIN > TABLEB ON IDB=1 ) First of all, "IDB=1" isn't a join condition; it doesn't compare a column from TABLEA with a column from TABLEB. Did you mean to say "WHERE" rather than "ON"? In which case you also had a cartesian product between TABLEA and TABLEB. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Viable alternatives to SQL?
Kelly Jones wrote:
> Many sites let you search databases of information, but the search
> queries are very limited.
A fact I can agree with time and again.
> I'm creating a site that'll allow arbitrary SQL queries to my data (I
> realize I'll need to handle injection attacks).
Now by "arbitrary SQL queries", you mean "arbitrary SQL SELECT statements" I
assume; otherwise, no need for injection as you're already letting them do
whatever they want.
Generally speaking, if you truly want arbitrary queries, you essentially have
to
provide a facility to users that is analogous to letting them write in a
programming language, like SQL. To a large extent, you could accomplish this
either by providing lots of form fields where they build up queries by picking
from smaller rules, or alternately you could let them type an actual query like
one would with SQL but instead they write in some simpler language, say one of
your own design, which you then parse and translate to SQL for SQLite to run.
To prevent injection attacks, you in the many-fields case make sure to escape
or
validate/restrict all inputs to allowed values, or in the latter case you
simply
don't provide features in your simpler query language that you don't want them
to have, such as non-read queries.
> Are there other viable ways to query data? I read a little on
> "Business System 12" (BS12), Tutorial D, and even something called
> T-SQL (I think), but they all seem theoretical and not fully
> implemented.
Business System 12 is a legacy project, one of the original relational database
implementations, that predates SQL. It isn't a separate language and has no
bearing on using with SQLite.
T-SQL is a variant of SQL used by the likes of Sybase and MS SQL Server, I
think, and maybe some other DBMSs; T-SQL is for those DBMSs what PL-SQL is for
Oracle. You see T-SQL/PL-SQL in SQL stored procedures, which SQLite doesn't
natively support anyway.
Tutorial D is indeed an actual language which isn't tied to a specific DBMS, as
with generic SQL itself, and could potentially be something SQLite could
support
directly in the future, but it doesn't now. There *are* several Tutorial D
implementations, but not over SQLite. A Java DBMS named "Rel" supports it for
one thing, and also the major SQL DBMS named Ingress is looking to add support
for it as a native language.
The syntax of Tutorial D is superficially like SQL and has most of the same
features, but with some extra features and some omission of mis-features.
For example, here are some simple query comparisons (I think):
SQL:
SELECT * FROM mytable
TD:
mytable
SQL:
SELECT col1, col2 FROM mytable
TD:
mytable{col1, col2}
SQL:
SELECT * FROM mytable WHERE col1 = 'foo' AND col2 = 'bar' OR col1 = 'baz'
AND
col2 = 'quux'
TD:
mytable WHERE col1 = 'foo' AND col2 = 'bar' OR col1 = 'baz' AND col2 = 'quux'
or:
mytable MATCHING RELATION { TUPLE { col1('foo'), col2('bar') }, TUPLE {
col1('baz'), col2('quux') } }
> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?
That actually describes SQL to some extent (and Tutorial D). Compared to other
general purpose languages, SQL is fundamentally easier to use, because it
focuses on people just saying "what" they want to happen rather than "how".
If you want your solution now, and use SQLite, you either may have to roll your
own solution, and/or look at the various database wrapper frameworks out there
(there are a bunch for Perl for example) which may help you do this.
I will also say that I'm making a solution for constructing arbitrarily complex
relational or SQL queries out of data structures in Perl, focusing on enabling
what you can do with stored procedures (which includes all other queries),
which
would work with SQLite. But it isn't ready to use yet. You might be able to
use it though depending on your time table. This project is multi-pronged, and
see
http://mm.darrenduncan.net/pipermail/muldis-db-devel/2009-August/thread.html
for the most recent prong that is expected to deliver useables the soonest,
maybe even in a month.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: SQLite 3.6.16.C#
P Kishor wrote: > On Sun, Aug 2, 2009 at 7:57 AM, Noah Hart<n...@lipmantpa.com> wrote: >> Richard sent me a gentle reminder that read in part: >> >> >> Please also note that the SQLite source code is in the public domain, but >> the "SQLite" name is not. SQLite is a registered trade mark. If I don't >> defend the trademark, then I could lose it. So, I really do need to insist >> that you not use the name "SQLite" for your product. >> >> >> This is an excellent reminder, and until this is done, I've removed access >> to the source code and will terminate this google code project. I'll post >> an announcement in the future when the new project is ready. That's great. > A very valid point from DRH re. protecting the sanctity of SQLite, the > (tm), but the horse may have already left the stable. A quick search > on macupdate.com reveals the following products with ‘SQLite’ in their > name (with the indicated capitalization) -- > > MesaSQLite > SQLite Migrator > SQLite Diff > SQLiteManager X > > and > > SqliteQuery > > I am sure there are more on Win and *nix platforms. Even if other projects exist, they can also still be requested to change their names too if DRH considers there might be confusion in people as to whether they are official works of DRH. Or barring that, the other projects can be asked to prominently state anywhere one may encounter them that they are not official works of DRH nor (if true) sanctioned by DRH. > One question might be -- if one chooses a name that does not have > ‘SQLite’ or any variations thereof in it (from what I can see, Noah > called the product ‘sqlitecs’), then how does one indicate that the > product is built on or inspired by SQLite? Use the *description* text of the product to indicate its relation to SQLite. > Also, is SQLite > trademarked, or sqlite or both? Trademark word-marks are case-insensitive (and usually are formally written fully in uppercase), so the answer is "all of the above". > I thought Tito Ciuro’s now-in-limbo QuickLite was very cleverly named, > but it is generally gonna be difficult to avoid mention of SQLite in > the name. Its very easy. Just make up some word that *doesn't* resemble "SQLite" (aside, if you want, the "SQL" part) and use the description to indicate similarity. >> Also, if anyone has an ideal about what to call it ... >> Regards, >> >> Noah Hart I suggest just putting together some combination of letters that hasn't been used yet and reads nicely, and use that. It doesn't have to be descriptive, just unique and brandable. Make your own brand identity and just document the relationship to SQLite for credit purposes. Searches for SQLite would still turn up yours due to the documentation. That's what I did when I made up the word MULDIS (MULtiverse of DIScourse) for my DBMS project, and it's one of the best strategic decisions I've made. Something else I did, and maybe DRH can do something similar if he hasn't already, is write up an easy to find policy page about the best ways to use or not use the word SQLITE (any capitalization) so to work with his trademark. See http://www.muldis.com/trademark_policy.html for my full version for example, and http://search.cpan.org/dist/Muldis-D/lib/Muldis/D.pm#TRADEMARK_POLICY for an example abbreviated version accompanying a branded product. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] good books for beginners (was Re: What is a Relation?)
Darren Duncan wrote: > So I farmed out this question yesterday to another list I'm on, and got > another > And another response said: > >After having read a few of the responses, the original question strikes me > as > "very hard to answer". > >I mean, how can one be a "seasoned programmer" and at the same time be > "new > to databases" Before anyone decides to call that responder out on this comment, someone already did, and followed with a generic recommendation: What about programmers who work in image processing, 3D graphics, games development, device driver development, real-time image rendering, systems programming, embedded systems, and so on? Such fields tend to be heavily focused on implementing and optimising algorithms or interfacing with hardware, and often don't even encounter persistence, let alone data management as we typically understand it. For programmers who have been working in these fields but are moving into developing (for example) business applications, I think almost any introductory database text would be a suitable starting point. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] good books for beginners (was Re: What is a Relation?)
Paul Claessen wrote: > So .. would anyone know a good book for seasoned programmers, who are new to > databases, that addresses all these issues? So I farmed out this question yesterday to another list I'm on, and got another recommendation for you. Quoth: For beginners, whether programmers or not, I recommend Fred Rolland's "Essence of Databases". http://www.amazon.co.uk/Essence-Databases-Computing-Fred-Rolland/dp/0137278276/ And also the same first recommendation I gave yesterday: "SQL and Relational Theory How to Write Accurate SQL Code" by C. J. Date http://oreilly.com/catalog/9780596523060/ And another response said: After having read a few of the responses, the original question strikes me as "very hard to answer". I mean, how can one be a "seasoned programmer" and at the same time be "new to databases" At the very least, "being a seasoned programmer" implies "being familiar with data management of some sort", because in the end, that is the only thing that programming is all about. So, "being a seasoned programmer" and at the same time "being new to databases", means imo the same thing as "understanding data (management), but not understanding how that is done with a ((T)R)DBMS. I feel like a book targeted to this kind of audience should go VERY DEEP on the subject of "the structure of data" as it is commonly perceived/imposed by a relational system (and after having covered that, it should go more or less equally deep on (some) relational algebra). I honestly doubt whether such a book really exists. Even Date's "Introduction to ..." doesn't strike me as suitable because it covers so much more subjects. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is a Relation?
Paul Claessen wrote: > So .. would anyone know a good book for seasoned programmers, who are new to > databases, that addresses all these issues? I suggest one of C. J. Date's latest works: See http://oreilly.com/catalog/9780596523060/ . SQL and Relational Theory How to Write Accurate SQL Code By C.J. Date January 2009 Pages: 426 Series: Theory In Practice ISBN 10: 0-596-52306-8 | ISBN 13: 978059652306 Description Understanding SQL's underlying theory is the best way to guarantee that your SQL code is correct and your database schema is robust and maintainable. In SQL and Relational Theory, author C.J. Date demonstrates how you can apply relational theory directly to your use of SQL, with numerous examples and clear explanations of the reasoning behind them. Anyone with a modest to advanced background in SQL will benefit from the many insights in this book. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] totally OT: debunking debunking SQL
(Top-posting, sorry.) What gives is that dbdebunk.com is effectively a fanboy site that has gone too far and it should generally be ignored; it is not official and has about as much to do with Date or The Third Manifesto as a rabid fan site for some TV show or sport has to do with the creators of that show or sport. The *official* site of C.J.Date, and Hugh Darwen, is http://thethirdmanifesto.com/ . It is just these two that co-authored The Third Manifesto. This web site is reasonably civilized and is where you want to look. (Also, all the electronic material there is free.) Date and Darwen are very reasonable people; don't let Pascal drag them down. On a tangent, C.J.Date continues to write books, which are quite useful. For example, the latest one is "SQL and Relational Theory", http://oreilly.com/catalog/9780596523060/ , which among other things illustrates how to better make use of the SQL DBMSs we already have. -- Darren Duncan P Kishor wrote: > from the recent thread on "what is a relation," I followed Jay's > suggestion and started reading up on relational division (an article > by Celko at http://www.dbazine.com/ofinterest/oi-articles/celko1). > That led me to reading up more on CJ Date, to an interview of Date at > http://www.oreillynet.com/pub/a/network/2005/07/29/cjdate.html which > led me to Fabian Pascal's http://www.dbdebunk.com/index.html. > > I came back less than overwhelmed. I found a lot of ranting on DB > Debunk generally converging toward, "if you need to ask questions > about SQL then you are an idiot and need to go back to the drawing > board and read up some good, dry, humorless books" (I am > paraphrasing). Most of it was bashing some poor woman named Dawn > Wolthuis and even calling Celko an idiot. > > I have no doubt that Date is a big man in SQL history. I am not so > sure about Fabian Pascal's pedigree other than that he was an > associate of Date and Codd. Nevertheless, I am not quite taken by > their penchant to bandy the "idiot" label so liberally. I spent a > couple of hours on dbdebunk.com website, which is horrible to read in > its attractiveness and design, and frankly came back not very > informed... for most things it seemed to offer a, "go back, read up, > get a degree, then come and ask questions" kind of attitude. I am none > the wiser about multivalue databases, trees, or n-normal form. > > Give me Dennis Cote, Roger Binns, Kees Nuyt, and Jay Kreibich any > day... or, just give me Igor Tantednik... over these other SQL "gods" > (gods spelled in lowercase). > > What gives? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is a Relation?
Rich Shepard wrote: > On Mon, 27 Jul 2009, Darren Duncan wrote: >> Object orientation has nothing to do with all this per se, though objects >> can easily be mapped to tuples. > >A related issue is that object orientation is almost always used in the > context of procedural languages (e.g., C++, Python, Ruby) while SQL is _not_ > a procedural language. SQL is a language for working with sets (tables, or > relations). I think I realized after I said it that I should have stated that last point better. What I meant to say is that ... Object orientation is just syntactic sugar for working with values, types, routines/functions, etc, and OO issues are orthogonal to the relational model of data; you can talk about relations/etc without talking about OO. Also, given that in typical OO languages, an object can represent either an (immutable) value or a (mutable) variable, all of the concepts in the relational model or in SQL can be cleanly represented by objects. Relations (rowsets) and tuples (rows) are *values*, and are fundamentally immutable, same as numbers/etc; they are like having array or hash-map values of a typical programming language. You can give them as arguments to functions, return them from functions, assign them to variables, etc. So SQL tables are essentially variables that hold relation values; when you make a change to a table, you are substituting its previous relation value for a new one, conceptually the same thing as when you change a numeric variable. The relational types and operators can all be implemented in general purpose languages if one wanted to and integrated into a normal program. For example, see http://search.cpan.org/dist/Set-Relation/ where I did just that; note that this is more of a demonstration, and I'm sure any SQL DBMS has it beat for performance. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is a Relation?
CityDev wrote: > Just to kill time over coffee - what do you take the word to mean? > > I've just been reading a 1991 James Martin book on Object Orientation and he > was using it to talk about links between entities. Chris Date was very > specific that a relation was essentially a table. Mainly however, people > seem to use the word to describe the connections you can make by performing > joins between tables. What do you think is 'correct'? How did the other > meaning gain currency? In the context of relational databases or mathematics, you want to use Chris Date's meaning, which comes from mathematics dating from before we had computers as we know them today. A relation is a set of tuples where all tuples are of the same degree and have the same set of attribute names/types. For example, you can have a "people" relation where each tuple represents a "person" and every tuple has 3 attributes, ["name", "birthdate", "address"]. A relation gets its name from that, for every tuple in it, each attribute value is related to the other attribute values. For example, in a "person" tuple ["Joe", "Feb 17, 1989", "53 Cherry Dr."], the "Joe" is related to "53 Cherry Dr."] and so on. A relational database is called that because it consists of a set of relations, each of which having a name. SQL uses the terms ["rowset","row","column/field"] to refer to a ["relation","tuple","attribute"], and the term "table" to refer to a relation-typed variable, such being what a persisting database consists of. A relational join is an operation that takes several relation values (rowsets) as input and combines them to yield another relation value (rowset) as output, such that member tuples (rows) are matched up with each other and catenated into a new set of tuples (rows). So the term relation refers both to the contents of a SQL table *and* to a process of connecting tables. Object orientation has nothing to do with all this per se, though objects can easily be mapped to tuples. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN - DBD::SQLite version 1.24_01 - amalgamation
All, I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a DBI Driver) version 1.24_01 has been released on CPAN (by Adam Kennedy). http://search.cpan.org/~adamk/DBD-SQLite-1.24_01/ The main feature of this release is that now DBD::SQLite also uses amalgamated source recommended at sqlite.org, meaning that the entire C source code of the SQLite library itself is now contained in a single file rather than being spread over several dozen files. Some advantages of this change include better performance due to cross-file optimization, and also an easier compilation on platforms with more limited make systems. The last DBD::SQLite release that doesn't use the amalgamated source is version 1.23, which was released 2 days earlier. Also the bundled SQLite library with both 1.23 and 1.24_01 has been updated to v3.6.13 from v3.6.12 that 1.20 had. Further improvements in 1.24_01 over 1.20 involve mainly a significant modernization of the whole test suite, so it uses Test::More, and also there were more bugs fixed, minor enhancements made, and RT items addressed. See http://cpansearch.perl.org/src/ADAMK/DBD-SQLite-1.24_01/Changes as well as http://sqlite.org/changes.html for details. Given that the switch to amalgamated SQLite sources is arguably a very large change (or arguably a very small change), mainly in subtle ways that might affect build/compile systems (though actual SQLite semantics should be identical), ... Please bash the hell out of the latest DBD::SQLite and report any outstanding bugs on RT. Test your dependent or compatible projects with it, which includes any DBMS-wrapping or object persistence modules, and applications. If you want in to DBD::SQLite development, then join the following email/IRC forums which MST created (the mailing list, I am administrating): http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite #dbd-sqlite on irc.perl.org And the canonical version control is at: http://svn.ali.as/cpan/trunk/DBD-SQLite/ Patches welcome. Ideas welcome. Testing welcome. Whining to /dev/null. Note that today's switch to amalgamated sources is the last major short term change to DBD::SQLite that I personally expected would happen (sans updates to the bundled SQLite library itself), but other developers probably have their own ideas for what directions the development will go next. Please do not reply to me directly with your responses. Instead send them to the forums or file with RT as is appropriate. Thank you. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN - DBD::SQLite version 1.20
Alberto Simões wrote: > I was just wondering, is it a good idea to bundle SQLite in the Perl Module? > Wasn't it better to use the system version, if it is available? (well, > I think this isn't done as I have it on the system and DBD::SQlite > just compiled it...) This was discussed, and it was deemed best to bundle the library. That way it ensures users get the latest version of the library when they update the DBD module, and the parts are known and tested together, versus system ones that tend to be out of date. You can still choose to use the system version, but that isn't the default. Also one of the advantages of SQLite in Perl vs other DBMSs is that its drop-dead simple to install, and the bundling is part of that experience. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN - DBD::SQLite version 1.20
All, I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a DBI Driver) version 1.20 has been released on CPAN. http://search.cpan.org/dist/DBD-SQLite/ This follows on the heels of 10 developer releases released starting 2009 March 27th (Adam "Alias" Kennedy has been doing release management). The previous production release of DBD::SQLite was version 1.14 about 18 months ago. Improvements in 1.20 over 1.14 include: * Updated the bundled SQLite library from v3.4.2 to v3.6.12, which carries many new features as well as bug fixes. * Added support for user-defined collations. * Added ->column_info(). * Resolved all but a handful of the 60+ RT items. * Many bug fixes and minor enhancements. * Added more tests, large refactoring of tests. * Minimum dependencies are now Perl 5.006 and DBI 1.57. See http://cpansearch.perl.org/src/ADAMK/DBD-SQLite-1.20/Changes as well as http://sqlite.org/changes.html for details. Now it is especially important, since automatic updates from CPAN such as with the CPAN/CPANPLUS utilities, would now be pulling this new 1.20 by default, ... Please bash the hell out of the latest DBD::SQLite and report any outstanding bugs on RT. Test your dependent or compatible projects with it, which includes any DBMS-wrapping or object persistence modules, and applications. If you want in to DBD::SQLite development, then join the following email/IRC forums which MST created (the mailing list, I am administrating): http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite #dbd-sqlite on irc.perl.org And the canonical version control is at: http://svn.ali.as/cpan/trunk/DBD-SQLite/ Patches welcome. Ideas welcome. Testing welcome. Whining to /dev/null. Regarding near future plans: Now, the current 1.20 uses the pristine several-dozen SQLite library source files, same as 1.14 did. While reality may be different, I believe that the next major planned change to DBD::SQLite is to substitute in the "amalgamation" version, which combines all the SQLite source files into a single file; the amalgamation is the recommended form for users according to the SQLite core developers. See http://sqlite.org/download.html for a description of that. Meanwhile there should be another stable release with any bug fixes for 1.20 to come out first. Any other major changes or features for DBD::SQLite are expected to come out separately from and after the stabilized switch to the amalgamation sources. Please do not reply to me directly with your responses. Instead send them to the forums or file with RT as is appropriate. Thank you. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN - DBD::SQLite version
All, I am pleased to announce that DBD::SQLite (Self Contained SQLite RDBMS in a DBI Driver) version 1.19_01 has been released on CPAN. http://search.cpan.org/~adamk/DBD-SQLite-1.19_01/ This is the first CPAN release of DBD::SQLite since version 1.14 about 18 months ago. This is the change summary since 1.14: 1.19_01 Fri 27 Mar 2009 - Updated to SQLite 3.6.10, and bumped up the version requirement for installed sqlite3 to 3.6.0 as 3.6.x has backward incompatiblity (ISHIGAKI) - fixed "closing dbh with active statement handles" issue with a patch by TOKUHIROM. (ISHIGAKI) - skip 70schemachange test for Windows users. (ISHIGAKI) - applied RT patches including #29497, #32723, #30558, #34408, #36467, #37215, #41047. (ISHIGAKI) - added TODO to show which issues are to be fixed. (ISHIGAKI) - license and configure_requires in Makefile.PL and META.yml (Alexandr Ciornii) - Spelling check for SQLite.pm (Alexandr Ciornii) - Adding arbitrary Perl 5.005 minimum Right now, DBD::SQLite has a new development team with Matt Sergeant's blessing, which is working to keep it updated and fix any outstanding bugs. Multiple people have made commits to it since Jan 24th. I am serving a role as project advocate among other things. So please bash the hell out of the latest DBD::SQLite and report any outstanding bugs on RT. Test your dependent or compatible projects with it, which includes any DBMS-wrapping or object persistence modules, and applications. And yes we are aware that 3.6.10 isn't the latest; that will be fixed soon. If you want in to DBD::SQLite development, then join the following email/IRC forums which MST created (the mailing list, I am administrating): http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite #dbd-sqlite on irc.perl.org Some discussion has also taken place in the dbi-dev list and there is also a general DBI related IRC channel, but the above DBD-SQLite forums were just created last night. The version control is the one Adam "Alias" Kennedy setup around January 24th, which is a Subversion repo. Here are some change log and browse urls: http://fisheye2.atlassian.com/changelog/cpan/trunk/DBD-SQLite http://svn.ali.as/cpan/trunk/DBD-SQLite/ Patches welcome. Ideas welcome. Testing welcome. Whining to /dev/null. In particular, we can use more people with C savvy, as we are somewhat bereft of that among the current team. For one thing, apparently using the amalgamation file from sqlite.org is incompatible with the XS code that talks to the multiplicity of original SQLite source code files, so some savvy is needed to patch it for that migration. Please do not reply to me directly with your responses. Instead send them to the forums or file with RT as is appropriate. Thank you. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Am I understanding Blobs correctly?
Mike McGonagle wrote: > I have never used Blobs before, and in reading some of the literature, > I am just a bit confused. From what I am understanding, I get the > impression that a Blob is no more than the binary data from a disk > file, stored as a string of bytes in a field of the database. > > Basically, I am trying to write some multimedia stuff, and as such, > was thinking that I need to write some "filters" that access specific > types of data (a wav file, or a MIDI file, or etc.). If this is the > case, it almost sounds like it would be better to store the filename > for the data, and then let the system take care of reading the > particular format of the data. A blob in the general sense is a lump of data that is undifferentiated (eg into characters or digits or fields etc) as far as the DBMS is concerned, just a string of bits; it serves as a catch all when you want to store anything that isn't better served by some more specialized type like text or number. Others already mentioned some advantages of storing these in the database rather than the file system, for example making it easier to organize and you don't have to worry about addressing schemes etc like filenames, or rather you sort of do but a database gives you a lot more flexibility. One big thing that wasn't mentioned is that storing things in a database can often give you improved reliability. For example, if you have several changes to your data you want to make atomically, which includes your blob data, then simply marking all the changes as a transaction means the DBMS worries about making everything atomic, and you don't have the extra book keeping and hassle of doing that yourself, as you would if you have pieces in the file system. Now that's not to say that some file systems aren't transactional; some are, but most and typical ones are't, and many that say they are only make meta-data atomic not changes to the file content itself. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request to become co-maintainer of DBD::SQLite
These are replies to posts on the sqlite-users list. However, if there is going to be ongoing discussion I prefer it happen on the dbi-dev list. Not that sqlite-users isn't very on topic itself, dbi-dev just seems *more* on topic, I think. Clark Christensen wrote: >> One of my first code changes will be to require DBI 1.607+ > > The current DBD-SQLite works fine under older versions of DBI. So unless > there's a compelling reason to do it, I would prefer you not make what seems > like an arbitrary requirement. I have 2 answers to that: 1. Sure, I can avoid changing the enforced dependency requirements for now, leaving them as Matt left them. However, I will officially deprecate support for the older versions and won't test on them. If something works with the newer dependencies but not the older ones, it will be up to those using or supporting the older dependencies to supply fixes. 2. On one hand I could say, why not update your DBI when you're updating DBD::SQLite, since even the DBI added lots of fixes one should have. On the other hand, I can understand the reality that you may have other legacy modules like drivers for other old databases that might break with a DBI update. I say might, since on the other hand they might not break. Still, I'll just go the deprecation angle for now. > Otherwise, it sounds like a good start. Matt must be really busy with other > work. > > I'll be happy to contribute where I can, but no C-fu here, either :-( Thank you. Ribeiro, Glauber wrote: > My only suggestion at the moment, please use the amalgamation instead of > individual files. This makes it much easier to upgrade when SQLite > releases a new version. Okay. Jim Dodgen wrote: > I'm for the amalgamation too. the rest of you ideas are great also. > excelent idea to use Audrey Tangs nameing convention. > > I have been stuck back at 3.4 for various issues. > > I do Perl and C and offer some help. Okay and thank you. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] request to become co-maintainer of DBD::SQLite
1.607+ and Perl 5.8.1+ (and the former requires the latter too), though I may only ever run it under 5.10.x on my machine. But if anyone knows that it will work with older versions, they can submit a patch to that effect. 7. I would also like to adopt the versioning scheme that Audrey Tang used, so that for example a first stable release with the current SQLite would be DBD::SQLite 3.6.8.0, with the last digit only being updated while updates to DBD::SQLite itself occur but updates to SQLite itself don't. One question I still have to figure out though is whether that can be done in combination with the _NN suffix to mark developer releases, eg as 3.6.8_0 or 3.6.8.0_0 etc, so that CPAN install tools work, and nothing on CPAN/PAUSE/etc would break. Presumably I'd add a dependency on version.pm (bundled with Perl 5.10.x) in any event. The main benefit of this versioning scheme is that it is easy for users to know at a glance what they're getting, and also if for some reason users need me to later bundle some older SQLite version, the space already exists for appropriate lower version numbers. Basically I'm doing this because someone has to do it, and I'm as good a default person as any until someone better suited (eg, with more C-fu) comes along and takes my place. Matt, thank you in advance for a quick reply. To everyone, please don't actually submit patches to me until I announce that I'm ready to receive them, or just send them to RT as you already were. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.8
I have contacted both flavors' authors about the update, even offering to co-maintain the binding experimentally if necessary, so we'll see what happens. Part of the outstanding issue is that according to some users I trust DBD::SQLite 1.14 introduced significant bugs that weren't in 1.13, such that they recommend using 1.13 instead, so there are binding-specific issues still to resolve as well. Hence doing the update well isn't as simple as just substituting updated SQLite source files. -- Darren Duncan Jim Dodgen wrote: > I am having better luck with the amalgamation that has been created by > Audrey Tang. > My production is still on 3.4 and testing on 3.6 with the amalgamation has > been promising > > On Mon, Jan 12, 2009 at 8:14 PM, Darren Duncan <dar...@darrenduncan.net>wrote: > >> Jim Dodgen wrote: >>> Please post back with success/failure of the Perl bindings. >> Yes, about that ... it seems the bindings have gotten stale and have some >> outstanding issues ... I'm hoping that someone will step up and maintain >> them >> ... I currently lack the C experience to do it easily myself if any such >> maintenance is needed, except on an experimental basis ... I'll also try >> contacting the most recent maintainers about it. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.8
Jim Dodgen wrote: > Please post back with success/failure of the Perl bindings. Yes, about that ... it seems the bindings have gotten stale and have some outstanding issues ... I'm hoping that someone will step up and maintain them ... I currently lack the C experience to do it easily myself if any such maintenance is needed, except on an experimental basis ... I'll also try contacting the most recent maintainers about it. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.8
D. Richard Hipp wrote: > SQLite version 3.6.8 adds support for nested transactions This is *excellent* news! Thank you so much for implementing nested transactions in SQLite! As far as I was concerned, and AFAIK had argued in the past, that was the single most important piece of missing functionality. Something whose presence can make reliable database development an order of magnitude easier. Unlike some add-on features which could be done in wrappers, nested transactions was definitely something that was only appropriate to be implemented in the same low level as normal transactions in the DBMS. Its about code being able to declare and have enforced at any level of granularity that a set of operations is atomic, as a transaction, without worrying about what calling code is doing with transactions, and making it easier to do error handling and retry/abort etc. I'm now a step closer to being able to easily implement my Muldis D relational database language over SQLite. I'll hopefully be able to start testing in a few weeks, assuming that DBD::SQLite et al / the Perl bindings are still functional under Mac OS X and Linuxen with the new version. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple tables within a database
Loosely speaking, I put things together in the same file if some of those things are necessary to understand or interpret the other things, or if there is a logical dependency between things (say, a foreign key), they go in the same file. Being in one file ensures that all the interdependent parts are kept together. If A and B can be fully understood in complete isolation from each other and have no mutual constraints, it is safe to have them in separate files. If SQLite supported user-defined data types, the type definitions and tables etc based on them would need to go in the same file. Triggers and views and the tables they work on need to be in the same file. -- Darren Duncan Eric S. Johansson wrote: > what's the general rule for deciding when to put multiple tables within a > single > sqlite db file? I think the answer is something like you put tables together > in > one database file if they refer to different aspects of the same data element > and you put them in separate database files if there's no connection except > the > databases are used in the same application. For example, in my case, I have > one > table which contains the raw original data, a thoroughly cooked form of the > original data, and a series of data elements which are used for searching and > display. The related table contains information derived from postprocessing > and > will also be used for searching and graphing. The second table's information > could be regenerated anytime at a cost of running through every record in the > database and recalculating it. As a result of this association, I figure it's > appropriate to place both records in the same database file. > > The third table tracks data from another part of the process and has no > connection to the first two tables except that it is used as part of the > postprocessing calculations that feed the second table described above. I > figure the third table should go in its own database. > > For what it's worth, record counts could easily cross 100,000 for each one of > the tables. Hope it's not time for mysql :-) > > Thanks for a feedback. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HOWTO: Parse user SQL and add persistent SQL
Kristoffer Danielsson wrote: > Consider an application where the user enters a search string: > SELECT Year FROM Car WHERE Brand = 'Audi' > > Now I want to add some conditions, depending on settings in the application. > For instance, I would like to modify the above SQL string to get this: > SELECT Year, Color FROM Car WHERE Brand = 'Audi' AND DateCreated > > '2008-10-10' > > Obviously, making string replacements is not a good idea. The only smart > solution is to somehow edit the tree-representation of the SQL statement. > Probably the same technique used for statement optimization. > > The parser is already there so the question is; how do I edit the > SQL-statement on a low level, before executing it? > > Thanks. A better safer solution for most cases is to not let user type raw SQL at all, but rather you can just provide a collection of form fields where they enter the basic information and then you generate a SQL query from it. In that case, you are *starting* with the tree as it were. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite syntax diagrams
Gavin Kistner wrote: > The diagrams are read most easily left to right; rolling to a new line > greatly reduces the utility, IMO. Roughly 7% of the 'net is still > using 800x600, and surely far less of tech savvy audience. > > I suggest increasing the image width to at least 900px before > wrapping, and possibly even suggest possibly never wrapping them > (instead putting each in a scrollable section). There are 2 problems with scrollable sections: 1. The diagrams can't easily be printed out if one wanted to do that. 2. Those are a lot more difficult to scan with the eye. It is a lot easier to scan if we can see the whole thing at once, even if on multiple lines, then if we have to side-scroll back and forth to find things. This said, if say making some diagrams 20% wider would lead to a large improvement in readability, that should be fine, as these would still print well, most people would see them without side-scrolling, the rest would only side-scroll a bit. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Suggestions
ivo welch wrote: > * SHOW columns FROM table--- would be a great addition, if only > for compatibility with MySQL. Aside, it is easier to remember than a > pragma. MySQL's SHOW features are just an old MySQL-proprietary way of doing introspection, prior to their adding support for the SQL standard INFORMATION_SCHEMA. If SQLite is going to add any introspection features for compatability with other DBMSs, it should go the information schema route, or in SQLite terms, information database. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the last row
Aravinda babu wrote: > Is there any easy way to get the last row in the table ? What do you mean by "last row"? Do you mean you want to get back the row that was most recently inserted? If so, then the table needs to have a column containing info about insertion order of rows, such as a serial number that is guaranteed to always increase and not repeat, or such as a timestamp. If you had a column like that, say name it 'myorder', then you could use a query like this to get the last row: select * from mytable where myorder IN (select max(myorder) from mytable) If you mean get the row that would sort last in a query, then you have something like this at the end of the query, after the "ORDER BY": LIMIT 1 OFFSET (select count(*) from mytable) - 1 Or better yet, reverse your ORDER BY condition / make it DESC, in which case you can then just return the *first* row: LIMIT 1 Note that rows in tables are not ordered (a table is a set of rows), so there is no concept of newly inserted ones going at the end. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] relational/OO (was Re: best language match for SQLite?)
Michael Schlenker wrote: > Patrick schrieb: >> I am a beginner to intermediate Python Programmer. I can use SQLite with >> it just fine but it is my understanding that relational database and >> object oriented programming our not the perfect marriage. > > Otherwise it does not really matter. Your right that there is a mismatch > between the set oriented operations of relational databases and the more > item oriented OO world, thats why there are all those ORMs like SQLalchemy, > Storm etc. which bridge the gap. Generally speaking there is no impedance mismatch between relational databases and the OO world. Both natively provide relation and tuple data types and relational operators, and sets and arrays etc, and booleans, numbers, text and binary strings etc. Both natively support the creation of arbitrarily complex user-defined data types and operators. Both natively support automatic persistence of any of the above, and atomicity, and transactions. Both support definition and enforcement of arbitrary type, state, and transition constraints, and triggers. Both support multiple views of the same data, sometimes updateable. Both support invoking the compiler at runtime. Both support type graphs and polymorphism, substitutability, inheritence. How they differ are in relatively minor ways, such as in OO you have the concept of a value that is a pointer to a memory address or implementation detail, while in a relational database you don't and there is a clear distinction between a value and a variable, and those are referred to symbolically. The reason that ORMs exist is to either compensate for relatively minor differences, or to provide a wider variety of APIs for a database than the database is providing itself, or compensate in the application-space for a database implementation that lacks some of the relational database features. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
Gerry Snyder wrote: > I am not sure what all this means when taken together. > > It sounds as if defining id as shown above is unnecessary, since it is > just an alias for ROWID, and if one is stable the other has to be. Correct? > > So now I can't interpret "ROWID can change" as meaning anything other > than "the ROWID supplied by SQLite while doing an insertion, if none is > supplied by the user, can change at any time." Is this correct? > > My little Tcl/Tk routines for displaying and managing SQLite files have > always assumed ROWID is a safe stable way of determining a row. Have I > missed something? (again?) If you want to truly be safe, then *never* use ROWID (that is, ignore its existence), and *always* declare your own primary key, even if that is just an integer, and always reference your explicit primary key rather than ROWID. This way, what columns exist and their values are always controlled by you, and moreover your schemata would then be more portable between different DBMSs. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] On UNIQUE and PRIMARY KEY
D. Richard Hipp wrote: > One occasionally sees SQLite schemas of the following form: > > CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ); > > In other words, one sometimes finds a PRIMARY KEY and a UNIQUE > declaration on the same column. This works fine in SQLite, but it is > wasteful, both of disk space and of CPU time. If we ignore the > INTEGER PRIMARY KEY exception, the "PRIMARY KEY" keyword in SQLite > means the same thing as "UNIQUE". Both create a unique index on the > column. So if you use them both on the same column, you get two > identical unique indices. SQLite will dutifully maintain them both - > requiring twice the CPU time and twice the disk space. But having a > redundant index does not make queries run any faster. The extra index > merely takes up time and space. > > So here is a good rule of thumb: Never use both UNIQUE and PRIMARY > KEY on the same column in SQLite. Is there any reason why you can't just optimize this away at the parser level by ignoring the UNIQUE keyword and only make the PRIMARY KEY index? (Or alternately to disallow those 2 terms appearing together?) Does having identical UNIQUE and PRIMARY KEY constraints lead to different semantics than having just the PRIMARY KEY? I suggest changing SQLite about this. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Kodok Márton wrote: > Hello, > I am working on a syncing project and I do have a lot of triggers to > read/write foreign keys. > And while I do the sync of one table the triggers are causing a strange > effect (as the other table is not yet synced). > Is there a pragma to disable triggers on the sqlite database? > Regards, > Marton I think a better solution here would be some kind of syntax that lets you perform multiple data-manipulation operations "simultaneously"; for example: INSERT INTO DEBITS (...) VALUES (...), INSERT INTO CREDITS (...) VALUES (...); In this example, the 2 statements are separated by a comma rather than a semicolon (you can use different syntax instead if you want), so they are treated as a single statement in that their updates all happen as a single update. In this case, the triggers don't have to be disabled; they would just run after the combined statement completes. Note that my proposal is not the same as transactions, since you can have triggers run between statements in a transaction and said statements are not collectively atomic in the same way where no database state exists between statements. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
D. Richard Hipp wrote: > 3. Double-quoted names fall back to being string literals if > there is no matching table or column name. > > So I'm giving some thought to removing feature (3) above and > disallowing double-quoted string literals. My concern is, though, > that this might break many existing applications. > > What opinion do you, gentle users, have of this matter? I think you should go ahead and make the change you propose, and soon as possible. But release that change as version 3.7.0 so it is more clear to people that this is a significant change as far as the user experience goes, that they should realize this might break something if they were relying on the old broken behaviour, and that they need to fix their own code. I don't agree with having a pragma to turn on the old behavior, but I do like the idea of issuing warnings when it appears that a user may be relying on the old behavior; these warnings can help them track down what areas of their code need to be fixed. Or alternately make a 3.6.x series release first that adds warnings but keeps the old behaviour, then make a 3.7.0 release that keeps the warnings (modified if needed) but drops the bad behaviour. Note that any warnings like this should be possible to turn off when the user knows that their code is compliant and their string literals shouldn't be checked anymore for similarity to column names etc. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Gregor, why did you do that more complicated version with the subquery and sorting et al? The short version that RBS would have worked a lot better; you just need to say? update binary_report_fmt set column_id = column_id + 1 where column_id > 1; ... and then insert a new row with column_id = 2. All that other stuff you did just makes things unnecessarily more complicated, and possibly buggy. On a related matter, UPDATE statements are atomic operations, so the fact that the id is a primary key doesn't matter. Since you're incrementing all the id values simultaneously, there are no duplicate values at any time, so the primary key constraint would remain happy. -- Darren Duncan Gregor Brandt wrote: > Hi, thanks this is great. Of course I forgot to mention that the id > is a primary key, so I get an error about duplicate primary keys. > > I tried this: > > update binary_report_fmt set column_id = column_id + 1 where column_id > = (select column_id from binary_report_fmt where column_id >= 3 order > by column_id desc); > > but it only updates the last item. I guess I can make it a non- > primary key..then it works perfectly. > > Gregor > > On 16-Jun-08, at 10:04 AM , [EMAIL PROTECTED] wrote: > >> update >> table >> set id = id + 1 >> WHERE >> id > 1 >> >> RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DDL statements in transactions
Robert Lehr wrote: > Oracle does not allow DDL statements to be executed in transactions, > i.e., it does but the statements are COMMITted as they are executed, > thus cannot be rolled back. > > PostgreSQL does allow DDL statements to be executed in transactions, > i.e., if a DDL query fails then then entire transaction is rolled back > and no tables, indices, etc., are created, modified, etc. > > Which behaviour is implemented in SQLite? AFAIK, SQLite has DDL subject to transactions, like you described for Postgres. And that's just how it should be. Transactions should subjugate *all* DBMS activity. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
P Kishor wrote: > On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote: >> Hi All, >> I have the database which has a lot of insertion and deletion. >> Is there anyway that I can get the actual database size without running >> VACUUM. > > Your question implies that VACUUM lets you "get the database size." > No, it doesn't. VACUUM recovers the space left behind by deleting data > from the db. > > To find out the size of the database, just read the size of the file > in the operating system. > > Or, maybe you are asking something completely different that I don't get. I think what Joanne's asking is if it were possible to query what size the database would become if it were vacuumed without actually vacuuming it. Maybe as part of a cost analysis for whether to vacuum, or stats for the user as to how much of the database file is unused space. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Federico Granata wrote:
>> Your example doesn't counter my suggestion at all, which is to use the data
>> only and not a special rowid. So you put 2 identical rows in a table.
>> Since rows in a table are unordered, there isn't even an ordinal position
>> to distinguish the 2 occurrences of that same row. Since they are
>> identical, they are redundant, and so they are equivalent to just 1 such
>> row. So updating both copies is perfectly fine. Though better yet is to
>> not store a second copy in the first place.
>>
> LOL
> English isn't my first language but I think you are joking ...
>
> If I want to make a table with a list of people (name and age) I can have
> two or more row with the same name and age and they aren't redundant and the
> implicit rowid is different.
The normal proper way to do what you said is to declare a table like this:
CREATE TABLE person (
person_id INT PRIMARY KEY,
name TEXT,
birthdate DATE
)
In my example, you are using only the normal data, which is the 3 columns
specified, and you are not referring to a column you didn't declare
("rowid" or whatever), but by a column you did declare, "person_id".
By contrast, defining a table like this is inferior:
CREATE TABLE person (
name TEXT,
birthdate DATE
)
In that example, the rowid would be generated and you can't use something
externally meaningful (such as SSN) to distinguish one Joe from another.
My point still stands. Or my other point of adding a LIMIT clause to
UPDATE also stands if you want to create tables the second way.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Ralf Junker wrote: > Darren Duncan wrote: > >> Ralf Junker wrote: >> >>> Can you suggest an alternative to a single reserved name to represent the >>> column which uniquely identifies a database record under any and all >>> circumstances? >> Yes, change the interface to RowID into a routine call rather than a column >> name; eg use "RowID()" rather than "RowID". > > I can not see how this would actually work with SQLite. Any use-created RowID column would override and hide the implicit rowid column even for the RowID() function, would it not? No it wouldn't. You can still access SQLite's hidden RowID no matter what users name their columns. The thing is, since my proposal involves SQLite making syntax for accessing its hidden rowid using a function rather than as a fake column name, that function or the syntax for invoking it can be anything the SQLite developers pick that they know will be in a separate namespace from the one that table columns are in. >> Then when using it in a SELECT, you can say "RowID() as foo" in the select list where "foo" is different than a normal table field. Such is how 'standard' SQL does it. > > What is 'standard" SQL? Can you give an example how this is used with other DB engines? I am not familiar with MySQL, but searching the documentation I could not find that it supports this concept. Maybe others do? Actually, what I was meaning to get at here was the concept of a user's SQL statement using 'as' to rename the result of the special keyword for a rowid et al to some arbitrary other word to represent it as a column name, that didn't conflict with any column names the user chose for their tables. Various examples using (ANSI/ISO SQL:2003) standard SQL or other DBMS did things like this; the other reason for renaming is eg so that when joining 2 tables, the rowid from each table has a distinct column name. >> Any manager app can read the database schema first and generate a name "foo" that is distinct. > > As things are at the moment, the implicit, unambigous RowID can not be retrieved from the database schema if all three "RowID", "_rowid_", and "OId" column names are overridden. This applies to SQL as well as to user-defined functions. Then a candidate fix is for SQLite to use some namespace syntax for referring to those special things that is distinct from the namespaces of user-defined things. If it were me, I would have eg all system-defined operators named sys.foo, and all user-defined ones grouped under usr.foo, or that idea. And don't worry about whether or not doing this is compatible with other DBMSs or not, considering that the whole rowid/oid stuff is very non-portable and wildly implementation dependent anyway. If you want true portability, you do this by ignoring all these special ids and have explicit (unique) keys on your data columns, and then you use your actual data as its own unique identifier ... which is how the true relational model works anyway. Doing other than using the actual data to identify itself is just asking for trouble, and is a main reason why this whole rowid problem started. Doing it right saves trouble and gives portability. In fact, data identifying itself is the only approach I really advocate; any of my other suggestions which may try to use the special rowids are never my first choice, and I have less impetus to argue for them. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Ralf Junker wrote: >> On a separate note, it is best for one to be able to name a table or column >> et al anything one wants, with all the choice of names as you can store in >> a text column for user data. Reserved words aren't an issue as long as >> entity names are referred to with an unambiguously different syntax, such >> as quoted identifiers as SQL does support. Then database users don't have >> to worry about implementation details and can name tables and columns >> whatever they want; saying they can't name their column "RowID" is a leaky >> abstraction. > > Sure we all dislike restrictions. Can you suggest an alternative to a single reserved name to represent the column which uniquely identifies a database record under any and all circumstances? Yes, change the interface to RowID into a routine call rather than a column name; eg use "RowID()" rather than "RowID". Then when using it in a SELECT, you can say "RowID() as foo" in the select list where "foo" is different than a normal table field. Such is how 'standard' SQL does it. Any manager app can read the database schema first and generate a name "foo" that is distinct. -- Darren Duncan P.S. Alternately, you can eliminate RowID entirely as a user-visible concept, leaving it just to SQLite's internals. And also disallow storing duplicate rows, so that the values of the row fields themselves will uniquely identify a database record under any and all circumstances. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Ralf Junker wrote: > But thinking more about hijacking "RowID" I am glad this is now a separate > thread. Lack of a reseverd "RowID" column name to guarantee unambiguous record operations by general SQLite tools is a potential thread to data security IMO. I think the real problem here is that SQL allows you to have non-distinct rows in a table, when all rows should be distinct. Working within SQL's flaws, the solution here is for every table to have a unique constraint on one or more table columns. Then applications just use that to uniquely identify the row. Rows should be identifiable by user-visible data, not hidden data, since a database is supposed to model reality and people identify things based on their someway-visible attributes. On a separate note, it is best for one to be able to name a table or column et al anything one wants, with all the choice of names as you can store in a text column for user data. Reserved words aren't an issue as long as entity names are referred to with an unambiguously different syntax, such as quoted identifiers as SQL does support. Then database users don't have to worry about implementation details and can name tables and columns whatever they want; saying they can't name their column "RowID" is a leaky abstraction. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Finding Missing Table Rows
Gerry Snyder wrote: > Darren Duncan wrote: >> Clue stick coming up. There's a much simpler solution. >> >> You should be using relational difference instead, the MINUS keyword, whose >> syntax is the same as UNION but for the keyword. >> > I think maybe you mean EXCEPT, not MINUS. > > Gerry Yes, different spelling but same feature. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Finding Missing Table Rows
Rich Shepard wrote: >I have two tables that should have the same number of rows, but one is 2 > rows short and I'm trying to identify which rows exists in the first table > (Fuzzyset) that's missing from the second table (Rules). > >I thought that a right outer join might work with "NOT EXISTS" in the > >I'm sure that I've seen (and probably used) this type of query before so > I've no idea why the proper syntax is eluding me. > >A clue stick will be very helpful. Clue stick coming up. There's a much simpler solution. You should be using relational difference instead, the MINUS keyword, whose syntax is the same as UNION but for the keyword. Try this: (select f.parent as c1, f.subcomp as c2, f.comp as c3 from Fuzzyset as f) minus (select r.var_name as c1, r.subcomp_name as c2, r.comp_name as c3 from Rules as r) The result should have 3 columns and 2 rows. Rename c1,2,3 to taste, or adjust any other details as necessary. If you wanted more info returned than that, then use the above in a subquery which is joined with Fuzzyset. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mailing list
[EMAIL PROTECTED] wrote: > I apologize if this is off topic but wouldn't it be better to use some > standard discussion board, like PHPBB instead of mailing list? It is pain to > use mailing lists (you have couple of them for sqlite), ((un)registration, > posting, receiving tons of e-mails, searching, etc.). > Message from my other free email was rejected because of it's IP was listed > at some spam list. I think mailing lists worked fine 20 years ago but does it > have any advantage today ? :) > Rado Email lists have several important advantages over typical web boards, as I see it: 1. All the discussion details come to my email box that I regularly check, so I can be aware of and respond to things quickly if I choose. By contrast with a web board, I'd have to go out of my way to remember to periodically check in with the board to see if anything interesting came up, and often I would forget to do so. This all said, it is very useful to see web archives of the email lists to browse. 2. With email lists, its easy to have a local archive of the list, at least if one downloads their emails to their computers, so our records stay and are searchable if the list disappears or the internet goes off. 3. Email programs are typically easier to use, and are more reliable. I may have missed some points. This all said, I recognize some advantages of BBs over email. I think the best option is a hybrid. Have some database-backed system with both a web interface and an email interface, and people can choose to connect one or both ways. Any messages posted on the web interface go to the email users, and vice-versa. Then you have both a web archive of everything, and local users can have their own archive too. Perhaps more specifically I suggest a hybrid of what things like Yahoo Groups provides, and a bulletin board. But I don't know if those tools exist yet. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-valued attributes
At 5:00 PM -0800 2/13/08, gongchengshi wrote: >I have a table were each row needs to be able to store a list of entries from >another table as one of its attributes. For instance the table is a >collection of search filters. The filters table has attributes: FromDate, >ToDate, Users, Devices. The Users attribute is not a single value but a >list of Users contained in the Users table. Same with the Devices >attribute. The Devices attribute is actually a list of Devices in the >Devices table. > >How do you go about defining this schema in SQL? The book I am reading >"Database Systems" by Connolly and Begg say that you can have such >relationships but they don't say how to create them. I am using sqlite as >my DBMS. Some quasi-relational DBMSs (and all truly relational DBMSs) support actual multi-valued attributes/fields, which in the general case are relation/rowset valued, or in the less general case are specifically set or array etc valued; PostgreSQL supports the latter to some extent. AFAIK, SQLite does not support multi-valued fields, and so with it your schema will have to be of the form you get when you split the prior relvar/table with each multi-valued attribute/field separated into its own relvar/table (every new table also has a copy of the original table's primary key attribute), and subsequently those extra relvars/tables are relational-ungrouped to turn each multi-valued-field tuple/row into multiple tuples/rows. You are then creating what are commonly called intersection tables, afaik, which are common when implementing many-to-many relationships between tables. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] SQLite character comparisons
on storage or on compare is beside the point), as that is what users would expect; in which case, the actual codepoints in use would be considered non-significant, and be abstracted away from the user. 6. So as long as identity considerations are handled properly, it doesn't matter for satisfying the relational model of data as to whether trailing spaces are significant, just as it doesn't for graphemes vs codepoints abstraction. So then in this regard I consider SQLite's current approach and the SQL standard's proscription to be equally valid. 7. So my argument about that trailing spaces should be considered significant comes more down to what is considered well established principles of good language design. I would argue that if you want a simpler situation, that all the characters are significant, and that is what most programming languages do for character string literals. 8. If one wants to argue for the merits of ignoring trailing spaces, then I would ask for what reason and why stop there? I would imagine that a valid reason to consider said spaces insignificant is if, say, the text is meant to represent some human speech, and it is more just that there are spaces between or around words at all that is significant, not how many spaces. And so, in such a situation where trailing spaces are insignificant, I would think that having varying amounts of space characters between words is also insignificant, and comparisons should treat as if each word is separated by exactly one space. 9. And so an argument for all characters being significant is largely an argument in keeping things simple, which I think in the general case is what people expect. For situations where people expect different, they probably expect multiple other differences in conjunction with the trailing spaces thing, such as middle or leading spaces. 10. In the interests of useability, the base behaviour should be simpler, such as SQLite is, and special-casing strings should be built on top of that base, rather than the other way around. It's probably a lot easier or more elegant to add special cases than to remove them. Also such as drh provided with his new collation commit. -- Darren Duncan P.S. As another piece of full-disclosure, I'm in the midst of writing the spec for an industrial-quality programming language, named Muldis D, which is intended to replace SQL as the defacto language of choice for relational databases. I'm also significantly involved in the design of the Perl 6 language. So I have been looking at the relevant issues quite closely and I believe I can rationalize any arguments I make in regards to how a DBMS or a programming language should behave, and moreover that such differences from the SQL standard are viable in the real world for real work. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
At 10:57 PM +0100 1/18/08, Zbigniew Baniewski wrote: On Fri, Jan 18, 2008 at 12:32:36PM -0800, Darren Duncan wrote: > If trailing spaces were supposed to be insignificant for an equality test, then it should not be possible to define a string value containing trailing spaces at all. Yes, yes: quite right... the above reminds me something... ;) Don't read too much into that statement; I'm was not raising the auto-trim thing. See my previous email in this thread, which replied to Jeff Fowler, which addresses his own comment on my statement. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite character comparisons
At 4:11 PM -0500 1/18/08, Fowler, Jeff wrote: "Better" depends on who you ask - I'd say it's worse, and I bet most DBA's would agree. The ANSI standard is to ignore trailing spaces when comparing character strings in a WHERE clause, a HAVING clause, or a join. So I can take the exact same data, run the exact same query, yet get a different answer from SQLite vs. Oracle or SQL Server. In fact, we found this issue because we DID get a different answer. And every SQL implementation already has many differences from every other one, even with fundamentals, making them all generally incompatible and proprietary, and this example is right in line with the kinds of differences they have. Other implementations have distinct var-char and fixed-char types, while SQLite only has the former. Part of the problem here is that the ANSI standard is itself flawed. (Maybe this particular case of trailing spaces is an exception, but many parts of ANSI SQL, AFAIK, are more focused on what the syntax of SQL is, and they say that the semantics, even of some fundamental operations, is left up to the implementation to decide for itself. So what good does it do you if SQL of the same syntax will compile on different DBMSs if it behaves differently in each one? And the standard considers this valid.) How many other programming langauges besides ANSI SQL treat trailing spaces as insignificant. Regarding whether by extension it should be impossible to create strings with trailing spaces; I side with the SQLite developers who say it isn't the engine's job to trim blanks in data. Most other engines I've used do not trim spaces either, even if the field is a varchar. And rightly so, you should not trim spaces, because spaces are significant. Herein lies a wider part of the problem. The ANSI SQL is inconsistent in how it treats trailing spaces in strings. On one hand it wants to preserve them, but on the other hand it wants to ignore them in its most fundamental operation other than preserving. (With my "it should not be possible" sentence, I was not saying that spaces should not be trimmed in the fictional scenario where a character string does by definition not contain trailing spaces, but that code specifying them should produce an error rather than succeed. The matter is analagous to what would happen if you write code that tries to treat the character string literal 'foo' as a number.) But - whether ANSI compliance is considered "bloat" is not really my place to comment upon. I guess it's really is up to the SQLite team. Yes it is up to the developers. And they have already demonstrated willingness to do some things differently than ANSI SQL because they considered the differences to be improvements, or alternately reasonable feature cutting. Purely from a business usability standpoint (not a programming one), I would say there's no question that it's far more useful to do comparisons the ANSI way. And why is it more useful to ignore trailing spaces than respect them. And if ignoring them is more useful, why do most programming languages (AFAIK) respect them? If for some reason I truly want to compare & respect trailing spaces, I can still do that using a function such as HEX(A) = HEX(B) or something better. I would argue that it is rediculous to do such ugly things in order to do something that should be fundamental, and is simple and fundamental in any other language. Better for basic '=' comparison to test that the values are the same, and have some other operator or function like 'equal_when_trimmed( v1, v2 )' when you want various exceptional comparisons. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
At 10:43 AM -0500 1/17/08, Fowler, Jeff wrote: Hello All, I've used SQL Server for over 15 years, Oracle off & on when I have no choice, but SQLite for a couple weeks. I've just learned (today) that SQLite respects trailing spaces when comparing two character fields. I.e. 'SQLITE' <> 'SQLITE ' Is this behavior intentional? Neither SQL Server nor Oracle do this. Just curious as to why it works this way. Because respecting the actual contents of the string is the better way to do things. The strings 'SQLITE' and 'SQLITE ' are not the same string. Just as the strings 'SQLITE' and 'sqlite' are not the same string. A computer language is more logical, predictable, and easy to use when a test for equality or inequality actually treats every distinct value as distinct. If trailing spaces were supposed to be insignificant for an equality test, then it should not be possible to define a string value containing trailing spaces at all. Moreover, treating 'SQLITE' and 'SQLITE ' as not being the same string also is consistent with the SQLite philosophy, because it means SQLite has fewer exceptions to be concerned with in a simpler set of rules, and also not having to check lengths and space pad before each compare also makes the code simpler, and less buggy, and it saves CPU cycles. A value equality test is a very common and fundamental thing to do in a DBMS, and bloating that will have a big performance impact. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Aggregates in SELECT without GROUP BY
At 11:41 PM -0800 1/14/08, Joe Wilson wrote: In sqlite, assuming there's at least one row, an aggregate SELECT with no GROUP BY clause is conceptually the same as an equivalent SELECT with GROUP BY NULL - i.e., the group of all rows. (I say 'conceptually' because GROUP BY NULL is much slower). I actually thought of raising this issue too, but then thought it would complicate the discussion. One could conceive a SQL SELECT, if it has no explicit GROUP BY but has explicit aggregate functions in the select list, as if it had an explicit GROUP BY but an empty column list, that is, a group per distinct source sub-rows of zero columns rather than per distinct source sub-rows of 1..M columns, and so a source rowset of 1..N rows would turn into a result rowset of exactly 1 row. However, unless I'm mistaken about SQL behaviour, I see this analogy not holding true when there are zero source rows. Normal SQL will return exactly 1 row when using aggregate functions and no GROUP BY clause, which is actually good when using things like COUNT or SUM. However, any GROUP BY, whether over zero columns or 1..N columns, would return zero rows if there were zero input rows. That is the only way it can work if its behaviour is intended to be consistent. Of course, that's not to say that there is any overall logical inconsistency, IF you consider that the native environment for aggregate functions is NOT with a GROUP BY. So, use an aggregate on any rowset of 0..N rows, you get 1 row back. If you conceive GROUP BY as actually just creating a table some of whose row field values are themselves tables (the columns being grouped by are outside of the inner tables, those not being grouped by are inside them), then using aggregate functions together with a GROUP BY is treating each inner table like the only table as far as the aggregates are concerned, and so applying the aggregates to inner tables to convert them to inner tables of one row each, then typically each of those is merged with its containing single outer row again. On that note, a group-by of zero columns would then produce a table having a single row and single field whose value is the original table. Now smarter relational DBMSs that support table-valued-fields could then let you use a GROUP BY in isolation, since if you keep any fields not being grouped by, they form rows of inner tables. Less capable DBMSs don't let you directly use the actual result of a relational group, and require you to do the additional step of either discarding non-grouped-by columns or using aggregates on them. I don't know if SQL has provisions for a relational operator that results in the intermediate value I mentioned (table of tables), but even if it doesn't, a truly relational DBMS would have it. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Aggregates in SELECT without GROUP BY
At 10:17 PM -0500 1/14/08, Griggs, Donald wrote:
Hi Duncan,
Regarding: " A DBMS accepting such queries isn't just a little
dangerous, its flat out wrong. I would ask what rationale there is for
this query not failing. -- Darren Duncan"
I'm not asserting that you have to agree with the rationale, but did you
see and read the discussion that Joe Wilson pointed out to you?
=
This issue is debated from time to time on the list:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html
Sorry, I missed the url on my first reading.
Also, my first comment was based on the idea that SQL usually returns
exactly one row on a query that uses an aggregate but no group-by,
and where all result field values are scalar.
However, I can see example "SELECT MAX(a), b FROM T;" conceptually
being valid where either 1 row is returned with the 'b' value being
collection-typed (containing {'Cat','Dog','Mouse'}), or alternately
(such as because actual collection-typed values aren't supported by
the DBMS) where that answer were ungrouped such that the main query
results in 3 rows where the 'b' value has each of those 3 and the 'a'
value is 7 for every row. Or substitute 5 for 3 in either case if
you are operating bag-oriented like SQL prefers rather than
set-oriented.
So if that's what happens, then fine.
But the OP implied that the query returned exactly 1 row, with '7'
for 'a' and a random value 'Mouse' for 'b', and such a result is what
I am objecting to.
-- Darren Duncan
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Aggregates in SELECT without GROUP BY
At 3:14 PM +0200 1/14/08, Lauri Nurmi wrote: SQLite seems to be accepting SELECT queries that use aggregate functions without a GROUP BY. This is a little dangerous, because queries that should not work at all are returning sensible-looking results. sqlite> SELECT MAX(a), b FROM T; 7|Mouse I would argue that this is a bug in the general case, where b does not have the same value in every row of T. A DBMS accepting such queries isn't just a little dangerous, its flat out wrong. I would ask what rationale there is for this query not failing. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
At 8:46 PM -0600 1/13/08, Rick Langschultz wrote: I was wondering what would constitute the creation of SQLite 4.0? Since the VDBE is being revamped I would consider this a pretty big revamp of the SQLite code. I am looking forward to testing this out new engine out. I also wanted to know what the difference between stack based and register based is. Unfortunately, i do not mess with a lot of C / C++ code in my work. So I was a little curious. Although I would have considered the current large update to be 3.6.0 material, I definitely do *not* consider it to be 4.0.0 material. Major version updates (such as to 4.0) should not be done lightly, and should only be done for things like large or particularly incompatible database file format changes, such as the 2.x to 3.x update was, or for large or incompatible API changes, either way things that users could have no excuse not to notice. So I agree with Richard's current versioning plan concerning this. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
At 9:07 PM -0500 1/12/08, D. Richard Hipp wrote: In case you haven't been watching the timeline (http://www.sqlite.org/cvstrac/timeline) we are in the middle of some major changes. The virtual machine inside of SQLite is being transformed from a stack-based machine into a register-based machine. The whole virtual machine and the code generator is being rewritten. Slowly. Piece by piece. I haven't done an overall line change count yet, but we are looking at some pretty serious code churn. 3.5.4 to 3.5.5 is likely to be the biggest single change in the history of SQLite. I would think something like that is worthy of a 3.6.0 version number. Not just a minor version increase that would be more suitable for minor changes or bugfixes. On the other hand, I can understand if you save the larger increments for changes that are user-visible and not just plumbing. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: "always-trim" - feature suggestion
At 9:14 PM +0100 1/7/08, Zbigniew Baniewski wrote: On Mon, Jan 07, 2008 at 10:41:35AM -0800, Ken wrote: In the spirit of the conversation the OP wants the DB to strip out white space. I contend that this is the applications responsibility either as DRH suggested by using proper sql expressions. Or by using a trigger. Or better yet by implementing inside his application if possible adding code to strip white space. I'm not sure, whether did you notice, that I wrote about the same - and my feature suggestion was: "because it's so common operation, repeated again and again - perhaps the database server/engine could do it"? Triggers are another route, but I recommend letting the DB do its job of storage. [..] Forgive me for being contrary: you just wrote, that most of core functions listed on http://www.sqlite.org/lang_expr.html seems to be useless. They aren't related to "DB's job of storage" at all, anyway. The job of a DB isn't just storage of your data, but also to ensure that the data it stores is always conformant to the types and restrictions or business rules that you tell it applies to the data, so that the data can be trusted. It makes a lot of sense for a DBMS to be smart and to do a lot of the data-centric work itself. However, that doesn't mean that the DBMS has to have a large laundry-list of built-in functions, but more that it provides the means for users to define the types and business rules that it wants the DBMS to enforce for data, and routines for data processing tasks. So the DBMS itself can be fairly 'lite', and users get the advantage of having work done in the DBMS while writing that work themselves. I believe that SQLite already fulfills this mandate to a large degree, and if it needs more features, it is to provide better support for users to define the their own database constructs, not pre-define every possible thing that a user may want to do. We don't want something designed like PHP. For your needs, the best thing SQLite can do is provide support for you to define constraint checks that the strings it stores don't have leading/trailing spaces, and provide support for you to write stored procedure wrappers over your insert/update/etc that strip out leading/trailing spaces. If this stuff ought to be a pragma, then SQLite should support users defining their own pragmas, rather than it having this strip-strings one built-in. I will also clarify that it is useful for SQLite to have some built-in functionality beyond letting users write their own, but I don't see that auto-strip is a good candidate for that list ... not enough of a case for that being generally useful, or too many cases for why it would be a mis-feature whose use should not get the encouragement from it being built-in. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
At 3:28 AM +0100 1/7/08, Zbigniew Baniewski wrote: I think, that it sometimes could be useful as secondary protection: a feature (perhaps another "pragma"?), which will cause stripping the spaces from beginning and end of every inserted string. But perhaps even not just only as "secondary"? Yes, usually it's done at application level; I was wondering lately, why not "from the other end"? Seems to not be that difficult to implement. In fact, almost always we want to insert into database the strings with no spaces at beginning, neither at the end - so perhaps adding a possibility to set such behaviour (using "pragma") as "default" seems to be logical? What do you think? I think that this would be a horrible thing if it were the default behaviour. A database needs to by default store and retrieve data pristine , so that people get out what they put in, not something else. Leading/trailing spaces *are* significant for character strings, eg, 'foo ' is not supposed to equal 'foo'. Better for for any changes to the data in the DBMS to be explicit, like by using an explicit trim() function at the appropriate times, or implementing a trigger routine or stored procedure that handles it. Or if you really have to have the pragma, it needs to be off by default. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite and Columnar Databases
At 6:59 AM +0530 12/18/07, Yuvaraj Athur Raghuvir wrote: Thanks for the interesting discussion. What I got so far is summarized below: 1) Row based versus Column based storage is an implementation detail. 2) SQL used for access is independent of storage mechanism adopted. 3) Row based storage with indices on all columns reaches read performance of column based storage. 4) Creating/updating indices fast using new algorithms is a direction of improvement for SQLite The main way that this difference is an implementation detail is in the sense that your database schema and the DBMS API can be used unchanged with both. However, the 2 have different performance characteristics, which is why one would pick one over the other. If a DBMS is smart enough, it can automatically pick the best storage method for performance and you don't have to think about it. However, many DBMS are not that smart and so typically users find themselves making explicit changes to their schemas, specifying the storage method explicitly, in order to compensate and/or give the DBMS hints. In these typical situations, what should be an implementation detail is something that can have a lot of impact on your schema design. Now, if the storage is an implementation detail, can the following scenario be realized? a) Given: Distributed highly-available system which is implemented as maintaining replicas of data b) The replicas of data have different storage mechanisms which is also recorded in the (distributed) database coordinator. c) This would, in essence, be a hybrid database - hybrid in the sense of using different data storage strategies (row-based / column-based) in the replicas. This would allow for the database coordinator to intelligently respond to the various operations on the database by redirecting the original request to the appropriate replica. The cost would be when the data changes and each of the replicas have to be brought into sync. Here again, the intelligence should be such that the storage schema that achieves the best performance for that SQL statement should be used and the sync can happen in the back ground. My perspective is that progressively, the data storage (implementation) strategies will pay an important role given that OLTP/OLAP requirements are getting blurred. That could all be made to work, but I don't know if anyone actually has implemented this yet ... or maybe that was your intention. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite and Columnar Databases
At 7:59 AM -0500 12/13/07, Tom Briggs wrote: > Something I will say about this, for people who don't know, is that this columnar thing is strictly an implementation detail. While I think that this is an oversimplification. That's somewhat like saying that the way you use a sledge hammer is no different than how you use a claw hammer, because they're both hammers. Anyone who tries to hang a picture with a sledge hammer will be rather unhappy with your advice. Though at some level how the data is stored is indeed an implementation detail, to take full advantage of the fact that it is requires re-thinking schema design and in some cases even query design. See http://www.fulltablescan.com/index.php?/archives/44-Compression-as-Normalization...-Kinda.html for more info. If you read further into your own link, including the comments, you'll see my case also being made. I don't see your claw hammer / sledge hammer argument being applicable here. The relational model of data is all about letting users defining the logical/possible structure of their data, with a focus on the meaning of the data relative to other data. It is intended to make data manipulation and queries both easy to use and logically rigorous. The schemas that users design, including their so-called normalization traits, are just for reflecting the meaning of their data in a logical manner. This model is completely removed from how the data is physically stored, eg whether in rows first or in columns first, and the physical store is determined just by the DBMS behind the scenes, and hence is an implementation detail. The DBMS can arrange how it likes in order to satisfy the logical model in a way that performs well. The users do not contort their schemas beyond what is logically clean in order to gain performance; having a clean schema will let the DBMS infer this automatically. According to some comments, Vertica (a column-store maker) is making the same case that I am, which is just to have a logical clean schema, and performance benefits will automatically follow from that. I will also note that the highest level of schema normalization, 6th Normal Form, essentially puts every non-key column in its own table. I will also note that a column-based store essentially works like a heavily indexed row-based store, in which there is an index on every key or every column, and so all searches, which includes those on which joins are performed, can/do look in what is otherwise indexes. This is potentially slower for updates (or maybe not), but can be faster for queries, depending on circumstances. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and Columnar Databases
At 4:42 AM +0530 12/13/07, Yuvaraj Athur Raghuvir wrote: Hello, There seems to be a high interest in columnar databases recently. Is there any plan of supporting data organization as a columnar database in SQLite? What are the challenges here? Regards, Yuva Something I will say about this, for people who don't know, is that this columnar thing is strictly an implementation detail. While adopting it would be probably backwards-incompatible file formats, the programmatic API and SQL et al wouldn't have to change a bit and would remain compatible. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Announcements
At 2:47 PM + 11/22/07, Alberto Simões wrote: Is it just me, or SQLite announcements for new releases are not being sent to this list? At least I didn't see 3.5.0, 3.5.1 and 3.5.2 announcements. I am aware of them just because complain about them :) I saw the 3.5.0 announcement on this list, but no announcements for 3.5.1 nor 3.5.2, which was part of the reason I previously stated I hadn't realized that 3.5.x had come out of alpha. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
At 1:40 AM + 11/14/07, [EMAIL PROTECTED] wrote: The new look for the SQLite website is now in place, if you haven't already noticed: http://www.sqlite.org/ The feedback from this mailing list has so far been very helpful. Please don't stop offering suggestions. So I just looked at the current website right now, and the main page and other pages look very good from what I can tell. Considerable improvements from the older website. Now, a few points on terminology that may seem minor to others, but not to me: 1. It is good that your main documentation is refering to SQLite as a "SQL DBMS" or "SQL engine" rather than using the word "relational" or "RDBMS" to describe it. The SQL standard doesn't call SQL "relational" and neither do other people who know what the relational model of data actually is, so I'm happy to see that the SQLite website seems to follow technical accuracy in this regard. 2. However, a different point needs adjusting in a few places, the term "database". A "database" is a managed collection of data, and it is *not* a program or program code. The website refers to SQLite being a "database" in a few places, but that is not true. Other places correctly call it a "database engine" or "database management system" or "DBMS", and that is correct; the other references should be fixed to say "database engine" or such when referring to SQLite itself. Only use the straight word "database" to refer to the data files or data file collections or RAM stores et al that store data managed by SQLite. Here are specific places on the front page that need the update, and are probably typos: * Unlike most other SQL databases, SQLite does not have a separate server process. Actually, that's the only such typo I found on the front page (should change to "SQL database engines"); all other occurrances of "database" looked correct, also great news. But this is something to keep in mind for the future if necessary. Keep up the good work. -- Darren Duncan P.S. I just noticed now that the 3.5.x line is now considered stable, which shows how much I was paying attention to some news details before ... thought it was still alpha. Now hopefully DBD::SQLite for Perl will be updated to include it sometime soon. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
At 8:39 PM -0600 11/14/07, andy wrote: <[EMAIL PROTECTED]> writes: > > * Somebody please suggest a better tag line - > something better than "The World's Most Widely > Used SQL Database". How about: "Small, Fast, Reliable. Choose any three." I'm not sure if I heard that someplace, but I googled it and didnt find a reference... The original is more like "Good, Fast, Cheap; Pick Two", which describes the reality when a client wants to hire someone to do a custom job done for them. But good open-source software that is already made allows you to say instead: "Good, Fast, Cheap; Pick Three" Taken at face value, that could be a good line in what it implies, that you can have your cake and eat it too. SQLite is good, you can get it immediately (fast), and you can get it at no cost (cheap). That said, this phrase can apply to any good open source software that is already done, and isn't specific to SQLite. Or you could use it anyway. Or you could go with what Andy suggested. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
At 9:42 AM -0400 11/2/07, Samuel R. Neff wrote: I like the term virtual 'cause that's exactly what they are.. a table that does not really exist in the db and is provided by some other system. This is not inconsistent with other DBMS's which use terms like "virtualized view", both are tables that are not linked to underlying physical data. The fact that the mechanism which provides these tables is different does not mean they are not both validly virtual tables. I think the real problem here is "virtual" is a broad enough term that it can validly apply to a broad spectrum of situations, which can differ a lot. At the very least, "virtual" is very applicable both to a SQL view and to a module whose interface looks like a SQL table, and so to apply the term to just the latter would cause confusion. Therefore, my primary recommendation is to use any reasonable term that is *not* spelled "virtual", not because "virtual" doesn't describe what we're talking about, but because "virtual" is too broad and causes confusion. At 7:37 AM -0700 11/2/07, Trevor Talbot wrote: It's simply an interface that looks like a table. Having thought about the matter more, perhaps the table-like-module-API feature could be called "interface table" then. In some respects, "interface" is very broad too, but then so is "table", however, I see it more important to not use the term "virtual" here. At 8:08 AM -0600 11/2/07, John Stanton wrote: To my mind "virtual" means something different. "Foreign" would be a more intuitive name. That would also work for me. In fact, "foreign" is arguably better than "federated", since "federated" also means to take multiple databases and make them look like one. And that is something that SQLite arguably does natively, where each file is a database, and a collection mounted at once being a federation thereof. At 7:37 AM -0700 11/2/07, Trevor Talbot wrote: Terms like "foreign" and "federated" conjure up images of physical tables that reside in external databases, which is not at all what this feature is. Maybe or maybe not. The relational model of data is intended to abstract the physical storage mechanism away from the user, such that users just see tables/relvars which they can run queries on and update the values of, and how this is implemented behind the scenes in terms of physical data formats or locations of data is something they shouldn't need to be concerned with. So in that respect, all of normal tables (real|base relvars), which conceptually store the data themselves, and normal "views" (virtual relvars), which conceptually have their data stored in other, normal tables, these are all just tables/relvars to the user. I now bring up a broader matter for consideration ... If SQLite's table-like-module feature is meant to be like a normal table in all ways but for its implementation, I would suggest considering looking at this whole issue in a different way, that SQLite supports multiple "engines". When you define a database in SQLite, you also indicate what "engine" you want to implement it with. SQLite has a built-in default engine, what normally gets used, or perhaps the file vs memory could be considered 2 engines. The third-party modules such as we were discussing would each then provide additional engines to choose from beyond SQLite's standard built-ins. For backwards compatibility or simplicity, if schema designers don't explicitly indicate an engine to use, they get SQLite's built-in by default. Now while I'm not going to argue any merits of MySQL as a DBMS, it can still serve as an example of the concept, with its multiple engine types, eg InnoDB vs Falcon or what have you. Other DBMS may have similar concepts. That said, and particularly since SQLite is already supports multiple databases under a common transaction, I strongly recommend that the pick of engine is applied at the level of a whole database, not at the individual table level. Note that I consider a database to be the widest range of multi-table database constraints, such as foreign keys, because all of its parts are always defined and live together. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
At 9:03 AM -0400 11/1/07, D. Richard Hipp wrote: The current virtual-table implementation does not work when you have shared cache mode enabled. We would like to fix this so that that you can (for example) use FTS and shared cache at the same time. But to do so seems likely to require an incompatible change to the virtual-table interface. The virtual-table interface is currently listed as "experimental" and does not appear in the "official" documentation in capi3ref.html. So we are free to change it if we need to. But I wonder how many people this would inconvenience. If you have or know of a virtual table implementation (other than the ones that are included with SQLite - such as FTS1-3) that will be broken by an API change, please let me know. And please also advice me how much of a hardship a change would be for you. While you're at it, I strongly recommend changing the feature name from "virtual table" to "federated table", or at least not something called "virtual", because the older/current name is a source of confusion. In general relational database contexts, the term "virtual table" usually means the same thing as "view" means, which is something that looks like a "real/base table" but is actually a mapping to one or more real/base tables as typically defined by a relational query, and this is done internally by the main DBMS that does the real/base tables. (In fact, a proper viewed/virtual table implementation would be such that users of the database don't have to know which tables are base or viewed, and if a DBA wanted to change the schema later to make viewed tables real and real tables viewed, it would not affect the users. Views that don't reflect enough mapping data notwithstanding.) Your term "virtual table" would be better called "federated table" (or "foreign table" or "remote table"), which is the more standard term with relational databases for tables that are interfaces to other DBMS systems with remote storage. If you're making an incompatible change anyway, then this rename would be good for hand-in-hand with it, to help people migrating apps from the old to the new; best to do all the incompatible changes at once. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Enumerating rows in a view
At 1:08 AM +1000 8/26/07, T wrote: When I create a view, is there any way to enumerate the output rows? Another method would I've developed/discovered is to create a temporary table, fill it with the data from the view, then use the automatically created rowid column as the enumeration. This works, but is not ideal since the creation of a temporary table can't be included in a view itself. If that works, then try using a subquery in the view definition instead. create view Enumerated as select rowid as Sequence, Name from ( select Name from Planets order by Name ) Sort of like that. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sparse matrix
At 1:54 PM +1000 8/23/07, T wrote: Hi Darren, It seems to me that you have a flawed design. Displaying sparse like that should be a function of your application display code, not the database I had to chuckle that when I asked "How do I use this to do that", your solution was "you shouldn't have that and you should do it with something other than this" ;-) Not really helpful, but good for a chuckle. Fortunately Simon's replies helped me out. Hopefully my followup posting gives a better example. Tom, my answer was quite appropriate, considering your example, and I stand by it. It is very common that people asking for help are demonstrating that they are heading in a bad direction and want help in dealing with the result of that; I think it is more helpful to assist with the bigger picture than to necessarily support a bad direction. Or put another way, often when people want to solve problem A, then rather than asking for help in solving problem A, perhaps with some examples of what they tried, they assume that a certain way to go is best, and only ask for help in going that way, that is problem B. At 1:53 PM +1000 8/23/07, T wrote: But with counts of repetitions, like this: Day Room Subject TeacherPeriod MondayA1 1 English 1 Ng 1 1 A2 2 Maths 1 Peters 2 2 Computing 1 3 H1 1 Sport 1 Kent 1 4 Tuesday A2 1 Maths 1 Peters 1 1 A1 3 History 1 Ng 3 2 English 1 3 History 1 4 Wednesday A2 1 Maths 1 Peters 1 1 H1 1 Sport 1 Kent 1 2 S1 2 Science 2 Who1 3 Smith 1 4 The "1" counts could even appear as null/blank, that would be fine. Now your newer example does demonstrate a more reasonable problem to solve. By the looks of it you want to display results in a prettier or easier to read grid by having blanks rather than repeated field values; perhaps you are rendering an HTML table, and the numerical count you want is to provide a "rowspan" attribute value. If the DBMS can do this, then your HTML generator would be extremely simple. Or you aren't making HTML, but have similar issues. That said, if what you want is even possible with SQL, then it would probably be rather verbose SQL, involving multiple joins and group-bys and order-bys etc, and I suspect that your total code size would be smaller if you used appropriate logic in your application to blank the result fields rather than SQL. So I still recommend you do what you want in your presentation layer rather than in the data layer. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sparse matrix
It seems to me that you have a flawed design. You should just have a 2 column database to begin with, with a table like this: Code Count a 4 b 2 c 3 Rather than plain inserting or deleting rows, just sometimes insert or delete, you should instead increment or decrement counts, adding a row if its the first instance of the code, and removing the row if it would decrement to zero. Displaying sparse like that should be a function of your application display code, not the database, and it can easily do that using a simple for-loop or something for each fetched row, iterating on the count. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Implementation of ANSI SQL-92 FOREIGN KEY and referential integrity
At 2:11 PM -0700 8/2/07, Mikey C wrote: Does anyone know if there is a plan to implement the enforcement of the SQL-92 FOREIGN KEY constraints? Seems to me the No.1 missing feature. After all, data integrity, even in an embedded DB is very important and bugs in client code can easily mess up the referential integrity. I believe that this is planned; however, some other planned upates have higher priority and are being done first. Note also that traditional foreign key constraints can only implement some kinds of business rules, and there are lots of others whose violation could cause problems, that foreign keys wouldn't help with; in theory, the more generic format of triggers is a more complete solution, or more specifically, free-form check constraints that can be comprised of any query are a more complete solution. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite on Mac
At 11:45 AM -0400 7/19/07, Ahmed Sulaiman wrote: Hi all, Does SQLite work on Mac, and if yes, is there any Mac enabled version that I could download? Cheers SQLite just works on Mac OS X. If you have the Mac OS X Developer Tools intalled, you can just compile the normal SQLite source distro and it will work. Otherwise, if you have Mac OS X 10.4 Tiger or later, a version of SQLite is also built-in as part of "Core Data". -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Table with 2 primary keys... what's the problem?
At 11:37 PM +0100 5/12/07, Paulo J. Matos wrote: Moreover, active in table product is a boolean but I'm using an int since I don't know if there's a boolean type, is there? SQLite has just these types: Int, Real, Text, Blob, Null. SQLite does not have a boolean data type, though I think it really should; the boolean type is fundamental to the relational model of data. For example, what is the data type of the expression in a WHERE clause if not a boolean? sqlite> CREATE TABLE productinfo (productasin INTEGER PRIMARY KEY, infoid INTEGER PRIMARY KEY); SQL error: table "productinfo" has more than one primary key Why is this? How can I solve the problem? If what you intended here was to have a single primary key which comprises both the asin and id columns (most likely), such that only the combination of the 2 must be unique, then you have to specify it using different syntax, such as this: CREATE TABLE productinfo ( productasin INTEGER, infoid INTEGER, PRIMARY KEY (productasin, infoid) ) If you intended that each column is individually unique (unlikely), then each one is simply a "key", which in SQLite syntax involves saying one or both are "UNIQUE" rather than "PRIMARY KEY". By definition, there can only be one "primary". -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with SQL syntax. Ticket #2296
At 7:22 PM + 4/12/07, [EMAIL PROTECTED] wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know this in order to fix ticket #2296. It might not be obvious why this is important to ticket #2296 but it is, so I would appreciate any help. Alternately, attempting to run that could just be made to fail with an error citing ambiguity. Then users can make it work by making sure the columns they are unioning have the same names (and hence, so does the result of the union), such as by using 'AS' in the select-list. Unless you are explicitly trying to accept ambiguous syntax just because other DBMSs do (to aid portability), that is the simplest option. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
ll database constraints in my RDBMS are immediate, and are applied between statement boundaries at all levels, so no statement will ever see a version of the database that is inconsistent / violates any constraints. And so then what, you may ask do we do if we have a constraint saying one table must be credited while another debited, and the constraint shouldn't apply between the first and second step; well to that I say, is what single multi-update statements are for; the 2 updates are conceptually happening "at the same time". My RDBMS is ACID in the strictest sense. It uses implicit transactions everywhere. Every operator/routine is implicitly atomic and hence a transaction. Every statement at any level of the call stack is atomic. Any explicit transaction within most types of routines takes the form of a try-style code block. So generally, the number of transaction layers is equal to the depth of the call stack. There are no standalone "start/commit/rollback" statements except in the parent-most anonymous routine in the callstack that the application directly invokes; all "stored" routines use the block or implicit form only, so we ensure no dangling transactions. Any statement failure, which can be due to the statement violating a database constraint, will throw an exception, which will rewind the routine call stack (and transaction stack) one at a time until some routine or block catches it. If a statement/routine completes/exits normally, its implicit transaction commits; it implicitly rolls back if an exception causes it to exit early. When an exception is caught, the transaction layers of the catching statement and its parents have not rolled back and can still be committed, and new child transactions can still be started, such as a "try again differently" on the failure. Suffice it to say that it will be a lot easier for me if the implementation of each operator and routine et al in my language against SQLite can simply issue "start/commit/rollback" transaction at its start/end as is appropriate, which would happen nicely if SQLite has native child transaction support. If SQLite doesn't support child transactions, I would have to add the complexity of remembering everything that was done by the parent-most routine and keeping track of level counts and what-have you, which is a real pain. Oh, and in case you say that I'm already managing it myself with Example, then I would say you are right, however in that case, Example's analogy to SQLite's pager layer is entirely built-in to it, and so I am implementing the feature right where it should be, there, and Example only has to track what pages changed, not a list of executed statements, which is a lot simpler. So what I'm proposing for SQLite is no less than what I would expect to do myself. How much more complicated is the nested transaction solution if *you* have to implement it? If you mean, in my own SQLite-using program, hopefully this has now been explained between my various posts. If you mean, my implementing it in SQLite itself, that is highly impractical since I'm very poor at C and the regular maintainers of SQLite would be able to do the job many orders of magnitude faster than I could. My contribution to the development of SQLite is mainly on the side of design suggestions (which are generally programming language agnostic) and what things are helpful from the users' perspective. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
At 3:33 PM -0600 4/11/07, Dennis Cote wrote: You have lost me here. If this transaction is considered successful without executing the middle step (which is the same as executing it and then undoing that execution) then that step does not belong in this transaction. Instead of begin step 1 savepoint after_1 optional step 2 if error rollback to savepoint after_1 step 3 commit You can do begin step 1 step 3 commit begin optional step 2 if error rollback else commit Your example assumes no coupling between the different steps, which is true some times, but in the general case there can be coupling. That is, the effect that step 3 actually has can be different depending on whether step 2 is rolled back or not, either because step 3 is operating on a different database state, or because step 3 contains conditionals that cause different statements to execute depending on database state that could have been changed by step 2. So in the general case, step 2 must always be run after step 1 and before step 3. This *is* an example of an application where a nested transaction or a savepoint could be useful. However there is a fairly simple workaround that gets the same result without a nested transaction. Instead of: begin step 1 begin nested temp step 2 var = query current state rollback nested step 3 using var commit You could do: begin step 1 temp step 2 var = query current state rollback begin step 1 step 3 using var commit The cost of this approach is repeating the work done in step 1. While that workaround may be an acceptable solution for some situations, I see that as overly complicated and difficult in the general case. For one thing, it requires the application to keep track of all the details of what step 1 was, and step 1 could be arbitrarily complex. Moreover, step 1 could have been expensive, involving a large amount of data which may have been input from somewhere and can't be retrieved again nor stored in RAM; the only copy of it is in the database. Or even ignoring the last point there is still the complexity, especially if one used bind variables that were since freed up for other tasks, since you aren't just keeping a log of SQL strings to re-run. I think that a SQLite pager-based mechanism for tracking child transactions is quite a bit less complicated and more reliable than using your workaround, since no details have to be remembered but for the pages that changed. Now going off on a tangent ... To address the oft-raised comment that some people make that any proposed additions or changes to SQLite be opposed in general on the principal that "it is supposed to be lite", I say this: It is perfectly in keeping with SQLite's philosophy for us to add lots of power to it if the cost of adding that power is low. Low cost meaning that the codebase doesn't need to increase much if any, the resource efficiency of running SQLite isn't impacted much, complexity doesn't raise the bug density appreciably, and particularly, it isn't more difficult for users to use. Some features, like proper child transactions as I described, are a situation where users gain a lot of power at very little cost. Having proper child transactions means it is a lot easier for users and developers, particularly SQLite extension or wrapper writers such as myself, to add powerful features to SQLite using programs while SQLite itself is hardly more complex. Users are saved a lot of work, and SQLite developers gain next to none. By contrast, say, supporting named users in the database and concurrent database writes and stuff like that is indeed way too complicated for SQLite to have, and I still support SQLite never adding support for it. So SQLite with child transactions is only trivially less lite than it is now, which is still lite. In fact, I propose moving rollbackable child transaction support to the top of the todo list, rather than it being in the middle, given that its presence can make a lot of other todo or wishlist items much easier to implement, I believe. And if it will make a difference, I will even make a monetary donation (as I can afford to) in order to sponsor its development (though I would like to think that the benefits are compelling on their own). -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
At 12:49 PM -0600 4/11/07, Dennis Cote wrote:
[EMAIL PROTECTED] wrote:
It appears that my requirements are to be able to do the following:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child; // child aborts
insert into t values ('d');
COMMIT parent;
As a result of this sequence, the table should have two new rows
with values 'a' and 'd', but not 'b' and 'c'.
Can you explain why your application is rolling back the child transaction?
If the above is really how your application works (and I don't think
it is), then the exact same result can always be achieved with the
simpler sequence:
BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;
You don't need to bother inserting b and c if you are going to undo
those insertions with a static rollback.
While it is true in some cases that an application can be written to
know in advance whether certain SQL statements need to be run or not,
there are other cases where it can only easily know after having
tried it.
One type of situation that stands out the most to me is if you have
state constraints defined (in some way) on the database for which it
is only really feasible to calculate the constraint definition after
DML has occurred, because you want SQLite to do the calculation
itself on the post-DML-state and it is technically simpler that way;
if the constraint fails, we would want to be able to just rollback
the DML that caused the state to break, but not the other valid stuff
before that, since we could have more stuff after this attempt that
needs to be atomic with stuff before the attempt.
Well, the key thing in that example, and many situations, is that the
child transaction is doing something that we may or may not want to
rollback and we won't know until after it is tried.
This said, I can also see situations where there is an unconditional
child rollback, and that is where we want SQLite itself to perform a
querying or calculating task using a temporarily changed database
state as input. We want the result of the query, but not the changed
state that went into its input. But there is other changed state
before that which does need to go into the input, and it needs to
persist, and be atomic with other state changes done after the
calculation.
Now, you could say make the application do that work, but the fact is
that certain kinds of data processing are more efficiently done by a
DBMS itself.
All this said, I look forward to rhurst's specific problem being
explained, so it is easier for us to see whether child transaction
support would help *that* problem.
-- Darren Duncan
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQLite and nested transactions
At 6:45 PM -0700 4/10/07, Darren Duncan wrote: If one wants to still deny that rolling back a child without rolling back a parent has no practical use, then we might as well not have built-in SQL statements that are atomic, because that is exactly the same end result for users. If someone is confused by the typo I just made here, it was meant to say: If one wants to still deny that rolling back a child without rolling back a parent has [any] practical use, then we might as well not have built-in SQL statements that are atomic, because that is exactly the same end result for users. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and nested transactions
At 6:05 PM -0600 4/10/07, Dennis Cote wrote: Darren Duncan wrote: I think that the question of what would be a reasonable minimum to do now depends on what SQLite's current behaviour is when an individual SQL statement fails that is part of a larger transaction. Of course not. A reasonable minimum is to do nothing and leave SQLite as is. :-) I wasn't speaking in absolutes there, only in the context that if child transactions were implemented, and there were 2 ways to do it, then which way might have the least impact would be the way that most closely resembles SQLite's current behaviour. An SQL statement is atomic. It either completes or does nothing. If it fails it returns an error to the application. It is up to the application to decide if an error warrants rolling back a transaction. The database never does that on its own. The application must execute a ROLLBACK statement. If a transaction is rolled back, it is done in an atomic fashion. The database is left in the same state it was before the transaction started. If you don't do this you have eliminated the atomicity property of the transaction. Good, then this is consistent with what I think should happen. A transaction is just another name for a unit of work that is atomic, and each individual SQL statement *is* a transaction. Therefore, SQLite right now already supports nested transactions to a limited degree, either 1 level (a SQL statement by itself) or 2 levels (a SQL statement within an explicit larger transaction). I propose it extend this to N levels. If you have 2 layers of explicit transactions, and the child transaction rolls back, and the parent one retains its changes prior to when the child started, then this behaviour is identical to a partially completed SQL statement rolling back on failure to the beginning of that statement, and the results of prior executed statements remaining. If you have a real example of an database application that needs to commit a part of a transaction, I'm fairly sure the application can split that optional part into a separate transaction. I mean, if that action can fail and the rest of the transaction can still be said to be complete, then there is no need to perform that action at that time along with the other actions in the transaction. It can be done later, and either succeed or fail on its own, because obviously it didn't matter if it succeeded or failed in the first place. The explanation to this is simple, really. Proper child transaction support, where rollback of a child does not rollback the parent, is needed to deal with the reality that SQL statements are recursive, and any given SQL statement is itself defined in terms of other SQL statements, to arbitrary levels, and a SQL statement needs to be atomic no matter what level of abstraction you are at. This is best illustrated with a stored procedure. A stored procedure is a routine that contains one or more SQL statements. Moreover, the invocation of said stored procedure is also a SQL statement. Therefore, both the individual SQL statements in the procedure plus the entire invocation of the stored procedure each need to be atomic, and hence a transaction. If only the SQL statements inside the procedure were atomic, and the call to the procedure wasn't, then we can end up with a situation where, if the procedure fails and aborts part way through completion, then from the point of view of the users, the single SQL statement that they wrote (which invoked the procedure) did not complete, but did not leave the database untouched either, and hence left an inconsistent state. This violates ACID. Or another example, say you have a trigger (an implicitly invoked procedure) defined to happen when a table is mutated (eg,ins/upd/del). From a user's point of view, everything that trigger does is part of the SQL statement that they invoked to mutate the table, and if the whole trigger isn't made atomic, then the user's ordinary SQL statement was not atomic from their POV. Or if you want to ignore stored procedures, consider a SQL INSERT statement that inserts 1 row vs one that inserts multiple rows (some DBMSs support the latter). Say you want to insert multiple rows as an atomic unit, and you want to invoke a multi-row INSERT statement, but the DBMS doesn't provide one, so you have to invoke single-row INSERT statements instead. From your point of view, all the inserts combined are conceptually a single statement, that should be atomic in the same way that normal SQL statements are atomic. A main advantage of child transaction support is that people can implement their own "SQL statements" and have them behave like system-defined ones, including the promise of atomicity. This is a very real need, that should be supported. If one wants to still deny that rolling back a child without rolling back a parent has no practical use, then we mig
RE: [sqlite] SQLite and nested transactions
At 9:20 AM -0400 4/10/07, Samuel R. Neff wrote: Under what circumstances would an older version of SQLite be used to rollback a newer journal? Situations I am thinking of include wanting to use multiple application programs with the same database, and each one includes a different version of SQLite due to one being newer than other or some such. Or a user upgrades an application, which as a newer SQLite, then finds the program has problems and they revert back to the older version while waiting for a fix. Think of the SQLite database file like an ordinary user application document; users don't usually expect that editing a document with a newer version of a program will stop them from later editing it with an older one. Sure that happens, but it shouldn't happen any more than necessary. Well, I am mainly thinking about the concurrent use of the database with 2 different applications is the more likely scenario where the backwards compatability would help. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and nested transactions
At 11:46 AM -0600 4/10/07, Dennis Cote wrote: Samuel R. Neff wrote: I personally would see value in supporting quasi-nested transactions where they are nested in name only--increment decrement a counter and commit on last commit, rollback entire transaction on first rollback. This would have the advantage that the library would support issuing multiple BEGIN TRANSACTION statements without error. I often find in my code that I have library routines that want to run in a transaction and can run in their own transaction or join an existing transaction. In SQLite I would need to have extra logic in place to detect the transaction state and only run in a transaction if one is not already in place. I say "would" because the SQLite.NET [1] wrapper provides nested transactions as a counter already, on top of the SQLite library (as long as I use the connection.BeginTransaction() method and don't issue an explicit BEGIN TRANSACTION call myself). I do the same thing in my wrapper for the same reason. I think this is by far the most common case where users see a need for nested transactions. It decouples subroutines from their calling context. This form of nested transactions is exactly what several other database engines provide. It is simple to implement and does what the vast majority of users need. I'm sure there are circumstances that can tolerate only some of the statements in a transaction being executed, but for most applications a transaction is *all or nothing*. They don't want to execute the first and last statement but skip the middle two. It seems to me any application that can tolerate some statements in a transaction not executing could factor those statements out into a separate transaction. I think that the question of what would be a reasonable minimum to do now depends on what SQLite's current behaviour is when an individual SQL statement fails that is part of a larger transaction. Remember that each SQL statement is conceptually an implicit child transaction of its own. If a failing SQL statement causes the entire transaction to implicitly rollback, then what you describe about any rollback causing the whole thing to rollback would be consistent. If a failing SQL statement just results in only that statement not leaving a trace and other uncommitted statements in a transaction are still applied subject to be committed, then an explicit rollback must not affect anything but the immediate child-most transaction, to be consistent. Regardless, I like to think of SQL statement failures like exceptions, and each transaction level can optionally act like a try-catch-block; a failed statement throws an exception, and each transaction in turn is rolled back up to said try-catch block, and anything done prior to the block being entered is not rolled back automatically. Now of course, users would then have to explicitly mark places, perhaps best as an extension to the "begin transaction" statement that indicates whether a failure within that transaction will auto-effect any of its parents; its like the "begin" doubles as a try-block. Or that may not be the best syntax, so alternatives could be tried. I will also say for the record that partial rollbacks are useful. Having all layers rolling back unconditionally is like having a web browser program that exits if it has trouble loading a web page, rather than just saying sorry and moving on. Sometimes it is reasonable to only rollback the "load this page" transaction rather than the "main()" transaction. Users may want to try again, or try going to a different page. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite and nested transactions
At 12:33 PM -0400 4/9/07, Igor Tandetnik wrote:
Dennis Cote <[EMAIL PROTECTED]> wrote:
Darren Duncan wrote:
I will clarify that child transactions are just an elegant way of
partitioning a larger task, and that parent transactions always
overrule children; even if a child transaction commits successfully,
a rollback of its parent means there are no lasting changes.
Because of this, and the fact that a transaction is basically a
guarantee that all or none of the enclosed statements are executed, it
is much simpler to implement nested transactions using a counter and
the existing transaction API in a set of wrapper functions. There is no
need to maintain all the intermediate state information.
Support for nested transactions should allow one to roll back the
child transaction to the point where it has started, but still
commit the overall transaction. Consider:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
ROLLBACK child;
insert into t values ('c');
COMMIT parent;
As a result of this sequence, the table should have two new rows
with values 'a' and 'c', but not 'b'. As far as I can tell, this is
impossible to implement in terms of existing SQLite API.
Yes, exactly.
The whole point of nested transactions is that if a child transaction
fails, the state still retains all changes made prior to the child
starting, and after its parent started. It is a lot less
complicated, and particularly more reliable, for the DBMS itself to
manage this.
Moreover, in the case of stored procedures like triggers where
triggers have their own child transactions, the application doesn't
even see control between different transaction levels.
The whole point of being implemented as separate transactions rather
than "save points" is that any particular block of code or SQL that
needs to be atomic doesn't have to special case how it is defined
depending on whether it is a main or child transaction, and it
doesn't matter how many parents it has ... just like a function
invocation in a normal programming language doesn't have to know how
high the call stack is.
Note that, if SQLite currently supports the ability to have an
individual SQL statement fail and leave no lasting effects, without
any non-nested transaction rolling back, then it is already partially
supporting the nested transaction feature, such that there are up to
2 transaction levels, any explicit one, plus the implicit one that is
SQL statement itself. So we just have to extend this to N levels.
Now, as far as user syntax goes ... considering my 3rd paragraph
above, it is vital that the syntax for start/commit/rollback a child
transaction is identical to invoking an initial/non-nested
transaction ... so that users don't have to know whether they are
nested or not.
-- Darren Duncan
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQLite and nested transactions
At 4:38 PM -0700 4/5/07, Darren Duncan wrote: To get this to work would basically involve having additional journal files, with the original one being for the parent-most transaction, and with an additional one for each transaction level, or some such arrangement; the extra ones could have file names like the original but numeric suffixes indicating the transaction level. Note that to maintain backwards compatability, the original journal file will still need all pre-change pages written to it too, but intermediate-level files don't necessarily need this, or it can be done, as the implementer wishes. Actually, I will clarify that any "additional" journal files do not need to be on disk ... said pages could just be in RAM, unless there are too many of them and they need to spill to disk ... only the parent-most transaction actually needs a journal file on disk, under the same circumstances that the current journal needs to be on disk ... before writes to the main SQLite db file are being made. My more general point is that to support child transactions, the pager layer would need to be updated to represent N ordered layers of state for changed pages, where N is the number of nested transactions (an ordinary SQL statement counting as 1), rather than just representing 2 layers, current and original. I believe that this can be done fairly easily, and should be done, assuming the pager is already well designed and doesn't make certain assumptions. Moreover, I don't believe that the addition of this feature should make the SQLite code base much larger, and it shouldn't cause much of a performance hit, if any at all. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and nested transactions
At 3:43 PM -0700 4/5/07, <[EMAIL PROTECTED]> wrote: I have just read the omitted features section and noticed that SQLite doesn't support nested transactions. Which is *very* unfortunate indeed. It would be *so* much easier for users to get atomicity right at multiple levels if nested transactions were supported. Eg, individual SQL statements, plus all the statements in a trigger, plus arbitrary parent routines, are each atomic and succeed or fail as a unit. In general, the feature would enable lots of other features with little additional cost for those. I will clarify that child transactions are just an elegant way of partitioning a larger task, and that parent transactions always overrule children; even if a child transaction commits successfully, a rollback of its parent means there are no lasting changes. Savepoints are conceptually related, but a lot less elegant, and should be avoided. What effort is required to add this feature? I would expect that 99% of the work would be in the pager layer, where transactions are currently implemented, afaik. Moreover, it should be fully possible for this to be done in a backwards-compatible way, such that no file format changes are necessary, and older versions of SQLite 3 could use the same database files as the newer ones without trouble. To get this to work would basically involve having additional journal files, with the original one being for the parent-most transaction, and with an additional one for each transaction level, or some such arrangement; the extra ones could have file names like the original but numeric suffixes indicating the transaction level. Note that each individual SQL statement is itself an implicit child transaction of whatever level we're otherwise in (or it would be the parent-most transaction if no other parents exist, which is the default situation), assuming SQL statements are atomic, and likewise, so would all the trigger invocations resulting from a SQL statement would be collectively implicitly atomic. Starting a child transaction would add a journal file, with the state of any child-changed pages prior to transaction start being written there, and if the child aborts, then that file is rolled back from or committed to the next parent level's file as appropriate. Note that to maintain backwards compatability, the original journal file will still need all pre-change pages written to it too, but intermediate-level files don't necessarily need this, or it can be done, as the implementer wishes. What would be the next best DB that does support this feature? Ray I don't know. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UTF-8 or ISO-8859-1
At 9:42 PM +0200 3/25/07, Ralph Müller wrote: to store (german) Text in a SQLite Database, is it better to use UTF-8 or is ISO-8859-1 more recommendable? It is recommended to use UTF-8 no matter what language text is in the database, since it is fully adaptable to any situation. Also, with SQlite 3, you don't get the second choice. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] attach in transaction
At 3:42 PM + 1/8/07, [EMAIL PROTECTED] wrote: [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? I think that it would be okay, though not mandatory, for an ATTACH to occur within the context of a parent-most transaction; similarly, a DETACH could happen within a parent-most transaction *if it was not updated in any way*. Generally speaking, all SQLite data files that are attached while a transaction is active are a collective entity for which the transaction succeeds (commit) or fails (rollback) entirely. It does not make sense to DETACH a file that was modified during the currently active transaction, because it would no longer be possible to fulfil the mandate of a transaction. A DETACH during a transaction should simply return an error if it was changed; the transaction must first be committed or rolled back before such a DETACH is allowed. Or more generally, we could disallow all ATTACH and DETACH during a transaction period, but the more specific case I mentioned before is all that is really needed. Note that I am assuming that things like foreign key constraints (when implemented) can not be defined over entities in different data files, since they couldn't be realized when both aren't mounted together. Note that I used the term "parent-most" above under the situation where parent+child transaction support is available ... which is trivially true anyway if individual SQL statements are atomic. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Transpose table
Can you please provide a use case for your example, so we know what you're trying to accomplish? That should help us to help you better. -- Darren Duncan At 12:08 AM + 12/14/06, RB Smissaert wrote: I am moving my code away from VBA and transferring it to SQL. There is one particular routine where I haven't found a good replacement for and that is to transpose a table from a vertical layout to a horizontal one, - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query generation
At 2:15 PM +0530 12/7/06, Lloyd wrote: Hi, I am generating queries dynamically from my program based on the input. The primary part of the query is always select a from mytable where (b=0) and based on the user input I add more where clauses to my query like select a from mytable where (b=0) and (x=10) but in one case there in no where clause to add, so my final query become select a from mytable where (b=0) and and this is a syntax error. Is there any option to add a null clause after the 'and' ? Thanks, Lloyd A better solution is to use leading 'and' rather than trailing 'and', so you should start off with: select a from mytable where (b=0) ... which is already valid syntax, and only add a bunch of "and " when you actually have the "". -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT on empty fields ??
At 5:36 PM -0600 11/27/06, Jay Sprenkle wrote: On 11/27/06, Isaac Raway <[EMAIL PROTECTED]> wrote: I'd like to strongly second this. Avoid NULL columns, even at apparent cost. Having a valid default value is always better. If a design appears to require NULL values, then the design is likely critically flawed. Using NULLS is NOT a critical design flaw. NULL means something specific and if you use it correctly it works perfectly. NULL indicates when nothing has been entered into a field. Not entering anything, and entering spaces or a default value, are different. If you need that information then it's very useful. If you don't then don't use it by assigning default values. Perhaps an intention behind the existence of NULLs was a useful idea, but in practice, they are a big mess in SQL. The NULL is used in SQL for a multiplicity of unrelated meanings, some of which are: value is unknown, no value is applicable here, value is at its default / has yet to be assigned to, value can not be determined, result of that operation is invalid. In fact, I read somewhere that there are a good 12 distint meanings attached to NULLs, so we don't have 3-valued-logic, its 14-valued-logic. But regardless, if you are given a NULL, how do you know what it means? Moreover, SQL is inconsistent with itself in its treatment of NULLs, in some situations treating 2 nulls as being distinct, and in other situations treating them as non-distinct. So NULLs can be helpful to you if you are very careful, but often they are more trouble than they are worth, and wherever possible, one should use some other way to express the meaning of what they were using NULLs for. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT on empty fields ??
You can save your self a lot of grief by declaring all of your fields to be NOT NULL and default the text fields to the empty string, ''. Use '' rather than NULL when you don't have a name. Then you can simply say "where foo=''". -- Darren Duncan At 10:52 PM +0100 11/26/06, Daniel Schumann wrote: Hello, i got a table 't' with two fields for example : Lastname | Name - Duck | Donald | Peter with : SELECT * FROM t WHERE (Lastname='Duck') AND (Name='Donald') everything is all right with : SELECT * FROM t WHERE (Lastname='') AND (Name='Peter') or SELECT * FROM t WHERE (Lastname=NULL) AND (Name='Peter') nothing is selected what do i wrong ? thx - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] invite to OSCAMP SQLite discussion in E146
Richard Hipp, and anyone else now at OSCON, Sorry for the late notice, but starting at 1:30pm today and possibly through to 5pm (to 3:15 at least) is a discussion on SQLite in E146, the OSCAMP room. You are invited to attend. While I didn't create the session, I will be participating. Hoping you get this in time. We will be around probably all afternoon if you come later. Or email a reply if you can suggest an alternate meeting time or when you might like to come. Good day. -- Darren Duncan
Re: [sqlite] Compressing the DBs?
At 6:04 PM -0300 7/5/06, Gussimulator wrote: Now, since theres a lot of repetitive data, I thought that compressing the database would be a good idea, since, we all know.. One of the first principles of data compression is getting rid of repetitive data, so... I was wondering if this is possible with SQLite or it would be quite a pain to implement a compression scheme by myself?.. I have worked with many compression libraries before so that wouldnt be an issue, the issue however, would be to implement any of the libraries into SQLite... First things first, what do you mean by "repetitive"? Do you mean that there are many copies of the same data? Perhaps a better approach is to normalize the database and just store single copies of things. If you have tables with duplicate rows, then add a 'quantity' column and reduce to one copy of the actual data. If some columns are unique and some are repeated, perhaps try splitting the tables into more tables that are related. This, really, is what you should be doing first, and may very well be the only step you need. If you can't do that, then please explain in what way the data is repetitive? -- Darren Duncan
[sqlite] unsubscribing from sqlite
At 5:53 PM + 6/27/06, prabhu kumaravelu wrote: i want to unsubscribe. I have been email from sqlite and fill up my inbox please unsubcribe this The instructions to get yourself off are in the header of every email: List-Post: <mailto:sqlite-users@sqlite.org> List-Help: <mailto:[EMAIL PROTECTED]> List-Unsubscribe: <mailto:[EMAIL PROTECTED]> List-Subscribe: <mailto:[EMAIL PROTECTED]> -- Darren Duncan
Re: [sqlite] Recovering when the SQLite database is corrupt
At 6:23 AM +0200 6/14/06, Olaf Beckman Lapré wrote: Hi, I'm using SQLite as the storage engine in my e-mail client but I'm worried that a user crashes or kills the client during normal operation resulting in a corrupted SQLite database. If the file is corrupted I'm afraid the user's entire e-mail database may be lost. Are there any standard procedures to recover from a corrupted database? Thanks, O. A user simply killing off the email client process, or shutting off the power to your machine, should not corrupt the database, since the database is ACID compliant; it will correct itself using its journal file when your client next is run. That said, if they manually edit the database file with some other program, or remove its journal file, then that could cause problems, as that goes outside the SQLite code and its ability to enforce ACID. -- Darren Duncan
Re: [sqlite] DBD Sqlite
At 15:03 -0700 4/4/06, Sripathi Raj wrote: Hi, I have a few questions regarding SQLite. I'm using it on Windows and connecting to it from Perl. And I will answer some of them. 1. How do I find out if the current version of DBD::SQLite uses SQLite 3.0or greater? DBD::SQLite versions >= 1.0 embed SQLite versions >= 3.x. DBD::SQLite versions < 1.0 and all DBD::SQLite2 embed SQLite versions <= 2.x. 2. How do I allow dirty reads? I understand that the whole file is locked for writing but I believe I can do reads using a deferred transaction. How do I do this from the Perl DBI? I'm not sure that what you want is possible. But why would you want to? 3. The performance for inserts is really bad. Around 40k entries takes a few hours. What might I be doing wrong? I do a commit after all the inserts. A few things to help with speed: 1. Use DBI's prepared statements; eg, 1 prepare() and many execute(). 2. Don't commit for each row inserted but batch them so, say, you commit once per 1000 rows. Thanks, Raj -- Darren Duncan
Re: [sqlite] all
At 08:40 +0800 16/3/06, Jiao wrote: I want to be off this mail list, how to do it ? Read the header of the email you sent to the list, as well as every other list message; it says there "List-Unsubscribe" plain as day. -- Darren Duncan
Re: [sqlite] updating SQLite to implement The Third Manifesto
At 7:05 PM -0500 3/12/06, [EMAIL PROTECTED] wrote: Let me amplify this by pointing out that I have zero interest in taking SQLite in the direction of The Third Manifesto. Those who want to do so are welcomed - encouraged even - to fork the tree and go off in their own direction. Just do not expect me to follow along, please. Richard, let me ask you this, though, to clarify your position. Would you be willing to build a distinctly new, and different-named product, reusing what you can of the SQLite code base (that you know so well) in the process, mainly the virtual machine and b-tree etc, which implements a set of specifications I provide, if you are paid your standard custom work rates for the time spent? I just wanted to know whether the whole idea I was raising was distasteful to you period, or whether you simply didn't want to make SQLite itself work that way. I should also clarify my own position, that I *have* started my own project to implement the ideas I had. It isn't on Sourceforge, but it is hosted on other public repositories and is released via CPAN. The reason I am raising these issues in the SQLite community is that I like SQLite and I think it provides a good and mature point of departure for implementing a fast and efficient performing version of what I am otherwise making on my own. But moreover, that I was preferring to pay someone else who was a lot stronger in C than I am to do this work, rather than doing it myself. All this said, I think I will shelve this discussion for now. Then, when I actually have a working implementation of my proposal, I will draw your attention to it later to see if anyone is interested in seeing how things actually work out in practice; it may not be the same as you preconceive. -- Darren Duncan
