[sqlite] Problem with SQLite version 3.9.2 together with user authentication extension

[Ulrich Telle]

I further tracked the problem down to lines 379 to 384 in build.c:

#if SQLITE_USER_AUTHENTICATION
   else if( pParse->db->auth.authLeveldb->auth.authLevel != 0 && 
pParse->db->auth.authLevel SQLite version 3.8.7 introduced the user authentication extension. This
> extension worked well for all 3.8.x versions.
>
> Now I intended to upgrade to SQLite version 3.9.2. However, if I enable
> the user authentication extension using the compile time define
> SQLITE_USER_AUTHENTICATION I experience problems.
>
> If I start with a new, empty database (using the SQLite shell), it
> should not require user authentication. However, I always get the error
> message
>
> Error: SQL logic error or missing database
>
> as soon as I enter for example a "create table" statement.
>
> I tracked it down to a call of function sqlite3Parser in function
> sqlite3RunParser. The statement to be parsed is
>
> SELECT name, rootpage, sql FROM 'main'.sqlite_master ORDER BY rowid
>
> The call is in line 461 of tokenize.c:
>
> sqlite3Parser(pEngine, TK_SEMI, pParse->sLastToken, pParse);
>
> This function returns with a return code 1 in pParse->rc. And
> pParse->zErrMsg contains "user not authenticated".
>
> Is this a bug in SQLite somewhere? Or am I doing something wrong?
>
> Regards,
>
> Ulrich

[sqlite] Problem with SQLite version 3.9.2 together with user authentication extension

[Ulrich Telle]

SQLite version 3.8.7 introduced the user authentication extension. This 
extension worked well for all 3.8.x versions.

Now I intended to upgrade to SQLite version 3.9.2. However, if I enable 
the user authentication extension using the compile time define 
SQLITE_USER_AUTHENTICATION I experience problems.

If I start with a new, empty database (using the SQLite shell), it 
should not require user authentication. However, I always get the error 
message

Error: SQL logic error or missing database

as soon as I enter for example a "create table" statement.

I tracked it down to a call of function sqlite3Parser in function 
sqlite3RunParser. The statement to be parsed is

SELECT name, rootpage, sql FROM 'main'.sqlite_master ORDER BY rowid

The call is in line 461 of tokenize.c:

sqlite3Parser(pEngine, TK_SEMI, pParse->sLastToken, pParse);

This function returns with a return code 1 in pParse->rc. And 
pParse->zErrMsg contains "user not authenticated".

Is this a bug in SQLite somewhere? Or am I doing something wrong?

Regards,

Ulrich

[sqlite] drop, create and copy a table

[Héctor Fiandor]

Dear Mr. Hipp and other members:

I have a table where I have introduced a lot of information during 2015.

In December 31, I have to select the records to be continue in 2016.
Previously, I have obtained a copy of the 2015 table for any consult.

In 2016, the year start with a table "cleaned" of the old records, with the
records to be continued as the firsts, for these reasons, I thought to drop
the table and create a new one, "cleaned" of any history related to 2015,
with the Id starting in "1".

Really, I don?t know that making the step (2) suggested by Mr. Hipp followed
by step (3) without dropping the table "clean" the history of the table and
start the Id with "1".

Really, I have not used de BEGIN...COMMINT commands.

I will test the routines as suggested by Mr. Hipp.

Any suggestion will be received as a gift.

Thanks very much for your answers.

Yours

Ing. H?ctor Fiandor
hfiandor at ceniai.inf.cu





-Mensaje original-
De: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] En nombre de H?ctor
Fiandor
Enviado el: mi?rcoles, 25 de noviembre de 2015 03:43 p. m.
Para: 'General Discussion of SQLite Database'
Asunto: [sqlite] drop, create and copy a table

Dear members:



I have asked previously about this matter but nobody answer me.

I have a table to be copied in another one with other name as a temp.

Then I drop the first one.

Then I create another one with same as the dropped table.

Then I copy from the temp to the new created.



I do all this but in three phases I may answer is It is possible to do this
in one phase?



Thanks in advance



Ing. H?ctor Fiandor

hfiandor at ceniai.inf.cu



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

[sqlite] FTS5 prefix index documentation may be incorrect

[Dan Kennedy]

On 11/25/2015 05:11 AM, Charles Leifer wrote:
> The FTS5 prefix index documentation[1] seems to not be working. I've tried
> with SQLite 3.9.0 and 3.10.0 (2015-11-06) and both show the same error
> messages.

Thanks for reporting this. Now fixed here:

   http://sqlite.org/src/info/11eb8e877e2ba859

Dan.


>
> Examples:
>
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3');
> Error: malformed prefix=... directive
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);
> Error: multiple prefix=... directives
>
> What does appear to work is using a comma:
>
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
> sqlite> .schema ft
> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
>
> 1: https://sqlite.org/fts5.html#section_4_2
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] drop, create and copy a table

[Bernardo Sulzbach]

What are you trying to do? Rename the table? You can use
https://www.sqlite.org/lang_altertable.html for that.

Please make your intentions clearer.

On Wed, Nov 25, 2015 at 6:43 PM, H?ctor Fiandor  
wrote:
> Dear members:
>
>
>
> I have asked previously about this matter but nobody answer me.
>
> I have a table to be copied in another one with other name as a temp.
>
> Then I drop the first one.
>
> Then I create another one with same as the dropped table.
>
> Then I copy from the temp to the new created.
>
>
>
> I do all this but in three phases I may answer is It is possible to do this
> in one phase?
>
>
>
> Thanks in advance
>
>
>
> Ing. H?ctor Fiandor
>
> hfiandor at ceniai.inf.cu
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] Fwd: Re: [sx-users] Corruption storage

[Török Edwin]

On 11/25/2015 06:19 PM, Richard Hipp wrote:
>>
>> The I/O path is more complicated than I originally thought though:
>> FreeBSD server (running our app with SQLite) with UFS+SU -> Linux (Xen host)
>> -> FreeBSD (ISCSI + ZFS)
> 
> I don't know what this means.  Is there a network filesystem involved?
> 

A network blockdevice is involved.
The physical storage is FreeBSD ZFS, which exports block devices (stored on 
ZFS) over ISCSI to Linux hosts that run Xen.
Then FreeBSD is run as guest in that Xen server, and uses its own filesystem 
(UFS+SoftUpdates) on top of the block device exported over the network (via 
ISCSI).

Those are lots of pieces where something can go wrong, I'm doing some tests to 
see whether I can reproduce the problem on a more usual setup (just one OS, one 
disk, and no hypervisors or network block devices).

-- 
Edwin T?r?k | Co-founder and Lead Developer

Skylable open-source object storage: reliable, fast, secure
http://www.skylable.com

[sqlite] [Sqlite3] segfault in sqlite3_step()

[김수민]

hi.

My program received signal SIGSEGV during performed to sqlite_step().

It was occurs only one time still.. but I should know cause. so I need help
you. please.



*Code:*

*// Already prepared statement using sqlite3_prepare_v2(db, QUERY, -1,
, NULL).*

*{*

*sqlite3  *db;*

*sqlite3_stmt *stmt;*

*...*

*RETRY:*

*   sqlite3_reset(stmt);*

*   sqlite3_bind_int(stmt, 1, dir);*

*   while((ret = sqlite3_step(stmt)) == SQLITE_ROW)*

*   {*

* id = sqlite3_column_int(stmt, 0);*

* name = (char*)sqlite3_column_text(stmt, 1);*

* ...*

* found++;*

*   }*

*   if(ret == SQLITE_BUSY && !found)*

*   {*

* msleep(50);*

* goto RETRY;*

*   }*

*   else if( ret != SQLITE_DONE && ret != SQLITE_ROW)*

*   {*

* sqlite3_errcode(db);*

* goto ERROR;*

*   }*

*   sqlite3_reset(stmt);*

*   return SUCCESS;*

*ERROR:*

*   sqlite3_reset(stmt);*

*   return FAILURE;*

*}*



It was debugging result..

line "while((ret = sqlite3_step(stmt)) == SQLITE_ROW)"



and sqlite3 library debugging result..

$ addr2line -e libsqlite3.so.0 0x5b81a

./sqlite3.c:64118

line " while( (rc = sqlite3Step(v))==SQLITE_SCHEMA

 && cnt++ < SQLITE_MAX_SCHEMA_RETRY

 && (rc2 = rc = sqlite3Reprepare(v))==SQLITE_OK )"



sqlite3 version is 3.7.13.



I think perhaps..

By some reason was error code returns in sqlite3_step()

and then sqlite3_reset() is returning an invalid pointer.

But I don't know why received signal SIGSEGV.



In any case I want to know what occurs segfault in sqlite3_step().

help me, please.



thank you.

[sqlite] Fwd: Re: [sx-users] Corruption storage

[Török Edwin]

On 11/25/2015 05:37 PM, Richard Hipp wrote:
> On 11/25/15, T?r?k Edwin  wrote:
>> Hi,
>>
>> A user of our application (CC-ed) reported a corruption in an SQLite DB on
>> FreeBSD (see below). There was no mmap used in this case, just a WAL DB with
>> PRAGMA SYNCHRONOUS=NORMAL.
>> From my reading of the SQLite docs this should not result in a corrupt DB,
>> worst case some COMMITs could be lost/missing.
>>
> 
> The database file "temp.db" and the WAL file "temp.db-wal" appear
> unrelated to one another.  It as if they came from different places,
> or from different points in time.
> 
> The database file contains 272 pages, but only the first 8 pages are
> used for content.  The final 264 pages of the file are all "freelist"
> pages, ready to be reused.
> 
> The WAL file contains 15 separate transactions.  But every single one
> of those transactions says that the database is only 24 pages long.
> Furthermore, 12 of the 15 transactions (including the first two and
> the last one) only touch pages that are on the freelist of the
> database, which is not really possible.

Hmm after the WAL is recovered the DB is indeed 24 pages long:
-rw-r--r-- 1 edwin edwin 24K Nov 25 17:47 temp.db

Here are the original files on the original server:
# stat /usr/local/sxserver/lib/sxserver/storage
79 244829 drwxr-x--- 2 nobody nobody 486168 7168 "Jul 23 17:24:08 2015" "Nov 25 
12:57:30 2015" "Nov 25 12:57:30 2015" "Jul 23 17:24:08 2015" 32768 16 0 
/usr/local/sxserver/lib/sxserver/storage

# stat /usr/local/sxserver/lib/sxserver/storage/temp.db
79 244943 -rw-r--r-- 1 nobody nobody 526624 278528 "Nov 25 13:57:34 2015" "Oct 
13 15:30:47 2015" "Oct 13 15:30:47 2015" "Jul 23 17:24:09 2015" 32768 544 0 
/usr/local/sxserver/lib/sxserver/storage/temp.db

# stat /usr/local/sxserver/lib/sxserver/storage/temp.db-wal
79 241313 -rw-r--r-- 1 nobody nobody 495152 36712 "Nov 25 13:57:34 2015" "Nov 
25 13:51:49 2015" "Nov 25 13:51:49 2015" "Nov 25 12:57:29 2015" 32768 72 0 
/usr/local/sxserver/lib/sxserver/storage/temp.db-wal

It looks like the timestamps are ordered from oldest to newest: temp.db, 
, temp.db-wal.

We do use SQLITE_CHECKPOINT_PASSIVE and SQLITE_CHECKPOINT_RESTART when certain 
thresholds or timer trigger, what happens if during a passive checkpoint it 
doesn't sync the DB?
"Checkpoint as many frames as possible without waiting for any database readers 
or writers to finish, then sync the database file if all frames in the log were 
checkpointed."
Would it still update the size of the .db file without syncing the parent dir?

> 
> I don't know how this could come about.  It's like you took a random
> database file and a random WAL file and paired them together by giving
> them similar names.
> 
> Do you have any additional information about how this problem might have 
> arisen?

Power was lost, we're doing some tests to reproduce the problem.

The I/O path is more complicated than I originally thought though:
FreeBSD server (running our app with SQLite) with UFS+SU -> Linux (Xen host) -> 
FreeBSD (ISCSI + ZFS)

Thanks for the quick response,
-- 
Edwin T?r?k | Co-founder and Lead Developer

Skylable open-source object storage: reliable, fast, secure
http://www.skylable.com

[sqlite] extension development

[Charles Leifer]

You could create a virtual table and use insert statements to generate new
functions. Not sure if that's actually any better though.
On Nov 25, 2015 3:10 PM, "Abilio Marques"  wrote:

> Hi,
>
> I've finished a version of an extension that let's you define new SQL
> functions using JavaScript,
>
> https://github.com/abiliojr/sqlite-js
>
>
> But I have 2 questions to see if someone can give me ideas:
>
> 1. So far, to call the function that defines new function, I'm using
> SELECT, e.g.,
> ??
>
> SELECT createjs('cos', 'Math.cos(arg[0])');
>
> Is there a better way to call a function defined with
> sqlite3_create_function,
> as in this case, I'm not really performing an SQL operation. Something like
> a pragma would be useful, but I can't seem to find a way to define a new
> one from within an extension.
>
> 2. That function (createjs) returns errors in case of error, but, is there
> a cleaner, more standard way to return an OK status? So far I've checked
> the C API, but I didn't manage to find something like that. Do functions
> need to return anything at all?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Fwd: Re: [sx-users] Corruption storage

[Török Edwin]

Hi,

A user of our application (CC-ed) reported a corruption in an SQLite DB on 
FreeBSD (see below). There was no mmap used in this case, just a WAL DB with 
PRAGMA SYNCHRONOUS=NORMAL.
>From my reading of the SQLite docs this should not result in a corrupt DB, 
>worst case some COMMITs could be lost/missing.

The DB is recoverable with SQLite 3.9.2 on Linux.

In fact the DB itself is not corrupt, but when WAL recovery is run on startup 
it corrupts the DB.
Would it be possible to provide a way (via a PRAGMA?) to detect corruption when 
recovering the WAL and rather than corrupting the DB to allow the application 
to ignore the corrupt WAL entries?

SQLite is 3.8.10 version: 2015-05-07 11:53:08 
cf975957b9ae671f34bb65f049acf351e650d437, compile options: OMIT_LOAD_EXTENSION 
SYSTEM_MALLOC THREADSAFE=0

$ ls -l temp.db*
-rw-r--r--  1 nobody  nobody  278528 Oct 13 15:31 temp.db
-rw-r--r--  1 nobody  nobody   32768 Oct 13 15:41 temp.db-shm
-rw-r--r--  1 nobody  nobody   65008 Oct 13 15:28 temp.db-wal
$ ./sqlite3 temp.db
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .schema
Error: database disk image is malformed
sqlite> pragma integrity_check;
Error: database disk image is malformed
sqlite> .clone /tmp/recover2
hashfs... done
tmpfiles... done
tmpmeta... done
sqlite_autoindex_hashfs_1... done
sqlite_autoindex_tmpfiles_1... done
tmpfiles_ttl... done
sqlite_autoindex_tmpmeta_1... done
sqlite> .open /tmp/recover2
sqlite> pragma integrity_check
   ...> ;
ok
sqlite> .schema
CREATE TABLE hashfs (key TEXT NOT NULL PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE tmpfiles (tid INTEGER PRIMARY KEY, token TEXT (32) NULL UNIQUE, 
volume_id INTEGER NOT NULL, name TEXT (1024) NOT NULL, size INTEGER NOT NULL 
DEFAULT 0, t TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f')), flushed 
INTEGER NOT NULL DEFAULT 0, content BLOB, uniqidx BLOB, ttl INTEGER NOT NULL 
DEFAULT 0, avail BLOB);
CREATE TABLE tmpmeta (tid INTEGER NOT NULL REFERENCES tmpfiles(tid) ON DELETE 
CASCADE ON UPDATE CASCADE, key TEXT (256) NOT NULL, value BLOB (1024) NOT NULL, 
PRIMARY KEY (tid, key));
CREATE INDEX tmpfiles_ttl ON tmpfiles(ttl) WHERE ttl > 0;

Lets try without the WAL
$ tar xf temp.tar.gz && cd temp
$ rm temp.db-*
$ sqlite3 temp.db
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> pragma integrity_check;
ok

I have uploaded the corrupted DB and WAL files here in case someone wants to 
take a look: http://vol-public.s3.indian.skylable.com:8008/corrupted/temp.tar.gz

There are other corrupted DBs too, for example this small one which is the 
first one opened by our application (and thus no WAL file because it was 
recovered already)

$ ls -l /usr/local/sxserver/lib/sxserver/storage/hashfs.db*
-rw-r--r--  1 nobody  nobody  20480 Nov 25 14:17 
/usr/local/sxserver/lib/sxserver/storage/hashfs.db

[2015-11-25 13:55:22.226] sx.fcgi[630]: Notice  | [qlog]: SQLite result 0x11b: 
recovered 59 frames from WAL file 
/usr/local/sxserver/lib/sxserver/storage/hashfs.db-wal
[2015-11-25 13:55:22.226] sx.fcgi[630]: ALERT   | [qlog]: SQLite result 0xb: 
database corruption at line 55740 of [cf975957b9]
[2015-11-25 13:55:22.226] sx.fcgi[630]: ALERT   | [qlog]: SQLite result 0xb: 
database disk image is malformed

 Forwarded Message 
Subject: Re: [sx-users] Corruption storage
Date: Wed, 25 Nov 2015 17:21:31 +0300
From: Veniamin Gvozdikov 
Reply-To: sx-users 
To: sx-users 

2015-11-25 17:17 GMT+03:00 T?r?k Edwin :

> On 11/25/2015 04:01 PM, Veniamin Gvozdikov wrote:
> > Hello,
> >
> > My server has lost power. I can't run sxserver and get some errors:
>
> What are your operating system, kernel and SX versions?
>

root at sx02:~ # uname -a
FreeBSD sx02 10.1-RELEASE-p15 FreeBSD 10.1-RELEASE-p15 #0: Tue Jul 21
18:00:00 UTC 2015
root at amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC
amd64

SX version is 1.2

Which filesystem do you use for SX node storage, and what are its mount
> options?
>

UFS+SU

root at sx02:~ # mount
/dev/ada0p2 on / (ufs, local, journaled soft-updates)



>
> Please read points 3 onward here and see if any applies to your OS/disks:
> https://www.sqlite.org/howtocorrupt.html
>
> Also some general information on hard disk caches and a script that can be
> used to test
> whether your I/O path honors fsync:
> http://www.postgresql.org/docs/9.4/static/wal-reliability.html
>
> >
> > Nov 25 13:58:38 sx02 sx.fcgi[837]: [qlog]: SQLite result 0xb: database
> > corruption at line 55740 of [cf975957b9]
> > Nov 25 13:58:38 sx02 sx.fcgi[837]: [qlog]: SQLite result 0xb: database
> disk
> > image is malformed
> > Nov 25 13:58:38 sx02 sx.fcgi[837]: [qprep_db]: Cannot prepare query
> "PRAGMA
> > synchronous = NORMAL": database disk image is malformed
> > Nov 25 13:58:38 sx02 sx.fcgi[837]: [main]: Failed to initialize the
> storage
> > interface
> > Nov 25 13:58:38 sx02 vg: /usr/local/etc/rc.d/sxserver: WARNING: failed to
> > start sxserver
> > Nov 25 

[sqlite] extension development

[Abilio Marques]

Hi,

I've finished a version of an extension that let's you define new SQL
functions using JavaScript,

https://github.com/abiliojr/sqlite-js


But I have 2 questions to see if someone can give me ideas:

1. So far, to call the function that defines new function, I'm using
SELECT, e.g.,
??

SELECT createjs('cos', 'Math.cos(arg[0])');

Is there a better way to call a function defined with sqlite3_create_function,
as in this case, I'm not really performing an SQL operation. Something like
a pragma would be useful, but I can't seem to find a way to define a new
one from within an extension.

2. That function (createjs) returns errors in case of error, but, is there
a cleaner, more standard way to return an OK status? So far I've checked
the C API, but I didn't manage to find something like that. Do functions
need to return anything at all?

[sqlite] Dont Repeat Yourself (DRY) and SQLite

[Bernardo Sulzbach]

On Wed, Nov 25, 2015 at 1:14 PM, Simon Slavin  wrote:
>
> CREATE TABLE use_mytype (...
>...
>one_type TEXT REFERENCES enum_type(theID) DEFAULT 2
> )
>
> It is now harder to know which value to insert for one_type.
>
> Simon.
>

I know this works, but I would suggest using INTEGER instead of text
for one_type. It seems like better design.

Simon S., DRY makes a lot of sense (and is pretty awesome) *in
application code*. You may have been doing it a lot and just didn't
know the name. Data models may be more referential than replicated
(point to where the text is instead of copying it), but I don't think
that this brings huge advantages.

-- 
Bernardo Sulzbach

[sqlite] Fwd: Re: [sx-users] Corruption storage

[Simon Slavin]

On 25 Nov 2015, at 4:26pm, T?r?k Edwin  wrote:

> Those are lots of pieces where something can go wrong, I'm doing some tests 
> to see whether I can reproduce the problem on a more usual setup (just one 
> OS, one disk, and no hypervisors or network block devices).

You betcha.  I'm betting that file system integrity is being lost somewhere 
over that mass of connections.  Probably one of them is faking locking without 
doing it correctly.  See item 1 of the checklist at the end of



Simon.

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Clemens Ladisch]

Kirill M?ller wrote:
> For a left join with a subquery on the right-hand side, that subquery
> doesn't seem to be flattened.

This is rule 3 of .


Regards,
Clemens

[sqlite] drop, create and copy a table

[Igor Tandetnik]

On 11/25/2015 3:43 PM, H?ctor Fiandor wrote:
> I have asked previously about this matter but nobody answer me.
>
> I have a table to be copied in another one with other name as a temp.
>
> Then I drop the first one.
>
> Then I create another one with same as the dropped table.
>
> Then I copy from the temp to the new created.
>
>
>
> I do all this but in three phases I may answer is It is possible to do this
> in one phase?

I can do it in zero phases. As far as I can tell from your description, 
the sequence leaves the database in the exact same state it started 
from. So you may as well do nothing at all and achieve the same result.
-- 
Igor Tandetnik

[sqlite] drop, create and copy a table

[Richard Hipp]

On 11/25/15, H?ctor Fiandor  wrote:
> Dear members:
>
>
>
> I have asked previously about this matter but nobody answer me.
>
> I have a table to be copied in another one with other name as a temp.
>
> Then I drop the first one.
>
> Then I create another one with same as the dropped table.
>
> Then I copy from the temp to the new created.
>

Why not instead:

(1) BEGIN;
(2) ALTER TABLE table_to_change RENAME TO temporary_name;
(3) CREATE TABLE table_to_change(... new schema here);
(4) INSERT INTO table_to_change SELECT ... FROM temporary_name;
(5) DROP TABLE temporary_name;
(6) COMMIT;

The BEGIN...COMMIT makes everything appear to happen all at once.

The use of ALTER TABLE in step (2) means that you only copy the data
once instead of twice.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] drop, create and copy a table

[Héctor Fiandor]

Dear members:



I have asked previously about this matter but nobody answer me.

I have a table to be copied in another one with other name as a temp.

Then I drop the first one.

Then I create another one with same as the dropped table.

Then I copy from the temp to the new created.



I do all this but in three phases I may answer is It is possible to do this
in one phase?



Thanks in advance



Ing. H?ctor Fiandor

hfiandor at ceniai.inf.cu

[sqlite] Dont Repeat Yourself (DRY) and SQLite

[Domingo Alvarez Duarte]

This way we repeat the string everywhere then it's not DRY !  

Cheers !  
>  Wed Nov 25 2015 11:38:50 am CET CET from "Simon Slavin"
>  Subject: Re: [sqlite] Dont Repeat Yourself (DRY) and
>SQLite
>
>  On 24 Nov 2015, at 11:24pm, Domingo Alvarez Duarte
> wrote:
> 
>  
>>If we do that we'll be repeating the same string on every column and need a
>> string comparison, with postgres enum types or foreign keys it's an
>>integer
>> and no repetition. 
>> 

>  Ah. Okay.
> 
> Well SQLite implements FOREIGN KEYs correctly. So where in Postgres you
>would use an enum you can use a foreign key in SQLite.
> 
> CREATE TABLE enum_type (theValue TEXT PRIMARY KEY);
> INSERT INTO enum_type VALUES ('simple'), ('tuple');
> 
> CREATE TABLE use_mytype (...
> ...
> one_type TEXT REFERENCES enum_type(theValue) DEFAULT 'tuple'
> )
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Bug report for MAX()

[R Smith]

Many thanks to all.

I should have checked - That table was not supposed to be able to even 
get strings in there - this exposed a bug in an application of ours too. 
Adding check constraints right away.
Thanks!


On 2015/11/25 1:56 PM, Richard Hipp wrote:
> On 11/25/15, Dave McKee  wrote:
>> I can replicate this behaviour if I insert a zero-length string into the
>> column.
>>
>> sqlite> create table foo(a);
>> sqlite> insert into foo values(5);
>> sqlite> insert into foo values("");
>> sqlite> select max(a) from foo;
>>
>> sqlite> select min(a) from foo;
>> 5
>> sqlite> select avg(a) from foo;
>> 2.5
>>
>> Is this a possible explanation?
>>
> Indeed.  Ryan sent me the database via private email:
>
> sqlite> select typeof(max(unitcost)), length(max(unitcost)) from bomdata;
> text|0
>

[sqlite] Dont Repeat Yourself (DRY) and SQLite

[Darren Duncan]

I think Domingo is showing signs of cargo-culting, simply taking some mantra to 
the extreme without even realizing the point of the mantra.

Logically speaking, enumerated values like this 'simple'/'tuple' are program 
identifiers in the same way as variable or field names are.  These enums are 
part of the code, not part of the data.  It makes as much sense to replace them 
with numbers as it is to give all variables and fields names that are numbers, 
because you don't want to repeat the variable/etc names all over the place.

The DRY concept is not meant to say use only numbers for enums.  In this 
particular case, using identifiers, which are strings, is the correct course of 
action.

DRY is better applied where the subject may be mutable like data, such as a 
person's name, not where it is code like these enums.

When I say like code, I mean that presumably with these 'simple'/'tuple' there 
are various places in the application that specifically dispatch different 
logic 
depending on those values, whereas with data, such as an enumeration of country 
names, it would not be the case.

-- Darren Duncan

On 2015-11-25 7:14 AM, Simon Slavin wrote:
> On 25 Nov 2015, at 2:23pm, Domingo Alvarez Duarte  dev.dadbiz.es> wrote:
>
>> This way we repeat the string everywhere then it's not DRY !
>
> You know, I think you're the first person to mention DRY here.  I had to look 
> it up.
>
> 
>
> For some reason it seems that under DRY repeating a string in lots of places 
> is bad, but repeating a number in lots of places is good.  I'm not sure about 
> the logic behind that.
>
> Okay, do this instead:
>
> CREATE TABLE enum_type (the_ID INTEGER PRIMARY KEY, the_value TEXT UNIQUE);
> INSERT INTO enum_type (the_value) VALUES ('simple'), ('tuple');
>
> CREATE TABLE use_mytype (...
> ...
> one_type TEXT REFERENCES enum_type(theID) DEFAULT 2
> )
>
> It is now harder to know which value to insert for one_type.

[sqlite] Bug report for MAX()

[R Smith]

It seems there are some instances where MAX() does not return a value.

I will send such an offending DB direct, but the sqlite3.exe results as 
follows:

F:\[BACKUP]>sqlite3.exe IPDB_ImptData.idb
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> SELECT max(UnitCost) FROM BOMData;

sqlite> SELECT min(UnitCost) FROM BOMData;
0.0
sqlite> SELECT avg(UnitCost) FROM BOMData;
153.665947345878
sqlite> PRAGMA integrity_check(100);
ok
sqlite> SELECT max(UnitCost) FROM BOMData;

sqlite>

Note that all aggregates obtained for UnitCost on that table work, 
except max().

On other similar tables it works too, but on 2 specific ones I've found 
it not to be working. All other table checks seem to be in order. I've 
tested this only as far back as 3.8.11, but there it also did not work.

Thanks,
Ryan

[sqlite] Dont Repeat Yourself (DRY) and SQLite

[da...@dandymadeproductions.com]

> On 25 Nov 2015, at 2:23pm, Domingo Alvarez Duarte
 wrote:
>
>> This way we repeat the string everywhere then it's not DRY !
>
> You know, I think you're the first person to mention DRY here.  I had to
look it up.
>
> 
>
> For some reason it seems that under DRY repeating a string in lots of
places is bad,
> but repeating a number in lots of places is good.  I'm not sure about
the logic
> behind that.
>
> Okay, do this instead:
>
> CREATE TABLE enum_type (the_ID INTEGER PRIMARY KEY, the_value TEXT UNIQUE);
> INSERT INTO enum_type (the_value) VALUES ('simple'), ('tuple');
>
> CREATE TABLE use_mytype (...
>...
>one_type TEXT REFERENCES enum_type(theID) DEFAULT 2
> )
>
> It is now harder to know which value to insert for one_type.
>
> Simon.

Thank you. I have been just about completed a plugin for my tool,
MyJSQLView, that will convert a PostgreSQL database query to a
SQLite table, then transfer the data to the newly created memory
or file database table.

I left out ENUM types as a possible data type since it is relatively
new to PostgreSQL, well a year or few perhaps.

That should work nicely.

danap.

[sqlite] Bug report for MAX()

[Simon Slavin]

On 25 Nov 2015, at 11:39am, Dave McKee  wrote:

> Is this a possible explanation?

You got it.  This is part of what I was worried about.  MAX processes not just 
numbers.  It would be useful to know what kind of values Ryan has stored in 
that column.

Simon.

[sqlite] Bug report for MAX()

[Dave McKee]

I can replicate this behaviour if I insert a zero-length string into the
column.

sqlite> create table foo(a);
sqlite> insert into foo values(5);
sqlite> insert into foo values("");
sqlite> select max(a) from foo;

sqlite> select min(a) from foo;
5
sqlite> select avg(a) from foo;
2.5

Is this a possible explanation?

On Wed, Nov 25, 2015 at 11:37 AM, Simon Slavin  wrote:

>
> On 25 Nov 2015, at 11:09am, R Smith  wrote:
>
> > sqlite> SELECT max(UnitCost) FROM BOMData;
> >
> > sqlite> SELECT min(UnitCost) FROM BOMData;
> > 0.0
>
> Can you please post the result of
>
> SELECT DISTINCT typeof(UnitCost) FROM BOMData;
>
> (I think that's how you do it.  You might need to use GROUP BY.)
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Bug report for MAX()

[Simon Slavin]

On 25 Nov 2015, at 11:09am, R Smith  wrote:

> sqlite> SELECT max(UnitCost) FROM BOMData;
> 
> sqlite> SELECT min(UnitCost) FROM BOMData;
> 0.0

Can you please post the result of

SELECT DISTINCT typeof(UnitCost) FROM BOMData;

(I think that's how you do it.  You might need to use GROUP BY.)

Simon.

[sqlite] Fwd: Re: [sx-users] Corruption storage

[Richard Hipp]

On 11/25/15, T?r?k Edwin  wrote:
>>
>> The WAL file contains 15 separate transactions.  But every single one
>> of those transactions says that the database is only 24 pages long.
>> Furthermore, 12 of the 15 transactions (including the first two and
>> the last one) only touch pages that are on the freelist of the
>> database, which is not really possible.
>
> Hmm after the WAL is recovered the DB is indeed 24 pages long:
> -rw-r--r-- 1 edwin edwin 24K Nov 25 17:47 temp.db

Correct, because when the WAL file is applied, SQLite truncates the
database file to the size of the file according to the commit record
in the WAL file - 24 pages.

>
> We do use SQLITE_CHECKPOINT_PASSIVE and SQLITE_CHECKPOINT_RESTART when
> certain thresholds or timer trigger, what happens if during a passive
> checkpoint it doesn't sync the DB?
> "Checkpoint as many frames as possible without waiting for any database
> readers or writers to finish, then sync the database file if all frames in
> the log were checkpointed."
> Would it still update the size of the .db file without syncing the parent
> dir?

Yes.  Truncating the database to the last transaction size is safe
because if power is lost and some pages fail to be written into the
database prior to the truncation, the WAL will be played back again
after power is restored and the pages will be fixed up then.  Syncing
only occurs prior to deleting or truncating the WAL file, since only
then does the necessary recovery information get destroyed.

>
> The I/O path is more complicated than I originally thought though:
> FreeBSD server (running our app with SQLite) with UFS+SU -> Linux (Xen host)
> -> FreeBSD (ISCSI + ZFS)

I don't know what this means.  Is there a network filesystem involved?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] FOREIGN KEY enhancement request

[Simon Slavin]

I request an enhancement to FOREIGN KEYs as follows.

The foreign key clause



requires a definition as follows:

REFERENCES foreign-table ( column-name [[, column-name]]) ...

It would be useful if rather than having to specify a column name for each item 
one could specify a fixed value (or perhaps an expression, though that might be 
more complicated to handle).  This means that, if some customers were dealers 
and others weren't you could ensure you gave discounts only to dealers using

REFERENCES customers ('dealer', id)

Simon.

[sqlite] Dont Repeat Yourself (DRY) and SQLite

[Simon Slavin]

On 24 Nov 2015, at 11:24pm, Domingo Alvarez Duarte  wrote:
> 
> If we do that we'll be repeating the same string on every column and need a
> string comparison, with postgres enum types or foreign keys it's an integer
> and no repetition. 

Ah.  Okay.

Well SQLite implements FOREIGN KEYs correctly.  So where in Postgres you would 
use an enum you can use a foreign key in SQLite.

CREATE TABLE enum_type (theValue TEXT PRIMARY KEY);
INSERT INTO enum_type VALUES ('simple'), ('tuple');

CREATE TABLE use_mytype (...
...
one_type TEXT REFERENCES enum_type(theValue) DEFAULT 'tuple'
)

Simon.

[sqlite] Fwd: Re: [sx-users] Corruption storage

[Richard Hipp]

On 11/25/15, T?r?k Edwin  wrote:
> Hi,
>
> A user of our application (CC-ed) reported a corruption in an SQLite DB on
> FreeBSD (see below). There was no mmap used in this case, just a WAL DB with
> PRAGMA SYNCHRONOUS=NORMAL.
> From my reading of the SQLite docs this should not result in a corrupt DB,
> worst case some COMMITs could be lost/missing.
>

The database file "temp.db" and the WAL file "temp.db-wal" appear
unrelated to one another.  It as if they came from different places,
or from different points in time.

The database file contains 272 pages, but only the first 8 pages are
used for content.  The final 264 pages of the file are all "freelist"
pages, ready to be reused.

The WAL file contains 15 separate transactions.  But every single one
of those transactions says that the database is only 24 pages long.
Furthermore, 12 of the 15 transactions (including the first two and
the last one) only touch pages that are on the freelist of the
database, which is not really possible.

I don't know how this could come about.  It's like you took a random
database file and a random WAL file and paired them together by giving
them similar names.

Do you have any additional information about how this problem might have arisen?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] [Sqlite3] segfault in sqlite3_step()

[Igor Tandetnik]

On 11/25/2015 4:26 AM, ??? wrote:
> My program received signal SIGSEGV during performed to sqlite_step().

The most likely cause is, your application corrupts the heap elsewhere 
(possibly much earlier and in a seemingly unrelated part), and 
sqlite_step just happens to be the first to hit the corrupted data 
structures when it tries to allocate memory. Heap corruption is nasty 
this way.
-- 
Igor Tandetnik

[sqlite] regular expression in check constraint?

[R Smith]

On 2015/11/25 1:44 AM, Igor Tandetnik wrote:
> On 11/24/2015 6:07 PM, Richard Hipp wrote:
>> On 11/24/15, James Hartley  wrote:
>>> I would like to add a check constraint which determines if a string
>>> contains all digits, ie.
>>>
>>> sqlite> select zip_code from zip_codes where regexp(zip_code,
>>> '^[[:digit:]]+$');
>>>
>>> However, this generates the error:
>>>
>>> Error: no such function: regexp
>>
>> Maybe this instead:
>>
>> SELECT * FROM zip_codes WHERE zip_code NOT GLOB '[^0-9]';
>
> Another variation: WHERE ltrim(zip_code, '0123456789')=''

Or perhaps if a zip code must be present:  WHERE zip_code <> '' AND 
ltrim(zip_code, '0123456789')=''

[sqlite] FTS5 prefix index documentation may be incorrect

[Charles Leifer]

Thank you for the quick fix, and thank you so much for SQLite!
On Nov 25, 2015 5:57 AM, "Dan Kennedy"  wrote:

> On 11/25/2015 05:11 AM, Charles Leifer wrote:
>
>> The FTS5 prefix index documentation[1] seems to not be working. I've tried
>> with SQLite 3.9.0 and 3.10.0 (2015-11-06) and both show the same error
>> messages.
>>
>
> Thanks for reporting this. Now fixed here:
>
>   http://sqlite.org/src/info/11eb8e877e2ba859
>
> Dan.
>
>
>
>> Examples:
>>
>> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3');
>> Error: malformed prefix=... directive
>> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);
>> Error: multiple prefix=... directives
>>
>> What does appear to work is using a comma:
>>
>> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
>> sqlite> .schema ft
>> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
>>
>> 1: https://sqlite.org/fts5.html#section_4_2
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Bug report for MAX()

[Richard Hipp]

On 11/25/15, Dave McKee  wrote:
> I can replicate this behaviour if I insert a zero-length string into the
> column.
>
> sqlite> create table foo(a);
> sqlite> insert into foo values(5);
> sqlite> insert into foo values("");
> sqlite> select max(a) from foo;
>
> sqlite> select min(a) from foo;
> 5
> sqlite> select avg(a) from foo;
> 2.5
>
> Is this a possible explanation?
>

Indeed.  Ryan sent me the database via private email:

sqlite> select typeof(max(unitcost)), length(max(unitcost)) from bomdata;
text|0

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] regular expression in check constraint?

[Keith Medcalf]

REGEXP is not builtin.  You have to compile it in yourself.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of James Hartley
> Sent: Tuesday, 24 November, 2015 15:37
> To: General Discussion of SQLite Database
> Subject: [sqlite] regular expression in check constraint?
> 
> I would like to add a check constraint which determines if a string
> contains all digits, ie.
> 
> sqlite> select zip_code from zip_codes where regexp(zip_code,
> '^[[:digit:]]+$');
> 
> However, this generates the error:
> 
> Error: no such function: regexp
> 
> Searching through sqlite.org points that this function may not be
> included.
> 
> So, is there any equivalent to this kind of functionality?  I can check at
> the application level beforehand, but there is some solace knowing that
> the
> database can check the condition at the time of insertion too.
> 
> Thanks!
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Dont Repeat Yourself (DRY) and SQLite

[Domingo Alvarez Duarte]

If we do that we'll be repeating the same string on every column and need a
string comparison, with postgres enum types or foreign keys it's an integer
and no repetition.  

Cheers !  
>  Tue Nov 24 2015 11:01:35 pm CET CET from "Simon Slavin"
>  Subject: Re: [sqlite] Dont Repeat Yourself (DRY) and
>SQLite
>
>  On 24 Nov 2015, at 7:09pm, Domingo Alvarez Duarte
> wrote:
> 
>  
>>one_type INTEGER NOT NULL REFERENCES mytype(id) NOT NULL, --how to use
>> a default here ?
>> 

>  Include "DEFAULT 'tuple'" just like you would in PostgreSQL.
> 
> Otherwise I'm with Igor. I don't see why you're using TRIGGERs and I don't
>see what problem you're having. Can you point out a specific section of your
>PostgreSQL code you can't translate into SQLite ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?