sqlitebrowser/sqlitebrowser

Development discussion for DB Browser for SQLite (previously SQLite Browser)

Chris Locke

@chrisjlocke

OK, have put all that lot into DB4S, and it just swallowed it.
Again - what makes you think it's not working - what isn't working.

Chris Locke

@chrisjlocke

If I add 'blah' as a category, it doesn't like that, which is as expected.

If I enter 'misc', it does.

That what the constraint is for - it'll only allow values you've entered into the categories table.

Chris Locke

@chrisjlocke

In an ideal world, the category table would be like the expenses table and have an ID, and you'd link on that id, not the word itself. Later on, it'll be problematic to rename 'misc' to something else - you'd have to add a new entry and edit every record which points to 'misc'. One line of SQL, but still problematic.

brightinnovator

@brightinnovator

Hi,

I have 5,000 txt files. I want to create a mobile app to do full text search

When i looked for it few suggested the following

a) use sqlite fts5 (or)
b) search-index library

I'm now sure what the drawbacks and how much slower sqlite would be when to do a full text search for 5,000 files which can be converted to 5,00,000 records as each file would have 100 paragraphs and each paragraph can be saved as a row

Is it worth to put 5 lakhs records in SQLite and do a full text search. Will it be faster?

Please let me know in detail

Thanks

sebastianolafhandjojo

@sebastianolafhandjojo

Hi , i would like a request , a check box to enable/disable latest release on startup, because we are behind firewall

Chris Locke

@chrisjlocke

Hi,
I have 5,000 txt files. I want to create a mobile app to do full text search

You don't need SQLite for this. SQLite is a database - to store data. Doesn't sound like your data is changing. Just search the text files for the search text and display any results. Using a database for this would be like using a hammer to make a milkshake. Possible, but unnecessary.

Hi , i would like a request , a check box to enable/disable latest release on startup, because we are behind firewall

If you click 'Edit' then select 'Preferences', you should get a tabbed dialog of many options. The first tab is 'General'.
Near the bottom of this is an option, 'Automatic updates'. If it is ticked, then DB4S will check for updates. If this is unticked, then it won't. Uncheck this option, then click on 'OK' and restart DB4S to confirm you no longer get an error at startup.

sebastianolafhandjojo

@sebastianolafhandjojo

ok thanks didn't realize that

Chris Locke

@chrisjlocke

No problem! Hope it resolves your issue. 😊

TheDemLabs

@TheDemLabs

Suggestions on the easiest way to share data to non-technical users who will be canvassing offline in the field using their phones? The file size could be upto 1m records but only have 6 columns. Search by Name & Date of Birth. Display Address, Phone, Voting status. Volunteers will make any corrections and then upload them when they have connectivity. Right now we are using SQLite to put the data on the phone but I need a simpler interface for volunteers to work with the data. Thanks.

TheDemLabs

@TheDemLabs

@chrisjlocke Hi Chris, Any suggestions on the easiest way to share data to non-technical users who will be canvassing offline in the field using their phones? The file size could be upto 1m records but only have 6 columns. Search by Name & Date of Birth. Display Address, Phone, Voting status. Volunteers will make any corrections and then upload them when they have connectivity. Right now we are using SQLite to put the data on the phone but I need a simpler interface for volunteers to work with the data. Thanks.

Chris Locke

@chrisjlocke

The first problem I see is confidential data - surely this would need to be encrypted somehow? If someone left a phone somewhere it would be easy to access this data.
When you say 'corrections', how much entry is entered?
You mention you're using a SQLite database already, but what app is accessing this on the phone currently?

Selvaganesh

@selvaganesh3m

Some one please help me

Any one here

Please I've doubt

Someone please clarify my doubt

TheDemLabs

@TheDemLabs

The data we are using is publicly available data, but we would like to keep it encrypted on the phone if possible. Corrections will be minor such as an incorrect address or phone number. Or they might a record for a person who is an eligible voter but not currently on the rolls. The information that the volunteers collect will be uploaded when they do have connectivity. Other people will screen and verify the volunteers' input before doing anything with that information. We are using the query tool that comes with SQLite (Filter). But I think this will be hard for many non-technical volunteers and am hoping that there is a more user-friendly app or front end we can use with SQLite.

Chris Locke

@chrisjlocke

Someone please clarify my doubt

Apologies - this chat isn't monitored 24/7, but feel free to comment your doubts and one of the team will respond when they can. 👍

Chris Locke

@chrisjlocke

We are using the query tool that comes with SQLite

I'm still not sure what application you're using on your phones to view/amend the database. SQLite Browser (or Database Browser for SQLite to use it's official title) isn't available for Android/iOS.

There is a handy 'repository' called dbhub.io which allows databases to be uploaded and supports revisions, but again, this works within DB4S which isn't available on a mobile device.

This would be a simple app to create - mainly a search form, then select the entry to view the record, allow any updates which would set a 'date updated' flag, so any records updated in the last X days could then be uploaded. Simple in theory...
While my .Net skills are good, I don't have the skills to create a Xaramin application, which would fit your bill nicely.
As an aside, whats the urgency of this - 'we want something now, now, now!!!' or 'we'll need something quick in a couple of weeks', etc. Could be an interesting project if you want to have a specific application written for you!

Would the volunteers be using Android phones, tablets, or Apple hardware? Or a mix?

Selvaganesh

@selvaganesh3m

My doubt is

# fetch all orders that have 'pepperoni' as one of the toppings (either in pizza-topping or in additional-topping)

I've written a query

SELECT
  o.*
FROM
  tom_orders AS o
  INNER JOIN tom_pizza_toppings AS pt ON pt.pizza_id = o.pizza_id
  INNER JOIN tom_toppings AS t ON t.id = pt.topping_id
WHERE
  t.name = 'Pepperoni'
UNION
SELECT
  o.*
FROM
  tom_orders AS o
  INNER JOIN tom_orders_addl_toppings AS addt ON addt.order_id = o.id
  INNER JOIN tom_toppings AS t ON t.id = addt.topping_id
WHERE
  t.name = 'Pepperoni'

This is my query

TheDemLabs

@TheDemLabs

I found SQLite Mobile Client on the Apple Store and downloaded that. There is a version ($3 onetime) that lets you import CSV files. I've hired a developer to help me with the simple form that would be needed for volunteers to conduct queries and add their notes while in offline mode. Volunteers will be using both Apple and Android devices. All good for now. Thanks.

Selvaganesh

@selvaganesh3m

Chris Locke

@chrisjlocke

So an order (tom_orders) can have one or more tom_toppings which can be either pizza_toppings or addl_toppings. I assume tom_toppings is the main list, while the other two relate to specific orders? I don't think you need a union, but assume that works.
So you've done the query - what is the issue?

Chris Locke

@chrisjlocke

All good for now. Thanks.

Glad you found something that works.

DavidKonicek

@DavidKonicek

Hello. Where to report bugs I found in SQLiteBrowser? Here? Somewhere else? Thanks.

Chris Locke

@chrisjlocke

In DB4S if you click 'Help' and 'Bug Report' it'll take you to the issues page on GitHub. You'll need a GitHub account.
https://bit.ly/submitBug

It's not recommended to submit it here, as it can't be easily followed up or referred to later on. The 'Issues' section on GitHub is the best place.

brightinnovator

@brightinnovator

I have a question on javascript. can someone help me on the javascript issue?

brightinnovator

@brightinnovator

I want to read 3 crore csv rows which is of 2GB csv file size and need to insert into MySQL via Java.

Could someone please help me know the fastest and memory efficient way to avoid out of memory exception as well load in lesser time?

Please kindly advise.

David Reilly

@greyblue9

MySQL or sqlite?

Selvaganesh, what is the result you get? Does it not match your expectation?

David Reilly

@greyblue9

I think you'll have trouble if the coloms in the two topping tables aren't exactly the same unless you explicitly select the common columns.

flywire

@flywire

Is there a demo for creating a report with a GUI?

Chris Locke

@chrisjlocke

There are no reporting capabilities within DB4S - it manages the database only.

I have a question on javascript

This is primarily a support chat area for DB4S which is a database management tool. I don't believe the users familiar in it are also familiar with Javascript.

flywire

@flywire

Can you recommend a GUI report tool(s) that might go with it?

btw, Can I get Gitter to send me an email for responses?

Chris Locke

@chrisjlocke

https://www.helicalinsight.com/open-source-bi-tool-for-sqlite/ ? There are paid-for solutions, but I always tend to drift towards open-source offerings first.

Can I get Gitter to send me an email for responses?

If you click the settings (assuming you're on the desktop browser version) there are notification options in there. Click 'Notifications' and not 'Settings' (once you've clicked the settings icon, I mean...)

flywire

@flywire

:thumbs up: x2

Aaron Mason

@thirdwheel

Is there a way to turn off the suggestion box that pops up as you're entering data in the Browse Data tab? It is useful from time to time but there are times when it slows me down

Chris Locke

@chrisjlocke

Where communities thrive

People

Repo info

Activity