Re: [sqlite] updating SQLite to implement The Third Manifesto
With every normal
data type, if "foo := 1; bar := foo;" then a subsequent comparison of
"foo = bar" would return true. But with nulls, if you say "foo :=
NULL; bar:= foo", then a subsequent comparison of "foo = bar" does
not return true.
More simply, with nulls, saying "foo = foo" will not return true,
which flies in the face of common sense.
All sorts of other problems in SQL result from that basic situation,
that no NULL value ever equals itself.
But its worse than that, in that SQL isn't even consistent with
itself in how it treats nulls. With some kinds of operations or
queries, it treats every null being unique, and in other situations
it treats them all as being equal. No normal data type has this
problem.
So you have to write much more complicated SQL and application code
to handle data which may be null to get the results that you want.
At 10:28 PM -0800 3/10/06, Roger Binns wrote:
My main app happens to store phone numbers. You won't believe
how irritating it is when I find things automatically assume they
are integers.
The problem you describe only happens when you *are* using manifest
types, since code that you haven't written is looking at the content
of your variable and guessing incorrectly how to treat it based on
what its content looks like. By contrast, if you explicitly declare
that your phone numbers are text (or a custom data type), for
example, then the database will never treat it like an integer. In
this respect at least, you made my point for me about strong types
reducing errors.
Just for the record:
it wouldn't require any significant amount more code.
Yes it would. My code currently approximates to this:
cursor.execute("insert into foo (x,y,z) values(?,?,?)", x,y,z)
It would have to change into this:
# column x is defined as string
if isinstance(x, string): storex=x
elif isinstance(x, int): storex=`x`
elif isinstance(x, bool):if x: storex="1" else: storex="0"
else # various other types and conditions for this context
# repeat for y and z
# add in values
cursor.execute("insert into foo (x,y,z) values(?,?,?)", storex,
storey, storez)
It's clear from your example that you actually want to store multiple
distinct types of data in the same table columns. In this case,
under my proposal, you would declare that column to either be of the
Scalar type or don't specify a type at all. Then your code remains
as it was.
My first point is that for people who actually want a column that
stores just text or just numbers etc, they declare columns as those
types explicitly, and therefore data of those types is all which will
be stored.
Moreover, such people using a manifestly typed programming language
would already be working under the assumption that, while their app
variables are capable of storing multiple data types, they think that
they are only storing the one type they want. Eg, a count variable
would not be assigned 'abc' in their program, or if it was, that
would be an error. Since they assume that the correct type of data
is in their variables, they can also just store it in the stricter
database type without any conditionals, using one line as before.
Not having manifest types in the database throws away information
when you store values and requires restituting them when reading.
I don't propose throwing away manifest types, but rather that people
can choose between manifest or non-manifest types as it suits
themselves. SQLite 3 sort of does that already with its column
afinity, but my proposal would make the distinction more formal or
easier to optimize.
Or, looking at this another way, perhaps the Python bindings for
SQLite should be taking care of this for you.
They can't, unless they do something like silenty add an extra
column that stores the types of the values in the other columns
and attempt to transparently modify the SQL as it flys by to get or
update that column. (BTW I also happen to be an author
of wrappers for Python). (Your proposal sort of does this
by introducing a manifest type.)
SQLite and Python both already do this behind the scenes to implement
their manifest typing. Computers only know numbers, with everything
else being an abstraction; some extra numbers are stored that tell it
how to interpret the other numbers.
But perhaps we're thinking of slightly different things.
I would suggest finding an open source application that uses
SQLite and see if you would indeed make it simpler. One good
example I would suggest is Trac which was originally written
to use SQLite.
I'll look into this and get back to you some time. Though I have
other usage scenarios that I would be addressing first.
-- Darren Duncan
Re: [sqlite] updating SQLite to implement The Third Manifesto
At 6:52 PM -0800 3/10/06, Roger Binns wrote: Only for some applications. It would be harder to use for my apps. Specifically manifest typing as currently implemented in SQLite is a perfect match for apps writen in Python (which also uses manifest typing). It would require a lot more code to go through and force the data type for each column. If that is so, then I would argue that any need to write more code isn't tied to manifest typed programming languages themselves, but specific programs themselves; depending on how you code your applications, it wouldn't require any significant amount more code. In fact, particularly for queries (and reading data tends to be more common than writing), there should be less code. Or, looking at this another way, perhaps the Python bindings for SQLite should be taking care of this for you. Or, put another way, I would say this is a small price to pay for what is gained. Or, I doubt there actually is more work. (But I don't really want to get into an argument on this point, as there are many other points in my proposal which I see as being of greater importance.) But regardless, I have an additional idea which may help bridge the gap and work well for people. That is, while the database itself is strongly typed, you can have a specific type which is defined to manifestly be able to store values from any of a variety of simpler types. So for example, SQLite could have these types: - Boolean - Integer - Real - Text - Blob - Scalar The first 5 are simple types that store just numbers or text or whatever. The last, new 1, Scalar, is an actually-strong type which is defined as being able to store any of the first 5 types (just one at a time), and hence acts like a weak type. In a conceptual sense, a Scalar value is like a composite type with 6 member elements, each of the last 5 being strongly typed as one of the first 5 simple types, and the first element being an enum which says which of the other 5 holds the over-all current value. I believe something like this is what manifestly typed languages actually do behind the scenes, having a multi-element struct where one element says how to treat the other one(s). I know Perl does this, with its SV C-struct, and I'm sure other languages do similar. I know SQLite does something similar, if you look at its design spec. (Sure, that sounds more complicated, but then the actual work being done to support manifest typing *is* more complicated. Things look more like they are.) So if SQLite does it this way, then you can declare columns to be the Scalar type when you want them to hold anything, and one of the other types if you don't. Moreover, the effectively manifest typed Scalar is what you would get if you don't explicitly declare a type for a column. This happens already, but now the "what you get" actually has a name. The point is that you still get well defined behaviour that is specific to the declared data type(s) you choose to use, and you can count on its being consistent. -- Darren Duncan
[sqlite] updating SQLite to implement The Third Manifesto
rwise. In TTM terms, it is impossible by definition to have duplicate rows. 5.2 The results of all stages of queries do not contain duplicate rows. In SQL terms, every query or subquery has an implicit 'distinct' or 'group by all' clause on it. No joins produce duplicates. No unions etc do either. 5.3 By doing this and #3, all queries that look like they should return the same results actually do, whereas in SQL they may return different results in the presence of duplicates or nulls. Queries can also be simpler. 6. Columns in tables and views and query results have no ordinal value; they all have names and are referred to using only those names. Moreover, every column must have a different name from every other column. 7. Rows in tables and views and query results have no ordinal value; they are referenced by relational expressions that match on the values of columns, like in a SQL where-clause. 7.1 An order-by or limit clause only makes sense in an outer-most query, right when results are being returned from the database to the application, where it then specifies the order to return otherwise order-less rows. In doing all of the above, SQLite should actually be simpler to implement, and it will be easier to use, with more predictable results and fewer bugs. This next one can be implemented separately from all the other suggestions: 8. Add some standard relational logic operators that can be combined and nested to get all the power of selects and more, with less effort, such as any of the following you don't already have: restrict, project, join, product, union, intersection, difference, divide, rename. 8.1 The simplest join syntax, such as an unqualified comma-delimited list, would perform a natural join by default. Or we could more or less just have natural joins (and cartesian products, 'product') as the only kind of join. 8.2 Using these instead of 'select' should allow for easier implementation and optimization; for one thing, the expressions are more associative or commutative. This next one can be implemented separately from all the other suggestions: 9. Support nested/child transactions, such as a 'begin transaction' inside another one, which can make things a lot easier for applications; they have to worry less about whether a transaction already exists before starting another one. These are functionally sort of like save-points in SQL, in that even if an inner transaction commits, it is still thrown away if the outer transaction rolls back. To implement this best, you would probably need multiple (cascading?) journal files, one per transaction level. Following are also features of The Third Manifesto, but can possibly be left out of SQLite in accordance with its Lite nature: 1. All views are updateable like they were tables. From the user's point of view, tables and views are the same sort of thing in how they can be used. 2. Tables can be assigned to directly like they were variables, and insert/update/delete is actually a short-hand for this. Eg, an insert is equivalent to an assignment to a table of the table's old value unioned with the rows being inserted. Supporting this allows users to define arbitrarily flexible updating operations, such as "replace or add" and such. 3. The system catalog tables can be updated directly using data definition language, which results in the schema being updated. Eg, you can use insert statements to create a table rather than a create statement. 4. Support definition and use of custom data types. 5. It should not be necessary to explicitly declare indexes to help with speed. 6. Generally speaking, users should not have to know about implementation details, but rather just express what their data actually means. Okay, that's about all for this initial proposal email. Ultimately, I believe that the core of my proposal involves simplifying SQLite, making it leaner and meaner, and also reduces possible or actual bugs or difficulty in understanding. At the very least, I hope that the trunk would have the pragma or compile option that essentially strips out current features like nulls and other ambiguity, so essentially we have a restricted or simplified SQL. I also bring this up because I would expect that SQLite should be able to perform faster when it doesn't handle nulls or duplicates or weak data types than if it does. The conceptual logic is simpler when we don't have those, and the implementation code should also be simpler, and perform faster, since there are fewer possibilities to check at logical decision points. And it should be easier to optimize queries. So even if no incompatible changes are made, I would hope that it is possible to optimize for the simplest case. -- Darren Duncan
Re: [sqlite] ORDER BY of UNION?
At 8:59 PM -0800 3/1/06, Boris Popov wrote: I can't seem to get unions to sort properly, SELECT DISTINCT * FROM (SELECT t1.ID FROM GR_ADDRESS t1 UNION ALL SELECT t1.ID FROM PERSON t1) t1 ORDER BY t1.ID DESC results in "no such column: t1.ID" error. How would I go about sorting the result set in this case? Cheers! -Boris Try removing the "t1." from both inner select statements, so it just says "select id" in both places. -- Darren Duncan
Re: [sqlite] Mac OS
At 11:32 AM +0600 2/15/06, Kirill wrote: Whether will be SqlLite in the future and under Mac OS if there will be that as soon? SQLite runs under Mac OS X right now, and has for a long time. A version is even bundled with X.4 Tiger. -- Darren Duncan
Re: [sqlite] Bug in insert into ... select * ....
At 3:32 PM +0800 1/29/06, shum [Ming Yik] wrote: Hi Sqlite Team, insert into disk.your_table select * from your_table; There is an bugwithin insert into table01 select * from table02 Always double the inserted rows in table01 ... where the structure of table01 and table02 are the same I use Sqlite 3.3.2b Your example code did not indicate that you were using 2 different tables; you said "insert contents of your_table into your_table", which would of course result in a doubling up. You need to use different table names. Or if you have 2 database files having the same named table, you need to say foo.your_table and bar.your_table; leaving the prefix off one is ambiguous, so it could choose the one in 'disk'. Try fixing your SQL and see if it works then. -- Darren Duncan
Re: [sqlite] Database merge?
At 11:20 PM -0600 1/26/06, michael munson wrote: I have two databases, one has many fields I need, the other only has one. Is it possible to go through the second database, and update specific rows on the first? DB1 has many rows, and DB2 has some. Every row in DB2 is in DB1 and they both have the same value for the key row so they can be cross-referenced. What is an easy way to merge DB2 into DB1? First, open a connection to one of the databases, probably best the one you are merging into. Then "attach" the second database. At this point, you will see all tables of both databases visible to you to do whatever with. It may actually be easier to merge the two databases into a third, new one. In which case, for each combined table, you can say something like: INSERT INTO SELECT FROM UNION SELECT FROM Adjust to taste. Or if that doesn't work, then your application may have to iterate through the rows of the source tables and insert conditionally into the destination table. -- Darren Duncan
Re: [sqlite] host parameter names in string literals
At 10:10 AM -0700 1/18/06, Robert Simpson wrote: - Original Message - From: "Marvin K. Bellamy" <[EMAIL PROTECTED]> I want to prepare a statement like this one: SELECT name FROM people WHERE name LIKE '%?%' But, it looks like host parameters inside string literals aren't parsed. Any clues as to how I pull this off or if the parameter really will be parsed? SELECT name from people WHERE name LIKE ? Your parameter then should contain the text to look for, such as "%mytext%" Alternately, and probably more elegantly, SELECT name FROM people WHERE name LIKE '%' || ? || '%' -- Darren Duncan
Re: [sqlite] SQL syntax possibilities
At 1:34 AM -0700 11/16/05, [EMAIL PROTECTED] wrote: uSQLite does not (generally) enter into the details of the query it is passed. There is however an exception for security. uSQLite requires a login and (depending on the user and network) assigns the user a level: 0: No access 1: Select only 2: Update/Insert only 3: Select/Update/Insert only 4: Power user Maybe your list wasn't complete, but I notice that 'Delete' is conspicuously absent, yet it is necessary to do many common tasks. And before you say that it was left out from all but Power User because of its possible destructiveness, I would say that Update is just as destructive; an Update can blow away anything a Delete can, if you say "update ... set foo = 0" for all fields and rows. I suggest that levels like this make more sense from a security standpoint: 0: No access 1: Select only (read-only) 2: Insert only (non-destructive drop-box) 3: Select/Insert only (fully non-destructive read and write) 4: Select/Insert/Update/Delete only (full read-write data, no ddl) 5: Power User -- Darren Duncan
Re: [sqlite] optimizing out function calls
According to my understanding of standard SQL, you should be able to say:
SELECT arbitrary_expression() AS bar FROM foo ORDER BY bar;
... and the expression is only evaluated once per row, not twice.
Your actual example seems confusing, since you appear to alias your
'vectors' table to 'match' in the from clause, which is also the name
of your function, and the name of what you sort by. Perhaps having
different names for each thing that is actually different will make
your question easier to answer.
For example:
SELECT uid, match_func("complex", "function", vector) AS match_res
FROM vectors AS match_tbl
ORDER BY match_res DESC
LIMIT 20;
-- Darren Duncan
At 10:01 PM -0700 11/12/05, Nathan Kurz wrote:
Hello --
I'm trying to figure out how to optimize a query a bit, and think I've
hit a case that could easily be optimized by sqlite but isn't. I'm
wondering if it would be an easy optimization to add, or whether there
is some way I can 'hint' the optization into being.
I'm using a computationally expensive user defined function called
'match()'. In case it makes a difference, match() is written in C,
and for testing, I'm loading it as a shared library into the sqlite3
shell application. I want to return the value of match(), and also
order by it. So my query looks something like this:
SELECT uid, match("complex", "function", vector) FROM vectors AS match
ORDER BY match DESC LIMIT 20;
I had expected that match() would only be called once per row, but it
turns out to be called twice: once for the select, and once for the
ordering. I've confirmed this both by putting in a counter, and by
using 'EXPLAIN'. Is there any way to tell SQLite to reuse the value
of the first call rather than calling the function again?
I'm a comfortable C programmer, but only superficially familiar with
the SQLite code so far. If I'm not missing something obvious, hints
on where to look at writing a patch for this would be appreciated.
Thanks!
Nathan Kurz
[EMAIL PROTECTED]
Re: [sqlite] Quoted identifiers
You don´t undertand me, maybe my poor english.
I will try to show with examples in the sqlite command line.
create table test("Full Name" varchar(30), "Login" varchar(15), Age integer);
insert into test ("Full Name", "Login", Age) values ("Enrique Esquivel",
"the_kique", 24);
.headers on
select * from test;
SQLite returns:
Full Name|Login|Age
Enrique Esquivel|the_kique|24
But when write:
select "Full Name", "Login", Age from test;
returns:
"Full Name"|"Login"|Age
Enrique Esquivel|the_kique|24
Moreover when quote all fields:
select "Full Name", "Login", "Age" from test;
returns:
"Full Name"|"Login"|"Age"
Enrique Esquivel|the_kique|24
Also:
select [Full Name], [Login], [Age] from test;
SQLite returns wrong:
"Full Name"|"Login"|"Age"
Enrique Esquivel|the_kique|24
The quotes should be used for SQLite only for understand the identifiers, the
fields in result must be unquoted. Try to test with other dbms and anyone has
this behavior.
Re: [sqlite] Request for comment: Proposed SQLite API changes
At 4:22 PM -0700 11/3/05, Robert Simpson wrote: It's 100,000 more memory allocations and about 4.6mb of string copies SQLite is doing behind the scenes. Every time SQLite prepares a statement it allocates and copies the string. If you're inserting a million rows into a database using the above "cheese" method (which I hate but a lot of folks do anyway) then that's a pretty serious performance hit IMO. This only happens if you are not reusing prepared statements for each row going into the same table. And if you're not reusing prepared statements, you're already doing 100,000 memory allocations that you shouldn't be doing, for the statement handle itself, and have already hit yourself. Use prepared statements and both hits are brought down to something negligible. -- Darren Duncan
Re: [sqlite] Request for comment: Proposed SQLite API changes
To summarize my thoughts on this thread so far: 1. None of the stuff being discussed should cause a major version change, such as to 4.x.y; a big leap like that should only be taken for large and sweeping changes, such as incompatible file formats or paradigm shifts. Mainly, they should be given a lot of time to think through and bundle a number of issues, such as 3.x.y did, and not just be done at the drop of a hat like doing it now would be. 2. Any changes being discussed should just raise the version to 3.3.0; really, it is only changes to the third digit which shouldn't break anything, while changing the second that could reasonably happen. 3. Having sqlite3_step() return the actual error is indeed an improvement, assuming that the error had always been detected at that time, deferring its report is counterintuitive. 4. A rebinding change on SQLITE_SCHEMA errors should be done with a new function name since it is significantly different behaviour and effectively a wrapper over several other function calls. Let users choose what they want to use. 5. That said, its fine to store the original SQL text in sqlite3_stmt regardless of whether users use the new function or not, for simplicity, and its storage may carry other uses later as well. 6. I suggest having a compile time pragma regarding SQL text storage so that people wanting to save the most RAM can have it not stored and lose the new function, while it is available by default otherwise. -- Darren Duncan
Re: [sqlite] CHECK constraints
I'm not sure if this applies, but in my experience it is normal for a unique value constraint to be satisfied on columns with null values, as is a foreign key constraint, which is only evaluated on not-null values. Following that precedent, I would say that the CHECK constraint should pass if its expression results to null. If you're trying to enforce a certain kind of behaviour in a particular check constraint, you probably want to add some IS NULL expressions to explicitly declare the behaviour you want, to specify times when a null input would result in a check failure. -- Darren Duncan At 6:30 PM -0500 11/2/05, [EMAIL PROTECTED] wrote: In a CHECK constraint, if the expression is NULL (neither true nor false) does the constraint fail? Example: CREATE TABLE ex1( x INTEGER, y REAL, CHECK( x
Re: [sqlite] R: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Given what I've read so far, you shouldn't have to change any of your code. Conceptually speaking, all numbers would be stored as reals internally, though actually some would be stored as integers if possible for efficiency. When you invoke a SQLite accessor function such as double() or int() then the value you asked for will be coerced into the requested data type, and then returned that way. This is how it would have to work, considering that external C code actually considers those types to be different machine native formats. No C code changes should be necessary. Only some SQL code may need changing. -- Darren Duncan At 10:10 AM +0100 11/2/05, Zibetti Paolo wrote: Most of the discussion so far was about proposed change number 2, on the contrary I'm concerned about proposed change number 1. Does this mean that a number that can be stored as an integer will be stored as an integer and, thus, I will need to read it back as an integer ? Here is what I mean: with SQLIte 3.2.x, if I run these two statements Insert into foo values(5.34); Insert into foo values(3.0); Table foo will contain two rows that both contain a real-type number, so, to read the values back from the DB, I can always use sqlite3_column_double(). With your proposed change it appears to me that for each row I will have to first test for the type of the field and then decide whether to use sqlite3_column_double() or sqlite3_column_int(). Is this correct ? If so, changes will be required to the existing code to port it to Sqlite 3.3.x. Bye -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato:martedì 1 novembre 2005 15.00 A: sqlite-users@sqlite.org Oggetto:[sqlite] Proposed 3.3.0 changes. Was: 5/2==2 (1) Floating point values are *always* converted into integers if it is possible to do so without loss of information.
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
I can think of a very elegant solution to this whole ordeal, which is inspired by Perl's way of doing things: Have *two* division operators which have different behaviour and which look different so you can tell what will happen where they are used, regardless of their operand data types. Perl is loosely typed by default and so has elegant handling down to a fine art, having for example different operators for string and numerical comparisons, so you always know what will happen regardless of the operand data types. So I propose for SQLite that any expression having '/' will cast both of its operands as reals and the result will be a real. Also, any expression using instead 'DIV' will cast both of its operands as integers (truncating them if necessary, not rounding) and return an integer. To go with that, 'MOD' will cast both operands as integers and return the integer modulus. It works out visually, both 'word' operators use integers and the one 'symbol' operator uses reals. Sure there's a difference, and while this should help an implementer, it is useful to users because it describes *behaviour*. On a different matter ... At 9:59 PM -0500 11/1/05, [EMAIL PROTECTED] wrote: John Stanton <[EMAIL PROTECTED]> wrote: Users love such a number system because it is natural and works like the Arithmetic they learned in Grade School. I find the idea of dividing two integers stored in binary form and getting a gratuitous conversion to floating point ugly and potentially very annoying. I admit that it has been 4 decades since I was in grade school, but back in the 60's we were taught that 5/2 is 2.5. Are they teaching something different now? Or have I misunderstood the comment? Well, if you want to know ... In the youngest grades, such as kindergarten and grade 1 etc, they only work with whole numbers, such as when dividing up apples or oranges, so 5/2 is "2 with 1 remainder". Only in later grades do they start with fractional numbers, such as 5/2 is "2.5" or "2 and 1/2". So young children actually get it both ways depending on their ages. -- Darren Duncan
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Also, regarding the name change suggestions, I disagree. As people have said, no database product is fully SQL standard compliant, and SQLite is no different in that regard. So in the current environment, SQLite's name is *not* misleading, despite any deviations. The name is a brand anyway, and brands transcend any meaningfulness. It should stay the same. -- Darren Duncan
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
To answer Richard's question directly: I do not see the proposed change causing any hardship for me. I happen to like static typing myself due to its ability to help prevent bad data from propagating, with explicit variadic data type for people that don't want to choose a more restrictive type, but SQLite already isn't statically typed, so this change won't make things any worse. Under the circumstances, this change will actually be an improvement to useability as I see it. We have round() or truncate() or CAST when we need integer division. -- Darren Duncan
Re: [sqlite] Quoted identifiers
At 9:50 PM -0300 10/17/05, [EMAIL PROTECTED] wrote:
I need to use a quoted identifiers and sqlite
support it well but when i obtain
the data from the database the cursos give to me
the fields with quotes. When i
do this directly there is no problem because i can remove the quotes, but i
access to sqlite through delphi using zeosdbo and i can´t touch the resultset
directly.
I don´t think this a useful thing, there isn´t any other database which
behavior like that, maybe the next versions will avoid that.
It sounds like Delphi needs to be fixed, then;
having quotes *is* standard, and many databases
support it, including both MySQL and Oracle.
In the SQL:2003 standard, delimited/quoted
identifiers are a different beast than bareword
identifiers. Delimited identifiers can contain
any characters at all, just as a string literal
can, including whitespace and punctuation, and
they are case-sensitive; they are always defined
and invoked using the delimiters, which are (")
usually, but MySQL uses (`) instead, and both are
different than the string literal delimiter of
('). Non-delimited identifiers can not contain
whitespace or most punctuation and can not be
plain numbers, and they are case-insensitive;
likewise, usually defined and invoked without
delimiters. As far as I know, SQL:2003 allows
you to invoke non-delimited identifiers using the
delimited format, in which case any non-delimited
identifiers match in their folded-to-uppercase
form; in this respect, non-delimited is a full
and clearly defined subset of delimited, so if
they are internally stored like character
strings, everything would just work.
And before anyone says that identifiers
containing whitespace is a stupid idea, I counter
that thought. From a non-programmer user
perspective, people who create databases using
GUI tools and that type names of tables and
fields into individual GUI form boxes, it is just
as natural to use spaces and punctuation as it is
to put those in file system file names, usually
done in a GUI. So it is all well and proper to
support this internally and expose it where
possible. Database design is not programming and
should not be subjected to the same limitations;
those elements are not variable or function
names. And even when we are programming to a
database, we are often writing programs that use
a data dictionary and/or are data driven, and
used by non-programmers. When we are composing
SQL directly, adding those delimiters is very
easy.
SQLite needs to default to the most compatible
and representitive format there is, which is the
delimited identifiers. Alternate output
behaviour can be accomplished by a connection or
statement specific pragma, or a wrapper.
-- Darren Duncan
Re: [sqlite] Maintaining a sequence that's not rowid
At 11:13 AM -0700 10/6/05, Antony Sargent wrote: Alternatively, you might consider making the id_allocator table have an auto-increment primary key, and then insert null to have sqlite generate id's for you, and use last_insert_rowid() to find out the value it generated. /* Initialize system */ BEGIN; CREATE TABLE id_allocator(id INTEGER PRIMARY KEY); COMMIT; /* Retrieve next id in sequence: */ INSERT INTO id_allocator (id) VALUES (NULL); SELECT last_insert_rowid(); /* This is the id to use */ I'm inclined to think that this is a bad idea by itself because your id_allocator table ends up with a large number of records in it, one per increment, which take up space but don't serve a useful purpose. Whereas, an updating approach will not take up any more space than necessary. -- Darren Duncan
[sqlite] the 3 numerical types (was Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?)
At 11:07 AM -0600 9/29/05, Dennis Cote wrote: As you can see, the result of exact (integer) division is also exact (integer) with implementation defined precision and scale. The result of an expression containing approximate (floating point) values is approximate (floating point). So SQLite is conforming to the SQL standard. You mis-understand what 'exact' means; 'exact' != 'integer', but rather 'integer is conceptually a sub-set of exact. An exact value can be fractional, such as '1.32', and yet not be floating point. Therefore, if the columns were defined as integers, then it is reasonable for the result to be an integer; however, if the columns were defined as exact fractionals, then the result should be an exact fractional, '2.5'. When it comes to concept and storage, there are 3 distinct types of numbers, which the SQL standard gives distinct names: 1. INTEGER - and big/little and sign/unsign variations - An exactly remembered whole number that can be stored and manipulated compactly in base-2 binary as is native for computers. Any value that will always be whole is optimally stored this way. 2. DECIMAL(p,s) - An exactly remembered fractional number that is typically stored in a form akin to text, such as one byte per base-10 digit. These can effectively store arbitrarily large numbers of any length and precision without loss of detail, although doing math with them may be slower. For example, if you store '2.5' in one, then '2.5' is actually stored. 3. FLOAT(p) - and double variation - An approximately remembered number that is stored and manipulated compactly in base-2 floating point. Increasing the precision will only better approximate a value, but the exact value is lost, though math with these is fast. For example, if you store '2.5' in one, then either '2.4' or '2.50001' is actually stored. SQLite should recognize the above 3 numerical types as being distinct, and do the correct actions with math involving any of them. -- Darren Duncan
Re: [sqlite] count(*) slow
At 8:56 AM -0500 9/15/05, Puneet Kishor wrote: Hence, it might be worthwhile maintaining the meta information no matter what... most of the folks won't ever notice it, and everyone would marvel at how quickly COUNT(*) was returning the results. You are assuming that everyone wants to do a count(), but many people don't; for them, putting that in the core slows things down; for people that do want it sped up, the trigger option is perfectly valid. I support leaving things the way they are, with no extra meta-info maintained. -- Darren Duncan
Re: [sqlite] CROSS keyword disables certain join optimizations
At 7:25 PM -0400 9/10/05, D. Richard Hipp wrote: The rational behind using the CROSS keyword to disable an optimization is that the CROSS keyword is perfectly valid SQL syntax but nobody ever uses it so I figured we can put it to use to help control the optimizer without creating any incompatibilities. Well, that's fine as long as CROSS still continues to mean and do what it has always meant, which is that you explicitly want the result set of "SELECT * FROM foo CROSS JOIN bar" to be every foo row crossed with every bar row. This actually is used in real situations, even though it is less common than an INNER or OUTER join. See SQL:2003, 7.7 "". -- Darren Duncan
Re: [sqlite] RFC Beginning digit in column name
At 8:46 PM +0100 8/9/05, Nuno Lucas wrote:
[09-08-2005 19:05, ender escreveu]
[...]
So my simple feature request would be: allow '`' as a quoting symbol -
as mySQL does. Or - what would be as helpful as the other idea - allow
unquoted column names with leading digits - as mySQL does.
Does MySQL allows [0_xy] to specify column names?
If I remember correctly, that is the standard SQL way to have columns
with spaces in the middle, and sqlite allows columns starting with a
digit in this way...
Regards,
~Nuno Lucas
The SQL standard has both delimited and bareword identifiers.
Bareword identifiers can only be letters, numbers, and underscore,
and start with a letter, to my knowledge; they are also
case-insensitive.
Delimited identifiers can have any characters in them, including
spaces, and they are delimited with double-quotes (") usually.
The standard may allow other delimiting characters, but I'm not sure.
I imagine that back-ticks (`) may be safe to support if they aren't
already used by SQL in some other way. You certainly don't want to
use single-quotes (') as those are always literal string delimiters.
You also don't want to use brackets ([]) as those are used by the
standard for array indices.
I suggest for simplicity that SQLite simply support single-quotes for
string delimiters and double-quotes for identifiers; clearly distinct
and simple.
-- Darren Duncan
Re: [sqlite] Richard Hipp Awarded Google-O'Reilly Open Source Award at OSCON 2005
At 4:33 PM +0200 8/5/05, Bert Verhees wrote: SQLite is a very great engine. Thanks for the good work, well earned this award, congratulations. Good luck Yes indeed. I regret that I didn't have my camera out to take a picture of DRH receiving he award. I had such a good seat too. Hopefully someone else took that picture. -- Darren Duncan
Re: [sqlite] Calculation between rows?
At 4:27 PM -0400 7/17/05, William Trenker wrote: I've been searching the web on such topics like "sql calculation between rows" but I'm not having much success. The best hint I've found is to construct a join between the table and itself but I can't see how to do that in a way that offsets one side of the join relative to the other side. Assuming there actually is a meta-data function like ROW_NUMBER(), which is the ordinal value of the row in the query result, you can just do something like this: SELECT FROM ( SELECT ROW_NUMBER() AS myrownum, sq.* FROM AS sq ) AS a FULL OUTER JOIN ( SELECT ROW_NUMBER + 1 AS myrownum, ... FROM AS sq ) AS b ON b.myrownum = a.myrownum ... So you do the join between the subquery and itself, and the "+1" causes a single row offset in the join. Note you may have to UNION ALL a single row to the top or bottom of each subquery in order to prevent losing the first/last row. -- Darren Duncan
Re: [sqlite] group by to return correlated results
A simple answer is to use a subquery:
select name, day, distance
from (
select max(distance) as max_distance
from t
group by name
) as foo inner join t on t.distance = foo.max_distance
My exact syntax may be off (eg, the second 'as' may need removing)
but otherwise you should be able to use that.
-- Darren Duncan
At 1:35 AM -0400 6/22/05, Al Danial wrote:
This table keeps track of how far two people ran in
a given week:
create table t(name, day, distance);
insert into t values("al", "monday" , 4.0);
insert into t values("al", "tuesday" , 4.1);
insert into t values("al", "wednesday", 5.5);
insert into t values("al", "thursday" , 2.3);
insert into t values("al", "friday" , 8.1);
insert into t values("al", "saturday" , 2.2);
insert into t values("ed", "thursday" , 14.0);
insert into t values("ed", "sunday" , 18.4);
I want to show the name and day that corresponds to
each person's maximum distance. I know I can use
'group by' like so:
sqlite> select name,max(distance) from t group by name;
namemax(distance)
-- --
al 8.1
ed 18.4
which is fine. But if I also want to know the day of
the week when the maximum occurred for each person
then things go haywire:
sqlite> select name,day,max(distance) from t group by name;
nameday max(distance)
-- -- --
al monday 8.1
ed thursday18.4
which isn't right because the day shown here isn't the day
when the maximum occurred. How would I rewrite the query
so that I'd see this result:
nameday max(distance)
-- -- --
al friday 8.1
ed sunday 18.4
?-- Al
Re: [sqlite] Quick news on the Perl-bindings front
At 1:29 PM -0400 6/21/05, Matt Sergeant wrote: 1.09 is now on CPAN. Note that there's a weird bug when trying to compile against the system sqlite on OS X Tiger due to some munging Apple have done to the header files. Someone is supplying me with a work-around. I noticed that upload before seeing this post; thanks. Can you please say which file(s) you changed to implement the (don't turn looks-like into numbers unless asked) change, mentioned in the changelog? When I used CPAN's diff utility and looked at the files which seemed to be specific to DBD::SQLite, other than the Makefile.PL, I didn't see any changes in them besides version number updates. I looked in SQLite.xs and SQLite.pm. -- Darren Duncan
Re: [sqlite] Training opportunity: The Inner Workings Of SQLite
At 3:04 PM -0400 6/19/05, D. Richard Hipp wrote: I will be giving a 3-hour lecture on how SQLite works at the O'Reilly Open Source Conference in Portland, OR on 2005-Aug-01. Yep. Already signed up for your tutorial a couple weeks ago. Looking forward to it, and meeting you in person. On a separate note, anyone who plans to go to OSCON, try hard to register by June 20th (tomorrow) because after that all the prices go up by about 15% (you lose early reg prices) and you lose certain other things. Also, if any of you are from around south-west BC (eg, Victoria, Vancouver), please email me privately. -- Darren Duncan
Re: [sqlite] preventing text to integer conversion of bind variables in perl
At 12:55 AM + 6/16/05, Mr. Tezozomoc wrote: sqlite is typeless I have addressed this issue in the following HOWTO: http://www.mail-archive.com/sqlite-users@sqlite.org/msg03205.html Please refer to it. Tezozomoc. I think not. From your own HOWTO: Assumption... this is based on SQLITE 2.8.6... forgive the aging.. I believe that this is out of date and not applicable. The SQLite 3.x line is not typeless and has distinct numerical and text and binary data types. Therefore it should be able to take columns declared as numbers and sort them as such. -- Darren Duncan
Re: [sqlite] preventing text to integer conversion of bind variables in perl
At 11:39 AM -0400 6/15/05, Matt Sergeant wrote:
I added it because of another bug report that was incorrectly
sorting integer columns based on text sort order. For example if you
inserted:
("k1", 8);
("k2", 9);
("k3", 10);
("k4", 11);
and then asked for: SELECT * FROM t ORDER BY Column2
you get back:
k3, 10
k4, 11
k1, 8
k2, 9
Which seems obviously incorrect.
In answer to your question though, yes you can remove that bit of
code, as long as you're aware of the above side effect.
Well, if Column2 is explicitly defined as an integer, then it should
always sort as an integer, and making that work is the responsibility
of SQLite itself.
I see that preserving the input data in all situations where the
declared column type can handle it is of the utmost importance, and
DBD::SQLite should do this.
So I vote to remove any de-stringification code you have in DBD::SQLite.
To be honest I'm not entirely sure what the correct fix is - maybe
ignore the above bug and tell the requestor he has to: SELECT * FROM
t ORDER BY int(Column2)
Yes, go ahead and do that. Alternately, tell the person to use
SQLite in strict mode so that it only ever stores ints in int
columns. Like most databases do.
Matt, I would also appreciate it if a new DBD::SQLite was released
asap that embeds and is known to work well with the 3.2 series,
specifically 3.2.2.
Thank you in advance.
-- Darren Duncan
Re: [sqlite] preventing text to integer conversion of bind variables in perl
At 3:34 PM -0400 6/14/05, D. Richard Hipp wrote: On Tue, 2005-06-14 at 20:18 +, [EMAIL PROTECTED] wrote: I have textual data that may look like integers (eg. "0325763213"). On insertion, any leading "0" will vanish. How do I prevent this and make the data be inserted verbatim? Simple illustration: sqlite3 test 'create table t ( k text unique, v text);' perl -e 'use DBI; $db = DBI->connect( "dbi:SQLite:dbname=test" ); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "key", "0325763213");' sqlite3 test 'select * from t;' returns: > key|325763213 It looks like perl is making this conversion for you. SQLite does not do this. As a work-around, consider prepending a single 'x' character to every "v" column entry then strip of the 'x' before you use it. Actually, Perl itself wouldn't be doing that. Perl only converts a string to a number when it is used in a numerical context; eg, '$bar = $foo + 0'; otherwise it continues representing it as a string. Since the inserted value was string quoted when it was defined, it started out as a string. I suspect that it is the DBD::SQLite module, or the DBI module, that is the problem. As I recall, DBD::SQLite was never updated to use the prepared statements feature added to SQLite 3 and continues to emulate that feature which DBI defines (as it did for SQLite 2). It does this by substituting the values into the raw SQL and executing that as a SQL string without variables. Moreover, I think this functionality will examine the variable, and if it looks like a number, will insert it into the SQL as a number rather than a character string, hence the loss of the zero. In that case, neither SQLite nor the Perl core is at fault, but the intermediary between them, and hence the best solution is to fix that so it at least always string-quotes (or ask Matt to do it). I ruled out SQLite because you were using version 3 and explicitly defined the field as a character string. Meanwhile, you could follow the the workaround that DRH mentioned. -- Darren Duncan
Re: [sqlite] philosophy behind public domain?
At 9:57 PM +0200 5/25/05, Ulrik Petersen wrote: Lawrence Rosen has been the general counsel for the Open Source Initiative, and he specializes in technology and computer law according to his website: Thanks for all the responses. And some of them such as the above show that I didn't pull my legal concern out of thin air; there was a significant background to it, even if it is an issue over which experts are divided. Not FUD at all, in the malicious sense of the word anyway. -- Darren Duncan
Re: [sqlite] Using variables within trigger definitions
The SQL:2003 standard says you use syntax like this to do what you want:
CREATE TRIGGER Side_Insert AFTER INSERT ON Side
BEGIN
DECLARE LowDate DATE;
SELECT MIN(Startdate) INTO LowDate FROM Basis;
INSERT INTO BASIS (Name,Startdate) VALUES ("Trigger", LowDate);
END;
You use 'declare' to declare a variable. Also,
you only use 'set' when assigning the value of
another variable or expression. You do not use
'set' to retrive the value of a query, but 'into'
instead.
I don't know if SQLite supports this feature, though.
-- Darren Duncan
At 9:50 AM +0200 5/10/05, Philipp Knüsel wrote:
Hello SQLite Users
Is there a possibility to define variables within trigger definitions?
I would like to to something like this (simplified example):
CREATE TRIGGER Side_Insert AFTER INSERT ON Side
BEGIN
SET LOWDATE = SELECT MIN(Startdate) FROM Basis;
INSERT INTO BASIS (Name,Startdate) VALUES ("Trigger", LOWDATE);
END;
I know, there is the possibility to do the same with SUBSELECTS,
but I'm looking for something easier/faster/more
elegant than doing the same subselect several
times.
(as my real example is more complicated)
Even if there is no possibility right know, I
would probably suggest this as a feature for the
future.
Thanks a lot!
Philipp
Re: [sqlite] how to COPY FROM stdin in 3.x?
At 1:27 PM -0700 5/4/05, scott wrote: I can rewrite this to do individual inserts, but would like to find out whether there's a better workaround/idiom for this. If you are going to use an INSERT statement, then your usage is a prime candidate to use prepared statements with. Your insert statement is parsed once and then the actual data insert is relatively little work and performs quickly. I believe this sort of activity is what happens to implement the you wanted anyway. -- Darren Duncan
Re: [sqlite] DBD-SQLite build query
At 6:20 PM -0700 4/27/05, Clark Christensen wrote: Being new to compilers, I have a question about building DBD-SQLite (for Perl). If I want to update the underlying SQLite code in DBD-SQLite to the current release, (v3.2.1), i sit simply a matter of putting the current SQLite sources into the DBD-SQLite-1.07 dir and rebuilding? Or is it more complicated? Anybody else have experience with this? I do, and the solution's way simpler than that. Just execute the getsqlite.pl script that's included with the DBD::SQLite distro after you decompress the archive and before you do the 'perl Makefile.PL'. Eg: perl getsqlite.pl perl Makefile.PL make make test make install The catch is that you need to have the CPAN module LWP::Simple installed with your Perl first, as the getsqlite.pl script depends on it to implement an HTTP client by which it fetches the most recent code. This module does not come with Perl by default, but it is easy to download from CPAN and install yourself. -- Darren Duncan
Re: [sqlite] SQLite in OS X 10.4
At 4:53 PM +0100 4/25/05, Tim Anderson wrote: I attended a press launch for "Tiger" today and was glad to see SQLite on one of the slides. However Apple was a bit vague about how it is used and the people there weren't sure if it was 2.x or 3.x that is integrated. I wondered if anyone has a quick summary of how SQLite is used in the Apple OS? As I understand it, the main way SQLite is used in Tiger is as part of its "Core Data" developer feature, which takes care of making your objects persistent. I assume that SQLite 3.x is being used. http://developer.apple.com/macosx/tiger/coredata.html -- Darren Duncan
Re: [sqlite] Syntax Error For 3.0.8 --> 3.2.1 Upgrade
At 12:07 PM -0500 4/21/05, William Hachfeld wrote: sqlite> CREATE TABLE 'Open|SpeedShop' (version INTEGER); sqlite> INSERT INTO 'Open|SpeedShop' (version) VALUES (1); One of your problems is that you are using single quotes for a delimited identifier, "Open|SpeedShop", when you should be using double quotes. Single quotes means a string literal in the SQL standard, where double quotes a delimited identifier, which are very different things. On the other hand, I think the SQL standard lets you define your own delimiter, for delimited identifiers, but even in that case it seems wrong to pick the same kind as that which always means string literal. -- Darren Duncan
Re: [sqlite] mysql converter?
At 10:39 PM -0400 4/18/05, tom wrote: Hey guys, Has anyone written one? I'd like to "upgrade," or be able to, if sqlite starts getting hit much more. In the meantime, I think I'll see how much it can handle...:-) There are several of those on http://search.cpan.org/ , which allow you to migrate between different databases; support for SQLite, MySQL, and PostgreSQL are the most commonly supported. I am also making such a solution, meant to be a lot better than the others, but currently it is not complete enough to be useable without a lot of extra work on the user's part; hopefully this will change within 1-3 months. -- Darren Duncan
Re: [sqlite] Bound parameters not working with prepared statement
I believe that exactly the right circumstances to allow bound parameters is all of the same places where literal values are allowed, namely strings, numbers, nulls, etc. It does not make sense to have bound parameters in any other situation. -- Darren Duncan
Re: [sqlite] Conversion mysql -> sqlite
At 3:26 PM -0500 3/15/05, Peter Jay Salzman wrote: And here's how I've tried to implement it on sqlite using PHP. Note that DB_query() is a wrapper for sqlite_query(). I've changed the "auto_increment" to "INTEGER PRIMARY KEY", as the FAQ said. I'm very new to SQL, but I guess "INTEGER PRIMARY KEY" and "NOT NULL" aren't separated by commas? Having NOT NULL with a PRIMARY KEY is redundant, if not an outright error. Defining something as a primary key is implicitly defining it to be both not null and distinct. -- Darren Duncan
Re: [sqlite] thoughts on a web-based front end to sqlite3 db?
I suggest using a Perl 5.8 based solution. Perl is very mature and has strong SQLite 3 support in its extensions (DBD::SQLite). This will run on anything. It also isn't dependent on Microsoft technology. There are a number of pre-existing solutions on CPAN and elsewhere that you can look at for ideas, or for customizing. -- Darren Duncan At 4:22 PM -0500 3/7/05, Eli Burke wrote: I've been working on a project using sqlite3 since last fall. At the time, I knew that it would need a web-based front-end eventually. I have a very small bit of experience with PHP, and I assumed that PHP would support sqlite3 sooner or later. Well, it's later, and as far as I know, PHP is still using the 2.x branch. So, I was wondering if any of the more opinionated among you would care to suggest an interface language. It'll be on a Linux box, presumably running apache although I'm open to alternatives. The app itself uses sqlite3 for scheduling jobs and storing job data, so the web interface only needs to be able to insert some data and do visualization (pretty standard stuff I think). Ease of learning is a plus as I need to get something basic up and running fairly fast. I've heard good things about Python in that respect. Does anyone have alternative suggestions, or if you agree that Python Is Good, would you suggest using APSW, pysqlite, or something else? Thanks, Eli
Re: [sqlite] Thanks!
At 11:43 AM -0800 3/3/05, Jay wrote: One of the things the C++ experts take particular care to remind everyone is that character arrays are evil. I thought using string classes mostly elminated the buffer overflow problem. The string class is heavily examined for such errors as are the intrepreters. I'm seeing reports of vulnerabilities in interpreted languages so I'm not sure I agree with that logic, or the orders of magnitude comparison. Do you have any references or research on it? Interpreter authors write code with errors too. If the interpreter gets tweaked every month the code only gets 30 days of testing. Few of the packages I've seen have any regression testing so new errors can be introduced with each revision and old errors can reappear. I should go look and see how they cracked php and string overflows. I say "orders of magnitude" if a class of errors is the one that occurs the most frequently, and only appears with the compiled languages. By emiminating the most common problems, orders of magnitude improvement is gained. With interpreters, any buffer overflows etc are only in the interpreter itself, and can never be in your own code written in the interpreted language. Usually, the amount of code in the interpreter is many orders of magnitude smaller than the amount of code that is written in the interpreted language. With C/C++ code, the entire code base can be suspect, but in an interpreted language, only the tiny fraction of the code making up the interpreter itself is suspect. When the code base that can have such problems is a lot smaller, the total number of actual problems is smaller. The greater part of my experience with interpreted languages is with Perl 5.8, which is used by tens or hundreds of thousands of programmers regularly, and whose source code is scrutinized and worked on by probably several dozens of people each day. Perl 5.8 also has a large and comprehensive unit test suite, including regression tests. It is very hard for bugs to get by it. Let me know if you're aware of any security vulnerabilities with Perl 5.8. The Parrot project also has strong regression testing, though that project is pre-alpha. As for PHP, well I consider that specifically to be less elegant than many other interpreted languages, and much more likely to be buggy. This has a lot to do with its ad-hoc design principle, just throwing in features wherever, rather than having a more thought out system design. All of the above being said, you do gain a lot of the same advantages of interpreted languages in C/C++ in regards to bugs and security when you use a library that is already well written to handle common tasks, as some other posters here mentioned. In that respect, the interpreted language is alike, since you are implicitly using libraries rather than explicitly. -- Darren Duncan
Re: [sqlite] Thanks!
At 7:27 AM -0800 3/3/05, Jay wrote: I believe writing C or C++ code is harder than writing interpreted code. My aim has always been to produce the best product I could, not to produce it with as little effort as possible. I hope the extra effort was worth it. I guess time will tell if I chose correctly. One caveat of languages like C and C++ is that you are opening yourself to several classes of potential security problems that interpreted languages tend not to have. You have to deal with things like buffer overflow attacks, one of the more common kind, that can come from you not crossing every "t" and dotting every "i" with your memory management. Interpreted languages take care of these sorts of things for you, so they are orders of magnitude more secure by default. You have to put in a lot of effort with C and C++ to be just as secure. (Sure, the interpreter engines themselves could have such problems, but they are exceedingly rare due to their common code being heavily used and examined by a relatively large group.) The main advantages of C is that you can get the smallest possible footprint and greatest possible speed; it is best applied to what would be bottlenecks. -- Darren Duncan
[sqlite] new API for query column sources (was Re: ticket 1147)
Here are some alternate API naming suggestions, that I have thought through at length and believe will work. Since the proposed new functions are all related and talk about the source table or view columns for the query, they should all have the word 'source' in their names. Here are my suggestions for new functions (and we keep the old ones as they are): sqlite3_column_source() or sqlite3_column_source_column() sqlite3_column_source_table() sqlite3_column_source_database() All 3 of the above functions return null values for a calculated field, and non-null values for a non-calculated field. The first function gives the source table or view field/column name, and is the same as many databases return when you say "select *". The second function gives the name of the table or view; since another name for a "view" is a "viewed table" (see SQL:2003), that name isn't inappropriate when the source is a view. And the third function is the database containing the source table. If desired, pair each one with a second version for UTF16. At 5:33 PM -0700 2/28/05, Dennis Cote wrote: D. Richard Hipp wrote: Wouldn't it be better to provide this information with a new API rather than depend on a column naming convention? That would avoid ambiguity in cases where users create dodgy column names that contain characters like space and '.' Yes, it sure would be better to use an API. The second would naturally become sqlite3_column_table(), and the fourth sqlite3_column_database(). Unfortunately, the natural name for the third item, sqlite3_column_name(), is already used. However, rather than the column name, it returns the usual column heading. This is sometime the column name alone, and sometimes the table name and the column name separated by a period, depending upon the type of query, joined or not, and the column name pragma settings. It's too bad this wasn't called sqlite3_column_heading(). I disagree with your assessment about the existing use of sqlite3_column_name(), and think that function should stay the way it is. My reason is that the common thing between all the "sqlite3_column" functions is that they refer to a RESULT column for a select query. Since the QUERY is the primary subject under discussion, it makes sense that sqlite3_column_name() refers to the name of the result column, which is determined either by the AS clause or other default rules. Calling this sqlite3_column_heading() is inappropriate when you consider that most of the time this value is used as a primary identifier for a query result column, for example being used as a hash key. Anyone agree or disagree with my suggestions? -- Darren Duncan
[sqlite] dis-ambiguating compound queries (was RE: ticket 1147)
At 12:29 PM -0500 2/28/05, D. Richard Hipp wrote: What about the result set of compound selects or of natural joins where the origin column is ambiguous? If knowing the original column is so important, what do people do with those cases? Since this was brought up, I'll answer it as a separate thread. The short answer is that the person writing the SQL needs to do a bit more work, and explicitly define an extra result column whose value differs for each member of the compound query, so one knows which said member the row came from. For example: SELECT 'first' AS member, foo, bar, baz FROM table_one UNION SELECT 'second' AS member, foo, bar, baz FROM table_two As for natural joins ... by definition a natural join combines columns that have the same name and equal values in every row; because of this, it is known that each returned value returns to both source columns. -- Darren Duncan
Re: [sqlite] ticket 1147
At 8:32 AM -0500 2/28/05, Clay Dowling wrote: D. Richard Hipp said: On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: I understand that this "column names" issue is becoming a pain for the sqlite authors, but OTOH, it is very important for wrapper authors... Why? Why does anybody care what the column names in the result are? What are the column names used for other than to print a header at the top of a table for human-readable output? I'll second Dr. Hipp's statement. I have my own wrapper that I've written for internal use and seem to be getting along just fine with column names as they are. I do expect the developer (me, in this case) to be smart enough refer to a column by the same name both when setting the SQL and when retrieving the results set. I'm curious just why you feel that you need something different than that. I can't imagine that I'd take too kindly to a wrapper that thought it knew better than me what I wanted to call the columns. I'll third Dr. Hipp's statement. I have my own wrappers (in Perl), made for public consumption, and never had problems with returned column names. Simply put, the elegant solution for wrapper authors is to always use 'as' to explicitly define the column names you want. You always know how these names map to original table columns because you explicitly said so. Insisting on using default names all the time is for uber-lazy users. -- Darren Duncan
Re: [sqlite] feature request
At 3:33 PM -0500 2/22/05, albert drent wrote: Reading the 'unsupported features page' there's a feature request about allowing the + and - join syntax like oracle has. It would allow more easy readable and writable queries although I know it's not quite standard. SQLite used to have this (I read) but has been removed. Albert Drent I beg to differ. I find having the join syntax in the FROM clause, which the SQL standard specifies, is a lot easier to understand. The Oracle syntax is a lot more prone to human errors, especially when you have to add redundancy when relating either more than 2 tables or more than 1 column between a pair of tables; eg, you need to have more than one '+' just to specify a single join, or it won't work properly. You even need to put the '+' on non-joining terms like "foo = 'z'" where 'foo' is a field name in a joining table, or the join won't work correctly. What a pain. Moreover, Oracle themselves have seen the light and support the SQL standard syntax in versions 9 and 10+. Please leave things the way they are now, like standard SQL. -- Darren Duncan
Re: [sqlite] Versions 2.8.16 and 3.1.2
At 10:07 PM -0500 2/15/05, D. Richard Hipp wrote: > one of the regression tests fail on 3.1.2: alter-1.8.2... Error: near ".": syntax error That is because I mistakenly included an older version of that test in the tarball. Ignore this error. Do you plan to release a corrected 3.1.2 tarball soon? Or is this a "so minor it will wait until 3.1.3" thing? -- Darren Duncan
Re: [sqlite] [OT] SQL: limit a query by sum(val)?
At 6:50 PM +0100 2/11/05, Philipp Knüsel wrote: select * from t1 where period < '2003-1' order by period desc: -- val periodsum(val) -- --- -- 15 2002-4 15 -- 10 2002-3 25 -- 5 2002-2 30 -- 15 2002-1 45 -- 10 2001-4 -- 5 2001-3 I need only the first records to fulfill sum(val) >= 40 (or all records if sum(val) < 40) -- so the result should be limited to: -- val period -- --- -- 15 2002-4 -- 10 2002-3 -- 5 2002-2 -- 15 2002-1 It appears to me that you have a multi-part problem to solve here. The first part of the problem is that you have to calculate a "running sum", and I don't know if there is any way to do this in a simple manner. Given how SQL works, *if* there were a running_sum() function, it would need to execute after the ORDER BY clause, because a running sum only makes sense in the context of already sorted results. Perhaps then the query might look something like this: SELECT val, period FROM ( SELECT val, period, running_total(val) AS runner FROM t1 ORDER BY period DESC ) AS l2 WHERE runner < 40 ORDER BY period DESC I don't see this being a simple problem yet, in any case. I don't know if the current mechanism for writing your own functions will let you execute them at the necessary time. (The example I gave may be wrong, if the SELECT line executes prior to ORDER BY.) -- Darren Duncan
Re: [sqlite] joining a table to the end of another table
At 9:05 PM + 1/31/05, Richard Boyd wrote: See example below if it's not clear what I'm looking to do: Table 0 Table1 0 | A 5 | F 1 | B 6 | G 2 | C 7 | H 3 | D 4 | E Combined table 0 | A 1 | B 2 | C 3 | D 4 | E 5 | F 6 | G 7 | H I'm sure there's a simple way to do it but I'm not sure how. Richard, try the UNION ALL operator, something like this: SELECT a, b FROM t0 UNION ALL SELECT a, b FROM t1 -- Darren Duncan
Re: [sqlite] Handling database updates in my program updates
At 12:19 AM -0500 1/30/05, Mrs. Brisby wrote: None of this is necessary if you select a durable schema. Whenever you think you need to "add a field" - add a whole new table and use joins anywhere you need access to the new field. You can't "delete" a field, but deleting a field usually means losing data anyway. You can't change the nature of a field without changing the domain that the data exists in. Keeping strict 1NF tables can really help avoid this. Your "update procedure" wouldn't be necessary at all. What you're talking about is only reasonable for minor updates and/or temporary transitional updates. Over the long term, doing that will result in cludges piled on cludges, all sorts of fields in non-optimal locations and all sorts of empty fields, or the same kind of data stored in multiple places etc. It is more elegant to allow for all parts of a schema to change over the long term except perhaps for the smallest amount of control information, and said control table could just have 2 columns that store key/value pairs; eg, one key/value for version or other special clues that tell a program how to deal with the rest of the schema. -- Darren Duncan
Re: [sqlite] Handling database updates in my program updates
If you are looking for longetivity of your program, as I am doing with mine, I would keep and continue to use a Control table such as you speak of. However, this Control table should not contain the database version number, but rather the version number of your own application program. That way, if the database schema that your program uses gets updated over time to support new program features, or you fix bugs in your program that caused data to be stored incorrectly in the older versions, you will have the best chance of importing the data despite schema changes and have the best chance at correcting the data errors, because you can predict what kind of errors there would be based on the old program version the data says it was used with. You could also store the SQlite version in the table if you want, but that would be mainly useful to recover from errors that older SQlite may have introduced, that were later detected. Otherwise, I assume that SQLite itself is smart enough to recognize when SQLite files are or are not compatible. You don't necessarily have to worry about this. -- Darren Duncan At 11:44 AM +1300 1/30/05, Murray Moffatt wrote: I'm creating a shareware program using Visual Basic and SQLite. I'm using the SQLiteDB wrapper. I want to plan how I am going to handle updates to my program that involve modifing the database, and thus I'd like some advice as I'm sure others have faced this problem before. I need to be able to send out an update to existing users which keeps all of their existing data in tact. I imagine that most of the updates will involve adding new fields. At the moment my plan is to have a Control table in the database that includes a field that holds the version number of the database. Then when my program starts up it can check this version against its own version and if the database version is lower then it can tell the user that they need to upgrade their database and automatically run an Update program that will be included with all update releases. The Update program will have an empty copy of the latest database (i.e. tables all set up but no actual data), and will import all the records from the old database into the new one. Then rename the old database (thus keeping a copy in case something goes wrong) and copy the new one in its place. One problem I thought of is what happens if a user skips an update and thus is upgrading from, say, version 1 to version 3. Rather than create a convoluted system where that Update program can convert from any version to any other, I would like to make the Update program as generic as possible, i.e. it reads the old database and matches the fields in it with the fields in the new database and copies that data. In this way it won't expect to see certain fields in certain versions, instead it just sees a Name field in the old database and copies the data found in that field into the Name field in the new database. Obviously all new fields will be empty. Does this sound like a logical way of handling database updates? Does anyone have any suggestions for a better method, or possible pitfalls in my idea? One assumption I'm making is that if I upgrade the verson of SQLite that is used by my program then I assume that newer versions will always be able to read databases created by older versions. Is this correct?
Re: [sqlite] Join two select field together
At 9:49 PM -0400 1/28/05, [EMAIL PROTECTED] wrote: Hi All, I wonder does Sqlite have feature such as join two string fields together. e.g: Select FirstName + LastName From Username Thank you, Ming The '+' is a numerical addition; its result is the sum of 2 numbers; that is not what you want. What you want is '||', the string concatenation operator. Also, all result fields that are calculations should use AS so that their names are reasonable. Eg: SELECT firstname || ' ' || lastname AS name FROM username -- Darren Duncan
Re: [sqlite] Finding duplicate records
At 10:43 PM -0500 1/26/05, D. Richard Hipp wrote: You are right - my original suggestion does not work. I forgot that the EXCEPT operator does a DISTINCT on the result set of both operands. Your approach works much better. This could be fixed by updating SQLite to accept the standard SQL syntax where a "set quantifier" can be used with not only a standard query but a compound query. Meaning: SELECT ... And: SELECT ... [ ] SELECT ... Where: ::= DISTINCT|ALL ::= UNION|EXCEPT|INTERCEPT This way, users can specify what behaviour they want, and get flexability. it should be very simple to implement. Since the is optional, the default behaviour is akin to ALL for regular selects and DISTINCT for compound selects, as SQLite and other databases already do. See SQL:2003, 7.13 "" (p351) for the definition. A slightly modified version of your suggestion would then produce the desired result, a duplicates list: SELECT * FROM table EXCEPT ALL SELECT DISTINCT * FROM table; -- Darren Duncan
Re: [sqlite] Is there any way to enable recursive triggers?
Regarding the risk of infinite loops with triggers ... I believe that SQLite's default case should be to simply let resources define the limits, and stop only when there simply aren't the resources to continue. As with most programming languages, it should be the user's responsibility to not write infinite recursion or iteration in the first place. If the user is doing something that looks like infinite recursion at a casual glance, then we should trust they know what they are doing. The main thing that SQLite should be responsible for is ensuring that any errors in SQL that the user writes will not corrupt the database. If SQLite runs out of resources, then it should terminate and roll back the actions that the trigger did and/or the entire transaction, such as would happen if a unique value or primary key constraint was violated. If possible, SQLite should also manage memory so that it has the resources necessary to roll back the infinite recursion and carry on as normal; SQLite should not crash from an infinity error. Similarly, note that SQLite could run out of resources for many other reasons besides infinite user recursion, so the latter should simply be handled as an instance of the former. So keep it simple and don't try to second-guess the user in the general case. Your code will be by far the simplest. That said, you could add some code for common special cases if you want to give the user a more friendly error message and recover from the situation faster. But these should only be special cases, and not add much complexity to the code. You *could* add a pragma that defines a hard limit for recursion, but that should be possible to disable and/or set to an extremely high number such that the resource limits kick in first. The hard limit would be an optional limiter, and not the main fallback limiter. On a separate matter, in your linked list example, this could be handled a lot more effectively if you supported simple iteration, such as a while-loop; that way, the memory footprint is the same small amount regardless of how many items are in the linked list. Note that the SQL standard defines triggers as being more or less the same as stored procedures as to what they can contain. -- Darren Duncan
Re: [sqlite] Are there any plans to support cursors?
At 2:42 PM -0800 12/29/04, Jackson, Scott M wrote: Hello, It appears that cursors are not supported in SQLite although there appears to be no mention of this fact in the documentation, lists of unsupported features, etc. Is that correct? Are there any plans to support cursors? Thank you! Scott Jackson Pacific Northwest National Laboratory In a manner of speaking, cursors *are* supported already by SQLite. At their most basic, the difference between using a cursor for a query and not using one is that cursors return one row at a time on demand where otherwise you get all the rows immediately. SQLite by design returns one row at a time as you ask for it. The normal way of fetching selected data returns one row at a time. sqlite3_step() I think it's called. On the other hand, if you want cursor features like scrolling backwards or updating the record at the current scroll cursor position, then I don't think those are supported. -- Darren Duncan
Re: [sqlite] ANN: alpha DB sqlite util for MacOS X 10.3
At 11:22 PM -0500 12/13/04, Fernando Morgan wrote: For MacOS X 10.3 users; I was going to start to using sqlite in a project, but I need first to create a skeleton of a browser to add some of my data. If someone wants to use it (disclaimer: version 0.1 software), it's here: http://homepage.mac.com/fernandoluis/.cv/fernandoluis/Public/SquidSQL.zip-link.zip (368k) On starting, it open a file browser to open the db file. If this is cancel, it opens a Save File dialog and can create new db files. If this is also cancelled, well.. there's not much you can do besides Command-Q. What is working: the table browser; it allows for viewing the data and to insert/update and delete rows in the table. Table management (creating tables and dropping them) isn't working yet (some capability to create tables with text fields). Done with SQLite 3.0.8. Fernando Thanks for posting that! I'll be sure to find it useful. -- Darren Duncan
RE: [sqlite] sqlite project--working with table structure
At 5:40 AM -0500 11/20/04, j-marvin wrote: Darren, this is cool. I can't wait to pass this along to a person at my job who works with DB's at his full-time job. Thanks, I appreciate it. Note also that another release of SQL::Routine should be posted on CPAN either today or tomorrow. I had thought wouldn't it be nice if people would agree on the table structue of the create table statement within the sqlite community for all these sqlite db tools popping up. In my own self serving way in part at least because I am not smart thought of the standardization because you just know I will mess up the table relations design. And like most projects who the hell wants the headache of maintaining all the changes. Why cant I borrow someone elses who did it before me who is smarter ;-) I know it sounds incredibly lazy but because of my brain it takes me longer to do things so I often think of things to cut corners to help save time. SQL is already an international ISO/ANSI standard and should be conformed to as closely as possible. If you mean that you want the community to agree on a parsed representation, then I'm not sure what to say. SQL::Routine implements one, which I hope will gain defacto standard usage. I never dreamed a whole language spanning multiple db systems would end up being developed. And if you look at some tools out there I believe the fancy structure change was omitted because of the headache to support my theory. Probably a feature creep decision. SQL::Routine is not tied to the database; it is soley a database description model that is strongly influenced by SQL but is not string-SQL. It should work with any vendor of database on equal terms. SQLite 3 is the first one to be tested, though, any day now. this looks like its required install OS is linux though??? thanks, jim No. SQLite runs on any operating system. The SQL::Routine library also runs on any operating system, including the many flavors of Unix or Linux, and Mac OS, and Windows. -- Darren Duncan
Re: [sqlite] sqlite project--working with table structure
At 3:37 AM -0500 11/20/04, j-marvin wrote: hi- i was hoping to get a little feedback on an idea i had. the create table statement can get complex with its variable number field constraints and table constraints etc. etc. when i first tackled the problem i tried to parse it. now i have a different idea. what if i viewed the create table statement as a group of tables with records . then when i am finished adding records to the structure db tables i could call a routine to write the create table statement based on the structure db database for that particular table. a problem i see with this design is someone with a table already designed would not want to use a program like this because they have all ready generated the table structure in sqlite_master.sql. i'll probably be the only user anyways. i thought i could use delphi personal edition and libsql to create an interface in this manner to insert,update,delete,select table structure data. is this a really bad idea? i have to try something. changing the structure is the worst part of my program :-( thanks, jim Have a look here: http://search.cpan.org/dist/SQL-Routine/ That's part of a project I'm working on. With it, each database table, as well as every other kind of thing you could possibly put in a schema or use with a database, is represented as a cross-referenced hierarchy of atomic-value nodes. You can create and manipulate your "create table statement" by adding, removing, and altering the nodes. Then the create can be generated from them when done. -- Darren Duncan
Re: [sqlite] Bug in CREATE TABLE?
At 1:59 AM +0100 11/20/04, Tito Ciuro wrote: When I create an 'address' table, I pass this string to SQLite: 1) CREATE TABLE address(ROWID INTEGER PRIMARY KEY,First QLString,Last QLString,ZIP QLString,Country QLString); And SQLite returns a series of data, from which you can see: 4) CREATE TABLE address(Last QLString,ROWID INTEGER PRIMARY KEY,First QLString,ZIP QLString,Country QLString); Clearly not what I passed in statement #1. You also see this table called 'address_mem_2', which is fine: 'ROWID' is a special word in SQLite, and implicitly exists for all tables as its primary key, or as an alias to such. SQLite probably stripped it out of your explicit declaration because it was redundant. Go to http://sqlite.org/lang.html and see 'Special Words' at the page bottom. What you should do to fix the problem is use a different column name for your explicitly defined primary key. On a separate matter, what is a 'QLString'? I've never heard of that SQL data type before. -- Darren Duncan
Re: [sqlite] problem: sqlite for PowerPC
At 11:25 AM +0800 11/19/04, user linux wrote: hi, I want to use sqlite in linux OS for IBM PowerPC, but I failed when I cross-compiled in MontaVista CGL pro3.1 platform. I have attentioned that somebody mentioned here he/she has SQLite 3 working on Power PC. Could you please send the sources or diffs? Thanks! SQLite 2 and 3 work for many people on Mac OS X, including me, which is a PowerPC platform. Most people use the GCC line of compilers, such as GCC 3.3. Try using GCC to compile yours and see if it works. -- Darren Duncan
RE: [sqlite] PHP5 && SQLite3
At 8:08 AM -0700 10/19/04, Keith Herold wrote: Out of curiosity, how hard would it be to build a translation layer? I suppose it's easier to keep both versions around, and upgrade when necessary, but as a technical problem, it's at least interesting? Only downwardly, of course, so there would be no expectation that 2.X could use 3.X, just the reverse. It had sounded as if only the file format was the problem; is it more than that? I'm not pushing, in any sense, just wondering if anyone has looked at this issue with an eye to resolving it. --Keith This isn't specific to PHP, but ... I am building a translation layer right now, in Perl. You should be able to, using my library, easily convert SQLite2 databases to SQLite3, or the other way, or between SQLite and various other database products, within the next 2-3 weeks. The library does the standard "scan and write" approach, analagous to a "dump and restore" but not so crude (it translates and emulates SQL too). Go to http://search.cpan.org/ ; look up 'Rosetta' and 'SQL::Routine'. They are pre-alpha, but I should have the functionality you need within 3 more releases of each. My library is also being ported to Parrot asap, so that all the languages which have compilers targeting Parrot (I think someone's even doing PHP) can use it. -- Darren Duncan
Re: [sqlite] DBD::SQLite2 installation on Debian
At 3:56 PM +0100 10/13/04, Slava Bizyayev wrote: I have strange results with sqlite-2.8.15. For some unclear for me reason I can not write to database via the dbish or DBI (DBD::SQLite2). I guess I'm doing something wrong, but I have no idea what exactly... [EMAIL PROTECTED]:~$ dbish dbi:SQLite2:/db/test.db DBI::Shell 11.93 using DBI 1.45 FYI, DBI 1.45 is partially experimental with known problems; DBI 1.43 is the last known stable one. Also, you should bring these issues up on a Perl list, such as dbi-users, rather than the SQLite list, as the issue seems not specific to SQLite itself. -- Darren Duncan
Re: [sqlite] Version 3.0.8
At 9:09 PM -0400 10/11/04, D. Richard Hipp wrote: Version 3.0.8 is now available on the website. http://www.sqlite.org/. The primary change is a series of code size optimizations. There are also some obscure bug fixes and a few minor enhancments. If you are not having problems with version 3.0.7 there is little reason to upgrade. Yeeaay! Thank you. - sayeth I
RE: [sqlite] still having problems with DBD::SQLite2
At 2:21 PM -0500 10/9/04, Freeman, Michael wrote: Well in my discovery, it seems sqlite is just a flat file and cannot be updated by multiple processes/threads/whatever at the same time. You can do selects, but not anything that modifies it. My problem was that I was working in a "threaded" environment where I had multiple threads trying to do inserts at the same time, and that wasn't working out for me. I'd like to work on the DBD::Sqlite2 code sometime when I have time to try and improve its error reporting and extend some of its capabilities. Look for patches to come. Also, on that note, might as well make a DBD::SQLite3. -Mike There is already a DBD for SQLite 3; it is called "DBD::SQLite", with no numerical suffix. The newest release is v1.06, which embeds SQLite 3.0.7. By contrast, DBD::SQLite2 v0.32 embeds SQLite 2.8.15. I strongly suggest that you focus all new development on SQLite 3 (unless you have a pile of legacy v2 data files) since that's where it will benefit the community (and yourself) best. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
At 4:28 PM -0500 10/8/04, Freeman, Michael wrote: I am pretty sure I know whats going on now. I am using POE (Perl Object environment, I highly recommend it poe.perl.org) and what is happening is my program is basically trying to do inserts into the database at the same time, which I think is creating a deadlock. It can handle doing one insert at one time, but when I fire a lot of events at it that are kind of happening asynchronously on the server, it fails. It would be nice if the debugging and logging output made some sort of damn sense or would tell you these things.. I think I have had my head up my ass all day cuz of this. I am going to try do some stuff in my program that will "pause" all the other helper "threads" when I'm doing a sql insert. Make sure that each thread has its own database connection via its own DBI->connect(), assuming that DBI isn't pooling and reusing the connections behind your back. This is analagous to C programs having a separate sqlite open() in each thread, which is necessary. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
At 3:26 PM -0500 10/8/04, Freeman, Michael wrote: Also, another weird error is that if I specify the full path to the database and I have DBI->trace(1); on, it says it can't connect to the database. Without the full path, It just says the trapdlog doesn't exist.. I was going to bring this up too, but the other problem was more glaring. When you do not give a full path, then SQLite will look in your current working directory for the database file. If you are running your program from a shell prompt, then you need to first cd into the directory that contains your database file before running your script. If you are running your program as a web application / CGI script, then your current working directory is probably whatever actual folder corresponds to the web address you invoked to run the program; unless this is where your database file is (and it shouldn't be, for security reasons), then giving an unqualified filename won't work. Your current working directory when running your script is probably different than the one your database is in, hence your problem. Using full paths gets around this because then it doesn't matter what your cwd is. -- Darren Duncan
Re: [sqlite] still having problems with DBD::SQLite2
The problem you are having is that, while your sqlite_connect() code
assumes it is fine to just create a database file if it doesn't exist
(which is what SQLite does automatically), your other code always
assumes that the database file did exist before.
Your other code is trying to update or insert into a table without
first checking that the table exists. And the table won't exist if
the database didn't exist; newly created databases have no tables in
them.
You need to issue a "create table trapdlog ..." statement if the
database was newly created just now, and the table doesn't exist yet,
prior to doing any inserts or updates.
-- Darren Duncan
At 1:02 PM -0500 10/8/04, Freeman, Michael wrote:
I am still having problems with a script trying to use SQLite2. My 4
line test script works fine, but my other code keeps giving me DBI
errors saying it can't find the table. Here is what I get in the DBI
trace.
!! ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
(err#0)
<- execute('1094662322' '3' ...)= undef at logwiz.pl line 377
DBD::SQLite2::st execute failed: no such table: trapdlog(1) at dbdimp.c
line 412 at ./logwiz.pl line 377.
no such table: trapdlog(1) at dbdimp.c line 412 at ./logwiz.pl line 377.
The code I'm using:
my $lite_dbh = _connect();
# prepare the update statement
my $lite_sth_update = $lite_dbh->prepare( q{ UPDATE trapdlog SET status
= ? WHERE node = ? } )
sub sqlite_connect {
# need to add code in here to check that the database exists. if it
does not we
# will create it. *thought*.
my $sqlite_dbh =
DBI->connect("dbi:SQLite2:dbname=trapdlog.db","","")
or die $DBI::errstr;
return $sqlite_dbh;
}
Re: [sqlite] DBD::SQLite2
At 10:32 AM -0500 10/8/04, Freeman, Michael wrote: How come DBD::SQLite2 does not produce a 'sqlite' binary? Because it's not supposed to, and doing so would be redundant. The sole purpose of DBD::SQLite[2|] is to be a plug-in module for the Perl DBI framework, so you can use SQLite via the DBI programming interface. The DBD::SQLite distribution leaves out a few files that are in the usual SQLite distro, including the shell and TCL wrappers, and provides an XS/Perl wrapper instead. If you want a sqlite binary that is used independent of Perl, then you don't use DBD::SQLite on CPAN, but rather the usual distro on sqlite.org. -- Darren Duncan
Re: [sqlite] new Ticket 949: add user-level test for file validity
At 12:22 PM +1000 10/8/04, [EMAIL PROTECTED] wrote: Darren, Are you asking for a pragma integrity_check (which already exists) or are you just wanting to verify the magic string at the beginning of the file? The magic string. I want an elegant way for a user to explicitly check the magic string, that is built into the SQLite library itself, and which means not issuing a superflous select. Personally, I think it would be nice to have some means to say "Open this file if it already exists and is an sqlite file: Don't create it if it doesn't exist". I'm not sure I've found any cases where it is entirely necessary, though. Benjamin. You're absolutely right. In fact, I very much want that too. 1. I want a command that is explicitly for creating a new file, that will fail with an error if the file already exists. 2. I want a command that is explicitly for opening an existing file, that will fail with an error if the file does not exist. 3. While we're at it, maybe an explicit built-in command to delete a database (done by the SQLite library since it knows best how to clean up after itself). Perhaps an elegant solution for this and similar things be to add a third argument to open() called "mode" which simply takes one of a list of defined numerical codes (or alternately, a single bitmask) representing the behaviour we want. Example values could be: 1. "new or fail"; 2. "existing or fail"; 3. "new or existing" (the default). Possibly the thing about checking the magic number could be a fourth and fifth option (to complement 2 and 3), unless that is simply asked for separately. Any thoughts on this idea? -- Darren Duncan
[sqlite] new Ticket 949: add user-level test for file validity
FYI, I added the following ticket today. A copy is also here on the list in case any discussion is necessary. (No replies may be taken as consensus.) -- Darren Duncan - Type: new Version: 3.0.7 Status: active Created: 2004-Oct-07 20:28 Severity: 3 Last Change: 2004-Oct-07 20:28 Priority: 3 Subsystem: Unknown Assigned To: anonymous Creator: anonymous Resolution: Pending Please add a one-step user-level test that one can invoke to determine whether a file is a valid SQLite database. I can understand open() not doing this automatically for performance reasons, but there should be an alternative for users who want behaviour as if it did. Performing a dummy select, the current recommended solution to performing the test, is a very in-elegant solution. 1. One alternative option I suggest adding a new stateless function whose prototype looks like open() but has a name like "is_a_database" or "validate_file" or what-have-you. Conceptually, what this function could do is: open(), perform smarter replacement for dummy select, close(); it would return the code for OK if the given file is a valid database, and the appropriate SQLite error code if it is not. 2. An alternative solution, which could be done instead of or in addition to the above, is a stateful function that you invoke on the structure that open() returns. A user would invoke it right after open(); they then explicitly call close() if testing the file was all they wanted to do, or otherwise they keep it open and do whatever they were going to do, but now they can be confident that any actual work will not fail due to it being an invalid file. 3. A third solution would be to add an optional boolean argument to open() which, if set true, would cause open() to perform the test in question. I suggest that option 2 may be the best one, for multiple reasons, including both flexability, future extensibility, performance, simplicity, and non-interference with other future design plans. I believe that making this change will result in better self-documenting code for users than the current work-around is. I also believe that this change should be very simple to do, especially if you pick option 2. I also request that you implement this in the 2.8.x branch as well, so that applications which can possibly work with both versions of SQLite databases have the same simplicity of testing. Finally, something which you don't have to do, but that my recommended change would make it easier for someone else to do, is that an application could quickly scan a file system (or a single directory at least) and quickly determine which files in it are SQLite databases and which aren't, a list of valid ones it would return for a user or program to select from.
Re: [sqlite] sqlite3 and statement variables
At 11:12 PM -0700 9/22/04, Vladimir Vukicevic wrote: Ah, ok.. I assumed that since the mention of ?N and :N: was explicitly added for version 3 that it would be supported, but testing and looking at the code says otherwise. I'll rework my code to just use unnumbered params then. Thanks! - Vlad The ?N and :N: were part of early/alpha 3.0.x versions, but they were updated to the SQL-2003 standard format of :A for the latest releases. So the current state of things is that SQLite 3 supports '?' and ':A' formats mainly, plus a special addition to help TCL people, plus something else. But the '?' and ':A' are the only ones that are SQL standard; I recommend you use ':A' myself, it's what I would use. -- Darren Duncan P.S. ":A" means "colon followed by an alpha character identifier string".
Re: [sqlite] sqlite3 and statement variables
At 10:20 PM -0700 9/22/04, Vladimir Vukicevic wrote: Howdy, I'm trying to prepare statements using the ?n and/or :N: notation mentioned in the documentation and the sqlite3.h header. However, trying something like "INSERT INTO foo VALUES( ?1 )" results in SQLITE_ERROR, with the error string being 'near "1": syntax error'. Similar results with :1:, I get 'unrecognized token: ";"'. Am I doing something wrong, or are numbered (and named, I guess) parameters somewhat broken? Thanks in advance, - Vlad The documentation for SQLite 3 is very much out of date and needs to be rewritten. In fact, a lot of the site documentation still refers to SQLite 2. So you can't always trust what the documentation says right now. Hopefully a new version of the main documentation will be written soon from the stand point of SQLite 3, with differences in SQLite 2 being mentioned as sidebars. Or have a completely different and completely self-contained set for each of SQLite 2 and 3. -- Darren Duncan
Re: [sqlite] How to 'flatten' query results?
At 10:05 PM +0200 9/22/04, Konstantin Malakhanov wrote: Write a "flatten" aggregate function for sqlite with sqlite3_create_function() or sqlite_create_aggregate() for sqlite3 resp. sqlite 2. This is what I've done for my project. The function will take a field name and append the content of it to some string buffer. Probably interleaving field values with "," At the end, the contents of string buffer is the result of the function. I have seen a more generic implementation of such a function in a built-in MySQL extension; it's called "GROUP_CONCAT". In my mind, some of these might be good to include in the SQL standard. Meanwhile, yes, sqlite3_create_function() is the way to go for now. -- Darren Duncan
Re: [sqlite] Version 3.0.7
At 6:02 PM -0400 9/18/04, D. Richard Hipp wrote: SQLite version 3.0.7 is now available on the website. With this release, SQLite version 3.0 leaves beta and becomes "stable". And I thank-a-you :) -- Darren Duncan
Re: [sqlite] Can I use both libraries?
At 3:41 PM -0400 9/18/04, D. Richard Hipp wrote: Check the website ;-) Well now! Aren't *we* happy as a clam! A new chapter in SQLite history begins today ... -- Darren Duncan
Re: [sqlite] Can I use both libraries?
At 10:04 AM -0400 9/18/04, D. Richard Hipp wrote: That problem was fixed on Sep 6 by check-in [1941]. See http://www.sqlite.org/cvstrac/chngview?cn=1941 While there are issues going on that prevent a move to 'production status' before the end of the month, would it be too much trouble to release a 3.0.7 today as another beta? That would make it easier for people to test the current code prior to the production release. -- Darren Duncan
Re: [sqlite] How to compile without TCL on Vs.Net
At 8:32 AM +0930 9/17/04, Peter Loveday wrote: how can i compile the sqlite3 source without any TCL bindings with Visual Studio 7.1? I simply left out 'tclsqlite.c' and 'shell.c', and the rest compiled fine. You could probably define NO_TCL if you really want the source file in there, but I just removed it. And is ist normal that i get so much conversion warnings? I get conversion warnings also, most of them are harmless, but annoying. I considered doing that myself. But then I looked and it seems that all the tests are written in TCL, so 'make test' would fail just as it does now if your TCL is too old. -- Darren Duncan
Re: [sqlite] counting distinctly
At 9:04 AM -0400 9/16/04, Downey, Shawn wrote:
Hello, I am new to sqlite and I wish to get the count of unique entries
in a particular field. The table is created as follows:
create table tablename
(
field_id char(10) not null ,
fieldname char(100)
);
create unique index findex on tablename
(field_id);
I get an error with the query:
select (distinct fieldname) from tablename;
but this gives me the wrong answer:
select distinct (fieldname) from tablename;
Sorry for such a novice question and thank you for any help.
The various solutions I've seen so far on this list have used either
sub-queries or what looks like non-standard SQL ('count' with an
argument). I have a different solution which should be simpler and
faster to implement, plus a lot more database-portable. This sort of
thing is exactly what GROUP BY is for.
If you want to know all distinct values and their counts, use this:
SELECT fieldname, count(*)
FROM tablename
GROUP BY fieldname
If you only want to know how many instances of one value there are, use this:
SELECT count(*)
FROM tablename
WHERE fieldname = 'foo'
Now don't get me wrong, sub-queries are a very valuable feature; I
just don't believe they should be used when an efficient group by or
join can be used instead. Best tool for the job and all that.
-- Darren Duncan
[sqlite] Re: my own fixes for symbol conflict - encode.c
At 5:25 AM +0100 9/13/04, Matt Sergeant wrote: I don't think encode.c is part of sqlite3 - it certainly isn't relevant now sqlite has proper blob support. I suspect it got left in DBD::SQLite as a remnant of the previous version. That same file is in the 3.0.6 source tarball on the sqlite.org website ( http://sqlite.org/sqlite-3.0.6.tar.gz ); I checked, and that file is identical to the copy in DBD-SQLite 1.05, except for the date stamp inside. That said, the original date stamp was from March or April of 2004, so it may not be used. If what you are saying is true, that it's not part of SQLite 3, then it should be removed from the main SQLite 3 CVS tree, and source tarball. For that matter, there should be an investigation in case any other files in there should be dropped too. Dropping unused-and-not-to-be-used code is good prior to a 'generally available' release, cutting the disk/ram footprint down. What say you, Richard? -- Darren Duncan
[sqlite] my own fixes for symbol conflict - encode.c
Following the application of Matt Sergeant's diffs, I still had some
similar problems. But this time, seeing what kinds of things he
changed, I tracked down and fixed the problems myself.
Below this letter is the diff of my changes, which when used in
addition to Matt's, allowed me to use SQLite2 and SQLite3 together
(for that test anyway) under Mac OS X 10.2.8.
This change is in the SQLite core, the file "encode.c" to be
specific, so someone with commit privileges on sqlite.org will have
to apply it.
In summary, I renamed all occurances of
sqlite_[encode|decode]_binary() to sqlite3_[encode|decode]_binary()
in "encode.c".
FYI, assuming this is accepted into the core (or you can easier redo
it yourself with a quick search-n-replace), it will mark the first
time I actually ever edited and returned changes to the SQLite core,
which to me is special. (And yes, I commit it to the public domain.)
Hopefully this will make it into SQLite 3.0.7, and a corresponding
DBD::SQLite 1.06.
-- Darren Duncan
[S0106000393c33758:/Volumes/Programming/DBD-SQLite-1.05]
darrenduncan% diff encode.c encode-DARREN.c
107c107
< ** or UPDATE statement. Use sqlite_decode_binary() to convert the
---
> ** or UPDATE statement. Use sqlite3_decode_binary() to convert the
123c123
< int sqlite_encode_binary(const unsigned char *in, int n,
unsigned char *out){
---
> int sqlite3_encode_binary(const unsigned char *in, int n,
unsigned char *out){
167c167
< ** This routine reverses the encoding created by
sqlite_encode_binary().
---
> ** This routine reverses the encoding created by
sqlite3_encode_binary().
175c175
< int sqlite_decode_binary(const unsigned char *in, unsigned
char *out){
---
> int sqlite3_decode_binary(const unsigned char *in, unsigned
char *out){
216c216
< nOut = sqlite_encode_binary(in, n, out);
---
> nOut = sqlite3_encode_binary(in, n, out);
222c222
< if( nOut!=sqlite_encode_binary(in, n, 0) ){
---
> if( nOut!=sqlite3_encode_binary(in, n, 0) ){
238c238
< j = sqlite_decode_binary(out, out);
---
> j = sqlite3_decode_binary(out, out);
Re: [sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
At 11:02 AM +0100 9/12/04, Matt Sergeant wrote: On Sun, 12 Sep 2004, Matt Sergeant wrote: > All DBD::SQLite dev is done on OS X (Panther currently). So it should be easy. Ah, ok so Panther fixed the annoying strictness of Jaguar. However I think the following patch should cover all bases: Thank you. I applied those changes and all the install stuff worked. However, there are more outstanding, similar issues: [S0106000393c33758:Documents/Perl Distributions/devworld] darrenduncan% ../perl58 dbd_load_test.pl dyld: ../perl58 multiple definitions of symbol _sqlite_decode_binary /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite/SQLite.bundle definition of _sqlite_decode_binary /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite2/SQLite2.bundle definition of _sqlite_decode_binary Trace/BPT trap So it's good you're running this by me before a CPAN install. BTW, if you *really* can't access your own 10.2.8 machine, then I could probably give you temporary shell access on mine for remote testing. I await the next reply. -- Darren Duncan
Re: [sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
At 12:49 AM +0100 9/12/04, Matt Sergeant wrote: This is just because Mac OSX is fussy - Linux won't complain and will let the latterly loaded symbol supercede. But it's a valid bug in DBD::SQLite2, so I'll fix it in the next release (should be simple). Matt. Now, unless you have a Mac OS X box of your own to test against, please send me a copy of the changed source files (the whole files, not diffs) asap, and I'll test your current distro with those changes. This way, if something else comes up, I can have tested that it works good prior to you uploading it to CPAN. I'll be waiting for them. -- Darren Duncan
Re: [sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
At 4:05 PM -0700 9/11/04, Scott Leighton wrote:
I'm not seeing any such problem here. The following code works
perfectly with DBD::SQLite2 v0.33 and DBD::SQLite v1.05.
You don't 'use' the DBD modules, you use DBI; and it handles loading
of the modules specified in the connect.
Scott
I know how one normally invokes DBD modules. I only used them
directly in order to give the simplest test case. They originally
failed when I tried to open both using the normal DBI interface.
Here's a pared-down example of what I was actually trying. And this
did work before, when the only SQLite on the system was DBD::SQLite
0.31.
use strict; use warnings;
use DBI;
my @working_dbi_drivers = ();
foreach my $dbi_driver (DBI->available_drivers()) {
eval { DBI->install_driver( $dbi_driver ); }; $@ and next;
push( @working_dbi_drivers, $dbi_driver );
}
And the results:
[S0106000393c33758:Documents/Perl Distributions/devworld]
darrenduncan% ../perl58 dbd_load_test.pl
dyld: ../perl58 multiple definitions of symbol _sqlite_busy_timeout
/Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite/SQLite.bundle
definition of _sqlite_busy_timeout
/Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite2/SQLite2.bundle
definition of _sqlite_busy_timeout
Trace/BPT trap
The error messages are the same as before, which is the important
part. Perl dies hard; this isn't a trappable error.
Does the above code sample work on your machine?
-- Darren Duncan
P.S. The above code is part of a larger routine that auto-detects
what data sources are available via all DBI drivers. It calls
DBI->data_sources() for each driver that passes the load test.
[sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
Just now I installed the newest versions of DBD::SQLite v1.05 (3.06) and DBD::SQLite2 v0.33 (2.8.15). They both tested and installed with no problems, along with DBI v1.43 and Perl v5.8.5, all using GCC 3.3 on Mac OS X 10.2.8. However, this SQLite v2 and SQLite v3 can not be used simultaneously as they have symbol conflicts. The one flagged was _sqlite_busy_timeout, but from a quick scan of the offending files there seem to be more conflicts. It all looks like a number of 'sqlite' not being changed to 'sqlite3'. Since the libraries are dynamically linked, you only encounter the problem when trying to dynamically load both of them. Loading just one or the other is fine, doing them both results in an error, no matter which order they are loaded, following the second one. Below this email I give the exact error in a simplest reproduction case. I'm not sure if this problem is in the SQLite core or in the Perl bindings. Has anyone tried to dynamically link the cores both SQLite versions into the same single C program; did similar problems come up? Matt, please publish a newer DBD::SQLite as soon as you can get the pair to pass the following simple test without errors: "use DBD::SQLite2; use DBD::SQLite;"; that's also a good test for any subsequent releases. Note that I tried searching the source code for "_sqlite_busy_timeout" but that only appears in the .o files following compilation; I don't know what names in the source files correspond to those, since they obviously were transformed. Since I don't know whether the problem is in the core or in the Perl bindings, should I file a ticket on SQLite.org for this? Thank you. -- Darren Duncan -- [S0106000393c33758:Documents/Perl Distributions/devworld] darrenduncan% ../perl58 use DBD::SQLite2; use DBD::SQLite; dyld: ../perl58 multiple definitions of symbol _sqlite_busy_timeout /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite2/SQLite2.bundle definition of _sqlite_busy_timeout /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite/SQLite.bundle definition of _sqlite_busy_timeout Trace/BPT trap [S0106000393c33758:Documents/Perl Distributions/devworld] darrenduncan% ../perl58 use DBD::SQLite; use DBD::SQLite2; dyld: ../perl58 multiple definitions of symbol _sqlite_busy_timeout /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite/SQLite.bundle definition of _sqlite_busy_timeout /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite2/SQLite2.bundle definition of _sqlite_busy_timeout Trace/BPT trap
Re: [sqlite] New DBD::SQLite*s
At 4:51 PM +0100 9/10/04, Matt Sergeant wrote: Uploaded to CPAN are DBD::SQLite 1.05 and DBD::SQLite2 0.33 Changes for DBD::SQLite2: 0.33 - Set HAVE_USLEEP appropriately. This massively improves concurrent access to your SQLite DB. Changes for DBD::SQLite: 1.05 - Enabled HAVE_USLEEP where available which should massively improve concurrent usage of DBD::SQLite - Update to sqlite 3.0.6 Hey, just in time! As it stands, I had myself last night just made another round of CPAN uploads. As preparation for the next round, that I am starting to work on today, I would be updating all my dependencies to the newest versions, so I can include yours. With this round, I will start using the new stuff like named host parameters. -- Darren Duncan
Re: [sqlite] Risks involved with using synchronous=OFF (was 'Single INSERT is very slow')
At 9:20 AM +0300 9/7/04, George Ionescu wrote: as Nuno Lucas suggested, I've tried inserting one record using synchronous = OFF. That makes a *huge* difference: 150 ms (synchronous=FULL) vs. 3 ms (synchronous=OFF) !! So I'm asking this: anyone has had any real-word experience with multi-user access and setting synchronous = OFF on Windows NT systems ? I've read the docs and I know the risks, but I was wondering if any of you could share your real-word experience... If the database becomes corrupted after a power failure while inserting records, is there any way of repairing the database? What you probably saw with the 3ms is the time between when you issued the insert command and when control was returned to your app, but the new record was simply in RAM and not on disk. The operating system would have written it to the disk some time later. So in other words, the time is so much faster because the slower action actually did something but the faster action did nothing during the time. The main risk is that your app is thinking the data is saved at a certain point in time, but it actually isn't. -- Darren Duncan
Re: [sqlite] LIMIT does not speed up query execution
Adam, your query using LIMIT and a less-restricting WHERE is slower because you have an ORDER BY clause. ORDER BY is always one of the slowest things you can do in a query because every record returned by WHERE (or HAVING if you're using GROUP BY) has to be compared to every other record for sorting. Only after ORDER BY is run, then LIMIT is applied, because LIMIT doesn't know which records to return until after they are sorted. So to make this faster you either have to make WHERE return fewer rows (better), or let it return more but remove the ORDER BY. -- Darren Duncan At 10:49 PM +0200 9/4/04, hilaner wrote: Hi all! Since my database growed to more than 20 000 records, I have noticed that select limited to a few numer of records by LIMIT takes much more time than select limited to similar number of records by another WHERE condition. I use sqlite_get_table function. In my case I have the following queries: (1) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 ORDER BY col_0 ASC col_5 DESC LIMIT 40 OFFSET 0; (2) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 AND col_6 = 5 ORDER BY col_0 ASC col_5 DESC; And the (2) query executes much faster than (1), even it has another contition in WHERE section. (1) takes nearly the same time like (1) without LIMIT condition (full table select). Is it normal? What can I do to speed up (1) query? (SQLite 2.8.15, one table of 16 columns and more than 2 records, Windows 2000, VC++ 6.0) Regards Adam
Re: [sqlite] Single INSERT is very slow
For a more useful test, please make a second table with 2 fields,
like Test but with a second VARCHAR(10) column. Then compare the
speed of inserting into that table vs inserting into your first one.
After all, if the problem is specific to single-column tables, then
we should get rid of all the other differences in what is being
compared. Please try that and resubmit the results here. Keep in
mind to try both without and with indexes on each field. -- Darren
Duncan
At 8:22 AM + 9/4/04, George Ionescu wrote:
Hello sqlite users,
Hello Dr. Hipp,
while using sqlite v3.0.6, I've discovered that doing a single
INSERT or UPDATE on a table which has only one field is very slow:
CREATE TABLE Test (Field1 VARCHAR(10));
INSERT INTO Test VALUES ('123');
the INSERT statement above takes approx. 150 milliseconds to
complete, which is very slow compared to Access/ADO, which does the
same in 3 milliseconds.
The timings were done on a 2.4 Celeron, 512 RAM, Windows XP.
Is there something I'm missing here or is this the expected
behavior? Timings on other operations (SELECT) yielded expected
results (e.g. faster than Access/ADO).
Best regards,
George Ionescu
Re: [sqlite] Locking in 3.0.5
At 6:06 PM -0400 9/1/04, D. Richard Hipp wrote: I fear that your patch has been overcome by events. A subtle bug has been uncovered in another area of locking which is going to require reworking large sections of the commit/rollback logic. It is very doubtful that your patch will survive this rework. I am assuming, then, that the next release of SQLite will also officially be beta status, even though the web site says the current one is probably the last. Staying beta is really the only option when making such a large change, as I see it. That is, unless 'beta' simply means that the API is not frozen yet. -- Darren Duncan
Re: [sqlite] How to determine db file version?
At 7:53 PM -0400 8/29/04, D. Richard Hipp wrote: Marco Bambini wrote: It is safe to read the first N bytes in a db file and check for the string "** This file contains an SQLite 2..." or the string "** This file contains an SQLite 3..." to determine if the db was created with the 2.x or 3.x version? Is there a better method? I think that works. Though the correct prefixes are: Version 2.8: [** This file contains an SQLite 2.1 database **] Version 3.0: [SQLite format 3.] I propose that a simple validation function is added to both the 2.8 branch and the 3.0 branch of SQLite that someone can invoke like open() except that it simply returns true or false if the target file is a valid SQLite 2/3 file or not. I would like a way for the SQLite library itself to tell me this in a simple and quick way; I don't want to have to first open a dubious file and then try to run SQL against it before I find out the file is invalid. This function doesn't have to be thorough; it could simply check for both the above string and the magic number. I think having to do such a check manually, opening a SQLite file without using the SQLite library, smacks of bad encapsulation. Note that, unless the behaviour of open() itself is changed to do this, this function could be added after leaving beta stage as such an addition is backwards compatible. But it would be nice to have now. One benefit of such a built-in is that I could use it from an application function that auto-detects SQLite database instances, so in case I just want to present the user a menu of local databases available for opening. In Perl, this can be used to help implement DBI's data_sources() function. So shall I start a ticket for this? -- Darren Duncan P.S. Richard, thanks for posting 3.0.5. Matt, I yearn for your adaption of it.
Re: [sqlite] SQLite for large bulletin board systems?
At 1:44 AM -0600 8/28/04, John LeSueur wrote: Also PearDB for PHP has some nice wrappers for a variety of different databases, that make this simpler, and other languages would have similar(at least I think perl does). I'll say it has. Practically every open-source database abstraction layer in use today is cloned from Perl's popular and mature DBI, which came out roughly 10 years ago. This is particularly true for PearDB. In fact, I think Pear in general even advertises itself as being a smaller but more micro-managed clone of CPAN, which is one type of resource that is fairly unique to the Perl community. -- Darren Duncan
Re: [sqlite] How to find out encoding for a table
At 9:06 AM +1000 8/28/04, EzTools Support wrote: No one has answered this query yet. Please can someone help with it. Thanks. try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com EzTools Support wrote: Hello all. I have found that I can create a table and write data to that table as either UTF-8 or UTF-16 by using "PRAGMA encoding...", so that all of the field names and text data are written as the encoding type. I need to be able to query (discover) the encoding of a table when the encoding is unknown. How do I do this? TIA -brett While this doesn't answer your question, I would ask why you need to know this information? SQLite 3 provides APIs for both encodings, so you can just use the one that corresponds to the encoding that your application uses internally, for simplicity. SQLite 3 will internally convert back and forth between the API you use and the encoding used on disk, so you don't have to. Also, unless I'm incorrect, all text in a SQLite database uses the same encoding; you can't choose different ones on a by-table basis. -- Darren Duncan
Re: [sqlite] SQLite for large bulletin board systems?
At 3:45 PM -0500 8/27/04, Larry Kubin wrote: Hello everyone. I am interested in creating a PHP/SQLite powered bulletin board system similar to phpBB. However, I have read that SQLite is best suited for applications that are mainly read-only (because it locks the database during writes). Do you think a SQLite powered bulletin board is a bad idea? How would I go about handling the case where two users are trying to write to the database simultaneously? The appropriateness really depends on how busy your bulletin board will be. If it has hundreds or thousands of people *simultaneously* trying to post, then you may run into problems. Otherwise, for a typical website, such as with no more than a few dozen posts per minute (and most likely a lot less than that), then SQLite should be able to handle the BB fine. SQLite being fast in general should help. Most BB writes are inserts, also, with few-to-none updates or deletes. While the whole DB is locked, the locking period should be milliseconds short, so for typical usage no one should notice slowdowns. Of course, try it and see. -- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
At 2:03 PM -0600 8/26/04, Dennis Cote wrote: Multiple use, single bind named parameters are the preferred way to go. You even say they are "ideal". The implementation cost of this is a trivially more complex than straight positional parameters. A simple loop calling strcmp() to match the parameter name. There are no changes required to the execution engine. This runtime cost of this support is incurred only once when the statement is prepared. There is no additional cost at execution time. It does not require each application to implement (and possibly get wrong) its own code to map between names and numbers. The SQLite code would be developed once, tested, and would work correctly for all applications. Why force all users to reinvent this stuff over and over?. I agree that this would be an ideal situation. And if someone does implement it, they will have my praise too. This should be done during the beta phase, of course. -- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
At 2:39 PM +0100 8/26/04, Matt Sergeant wrote:
I already support sqlite3's numeric placeholders via the standard
DBI API. Switching to non-numeric placeholders will be more complex
(I'll have to use a hash instead of an array to store the
placeholders) but quite doable.
In case I was giving off the wrong idea, I don't mean to lose support
for the positional parameters, but rather to support both posit/named
concurrently.
But yes, the ability to do this would be very powerful, but hopefully
very simple to implement:
...
my $sth = $dbh->prepare(
"SELECT * FROM bar ".
"WHERE baz = :yours OR foo = :mine OR zee = :yours" );
$sth->execute( { 'yours' => 3, 'mine' => 'hello' } );
...
$sth->execute( { 'yours' => 6, 'mine' => 'goodbye' } );
...
-- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
At 11:29 PM -0400 8/25/04, Matt Wilson wrote: Not only backwards compatibility, but standards compliance as well. While I see this issue now closed, following Richard's explanation of how things actually are working now, I'm curious as to where in the SQL:2003 standard it mentions positional host parameters and '?'; please give a reference; I only saw the named host parameters mentioned in the standard. -- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
At 9:34 PM -0400 8/25/04, D. Richard Hipp wrote: Parameters can be in any of three forms: * * * Each parameter is assigned a number. Numbers are sequential from left to right and begin with 1. The parameter number is used to bind values to the parameter. All parameters get a different number, even those with identical names. The sqlite3_bind_parameter_count() API returns the number of parameters in a compiled SQL statement. sqlite3_bind_parameter_name() returns the text of a particular bound parameter. This implementation is very simple and compact. And with the exception of not supporting parameters, the implementation is sufficient, I believe to efficiently emulate all of the behaviors described by Darren and Dennis. Support for can be added in the future if a genuine need appears. That sounds great, Richard, thanks! I suggest that this may be a good time to put out an official 3.0.5 release, which is still *beta* status, so more people can thorougly test the addition of :identifier and removal of [?NNN, :NNN:]. This is a *significant* change. Matt Sergeant, when this happens, please release a corresponding DBD::SQLite 1.05, which provides proper pass-thru access for both the ? and :identifier binding methods. DBI itself declares ? in its standard interface, which each DBD either passes through to a natively supporting DB, or emulates; you probably used to emulate ? with SQLite2, but can do pass-thru with SQLite3. But if the newer DBI doesn't have a corresponding native API for :identifier, that takes a Perl hash ref as an argument, then please have passthru support for that via an extension in appropriate places, particularly having the $sth->execute() argument allowing a hash ref instead of a list. (In any event, I will petition for the standard DBI API to include :identifier support, if it doesn't already.) Also, the SQLite core documentation at http://sqlite.org/capi3.html and other places will have to be updated to include the changes. It does not yet mention the bind_parameter_count/name() functions or :identifier, and does mention the old NNN formats. Thank you very much to everyone for their hard work. Good day. -- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
At 6:31 PM -0600 8/25/04, Dennis Cote wrote: The application should not be setting the mapping between the parameter names and their index numbers. This should be done automatically by SQLite as it parses the SQL statement. As each named parameter is encountered SQLite should scan the parameter table to see if this name has already be assigned an index number. If it has, then that number is used for this parameter in the VDBE code that is being generated. If not, then this is a new parameter which is assigned the next highest unused index number automatically. The reason I suggested having an application function for just explicit mapping is so that we can save on a bloat of bind() functions. There are about 7-10 bind functions now, one for each of the data types of values being bound, and what host params they are being bound to is given as an integer, the second argument. If we ditch the extra function I mentioned, then we will need a set of bind() functions each of whose second argument is the string param name instead of the integer. And twice if we want UTF-8 and UTF-16 versions. A good 15 extra functions. If there is a concern with cost of scanning the table for each named parameter, this could be replaced with a hash based lookup using SQLite's existing hash functions. I doubt that this will beneficial for anything but extreme cases with very many named parameters. In either case this process only takes time when preparing the statement. There is no additional overhead when executing a precompiled statement multiple times. A lookup hash will, of course, still be used by SQLite to map the :ident in the SQL statement and the names given by host_param_name(). And the value for a :foo used more than once in a SQL statement will still be bound exactly once and used multiple times, just as ?N is. So the question is, would we rather have 1-2 extra host_param_name() functions, or 14-20 extra bind() functions? Of course, the 14-20 may actually be better, and if we actually want to do it that way, then it's all great news to me; and that approach does mean exactly one function call by the app instead of 2. (FYI, the named parameters, as given in my #1, and the SQL standard, is all I ever plan to use myself, and if it were to come to that being the only means provided, I would be fine with it. The main advantage of the positional numbered options is speed in some environments, or maybe not.) Would anyone suffer if the plain '?' were just dropped entirely, and :ident + ?N were the only options? Both of the remaining would work great for generated SQL; in fact, with my own SQL generator, named params are dead easy to deal with, but purely positional plain-? ones are an order of magnitude more difficult. Even with hand-coded SQL, plain '?' are a pain in all but the most trivial statements, since programmers have to be really careful to get their lists of bind values exactly matched up correctly with the '?', with additional trouble if we want to edit, and that disallows use of the same value multiple times. Plain '?' is generally a detriment to troubleshooting. So then, if we drop bare '?' entirely, then that will let us conceptually or actually make both the ?N and the :ident into hash keys in a sparse list. The second argument to the existing bind() would no longer be an array index per se. Moreover, the ?N and :ident can be conceptually separate lists, where elements from both can be used in the same SQL statement. The ? or : would just tell SQLite how to treat the characters following, as an integer or identifier. For "backwards compatability", any plain '?' could still be allowed, and be mixed with both other usages, and each '?' occurance would implicitly be the same as ?1, ?2, etc. -- Darren Duncan
