SQLite Forum

Timeline
Login

50 most recent forum posts by user little-brother

2021-12-13
12:42 Reply: Importing XML (artifact: c53e2ff0a4 user: little-brother)

It looks like a VCard format. So the simplest way is to use any online converter e.g. https://www.aconvert.com/document/vcf-to-csv/ to convert your file to csv and then you can easily import it to SQLite database by CLI (or an another visual tool).

2021-12-08
17:43 Reply: Suggestions for learning SQL(ite) (artifact: b330553ded user: little-brother)

Check sql-ex. This is a site to learn SQL through task solving.

2021-10-03
17:16 Reply: install sqlite on windows10 (artifact: cb9e018de0 user: little-brother)

Thanks. I didn't find any version.

2021-09-19
00:47 Reply: FileIO. Extension (artifact: 0eb1b89441 user: little-brother)

Download DB4S then go to extensions\fileio.dll

2021-09-02
17:13 Edit reply: install sqlite on windows10 (artifact: 4a84169869 user: little-brother)

Sqlite3Explorer from Singular in version 3.04

Could you provide a download link? I'm developing a similar tools and to do it better I explore another applications.

17:12 Reply: install sqlite on windows10 (artifact: 36b25baa3d user: little-brother)

Could you provide a download link? I'm developing a similar tools and to do it better I explore another applications.

2021-04-27
10:28 Reply: Virtual Table based on template templatevtab loads OK but "no such module" (artifact: 9b1f4db904 user: little-brother)

You should define xCreate and xDestroy in module structure. They can be the same as xConnect and xDisconnect.

2021-04-12
22:35 Reply: XML extension (artifact: ad789be742 user: little-brother)

One thing to note though is that pugixml is not Namespace aware.

I know about this limitation. I choose pugixml because it's a simple to build and simple to use. It seems to me that namespaces are rare case, therefore it can be skipped.

In any case, I have to add this nuance to code and Wiki. Thank you for pointing that out.

22:13 Edit reply: Noob Question (artifact: f785b35e32 user: little-brother)

Let us suppose, you have a bunch of xls-files in D:/xlsData folder.
You can try to import data within sqlite-gui in an one select without CSV-step.

select odbc_read('Driver={Microsoft Excel Driver (*.xls)};Dbq=D:\xlsData\' || line, 'select * from "Sheet1$"', line)
from exec('powershell Get-ChildItem -Path D:/xlsData -Name')

What happen here:

  • exec(command) returns list of files in folder. Test it:
    select line from exec('powershell Get-ChildItem -Path D:/xlsData<br> -Name')
    
  • odbc_read(DSN, query, target) fetchs data from sheet1 to table named as filename for each row.

Both exec and odbc_read are only sqlite-gui abilities. Check Wiki (link) for odbc and exec extensions.
If you need to fetch data from different sheets then you can create auxilary table aux with columns filename and sheet number

select odbc_read('Driver={Microsoft Excel Driver (*.xls)};Dbq=D:\xlsData\' || line, 'select * from "'|| aux.sheet || '$"', line)
from exec('powershell Get-ChildItem -Path D:/xlsData -Name') e inner join aux on e.line = aux.filename

P.S. For some data odbc_read can be broken. It has a lack of testing.
P.P.S. I'm a author of sqlite-gui :)

22:08 Reply: Noob Question (artifact: 9bfcc6cd83 user: little-brother)

Let us suppose, you have a bunch of xls-files in D:/xlsData folder.
You can try to import data within sqlite-gui in an one select without CSV-step.

select odbc_read('Driver={Microsoft Excel Driver (*.xls)};Dbq=D:\xlsData\' || line, 'select * from "Sheet1$"', line)
from exec('powershell Get-ChildItem -Path D:/xlsData -Name')

What happen here:

  • exec returns list of files in folder. Test it:
    select line from exec('powershell Get-ChildItem -Path D:/xlsData<br> -Name')
    
  • odbc_read fetchs data from sheet1 to table named as filename for each row.

Both exec and odbc_read are only sqlite-gui abilities.
If you need to fetch data from different sheets then you can create auxilary table aux with columns filename and sheet number

select odbc_read('Driver={Microsoft Excel Driver (*.xls)};Dbq=D:\xlsData\' || line, 'select * from "'|| aux.sheet || '$"', line)
from exec('powershell Get-ChildItem -Path D:/xlsData -Name') e inner join aux on e.line = aux.filename

P.S. For some data odbc_read can be broken. It has a lack of testing. P.P.S. I'm a author of sqlite-gui :)

2021-04-11
16:59 Reply: How to get the last row_id in a table without inserting anything (artifact: aa0b66e19f user: little-brother)
select max(id) from t
-- or
select seq + 1 from sqlite_sequence where name = 't'

?

14:16 Post: XML extension (artifact: 1c0294834b user: little-brother)

I made a small extension (link) to processing XML. It uses a pugixml-library to parse XML-tree.
There are five scalar functions and one table-valued. Usage examples can be found in the code and in a Wiki.
xml_valid(xml) xml_extract(xml, xpath, sep = "") xml_append(xml, xpath, insertion, pos = after) xml_update(xml, xpath, replacement) xml_remove(xml, xpath) xml_each(xml, xpath)

It looks like XML-format is more dead than alive replaced by json, yaml and protobuf. And parsing XML is not usual in databases too.
But perhaps this extension will be useful to someone.

The extension can be build by mingw like that: g++ -I ../include -shared xml.cpp ../include/pugixml.cpp -o xml.dll -s -static -DPUGIXML_NO_STL -Os

2021-03-15
16:22 Reply: Are MATERIALIZED and RETURNING keywords? (artifact: 91764a7de6 user: little-brother)

Thanks.

09:44 Post: Are MATERIALIZED and RETURNING keywords? (artifact: 35ce7e4c63 user: little-brother)

They are not in keyword list and the sqlite3_keyword_name() doesn't enumerate them. Is it ok?

2021-01-26
18:06 Reply: Bad practice to open AND attach the same database? (artifact: a28dabb602 user: little-brother)

Thanks. I'm a not a OP, but it's helpfull for me too.

2021-01-25
13:55 Reply: C/C++ API: Is it posiible to clone database connection? (artifact: d67c0e0144 user: little-brother)

I did research deeper for SQLite Studio. It uses a separate (perhaps one at all) thread for SQLite.

When an user run multiply queries then Studio push all of them to an own executable stack and execute them consequentially i.e. Studio doesn't run queries immediately. Studio waits for a running query to complete and then pops the next one off the stack and runs it. So when user press "Interrupt"-button for a not-running-yet query there is no call sqlite3_interrupt, only removal from the stack.

Very simple and works for most cases. But there are also side effects: openning of data editor for table during a long time query freezes GUI.

.

12:32 Reply: C/C++ API: Is it posiible to clone database connection? (artifact: 36dd35de7b user: little-brother)

I refer to a succession of 3 post versions which differ in no way other than timestamp

The last change was a remove double ii (posiible) in the start post title. I don't update a post without changes.

Apply changes in tandem, perhaps iterating over the pool.

Yeah, it's idea. Detect any pragma/attach/dettach command is simple. Thanks.

I would not worry much about ...

I don't worry about performance. I want to allow an user to execute (and interrupt if he needs) long time queries.

12:18 Reply: C/C++ API: Is it posiible to clone database connection? (artifact: db77d40266 user: little-brother)

This must be a pretty stupid user to have such unrealistic expectations

Take a look to most popular SQLite GUIs "SQLite Studio" and "DB Browser for SQLite". They both have tabs. If I attach a database in a one tab-editor then I can referenced to it in an another tab.

DB Browser obviously uses a one connection, so it can't perform queries in parallel (GUI suggests to interrupt the running query and run current one).

SQLite Studio uses some tricky mode.

  1. On the first tab I run begin; create table t (id integer, data text);
    On the second tab I execute rollback and the table t is disappeared.
    Looks like a serialized thread mode with the one shared connection.
  2. On the first tab I run begin; create table t (id integer, data text); WITH RECURSIVE t(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM t LIMIT 10000) ...;
    On the second tab I execute rollback. It's freezing until the long time query will be completed. And after that the table t will be disappeared. Serialized mode!
    But in the same time Studio can stop two different long time queries in different tabs separately.
08:51 Reply: C/C++ API: Is it posiible to clone database connection? (artifact: 036adcbf41 user: little-brother)

Your dummy, no-real-changes editing makes me want to just block you. ...

Impressive. I forgot that this forum engine sorts threads also by edit data. It's an unusual behaviour. I'm used to editing posts if I think it's necessary. Next time I'll use Stack Overflow in order not to annoy you.

keeping them in a pool

Assume the app has 2 tabs and therefore two connections in the pool. In first tab user writes attach database a.sqlite or pragma synchronous = 0. He will expect that these changes will be applyed to both tabs. But there is no mechanizm to sync them. And that is why I can't use "by using common code to get connections to the configuration you want".

The real problem is not management of data held by some sqlite3 struct(s); it is management of the resources referenced by the struct(s)

I don't want to get a real copy. I need the same runtime configuration. I can achieve it by next steps:

  1. Read all pragmas state (or most used and reject others).
  2. Read all attached databases
  3. Apply all pragmas to a new connection
  4. Attach databases
  5. Load extensions for the new connection

But it is a lot of work for me and sqlite. I thought there is a simple way. It does not exist. ОК.

2021-01-24
22:07 Edit: C/C++ API: Is it posiible to clone database connection? (artifact: 8c3d7478b8 user: little-brother)
I have a sql editor with multiply windows (tabs) using a one connection in single-thread or serialized mode. It's ok for short time queries. But a long query blocks a execution in another window.

I want that each windows have similar a db connection configuration e.g. attached databases, loaded extensions, pragmas. I can use multi-thread mode but in this case I should synchronize settings between connections. It looks hard to implement. So I ponder about using a copy of a "template"-connection for each executing query (run a thread, open a connection copy, execute the query, close the connection, destoy the thread). 

That is why I need a way to make a connection copy. But I didn't find this ability in API. Is it possible?
22:06 Reply: C/C++ API: Is it posiible to clone database connection? (artifact: 103fbbae3f user: little-brother)

As I understand, the WAL mode is used to readers do not block writers and a writer does not block readers. My case may be limited to readers.

Let's assume we have a long time query e.g.

WITH RECURSIVE t(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM t LIMIT 10000),
t2(x) as (select x from t order by random())
select avg(t.x * t2.x) from t, t2 order by random()
This statement is executed by typical calls
sqlite3_prepare_v2(...)
while (SQLITE_ROW == sqlite3_step(stmt)) { ... }
sqlie3_finalize(stmt);
There are 3 threading modes: Single-thread, Serialized and Multi-thread.

If steps running in main loop (main thread) in single thread mode, they will stop GUI.
The simpest way to stay GUI responsible is to use progress handler.
But in the single thread I can't run an another query in parallel.

I can use Serialized mode: use one database connection and a separate thread per query.
But there are two problems:

  1. Queries will be running in sequental mode - one by one.
  2. An interrupt operation will be terminate all executing/planned queries.

So I come to multi-thread mode.
But there is problem with the synchronization of connection configs (they all should be same).

17:53 Reply: Getting rid of double DATE() (artifact: beb2446d31 user: little-brother)

Check UPDATE FROM.

08:43 Edit: C/C++ API: Is it posiible to clone database connection? (artifact: 4bb4fc3a98 user: little-brother)
I have a sql editor with multiply windows (tabs) using a one connection in single-thread or serialized mode. It's ok for short time queries. But a long query blocks a execution in another window.

I want that each windows have similar a db connection configuration e.g. attached databases, loaded extensions, pragmas. I can use multi-thread mode but in this case I should synchronize settings between connections. It looks hard to implement. So I ponder about using a copy of a "template"-connection for each executing query (run a thread, open a connection copy, execute the query, close the connection, destoy the thread). 

That is why I need a way to make a connection copy. But I didn't find this ability in API. Is it possible?
08:42 Post: C/C++ API: Is it posiible to clone database connection? (artifact: 729d4ea8d3 user: little-brother)
I have a sql editor with multiply windows (tabs) using a one connection in single-thread or serialized mode. It's ok for short time queries. But a long query blocks a execution in another window.

I want that each windows have similar a db connection configuration e.g. attached databases, loaded extensions, pragmas. I can use multi-thread mode but in this case I should synchronize settings between connections. It looks hard to implement. So I ponder about using a copy of a "template"-connection for each executing query (run a thread, open a connection copy, execute the query, close the connection, destoy the thread). 

That is why I need a way to make a connection copy. But I didn't find this ability in API. Is it possible?
2021-01-12
23:28 Edit reply: ODBC 64bit drivers for windows download (artifact: e5b856a6e3 user: little-brother)

Yeah. I reread topic. You are right. So I deleted my message.

I agree that the issue related to access level. OP should try to run installer as Adminstrator: right click on installator and "Run as Administrator".

23:26 Edit reply: ODBC 64bit drivers for windows download (artifact: c9aaf98de7 user: little-brother)

Yeah. I reread topic. You are right. So I deleted my message.

I agree that the issue related to access level. OP should try to run installer as Adminstrator: right click on intallator and "Run as Administrator".

23:19 Edit reply: ODBC 64bit drivers for windows download (artifact: 97225465f4 user: little-brother)

Yeah. I reread topic. You are right. So I deleted my message.

23:15 Edit reply: ODBC 64bit drivers for windows download (artifact: 3a5361492a user: little-brother)

deleted

23:15 Edit reply: ODBC 64bit drivers for windows download (artifact: ee6ac3c65f user: little-brother)

Yeah. I reread topic. You are right. So I delete my message.

22:48 Edit reply: ODBC 64bit drivers for windows download (artifact: 58a7e74442 user: little-brother)

I am new to SQLite and I have installed SQLite3

I have SQLite installed(SQLiteStudio-3.2.1) no issues, I can query. My ask is if I want to connect to the tables in SQLite through ODBC from a different application, how can I do that.

I suppose that OP used SQLite Studio and he wants to view data in GUI from another databases. The OP's description is vague, so I try to realize what he really needs. Perhaps I'm wrong.

Also OP could get "odbc.dll" from sqlite-gui/extensions and load it to any 32-bit GUI or standard CLI (e.g. ".load odbc.dll").
This extension provides 4 functions (odbc_read, odbc_write, odbc_query and odbc_dsn) to work with another databases through ODBC.

22:28 Edit reply: ODBC 64bit drivers for windows download (artifact: f0bb878b5f user: little-brother)

I am new to SQLite and I have installed SQLite3

I have SQLite installed(SQLiteStudio-3.2.1) no issues, I can query. My ask is if I want to connect to the tables in SQLite through ODBC from a different application, how can I do that.

I suppose that OP used SQLite Studio and he wants to view data in GUI from another databases. The OP's description is vague, so I try to realize what he really needs. Perhaps I'm wrong.

Also OP can extract "odbc.dll" from sqlite-gui/extensions and load it to any 32-bit GUI or standard CLI (e.g. ".load odbc.dll").
This extension provides 4 functions (odbc_read, odbc_write, odbc_query and odbc_dsn) to work with another databases through ODBC.

22:19 Reply: ODBC 64bit drivers for windows download (artifact: 28bf11cad0 user: little-brother)

I am new to SQLite and I have installed SQLite3

I have SQLite installed(SQLiteStudio-3.2.1) no issues, I can query. My ask is if I want to connect to the tables in SQLite through ODBC from a different application, how can I do that.

I suppose that OP used SQLite Studio and he wants to view data in GUI from another databases. The OP's description is vague, so I try to realize what he really needs. Perhaps I'm wrong.

21:45 Edit reply: ODBC 64bit drivers for windows download (artifact: 80b7beda96 user: little-brother)

You could try my app - https://github.com/little-brother/sqlite-gui
It has a basic support for ODBC import through 32-bit ODBC drivers.

  1. Run C:\Windows\SysWOW64\odbcad32.exe
  2. Create DSN to a desired database
  3. Run sqlite-gui and go to Tools > Import via ODBC

It was tested for Access and CSV. I didn't test another databases e.g. PostgreSQL and MySQL.

Download the latest version.

21:45 Edit reply: ODBC 64bit drivers for windows download (artifact: a5438dfc24 user: little-brother)

You could try my app - https://github.com/little-brother/sqlite-gui
It has basic support for ODBC import through 32-bit ODBC drivers.

  1. Run C:\Windows\SysWOW64\odbcad32.exe
  2. Create DSN to a desired database
  3. Run sqlite-gui and go to Tools > Import via ODBC

It was tested for Access and CSV. I didn't test another databases e.g. PostgreSQL and MySQL.

Download the latest version.

20:58 Edit reply: ODBC 64bit drivers for windows download (artifact: ebc8896224 user: little-brother)

You could try my app - https://github.com/little-brother/sqlite-gui
It has basic support for ODBC import through 32-bit ODBC drivers. Tools > Import via ODBC.
It was tested for Access and CSV. I didn't test another databases e.g. PostgreSQL and MySQL.

Download the latest version.

20:50 Reply: ODBC 64bit drivers for windows download (artifact: 2c2b9fcae5 user: little-brother)

You could try my app - https://github.com/little-brother/sqlite-gui
It has basic support for ODBC import through 32-bit ODBC drivers.
It was tested for Access and CSV. I didn't test another databases e.g. PostgreSQL and MySQL.

Download the latest version.

2021-01-08
10:46 Reply: Indexing JSON array contents (artifact: dfd4739c57 user: little-brother)

use generated columns with table-valued functions, then you could create a STORED version of your EXISTS expression

More info here - https://dgl.cx/2020/06/sqlite-json-support :)

2021-01-05
07:27 Reply: Make a time-range query faster when "GROUP BY day" (artifact: 5396a01a9e user: little-brother)

No, both are with timezones (... fetching): AND dt BETWEEN 1600000000+5243600 AND 1600000000+15243600 GROUP BY day")

If 1600000000 is calculated as a local time then is OK.

There is an implicit index on (shop, dt)

Yeah, I was wrong. I drop shop = 'A'-condition and forgot about it :(

2021-01-04
18:20 Edit reply: Make a time-range query faster when "GROUP BY day" (artifact: 35f3fdd2cf user: little-brother)

I can't do a generated column because I need group by day (in localtime)

You group data with timezones, but fetch them without. Hmmm.
Why don't you use indexes on shop and dt? Without them I have a full table scan.

P.S. Aren't strftime('%Y-%m-%d', datetime(dt, 'unixepoch', 'localtime')) and strftime('%Y-%m-%d', dt, 'unixepoch', 'localtime') the same thing?

18:18 Edit reply: Make a time-range query faster when "GROUP BY day" (artifact: 0b9c363382 user: little-brother)

I can't do a generated column because I need group by day (in localtime)

You group data with timezones, but fetch them without. Hmmm.
Why don't you use indexes on shop and dt? Without them I have a full table scan.

18:15 Edit reply: Make a time-range query faster when "GROUP BY day" (artifact: cbd541582b user: little-brother)

I can't do a generated column because I need group by day (in localtime)

You group data with timezones, but fetch them without. Hmmm.

18:06 Edit reply: Make a time-range query faster when "GROUP BY day" (artifact: 47518ec028 user: little-brother)

I can't do a generated column because I need group by day (in localtime)

You group data with timezones, but fetch them without. Hmmm.
Why don't you use indexes on shop and dt?

18:05 Reply: Make a time-range query faster when "GROUP BY day" (artifact: 69b9763095 user: little-brother)

I can't do a generated column because I need group by day (in localtime)

You group data with timezones, but fetch them without. Hmmm.
Why don't you use index on dt?

2021-01-03
17:29 Reply: sqlite-gui application (artifact: af4837b4db user: little-brother)

Looks like I solved the problem by a compiler update. I recompiled dlls and now it is safe from a virustotal's point of view.

2020-12-30
11:03 Reply: sqlite-gui application (artifact: a33ae4c538 user: little-brother)

Thanks for your report. I'll try to fix it.
P.S. I added it as a new issue.

07:28 Reply: sqlite-gui application (artifact: 763e3dab73 user: little-brother)

None of them cause triggering the virus detection. I don't use any of them.
I've been told that a reason is a empty entry point like this. I'm a not skilled C-programmer, so it take a time to grok it.

2020-12-27
17:18 Edit reply: sqlite-gui application (artifact: 3f69ab5d8b user: little-brother)

Thanks a lot! I don't know about this issue because I primarily use Firefox.

This is due to the fact that some antiviruses do not like small files :/

But the fact that this is the reason for the blockage is nonsense.

I recompiled all dll-s with -O option (optimize for speed) and updated releases files to reduce a Chrome paranoia.

17:11 Reply: sqlite-gui application (artifact: 0cb56a5fe5 user: little-brother)

Thanks a lot! I don't know about this issue because I primarily use Firefox.

This is due to the fact that some antiviruses do not like small files :/

But the fact that this is the reason for the blockage is nonsense.

I recompiled all dll-s with -O option (optimize for speed) and update releases files to reduce a Chrome paranoia.

08:32 Reply: sqlite-gui application (artifact: 23b709f688 user: little-brother)

Since 1.3.2 I have added two features

  1. Enable/disable indexes/triggers
  2. Special an editor tab with terminal mode (picture).

Download 1.3.6

P.S. I don't feel as I have right to publish releases here. So I stop it :)

2020-11-18
09:11 Reply: What would be the recommended way to hold an array of ~600 double values? (artifact: 5a0dad7514 user: little-brother)

The current format of these values is in a string.

At least you could use json by adding open and closed brackets [10.23,0,12,...]. It allows you to use json1 function to manipulate data.

How to store data depends on your priorities: CPU, storage size, easy to view/changing and etc. There is no silver bullet.

More ↓