[sqlite] convert zip to sqlar file

Hi, I'd like to convert zip files to sqlar files. Is there a command line tool that I can help with the conversion? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

On 3/8/20, Keith Medcalf wrote: > > On Sunday, 8 March, 2020 21:24, Peng Yu wrote: > >>When I open an sqlite3 db using the following python code, > >>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY) > >>, I got the following error. > >>T

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

I see this. What does it mean? Does it mean even when the sqlite3 session is closed there is still -wal and -shm left on the disk? """ There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as

[sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

When I open an sqlite3 db using the following python code, conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY) , I got the following error. Traceback (most recent call last): File "/xxx.py", line 21, in for x in c.execute('SELECT (data) FROM sqlar'): File "src/cursor.c",

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

> Does it work when you close Firefox? If it works when Firefox is closed but > not when Firefox is open, then the answer is probably no. I can check the content when Firefox is closed (the -wal file disappears after Firefox is closed). Why the database can not be read by another sqlite3

[sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

Hi, I am trying to see what tables are available in sqlite_master from firefox cookies sqlite3 fire. ~/Library/Application Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite But the error message says "Error: database is locked". I see a cookies.sqlite-wal file in the same

[sqlite] Make sqlite3 database searchable on Mac OS X

Hi, It seems that sqlite3 databases are not searchable by Spotlight on Mac OS X. Is there a way to make them searchable? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

But it doesn't explain what is "the temp database". Is it an actual database file? Or it is just a in-memory database? On 2/1/20, Simon Slavin wrote: > On 1 Feb 2020, at 1:30pm, Peng Yu wrote: > >> https://www.sqlite.org/lang_createtable.html >> >> &q

[sqlite] Is the table created by "create temp table" in the database file or in memory?

Hi, https://www.sqlite.org/lang_createtable.html "The database in which the new table is created. Tables may be created in the main database, the temp database, or in any attached database." It is not clear to me in the doc. If a table is created by "create temp table", is it temporarily a

Re: [sqlite] Is there something like PATH to search for extension libraries?

OK. This was not clear to me by reading https://www.sqlite.org/loadext.html. Maybe it should be included by the maintainer of that page. On 2/1/20, Keith Medcalf wrote: > > On Friday, 31 January, 2020 21:15, Peng Yu wrote: > >>I have to specify either a full path (either relativ

[sqlite] Is there something like PATH to search for extension libraries?

Hi, I have to specify either a full path (either relative or absolution) to use .load. But it would be more convenient if there is something like PATH (may be named as something like SQLITE3_LIB_PATH) to search for library files. Is it available in sqlite3? Thanks.

Re: [sqlite] log() in sqlite3

don't need to do the more tedious: >> a) Get original data with statement 1 >> b) Do calculations in the outside program >> c) Use the result in statement 2 >> >> >> -Original Message- >> From: sqlite-users On >> Behalf Of Peng Yu >>

Re: [sqlite] Is there a way to yield an error when opening a nonexistent dbfile?

What is the recommended way to do so in python? I see the following two ways. ``` db = sqlite3.connect('file:/path/to/database?mode=ro', uri=True) fd = os.open(filename, os.O_RDONLY) c = sqlite3.connect('/dev/fd/%d' % fd) os.close(fd) ```

[sqlite] log() in sqlite3

Hi, I see that many math functions (like log()) are not in sqlite3. It seems that SQL standard doesn't have them. https://www.sqlite.org/lang_corefunc.html But since sqlite3 contains non-standard functions anyway. Would it be considered to add those functions? Given the current version of

[sqlite] Is there a way to yield an error when opening a nonexistent dbfile?

Hi, By default the command sqlite3 will just open a dbfile if it does not exist. Suppose that I just want to perform read-only operations in a sqlite3 session, I will not need to create a non-exsitent file. Rather, I want the sqlite3 to fail when the dbfile does not exist. Is there a way to

[sqlite] Import data from stdin to sqlite3 in python

Hi, I see this post uses Pandas to import data to sqlite3. https://datatofish.com/create-database-python-using-sqlite3/ But I don't want to make my code depend on Pandas. I'd like to use something like ".import /dev/stdin" to directly import data from stdin in python. Is it possible? Thanks.

Re: [sqlite] Default isolation_level for sqlite3.connect?

The following is still very confusing. https://docs.python.org/3.8/library/sqlite3.html#controlling-transactions """ autocommit mode means that statements that modify the database take effect immediately." ... The Python sqlite3 module by default issues a BEGIN statement implicitly before a

Re: [sqlite] conditional insert operations

te.org/lang_UPSERT.html) > > create unique index tbl_uidx_h1_h2 on tbl (h1, h2); > > insert into tbl values ('a', '', 'X') > on conflict (h1, h2) > do update set v = excluded.v > where v is not excluded.v; > > > -Original Message- > From: sqlite-users On Behalf &g

Re: [sqlite] Default isolation_level for sqlite3.connect?

I still have a hard time to understand what the difference is according to the python manual. It keeps saying see somewhere else in the python manual. But I don't see where it explains the differences between the differences comprehensively and clearly.

Re: [sqlite] conditional insert operations

t; few examples of "here's what used to be in there", "here's what I want to > insert", "here's what it should like in the end" > > > > -Original Message- > From: sqlite-users On Behalf > Of Peng Yu > Sent: Thursday, January 30, 2020 11:27 AM > T

Re: [sqlite] Default isolation_level for sqlite3.connect?

On 1/30/20, Keith Medcalf wrote: > > The isolation_level specifies the default suffix to put after 'BEGIN' when > beginning a transaction. Inside the library the following is used when the > magic wants to start a transaction: > > if isolation_level is not None: >.execute('BEGIN %s' %

Re: [sqlite] Default isolation_level for sqlite3.connect?

> and if you don't specify one it issues a plain "begin;" So that is basically isolation_level = None? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] conditional insert operations

Hi, Suppose that I have a table with three columns h1, h2, v. I want to delete all rows with h1=a, and insert rows like the following (data shown in TSV format), only if there is not an entry with h1=a and h2="" (empty), it exists but its v is not equal to a value X. a,A,v1 a,B,v2 ...

Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

Thanks. What is the purpose of contextlib. If I just use `cur = conn.cursor()`, what problems it will cause? > with contextlib.closing(conn.cursor()) as cur: -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Default isolation_level for sqlite3.connect?

Hi, I don't see what is the default isolation_level here. Is it None? Thanks. https://docs.python.org/3/library/sqlite3.html#module-functions-and-constants sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])¶ -- Regards, Peng

[sqlite] Where is the journal file for locking_mode=EXCLUSIVE?

https://www.sqlite.org/tempfiles.html The above page says that there should be a journal file. "The PERSIST journal mode foregoes the deletion of the journal file and instead overwrites the rollback journal header with zeros, which prevents other processes from rolling back the journal and thus

[sqlite] What is a "journal"?

Hi, https://www.sqlite.org/lockingv3.html#rollback "When a process wants to change a database file (and it is not in WAL mode), it first records the original unchanged database content in a rollback journal. The rollback journal is an ordinary disk file that is always located in the same

Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

to implement this correctly. On Thu, Jan 30, 2020 at 12:43 AM Keith Medcalf wrote: > > On Wednesday, 29 January, 2020 22:45, Peng Yu wrote: > > >In python sqlite3 program, if I call .execute() multiple times then > >call .commit(). Does it ensure that all the sqlit

Re: [sqlite] Is it necessary to encode() for file names in sqlar format?

that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-Original Message----- > >From: sqlite-users On > >Behalf Of Peng Yu > >Sent: Wednesday, 29 January, 2020 22:57 > >To: SQLite mailing list > &

[sqlite] Is it necessary to encode() for file names in sqlar format?

I use the following python3 code to create sqlar file. Is it necessary to sys.argv[2].encode('utf-8') in the line of execute()? In other word, does the native sqlar tools inteprete the name column as an encoded value or a non-encode value? Thanks. import sqlite3 conn=sqlite3.connect(sys.argv[1])

[sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

In python sqlite3 program, if I call .execute() multiple times then call .commit(). Does it ensure that all the sqlite3 commands specified by execute()'s either all take effect or none effect? In other words, if any error occurs while running the sqlite3 commands specified in execute(), what

[sqlite] Is mutliple-execute-one-commit slower than multiple single-execute-single-commit?

Hi, I have two python programs using sqlite3. They function the same, except the following. In the first, execute() is called in batches and then commit() is called following them. In the second, commit() is called after each execute(). It seems that the second case is faster (I can not separate

[sqlite] Is sqlite3 smart enough to remove redundant command?

Suppose that I have the following command, which writes two entries with the same key. So the 1st entry will be overwritten by the 2nd entry. Therefore, there is no need to write the 1st entry. Is sqlite3 smart enough to not to write the 1st entry? Or it will write both the 1st entry and the 2nd

Re: [sqlite] Where is in_transaction of sqlite3.Connection?

Forget about. I think in_transaction is only available in python3 but not python2. On 10/25/19, Peng Yu wrote: > https://docs.python.org/3.9/library/sqlite3.html > > The manual says in_transaction is an attribute of sqlite3.Connection. > But I don't see it. Why? > > "&qu

Re: [sqlite] What does commit actually do?

The manual says this. "The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE)." > If you did NOT specify "isolation_level = None" in the .connect() call then > you probably ARE in a

Re: [sqlite] What is the default value of isolation_level?

t; > > -Original Message- > From: sqlite-users On Behalf > Of Peng Yu > Sent: Friday, October 25, 2019 3:49 PM > To: SQLite mailing list > Subject: [sqlite] What is the default value of isolation_level? > > Hi, > > isolation_level is an empty string by default as s

[sqlite] What is the default value of isolation_level?

Hi, isolation_level is an empty string by default as shown below. But the python manual does not say that it can take an empty string. What does an empty string mean? Is it equivalent to None? Thanks. https://docs.python.org/3/library/sqlite3.html#connection-objects """ isolation_level Get

[sqlite] Where is in_transaction of sqlite3.Connection?

https://docs.python.org/3.9/library/sqlite3.html The manual says in_transaction is an attribute of sqlite3.Connection. But I don't see it. Why? """ in_transaction True if a transaction is active (there are uncommitted changes), False otherwise. Read-only attribute. """ ==> main.py <==

Re: [sqlite] What is the default value of isolation_level?

> if the isolation_level is None then no extra command is issued. > if the isolation_level is '' then the command BEGIN TRANSACTION; is issued > if the isolation_level is 'DEFERRED' then the command BEGIN DEFERRED > TRANSACTION; is issued > if the isolation_level is 'IMMEDIATE' then the command

[sqlite] What does commit actually do?

The python manual just tell me what I should do but it is not very clear what commit() actually does under the hood. https://docs.python.org/2/library/sqlite3.html """ commit() This method commits the current transaction. If you don’t call this method, anything you did since the last call

Re: [sqlite] Why not allow subsecond file modification time in sqlar format?

But what about `sqlite3 -Atv`? I think it only supports INT time? On 10/23/19, Richard Hipp wrote: > On 10/23/19, Peng Yu wrote: >> Hi, >> >> https://www.sqlite.org/sqlar.html >> >> Only integer is allowed for last modification time. Why not allow >> su

[sqlite] Why not allow subsecond file modification time in sqlar format?

Hi, https://www.sqlite.org/sqlar.html Only integer is allowed for last modification time. Why not allow subsecond file modification time in sqlar format, given subsecond time is allowed in many OSes? Thanks. mtime INT, -- last modification time -- Regards, Peng

Re: [sqlite] Why sqlite3 running at different time can have very different speed?

> You can try clearing Linux file system cache to convince > yourself that cache misses contributes to performance drop. > > Run this as root: > > # sync; echo 3 > /proc/sys/vm/drop_caches I am on Mac OS X. Is there anything equivalent? Thanks. -- Regards, Peng

Re: [sqlite] Why sqlite3 running at different time can have very different speed?

s How to prove the large time difference of sqlite3 is indeed due to cache and where is the cache? Why the caching used by sqlite3 can not persist for a longer time? On Sat, Oct 19, 2019 at 7:29 PM Keith Medcalf wrote: > > > On Saturday, 19 October, 2019 18:05, Peng Yu wrote:

[sqlite] Why sqlite3 running at different time can have very different speed?

Hi, I see that sqlite3 can be very different in terms of run time. $ time sqlite3 file.sqa -Atv > /dev/null real0m3.259s user0m0.193s sys0m0.704s $ time sqlite3 file.sqa -Atv > /dev/null real0m0.148s user0m0.086s sys0m0.057s # Then do some else briefly $ time

Re: [sqlite] Network file system that support sqlite3 well

> > I will need to use the actual files to test for dependency (just as > > the dependency that can be used by GNU make) > > I don’t understand what that means. You want to use a makefile that checks > the mod date of the database? Suppose A is a sqlite3 db file, B is some other file which is

Re: [sqlite] Network file system that support sqlite3 well

On 10/16/19, Simon Slavin wrote: > On 15 Oct 2019, at 11:47pm, Peng Yu wrote: > >> Is there a solution that are known to fill in this niche? Thanks. > > Unfortunately, no. Multiuser SQLite depends on locking being implemented > correctly. The developers haven't found any

Re: [sqlite] Network file system that support sqlite3 well

> I know for sure that IBM's GPFS guarantees locking. I think GPFS is "global > parallel file system". It is a distributed file system. But it will be > rather slow. If only few jobs run in parallel, all will be ok. Locking will > always guarantee database integrity. > > With lots of jobs, you

Re: [sqlite] Network file system that support sqlite3 well

> Why not use an actual client-server database system like MySQL? It's > optimized for this use case, so it incurs a lot less disk (network) I/O. I will need to use the actual files to test for dependency (just as the dependency that can be used by GNU make). With just database tables in MySQL,

[sqlite] Network file system that support sqlite3 well

Hi, I'd like to use sqlite3 db files on many compute nodes. But they should access the same storage device for the sqlite3 db files. The directory storing the db files looks the same on any compute node logically---the storage is mounted at the same mount point on the compute nodes. To achieve

Re: [sqlite] How to install REGEXP support?

My question is `But it is not clear how to install it for sqlite3 installed by homebrew.` On 9/19/19, Warren Young wrote: > On Sep 18, 2019, at 8:33 AM, Peng Yu wrote: >> >> But I don't want to always specify a full path. I am asking where is >> the standard place to pu

Re: [sqlite] How to do setdiff but update the original table by the result?

Not relevant. See a previous follow up reply of mine. On Wed, Sep 18, 2019 at 11:21 AM Simon Slavin wrote: > > On 18 Sep 2019, at 3:37pm, Peng Yu wrote: > > > I'd like to perform setdiff. I think this should be relevant. But it > > does not update the original ta

[sqlite] The best way to check if a file is a sqlite3 DB file?

Hi, I use `file` to check if a file is a sqlite3 DB file. But I have to parse the result. Is there a better way to just return an exit status of 0 for a sqlite3 DB file and 1 otherwise? Thanks. $ file /tmp/tmp.erZ5aS6PUX.sqa /tmp/tmp.erZ5aS6PUX.sqa: SQLite 3.x database, last written using SQLite

Re: [sqlite] How to do setdiff but update the original table by the result?

> I want to find all the rows in table1 that are not in table2 and then delete > those rows from table1. It is the above. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] How to do setdiff but update the original table by the result?

Hi, I'd like to perform setdiff. I think this should be relevant. But it does not update the original table. I want to update the original table by the result. Is there a command to do so? Thanks. https://www.sqlitetutorial.net/sqlite-except/ -- Regards, Peng

Re: [sqlite] How to install REGEXP support?

But I don't want to always specify a full path. I am asking where is the standard place to put the library file so that I don't have to always specify the whole path. On 9/17/19, Jens Alfke wrote: > >> On Sep 17, 2019, at 8:13 AM, Peng Yu wrote: >> >> I can compile it. Wher

Re: [sqlite] How to install REGEXP support?

/sqlite3ext.h ./lib ./lib/libsqlite3.0.dylib ./lib/pkgconfig ./lib/pkgconfig/sqlite3.pc ./lib/libsqlite3.dylib ./lib/libsqlite3.a ./README.txt ./share ./share/man ./share/man/man1 ./share/man/man1/sqlite3.1 On Mon, Sep 16, 2019 at 7:58 PM Warren Young wrote: > > On Sep 16, 2019, at 6:24 PM, P

Re: [sqlite] How to install REGEXP support?

Do you know what the commands are to just compile for the regex support? > SQLite doesn't come with a regexp implementation; it has to be added by an > extension. > I searched the website; there's a simple implementation here: > >

[sqlite] How to install REGEXP support?

Hi, I'd like to have regexp support in sqlite3. https://www.sqlite.org/lang_expr.html#regexp But it is not clear how to install it for sqlite3 installed by homebrew. Does anybody how to install it? Thanks. https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query --

Re: [sqlite] Merge two sqlar files

OK. I made a mistake. UNION of multiple tables should work. On Fri, Sep 6, 2019 at 7:28 PM Peng Yu wrote: > > Hi, I think that there is no way to UNION 3 tables in one command is > it. What is the best way to merge 3 and more sqlar files? Thanks. > > On Fri, Sep 6, 2019 at 6:30 P

Re: [sqlite] Merge two sqlar files

but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users On Behalf > >Of Peng Yu > >Sent: Friday, 6 September, 2019 17:04 > >To: SQLite mailing list > >Subject: [sqlite] Merge two sqla

Re: [sqlite] Merge two sqlar files

OK. It works. But it seems that `.ar` is not necessary. import sqlite3 infile1, infile2, outfile = sys.argv[1:] conn=sqlite3.connect(outfile) c=conn.cursor() c.execute('ATTACH DATABASE ? AS d1', [infile1]) c.execute('ATTACH DATABASE ? AS d2', [infile2]) c.execute('CREATE TABLE sqlar AS SELECT *

[sqlite] Merge two sqlar files

Hi, UNION is for tables in the same db file. Is there an easy command to combine two sqlar files? Thanks. https://www.sqlitetutorial.net/sqlite-union/ -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Is pcre available on homebrew?

I'd like to use regex. https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query But I don't find pcre. https://formulae.brew.sh/formula/sqlite Does anybody know how to make regex available for slqite3 installed by homebrew? Thanks. -- Regards, Peng

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

> Nope. If there was a problem in closing down they can hang around (which is > their whole point for recovery). Also if a journal mode of "persit" was > used. But mostly from incorrect closure. > > So check for any -journal, -wal, or -shm files of the same name if you want > to obliterate a

Re: [sqlite] lockfile but using db table row instead of file

On 9/4/19, Keith Medcalf wrote: > > On Wednesday, 4 September, 2019 04:40, Peng Yu wrote: > >>The command line program `lockfile` locks based on files. I wants to do the >>same thing but based on a row in a table. For example (this is just one >>example feature, bu

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

> If you delete the database file then make sure you also delete any other > files that might have been associated with it, such as left over journals > and so forth. I never see those extra files in practice. Are they guaranteed to be deleted automatically once an SQLite session is finished? --

[sqlite] lockfile but using db table row instead of file

The command line program `lockfile` locks based on files. I wants to do the same thing but based on a row in a table. For example (this is just one example feature, but all other features should also be preserved), if a given row is there, the process will hang unless some other process delete the

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

but > still took 40-60 mins from memory. > > Rob > > On 4 Sep 2019, at 11:18, Peng Yu wrote: > > > For now, I just delete the db file if it exists already. So that I > > don’t > > need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the > > db &g

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

For now, I just delete the db file if it exists already. So that I don’t need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the db file can be faster than the latter. Is it so? On Tue, Sep 3, 2019 at 3:06 PM Simon Slavin wrote: > On 3 Sep 2019, at 8:57pm, Peng Yu wr

[sqlite] What concurrency level is of sqlite?

Hi, In other words, if two processes write to the same sqlite file but to different tables, will one wait for the other? What if to the same table but different rows? Thanks. -- Regards, Peng ___ sqlite-users mailing list

[sqlite] How to create a new table or overwrite an existing table in one command?

Hi, If I try to create a table that already exists, sqlite will give me an error. Is there way to issue one command to create a table, but if there is already a table with the same name, drop it then create the new table? Thanks. -- Regards, Peng ___

[sqlite] What is the zlib compression level used by sqlar?

It is not clear what level of zlib compression is used in sqlar. Does anybody know? https://sqlite.org/sqlar/doc/trunk/README.md Is the following python code guarantee to generate a file that conform to the sqlar format standard? (Note that I deliberately set the timestamp and permission to zero

Re: [sqlite] strip off file metadata in sqlar

I just need the return status of sqldiff (as `cmp -s`). Is there an option to suppress all the screen output? Thanks. On 8/27/19, Warren Young wrote: > On Aug 27, 2019, at 2:40 PM, Peng Yu wrote: >> >> Where is the binary or source code of sqldiff? > > The source is part o

Re: [sqlite] strip off file metadata in sqlar

Where is the binary or source code of sqldiff? I don't find sqldiff in the package sqlite installed in homebrew. I also don't find sqldiff after compiling sqlite-autoconf-329.tar.gz. > https://www.sqlite.org/sqldiff.html -- Regards, Peng ___

[sqlite] sqlar: provide file content from stdin

Hi, https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line To add something to sqlite3, I must provide an actual file. sqlite3 alltxt.sqlar -Ac *.txt What if I just want to specify the `name` field in the resulted .sqlar file in the command line, but take the file

[sqlite] strip off file metadata in sqlar

The .sqlar files contain file metadata. I'd like two .sqlar files to be exactly the same (`cmp` should return 0) when they store the same content. Is it possible to strip off all metadata of stored files? Thanks. $ sqlite3 my.sqlar -Atv -rwxr-xr-x181 2019-08-27 15:18:27 main.sh --

Re: [sqlite] database like file archive

> The standard "sqlite3" command-line tool will read and write SQLite > archive files. See the documentation at > https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line OK. So there is basically no need to install the sqlar command since all features from the sqlar

Re: [sqlite] database like file archive

> There is the sqlar archive format, which you can test using the official > sqlite3 command line shell. There is also a library for it as part of > the Sqlite3 repository. > > https://www.sqlite.org/sqlar.html > https://sqlite.org/sqlar/doc/trunk/README.md This is good to know. How to install

[sqlite] database like file archive

Hi, I haven't found an archive format that allows in-place delete (I know that .zip, .7z and .tar don't). This means that whenever delete is needed, the original archive must be copied first. This can be problematic when the archive is large and the file to delete is small. Something along the

Re: [sqlite] How lock is implemented upon write?

> Here's my guess. OP is trying to implement locking in Python. OP sees that > SQLite does locking and wants to copy code. > > Obviously, that's beyond the range of this mailing list, but just to be > helpful, here's some stackoverflow: > >

Re: [sqlite] How lock is implemented upon write?

So the fundamental C API that unix_os.c is based on is the only following but nothing else? - fcntl: http://pubs.opengroup.org/onlinepubs/9699919799/functions/fcntl.html - flock: https://www.freebsd.org/cgi/man.cgi?query=flock=2 On 7/3/19, Jens Alfke wrote: > >> On Jul 3, 2019, at 6:20

Re: [sqlite] How lock is implemented upon write?

on them, it is hard for me to make my own toying implementation on the locking mechanism to understand the nuts and bolts. On 7/3/19, Keith Medcalf wrote: > > On Wednesday, 3 July, 2019 11:59, Peng Yu wrote: > >>As I said in my original message "I am trying to understand how l

Re: [sqlite] How lock is implemented upon write?

As I said in my original message "I am trying to understand how lock is implemented in sqlite3". On 7/2/19, Keith Medcalf wrote: > > On Tuesday, 2 July, 2019 13:26, Peng Yu : > >>I not sure how to use os_unix.c. Are there any easy to follow >>examples in pytho

Re: [sqlite] How lock is implemented upon write?

I not sure how to use os_unix.c. Are there any easy to follow examples in python? On Mon, Jul 1, 2019 at 9:08 PM Simon Slavin wrote: > You might want to take a look at the standard VFSen: > > > > At a low level, SQLite depends on the

[sqlite] How lock is implemented upon write?

Hi, I am trying to understand how lock is implemented in sqlite3. But the following manual page is too much such that I can figure out the main low level implementation techniques. https://www.sqlite.org/draft/lockingv3.html Could anybody provide some equivalent python code (other high level

Re: [sqlite] compressed sqlite3 database file?

What do you recommend for Mac? Thanks. On 4/10/19, Simon Slavin wrote: > On 10 Apr 2019, at 7:08pm, Peng Yu wrote: > >> https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos >> >> I work on Mac. Would this be worthwhile to try

Re: [sqlite] compressed sqlite3 database file?

> > Wout. > > On Wed., Apr. 10, 2019, 5:04 p.m. Warren Young wrote: > > > On Apr 9, 2019, at 11:39 PM, Peng Yu wrote: > > > > > > Is there a way to make the database file of a size comparable (at least > > > not over 5 times) to the original TSV ta

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

On 4/10/19, Keith Medcalf wrote: > > The first column is of strings ... > > Do you mean a single string as in "KerfufledAllaHasbalah" > Or a "bunch of strings with some implied delimiter" such as > "Kerfufled/Alla/Hasballah" where "/" is the separator between strings? > > If the latter, the data

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

I don't know specifically what you refer to as data normalization. My guess is something like this. But it is irrelevant to my case. https://www.studytonight.com/dbms/database-normalization.php For my specific TSV file, it has about 50 million rows and just two columns. The first column is of

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

n the much larger disk space required, for an occasional search of the data, it seems that it makes no sense to use sqlite3 if disk space is a major concern. On 4/10/19, Peng Yu wrote: >> > Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in >> > any way human re

[sqlite] Does sqlite3 support alter table add primary key?

Hi, I got the following error. Does sqlite3 support alter table add primary key? sqlite> alter table mytab add primary key (h1); Error: near "primary": syntax error https://www.tutorialspoint.com/sqlite/sqlite_primary_key.htm -- Regards, Peng ___

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

> > Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in any > > way human readable without uncompressing it first. But to store the file (and occasionally search the data), I would prefer 278MB instead of 1.5GB. With a .gz file, at least I can zcat and zgrep. > How big is the

[sqlite] compressed sqlite3 database file?

I have some TSV table in .gz format of only 278MB. But the corresponding sqlite3 database exceeds 1.58GB (without any index). Is there a way to make the database file of a size comparable (at least not over 5 times) to the original TSV table in the .gz file? Thanks. -- Regards, Peng

[sqlite] How many concurrent sqlite3 connections are allowed?

Hi, I have many concurrent sqlite3 connections from many python scripts. I am not sure this causes some deadlock condition, but my scripts do not show any progress after some initial progress. Is it that somehow many open connections can cause problems? Thanks. -- Regards, Peng

[sqlite] What is the most commonly used file extension for sqlite3 database files?

Hi, I see a variety of extensions for sqlite3 database files, such as .db, .db3, .sqlite3 and .sq3. Is there a most commonly used/accepted convention for the extension? Thanks. -- Regards, Peng ___ sqlite-users mailing list

[sqlite] Is there a way to select using cid?

There are cid's for each table. Is there a way to use "select" with cid's instead of their names? Thanks. $ sqlite3 dbfile

[sqlite] Row locking sqlite3

Hi, I see that sqlite3 still does not support row locking. This package tries to resolve this problem. But it does not have a standard build process for Linux. https://github.com/sqlumdash/sqlumdash/ Are there other packages similar to sqlite3 but support row locking? Thanks. -- Regards, Peng

[sqlite] How to import TSV table with double quote in it without having to escape double quote

Hi, I got the following error. I don't want to escape the quote charaters in the input. Is there still a way to import quote characters into a sqlite3 table? $ cat my.sql3 create table mytab (var text); .separator "\t" .import /dev/stdin mytab $ cat main.sh #!/usr/bin/env bash # vim: set

Re: [sqlite] How to dump all the tables into separate files?

Could you provide the working code for bash (on Mac OS X or linux)? Thanks. On Fri, Dec 8, 2017 at 7:52 AM, Luuk <luu...@gmail.com> wrote: > On 08-12-17 14:13, Simon Slavin wrote: >> >> >> On 8 Dec 2017, at 7:02am, Peng Yu <pengyu...@gmail.com> wrote: >&

  1   2   >