Re: [sqlite] Trying to edit my profile on this list
Thank you. Now that you pointed it out to me, I can see it. GA On 8/24/2019 9:45 AM, Clemens Ladisch wrote: g a sansom wrote: http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users That page has links for ... unsubscribing. That button is labelled "Unsubscribe or edit options". Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trying to edit my profile on this list
OK, I have been reading this list as separate emails for several weeks now. Thought that I would reduce the clutter in my in basket by resetting to digest form. The only administrative link that I can find is: http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users That page as links for the archives, posting to the list (this!), creating a new subscription (that ONLY creates a new subscription and nags you about already being subscribed) and unsubscribing. There is data entry boxes for the admin to access the subscription list, but I fail that one, too. What am I missing? How do I edit my profile? GA --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] found a glitch in ALTER TABLE RENAME (3.25.x)
Hallo, I've casually discovered that the behavior of ALTER TABLE RENAME TO (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN KEY constraints are updated as expected only when PRAGMA foreign_keys=1, otherwise they are just ignored. example (common part) - CREATE TABLE mother ( id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES mother (id)); test #1 -- PRAGMA foreign_keys=0; ALTER TABLE mother RENAME TO mom; SELECT sql FROM sqlite_master WHERE name = 'daughter'; CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES mother (id)) as you can see, the FK constraint definition has not been updated. test #2 -- PRAGMA foreign_keys=1; ALTER TABLE mother RENAME TO mom; SELECT sql FROM sqlite_master WHERE name = 'daughter'; -- CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES "mom" (id)) this second time the FK constraint has been properly updated. Note: ALTER TABLE RENAME COLUMN seems to be immune from the issue. test #3 -- PRAGMA foreign_keys=0; ALTER TABLE mother RENAME COLUMN id TO pkuid; SELECT sql FROM sqlite_master WHERE name = 'daughter'; CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES mother (pkuid)) test #3 -- PRAGMA foreign_keys=1; ALTER TABLE mother RENAME COLUMN id TO pkuid; SELECT sql FROM sqlite_master WHERE name = 'daughter'; CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES mother (pkuid)) the FK constraint definition is correctly updated in both cases, the actual setting of PRAGMA foreign_key is not relevant. best regards, Sandro Furieri (developer of SpatiaLite) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] possible bug affecting 3.22 and 3.23 - load_extension()
it seems that some unexpected regression is affecting load_extension()
on Linux when using the most recent versions of SQLite (3.22 and 3.23).
my test configuration:
- sqlite3 installed on the current directory
- extension module installed on /usr/local/lib
(/usr/local/lib/mod_spatialite.so)
$ export "LD_LIBRARY_PATH=/usr/local/lib"
$ ./sqlite3
sqlite> SELECT load_extension('mod_spatialite');
- the extension module is successfully loaded when using 3.20.1
- but it fails when using 3.22.0 or 3.23.1
digging into the code I was finally able to reestablish the expected
behavior of load_extension() after applying the following patch:
sqlite3.c (v.3.23.1) - line 115917 - sqlite3LoadExtension()
---
handle = sqlite3OsDlOpen(pVfs, zFile);
#if SQLITE_OS_UNIX || SQLITE_OS_WIN
for(ii=0; iihttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] OutOfMemory exception when returning a small DataTable
I have a SQLite database that has a single table with 18 million rows and
24 columns. I have written a SQL query function in C#, which I expose to
Excel with ExcelDNA.
This is the complete code,
string constr = constr = "Data Source=" + FilePath +
";Version=3;Synchronous=OFF;temp_store=memory;cache_size=
70;count_changes=off;";
DataTable dt = new DataTable();
try
{
SQLiteConnection conn = new SQLiteConnection(constr);
SQLiteCommand command = new SQLiteCommand(SQLStatement,conn);
conn.Open();
SQLiteDataAdapter sda = new SQLiteDataAdapter(command);
sda.Fill(dt);
sda.Dispose();
command.Dispose();
conn.Dispose();
int numRows = (IncludeHeaders ? dt.Rows.Count + 1 :
dt.Rows.Count);
object[,] ret = new object[numRows, dt.Columns.Count];
int rowCount = 0;
if (IncludeHeaders)
{
int colCount = 0;
foreach (DataColumn col in dt.Columns)
{
ret[rowCount, colCount] = col.ColumnName;
colCount++;
}
}
rowCount = (IncludeHeaders ? 1 : 0);
foreach (DataRow row in dt.Rows)
{
int colCount = 0;
foreach (DataColumn col in dt.Columns)
{
if (row[col] != DBNull.Value)
ret[rowCount, colCount] = row[col];
else
ret[rowCount, colCount] = "";
colCount++;
}
rowCount++;
}
return ret;
}
catch (Exception ex)
{
object[,] err = new object[1, 1];
err[0, 0] = ex.ToString();
return err;
}
finally
{
dt.Clear();
dt.Dispose();
dt = null;
}
If I run the query twice (two hits of Shift+F9), I get an
OutOfMemoryException. In the Task Manager I can see the Working Set(Memory)
of the EXCEL.EXE image go from 200MB to 1500MB before the exception is
thrown.
However, this behavior isn't entirely consistent. Other queries where I
return upto 5 columns and 1100 rows work just fine. I see the memory usage
tick up in the Task Manager and once the results are returned to Excel I
see the memory come back down.
Debugging the application above shows that it trips up at the
`sda.Fill(dt)` line.
Would appreciate any thoughts? Would I be better off using
`SQLiteDataReader` instead? Or are there any other tips or tricks I can
use? Thank you.
Incidentally if I run the exact query via Python I don't get this problem,
so I'd assume it's something to do with the garbage collection in C#.
Here are some details on the database and query. The schema is along the
lines of,
Date (VARCHAR)
CompanyName (VARCHAR)
Amount (REAL)
AggCode (VARCHAR)
Level1 ... Level20 (VARCHAR)
The queries are usually run combining the fields `Level9`, `Level5`,
`AggCode`, `Date`, `CompanyName` in the `WHERE` clause. So apart from the
raw table, I have also configured the following four indices,
CREATE INDEX idx1 on my(Level09, AggCode);
CREATE INDEX idx2 on my(Level05, AggCode);
CREATE INDEX idx3 on my(CompanyName, AggCode);
CREATE INDEX idx4 on my(Date, AggCode);
The query that returns 1100 rows and 2 columns successfully,
SELECT CompanyName, SUM(Amount) FROM my where Level09="T_EU" and
AggCode = "R_S_V" GROUP BY CompanyName ORDER BY SUM(Amount) DESC
The query that throws the memory exception,
SELECT Date, CompanyName, Sum(Amount) FROM my WHERE Level05 ="M_TO" AND
AggCode = "C_DTA" GROUP BY Date, CompanyName
The second query returns 163 rows and 3 columns in Python.
The full stack trace of the exception is below,
System.Data.SQLite.SQLiteException (0x80004005): out of memory
out of memory
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32
startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable
[sqlite] Backslash problems with json() and json_valid()
It seems as json() and json_valid() misbehave about unescaped
backslashes. json() creates invalid JSON, and json_valid() happily
accepts it:
$ sqlite3
SQLite version 3.15.1 2016-11-04 12:08:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select json('" \ "'); -- Creates invalid JSON
" \ "
sqlite> select json('" \\ "');
" \\ "
sqlite> select json_valid('" \ "'); -- Accepts invalid JSON
1
sqlite> select json_quote('\'); -- Works correctly
"\\"
sqlite>
$ sqlite3 :memory: "select json('\" \\ \"');"
" \ "
$ sqlite3 :memory: "select json('\" \\ \"');" | json_verify
lexical error: inside a string, '\' occurs before a character which it
may not.
" \ "
(right here) --^
JSON is invalid
$
It's been like this since JSON arriveed in v3.9.0.
And oh, thanks for creating this wonderful piece of software.
Regards,
Øyvind
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert MySQL to sqlite
Funny, I was just doing the same thing. I have hacked together some Python code to convert the MySQL exports from the Workbench into SQLite format for import. It's not pretty, and is a work in progress, but you are welcome to it. Uncheck everything from the advanced tab and select Dump Structure and Data. This will not convert triggers and only converts a few functions but is easy enough to manipulate to do what you need. https://drive.google.com/open?id=0B_3LcLJUnNA4MVozYWtUaGZFbUk Once converted run: SQLITE3 OuputName.sqlite ".log log.txt" ".read InputFromConversion.sql" Hope it helps. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Scott Doctor Sent: Saturday, September 10, 2016 4:25 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Convert mysql to sqlite I have a database with a few tables and about 140MB of data in it that exists as a MySQL database. Using MySQL workbench I can export the data, but it dumps as a SQL dump with its flavor of sql. I want to convert this database into a sqlite database. Nothing fancy in it. No triggers, procedures, or foreign keys. Tables are normal structure with a pk field and some text and integer fields in each table. Anyone know of a utility to directly convert from MySQL to sqlite? -- - Scott Doctor sc...@scottdoctor.com - ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE FUNCTION
Trust, me if I could remove that dumb message I would. :) Thanks to everyone for your answers. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Monday, August 22, 2016 2:39 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] CREATE FUNCTION Rousselot, Richard A wrote: > This communication is the property of CenturyLink and may contain > confidential or privileged information. So, officially, you did not ask anything? Well, then I'm not going to answer. But it should be mentioned that SQLite is an _embedded_ database, i.e., it is meant to be part of a program written in a 'real' programming language, so with sqlite3_create_function(), there is no use case for the feature that you did not ask about Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE FUNCTION
Are there any plans to add a CREATE FUNCTION function for SQLite? Is this an SQLite4 type thing? Something similar PostgreSQL? https://www.postgresql.org/docs/9.5/static/sql-createfunction.html This particular class of function is not mentioned in "SQL Features That SQLite Does Not Implement" and it is not in "SQL As Understood By SQLite"; kind of a grey area, in my mind, for future support. Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Yes, it is much faster. The process was done in Excel/Access before and took ages. I have had processes go from 8 hours before to 30 min now using SQLite. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Michael Gratton Sent: Wednesday, August 17, 2016 8:05 PM To: SQLite mailing list Subject: Re: [sqlite] 64-bit SQLite3.exe Richard, On Thu, Aug 18, 2016 at 2:23 AM, Rousselot, Richard A wrote: > I was only interested in doing these calculations quickly; that is my > real-world. The SQL scripts I am using were built with multiple steps > with intermediate temp tables that are used to create a final result > table. > > I understand my needs are not generally how others use SQLite. I am > using it as a platform for fast calculations that happens to store > results in a way that can be quarried. Every time, my process begins > all prior results are deleted, only the input tables stay the same. Out of curiosity, are you using these in an end-user application, i.e. as an interactive response to user actions? I would be curious to know if building intermediate temporary tables is fast enough for returning results for such uses. Thanks, //Mike -- ⊨ Michael Gratton, Percept Wrangler. ⚙ <http://mjog.vee.net/> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Simon, I was only interested in doing these calculations quickly; that is my real-world. The SQL scripts I am using were built with multiple steps with intermediate temp tables that are used to create a final result table. I understand my needs are not generally how others use SQLite. I am using it as a platform for fast calculations that happens to store results in a way that can be quarried. Every time, my process begins all prior results are deleted, only the input tables stay the same. I will pass the baton on to someone else to do more realistic, real world, testing of 32-bit vs 64-bit. Richard -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, August 17, 2016 11:09 AM To: SQLite mailing list Subject: Re: [sqlite] 64-bit SQLite3.exe On 17 Aug 2016, at 4:54pm, Rousselot, Richard A wrote: >PRAGMA journal_mode = Off; This configuration is designed for cases where the resulting database will be thrown away soon (e.g. when the application quits). It does increase speed greatly, but a consequence is that the benchmark is not useful as an indicator of performance in the real world. Some of your other PRAGMAs also seem to be suitable only to running benchmarks quickly. A fairer test, one more like real world performance, would be not to use any PRAGMAs at all apart from turning on FOREIGN KEYS. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Speed improvements based on 64-bit MinGW. SQLite 3.14.2, built with MinGW 64-bit. - https://sourceforge.net/projects/mingw-w64/?source=typ_redirect gcc -m64 -O2 -DSQLITE_THREADSAFE=0 shell.c sqlite3.c -o sqlite3.exe gcc -g -shared extension-functions.c -o extension-function.dll Built with SQLITE options of default Module SQL Script (146k records)T1 Sec T2 Sec T3 Sec T4 Sec Delta Delta Delta Processing Module 1 of 8: (CTEs)138 110 92 49 125%150%282% Processing Module 2 of 8: 26 20 17 14 130% 153%186% Processing Module 3 of 8: 34 28 23 18 121% 148%189% Processing Module 4 of 8: 14 7 6 5 200% 233%280% Processing Module 5 of 8: 9 6 6 4 150% 150%225% Processing Module 6 of 8: 30 14 12 11 214% 250%273% Processing Module 7 of 8: (CTEs)290 222 212 86 131% 137%337% Processing Module 8 of 8: 14 14 10 10 100% 140%140% Total Processing Time: 557 424 374 200 131% 149%279% Test 1: 32-Bit 3.13 (sqlite.org), No PRAGMA (Baseline) Test 2: 32-bit 3.13 (sqlite.org), with PRAGMA but not temp_store = 2; Test 3: 64 bit 3.14.2 MinGW, with PRAGMA but not temp_store = 2; Test 4: 64 bit 3.14.2 MinGW, all PRAGMA PRAGMAs used... PRAGMA synchronous = 0; PRAGMA read_uncommitted = 1; PRAGMA locking_mode = exclusive; PRAGMA journal_mode = Off; PRAGMA temp_store = 2; For reference both sets of tests on same machine; Windows 10, i5-3470 3.20GHz, 12GB RAM and on internal spinning HDD. There is a slight improvemnt (~5% faster) going to SSD but wanted to stay consistent. I don't plan on testing Cygwin, I am happy with the MinGW results. Thanks everyone for your help. Richard -Original Message- From: Rousselot, Richard A Sent: Monday, August 15, 2016 12:20 PM To: sqlite-users@mailinglists.sqlite.org Cc: Rousselot, Richard A Subject: Re: [sqlite] 64-bit SQLite3.exe Speed improvements based on 64-bit Visual Studio build. SQLite 3.14.0, built with MSVC 18.00.30723 for x64. Built with VS options /O2 /Ot /Ox. Built with SQLITE options of SQLITE_ENABLE_FTS3 SQLITE_ENABLE_STAT2 SQLITE_ENABLE_RTREE. Module SQL Script (146k records)T1 Sec T2 Sec T3 Sec T4 Sec Sp Inc Sp Inc Sp Inc Processing Module 1 of 8: (CTEs)138 11096 56 125%144%246% Processing Module 2 of 8: 26 20 18 15 130% 144%173% Processing Module 3 of 8: 34 28 26 21 121% 131%162% Processing Module 4 of 8: 14 7 7 6 200% 200%233% Processing Module 5 of 8: 9 6 5 4 150% 180%225% Processing Module 6 of 8: 30 14 13 11 214% 231%273% Processing Module 7 of 8: (CTEs)290 222 194 100 131% 149%290% Processing Module 8 of 8: 14 14 11 11 100% 127%127% Total Processing Time: 557 424 372 227 131% 150%245% Test 1: 32-Bit 3.13 (sqlite.org), No PRAGMA (Baseline) Test 2: 32-bit 3.13 (sqlite.org), with PRAGMA but not temp_store = 2; Test 3: 64 bit 3.14.0 Visual Studio 18, with PRAGMA but not temp_store = 2; Test 4: 64 bit 3.14.0 Visual Studio 18, all PRAGMA PRAGMAs used... PRAGMA synchronous = 0; PRAGMA read_uncommitted = 1; PRAGMA locking_mode = exclusive; PRAGMA journal_mode = Off; PRAGMA temp_store = 2; I am still trying to figure out how to make a 64-bit build in MinGW. There is a nice installer for 32-bit but not for 64-bit, go figure. Hopefully I have the PRAGMAs right. If there is a better configuration for speed, let me know. Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Correction on description: Test 2: 32-bit 3.13 (sqlite.org), with PRAGMA but not temp_store = 2; -Original Message- From: Rousselot, Richard A Sent: Monday, August 15, 2016 12:20 PM To: sqlite-users@mailinglists.sqlite.org Cc: Rousselot, Richard A Subject: Re: [sqlite] 64-bit SQLite3.exe Speed improvements based on 64-bit Visual Studio build. SQLite 3.14.0, built with MSVC 18.00.30723 for x64. Built with VS options /O2 /Ot /Ox. Built with SQLITE options of SQLITE_ENABLE_FTS3 SQLITE_ENABLE_STAT2 SQLITE_ENABLE_RTREE. Module SQL Script (146k records)T1 Sec T2 Sec T3 Sec T4 Sec Sp Inc Sp Inc Sp Inc Processing Module 1 of 8: (CTEs)138 11096 56 125%144%246% Processing Module 2 of 8: 26 20 18 15 130% 144%173% Processing Module 3 of 8: 34 28 26 21 121% 131%162% Processing Module 4 of 8: 14 7 7 6 200% 200%233% Processing Module 5 of 8: 9 6 5 4 150% 180%225% Processing Module 6 of 8: 30 14 13 11 214% 231%273% Processing Module 7 of 8: (CTEs)290 222 194 100 131% 149%290% Processing Module 8 of 8: 14 14 11 11 100% 127%127% Total Processing Time: 557 424 372 227 131% 150%245% Test 1: 32-Bit 3.13 (sqlite.org), No PRAGMA (Baseline) Test 2: 64-bit 3.14.0 Visual Studio 18, No PRAGMA Test 3: 64 bit 3.14.0 Visual Studio 18, with PRAGMA but not temp_store = 2; Test 4: 64 bit 3.14.0 Visual Studio 18, all PRAGMA PRAGMAs used... PRAGMA synchronous = 0; PRAGMA read_uncommitted = 1; PRAGMA locking_mode = exclusive; PRAGMA journal_mode = Off; PRAGMA temp_store = 2; I am still trying to figure out how to make a 64-bit build in MinGW. There is a nice installer for 32-bit but not for 64-bit, go figure. Hopefully I have the PRAGMAs right. If there is a better configuration for speed, let me know. Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Speed improvements based on 64-bit Visual Studio build. SQLite 3.14.0, built with MSVC 18.00.30723 for x64. Built with VS options /O2 /Ot /Ox. Built with SQLITE options of SQLITE_ENABLE_FTS3 SQLITE_ENABLE_STAT2 SQLITE_ENABLE_RTREE. Module SQL Script (146k records)T1 Sec T2 Sec T3 Sec T4 Sec Sp Inc Sp Inc Sp Inc Processing Module 1 of 8: (CTEs)138 11096 56 125%144%246% Processing Module 2 of 8: 26 20 18 15 130% 144%173% Processing Module 3 of 8: 34 28 26 21 121% 131%162% Processing Module 4 of 8: 14 7 7 6 200% 200%233% Processing Module 5 of 8: 9 6 5 4 150% 180%225% Processing Module 6 of 8: 30 14 13 11 214% 231%273% Processing Module 7 of 8: (CTEs)290 222 194 100 131% 149%290% Processing Module 8 of 8: 14 14 11 11 100% 127%127% Total Processing Time: 557 424 372 227 131% 150%245% Test 1: 32-Bit 3.13 (sqlite.org), No PRAGMA (Baseline) Test 2: 64-bit 3.14.0 Visual Studio 18, No PRAGMA Test 3: 64 bit 3.14.0 Visual Studio 18, with PRAGMA but not temp_store = 2; Test 4: 64 bit 3.14.0 Visual Studio 18, all PRAGMA PRAGMAs used... PRAGMA synchronous = 0; PRAGMA read_uncommitted = 1; PRAGMA locking_mode = exclusive; PRAGMA journal_mode = Off; PRAGMA temp_store = 2; I am still trying to figure out how to make a 64-bit build in MinGW. There is a nice installer for 32-bit but not for 64-bit, go figure. Hopefully I have the PRAGMAs right. If there is a better configuration for speed, let me know. Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Your link says I do not have permission to open. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Random Coder Sent: Saturday, August 13, 2016 12:17 PM To: SQLite mailing list Subject: Re: [sqlite] 64-bit SQLite3.exe On Sat, Aug 13, 2016 at 8:39 AM, Rousselot, Richard A wrote: > Thanks, this does allow the library to load and process. The thing is your > build is by far the slowest I have received, slower even than the 32-bit > version. Sorry about that, it was a debug build. https://new-bucket-2a9cf983.s3.amazonaws.com/sqlite64_release.zip Might be faster. > Could you tell me what version of SQLite you compiled, your compiler and the > settings you used on each build? SQLite 3.14.0, built with MSVC 18.00.30723 for x64. The debug version was built with /Od, this version is built with /O2 /Ot /Ox. Both versions are built with the SQLITE options of SQLITE_ENABLE_FTS3 SQLITE_ENABLE_STAT2 SQLITE_ENABLE_RTREE. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Additionally, I and others have got the Cygwin64 compiler to build 64-bit on both SQLite3 and the math extensions. Not optional since, as far as I know I need to install Cygwin64 on each machine that I will have running this process. But one thing for Cygwin64 is that one of my colleagues got a 4x speed improvement, albeit changing a script from using a Ceil function (requires math lib) to a Round function (core function) for testing purposes. I need to test myself to see if it is truly that much an improvement, I am dubious. Richard -Original Message- From: Rousselot, Richard A Sent: Saturday, August 13, 2016 10:40 AM To: SQLite mailing list Cc: Rousselot, Richard A Subject: RE: [sqlite] 64-bit SQLite3.exe Thanks, this does allow the library to load and process. The thing is your build is by far the slowest I have received, slower even than the 32-bit version. Could you tell me what version of SQLite you compiled, your compiler and the settings you used on each build? I plan on compiling all the settings and speed tests of the builds I have received and tried to build myself. It should be an interesting matrix. Richard -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Random Coder Sent: Friday, August 12, 2016 4:54 PM To: SQLite mailing list Subject: Re: [sqlite] 64-bit SQLite3.exe On Fri, Aug 12, 2016 at 10:06 AM, Rousselot, Richard A wrote: > I, and others, have tried to compile this as a 64-bit library but it will not > load from the command line (using .load) and gives the "Error: The specified > module could not be found." > > Anyone have tips on how to resolve this? Is this library somehow > incompatible with 64-bit? Generally that means the exports haven't been properly setup. The easiest way to do this generally is to setup a .def file to get the proper function exported from the DLL with the correct naming convention. I've done just that for a quick test version at https://new-bucket-2a9cf983.s3.amazonaws.com/sqlite64.zip , which may or may not work for your needs. Other than verifying acos() was present, I've done no testing. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Thanks, this does allow the library to load and process. The thing is your build is by far the slowest I have received, slower even than the 32-bit version. Could you tell me what version of SQLite you compiled, your compiler and the settings you used on each build? I plan on compiling all the settings and speed tests of the builds I have received and tried to build myself. It should be an interesting matrix. Richard -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Random Coder Sent: Friday, August 12, 2016 4:54 PM To: SQLite mailing list Subject: Re: [sqlite] 64-bit SQLite3.exe On Fri, Aug 12, 2016 at 10:06 AM, Rousselot, Richard A wrote: > I, and others, have tried to compile this as a 64-bit library but it will not > load from the command line (using .load) and gives the "Error: The specified > module could not be found." > > Anyone have tips on how to resolve this? Is this library somehow > incompatible with 64-bit? Generally that means the exports haven't been properly setup. The easiest way to do this generally is to setup a .def file to get the proper function exported from the DLL with the correct naming convention. I've done just that for a quick test version at https://new-bucket-2a9cf983.s3.amazonaws.com/sqlite64.zip , which may or may not work for your needs. Other than verifying acos() was present, I've done no testing. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
Update to my 64-bit saga. I was able to work with some helpful mailing list members to create a 64-bit SQLite3.exe. Far as I can tell it works fine but unfortunately I also use the math extension library (extensions-functions.c) in my CTE queries which is also 32-bit. I, and others, have tried to compile this as a 64-bit library but it will not load from the command line (using .load) and gives the "Error: The specified module could not be found." Anyone have tips on how to resolve this? Is this library somehow incompatible with 64-bit? Any insight would be appreciated. Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
First of all thanks for the discussion. Special thanks out to DD. ;) I will just summarize my main view. * CTEs can easily exceed memory limits of the 32-bit SQLite3.exe. So I have a valid need and am not on a crusade to kill 32-bits. Telling me that other people don't have my problems is irrelevant, to me. :) Response to all other banter; in good humor. * The rest of the arguments about Microsoft currently shipping software that is 32-bit, is a red herring. I bet if you tried hard enough, you could find code from Windows 3.1 in Windows 10. That wasn’t a properly thought out design decision. That is laziness and frugality (possibly stupidity) on Microsoft's part. I once read that MS would hire the developer of ReactOS so they could explain how their own operating system works. (I digress) * If I did figure out how to build the 64-bit executable, I guarantee, that it would be a catastrophe. I can't even figure out how to post on Nabble for freaks sake. ;) * I can still buy a VW bug designed in the 30s; does that mean that all new engines developed should have to fit in the back of that thing? * I acquiesce on all other points, I am weary. Finally, I just want to say I really appreciate the work that is put in to SQLite. It is one of many tools I use on a day to day basis but it is the only one where I actively watch the discussions on a mailing list. The regulars here are thoughtful, intelligent and infinitely patient. I learn new things every day watching you guys and for that I thank you. Richard -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Wednesday, August 10, 2016 7:03 AM To: SQLite mailing list Subject: Re: [sqlite] 64-bit SQLite3.exe > Even on a 64-bit processor, there’s usually no reason to run 64-bit > Windows unless you have more than 4 GB of RAM, a threshold we didn’t > pass very long ago. Yes, please remember to keep the "addressable memory limits" linkage to "processor bitted-ness" is a Microsoft Only phenomenon. You do NOT need 64-bit processors or 64-bit Operating systems to be able to address more than 4 GB of physical RAM. In fact that there are 32-bit versions of Windows (NT 3.5, 4.0, 2000, XP, 2003 etc) which have been compiled without this artificially imposed limitation. You pay more for "properly written and compiled" software however because, well, it is easy to do stupid things and impose stupid limits for no reason and you need higher wattage (therefore more expensive people) if you want software that is not bounded by a crapload of inane if not brain-dead) design decisions. It is also quite profitable to claim that your prior incompetencies were to blame on "something else" and if everyone would just spend a boatload of money and replace all the defective crap we sold them with a new boatload of defective crap (every six months), a very few of the deliberately imposed limitations will be removed (plus we make yet another boatload of money by ripping off the customer). > Or maybe you’d like to look to a less legacy-bound company? Say, > Google, who ships Chrome still built as 32-bit, originally for > compatibility with 32-bit NSAPI plugins. Since they dropped that, I > can only guess why they’re still building 32-bit binaries, and that > guess is that with the tab-per-process isolation, no single tab needs more > than 4 GB of VM space. Or they are using defective compilers (primary supplier in that field is Microsoft) that cannot switch memory models without re-writing the code. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
I guess it is a matter of support. Can the people using unpatched, unsupported 32-bit windows instances just live with SQLite 3.13 (or whatever the cutover version)? Are these 32-bit windows users really actively updating SQLite? Can the command line tool interact with a driver? How does a 32-bit windows user get SQLite3.exe to run on a legacy 16-bit (windows 3.1?) machine? Sorry to press on this so much but I find all these arguments hollow. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Empson Sent: Wednesday, August 10, 2016 12:09 AM To: SQLite mailing list Subject: Re: [sqlite] 64-bit SQLite3.exe > On 10/08/2016, at 3:30 PM, Rousselot, Richard A > wrote: > > As I said, I am not a software engineer. I could spend a few hours figuring > this out and be fine but it will be painful for me. > > I see no downsides in a 64-bit CLI. The last 32-bit Intel CPU was the PIII > in 2004, no supported Windows OS requires 32-bit CPUs, the file size may be > marginally bigger but who cares on a PC. The 64-bit version will, I assume, > happily work on DBs created in the 32-bit version. And for those that need > 32-bit for their applications and drivers still have access to the 32-bit > DLL. What am I missing? Are windows command line tools 32-bit only? A 32-bit installation of Windows cannot run 64-bit executables (ignoring VM solutions). Because of the large installed base of 32-bit Windows, the Windows command line tools for SQLite needs to be available as 32-bit versions. If 64-bit versions were provided, they would need to be in addition to the 32-bit versions. There are an awful lot of 32-bit installations of Windows. This includes a lot of 32-bit installations of Windows on 64-bit processors, which exist for many reasons including defaults offered by the manufacturer, lack of 64-bit drivers, corporate policy decisions, reduced memory footprint in limited machines, or the user requiring 32-bit Windows in order to be able to run legacy 16-bit software (again, ignoring VM solutions). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-bit SQLite3.exe
>On Aug 9, 2016, at 9:30 PM, Rousselot, Richard A > wrote: > >> I could spend a few hours figuring this out and be fine but it will be >> painful for me. > >Or you can spend many hours waiting for someone to build it for you. How many >hours are you willing to wait to save yourself some pain? (And since when did >learning something new cause pain?) > I like learning as much as the next guy but I prefer to spend my time on skills I can use in the future; compiling a 64-bit binary is not a useful skill. I may wait for someone to compile and provide it to me but I am really wary of getting code from strangers these days. I tend to trust the SQLite team. >As to your problem with corporate IT, will they let you install Cygwin? >SQLite is well-supported in Cygwin, and there is a 64-bit version of Cygwin. >Due to the way Cygwin works, all packages available for 64-bit Cygwin are also >64-bit. > I would probably have an easier time getting a Mac (impossible) than get Cygwin installed at work. :) >Cygwin SQLite should be nearly as fast as native SQLite. There are some big >speed hits in Cygwin, but for the things SQLite does, I can’t see that you’re >going to run into any of the biggest ones. > >> The last 32-bit Intel CPU was the PIII in 2004 > >That’s simply not true. Many P4s were 32-bit, the Atom processors were 32-bit >only until 2008, and I believe the Core Solo processors were also 32-bit only. > >(That latter caused a lot of trouble for me when Apple went 64-bit only and >cut off a bunch of the still-useful Macs I had still in use.) > Ok, P4 in 2008 that is still 8 years ago. (Your 32-bit Mac is not windows machine). How long do I have to wait for everyone to upgrade? So, if there is one person in the universe still using a 32-bit windows machine we all have to wait? >> no supported Windows OS requires 32-bit CPUs > >But equally, Microsoft retrenched from their threat to make Windows 10 the >first 64-bit-only version of Windows. Wonder why? :) > Microsoft keeps 32-bit compatibility for legacy applications. I don't consider and actively developed piece of software from 2016 a legacy application, do you? >> The 64-bit version will, I assume, happily work on DBs created in the 32-bit >> version. > >Yes. > >> What am I missing? > >Someone has to do it. Time is not free. > I agree, time is not free. If I compile a 64-bit SQLite3.exe that only helps me and wastes a lot of my time. I bet Dr. Hipp et al could have that thing (build scripts at least) complied in a matter of minutes and his work would be available for anyone in the world to use. Why not, on the other hand, save some time by not compiling the 32-bit version? The 64-bit version will probably shave an hour off my many 8 hour processing jobs. That will add up very quickly for me. >> Are windows command line tools 32-bit only? > >The opposite, actually: the first 64-bit versions of the Visual C++ tool set >were command-line only, as I recall. I believe that was back in the >pre-VC++2005 days. > >> Why add powerful features like CTE if you can't access their power? > >Because most of the SQLite binaries are shipped by third parties, not directly >from sqlite.org. The biggest sources are OSes (virtually all mobile phones, >Mac OS X, Windows, etc.) and third-party applications (virtually all web >browsers, many Adobe and Apple products, etc.) These third parties built >SQLite to meet their needs. > This doesn't make sense, what does a 3rd party binary based on a dll have to do with a command line tool? Are you saying that no one needs the command line tool so its development should be abandoned? >I’d bet the number of regularly run instances of binaries downloaded directly >from sqlite.org is under 0.01% of the total usage of SQLite. > >(That’s a considered guess, not a wild guess. There are billions of SQLite >instances in the world, and I’m betting there are less than 100,000 users of >the SQLite.org binaries. I wouldn’t be surprised if it’s under 0.001%.) > >Of that tiny percentage, only a small fraction will actually need a 64-bit, >and of that fraction of a fraction, only a small number will be unable to >acquire or build a 64-bit binary. > >Why spend a lot of effort on such a small user base? > A 64-bit SQLite3.exe would help the whole user based of command line users. Why spend time making a 32-bit version for the minority of people still running 8 year old equipment? It's not like they can't download an older 32-bit version. This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful.
Re: [sqlite] 64-bit SQLite3.exe
As I said, I am not a software engineer. I could spend a few hours figuring this out and be fine but it will be painful for me. I see no downsides in a 64-bit CLI. The last 32-bit Intel CPU was the PIII in 2004, no supported Windows OS requires 32-bit CPUs, the file size may be marginally bigger but who cares on a PC. The 64-bit version will, I assume, happily work on DBs created in the 32-bit version. And for those that need 32-bit for their applications and drivers still have access to the 32-bit DLL. What am I missing? Are windows command line tools 32-bit only? Why add powerful features like CTE if you can't access their power? Isn't this just a matter of making a few changes on some automated scripts that generate each releases files and done? (Sorry if this double posts, I attempted to use Nabble and the message bounced) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Donald Shepherd Sent: Tuesday, August 09, 2016 9:28 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] 64-bit SQLite3.exe Why don't you build it yourself as a 64 bit executable? On Wed, 10 Aug 2016 at 00:31 Rousselot, Richard A < richard.a.rousse...@centurylink.com> wrote: > I would like to request a SQLite official 64-bit SQLite3.exe CLI (not > DLL) be created. > > I have reviewed the prior discussions regarding 64-bit SQLite3 and the > reasoning for which why creating a 64-bit version is denied are "it > does not make a real difference", "you can just use ram disks", etc., etc. > > Here is my plea... I am using a set of complicated CTEs to crawl > through a network (tree) to aggregate and calculate formulas. I don't > have exceptionally large datasets but my CTEs result in a ton of memory usage. > The process works well from disk, in Windows, but using a smaller test > sample I get about a 30% to 40% increase in processing time if I set > the PRAGMA to temp_store = 2. If I use a normal dataset, not a small > test, I hit an approximate 2G limit and get a "out of memory" message, > which I understand is due to SQLite3.exe being 32-bit. I have found > some 3rd party 64-bit builds for SQLite3 (best found is 3.8.5) but > they are out of date and don't allow all functionality that I am > using. So, I do have a use case that requires 64-bit and I would see a > significant increase in speed. > > As to RAM disks, I work in a corporate environment that locks down > user rights which precludes me from distributing a tool that requires > the creation of a tool that needs administrator rights. I also, would > like to avoid having to compile it myself; I am not a software engineer. > > Thanks for your consideration. > > Richard > This communication is the property of CenturyLink and may contain > confidential or privileged information. Unauthorized use of this > communication is strictly prohibited and may be unlawful. If you have > received this communication in error, please immediately notify the > sender by reply e-mail and destroy all copies of the communication and > any attachments. > ___ > 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 This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 64-bit SQLite3.exe
I would like to request a SQLite official 64-bit SQLite3.exe CLI (not DLL) be created. I have reviewed the prior discussions regarding 64-bit SQLite3 and the reasoning for which why creating a 64-bit version is denied are "it does not make a real difference", "you can just use ram disks", etc., etc. Here is my plea... I am using a set of complicated CTEs to crawl through a network (tree) to aggregate and calculate formulas. I don't have exceptionally large datasets but my CTEs result in a ton of memory usage. The process works well from disk, in Windows, but using a smaller test sample I get about a 30% to 40% increase in processing time if I set the PRAGMA to temp_store = 2. If I use a normal dataset, not a small test, I hit an approximate 2G limit and get a "out of memory" message, which I understand is due to SQLite3.exe being 32-bit. I have found some 3rd party 64-bit builds for SQLite3 (best found is 3.8.5) but they are out of date and don't allow all functionality that I am using. So, I do have a use case that requires 64-bit and I would see a significant increase in speed. As to RAM disks, I work in a corporate environment that locks down user rights which precludes me from distributing a tool that requires the creation of a tool that needs administrator rights. I also, would like to avoid having to compile it myself; I am not a software engineer. Thanks for your consideration. Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Education Opportunity Alert: The SQL Primer
Thanks! =) Sent from my iPhone > On Aug 4, 2016, at 5:38 PM, jungle Boogie wrote: > >> On 4 August 2016 at 08:14, R.A. Nagy wrote: >> Comments & suggestion for improvement be both respected, as well as >> appreciated here, as usual! > > WOW! Such great energy and enthusiasm in the primer video! > I'm looking forward to the rest. > > Best, > sean > > > -- > --- > inum: 883510009027723 > sip: jungleboo...@sip2sip.info > ___ > 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] Education Opportunity Alert: The SQL Primer
Enjoy yourself - by all means, do! Sent from my iPhone > On Aug 4, 2016, at 1:37 PM, Stephen Chrzanowski wrote: > > *sits back and waits for the people to cringe about Sequal or Ess-Que-El > and smirks* > >> On Thu, Aug 4, 2016 at 11:14 AM, R.A. Nagy wrote: >> >> Hi all, >> >> Let me begin by thanking everyone for the feedback on the YouTube video >> effort! >> >> For those who would like to revisit our relatively comprehensive update to >> a professional introduction to SQL & SQLite, please feel free to share & >> enjoy =) this latest: >> >> https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy >> >> Comments & suggestion for improvement be both respected, as well as >> appreciated here, as usual! >> >> >> Cheers, >> >> Randall Nagy >> President, Soft9000.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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Logo
Excellent. Thanks! Sent from my iPhone > On Jun 25, 2016, at 9:47 PM, Richard Hipp wrote: > > The use of the SQLite trademarks is allowed for cases where you want > to convey that you are using SQLite are talking about SQLite. But it > must be clear to readers that your content is not endorsed by SQLite. > > An example: there was a product called (something like) "SQLite > Database Viewer". Because of the way SQLite appeared in that names, > customers were confused and thought that the application was an > official product of the SQLite development team. We asked the > developers to change the name to "Database Viewer for SQLite". This > made it clear to end customers that the application was a third-party > tool. After that name change, end users stopped coming to us for > support, and started contacting the application developers instead, as > they should. > > The same standard must be applied to your use of the SQLite trademark. > You are welcomed to use the SQLite trademark on your literature as > long as your customers clearly understand that you are just a great > teacher who happens to be instructing on SQLite, and that your are not > an official spokesman or representative for the SQLite project. > >> On Fri, Jun 24, 2016 at 2:03 PM, R.A. Nagy wrote: >> I am putting together a commercial training for SQLite. I would like to use >> the SQLite logo - as seen on the website - on the cover of the materials. >> >> Can do? >> ___ >> 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BugReport]Data changes cannot actually flush to disk
Some times writes that have been cached will not be written to disk. In code we use the flush() to insure changes are made. In the OS we need to be sure that the system has been shutdown properly. .02 ends. Sent from my iPhone > On Jun 8, 2016, at 7:20 AM, Simon Slavin wrote: > > >> On 7 Jun 2016, at 8:13am, 刘翔 wrote: >> >> Problem: >> When update database and power off immediately, > > How soon do you turn the power off ? Two seconds ? Ten seconds ? One > minute ? > > What type of hard disk do you have ? Is it a rotating disk or a solid state > drive ? > > Is the drive you have the database on the boot drive of the computer ? > > If you try to make a database on an external drive (Flash drive ? External > rotating drive ?) instead of the drive you were using, do you see the same > problem ? > > 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] Messages posted on Nabble not getting to list
Gmail track threads - I prefer over just about all html stuff ... Sent from my iPhone > On May 28, 2016, at 3:34 PM, Lauri Nurmi wrote: > > 27.5.2016, 2:48, Balaji Ramanathan kirjoitti: >> Why does SQLite use a mailing list instead of a proper web-hosted >> forum/bulletin board type setup? > > Is there any forum platform whose usability is not totally terrible compared > to mailing lists? > > First of all, to keep track of who has replied to whose post, and which post, > I want to be able to see the posts as a tree. But I don't recall seeing any > tree views on web-based forums. > > > BR, > LN > ___ > 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] Messages posted on Nabble not getting to list
@Daren Any reasons for the thumbs down on MySQL? Their workbench is better that Toad ... Sent from my iPhone > On May 27, 2016, at 10:00 PM, Darren Duncan wrote: > >> On 2016-05-27 2:28 PM, Balaji Ramanathan wrote: >> But when I was debating between MySQL and >> SQLite for my project, I almost didn't choose SQLite because of the archaic >> look and feel of the sqlite.org website and support options available >> there. > > For the love of all that's good, don't choose MySQL for anything. If you > want something bigger than SQLite, look at Postgres instead of MySQL. As a > bonus, the Postgres documentation is much better. -- Darren Duncan > > ___ > 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] Messages posted on Nabble not getting to list
FWIW, I like the look of the website. ) Sent from my iPhone > On May 27, 2016, at 5:47 PM, Scott Robison wrote: > > On Fri, May 27, 2016 at 3:28 PM, Balaji Ramanathan < > balaji.ramanat...@gmail.com> wrote: > >> Ryan Smith wrore: >>> A valid point indeed - for most of us this is simply achieved by e-mail >> f>iltering or perhaps using a different mail account. >>> As an aside - If your company makes software that uses SQLite in any >>> way, you should probably receive the SQLite forum mails somewhere into a >>> folder in your company mails as reference material. >>> >>> This forum is one of the pillars of SQLite's usability. >> >> Well, truth be told, this mailing list gets so little traffic precisely >> because it is a mailing list. Most forums I am a member of have 1000's of >> messages a day compared to the few dozen this mailing list gets in a >> typical day. I have seen more SQLite questions answered on a single day in >> forums like stack overflow than are answered in this mailing list in a >> month or more. But then again, if this mailing list actually had a few >> hundred or thousand posts to it daily, I wouldn't tolerate the flood of >> emails into my inbox. As it stands, it works fine precisely because it is >> so low-volume. And of course, the quality of contributors is probably much >> higher because only the hard-core, dedicated users of SQLite sign up and >> contribute on this mailing list. But when I was debating between MySQL and >> SQLite for my project, I almost didn't choose SQLite because of the archaic >> look and feel of the sqlite.org website and support options available >> there. Flash for the sake of flash is not good, but sometimes you have to >> show people that you and your product are keeping up with the times, not >> already obsolete before you even download it and start using it. > > > Good points. Of course, will billions of deployments around the world, > SQLite isn't hurting for devs or users (whether they know they're using > SQLite or not). :) > > -- > Scott Robison > ___ > 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] Fastest way to add many simple rows to a table?
Use transactions. Or batch mode. Indeed, when we .dump a table from the sqlite3 console, one will see a very fast way to use the former. Sent from my iPhone > On May 26, 2016, at 10:15 AM, Eric Grange wrote: > > Hi, > > I am looking for the fastest way to insert many rows to a simple table. > > By "simple" I mean a relation table (with just a couple integer/key fields) > or even a single-column (temp table used for filtering as an alternative to > a "in" literal), but I want to insert hundreds of thousandths of them as > fast as possible. > > The fastest I have found so far is to use an insert from a select with > json_each() used to provide the data. > Using multiple "insert into", even within a prepared statement within a > transaction is quite slower. > > The json_each approach would satisfy my need, but I am curious is there is > another approach that could be suggested? (without involving a custom > function) > > Eric > ___ > 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] SQL / SQLite for Beginners
Loved the interview. Thanks for the offset. I used to be a principle at Informix. ... Tiz a small world! FWIW, since it's inception, S.Q.L has been pronounced allot like CICS. In as much as master Hipp says that the Ite is the most hip way to say things (pun intended) - and that using is more important than saying, I think that we will stick with that classic "kicks" Tao by pronouncing SQL - as classically pronounced - by tenured S.Q.L professionals. I just don't want students to be laughed at by db folks - Will explain more on the video-up. Sent from my iPhone > On May 26, 2016, at 10:05 AM, jungle Boogie wrote: > >> On 26 May 2016 at 05:49, R.A. Nagy wrote: >> Would this be the preferred pronunciation? >> >> https://youtu.be/hB54p_Xh37M > > That goes a bit too fast but it sounds right to my ears. > > Listen to this interview: > https://changelog.com/201/ > > If you tune in around 17:20 seconds, they'll talk about the name and > it's pronunciation. > > keep up the great work with the tutorials and I'll agree with the > others--narrate the videos as you did very well the first time. > > >> >> Thanks in advance, >> >> -Rn > > > > > -- > --- > inum: 883510009027723 > sip: jungleboo...@sip2sip.info > xmpp: jungle-boo...@jit.si > ___ > 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] Mailing List Request
Any way we could have the mailing list strip tabs from the subject lines? I have been getting very odd subject formatting lately. Ex. Re: [sqlite] How to use sqlite3_blob_open when the blobcolumn is empty? Re: [sqlite] Are BLOB stored inline with the rest of the record fields? Re: [sqlite] How to check if connection to main database is still open or closed before new query ? This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] How to check if connection to main database is still open or closed before new query ?
understood, i will use that way if it is the appropiate though it worked in the
previous i mentioned
Thanks
From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Andy Ling
Sent: Tuesday, March 1, 2016 7:23 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] How to check if connectionto main
databaseis still open or closed before new query ?
I don't think this will work. sqlite3_open and sqlite3_close return a status
which, if they worked, is SQLITE_OK.
A simpler solution might be to set ppDB to NULL in your code every time you
close the database.
So add something like
sqlite3_close(ppDB);
ppDB = NULL ;
Then the original test Simon suggested will work.
Regards
Andy Ling
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of a a
Sent: Tue 01 March 2016 17:13
To: SQLite mailing list
Subject: Re: [sqlite] How to check if connection to main database is still open
or closed before new query ?
Let me explain it better cause i got confused and i am a newbie also.
I open a connection like this
sqlite3 *ppDB;
int dbcon;
dbcon = sqlite3_open(databasename, &ppDB);
/// i do my code i query the database i do some stuff and i close the database
like this
dbcon = sqlite3_close(ppDB);
I want to check after a while if the connection is allready closed or not for
the simple reason not to reopen the database but if is open to run a query or
if it is closed
to reopen the database and then run the query.
so i think the dbcon is the one i have to check not the ppDB :/
it works now maby i didnt understand what ppDB you were reffering to
if(dbcon){
//is open
}else{
//is closed
}
:) thanks though it is tested and runs ok
From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Simon Slavin
Sent: Tuesday, March 1, 2016 7:01 PM
To: SQLite mailing list
Subject: Re: [sqlite] How to check if connection to maindatabase
is still open or closed before new query ?
On 1 Mar 2016, at 4:59pm, a a wrote:
> the sqlite3_close() <--- needs one argument if i pass sqlite3_close(NULL) it
> actually closes again a closed connection ?
No. If you pass it null it realises that the connection is closed and does
nothing.
Simon.
___
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
---
This email has been scanned for email related threats and delivered safely by
Mimecast.
For more information please visit http://www.mimecast.com
---
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to check if connection to main database is still open or closed before new query ?
Let me explain it better cause i got confused and i am a newbie also.
I open a connection like this
sqlite3 *ppDB;
int dbcon;
dbcon = sqlite3_open(databasename, &ppDB);
/// i do my code i query the database i do some stuff and i close the database
like this
dbcon = sqlite3_close(ppDB);
I want to check after a while if the connection is allready closed or not for
the simple reason not to reopen the database but if is open to run a query or
if it is closed
to reopen the database and then run the query.
so i think the dbcon is the one i have to check not the ppDB :/
it works now maby i didnt understand what ppDB you were reffering to
if(dbcon){
//is open
}else{
//is closed
}
:) thanks though it is tested and runs ok
From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Simon Slavin
Sent: Tuesday, March 1, 2016 7:01 PM
To: SQLite mailing list
Subject: Re: [sqlite] How to check if connection to maindatabase
is still open or closed before new query ?
On 1 Mar 2016, at 4:59pm, a a wrote:
> the sqlite3_close() <--- needs one argument if i pass sqlite3_close(NULL) it
> actually closes again a closed connection ?
No. If you pass it null it realises that the connection is closed and does
nothing.
Simon.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to check if connection to main database is still open or closed before new query ?
the sqlite3_close() <--- needs one argument if i pass sqlite3_close(NULL) it
actually closes again a closed connection ?
I cant find if the certain sqlite3 *ppDB is open or closed :/
if(ppDB){
//is open
}else{
//is closed
}
It returns always that the ppDB is open in the above function
From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Simon Slavin
Sent: Tuesday, March 1, 2016 6:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] How to check if connection to main database is
still open or closed before new query ?
On 1 Mar 2016, at 4:47pm, a a wrote:
> Nope the previous was errored it doesn`t actually check it if the ppDB is
> allready closed :/
> It works only if it is open and not closed otherwise if sqlite3_close(ppDB)
> was previously called it sees it as open again
sqlite3_close() ignores connections which are already closed. If you call it
and pass it null, it will return, doing nothing but returning no error message.
So it is safe to issue sqlite3_close() on one connection.
Simon.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to check if connection to main database is still open or closed before new query ?
Nope the previous was errored it doesn`t actually check it if the ppDB is allready closed :/ It works only if it is open and not closed otherwise if sqlite3_close(ppDB) was previously called it sees it as open again :/ Any other way ? From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Simon Slavin Sent: Tuesday, March 1, 2016 6:33 PM To: SQLite mailing list Subject: Re: [sqlite] How to check if connection to main database isstill open or closed before new query ? On 1 Mar 2016, at 4:31pm, a a wrote: > Thank you Simon indeed what i was looking for Please test it first. I can't test C programs here at work and I can't find documentation that it works. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to check if connection to main database is still open or closed before new query ?
It is
if (ppDb != NULL) {
// it's open
} else {
// it's closed
}
yeap it works correct
thank you
From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Simon Slavin
Sent: Tuesday, March 1, 2016 6:33 PM
To: SQLite mailing list
Subject: Re: [sqlite] How to check if connection to main database isstill
open or closed before new query ?
On 1 Mar 2016, at 4:31pm, a a wrote:
> Thank you Simon indeed what i was looking for
Please test it first. I can't test C programs here at work and I can't find
documentation that it works.
Simon.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to check if connection to main database is still open or closed before new query ?
Thank you Simon indeed what i was looking for
From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Simon Slavin
Sent: Tuesday, March 1, 2016 6:17 PM
To: SQLite mailing list
Subject: Re: [sqlite] How to check if connection to main database is still
open or closed before new query ?
On 1 Mar 2016, at 4:02pm, a a wrote:
> I am trying to find if there is a return value of true false or anything that
> can tell me that a connection to sqlite database is open or close
>
> Can you tell me which function is appropiate for this reason ?
If I understand correctly, SQLite sets the value of a database handle to NULL
when it successfully closes the connection. So you should be able to tell if
the handle is 'in use' without any other API calls, just by doing
if (ppDb) {
// it's open
} else {
// it's closed
}
Simon.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to check if connection to main database is still open or closed before new query ?
I am trying to find if there is a return value of true false or anything that can tell me that a connection to sqlite database is open or close Can you tell me which function is appropiate for this reason ? Thank you in advance
[sqlite] Using My Own Encryption Possible ?
Thank you for your responces, I am looking for a non commercial way to make my own encrypted database file if it can be done and if it can be easy by using any kind of encryption i prefer with my own script I want to be able to enrypt and manage the database encrypted with my own encryption like SEE commercial use .. thats what i am asking if it is possible From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Simon Slavin Sent: Monday, February 29, 2016 4:02 PM To: SQLite mailing list Subject: Re: [sqlite] Using My Own Encryption Possible ? On 29 Feb 2016, at 1:28pm, Dominique Devienne wrote: > Can an authorizer really be "abused" that way? You're quite right. It's not useful for this. Thanks for the heads-up. Sorry. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using My Own Encryption Possible ?
Hello, Is there a possibility to use my own Encryption for enctrypting the database file or i can use it only for my data being stored ? I am wondering if there is way to get the full database encrypted (database schema) and not only my entered values. If it needs to go through a perpetual way mentioned at sqlite.org or i can use my own for enctrypting the file. Thanks in advance
[sqlite] How to enabled PRAGMA options in c++ app ?
Thank you very much, i was spending hours of trying to make it work and yes it works fine :) Maby you could update your Documentation in the first lines of PRAGMA documents for such unknowldged like me :/ cause this PRAGMA is a bit confusing in some cases for newbies Thank you in advance Simon From: sqlite-users-bounces at mailinglists.sqlite.org on behalf of Simon Slavin Sent: Thursday, February 25, 2016 2:34 AM To: SQLite mailing list Subject: Re: [sqlite] How to enabled PRAGMA options in c++ app ? On 25 Feb 2016, at 12:01am, a a wrote: > PRAGMA secure_delete = true; SQLite's PRAGMA commands should be executed as if they were SQL commands. They are not C or C++ code. In other words you can take whatever program structure you would use to execute CREATE TABLE MyTable (a, b) and use it for the PRAGMA command. You'll probably do it using sqlite3_exec() but you may have other ideas. By the way, since you wrote that you're new to C++ I warn you of a problem. Your project is a C++ project but you have included sqlite3.h and sqlite3.c, and they are C files. So make sure that you have set them as C and not C++ in your project. Depending on which compiler you use, if you don't do this you can run into compilation problems. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to enabled PRAGMA options in c++ app ?
Hi to all, I am new to sqlite and c++ also. I have download the latest source code of amalgamation from sqlite.org and i added the two files of the four into my app (sqlite3.h and sqlite3.c) I have manage to connect use and run anything i liked within my app with sqlite3 and its very easy indeed. I have some missknowledge though and i can`t do by my own some small modifications that i need into my c++ app I can`t define or use this PRAGMA option and especially i want for example to enable the PRAGMA secure_delete = true; I don`t know where or what should i do with this line of code cause by entering it as it is in my main.cpp it doesn`t do anything and has errors. I have tried also with different ways such as SQLITE_SECURE_DELETE = true; #define SQLITE_SECURE_DELETE = true; #define SQLITE_SECURE_DELETE secure_delete = true; #pragma SQLITE_SECURE_DELETE secure_delete = true; No matter how i tried it ends up with errors in my main.cpp or with no secure delete to true, the database after deleting values doesn`t replace with 0. Can anyone give me an example on how and where to use it ?
[sqlite] SQLite remote access over WAN
> Mensaje original > De: Eduardo Morras > Para: sqlite-users at mailinglists.sqlite.org > Fecha: Mon, 4 Jan 2016 23:27:35 +0100 > Asunto: Re: [sqlite] SQLite remote access over WAN > >On Mon, 04 Jan 2016 22:52:56 +0100 >ajm at zator.com wrote: > >> Hi list: >> >> I've built a Windows desktop app using MS Visual C++ and SQLite. It >> works fair well in local mode, but now I need made it work over a >> TCP/IP network. >> >> My question is if some in this list can suggest a library to that >> purpose, on the basis that I have not concerns on questions like >> concurrency, access permissions or encriptation. Preferable a light >> weigth open source C/C++ library. Not .NET, MFC or other external >> dependencies, although Boost Asio may be Ok. (already I'm using it) > >I used libevent (http://libevent.org/), ZMQ (http://zeromq.org/), nanomsg >(http://nanomsg.org/) and plain standard libc in different projects. Depending >on what you need, where your app will work, the workload you'll have, "the >marketing wise boys" opinion, etc... you should choose one or another. > Eduardo: Thanks for your input. The ZeroMQ framework seem promising, but still I need some dig in it, and see if its asynchronous mode can be adequate to my purposes. As as far as I can see, the matter has no easy solution (in Windows without .NET), so as a last resource, perhaps I use a plain skeletal RPC mechanism (MSRPC). Cheers. -- Adolfo J. Millan.
[sqlite] SQLite remote access over WAN
Hi list: I've built a Windows desktop app using MS Visual C++ and SQLite. It works fair well in local mode, but now I need made it work over a TCP/IP network. My question is if some in this list can suggest a library to that purpose, on the basis that I have not concerns on questions like concurrency, access permissions or encriptation. Preferable a light weigth open source C/C++ library. Not .NET, MFC or other external dependencies, although Boost Asio may be Ok. (already I'm using it) Of course, I've done some dig in Google, codeproject, Stack Overflow, and so on. But at the moment I have not a formed opini?n on that respect. Not to say that in most cases, the concern turn around questions like performance, concurrency, threads and so on. In this respect, the SQLite Network page (http://www2.sqlite.org/cvstrac/wiki?p=SqliteNetwork) seems a bit outdate, and that by the way, a reference, the one of "uSQLiteServer and Client" seem to refer to a dangerous site (Google Chrome security warning). Any sugestion or comment are wellcome. -- Adolfo J. Mill?n
[sqlite] Problem when upgrading from FTS3/4toFTS5modules(revisited)
> > Mensaje original > De: Dan Kennedy > Para: sqlite-users at mailinglists.sqlite.org > Fecha: Mon, 14 Dec 2015 19:15:23 +0700 > Asunto: Re: [sqlite] Problem when upgrading from > FTS3/4toFTS5modules(revisited) > > > >So that looks like database corruption, except we don't think the >database is actually corrupt as the DROP TABLE statement did not fail >when run in the shell tool. So perhaps heap-corruption has caused the >in-memory cache to become corrupt. > >Can you run the whole thing under valgrind? > Dan: This is a Windows app build with MS Visual Studio C++, so can't use Valgrind. Perhaps the cause is a heap corruption, but I doubt because the problem behaves consistently in the following scenarios: a.- The problem appears only when the table is build with the FTS5 modules and not when using FTS3/4. b.- The problem appears when calling the query in different points in the code. c.- The problem appears in both: debug mode and release mode. d.- The code behaves correctly changing the name of the table (deletes successfully any other table). I'm pending to tests the same whith other System version in the same equipe (actually using Windows 10 with AMD 64 bits, but can boot the same with Window 7). Any way I managed a workaround with a fakeDelete function with this logic: Detect if the table ftsm exist: Not exist: -> Create and populate the table. Exists -> "DELETE FROM ftsm WHERE nm IS NOT null" (hope this erase all files) -> "VACUUM ftsm" -> Populate the table Cheers. -- Adolfo J. Millan
[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)
I apologize for the mistake, but you must forget my last two posts (probably due the tiredness or the excitement of the moment). In fact, the query "DELETE TABLE IF EXIST database_name.table_name" goes flawlessly, but indeed does not drop the table. Sorry for the confusion. -- Adolfo J. Millan
[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)
Oops: Sorry for the typo. I mean "DROP TABLE IF EXISTS database_name.table_name" -- Adolfo J. Millan
[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)
> > Mensaje original > De: Dan Kennedy > Para: sqlite-users at mailinglists.sqlite.org > Fecha: Fri, 11 Dec 2015 22:54:45 +0700 > Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 > toFTS5modules(revisited) > > > >Are you able to compile a debugging build of SQLite and set a breakpoint >in sqlite3CorruptError()? > >Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS >statement (pretty good chance), the stack trace will tell us more about >the form of corruption SQLite thinks it has found. > > By the way, diggin in Google for solutions, I gave a try to the complete syntax for the DROP TABLE query, and has found that the form "DROP TABLE IF EXIST table_name.ftsm" goes flawlessly. So: - I've a workaround to the problem. - I'm pretty sure that the problem isn't in my code. HTH. -- Adolfo J. Millan
[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)
> > Mensaje original > De: Dan Kennedy > Para: sqlite-users at mailinglists.sqlite.org > Fecha: Fri, 11 Dec 2015 22:54:45 +0700 > Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 > toFTS5modules(revisited) > >>> >>>> 1a.- Delete the previous table. >>>> DROP TABLE IF EXIST ftsm >>>> >>>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives >>>> an erroro in 1a: "database disk image is malformed". >>>> >>>> Note that in previous attemps I believed that the problem was into try to >>>> delete a ftsm table build with the previous modules, but the error happen >>>> when trying delete a table build with the FTS5 module. >>>> >>>> ... >>>>>> Some clues? >>>>> Not really sure why it might fail there. Can you post the entire >>>>> database schema (results of "SELECT * FROM sqlite_master" or the output >>>>> of the .schema shell tool command)? >>>>> >>>> Dan: >>>> >>>> Here is the schema: >>> That all looks Ok. >>> >>> If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm" >>> command does it report an error as well? >>> >> Dan: >> >> Surprisingly, the shell does not complain when using the same query, and >> indeed, drop the table. >> >> Yeah, I also believe that the problem is in my roof, although that code has >> been behaving Ok from ages with the FTS3/4 modules (only changes some >> directives in other places of the code). >> >> Any way, when running again the code when the table has been previously >> erased (with the shell), the routine works without complaint (executes the >> query correctly), and despite the warning, the rest of the app behaves >> correctly and the queries related with the search works fine. >> >> When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, >> but sqlite3_step() ends with error 11. > >Are you able to compile a debugging build of SQLite and set a breakpoint >in sqlite3CorruptError()? > >Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS >statement (pretty good chance), the stack trace will tell us more about >the form of corruption SQLite thinks it has found. > Dan: I managed a breakpoint int the requested function, and can tell that it has been called twice inside the sqlite3_step() function before it returns. Here you have the call's stack: First call of sqlite3CorrupError() lineno == 56209 sqlite3CorruptError(int lineno) Line 133961 decodeFlags(MemPage * pPage, int flagByte) Line 56209 btreeInitPage(MemPage * pPage) Line 56251 getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, BtCursor * pCur, int bReadOnly) Line 56495 clearDatabasePage(BtShared * pBt, unsigned int pgno, int freePageFlag, int * pnChange) Line 62907 sqlite3BtreeClearTable(Btree * p, int iTable, int * pnChange) Line 62970 btreeDropTable(Btree * p, unsigned int iTable, int * piMoved) Line 63028 sqlite3BtreeDropTable(Btree * p, int iTable, int * piMoved) Line 63111 sqlite3VdbeExec(Vdbe * p) Line 77954 sqlite3Step(Vdbe * p) Line 71546 sqlite3_step(sqlite3_stmt * pStmt) Line 71608 sqlite3_exec(sqlite3 * db, const char * zSql, int (void *, int, char * *, char * *) * xCallback, void * pArg, char * * pzErrMsg) Line 103955 fts5ExecPrintf(sqlite3 * db, char * * pzErr, const char * zFormat, ...) Line 180863 sqlite3Fts5DropAll(Fts5Config * pConfig) Line 180876 fts5DestroyMethod(sqlite3_vtab * pVtab) Line 178532 sqlite3VtabCallDestroy(sqlite3 * db, int iDb, const char * zTab) Line 117587 sqlite3VdbeExec(Vdbe * p) Line 79084 sqlite3Step(Vdbe * p) Line 71546 sqlite3_step(sqlite3_stmt * pStmt) Line 71608 Secon call of sqlite3CorrupError() lineno == 56251 sqlite3CorruptError(int lineno) Line 133961 btreeInitPage(MemPage * pPage) Line 56251 getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, BtCursor * pCur, int bReadOnly) Line 56495 ... Rest the same... Note that the line numbers can be slightly greater than the ones in the original file (SQLite 3.9.1) because the inclusion of some comments. As I can reproduce the problem as needed, In case of interest, I can try to provide any intermediate value inside the stack. Thanks again for yours attention. -- Adolfo J. Millan
[sqlite] Problem when upgrading from FTS3/4 to FTS5modules(revisited)
> > Mensaje original > De: Dan Kennedy > Para: sqlite-users at mailinglists.sqlite.org > Fecha: Fri, 11 Dec 2015 15:28:33 +0700 > Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 to > FTS5modules(revisited) > >> 1a.- Delete the previous table. >> DROP TABLE IF EXIST ftsm >> >> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives >> an erroro in 1a: "database disk image is malformed". >> >> Note that in previous attemps I believed that the problem was into try to >> delete a ftsm table build with the previous modules, but the error happen >> when trying delete a table build with the FTS5 module. >> >> ... >>>> Some clues? >>> Not really sure why it might fail there. Can you post the entire >>> database schema (results of "SELECT * FROM sqlite_master" or the output >>> of the .schema shell tool command)? >>> >> Dan: >> >> Here is the schema: > >That all looks Ok. > >If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm" >command does it report an error as well? > Dan: Surprisingly, the shell does not complain when using the same query, and indeed, drop the table. Yeah, I also believe that the problem is in my roof, although that code has been behaving Ok from ages with the FTS3/4 modules (only changes some directives in other places of the code). Any way, when running again the code when the table has been previously erased (with the shell), the routine works without complaint (executes the query correctly), and despite the warning, the rest of the app behaves correctly and the queries related with the search works fine. When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, but sqlite3_step() ends with error 11. When the table is already deleted, sqlite3_prepare_v2() ends Ok, and sqlite3_step() ends with 101. Frankly, because the routine is the same in both times, and there are not variables to bind, really I don't know where to look. Anyway thanks for your's attention. Cheers! -- Adolfo J. Millan
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules(revisited)
> > Mensaje original > De: Dan Kennedy > Para: sqlite-users at mailinglists.sqlite.org > Fecha: Fri, 11 Dec 2015 02:41:43 +0700 > Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 to FTS5 > modules(revisited) > >> ... >> 1a.- Delete the previous table. >> DROP TABLE IF EXIST ftsm >> >> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives >> an erroro in 1a: "database disk image is malformed". >> >> Note that in previous attemps I believed that the problem was into try to >> delete a ftsm table build with the previous modules, but the error happen >> when trying delete a table build with the FTS5 module. >> ... >> >> Some clues? > >Not really sure why it might fail there. Can you post the entire >database schema (results of "SELECT * FROM sqlite_master" or the output >of the .schema shell tool command)? > Dan: Here is the schema: CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 remove_diacri tics 0',columnsize=0); And here the entire database schema as produced by the shell: sqlite> SELECT * FROM sqlite_master; table|usr|usr|2|CREATE TABLE usr (ky INTEGER PRIMARY KEY,id CHARACTER UNIQUE,lev INTEGER,pwd TEXT) index|sqlite_autoindex_usr_1|usr|3| table|block|block|4|CREATE TABLE block (Stat INTEGER,User INTEGER,Page TEXT,Text INTEGER) table|FreqUse|FreqUse|5|CREATE TABLE FreqUse (Stat INTEGER,User INTEGER,Page TEX T,Text INTEGER) table|blb|blb|6|CREATE TABLE blb (Id INTEGER PRIMARY KEY,Nm INTEGER) table|atm|atm|7|CREATE TABLE atm (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl INTEGER,D c REAL,Dm REAL,St INTEGER) table|coco|coco|8|CREATE TABLE coco (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl INTEGE R,Dc REAL,Dm REAL,St INTEGER) table|lnk|lnk|9|CREATE TABLE lnk (So INTEGER NOT NULL,Ta INTEGER NOT NULL,Cl INT EGER,Tpt INTEGER,UNIQUE (So,Ta,Cl),CHECK(typeof(So)='integer'),CHECK(typeof(Ta)= 'integer'),CHECK((typeof(Cl)='integer') OR (typeof(Cl)='null'))) index|sqlite_autoindex_lnk_1|lnk|10| table|prm|prm|11|CREATE TABLE prm(ref INTEGER, val INTEGER, own INTEGER, UNIQUE( ref, own)) index|sqlite_autoindex_prm_1|prm|12| table|email|email|13|CREATE TABLE email (Id INTEGER PRIMARY KEY, Tit INTEGER, No m INTEGER, Org INTEGER,eHnm INTEGER, ePort INTEGER, eUnm INTEGER, ePsw INTEGER, eScon INTEGER, eDel INTEGER,sHnm INTEGER, sPort INTEGER, sUnm INTEGER, sPsw INTE GER, sScon INTEGER,Enam INTEGER, Rnam INTEGER, Unam INTEGER, Onam INTEGER, iucs INTEGER, sec1 INTEGER, sec2 INTEGER, sec3 INTEGER, sec4 INTEGER,Cl INTEGER, St I NTEGER, aux1 INTEGER, aux2 INTEGER, aux3 INTEGER, aux4 INTEGER, aux5 INTEGER, au x6 INTEGER, aux7 INTEGER) view|AgVtHolder|AgVtHolder|0|CREATE VIEW AgVtHolder AS SELECT id FROM atm WHERE id IN(SELECT so FROM lnk L WHERE L.ta=73 AND L.cl=47) view|AgVtIDt|AgVtIDt|0|CREATE VIEW AgVtIDt AS SELECT L.ta AS 'Hd', C.nm AS 'Dt' FROM atm C, lnk L WHERE C.cl=17 AND C.id IN (SELECT L.so FROM lnk L WHERE L.cl=4 8 AND L.ta IN(SELECT id FROM AgVtHolder)) AND L.so=C.id view|AgVtPre|AgVtPre|0|CREATE VIEW AgVtPre AS SELECT L.ta AS 'Hd', CAST(Nm AS IN T) AS 'Pr' FROM atm C, lnk L WHERE C.cl=17 AND C.id IN(SELECT so FROM lnk L WHER E L.cl=49 AND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L W HERE L.ta=73 AND L.cl=47))) AND L.So=C.id view|AgVtos|AgVtos|0|CREATE VIEW AgVtos AS SELECT D.Hd AS 'Hd', D.Dt AS 'Dt', P. Pr AS 'Pr' FROM AgVtIDt D, AgVtPre P WHERE P.Hd=D.Hd view|AgPdHolder|AgPdHolder|0|CREATE VIEW AgPdHolder AS SELECT id FROM atm WHERE id IN(SELECT So FROM lnk L WHERE L.ta=75 AND L.cl=53) view|AgPdIDt|AgPdIDt|0|CREATE VIEW AgPdIDt AS SELECT L.ta AS 'Hd', C.Nm AS 'Dt' FROM atm C, lnk L WHERE C.Cl=18 AND C.id IN (SELECT L.so FROM lnk L WHERE L.cl=5 4 AND L.ta IN(SELECT id FROM AgPdHolder)) AND L.so=C.id view|AgEfHolder|AgEfHolder|0|CREATE VIEW AgEfHolder AS SELECT id FROM atm WHERE id IN(SELECT So FROM lnk L WHERE L.ta=77 AND L.cl=59) view|AgEfIDt|AgEfIDt|0|CREATE VIEW AgEfIDt AS SELECT L.ta AS 'Hd', C.Nm AS 'Dt' FROM atm C, lnk L WHERE C.Cl=19 AND C.id IN (SELECT L.So FROM lnk L WHERE L.cl=6 0 AND L.ta IN(SELECT id FROM AgEfHolder)) AND L.So=C.id view|AgEfKlv|AgEfKlv|0|CREATE VIEW AgEfKlv AS SELECT L.ta AS 'Hd', C.Nm AS 'Kl' FROM atm C, lnk L WHERE C.cl=19 AND C.id IN(SELECT so FROM lnk L WHERE L.cl=61 A ND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L WHERE L.ta=7 7 AND L.cl=59))) AND L.so=C.id view|AgEfemer|AgEfemer|0|CREATE VIEW AgEfemer AS SELECT D.Hd AS 'Hd', D.Dt AS 'D t', P.Kl AS 'Kl' FROM AgEfIDt D, AgEfKlv P WHERE P.Hd=D.Hd table|ftsm|ftsm|0|CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unico de61 remove_diacritics 0',columnsize=0) table|ftsm_data
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules (revisited)
Hi list: In a C++ Windows app that uses SQLite v. 3.9.1 and behaves well, I try change the search engine from FTS3/4 modules to FTS5, by means off: 1. Define the directive #define SQLITE_ENABLE_FTS5 1 2.- Create the table: CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 remove_diacritics 0',columnsize=0) 3.- Populate the table: INSERT INTO ftsm (row,nm) SELECT id,nm FROM atm WHERE(..) After that, the app and the search engine works as espected. To update the ftsm table after several inserts and deletes, I try to follow this steps 1a.- Delete the previous table. DROP TABLE IF EXIST ftsm 2a.- Create table (as above) 3a.- Populate table (as above). This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an erroro in 1a: "database disk image is malformed". Note that in previous attemps I believed that the problem was into try to delete a ftsm table build with the previous modules, but the error happen when trying delete a table build with the FTS5 module. I managed to drop the ftsm table by means of create a new dbase; create the same tables (except ftsm); populate the tables, and replacing the previous dbase with the new one. But obviously, this method is primitive; time consuming, and has problems when the dbase is in use. Some clues? Thanks in advance. -- Adolfo J. Millan
[sqlite] [AGAIN] SQLite on network share
> Why do you think that is a problem? (the x86_64)? > Yes there isn't a x64 Jet version. Or at least, there is the ACE x64 but can't be installed side-by-side to Office 32 bit. > Other suggests are welcomed? > Microsoft SQL Server is free (with a limitation of a 4GB database) and it > probably integrates the best with other Microsoft "technologies" (I use that > word very loosely). > Yes I use it in other contests but, as written in another message, in need a serverless solutions. Thanks A
[sqlite] [AGAIN] SQLite on network share
> Basically the decision is easy - If you require either of: > - Network data > - User control > > Then you should use a suited Network DB and not a file-based DB. Best > free (without limitations) choices are (In no particular order): > - PostGres | http://www.postgresql.org/ > - MariaDB / MySQL | https://www.mysql.com/ > > ... in fact, let me simply link you to a site with a listing already: > http://tech.gaeatimes.com/index.php/archive/top-7-free-open-source-database-server/ > > > Personally I just use the two above, MySQL especially for Web things, > and PG for Networked systems (but that's just my preference, they both > work either way). I use SQLite to store local data always. I don't > wish to start a fanboy fight, but my feeling is that: MySQL is easier > to code the broad-spectrum SQL for... Postgres is more stable, strict > and secure. (My biased views). > > HTH, > Ryan > > Hi Ryan, your suggestions are good choices (that i use in other constests) but i was oriented to server-less solutions because the application should be able to work in a peer-to-peer environment without a server. Moreover I need a solution that doesn't require much skills to setup. I would avoid MySql/MSSQL/PostGRES/Other server setup, open ports or other possible problems. Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
Ok, thanks for all your replies!!! First, i was asking to understand...before to start development in a wrong direction. I don't have experience with SQLite and even less on a network share. I would understand if corruption is a remote possibility or a certainty. Someone said that Access suffer the same problem... In my experience even with 20-30 clients with low concurrency (management software) MS Access file corruption is a rare event. (the article you linked refer to a bug with an hotfix) I can't use Access in my case because my application is x64. About VistaDB it support use on network share look at http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.html#VistaDB_Introduction_SupportedPlatforms.html and confirmed from its support. Unfortunately i have not experiences with iti can't say how much this is true... Other suggests are welcomed? Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
Il 13/11/2015 19:31, Richard Hipp ha scritto: > On 11/13/15, A. Mannini wrote: >> Hi, >> >> i read SQLite FAQ and understood that use of SQLite on network share CAN >> corrupts database file. >> Fo me, it isn't clear if there is a way to safely use SQLite on a >> network share in contests with few clients (max 5 for ex) and low read / >> write concurrency.. >> > If your network filesystem implements file locks correctly, then > SQLite will work fine. Just be warned that there are many network > filesystems that claim to implement locks correctly, and do most of > the time, but sometimes mess up. > Ok, two questions: 1) is there a list of FS where SQLite works fine? 2) why there are SERVERLESS database (MS Access or VistaDB) that works without FS restrictions? Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
Hi, i read SQLite FAQ and understood that use of SQLite on network share CAN corrupts database file. Fo me, it isn't clear if there is a way to safely use SQLite on a network share in contests with few clients (max 5 for ex) and low read / write concurrency.. Thanks Alessandro
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules
> Mensaje original > De: Richard Hipp >Fecha: 4/11/2015 23:23 (GMT+01:00) >Para: SQLite mailing list >Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 to FTS5 modules >Here is a hack. >Let the name of your FTS3 table be "xyzzy" >(1) Invoke "PRAGMA writable_schema=ON". (Read the documentation about >the dangers thereof. Do not complain if something goes wrong and you >corrupt your database.) >(2) Run "DELETE FROM sqlite_master WHERE name='xyzzy';" >(3) Set "PRAGMA writable_schema=OFF" again. >(4) Run the following: > >DROP TABLE IF EXISTS xyzzy_content; >DROP TABLE IF EXISTS xyzzy_segments; >DROP TABLE IF EXISTS xyzzy_segdir; >DROP TABLE IF EXISTS xyzzy_docsize; >DROP TABLE IF EXISTS xyzzy_stat; Thanks a lot That tricky worked fine. -- Adolfo
[sqlite] "Shipping a New Mindset" - SQLite in Windows 10
> > Mensaje original > De: Simon Slavin > Para: SQLite mailing list > Fecha: Wed, 11 Nov 2015 09:25:47 + > Asunto: Re: [sqlite] "Shipping a New Mindset" - SQLite in Windows 10 > >There's a story behind this. > >During the review leading to HTML5 the web consortium (the one which decides >which features should be introduced to HTML/CSS/JavaScript) listed a >'webdatabase' specification so that browsers could maintain data in a >database-like structure. The specification boiled down to "Implement a simple >SQL engine with an API like [this].". > >Unfortunately all the leading browser publishers implemented this happily and >quickly. "Unfortunately" because they all did it by including SQLite in their >project. This is a problem because the only way to ensure compatibility in >that case is for the web consortium to include the full specification of >SQLite inside the specification for a compatible web browser. Otherwise >people would start doing things like calling PRAGMA commands from web pages, >and then an unofficial standard would evolve which required PRAGMA commands to >be supported from the web API. > >So they did. And it failed. And that's where we are today. > Simon: You are right, and I realized that before. By the way the "storage" issue from JavaScript has been my nightmare in the recent past. I believe that it's a nonsense that the standard suppose a limitation in the field. Although it seem that the Standard work like the lawyers who usually go behind the real life. But that reminds me the adoption of the C++ STL whos first incarnation was -I think remember- in a unique piece from HP and nobody put objections. Any way in the real life we need it desperately IMHO. -- Adolfo
[sqlite] "Shipping a New Mindset" - SQLite in Windows 10
> Mensaje original > De: Richard Hipp > Para: General Discussion of SQLite Database mailinglists.sqlite.org> > Fecha: Tue, 10 Nov 2015 14:41:28 -0500 > Asunto: [sqlite] "Shipping a New Mindset" - SQLite in Windows 10 > > >http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/ > Congratulations to all the team. A great praise coming from M$. A great notice to all us who write Windows. And a final prayer to MS: add an extension to use it from JavaScript. -- Adolfo J. Millan
[sqlite] "Shipping a New Mindset" - SQLite in Windows 10
What no love for their own Access DB? Congrats! -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski Sent: Tuesday, November 10, 2015 2:19 PM To: SQLite mailing list Subject: Re: [sqlite] "Shipping a New Mindset" - SQLite in Windows 10 Wicked. Congrats! On Tue, Nov 10, 2015 at 2:41 PM, Richard Hipp wrote: > http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/ > > -- > D. Richard Hipp > drh at sqlite.org > ___ > 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 This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules
Hi list:
Using SQLite 3.9.1, suppose a C++ application using FTS3/4 modules with this
pseudocode:
1a In config.h
#define SQLITE_ENABLE_FTS3 1
#define SQLITE_ENABLE_FTS3_PARENTHESIS 1
2a.- Delete the previous table if exist
DROP TABLE IF EXISTS ftsm
3a.- Then create the table
CREATE VIRTUAL TABLE ftsm USING fts4(nm,tokenize=simple,matchinfo=fts3)
4a.- Populate the table
INSERT INTO ftsm (rowid,nm) SELECT id,nm FROM atm WHERE(...)
The app run as espected and the search mechanism work as a charm.
Then try to upgrade the app to use the FTS5 module -within the previous dbases-
with this pseudocode:
1b In config.h
#define SQLITE_ENABLE_FTS5 1
2b.- Delete the previous table if exist (same as previous)
DROP TABLE IF EXISTS ftsm
3b.- Then create the table
CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='unicode61
remove_diacritics 0',columnsize=0)
4b.- Populate the table (same as previous)
INSERT INTO ftsm (rowid,nm) SELECT id,nm FROM atm WHERE(...)
When reaching 2b, the new app show an error: "no such module: fts4"
When accessing the dataBase with the shell, the .tables command show the ftsm
table. Then, using manually the 2a query ("DROP TABLE IF EXISTS ftsm;") the
table is deleted without complain and the .tables command show that the table
not longer exist.
Then, the app step thru 2b without complain and creates and populates the new
table smoothly (steps 3b and 4b).
The question: do is there any method to delete the old table without including
the FTS3/4 modules to those users "in the field"?
Thanks for any idea.
--
Adolfo J. Millan
[sqlite] Erros using some compiler options for SQLite 3. and V. Studio 2015
Keith: Thank a lot. Now, the file compiles without error, although still there are two warnings (unreferenced local variables): 1>sqlite3\sqlite3.c(79452): warning C4101: 'z': variable local sin referencia 1>sqlite3\sqlite3.c(79451): warning C4101: 'zTrace': variable local sin referencia Perhaps the sentence #ifndef SQLITE_OMIT_TRACE must be moved up a couple lines Thanks again. -- Adolfo > > Mensaje original > De: "Keith Medcalf" > Para: "ajm at zator.com" , "General Discussion of SQLite > Database" > Fecha: Sun, 25 Oct 2015 13:56:33 -0600 > Asunto: RE: [sqlite] Erros using some compiler options for SQLite 3. and V. > Studio 2015 > > >Try changing: > >> #define SQLITE_OMIT_TCL_VARIABLE >> #define SQLITE_OMIT_PROGRESS_CALLBACK >> #define SQLITE_OMIT_FOREIGN_KEY >> #define SQLITE_OMIT_AUTOVACUUM >> #define SQLITE_OMIT_EXPLAIN >> #define SQLITE_SECURE_DELETE >> #define SQLITE_OMIT_BUILTIN_TEST >> #define SQLITE_OMIT_TRACE >> #define SQLITE_ENABLE_FTS3 >> #define SQLITE_ENABLE_FTS3_PARENTHESIS > >to > >#define SQLITE_OMIT_TCL_VARIABLE 1 >#define SQLITE_OMIT_PROGRESS_CALLBACK 1 >#define SQLITE_OMIT_FOREIGN_KEY 1 >#define SQLITE_OMIT_AUTOVACUUM 1 >#define SQLITE_OMIT_EXPLAIN 1 >#define SQLITE_SECURE_DELETE 1 >#define SQLITE_OMIT_BUILTIN_TEST 1 >#define SQLITE_OMIT_TRACE 1 >#define SQLITE_ENABLE_FTS3 1 >#define SQLITE_ENABLE_FTS3_PARENTHESIS 1 > >in your config.h and see if this resolves the issue. > >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of ajm at zator.com >> Sent: Sunday, 25 October, 2015 13:24 >> To: sqlite-users at mailinglists.sqliteorg >> Subject: [sqlite] Erros using some compiler options for SQLite 3. and V. >> Studio 2015 >> >> Hi list: >> >> Using SQLite v.3.9.1 amalgamation code. >> MS Visual Studio 2015 for desktop C++ compiler >> >> I've got the same compiler error >> >> 1>sqlite3\sqlite3.c(x): fatal error C1017: expresi?n constante de tipo >> entero no v?lida >> >> in the following lines of sqlite3.c: >> >> #if SQLITE_ENABLE_FTS3 >> #if SQLITE_ENABLE_FTS3_PARENTHESIS >> #if SQLITE_OMIT_AUTOVACUUM >> #if SQLITE_OMIT_BUILTIN_TEST >> #if SQLITE_OMIT_EXPLAIN >> #if SQLITE_OMIT_FOREIGN_KEY >> #if SQLITE_OMIT_PROGRESS_CALLBACK >> #if SQLITE_OMIT_TCL_VARIABLE >> #if SQLITE_OMIT_TRACE >> #if SQLITE_SECURE_DELETE >> >> The application compiles correctly except when includes the config.h file >> with these compiler options: >> >> #define SQLITE_OMIT_TCL_VARIABLE >> #define SQLITE_OMIT_PROGRESS_CALLBACK >> #define SQLITE_OMIT_FOREIGN_KEY >> #define SQLITE_OMIT_AUTOVACUUM >> #define SQLITE_OMIT_EXPLAIN >> #define SQLITE_SECURE_DELETE >> #define SQLITE_OMIT_BUILTIN_TEST >> #define SQLITE_OMIT_TRACE >> #define SQLITE_ENABLE_FTS3 >> #define SQLITE_ENABLE_FTS3_PARENTHESIS >> >> The only directives of my config.h that seem be accepted, are: >> >> #define SQLITE_MAX_VARIABLE_NUMBER 25 >> #define SQLITE_THREADSAFE 1 >> >> Note that the proyect also compiles smoothly without errors using all the >> compiler directives with SQLite v.3.7.15.1 and MS V. Studio 2013. >> >> Any suggestion would be appreciated. >>
[sqlite] Erros using some compiler options for SQLite 3. and V. Studio 2015
Hi list: Using SQLite v.3.9.1 amalgamation code. MS Visual Studio 2015 for desktop C++ compiler I've got the same compiler error 1>sqlite3\sqlite3.c(x): fatal error C1017: expresi?n constante de tipo entero no v?lida in the following lines of sqlite3.c: #if SQLITE_ENABLE_FTS3 #if SQLITE_ENABLE_FTS3_PARENTHESIS #if SQLITE_OMIT_AUTOVACUUM #if SQLITE_OMIT_BUILTIN_TEST #if SQLITE_OMIT_EXPLAIN #if SQLITE_OMIT_FOREIGN_KEY #if SQLITE_OMIT_PROGRESS_CALLBACK #if SQLITE_OMIT_TCL_VARIABLE #if SQLITE_OMIT_TRACE #if SQLITE_SECURE_DELETE The application compiles correctly except when includes the config.h file with these compiler options: #define SQLITE_OMIT_TCL_VARIABLE #define SQLITE_OMIT_PROGRESS_CALLBACK #define SQLITE_OMIT_FOREIGN_KEY #define SQLITE_OMIT_AUTOVACUUM #define SQLITE_OMIT_EXPLAIN #define SQLITE_SECURE_DELETE #define SQLITE_OMIT_BUILTIN_TEST #define SQLITE_OMIT_TRACE #define SQLITE_ENABLE_FTS3 #define SQLITE_ENABLE_FTS3_PARENTHESIS The only directives of my config.h that seem be accepted, are: #define SQLITE_MAX_VARIABLE_NUMBER 25 #define SQLITE_THREADSAFE 1 Note that the proyect also compiles smoothly without errors using all the compiler directives with SQLite v.3.7.15.1 and MS V. Studio 2013. Any suggestion would be appreciated. -- Adolfo J. Millan
[sqlite] Simple Math Question
Scott, I agree with everything you said but... To me if a program/CPU evaluates something internally, then when it reports the result it should be the result as it sees it. It shouldn't report something different. So using your analogy, I ask a English speaking person a two interrelated questions, they translate the questions to Japanese in their head, then answers one question in Japanese and another in English. I say pick a language and stick with it. Either answer my question all in English or all in Japanese don't mix it. I think we are getting to hung up on the details of what is going on internally. The real question is why don't the two results, which are coming from the same program, agree? (i.e. return 22.99 not 23.0) Richard -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Scott Hess Sent: Friday, October 23, 2015 10:05 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Simple Math Question On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne wrote: > On Fri, Oct 23, 2015 at 4:16 PM, Rousselot, Richard A < > Richard.A.Rousselot at centurylink.com> wrote: > > So I decided to output 1000 digits, because why not? So now I am > > more perplexed with all these digits showing it is working the > > opposite of > how I > > expected it. Why is the second set of equations evaluating to a "yes" > when > > it is the only one that is obviously NOT equal to the expression??? > > Indeed, that's puzzling :) Just to be clear, though, how floating-point numbers work is breaking your expectations because your expectations are wrong when applied to floating-point numbers. Internally, they are base-2 scientific notation, so asking for more significant digits in the base-10 representation won't help - base-10 fractional numbers cannot always be represented precisely in base-2, ALSO base-2 fractional numbers cannot always be represented precisely in base-10, so it's like a game of telephone where you can end up slightly removed from where you started out, even though it seems like it's a simple round trip. Since each individual digit cannot be represented perfectly, it doesn't matter how many digits of precision you ask for, you'll always be able to find cases where it doesn't line up like you expect. Think of it this way: Find an English sentence, and find an English to Japanese translator. Translate each individual word of the sentence from English to Japanese, then concatenate the results together. Then translate the entire original sentence to Japanese. The results will almost never be the same. Then do the same process translating the Japanese back to English. Again, the two routes will provide different results, _and_ both of those results will almost certainly not match the original English sentence. This isn't a reflection of the translator's abilities at all. I'm not saying the computer is always right, just that the computer is following a very strict recipe with reproducible results. I don't mean reproducible like your three examples make logical sense to you, the user, I mean reproducible like my Intel box gives the same results as my AMD box as my ARM box. If you want to be able to deal with fractional decimal values with high fidelity, you either need to arrange for base-10 representation (slow, because computers have to simulate it), or you have to do your math in shifted fashion (fast, but can be error prone). -scott ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] Simple Math Question
Dominique,
Thanks for that idea but now I have more questions?
So I decided to output 1000 digits, because why not? So now I am more
perplexed with all these digits showing it is working the opposite of how I
expected it. Why is the second set of equations evaluating to a "yes" when it
is the only one that is obviously NOT equal to the expression???
SELECT
printf('%4.1000f',9.2+7.9+0+4.0+2.6+1.3),
case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" end,
printf('%4.1000f',9.2+7.8+0+3.0+1.3+1.7),
case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" end,
printf('%4.1000f',9.2+7.9+0+1.0+1.3+1.6),
case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" end
Result
25.0
000|no|22.99
00|yes|21.00
00|no
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique
Devienne
Sent: Friday, October 23, 2015 8:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Simple Math Question
On Fri, Oct 23, 2015 at 3:45 PM, Rousselot, Richard A < Richard.A.Rousselot at
centurylink.com> wrote:
> Anyhow, one last question. If the case statements are evaluating
> something that is not a whole number (or better yet not exactly
> matching on both sides of the equation) . Why is it that when
> displaying the results of the math sqlite does not produce something
> like 25.03 or 22.999? Shouldn't the result have all
> significant digits showing?
> I guess that is why these results seem misleading.
>
The output is done by sqlite3.exe, the command line shell, and you'd have to
look at its code to know for sure.
But instead, take control of the formatting, by using prin
[sqlite] Simple Math Question
Sorry, I seemed to kick off a nerd fight. :) Anyhow, one last question. If the case statements are evaluating something that is not a whole number (or better yet not exactly matching on both sides of the equation) . Why is it that when displaying the results of the math sqlite does not produce something like 25.03 or 22.999? Shouldn't the result have all significant digits showing? I guess that is why these results seem misleading. Richard From: Rousselot, Richard A Sent: Thursday, October 22, 2015 2:45 PM To: 'sqlite-users at mailinglists.sqlite.org' Cc: Rousselot, Richard A Subject: Simple Math Question Doing the following math, why is it that the results are not all returning "yes"? SELECT (9.2+7.9+0+4.0+2.6+1.3), case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" end, (9.2+7.8+0+3.0+1.3+1.7), case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" end, (9.2+7.9+0+1.0+1.3+1.6), case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" end FROM sometable; Result 25.0 no 23.0 yes 21.0 no I'm sure this has an obvious answer but it isn't jumping out at me. Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] Simple Math Question
I fully expected something like this from a fractional number like 1/3 but didn't realize that this would be the case if when the digits were explicitly stated and they summed to a whole number. I will use a Cast statement to resolve. FWIW, MySQL and Oracle both return all yes for that query. Thanks to everyone that replied. Richard -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephan Beal Sent: Thursday, October 22, 2015 2:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Simple Math Question On Thu, Oct 22, 2015 at 9:45 PM, Rousselot, Richard A < Richard.A.Rousselot at centurylink.com> wrote: > Doing the following math, why is it that the results are not all > returning "yes"? > > SELECT > (9.2+7.9+0+4.0+2.6+1.3), > case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else > "no" end, > (9.2+7.8+0+3.0+1.3+1.7), > case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else > "no" end, > (9.2+7.9+0+1.0+1.3+1.6), > case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else > "no" end FROM > sometable; > Try the same with integers as you'll likely see different results. Floating-point math is fraught with problems when it comes to expecting exact results at a specific precision. See: http://floating-point-gui.de/ the first example of which demonstrates the problem you are seeing. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] Simple Math Question
Doing the following math, why is it that the results are not all returning "yes"? SELECT (9.2+7.9+0+4.0+2.6+1.3), case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" end, (9.2+7.8+0+3.0+1.3+1.7), case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" end, (9.2+7.9+0+1.0+1.3+1.6), case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" end FROM sometable; Result 25.0 no 23.0 yes 21.0 no I'm sure this has an obvious answer but it isn't jumping out at me. Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] Equivalent Syntax for Oracle row_number() OVER (PARTITION)
What is the equivalent SQLite syntax for the Oracle SQL syntax below? row_number() OVER (PARTITION BY x ORDER BY y DESC, z) AS aField Example... SELECT department_id, first_name, last_name, salary FROM ( SELECT department_id, first_name, last_name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn FROM employees ) WHERE rn <= 3 ORDER BY department_id, salary DESC, last_name; http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions156.htm#SQLRF06100 Thanks in advance, Richard This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] order by not working in combination with random()
I have noticed that SQLite Query Browser is running slower than other IDEs,
including SQLitespeed, for some reason. Even when each IDE is set to using
similar versions of the SQLite3.dll. We had a recursive query in SQB take 6
min, on other IDEs it would be less than 2 min.
My $0.02
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.Smith
Sent: Friday, August 28, 2015 7:44 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] order by not working in combination with random()
On 2015-08-28 01:17 PM, Yahoo! Mail wrote:
> Obviously you did not get my issue; something is wrong and your timer
> suggestion indicates this. During the execution of each command, I
> would monitor it with *watch "du test.db*"*. The journal size would go
> mad even surpassing the database's actual size at some moments.
>
> *sqlite> .timer on
> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
> t1; begin; with recursive c(x) as (values(1) union all select x + 1
> from c where x < 100) insert into t1(a) select datetime('now') from c;
> commit; Run Time: real 0.207 user 0.00 sys 0.004000
>
> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
> t1; begin; with recursive c(x) as (values(1) union all select x + 1
> from c where x < 1) insert into t1(a) select datetime('now')
> from c; commit; Run Time: real 94.226 user 73.096000 sys 4.788000
>
> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
> t1; begin; with recursive c(x) as (values(1) union all select x + 1
> from c where x < 100) insert into t1(a) select datetime('now') from c;
> commit; Run Time: real 209.612 user 4.724000 sys 21.588000*
Firstly, that's a 100-million inserts, not a billion as in the previous post.
The billion inserts should have taken around 1000s or 20-ish minutes.
It might be the vacuum that takes the time for you... the drop table is quick.
If I run the same through SQLitespeed hooking SQLite 3.8.11.1 with full
diagnostics (and probably worse hardware than you have), I get about 120sec (2
minutes) on the insert into an empty table, and 3.6s on the
drop+insert 10 items.
I am not vacuuming inside the following script since vacuums cannot happen
inside transactions, but I vacuum directly after it and the vacuum takes less
than 2 seconds, so I doubt that is the problem either.
--
====
drop table if exists t1;
create table t1(a datetime);
with recursive c(x) as (values(1) union all select x + 1 from c where x <
1) insert into t1(a) select datetime('now') from c;
-- Script Stats: Total Script Execution Time: 0d 00h 02m and
00.082s
-- Total Script Query Time: 0d 00h 02m and
00.064s
-- Total Database Rows Changed: 1
-- Total Virtual-Machine Steps: -2094967211
-- Last executed Item Index:3
-- Last Script Error:
--
-- 2015-08-28 14:25:16.109 | [Success]Script Success.
-- 2015-08-28 14:25:45.088 | [Success]Transaction Committed.
--
drop table if exists t1;
create table t1(a datetime);
with recursive c(x) as (values(1) union all select x + 1 from c where x
< 10) insert into t1(a) select datetime('now') from c;
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
03.605s
-- Total Script Query Time: 0d 00h 00m and
03.582s
-- Total Database Rows Changed: 10
-- Total Virtual-Machine Steps: 305
-- Last executed Item Index:3
-- Last Script Error:
--
-- 2015-08-28 14:26:29.095 | [Success]Script Success.
-- 2015-08-28 14:26:29.239 | [Success]Transaction Committed.
--
Maybe some more information about your specific use case and environment
is needed.
Cheers,
Ryan
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential
or privileged information. Unauthorized use of this communication is strictly
prohibited and may be unlaw
[sqlite] SQLite database becomes corrupt on iOS
Are all your customers on the latest version of iOS? Maybe you need to make your app only compatible with current versions that behave properly? What version of iOS and model is the customer that has the issue running? Regards, A fly on the wall... -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of skywind mailing lists Sent: Friday, August 14, 2015 10:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite database becomes corrupt on iOS Hi, this is the problem. I could never reproduce it by myself and even my customers have normally no problems. But it happens once a year or so to one of my customers. Still this is very annoying as it results in data loss. Regards, Hartwig > Am 14.08.2015 um 17:15 schrieb Simon Slavin : > > > On 14 Aug 2015, at 4:10pm, skywind mailing lists > wrote: > >> I do not get any error message from SQLite. And the database only gets >> corrupted when the iDevice has to shut down due to battery issues. I have >> never had a customer complaining about a corrupt database during normal >> operation. > > If you run the application on your own iDevice, and let it run out of power, > do you get the corruption yourself ? > > Do you have any way to log when you're getting background and quit > notifications ? Could you have your application store them in a text file > (not a SQLite database, obviously) so you can check to see whether it's > getting them before the device runs out of power ? > > Simon. > ___ > 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 This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] Can you enable Recursive Triggers via the connection string?
I hope this is the right address to send my questions to. I found this mailing list on the SQLite.org<http://SQLite.org> site under the ?Support? section. In short, can you enable Recursive Triggers via the connection string? I?ve read in some places that you can specify PRAGMA statements on the connection string, but that doesn?t appear to be the case, at least not as far as Recursive Triggers go. The problem is we have logic that depends on them, but we?re using frameworks which open and close the connections automatically so it?s not easy for us to manually execute the PRAGMA statement at that time. Here?s my question on StackOverflow.com<http://StackOverflow.com> regarding it. http://stackoverflow.com/questions/30457501/in-sqlite-can-you-set-the-recursive-triggers-pragma-via-the-connection-string As you can see, I haven?t received a single comment in over a month, let alone an answer: I?m starting to think our only course of action would be to manually recompile the DLL but that seems like it would be opening a giant can of worms for a simple feature. Thanks in advance for your help. I?m hoping this is a simple ?Yes, here?s how? or ?No, it can?t be done.? Mark
[sqlite] Query tooooo sloooooooooow
How slow is too slow? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of James Qian Wang Sent: Thursday, July 30, 2015 5:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query to sloow my desktop (windows 7) SQLite version 3.8.7.4 2014-12-09 01:34:36 and explain query plan showed: 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?) my linux box needs upgrade. regardless, both very slow On Thu, Jul 30, 2015 at 11:03 AM, Clemens Ladisch wrote: > James Qian Wang wrote: > > select count(*) from contact c left join history h on > > (h.elid=c.elid); > > Please show the output of EXPLAIN QUERY PLAN for this query. > > If it does not look like this: > 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 > 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?) > you should get a newer SQLite version. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- James Qian Wang Mobile: 44 7986 099 233 ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
[sqlite] Mozilla wiki 'avoid SQLite'
> Mensaje original > De: > Para: "'General Discussion of SQLite Database'" mailinglists.sqlite.org> > Fecha: Thu, 18 Jun 2015 14:50:40 +1000 > Asunto: Re: [sqlite] Mozilla wiki 'avoid SQLite' > Darren: Maybe you're right. But to that relationship of "goodness" that would be desirable, perhaps we should add a few drops of pragmatism. Experience shows that in many cases, the perfect is the enemy of the good, and despite a disrupting transition (refusing the bad legacy of SQL) can avoid some drawbacks, however a non-disrupting evolution, has the advantage of being more easily accepted and assimilated by the users, as has happened before. -- A.J. Millan
[sqlite] Mozilla wiki 'avoid SQLite'
Indeed, I'm agree with Darren, and continuing its thought, perhaps that hypothetical new language would be a clean extensi?n of SQL in the same way that C++ was respect to C, simplifying the transition and reutilization of legacy code. Cheers. -- A.J. Millan > > Mensaje original > De: > Para: "'General Discussion of SQLite Database'" mailinglistssqlite.org> > Fecha: Thu, 18 Jun 2015 14:50:40 +1000 > Asunto: Re: [sqlite] Mozilla wiki 'avoid SQLite' > >The question for now is: does a new database programming language have a >place? >
[sqlite] Mozilla wiki 'avoid SQLite'
> Mensaje original > De: Scott Hess > Para: General Discussion of SQLite Database mailinglists.sqlite.org> > Fecha: Sun, 14 Jun 2015 10:10:24 -0700 > Asunto: Re: [sqlite] Mozilla wiki 'avoid SQLite' > While certainly, the thread has derived from the concept of the OP, and even some think that this kind of discussion is out of place, I must confess that I enjoy greatly with this type of thread, that seem to me like images of certain books that seem too dry without them. Usually I learn a lot from them, because readed dispassionately, the contributions of the various comments -which by the way tend to be fairly level- often clarify and put in context, aspects that are important. From the allegation of the Mozilla Wiki, to the response of Dr. Hipp, passing through the funny misidentification of Alex Bowden (who does not make mistakes?). That said, I would like to complete the opinion of Aleksey Tulinov: >In my opinion best database language would reflect the way in which >database works and best computer programming language would reflect the >way in which computer works, as close as reasonably possible. I think the best database language should reflect how earthlings think about the data, and the best computer programming language would reflect easily the result we want get from them. Cheers. -- A. J. Millan
[sqlite] BUG in 3.8.8.3 and pre-release: Wrong ERROR_DISK_FULL writing a blob on Windows
Hi, this is actually a Ticket, the good new is that is already pre-analyzed
and probably also the cause has been found.
I hope this is the right place to put this
TITLE: Wrong ERROR_DISK_FULL writing a blob on Windows
DESCRIPTION:
Having a sqlite database about 4.1 GB
writing blobs in some table, for instance using a statement like
INSERT INTO inodeCargo VALUES (1000, readfile ("ScreenShot.png"));
an error ERROR_DISK_FULL can be through wrongly (under certain
conditions)
Windows 7 64-bit
REPRODUCIBLE:
When the conditions are met, always reproducible
CAUSE :
windows function WriteFile return error 112 (ERROR_DISK_FULL) when
trying to write
1024 bytes using a offset = 4294966272 = 0xFC00 = - 2023
This was observed using the default mode (OVERLAPPED) although a
test compiling
the code with
#define SQLITE_WIN32_NO_OVERLAPPED
at the begining also failed.
PATCH:
The patch (see sqlite3_modified.c) work in my case, but obviously cannot
fix the mode NO_OVERLAPPED (it does not compile in this mode). Also
all procedures
using the buggy function (if this result to be the cause) should be
reviewed.
A more precise fix has to be developed by some expert in sqlite code.
ANALYSIS:
A sqlite db used to store files came in the conditions described
size of database file : 4.194.303 KB
size of journal file : 8 KB
size of the image (png file) : 4.417 KB
SQL Query returning systematically ERROR_DISK_FULL:
INSERT INTO inodeCargo VALUES (-24, 4522876, 0, readfile
("ScreenShot320.png"));
(hard disk of the test with about 1 TB free space)
Since the case was 100% reproducible I could debug the problem using
sqlite-amalgamation-3080803.zip (ver 3.8.8.3)
I tried sqlite-amalgamation-201503091040.zip as well with the same
result, so the problem was also not fixed in this pre-release.
In debug session I found that the error came from
static int winWrite (...
and it is given in the line
if( !osWriteFile(pFile->h, aRem, nRem, &nWrite, &overlapped) ){
which is a call to a windows api function WriteFile
adding the extra debug messages into a file (see sqlite3_modified.c
function "static int winWrite")
this was the output before the fail (ERROR_DISK_FULL)
...
WRITE file=0030, buffer=00EDBE08, amount=1024, offset=4294965248, lock=4
OVERLAPPED offset=4294965248, OffsetHigh=0
WRITE file=0030, buffer=00EDC2E0, amount=1024, offset=4294966272, lock=4
OVERLAPPED offset=4294966272, OffsetHigh=0
Now note that
4294966272 = 0xFC00
and - FC00 = 3FF = 1023 ! and we want to write
1024 bytes!!!
That this is a Microsoft bug is only a guess for what I have seen
debugging the code,
anyway if not I hope this analysis helps to find it the final cause of
the problem and it could be fixed.
Regards
Alejandro
-- next part --
TITLE: Wrong ERROR_DISK_FULL writing a blob on Windows
DATE: 2015.03.15
AUTHOR: Alejandro Xalabarder
DESCRIPTION:
Having a sqlite database about 4.1 GB
writing blobs in some table, for instance using a statement like
INSERT INTO inodeCargo VALUES (1000, readfile ("ScreenShot.png"));
an error ERROR_DISK_FULL can be through wrongly (under certain conditions)
Windows 7 64-bit
REPRODUCIBLE:
When the conditions are met, always reproducible
CAUSE :
windows function WriteFile return error 112 (ERROR_DISK_FULL) when trying to
write
1024 bytes using a offset = 4294966272 = 0xFC00 = - 2023
This was observed using the default mode (OVERLAPPED) although a test
compiling
the code with
#define SQLITE_WIN32_NO_OVERLAPPED
at the begining also fails.
PATCH:
The patch (see sqlite3_modified.c) has worked in my case, but obviously
cannot
fix the mode NO_OVERLAPPED since is not compiled in this mode. Also all
procedures
using the buggy function, if this result to be the cause, should be reviewed.
Therefore a more precise fix has to be developed by some expert in sqlite
code.
ANALYSIS:
A sqlite db used to store files came in the conditions described
size of database file : 4.194.303 KB
size of journal file : 8 KB
size of the image (png file) : 4.417 KB
SQL Query returning systematically ERROR_DISK_FULL:
INSERT INTO inodeCargo VALUES (-24, 4522876, 0, readfile
("ScreenShot320.png"));
(hard disk of the test with about 1 TB free space)
Since the case was 100% reproducible I could debug the problem using
sqlite-amalgamation-3080803.zip (ver 3.8.8.3)
I tried sqlite-amalgamation-201503091040.zip as well with the same
result, so the problem was also not fixed in this pre-release.
In debug session I found that the error came from
static int winWrite (...
and it is given in the line
if( !osWrite
Re: [sqlite] XML into sqlite
For the time being, it's a one-off thing. In the future it could become regular. I used to solve all my analytical DB needs with MS Access. Since I migrated to a Mac I decided to look for a new DB engine and am exploring sqlite. I have been running a couple of tests, based on actual data problems I have solved in the past with Access. So far, I have been able to replicate everything. Next step is to venture into XML - sqlite integration...both ways. With some restrictions, it worked well between Access and XML, via Excel. Cheers, CARLOS A. Sent from my iPad > On 11/09/2014, at 6:04, Simon Slavin wrote: > > >> On 11 Sep 2014, at 6:04am, Carlos A. Gorricho >> wrote: >> >> How would you recommend to drop XML data into an sqlite DB? I am guessing >> there is more than one way. > > Is this a one-time thing or are you going to have to do it every month/week ? > > Do you have a favourite programming language, or do you want to do this with > some combination of spreadsheets, word processing or shell commands ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] XML into sqlite
How would you recommend to drop XML data into an sqlite DB? I am guessing there is more than one way. I thought of importing XML into Excel, converting to csv, for further import into sqlite, now that I know how to import csv into sqlite ... almost always! But this seems like a long haul. Cheers, CARLOS A. Sent from my iPad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems uploading CSV into sqlite3 DB
Dear all, A brief update on how I managed to solve the problem...thank you all for you remarks and comments, since these were instrumental in the solution. Indeed sqlite versions had to do a lot with the problem, where 3.8 can handle csv imports, not without some glitches. I had to go in circles several times to realize how to make sure I was always working on the right sqlite version. I also noticed that I had some problems with my data, for csv import purposes, in the sqlite environment. This problem did NOT affect csv import into MS Access. The problem was a field with a very long blank string, something like 40 space-characters. Apparently the sqlite import engine was not able to recognize the end of this string and was merging it with the next field, causing as mismatch between the column field names and the data fields. This mismatch would compound with every new line imported. I found the solution by sheer luck. I added manually a new column in the csv file (from Excel) with a formula to test if the blank field was empty or text. This created a column filled with boolean values, which sqlite csv import engine was able to recognize, hence delimiting the large string of spaces...odd I know, but it worked! Saludos/Cheers, *CARLOS A. GORRICHO* Managing Partner Heptagon Group S.A.S. cgorri...@heptagongroup.co Cel COL +57 314 771 0660 Cell USA +1 713 574 2242 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems uploading CSV into sqlite3 DB
So, any ideas on how to solve this issue will be more than welcome. I have tried several shortcuts...none works. Latest was to install an Ubuntu 14.04 Virtual Machine on my Mac, via Parallels software. Downloaded sqlite de Linux way: $ sudo apt-get install sqlite3 Ran the CSV .import protocol and didn't work Result was eve weirder, since it create a table using ALL the records as columns headers... Saludos/Cheers, *CARLOS A. GORRICHO* Managing Partner Heptagon Group S.A.S. cgorri...@heptagongroup.co Cel COL +57 314 771 0660 Cell USA +1 713 574 2242 2014-09-03 10:24 GMT-05:00 Carlos A. Gorricho (HGSAS) < cgorri...@heptagongroup.co>: > When I start sqlite double clicking the icon in the Mac Applications > folder: > > Last login: Wed Sep 3 10:13:32 on ttys000 > > HEPTAGON-GROUP-SAS:~ carlosgorricho$ /Applications/sqlite3 ; exit; > > SQLite version 3.8.5 2014-06-04 14:06:34 > > Enter ".help" for usage hints. > > Connected to a *transient in-memory database*. > > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> > > > When starting sqlite on a Terminal Window: > > Last login: Wed Sep 3 10:11:42 on ttys002 > > HEPTAGON-GROUP-SAS:~ carlosgorricho$ sqlite3 > > SQLite version 3.7.13 2012-07-17 17:46:21 > > Enter ".help" for instructions > > Enter SQL statements terminated with a ";" > > sqlite> > > > I downloaded the only precompile binary currently available for Mac in > www.sqlite.org website. > > To perform the job described earlier, I run the Terminal version in batch > mode. That is, I created a script file with all the .commands to upload de > CSV file, and ran it on a new database: > > $ sqlite3 newDB.db < my_script_file > > > When I got the tab1 error message, I reverted to performing the job from > inside the database, creating first the table and then uploading the data. > The result was the huge single-record-field DB I shared earlier. > > I would venture to say I did this from the Terminal sqlite version, but I > am not certain... > > Thanks in advance for your comments and guidance. Please let me know if I > can do anything to help you in other fronts. > > > > > Saludos/Cheers, > > > *CARLOS A. GORRICHO* > Managing Partner > Heptagon Group S.A.S. > cgorri...@heptagongroup.co > Cel COL +57 314 771 0660 > Cell USA +1 713 574 2242 > > > 2014-09-03 9:25 GMT-05:00 Simon Slavin : > > >> On 3 Sep 2014, at 1:51pm, Richard Hipp wrote: >> >> > What version of SQLite is installed? >> >> Or rather, when you start the shell tool 'sqlite3' what version does it >> report itself as ? >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems uploading CSV into sqlite3 DB
When I start sqlite double clicking the icon in the Mac Applications folder: Last login: Wed Sep 3 10:13:32 on ttys000 HEPTAGON-GROUP-SAS:~ carlosgorricho$ /Applications/sqlite3 ; exit; SQLite version 3.8.5 2014-06-04 14:06:34 Enter ".help" for usage hints. Connected to a *transient in-memory database*. Use ".open FILENAME" to reopen on a persistent database. sqlite> When starting sqlite on a Terminal Window: Last login: Wed Sep 3 10:11:42 on ttys002 HEPTAGON-GROUP-SAS:~ carlosgorricho$ sqlite3 SQLite version 3.7.13 2012-07-17 17:46:21 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> I downloaded the only precompile binary currently available for Mac in www.sqlite.org website. To perform the job described earlier, I run the Terminal version in batch mode. That is, I created a script file with all the .commands to upload de CSV file, and ran it on a new database: $ sqlite3 newDB.db < my_script_file When I got the tab1 error message, I reverted to performing the job from inside the database, creating first the table and then uploading the data. The result was the huge single-record-field DB I shared earlier. I would venture to say I did this from the Terminal sqlite version, but I am not certain... Thanks in advance for your comments and guidance. Please let me know if I can do anything to help you in other fronts. Saludos/Cheers, *CARLOS A. GORRICHO* Managing Partner Heptagon Group S.A.S. cgorri...@heptagongroup.co Cel COL +57 314 771 0660 Cell USA +1 713 574 2242 2014-09-03 9:25 GMT-05:00 Simon Slavin : > > On 3 Sep 2014, at 1:51pm, Richard Hipp wrote: > > > What version of SQLite is installed? > > Or rather, when you start the shell tool 'sqlite3' what version does it > report itself as ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Problems uploading CSV into sqlite3 DB
Dear all, I am experiencing some difficulties loading CSV data into it. I am using the precompiled binary available on the web site for Mac OSX. I am running OS X 10.9.4 on a Mac Book Pro. When following instructions to upload CSV on a new database: sqlite> *.mode csv* sqlite> *.import My-CSV-file.csv tab1* I get an error message: sqlite> .import BD-Costes-17072014.csv tab1 Error: no such table: tab1 sqlite> I understood that if tab1 did not exist, sqlite3 would create a new one and use the first line in the CSV file as column headers. Then I created the table tab1: sqlite> .tables tab1 sqlite> .schema CREATE TABLE tab1 ( CODIGO DESCRIPCION TIPO_ARTICULO DESCRIPCION_TIPO_ARTICULO NIVEL CODIGO_MATERIAL MATERIAL CàDIGO_SUSTITUTO SUSTITUTO FACTOR FACTOR_X_SKU UNIDAD PRECIO COSTE); sqlite> These are the exact same headers as in my CSV file. I removed the headers from the CSV file and run the .import command again, apparently with no error. When checking for the amount of records uploaded: sqlite> select count(*) from tab1; 1 sqlite> The CSV files has 2651 records. Apparently, all the records are being loaded as one huge string into the first db record, AND the first field: sqlite> select CODIGO from tab1 LIMIT 1; 24873,PULP DURAZNO 0.150 ML X 96,4,PRODUCTO TERMINADO,1,559,COLA PARA PITILLOS HOT MELT ADHESIVE JOWATERM 25030 [OUTPUT GOES ON TO THE END OF THE CSV FILE...] Compare this with a query on the second field: sqlite> select DESCRIPCION from tab1; Error: no such column: DESCRIPCION I have not been able to understand what is going on. I am an intensive user of CSV files for analytical purposes, hence being able to upload them correctly into SQLite3 is key. I am suspecting maybe an environmental variable or something like that needs to be modified, but would not venture without guidance. Looking forward to hearing your feedback. Attached the original CSV file and resulting database. Saludos/Cheers, *CARLOS A. GORRICHO* Managing Partner Heptagon Group S.A.S. cgorri...@heptagongroup.co Cel COL +57 314 771 0660 Cell USA +1 713 574 2242 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Still trying to track down loadable extensions
On Mon, 24 Feb 2014 00:43:40 +0200, Alek Paunov wrote: Just in case if Alessandro Furieri do not follow the list closely Hi Alek, I can simply add few more details to your already excellent introduction. SpatiaLite is a loadable extension adding standard Spatial SQL capabilities to the SQLite's basic core. It's almost conformant to OGC-SFS [1] and ISO SQL/MM-Spatial international standards for Spatial DBMSes and Spatial SQL; roughly speaking it offers a sound and powerful light-weight alternative to the much more complex PostgreSQL/PostGIS client-server Spatial DBMS. It's certainly interesting to note that SpatiaLite has been successfully ported on Android and other mobile or embedded devices. directly included in SpatiaLite are several Virtual Tables implementing the following features: - VirtualText: direct read access by SQL to external CSV and structured text files - VirtualDbf: direct read access by SQL to external DBF files - VirtualXls: direct read access by SQL to external MS XLS spreadsheets (old Excel binary format) - VirtualShape: direct read access by SQL to external ESRI Shapefiles (the de facto standard exchange format for Geographic vector data) - VirtualSpatialIndex: full fledged Spatial Index support based on R*Trees - VirtualNetwork: pure SQL Routing based on a road network (aka graph) [Dijkstra's Shortest Path] - VirtualXPath: standard XML Path Language a further self-standing loadable extension closely related to SpatiaLite is VirtualPG, allowing to establish a client connection to a PostgreSQL/PostGIS server and then supporting direct data exchange between the two DBMSes by standard SQL statements. both SpatiaLite and VirtualPG are released under the MPL "tri-licence"; you can freely choose you best fit license between the GPL, LGPL or the MPL itself. bye Sandro [1] http://portal.opengeospatial.org/files/?artifact_id=25355 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Load spatialite-extension through System.Data.SQLite
On Tue, 14 Jan 2014 20:54:54 +0100, Markus Dibo wrote:
I am trying to use SQLite/Spatialite from a .Net-4.0-Application. To
get easy access to SQLite I am using System.Data.SQLite.
I try to load version 4.1.1 of Spatialite via the command-text
"**SELECT load_extension("libspatialite-4.dll")" which leads to an
Access Violation.
Other users seem to have the same problem:
https://groups.google.com/forum/#!topic/spatialite-users/fOb_J8djVP4
Hi Markus,
more or less similar issues were reported about using splite 4.1.1
on Java/JDBC and Python: so for sure it's not a System.Data.SQLite
specific problem.
you can find more recent news related to this topic on the
SpatiaLite Users list:
https://groups.google.com/forum/#!forum/spatialite-users
and specifcally:
https://groups.google.com/forum/#!topic/spatialite-users/EqJAB8FYRdI
https://groups.google.com/forum/#!topic/spatialite-users/JbcX_k4S1m4
bye Sandro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXPLAIN QUERY PLAN
On Wed, Nov 13, 2013 at 12:20:42AM +, Walter Hurry scratched on the wall: > On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > > > I am trying to speed up our queries and normalize our DB and I am > > reading, > > > > http://www.sqlite.org/eqp.html > > > > But, I am missing a lot. Where do I read about the results and how to > > make changes to the DB to speed up/enhance the DB response? Thanks. > > > Normalize first. > Then work out what queries you need, and add appropriate indexes. > How fast do you need it to be? If it's still not fast enough, consider > denormalizing selectively, and what the overhead will be in maintaining > redundant data. "Normalize 'til it hurts. Denormalize 'til it works." (and in that order!) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER COLUMN in sqlite
On Wed, Oct 16, 2013 at 07:32:08AM -0500, John McKown scratched on the wall: > I'm not truly against such a thing. But we need to remember the main use > for SQLite is to be small, fast, and "embedded". At least as best as I can > tell, it is not meant to compete with MariaDB (nee MySQL) or PostgreSQL. It > doesn't appear to be _committed_ to being 100% SQL compliant (as in exactly > matching the ANSI/ISO requirements). > I can't say for sure, but would somebody want to store 20 million rows > in a SQLite data base? Why? Overall I agree with your point... SQLite is not trying to be-- and shouldn't be-- everything to everyone. It is not trying to compete with or replace large client/server systems. In fact, I think SQLite's success is largely driven by the fact it compliments such products so well. Developers have learned the power and usefulness of RDBMS systems, and want to use that power, experience, and feature set in environments where a full client/server system would be impractical. That said, I don't think it is fair to compare feature set to database size. The complexity of the database system has little to do with the size of the dataset it is expected to handle. The "Lite" in SQLite generally refers to the design, feature set, and "weight" of the overall database engine, not the size of the data it is expected to handle. People choose SQLite for where they need to run it, not what they put into it (except in the extreme degree). So while I agree with your feeling that this kind of feature may be getting into a realm that is outside of SQLite's core use-case and market, that has more to do with how SQLite is used, the class of application that uses it, and the life-cycle of data stored in SQLite. None of these things have to do with the *amount* of data stored in a database. The SQLite team takes some pride (and rightfully so) in how well SQLite scales to extremely large data sets, even with extremely tight memory constraints. When things get really big, generally the biggest performance limitation is the underlying filesystem, not the SQLite engine. And for what it's worth, yes-- I have put 20 million rows in an SQLite database. Considerably more, actually. Some six years ago I imported all the English text portions of Wikipedia into an SQLite database so that I could access all the data on an embedded device that was designed to operate without constant network access. Back then, with some very carefully tuned string compression, you could just squeeze the whole thing onto a 4GB SD card. It was actually that project (and my frustrations with the older SQLite API docs) that lead to me write the "Using SQLite" book. Since then I've done some SQLite related consulting work and fire fighting, and seen SQLite deployed in situations that it really shouldn't have been... like the center of extremely busy and popular websites. While it was the wrong choice for the situation, I have to give SQLite a lot of credit for struggling through the situation. Performance wasn't great, but the simple fact it worked at all was very impressive. > I really am curious. Perhaps I'm "out of touch" (won't be the first time). > My use for SQLite is for storing smallish amount of data which is > dedicated to a single application. My "large" data base needs are > relegated to PostgreSQL data bases. One must remember that different people have radically different definitions of "big" and "small". As data gets bigger and simple off-the-shelf desktops and server systems get more powerful, you also have the situation summed up by one of the data scientists at Strata last year: "'Big' is getting smaller and 'small' is getting bigger." Our core application runs databases in the ~2 TB range (PostgreSQL), which we consider on the smallish side compared to other companies in the same industry. We often export data into SQLite files for personal work or analysis, and at any time I've got a dozen or so SQLite files on my laptop in the 30 to 80 GB range. You can do an amazing amount of data work, on some pretty big data sets, with nothing more than SQLite and a semi-descent laptop (especially if it has an SSD!). > IMO, the "proper" way to do this is just what you outlined. It is a "one > shot" and should not take long to run in most cases. Unless those million > row SQLite data bases are more prevalent than that I had ever thought Returning to the question at hand, yes, that might be the most proper way, but there are still concerns. There are a lot of interdependencies between database objects... tables, views, indexes, foreign keys, and triggers a
Re: [sqlite] ALTER COLUMN in sqlite
On Wed, 16 Oct 2013 07:32:08 -0500, John McKown wrote: I can't say for sure, but would somebody want to store 20 million rows in a SQLite data base? Why? I really am curious Hi John, you could eventually find interesting in some way my own first hand experiences in the Geographic/GeoSpatial field. in this very specific environment (certainly not the most common one, I agree) it's not at all exceptional deploying SQLite/SpatiaLite DB-files as big as 20/40 GB (and even more) containing several tenths/hundredths million rows disseminated in many hundredths different tables (aka layers in GIS jargon) strictly related the one to the other not only in the "classic" relational way based on Primary and Foreign Keys, but even in the "awkward spatial way" based on geometric and topological relationships. there are several good technical reasons suggesting to use exactly SQLite/SpatiaLite for processing, validating and distributing huge geographic datasets: - SQLite/SpatiaLite offers exactly the same overall level of standard Spatial SQL processing capabilities supported by the much more sophisticated (and complex) PostgreSQL/PostGIS; but it's by way simpler to be installed and configured, and it's usually faster under many common working conditions (mainly thanks to its really outstanding first class R*Tree implementation). - not to mention the SQLite's "forbidden weapon"; if your HW supports an adequate amount of RAM you can directly load a whole DB in memory at once; and under such a configuration you can easily reach dramatically impressive supersonic speeds. - you can directly copy / send a whole DB (even a really huge one) from a locations to another in a single shot and in the most painless way, because after all it simply is an ordinary file. - if something goes completely wrong during any complex Spatial data processing operation (it happens ... nobody's perfect), you simply have to remove a single file and then patiently restart yet again from scratch after correcting your buggish Spatial SQL scripts. under the same conditions fully recovering some client/server Spatial DBMS left in an inconsistent state could eventually be a not so pleasant and easy affair. - you can freely ATTACH and DETACH together many DB-files depending on your very variable specific requirements, thus achieving an outstanding and unconstrained flexibility. Quite often this is a really usefull feature, e.g. when you have to collect, integrate and merge together many different datasets presenting a very loose standardization because they were produced during a long period of time by many different subjects for different initial purposes. all this considered, my answer is: "oh yes, it makes perfectly sense storing 20 million rows in a SQLite DB; and it works absolutely well" :-D by Sandro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct PostgreSQL to SQLite connection?
On Thu, 26 Sep 2013 00:01:06 -0700, Alex Mandel wrote: Spatialite I think has a Virtual Postgres table tool too. (You can probably ignore that these are mostly for spatial data, should work fine on regular tables) I'm glad to confirm: SpatiaLite effectively supports a VirtualPG extension module (available under MPL/LGPL/GPL tri-license). please see: https://www.gaia-gis.it/fossil/virtualpg/index and here you can find a very quick tutorial: https://www.gaia-gis.it/fossil/virtualpg/wiki?name=tutorial bye Sandro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dynamically generate SQL statements for SQLite
On Fri, Sep 13, 2013 at 09:19:23AM -0700, dochsm scratched on the wall: > I agree with the other poster. You can just use any string manipluation > routines you like to assemble the SQL into a string and then execute it. > Take care with literals, eg where name = 'fred' might have to become where > name = ''fred'' in Delphi (thats two single quotes at each end). To be more specific, you should never be dealing with literals if you construct an SQL statement. String manipulation is required for dynamic identifiers (table & column names), but *all* dynamic literals should be run through bind parameters. NEVER use string manipulations to deal with literal values... nearly all SQL Injection vulnerabilities could be avoided with this simple rule. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table name length in sqlite affects performance. Why?
On Tue, Sep 03, 2013 at 06:43:52PM -0600, Jared Albers scratched on the wall:
> On my machine, when using relatively short table names like
> `TABLE_{table #}`, creation of a database with 10,000 tables takes
> approximately 14 seconds. These table names vary from 7 to a max of 11
> characters.
>
> When using relatively long table names like `TABLE_{table #}_{some
> unique identifying name that adds 120 or so characters}`, creation of
> a database with 10,000 tables takes approximately 60 seconds.
>
> Creating the database with long table names took over 4 times longer!
>
> Why is this the case? Is this expected behavior or a bug?
You're asking SQLite to deal with 10x the data, and it takes 4x
longer. That seems like a win to me.
Table names are stored as plain-text strings in the sqlite_master
table. Making the names much, much bigger is going to add more data
to the table, including more database pages. While sqlite_master is
not ordered, so the insertion shouldn't require shuffling the
internal B-tree, you're still dealing with a lot more pages, and
syncing all those pages to disk is going to take longer.
Like any other large insert, try wrapping the whole thing in a
transaction. SQLite is one of the few databases that allows
transactions on DDL.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] curious: Why no "shared library" or DLL?
On Fri, Aug 30, 2013 at 03:41:18PM +0100, Simon Slavin scratched on the wall: > The arguments against DLLs are not the normal arguments you see, > they're governed by an unusual combination of four characteristics of SQLite: > > A) SQLite is very much liked, and a huge number of programs use it. > B) SQLite is tiny. You don't waste much disk space by having lots of copies. > C) There are frequent version updates to SQLite which introduce new >facilities. > D) SQLite is detailed and complicated, so updates sometimes break >assumptions made by programmers. I'm not sure that last one is fair. Generally, the SQLite team makes a huge effort to avoid breaking backwards compatibility, both documented and assumed. There are a whole list of known bugs that will never get fixed because they might break existing applications. While there are some changes to the query optimizer that sometimes do odd things, overall this is a pretty poor reason. For me, the biggest issue is A and B, combined with the fact that many applications that very heavily use and integrate SQLite often have a custom compile with specific extensions, default values, and features turned on or off. An application that uses SQLite for its document format, for example, is going to be very intimately tied to their particular configuration of SQLite. It is much easier to just integrate that fine-tuned build directly into the application. The amalgamation encourages this. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] recommended extension entry point name
On Fri, Aug 30, 2013 at 10:37:47AM +0200, Pepijn Van Eeckhoudt scratched on the wall: > http://www.sqlite.org/loadext.html states that: > ...omitting the second argument for the load_extension() SQL > interface - and the extension loader logic will attempt to > figure out the entry point on its own. It will first try the > generic extension name "sqlite3_extension_init". If that does > not work, it constructs a entry point using the template > "sqlite3_X_init" where the X is replaced by the lowercase > equivalent of every ASCII character in the filename... > > The documentation for sqlite3LoadExtension on the other hand says: > The entry point is zProc. zProc may be 0 in which case a > default entry point name (sqlite3_extension_init) is used. Use > of the default name is recommended. > > AFAICT the second description matches what the code actually does. The > example extensions in ext/misc all use sqlite3__init. > This avoids name clashes but makes it a little bit more cumbersome for > people to load the extensions. > > What is the recommended practice for naming the extension entry point? > Should I use sqlite3_extension_init or sqlite3__init? Although using the common name does, in theory, make it a bit easier to hand-load extensions, I've always recommended using a custom entry point, even before the extension loader enhanced to search for the "sqlite3__init()" format. The main benefit to using a common entry point is for people hand-loading modules into the sqlite3(1) command line tool. That tends to be a somewhat rare situation, and when it does happen, it tends to be done by people with a strong working knowledge of SQLite, the extension system, and whatever extension they're trying to load. It means you have to type a bit less, but it is more of a convenience thing then a end-user thing. The disadvantage of using a common entry point is much more significant (IMHO). It is pretty trivial to write a module that can be built into an SO/DLL/DYLIB and loaded dynamically, or use the same code to build the module statically directly into an SQLite library. ...*if* you use a custom entry point. If you use the generic sqlite3_extension_init() entry point, you'll quickly get into namespace issues, and it can get somewhat messy. Easier to just use a custom entry point for all your extensions. Best practices for designing extensions, including entry points are covered in some detail in chapter 9 of the book "Using SQLite". -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Number of Colum!!!
On Tue, Aug 13, 2013 at 07:41:25PM +0530, techi eth scratched on the wall: > Is their any limit on number of Colum in SQLite3 table? Yes. http://www.sqlite.org/limits.html#max_column -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can column name come from a table?
> I was going to say, try using a SQL prepared statement. > > For example, in Postgres you can write something like this: > > execute format( 'SELECT %I FROM table that has that columnName', >(SELECT columnName FROM columnNameTable WHERE condition how to > select limit 1) ); > > But I don't know if SQLite can do that in SQL; you might have to use > the host language. You have to use a host language. Bind parameters in prepared statements also won't work, because you cannot bind an identifier. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing collation to xBestIndex
On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the wall: > But it is the same as with new functions in sqlite3_module. Old SQLite > extensions does not implement xRename function which is now needed. Also, > new feature could be made optional using macro switch like some other > features in SQLite. Actually, xRename() was part of the v1 module interface. xSavepoint(), xRelease(), and xRollbackTo() were added in the v2 interface. All three of these functions are optional, and the fact they were added at the end of the data structure means a v1 module will run under a modern version of SQLite just fine. http://www.sqlite.org/c3ref/module.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
On Mon, Jul 15, 2013 at 08:49:52PM +0200, Paolo Bolzoni scratched on the wall: > >From 35-40MB to 940MB; I would put massif result but I think the > list deletes attachments. By default, the page-cache is 2000. Pages are typically 1KB, but have some minor overhead in the cache. Assuming you haven't turned the page cache down, until you top ~3MB there is nothing unusual at all. -j > On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal wrote: > > On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni < > > paolo.bolzoni.br...@gmail.com> wrote: > > > >> So, sorry if the question sounds very vague. But what can > >> cause high memory usage in sqlite? A large transaction > >> maybe? > >> > > > > What is "high"? In my apps sqlite tends to use 200-400kb or so, which i > > don't consider to be all that high considering what it's capable of doing > > for me. > > > > -- > > - stephan beal > > http://wanderinghorse.net/home/stephan/ > > http://gplus.to/sgbeal > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
