SQLite Forum

Timeline
Login

50 events by user anonymous occurring around 2021-10-26 11:00:36.

More ↑
2021-11-02
04:28 Post: NFS 4 that is guaranteed to work with Sqlite artifact: 49fe87db53 user: anonymous

Hi,

we see that NFS implementations might have issues with locking because of which sqlite might not function properly. There is a proper documentation available on this from Sqlite: https://www.sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations I need to know if the problem is across all versions of NFS or just version 3. Is NFS 4 version is guaranteed to work with Sqlite.

2021-11-01
18:08 Reply: sqlar : how to remove files artifact: e76208001a user: anonymous

Documentation gap: the stand-alone sqlar README doesn't mention any deletion capability. You have to read the source to discover the existence of the -d option.

05:22 Post: Unable to open the connection artifact: c394ccb1ff user: anonymous
I am using SQLite DB with .Net Core.
I would like to keep the DB created outside the application context @ c: drive so that multiple process can access it .
Able to create the connection and get connection object but unable to open connection. I tried read/write access to that drive.

please help
04:33 Reply: sqlar : how to remove files artifact: 59d6eddc96 user: anonymous

You seem to be correct there isn't a command to remove a file from the archive, but can use the SQL DELETE command to delete files from an archive, by typing a SQL command at the sqlite> prompt, such as: delete from sqlar where name='db.sqlar';

2021-10-31
02:55 Reply: Request: Allow VACUUM INTO with a new page size even in WAL mode artifact: b291f0bd66 user: anonymous

Thanks!

2021-10-30
06:03 Post: No reference to .NETFramework.... artifact: 5e06dd6c13 user: anonymous

I'm trying to build using either the build.bat with VS 2019 and getting the following error

Your project does not reference ".NETFramework,Version=v4.7"

Prior to build I followed the instructions here http://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki

What am I missing?

thanks.

05:59 Post: How to insert TEXT data from SQL server to SQLite with SQLite ODBC Driver artifact: fcd13b88f8 user: anonymous
Greetings, I didn't manage to find same problem here on any other platform, so'll try luck and ask here :). I'm struggling with inserting text data FROM SQL server TO SQLite. The SQLite is as linked server. I'm using SQLite ODBC Driver from Mr. Werner. 
When I try to insert float/int there is no problem, but when it comes to inserting str/datetime to target column thats type is text I ran into error "Das abfragebasierte Einfügen oder Aktualisieren von BLOB-Werten wird nicht unterstützt".

From what I've understood for some reason the query has the feeling that sqlite target columns data type is BLOB, which is not. 

I've also tried cast & convert data type inside query on various sql server text types.

Only thing that worked but not perfectly, is create blob col in sqlite table & transform inside query to nvarbinary(max) and then insert text data. 

Any help is appreciated.

SYSTEM:
SQL SERVER 2017 - 64bit
SQLite 3.32.3
SQLITE ODBC driver for Win64
Windows 10 Pro
2021-10-29
12:51 Post: Insert operation leads to exception on Windows when installed using msi artifact: d550ce0911 user: anonymous

The msi installed exe is unable to insert data on sqlite on OS build 19042.1288+ with below exception call stack

Framework Version: v4.0.30319

Description: The process was terminated due to an unhandled exception.

Exception Info: System.Data.SQLite.SQLiteException at System.Data.SQLite.SQLite3.Reset(System.Data.SQLite.SQLiteStatement) at System.Data.SQLite.SQLite3.Step(System.Data.SQLite.SQLiteStatement) at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(System.Data.SQLite.SQLiteCommand, System.Data.CommandBehavior) at System.Data.SQLite.SQLiteCommand.ExecuteReader(System.Data.CommandBehavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(System.Data.CommandBehavior) at CromptonConfigurator.CustomMeter.CUA_SaveClick(System.Object, System.Windows.RoutedEventArgs) at System.Windows.EventRoute.InvokeHandlersImpl(System.Object, System.Windows.RoutedEventArgs, Boolean) at System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject, System.Windows.RoutedEventArgs) at System.Windows.Controls.Primitives.ButtonBase.OnClick() at System.Windows.Controls.Button.OnClick() at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(System.Windows.Input.MouseButtonEventArgs) at System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate, System.Object) at System.Windows.RoutedEventHandlerInfo.InvokeHandler(System.Object, System.Windows.RoutedEventArgs) at System.Windows.EventRoute.InvokeHandlersImpl(System.Object, System.Windows.RoutedEventArgs, Boolean) at System.Windows.UIElement.ReRaiseEventAs(System.Windows.DependencyObject, System.Windows.RoutedEventArgs, System.Windows.RoutedEvent) at System.Windows.UIElement.OnMouseUpThunk(System.Object, System.Windows.Input.MouseButtonEventArgs) at System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate, System.Object) at System.Windows.RoutedEventHandlerInfo.InvokeHandler(System.Object, System.Windows.RoutedEventArgs) at System.Windows.EventRoute.InvokeHandlersImpl(System.Object, System.Windows.RoutedEventArgs, Boolean) at System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject, System.Windows.RoutedEventArgs) at System.Windows.UIElement.RaiseTrustedEvent(System.Windows.RoutedEventArgs) at System.Windows.Input.InputManager.ProcessStagingArea() at System.Windows.Input.InputManager.ProcessInput(System.Windows.Input.InputEventArgs) at System.Windows.Input.InputProviderSite.ReportInput(System.Windows.Input.InputReport) at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr, System.Windows.Input.InputMode, Int32, System.Windows.Input.RawMouseActions, Int32, Int32, Int32) at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr, MS.Internal.Interop.WindowMessage, IntPtr, IntPtr, Boolean ByRef) at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr, Int32, IntPtr, IntPtr, Boolean ByRef) at MS.Win32.HwndWrapper.WndProc(IntPtr, Int32, IntPtr, IntPtr, Boolean ByRef) at MS.Win32.HwndSubclass.DispatcherCallbackOperation(System.Object) at System.Windows.Threading.ExceptionWrapper.InternalRealCall(System.Delegate, System.Object, Int32) at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(System.Object, System.Delegate, System.Object, Int32, System.Delegate) at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(System.Windows.Threading.DispatcherPriority, System.TimeSpan, System.Delegate, System.Object, Int32) at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr, Int32, IntPtr, IntPtr) at MS.Win32.UnsafeNativeMethods.DispatchMessage(System.Windows.Interop.MSG ByRef) at System.Windows.Threading.Dispatcher.PushFrameImpl(System.Windows.Threading.DispatcherFrame) at System.Windows.Application.RunDispatcher(System.Object) at System.Windows.Application.RunInternal(System.Windows.Window)

But is able to insert when ran in administrative mode. How to resolve this issue?

08:43 Reply: --skip option does not work correctly. artifact: 9c6d904a61 user: anonymous

Thanks for reproducing the issue and fix. I appreciate your accomplishment.

08:43 Reply: --skip option does not work correctly. artifact: a636d33924 user: anonymous

Thanks for reproducing the issue and fix. I appreciate your accomplishment.

2021-10-28
23:22 Post: Request: Allow VACUUM INTO with a new page size even in WAL mode artifact: 033f2c9d1f user: anonymous

The page size being fixed while in WAL mode is a perfectly reasonable limitation. However, I don't see how those reasons apply to VACUUM INTO, which creates a new database without changing the original one.

The patch below seems to do what I want without breaking anything else.


Index: src/vacuum.c
==================================================================
--- src/vacuum.c
+++ src/vacuum.c
@@ -247,12 +247,14 @@
   if( rc!=SQLITE_OK ) goto end_of_vacuum;
   rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0);
   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 
   /* Do not attempt to change the page size for a WAL database */
-  if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
-                                               ==PAGER_JOURNALMODE_WAL ){
+  if( !pOut
+      && sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
+                                               ==PAGER_JOURNALMODE_WAL
+  ){
     db->nextPagesize = 0;
   }
 
   if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0)
    || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0))


13:44 Post: Sqlite 1.0.111.0 download not found artifact: 72d5b4ca6a user: anonymous

Hi Team, Where I could find Sqlite 1.0.111.0 download.

03:13 Post: --skip option does not work correctly. artifact: 4c0816c24f user: anonymous

When csv file 1st line is as below, --skip option does not work correctly.

,,,,,,,,,,,,,,,,,,,,,,,,,,,

If I inserted some keyword for 1st comma,like

anykeyword,,,,,,,,,,,,,,,,,,,,,,,,,,,

This will work fine. Is this restriction of --skip option? or Starting with , is not allowed in csv format?

2021-10-27
17:49 Reply: download checksum doesn't match artifact: 0ff92e66f3 user: anonymous

Thank you for the clarification.

17:29 Post: download checksum doesn't match artifact: 12e55fd170 user: anonymous

The sha3 checksum listed for sqlite-tools-win32-x86-3360000.zip on https://sqlite.org/download.html does not match the sha3 checksum generated locally for the file from sha3sum under Ubuntu 20.04 LTS, running under WSL 2 on Windows 10. Has this issue been noted?

15:03 Post: database creation getting hung up on a NFS mount path artifact: 218bf4ac5d user: anonymous

I am trying to create create database on a NFS mount point I create a db connection using sqlite3_open call.. As per the documentation the default VFS using on unix OS is 'unix.'
The execution gets stuck at the function sqlite3_exec

Now, if I register any other VFS like unix-excl using sqlite3_open_v2 call.. then it works fine.

The NFS s/c version used is 3.

I have few questions around this:

  • Why does VFS 'unix' not work for NFS? Is it version dependent?
  • The process just gets hung up on the sqlite3_exec. Can there be a way to exit from this situation?
2021-10-26
18:17 Reply: Get SQLITE_PROTOCOL error when opening database artifact: 4bda3c9b0e user: anonymous

I am doing nothing special for the compilation. I just added the 3 files that were the wrapper for SQLite to my project as normal. Then I added winsqlite3.dll and .lib files to the project also. Everything compiles with no errors. I did not no about the MACRO, so I will check that out. While waiting for an answer I was messing around with the project and I did something (don't know what) but now the open function works. I can only guess that I fixed the project scope with the database file because that was the only thing I was changing. Strange!!! I am happy now.

18:06 Reply: Get SQLITE_PROTOCOL error when opening database artifact: 2cece78f9d user: anonymous

The file is in my home folder for the project hence the lack of any path on the file. SQLite says the full path is not necessary so I did not use it. The reason I dumped the code that it was not working. I have used the wrapper previously in another project that was written in MFC and it worked flawlessly. I always try to use things that work, but in this case I was following the example on sqlite.org first, and it did not work. I moved on because this has been so frustratingly hard to get to work.

16:37 Reply: Get SQLITE_PROTOCOL error when opening database artifact: 61d6aa44a2 user: anonymous

Update - I dumped all the code above and tried a different scheme. I used a wrapper for SQLite3 from https://github.com/neosmart/CppSQLite and used the same .lib and .dll files. Everything compiles and I get a different error, which is 14 or SQLITE_CANTOPEN. I suspected that was the problem. Why it was one off? Here is the code.

CppSQLite3DB m_dB; const char* m_szFile = "db.sqlite3"; const char* m_szFilesTable = "Files";

void UI_Database::InitializeDatabase(void) { bool fileTableExists = false; const char* ver; int verNumb = 0;

ver = CppSQLite3DB::SQLiteHeaderVersion();
ver = CppSQLite3DB::SQLiteLibraryVersion();
verNumb = CppSQLite3DB::SQLiteLibraryVersionNumber();

try
{
    // Open the database.
    m_dB.open(m_szFile);

    // Check if database contains the 'Files' table.
    fileTableExists = m_dB.tableExists(m_szFilesTable);

    if (fileTableExists == false)
    {
    }
}
catch (CppSQLite3Exception& e)
{
    // Show the exception.
    ver = e.errorMessage();
}

}

Everything works except for getting an exception when open on the database is called. It returns SQLITE_CANTOPEN. I think it is getting blocked from opening by WinRT, but I don't know how to give it access. I just want to confirm my suspicions. Thanks.

15:40 Reply: No ".open" option in version 3.7.17 2013-05-20 artifact: 715037eaca user: anonymous

Thanks... I will copy the db, install several database engines and try to import the db into those engines to see which one recognizes it... thanks a lot for your time and patience :-)

15:20 Reply: No ".open" option in version 3.7.17 2013-05-20 artifact: 93938f101c user: anonymous

0 0 0 0 0 0 0 0 M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R 0 357 R 0 0 0 0 0 0 0 0 0 0 0 0 0 0 357 R 0 0 0 0 0 ...

14:39 Reply: No ".open" option in version 3.7.17 2013-05-20 artifact: 1a9f396689 user: anonymous

Hi, thanks. It said it is of type "data"

14:32 Post: Get SQLITE_PROTOCOL error when opening database artifact: 2cdfe4f3f4 user: anonymous
Hi, Can anyone help with this error? I am using this code in UWP C++/WinRT

sqlite3** m_dB;
const char* m_szFile = "DataBase/db.sqlite3";
char* sqlite3_temp_directory;

void UI_Database::InitializeDatabase(void)
{
    int sqlError = SQLITE_OK;
    
    LPCWSTR zPath = ApplicationData::Current().TemporaryFolder().Path().data();
    char zPathBuf[MAX_PATH + 1];
    memset(zPathBuf, 0, sizeof(zPathBuf));
    WideCharToMultiByte(CP_UTF8, 0, zPath, -1, zPathBuf, sizeof(zPathBuf), NULL, NULL);
    sqlite3_temp_directory = sqlite3_mprintf("%s", zPathBuf);

    sqlError = sqlite3_open(m_szFile, m_dB);

    if (sqlError == SQLITE_OK)
    {
    }
}

All I every get for sqlError is 15 (SQLITE_PROTOCOL). It never tries to create the file if missing. I suspect it might be a permissions thing in Win10. Thanks.
12:38 Reply: No ".open" option in version 3.7.17 2013-05-20 artifact: 7a5a24d36c user: anonymous

I did so and can read text/words does make sense, but there was no "SQLite format 3" text on the head...

Does this mean that 1) the db is an sqlite db (why could I not get any .table nor .schema? it says file is not a database of it is encrypted 2) the db is not an sqlite db but its encoding could be read with "strings myfile.db | head"?

I also run "strings myfile.db more" and seen
  1. What would you recommend me to get the schema of this db and manage to read it?
  2. Can I dump somehow these strings to a kind of csv textfile?
11:00 Reply: No ".open" option in version 3.7.17 2013-05-20 artifact: 8d10f33d81 user: anonymous

Thanks, I did so and then typed ".tables" and nothing appeared... does this mean, does the db is not an sqlite db? (because I now for certain, that the db is not empty)

09:12 Post: No ".open" option in version 3.7.17 2013-05-20 artifact: ccc79ad508 user: anonymous

Hi, I am trying to open a db in my server directory but the command ".open" is not recognized. When I type ".help", the ".open" is not listed... How can I open then the db? Thanks!

2021-10-22
18:48 Post: Documentation bug regarding max SQL length artifact: ba0e448646 user: anonymous

Hi, We recently saw SQLite error code regarding queries being too large. On checking the error code page, the docs mention limit as 1,000,000 (https://www.sqlite.org/rescode.html#toobig)

However the actual limit is 1,000,000,000 as documented here: https://www.sqlite.org/limits.html#max_sql_length, and in the code

https://sqlite.org/src/file?name=src/sqliteLimit.h&ci=trunk

/*
** The maximum length of a single SQL statement in bytes.
**
** It used to be the case that setting this value to zero would
** turn the limit off.  That is no longer true.  It is not possible
** to turn this limit off.
*/
#ifndef SQLITE_MAX_SQL_LENGTH
# define SQLITE_MAX_SQL_LENGTH 1000000000
#endif

Wanted to confirm that this indeed is just a documentation issue, and I'm not misreading the default value.

Regards, Kanishka

11:36 Reply: SELECT STATEMENT FOR OGR2OGR IN GIS artifact: db3ea1f39c user: anonymous

Do not concatenate with + but use double pipe

06:59 Reply: Inconsistency in BETWEEN Query artifact: 38c1428c5c user: anonymous

For SQLite the integer can be up to 8 bytes and will casting as "INTEGER" solve the problem without overflow?

My Query will be;

SELECT * FROM my_table WHERE 4011110000001 BETWEEN CAST(col_a AS INTEGER) AND CAST(col_b AS INTEGER)

06:40 Reply: Inconsistency in BETWEEN Query artifact: 42cbaee951 user: anonymous

I can't use "int" because of the length of the numbers. If I cast the query as "long", can you guarantee that all query ranges will run without errors?

My Query will be; SELECT * FROM my_table WHERE 4011110000001 BETWEEN CAST(col_a AS LONG) AND CAST(col_b AS LONG)

2021-10-21
22:06 Post: SELECT STATEMENT FOR OGR2OGR IN GIS artifact: 5adbcde819 user: anonymous

I seek the syntax to create APN numbers that are text strings with and without dashes eg XXX-XXX-XXX & XXXXXXXXX with a CASE statement?

CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN LEFT(APN_D, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + RIGHT(APN_D, 3) ELSE APN_D

THe error "near "(": syntax error" any assistance is MUCH appreciated.

13:49 Reply: Inconsistency in BETWEEN Query artifact: 09df8cd9e2 user: anonymous

Yes col_a and col_b is TEXT.

12:38 Post: Inconsistency in BETWEEN Query artifact: e7c9c8b21b user: anonymous
Hello I am working on an Android SQLite project and can not find a way to get right value, The program takes an input value from the user and searches it in the database, I want to do a query like; if the input value is 3; look through the col_a and col_b if the value is between col_a and col_b prints the col_c value "AA" and col_d value "BB". Like col_a < 3 <col_b prints AA, BB. If col_a < 7 < col_b prints CC, DD.

I am doing a query with the "BETWEEN" but it returns an inconsistent value.

My Table;
ID  |        col_a        |        col_b        |  col_c  |  col_d 
--------------------------------------------------------------------
1   |    4011110000000    |    4011119999999    |   AA    |   BB   
--------------------------------------------------------------------
2   |    4022220000000    |    4022229999999    |   CC    |   DD    
--------------------------------------------------------------------

My Query;
SELECT * FROM my_table WHERE 401111000001 BETWEEN col_a AND col_b

You can see the values in column col_a and col_b is 13 digits long and the query value is 12 digits long. The query returns the item with the ID:1. If I put a 13 digits long value(4011110000001) it also returns the row with ID:1. The problem is 12 digits long value is not in between among the values to be checked.

Android Java Code;
String sql = "SELECT * FROM my_table WHERE ? BETWEEN col_a  AND col_b";
Cursor cursor = db.rawQuery(sql, new String[] {String.valueOf(x)});

if (cursor.moveToNext()) {
    String element1 = cursor.getString(cursor.getColumnIndexOrThrow("col_c"));
    String element2 = cursor.getString(cursor.getColumnIndexOrThrow("col_d"));
    String element3 = cursor.getString(cursor.getColumnIndexOrThrow("ID"));
    cursor.close();
    Log.d(""," " +element1);
    Log.d(""," " +element2);
    Log.d(""," " +element3);
}
2021-10-20
23:13 Reply: How do i submit a bug report artifact: 41e35286df user: anonymous

Thank you very much for the info. I do not know a whole lot regarding how sqlite uses lemon and i have also contacted the creators of lemon. I just thought i would point it out (regardless of the criticality of the vulnerability because i came across it and felt obligated. Could you please go into more detail on how sqlite uses lemon to generate C code?

Thanks, Cyber Security Researcher

10:56 Reply: INDEX usage on inequality and IN artifact: 882fa9fdf4 user: anonymous

Another example is with LIKE operator if you do WHERE LIKE 'TEST%' explainer tells you will use index WHERE NOT LIKE 'TEST%' just scan table

but you could end up in an example like that in the NOT LIKE statement gives you less result than the first

06:16 Reply: INDEX usage on inequality and IN artifact: 51a888675d user: anonymous

Ok understand, but suppose you have 1.000.000 records and only 4 possible values for type. So basically you have 25% of type=1 25% type=2 and so on... So if I have an index on type and I do WHERE type!=1 the index can help me to skip 25% of records, is it correct? Otherwise how can I rewrite my query to benefit from index?

01:31 Post: How do i submit a bug report artifact: 1da3fa19b8 user: anonymous

Hello. I am a cyber security student. I have found multiple memory corruption vulnerabilities within the lemon LALR parser resulting in controlling multiple registers. I believe that a return to libc exploit is likely to be possible and i would like info on how to properly report this and go through responsible disclosure.

I have done a lot of crash analysis in gdb and I have screenshots and further documentation that the vulnerability does indeed exist.

Thanks, Cyber Security Researcher.

2021-10-19
14:47 Post: INDEX usage on inequality and IN artifact: cf5a6d385f user: anonymous

Hi all, i write you below some line to reproduce a "strange" behavior I can't understand about using or not index. Why do you think parse engine act like that, and how can I rewrite the query to force sqlite use index?

create table table_a(id INTEGER PRIMARY KEY AUTOINCREMENT,type INTEGER); CREATE INDEX IF NOT EXISTS index_a ON table_a(type);

explain query plan select * from table_a WHERE id=1; QUERY PLAN --SEARCH TABLE table_a USING INTEGER PRIMARY KEY (rowid=?) explain query plan select * from table_a WHERE id!=1; QUERY PLAN --SCAN TABLE table_a

explain query plan select * from table_a WHERE id in (1); QUERY PLAN --SEARCH TABLE table_a USING INTEGER PRIMARY KEY (rowid=?) explain query plan select * from table_a WHERE id not in (1); QUERY PLAN --SCAN TABLE table_a

explain query plan select * from table_a WHERE type=1; QUERY PLAN --SEARCH TABLE table_a USING COVERING INDEX index_a (type=?) explain query plan select * from table_a WHERE type!=1; QUERY PLAN --SCAN TABLE table_a

explain query plan select * from table_a WHERE type IN (1); QUERY PLAN `--SEARCH TABLE table_a USING COVERING INDEX index_a (type=?)

explain query plan select * from table_a WHERE type NOT IN (1); QUERY PLAN `--SCAN TABLE table_a

2021-10-18
15:38 Reply: sqlite3_busy_handler missing artifact: e5242272b8 user: anonymous

I cannot be sure if all data readers and commands were manually disposed but I suspect so (it's been already some years since I debugged this). What I do know, as I have a mechanism in place to track of all open connections, is that when this problem appeared all opened connections had already been properly closed.

I believe that it is part of the contract of IDbConnection.Close() that it should actually close the connection (when no pooling is active) and if some command or reader tries to execute it should fail. So the native connection staying open after being explicitly closed seems to me an undesirable behavior.

Thank you for formally exposing the busy handler so that I can finally get rid of all the reflection stuff I had to use.

12:00 Reply: x86_64/amd64 sqlite3 binaries artifact: 1dba1c6bc4 user: anonymous

the win64 version build from source is about 30% faster than the win32 version by my testing.

with t6(n) as (select 1 union all select n+1 from t where n<6)
,
b3(a,b,c,bit)as(select a.n,b.n,c.n,(1<<(a.n-1))+(1<<(b.n-1))+(1<<(c.n-1)) from t6 a,t6 b,t6 c where a.n<b.n and b.n<c.n)
,
b9(b)as(select c.bit*((1<<(r.a-1)*6)+(1<<(r.b-1)*6)+(1<<(r.c-1)*6)) from b3 c,b3 r)
,
t(n,c,x,y)
as(select 1,'1',1,1 union all select n+1,cast(n+1 as varchar),n/6+1,n%6+1 from t where n<6*6)
,
s(lv,n,nlist,blist,px,py)
as(select 1,t.n,t.c,1<<(t.n-1),pow(10,t.x),pow(10,t.y) from t where t.n<=6 
union all
select lv+1,b.n,nlist||','||c,blist+(1<<(b.n-1)),px+pow(10,b.x),py+pow(10,b.y)
from s,t b 
where lv<10 and s.n<b.n 
and (instr(px+pow(10,b.x),3)=0 and instr(py+pow(10,b.y),3)=0)
--and(select max(sum(case when a.x=b.x then 1 end), sum(case when a.y=b.y then 1 end))from t a 
--  where ((blist+(1<<(b.n-1))&(1<<(a.n-1)))>0))<=2
and b.n  between lv/2*6+1 and lv/2*6+6*2
)
select * from s where lv=10 and (lv<3 or not exists(select 1 from b9 where  (blist & b9.b) =0)) ;

2021-10-16
14:47 Reply: Compiling sqlite3 with readline support artifact: c4b3ae5154 user: anonymous

../sqlite/configure --with-tcl=/usr/local/lib/tcl/tcl8.6 --enable-all --disable-editline --enable-readline

The above worked on OpenBSD 7.0, with TCL installed from pkg_add. No need to muck around with symlinks.

07:50 Reply: Temp database artifact: d871cfd0d9 user: anonymous

Thanks Keith. I need clarification on a some aspects:

1.In a new CLI session,

PRAGMA temp_store_directory;

returns nothing. (If I assign to it, it returns what I assigned). How do I find the current/default location?

2.Consider this session:

sqlite> attach ':memory:' as AUX; sqlite> attach '' as TMP; sqlite> .mode column sqlite> select * from pragma_database_list(); seq name file 0 main 2 AUX 3 TMP

A. Why do I never find 1 in the seq column? (first column)

B. The file column values is identical for :memory: and '' - why?

3.As with :memory: I can attach as many '' (temp) databases as long as the name/alias is unique. Correct?

07:30 Reply: binary .dump format artifact: dd8ff6dfae user: anonymous

Are you sure you won't get better deduplication with backup API + VACUUM?

2021-10-15
15:44 Reply: Build fails with SQLITE_OMIT_WAL and SQLITE_MAX_MMAP_SIZE=0 artifact: f34eb00302 user: anonymous

(The previous response wasn't from me. I'm the original author of this thread, probably trying to solve a different use case).

I'm removing as much as possible functionality from SQLite and actually tried to define SQLITE_OMIT_DISKIO as well (there are several things which look like they would need major changes, though). I'm using SQLite as an in-memory data processor without any disk IO.

09:50 Reply: Temp database artifact: cf0b2066e3 user: anonymous

Perfect! Thank you.

08:57 Post: Temp database artifact: 1c28585044 user: anonymous

If possible, how do I open a temp database?

I specify ':memory:' for an in-memory database; what is the string for opening/attaching a temporary database?

2021-10-14
14:47 Reply: Pragma_Function_List() artifact: 130b2f8c14 user: anonymous

name builtin type enc narg flags max 1 s utf8 ¯1 2099200 max 1 w utf8 1 2097152 min 1 s utf8 ¯1 2099200 min 1 w utf8 1 2097152

I assume narg stands for the number of arguments; if so, how do I interpret -1?

13:22 Post: Pragma_Function_List() artifact: 76ab9d8b72 user: anonymous

name builtin type enc narg flags pow 1 s utf8 2 2099200 group_concat 1 w utf8 1 2097152

How do I interpret (or what are the meanings of):

  • type (s = ? w = ?)
  • flags
09:06 Post: sqldiff for Windows 10 x 64 bits artifact: 31ba540c4a user: anonymous

Hi all, I tried to run sqldiff.exe on a Windows 10 x 64 bits and does not start... I guess there should be a 64 bit version as the 32 bits one is not compatible, but I cannot find it anywhere. Can anybody help me finding it? Thanks,

2021-10-13
19:31 Reply: binary .dump format artifact: 4870e9b119 user: anonymous

You can do it in one step with VACUUM INTO.

More ↓