Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
Stefan Keller wrote: Thank you, Tom and Dan, for your constructive answers. To Pavel: My application reads the column types out in order to pretty print the values - as mentioned by Tom - but also to generate a dialog for entering new data (of course combined with INSTEAD OF TRIGGERs). I understand that it's difficult to implement result-types for expressions in general. To me it would be enough if there would be at least one way to assign result-types with a CAST wrapper as I have mentioned before. Does anybody know whom to present this proposal in order remedy this current inconsistency in SQLite? sqlite-dev? Actually, I'd say that the inconsistency is that views have declared types at all. Declared types determine type affinity, which determines what happens when you put data in a column. But SQLite doesn't have updatable views, so typed columns don't *do* anything on them. but you'd have to do it for operators as well, and what type should A * B be if A is SMALLINT and B is UINT? I hadn't thought of operators. As far as I know, mathematical operators (* / + -) only work with integers and reals, Perhaps the result type could be set to numeric or some other superclass. Or, to get real fancy, if the two operands are declared integers, then the result is an integer; if one is real then the result is real. AFAIK SMALLINT and UINT are both treated by SQLite as integer, so the result would be an integer. There would be 3 (or 4) cases to deal with: * String concatenation (||) returns TEXT. * Arithmetic operators (+ - * / %) return NUMERIC. * Bit operators ( | ~) return INTEGER. (BTW, why is there no XOR?) * Relational and logical operators return INTEGER, but you may want to distinguish them with a BOOLEAN type name. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
BareFeetWare wrote: On 04/05/2010, at 3:14 AM, Stefan Keller wrote: But in SQLite if a view column comes from a function result or some computation, then the column type is NULL...!? It's not taking the result-type as mentioned in the manual (http://www.sqlite.org/lang_select.html) - even when I try to do a CAST in the projection clause, like this: SELECT id, name, CAST(salary + 10 AS INTEGER) salaryplus10 FROM mytable; I mean that a VIEW should behave like a (read only) TABLE in any case. = Is there a way to give such columns a type anyway? I've had the same issue. In the end I had to parse my view functions in my own code and look for functions that give a particular type of result. So, for instance, round() gives an integer, round(..., 2) gives a real, concat() gives text. I also look for a cast(... as type) to use that declared type. It's fiddly and I would have hoped SQLite would have at least declared the type if a cast was present, but it seems not. A CAST expression could be assigned a declared type, but it would be difficult to implement it for expressions in general. Not only would you have to declare a type for every function, but you'd have to do it for operators as well, and what type should A * B be if A is SMALLINT and B is UINT? Should relational operators have a declared type of BOOLEAN? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Implementing a CREATE_FUNCTION function
It's convenient to be able to define new functions in C. But sometimes,
it would be *more* convenient to be able to define new functions in
SQL. This could be done by registering a CREATE_FUNCTION() function;
then you could write something like:
SELECT CREATE_FUNCTION('LEFT', 2, 'SUBSTR(?1, 1, ?2)');
My first idea for implementing CREATE_FUNCTION is:
1. Create (if not exists) a table with columns for the function name,
number of arguments, and SQL expression.
2. Add the new function to the table.
3. Call sqlite3_create_function to register the new function.
C doesn't have the ability to create functions at runtime, so the xFunc
parameter would refer to a common global function, which would:
1. Look up the SQL expression corresponding to the SQL function name.
2. Evaluate the expression.
But how do I get the SQL function name from within the xFunc function?
Can I get it from the sqlite3_context object, or do I have to use
sqlite3_user_data()?
Or is there a better approach?
As a first step, I've written an EVAL() function, which supports
parameter binding.
// EVAL(expr [, param]*)
void x_sqlite_eval(sqlite3_context* pContext,
int argc,
sqlite3_value* argv[])
{
sqlite3* pDB = sqlite3_context_db_handle(pContext);
sqlite3_stmt*pStmt = NULL;
const unsigned char* expr= NULL;
char*sql = NULL;
size_t len;
int err;
int index;
if (argc == 0)
{
sqlite3_result_null(pContext);
return;
}
expr = sqlite3_value_text(argv[0]);
len = sqlite3_value_bytes(argv[0]);
// Build the SQL statement SELECT (expr)
sql = malloc(len + 10);
if (sql == NULL)
{
sqlite3_result_error_nomem(pContext);
return;
}
memcpy(sql, SELECT (, 8);
memcpy(sql + 8, expr, len);
sql[8 + len] = ')';
err = sqlite3_prepare_v2(pDB, sql, len + 9, pStmt, NULL);
if (err != SQLITE_OK)
{
sqlite3_result_error_code(pContext, err);
goto EXIT;
}
// bind parameters
for (index = 1; index argc; index++)
{
err = sqlite3_bind_value(pStmt, index, argv[index]);
if (err != SQLITE_OK)
{
sqlite3_result_error_code(pContext, err);
goto EXIT;
}
}
// execute the statement
err = sqlite3_step(pStmt);
if (err != SQLITE_ROW)
{
sqlite3_result_error_code(pContext, err);
goto EXIT;
}
sqlite3_result_value(pContext, sqlite3_column_value(pStmt, 0));
EXIT:
sqlite3_finalize(pStmt);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with conflict resolution in triggers
I'm trying to implement a string pool using views and triggers:
CREATE TABLE StringPool (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Val TEXT UNIQUE
);
CREATE TABLE T (
KeyTEXT PRIMARY KEY,
ValRef INTEGER REFERENCES StringPool(ID)
);
CREATE VIEW V AS
SELECT Key, Val FROM T LEFT JOIN StringPool ON ValRef = StringPool.ID;
CREATE TRIGGER _trg_ii_V
INSTEAD OF INSERT ON V
BEGIN
INSERT OR IGNORE INTO StringPool(Val) VALUES (NEW.Val);
INSERT INTO T
SELECT NEW.Key, ID FROM StringPool WHERE Val = NEW.Val;
END;
The approach seemed to be working fine:
sqlite INSERT INTO V VALUES ('K1', 'String1');
sqlite INSERT INTO V VALUES ('K2', 'String2');
sqlite SELECT * FROM V;
K1|String1
K2|String2
sqlite SELECT * FROM StringPool;
1|String1
2|String2
...until I tried a REPLACE instead of a normal INSERT:
sqlite INSERT OR REPLACE INTO V VALUES ('K3', 'String1');
sqlite SELECT * FROM V;
K1|
K2|String2
K3|String1
sqlite SELECT * FROM StringPool;
2|String2
3|String1
The trigger is replacing the ID from the string pool even though I had
specifically NOT written a REPLACE in the trigger program.
It turns out that this is documented behavior (
http://www.sqlite.org/lang_createtrigger.html ):
An ON CONFLICT http://www.sqlite.org/lang_conflict.html clause may
be specified as part of an UPDATE
http://www.sqlite.org/lang_update.html or INSERT
http://www.sqlite.org/lang_insert.html action within the body of the
trigger. However if an ON CONFLICT
http://www.sqlite.org/lang_conflict.html clause is specified as part
of the statement causing the trigger to fire, then conflict handling
policy of the outer statement is used instead.
...and that other people have been bitten by it:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg01668.html
http://www.mail-archive.com/sqlite-users@sqlite.org/msg21206.html
So, my questions are:
(1) Why was it done this way? Is there an example in which SQLite's
behavior here is desirable?
If this is indeed a bug, then it seems to be a trivial one to fix:
85432c85432
pParse-eOrconf = (orconf==OE_Default)?pStep-orconf:(u8)orconf;
---
pParse-eOrconf =
(pStep-orconf==OE_Default)?(u8)orconf:pStep-orconf;
(2) How, other than changing the SQLite source code, could I make my
trigger work the way I want it to?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CHECK constraints and type affinity
If I write
sqlite CREATE TABLE T1 (N INTEGER CHECK(N = 0));
the constraint is applied AFTER converting N to an integer.
sqlite INSERT INTO T1 VALUES('42');
sqlite INSERT INTO T1 VALUES('-5');
SQL error: constraint failed
But if I write
sqlite CREATE TABLE T2 (N INTEGER CHECK(TYPEOF(N) = 'integer'));
the constraint is applied BEFORE converting N to an integer.
sqlite INSERT INTO T2 VALUES(17);
sqlite INSERT INTO T2 VALUES('18');
SQL error: constraint failed
Why the inconsistency?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] String interning using foreign keys: How to write an INSERT trigger?
We have a e-mail program that uses a table like: CREATE TABLE Emails ( MessageID TEXT, FromAddr TEXT, ToAddr TEXT, -- ... ); The database takes up hundreds of megabytes of disk space. In order to avoid the duplication of storing the same addresses thousands of times, I'm planning to replace this with something like: CREATE TABLE Addresses ( ID INTEGER PRIMARY KEY, Address TEXT UNIQUE ); CREATE TABLE Emails ( MessageID TEXT, FromAddrRef INTEGER REFERENCES Addresses(ID), ToAddrRef INTEGER REFERENCES Addresses(ID), -- ... ); -- Emulate the SELECT behavior of the old table. CREATE VIEW EmailsView AS SELECT MessageID, AF.Address AS FromAddr, AT.Address AS ToAddr, -- ... FROM Emails LEFT JOIN Addresses AF ON (FromAddrRef = AF.ID) LEFT JOIN Addresses AT ON (ToAddrRef = AT.ID); I'd like to be able to write INSERT statements on this view like on the old version of the Emails table. Something like: CREATE TRIGGER TriggerUpdateEmailsView INSTEAD OF INSERT ON EmailsView BEGIN INSERT OR IGNORE INTO Addresses VALUES(NULL, FromAddr); INSERT OR IGNORE INTO Addresses VALUES(NULL, ToAddr); -- The statement below is invalid syntax. INSERT INTO Emails ( MessageID, (SELECT ID FROM Addresses WHERE Address=FromAddr), (SELECT ID FROM Addresses WHERE Address=ToAddr) ) END; How do I do this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quoting strings for SQLite
Roger Binns wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 04/18/2010 11:03 PM, Dan Bishop wrote:
I've written a quoting routine too, in C++. I just truncated strings at
the first NUL character because I didn't think that SQLite supported them.
SQLite supports some weird things. In addition to supporting nulls in
values, it also supports zero length strings as identifiers. For example:
create table ();
The other special case I remember is floating-point infinity: I used
9.9e999 and -9.9e999 for those.
Good reminder - I hadn't bothered with infinity and nan and my tests.
Don't worry about NaNs: SQLite converts them to NULL.
CAST(X'43440045' AS CHAR)
You don't have to write it all in hex. You can just do
'CD' || x'00' || 'E'
That only works for UTF8 encoded databases. If the database is UTF16
encoded then it completely messes up. Only a single null byte is inserted
which causes all the remaining ones to be shifted off by one.
sqlite insert into foo values('CD' || x'00' || 'E');
sqlite select cast(x as blob) from foo;
INSERT INTO table VALUES(X'4300444500');
sqlite select typeof(x) from foo;
text
sqlite select length(x) from foo;
4
This is a pretty bad result as the text is an odd number of bytes long -
something that is in no way valid UTF16. I'll bug report the issue.
My suggestion has the same issue: You have to write x'' for UTF-16.
Sadly.
For this case, I think the simplest approach would be to ignore BLOBs
altogether and register a CHR function.
Is that part of the SQL standard? I think I might also be able to abuse
replace - experiments will be needed.
AFAIK, it's not part of the standard, but several major SQL
implementations have a CHAR or CHR function.
http://www.coderecipes.net/sql-char-chr-function.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quoting strings for SQLite
Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/17/2010 07:12 PM, Dan Bishop wrote: Newlines, backslashes, and double quotes can be included literally. The only other character you need to worry about is NUL. Funnily enough I'm busy writing my own quoting routine right now (the source are Python objects so I can't reuse the one in SQLite). I've written a quoting routine too, in C++. I just truncated strings at the first NUL character because I didn't think that SQLite supported them. The other special case I remember is floating-point infinity: I used 9.9e999 and -9.9e999 for those. The only sensible way I can see of producing a string literal with NUL (which SQLite quite happily supports) is to output something like: CAST(X'43440045' AS CHAR) You don't have to write it all in hex. You can just do 'CD' || x'00' || 'E' which works because || automatically converts its operands to strings. Unfortunately the bytes have to be in the same encoding as the database but when generating this you don't know what the encoding is or will be for a dump. My suggestion has the same issue: You have to write x'' for UTF-16. The SQLite shell dump command just ignores the NUL onwards silently losing data. Even if it didn't interpret the NUL as terminating the string, you'd still have the problem of the NULs themselves being invisible. I guess the only other alternative is to register a function that does the blob to string conversion taking into account blob encoding but that will then only work in SQLites where the function is registered. For this case, I think the simplest approach would be to ignore BLOBs altogether and register a CHR function. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quoting strings for SQLite
Simon Slavin wrote: I am using a particular program which needs to be able to mess with an already-established database. It has to issue UPDATE and INSERT commands using one string for the entire command: no opportunity for binding. So it has to assemble commands by concatenation. In order to do this properly I need to know how to 'quote' a string to make it safe for inclusion in a command. In the following I am not worrying too much about BLOB fields: it would be nice if they worked but I can afford to ignore them if they're difficult. SQLite uses single quotes for surrounding a string in a command. Single quotes inside the string should be doubled. I can do that. Is there anything else I need to worry about ? Return and newline characters ? Escape characters ? Double quotes ? Newlines, backslashes, and double quotes can be included literally. The only other character you need to worry about is NUL. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
Kevin M. wrote:
I have a C/C++ application in which I want to store data from a struct into a
table (using SQLite 3.6.23) and later retrieve data from the table and store
it back in the struct. But, I need a general interface for this as there are
many types of structs used. So, what I have is a function that accepts a
pointer to the struct (ptr), the struct size, and a sqlite3_stmt* variable
for the current row. I then iterate through each column of the row and store
the value retrieved at ptr's address. Then I increment ptr based on the size
of the column. Thus, as long as I store and retrieve columns in the same
order I should be storing the right values in the right variables for a
particular struct instance.
However, this method breaks down if a store a 16-bit integer value like 99
and SQLite stores it internally as an 8-bit value (to save space) and
subsequently retrieves it and gives me a value of 1 for
sqlite3_column_bytes(). This causes alignment issues with the variables in
the struct. So, is there a way I can tell SQLite to preserve the data size
in a particular column? E.g.:
CREATE TABLE test ( val1 INTEGER, val2 INT2, val3 INT1 ... );
Here val1 is always 4-bytes, val2 is always 2 bytes, and val3 is always 1
byte.
I'd prefer a way to do this without having to cast every last column in a
SELECT query to the right size.
The layout of a column is an implementation detail and not part of
SQLite's type system. I wouldn't recommend depending on it.
You might want to consider writing a user-defined function to convert a
column row into a BLOB that matches your struct layout. Then you can
just write
SELECT PACK('ihb', val1, val2, val3) FROM test
and memcpy the resulting BLOB into your struct.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dynamic typing misunderstanding
Igor Tandetnik wrote:
eternelmangekyosharingan
eternelmangekyosharin...@gmail.com
wrote:
I create the following table:
sqlite create table t1(a);
sqlite insert into t1 values(123456789.123456789);
I ran the following commands:
sqlite select * from t1;
123456789.123457
sqlite select typeof(a) from t1;
real
What I expected to get is:
sqlite select * from t1;
123456789.123456789
sqlite select typeof(a) from t1;
text
since the conversion of 123456789.123456789 to real induce some loss.
http://www.sqlite.org/datatype3.html
For conversions between TEXT and REAL storage classes, SQLite considers the
conversion to be lossless and reversible if the first 15 significant decimal
digits of the number are preserved.
So no, by SQLite's definition, there is no loss.
That definition doesn't work for denormals:
sqlite CREATE TABLE T (x REAL);
sqlite INSERT INTO T VALUES ('5.12345678901234567890e-324');
sqlite SELECT x, TYPEOF(x) FROM T;
4.94065645841247e-324|real
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graphs and sql
Robert Citek wrote: Hello all, Does anyone have any recommendations for books or other resources that deal with working with graphs (i.e. vertexes and edges) using sql? For example, if I could store a graph in a sqlite database, I'd like to query the database to know if the graph contains a Eulerian path[1]. [1] http://en.wikipedia.org/wiki/Eulerian_path I don't think that SQL is the best language for working with graphs, but: CREATE TABLE Graph ( NodeFrom INTEGER, NodeTo INTEGER ); -- Seven Bridges of Königsberg BEGIN; INSERT INTO Graph VALUES (1, 2); INSERT INTO Graph VALUES (1, 2); INSERT INTO Graph VALUES (1, 3); INSERT INTO Graph VALUES (2, 3); INSERT INTO Graph VALUES (2, 4); INSERT INTO Graph VALUES (2, 4); INSERT INTO Graph VALUES (3, 4); COMMIT; CREATE TABLE Degrees AS SELECT NodeFrom AS Node, COUNT(*) AS Degree FROM (SELECT NodeFrom, NodeTo From Graph UNION ALL SELECT NodeTo, NodeFrom FROM Graph) GROUP BY NodeFrom; -- Find the number of odd nodes -- If 0, there is an Eulerian circuit/tour -- If 0 or 2, there is an Eulerian path/walk SELECT SUM(Degree 1) FROM Degrees; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import a other database. Search for the fastes way
Carsten Giesen wrote:
Now my question.
I work on a way to have a cache DB from the Main Server on the PC of my
client.
In the case the server is down they can go on working. (Like Outlook and
Exchange)
For the first time I have to copy a lot of data from the main server.
In the moment I do it Table by Table, and Row by Row in a for each loop.
An then INSERT INTO {table} (Field..) values (Item.)
Is the a faster way?
Put a BEGIN TRANSACTION before your loop and a COMMIT TRANSACTION
after it. Otherwise, each INSERT statement is its own transaction,
which is *much* slower.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import feature requests
Simon Slavin wrote: On 14 Dec 2009, at 5:13am, Walter Dnes wrote: The following might be options (compile time, config file, set manually; I don't care), but they should be available... It might be worth writing a separate sqlite3 import facility which just reads a .csv into a table. It could have some command-line switches (options ?) or the first time it comes across an ambiguous value it could stop and ask the user what the user wants. http://www.mail-archive.com/sqlite-users@sqlite.org/msg46507.html Feel free to modify the code as needed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
Simon Slavin wrote: On 13 Nov 2009, at 3:30am, Roger Binns wrote: Simon Slavin wrote: Integers in all languages I'm aware of are not stored as mantissa/exponent, they're stored as bits with complete precision. There is one huge exception I found out the hard way recently: Javascript stores all 'integers' as floating point and so Javascript integers lose precision. For example if you try to use 9223372036854775807 in Javascript it will keep coming back as 9223372036854776000. JavaScript doesn't have an integer type, just a number type: var myVariable = 42 document.writeln ( myVariable is a + typeof myVariable ) Microsoft Excel has a similar problem. I ran into it back when I was working in a credit union and tried to import a CSV file containing credit card numbers. Wouldn't have noticed except that credit card numbers are 16 digits long and double only has 15 digits of precision. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feature proposal - strong but dynamic typing
Darren Duncan wrote: Roger Binns wrote: In fact this support might even be easier as it may only require enhancements to the SQL parser, which would generate VM opcodes like for a CHECK constraint, unless further work is done to optimize for the presented cases, or to enhance semantics. It has been stated in several places that the increasing items in the parser will lead to various fields needing to go to the next size up which would increase memory consumption. There is also the whole backwards compatibility angle - what would happen if the database was loaded into an older version of SQLite which then ignored this whole UNIVERSAL thing allowing wrongly typed data to be inserted? An added type name like UNIVERSAL would be completely backwards compatible because, as far as I recall, if SQLite currently sees a type name it doesn't recognize, then the column has no affinity and will accept any value, so same behavior. And so then, for older systems using that keyword would be effectively a documentation convention. This is not correct. See http://www.sqlite.org/datatype3.html, section 2.1: The type affinity of a column is determined by the declared type of the column, according to the following rules: 1. If the datatype contains the string INT then it is assigned INTEGER affinity. 2. If the datatype of the column contains any of the strings CHAR, CLOB, or TEXT then that column has TEXT affinity. Notice that the type VARCHAR contains the string CHAR and is thus assigned TEXT affinity. 3. If the datatype for a column contains the string BLOB or if no datatype is specified then the column has affinity NONE. 4. If the datatype for a column contains any of the strings REAL, FLOA, or DOUB then the column has REAL affinity 5. Otherwise, the affinity is NUMERIC. If a table is created using a CREATE TABLE table AS SELECT... statement, then all columns have no datatype specified and they are given no affinity. #5 means that your UNIVERSAL type is not backwards-compatible, and #3 means that it is not necessary. Your proposal would break any databases that store strings or blobs in columns with unrecognized types. In particular, it would break all the tables I have that store timestamps as text in columns declared as TIMESTAMP. We could deal with this by adding a new affinity rule: If the datatype for a column contains either of the strings DATE or TIME, then the column has DATETIME affinity. A column with DATETIME affinity behaves in the same way as a column with NUMERIC affinity, except that in strict affinity mode TEXT and BLOB values are allowed only if they are valid time strings (as determined by the datetime() or julianday() function). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Late data typing. Am I missing something?
John Crenshaw wrote: Strings have a number of other disadvantages in this case. They take more computations to compare, they take time to parse when you read them, and they take longer to build when you insert them. Generally, storing dates as a number of some sort is ideal. I do agree with that. The problem is that the ideal way to store a date is different from the ideal way to *display* a date. And that the conversion between the two has to be done manually. Building a query to return the value as a human readable string is fairly easy: SELECT datetime(2455133.71759947); -- returns '2009-10-29 05:13:20' Yes, it's easy to do that -- *if* you know that the number *is* a date. This is fine as long as you always view your data with application-specific tools and never with generic ones. I imagine the timezones aren't documented, because they aren't actually stored if the Julian format is used internally (they have to be converted to get the Julian in UTC.) If you use a string, it can store the timezone I guess, but it will cost you in terms of speed. That's not the point. Timezones can't be part of the return value of the strftime/datetime/julianday functions, but it's still worth mentioning that they can be part of the parameter. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Late data typing. Am I missing something?
John Crenshaw wrote: *if* you know that the number *is* a date. If the column has a type of timestamp, it should be safe to always assume that it IS a date. sqlite CREATE TABLE t1 (StartTime TIMESTAMP, Duration REAL); sqlite CREATE TABLE t2 AS SELECT StartTime, StartTime + Duration / 86400.0 AS StopTime FROM t1; sqlite SELECT sql FROM sqlite_master WHERE name = 't2'; CREATE TABLE t2(StartTime TIMESTAMP,StopTime) What column type? Don't put non-dates in it, and there isn't a problem. I can't imagine a case where you would not know whether the value is a date. Am I missing something here? Consider the case of an application using an SQLite database to store its settings (like the Windows registry, but portable). The dynamic type system is great for this. CREATE TABLE Settings (Name TEXT PRIMARY KEY, Value BLOB); NameValue - --- LogPath 'E:\log\FooApp' MaxAttachmentSize 250 LastUpdate 2455130.1125 Now, in the SQLite equivalent of regedit, how is it supposed to know that LastUpdate is timestamp 2009-10-25 14:42:00 but MaxAttachmentSize is NOT the date 2132-08-31 12:00:00? Without knowledge of the application that created this table, it can't. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to input a double num?
liubin liu wrote: Now I use the sqlite3_mprintf() and the %f to get the double num. My code is below. Now there is a num like 212345678901234567890123456.988290112. With the way of sqlite3_mprintf() and %f, the num is cut to 2123456789012346000.00. How to input the num 212345678901234567890123456.988290112? You can't. A double only has 53 bits (about 16 decimal digits) of precision. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Late data typing. Am I missing something?
John Crenshaw wrote:
SQLite has plenty of date editing routines. Dates are stored in a double
as a Julian date.
Well, that's one way of doing it. I store them as strings because I
wanted a human-readable format. The downside is that this requires 19
bytes instead of 8. I wish SQLite could handle the storage optimization
behind the scenes.
SQLite's understanding of dates is capable of
supporting null, date, time, or datetime. The only real problem is that
timezone is not stored, dates are always stored and retrieved in UTC,
and dates with timezones are converted prior to storage.
Wow! I didn't realize that SQLite supported timezones, but sure enough,
it does:
sqlite select datetime('2009-10-28T22:54:52-05:00');
2009-10-29 03:54:52
Why isn't this documented at http://www.sqlite.org/lang_datefunc.html ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Late data typing. Am I missing something?
Jay A. Kreibich wrote: -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen Sent: Wednesday, October 28, 2009 3:11 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Late data typing. Am I missing something? You can get close if you put some check constraints on the columns. On Wed, Oct 28, 2009 at 03:24:34PM -0400, John Crenshaw scratched on the wall: Yeah, the code is fortunately all there, so once you know what you're looking for it is easy to copy out, but it should have been exposed in the API. No, no... Something like this, in SQL: CREATE TABLE t ( i INTEGER CHECK (typeof(i) = 'integer') ); This allows strong type checking on just the columns you care about. Just be aware that it also prevents NULLs. What's stopping you from adding OR i IS NULL to the CHECK constraint? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar of X is Y
John Crenshaw wrote: Yeah, I tend to agree that null != null is confusing, But SQL doesn't have NULL != NULL. It has NULL != NULL IS NULL. That makes it even more confusing. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTERSECT?
P Kishor wrote: I don't even know how to title this post, and it just might be something very obvious. Either way, I apologize in advance. Consider the following -- sqlite SELECT * FROM foo; a b -- -- 1 6 2 6 2 3 3 3 3 4 3 5 4 7 4 8 5 3 5 4 6 9 6 10 7 11 7 12 8 13 8 14 9 7 9 15 9 16 10 17 I want the values of ‘a’ for which ‘b’ = 3 AND ‘b’ = 4 (This is just an example. In reality, b could be any set of numbers, not just two numbers). To illustrate -- sqlite SELECT * FROM foo WHERE b = 3; a b -- -- 2 3 3 3 5 3 sqlite SELECT * FROM foo WHERE b = 4; a b -- -- 3 4 5 4 So, I really want only the following rows 3 3 5 3 3 4 5 4 I don’t want 2 3 because there is no 2 4 sqlite SELECT * FROM foo WHERE b = 3 AND b = 4; sqlite SELECT * FROM foo WHERE b = 3 OR b = 4; a b -- -- 2 3 3 3 3 4 5 3 5 4 sqlite SELECT * FROM foo WHERE b IN (3, 4);; a b -- -- 2 3 3 3 3 4 5 3 5 4 sqlite SELECT * FROM foo WHERE b = 3 ... UNION ... SELECT * FROM foo WHERE b = 4; a b -- -- 2 3 3 3 3 4 5 3 5 4 I guess I need something like sqlite SELECT * FROM ideas_tags WHERE tag_id = 3 ... INTERSECT ... SELECT * FROM ideas_tags WHERE tag_id = 4; sqlite You can do it with a self-join: SELECT foo.a, foo.b FROM foo INNER JOIN foo foo2 ON foo.a = foo2.a WHERE (foo.b = 3 AND foo2.b = 4) OR (foo.b = 4 AND foo2.b = 3) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sparse matrix in scientific computing with sqlite
Michael Chen wrote: --this is my first version for the purpose of storing sparse numerical matrix in sql --please let me know how to fix the bug at the end of the file, and how to tune the performance --or any better reference, thanks! .explain-- return result in more readable format .echo on-- echo the sql stmnt --I plan to store my sparse matrix (3 x 4) in table like this: -- [ 1 2 0 0 ] -- [ 0 3 9 0 ] -- [ 0 1 4 0 ] A sparse matrix can be stored in a table like CREATE TABLE Matrix ( Row INTEGER NOT NULL, Col INTEGER NOT NULL, Value REAL, PRIMARY KEY (Row, Col) ); With this representation, arithmetic on matrices can be done as: -- MatrixA + MatrixB SELECT Row, Col, SUM(Value) AS Value FROM (SELECT * FROM MatrixA UNION ALL SELECT * FROM MatrixB) GROUP BY Row, Col; -- MatrixA - MatrixB SELECT Row, Col, SUM(Value) AS Value FROM (SELECT * FROM MatrixA UNION ALL SELECT Row, Col, -Value FROM MatrixB) GROUP BY Row, Col; -- MatrixA * MatrixB SELECT MatrixA.Row AS Row, MatrixB.Col AS Col, SUM(MatrixA.Value * MatrixB.Value) AS Value FROM MatrixA INNER JOIN MatrixB ON MatrixA.Col = MatrixB.Row GROUP BY Row, Col; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Infinity
I've noticed that I can use IEEE Infinity values in SQLite by writing any literal too big for a double. sqlite CREATE TABLE foo (x REAL); sqlite INSERT INTO foo VALUES (9e999); -- +Inf sqlite INSERT INTO foo VALUES (-9e999); -- -Inf sqlite INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets converted to NULL sqlite .null NULL sqlite select * FROM foo; Inf -Inf NULL sqlite SELECT * FROM foo WHERE ABS(x) = 9e999; Inf -Inf Is it true on all platforms that 9e999 = Infinity and CAST(9e999 AS TEXT) = 'Inf'? What's the preferred SQL syntax for infinity? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C code spanish character insert problem
Bible Trivia Extreme wrote:
On Tue, Sep 29, 2009 at 9:48 PM, Simon Slavin
slav...@hearsay.demon.co.ukwrote:
On 30 Sep 2009, at 2:25am, Bible Trivia Extreme wrote:
Is there something special I need to do in the C/Sqlite
code to make this work properly?
Which SQLite function calls are you using to run your INSERT commands ?
Simon.
My psuedo code is as follows:
sqlite3_open(...);
sqlite3_exec(db, PRAGMA encoding = UTF8-8, 0, 0, 0);
char *query = create table questions(q text, ans text);
sqlite3_exec(db, query, NULL, NULL, errMsg);
(loop over spanish .txt file here while inserting)
sprintf(query, INSERT INTO questions VALUES('%s', '%s'), question,
answer);
end of loop
sqlite3_close(db);
Open your spanish.txt file in a hex editor. The letter 'ñ' should be
encoded as C3 B1. If you see F1 instead, it means your file is in
ISO-8859-1 or something similar.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C code spanish character insert problem
Bible Trivia Extreme wrote: Open your spanish.txt file in a hex editor. The letter 'ñ' should be encoded as C3 B1. If you see F1 instead, it means your file is in ISO-8859-1 or something similar.Thanks Dan, it seems to be F1. So what do I do exactly? Im assuming I need to fix the .txt file somehow, but how? Thanks for your help Use the iconv command-line program, or any programming language containing the equivalent functionality. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inserting a number as a string and keeping it that way
Sean Moss-Pultz wrote:
Hi List
Here's basically what I'm doing:
sqlite create table test(t text);
sqlite insert into test values(0123);
sqlite select * from test;
123
How can I get the string to stay 0123? I've read the docs about
Column Affinity. But I guess I'm not smart enough to figure this out
:-)
0123 is a numeric literal, in which leading zeros aren't significant.
So 0123 = 123, which gets converted to the string '123'.
BTW, I can't put the number into quotes first because that would break
my code, at a later point, when I handle this value. IE, the user
could insert '0123' (string) or 0123 (number). I need these to stay
unique.
Then you shouldn't be using a TEXT-affinity column, because all your
numbers will get converted to strings. And INTEGER, REAL, and NUMERIC
affinities won't work for the opposite reason. So you need to use the
NONE affinity, i.e., a declared type of BLOB.
sqlite create table test(t blob);
sqlite insert into test values(0123);
sqlite insert into test values('0123');
sqlite select t, typeof(t) from test;
123|integer
0123|text
(BTW, is there a way to get the command-line interface to quote strings?)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tedious CSV import question
C. Mundi wrote: On 9/25/09, Jay A. Kreibich j...@kreibi.ch wrote: ... CSV is a great quick and dirty format to move data. But it isn't simple and it isn't nearly as universal as many assume. It works great if you're just moving simple numbers and strings that don't include commas, but becomes a mess when you get into exceptions. Personally, I'd rather have the SQLite team working on core database features than trying to build a better CSV parser. The problem is non-trivial and borders on unobtainable and, as Carlos proved so clearly, there are better, easier, faster ways. Jay... Your post neatly articulates virtually every facet of this issue. Thank you. I wish we could get everyone to stop using csv. I hate to look at xml but I often wish everyone would use it instead of csv. I would hate to see any of the sqlite core devs waste time on csv. Carlos But XML has its own difficulties with SQL data sets. For example, how do you distinguish between NULL and the empty string? And then there's the obvious inefficiency in writing the column name twice for each row. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing data into SQLite
T wrote: You can use the sqlite binary to import data from a CSV file - if you do it that way you have to make sure that your data fields in the SQLite database match exactly in order the data in the CSV file. That's been my experience. The other way is to do it programmatically (Java, C++, etc). The advantage of that is you have control over the data import. Though the sqlite binary does an excellent job of importing data, it might be better to use the latter option if you want more control over the data. It's not a difficult job to do. Could you please explain how does it go by doing other way-programmatically in c++? I have installed sqlite and then i have found somewhere information that i also need a sqlite++ wrapper to be able to use sqlite trought c++. so, i have installed both, but i dont dont know how to connect sqlite and sqlite++ to each other, if i have to do it, at all. I'm really new to all this, but i have an asignment to copy datas from c++ into sqlite and i dont know even how to start.. If someone could just tell me in short steps what do i need, then i will find somehow solutions for each step.. Thank you very much! (1) Parse the CSV data into a two-dimensional array. (2) Get the column headers from the first row. (3) Scan through each column to see if its data can be converted to INTEGER or REAL. Based on this, choose a type affinity for the column. (4) From the information determined in steps (2) and (3), build and execute a CREATE TABLE statement. (5) BEGIN TRANSACTION (6) Build and execute an INSERT statement for each row in the table. (7) COMMIT ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tedious CSV import question
C. Mundi wrote:
Hi. I have scanned the list archives and Googled. I may have missed
something, but what I found was not sufficiently general or robust for my
needs.
Happily, sqlite has a very nice .output mode for CSV. It correctly
double-quotes fields when they contain a space or a comma, and it correctly
'escapes' double-quote literals as . Great! I need to go the other way.
I don't see an inverse .import mode in sqlite to handle the completely
general CSV syntax. As anyone who has ever tried knows, parsing compliant
CSV is a lot harder than writing compliant CSV.
I'm hoping someone will (please) tell me I missed something in the sqlite
docs. Otherwise, I guess I'll be using python's csv module to turn my CSV
file into SQL insert statements. This is likely to be an infequent task,
but it has to be done perfectly. So if someone knows of a command-line tool
to turn CSV into SQL inserts, I would appreciate that too.
Here's one I just wrote.
#!/usr/bin/env python
Utility to import CSV files into an SQLite database.
from __future__ import division
import csv
import getopt
import os
import sqlite3
import sys
USAGE = 'USAGE: csv2sqlite [-o dbfile] csvfile...'
DEFAULT_OUTPUT_FILE = 'a.db'
_INTEGER_MAX = (1 63) - 1
_INTEGER_MIN = -_INTEGER_MAX - 1
def identify_type(value):
Identify the SQLite type for a value.
try:
int_value = int(value)
if int_value _INTEGER_MIN or int_value _INTEGER_MAX:
return 'REAL'
return 'INTEGER'
except ValueError:
try:
float(value)
return 'REAL'
except ValueError:
return 'TEXT'
def common_type(values):
Return the common type for a list of values.
types = set(identify_type(value) for value in values if value)
if len(types) == 1:
return types.pop()
elif types == set(['INTEGER', 'REAL']):
return 'NUMERIC'
else:
return 'TEXT'
def add_table_from_file(dbconn, filename):
Read a CSV file and add it as a database table.
dbconn -- open database connection
filename -- path to the CSV file
table_name = os.path.splitext(os.path.basename(filename))[0]
data = list(csv.reader(open(filename)))
if not data:
return
headers = data.pop(0)
num_columns = len(headers)
# Replace all empty strings with NULL
data = [[value or None for value in row] for row in data]
# Ensure that all rows have the same number of columns
for row in data:
for dummy in xrange(num_columns - len(row)):
row.append(None)
# Determine the appropriate type for each column
column_info = [(column_name, common_type(row[col] for row in data))
for (col, column_name) in enumerate(headers)]
# Build the SQL statements
sql_create_table = 'CREATE TABLE IF NOT EXISTS %s (%s)' % \
(table_name, ', '.join('%s %s' % column for column in column_info))
sql_insert = 'INSERT INTO %s VALUES (%s)' % \
(table_name, ', '.join(['?'] * num_columns))
# Build the database table
dbconn.execute(sql_create_table)
for row in data:
dbconn.execute(sql_insert, row)
def _main(argv=None):
Executed when this module is run as a script.
if argv is None:
argv = sys.argv
options, args = getopt.getopt(argv[1:], 'o:', ['help'])
options = dict(options)
if '--help' in options:
print USAGE
else:
dbconn = sqlite3.connect(options.get('-o', DEFAULT_OUTPUT_FILE))
for filename in args:
add_table_from_file(dbconn, filename)
dbconn.commit()
dbconn.close()
if __name__ == '__main__':
_main()
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Lite date / time functions
Alexey Pechnikov wrote:
Hello!
On Monday 21 September 2009 19:56:07 Igor Tandetnik wrote:
Are correct selects like as
SELECT * from tbl_name where date = julianday('now','start of month');
I see no reason why not. Note that the condition will only hold when
date column represents midnight on the corresponding day.
Equal condition for dates is work now but is it safety?
In general, comparing double values for exact equality is risky.
However, fractions that are powers of two are represented exactly in a
double, so the comparison would work for values that represent, for
example, 3, 6, 9 and 12 o'clock (both AM and PM).
Can you add this note to documentation? This is very speculate question.
Especially because internal SQLite representation of julianday was chanded
some times ago.
It's not a date-specific issue; the same thing occurs with ordinary numbers.
sqlite select (1.0/49)*49 = 1.0;
0
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Viewer for blobs in hex?
Yan Bertrand wrote: Hi all, I would like to display the contents of blobs in my table as hexadecimal. I have not found any easy way of doing this. I tried : - wxSQLitePlus, but it does not display blobs contents (or I could not make it do so) - SQLiteManager plugin (for Mozilla Firefox), but it does not do this by default. The website says it can but the explaination for it is still to be done . - SQLiteStudio does not display anything (it says NULL in italic, but it is not a NULL content.) - SQLite2009 Pro but it does not dosplay blobs either - I could dump the table but it really gets tedious. Note: I tried exporting to other formats but the blobs are replaced by a string (either NULL or NONE ). I know my blobs are not empty because C-code can read them. Any idea? SQLiteSpy displays blobs in hex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Date problem
Max_wang wrote: A few months ago,I used SQLite 3.5.0 execute SQL:SELECT date(253392451200.0, 'unixepoch'); The result was -09-09. But now I use SQLite 3.6.18 replace it,this SQL execute result is -1413-03-01. Is this a Bug? Apparently so. And the problem first occurs in 5352. sqlite SELECT date(106751991167.30063, 'unixepoch'); 5352-11-01 sqlite SELECT date(106751991167.30064, 'unixepoch'); -1413-03-01 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Date problem
P Kishor wrote: On Sun, Sep 20, 2009 at 4:16 AM, Dan Bishop danbisho...@gmail.com wrote: Max_wang wrote: A few months ago,I used SQLite 3.5.0 execute SQL:SELECT date(253392451200.0, 'unixepoch'); The result was -09-09. But now I use SQLite 3.6.18 replace it,this SQL execute result is -1413-03-01. Is this a Bug? Apparently so. And the problem first occurs in 5352. sqlite SELECT date(106751991167.30063, 'unixepoch'); 5352-11-01 sqlite SELECT date(106751991167.30064, 'unixepoch'); -1413-03-01 How on earth did you discover that?! The Bisection Method. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Convention for column type names?
I understand that SQLite lets you use arbitrary names for column types, and all that's significant is a few substrings like INT, CHAR, and BLOB. But what's the common practice? Do you declare everything as INTEGER, TEXT, BLOB, REAL, or NUMERIC for a one-to-one match with the actual type affinities? Or do you declare types like DATE, BOOLEAN, VARCHAR(20), SMALLINT, etc.? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Booleans in SQLite
Nicolas Williams wrote: On Wed, Sep 02, 2009 at 05:44:38PM -0400, Shaun Seckman (Firaxis) wrote: I'm just curious how difficult it would be to add support for booleans in SQLite. This would most likely involve adding a new type affinity as well as adding true and false keywords to the lexer. There's much more that could be done but I'm just looking for rudimentary support. ... I would just like to reduce some memory overhead as well as use those keywords instead of creating integer fields using 0 and 1. SQLite3 uses a variable length encoding for integers on disk, so you will gain no space on disk by having a native boolean type. And according to http://www.sqlite.org/fileformat.html#record_format, there's even a special optimization for the integers 0 and 1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
