If you want just 1 value (any) from lookup, you can use:
SELECT
post_processing_info,
(
SELECT
is_json
FROM
tables_lookup
WHERE
tables_lookup.content_hash=webpage_contents.content_hash
LIMIT 1
) AS is_json
FROM
Keith Medcalf wrote
> Full Schema Tables:
Thanks for this, I will have grat use for it too, soon! Actually it's so
nice I think it could go into the documentation.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite
OK, thanks.
I think I will code something along similar lines, so that I have the
declared column type (the custom types, eg int_date) always easily
available.
Of course with casting and UDF's etc. it will get a bit tricky.
RBS
On Sat, Jan 6, 2018 at 11:26 PM, Keith Medcalf wrote:
>
> This is
This is how pySqlite2 (aka sqlite3) wrapper in Python achieves such data
manipulations, by tagging the column name specially (since by default rows are
only retrieved and accessible as positional locations in a row tuple. If you
open the database and tell it to "parse column types" then it wil
OK, thanks will have a look at that.
So, how about this one then:
select max(integer_date) as [max_integer_date int_date] from table1
?
RBS
On Sat, Jan 6, 2018 at 10:35 PM, Keith Medcalf wrote:
>
> Yes. You can use "pragma table_info(tablename)" to get information about
> a table, the whole
Yes. You can use "pragma table_info(tablename)" to get information about a
table, the whole table, and nothing but the one table. Or you can use the
bunch-o-views which will give you a data dictionary to be able to get the same
data by treating all the various introspection pragma's as tables
I pull the fossil source on a Linux machine by fossil update trunk.
I then do the following:
cd ../bld
rm -rf *
../src/configure --enable-load-extension --enable-threadsafe
--with-readline-lib=auto --with-pic --enable-fts5 --enable-json1
make sqlite3.c
cp ../src/src/test_intarray.c intarray.c
cp
Not sure how this works.
I had a look at your views.
Are you saying that I should get the data type (only if
sqlite3_column_decltype produces null) by querying these views, passing it
the column name?
RBS
On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf wrote:
>
> select max(integer_date) as [max
I think storing index prefix counts would only make sense in a special kind
of 'statistical' index, where you would store count(x IS NOT NULL), sum(x),
sum(x^2) so that usual statistical functions can be computed optimally.
For a table count, I think it would make sense.
--
Sent from: http://sq
If I understand your question correctly you have not normalized your data. The
whole point of a RELATIONAL DATABASE is that the relationships are based ON THE
DATA and ONLY ON THE DATA. If you have not normalized you data to at least
BCNF you can expect terrible performance and all sorts of a
On 6 Jan 2018, at 8:42pm, Dinu wrote:
> Richard Hipp-3 wrote
>> all the parent b-tree pages must be updated
>
> Yup, no question about it, at best it could be an opt-in. But as it is a
> design decision, I checked to make sure count() really is O(n) as Jonathan's
> question implied.
It would be
Could you describe your intent in more detail?
SELECT * FROM ... GROUP BY is unstable at best, so I cannot really grasp
your intention. Which lookup record's
If I guess right, you might want something in the lines of:
SELECT
w.post_processing_info,
l.is_json
FROM
webpage_conte
On 1/6/18, Keith Medcalf wrote:
>
> fileio.c attempts to #include a file "test_windirent" which does not exist.
> (Perhaps it does somewhere, but it is not part of amalgamation files nor the
> generated shell.c).
What (exact) sources are you using, and what steps are you taking to build?
--
D. R
Richard Hipp-3 wrote
> all the parent b-tree pages must be updated
Yup, no question about it, at best it could be an opt-in. But as it is a
design decision, I checked to make sure count() really is O(n) as Jonathan's
question implied.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_
fileio.c attempts to #include a file "test_windirent" which does not exist.
(Perhaps it does somewhere, but it is not part of amalgamation files nor the
generated shell.c).
Snagged a copy and it does not appear to work. Neither -m32 nor -m64 code will
compile with
gcc version 7.1.0 (x86_
Full Schema Tables:
-- Catalog Views using sqlite_master for SysObjects (Object Names)
-- and the various pragma_(ObjectName) tables to retrieve schema data
-- all TEXT columns in views have "collate nocase" attachmented to the output
-- columns to ensure that where conditions on retrievals are n
SQLite version 3.22.0 2018-01-02 18:11:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .head on
sqlite> .mode col
sqlite> create table x(a int_date);
sqlite> pragma table_info(x);
cid name
select max(integer_date) as [max_integer_date int_date] from table1
fetch the cursor.description and split the column name at the space. The right
part is the datatype for your handy dandy use.
You may also be able to use the same method in the table declaration, provided
that the type carri
> Which is bound to lead to problems at some point.
Not sure it is a problem if SQLite still recognizes the custom data types
as it's own data types.
Eg, int_date will be considered integer.
RBS
On Sat, Jan 6, 2018 at 5:59 PM, Simon Slavin wrote:
> On 6 Jan 2018, at 5:45pm, Bart Smissaert wro
>>sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
>>selectid|order|from|detail
>>0|0|0|SCAN TABLE Tbl
>I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is
>returned in case of not ``NOT NULL'' field.
Yeah, I would get that result as well if I had
On 6 Jan 2018, at 5:45pm, Bart Smissaert wrote:
> The custom datatype int_date is needed for formatting purpose to tell the
> app that receives
> the data that the column holds dates as integers.
You can only get that in a documented way by parsing the CREATE TABLE command
from sqlite_master.
Is there any way with sqlite3_column_decltype (or otherwise) to get the
declared data type (as in the table create sql in SQLite_master) when it is
a simple expression such as max and min?
for example we have a table created like this:
create table1([integer_date] int_date)
and we do:
select ma
Thank you for your response Ulrich
From: sqlite-users on behalf of
Ulrich Telle
Sent: Saturday, January 6, 2018 5:40:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] How can i encrypt my database file ?
> My first steps i made for encyption nowadays is tha
>Neither will return any values since a primary key can not be NULL.
Untrue.
The RowID alias "integer primary key" cannot be NULL
However components of primary key(...) can be null (ie, that are not aliases
for the RowID) and are not the PRIMARY KEY of a without rowid table.
" According to
These are different requests in SQL.
"ID = NULL" is comparing the ID to "NULL". Comparing any value to "NULL" fails.
This is equivalent to
SELECT ID FROM Tbl WHERE FALSE;
A smarter query planner would run it in zero ms. :)
"ID IS NULL" is checking if the value in ID is null.
Neither will retu
Hello,
On 2018-01-06 15:22, x wrote:
Because the field is not NULL in Luuk's definition and NULL values are
not covered by the INDEX. SQLite assumes that you know what you are
doing and tries to find NULL values by full scan.
The ID field in my definition is also not null.
If so, then you
My first steps i made for encyption nowadays is that i read and test some free
encryptions around the net such as sql cipher and some others but i have some
doubts if this is correct cause inside the sqlite3.cpp in the section that i
define SQLITE_HAS_CODEC it says that is not in the public dom
My first steps i made for encyption nowadays is that i read and test some free
encryptions around the net such as sql cipher and some others but i have some
doubts if this is correct cause inside the sqlite3.cpp in the section that i
define SQLITE_HAS_CODEC it says that is not in the public dom
My first steps i made for encyption nowadays is that i read and test some free
encryptions around the net such as sql cipher and some others but i have some
doubts if this is correct cause inside the sqlite3.cpp in the section that i
define SQLITE_HAS_CODEC it says that is not in the public dom
Hi All,
This is more of an academic question as I've come up with a better
query, but I was wondering why SQLite doesn't optimise this query.
Lets say I have two tables, simplified here. One contains webpage
contents and a unique hash of those contents (the primary key), the
other contains
Kenneth Shuai wrote:
> conn = sqlite3.connect(db_dest)
>
> sql = """
> attach db_source.db as db_source;
> drop table if exists tbs1 # it is used to check if tbs1 already in db_dest.
> """
> Conn.execute(sql)
Okay, let's try:
>>> conn = sqlite3.connect(db_dest)
Traceback (most recent call la
Thanks Cezary but I’m none the wiser.
>Because the field is not NULL in Luuk's definition and NULL values are
>not covered by the INDEX. SQLite assumes that you know what you are
>doing and tries to find NULL values by full scan.
The ID field in my definition is also not null.
>``SCAN TABL
Hello and happy new year.
After long time i am using sqlite in a free and closed code application i would
like to make my database file encrypted.
The database is created through this program and also is updated through it
without the user to know about its internal functions.
My first steps
db_source.db with tbs1, tbs2
db_dest.db with tbd1, tbd2
conn = sqlite3.connect(db_dest)
sql = """
attach db_source.db as db_source;
drop table if exists tbs1 # it is used to check if tbs1 already in db_dest.
"""
Conn.execute(sql)
Above will delete tbs1 from db_source.db
it might be a bug cause w
On 1/6/18, Dinu wrote:
>
> I think b-trees can store the counts of descendant nodes for every node to
> solve this issue in O(log n), but I don't see anything like it in the SQLite
> format.
They can do that, but it also means that all the parent b-tree pages
must be updated whenever an entry is
Hello,
On 2018-01-06 13:33, x wrote:
sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
selectid|order|from|detail
0|0|0|SCAN TABLE Tbl
Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index
XXX then the explain query plan will match mine (SCAN T
>sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
>selectid|order|from|detail
>0|0|0|SCAN TABLE Tbl
Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index
XXX then the explain query plan will match mine (SCAN TABLE Tbl USING COVERING
INDEX XXX). Y
Clemens Ladisch wrote
> For count(*), the database does not need the actual table rows.
I think this is not true, he has a point here: SELECT COUNT(*) WHERE
=? needs to examine every index key prefix (excluding at least
ROWID) that matches. This may mean reading in the whole index.
I think b-tree
42
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
On 06-01-18 10:44, x wrote:
> Thanks for the replies.
>
> While I’m now on board as to what a NULL is I’m still a bit puzzled by the
> query planner.
>
> Explain query plan select ID from Tbl where ID is null order by ID;
>
> returns
>
> SCAN TABLE Tbl USING COVERING INDEX ...
>
>
I do see differe
On Sat, 6 Jan 2018 09:53:46 +0100, Luuk wrote:
> On 06-01-18 00:49, Simon Slavin wrote:
>>
>> To expand on this, in SQL NULL has a special meaning.
>
> This should read:
> NULL has a special meaning.
> and not:
> in SQL NULL has a special meaning.
>
> Because the use of NULL is not 'reserverd'
Thanks for the replies.
While I’m now on board as to what a NULL is I’m still a bit puzzled by the
query planner.
Explain query plan select ID from Tbl where ID is null order by ID;
returns
SCAN TABLE Tbl USING COVERING INDEX ...
It doesn’t even use the ID pk despite the fact it’s the request
Nick wrote:
> Or in another word, if a TEXT column has similar meaning with an INTEGER
> column in my applications,(such as use userID instead of userName, still the
> way that the data works in my head:) ) is it recommended to use INTEGER one
> in order to get a less index pages?
Yes; an index on
On 06-01-18 00:49, Simon Slavin wrote:
>
> To expand on this, in SQL NULL has a special meaning.
This should read:
NULL has a special meaning.
and not:
in SQL NULL has a special meaning.
Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not
more special than in any other envi
44 matches
Mail list logo
