Re: [sqlite] How to retrieve table names for the given string

[Keith Medcalf]

Insert the following schema views:


-- 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 not case sensitive
-- Column Names in views defined so as to not conflict with keywords to ensure
-- quoting when using views is not required

drop view if exists SysIndexColumns;
drop view if exists SysIndexes;
drop view if exists SysColumns;
drop view if exists SysObjects;

create view if not exists SysObjects
as
select ObjectType   collate nocase,
   ObjectName   collate nocase
  from (
select type as ObjectType,
   name as ObjectName
  from sqlite_master
 where type in ('table', 'view', 'index')
   );

create view if not exists SysColumns
as
select ObjectType   collate nocase,
   ObjectName   collate nocase,
   ColumnID collate nocase,
   ColumnName   collate nocase,
   Type collate nocase,
   Affinity collate nocase,
   isNotNull,
   DefaultValue,
   isPrimaryKey
from (
select ObjectType,
   ObjectName,
   cid  as ColumnID,
   name as ColumnName,
   type as Type,
   --- Affinity Rules from https://www.sqlite.org/datatype3.html 
Section 3.1
   case when trim(type) = ''then 'Blob'
when instr(UPPER(type), 'INT')  > 0 then 'Integer'
when instr(UPPER(type), 'CLOB') > 0 then 'Text'
when instr(UPPER(type), 'CHAR') > 0 then 'Text'
when instr(UPPER(type), 'TEXT') > 0 then 'Text'
when instr(UPPER(type), 'BLOB') > 0 then 'Blob'
when instr(UPPER(type), 'REAL') > 0 then 'Real'
when instr(UPPER(type), 'FLOA') > 0 then 'Real'
when instr(UPPER(type), 'DOUB') > 0 then 'Real'
else 'Numeric'
end as Affinity,
   "notnull"as isNotNull,
   dflt_value   as DefaultValue,
   pk   as isPrimaryKey
  from SysObjects
  join pragma_table_info(ObjectName)
 );

create view if not exists SysIndexes
as
select ObjectType   collate nocase,
   ObjectName   collate nocase,
   IndexNamecollate nocase,
   IndexID,
   isUniqueIndex,
   IndexOrigin  collate nocase,
   isPartialIndex
  from (
select ObjectType,
   ObjectName,
   name as IndexName,
   seq  as IndexID,
   "unique" as isUniqueIndex,
   origin   as IndexOrigin,
   partial  as isPartialIndex
  from SysObjects
  join pragma_index_list(ObjectName)
   );

create view if not exists SysIndexColumns
as
select ObjectType   collate nocase,
   ObjectName   collate nocase,
   IndexNamecollate nocase,
   IndexColumnSequence,
   ColumnID,
   ColumnName   collate nocase,
   isDescendingOrder,
   Collationcollate nocase,
   isPartOfKey
  from (
select ObjectType,
   ObjectName,
   IndexName,
   seqnoas IndexColumnSequence,
   cid  as ColumnID,
   name as ColumnName,
   "desc"   as isDescendingOrder,
   coll as Collation,
   key  as isPartOfKey
  from SysIndexes
  join pragma_index_xinfo(IndexName)
   );

then

select ObjectName as TableName
  from SysColumns 
 where ColumnName == ?
   and ObjectType = 'table';


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Revathi Narayanan
>Sent: Thursday, 27 September, 2018 07:44
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] How to retrieve table names for the given string
>
>Hi,
>
>I have one requirement like I want to display all the table names for
>the
>given column name.
>
>Ex: If the table T1 and T2 has column names like C1 then it should
>display
>both the table names T1 and T2.
>
>I tried to execute the query using sqlitemaster. But it's displaying
>only
>table names not column names.
>
>Kindly do the needful.
>
>
>Thanks
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list

Re: [sqlite] How to retrieve table names for the given string

[Richard Hipp]

On 9/27/18, Revathi Narayanan  wrote:
> Hi,
>
> I have one requirement like I want to display all the table names for the
> given column name.
>
> Ex: If the table T1 and T2 has column names like C1 then it should display
> both the table names T1 and T2.
>
> I tried to execute the query using sqlitemaster. But it's displaying only
> table names not column names.

Let the column name be in the variable $c1

   SELECT a.name
   FROM sqlite_master AS a
   JOIN pragma_table_info(a.name) AS b
   WHERE a.type='table'
   AND b.name=$c1;



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to retrieve table names for the given string

[Igor Tandetnik]

On 9/27/2018 9:43 AM, Revathi Narayanan wrote:

I have one requirement like I want to display all the table names for the
given column name.

Ex: If the table T1 and T2 has column names like C1 then it should display
both the table names T1 and T2.


With sufficiently recent SQLite version, you can select from 
pragma_table_info('tablename') and get the same resultset as PRAGMA 
table_info(tablename): https://www.sqlite.org/pragma.html#pragfunc . These 
functions can participate in joins: the table name doesn't have to be a literal.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to retrieve table names for the given string

[Simon Slavin]

On 27 Sep 2018, at 2:43pm, Revathi Narayanan  wrote:

> I tried to execute the query using sqlitemaster. But it's displaying only
> table names not column names.

sqlite_master does not have column name columns.  They're just mentioned in the 
CREATE statement.

You might want to combine it with



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to retrieve table names for the given string

[Revathi Narayanan]

Hi,

I have one requirement like I want to display all the table names for the
given column name.

Ex: If the table T1 and T2 has column names like C1 then it should display
both the table names T1 and T2.

I tried to execute the query using sqlitemaster. But it's displaying only
table names not column names.

Kindly do the needful.


Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A SQL statement reformatter

[Simon Slavin]

For those times when you have to understand a poorly-formatted SQL statement:



I seem to prefer 'full' mode.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing unsigned 64 bit values

[Keith Medcalf]

If you wanted to store it as purple tree smoke signals you could do that too.  
However, the fact of the matter is that SQLite3 does not perform conversions to 
and from purple tree smoke signals, and the OP was not trying to store the 
value as a "hex or decimal representation of the number", or as a blob, or 
anything else.  He was trying to store it as a signed integer.  And it was 
clearly too large a magnitude for a signed integer.  Of the numeric formats 
available that it could be stored as in the current version of SQLite3, that 
leaves an IEEE-754 binary64 as the only other option.  Thus that is what was 
done.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Nathan Wagner
>Sent: Thursday, 27 September, 2018 11:50
>To: SQLite mailing list
>Subject: Re: [sqlite] storing unsigned 64 bit values
>
>On Thu, Sep 27, 2018 at 11:05:24AM -0600, Keith Medcalf wrote:
>
>> so the only way to store something [larger than a signed 64-bit
>int]
>> is as a double-precision float.
>
>I'd like to point out that you could *store* it as the hex or decimal
>text representation of the integer.  If you included leading zeros,
>it
>would even sort correctly.  If you actually need to do arithmetic on
>it
>within sqlite, that's another matter.
>
>(Or any non-weird base representation, for that matter.)
>
>--
>nw
>___
>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

Re: [sqlite] storing unsigned 64 bit values

[Nathan Wagner]

On Thu, Sep 27, 2018 at 11:05:24AM -0600, Keith Medcalf wrote:

> so the only way to store something [larger than a signed 64-bit int]
> is as a double-precision float.

I'd like to point out that you could *store* it as the hex or decimal
text representation of the integer.  If you included leading zeros, it
would even sort correctly.  If you actually need to do arithmetic on it
within sqlite, that's another matter.

(Or any non-weird base representation, for that matter.)

-- 
nw
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLITE_DETERMINISTIC ignored on an aggregate?

[Richard Hipp]

On 9/27/18, Deon Brewis  wrote:
> Is there anything that SQLITE_DETERMINISTIC would ever be used for in an
> aggregate? (Function with xStep/xFinal as opposed to just xFunc).
>
> I assume it's ignored, but just checking.

I believe you are correct.  But why are you asking?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing unsigned 64 bit values

[Keith Medcalf]

Well, you could call it a shiny shoe integer.  the "shiny shoe" part is just 
ignored, just like your use of the word unsigned.  And no, the value stored was 
a IEEE-754 double precision floating point so you got to keep the high 53 bits 
are the rest were discarded (this is because the value was numeric (that is, 
all numbers) and could not fit in a signed integer, so the only way to store 
something of that magnitude is as a double-precision float.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Conor Lennon
>Sent: Thursday, 27 September, 2018 10:10
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] storing unsigned 64 bit values
>
>
>
>On 27/09/18 17:03, Simon Slavin wrote:
>> On 27 Sep 2018, at 11:53am, Conor Lennon
> wrote:
>>
>>> e.g. 18446744073709551615 (one less than 2 to the power of 65)
>>>
>>> I seem to have managed to store this value in a database.
>> What is the affiliation for that column ?  Did you declare it as
>INTEGER or something else ?
>>
>> Simon.
>>
>
>It's declared as a unsigned integer
>
>sqlite> .schema mytable
>CREATE TABLE IF NOT EXISTS "mytable" (
>    "id" integer NOT NULL PRIMARY KEY,
>    "bigvalue" integer unsigned NOT NULL UNIQUE
>);
>
>___
>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

Re: [sqlite] storing unsigned 64 bit values

[Simon Slavin]

On 27 Sep 2018, at 5:10pm, Conor Lennon  wrote:

> It's declared as a unsigned integer

There is no such thing in SQLite.  SQLite has an integer type, but it is an 
8-byte signed integer.



If you don't need to sort on that column, just search and select, then you 
could store that value as TEXT or a BLOB, whichever is more convenient.  If you 
need to sort but don't need perfect precision, you could store the value as 
REAL.  But SQLite has no way of handing a 64-bit unsigned integer.  Sorry.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: storing unsigned 64 bit values

[Hick Gunter]

"unsigned" is ignored by sqlite. Depending on how you inserted the value, it 
could be stored as a text or as a real value, irrespective of the declared type.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Conor Lennon
Gesendet: Donnerstag, 27. September 2018 18:10
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] storing unsigned 64 bit values



On 27/09/18 17:03, Simon Slavin wrote:
> On 27 Sep 2018, at 11:53am, Conor Lennon  wrote:
>
>> e.g. 18446744073709551615 (one less than 2 to the power of 65)
>>
>> I seem to have managed to store this value in a database.
> What is the affiliation for that column ?  Did you declare it as INTEGER or 
> something else ?
>
> Simon.
>

It's declared as a unsigned integer

sqlite> .schema mytable
CREATE TABLE IF NOT EXISTS "mytable" (
"id" integer NOT NULL PRIMARY KEY,
"bigvalue" integer unsigned NOT NULL UNIQUE );

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing unsigned 64 bit values

[Conor Lennon]

On 27/09/18 17:03, Simon Slavin wrote:
> On 27 Sep 2018, at 11:53am, Conor Lennon  wrote:
>
>> e.g. 18446744073709551615 (one less than 2 to the power of 65)
>>
>> I seem to have managed to store this value in a database.
> What is the affiliation for that column ?  Did you declare it as INTEGER or 
> something else ?
>
> Simon.
>

It's declared as a unsigned integer

sqlite> .schema mytable
CREATE TABLE IF NOT EXISTS "mytable" (
    "id" integer NOT NULL PRIMARY KEY,
    "bigvalue" integer unsigned NOT NULL UNIQUE
);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing unsigned 64 bit values

[Simon Slavin]

On 27 Sep 2018, at 11:53am, Conor Lennon  wrote:

> e.g. 18446744073709551615 (one less than 2 to the power of 65)
> 
> I seem to have managed to store this value in a database.

What is the affiliation for that column ?  Did you declare it as INTEGER or 
something else ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is SQLITE_DETERMINISTIC ignored on an aggregate?

[Deon Brewis]

Is there anything that SQLITE_DETERMINISTIC would ever be used for in an 
aggregate? (Function with xStep/xFinal as opposed to just xFunc).

I assume it's ignored, but just checking.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite pre-compiled DLL for Windows x64

[Eric Grange]

Hi,

For version 3.25.2, the precompiled x64 DLL appears missing, there are
several vsix downloads, but when extracting the bundled DLLs
(in the Redist/Retail directories), they appear different (or non-standard
ones that need some form of post-processing ?),
for instance the x86 dll is larger (1222656 bytes vs 910716 bytes for the
one in the zip), and the x64 dll fails when loaded with LoadLibrary().

Eric
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in 3.25.2 (RECURSIVE CTE + window function)

[Richard Hipp]

Thanks for the report and test case.  Now fixed on trunk and on branch-3.25.

On 9/25/18, Щекин Ярослав  wrote:
> Hello.
>
> Here's the self-contained test case:
>
> WITH t(id, parent) AS (
> SELECT CAST(1 AS INT), CAST(NULL AS INT)
> UNION ALL
> SELECT 2, NULL
> UNION ALL
> SELECT 3, 1
> UNION ALL
> SELECT 4, 1
> UNION ALL
> SELECT 5, 2
> UNION ALL
> SELECT 6, 2
> ), q AS (
> SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
>   FROM t
>  WHERE parent IS NULL
>  UNION ALL
> SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
>   FROM q
>   JOIN t
> ON t.parent = q.id
> )
> SELECT *
>   FROM q;
>
> Results in segmentation fault.
> (I also wanted to thank [Arfrever] (in #sqlite IRC) for testing /
> confirmation.)
>
> --
> WBR, Yaroslav Schekin.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE PERCENTAGE

[Radovan Antloga]

select 100.0 * sum(survived) / count(*) as tot_pct_survived
FROM passengers

Regards
Radovan

frknozdmr01 je 26.09.2018 ob 3:03 napisal:

Hi, I need to find the percent of passengers survived? (total). How do you do
it? I tried the comment below but it did not work.

SELECT
 SUM(CASE WHEN survived=1 THEN 1.0 ELSE 0.0 END) /
 CAST(COUNT(*) AS FLOAT)*100
 AS tot_pct_survived
FROM passengers;


This is the table;

CREATE TABLE passengers (
 id INTEGER NOT NULL,
 lname TEXT,
 title TEXT,
 class TEXT,
 age FLOAT,
 sex TEXT,
 survived INTEGER,
 code INTEGER
);




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] storing unsigned 64 bit values

[Conor Lennon]

I am trying to store and retrieve unsigned 64 bit integer values in sqlite 
through c bindings.

e.g. 18446744073709551615 (one less than 2 to the power of 65)

I seem to have managed to store this value in a database.

When I run sqlite3 on the command line and select the column, I get back 
1.84467440737096e+19

The problem that I have is retrieving the value using c bindings.

I'm calling sqlite3_column_int64.

This function returns back a sqlite3_int64 value, which is signed.

When I call the function it returns back 9223372036854775807, which is the 
maximum size of a signed 64 bit integer (one less than 2 to the power
of 63)

There doesn't seem to be a sqlite3_column_uint64 function.

Any ideas?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.AccessViolationException: 'Attempted to read or write protected memory. This is often an indication that other memory is corrupt.'

[Ayush Maheshwari]

When Connection is open then the exception
is:-System.AccessViolationException

Code is:--
 *   SQLiteCommand cmd = new SQLiteCommand();*
*cmd.Connection = connection;*
*connection.Open();*
*Helper helper = new Helper(cmd);*
*helper.BeginTransaction();*

Please help me.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE PERCENTAGE

[frknozdmr01]

Hi, I need to find the percent of passengers survived? (total). How do you do
it? I tried the comment below but it did not work.

SELECT 
SUM(CASE WHEN survived=1 THEN 1.0 ELSE 0.0 END) / 
CAST(COUNT(*) AS FLOAT)*100 
AS tot_pct_survived 
FROM passengers;


This is the table;

CREATE TABLE passengers (
id INTEGER NOT NULL,
lname TEXT,
title TEXT,
class TEXT, 
age FLOAT,
sex TEXT,
survived INTEGER,
code INTEGER
);




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in 3.25.2 (RECURSIVE CTE + window function)

[Щекин Ярослав]

Hello.

Here's the self-contained test case:

WITH t(id, parent) AS (
SELECT CAST(1 AS INT), CAST(NULL AS INT)
UNION ALL
SELECT 2, NULL
UNION ALL
SELECT 3, 1
UNION ALL
SELECT 4, 1
UNION ALL
SELECT 5, 2
UNION ALL
SELECT 6, 2
), q AS (
SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
  FROM t
 WHERE parent IS NULL
 UNION ALL
SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
  FROM q
  JOIN t
ON t.parent = q.id
)
SELECT *
  FROM q;

Results in segmentation fault. 
(I also wanted to thank [Arfrever] (in #sqlite IRC) for testing / confirmation.)

-- 
WBR, Yaroslav Schekin.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users