Re: [sqlite] SQLite3 Tutorial error

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf

>>>SQLite does not use the glob function from the standard library -- the 
>>>function is defined in func.c
>>>
>>>Both "glob" and "like" call the same function, likeFunc with different sets 
>>>of user_data.  likeFunc does a bunch of validation then calls patternCompare 
>>>which actually implements the like and glob functionality.  How like and 
>>>glob work are documented in the preface to patternCompare.
>>>
>>>like implements the standard sql like using % (0 or more) and _ (exactly 1 
>>>char) as wildcard matches.
>>>
>>>glob implements unix globbing using * (0 or more) and ? (exactly 1) as 
>>>wildcard matches. "sets" of characters are indicated by  squockets (square 
>>>brackets -- []).  Different from the standard unix glob however, it uses ^ 
>>>to invert the sense of a set rather than an !.  Since it is unicode, a 
>>>character is [\u-\u10].  [^1-7] is equivalent to a match of any of 
>>>the remaining unicode characters.
>>>
>>>thus in unix/linux one may pronounce "match anything where one character is 
>>>not the digits 1 through 7" as *[!1-7]* one would pronounce the same request 
>>>to SQLite as *[^1-7]*
>>>
>>>This of course would match any string that was not composed entirely of only 
>>>the characters 1 through 7 (not that there are no characters 1 through 7 in 
>>>the string) -- and must be at least 1 character long.
>>>
>>>If one wanted to match strings that contained a 1 through 7 anywhere within, 
>>>then one would pronounce *[1-7]* on both unix/linux and to SQLite
>>>
>>>Were one to want a glob that excluded all strings that contained the digits 
>>>1 though 7 anywhere within, then one would pronounce, in SQLite, WHERE NOT x 
>>>GLOB '*[1-7]*' -- though this would also now match 0 length strings.
>>>
>>>There is no way to "invert" the match-sense of a glob pattern within the 
>>>pattern itself.  That is, one cannot use '^*[1-7]*' as an equivalent to the 
>>>above inversion of the results of a positive match.  GLOB patterns only 
>>>search for a positive match, not an exclusion.  The [^stuf] excludes the 
>>>characters or range provided from the characters matched by a ? -- [^stuf] 
>>>is not an exclusion of the characters stuf but rather a match for any of the 
>>>other unicode characters except stuf -- in other words a "somewhat limited 
>>>?".

How about adding the above to the permanent docs? AFAIK there is currently no 
way to know all this stuff other than reading source.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Foreign key integrity checking.

[Dominique Devienne]

On Fri, Jan 6, 2017 at 10:30 PM, David Raymond 
wrote:

> http://www.sqlite.org/foreignkeys.html
>
> My version of the very basic basics anyway:
> -Foreign key enforcement is turned on or off with a pragma and is a
> connection-level setting, not a property of the database itself.
> -The default enforcement is off unless a compile option is used.
> -So unless you have full control over what's accessing a database then you
> can't be sure clients are enforcing it.
> -There's a foreign_keys_check pragma which will run an integrity check and
> give you all the key violations.
> -"deferrable initially deferred" is the only way to have a deferred key,
> any other combo will be immediate. You can't change the schema for that at
> will.
> -You can force everything to be deferred if you use the defer_foreign_keys
> pragma, but you can't force anything to immediate if it started deferred.
> -Violation messages will often not tell you exactly which foreign key it
> was that was broken, though if you name your constraints it can help.
> -In keeping track of deferred foreign keys basically a tally of how many
> keys were broken vs fixed is kept. If things don't come out even it can't
> tell you where the problem record was, only that there was at least one
> problem somewhere.
> -A unique index has to be kept on the target (parent) field(s). Optionally
> a regular one on the source (child) fields speeds up enforcement checks.


Great summary David!

I'd just add the new recently announced ".lint fkey-indexes"
from the shell tool (cf http://sqlite.org/releaselog/3_16_2.html),
which find instances of un-indexed FK child column(s).

This is a classic in Oracle, where such unindexed FKs can easily lead to
deadlocks.
In SQLite, it's "just" of matter of performance, since locking is always at
the db level anyway. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key integrity checking.

[David Raymond]

http://www.sqlite.org/foreignkeys.html

My version of the very basic basics anyway:
-Foreign key enforcement is turned on or off with a pragma and is a 
connection-level setting, not a property of the database itself.
-The default enforcement is off unless a compile option is used.
-So unless you have full control over what's accessing a database then you 
can't be sure clients are enforcing it.
-There's a foreign_keys_check pragma which will run an integrity check and give 
you all the key violations.
-"deferrable initially deferred" is the only way to have a deferred key, any 
other combo will be immediate. You can't change the schema for that at will.
-You can force everything to be deferred if you use the defer_foreign_keys 
pragma, but you can't force anything to immediate if it started deferred.
-Violation messages will often not tell you exactly which foreign key it was 
that was broken, though if you name your constraints it can help.
-In keeping track of deferred foreign keys basically a tally of how many keys 
were broken vs fixed is kept. If things don't come out even it can't tell you 
where the problem record was, only that there was at least one problem 
somewhere.
-A unique index has to be kept on the target (parent) field(s). Optionally a 
regular one on the source (child) fields speeds up enforcement checks.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Michael Tiernan
Sent: Friday, January 06, 2017 4:07 PM
To: SQLite mailing list
Subject: [sqlite] Foreign key integrity checking.

I'm going to assume this has come up before so instead of asking for 
help, I'll simply ask for pointers to FAQs about some of the more 
mundane things such as ensuring foreign key integrity and checking for it.

Thanks for everyone's time.

-- 
   << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
   Non Impediti Ratione Cogatationis
   Women and cats will do as they please, and men and dogs
should relax and get used to the idea. -Robert A. Heinlein

___
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] Foreign key integrity checking.

[Michael Tiernan]

I'm going to assume this has come up before so instead of asking for 
help, I'll simply ask for pointers to FAQs about some of the more 
mundane things such as ensuring foreign key integrity and checking for it.


Thanks for everyone's time.

--
  << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
  Non Impediti Ratione Cogatationis
  Women and cats will do as they please, and men and dogs
   should relax and get used to the idea. -Robert A. Heinlein

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

Re: [sqlite] SQLite3 Tutorial error

[Keith Medcalf]

On Friday, 6 January, 2017 12:49, James K. Lowden  
wrote:

> On Fri, 6 Jan 2017 10:23:06 +1100
> "dandl"  wrote:
> 
> > Unix globbing for Linux is defined here:
> > http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does
> > not implement this behaviour.
> 
> A quick scan of SQLite sources shows only references to the glob
> function, no implementation.  In func.c, we find
> 
> LIKEFUNC(glob, 2, , SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
> 
> It looks to me like SQLite imports glob(3) as its default
> implementation.  Have you an example for which a glob pattern behaves
> differently in SQLite versus C?
> 
> (For those following along at home, bear in mind that glob(3) need not
> necessarily be what your favorite shell uses.)
> 
> If indeed SQLite is using the glob function from libc, ISTM it's
> perfectly sufficient to refer to glob(7) for syntax, since that's the
> documentation for the controlling implementation.

SQLite does not use the glob function from the standard library -- the function 
is defined in func.c

Both "glob" and "like" call the same function, likeFunc with different sets of 
user_data.  likeFunc does a bunch of validation then calls patternCompare which 
actually implements the like and glob functionality.  How like and glob work 
are documented in the preface to patternCompare.

like implements the standard sql like using % (0 or more) and _ (exactly 1 
char) as wildcard matches.

glob implements unix globbing using * (0 or more) and ? (exactly 1) as wildcard 
matches. "sets" of characters are indicated by  squockets (square brackets -- 
[]).  Different from the standard unix glob however, it uses ^ to invert the 
sense of a set rather than an !.  Since it is unicode, a character is 
[\u-\u10].  [^1-7] is equivalent to a match of any of the remaining 
unicode characters.

thus in unix/linux one may pronounce "match anything where one character is not 
the digits 1 through 7" as *[!1-7]*
one would pronounce the same request to SQLite as *[^1-7]*

This of course would match any string that was not composed entirely of only 
the characters 1 through 7 (not that there are no characters 1 through 7 in the 
string) -- and must be at least 1 character long.

If one wanted to match strings that contained a 1 through 7 anywhere within, 
then one would pronounce *[1-7]* on both unix/linux and to SQLite

Were one to want a glob that excluded all strings that contained the digits 1 
though 7 anywhere within, then one would pronounce, in SQLite, WHERE NOT x GLOB 
'*[1-7]*' -- though this would also now match 0 length strings.

There is no way to "invert" the match-sense of a glob pattern within the 
pattern itself.  That is, one cannot use '^*[1-7]*' as an equivalent to the 
above inversion of the results of a positive match.  GLOB patterns only search 
for a positive match, not an exclusion.  The [^stuf] excludes the characters or 
range provided from the characters matched by a ? -- [^stuf] is not an 
exclusion of the characters stuf but rather a match for any of the other 
unicode characters except stuf -- in other words a "somewhat limited ?".





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

Re: [sqlite] Date Formatting from Excel Float Date/Time Representation

[Ben Newberg]

select t0.key, "Issue Type",  strftime('%Y-%m-%d %H:%M:%S', (Updated -
(julianday('1970-01-01') - julianday('1899-12-30'))) + 2440587.5) Updated
from JIRA_Stat_0_20170106124800 t0 inner join JIRA_Stat_1_20170106124800 t1
on t0.key = t1.key

where "Last Comment" is not null

order by assignee;

give that a try




On Fri, Jan 6, 2017 at 2:23 PM, Jens Alfke  wrote:

>
> > On Jan 6, 2017, at 12:16 PM, Ed Lipson  wrote:
> >
> > The Updated column is properly reflected as a float, as that is the
> > internal Excel format. What formatting functions can I use to get it to
> > appear as a date time in SQL output, as it appears in Excel? I have tried
> > strftime and date but I don't get any meaningful data.
>
> This doesn’t seem like a question related to SQLite; you’ll need to find
> out the definition of Excel’s internal date/time format. Most numeric time
> formats have much larger numbers since they count in seconds, but the
> values around 42,000 that you showed would be consistent with a number of
> days since 1/1/1900. You can probably use one of the standard library
> functions to convert that into a time_t and print it.
>
> —Jens
> ___
> 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] Date Formatting from Excel Float Date/Time Representation

[Jens Alfke]

> On Jan 6, 2017, at 12:16 PM, Ed Lipson  wrote:
> 
> The Updated column is properly reflected as a float, as that is the
> internal Excel format. What formatting functions can I use to get it to
> appear as a date time in SQL output, as it appears in Excel? I have tried
> strftime and date but I don't get any meaningful data.

This doesn’t seem like a question related to SQLite; you’ll need to find out 
the definition of Excel’s internal date/time format. Most numeric time formats 
have much larger numbers since they count in seconds, but the values around 
42,000 that you showed would be consistent with a number of days since 
1/1/1900. You can probably use one of the standard library functions to convert 
that into a time_t and print it.

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

[sqlite] Date Formatting from Excel Float Date/Time Representation

[Ed Lipson]

I'm trying to work with JIRA information. I export my JIRA records and load
them into a SQLite3 database via a python script. The export is in Excel
format. When I send the data back from SQLite3 to Excel and have Excel
format the date, it is correctly interpreted. From that I am assuming I
have a 'proper' process of reading the Excel and writing the Excel, with
SQLite3 as the middle.

How would I format the data in a SQL statement so it looks correct? When I
run:

select t0.key, "Issue Type",  Updated

from JIRA_Stat_0_20170106124800 t0 inner join JIRA_Stat_1_20170106124800 t1
on t0.key = t1.key

where "Last Comment" is not null

order by assignee

I get output which looks like:
key Issue Type Updated
TSGOPM-176 Task 42674.629861
TSGOPM-139 Task 42698.225695
The Updated column is properly reflected as a float, as that is the
internal Excel format. What formatting functions can I use to get it to
appear as a date time in SQL output, as it appears in Excel? I have tried
strftime and date but I don't get any meaningful data.




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

Re: [sqlite] SQLite3 Tutorial error

[Richard Hipp]

On 1/6/17, James K. Lowden  wrote:
>
> A quick scan of SQLite sources shows only references to the glob
> function, no implementation.

The implementation is built-in here:
https://www.sqlite.org/src/artifact/d8582ee919759756?ln=610-770

For portability reasons, SQLite avoids using routines from the
standard library.  For example,  atof() scan behave differently
depending on LOCALE and so SQLite has its own code for text-to-float
conversion.

-- 
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] Quest for "scratch table" implementation in SQLite.

[dmp]

> I'm still trying to figure out a graceful way to implement
> a "scratch" database table that has the following properties:

> o Can be created on demand while a database connection is open.
> o Can include references to other material in the database to
>   ensure integrity.
> o Will be automatically deleted when the database is closed.
>
> TEMPORARY tables can't do the second bullet, as I understand it.
>
> Right now I have application code that manages such tables,
> but it seems clunky and error-prone.  Am I missing any SQLite
>  feature that would let me do this more easily?
>
> Thanks for any suggestions.
>
> Randall.

I have a Java program that is used as a plugin for my application
that can before a query on a database that then creates a mem/file
database of the data. Not sure if it would meet point 2. Still
needs some work because it works with other databases also, but
the SQLite to SQLite should be fine.

danap.

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

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[James K. Lowden]

On Thu, 5 Jan 2017 19:26:26 +
"Smith, Randall"  wrote:

> * Can be created on demand while a database connection is open.
> * Can include references to other material in the database to
>ensure integrity. 
> * Will be automatically deleted when the database is closed.

There is a conventional approach that would work just as well.  

Instead of creating and dropping the table per session, create it once
as part of the schema.  Obviously, you can set up foreign key
constraints for it.  

Per session, delete all rows at the beginning. If multiple users are
involved, make the user id part of the key, and delete all rows per
user.  If you want to, delete all rows before exiting, too.  But that's
optional; no one can see rows in a database that's not open.  

It may help to remember that relational theory doesn't include any
notion of "connection" to the database.  There are tables and they have
rows. Operations on those tables affect the rows.  Creating the tables,
per se, is outside the theory, as too is the idea of creating them
automatically at time of connection.  

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

Re: [sqlite] Performance degradation of in-memory database with high row count and overall design discussion of my project

[James K. Lowden]

On Thu, 5 Jan 2017 18:56:13 +0100
Simone Mosciatti  wrote:

> I could store in some buffer some SQL statement and I could execute
> all of them in one single transaction.

That's a good idea, all things being equal.  But are they equal?  Your
problem description is entirely technical.  It doesn't address how
transactions map onto the problem domain.  

Question you should ask yourself:

1.  What is the unit of work?  What combinations of operations
constitute a logically consistent change to the database?  

2.  How many units of work are you prepared to lose in the event of
system failure?  That determines how often you commit.  

> 2) I need some way to indicate if an error occurs during a write 
> operation, which is a problem I am not able to solve in a reasonable 
> bound of complexity.

I'm not sure I understand.  SQLite will commit the transaction
atomically.  If the transaction fails for logical or physical reasons,
the error is indicated at commit time.  How is indicating that error to
the user in any way difficult?  

--jkl

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

Re: [sqlite] SQLite3 Tutorial error

[James K. Lowden]

On Fri, 6 Jan 2017 10:23:06 +1100
"dandl"  wrote:

> Unix globbing for Linux is defined here:
> http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does
> not implement this behaviour.

A quick scan of SQLite sources shows only references to the glob
function, no implementation.  In func.c, we find

LIKEFUNC(glob, 2, , SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),

It looks to me like SQLite imports glob(3) as its default
implementation.  Have you an example for which a glob pattern behaves
differently in SQLite versus C?  

(For those following along at home, bear in mind that glob(3) need not
necessarily be what your favorite shell uses.)

If indeed SQLite is using the glob function from libc, ISTM it's
perfectly sufficient to refer to glob(7) for syntax, since that's the
documentation for the controlling implementation.  

--jkl

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

Re: [sqlite] sqlite-users Digest, Vol 109, Issue 6

[Simon Slavin]

On 6 Jan 2017, at 3:20pm, Simone Mosciatti  wrote:

> 1. Retrieve the connection object inside Redis
> 2. Parse the SQL statement (sqlite3_prepare_v2)
> 3. Execute the parsed statement
> 4. Return an "OK" to the client

Are you using the sequence

sqlite3_prepare_v2(), sqlite3_step(), sqlite3_finalize()

for each statement ?  If not you are asking your program to do a lot of extra 
memory handling.

Alternatively you could just use sqlite3_exec(), which does all three of the 
above things.

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

Re: [sqlite] sqlite-users Digest, Vol 109, Issue 6

[Simone Mosciatti]

Hi Dan,

I run your TCL script and I can confirm that I am seeing values 
extremely different, in my machine, the same that I used for the 
previous test, I got a rather stable 240k insert per second.


In my understanding my code is not doing anything different.

The only difference is the presence of another layer (Redis), however 
this should not give us such decrease of performance (it is going more 
than 100x slower), nor it should show such degradation function of the 
row count.


What I am doing in the module is simply to:

1. Retrieve the connection object inside Redis
2. Parse the SQL statement (sqlite3_prepare_v2)
3. Execute the parsed statement
4. Return an "OK" to the client

Of course with all the error check in the middle.

I do not see why, it should go SO slower, I can understand a little 
slower, but this is too much.


I don't think it is SQLite faults, however if you see something that I 
am clearly doing wrong I do appreciate any help.


Best,

Simone


On 06/01/2017 13:00, sqlite-users-requ...@mailinglists.sqlite.org wrote:

Can you describe the performance test you are running?

If I create a table in an in-memory database with:

CREATE TABLE test (a INT, b INT, c INT);

Then run this:

INSERT INTO test VALUES(random(), random(), random())

10,000,000 times, I get a fairly stable 330,000 inserts per second or
so. Tcl code to do this below. What is your test doing differently?

Dan.


## START TCL CODE
 package require sqlite3

 sqlite3 db :memory:
 db eval { CREATE TABLE test (a INT, b INT, c INT); }

 proc insert_n_rows {n} {
   for {set i 0} {$i<$n} {incr i} {
 db eval { INSERT INTO test VALUES(random(), random(), random()) }
   }
 }

 set nStep 10

 for {set i 0} {$i < 100} {incr i} {
   set us [lindex [time { insert_n_rows $nStep }] 0]
   puts "[expr $i*$nStep] [format %.2f [expr (100.0 * $nStep) /
$us]]/sec"
 }
## END TCL CODE


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

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

Hello Don !

Thanks for reply !

It's my fault I wrote it without actually testing it for the purpose of 
show my use case:


===

--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_master_list_view_tidy" AS
SELECT
a.*,
b.name as modalidade_licitacao,
c.name as credor,
d.*
FROM despesas_master AS a  fixed missing table ???
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;

===


On 06/01/17 11:35, Don V Nielsen wrote:

In the below view, what is "a"? A FROM is not defined.

--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_master_list_view_tidy" AS
SELECT
 a.*,
 b.name as modalidade_licitacao,
 c.name as credor,
 d.*
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;


On Fri, Jan 6, 2017 at 3:27 AM, Domingo Alvarez Duarte 
wrote:


Hello Richard !

The simple example I've sent is the minimal to show the problem, the real
database schema where I found this problem has a lot of small tables joined
and I was using the views to simplify (not duplicate) code, so on that case
it'll result in bloat and repetition. see bellow:

===

 create table if not exists municipios(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists municipios_orgaos(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists municipios_poder(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists credores(
 id integer primary key,
 cnpj_cpf varchar collate nocase_slna,
 name varchar not null collate nocase_slna,
 unique(cnpj_cpf, name)
 );

 create table if not exists tipos_despesa(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists funcoes_governo(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists subfuncoes_governo(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists programas_governo(
 id integer primary key,
 code integer,
 name varchar not null collate nocase_slna,
 unique(code, name)
 );

 create table if not exists acoes_governo(
 id integer primary key,
 code integer,
 name varchar not null collate nocase_slna,
 unique(code, name)
 );

 create table if not exists fontes_recursos(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists aplicacoes_fixo(
 id integer primary key,
 code integer,
 name varchar not null collate nocase_slna,
 unique(code, name)
 );

 create table if not exists aplicacoes_variavel(
 id integer primary key,
 code integer,
 name varchar not null collate nocase_slna,
 unique(code, name)
 );

 create table if not exists licitacao_modalidades(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists elementos_despesa(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

--
-- the table bellow has 6M records
--
 CREATE TABLE if not exists empenhos(
 id integer primary key,
 ano_exercicio integer not null,
 nr_empenho varchar not null,
 valor decimal,
 municipio_id integer not null,
 orgao_id integer not null,
 funcao_governo_id integer,
 subfuncao_governo_id integer,
 cd_programa integer,
 cd_acao integer,
 fonte_recurso_id integer,
 cd_aplicacao_fixo integer,
 elemento_id integer,
 unique(municipio_id, nr_empenho)
 );


--
-- the table bellow has 6M records
--
 CREATE TABLE if not exists despesas_master(
 id integer primary key,
 empenho_id integer not null,
 modalidade_lic integer not null,
 credor_id integer not null,
 historico_despesa varchar collate nocase_slna,
 unique(credor_id, empenho_id)
 );

--
-- the table bellow has 24M records
--
 CREATE TABLE if not exists despesas_detalhe(
 id integer primary key,
 id_despesa_detalhe integer not null,
 despesa_id integer not null,
 mes_referencia integer not null,
 tp_despesa_id integer not 

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

[Don V Nielsen]

In the below view, what is "a"? A FROM is not defined.

--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_master_list_view_tidy" AS
SELECT
a.*,
b.name as modalidade_licitacao,
c.name as credor,
d.*
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;


On Fri, Jan 6, 2017 at 3:27 AM, Domingo Alvarez Duarte 
wrote:

> Hello Richard !
>
> The simple example I've sent is the minimal to show the problem, the real
> database schema where I found this problem has a lot of small tables joined
> and I was using the views to simplify (not duplicate) code, so on that case
> it'll result in bloat and repetition. see bellow:
>
> ===
>
> create table if not exists municipios(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists municipios_orgaos(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists municipios_poder(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists credores(
> id integer primary key,
> cnpj_cpf varchar collate nocase_slna,
> name varchar not null collate nocase_slna,
> unique(cnpj_cpf, name)
> );
>
> create table if not exists tipos_despesa(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists funcoes_governo(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists subfuncoes_governo(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists programas_governo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists acoes_governo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists fontes_recursos(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists aplicacoes_fixo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists aplicacoes_variavel(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists licitacao_modalidades(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists elementos_despesa(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> --
> -- the table bellow has 6M records
> --
> CREATE TABLE if not exists empenhos(
> id integer primary key,
> ano_exercicio integer not null,
> nr_empenho varchar not null,
> valor decimal,
> municipio_id integer not null,
> orgao_id integer not null,
> funcao_governo_id integer,
> subfuncao_governo_id integer,
> cd_programa integer,
> cd_acao integer,
> fonte_recurso_id integer,
> cd_aplicacao_fixo integer,
> elemento_id integer,
> unique(municipio_id, nr_empenho)
> );
>
>
> --
> -- the table bellow has 6M records
> --
> CREATE TABLE if not exists despesas_master(
> id integer primary key,
> empenho_id integer not null,
> modalidade_lic integer not null,
> credor_id integer not null,
> historico_despesa varchar collate nocase_slna,
> unique(credor_id, empenho_id)
> );
>
> --
> -- the table bellow has 24M records
> --
> CREATE TABLE if not exists despesas_detalhe(
> id integer primary key,
> id_despesa_detalhe integer not null,
> despesa_id integer not null,
> mes_referencia integer not null,
> tp_despesa_id integer not null,
> dt_emissao_despesa date not null,
> vl_despesa decimal
> );
>
> CREATE VIEW if not exists "empenhos_list_view" AS
> SELECT
> a."id",
> a."ano_exercicio",
> a."nr_empenho",
> a."valor",
> d.name as municipio,
> e.name as orgao,
> f.name as funcao,
> g.name as subfuncao,
> h.name as programa,
> i.name as acao,
> k.name as fonte_recurso,
> l.name as aplicacao_fixo,
> n.name as elemento,
> 

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[Domingo Alvarez Duarte]

Hello Simon !

Thanks for reply !

I already raised this point here before and asked people using sqlite 
with multi million records and they said that they use sqlite mostly as 
append/read only on this case.


I raised one example when sqlite uses too much memory and even Richard 
Hipp recognized it.


I understand that sqlite is a good piece of software and indeed I use it 
a lot and care about it, but that doesn't mean it actually doesn't have 
limitations and room for improvement, I'm not detracting sqlite I'm 
pointing areas where it doesn't perform well.


Cheers !

==

On 10/2/16, Domingo Alvarez Duarte  wrote:


Hello !

I'm still fighting to use sqlite with a 20GB database and now I'm trying
to update a table with 980M rows but sqlite is eating all my memory
(2GB) and making blocking my computer (too much swap).

I'm in doubt if sqlite can really manage databases above 1GB.


For some (many) UPDATE statements, SQLite has to make two passes.  On
the first pass, it determines the ROWID of every row in the table that
needs to be changed, and then on the second pass it updates those
rows.

You are trying to updates 900 million rows all in one go, and SQLite
requires 8 bytes for each row, so that is 7.2 GB of RAM right there,
just to hold all of the rowids.

For your specific UPDATE, it seems like SQLite ought to be able to do
it all in one pass, and thus avoid using all that memory to store the
rowids.  This looks like an opportunity to improve the query planner.
-- D. Richard Hipp d...@sqlite.org

==


On 06/01/17 10:00, Simon Slavin wrote:

On 6 Jan 2017, at 10:10am, Domingo Alvarez Duarte  wrote:


Why I want this ?

To overcome sqlite3 limitations with one writer per database and difficulty to 
manage multi million records tables spreading then in individual databases.

SQLite does not have a limitation for one writer per database.  SQLite does not 
have any problem with multi-million records in a database.  I have a database 
with a table which has over a billion rows in it.

You may have other reasons for designing your database structure this way, but 
you are not doing it because of limitations in SQLite.

Simon.
___
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] Quest for "scratch table" implementation in SQLite.

[Simon Slavin]

On 6 Jan 2017, at 10:10am, Domingo Alvarez Duarte  wrote:

> Why I want this ?
> 
> To overcome sqlite3 limitations with one writer per database and difficulty 
> to manage multi million records tables spreading then in individual databases.

SQLite does not have a limitation for one writer per database.  SQLite does not 
have any problem with multi-million records in a database.  I have a database 
with a table which has over a billion rows in it.

You may have other reasons for designing your database structure this way, but 
you are not doing it because of limitations in SQLite.

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

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[Domingo Alvarez Duarte]

Hello !

I'm also looking for a way to achieve something similar but using 
attached databases, I mean:


Open a local database that automatically attach other databases and have 
views/triggers that use/apply to the attached databases.


Why I want this ?

To overcome sqlite3 limitations with one writer per database and 
difficulty to manage multi million records tables spreading then in 
individual databases.


Something that the "temp" database can already partially do without 
persistence.


Cheers !


On 06/01/17 06:09, Dominique Devienne wrote:

On Thu, Jan 5, 2017 at 11:40 PM, Simon Slavin  wrote:


On 5 Jan 2017, at 7:26pm, Smith, Randall  wrote:

I'm still trying to figure out a graceful way to implement a "scratch"

database table that has the following properties:

o Can be created on demand while a database connection is open.
o Can include references to other material in the database to

ensure integrity.

o Will be automatically deleted when the database is closed.

It’s not possible to combine the second and third requirements.  SQLite is
designed as a multi-process multi-user database.


Not quite though. SQLite does support multi-process multi-user databases of
course,
but one of its primary use case is as an application format too, and often
in that
configuration, there's 1 process and 1 user. Or during an EXCLUSIVE
transaction too.

FWIW. --DD
___
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] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

Hello Richard !

The simple example I've sent is the minimal to show the problem, the 
real database schema where I found this problem has a lot of small 
tables joined and I was using the views to simplify (not duplicate) 
code, so on that case it'll result in bloat and repetition. see bellow:


===

create table if not exists municipios(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists municipios_orgaos(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists municipios_poder(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists credores(
id integer primary key,
cnpj_cpf varchar collate nocase_slna,
name varchar not null collate nocase_slna,
unique(cnpj_cpf, name)
);

create table if not exists tipos_despesa(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists funcoes_governo(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists subfuncoes_governo(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists programas_governo(
id integer primary key,
code integer,
name varchar not null collate nocase_slna,
unique(code, name)
);

create table if not exists acoes_governo(
id integer primary key,
code integer,
name varchar not null collate nocase_slna,
unique(code, name)
);

create table if not exists fontes_recursos(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists aplicacoes_fixo(
id integer primary key,
code integer,
name varchar not null collate nocase_slna,
unique(code, name)
);

create table if not exists aplicacoes_variavel(
id integer primary key,
code integer,
name varchar not null collate nocase_slna,
unique(code, name)
);

create table if not exists licitacao_modalidades(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists elementos_despesa(
id integer primary key,
name varchar not null unique collate nocase_slna
);

--
-- the table bellow has 6M records
--
CREATE TABLE if not exists empenhos(
id integer primary key,
ano_exercicio integer not null,
nr_empenho varchar not null,
valor decimal,
municipio_id integer not null,
orgao_id integer not null,
funcao_governo_id integer,
subfuncao_governo_id integer,
cd_programa integer,
cd_acao integer,
fonte_recurso_id integer,
cd_aplicacao_fixo integer,
elemento_id integer,
unique(municipio_id, nr_empenho)
);


--
-- the table bellow has 6M records
--
CREATE TABLE if not exists despesas_master(
id integer primary key,
empenho_id integer not null,
modalidade_lic integer not null,
credor_id integer not null,
historico_despesa varchar collate nocase_slna,
unique(credor_id, empenho_id)
);

--
-- the table bellow has 24M records
--
CREATE TABLE if not exists despesas_detalhe(
id integer primary key,
id_despesa_detalhe integer not null,
despesa_id integer not null,
mes_referencia integer not null,
tp_despesa_id integer not null,
dt_emissao_despesa date not null,
vl_despesa decimal
);

CREATE VIEW if not exists "empenhos_list_view" AS
SELECT
a."id",
a."ano_exercicio",
a."nr_empenho",
a."valor",
d.name as municipio,
e.name as orgao,
f.name as funcao,
g.name as subfuncao,
h.name as programa,
i.name as acao,
k.name as fonte_recurso,
l.name as aplicacao_fixo,
n.name as elemento,
a."municipio_id",
a."orgao_id",
a."funcao_governo_id",
a."subfuncao_governo_id",
a."cd_programa",
a."cd_acao",
a."fonte_recurso_id",
a."cd_aplicacao_fixo",
a."elemento_id"
FROM "empenhos" AS a
LEFT JOIN municipios AS d ON a.municipio_id=d.id
LEFT JOIN municipios_orgaos AS e ON a.orgao_id=e.id
LEFT JOIN funcoes_governo AS f ON a.funcao_governo_id=f.id
LEFT JOIN subfuncoes_governo AS g ON a.subfuncao_governo_id=g.id
LEFT JOIN programas_governo AS h ON a.cd_programa=h.id
LEFT JOIN acoes_governo AS i ON a.cd_acao=i.id
LEFT JOIN fontes_recursos AS k ON a.fonte_recurso_id=k.id
LEFT JOIN aplicacoes_fixo AS l ON a.cd_aplicacao_fixo=l.id
LEFT JOIN elementos_despesa AS n ON a.elemento_id=n.id;
--
-- Ideally I want to write the next query using the previous view
--
CREATE 

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[Dominique Devienne]

On Thu, Jan 5, 2017 at 11:40 PM, Simon Slavin  wrote:

> On 5 Jan 2017, at 7:26pm, Smith, Randall  wrote:
> > I'm still trying to figure out a graceful way to implement a "scratch"
> database table that has the following properties:
> >
> >o Can be created on demand while a database connection is open.
> >o Can include references to other material in the database to
> ensure integrity.
> >o Will be automatically deleted when the database is closed.
>
> It’s not possible to combine the second and third requirements.  SQLite is
> designed as a multi-process multi-user database.
>

Not quite though. SQLite does support multi-process multi-user databases of
course,
but one of its primary use case is as an application format too, and often
in that
configuration, there's 1 process and 1 user. Or during an EXCLUSIVE
transaction too.

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