Re: [sqlite] Joining list?
Hi, it's a GMail issue, not mailman. GMail stops every returned email from mailman which you have sent to it. GMail thinks "if you've sent this message, it's already in your sent folder, and you don't want to see it again in your inbox folder". It's stupid, but it's how gmail works :-( I have also a mailman installation in a server for my own bussines, and some of my coleages had to drop their gmail accounts and switched to other email providers. That's all. Regards, Jose F. Gimenez El 28/09/2017 a las 13:03, pisymbol . escribió: On Thu, Sep 28, 2017 at 6:57 AM, Simon Slavin <slav...@bigfraud.org> wrote: cc: OP On 27 Sep 2017, at 4:31pm, pisymbol . <pisym...@gmail.com> wrote: I hope this arrives and gets moderated so someone can look at it. Your mesage and replies to it reached the mailing list without probblems. If you’re not seeing it there’s something wrong with your mail setup. Check your junk folder and other possibilities. I never received a confirmation from Mailman either. So it's strange that my emails are being delivered in the first place (one typically has to confirm their subscription). But again, thanks for letting me know and sorry for all the noise! -aps ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] determining is-leap-year in sqlite
Stephan, > Yeah, i should have mentioned that i'm simplifying to the range of dates > "sometime within my lifetime." Anything else is irrelevant for my > presentation ;). then, you only need to calculate ( year % 4 ). This gives you a window from 1900-3-1 to 2100-2-28 (two complete centuries!). I belive none of us (people in this mailingist) will still be alive in 2100 ;-) Regards, Jose F. Gimenez
Re: [sqlite] sqlite_column_table_name() and table alias name
Hi, any chance that this or a similar sqlite3_column_table_alias_name() were added to SQLite? TIA, Jose F. Gimenez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_column_table_name() and table alias name
Simon, BTW, by using "pragma full_column_names=1" and "pragma short_column_names=0", the sentence: SELECT * FROM mytable AS myalias returns the column names as myalias.col1, myalias.col2, etc., which is perfectly correct. But I'm aware that those pragma are deprecated. And in fact, if you use them, other errors arise. Regards, Jose F. Gimenez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_column_table_name() and table alias name
Simon, If you develop bussiness software, where you know exactly the querys because you are writing them, then there is no problem at all. But other kind of software, like a database manager, need all available metadata. And more metadata info is better, for sure. And this is my case now. Yep. That's what <http://www.sqlite.org/pragma.html#pragma_table_info> is for. No, sorry. It's not. That pragma shows information about a given table, and that it's ok. But it's not what I'm talking about. Please, reread my first post. A simple sentence like: SELECT * FROM mytable AS myalias causes that sqlite3_column_table_name() returns "mytable" for every column. And what I'm asking for is a function that returns "myalias" in that case. No less no more. In my previous message I attached a patch that adds the function sqlite3_column_table_alias_name(), which does the job. IMHO, sqlite3_column_table_name() should returns the table alias, and there should be another function sqlite3_columns_orgtable_name() which returns the original table name. But now, it's not desirable to break backwards compatibility. Regards, Jose F. Gimenez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_column_table_name() and table alias name
Simon,
This is from the documentation of SQLite:
<https://www.sqlite.org/c3ref/column_name.html>
"The name of a result column is the value of the "AS" clause for that column, if
there is an AS clause. If there is no AS clause then the name of the column is unspecified and may
change from one release of SQLite to the next."
If you do not use AS, then you have no idea what the names of your columns are.
You cannot even rely on each column having a different name. You might
upgrade to a slightly higher version of SQLite because of a bug, and get
completely different names for the columns in your result.
If you want to have any idea about your column names at all, assign each one using an
"AS" clause.
If you want to pass the results of "SELECT * FROM ..." to something else and don't know
the names of your columns, don't do a "SELECT * FROM ..." in the first place. Use
<http://www.sqlite.org/pragma.html#pragma_table_info>
to find the names of the columns which exist, and create your SELECT from that.
If you develop bussiness software, where you know exactly the querys
because you are writing them, then there is no problem at all. But other
kind of software, like a database manager, need all available metadata.
And more metadata info is better, for sure. And this is my case now.
If metadata were not useful, why exist functions as
sqlite3_column_name(), sqlite3_column_origin_name() or
sqlite3_column_table_name(), among many others? I know there is a
compilation switch to enable metadata (SQLITE_ENABLE_COLUMN_METADATA),
so, what's the problem to add such useful information that I'm asking for?
Well, at last I've tried to do myself, and I belive I've got it ;-)
I attach a patch (aplied over sqlite-amalgamation-3080701.zip), which
adds the functions sqlite3_column_table_alias_name() and
sqlite3_column_table_alias_name16(). Those functions return the table
alias for the related column, or the original table name if there is no
alias for it. It's rather small patch, althougt I've not added those
functions to the load_extension system, in order to not grow it and make
it more readable. But if needed, I can make a full patch. The key change
is this:
@@ -105364,6 +105386,7 @@
if( jnSrc ){
pTab = pTabList->a[j].pTab;
pS = pTabList->a[j].pSelect;
+ zTableAlias =
pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;
}else{
pNC = pNC->pNext;
}
The rest of changes are to accomodate that info into the actual code.
Please, I'll be very happy if it's evaluated and incorporated to sqlite
code (if approved, of course).
Best regards,
Jose F. Gimenez
<https://www.sqlite.org/compile.html#enable_column_metadata>
Index: sqlite3.c
===
--- sqlite3.c (revisión: 28)
+++ sqlite3.c (copia de trabajo)
@@ -3747,6 +3747,8 @@
SQLITE_API const void *sqlite3_column_table_name16(sqlite3_stmt*,int);
SQLITE_API const char *sqlite3_column_origin_name(sqlite3_stmt*,int);
SQLITE_API const void *sqlite3_column_origin_name16(sqlite3_stmt*,int);
+SQLITE_API const char *sqlite3_column_table_alias_name(sqlite3_stmt*,int);
+SQLITE_API const void *sqlite3_column_table_alias_name16(sqlite3_stmt*,int);
/*
** CAPI3REF: Declared Datatype Of A Query Result
@@ -9326,8 +9328,9 @@
#define COLNAME_DATABASE 2
#define COLNAME_TABLE3
#define COLNAME_COLUMN 4
+#define COLNAME_TABALIAS 5
#ifdef SQLITE_ENABLE_COLUMN_METADATA
-# define COLNAME_N5 /* Number of COLNAME_xxx symbols */
+# define COLNAME_N6 /* Number of COLNAME_xxx symbols */
#else
# ifdef SQLITE_OMIT_DECLTYPE
# define COLNAME_N 1 /* Store only the name */
@@ -68230,9 +68233,10 @@
**2 The name of the database that the column derives from
**3 The name of the table that the column derives from
**4 The name of the table column that the result column derives from
+**5 The name of the table for the column as its refered in the
sentence (maybe an alias)
**
** If the result is not a simple column reference (if it is an expression
-** or a constant) then useTypes 2, 3, and 4 return NULL.
+** or a constant) then useTypes 2, 3, 4 and 5 return NULL.
*/
static const void *columnName(
sqlite3_stmt *pStmt,
@@ -68355,6 +68359,22 @@
#endif /* SQLITE_OMIT_UTF16 */
#endif /* SQLITE_ENABLE_COLUMN_METADATA */
+/*
+** Return the name of the table for the column (maybe an alias).
+** NULL is returned if the result column is an expression or constant or
+** anything else which is not an unambiguous reference to a database column.
+*/
+SQLITE_API const char *sqlite3_column_table_alias_name(sqlite3_stmt *pStmt,
int N){
+ return columnName(
+ pStmt, N, (const void*(*)(Mem*))sqlite3_value_text, COLNAME_TABALIAS);
+}
+#ifndef SQLITE_OMIT_UTF16
Re: [sqlite] sqlite_column_table_name() and table alias name
Simon,
thanks for replying.
Specify which names you want SQLite to use:
SELECT a, b, table2.c AS table2, alias.c AS alias
FROM table1
LEFT JOIN table2 ON ...
LEFT JOIN table2 AS alias ON ...
However, I strongly advise against having one string which is both a table name and a column
name ("table2" in your example). I cannot right now think of a problem this will
trigger but I suspect you're just setting yourself up for later confusion. Perhaps use
something like .
Well, I know I can specify every column in the query, but that's not the
question. Perhaps I didn't explain correctly. Sorry for my bad english.
The question is that there is a query where there are two JOINs over the
same table. Let's use a more real example: an internal message system.
This could be a sample query:
SELECT subject, sender.name, receipt.name
FROM messages
LEFT JOIN people AS sender ON messages.idsender=people.id
LEFT JOIN people AS receipt ON messages.idreceipt=people.id
That retrieves a list for messages, with columns: , name>, . But if I need the column's fullname, I get:
messages.subject, people.name, people.name
And yes, I know that I can specify an alias for those columns which
could be ambiguous. That is "sender.name AS sender_name" and
"receipt.name as receipt_name", but what about a query like "SELECT
messages.*, sender.*, receipt.* ..." which will be processed later by a
reporting system that knows nothing about original columns? In this
case, it's absolutely needed to distingish between and
, and , and so on.
So, the question is how to get the alias table name for every column in
the query. Of course, SQLite retrieves correctly all data for the query
(I mean that retrieved data has columns from sender and receipt), but
seems that it's not possible to get the alias table name. BTW, in my
work I use both SQLite and MySQL/MariaDB, and MySQL/MariaDB allows to
get that information (there are and fields in
MYSQL_FIELD struct). I only have problems with SQLite.
TIA,
Jose F. Gimenez
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite_column_table_name() and table alias name
Hi, in this kind of statements: SELECT a, b, table2.c, alias.c FROM table1 LEFT JOIN table2 ON ... LEFT JOIN table2 AS alias ON ... the API sqlite_column_table_name() applied to both columns 3 and 4 returns . I know that belongs to in both cases, but is there any way to get for column 3 and for column 4? TIA, Jose F. Gimenez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running on windows 98
Richard, Ok, I fully understand your POV. I also write software for other developers, and many times I have to deal with these kind of issues. Thanks again. Jose F. Gimenez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running on windows 98
Richard, thanks for replying. We have no way of testing SQLite on Win9x and so we do not intend to support Win9x moving forward. Some older versions of SQLite are known to work on Win9x. If you are still supporting Win9x applications, I suggest you use those older versions of SQLite. Versions up to 3.7.11 are working fine on windows 98. Was version 3.7.12, where an optimization in read and write operations (overlapped mode) was introduced which broke compatibility with older windows versions. What I attached in my first post, was a minor change to avoid using that optimization for older windows, while keeping it for newer ones, so sqlite works fine in all windows versions. I've used isNT() function to check if that optimization is done or not. Please, review it; it doesn't break anything. I'm using it on Windows 98, XP, Vista and 7, and is working fine everywhere. Thanks, Jose F. Gimenez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running on windows 98
Hi, any comment about this subject? TIA, Jose F. Gimenez El 09/01/2013 20:36, Jose F. Gimenez escribió: Hi all, I've recently noticed that sqlite doesn't work on windows 98 since version 3.7.12, due to the change to use OVERLAPPED when read and write files. I've made a small modification to take care of it, and avoid to use that way on win 98 (by using the function isNT() to check that condition). I've attached a patch in this message. I've tested it and it seems to work fine (tested on win98, xp and win7), but I'd like to know if it's correct or if there is any problem which I can't see. TIA, Jose F. Gimenez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
